Skip to content

Headless calculation

Edition: Commercial

The calculation service on this page is part of the Commercial edition (authenticated package index, latest published release 2.3.0). Community (MIT, public PyPI, 2.0.9) evaluates common functions in Python through Workbook.calculate() and does not include Workbook.calculator, native recalculation, or the headless calculation diagnostics described here. See Install for the difference between the editions.

Workbook.calculator is the stable, native-first calculation service. It evaluates formula dependencies without launching Excel and writes formula caches back to the workbook so a later .xlsx save persists the evaluated values.

This page covers the calculation service itself, the save-time cache policy, and what-if data tables. The verified-operation workflow that publishes a recalculated .xlsx under a hash-bound plan is covered by its own section below; see Verified Workbook Operations for the plan, approval, and delivery phases.

from wolfxl.calc import CalculationOptions

report = workbook.calculator.calculate(
    CalculationOptions(now_serial=45_000.75, random_seed=73)
)
assert report.values["Summary!B12"] == 42

CalculationReport.values is read-only. Its CalculationDiagnostics is frozen and records the selected engine, dependency/iteration facts, unsupported formula references, fallback reason, policy facts, and dynamic-array spill outcomes.

Dynamic-array diagnostics

Every declared array formula has one immutable SpillDiagnostic in report.diagnostics.spills. It contains:

  • anchor and a sheet-qualified footprint;
  • deterministic child_values as (cell_ref, value) records, excluding the anchor itself;
  • an obstruction when a formula cell blocks the array;
  • outcome: committed, obstructed, unsupported, or released;
  • release_outcome and released_cells when a prior cached footprint was removed or narrowed.

A blocked spill writes no child values. Its anchor evaluates to #SPILL!, the blocking formula remains intact, and the diagnostic identifies that cell. On a successful calculation, child caches are staged with the formula caches and are therefore persisted by workbook.save(...).

Regression and trimmed arrays

The native evaluator supports LINEST, LOGEST, GROWTH, and TRIMRANGE. LINEST and LOGEST return coefficients in reverse regressor order followed by the constant. With stats=TRUE, they return all five statistics rows; with const=FALSE, the constant is respectively 0 or 1, and its standard error is #N/A. LOGEST statistics describe the linear fit of ln(y); GROWTH returns the fitted exponential values with the shape of the prediction inputs. Redundant regressors have zero linear coefficients and standard errors.

TRIMRANGE(range, [trim_rows], [trim_cols]) independently trims each axis: 0 keeps both edges, 1 trims leading blanks, 2 trims trailing blanks, and 3 (the default) trims both. Interior blanks remain, and empty strings, zero, FALSE, and errors retain their row and column. Entirely blank inputs return #REF!. Declared array allocations persist their child caches through wolf recalc; the evaluator also supports natural dynamic spills.

The Excel-cached regression and trim-range checks cover these functions. One exclusion remains: linest_collinear_first_zero_col, an all-zero first regressor with anomalous Excel statistics. It is not an unrestricted Excel-statistics parity claim. Function contracts follow Microsoft's LINEST and TRIMRANGE documentation.

Save-time formula cache policy

Every XLSX save accepts formula_cache_policy. The same keyword is available on Workbook.save(), save_to_bytes(), and save_to_stream().

workbook.save("report.xlsx", formula_cache_policy="calculate_supported")

report = workbook.last_formula_cache_report
assert report is not None
for decision in report.decisions:
    print(decision.cell_ref, decision.action.value, decision.reason)
Policy Cache behavior
preserve Default. Keeps cached values when the workbook calculation inputs are unchanged. If they changed, stale caches are removed and recalculation-on-open is requested. The unchanged path uses generation tokens rather than scanning formula cells.
invalidate Removes formula cached values, preserves formula text, and requests recalculation-on-open.
calculate_supported Recalculates the dependency-safe supported subset. Unsupported formulas and formulas that depend on them keep their formula text, lose stale cached values, and are reported as skipped.
fail_on_unsupported Rejects the operation with FormulaCachePolicyError before writing or replacing output when any formula cannot be safely recalculated.

Call workbook.preflight_formula_cache(policy) to inspect the immutable FormulaCacheReport without changing the workbook. Reports contain deterministic per-cell decisions, categorized diagnostics, counts, and a requires_recalculation flag. When preserve takes the unchanged fast path, inventory_complete is false and formula counts are unknown because no formula inventory was needed.

What-if data tables

