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
Overall, Python Excel Automation is an innovative process that you can use to create visual reports on Python just the same way as you would on Excel seamlessly. Organizations can leverage Python Excel Automation to leverage their business tasks according to their needs.
Openpyxl is a Python library that provides various methods to interact with Excel Files using Python. It allows operations like reading, writing, arithmetic operations, plotting graphs, etc.
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:
I've done this by using pywin32. It's not a particularly pleasant experience, since there's not really any abstraction; it's like using VBA, but with python syntax. You can't rely on docstrings, so you'll want to have the MSDN Excel reference handy (http://msdn.microsoft.com/en-us/library/aa220733.aspx is what I used, if I remember correctly. You should be able to find the Excel 2007 docs if you dig around a bit.).
See here for a simple example.
from win32com.client import Dispatch xlApp = Dispatch("Excel.Application") xlApp.Visible = 1 xlApp.Workbooks.Add() xlApp.ActiveSheet.Cells(1,1).Value = 'Python Rules!' xlApp.ActiveWorkbook.ActiveSheet.Cells(1,2).Value = 'Python Rules 2!' xlApp.ActiveWorkbook.Close(SaveChanges=0) # see note 1 xlApp.Quit() xlApp.Visible = 0 # see note 2 del xlApp
Good luck!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With