Monday, June 8, 2009

IronPython Excel COM Automation: Pivot this!

I have been playing around with IronPython recently while creating spreadsheets in Resolver One. So far I have been having a lot of fun, but I haven't really been doing anything that would be useful in a work setting. The main reason for this is because most of the spreadsheet work that I do revolves around pivot tables and Auto Filter, which are immensely helpful for visually analyzing data. I thought I might try and use Excel's COM Automation to add Pivot Charts and Auto Filter to any system with IronPython, and here is the code that I have come up with so far:
UPDATE 7/13/2009: After releasing the initial code, I found that there was a problem with the HideToolbars function causing the Excel toolbars to be disabled permanently. Also, the code would not work if the user's Region was set to something other than "en-US" due to this known bug. I updated the code to disable the HideToolbars function and use Reflection and the InvokeMember method to force the Culture to be "en-US". If you want to view the old "en-US" only code, you can download it here.

The code should be pretty straight forward. The Reload() method is helpful for reloading the whole module when testing from a console. The Run() method shows the steps to load a new spreadsheet. The ExcelManager static class is the core of the logic and provides an entry point for interacting with the spreadsheet.
There is not a lot of debuging code, so if something goes wrong it is just going to throw an exception, but this should be enough to help get things started. I did fix some funky problems with importing DateTime, where they kept getting converted into integers. Should work fine now, but anything other than Datetime, numerical types or strings will probably not get imported correctly.
If anyone else needs to do Excel Interop with IronPython, feel free to use the code, which can be downloaded here.


Greg Bray said...

NOTE: the HideToolbars method needs a bit of work, as right now it can hide some of the Excel toolbars permanently. To restore them back to normal you have to rename or delete the Excel.xlb file, which is usually located at:

C:\Documents and Settings\username\Application Data\Microsoft\Excel

I thought the changes weren't permanently, but sometime they are. I'll try making another version later...

Greg Bray said...

Also, if you get an error message such as "IOError: Could not add reference to assembly Microsoft.Office.Interop.Excel" then you need to install the Primary Interop Assemblies for Microsoft Office.

Download PIA for 2003 Here

Download PIA for 2007 Here

or Install the PIA from the Office Setup CD

Blog.TheG2.Net - Your guide to life in the Internet age.