Use the calculation service for one- and two-variable DataTableFormula grids. The service delegates to the workbook's existing native data-table evaluator; it does not implement a second table engine.

from wolfxl.calc._service import DataTableRequest

result = workbook.calculator.calculate_data_table(
    DataTableRequest(formula_cell="Model!C1", table_cell="Model!D2")
)
# `values` is an immutable tuple-of-tuples in worksheet row/column order.
assert result.values == ((20.0, 30.0), (40.0, 60.0))

The result records the exact materialized grid, table kind (one_variable_row, one_variable_column, or two_variable), footprint, and calculation policy. The workbook convenience method continues to return its historical mutable list-of-lists result; both surfaces use the same calculation implementation and materialize the same cells.

Data-table evaluation is native-only. If the native binding or required formula graph is unavailable, calculate_data_table raises CalculationError with CALCULATION_UNSUPPORTED, feature="what_if_data_table", outcome="unsupported", and network_access="disabled". It never silently retries with the Python evaluator. The service snapshots the target result cells before delegation and restores them if the delegated evaluation fails, so a failed table cannot leave a partially materialized grid.

Goal seek

workbook.goal_seek(...) and workbook.calculator.goal_seek(...) solve one variable by bounded bisection: they search for the numeric value of a changing input cell that brings a target formula cell to a requested value within an absolute tolerance.

report = workbook.goal_seek(
    "Model!B4",        # target formula cell
    "Model!B1",        # changing numeric input cell
    1_000_000.0,       # target value
    lower_bound=0.0,
    upper_bound=500_000.0,
)
assert report.converged
print(report.changing_value, report.residual)

The bracket is explicit and required. tolerance (default 1e-7) is the absolute residual that counts as converged, and max_iterations (default 100) is capped at 1000. Bounds must be finite with lower_bound < upper_bound.

Candidates are evaluated through the same isolated native scenario machinery as what-if data tables, so the live workbook is never mutated during the search. The changing cell is written, and pending formula caches cleared, only when the search converges, including the endpoint shortcut when a bound already satisfies the target.

Requests are validated up front: the target must be a formula cell, the changing cell must be a distinct non-formula cell holding a finite number, sheet-qualified references are supported, and read-only and write-only workbooks are refused. CalculationOptions supplies cancellation, progress, volatile snapshots, and sheet selection; selected sheets must include both the target and the changing worksheet.

Non-convergence is reported, never raised or silently mutated: the immutable GoalSeekReport carries converged, the iteration count, requested and achieved values, the committed changing value and residual, the search bounds, and a tuple of GoalSeekBoundary diagnostics with stable codes unbracketed, non_finite, and max_iterations_exceeded.

This is a bounded one-variable root finder. For several variables or a minimum or maximum, use solve below. It is not a VBA Range.GoalSeek equivalence or evaluator-tracing claim.

Solver

workbook.solve(...) and workbook.calculator.solve(...) minimize, maximize, or reach a target value for a formula cell by changing up to 200 numeric input cells within bounds, like Excel Solver's "Set Objective / By Changing Variable Cells" with bound constraints.

report = workbook.solve(
    "Mix!G7",                    # objective formula cell
    goal="max",                  # "min", "max", or "target" (with target_value=)
    variables="Mix!B2:B4",       # range, comma-separated areas, or a list
    bounds=[(0, 100), (0, 100), (0, 60)],  # one pair for all, or one per cell
    method="l_bfgs_b",           # or "nelder_mead"
    max_iterations=1_000,
    max_time=100.0,              # seconds
)
if report.converged:
    print(report.objective_value, report.variable_values, report.active_bounds)
else:
    print([boundary.code for boundary in report.boundaries])

Both methods are pure Python. "l_bfgs_b" (the default) is projected limited-memory BFGS with central finite-difference gradients (one-sided at a bound), an active set for variables held at a bound, and a projected Armijo line search. "nelder_mead" is the derivative-free downhill simplex with trial points projected into the bounds and restarts on convergence. None on either side of a bound means unbounded on that side; start values outside the bounds are projected into them. tolerance (default 1e-8) is the relative objective tolerance for min/max and the absolute residual for target.

Candidates are evaluated on an isolated native snapshot through the same data-table scenario machinery as goal seek, so live cells never change during the search. Variable cells are written, and pending formula caches cleared, only when the search converges. Otherwise the workbook is untouched and the immutable SolveReport (status of "refused" or "nonconvergent") carries SolveBoundary diagnostics with stable codes: non_finite_objective (an error, text, or non-finite objective at the start values), unbounded_objective (the objective keeps improving as an unbounded variable diverges), max_iterations_exceeded, max_time_exceeded, line_search_failed, and target_unreachable. The report also records the start values and objective, the best candidate found, iteration and evaluation counts, elapsed time, and the variables whose value sits on a bound. Error results at trial points are treated as infeasible and counted in non_finite_trials.

