Spreadsheet (Python recipe) by Raymond Hettinger

ActiveState Code (http://code.activestate.com/recipes/355045/)

You may or may not want to use globals(). This is an example of why I like languages with "eval". ;) To be on the safe side, you may want not to use globals() as a simple

s = Spreadsheet()
s["a1"] = 'exec("import os; os.unlink(\"really_important_file\")")'
print s['a1']

will delete an important file.

In short, if you are not very careful with evaluation, a bad spreadsheet may really mess up your system.

If you want to allow this depends on how much you trust your users.

really kewl! But I had to move things around a bit to determine that the math module wasn't part of the class. Here's how I have it rearranged it right now - this way I could also obviously use it with decimal or datetime or whatever I need to, but that I don't have to import anything for the class itself.

class SpreadSheet:
    _cells = {}
    def __setitem__(self, key, formula):
        self._cells[key] = formula
    def getformula(self, key):
        return self._cells[key]
    def __getitem__(self, key ):
        return eval(self._cells[key], globals(), self)

if __name__ == "__main__":

    from math import sin, pi

    ss = SpreadSheet()
    ss['a1'] = '5'
    ss['a2'] = 'a1*6'
    ss['a3'] = 'a2*7'
    print "a3: ", ss['a3']
    ss['b1'] = 'sin(pi/4)'
    print "b1: ", ss['b1']
    print "b1 formula: ", ss.getformula('b1')

Make it work in Python 2.3 & restrict eval. By catching NameError, you can make this work even under Python 2.3. It would also be possible to modify it a bit to easily detect infinite loops. The code below shows how to further restrict what kind of formulas you use in a cell by setting __builtins__ to None in the tools dict. (This disallows the nastiness Andreas pointed out above.)

class SpreadSheet:
    _cells = {}
    tools = {}
    _cache = None
    def getformula(self, key):
        return self._cells[key]
    def __setitem__(self, key, formula):
        self._cells[key] = formula
    def __getitem__(self, key ):
        bCache = self._cache is None
        if bCache: self._cache = {}
        while True:
            try:
                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:
                    raise
        if bCache: self._cache = None
        return rv

if __name__ == "__main__":

    from math import sin, pi
    from pprint import pprint
    ss = SpreadSheet()
    ss.tools.update({'sin':sin, 'pi':pi})
    ss.tools.update({'__builtins__':None})
    ss['a1'] = '5'
    ss['a2'] = 'a1*6'
    ss['a3'] = 'a2*7'
    ss['a4'] = '__import__("sys").path'
    print "a3: ", ss['a3']
    try:
        print 'a4: ', ss['a4']
    except NameError, ne:
        print ne
    del ss.tools['__builtins__']
    print 'a4: ', ss['a4']
    ss['b1'] = 'sin(pi/4)'
    print "b1: ", ss['b1']
    print "b1 formula: ", ss.getformula('b1')

Here's my take on it. It works on 2.3, it supports cell dependencies, automatically recalculates when needed, caches compiled versions of the formulae, and a couple of other things.

Sadly, that bloats it to about 100LOC, but I don't think that's too bad for a almost-working spreadsheet!

It has some PyQt-ism somewhere, feel free to ignore them. And feel free to fix what may be broken ;-)

class SpreadSheet(QObject):
    _cells = {}
    _compiledcells={}
    tools = {}
    _cache = None
    _lastval={}

    #Reverse dependencies: if cellA is in _deps[cellB], then cellA depends on cellB
    _deps={}

    #What cells I am evaluating right now
    _eving=[]

    def __init__(self,parent):
        global obj
        QObject.__init__(self,parent)
        for name in dir(math):
            if name[0]<>"_":
                self.tools[name]=eval('math.'+name)


    def getformula(self, key):
        if key in self._cells:
            return self._cells[key]
        else:
            return ''

    def reCalculate(self,key):
        #recalculates all the dependencies of the key
        if key in self._deps:
            for dep in self._deps[key]:
                self.emit(PYSIGNAL("cellChanged"),(dep,self[dep]))
                self.reCalculate(dep)

    def __setitem__(self, key, formula):
        if formula.strip()=='': #Empty formula
            if key in self._cells:
                del self._cells[key]
                del self._compiledcells[key]
            if key in self._deps:
                del self._deps[key]
        else:
            self._cells[key] = formula
            self._compiledcells[key] = compiler.compile(formula,"Formula for %s"%key,'eval')
            if key not in self._deps:
                self._deps[key]=[]

        #Since this a new formula, it doesn't yet depend on
        #any other cells. The dependencies will be
        #calculated when it's evaluated
        for k in self._deps:
            if key in self._deps[k]:
                self._deps[k].remove(key)

    def __getitem__(self, key ):
        #Dependency tree
        if key in self._eving:
            #Evaluating a cell in a loop
                self._eving=[]
                raise "Loop1"

        #whatever I am evaluating is a dependency
        #for the last key I was evaluating
        if len(self._eving)>0:
            if self._eving[-1] not in self._deps[key]:  #But only once
                    self._deps[key].append(self._eving[-1])

        self._eving.append(key)

        bCache = self._cache is None
        if bCache: self._cache = {}
        while True:
            try:
                try:
                    f=self._cells[key]
                    if f.strip()=='':
                        rv=''
                    else:
                        rv = eval(self._compiledcells[key], self.tools, self._cache)
                except KeyError: #key not in _cells
                    rv=''
                break
            except NameError, ne:
                name = ne.args[0][6:-16] # Extract name from NameError

