Formula Engine

The FormulaPlugin adds spreadsheet-style formulas to @witqq/spreadsheet. It includes a tokenizer, parser, evaluator, and dependency graph for automatic recalculation when referenced cells change.

Live Demo

Edit values in columns A or B to see formulas in C (sum) and D (product) recalculate automatically.

Initializing…

View source code

import { useRef, useEffect, useState } from 'react';
import { Spreadsheet } from '@witqq/spreadsheet-react';
import type { SpreadsheetRef } from '@witqq/spreadsheet-react';
import type { ColumnDef } from '@witqq/spreadsheet';
import { FormulaPlugin } from '@witqq/spreadsheet-plugins';
import { DemoWrapper } from './DemoWrapper';
import { useSiteTheme } from './useSiteTheme';

const columns: ColumnDef[] = [
  { key: 'a', title: 'A', width: 100, type: 'number' },
  { key: 'b', title: 'B', width: 100, type: 'number' },
  { key: 'c', title: 'C (A+B)', width: 120 },
  { key: 'd', title: 'D (A×B)', width: 120 },
];

const data = [
  { a: 10, b: 20, c: '', d: '' },
  { a: 25, b: 15, c: '', d: '' },
  { a: 8, b: 32, c: '', d: '' },
  { a: 42, b: 7, c: '', d: '' },
  { a: 15, b: 28, c: '', d: '' },
  { a: 33, b: 11, c: '', d: '' },
  { a: 5, b: 45, c: '', d: '' },
  { a: 19, b: 23, c: '', d: '' },
];

export function FormulaDemo() {
  const { witTheme } = useSiteTheme();
  const tableRef = useRef<SpreadsheetRef>(null);
  const [active, setActive] = useState(false);

  useEffect(() => {
    const engine = tableRef.current?.getInstance();
    if (!engine) return;

    const plugin = new FormulaPlugin({ syncOnly: true });
    engine.installPlugin(plugin);

    // Set formula cells and emit cellChange so the plugin computes them
    const visibleCols = columns;
    for (let row = 0; row < data.length; row++) {
      const r = row + 1; // 1-based cell references
      const formulas: [number, string][] = [[2, `=A${r}+B${r}`], [3, `=A${r}*B${r}`]];
      for (const [col, formula] of formulas) {
        engine.setCell(row, col, formula);
        engine.getEventBus().emit('cellChange', {
          row, col, value: formula,
          column: visibleCols[col],
          oldValue: '', newValue: formula, source: 'edit' as const,
        });
      }
    }
    engine.requestRender();
    setActive(true);
  }, []);

  return (
    <DemoWrapper
      title="Live Demo"
      description="Edit values in columns A or B to see formulas in C (sum) and D (product) recalculate automatically."
      height={380}
    >
      <div style={{ display: 'flex', flexDirection: 'column', height: '100%' }}>
        <div style={{ padding: '0.5rem 0.75rem', borderBottom: '1px solid #e2e8f0', flexShrink: 0, display: 'flex', alignItems: 'center', gap: '0.5rem' }}>
          <span style={{
            display: 'inline-block',
            width: 8,
            height: 8,
            borderRadius: '50%',
            background: active ? '#22c55e' : '#94a3b8',
          }} />
          <span style={{ fontSize: '0.8rem', color: '#64748b' }}>
            {active ? 'Formula Engine Active' : 'Initializing…'}
          </span>
        </div>
        <div style={{ flex: 1 }}>
          <Spreadsheet
            theme={witTheme}
            ref={tableRef}
            columns={columns}
            data={data}
            showRowNumbers
            editable
            style={{ width: '100%', height: '100%' }}
          />
        </div>
      </div>
    </DemoWrapper>
  );
}

Installation

import { FormulaPlugin } from '@witqq/spreadsheet-plugins';

const formulaPlugin = new FormulaPlugin();
engine.installPlugin(formulaPlugin);

Options

interface FormulaPluginOptions {
  worker?: Worker;    // Web Worker for off-thread evaluation
  syncOnly?: boolean; // Force synchronous mode (default: false)
}

Formula Syntax

Formulas start with = and are entered in any cell:

=SUM(A1:A10)
=IF(B2>100, "High", "Low")
=AVERAGE(C1:C50) * 1.1

Cell References

Syntax	Description
`A1`	Relative reference (column A, row 1)
`$A$1`	Absolute reference (won’t shift on copy)
`A1:B10`	Range reference

Operators

Category	Operators
Arithmetic	`+`, `-`, `*`, `/`, `^`, `%`
Comparison	`=`, `<>`, `<`, `>`, `<=`, `>=`
Text	`&` (concatenation)

Built-in Functions

Aggregate

Function	Description
`SUM(range)`	Sum of numeric values
`AVERAGE(range)`	Arithmetic mean
`COUNT(range)`	Count of numeric values
`COUNTA(range)`	Count of non-empty cells
`MIN(range)`	Minimum value
`MAX(range)`	Maximum value

Logic

Function	Description
`IF(condition, then, else)`	Conditional evaluation
`AND(a, b, ...)`	All arguments are truthy
`OR(a, b, ...)`	Any argument is truthy
`NOT(value)`	Logical negation

Math

Function	Description
`ABS(value)`	Absolute value
`ROUND(value, digits)`	Round to N decimal places

Text

Function	Description
`CONCATENATE(a, b, ...)`	Join strings
`LEN(text)`	String length
`UPPER(text)`	Convert to uppercase
`LOWER(text)`	Convert to lowercase
`LEFT(text, n)`	First N characters
`RIGHT(text, n)`	Last N characters

Date

Function	Description
`TODAY()`	Current date

Dependency Graph

