Formula grid: real formulas, live

Every cell in columns E–J (and every odd column beyond) holds a real formula — =A1+B1, =SUM(A1:D1), =IF(A1>0,B1,C1), a running sum — evaluated by fast-formula-parser (280 Excel functions), with the dependency graph discovered by Vue's tracking rather than parsed up front. The parser loads on demand when you click; nothing runs on page load.

What to notice

• Set A1 to 5000 and watch E1, G1, H1, I1 and the J column cascade.
• Conditional dependencies are live. Select I1 and flip A1's sign — the tracked dependency set shifts between branches, because the graph is whatever the formula actually read.
• An unrendered cell allocates nothing. The virtualized window mounts a few hundred DOM cells out of up to a million in the model; a formula cell's ref and computed materialize only when observed.

The source

Sheet.ts

/**
 * The Sheet — a PLAIN (non-reactive) container that owns the 100,000
 * `FormulaCell` instances and the ONE shared formula parser.
 *
 * The reactivity lives on the cells (each has its own ref + computed); the
 * Sheet is just the structure + the integration seam:
 *
 *   - `grid[r0][c0]` holds the cells, giving an O(1) 1-based `cellAt(row,col)`.
 *   - a SINGLE `FormulaParser` (not one per cell — that would reintroduce the
 *     per-instance allocation this whole design fights) whose `onCell`/`onRange`
 *     hooks read cells' `value.value`. Because those reads happen while a cell's
 *     `value` computed is evaluating, VUE tracks them as real dependencies —
 *     the dependency graph is discovered, never hand-built.
 */
import FormulaParser from 'fast-formula-parser';
import { FormulaCell } from './FormulaCell';
import {
  COLS,
  type CellValue,
  initialFormula,
  stripFormula,
} from './formula-logic';

const { FormulaError } = FormulaParser as unknown as {
  FormulaError: new (error: string, details?: unknown) => CellValue;
};

interface RangeRef {
  from: { row: number; col: number };
  to: { row: number; col: number };
}

export class Sheet {
  readonly rows: number;
  readonly cols: number;
  /** [row0][col0] — the cells, doubling as the O(1) cellAt index. */
  readonly grid: FormulaCell.Instance[][];

  /** ONE parser for the entire sheet, shared by all cells. */
  private readonly parser: FormulaParser;
  /** Recursion guard: a cell re-entered mid-evaluation is a cycle → #REF!. */
  private readonly evaluating = new Set<object>();
  /** When non-null, cellValueAt records every (row,col) read (dep tracing). */
  private tracer: Array<[number, number]> | null = null;

  constructor(rows: number, cols: number = COLS) {
    this.rows = rows;
    this.cols = cols;

    const grid: FormulaCell.Instance[][] = new Array(rows);
    for (let r = 0; r < rows; r++) {
      const rowArr: FormulaCell.Instance[] = new Array(cols);
      for (let c = 0; c < cols; c++) {
        rowArr[c] = new FormulaCell.Class(
          this,
          r + 1,
          c + 1,
          initialFormula(r, c),
        );
      }
      grid[r] = rowArr;
    }
    this.grid = grid;

    this.parser = new FormulaParser({
      onCell: (ref) => this.cellValueAt(ref.row, ref.col),
      onRange: (ref) => this.rangeValues(ref as RangeRef),
    });
  }

  /** O(1) 1-based lookup. Out of bounds → undefined. */
  cellAt(row: number, col: number): FormulaCell.Instance | undefined {
    if (row < 1 || row > this.rows || col < 1 || col > this.cols)
      return undefined;
    return this.grid[row - 1][col - 1];
  }

  /**
   * onCell seam — read the referenced cell's REACTIVE value. The read is
   * tracked by whatever computed is currently evaluating, so editing that cell
   * later invalidates the dependent formula automatically.
   */
  private cellValueAt(row: number, col: number): CellValue {
    if (this.tracer) this.tracer.push([row, col]);
    const cell = this.cellAt(row, col);
    return cell ? cell.value.value : null; // out of bounds / blank → 0 in math
  }