Validation, read-only and write-only refusals, and CalculationOptions cancellation, progress (phase "solve", one checkpoint per iteration), volatile snapshots, and sheet selection behave as for goal seek. Constraints other than variable bounds, integer constraints, and the Simplex LP and Evolutionary engines are not supported.

Dependency tracing

workbook.trace_dependencies(...) and workbook.calculator.trace_dependencies(...) return an immutable DependencyTraceReport describing the formula dependency graph around one cell. They answer "what feeds this cell" and "what does this cell feed", without calculating anything.

report = workbook.trace_dependencies("Model!B12", max_depth=8)
for node in report.nodes:
    print(node.depth, node.cell_ref, node.formula, node.value_status)
for origin in report.error_origins:
    print("cached error root:", origin)

Parameters: direction is 'precedents' (default) or 'dependents'; max_depth (default 16) bounds the search depth from the target cell; and max_nodes (default 1000) bounds how many distinct cells are collected. An unqualified coordinate targets the active sheet; sheet-qualified and quoted references resolve against the workbook's sheets. Invalid direction, non-positive bounds, and unknown sheets or coordinates raise ValueError before any traversal.

The report contains:

  • nodes: one DependencyTraceNode per visited cell with the sheet-qualified reference, formula text, the stored value with a cached/literal/empty/unknown status, the traversal depth, and an is_error_origin flag;
  • edges: directed DependencyTraceEdge records in traversal direction;
  • cycles: detected reference cycles as tuples of cell references;
  • truncated: whether the depth or node bound stopped the traversal;
  • boundaries: DependencyTraceBoundary diagnostics with stable codes trace_truncated (a bound was reached) and trace_unsupported_reference (external, 3-D, table, defined-name, or oversized range references were encountered and skipped rather than guessed);
  • error_origins: when the target holds an Excel error, the trace attributes it to error-valued roots whose in-trace precedents are error-free.

Tracing is strictly read-only: it builds the dependency graph without recalculating, materializing, or mutating the workbook. It explains the graph and cached error origins only — it does not explain step-by-step evaluation, does not diff results across a recalculation, and reports cached rather than freshly computed values.

Custom worksheet functions

workbook.register_custom_function(name, callback, *, volatile=False) binds one Python callable as a worksheet function on that workbook; workbook.unregister_custom_function(name) removes it and returns the stored callback. Formulas call the function by name:

def net_amount(gross, rate):
    return gross * (1 - rate)

workbook.register_custom_function("NET_AMOUNT", net_amount)
ws["B3"] = "=NET_AMOUNT(B1, B2)"

report = workbook.calculator.calculate()

workbook.unregister_custom_function("NET_AMOUNT")

Registration is workbook-scoped and native-only:

  • Names are trimmed and uppercased, and must match [A-Z_][A-Z0-9_]*. Registration rejects collisions with builtin functions, registered VBA functions, defined names, and already-registered custom functions with ValueError; a non-string name or non-callable callback raises TypeError; a non-bool volatile raises TypeError.
  • Unregistering a name that is not registered raises KeyError.
  • Registering when the native evaluator is unavailable raises RuntimeError; calculation never silently falls back to the Python evaluator while custom functions are registered.
  • Registering and unregistering invalidate the workbook's cached evaluator, so the next calculate() or recalculate() reflects the change. Callback failures propagate as errors rather than being swallowed.
  • volatile=True marks the function for recalculation on calculations that would otherwise skip it; the flag must be a real boolean.
  • Registrations do not leak between workbooks.

Callbacks receive scalar and rectangular-range arguments and return typed values or errors, matching the native binding's callback contract. Callbacks must be pure: native spill-aware calculation evaluates formulas and may invoke a callback again while probing and converging spill candidates, so calculate() does not guarantee exactly-once callback invocation, and no invocation-count equivalence with Excel is claimed. Remaining boundary: no concurrency policy is documented or gated for registering, unregistering, or evaluating custom functions from multiple threads against one workbook.

Pinned policies and boundaries

CalculationDiagnostics.policy and DataTableDiagnostics.policy make the following behavior explicit:

