@worksheet-js/formula

Industrial-grade spreadsheet formula engine for modern web applications.

@worksheet-js/formula is a high-performance, standalone AST-based formula engine that parses and evaluates complex spreadsheet formulas with full compatibility with Microsoft Excel and Google Sheets function semantics.

Use it embedded within @worksheet-js/core (via CalcWorkerHost) or as a standalone formula evaluation library in any JavaScript/TypeScript application — with zero external dependencies.

Table of Contents

• Key Features
• Installation
• License Initialization
• Quick Start
• Public API
  • FormulaEngine
  • CellDataProvider Interface
  • Custom Functions
  • Parser Utilities
• Function Library
• Dependency Tracking
• Error Handling
• License

Key Features

• AST-Based Evaluation — Full expression parser with correct operator precedence, array formulas, and nested function calls.
• 300+ Functions — Math, Statistical, Financial, Logical, Text, Date/Time, Lookup, and Engineering categories.
• Intelligent Dependency Tracking — DAG (Directed Acyclic Graph) tracks cell relationships; only dependents of changed cells are re-evaluated.
• Bulk Invalidation — invalidateBatch() minimizes re-evaluation overhead during bulk updates.
• Custom Function Registration — Extend the engine with domain-specific business logic.
• Cross-Sheet References — Sheet2!A1, named ranges, and scoped references supported.
• Zero Dependencies — No runtime dependencies; fully self-contained.
• Type-Safe — Strict TypeScript with exhaustive type definitions.

Installation

npm install @worksheet-js/formula
# or
pnpm add @worksheet-js/formula
# or
yarn add @worksheet-js/formula

No peer dependencies required.

License Initialization

@worksheet-js/formula is proprietary software. Initialize your license key before calling new FormulaEngine().

import { initializeLicense } from '@worksheet-js/formula';

// Call once at application startup
initializeLicense('YOUR-LICENSE-KEY');

To obtain a license key, contact [email protected].

Quick Start

import { FormulaEngine, initializeLicense } from '@worksheet-js/formula';

// 1. Initialize license
initializeLicense('YOUR-LICENSE-KEY');

// 2. Define a data provider that bridges the engine to your cell storage
const grid: (string | number)[][] = [
  [10, 20, 30], // Row 0 (A1, B1, C1)
  [40, 50, 60], // Row 1 (A2, B2, C2)
  ['=SUM(A1:B2)', '', ''],
];

const provider = {
  getValue: (x: number, y: number) => grid[y]?.[x] ?? '',
  getRange: (x1: number, y1: number, x2: number, y2: number) => {
    const rows = [];
    for (let row = y1; row <= y2; row++) {
      const cols = [];
      for (let col = x1; col <= x2; col++) {
        cols.push(grid[row]?.[col] ?? '');
      }
      rows.push(cols);
    }
    return rows;
  },
};

// 3. Create the engine
const engine = new FormulaEngine(provider);

// 4. Evaluate a formula cell — (x=0, y=2) = A3
const result = engine.evaluate(0, 2);
console.log(result); // 120  (10+20+40+50)

// 5. Update a cell and invalidate
grid[0][0] = 100;
engine.invalidate(0, 0);

const updated = engine.evaluate(0, 2);
console.log(updated); // 210

Public API

FormulaEngine

class FormulaEngine {
  /**
   * Create a new engine instance bound to a cell data provider.
   */
  constructor(provider: CellDataProvider);

  /**
   * Evaluate the formula or value at (x, y).
   * Results are memoized until the cell is invalidated.
   * @returns Computed value — string, number, boolean, or FormulaError
   */
  evaluate(x: number, y: number): unknown;

  /**
   * Mark cell (x, y) as changed.
   * All direct and transitive dependents are invalidated
   * and will re-evaluate on the next evaluate() call.
   */
  invalidate(x: number, y: number): void;

  /**
   * Optimized bulk invalidation.
   * Prefer over calling invalidate() in a loop for batch updates.
   */
  invalidateBatch(coords: Array<{ x: number; y: number }>): void;

  /**
   * Clear all caches and the entire dependency graph.
   * Use when replacing the complete sheet data.
   */
  invalidateAll(): void;

  /**
   * Return all cells that transitively depend on (x, y).
   * Useful for building custom change propagation logic.
   */
  getDependents(x: number, y: number): Array<[number, number]>;

  /**
   * Remove a cell from the cache and dependency graph.
   * Use when a cell is permanently deleted.
   */
  remove(x: number, y: number): void;

  /**
   * Register a custom function callable from any formula.
   * @param name - Case-insensitive function name (e.g. 'MYFUNCTION')
   * @param impl - Implementation receiving an array of evaluated arguments
   *
   * @example
   * engine.registerFunction('DOUBLE', (args) => Number(args[0]) * 2);
   * // Now usable in cells as: =DOUBLE(A1)
   */
  registerFunction(name: string, impl: (args: unknown[]) => unknown): void;
}

CellDataProvider Interface

Implement this interface to connect the formula engine to your data storage layer:

interface CellDataProvider {
  /**
   * Return the raw value at (x, y).
   * If the string starts with '=', the engine will parse and evaluate it.
   */
  getValue(x: number, y: number): string | number | boolean | null;