  /** onRange seam — a 2D array of the referenced cells' values. */
  private rangeValues(ref: RangeRef): CellValue[][] {
    const { from, to } = ref;
    const out: CellValue[][] = [];
    for (let r = from.row; r <= to.row; r++) {
      const rowArr: CellValue[] = [];
      for (let c = from.col; c <= to.col; c++)
        rowArr.push(this.cellValueAt(r, c));
      out.push(rowArr);
    }
    return out;
  }

  /**
   * Parse + evaluate a formula body (no leading '='), through the SHARED parser.
   * Called from a cell's `value` computed, so every onCell/onRange read inside
   * becomes a tracked dependency of that computed. Guarded against cycles.
   */
  evaluate(cell: { row: number; col: number }, body: string): CellValue {
    if (body.trim().length === 0) return null; // "=" alone → blank
    if (this.evaluating.has(cell)) return new FormulaError('#REF!'); // circular ref
    this.evaluating.add(cell);
    try {
      return this.parser.parse(body, {
        row: cell.row,
        col: cell.col,
        sheet: 'Sheet1',
      }) as CellValue;
    } catch (e) {
      return e instanceof (FormulaError as unknown as Function)
        ? (e as CellValue)
        : new FormulaError('#ERROR!');
    } finally {
      this.evaluating.delete(cell);
    }
  }

  /**
   * Diagnostic: which cells does (row,col)'s formula CURRENTLY read? Re-parses
   * once outside Vue tracking with the read-tap on. It walks the exact same
   * onCell/onRange path Vue tracks, so the returned set IS the live Vue
   * dependency set for that cell — which is why it visibly SHIFTS when an
   * IF()'s condition crosses a branch boundary.
   */
  traceDeps(row: number, col: number): Array<[number, number]> {
    const cell = this.cellAt(row, col);
    if (!cell || !cell.isFormula) return [];
    const body = stripFormula(cell.raw.value);
    if (body.trim().length === 0) return [];

    const prev = this.tracer;
    this.tracer = [];
    try {
      this.parser.parse(body, { row, col, sheet: 'Sheet1' });
    } catch {
      /* keep whatever reads it made before erroring */
    }
    const recorded = this.tracer;
    this.tracer = prev;

    const seen = new Set<string>();
    const deps: Array<[number, number]> = [];
    for (const [r, c] of recorded) {
      const k = r + ',' + c;
      if (!seen.has(k)) {
        seen.add(k);
        deps.push([r, c]);
      }
    }
    return deps;
  }

  /** Iterate every cell — the measurement harness uses this to force full
   *  materialization of all cells for the worst-case heap figure. */
  forEach(fn: (cell: FormulaCell.Instance) => void) {
    for (let r = 0; r < this.rows; r++) {
      const rowArr = this.grid[r];
      for (let c = 0; c < this.cols; c++) fn(rowArr[c]);
    }
  }
}

FormulaCell.ts

/**
 * A single spreadsheet cell, authored per the ivue operating manual.
 *
 *   - raw       → ref-getter: the LITERAL text the user typed
 *                 ('42' | 'hello' | '=A1+B2'). Mutable state, `.value` to r/w.
 *   - value     → the ONE computed(): parse + evaluate the formula (or resolve
 *                 the literal). Parsing+evaluating is real work, correctly
 *                 memoized. This is the whole integration seam: the parser's
 *                 onCell/onRange hooks read OTHER cells' `value.value` from
 *                 inside THIS computed's effect, so Vue discovers the formula's
 *                 dependencies automatically — no hand-built dependency graph.
 *   - display / isFormula / cssClass → PLAIN getters (0 bytes/instance,
 *                 reactive via leaf tracking), exactly the `IvueCell` shape.
 *
 * Instances are plain objects. None of the getters run at construction, so the
 * ref and computed MATERIALIZE LAZILY — a cell that is never rendered never
 * allocates a Ref or a Computed. That laziness is the whole point.
 */
import { computed, ref, type ComputedRef } from 'vue';
import { Reactive } from '../../ivue';
import type { Sheet } from './Sheet';
import {
  type CellValue,
  cssOf,
  displayOf,
  evalLiteral,
  isFormulaText,
  stripFormula,
} from './formula-logic';

class $FormulaCell {
  // CONSTANTS / CONFIG — plain fields, set once, never mutated.
  // `sheet` is an injected dependency (the parser + O(1) cellAt live on it),
  // `row`/`col` are the 1-based position used by the parser and ROW()/COLUMN().
  readonly sheet: Sheet;
  readonly row: number;
  readonly col: number;
  private readonly iv: string;

  constructor(sheet: Sheet, row: number, col: number, initial: string) {
    this.sheet = sheet;
    this.row = row;
    this.col = col;
    this.iv = initial;
  }

  // MUTABLE STATE — ref-getter; materializes on first touch.
  get raw() {
    return ref(this.iv);
  }

  // HOT DERIVED — the single surgical computed(). Parsing + evaluating is real
  // work; memoizing it is exactly the "one hot value" the ivue idiom promotes.
  get value(): ComputedRef<CellValue> {
    return computed<CellValue>(() => {
      const text = this.raw.value;
      if (!isFormulaText(text)) return evalLiteral(text);
      // The reads inside sheet.evaluate() (onCell/onRange → other cells'
      // value.value) are tracked by THIS computed's effect → auto deps.
      return this.sheet.evaluate(this, stripFormula(text));
    });
  }

  // DERIVED — plain getters. Reactive via leaf tracking; zero per-instance cost.
  get isFormula() {
    return isFormulaText(this.raw.value);
  }
  get display() {
    return displayOf(this.value.value);
  }
  get cssClass() {
    return cssOf(this.value.value, this.isFormula);
  }

  // Read every derived value once — used only by the measurement harness to
  // force full materialization of all 100k cells for the worst-case number.
  touch() {
    void this.value.value;
    void this.display;
    void this.isFormula;
    void this.cssClass;
  }
}

export namespace FormulaCell {
  export const $Class = $FormulaCell;
  export let Class = Reactive($Class);
  export type Instance = typeof Class.Instance;
}

formula-logic.ts

/**
 * Shared, arm-agnostic configuration + PURE cell logic for the formula grid.
 *
 * Nothing here is reactive and nothing here imports the parser — these are the
 * plain functions the reactive `FormulaCell` wraps. Keeping them pure means the
 * cell class stays a thin reactivity shell (one `computed()` + plain getters)
 * and the same functions can be unit-reasoned about in isolation.
 */

/** Grid shape — 40 columns × 2,500 rows = 100,000 cells. */
export const COLS = 40;
export const ROWS = 2500;
export const CELL_COUNT = COLS * ROWS; // 100,000

/** The scaled-up option — 40 columns × 25,000 rows = 1,000,000 cells. */
export const ROWS_1M = 25000;

/** Row-windowing geometry (identical to the plain grid demo). */
export const ROW_HEIGHT = 28; // px
export const VIEWPORT_HEIGHT = 448; // px  → ~16 rows on screen
export const OVERSCAN = 4; // extra rows above/below the viewport

/**
 * The running-sum column resets every RUNSUM_BLOCK rows so no dependency chain
 * is thousands of levels deep (a cold read of the bottom of a full-height chain
 * would recurse through the parser thousands of times). A block of 50 keeps the
 * chain shallow while still cascading across ~3 screens when the top is edited.
 */
export const RUNSUM_BLOCK = 50;

/** A value a cell can resolve to. `FormulaError` is detected structurally. */
export type CellValue =
  number | string | boolean | null | { _error?: string; error?: string };

/** Spreadsheet-style column label: 0→A, 25→Z, 26→AA … */
export function colLabel(n: number): string {
  let s = '';
  let x = n + 1;
  while (x > 0) {
    const m = (x - 1) % 26;
    s = String.fromCharCode(65 + m) + s;
    x = Math.floor((x - 1) / 26);
  }
  return s;
}

/**
 * Deterministic numeric input for a data cell: a repeatable mix of
 * positives/negatives/decimals with ~8% blanks (so `cssClass` and the blank
 * dot vary). Kept purely numeric so the formulas that reference these columns
 * never error on the initial data — text can still be typed in live.
 * Same (row,col) → same value on every build.
 */
export function numData(row: number, col: number): string {
  const seed = row * COLS + col;
  if (seed % 13 === 5) return ''; // ~7.7% blanks
  const v = ((seed * 2654435761) % 100000) / 100 - 500; // −500 … 500
  return (Math.round(v * 100) / 100).toString();
}

