ActiveState Code

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:

https://www.resolversystems.com

https://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)'