""" MoinMoin - sctable a Processor for spread sheet calculations by sc @license: GNU GPL, see COPYING for details. PURPOSE: This processor is used to do some spread sheet calculation based on sc in a regular wiki table. The first column/first line coordinate is A0. CALLING SEQUENCE: {{{ #!sctable [-column_header, -row_header, -show_formular, -format ] }}} OPTIONAL INPUTS: -column_header: additional in the result the column header is shown -row_header: additional in the result the line number header is shown -show_formular: if set the formular instead of the result is shown, data is arranged in textmode. Blanks in formulars are removed -format: is used to the set the number of digits for the column values EXAMPLE: {{{ SUM over columns}}} {{{ #!sctable ||1||2||=A0+B0|| ||10||20||=@sum(A1:B1)|| }}} RESULT: ||<)>1.00||<)>2.00||<)>3.00|| ||<)>10.00||<)>20.00||<)>30.00|| ------ {{{ cell B1 no data}}} {{{ #!sctable ||A||B||C||D|| ||1||||2||=A1+C1|| }}} RESULT: ||<(>A||<(>B||<(>C||<(>D|| ||<)>1.00||<)>||<)>2.00||<)>3.00|| ----- {{{ SUM over rows}}} {{{ #!sctable ||1||2||=A0+B0|| ||10||20||30|| ||=@sum(A0:A1)||=@sum(B0:B1)||=@sum(C0:C1)|| }}} RESULT: ||<)>1.00||<)>2.00||<)>3.00|| ||<)>10.00||<)>20.00||<)>30.00|| ||<)>11.00||<)>22.00||<)>33.00|| ----- {{{ SUM over rows and columns}}} {{{ #!sctable ||A||B||C|| ||1||2||=A1+B1|| ||10||20||=@sum(A2:B2)|| ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)|| }}} RESULT: ||<(>A||<(>B||<(>C|| ||<)>1.00||<)>2.00||<)>3.00|| ||<)>10.00||<)>20.00||<)>30.00|| ||<)>11.00||<)>22.00||<)>33.00|| ----- {{{ -column_header}}} {{{ #!sctable -column_header ||1||2|| ||3||4|| ||5||6|| }}} RESULT: ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''|| ||<)>1.00||<)>2.00|| ||<)>3.00||<)>4.00|| ||<)>5.00||<)>6.00|| ----- {{{ -row_header}}} {{{ #!sctable -row_header ||1||2|| ||3||4|| ||5||6|| }}} RESULT: ||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00|| ||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00|| ||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00|| ----- {{{ -column_header -row_header}}} {{{ #!sctable -column_header -row_header ||1||2|| ||3||4|| ||5||6|| }}} RESULT: ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''|| ||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00|| ||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00|| ||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00|| ----- {{{ -show_formular -column_header -row_header}}} {{{ #!sctable -show_formular -column_header -row_header ||m||p|| ||1||=A1 * 5|| ||2||=A2-3|| ||3||4|| }}} RESULT: ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''|| ||<)5%#CCCCCC>'''0'''||<(>m||<(>p|| ||<)5%#CCCCCC>'''1'''||<(>1||<(>=A1*5|| ||<)5%#CCCCCC>'''2'''||<(>2||<(>=A2-3|| ||<)5%#CCCCCC>'''3'''||<(>3||<(>4|| ----- {{{ -column_header and blanks in cells}}} {{{ #!sctable -column_header ||Name Vorname|| || || 3 || || 5|| ||Name Vorname|| 1 || 2 || || 4 || 5|| ||Name Vorname|| 1 || 2 || || || 5|| }}} RESULT: ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||<:#CCCCCC>'''D'''||<:#CCCCCC>'''E'''||<:#CCCCCC>'''F'''|| ||<(>Name Vorname|| || ||<)>3.00|| ||<)>5.00|| ||<(>Name Vorname||<)>1.00||<)>2.00|| ||<)>4.00||<)>5.00|| ||<(>Name Vorname||<)>1.00||<)>2.00|| || ||<)>5.00|| ----- {{{ -format 1,1}}} {{{ #!sctable -format 1,1 ||1||2|| ||3||4|| ||=@sum(a0:a1)||=a2*4|| }}} RESULT: ||<)>1.0||<)>2.0|| ||<)>3.0||<)>4.0|| ||<)>4.0||<)>16.0|| ----- {{{ useage of variable names -show_formular -column_header -row_header }}} {{{#!sctable -show_formular -column_header -row_header ||A||B||C|| ||1||{two}2||=A1+two|| ||10||20||=@sum(A2:B2)|| ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)|| }}} RESULT: ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''|| ||<)5%#CCCCCC>'''0'''||<(>A||<(>B||<(>C|| ||<)5%#CCCCCC>'''1'''||<(>1||<(>{two}2||<(>=A1+two|| ||<)5%#CCCCCC>'''2'''||<(>10||<(>20||<(>=@sum(A2:B2)|| ||<)5%#CCCCCC>'''3'''||<(>=@sum(A1:A2)||<)>=@sum(B1:B2)||<(>=@sum(C1:C2)|| and if we calculate [[BR]] RESULT: {{{#!sctable -column_header -row_header ||A||B||C|| ||1||{two}2||=A1+two|| ||10||20||=@sum(A2:B2)|| ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)|| }}} ----- {{{color in cells }}} {{{#!sctable ||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''|| ||<)#dddddd>1||<)#dddddd>{two}2||<)#cccccc>=A1+two|| ||<(>10||<)>20||<:#dddddd>=@sum(A2:B2)|| ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)|| }}} RESULT: ||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''|| ||<)#dddddd>1.00||<)#dddddd>2.00||<)#cccccc>3.00|| ||<(>10.00||<)>20.00||<:#dddddd>30.00|| ||11.00||22.00||33.00|| ----- PROCEDURE: This processor needs the external sc (http://freshmeat.net/projects/sc/) routine. It is necessary to have a tmp directory in the wiki data dir. All formulars have to start by a "=" sign. Please remove the version number from the routine name! RESTRICTIONS: * german umlauts e.g. äöü are not implemented by now. they need to be masked because sc is not able to proceed calculation with such signs. MODIFICATION: @copyright: 2004-09-19 by Reimar Bauer (R.Bauer@fz-juelich.de) sctable-1.2.3-1 1.2.3-2 : (RB) bug fixed line #!sctable was not found by giving input parameters 1.2.3-3 : (RB) input parameter -show_formular added, column width is set to 200 chars : if this parameter is used : (RB) bug removed (already) #!sctable position could be different from 0 : but always greater -1 1.2.3-4 : 2004-10-05 RB format always extended for sc call to 200 signs. bug with blanks in names removed, more as one blank in a cell handled as one blank. 1.2.3-5 : 2004-10-16 RB format codes for colors and cells removed before calculations -format optional input var added 1.3 : 2004-11-13 RB changed to PARSER bug fixed: strings with blanks are formatted to the left feature added: column width of row numbers is set to 5% some examples fixed 1.3.3-2 patch and examples from towi AT geocities DOT SPAM com implemented : format and colors are used by now!! FUNCTIONAL ADDITIONS * a cell can now start with an wiki format string that will format the cell according to wiki table formatting, examples: <:>@sum(A3:a6) Title * you can define a name for a cell and use it later (no range defines yet): when combinign with the format must come first. examples: {income}20000 <:>{outcome}=income/2 DISCUSSION (need help): * we need a better routine to destinguish between strings and numbers, i have still some problems with e.g def is_number(txt): ItIs = True try: f =float(txt) except TypeError: ItIs = False return(ItIs) This doesn't work with formulars =@sum(a0:a2) what I do at the moment is to scan digits and signs from a string. This does not right identify a dot (.) in a string. This will be formatted as number. Thats the only bad thing I know on. ideas are welcome. 1.3.5-3: 2005-08-05 RB tmp path set relativ to installation and created if it isn't there space before and after a cell entry by now ignored """ Dependencies = [] import sys, os, re, sha from MoinMoin.parser import wiki from MoinMoin.action import AttachFile from MoinMoin.Page import Page import string,os config_external_sc="/usr/local/bin/sc" def table2sc(lines,format,show_formular,request): result=[] formats = {} # { (row,col): '', ... } r=0 name="=" # searchstring col_names=' ABCDEFGHIJKLMNOPQRSTUVWXYZ' first_line=lines[0] col_list=first_line.split('||') digit="2" c=0 form=[] for value in col_list: if (len(value.lstrip()) > 0): if (format=="") : digit="2" else: digit=format[c-1] f="%(command)s %(column)s %(arg)s" %{ "command":"format", "column":col_names[c], "arg":"200 " + digit + " 0" } form.append(f) c=c+1 result.append(form) for txt in lines: n_name=txt.count(name) txt=txt.lstrip() sargs=txt.split('||') n=len(sargs) sargs=sargs[0:n-1] c=0 # linecounter digits="=-.0123456789" for arg in sargs: arg=arg.strip() # check for wiki formatting string at beginning of sc data: "..." if arg.startswith('<'): p = arg.find('>')+1 if p>1: formats[(r,c)] , arg = arg[:p] , arg[p:] arg=arg.strip() # check for a sc column name define: "{defname}..." defname = None if (show_formular == 0): if arg.startswith('{'): p = arg.find('}') if p>0: defname , arg = arg[1:p] , arg[p+1:] arg=arg.strip() # sc data if (len(arg) > 0): if arg[0] in digits: if (arg.find(name) == 0): if (show_formular == 0): sargs[c]="%(command)s %(column)s %(arg)s" %{ "command":'let ', "column":col_names[c]+str(r), "arg":string.strip(arg) } else: arg=string.replace(arg,"="," =") sargs[c]="%(command)s %(column)s=%(arg)s" %{ "command":'leftstring', "column":col_names[c]+str(r), "arg":'"'+string.replace(arg,' ','')+'"' } else: if (show_formular == 0): sargs[c]="%(command)s %(column)s=%(arg)s" %{ "command":'let ', "column":col_names[c]+str(r), "arg":string.strip(arg) } else: sargs[c]="%(command)s %(column)s=%(arg)s" %{ "command":'leftstring', "column":col_names[c]+str(r), "arg":'"'+string.replace(arg,' ','')+'"' } else: if (c > 0): if (string.strip(arg) == ""): arg="Blank!@" else: arg=string.strip(arg) arg=string.replace(arg," ","Blank!@") sargs[c]="%(command)s %(column)s=%(arg)s" %{ "command":'leftstring ', "column":col_names[c]+str(r), "arg":'"'+arg+'"' } else: if (c > 0): sargs[c]="%(command)s %(column)s=%(arg)s" %{ "command":'leftstring ', "column":col_names[c]+str(r), "arg":'" Blank!@"' } if defname: sargs[c] = '%s\ndefine "%s" %s%s' % (sargs[c], defname, col_names[c], r) c=c+1 result.append(sargs) r=r+1 return(result,formats) class Parser: extensions = ['.sc'] def __init__(self, raw, request, **kw): self.raw = raw self.request = request self.form = request.form self._ = request.getText self.kw = [] for arg in kw.get('format_args','').split(): self.kw.append(arg) def format(self, formatter): config_sc_vartmp_dir = os.path.join(self.request.rootpage.getPagePath(),'tmp') if not os.path.exists(config_sc_vartmp_dir): os.mkdir(config_sc_vartmp_dir) lines = self.raw.split('\n') zt=0 for txt in lines: txt=txt.replace(u'\xc3\x84','AE') lines[zt]=txt zt=zt+1 kw=self.kw column_header=0 row_header=0 show_formular=0 format='' zt=0 for test in kw: if (test == '-column_header') : column_header=1 if (test == '-row_header') : row_header=1 if (test == '-show_formular') : show_formular=1 if (test == '-format') : format=string.split(kw[zt+1],",") zt=zt+1 matrix = [] textstr = '\n'.join(lines).strip() tmpname = re.sub('\s+', ' ', textstr) tmpname=sha.new(tmpname.encode('utf8','replace')).hexdigest().upper() #tmpname=sha.new(tmpname).hexdigest() tmpname = tmpname + "_sc" tmpfile = "%s/%s.sc" % (config_sc_vartmp_dir, tmpname) textstr, formats=table2sc(lines,format,show_formular,self.request) data = open(tmpfile, "w") i=0 for txt in textstr: if (len(textstr[i]) > 0): tmpstr=string.join(textstr[i],'\n') data.write('%s\n' % tmpstr.encode('latin-1')) i=i+1 data.close() cmd = "%(external_sc)s %(argument)s %(file)s " % { "external_sc": config_external_sc, "argument": " -W% ", "file":tmpfile } f=os.popen(cmd,'r')# popen to get the result of the calculation sys.stderr.write(cmd) result=f.readlines() #f.flush() os.unlink(tmpfile) # remove the tmpfile right_format='<)>' left_format='<(>' for txt in result: txt=string.join(txt,'') cells=string.split(txt,' ') zres=[] c=1 for value in cells: value=string.join(value,'') value=string.strip(value) strlen=len(value) if (strlen > 0) : v = ord(value[0]) if ord('a')<=v<=ord('z') or ord('A')<=v<=ord('Z') or value[0] in " ": format=left_format else: if (show_formular == 0) : format=right_format else: format=left_format if (value == "Blank!@"): value=" " if string.find(value,"Blank!@"): value=string.replace(value,"Blank!@", " ") zres.append( [format, value] ) c=c+1 matrix.append(zres) # post formatting of cells according to cut out wiki format strings # - matrix is here: [ [ [format,content], ... ], ... ] for cellname, cellformat in formats.items(): row,col = cellname m = matrix[row][col-1] m[0] = cellformat matrix = map(lambda row: "||" + "||".join([f+c for f,c in row]) + "||", matrix) # post processing of over all matrix # - matrix is here: [ "||cell||cell||...||", "||cell||...||", ... ] if (len(matrix[0]) == 2): # das ist noch nicht die beste Loesung (workaround) matrix=matrix[1:] if (row_header == 1): y=[] r=len(matrix) lines=range(r) i=0 for no in lines: matrix[i]="||<)5%#CCCCCC>'''"+str(no)+"'''"+matrix[i] i=i+1 if (column_header == 1): col_names=' ABCDEFGHIJKLMNOPQRSTUVWXYZ' x='||' if(row_header == 1): start=0 else: start=1 for name in col_names[start:c]: x = x + "%(format)s %(value)s %(tab)s" % { "format": '<:#CCCCCC>', "value": "'''"+name+"'''", "tab":'||'} matrix.insert(0,x) wikiizer = wiki.Parser(unicode(string.join(matrix,"\n"),'latin-1'),self.request) # parser for wiki tabular wikiizer.format(formatter)