MoinMoin: attachment:sctable-1.5.2-5.py of ParserMarket/Sctable

   1  
   2 """
   3     MoinMoin - sctable a Processor for spread sheet calculations by sc
   4     @license: GNU GPL, see COPYING for details.
   5 
   6     PURPOSE:
   7         This processor is used to do some spread sheet calculation based on sc in a
   8     regular wiki table. The first column/first line coordinate is A0.
   9 
  10     CALLING SEQUENCE:
  11        {{{
  12        #!sctable [-column_header, -row_header, -show_formular, -format  ]
  13        }}}
  14 
  15     OPTIONAL INPUTS:
  16        -column_header: additional in the result the column header is shown
  17        -row_header: additional in the result the line number header is shown
  18        -show_formular: if set the formular instead of the result is shown,
  19                        data is arranged in textmode. Blanks in formulars are removed
  20        -format: is used to the set the number of digits for the column values
  21 
  22 
  23     EXAMPLE:
  24 {{{
  25 SUM over columns}}}
  26 {{{
  27 #!sctable
  28 ||1||2||=A0+B0||
  29 ||10||20||=@sum(A1:B1)||
  30 }}}
  31 
  32 RESULT:
  33 ||<)>1.00||<)>2.00||<)>3.00||
  34 ||<)>10.00||<)>20.00||<)>30.00||
  35 -----
  36 
  37 {{{
  38 cell B1 no data}}}
  39 
  40 {{{
  41 #!sctable
  42 ||A||B||C||D||
  43 ||1||||2||=A1+C1||
  44 }}}
  45 
  46 RESULT:
  47 ||<(>A||<(>B||<(>C||<(>D||
  48 ||<)>1.00||<)>||<)>2.00||<)>3.00||
  49 
  50 -----
  51 {{{
  52 SUM over rows}}}
  53 {{{
  54 #!sctable
  55 ||1||2||=A0+B0||
  56 ||10||20||30||
  57 ||=@sum(A0:A1)||=@sum(B0:B1)||=@sum(C0:C1)||
  58 }}}
  59 
  60 RESULT:
  61 ||<)>1.00||<)>2.00||<)>3.00||
  62 ||<)>10.00||<)>20.00||<)>30.00||
  63 ||<)>11.00||<)>22.00||<)>33.00||
  64 
  65 -----
  66 {{{
  67 SUM over rows and columns}}}
  68 {{{
  69 #!sctable
  70 ||A||B||C||
  71 ||1||2||=A1+B1||
  72 ||10||20||=@sum(A2:B2)||
  73 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
  74 }}}
  75 
  76 RESULT:
  77 ||<(>A||<(>B||<(>C||
  78 ||<)>1.00||<)>2.00||<)>3.00||
  79 ||<)>10.00||<)>20.00||<)>30.00||
  80 ||<)>11.00||<)>22.00||<)>33.00||
  81 
  82 -----
  83 {{{
  84 -column_header}}}
  85 {{{
  86 #!sctable  -column_header
  87 ||1||2||
  88 ||3||4||
  89 ||5||6||
  90 }}}
  91 
  92 RESULT:
  93 ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
  94 ||<)>1.00||<)>2.00||
  95 ||<)>3.00||<)>4.00||
  96 ||<)>5.00||<)>6.00||
  97 
  98 
  99 -----
 100 {{{
 101 -row_header}}}
 102 {{{
 103 #!sctable -row_header
 104 ||1||2||
 105 ||3||4||
 106 ||5||6||
 107 }}}
 108 
 109 RESULT:
 110 ||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00||
 111 ||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00||
 112 ||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00||
 113 
 114 -----
 115 {{{
 116 -column_header  -row_header}}}
 117 {{{
 118 #!sctable  -column_header  -row_header
 119 ||1||2||
 120 ||3||4||
 121 ||5||6||
 122 }}}
 123 
 124 RESULT:
 125 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
 126 ||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00||
 127 ||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00||
 128 ||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00||
 129 
 130 -----
 131 {{{
 132 -show_formular  -column_header  -row_header}}}
 133 {{{
 134 #!sctable -show_formular  -column_header  -row_header
 135 ||m||p||
 136 ||1||=A1 * 5||
 137 ||2||=A2-3||
 138 ||3||4||
 139 }}}
 140 
 141 RESULT:
 142 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
 143 ||<)5%#CCCCCC>'''0'''||<(>m||<(>p||
 144 ||<)5%#CCCCCC>'''1'''||<(>1||<(>=A1*5||
 145 ||<)5%#CCCCCC>'''2'''||<(>2||<(>=A2-3||
 146 ||<)5%#CCCCCC>'''3'''||<(>3||<(>4||
 147 
 148 -----
 149 {{{
 150 -column_header and blanks in cells}}}
 151 {{{
 152 #!sctable -column_header
 153 ||Name Vorname||  ||  || 3 || || 5||
 154 ||Name Vorname|| 1 || 2 ||  || 4 || 5||
 155 ||Name Vorname|| 1 || 2 || || || 5||
 156 }}}
 157 
 158 RESULT:
 159 ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||<:#CCCCCC>'''D'''||<:#CCCCCC>'''E'''||<:#CCCCCC>'''F'''||
 160 ||<(>Name Vorname||    ||    ||<)>3.00||    ||<)>5.00||
 161 ||<(>Name Vorname||<)>1.00||<)>2.00||    ||<)>4.00||<)>5.00||
 162 ||<(>Name Vorname||<)>1.00||<)>2.00||    ||    ||<)>5.00||
 163 
 164 -----
 165 {{{
 166 -format 1,1}}}
 167 {{{
 168 #!sctable -format 1,1
 169 ||1||2||
 170 ||3||4||
 171 ||=@sum(a0:a1)||=a2*4||
 172 }}}
 173 
 174 RESULT:
 175 ||<)>1.0||<)>2.0||
 176 ||<)>3.0||<)>4.0||
 177 ||<)>4.0||<)>16.0||
 178 
 179 -----
 180 {{{ useage of variable names -show_formular -column_header  -row_header
 181 }}}
 182 {{{#!sctable -show_formular -column_header  -row_header
 183 ||A||B||C||
 184 ||1||{two}2||=A1+two||
 185 ||10||20||=@sum(A2:B2)||
 186 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
 187 }}}
 188 
 189 
 190 RESULT:
 191 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||
 192 ||<)5%#CCCCCC>'''0'''||<(>A||<(>B||<(>C||
 193 ||<)5%#CCCCCC>'''1'''||<(>1||<(>{two}2||<(>=A1+two||
 194 ||<)5%#CCCCCC>'''2'''||<(>10||<(>20||<(>=@sum(A2:B2)||
 195 ||<)5%#CCCCCC>'''3'''||<(>=@sum(A1:A2)||<(>=@sum(B1:B2)||<(>=@sum(C1:C2)||
 196 
 197 and if we calculate [[BR]]
 198 RESULT:
 199 {{{#!sctable -column_header  -row_header
 200 ||A||B||C||
 201 ||1||{two}2||=A1+two||
 202 ||10||20||=@sum(A2:B2)||
 203 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
 204 }}}
 205 -----
 206 {{{color in cells
 207 }}}
 208 {{{#!sctable
 209 ||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
 210 ||<)#dddddd>1||<)#dddddd>{two}2||<)#cccccc>=A1+two||
 211 ||<(>10||<)>20||<:#dddddd>=@sum(A2:B2)||
 212 ||<rowbgcolor="#cc99ff">=@sum(A1:A2)||=@sum(B1:B2)||<bgcolor=magenta>=@sum(C1:C2)||
 213 }}}
 214 
 215 RESULT:
 216 ||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
 217 ||<)#dddddd>1.00||<)#dddddd>2.00||<)#cccccc>3.00||
 218 ||<(>10.00||<)>20.00||<:#dddddd>30.00||
 219 ||<rowbgcolor="#cc99ff">11.00||22.00||<bgcolor=magenta>33.00||
 220 -----
 221 
 222 
 223     PROCEDURE:
 224       This processor needs the external sc (http://freshmeat.net/projects/sc/) routine.
 225       It is necessary to have a tmp directory in the wiki data dir.
 226       All formulars have to start by a "=" sign.
 227 
 228       Please remove the version number from the routine name!
 229 
 230 
 231     MODIFICATION:
 232        @copyright: 2004-09-19 by Reimar Bauer (R.Bauer@fz-juelich.de) sctable-1.2.3-1
 233        1.2.3-2 : (RB) bug fixed line #!sctable was not found by giving input parameters
 234        1.2.3-3 : (RB) input parameter -show_formular added, column width is set to 200 chars
 235                :      if this parameter is used
 236                : (RB) bug removed (already) #!sctable position could be different from 0
 237            :      but always greater -1
 238       1.2.3-4  : 2004-10-05 RB format always extended for sc call to 200 signs.
 239                  bug with blanks in names removed, more as one blank in a cell handled as one blank.
 240       1.2.3-5  : 2004-10-16 RB format codes for colors and cells removed before calculations
 241                  -format optional input var added
 242 
 243       1.3      : 2004-11-13 RB changed to PARSER
 244                  bug fixed: strings with blanks are formatted to the left
 245          feature added: column width of row numbers is set to 5%
 246          some examples fixed
 247 
 248       1.3.3-2 patch and examples from towi AT geocities DOT SPAM com implemented
 249               : format and colors are used by now!!
 250 
 251           FUNCTIONAL ADDITIONS
 252 
 253       * a cell can now start with an wiki format string that will format
 254           the cell according to wiki table formatting,
 255           examples:
 256           <:>@sum(A3:a6)
 257           <rowbgcolor=cyan>Title
 258 
 259        * you can define a name for a cell and use it later (no range defines yet):
 260          when combinign with <format> the format must come first.
 261          examples:
 262          {income}20000
 263          <:>{outcome}=income/2
 264 
 265     DISCUSSION (need help):
 266       * we need a better routine to destinguish between strings and numbers, i have still some problems with e.g
 267         def is_number(txt):
 268             ItIs = True
 269         try:
 270                 f =float(txt)
 271         except TypeError:
 272                 ItIs = False
 273 
 274         return(ItIs)
 275 
 276     This doesn't work with formulars =@sum(a0:a2)
 277 
 278         what I do at the moment is to scan digits and signs from a string. This does not right identify a dot (.)
 279     in a string. This will be formatted as number. Thats the only bad thing I know on.
 280 
 281     ideas are welcome.
 282 
 283         1.3.5-3: 2005-08-05 RB tmp path set relativ to installation and created if it isn't there
 284                            space before and after a cell entry by now ignored
 285                 
 286 
 287         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      
 288         
 289                
 290         1.5.2-5 : R.Bauer code revised (tabs removed!) 
 291 
 292 """
 293 
 294 Dependencies = []
 295 import sys, os, re, sha, string
 296 from MoinMoin.parser import wiki
 297 from MoinMoin.action import AttachFile
 298 from MoinMoin.Page import Page
 299 
 300 
 301 config_external_sc = "/usr/bin/sc"
 302 
 303 def table2sc(lines,format,show_formular,request):
 304     result = []
 305     formats = {} # { (row,col): '<wikiformat>', ... }
 306     r = 0
 307     name = "=" # searchstring
 308     col_names = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 309 
 310     first_line = lines[0]
 311     col_list = first_line.split('||')
 312     digit = "2"
 313     c = 0
 314     form = []
 315 
 316     col_list = col_list[0:len(col_list)-1]
 317     
 318     for value in col_list:
 319         if len(value.lstrip()) > 0:
 320               if (format == "") :
 321                   digit = "2"
 322               else:
 323                   digit = format[c-1]
 324 
 325         f = "%(command)s %(column)s 200 %(digit)s 0" % {
 326             "command": "format",
 327             "column": col_names[c],
 328             "digit": digit
 329             }
 330         form.append(f)
 331         c += 1
 332     result.append(form)
 333 
 334     for txt in lines:
 335         n_name = txt.count(name)
 336         txt = txt.lstrip()
 337         sargs = txt.split('||')
 338         n = len(sargs)
 339         sargs = sargs[0:n-1]
 340                 
 341         c = 0      # linecounter
 342         digits = "=-.0123456789"
 343         for arg in sargs:
 344             arg = arg.strip()
 345            
 346            # check for wiki formatting string at beginning of sc data: "<format>..."
 347             if arg.startswith('<'):
 348                 p = arg.find('>') + 1
 349                 if p > 1:
 350                     formats[(r,c)] , arg = arg[:p] , arg[p:]
 351                     arg = arg.strip()
 352                  
 353             # check for a sc column name define: "{defname}..."
 354             defname = None
 355             if show_formular == 0:
 356                 if arg.startswith('{'):
 357                     p = arg.find('}')
 358                     if p > 0:
 359                         defname, arg = arg[1:p] , arg[p+1:]
 360                     arg = arg.strip()
 361                     
 362             # sc data
 363             if len(arg) > 0:
 364                 if arg[0] in digits:
 365                     if arg.find(name) == 0:
 366                         if show_formular == 0:
 367                             sargs[c] = '%(command)s %(column)s%(r)s %(arg)s' % {
 368                                        'command': 'let',
 369                                        'column': col_names[c],
 370                                        'r': str(r),
 371                                        'arg': string.strip(arg)
 372                             }
 373                         else:
 374                             arg = string.replace(arg,"="," =")
 375                             sargs[c] = '%(command)s %(column)s%(r)s ="%(arg)s"' % {
 376                                        'command': 'leftstring',
 377                                        'column': col_names[c],
 378                                        'r': str(r),
 379                                        'arg': string.replace(arg,' ','')
 380                             }
 381 
 382                     else:
 383                          if (show_formular == 0):
 384                              sargs[c] = '%(command)s %(column)s%(r)s =%(arg)s' % {
 385                                         'command': 'let',
 386                                         'column': col_names[c],
 387                                         'r': str(r),
 388                                         'arg': string.strip(arg)
 389                              }
 390                          else:
 391                              sargs[c] = '%(command)s %(column)s%(r)s ="%(arg)s"' % {
 392                                         'command': 'leftstring',
 393                                         'column': col_names[c],
 394                                         'r': str(r),
 395                                         'arg': string.replace(arg,' ','')
 396                              }
 397 
 398                 else:
 399                     if c > 0:
 400                         if string.strip(arg) == "":
 401                             arg = "Blank!@"
 402                         else:
 403                             arg = string.strip(arg)
 404                             arg = string.replace(arg," ","Blank!@")
 405                         
 406                         sargs[c] = '%(command)s %(column)s%(r)s ="%(arg)s"' % {
 407                                    'command': 'leftstring',
 408                                    'column': col_names[c],
 409                                    'r': str(r),
 410                                    'arg': arg
 411                         }
 412             else:
 413                 if c > 0:
 414                     sargs[c] = '%(command)s %(column)s%(r)s ="%(arg)s"' % {
 415                                'command': 'leftstring',
 416                                'column': col_names[c],
 417                                'r': str(r),
 418                                'arg': ' Blank!@'
 419                     }
 420             
 421                             
 422             if defname:
 423                 sargs[c] = '%s\ndefine "%s" %s%s' % (sargs[c], defname, col_names[c], r)
 424             c += 1
 425         result.append(sargs)
 426         r += 1
 427     
 428     return(result,formats)
 429 
 430 class Parser:
 431 
 432     extensions = ['.sc']
 433     
 434     def __init__(self, raw, request, **kw):
 435 
 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         config_sc_vartmp_dir = os.path.join(self.request.rootpage.getPagePath(),'tmp')
 447         
 448         if not os.path.exists(config_sc_vartmp_dir):
 449             os.mkdir(config_sc_vartmp_dir)
 450 
 451         lines = self.raw.split('\n')
 452         
 453         kw = self.kw
 454         column_header = 0
 455         row_header = 0
 456         show_formular = 0
 457         format = ''
 458         zt = 0
 459         for test in kw:
 460              if test == '-column_header': column_header = 1
 461              if test == '-row_header': row_header = 1
 462              if test == '-show_formular': show_formular = 1
 463              if test == '-format': format = string.split(kw[zt+1],",")
 464              zt += 1
 465 
 466         matrix = []
 467         textstr = '\n'.join(lines).strip()
 468         tmpname = re.sub('\s+', ' ', textstr)
 469         tmpname = sha.new(tmpname.encode('utf8','replace')).hexdigest().upper()
 470         tmpname = tmpname + "_sc"
 471         tmpfile = "%s/%s.sc" % (config_sc_vartmp_dir, tmpname)
 472         textstr, formats = table2sc(lines,format,show_formular,self.request)
 473         
 474         data = open(tmpfile, "w")
 475         i = 0
 476         for txt in textstr:
 477             if len(textstr[i]) > 0:
 478                 tmpstr = string.join(textstr[i],'\n')
 479                 data.write('%s\n' % tmpstr.encode('latin-1'))
 480             i += 1
 481         data.close()
 482 
 483         cmd = "%(external_sc)s %(argument)s %(file)s " % {
 484                      "external_sc": config_external_sc,
 485                      "argument": " -W% ",
 486                      "file":tmpfile
 487                }
 488         
 489         f = os.popen(cmd,'r') # popen to get the result of the calculation
 490         result = f.readlines()
 491         #f.flush()
 492         os.unlink(tmpfile) # remove the tmpfile
 493 
 494         right_format = '<)>'
 495         left_format = '<(>'
 496 
 497         for txt in result:  
 498             txt = string.join(txt,'')
 499             cells = string.split(txt,' ')
 500             zres = []
 501             c = 1
 502             for value in cells:
 503                 value = string.join(value,'')
 504                 value = string.strip(value)
 505                 strlen = len(value)
 506 
 507                 if strlen > 0:
 508                     v = ord(value[0])
 509                     if ord('a') <= v <= ord('z') or ord('A') <= v <= ord('Z') or value[0] in " ":
 510                        format = left_format
 511                     else:
 512                         if show_formular == 0:
 513                             format = right_format
 514                         else:
 515                             format = left_format
 516 
 517                     if value == "Blank!@":
 518                         value = " "
 519 
 520                     if string.find(value,"Blank!@"):
 521                         value = string.replace(value,"Blank!@", " ")
 522 
 523                     zres.append( [format, value] )
 524 
 525                     c += 1
 526             matrix.append(zres)
 527         # post formatting of cells according to cut out wiki format strings
 528         # - matrix is here: [ [  [format,content], ... ], ... ]
 529                 
 530         for cellname, cellformat in formats.items():
 531             row, col = cellname
 532             m = matrix[row][col-1]
 533             m[0] = str(cellformat)
 534         
 535         matrix = map(lambda row: "||" + "||".join([f+c for f,c in row]) + "||", matrix)
 536     
 537             # post processing of over all matrix
 538             # - matrix is here: [ "||cell||cell||...||", "||cell||...||", ... ]
 539        
 540         if row_header == 1:
 541             y = []
 542             r = len(matrix)
 543             lines = range(r)
 544 
 545             i = 0
 546             for no in lines:
 547                 matrix[i] = "||<)5%#CCCCCC>'''" + str(no) + "'''"+matrix[i]
 548                 i += 1
 549   
 550         if column_header == 1:
 551             col_names = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 552             x = '||'
 553             if row_header == 1:
 554                 start = 0
 555             else:
 556                 start = 1
 557         
 558             for name in col_names[start:c]:
 559                 x = x + '%(format)s %(value)s %(tab)s' % {
 560                             "format": '<:#CCCCCC>',
 561                             "value": "'''"+name+"'''",
 562                             "tab": '||'}
 563 
 564             matrix.insert(0,x)
 565                 
 566         wikiizer = wiki.Parser(unicode(string.join(matrix,"\n"),'latin-1'),self.request) # parser for wiki tabular
 567         wikiizer.format(formatter)
 568 
 569