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.
"""ExcelManager contains code for interacting with an Excel spreadsheet. This code should work with both IronPython 1.1 and IronPython 2.0 NOTE: Microsoft Excel must be installed on the machine for this to work. Code should be compatible with Office 2003 or Office 2007 If you get an error message about "IOError: Could not add reference to assembly Microsoft.Office.Interop.Excel" then you need to install the Primary Interop Assemblies for Microsoft Office, which can be downloaded from: Office 2003 PIA: http://www.microsoft.com/downloads/details.aspx?familyid=3c9a983a-ac14-4125-8ba0-d36d67e0f4ad&displaylang=en Office 2007 PIA: http://www.microsoft.com/downloads/details.aspx?familyid=59DAEBAA-BED4-4282-A28C-B864D8BFA513&displaylang=en Install from Office Setup CD: http://msdn.microsoft.com/en-us/library/aa159923(office.11).aspx#officeprimaryinteropassembliesfaq_after The original code gave an Error: 0x80028018 "Old Format or Invalid Type Library" when trying to set worksheet values (Name, Cell.Value2, etc) This is a known issue when the computer's region is set to something other than en-us: http://support.microsoft.com/kb/320369 The ExcelManager.SetProperty method was added as a workaround to this issues. It uses the Reflection and the InvokeMember method to ensure that the CurrentCulture is set to en-US. This method should be used whenever you set a property value to ensure that the code will work in any culture. More info at http://www.ironpython.info/index.php/Interacting_with_Excel """ import sys import clr clr.AddReference("Office") clr.AddReference("Microsoft.Office.Interop.Excel") from Microsoft.Office.Interop import Excel from System.Reflection import Missing, BindingFlags from System.Threading import Thread from System.Globalization import CultureInfo from System import Array, DateTime, DayOfWeek def reload(): """Helper method to reload this script when testing or debuging""" print "Reloading..." global ExcelManager ExcelManager.CloseExcel(True) #Force Excel to close execfile( "D:\\My Documents\\Resolver\\RslPivot V1.4\\ExcelManager.py") def run(): """Sample method for how to use the Excel Manager""" global SheetData SheetData = [['a','b','c'],[1,2,3],[4,5,6],[7,8,9]] #Data should be packed row by row in a multi-dimentional array. First Row is Header Row. ExcelManager.OpenExcel(True) #Open Excel but do not show the window yet ExcelManager.NewBlankWorkbook("My Autofilter") ExcelManager.LoadSheetData(SheetData) ExcelManager.CreatePivotTable("My Pivot Table") ExcelManager.CreatePivotChart("My Pivot Chart") ExcelManager.HideToolbars(lstExcept=["PivotTable Field List", "Standard", "FormulaBar"]) #Disable all the toolbars except the Pivottable, Standard toolbar and FormulaBar ExcelManager.ShowWindow() class ExcelManager(object): """Static class for managing the Excel application. See http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel(office.11).aspx for more details.""" ExcelApp = None #Excel Application Interop Object ExcelWorkbook = None #Excel Workbook Interop Object ExcelWorksheet = None #Excel Worksheet Interop Object PivotTable = None PivotChart = None LocalShortDateFormat = Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern #used to format from OADate to correct format @staticmethod def SetProperty(target, name, value): """Used to set properties using the en-US culture, which is the safest way to do Excel COM integration. See: http://support.microsoft.com/kb/320369""" try: args = Array[object]([value]) return target.GetType().InvokeMember(name, BindingFlags.Instance | BindingFlags.SetProperty | BindingFlags.Public, None, target, args, CultureInfo(1033)) except: print "Error setting Property: " + name print sys.exc_info() raise @staticmethod def OpenExcel(ShowWindow=False): """Opens a new Excel Application. ShowWindow: True means the window will be visible, otherwise the window will be hidden. """ #print "ExcelManager: Opening Excel" ExcelManager.ExcelApp = Excel.ApplicationClass() if ShowWindow: ExcelManager.ShowWindow() else: ExcelManager.HideWindow() @staticmethod def ShowWindow(): ExcelManager.ExcelApp.Visible = True @staticmethod def HideWindow(): ExcelManager.ExcelApp.Visible = False @staticmethod def CloseExcel(ForceClose=False): """Closes the Excel application. If ForceClose=True then the user will not be prompted to save any changes.""" if ExcelManager.ExcelApp <> None and ExcelManager.ExcelWorkbook <> None: print "Quitting Excel..." if ForceClose and ExcelManager.ExcelApp.Workbooks.Count > 0: ExcelManager.SetProperty(ExcelManager.ExcelWorkbook, "Saved", True) ExcelManager.ExcelApp.Quit() @staticmethod def NewBlankWorkbook(SheetName="Worksheet"): """Opens a new blank workbook in Excel with one worksheet with the given Name.""" #print "ExcelManager: NewBlankWorkbook" ExcelManager.ExcelWorkbook = ExcelManager.ExcelApp.Workbooks.Add() #NOTE: use Item.__getitem__(index) to be compatable with both IronPython 1.1 and IronPython 2.0 ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(3).Delete() #workbook starts with 3 blank worksheets. Delete the second and 3rd. ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(2).Delete() #ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(1).Name = SheetName #Threw error whenever the computer's region was set to something other than en-us. Use SetProperty method instead. ExcelManager.SetProperty(ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(1), "Name", SheetName) ExcelManager.ExcelWorksheet = ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(1) @staticmethod def SetCell(RowIndex, ColIndex, Value): #print "Loading Cell(" + str(RowIndex) + str(ColIndex) + ") with " + str(Value) aCell = ExcelManager.ExcelWorksheet.Cells.Item.__getitem__(ColIndex).Item.__getitem__(RowIndex) if type(Value) == DateTime: #aCell.Value2 = Value.ToOADate() #Threw exception when culture was not en-us. Use SetProperty instead ExcelManager.SetProperty(aCell, "Value2", Value.ToOADate()) ExcelManager.SetProperty(aCell, "NumberFormat", ExcelManager.LocalShortDateFormat) else: ExcelManager.SetProperty(aCell, "Value2", Value) @staticmethod def SetRow(RowIndex, ArrayValues): #print "Loading Row " + str(RowIndex) + " with " + str(ArrayValues) intColIndex = 0 for aValue in ArrayValues: intColIndex += 1 ExcelManager.SetCell(RowIndex,intColIndex, aValue) @staticmethod def GetCommandBar(strName): """Helper method used to get a Commandbar. This way the code works with both IronPython 1.1 and IronPython 2.0""" if hasattr(ExcelManager.ExcelApp.CommandBars, "Item"): return ExcelManager.ExcelApp.CommandBars.Item(strName) #IronPython 2.0 else: return ExcelManager.ExcelApp.CommandBars[strName] #IronPython 1.1 @staticmethod def LoadSheetData(SheetData): """Loads data into the Excel WorkSheet. SheetData should contain an object for each row, with each object containing data for that row.""" intRow = 0 for aRow in SheetData: intRow +=1 ExcelManager.SetRow(intRow,aRow) ExcelManager.ExcelWorksheet.UsedRange.AutoFilter(1, Missing.Value, Excel.XlAutoFilterOperator.xlAnd, Missing.Value, True) #Enable Autofilter @staticmethod def CreatePivotTable(strName="Pivot Table"): """Creates a Pivot Table. See http://www.eggheadcafe.com/community/aspnet/2/10050513/solved--export-data-from.aspx for more info.""" NewSheet = ExcelManager.ExcelWorkbook.Worksheets.Add() ExcelManager.SetProperty(NewSheet, "Name", "Pivot Table") ExcelManager.ExcelWorkbook.PivotTableWizard(Excel.XlPivotTableSourceType.xlDatabase, ExcelManager.ExcelWorksheet.UsedRange, NewSheet.UsedRange, strName, True, True, True, False, Missing.Value, Missing.Value, False, False, Excel.XlOrder.xlDownThenOver, 5, Missing.Value, Missing.Value) ExcelManager.ExcelWorkbook.ShowPivotTableFieldList = True ExcelManager.GetCommandBar("PivotTable").Visible = False ExcelManager.PivotTable = NewSheet @staticmethod def CreatePivotChart(strName="Pivot Chart"): """Creates a Pivot Chart. See http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.chart_properties(VS.80).aspx for more info. Example code: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/c7b0f6dd-8890-41c7-a5e0-e18f70686f8c""" ExcelManager.PivotChart = ExcelManager.ExcelWorkbook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) #ExcelManager.PivotChart.ChartWizard(ExcelManager.ExcelWorksheet.UsedRange, Excel.XlChartType.xlColumnClustered, Missing.Value, Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, True, "Pivot Chart Name", Missing.Value, Missing.Value, Missing.Value) #This method threw an 0x800A03EC error message, so set the values seperately ExcelManager.SetProperty(ExcelManager.PivotChart, "ChartType", Excel.XlChartType.xlColumnClustered) #ExcelManager.PivotChart.SetSourceData(ExcelManager.ExcelWorksheet.UsedRange, Excel.XlRowCol.xlColumns) #Threw error... but seems to work fine without it. Must assume that it is a pivot chart ExcelManager.SetProperty(ExcelManager.PivotChart, "HasLegend" , True) ExcelManager.SetProperty(ExcelManager.PivotChart, "HasTitle", True) #Show chart title and set it's text ExcelManager.SetProperty(ExcelManager.PivotChart.ChartTitle, "Text", strName) ExcelManager.SetProperty(ExcelManager.PivotChart, "Name","Pivot Chart") #Set Tab name @staticmethod def HideToolbars(lstExcept=["PivotTable Field List", "Standard", "FormulaBar"]): """Hides all the toolbars and only shows the worksheet and charts. If the toolbar's name is in lstExcept then it will remain visible. NOTE: May cause tool bars to be perminantly disabled if the worksheet is saved! It is advised not to use this method, needs to be reworked.""" #for aBar in ExcelManager.ExcelApp.CommandBars: # if lstExcept.Contains(aBar.Name): # aBar.Enabled = True # else: # aBar.Enabled = False #disable #ExcelManager.ExcelApp.DisplayFormulaBar = lstExcept.Contains("FormulaBar") pass
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.
Enjoy!