I had pro­posed a talk for Py­Con Ar­genti­na called "Driv­ing 3 Nails with a Shoe". I know, the ti­tle is sil­ly, but the idea was show­ing how to do things us­ing the wrong tool, in­ten­tion­al­ly. Why? Be­cause:

1. It makes you think dif­fer­­ent

2. It's fun

The bad side is, of course, that this talk's con­tents have to be a se­cret, or else the fun is spoiled for ev­ery­one. Since the re­view process for Py­ConAr talks is pub­lic, there was no way to ex­plain what this was about.

And since that means the re­view­ers ba­si­cal­ly have to take my word for this be­ing a good thing to have at a con­fer­ence, which is un­fair, I delet­ed the pro­pos­al. The good (may­be) news is that now ev­ery­one will see what those ideas I had were about. And here is nail num­ber 1: Writ­ing a spread­sheet us­ing doit.

This is not my first "spread­sheet". It all start­ed a long, long time ago with a fa­mous recipe by Ray­mond Het­tinger which I used again and again and again (I may even be miss­ing some post there).

Since I have been us­ing doit for Niko­la I am im­pressed by the pow­er it gives you. In short, doit lets you cre­ate tasks, and those tasks can de­pend on oth­er tasks, and op­er­ate on data, and pro­vide re­sults for oth­er tasks, etc.

See where this is go­ing?

So, here's the code, with ex­pla­na­tion­s:

cells is our spreadsheet. You can put anything there, just always use "cellname=formula" format, and the formula must be valid Python, ok?

from tokenize import generate_tokens

cells = ["A1=A3+A2", "A2=2", "A3=4"]
values = {}

task_calculate creates a task for each cell, called calculate:CELLNAME. The "action" to be performed by that task is evaluating the formula. But in order to do that successfully, we need to know what other cells have to be evaluated first!

This is im­ple­ment­ed us­ing doit's cal­cu­lat­ed de­pen­den­cies by ask­ing doit to run the task "get_de­p:­FOR­MU­LA" for this cel­l's for­mu­la.

def evaluate(name, formula):
    value = eval(formula, values)
    values[name] = value
    print "%s = %s" % (name, value)

def task_calculate():
    for cell in cells:
        name, formula = cell.split('=')
        yield {
            'name':name,
            'calc_dep': ['get_dep:%s' % formula],
            'actions': [(evaluate, (name, formula))],
            }

For example, in our test sheet, A1 depends on A3 and A2 but those depend on no other cells. To figure this out, I will use the tokenize module, and just remember what things are "names". More sophisticated approaches exist.

The task_get_dep function is a doit task that will create a task called "get_dep:CELLNAME" for every cell name in cells.

What get_dep returns is a list of doit tasks. For our A1 cell, that would be ["calculate:A2", "calculate:A3"] meaning that to calculate A1 you need to perform those tasks first.

def get_dep(formula):
    """Given a formula, return the names of the cells referenced."""
    deps = {}
    try:
        for token in generate_tokens([formula].pop):
            if token[0] == 1:  # A variable
                deps[token[1]] = None
    except IndexError:
        # It's ok
        pass
    return {
        'result_dep': ['calculate:%s' % key for key in deps.keys()]
        }

def task_get_dep():
    for cell in cells:
        name, formula = cell.split('=')
        yield {
            'name': formula,
            'actions': [(get_dep, (formula,))],
            }

And that's it. Let's see it in action. You can get your own copy here and try it out by installing doit, editing cells and then running it like this:

ralsina@perdido:~/dosheet$ doit -v2 calculate:A3
.  get_dep:4
{}
.  calculate:A3
A3 = 4
ralsina@perdido:~/dosheet$ doit -v2 calculate:A2
.  get_dep:2
{}
.  calculate:A2
A2 = 2
ralsina@perdido:~/dosheet$ doit -v2 calculate:A1
.  get_dep:A3+A2
{'A3': None, 'A2': None}
.  get_dep:4
{}
.  calculate:A3
A3 = 4
.  get_dep:2
{}
.  calculate:A2
A2 = 2
.  calculate:A1
A1 = 6

As you can see, it al­ways does the min­i­mum amount of ef­fort to cal­cu­late the de­sired re­sult. If you are so in­clined, there are some things that could be im­proved, and I am leav­ing as ex­er­cise for the read­er, for ex­am­ple:

1. Use up­­­to­­date to avoid re­­cal­cu­lat­ing de­pen­­den­­cies.

2. Get rid of the glob­al val­ues and use doit's com­put­ed val­ues in­stead.

Here is the full list­ing, en­joy!