OfficeCLI Excel Formula Functions: Complete Guide to Auto-Evaluation

OfficeCLI supports approximately 350 built-in Excel formula functions through its native FormulaEvaluator engine, which parses and evaluates formulas instantly when writing to workbooks without requiring Microsoft Excel to be installed.

OfficeCLI is an open-source command-line interface for manipulating Office documents. Its Excel handler ships with a complete formula evaluation engine that eliminates the need for external recalculation, enabling instant feedback when working with spreadsheets programmatically.

Supported Excel Formula Functions in OfficeCLI

The FormulaEvaluator class implements roughly 350 Excel functions covering the entire standard surface area. These definitions reside in src/officecli/Core/Formula/FormulaEvaluator.Functions.cs, where each function maps to a dedicated method on the evaluator class.

Lookup and Reference Functions

OfficeCLI fully supports navigation and lookup operations including VLOOKUP, XLOOKUP, INDEX, MATCH, OFFSET, and INDIRECT. The evaluator resolves cross-sheet references during the AST traversal, allowing formulas to pull data from other worksheets within the same workbook.

Mathematical and Statistical Functions

The engine covers aggregation and analysis functions such as SUM, AVERAGE, COUNT, COUNTIF, COUNTIFS, SUMPRODUCT, SUMIFS, MIN, MAX, MEDIAN, STDEV.P, STDEV.S, NORM.DIST, LINEST, and T.TEST. Array literals like ={1,2,3} are parsed and computed correctly.

Dynamic Array Functions

Modern spill-capable functions including FILTER, SORT, UNIQUE, SEQUENCE, LET, LAMBDA, and MAP are implemented with automatic compatibility handling. The evaluator automatically prepends the _xlfn. prefix to these functions, ensuring the generated file opens correctly in older Excel versions while maintaining full functionality in newer clients.

Financial and Date Functions

Financial modeling functions such as NPV, XNPV, IRR, XIRR, PRICE, YIELD, DURATION, and COUPNUM evaluate immediately upon writing. Date and time calculations use TODAY, NOW, DATE, EDATE, EOMONTH, and YEARFRAC with proper serial number conversion.

Text and Logical Functions

String manipulation includes CONCAT, TEXTJOIN, LEFT, RIGHT, MID, TRIM, UPPER, LOWER, and PROPER. Logical operations utilize IF, IFS, CHOOSE, AND, OR, and NOT with short-circuit evaluation.

How OfficeCLI Auto-Evaluation Works

The auto-evaluation pipeline operates in four distinct phases when processing formulas through the CLI.

Parsing Phase with FormulaParser

When a cell value starts with =, the text routes through FormulaParser.ParseLenient as implemented in src/officecli/Handlers/Excel/ExcelHandler.Set.Cells.cs. This parser constructs an abstract syntax tree (AST) and validates LaTeX-style syntax documented at the top of src/officecli/Core/Formula/FormulaParser.cs.

Evaluation Phase with FormulaEvaluator

The system instantiates new Core.FormulaEvaluator(sheetData, _doc.WorkbookPart) during view and get operations (see lines 34 and 140 in src/officecli/Handlers/Excel/ExcelHandler.View.cs). The evaluator walks the AST, resolves cell references (including cross-sheet dependencies), and executes the corresponding method from FormulaEvaluator.Functions.cs.

Caching Mechanism

Computed scalar values or spilled array results store directly as the cell's cachedValue. Subsequent get or query commands return this cached value immediately without recalculating, providing instant read access to formula results.

Fallback Handling

If a formula references an unsupported function or a missing sheet, the evaluator returns the sentinel #OCLI_NOTEVAL!. The original formula string remains intact in the workbook, allowing Excel to compute the correct result when the file is opened in the desktop application.

Practical Code Examples

Write a formula and retrieve the computed value instantly:

# Write a sum formula - auto-evaluates and caches the result

officecli set my.xlsx /Sheet1!A1 --type cell --prop value='=SUM(B1:B5)'

# Retrieve the computed value - no Excel process needed

officecli get my.xlsx /Sheet1!A1 --json

# → {"value":42,"format":{"cachedValue":42,"type":"Number"}}

Use dynamic array functions that spill across cells:

# FILTER spills into adjacent cells automatically

officecli set my.xlsx /Sheet1!C1 --type cell --prop value='=FILTER(A1:A10,B1:B10>5)'

# View the spilled results in HTML format

officecli view my.xlsx html

Handle financial calculations with fallback behavior:

# NPV evaluates immediately on write

officecli set my.xlsx /Sheet1!D1 --type cell --prop value='=NPV(0.08,A2:A6)'

# Missing references return the sentinel value

officecli get my.xlsx /Sheet1!D1 --json

# → {"value":"#OCLI_NOTEVAL!","format":{"type":"Error"}}

Summary

• OfficeCLI implements approximately 350 Excel functions natively in FormulaEvaluator.Functions.cs, covering lookup, math, dynamic arrays, financial, date, text, and logical categories.
• Auto-evaluation triggers immediately when writing formulas starting with =, using FormulaParser.ParseLenient to build an AST and FormulaEvaluator to compute results.
• The system caches computed values for instant retrieval via subsequent get commands, eliminating the need for Excel recalculation.
• Compatibility prefixes (_xlfn.) are automatically added to modern functions like LAMBDA and FILTER for backward compatibility.
• Fallback sentinel #OCLI_NOTEVAL! appears when formulas reference unsupported functions or missing sheets, preserving the formula for later Excel calculation.

Frequently Asked Questions

How many Excel functions does OfficeCLI support?

OfficeCLI supports approximately 350 built-in Excel functions as implemented in the FormulaEvaluator.Functions.cs source file. This includes modern dynamic array functions like FILTER and LAMBDA, financial functions like NPV and IRR, and standard statistical and lookup functions.

Does OfficeCLI require Microsoft Excel to evaluate formulas?

No. OfficeCLI contains a native FormulaEvaluator engine that parses and computes formulas entirely within the CLI process. The evaluator resolves references, executes function logic, and caches results without launching Excel or any external process.

What happens if I use a function that OfficeCLI does not support?

If a formula uses an unsupported function or references a non-existent sheet, OfficeCLI stores the sentinel value #OCLI_NOTEVAL! as the cached result while preserving the original formula text in the cell. When you open the file in Excel, the desktop application will calculate the correct result.

How does OfficeCLI handle modern Excel functions in older file formats?

The evaluator automatically prepends the _xlfn. namespace prefix to dynamic array functions like LET, LAMBDA, MAP, and FILTER. This ensures compatibility with older Excel versions while maintaining full functionality in Microsoft 365 and Excel 2021+.

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