(comment continued...)

(...continued from previous comment)

                if name in self._cells:
                    self._cache[name] = self[name]
                elif name[0]=='_' and name[1:] in self._cells:
                    self._cache[name] = self[name[1:]]
                elif isKey(name): #Default value is  ''
                    self[name]=''
                    self._cache[name] = self[name]
                else:
                    self._eving=[]
                    raise
        if bCache: self._cache = None
        if self._lastval.has_key(key):
            if self._lastval[key] <> rv:
                self.emit(PYSIGNAL("cellChanged"),(key,rv))
                self._lastval[key]=rv
        self._eving.remove(key)
        return rv


def isKey(key):
    if (key[0].isalpha() and key[1:].isdigit()) or (key[0:1].isalpha() and key[2:].isdigit()):
        return True
    return False

def coordKey(x,y):
    if x< 26:
        key=chr(97+x)
    else:
        key=chr(97+int(x/26))+chr(97+x%26)
    key=key+str(y+1)
    return key


def keyCoord(key):
    if key[1].isalpha():
        x=(ord(key[0])-97)*26+ord(key[1])-97
        y=int(key[2:])-1
    else:
        x=ord(key[0])-97
        y=int(key[1:])-1
    return (x,y)

Resolver IronPython Spreadsheet. There is an IronPython application that has taken this idea many steps further:

http://www.resolversystems.com

http://www.resolverhacks.net

You have the API to create the spreadsheet programmatically, or the grid to enter data and formulae - which are translated into Python code!

Ray's spreadsheet is brilliant. I wish I could think of such concise algorithms. If I were ambitious I'd insert a __str__ method.

But everybody knows--formulas start with '='!

This is a marvelous, elegant recipe. I decided to tweak it slightly, to more closely mirror real spreadsheets--which require an = on the front of a live expression. I also store static values in their native types, instead of as strings. Internally I store live expressions as strings without the '=' (so I don't have to slice it each time I eval it) and static values as a one-element tuple. Sadly, this meant the code is slightly less elegant.

(p.s. It works unchanged in Python 2.6 and 3.0!)

class SpreadSheet:
  _cells = {}
  tools = {}
  def __setitem__(self, key, formula):
    if isinstance(formula, str) and formula[0] == '=':
        formula = formula[1:]
    else:
        formula = (formula,)
    self._cells[key] = formula
  def getformula(self, key):
    c = self._cells[key]
    if isinstance(c, str):
        return '=' + c
    return c[0]
  def __getitem__(self, key ):
    c = self._cells[key]
    if isinstance(c, str):
      return eval(c, SpreadSheet.tools, self)
    return c[0]

from math import sin, pi
SpreadSheet.tools.update(sin=sin, pi=pi, len=len)
ss = SpreadSheet()
ss['a1'] = 5
ss['a2'] = '=a1*6'
ss['a3'] = '=a2*7'
assert ss['a3'] == 210
ss['b1'] = '=sin(pi/4)'
assert ss['b1'] == 0.70710678118654746
assert ss.getformula('b1') == '=sin(pi/4)'

Do the codes work on Python 2.5? My work mainly involves using 2.5, and it would be nice if they would work on Py2.5...

Thanks in advance!

Dee

Hello Dee. Yes, it should work just fine on {ython 2.5.

s["a1"] = 'exec("import os; os.unlink(\"really_important_file\")")'

Try pysandbox to avoid this kind of problem: https://github.com/haypo/pysandbox