Area Policy
Network and external references Network access is always disabled. External workbook values are only supplied through explicitly registered native caches; calculation never refreshes a linked workbook or service.
Volatile formulas NOW, TODAY, RAND, and related functions use one generation snapshot. Supplying now_serial or timezone-naive now_datetime, plus random_seed, makes it reproducible. Without an explicit time, the service records system_clock_snapshot.
Date systems Explicit serial snapshots use the Excel 1900 system at the API boundary. The evaluator translates them for a workbook declared with the Excel 1904 date system; diagnostics report the workbook's active date system.
Excel errors Formula errors are returned as Excel error values (for example #DIV/0! and #SPILL!) and are cacheable/persisted where supported. They are not replaced with Python exceptions.
Fallback Python fallback is allowed only for a graph it supports. External native caches, VBA UDFs, and registered custom worksheet functions require native calculation. Unsupported native-only semantics are surfaced as CalculationError; they are never silently evaluated by a different engine.

Unsupported boundaries remain visible rather than approximated: no external workbook refresh, no network calculation services, no Solver/scenario-manager execution, no cube/external-service evaluation, no Python fallback for what-if data tables, no step-by-step evaluation replay or changed-result diffing in dependency traces, no exactly-once custom-function callback invocation guarantee, and no documented concurrency policy for custom worksheet functions.

Verified operation recalculation

wolfxl.operations can carry one calculation into a published artifact instead of asking the reader to recalculate. It is opt-in per request, with OperationRequest.recalculate defaulting to false; the flag must be a real boolean and is serialized only when it is true, so a default request keeps the identity its plan, approval, and attestation were bound to.

from wolfxl.operations import (
    apply,
    approve,
    attest,
    commit,
    inspect_workbook,
    plan,
    verify,
)

request["recalculate"] = True
inspected = inspect_workbook(request["source"]["path"], request_id=request["request_id"])
planned = plan(request)  # expected_changes["calculation"] declares the authority
approval = approve(
    request, planned, approved_by="host:reviewer", reason="Reviewed bounded edit"
)
applied = apply(request, planned, approval=approval)
verified = verify(request, planned, applied)
assert verified.calculation_document() == {
    "status": "computed",
    "caches_updated": True,
    "values": {"Summary!B1": 15.0, "Model!B1": 10.0},
    "value_source": "calculated",
}
attested = attest(
    request, inspected, planned, applied, verified, approval=approval
)
committed = commit(
    request, inspected, planned, applied, verified, attested, approval=approval
)

The opt-in is a proof obligation, not a convenience flag:

  • plan() publishes the cache authority it derived from the source package (mode, worksheet parts, bounding ranges, cell_count, cells_sha256). The request cannot widen it later.
  • apply() recalculates the staged post-edit workbook and rewrites only those cells' cache payloads through the package-preserving patcher. A partial calculation leaves nothing behind: an unsupported, cyclic, non-finite, or incomplete result refuses and removes the managed temporary artifact.
  • verify() re-derives the authority from the source bytes, then reads the cache payloads back from the output package and compares them with the calculated values. caches_updated: true therefore means the values are in the file, not merely in a report object.
  • attest() and commit() refuse to publish unless that evidence exists, matches the plan authority, and was actually requested.

Visible limitations

  • Standard per-cell formula caches only. Array, data-table, and spilled formula footprints are outside the authority and refuse at plan time, because a per-cell writeback cannot prove their results.
  • One writeback covers at most 50,000 formula cells.
  • Calculated results must be finite and representable as an OOXML cache payload: numbers, booleans, text, and the cacheable error values. A result that cannot be persisted is a refusal, never a silently dropped cache.
  • Iteration and cycles are not proven from caches. A cyclic formula inventory refuses.
  • Nothing else on a cache-bearing worksheet may change. Verification compares the rest of that worksheet against the source and fails on any unrelated content difference.
  • The opt-in changes nothing by default: without it, no calculation is performed or claimed, calculated_values remains unassessed, and the output keeps the historical {"status": ...} calculation document.
  • Host delegation is not proof. The CLI's --approve-source-formula-risk approves an exact plan; when a request opts in, its delegated reason states that the requested native cache writeback must pass independent verification before commit, and without the opt-in it states that formula cache freshness remains unassessed. The writeback evidence, not the approval, is what makes the persisted values provable.
  • Qualification boundary: adopted values are what this library's own engine computed, persisted, and read back. That is not an Excel or LibreOffice equivalence claim; independently authored corpus qualification and customer acceptance remain separate lanes.