Spreadsheet « ActiveState Code

Use eval() to drive spreadsheet style logic. The sleeper feature of Py2.4 is the ability to use any object with a mapping interface as the locals argument to eval().

Python

      class SpreadSheet:
    _cells = {}
    tools = {}
    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], SpreadSheet.tools, self)

>>> 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'
>>> ss['a3']
210
>>> ss['b1'] = 'sin(pi/4)'
>>> ss['b1']
0.70710678118654746
>>> ss.getformula('b1')
'sin(pi/4)'

      

Discussion

The _cells dictionary maps cell addresses to formulas. Looking up the cell triggers an eval on that cell formula, possibly resulting in other cells being looked up and evaluated.

The tools class variable is a dictionary of functions and constants you want to make visible to users of the spreadsheet. This doesn't make eval() totally secure, but it improves the situation somewhat.

This is meant to be a minimal example to show the basic idea. The idea scales up well. Without too much effort, I've built out a modest spreadsheet program with error trapping, limited access to globals, handling of circular references, strings, summations, a library of spreadsheet functions, the ability to save and restore (using pickle), and a TkInter GUI that looks and acts like a regular spreadsheet.

Comments

1. At 2:54 a.m. on 1 dec 2004, Andreas Kloss said:
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.

2. At 4:41 a.m. on 1 dec 2004, Anna Ravenscroft said:

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')

3. At 6:29 a.m. on 3 dec 2004, Richard Copeland said:

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')

4. At 12:33 p.m. on 11 oct 2005, Roberto Alsina said:

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]&lt;&gt;"_":
                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...)

5. At 12:33 p.m. on 11 oct 2005, Roberto Alsina said:

(...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] &lt;&gt; 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&lt; 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)

6. At 3:47 p.m. on 8 dec 2007, Michael Foord said:

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!
7. At 7:51 p.m. on 7 oct 2008, David Lambert said:

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

languages:	Python
posted:	Tue, 30 Nov 2004
by:	Raymond Hettinger
rev:	2 (3 years, 11 months ago)
rating:	11 points (11 votes) Sign in to rate this recipe

ActiveState Code

Recipe 355045: Spreadsheet

Discussion

Comments

Tags