Geomagic :: Knowledge Base Nuggets - How can I interact with Microsoft Excel© with Python?

Sandham's Flying Circus

Ping-Fu-exec

Beyond the Box

The news and stories from Geomagic that you don't find anywhere else.

Ping-Fu-exec

Sandham's Flying Circus

The crazy world of customizing Geomagic products

Ping-Fu-exec

The World is 3D

Seeing the world in 3D from Ping Fu

May 15, 2012

Knowledge Base Nuggets - How can I interact with Microsoft Excel© with Python?

Let's face it...many of us use Microsoft Excel.  It's easy to learn, intuitive, and a proven technology that can organize, process, and present data.  Even if you haven't authored a Workbook you probably use someone else's spreadsheet and macro's...maybe not even realizing the power at your finger tips. Well my fellow system integrators...tired of CSV?...tired of XML?...get ready and hang on...and let your Geomagic scripts dynamically update your workbooks...

Q: How can I interact with Microsoft Excel© with Python?

A: The answer is Yes.  There are several ways to read and write Excel formatted data with Python.  This article will cover two of these methods.  The first is simply read and write Excel formatted data to disk.  The second is an interactive connection to Excel itself.

 

xlrd and xlwt

Python-Excel.org distributes and supports the xlrd and xlwt Python modules that enable you to read and write Microsoft Excel 2003 (and earlier) data files.  So far in experimentation these files are compatible with the latest versions of Excel, but do not support all of the later developments of Excel 2007 and beyond...yet.

The modules are xlrd and xlwt.  These provide the ability to read and write xls, cvs and do not require Windows or even Excel.  They can be downloaded from their support site here:

 

http://www.python-excel.org/

On this site you can also find a tutorial on their usage: http://www.simplistix.co.uk/presentations/python-excel.pdf

You can also find support at other sites as well, such as Google http://groups.google.com/group/python-excel

 

pywin32

This next method is a little more tricky, but it allows for a "live" connection to an open Excel Document...even the latest version of Excel.  It requires a few modules that are not "stock" Python and decent ability to convert VB to Python.  It also reuires Windows and Excel.

The solution uses win32 to open an Excel application process on a Windows platform and connect to it using COM.

Some great references that I use can be found online (stackoverflow.com is my favorite), but I would start with a book: Python programming on Win32 by Mark Hammond and Andy Robinson, published by O'Reilly.

You can also find a good helper class from these guys from their website: http://starship.python.net/~skippy/ppw32/index.html

The latest version of pywi32 (as of the publish data of this article) can be found here: http://sourceforge.net/projects/pywin32/files/pywin32/Build%20217/

With sample code: http://starship.python.net/~skippy/ppw32/ppw32_samples.zip

 

Here is a simple implementation within the GPSE using Qualify, Qualify Probe, Wrap or Studio: PythonExcel.zip