A library for compiling excel spreadsheets to python code & visualizing them as a graph
Project description
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.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
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.