---
author: ''
category: ''
date: 2007/05/22 10:18
description: ''
link: ''
priority: ''
slug: BB585
tags: StupidSheet, programming, python
title: 'PyCells: The Python SpreadSheet redux'
type: text
updated: 2007/05/22 10:18
url_type: ''
---
In 2004 I saw a recipe_ about how to make a "spreadsheet" in python in 10 lines of code:

.. _recipe: http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/355045

.. code-block:: 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)

It's shocking. And it works, too:

.. code-block:: python

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

.. _the code was not so pretty anymore: //ralsina.me/static/StupidSheet-0.2/ssheet/engine.py

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:

.. code-block:: python

    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.

.. _commenting on PyCells: http://webjazz.blogspot.com/2006/05/pycells-and-complex-and-emergent.html


.. _Cells: http://common-lisp.net/project/cells/

.. _pycells: http://pycells.pdxcb.net/

.. _excel formula parser madness: //ralsina.me/weblog/2007/05/21.html#BB584

.. _coded around it: //ralsina.me/weblog/2004/12/14.html#P276
.. _a pyqt version: //ralsina.me/weblog/2004/12/07.html#P272

.. topic:: UPDATE

  It seems there is a bug wither in PyCells, or in my example, or something, because it breaks pretty easily, if the dependency chain is even two cells::

    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 example, I am setting A3 to 2*A2, and when I update A1, A3 is
  not updated. Further research is needed.

.. topic:: UPDATE 2

  Please check the reddit comments_. They are very educational!

.. _comments: http://programming.reddit.com/info/1smqd/comments