Pycel

1.0b30 · active · verified Wed Apr 15

Pycel is a Python library designed to translate Excel spreadsheets into executable Python code, enabling them to run independently of Excel. It leverages a graph-based representation with caching and lazy evaluation for relatively fast execution. The library also supports visualizing the spreadsheet's dependency graph. It is currently in beta, version 1.0b30, with the last release in October 2021, and its release cadence has been irregular.

Warnings

breaking Pycel is currently in beta (version 1.0b30) and was last released in October 2021. The API and behavior may still evolve, and breaking changes could occur in future non-beta or major releases without strict adherence to semantic versioning typical of stable libraries.
Fix: Refer to the official GitHub repository for the latest documentation and changelog. Pin specific beta versions if stability is critical.
gotcha Pycel reads an Excel file and compiles its formulas into Python code for independent execution; it does NOT modify or write back to the original Excel `.xlsx` file. Changes made via `set_value` only affect the in-memory compiled model.
Fix: Understand that Pycel operates as a compiler and evaluator. If you need to write results back to an Excel file, you must use another library (e.g., `openpyxl`) and explicitly save the data.
gotcha VBA (Visual Basic for Applications) code embedded in Excel spreadsheets is not compiled by Pycel. Any logic implemented in VBA needs to be manually re-implemented in Python if its functionality is required in the Pycel model.
Fix: Extract and translate VBA logic into equivalent Python code. Pycel focuses solely on Excel formulas.
gotcha Pycel employs lazy evaluation. The internal graph representing the spreadsheet's dependencies is not fully built or processed until a cell is explicitly evaluated using `excel.evaluate()`. Attempting to plot or export the graph before evaluation may result in an empty or incomplete graph.
Fix: Always call `excel.evaluate()` on at least one output cell of interest to trigger the compilation and graph construction before attempting to visualize or export the graph.
gotcha The support for Excel functions in Pycel is driven by the developer's specific needs and may not be exhaustive. While common mathematical functions and operators are supported, some less frequently used or complex functions might be missing. The `OFFSET` function, in particular, can fail if a referenced cell is not already compiled.
Fix: Consult the Pycel documentation or source code for a list of supported functions. If a required function is missing, it may need to be implemented or a workaround devised. For `OFFSET`, ensure all potentially referenced cells are part of the compiled model.

Install

pip install pycel Install Pycel

Imports

ExcelCompiler
```
from pycel import ExcelCompiler
```
ExcelCompiler
wrong:
```
from pycel import ExcelCompiler
```
correct:
```
from pycel.excelcompiler import ExcelCompiler
```
While `from pycel import ExcelCompiler` works, direct import from `pycel.excelcompiler` is also commonly shown in examples.

Quickstart

This quickstart demonstrates how to create a simple Excel file, compile it using `pycel.ExcelCompiler`, and then evaluate cell values. It also shows how to dynamically update an input cell and observe the change in a dependent cell, illustrating `pycel`'s evaluation capabilities. The example cleans up the created Excel file afterwards.

import os
from openpyxl import Workbook
from pycel import ExcelCompiler

# Create a dummy Excel file for demonstration
workbook_name = 'example_spreadsheet.xlsx'
wb = Workbook()
ws = wb.active
ws.title = "Sheet1"
ws['A1'] = 10
ws['A2'] = 20
ws['A3'] = '=A1+A2'
ws['B1'] = 'Hello'
ws['B2'] = '=CONCATENATE(B1, " World")'
wb.save(workbook_name)

try:
    # Compile the Excel spreadsheet
    excel = ExcelCompiler(filename=workbook_name)

    # Evaluate a cell
    result_a3 = excel.evaluate('A3')
    print(f"Value of A3: {result_a3}")

    result_b2 = excel.evaluate('B2')
    print(f"Value of B2: {result_b2}")

    # Set a new value for A1 and re-evaluate A3
    excel.set_value('A1', 100)
    result_a3_updated = excel.evaluate('A3')
    print(f"Value of A3 after changing A1 to 100: {result_a3_updated}")

finally:
    # Clean up the dummy Excel file
    if os.path.exists(workbook_name):
        os.remove(workbook_name)

view raw JSON →