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 { WitTable } from '@witqq/spreadsheet-react';
import type { WitTableRef } 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<WitTableRef>(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 }}>
          <WitTable
            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 { WitTable, WitTableRef } 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<WitTableRef>(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 <WitTable 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.