Skip to main content

Ralsina.Me — Roberto Alsina's website

PyCells: The Python SpreadSheet redux

In 2004 I saw a recipe about how to make a "spread­sheet" in python in 10 lines of code:

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)

It's shock­ing. And it work­s, too:

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

I was so awed, I wrote a PyQt ver­sion . Of course there is a catch in that code: it sucks if you are try­ing to write a spread­sheet with it.

Why? Be­cause it does­n't store re­sult­s, but on­ly for­mu­las.

For ex­am­ple:

A1=2
A2=A1*2

If you ask for the val­ue of A2, you get 4. If you set A1 to 7, what's the val­ue of A2?

Well, it's noth­ing yet, be­cause it's on­ly cal­cu­lat­ed when you ask for it. But sup­pose you are try­ing to dis­play that sheet... you need to know A2's val­ue changed when you set A1!

That's cell de­pen­den­cies, and while that sim­ple code han­dles them in a way, it to­tal­ly sucks in an­oth­er.

So, I went ahead and cod­ed around it suc­cess­ful­ly. Of course the code was not so pret­ty any­more (although a large part of the ug­ly­ness is just for mak­ing it work with Python 2.3 and rel­a­tive cell­s).

Then yes­ter­day, while look­ing at the ex­cel for­mu­la pars­er mad­ness I saw a ref­er­ence to Py­Cells, a python port of Cells from CLOS.

Here is a blog com­ment­ing on Py­cells:

It ba­si­cal­ly takes the con­cept of a cell in a spread­sheet that get up­dat­ed au­to­mat­i­cal­ly to pro­gram­ming where there are a lot of in­ter­nal da­ta states that are de­pen­dent on one an­oth­er in a chain, or a com­plex graph of de­pen­den­cies. Like, the col­or of a but­ton de­pends on whether you se­lect­ed a ra­dio but­ton or not. Or, shut down the mo­tor if the sen­sor reads above 100 de­grees (ex­am­ple giv­en in tex­t).

Al­most ev­ery­one us­es that anal­o­gy... how­ev­er, no mat­ter how hard I looked, I could­n't find any­one who had ac­tu­al­ly tried writ­ing a spread­sheet us­ing Py­Cell­s! Not even as an ex­am­ple!

So here it is:

import cells

class Cell(cells.Model):
    formula=cells.makecell(value='')
    @cells.fun2cell()
    def value(self,prev):
        print "eval ",self.formula
        return eval(self.formula, {}, self.ss)
    def __init__(self, ss, *args, **kwargs):
        self.ss=ss
        cells.Model.__init__(self, *args, **kwargs)


class ssDict:
        def __init__(self):
                self.ss={}

        def __getitem__(self,key):
                return self.ss[key].value

        def __setitem__(self,key,v):
                if not self.ss.has_key(key):
                        c=Cell(self)
                        c.formula=v
                        self.ss[key]=c
                else:
                        self.ss[key].formula=v

if __name__ == "__main__":
        ss=ssDict()
        ss['a1'] ='5'

        ss['a2']='2*a1'
        print "a1: ", ss['a1']
        print "a2: ", ss['a2']

        ss['a1'] = '7'
        print "a1: ", ss['a1']
        print "a2: ", ss['a2']

And here you can see it run­ning:

[ralsina@monty cells]$ python ctest.py
a1:  eval  5
5
a2:  eval  2*a1
10
eval  7
eval  2*a1
a1:  7
a2:  14

See how when I set a1 to 7 I get "e­val 7" and "e­val 2*a1"? That's be­cause it's prop­a­gat­ing changes the right way. And that's why this would work as a ba­sis for a spread­sheet.

UP­DATE

It seems there is a bug with­er in Py­Cell­s, or in my ex­am­ple, or some­thing, be­cause it breaks pret­ty eas­i­ly, if the de­pen­den­cy chain is even two cell­s:

a1:  eval  5
5
a2:  eval  2*a1
10
a3:  eval  2*a2
20
eval  7
eval  2*a1
a1:  7
a2:  14
a3:  20

In this ex­am­ple, I am set­ting A3 to 2*A2, and when I up­date A1, A3 is not up­dat­ed. Fur­ther re­search is need­ed.

UP­DATE 2

Please check the red­dit com­ments. They are very ed­u­ca­tion­al!

Anonymous / 2007-05-22 15:12:

Sweet use of self.ss as local namespace for "eval". Well done !

Roberto Alsina / 2007-05-22 16:08:

I can't take credit for that, it's from Raymond Hettinger's original recipe.

Jim Thomas / 2009-04-29 00:47:

You can solve dependencies using Tarjan's algorithm. If you can build a dictionary like this:

{ 0 : [1], # 1 depends on 0
1 : [2], # 2 depends on 1
2 : [1,3], # 1 and 3 depends on 2
3 : [], # Nothing depends on 3
}

Tarjan's will give you a list back like so:
[(0,), (1, 2), (3,)]

If you calculate in that order you will not have a problem. It also shows you that 1 and 2 have to be solved simultaneously.

There is a nice, liberally licensed, implementation available here: http://www.logarithmic.net/...

It will work with any hashable type for the indexes, not just numbers.

JT

Roberto Alsina / 2009-04-29 13:07:

Thanks, I'll try to take a look

A3 printer / 2010-08-28 08:19:

This is cool! And so interested! Are u have more posts like this? Plese tell me, thanks

phone number lookup / 2011-12-03 22:22:

this is really interesting viewpoint on the subject i might add

employment background check / 2011-12-27 23:20:

great post

cell phone lookup / 2012-01-17 05:52:


Your blog has the same post as another author but i like your better