Welcome, guest | Sign In | My Account | Store | Cart
24

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, 23 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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)'

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.

11 comments

Andreas Kloss 11 years, 8 months ago  # | flag

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.

Anna Ravenscroft 11 years, 8 months ago  # | flag

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')
Richard Copeland 11 years, 8 months ago  # | flag

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')
Roberto Alsina 10 years, 9 months ago  # | flag

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

Roberto Alsina 10 years, 9 months ago  # | flag

(...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)
Michael Foord 8 years, 7 months ago  # | flag

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!

David Lambert 7 years, 9 months ago  # | flag

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

Larry Hastings 7 years, 6 months ago  # | flag

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)'
Dee Sydia 5 years, 2 months ago  # | flag

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

Raymond Hettinger (author) 5 years, 2 months ago  # | flag

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

Victor Stinner 5 years, 2 months ago  # | flag

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

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

Add a comment

Sign in to comment

Created by Raymond Hettinger on Tue, 30 Nov 2004 (PSF)
Python recipes (4483)
Raymond Hettinger's recipes (97)
HongxuChen's Fav (39)
Rapid prototyping (11)

Required Modules

  • (none specified)

Other Information and Tasks