The formula engine maintains a directed dependency graph. When a cell value changes, all dependent formulas are automatically recalculated in topological order:

A1 = 10
B1 = =A1 * 2        → 20
C1 = =B1 + A1       → 30

Changing A1 to 5 triggers: B1 → 10, then C1 → 15.

Error Types

Error	Cause
`#REF!`	Invalid cell reference
`#VALUE!`	Wrong argument type
`#DIV/0!`	Division by zero
`#NAME?`	Unknown function name
`#NUM!`	Invalid numeric value
`#NULL!`	Null intersection of ranges

Usage with React

import { Spreadsheet, SpreadsheetRef } from '@witqq/spreadsheet-react';
import { FormulaPlugin } from '@witqq/spreadsheet-plugins';

interface Row {
  price: number;
  quantity: number;
  total: string;
}

const columns: ColumnDef[] = [
  { key: 'price', title: 'Price', width: 100, type: 'number' },
  { key: 'quantity', title: 'Qty', width: 80, type: 'number' },
  { key: 'total', title: 'Total', width: 120, type: 'formula' },
];

function App() {
  const ref = useRef<SpreadsheetRef>(null);

  useEffect(() => {
    ref.current?.installPlugin(new FormulaPlugin());
  }, []);

  const data: Row[] = [
    { price: 10, quantity: 5, total: '=A1*B1' },
    { price: 20, quantity: 3, total: '=A2*B2' },
  ];

  return <Spreadsheet ref={ref} columns={columns} data={data} />;
}

Formula Internals

Tokenizer

The tokenize() function converts a formula string into a token stream:

import { tokenize, TokenType, type Token } from '@witqq/spreadsheet-plugins';

const tokens: Token[] = tokenize('A1*2+SUM(B1:B10)');
// TokenType: Number, String, Boolean, CellRef, FunctionName, Operator, OpenParen, CloseParen, Comma, Colon, EOF

Parser

The parse() function builds an AST from tokens using recursive descent:

import { parse, type ASTNode } from '@witqq/spreadsheet-plugins';

const ast: ASTNode = parse(tokens);

AST Node Types: NumberNode, StringNode, BooleanNode, CellRefNode, RangeNode, BinaryOpNode, UnaryOpNode, FunctionCallNode, PercentNode, ErrorNode.

Evaluator

The evaluate() function walks the AST and computes the result:

import { evaluate, type CellValueResolver } from '@witqq/spreadsheet-plugins';

const resolver: CellValueResolver = {
  getCellValue: (row, col) => cellStore.get(row, col)?.value,
};

const result = evaluate(ast, resolver); // number | string | boolean | FormulaError

Dependency Graph Utilities

import { cellKey, parseCellKey, extractDependencies } from '@witqq/spreadsheet-plugins';

cellKey(5, 3);               // "5:3"
parseCellKey("5:3");          // { row: 5, col: 3 }
extractDependencies(ast);     // ["0:0", "1:0", "1:1", ...] — all referenced cells

The dependency graph performs topological sort for recalculation order and detects circular references.

Worker Mode

For large datasets, offload formula evaluation to a Web Worker:

const worker = new Worker(
  new URL('@witqq/spreadsheet-plugins/formula-worker', import.meta.url),
  { type: 'module' }
);

const formulaPlugin = new FormulaPlugin({ worker });
engine.installPlugin(formulaPlugin);

Worker mode uses FormulaWorkerBridge internally. It posts setCellValue, processFormula, cellChanged, and recalculateAll messages to the worker thread and receives computed results asynchronously.

API Types

TokenType

Enum of all token categories produced by the tokenizer.

enum TokenType {
  Number = 'Number',
  String = 'String',
  Boolean = 'Boolean',
  CellRef = 'CellRef',
  FunctionName = 'FunctionName',
  Operator = 'Operator',
  OpenParen = 'OpenParen',
  CloseParen = 'CloseParen',
  Comma = 'Comma',
  Colon = 'Colon',
  EOF = 'EOF',
}

Token

A single token produced by tokenize(), carrying its type, raw text, and position in the formula string.

interface Token {
  type: TokenType;
  value: string;
  position: number;
}

ASTNode

Union type of all AST nodes produced by parse().

type ASTNode =
  | NumberNode
  | StringNode
  | BooleanNode
  | CellRefNode
  | RangeNode
  | BinaryOpNode
  | UnaryOpNode
  | FunctionCallNode
  | PercentNode
  | ErrorNode;

interface NumberNode { type: 'Number'; value: number; }
interface StringNode { type: 'String'; value: string; }
interface BooleanNode { type: 'Boolean'; value: boolean; }
interface CellRefNode { type: 'CellRef'; ref: string; col: number; row: number; absCol: boolean; absRow: boolean; }
interface RangeNode { type: 'Range'; start: CellRefNode; end: CellRefNode; }
interface BinaryOpNode { type: 'BinaryOp'; op: string; left: ASTNode; right: ASTNode; }
interface UnaryOpNode { type: 'UnaryOp'; op: string; operand: ASTNode; }
interface FunctionCallNode { type: 'FunctionCall'; name: string; args: ASTNode[]; }
interface PercentNode { type: 'Percent'; operand: ASTNode; }
interface ErrorNode { type: 'Error'; error: FormulaError; }

FunctionDef

Signature for built-in function implementations used by the evaluator.

type FunctionDef = (
  args: ASTNode[],
  resolver: CellValueResolver,
  evalNode: (node: ASTNode) => FormulaResult,
) => FormulaResult;

CellValueResolver

Interface for resolving cell values during formula evaluation.

interface CellValueResolver {
  getCellValue(row: number, col: number): unknown;
}

⚡ Made with Moira