""" MoinMoin - sstable a Processor for spread sheet calculations using only Python. @license: GNU GPL, see COPYING for details. PURPOSE: This processor is used to do some spread sheet calculation in a regular wiki table using only Python. The first column/first line coordinate is A0. This code is based on the spreadsheet code posted by Raymond Hettinger and Richard Copeland at http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/355045 It is also based on the sctable parser by Reimar Bauer (R.Bauer AT fz-juelich.de). CALLING SEQUENCE: {{{ #!sstable [-column_header, -row_header, -show_formular, -format, -input_separator, -output_separator ] }}} 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 -input_separator: used to read tables delimitted by something other than the default '||' -output_separator: used to write tables delimitted by something other than the default '||' EXAMPLE: {{{ SUM over columns}}} {{{ #!sstable ||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}}} {{{ #!sstable ||A||B||C||D|| ||1||||2||=A1+C1|| }}} RESULT: ||<(>A||<(>B||<(>C||<(>D|| ||<)>1.00||<)>||<)>2.00||<)>3.00|| ----- {{{ SUM over rows}}} {{{ #!sstable ||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}}} {{{ #!sstable ||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}}} {{{ #!sstable -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}}} {{{ #!sstable -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}}} {{{ #!sstable -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}}} {{{ #!sstable -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}}} {{{ #!sstable -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}}} {{{ #!sstable -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 }}} {{{#!sstable -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: {{{#!sstable -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 }}} {{{#!sstable ||<: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|| ----- {{{delimit input with ',' }}} {{{ #!sstable -column_header -input_separator , ,1,2, ,3,4, ,5,6, }}} RESULT: ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''|| ||<)>1.00||<)>2.00|| ||<)>3.00||<)>4.00|| ||<)>5.00||<)>6.00|| ----- {{{delimit input and output with ',' }}} {{{ #!sstable -column_header -row_header -input_separator , -output_separator , 1,2 3,4 5,6 }}} RESULT: {{{ ,''' ''','''A''','''B''', ,'''0''', 1.00, 2.00, ,'''1''', 3.00, 4.00, ,'''2''', 5.00, 6.00, }}} ----- PROCEDURE: All formulars have to start by a "=" sign. All formulars do not need to start with a "@", but they can. Please remove the version number from the routine name! MODIFICATION: @copyright: 2006-03-25 by Andrew Shewmaker (agshew AT gmail) sstable-1.0 2008 MoinMoin:ReimarBauer (slightly changed for 1.7.1) 1.0.4: improved regular expression that detects numbers - Andrew Shewmaker moved formula parsing into SpreadSheet class - Andrew Shewmaker return 0 when eval results in type error in SpreadSheet class - Andrew Shewmaker 1.0.3: improved compatibility with other spreadsheets - Andrew Shewmaker 1.0.2: improved cell range functions - Andrew Shewmaker correct special character handling - Reimar Bauer 1.0.1: use unicode function - Andrew Shewmaker 1.0: based on sctable-1.5.2-5 by Reimar Bauer (R.Bauer AT fz-juelich.de) """ Dependencies = [] import re from math import * from MoinMoin.parser import text_moin_wiki as wiki from MoinMoin.action import AttachFile from MoinMoin.Page import Page from MoinMoin import wikiutil class SpreadSheet: _cells = {} tools = {} _cache = None def __init__(self): self.eval_re = re.compile('^=') self.num_re = re.compile('^-?(\d*)\.?[\d]+$') self.range_re = re.compile('(\w+).(\w+:\w+).') self.var_re = re.compile('\{(.+)\}(.+)') self.tic_re = re.compile("^'(.?)'?") self.tools.update({'__builtins__':None}) pmath = {'ceil': ceil, 'floor': floor, 'fabs': fabs, 'fmod': fmod, 'modf': modf, 'frexp': frexp, 'ldexp': ldexp, 'exp': exp, 'log': log, 'log10': log10, 'pow': pow, 'sqrt': sqrt, 'acos': acos, 'asin': asin, 'atan': atan, 'atan2': atan2, 'cos': cos, 'sin': sin, 'tan': tan, 'cosh': cosh, 'sinh': sinh, 'tanh': tanh, 'hypot': hypot, 'degrees': degrees, 'radians': radians, 'pi': pi, 'e': e, 'cmp': cmp, 'len': len, 'round': round, 'hex': hex, 'oct': oct, 'max': self.max, 'min': self.min, 'sum': self.sum, 'average': self.average, 'median': self.median, } self.tools.update(pmath) def getformula(self, key): return self._cells[key] def max(self, keys): return max(map(self.__getitem__, self.cellrange(keys))) def min(self, keys): return min(map(self.__getitem__, self.cellrange(keys))) def sum(self, keys): return sum(map(self.__getitem__, self.cellrange(keys))) def average(self, keys): cells = map(self.__getitem__, self.cellrange(keys)) return sum(cells)/float(len(cells)) def median(self, keys): cells = map(self.__getitem__, self.cellrange(keys)) n = len(cells) midpt = n / 2 if (n % 2): return cells[midpt] else: return (cells[midpt-1] + cells[midpt]) / 2.0 def __setitem__(self, key, formula): key = key.lower() # save variable name to cell name mapping m = self.var_re.search(formula) if m: formula = m.group(2) self._cells[m.group(1).lower()] = key if self.eval_re.match(formula): # case insensitivity for everything after an '=' formula = formula.lower() # cell ranges are passed as strings m = self.range_re.search(formula) if m: formula = m.group(1) + "('" + m.group(2) + "')" # '=' and '@' are unwanted for eval formula = re.sub('^=@?', '', formula) elif not self.num_re.match(formula): m = self.tic_re.match(formula) if m: formula = m.group(1) formula = "'" + formula + "'" self._cells[key] = formula def __getitem__(self, key): bCache = self._cache is None if bCache: self._cache = {} while True: try: if (self._cells[key] == None or self._cells[key] == ''): rv = '' else: rv = eval(self._cells[key], self.tools, self._cache) break except NameError, ne: name = ne.args[0][6:-16] # Extract name from NameError if name in self._cells: self._cache[name] = self[name] else: self._cache[key] = None self._cells[key] = None except SyntaxError, se: rv = self._cells[key] break except TypeError, te: #rv = 'type error: ' + self._cells[key] + ', ' + str(te) rv = 0 break if bCache: self._cache = None return rv def cellrange(self, keys): m = re.search('([a-z]+)(\d+):([a-z])+(\d+)?', keys) result = [] if not m: result = re.split(',', keys) return result (c1, r1, c2, r2) = m.groups() mincol = min(c1, c2) maxcol = max(c1, c2) minrow = min(int(r1), int(r2)) maxrow = max(int(r1), int(r2)) col_names = 'abcdefghijklmnopqrstuvwxyz' for i in range(col_names.index(mincol), col_names.index(maxcol)+1): for j in range(minrow, maxrow+1): result.append(col_names[i] + str(j)) return result class Parser: def __init__(self, raw, request, **kw): self.ss = SpreadSheet() 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): lines = self.raw.split('\n') kw = self.kw column_header = 0 row_header = 0 show_formular = 0 format = '' insep = '||' outsep = '||' right_format = '<)>' left_format = '<(>' colheader_format = '<:#CCCCCC>' rowheader_format = '<)5%#CCCCCC>' 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 = re.split(',', kw[zt + 1]) if test == '-input_separator': insep = kw[zt + 1] if test == '-output_separator': outsep = kw[zt + 1] right_format = '' left_format = '' colheader_format = '' rowheader_format = '' zt += 1 formats = {} # { (row,col): '', ... } r = 0 # row counter c = 0 # column counter col_names = 'abcdefghijklmnopqrstuvwxyz' maxcolumns = 0 for txt in lines: txt = txt.lstrip() if txt == '': continue columns = txt.split(insep) if re.search('^' + insep, txt): columns = columns[1:] if re.search(insep + '$', txt): columns = columns[:-1] if len(columns) > maxcolumns: maxcolumns = len(columns) c = 0 for cell in columns: # check for wiki formatting string at beginning of ss data: "..." if cell.startswith('<'): p = cell.find('>') + 1 if p > 1: formats[(r, c)], cell = cell[:p], cell[p:] else: formats[(r, c)] = "" self.ss[col_names[c]+str(r)] = cell.strip() c += 1 r += 1 maxrows = r result = "" if outsep != '||': self.request.write('
')

        if column_header == 1:
            header_names = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
            result += '\n' + outsep
            if row_header == 1:
                start = 0
            else:
                start = 1

            for name in header_names[start:maxcolumns+1]:
                result += colheader_format + "'''" + name + "'''" + outsep

        for r in range(maxrows):
            result += '\n' + outsep

            if row_header == 1:
                result += rowheader_format + "'''" + str(r) + "'''" + outsep

            for c in range(maxcolumns):
                cellname = col_names[c]+str(r)

                cell = str(self.ss[cellname])
                if cell == '':
                    cell = ' '

                if show_formular == 0:
                    num_match = self.ss.num_re.match(cell)
                    if num_match:
                        fmt = right_format
                        if formats[(r,c)] != '':
                            fmt = formats[(r,c)]

                        if format == '':
                            cell = '%s %.*f' % (fmt, 2, float(self.ss[cellname]))
                        else:
                            cell = '%s %.*f' % (fmt, int(format[c-1]), float(self.ss[cellname]))

                elif show_formular == 1:
                    cell = self.ss.getformula(cellname)
                else:
                    if formats[(r, c)] == '':
                        cell = left_format + cell
                    else:
                        cell = formats[(r, c)] + cell

                result += cell + outsep

        if outsep != '||':
            self.request.write(result + '
') else: result = result[1:] result = wikiutil.unquoteWikiname(result) wikiizer = wiki.Parser(result, self.request) # parser for wiki tabular wikiizer.format(formatter)