xlsx-formula-compiler 0.1.4

Translate modern Excel formulas into OOXML syntax (e.g., _xlfn. prefixes) to work perfectly with xlsxwriter.

xlsx_formula_compiler

Translate modern Excel formulas into OOXML-compliant syntax (e.g., adding _xlfn. prefixes) for seamless use with xlsxwriter.

Installation

uv add xlsx_formula_compiler
# or
pip install xlsx_formula_compiler

Why do I need this?

Modern Excel features like Dynamic Arrays (the # operator) or functions like XLOOKUP require specific XML prefixes (_xlfn., _xlws.) to be recognized correctly by Excel when generated via libraries like xlsxwriter.

This library automates that translation so you can write formulas as they appear in the Excel formula bar.

Quick Start

from xlsx_formula_compiler import compile_formula

# Convert a dynamic array formula
raw_formula = "=A1#"
compiled = compile_formula(raw_formula)
print(compiled) 
# Output: _xlfn.ANCHORARRAY(A1)

# Use with xlsxwriter
import xlsxwriter
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write_dynamic_array_formula('B1', compiled)
workbook.close()

Complex Example

The real power of xlsx_formula_compiler shows up when you combine multiple modern Excel features in a single formula. Here is a realistic report formula that uses:

• LET — named variables compiled with the _xlpm. prefix
• Spill references (#) — compiled to _xlfn.ANCHORARRAY
• FILTER / SORT / UNIQUE — worksheet/future functions (compiled with _xlfn._xlws. or _xlfn.)
• XLOOKUP — future function compiled with _xlfn.
• Implicit intersection (@) — compiled to _xlfn.SINGLE

from xlsx_formula_compiler import compile_formula

formula = """=@LET(
    raw,        Sales[Amount],
    categories, UNIQUE(Sales[Category]),
    sorted_cat, SORT(categories),

    lookup_result, XLOOKUP(@sorted_cat,
        Products[Category],
        Products[Description],
        "N/A"
    ),

    filtered, FILTER(
        HSTACK(sorted_cat, lookup_result),
        sorted_cat <> ""
    ),

    VSTACK({"Category","Description"}, filtered)
)"""

compiled = compile_formula(formula)
print(compiled)

Output:

_xlfn.SINGLE(_xlfn.LET(
    _xlpm.raw,        Sales[Amount],
    _xlpm.categories, _xlfn.UNIQUE(Sales[Category]),
    _xlpm.sorted_cat, _xlfn._xlws.SORT(_xlpm.categories),

    _xlpm.lookup_result, _xlfn.XLOOKUP(_xlfn.SINGLE(_xlpm.sorted_cat),
        Products[Category],
        Products[Description],
        "N/A"
    ),

    _xlpm.filtered, _xlfn._xlws.FILTER(
        _xlfn.HSTACK(_xlpm.sorted_cat, _xlpm.lookup_result),
        _xlpm.sorted_cat <> ""
    ),

    _xlfn.VSTACK({"Category","Description"}, _xlpm.filtered)
))

You can then pass compiled directly to xlsxwriter:

import xlsxwriter

workbook = xlsxwriter.Workbook('report.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write_dynamic_array_formula('A1', compiled)
workbook.close()

License

Apache License 2.0