Skip to content

Instantly share code, notes, and snippets.

@adamvoss

adamvoss/sstable.py

Created March 4, 2014 04:42
Show Gist options
  • Save adamvoss/9340415 to your computer and use it in GitHub Desktop.
Save adamvoss/9340415 to your computer and use it in GitHub Desktop.
Download ZIP
SSTable (MoinMoin Parser)
Raw
sstable.py
"""
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)||
||<rowbgcolor="#cc99ff">=@sum(A1:A2)||=@sum(B1:B2)||<bgcolor=magenta>=@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||
||<rowbgcolor="#cc99ff">11.00||22.00||<bgcolor=magenta>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
1.0.5: Fixed to work with MoinMoin 1.9 - TiN (on http://moinmo.in/)
Fixed bug reported by TheAnarcat in which formulas containing a
function call only gave the function result ignoring any other operations
This fix also allows multiple functions to be used in a single cell. - Adam Voss
Made functions treat invalid values as 0
so the calculation can still be performed. - Adam Voss
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
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('([a-z]+\d+:[a-z]+\d+)')
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_as_number, self.cellrange(keys)))
def min(self, keys):
return min(map(self.getitem_as_number, self.cellrange(keys)))
def sum(self, keys):
return sum(map(self.getitem_as_number, self.cellrange(keys)))
def average(self, keys):
cells = map(self.getitem_as_number, self.cellrange(keys))
return sum(cells)/float(len(cells))
def median(self, keys):
cells = map(self.getitem_as_number, 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 getitem_as_number(self, s):
val = self[s]
try:
return float(val)
except ValueError:
return 0
def __setitem__(self, key, formula):
key = key.lower()
# save variable name to cell name mapping
m = self.var_re.search(formula)
if m != None:
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
formula = self.range_re.sub(r"'\1'", formula)
# '=' and '@' are unwanted for eval
formula = re.sub('^=@?', '', formula)
elif not self.num_re.match(formula):
m = self.tic_re.match(formula)
if m != None:
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
""" Returns a list containing the names of all cells within the range.
If a range is not provided, returns the split of the input on ','
"""
def cellrange(self, keys):
m = re.search('([a-z]+)(\d+):([a-z])+(\d+)?', keys)
result = []
if m == None:
result = re.split(',', keys)
return result
(c1, r1, c2, r2) = m.groups()
# This looks like a bug when dealing with >26 columns because aa < b.
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): '<wikiformat>', ... }
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: "<format>..."
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('<pre>')
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 != None:
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 + '</pre>')
else:
result = result[1:]
result = wikiutil.unquoteWikiname(result)
wikiizer = text_moin_wiki.Parser(result,self.request) # parser for wiki tabular
wikiizer.format(formatter)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment