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

The OfficeCLI formula engine mirrors Excel 365 behavior by classifying functions with ModernFunctionQualifier, evaluating lambda-driven spills in 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 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, 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 (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 (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 (lines 332-340): Handles overwrite scenarios for existing cells
• 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

// 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

// 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	Function classification tables
Core/Formula/FormulaEvaluator.Functions.cs	Function dispatch routing
Core/Formula/FormulaEvaluator.SpillLambda.cs	Lambda-driven spill algorithms
Handlers/Excel/ExcelHandler.DynamicArray.cs	XLDAPR metadata generation
Handlers/Excel/ExcelHandler.Set.Cells.cs	Dynamic array detection in updates
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 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 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) 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}