|build-state| |coverage| |requirements|
|pypi| |pypi-pyversions| |repo-size| |code-size|
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 <http://www.gephi.org>_. See the contained example
for an illustration.
Required python libraries:
ruamel.yaml <https://yaml.readthedocs.io/en/latest/>, and optionally:
The full motivation behind pycel including some examples & screenshots is
described in this
blog post <http://www.dirkgorissen.com/2011/10/19/ pycel-compiling-excel-spreadsheets-to-python-and-making-pretty-pictures/>_.
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: |notebook|
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.
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 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.
It's possible to run pycel as an excel addin using
PyXLL <http://www.pyxll.com/>_. 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.
This code was originally made possible thanks to the python port of
Excel formula parsing code <http://ewbi.blogs.com/develops/popular/excelformulaparsing.html>_
by Robin Macharg.
The code currently uses a tokenizer of similar origin from the
openpyxl library. <https://foss.heptapod.net/openpyxl/openpyxl/-/tree/branch/default/openpyxl/formula/>_
.. Image links
.. |build-state| image:: https://travis-ci.com/dgorissen/pycel.svg?branch=master :target: https://travis-ci.com/dgorissen/pycel :alt: Build Status
.. |coverage| image:: https://codecov.io/gh/dgorissen/pycel/branch/master/graph/badge.svg :target: https://codecov.io/gh/dgorissen/pycel/list/master :alt: Code Coverage
.. |requirements| image:: https://requires.io/github/stephenrauch/pycel/requirements.svg?branch=master :target: https://requires.io/github/stephenrauch/pycel/requirements/?branch=master :alt: Requirements Status
.. |repo-size| image:: https://img.shields.io/github/repo-size/dgorissen/pycel.svg :target: https://github.com/dgorissen/pycel :alt: Repo Size
.. |code-size| image:: https://img.shields.io/github/languages/code-size/dgorissen/pycel.svg :target: https://github.com/dgorissen/pycel :alt: Code Size
.. |notebook| image:: https://mybinder.org/badge.svg :target: https://mybinder.org/v2/gh/dgorissen/pycel/master?filepath=notebooks%2Fexample.ipynb :alt: Open Notebook