Friday, August 10, 2012

3 ways to use Python in Excel

By Vasudev Ram


DataNitro seems powerful. It can be used interactively in Excel, or by writing scripts, including defining functions.

Here is an example of interactive use, from their web site:
>>> from ystockquote import get_price
>>> Cell('A1').value= get_price('GOOG')
>>> Cell('A1').value
642.35
Though they don't mention it on the home page, I think this example embedded formula's result probably will be updated when the spreadsheet is recalculated. Otherwise it would not be too useful.

The ystockquote module used in the example is by Corey Goldberg, a Python developer. It allows you to get stock quotes from Yahoo!. Corey's blog has good Python information and examples. He has also written many open source software tools, in Python and other languages.

Pyvot is a Python tool for Visual Studio. Excerpts:

[ Pyvot enables easy transit of data between Python and Excel.

For example, we can move a list of Python values to Excel, view and manipulate the data, and retrieve the new version. In a symmetric and equally common usage, we can grab and process Excel values in Python, and display the result to Excel.
...
It requires CPython 2.6 or 2.7 with the Python for Windows extensions (pywin32) installed, and Office 2010. ]

Pyvot uses Microsoft COM (Component Object Model) to communicate with Excel. One issue many people (including me) have found with COM, is that it is a) somewhat buggy, and b) is resource-heavy. At least, that was the case when I used it in a project a while ago.

pyxll (Python Excel Addins) makes it possible to write addins for Microsoft Excel in Python. Excerpts:

[ PyXLL makes it possible to write addins for Microsoft Excel in Python. Using simple decorators your Python code can instantly be exposed to Excel as worksheet functions, menu items or macros.
...
Excel addins written using PyXLL are fast to develop and offer high performance as well as being easier to maintain and deploy than other methods of developing Excel addins.
...
PyXLL is used by investment banks, hedge funds and engineering companies all over the world. ]

Finally, if all you need to do is to read the contents of Excel worksheets programmatically, and then process the contents in some way, you may want to check out my xtopdf toolkit, which provides some minimal support for that. This article by me on the Packt Publishing site, shows how to use xtopdf to read the basic contents of Excel files; it can read numbers and text only; it does not support reading font information, formatting, colors, etc. To achieve this, xtopdf uses the xlrd library for Python, so that is a prerequisite.

The steps for setting up xtopdf for reading Excel files, is given at the end of that article, in the section named "4. Conclusion." Note: I had used what was probably an earlier version of xlrd to develop this feature, since I did it some time ago, but after taking a look at the PyPI page for xlrd (linked above) just now, it looks as though things should still work.

An interesting point about xlrd is that is written in pure Python, so you don't need to be on Windows to use it. You can use it to process Excel files on any other platform (such as Linux or UNIX) that supports Python, which may be more convenient for some needs, since Linux and UNIX have powerful software development tools. One obvious way to leverage this is to read the Excel content using xlrd and convert it to tab-delimited values, then process it with AWK.

- Vasudev Ram - Dancing Bison Enterprises

No comments: