Tuesday, June 09, 2009

DateSelector and Excel COM Automation Examples

Greg Bray has been using IronPython since entering (and winning) a round of the Resolver Systems spreadsheet competition. Since then he has had fun experimenting with IronPython has posted a couple of fairly complex example programs to his blog.
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.


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.
Another quick IronPython snipet that shows how to use the Windows.Form.MonthCalendar control to create a simple date selector.


Works well for selecting singe dates, and could be modified to support selecting a date range if desired. I prefer having a Start Date and End Date, and selecting each separately.

No comments:

Post a Comment

Note: only a member of this blog may post a comment.