sstable

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 wiki:/sctable parser by ReimarBauer.

CALLING SEQUENCE

{ { {
#!sstable [-column_header, -row_header, -show_formular, -format, -input_separator, -output_separator ]
} } }

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 set the number of digits of a real number for each column (separator is a comma), e.g. -format 1,2 means first column has one digit and second has two digits.
-input_separator : is used to read tables delimitted by something other than the default '||'
-output_separator : is used to write tables delimitted by something other than the default '||'

PROCEDURE

• All formulars have to start by a "=" sign.
• All formulars do not need a "@", but they can have one.
• text in the cells is printed to the left and numbers to the right
• Please remove the version number from the routine name!

RESTRICTIONS

MODIFICATION

       @copyright: 2006-03-25 by Andrew Shewmaker (agshew AT gmail) sstable.py

        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)

resolved issues

• You don't use eval, don't you? -- FlorianFesti

  Yes. However, the code (from Richard Copeland) restricts what is evaluated by setting __builtins__ to None in the tools dict. -- AndrewShewmaker

• May be you could add a History tag to the code comments as we have done it for ImageLink. Or add my name behind 1.0: based on sctable-1.5.2-5. I do prefer a python only version too. Nice work! -- ReimarBauer 2006-04-01 08:56:57

  Thanks! I'll add your name behind the reference to sctable. -- AndrewShewmaker

