Metadata-Version: 2.3
Name: xlsx-formula-compiler
Version: 0.1.4
Summary: Translate modern Excel formulas into OOXML syntax (e.g., _xlfn. prefixes) to work perfectly with xlsxwriter.
Author: erwin314
Author-email: erwin314 <10900376+erwin314@users.noreply.github.com>
Requires-Python: >=3.12
Description-Content-Type: text/markdown

# 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

```python
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`

```python
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:

```python
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