# PyCells: The Python SpreadSheet redux

In 2004 I saw a recipe about how to make a "spreadsheet" 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 shocking. And it works, 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 version . Of course there is a catch in that code: it sucks if you are trying to write a spreadsheet with it.

Why? Because it doesn't store results, but only formulas.

For example:

A1=2 A2=A1*2

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

Well, it's nothing yet, because it's only calculated when you ask for it. But suppose you are trying to **display** that sheet... you need to know A2's value changed when you set A1!

That's cell dependencies, and while that simple code handles them in a way, it totally sucks in another.

So, I went ahead and coded around it successfully. Of course the code was not so pretty anymore (although a large part of the uglyness is just for making it work with Python 2.3 and relative cells).

Then yesterday, while looking at the excel formula parser madness I saw a reference to PyCells, a python port of Cells from CLOS.

Here is a blog commenting on Pycells:

It basically takes the concept of a cell in a spreadsheet that get updated automatically to programming where there are a lot of internal data states that are dependent on one another in a chain, or a complex graph of dependencies. Like, the color of a button depends on whether you selected a radio button or not. Or, shut down the motor if the sensor reads above 100 degrees (example given in text).

Almost everyone uses that analogy... however, no matter how hard I looked, I couldn't find anyone who had actually tried writing a spreadsheet using PyCells! Not even as an example!

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 running:

[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 "eval 7" and "eval 2*a1"? That's because it's propagating changes the right way. And that's why this would work as a basis for a spreadsheet.

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

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

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

Thanks, I'll try to take a look

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

this is really interesting viewpoint on the subject i might add

great post

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