Excel gets Python programming power, thanks to Xlwings library

A new Python library allows Microsoft Excel spreadsheets and Python applications to share data and code

Excel for iPad icon

Microsoft Excel remains a tool of choice for big data mavens, due to the numerous ways it can plug into third-party data sources and massage them with ease. Python, too, has become a go-to technology for number-crunchers of all persuasions. So isn't it high time Excel and Python worked better together?

That's the concept behind Xlwings, a BSD-licensed Python library that connects to Excel and allows Excel spreadsheets and Python applications to interact directly.

Xlwings creator Zoomer Analytics touts its library as a way to use Python code instead of Visual Basic within Excel -- which most Pythonistas would find appealing, given that Python has a far broader (and more powerful) range of math and stats libraries than VB.

Setting up Xlwings requires only having an instance of Python with the Xlwings library installed, no other dependencies needed. Executing Python code from within Excel involves importing a single BAS file and using the RunPython command. No add-ins have to be installed.

The resulting spreadsheets can be packaged and distributed as is, although other users will need both Excel and an instance of Python with Xlwings to use them. Some examples are available as prepackaged downloads from the Xlwings site. Both Python 2 and Python 3 are supported, and Xlwings can make proper use of features from two common math and stats packages, NumPy and Panda.

The Xlwings developers warn that the project is still in its early stages, and later revisions may break the API in ways that are backward-incompatible.

Automating Microsoft Office with Visual Basic hasn't changed much over the course of the product's lifetime, in large part because of its massive installed base. Any major changes would most likely break backward compatibility with the wealth of documents and templates already created for Office. OpenOffice.org and LibreOffice, by contrast, can use Python directly as a macro language.

Python and Microsoft Office have had ways to work together in the past -- for instance, by using libraries like Ipython and Pywin32. But Xlwings hints at the ways deeper integration could be built between Microsoft Office and Python -- with or without Microsoft's help.

Copyright © 2014 IDG Communications, Inc.

How to choose a low-code development platform