Two IronPythonic Spreadsheets

Jon Udell is at it again; this time looking at different ways to wrangle data with IronPython.

I should get a life, I know, but I can’t help myself, one of my favorite pastimes is figuring out new ways to wrangle information. One of the reasons that IronPython had me at hello is that, my fondness for the Python programming language notwithstanding, IronPython sits in an interesting place: on Windows, side by side with Office, where a lot of information gets wrangled — particularly in spreadsheets.

• Two IronPythonic Spreadsheets

He has been looking at two very different ways of working with spreadsheets, and both of them have IronPython integration. The first of these is a tool called Inference.NET, which is part of the Inference suite that integrates statistical and analytical tools into Microsoft Excel. Inference.NET allows you to use the "general-purpose scripting capabilities of the dynamic .NET languages, including IronPython and IronRuby".

The second is Resolver One by Resolver Systems (the company I work for). Resolver One is a programmable spreadsheet that is actually written in IronPython and exposes the core object model of the spreadsheet to user code:

With Inference, the IronPython engine is loosely coupled to the Office apps. That buys you the full fidelity of the applications, but costs you Pythonic impedance.

With Resolver One there is no impedance. The application and your data are made of Pythonic stuff. You give up a ton of affordances in order to get that unification, but it enables some really interesting things.

He particularly likes one of our new features: row and column level formulae.

This is a pretty handy idea all by itself. Instead of putting a formula into the first row of a column and then copying it down, you put it into the column header where it applies to the whole column automatically.

The rest of the post demonstrates how column level formulae work, and how he uses them in his crime analysis spreadsheet to transform data using a pipeline, ending up with XML.