formulas

For automated workflows, you can use the formulas.cli module to run multiple calculation scenarios in a single batch.

1. Create a JSON batch file where each entry contains a name, an overwrite dictionary for inputs, and a renders list for outputs.
2. Execute the CLI using python -m formulas.cli calc <workbook_path> --batch <batch_file_path>.

Available CLI flags for batching:

• --batch: Path to the JSON batch file.
• --processes: Number of processes to use for parallel execution.
• --output-format: Set to json to receive a summary of results.
• --output-dir: Directory where results will be saved.

import json
import subprocess
import sys
import tempfile
from pathlib import Path

with tempfile.TemporaryDirectory() as tmp:
    batch = Path(tmp) / 'batch.json'
    batch.write_text(json.dumps([
        {
            'name': 'base',
            'overwrite': {
                "'[excel.xlsx]'!INPUT_A": 3,
                "'[excel.xlsx]DATA'!B3": 1,
            },
            'renders': ["'[excel.xlsx]DATA'!C2=result"],
        },
        {
            'name': 'stress',
            'overwrite': {
                "'[excel.xlsx]'!INPUT_A": 4,
                "'[excel.xlsx]DATA'!B3": 1,
            },
            'renders': ["'[excel.xlsx]DATA'!C2=result"],
        },
    ], indent=2))

    result = subprocess.run([
        sys.executable, '-m', 'formulas.cli', 'calc',
        'test/test_files/excel.xlsx',
        '--batch', str(batch),
        '--processes', '2',
        '--output-format', 'json',
        '--output-dir', tmp,
    ], capture_output=True, text=True, check=False)

    assert result.returncode == 0, result.stderr
    summary = json.loads(result.stdout)
    assert [item['name'] for item in summary] == ['base', 'stress']

You can embed formulas as a calculation engine within a lightweight web application using formulas.app.create_app. This allows you to load a workbook once and expose its calculation logic via a JSON API.

When using the API, you send a POST request to /api/calculate with a JSON body containing:

• inputs: A dictionary mapping cell addresses (e.g., '[filename.xlsx]'!CELL') to their new values.
• renders: A list of strings defining the output mapping in the format '[filename.xlsx]SHEET'!CELL=variable_name.

from formulas.app import create_app

app = create_app(files=('test/test_files/excel.xlsx',), circular=False)
client = app.test_client()
response = client.post('/api/calculate', json={
    'inputs': {
        "'[excel.xlsx]'!INPUT_A": 3,
        "'[excel.xlsx]DATA'!B3": 1,
    },
    'renders': ["'[excel.xlsx]DATA'!C2=result"],
})

assert response.status_code == 200
assert response.get_json()['outputs'] == {'result': 10.0}

You can treat an Excel workbook as a transformation step in a data pipeline. By using ExcelModel, you can compile a specific subset of inputs and outputs into a function that can be called repeatedly with structured data.

Workflow:

1. Load the workbook using formulas.ExcelModel().loads(path).finish().
2. Compile a function using .compile(inputs=[...], outputs=[...]).
3. Call the resulting function with values. The function returns a tuple where the first element contains the calculated values (accessible via .value).

import formulas

model = formulas.ExcelModel().loads('test/test_files/excel.xlsx').finish()
func = model.compile(
    inputs=["'[excel.xlsx]'!INPUT_A", "'[excel.xlsx]DATA'!B3"],
    outputs=["'[excel.xlsx]DATA'!C2"],
)
records = [
    {'id': 'row-1', 'input_a': 3, 'b3': 1},
    {'id': 'row-2', 'input_a': 4, 'b3': 1},
]
results = []

for record in records:
    result, = func(record['input_a'], record['b3'])
    results.append({
        'id': record['id'],
        'result': result.value[0, 0],
    })

assert results == [
    {'id': 'row-1', 'result': 10.0},
    {'id': 'row-2', 'result': 11.0},
]

You can transform an ExcelModel into a high-performance DispatchPipe_ object (a callable function) by defining specific input and output cell references. This is useful for treating a spreadsheet like a standard Python function.

# Define inputs and outputs by cell reference
func = xl_model.compile(
    inputs=[
        "'[excel.xlsx]'!INPUT_A",
        "'[excel.xlsx]DATA'!B3"
    ],
    outputs=[
        "'[excel.xlsx]DATA'!C2", 
        "'[excel.xlsx]DATA'!C4"
    ]
)

# Execute the function with positional arguments
# The return value is a list of results corresponding to the outputs
results = [v.value[0, 0] for v in func(3, 1)]
print(results)  # e.g., [10.0, 4.0]

func = xl_model.compile(
    inputs=["'[excel.xlsx]'!INPUT_A", "'[excel.xlsx]DATA'!B3"],
    outputs=["'[excel.xlsx]DATA'!C2", "'[excel.xlsx]DATA'!C4"]
)
results = [v.value[0, 0] for v in func(3, 1)]