  /**
   * Return a 2D array of values for a rectangular range.
   * Optional — the engine falls back to repeated getValue() calls if omitted.
   * Strongly recommended for range functions (SUM, AVERAGE, VLOOKUP, etc.).
   */
  getRange?(x1: number, y1: number, x2: number, y2: number): (string | number | boolean | null)[][];
}

Custom Functions

Register custom functions to extend the engine with domain-specific logic:

// Simple transformation
engine.registerFunction('TRIPLE', (args) => Number(args[0]) * 3);

// Conditional business logic
engine.registerFunction('TAX_RATE', (args) => {
  const income = Number(args[0]);
  if (income < 50_000) return 0.15;
  if (income < 200_000) return 0.25;
  return 0.37;
});

// Multi-argument function
engine.registerFunction('WEIGHTED_AVG', (args) => {
  const values = args[0] as number[];
  const weights = args[1] as number[];
  const total = weights.reduce((a, b) => a + b, 0);
  return values.reduce((sum, v, i) => sum + v * weights[i], 0) / total;
});

// Usage in cells:
// =TRIPLE(A1)
// =TAX_RATE(B5)
// =WEIGHTED_AVG(A1:A5, B1:B5)

Parser Utilities

The parser is also available independently for tooling, syntax highlighting, or validation:

import { parse, FormulaError, FUNCTION_NAMES, getFunctionsByCategory } from '@worksheet-js/formula';

// Parse an expression into an AST node tree
const ast = parse('=SUM(A1:B10) * 1.1');
console.log(ast); // AstNode — full parse tree

// Check if a result is a formula error
if (value instanceof FormulaError) {
  console.log(value.code); // '#VALUE!', '#REF!', etc.
}

// All built-in function names as a string array
console.log(FUNCTION_NAMES); // ['ABS', 'AVERAGE', 'CONCATENATE', ...]

// Functions organized by category
const financialFns = getFunctionsByCategory('financial');
const mathFns = getFunctionsByCategory('math');

Function Library

300+ functions across 8 categories — compatible with Microsoft Excel and Google Sheets:

| Category | Example Functions | | :--------------------- | :----------------------------------------------------------------------------------------------------------------------- | | Math & Trig | SUM, SUMIF, SUMIFS, ROUND, CEILING, FLOOR, MOD, POWER, SQRT, ABS, LOG, EXP | | Statistical | AVERAGE, AVERAGEIF, COUNT, COUNTA, COUNTIF, COUNTIFS, MAX, MIN, MEDIAN, STDEV, VAR | | Logical | IF, IFS, AND, OR, NOT, XOR, IFERROR, IFNA, SWITCH | | Text | CONCATENATE, CONCAT, LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, SUBSTITUTE, FIND, SEARCH, TEXT | | Lookup & Reference | VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET, INDIRECT, CHOOSE, XLOOKUP | | Date & Time | TODAY, NOW, DATE, DATEVALUE, DATEDIF, YEAR, MONTH, DAY, WEEKDAY, NETWORKDAYS | | Financial | PMT, NPV, IRR, FV, PV, RATE, NPER, XNPV, XIRR | | Engineering | BIN2DEC, DEC2BIN, HEX2DEC, DEC2HEX, BITAND, BITOR, BITXOR |

Dependency Tracking

The engine builds a Directed Acyclic Graph (DAG) of cell dependencies at parse time:

A1 (10)  ──┐
            ├──▶  C1 = =SUM(A1:B1)  ──▶  D1 = =C1 * 1.1
B1 (20)  ──┘

When A1 changes:

1. Call engine.invalidate(0, 0).
2. The engine traverses the DAG and marks C1 and D1 as stale.
3. On the next evaluate() call for D1, the chain re-evaluates bottom-up.
4. Cells not in the dependency chain are never touched.

Result: Sub-millisecond recalculation even in large workbooks with hundreds of formula cells.

For bulk changes (e.g. paste, import), use invalidateBatch() to process all invalidations in a single DAG traversal:

engine.invalidateBatch([
  { x: 0, y: 0 }, // A1
  { x: 1, y: 0 }, // B1
  { x: 2, y: 0 }, // C1
]);

Error Handling

The engine returns Excel-compatible error objects when evaluation fails:

import { FormulaError } from '@worksheet-js/formula';

const result = engine.evaluate(0, 0);

if (result instanceof FormulaError) {
  switch (result.code) {
    case '#DIV/0!':
      console.error('Division by zero');
      break;
    case '#VALUE!':
      console.error('Wrong value type');
      break;
    case '#REF!':
      console.error('Invalid cell reference');
      break;
    case '#NAME?':
      console.error('Unknown function or name');
      break;
    case '#N/A':
      console.error('Value not available');
      break;
    case '#NUM!':
      console.error('Invalid numeric value');
      break;
    case '#NULL!':
      console.error('Incorrect range operator');
      break;
  }
}

| Error Code | Meaning | | :--------- | :------------------------------------------- | | #DIV/0! | Division by zero | | #VALUE! | Argument is of the wrong type | | #REF! | Formula references a deleted or invalid cell | | #NAME? | Unrecognized function name or named range | | #N/A | Lookup value not found | | #NUM! | Formula produces an invalid numeric result | | #NULL! | Two ranges do not intersect |

License

Copyright © 2024-present Worksheet Systems. All rights reserved.

This software is proprietary. Usage is subject to the terms of the End-User License Agreement (EULA).

For licensing and technical support: [email protected]