# How the OfficeCLI Formula Engine Handles Dynamic Array Spilling and LAMBDA Functions

> Discover how the OfficeCLI formula engine replicates Excel 365 dynamic array spilling and LAMBDA functions. Learn about its innovative approach to modern spreadsheet calculations.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: internals
- Published: 2026-08-05

---

**The OfficeCLI formula engine mirrors Excel 365 behavior by classifying functions with `ModernFunctionQualifier`, evaluating lambda-driven spills in [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs), and writing only anchor cells with dynamic array metadata (`XLDAPR`).**

The OfficeCLI dynamic array formula engine enables command-line spreadsheet generation that fully supports Excel's modern spill behaviors and user-defined LAMBDA functions. This implementation allows developers to programmatically create workbooks with `MAP`, `BYROW`, `FILTER`, and other dynamic array formulas while ensuring Excel compatibility through proper metadata handling.

## Function Classification with ModernFunctionQualifier

The engine starts by identifying which formulas require special handling. The `ModernFunctionQualifier` class in [`Core/Formula/ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/Core/Formula/ModernFunctionQualifier.cs) maintains two static string tables that categorize functions:

- **Dynamic array functions** (lines 29-31): `SORT`, `FILTER`, `UNIQUE`, `SEQUENCE`, and others that automatically spill their results into neighboring cells
- **Lambda-driven spill functions** (lines 85-87): `MAP`, `BYROW`, `BYCOL`, `SCAN`, `MAKEARRAY`, and `REDUCE` which accept LAMBDA arguments and produce spilled arrays

When a formula is parsed, `IsDynamicArrayFormula` checks these tables to determine the evaluation path and whether spill metadata is required.

## Formula Evaluation and Dispatch

The evaluator routes recognized functions to specialized handlers. In [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs), the dispatch table maps function names to their implementations—`MAP` routes to `EvalMap` at line 311, for example.

### Lambda-Driven Spill Evaluation

The core spill logic resides in [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs) (lines 6-10 contain the implementation overview). Each lambda-driven function follows this pattern:

1. **Validate the lambda body** returns a scalar value—returning an array or range produces `#CALC!`
2. **Iterate input ranges** and invoke the stored `Lambda` object for each element
3. **Collect scalar results** into a 2D `FormulaResult` array
4. **Return the full array** where the anchor cell receives the top-left value and remaining cells form the spill region

The `InvokeLambda` helper executes lambdas with current element values or row/column indices, enforcing scalar results throughout.

## Workbook Write-Back and Metadata Handling

OfficeCLI never writes spill region cells directly. Instead, it preserves formula integrity through Excel's native recomputation:

### Dynamic Array Metadata Injection

[`ExcelHandler.DynamicArray.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.DynamicArray.cs) (lines 32-53) implements `EnsureDynamicArrayMetadata`, which injects a minimal `XLDAPR` record into the workbook's `<Metadata>` part. This XML fragment tells Excel to recompute the spill on file open.

### Anchor Cell Processing

Before writing, the engine checks `ModernFunctionQualifier.IsDynamicArrayFormula`:

- **[`ExcelHandler.Set.Cells.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Cells.cs)** (lines 332-340): Handles overwrite scenarios for existing cells
- **[`ExcelHandler.Add.Cells.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Add.Cells.cs)** (lines 625-634): Handles new cell addition

Both paths call `EnsureDynamicArrayMetadata` on anchor cells only, leaving spill regions for Excel to populate.

## LAMBDA Function Support

LAMBDA definitions parse as standard formulas. When invoked, the evaluator creates a `LambdaValue` object capturing parameters and body expressions. Lambda-driven spill functions receive these objects and execute them per-element through `InvokeLambda`.

Error handling matches Excel semantics: invalid lambda returns bubble up as `#CALC!` errors.

## Code Examples

```csharp
// MAP with LAMBDA - multiplies each element by 2
cell.CellFormula = new CellFormula { 
    Text = "MAP(A2:A10, LAMBDA(x, x*2))" 
};

// OfficeCLI processing:
// 1. Detects MAP as lambda-driven spill via ModernFunctionQualifier
// 2. Evaluates A2:A10 through the lambda, building result array
// 3. Writes anchor cell (e.g., B2) with formula text only
// 4. Attaches XLDAPR metadata for Excel to expand B2:B9

```

```csharp
// Plain dynamic array - no LAMBDA involved
cell.CellFormula = new CellFormula { 
    Text = "SORT(C2:C20)" 
};
// ModernFunctionQualifier flags as dynamic array
// EnsureDynamicArrayMetadata adds spill metadata

```

## Key Source Files

| File | Purpose |
|------|---------|
| [`Core/Formula/ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/Core/Formula/ModernFunctionQualifier.cs) | Function classification tables |
| [`Core/Formula/FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/Core/Formula/FormulaEvaluator.Functions.cs) | Function dispatch routing |
| [`Core/Formula/FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/Core/Formula/FormulaEvaluator.SpillLambda.cs) | Lambda-driven spill algorithms |
| [`Handlers/Excel/ExcelHandler.DynamicArray.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/Handlers/Excel/ExcelHandler.DynamicArray.cs) | XLDAPR metadata generation |
| [`Handlers/Excel/ExcelHandler.Set.Cells.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/Handlers/Excel/ExcelHandler.Set.Cells.cs) | Dynamic array detection in updates |
| [`Handlers/Excel/ExcelHandler.Add.Cells.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/Handlers/Excel/ExcelHandler.Add.Cells.cs) | Dynamic array detection in additions |

## Summary

- **Classification**: `ModernFunctionQualifier` identifies dynamic array and lambda-driven functions through static lookup tables
- **Evaluation**: [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs) executes MAP/BYROW/etc. by invoking lambdas per-element and building result arrays
- **Persistence**: Only anchor cells write to file with `XLDAPR` metadata; Excel recomputes spill regions on load
- **Compatibility**: This approach avoids ghost cell corruption and maintains backward compatibility

## Frequently Asked Questions

### What happens if a LAMBDA returns an array instead of a scalar?

The `InvokeLambda` validator catches non-scalar returns and propagates `#CALC!` per Excel's error semantics. This occurs in [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs) during lambda-driven spill evaluation.

### Does OfficeCLI support nested LAMBDA calls within spill functions?

Yes. The `LambdaValue` object captures the complete parameter and body structure. Nested lambdas resolve through recursive evaluation, with each level checked for scalar compliance at execution time.

### Why doesn't OfficeCLI write the full spill region to the file?

Writing ghost cells risks corruption if Excel's recalculation produces different dimensions. The `XLDAPR` metadata approach (implemented in [`ExcelHandler.DynamicArray.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.DynamicArray.cs)) delegates spill expansion to Excel, ensuring formula correctness regardless of runtime data changes.

### Which Excel versions can open OfficeCLI files with dynamic arrays?

Files with `XLDAPR` metadata require Excel 365 or Excel 2021 for full spill functionality. Older versions display the anchor cell formula and may show `#SPILL!` errors without dynamic array support.