""" 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! 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 1.3.5-4: I had problems running this on FreeBSD. I had to install manually the source because the ports in FreeBSD seem to be out of date (using version 6.x of sc). Furthermore, i had to comment out the f.flush() call that was causing an I/O error for some reason. -- TheAnarcat 2005-12-18 23:34:15 1.5.2-5 : R.Bauer code revised (tabs removed!) """ Dependencies = [] import sys, os, re, sha, string from MoinMoin.parser import wiki from MoinMoin.action import AttachFile from MoinMoin.Page import Page config_external_sc = "/usr/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 = [] col_list = col_list[0:len(col_list)-1] for value in col_list: if len(value.lstrip()) > 0: if (format == "") : digit = "2" else: digit = format[c-1] f = "%(command)s %(column)s 200 %(digit)s 0" % { "command": "format", "column": col_names[c], "digit": digit } form.append(f) 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%(r)s %(arg)s' % { 'command': 'let', 'column': col_names[c], 'r': str(r), 'arg': string.strip(arg) } else: arg = string.replace(arg,"="," =") sargs[c] = '%(command)s %(column)s%(r)s ="%(arg)s"' % { 'command': 'leftstring', 'column': col_names[c], 'r': str(r), 'arg': string.replace(arg,' ','') } else: if (show_formular == 0): sargs[c] = '%(command)s %(column)s%(r)s =%(arg)s' % { 'command': 'let', 'column': col_names[c], 'r': str(r), 'arg': string.strip(arg) } else: sargs[c] = '%(command)s %(column)s%(r)s ="%(arg)s"' % { 'command': 'leftstring', 'column': col_names[c], 'r': 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%(r)s ="%(arg)s"' % { 'command': 'leftstring', 'column': col_names[c], 'r': str(r), 'arg': arg } else: if c > 0: sargs[c] = '%(command)s %(column)s%(r)s ="%(arg)s"' % { 'command': 'leftstring', 'column': col_names[c], 'r': str(r), 'arg': ' Blank!@' } if defname: sargs[c] = '%s\ndefine "%s" %s%s' % (sargs[c], defname, col_names[c], r) c += 1 result.append(sargs) 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') 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 += 1 matrix = [] textstr = '\n'.join(lines).strip() tmpname = re.sub('\s+', ' ', textstr) tmpname = sha.new(tmpname.encode('utf8','replace')).hexdigest().upper() 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 += 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 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 += 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] = str(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 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 += 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)