pycel 1.0b5

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:

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 not support cell references so a function like OFFSET would take some more work to implement. Not inherently difficult, its just that I have had no need for references yet. 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.

Changes

1.0b5 (2019-02-24)

Changes

• Implement XOR(), NOT(), TRUE(), FALSE()

• Improve error handling for AND(), OR()

• Implement POWER() function

1.0b4 (2019-02-17)

Changes

• Move to openpyxl 2.6+

• Remove support for Python 3.4

1.0b3 (2019-02-02)

Changes

• Work around openpyxl returning datetimes

• Pin to openpyxl 2.5.12 to avoid bug in 2.5.14 (fixed in PR #315)

1.0b2 (2019-01-05)

Changes

• Much work to better match Excel error processing

• Extend validate_calcs() to allow testing entire workbook

• Improvements to match(), including wildcard support

• Finished implementing match(), lookup(), vlookup() and hlookup()

• Implement COLUMN() and ROW()

• Implement % operator

• Implement len()

• Implement binary base number Excel functions (hex2dec, etc.)

• Fix PI()

1.0b0 (2018-12-25)

Major changes

• Converted to Python 3.4+

• Removed Windows Excel COM driver (openpyxl is used for all xlsx reading)

• Add support for defined names

• Add support for structured references

• Fix support for relative formulas

• set_value() and evaluate() support ranges and lists

• Add several more library functions

• Add AddressRange and AddressCell classes to encapsulate address calcs

• Add validate_calcs() to aid debugging excellib functions

• Add build feature which can limit recompile to only when excel file changes

• Improved handling for #DIV0! and #VALUE!

Compatibility

• Tests run on Python 3.4, 3.5, 3.6, 3.7 (via tox)

• Python 2 no longer supported

Other Changes

• Heavily refactored ExcelCompiler

• Moved all formula evaluation, parsing, etc, code to ExcelFormula class

• Convert to using openpyxl tokenizer

• Converted prints to logging calls

• Convert to using pytest

• Add support for travis and codecov.io

• 100% unit test coverage (mostly)

• Add debuggable formula evaluation

• Cleanup generated Python code to make easier to read

• Add a text format (yaml or json) serialization format

• flake8 (pep8) checks added

• pip now handles which Python versions can be used

• Release to PyPI

• Docs updated

Bugfixes

• Numerous

0.0.1 (unreleased)

• Original version available from Dirk Ggorissen’s Pycel Github Page.

• Supports Python 2