stjordanis / pycel

A library for compiling excel spreadsheets to python code & visualizing them as a graph

Pycel

Pycel is a small python library that can translate an Excel spreadsheet into executable python code which can be run independently of Excel.

The python code is based on a graph and uses caching & lazy evaluation to ensure (relatively) fast execution. The graph can be exported and analyzed using tools like Gephi. See the contained example for an illustration.

Required python libraries:

dateutil, networkx, numpy, openpyxl, ruamel.yaml, and optionally: matplotlib, pydot

The full motivation behind pycel including some examples & screenshots is described in this blog post.

Usage

Download the library and run the example file.

Quick start: You can use binder to see and explore the tool quickly and interactively in the browser:

The good:

All the main mathematical functions (sin, cos, atan2, ...) and operators (+,/,^, ...) are supported as are ranges (A5:D7), and functions like MIN, MAX, INDEX, LOOKUP, and LINEST.

The codebase is small, relatively fast and should be easy to understand and extend.

I have tested it extensively on spreadsheets with 10 sheets & more than 10000 formulae. In that case calculation of the equations takes about 50ms and agrees with Excel up to 5 decimal places.

The bad:

My development is driven by the particular spreadsheets I need to handle so I have only added support for functions that I need. However, it is should be straightforward to add support for others.

The code does currently support cell references so a function like OFFSET works, but suffers from the fact that if a cell is not already compiled in, then the function can fail. Also, for obvious reasons, any VBA code is not compiled but needs to be re-implemented manually on the python side.

The Ugly:

The resulting graph-based code is fast enough for my purposes but to make it truly fast you would probably replace the graph with a dependency tracker based on sparse matrices or something similar.

Excel Addin

It's possible to run pycel as an excel addin using PyXLL. Simply place pyxll.xll and pyxll.py in the lib directory and add the xll file to the Excel Addins list as explained in the pyxll documentation.

Acknowledgements

This code was originally made possible thanks to the python port of Eric Bachtal's Excel formula parsing code by Robin Macharg.

The code currently uses a tokenizer of similar origin from the openpyxl library.