Python Spreadsheets: Like Resolver, Only in Excel and Free

(see http://catalystac.com/trac for latest code source)

Resolver Systems sells a product that does Python with a spreadsheet UI. Resolver uses their own spreadsheet frontend. Here's a demo of how to add Pythonability to Excel. This code is buggy and barely tested and its only a small part of what is an even buggier master's thesis. This demo will show you how to synchronize the visible range in Excel from a running Python session. This allows the Python spreadsheet engine to run independently of Excel allowing me to partition and compile sheet iteration functions onto various co-processors and report the values to the frontend at a lower frequency. I worked on this demo enough to hope that it might work on your machine too.

Grab the code that I pieced together and extract both RhoZeta.py and Excel.py into the same directory. The code is free for you to use: Python is a language that thrives off of open-source sweat. Let me know if you do anything with this (amirh at mit dot edu).

Here's the demo steps with screenshots:

CAUTION: DO NOT RUN THIS WITH OPEN EXCEL WORKBOOKS OR YOU MAY SCREW UP YOUR DATA.

Load RhoZeta.py in PythonWin and click on "Run" and "OK."


After Excel loads you will have an empty workbook. Try typing "=range(0,10)" into A1.

As you might expect from a Python spreadsheet this produces the repr() or the list as a text string in cell A1. Now type "=dir()" in cell A2 to discover the local context.

When we entered the formula in A2, the only assigned cell is A1. If this spreadsheet were recalculating we would want cell A2 to constantly update to inform us which cells are in the local context. Now let's see what the global dictionary looks like:

This produces a dictionary representation in A3 (encased by squiggly {} brackets) . In order to access the members of this dictionary we would like to be able to use the form "=A3[key]" but because Excel doesn't like the square [] brackets we need to precede our formula with an apostrophe. If Excel complains about your perfectly reasonable Python formula, you can precede it with an apostrophe (seriously try without the apostrophe ' and enjoy the message).

OK, so we can reference into a dictionary and fetch a function object. In this case "RangeToAddressArray" is a utility function which takes a textual representation of a range and converts it into a 2-D array of addresses. The point here is that we can store a function in a cell and then apply that function using the cell's address.

We can also bind a cell to an anonymous functions using lambda. Here's how to create the square function:


Now let's go put a value in B8 and apply that square function:

You can also reference attributes in the usual way. In this case the ExcelEvents class has an attribute RZ which points to the RhoZeta spreadsheet engine. (The binding mechanism is a bit awkward because of the way ExcelEvents is passed to the win32com DispatchWithEvents method).

We can actually use the RZ object here to read from the cells:

Here B13 contains the formula '=B11['Book1'].Sheets. This screenshot also demos how the ActiveCell has it's formula overwritten with the internal RhoZeta implementation. The idea was to have editable and draggable formulas, but setting the formulas of a Range doesn't work quite yet. Notice B11 reports "#NAME?" as Excel attempts to execute the formula "=ExcelEvents.RZ" which displays in the formula bar.


So this system doesn't do a lot yet, but hopefully this will give a good foot forward to other people to tinker. The remaining spreadsheet engine from my thesis consists of CellManager threads which compile and run the formulas (using asynchronous, blocking or non-blocking interpretations) and Catalyst threads which walk the spreadsheet graph and perform optimizations. I'll incorporate these elements and eventually this will suck even less.

You can also have fun graphing from Excel into PyOpenGL Kinda like what this guy did, but in PyOpenGL. Here's a tease: