Search code examples
pythonexcelscripting

Driving Excel from Python in Windows

We have various spreadsheets that employ deliciously complicated macros and third party extensions to produce complicated models. I'm working on a project that involves slightly tweaking various inputs and seeing the results. Rather than doing this by hand or writing VBA, I'd like to see if I can write a python script to drive this. In other words, the python script will start up, load the excel sheet, and then interact with the sheet by making minor changes in some cells and seeing how they affect other cells.

So, my question is twofold:

  • What is the best library to use to drive excel from python in such fashion?

  • Where's the best documentation/examples on using said library?

Cheers, /YGA

Solution

  • For controlling Excel, use pywin32, like @igowen suggests.

    Note that it is possible to use static dispatch. Use makepy.py from the pywin32 project to create a python module with the python wrappers. Using the generated wrappers simplifies development, since for instance ipython gives you tab completion and help during development.

    Static dispatch example:

    x:> makepy.py "Microsoft Excel 11.0 Object Library"
...
Generating...
Importing module
x:> ipython

    > from win32com.client import Dispatch
> excel = Dispatch("Excel.Application")
> wb = excel.Workbooks.Append()
> range = wb.Sheets[0].Range("A1")
> range.[Press Tab]
range.Activate                 range.Merge
range.AddComment               range.NavigateArrow
range.AdvancedFilter           range.NoteText
...
range.GetOffset                range.__repr__
range.GetResize                range.__setattr__
range.GetValue                 range.__str__
range.Get_Default              range.__unicode__
range.GoalSeek                 range._get_good_object_
range.Group                    range._get_good_single_object_
range.Insert                   range._oleobj_
range.InsertIndent             range._prop_map_get_
range.Item                     range._prop_map_put_
range.Justify                  range.coclass_clsid
range.ListNames                range.__class__
> range.Value = 32
...

    Documentation links: