Formula Engine

Formula Engine

modern-xlsx includes a complete formula engine that can tokenize, parse, evaluate, and manipulate Excel formulas — all in pure TypeScript, no Excel required.

Quick Start

import { initWasm, evaluateFormula, createDefaultFunctions } from 'modern-xlsx';

await initWasm();

const functions = createDefaultFunctions(); // 54 built-in functions

// Evaluate a simple formula
const result = evaluateFormula('SUM(1, 2, 3) * 2', {
  currentSheet: 'Sheet1',
  functions,
  getCell: () => null,
});
console.log(result); // 12

Evaluating Formulas with Cell Data

import { Workbook, evaluateFormula, createDefaultFunctions } from 'modern-xlsx';

const wb = new Workbook();
const ws = wb.addSheet('Sheet1');
ws.cell('A1').value = 10;
ws.cell('A2').value = 20;
ws.cell('A3').value = 30;

const functions = createDefaultFunctions();
const result = evaluateFormula('SUM(A1:A3)', {
  currentSheet: 'Sheet1',
  functions,
  getCell: (sheet, col, row) => {
    const s = wb.getSheet(sheet);
    if (!s) return null;
    const letter = String.fromCharCode(65 + col);
    return s.cell(`${letter}${row}`).value;
  },
});
console.log(result); // 60

Built-in Functions (54)

String & Logical (20)

Function	Description	Example
IF(test, then, else)	Conditional	IF(A1>10,"big","small")
AND(val1, ...)	Logical AND	AND(A1>0, B1>0)
OR(val1, ...)	Logical OR	OR(A1>0, B1>0)
NOT(val)	Logical negation	NOT(A1>10)
IFERROR(value, fallback)	Error handler	IFERROR(A1/B1, 0)
CONCATENATE(s1, ...)	Join strings	CONCATENATE("Hello", " ", "World")
LEFT(text, n?)	Left substring	LEFT("Hello", 3) → "Hel"
RIGHT(text, n?)	Right substring	RIGHT("Hello", 3) → "llo"
MID(text, start, n)	Substring	MID("Hello", 2, 3) → "ell"
LEN(text)	String length	LEN("Hello") → 5
TRIM(text)	Remove extra spaces	TRIM(" hi ") → "hi"
UPPER(text)	Uppercase	UPPER("hello") → "HELLO"
LOWER(text)	Lowercase	LOWER("HELLO") → "hello"
TEXT(value, format)	Format number	TEXT(1234.5, "0.00") → "1234.50"
VALUE(text)	Parse number	VALUE("123") → 123
EXACT(a, b)	Case-sensitive compare	EXACT("abc", "ABC") → false
SUBSTITUTE(text, old, new)	Replace text	SUBSTITUTE("abc", "b", "x") → "axc"
REPT(text, n)	Repeat string	REPT("ab", 3) → "ababab"
FIND(find, within, start?)	Case-sensitive find	FIND("lo", "Hello") → 4
SEARCH(find, within, start?)	Case-insensitive find	SEARCH("LO", "Hello") → 4

Math & Statistical (25)

Function	Description
SUM(vals...)	Sum of values/ranges
AVERAGE(vals...)	Arithmetic mean
MIN(vals...)	Minimum value
MAX(vals...)	Maximum value
COUNT(vals...)	Count numeric values
COUNTA(vals...)	Count non-empty values
COUNTBLANK(range)	Count empty cells
ROUND(n, digits)	Round to N decimals
ROUNDUP(n, digits)	Round away from zero
ROUNDDOWN(n, digits)	Round toward zero
ABS(n)	Absolute value
SQRT(n)	Square root
MOD(n, divisor)	Modulus
INT(n)	Floor to integer
CEILING(n, significance)	Round up to multiple
FLOOR(n, significance)	Round down to multiple
POWER(base, exp)	Exponentiation
LOG(n, base?)	Logarithm (default base 10)
LN(n)	Natural logarithm
PI()	Return π
RAND()	Random number 0–1
SUMIF(range, criteria, sum_range?)	Conditional sum
COUNTIF(range, criteria)	Conditional count
AVERAGEIF(range, criteria, avg_range?)	Conditional average
SUMPRODUCT(array1, array2, ...)	Sum of element-wise products

Lookup (9)

Function	Description
VLOOKUP(value, range, col, approx?)	Vertical lookup
HLOOKUP(value, range, row, approx?)	Horizontal lookup
INDEX(range, row, col?)	Value at position
MATCH(value, range, type?)	Find position
CHOOSE(index, val1, val2, ...)	Choose from list
ROW(ref?)	Row number
COLUMN(ref?)	Column number
ROWS(range)	Count rows
COLUMNS(range)	Count columns

Parsing & Serialization

import { parseFormula, serializeFormula } from 'modern-xlsx';

const { ast, errors } = parseFormula('SUM(A1:B2) + IF(C1>0, C1, 0)');
console.log(ast);
// { type: 'binary_op', op: '+', left: { type: 'function', ... }, right: { type: 'function', ... } }

// Round-trip back to string
const formula = serializeFormula(ast);
console.log(formula); // 'SUM(A1:B2)+IF(C1>0,C1,0)'

Reference Rewriting

Adjust cell references when rows or columns are inserted/deleted:

import { rewriteFormula } from 'modern-xlsx';

// Insert 2 rows before row 3
const updated = rewriteFormula('SUM(A1:A5)', {
  type: 'insert_rows',
  row: 3,
  count: 2,
});
console.log(updated); // 'SUM(A1:A7)'

// Delete column B
const updated2 = rewriteFormula('A1+B1+C1', {
  type: 'delete_cols',
  col: 2,
  count: 1,
});
console.log(updated2); // 'A1+#REF!+B1'

Shared Formula Expansion

Derive child formulas from a master formula:

import { expandSharedFormula } from 'modern-xlsx';

// Master at B1 has formula A1*2
// Expand to B3 (row offset +2)
const child = expandSharedFormula('A1*2', 'B1', 'B3');
console.log(child); // 'A3*2'

Custom Functions

Register your own functions:

import { evaluateFormula, createDefaultFunctions } from 'modern-xlsx';

const functions = createDefaultFunctions();

// Add a custom DOUBLE function
functions.set('DOUBLE', (args, ctx, evaluate) => {
  const val = evaluate(args[0], ctx);
  if (typeof val === 'number') return val * 2;
  return '#VALUE!';
});

const result = evaluateFormula('DOUBLE(21)', {
  currentSheet: 'Sheet1',
  functions,
  getCell: () => null,
});
console.log(result); // 42

EvalContext Interface

interface EvalContext {
  /** Get cell value by sheet name, 0-based column, 1-based row. */
  getCell(sheet: string, col: number, row: number): CellValue;
  /** Current sheet name (used when formula has no sheet prefix). */
  currentSheet: string;
  /** Optional function registry. */
  functions?: Map<string, FormulaFunction>;
}

Error Handling

The evaluator follows Excel error semantics:

Error	Cause
#DIV/0!	Division by zero
#VALUE!	Type coercion failure
#REF!	Invalid cell reference
#NAME?	Unknown function or named range
#NUM!	Invalid numeric operation (e.g., SQRT(-1))
#N/A	Lookup value not found

Errors propagate through expressions: 1 + #REF! → #REF!.