Pycel

Pycel is a small Python library that translates an Excel spreadsheet into executable Python code, which can then be run independently of Excel.

The generated code is built around a dependency graph and uses caching & lazy evaluation to ensure (relatively) fast execution. The graph can be exported and analyzed using tools like Gephi. See the example directory for a runnable illustration.

Required Python libraries:

python-dateutil, networkx, numpy, openpyxl, ruamel.yaml, and optionally: matplotlib, pydot (for graph plotting/export).

The original motivation behind pycel, along with some examples & screenshots, is described in this blog post.

Installation

pip install pycel

Or, for development, clone the repo and install it in editable mode:

git clone https://github.com/dgorissen/pycel.git
cd pycel
pip install -e .

Usage

Quick start: you can use Binder to explore pycel interactively in the browser, no install required:

A minimal example:

from pycel import ExcelCompiler

# Load & compile a workbook into a dependency graph
excel = ExcelCompiler(filename='example.xlsx')

# Evaluate a cell
print(excel.evaluate('Sheet1!D1'))

# Change an input and see the result recalculate, just like in Excel
excel.set_value('Sheet1!A1', 200)
print(excel.evaluate('Sheet1!D1'))

# Persist the compiled graph so it can be reloaded without Excel/openpyxl
excel.to_file('example.xlsx')
excel = ExcelCompiler.from_file('example.xlsx')

See example/example.py for a more complete walkthrough, including graph plotting/export, trimming the graph down to only the cells needed for a given input/output, and validating compiled calculations against the values already stored in the workbook.

The good:

All the main mathematical functions (SIN, COS, ATAN2, ...) and operators (+, /, ^, ...) are supported, as are ranges (A5:D7) and a wide range of built-in functions such as MIN, MAX, INDEX, VLOOKUP, SUMIFS, SWITCH, and LINEST (see src/pycel/excellib.py and src/pycel/lib/ for the full list).

The codebase is relatively small and fast, and should be easy to understand and extend. It has been tested on spreadsheets with 10+ sheets and more than 10,000 formulae, where calculation of the equations takes on the order of tens of milliseconds and agrees with Excel to several decimal places.

The bad:

Development has historically been driven by the particular spreadsheets contributors needed to handle, so not every Excel function is implemented. Adding support for a missing function is usually straightforward though - contributions are welcome.

Cell references are supported, so functions like OFFSET work, but the compiler can fail if it encounters a cell that has not already been pulled into the graph. Also, for obvious reasons, VBA macros are not compiled and need to be re-implemented manually on the Python side.

The ugly:

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

Security

Pycel compiles formulas from a workbook into Python source and executes them. Only compile/evaluate workbooks you trust - a maliciously crafted formula in an untrusted .xlsx file could otherwise attempt to run arbitrary code. See the changelog for past advisories (e.g. GHSA-pw67-xjhq-389w / CVE-2024-53924) and the fixes that landed for them.

Development & Testing

Pycel uses tox to run the test suite (pytest, flake8, and coverage) across supported Python versions:

pip install tox
tox

See CHANGES.rst for the full changelog, and .github/CONTRIBUTING.md before opening an issue or pull request.

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.

Thanks also to the many contributors who have kept pycel going over the years - see the project history on GitHub for the full list.