/**
 * The literal text every cell starts with — REAL Excel-formula syntax wired so
 * roughly half the grid is cross-referencing formulas and the other half is the
 * numeric source data they read. `row`/`col` are 0-based here; the A1 refs they
 * emit are 1-based.
 *
 * Column map (per row r = row + 1):
 *   A B C D (0-3) input numbers            — the source data
 *   E   (4)  =A+B                          — cross-cell arithmetic
 *   F   (5)  =C-D                           — cross-cell arithmetic
 *   G   (6)  =SUM(A:D)                      — range, exercises onRange
 *   H   (7)  =AVERAGE(A:D)                  — range, exercises onRange
 *   I   (8)  =IF(A>0, B, C)                 — CONDITIONAL dependency (marquee)
 *   J   (9)  =J(r-1)+A  (block-reset)       — running sum (marquee cascade)
 *   K…AN(10+) even col = input, odd col = =<left1>+<left2>  (cross-column mesh)
 */
export function initialFormula(row: number, col: number): string {
  const r = row + 1; // 1-based row for A1 notation
  switch (col) {
    case 0:
    case 1:
    case 2:
    case 3:
      return numData(row, col);
    case 4:
      return `=A${r}+B${r}`;
    case 5:
      return `=C${r}-D${r}`;
    case 6:
      return `=SUM(A${r}:D${r})`;
    case 7:
      return `=AVERAGE(A${r}:D${r})`;
    case 8:
      return `=IF(A${r}>0,B${r},C${r})`;
    case 9:
      // Running sum, reset at the top of each RUNSUM_BLOCK-row block.
      return row % RUNSUM_BLOCK === 0 ? `=A${r}` : `=J${r - 1}+A${r}`;
    default:
      // Filler mesh: even columns are input data; odd columns sum the two
      // cells immediately to their left (one data, one formula) — a real
      // cross-column dependency, not a decorative one.
      if (col % 2 === 0) return numData(row, col);
      return `=${colLabel(col - 1)}${r}+${colLabel(col - 2)}${r}`;
  }
}

/** Does the literal text start (after leading spaces) with '='? */
export function isFormulaText(text: string): boolean {
  const t = text.trimStart();
  return t.length > 0 && t[0] === '=';
}

/** Strip the leading '=' (and any leading spaces) to get the formula body. */
export function stripFormula(text: string): string {
  return text.trimStart().slice(1);
}

/** Structural FormulaError detection — avoids importing the parser here. */
export function isFormulaError(
  value: CellValue,
): value is { _error?: string; error?: string } {
  return (
    typeof value === 'object' &&
    value !== null &&
    ('_error' in value || 'error' in value)
  );
}

/**
 * Resolve a NON-formula literal to its value: '' → null (blank, SUMs as 0),
 * a numeric string → a number, anything else → the text verbatim.
 */
export function evalLiteral(text: string): CellValue {
  const t = text.trim();
  if (t.length === 0) return null;
  const n = Number(t);
  return !Number.isNaN(n) && Number.isFinite(n) ? n : text;
}

/** Display string for a resolved value. */
export function displayOf(value: CellValue): string {
  if (value == null) return '·';
  if (isFormulaError(value))
    return String(value.error ?? value._error ?? '#ERR');
  if (typeof value === 'number') {
    return Number.isFinite(value)
      ? value.toLocaleString('en-US', { maximumFractionDigits: 2 })
      : String(value);
  }
  if (typeof value === 'boolean') return value ? 'TRUE' : 'FALSE';
  return String(value);
}

/** CSS class driven by error-ness, number sign, and whether it's a formula. */
export function cssOf(value: CellValue, isFormula: boolean): string {
  let base: string;
  if (isFormulaError(value)) base = 'gc-err';
  else if (value == null) base = 'gc-zero';
  else if (typeof value === 'number')
    base = value < 0 ? 'gc-neg' : value > 0 ? 'gc-pos' : 'gc-zero';
  else base = 'gc-text';
  return isFormula ? base + ' gc-formula' : base;
}

Open in StackBlitz ⚡ — the playground boots with this example's route and file active.

The measured heap/creation protocol lives in demo/formula/RESULTS.md; the benchmark context is on the Interactive Benchmarks page.