• special signs as e.g. german "Umlaut" chars can't be used in the current version

  ||öäü||ÄÜÖ|| asd||
  ||1||2||=A0+B0||
  ||10||20||=@sum(A1:B1)||

  I had been developing this parser under Mac OS X, Python 2.3.5, and MoinMoin 1.3.x and it didn't like unicode function, so I disabled it. When I tested it on FC4, Python 2.4.1, MoinMoin 1.5, I forgot to switch the second to the last line of the parser back to using the unicode function. Change is in sstable-1.0.1.py, and the Parser doesn't choke now, but it didn't look correct because I guess I don't have the right fonts. -- AndrewShewmaker

  #wikiizer = wiki.Parser(result,self.request) # parser for wiki tabular
  wikiizer = wiki.Parser(unicode(result,'latin-1'),self.request) # parser for wiki tabular

  The last four lines should be better something like this. Then it looks correct. -- ReimarBauer 2006-04-01 20:18:30

  from MoinMoin import wikiutil
  result = wikiutil.unquoteWikiname(result)
  wikiizer = wiki.Parser(result,self.request) # parser for wiki tabular
  wikiizer.format(formatter)

  Yes, that looks right. Included in 1.0.2 -- AndrewShewmaker 2006-04-02 00:27:28

  • Here I cannot enter non-ASCII characters in a table. 1.0.3. The following table cashes with 'ascii' codec can't encode character u'\xe9' in position 13: ordinal not in range(128):

    • ||'''Taux horaire:'''||=D2/B2      ||'''Heures prévues:'''|| 40 ||'''Heures restantes:'''   || =D0-B2 ||
      ||Travailleur       ||Heures      ||Ratio        ||Salaire|| Avance || Balance || 
      ||'''Total'''       ||=@sum(B3:B4)||=@sum(C3:C4) ||=D0*35||=@sum(E3:E4) || =D2-E2  ||
      ||JoeUser           ||2.5         ||=B3/B2       ||=C3*D2|| 0       || =D3-E3  ||
      ||OtherUser         ||10          ||=B4/B2       ||=C4*D2|| 0       || =D3-E3  ||

      it's the "é" it doesn't like. This works in sctable. -- TheAnarcat 2006-05-05 17:50:21

      • Update: this table now works with sstable 1.0.4 on moinmoin 1.5.4, but i have to remove the "bold" (''') because if i leave them there, the cells appear empty (!?). -- TheAnarcat 2006-09-04 19:28:04

• unparseable entries should be just displayed as is... I was having trouble with sctable (and now equally with sstable) with entries that look like numbers but aren't. Dates, for example, are not well parsed by sstable (2006-02-02 becomes 2002.00), IP addresses crash the thing (invalid float or something), etc. IMHO, entries should be calculated only if prefixed by an equal sign, as Excel/OpenOffice Calc does. BTW, to enter an equal sign in Excel, you prefix a single quote. This is what most people (or at least me ) would expect from a spreadsheet. If you don't want to change semantics like this, at least avoid crashing on invalid input and just display invalid input as such... -- TheAnarcat 2006-04-21 20:45:13

  When I first made this parser, I thought that I would expose the Python implementation of the spreadsheet. In other words, I was planning on treating strings and numbers exactly as Python does. However, I think that you are right that most people would expect this to behave more like other spreadsheets they've used. That's what I've attempted to provide with version 1.0.3. Dates and IP addresses are presented as typed, entries are only calculated if prefixed with an equal sign, and you can prefix an equal sign with a single quote. Please try it again. -- AndrewShewmaker 2006-04-24 11:54:21

  • Super! That fixed display of IP addresses! There are a few more things though, I hope you don't mind me sharing those here...

    • If I input just a dash ("-") in a field, I get ValueError: invalid literal for float(): -.

    • The sstables need to be square, as opposed to regular Moin tables.
    • Invalid values are not tolerated by functions (should be considered as 0)

    Can that be fixed too? In general, I think there should be a try: somewhere that catches parse errors and just sets fields to 0 or raw. I had a hard time figuring out where to do this in the code... Thanks for everything! -- TheAnarcat 2006-04-25 16:25:42

  • You're welcome. As of 1.0.4, a single dash is valid and a type error will cause a spreadsheet function to return 0 (one invalid input will currently cause the function to return 0). I don't think I can help you with the square tables request. I've moved parsing of input into the setitem method of the Spreadsheet class. In my experience, I usually see errors raised in the getitem method (which does have try/catch blocks). -- AndrewShewmaker 2006-04-26 07:19:05

    • Excellent, thank you!! -- TheAnarcat 2006-05-05 17:50:21

• May be we need only to write a python routine from one of our idl routines string_is_number. May be something similiar is already somewhere defined. -- ReimarBauer 2006-04-25 17:38:23

  • I'd like to keep trying to make a regular expression work. Do you see any problems with self.num_re = re.compile('^-?(\d*)\.?[\d]+$') -- AndrewShewmaker 2006-04-26 07:19:05

    • This seems to go well I do some further tests later.

• Another problem is if you missed to fill the whole table e.g.

  ||10||20||
  ||=1*3|| 

  then it ends up with a KeyError 'b1'. sctable is different for that and has shown the result of all given cells -- ReimarBauer DateTime(2006-04-29T18:10:51Z)

  • Update: this now doesn't crash in 1.0.4, but the filling cell has some random string in it. -- TheAnarcat 2006-09-04 19:28:04

• I would like to make sstable use "0" for empty rows, where in the code do I have to look for this? Thanks! -- AlvaroTejero 2007-01-05 16:45:25

  • One of these lines ought to be the place. You probably don't need t change both. I didn't test this. -- AndrewShewmaker 2007-01-05 23:51:05

    --- sstable.py  2006-04-26 07:07:06.000000000 -0600
    +++ sstable-test.py     2007-01-05 23:46:24.000000000 -0700
    @@ -384,7 +384,7 @@
             while True:
                 try:
                     if ( self._cells[key] == None or self._cells[key] == ''):
    -                    rv = ''
    +                    rv = '0'
                     else:
                         rv = eval(self._cells[key], self.tools, self._cache)
                     break
    @@ -534,7 +534,7 @@
     
                     cell = str(self.ss[cellname])
                     if cell == '':
    -                    cell = ' '
    +                    cell = '0'
     
                     if show_formular == 0:
                         num_match = self.ss.num_re.match(cell)