MoinMoin: attachment:sstable-1.0.4.py of ParserMarket/Sstable

   1  
   2 """
   3     MoinMoin - sstable a Processor for spread sheet calculations using only Python.
   4 
   5     @license: GNU GPL, see COPYING for details.
   6 
   7     PURPOSE:
   8         This processor is used to do some spread sheet calculation in a regular wiki
   9     table using only Python. The first column/first line coordinate is A0.
  10 
  11     This code is based on the spreadsheet code posted by Raymond Hettinger and
  12     Richard Copeland at http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/355045
  13     It is also based on the sctable parser by Reimar Bauer (R.Bauer AT fz-juelich.de).
  14 
  15     CALLING SEQUENCE:
  16        {{{
  17        #!sstable [-column_header, -row_header, -show_formular, -format,
  18                   -input_separator, -output_separator ]
  19        }}}
  20 
  21     OPTIONAL INPUTS:
  22        -column_header: additional in the result the column header is shown
  23        -row_header: additional in the result the line number header is shown
  24        -show_formular: if set the formular instead of the result is shown,
  25                        data is arranged in textmode. Blanks in formulars are removed
  26        -format: is used to the set the number of digits for the column values
  27        -input_separator: used to read tables delimitted by something other than the 
  28                          default '||'
  29        -output_separator: used to write tables delimitted by something other than the 
  30                           default '||'
  31 
  32 
  33     EXAMPLE:
  34 {{{
  35 SUM over columns}}}
  36 {{{
  37 #!sstable
  38 ||1||2||=A0+B0||
  39 ||10||20||=@sum(A1:B1)||
  40 }}}
  41 
  42 RESULT:
  43 ||<)>1.00||<)>2.00||<)>3.00||
  44 ||<)>10.00||<)>20.00||<)>30.00||
  45 -----
  46 
  47 {{{
  48 cell B1 no data}}}
  49 
  50 {{{
  51 #!sstable
  52 ||A||B||C||D||
  53 ||1||||2||=A1+C1||
  54 }}}
  55 
  56 RESULT:
  57 ||<(>A||<(>B||<(>C||<(>D||
  58 ||<)>1.00||<)>||<)>2.00||<)>3.00||
  59 
  60 -----
  61 {{{
  62 SUM over rows}}}
  63 {{{
  64 #!sstable
  65 ||1||2||=A0+B0||
  66 ||10||20||30||
  67 ||=@sum(A0:A1)||=@sum(B0:B1)||=@sum(C0:C1)||
  68 }}}
  69 
  70 RESULT:
  71 ||<)>1.00||<)>2.00||<)>3.00||
  72 ||<)>10.00||<)>20.00||<)>30.00||
  73 ||<)>11.00||<)>22.00||<)>33.00||
  74 
  75 -----
  76 {{{
  77 SUM over rows and columns}}}
  78 {{{
  79 #!sstable
  80 ||A||B||C||
  81 ||1||2||=A1+B1||
  82 ||10||20||=@sum(A2:B2)||
  83 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
  84 }}}
  85 
  86 RESULT:
  87 ||<(>A||<(>B||<(>C||
  88 ||<)>1.00||<)>2.00||<)>3.00||
  89 ||<)>10.00||<)>20.00||<)>30.00||
  90 ||<)>11.00||<)>22.00||<)>33.00||
  91 
  92 -----
  93 {{{
  94 -column_header}}}
  95 {{{
  96 #!sstable  -column_header
  97 ||1||2||
  98 ||3||4||
  99 ||5||6||
 100 }}}
 101 
 102 RESULT:
 103 ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
 104 ||<)>1.00||<)>2.00||
 105 ||<)>3.00||<)>4.00||
 106 ||<)>5.00||<)>6.00||
 107 
 108 
 109 -----
 110 {{{
 111 -row_header}}}
 112 {{{
 113 #!sstable -row_header
 114 ||1||2||
 115 ||3||4||
 116 ||5||6||
 117 }}}
 118 
 119 RESULT:
 120 ||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00||
 121 ||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00||
 122 ||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00||
 123 
 124 -----
 125 {{{
 126 -column_header  -row_header}}}
 127 {{{
 128 #!sstable  -column_header  -row_header
 129 ||1||2||
 130 ||3||4||
 131 ||5||6||
 132 }}}
 133 
 134 RESULT:
 135 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
 136 ||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00||
 137 ||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00||
 138 ||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00||
 139 
 140 -----
 141 {{{
 142 -show_formular  -column_header  -row_header}}}
 143 {{{
 144 #!sstable -show_formular  -column_header  -row_header
 145 ||m||p||
 146 ||1||=A1 * 5||
 147 ||2||=A2-3||
 148 ||3||4||
 149 }}}
 150 
 151 RESULT:
 152 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
 153 ||<)5%#CCCCCC>'''0'''||<(>m||<(>p||
 154 ||<)5%#CCCCCC>'''1'''||<(>1||<(>=A1*5||
 155 ||<)5%#CCCCCC>'''2'''||<(>2||<(>=A2-3||
 156 ||<)5%#CCCCCC>'''3'''||<(>3||<(>4||
 157 
 158 -----
 159 {{{
 160 -column_header and blanks in cells}}}
 161 {{{
 162 #!sstable -column_header
 163 ||Name Vorname||  ||  || 3 || || 5||
 164 ||Name Vorname|| 1 || 2 ||  || 4 || 5||
 165 ||Name Vorname|| 1 || 2 || || || 5||
 166 }}}
 167 
 168 RESULT:
 169 ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||<:#CCCCCC>'''D'''||<:#CCCCCC>'''E'''||<:#CCCCCC>'''F'''||
 170 ||<(>Name Vorname||    ||    ||<)>3.00||    ||<)>5.00||
 171 ||<(>Name Vorname||<)>1.00||<)>2.00||    ||<)>4.00||<)>5.00||
 172 ||<(>Name Vorname||<)>1.00||<)>2.00||    ||    ||<)>5.00||
 173 
 174 -----
 175 {{{
 176 -format 1,1}}}
 177 {{{
 178 #!sstable -format 1,1
 179 ||1||2||
 180 ||3||4||
 181 ||=@sum(a0:a1)||=a2*4||
 182 }}}
 183 
 184 RESULT:
 185 ||<)>1.0||<)>2.0||
 186 ||<)>3.0||<)>4.0||
 187 ||<)>4.0||<)>16.0||
 188 
 189 -----
 190 {{{ useage of variable names -show_formular -column_header  -row_header
 191 }}}
 192 {{{#!sstable -show_formular -column_header  -row_header
 193 ||A||B||C||
 194 ||1||{two}2||=A1+two||
 195 ||10||20||=@sum(A2:B2)||
 196 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
 197 }}}
 198 
 199 
 200 RESULT:
 201 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||
 202 ||<)5%#CCCCCC>'''0'''||<(>A||<(>B||<(>C||
 203 ||<)5%#CCCCCC>'''1'''||<(>1||<(>{two}2||<(>=A1+two||
 204 ||<)5%#CCCCCC>'''2'''||<(>10||<(>20||<(>=@sum(A2:B2)||
 205 ||<)5%#CCCCCC>'''3'''||<(>=@sum(A1:A2)||<(>=@sum(B1:B2)||<(>=@sum(C1:C2)||
 206 
 207 and if we calculate [[BR]]
 208 RESULT:
 209 {{{#!sstable -column_header  -row_header
 210 ||A||B||C||
 211 ||1||{two}2||=A1+two||
 212 ||10||20||=@sum(A2:B2)||
 213 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
 214 }}}
 215 -----
 216 {{{color in cells
 217 }}}
 218 {{{#!sstable
 219 ||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
 220 ||<)#dddddd>1||<)#dddddd>{two}2||<)#cccccc>=A1+two||
 221 ||<(>10||<)>20||<:#dddddd>=@sum(A2:B2)||
 222 ||<rowbgcolor="#cc99ff">=@sum(A1:A2)||=@sum(B1:B2)||<bgcolor=magenta>=@sum(C1:C2)||
 223 }}}
 224 
 225 RESULT:
 226 ||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
 227 ||<)#dddddd>1.00||<)#dddddd>2.00||<)#cccccc>3.00||
 228 ||<(>10.00||<)>20.00||<:#dddddd>30.00||
 229 ||<rowbgcolor="#cc99ff">11.00||22.00||<bgcolor=magenta>33.00||
 230 -----
 231 {{{delimit input with ','
 232 }}}
 233 {{{
 234 #!sstable -column_header -input_separator ,
 235 ,1,2,
 236 ,3,4,
 237 ,5,6,
 238 }}}
 239 
 240 RESULT:
 241 ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
 242 ||<)>1.00||<)>2.00||
 243 ||<)>3.00||<)>4.00||
 244 ||<)>5.00||<)>6.00||
 245 -----
 246 {{{delimit input and output with ','
 247 }}}
 248 {{{
 249 #!sstable -column_header -row_header -input_separator , -output_separator ,
 250 1,2
 251 3,4
 252 5,6
 253 }}}
 254       
 255 RESULT:
 256 {{{
 257 ,''' ''','''A''','''B''',
 258 ,'''0''', 1.00, 2.00,
 259 ,'''1''', 3.00, 4.00,
 260 ,'''2''', 5.00, 6.00,
 261 }}}
 262 -----
 263 
 264 
 265 
 266     PROCEDURE:
 267       All formulars have to start by a "=" sign.
 268       All formulars do not need to start with a "@", but they can.
 269 
 270       Please remove the version number from the routine name!
 271 
 272 
 273     MODIFICATION:
 274        @copyright: 2006-03-25 by Andrew Shewmaker (agshew AT gmail) sstable-1.0
 275 
 276         1.0.4: improved regular expression that detects numbers - Andrew Shewmaker
 277                moved formula parsing into SpreadSheet class - Andrew Shewmaker
 278                return 0 when eval results in type error in SpreadSheet class - Andrew Shewmaker
 279         1.0.3: improved compatibility with other spreadsheets - Andrew Shewmaker
 280         1.0.2: improved cell range functions - Andrew Shewmaker
 281                correct special character handling - Reimar Bauer
 282         1.0.1: use unicode function - Andrew Shewmaker
 283         1.0: based on sctable-1.5.2-5 by Reimar Bauer (R.Bauer AT fz-juelich.de)
 284 """
 285 
 286 Dependencies = []
 287 import re
 288 from math import *
 289 from MoinMoin.parser import wiki
 290 from MoinMoin.action import AttachFile
 291 from MoinMoin.Page import Page
 292 from MoinMoin import wikiutil
 293 
 294 class SpreadSheet:
 295     _cells = {}
 296     tools = {}
 297     _cache = None
 298     def __init__(self):
 299       self.eval_re = re.compile('^=')
 300       self.num_re = re.compile('^-?(\d*)\.?[\d]+$')
 301       self.range_re = re.compile('(\w+).(\w+:\w+).')
 302       self.var_re = re.compile('\{(.+)\}(.+)')
 303       self.tic_re = re.compile("^'(.?)'?")
 304       self.tools.update({'__builtins__':None})  
 305       pmath = { 'ceil':ceil, 'floor':floor, \
 306                 'fabs':fabs, \
 307                 'fmod':fmod, 'modf':modf, \
 308                 'frexp':frexp, 'ldexp':ldexp, \
 309                 'exp':exp, \
 310                 'log':log, 'log10':log10, \
 311                 'pow':pow, \
 312                 'sqrt':sqrt, \
 313                 'acos':acos, 'asin':asin, 'atan':atan, 'atan2':atan2, \
 314                 'cos':cos, 'sin':sin, 'tan':tan, \
 315                 'cosh':cosh, 'sinh':sinh, 'tanh':tanh, \
 316                 'hypot':hypot, \
 317                 'degrees':degrees, 'radians':radians, \
 318                 'pi':pi, 'e':e, \
 319                 'cmp':cmp, \
 320                 'len':len, \
 321                 'round':round, \
 322                 'hex':hex, 'oct':oct, \
 323                 'max':self.max, 'min':self.min, \
 324                 'sum':self.sum, 'average':self.average, 'median':self.median, \
 325                }
 326       self.tools.update(pmath)
 327 
 328     def getformula(self, key):
 329         return self._cells[key]
 330 
 331     def max(self, keys):
 332         return max(map(self.__getitem__, self.cellrange(keys)))
 333 
 334     def min(self, keys):
 335         return min(map(self.__getitem__, self.cellrange(keys)))
 336 
 337     def sum(self, keys):
 338         return sum(map(self.__getitem__, self.cellrange(keys)))
 339 
 340     def average(self, keys):
 341         cells = map(self.__getitem__, self.cellrange(keys))
 342         return sum(cells)/float(len(cells))
 343 
 344     def median(self, keys):
 345         cells = map(self.__getitem__, self.cellrange(keys))
 346         n = len(cells)
 347         midpt = n/2
 348         if (n%2):
 349           return cells[midpt]
 350         else:
 351           return (cells[midpt-1] + cells[midpt])/2.0
 352 
 353     def __setitem__(self, key, formula):
 354         key = key.lower()
 355 
 356         # save variable name to cell name mapping
 357         m = self.var_re.search(formula)
 358         if m != None:
 359             formula = m.group(2)
 360             self._cells[m.group(1).lower()] = key
 361 
 362         if self.eval_re.match(formula):
 363             # case insensitivity for everything after an '='
 364             formula = formula.lower()
 365 
 366             # cell ranges are passed as strings
 367             m = self.range_re.search(formula)
 368             if m != None:
 369                 formula = m.group(1) + "('" + m.group(2) + "')"
 370 
 371             # '=' and '@' are unwanted for eval
 372             formula = re.sub('^=@?', '', formula)
 373         elif not self.num_re.match(formula):
 374             m = self.tic_re.match(formula)
 375             if m != None:
 376                 formula = m.group(1)
 377             formula = "'" + formula + "'"
 378 
 379         self._cells[key] = formula
 380 
 381     def __getitem__(self, key):
 382         bCache = self._cache is None
 383         if bCache: self._cache = {}
 384         while True:
 385             try:
 386                 if ( self._cells[key] == None or self._cells[key] == ''):
 387                     rv = ''
 388                 else:
 389                     rv = eval(self._cells[key], self.tools, self._cache)
 390                 break
 391             except NameError, ne:
 392                 name = ne.args[0][6:-16] # Extract name from NameError
 393                 if name in self._cells:
 394                     self._cache[name] = self[name]
 395                 else:
 396                     self._cache[key] = None
 397                     self._cells[key] = None
 398             except SyntaxError, se:
 399                 rv = self._cells[key]
 400                 break
 401             except TypeError, te:
 402                 #rv = 'type error: ' + self._cells[key] + ', ' + str(te)
 403                 rv = 0
 404                 break
 405                 
 406         if bCache: self._cache = None
 407         return rv
 408 
 409     def cellrange(self, keys):
 410         m = re.search('([a-z]+)(\d+):([a-z])+(\d+)?', keys)
 411         
 412         result = []
 413         if m == None:
 414             result = re.split(',', keys)
 415             return result
 416         
 417         (c1, r1, c2, r2) = m.groups()
 418         
 419         mincol = min(c1, c2)
 420         maxcol = max(c1, c2)
 421         minrow = min(int(r1), int(r2))
 422         maxrow = max(int(r1), int(r2))
 423         
 424         col_names = 'abcdefghijklmnopqrstuvwxyz'
 425         for i in range(col_names.index(mincol), col_names.index(maxcol)+1):
 426             for j in range(minrow, maxrow+1):
 427                 result.append(col_names[i] + str(j))
 428         
 429         return result
 430 
 431 
 432 class Parser:
 433 
 434     def __init__(self, raw, request, **kw):
 435         self.ss = SpreadSheet()
 436         self.raw = raw
 437         self.request = request
 438         self.form = request.form
 439         self._ = request.getText
 440         self.kw = []
 441         for arg in kw.get('format_args','').split():
 442             self.kw.append(arg)
 443 
 444             
 445     def format(self, formatter):
 446         lines = self.raw.split('\n')
 447         
 448         kw = self.kw
 449         column_header = 0
 450         row_header = 0
 451         show_formular = 0
 452         format = ''
 453         insep = '||'
 454         outsep = '||'
 455         right_format = '<)>'
 456         left_format = '<(>'
 457         colheader_format = '<:#CCCCCC>'
 458         rowheader_format = '<)5%#CCCCCC>'
 459         zt = 0
 460         for test in kw:
 461              if test == '-column_header': column_header = 1
 462              if test == '-row_header': row_header = 1
 463              if test == '-show_formular': show_formular = 1
 464              if test == '-format': format = re.split(',', kw[zt+1])
 465              if test == '-input_separator': insep = kw[zt+1]
 466              if test == '-output_separator':
 467                  outsep = kw[zt+1]
 468                  right_format = ''
 469                  left_format = ''
 470                  colheader_format = ''
 471                  rowheader_format = ''
 472              zt += 1
 473 
 474         formats = {} # { (row,col): '<wikiformat>', ... }
 475         r = 0 # row counter
 476         c = 0 # column counter
 477         col_names = 'abcdefghijklmnopqrstuvwxyz'
 478         maxcolumns = 0
 479     
 480         for txt in lines:
 481             txt = txt.lstrip()
 482             if ( txt == '' ):
 483                 continue
 484             columns = txt.split(insep)
 485             if re.search('^' + insep, txt):
 486                 columns = columns[1:]
 487             if re.search(insep + '$', txt):
 488                 columns = columns[:-1]
 489                     
 490             if ( len(columns) > maxcolumns):
 491                 maxcolumns = len(columns)
 492             
 493             c = 0
 494             for cell in columns:
 495                 # check for wiki formatting string at beginning of ss data: "<format>..."
 496                 if cell.startswith('<'):
 497                     p = cell.find('>') + 1
 498                     if p > 1:
 499                         formats[(r,c)] , cell = cell[:p] , cell[p:]
 500                 else:
 501                     formats[(r,c)] = ""
 502     
 503                 self.ss[ col_names[c]+str(r) ] = cell.strip()
 504 
 505                 c += 1
 506     
 507             r += 1
 508         
 509         maxrows = r
 510         result = ""
 511 
 512         if outsep != '||':
 513             self.request.write('<pre>')
 514 
 515         if column_header == 1:
 516             header_names = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 517             result += '\n' + outsep
 518             if row_header == 1:
 519                 start = 0
 520             else:
 521                 start = 1
 522         
 523             for name in header_names[start:maxcolumns+1]:
 524                 result += colheader_format + "'''" + name + "'''" + outsep
 525 
 526         for r in range(maxrows):
 527             result += '\n' + outsep
 528 
 529             if row_header == 1:
 530                 result += rowheader_format + "'''" + str(r) + "'''" + outsep
 531 
 532             for c in range(maxcolumns):
 533                 cellname = col_names[c]+str(r)
 534 
 535                 cell = str(self.ss[cellname])
 536                 if cell == '':
 537                     cell = ' '
 538 
 539                 if show_formular == 0:
 540                     num_match = self.ss.num_re.match(cell)
 541                     if num_match != None:
 542                         fmt = right_format
 543                         if formats[(r,c)] != '':
 544                             fmt = formats[(r,c)]
 545 
 546                         if format == '':
 547                             cell = '%s %.*f' % (fmt, 2, float(self.ss[cellname]))
 548                         else:
 549                             cell = '%s %.*f' % (fmt, int(format[c-1]), float(self.ss[cellname]))
 550 
 551                 elif show_formular == 1:
 552                     cell = self.ss.getformula(cellname)
 553                 else:
 554                     if formats[(r,c)] == '':
 555                         cell = left_format + cell
 556                     else:
 557                         cell = formats[(r,c)] + cell
 558 
 559                 result += cell + outsep
 560 
 561         if outsep != '||':
 562             self.request.write(result + '</pre>')
 563         else:
 564             result = result[1:]
 565             result = wikiutil.unquoteWikiname(result)
 566             wikiizer = wiki.Parser(result,self.request) # parser for wiki tabular
 567             wikiizer.format(formatter)