Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

57.2 KB
Raw Blame
import {
  type CompletionContext,
  getTokens,
  Model,
  setFixedTime,
  setRandomSeed,
} from '@ironcalc/wasm';
import { format as formatExcelNumber } from 'ssf';
import { cellDateMention, cellPlainText } from './cell-mentions';
import {
  addConditionalFormats,
  type ConditionalAppearance,
  readConditionalAppearance,
} from './conditional-formatting';
import {
  type GoalSeekRequest,
  type GoalSeekResult,
  goalSeekInput,
  solveGoalSeek,
} from './goal-seek';
import { displayNumber, numericLiteral } from './number-display';
import { type SheetPivotLayout, sheetPivotLayouts } from './pivot-layout';
import { sheetTableLayouts } from './sheet-tables';
import {
  formatCellAddress,
  parseCellAddress,
  SPREADSHEET_DEFAULT_STYLE,
  SPREADSHEET_MAX_ROWS,
  SPREADSHEET_ROWS,
  type SpreadsheetCell,
  type SpreadsheetCellEdits,
  type SpreadsheetCells,
} from './spreadsheet-document';
import {
  protectStructuredReferences,
  resolveStructuredReferences,
  type WorkbookTable,
} from './structured-references';
import type { WorkbookSheetMetadata } from './workbook-metadata';
import { changeWorkbookAxis, moveWorkbookRange } from './workbook-structure';

export type CalculatedCell = {
  display: string;
  number?: number;
  error?: string;
  warning?: string;
  /** The number shows as a calendar date. */
  date?: boolean;
  /** Included only for callers requesting typed results. */
  type?: 'blank' | 'number' | 'text' | 'boolean' | 'error';
  value?: string | number | boolean | null;
  /** Rows and columns an array formula's result occupies from this anchor. */
  spill?: { rows: number; columns: number };
  /** Conditional formatting that applies to the cell. */
  conditional?: ConditionalAppearance;
};

export type SpreadsheetCalculation = Record<string, CalculatedCell>;
export type CalculationSheet = {
  id: string;
  name: string;
  cells: SpreadsheetCells;
  rowCount: number;
  metadata?: WorkbookSheetMetadata;
  /** The layouts of its pivot tables, when `metadata` leaves them out. */
  pivots?: SheetPivotLayout[];
  /** Its tables with their column names, when `metadata` leaves them out. */
  tables?: WorkbookTable[];
};
export type WorkbookCalculation = Record<string, SpreadsheetCalculation>;
export type CalculationContext = { sheetNames: string[]; activeSheet: number };
export type CellCopy = {
  from: { row: number; column: number };
  to: { row: number; column: number };
  cell: SpreadsheetCell;
};

export type SpreadsheetCalculator = {
  calculate: (
    cells: SpreadsheetCells,
    rowCount?: number
  ) => SpreadsheetCalculation;
  calculateWorkbook: (
    sheets: CalculationSheet[],
    options?: { includeTypes?: boolean }
  ) => WorkbookCalculation;
  copy: (
    copies: CellCopy[],
    context?: CalculationContext
  ) => SpreadsheetCellEdits;
  complete: (
    text: string,
    cursor: number,
    context?: CalculationContext
  ) => CompletionContext;
  changeAxis: typeof changeWorkbookAxis;
  moveRange: typeof moveWorkbookRange;
  /** An incremental calculation for a workbook that is edited repeatedly. */
  session: () => WorkbookCalculationSession;
  /**
   * Goal Seek on a private copy of `sheets`. The caller's workbook is
   * unchanged; the returned input is what a later edit would write.
   */
  goalSeek: (
    sheets: CalculationSheet[],
    request: GoalSeekRequest
  ) => GoalSeekResult;
  dispose: () => void;
};

/** A fixed clock and random sequence, for repeatable calculations. */
let fixedClock: { time?: number; timezone?: string } | undefined;

/**
 * Fix the time TODAY and NOW read (milliseconds since 1970, in `timezone`)
 * and the sequence RAND returns, for tests and reproducible imports. Without
 * a clock, calculations read the viewer's clock and time zone, as Excel reads
 * the computer's, and RAND is random.
 */
export function setCalculationClock(
  clock: { time?: number; timezone?: string; seed?: number } | undefined
) {
  fixedClock = clock;
  setFixedTime(clock?.time);
  setRandomSeed(clock?.seed);
}

/** The time zone TODAY and NOW read. */
function calculationTimezone(): string {
  if (fixedClock?.timezone) return fixedClock.timezone;
  try {
    return Intl.DateTimeFormat().resolvedOptions().timeZone || 'UTC';
  } catch {
    return 'UTC';
  }
}

/** A model that reads the calculation clock; UTC if the engine does not
 * know the zone. */
function calculationModel(name: string): Model {
  try {
    return new Model(name, 'en', calculationTimezone(), 'en');
  } catch {
    return new Model(name, 'en', 'UTC', 'en');
  }
}

const DAY_MILLISECONDS = 86_400_000;
const SERIAL_EPOCH = Date.UTC(1899, 11, 30);
/** 1 January 2000 as an Excel serial number. */
const MIN_FORMULA_DATE_SERIAL = 36_526;
// IronCalc treats this exact extent as a column style, without materializing
// a style cell for every empty row. The editable grid is still bounded to
// SPREADSHEET_MAX_ROWS.
const ENGINE_COLUMN_HEIGHT = 1_048_576;
const ENGINE_COLUMNS = 16_384;

/**
 * Excel serial for the mention's local calendar day. Presets such as
 * "Tomorrow" carry an end-of-day time, so the time of day is dropped to keep
 * date differences whole.
 */
function dateMentionSerial(iso: string): number {
  const date = new Date(iso);
  return (
    (Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()) -
      SERIAL_EPOCH) /
    DAY_MILLISECONDS
  );
}

/**
 * Whether an Excel number format shows a date or a time of day. IronCalc
 * assigns such formats to typed dates and to formulas that operate on them,
 * as Excel does. Cells without an explicit Macro format honor those; other
 * inferred formats stay general.
 */
function dateTimeFormat(numberFormat: string): 'date' | 'time' | undefined {
  const tokens = numberFormat.replace(/"[^"]*"|\\.|\[[^\]]*\]/g, '');
  if (/[dy]/i.test(tokens)) return 'date';
  if (/[hs]/i.test(tokens)) return 'time';
}

/**
 * Whether the engine's own number format may decide how a general cell shows.
 * Plain numeric literals never become dates; dates and formulas may.
 */
function canInferFormat(
  cell: SpreadsheetCell,
  value: string,
  isDate: boolean
): boolean {
  return (
    (!cell.format || cell.format === 'general') &&
    !cell.numberFormat &&
    (isDate || !numericLiteral.test(value))
  );
}

const errorDescriptions: Record<string, string> = {
  '#DIV/0!': 'This formula divides by zero or an empty cell.',
  '#CIRC!': 'This formula contains a circular reference.',
  '#ERROR!': 'This formula could not be parsed. Check its syntax.',
  '#NAME?': 'This formula contains an unknown function or name.',
  '#REF!': 'This formula refers to a cell or sheet that does not exist.',
  '#VALUE!': 'A value has the wrong type for this formula.',
  '#NUM!': 'This formula produced an invalid number.',
  '#N/A': 'A value needed by this formula is not available.',
  '#SPILL!': 'This array formula needs empty cells for its results.',
};

/** Rename through temporary names to avoid collisions with default SheetN names. */
function configureSheets(model: Model, names: string[]) {
  while (model.getWorksheetsProperties().length < names.length)
    model.newSheet();
  const reserved = new Set(names.map((name) => name.toLowerCase()));
  let prefix = '__macro_tmp_';
  while (names.some((_, index) => reserved.has(`${prefix}${index}`)))
    prefix += '_';
  for (let index = 0; index < names.length; index++)
    model.renameSheet(index, `${prefix}${index}`);
  for (let index = 0; index < names.length; index++)
    model.renameSheet(index, names[index]);
}

/** Fifteen significant digits, as Excel keeps. IronCalc truncates a sixteenth
 * digit instead of rounding it, which read 979 as 978.999999999999. */
const SCIENTIFIC_FORMAT = '0.##############E+00';
const constantDefinition =
  /^(?:[+-]?(?:\d+(?:\.\d*)?|\.\d+)(?:[eE][+-]?\d+)?|TRUE|FALSE|"(?:[^"\r\n]|"")*")$/i;

type DefinedNames = {
  /** `${sheet index | 'global'}:${lowercase name}` */
  keys: Set<string>;
  constants: Map<string, string>;
  /** Definitions IronCalc cannot register, expanded wherever they are used. */
  formulas: Map<string, string>;
  invalid: Set<string>;
  definitions: Map<string, string>;
};

/** Longest formula text that name expansion may produce. */
const MAX_EXPANDED_FORMULA = 32_768;

function hasRelativeReference(formula: string) {
  return getTokens(formula).some(({ token }) => {
    if (typeof token !== 'object') return false;
    if ('Reference' in token)
      return !token.Reference.absolute_row || !token.Reference.absolute_column;
    if (!('Range' in token)) return false;
    const { left, right } = token.Range;
    return [left, right].some(
      (bound) => !bound.absolute_row || !bound.absolute_column
    );
  });
}

function legalTokens(formula: string) {
  return getTokens(formula).every(
    ({ token }) =>
      token !== 'Illegal' && !(typeof token === 'object' && 'Illegal' in token)
  );
}

function defineNames(model: Model, sheets: CalculationSheet[]): DefinedNames {
  const names: DefinedNames = {
    keys: new Set(),
    constants: new Map(),
    formulas: new Map(),
    invalid: new Set(),
    definitions: new Map(),
  };
  for (const [sheetIndex, sheet] of sheets.entries()) {
    for (const entry of sheet.metadata?.definedNames ?? []) {
      const key = `${entry.local ? sheetIndex : 'global'}:${entry.name.toLowerCase()}`;
      if (names.keys.has(key))
        throw new Error(`Duplicate Excel name: ${entry.name}`);
      names.keys.add(key);
      const formula = entry.formula.replace(/^=/, '');
      names.definitions.set(key, formula);
      // IronCalc's named-range API only accepts references. Named constants
      // remain authoritative definitions; substitute tokens for calculation.
      if (constantDefinition.test(formula)) names.constants.set(key, formula);
      // IronCalc would pin relative references to the cells they name from A1.
      else if (hasRelativeReference(formula)) names.formulas.set(key, formula);
      else {
        try {
          model.newDefinedName(
            entry.name,
            entry.local ? sheetIndex : undefined,
            formula
          );
        } catch {
          // Excel keeps names whose references were deleted as #REF!.
          if (legalTokens(formula)) names.formulas.set(key, formula);
          else if (formula.includes('#REF!')) names.constants.set(key, '#REF!');
          else names.invalid.add(key);
        }
      }
    }
  }
  return names;
}

/** Tokens omit `sheet` for unqualified references. */
function quotedSheetPrefix(sheet: string | null | undefined) {
  return sheet ? `'${sheet.replaceAll("'", "''")}'!` : '';
}

type EngineReference = {
  row: number;
  column: number;
  absolute_row: boolean;
  absolute_column: boolean;
};

/**
 * Excel stores a name's relative references as offsets from A1, so the same
 * name refers to different cells from each formula. Shift them to the cell
 * using the name, wrapping at the sheet edges as Excel does.
 */
function shiftDefinition(definition: string, row: number, column: number) {
  if (!row && !column) return definition;
  const shift = (reference: EngineReference) => ({
    ...reference,
    row: reference.absolute_row
      ? reference.row
      : ((reference.row - 1 + row) % ENGINE_COLUMN_HEIGHT) + 1,
    column: reference.absolute_column
      ? reference.column
      : ((reference.column - 1 + column) % ENGINE_COLUMNS) + 1,
  });
  const cell = (reference: EngineReference) =>
    `${reference.absolute_column ? '$' : ''}${columnLabel(reference.column)}${reference.absolute_row ? '$' : ''}${reference.row}`;
  const characters = Array.from(definition);
  let changed = false;
  const tokens = getTokens(definition);
  for (let index = tokens.length - 1; index >= 0; index--) {
    const { token, start, end } = tokens[index];
    if (typeof token !== 'object') continue;
    let text: string | undefined;
    if ('Reference' in token) {
      const reference = token.Reference;
      if (reference.absolute_row && reference.absolute_column) continue;
      text = `${quotedSheetPrefix(reference.sheet)}${cell(shift(reference))}`;
    } else if ('Range' in token) {
      const { sheet, left, right } = token.Range;
      if (
        left.absolute_row &&
        left.absolute_column &&
        right.absolute_row &&
        right.absolute_column
      )
        continue;
      const [first, last] = [shift(left), shift(right)];
      const rows = left.column === 1 && right.column === ENGINE_COLUMNS;
      const columns = left.row === 1 && right.row === ENGINE_COLUMN_HEIGHT;
      const bound = (reference: EngineReference) =>
        rows
          ? `${reference.absolute_row ? '$' : ''}${reference.row}`
          : columns
            ? `${reference.absolute_column ? '$' : ''}${columnLabel(reference.column)}`
            : cell(reference);
      text = `${quotedSheetPrefix(sheet)}${bound(first)}:${bound(last)}`;
    }
    if (text === undefined) continue;
    const leading = characters.slice(start, end).join('').match(/^\s*/)?.[0];
    characters.splice(start, end - start, ...Array.from(`${leading}${text}`));
    changed = true;
  }
  return changed ? characters.join('') : definition;
}

function columnLabel(column: number) {
  return formatCellAddress(0, column - 1).replace(/\d+$/, '');
}

/**
 * IronCalc has no HYPERLINK. Its result is the text Excel shows: the friendly
 * name, or the link location when there is none.
 */
function hyperlinkText(formula: string) {
  let text = formula;
  // One call per pass, last first, so nested calls see current positions.
  for (let pass = 0; pass < 20 && /HYPERLINK\s*\(/i.test(text); pass++) {
    const tokens = getTokens(text);
    const index = tokens.findLastIndex(
      ({ token }, at) =>
        typeof token === 'object' &&
        'Ident' in token &&
        token.Ident.toUpperCase().replace(/^_XLFN\./, '') === 'HYPERLINK' &&
        tokens[at + 1]?.token === 'LeftParenthesis'
    );
    if (index < 0) break;
    // Split the call's top-level arguments.
    const commas: number[] = [];
    let depth = 0;
    let close = -1;
    for (let at = index + 1; at < tokens.length && close < 0; at++) {
      const current = tokens[at].token;
      if (current === 'LeftParenthesis' || current === 'LeftBrace') depth++;
      else if (current === 'RightParenthesis' || current === 'RightBrace') {
        if (--depth === 0) close = at;
      } else if (current === 'Comma' && depth === 1) commas.push(at);
    }
    if (close < 0 || commas.length > 1) break;
    const characters = Array.from(text);
    const from = commas.length ? tokens[commas[0]].end : tokens[index + 1].end;
    const argument = characters.slice(from, tokens[close].start).join('');
    characters.splice(
      tokens[index].start,
      tokens[close].end - tokens[index].start,
      `(${argument})`
    );
    text = characters.join('');
  }
  return text;
}

const tokenKind = (token: unknown) =>
  typeof token === 'string' ? token : Object.keys(token as object)[0];
const isMinus = (token: unknown) =>
  typeof token === 'object' &&
  token !== null &&
  'Addition' in token &&
  token.Addition === 'Minus';
/** Tokens after which a minus subtracts instead of negating. */
const OPERAND_ENDS = new Set([
  'Number',
  'String',
  'Boolean',
  'Error',
  'Reference',
  'Range',
  'StructuredReference',
  'Ident',
  'RightParenthesis',
  'RightBrace',
  'Percent',
]);
const SINGLE_OPERANDS = new Set([
  'Number',
  'String',
  'Boolean',
  'Error',
  'Reference',
  'Range',
  'StructuredReference',
  'Ident',
]);
/** Tokens that bind to an operand before negation does (ranges, intersections). */
const TIGHTER_THAN_NEGATION = new Set([
  'Colon',
  'Reference',
  'Range',
  'StructuredReference',
  'Ident',
  'LeftParenthesis',
]);

/** The first `--operand` IronCalc would misread, as character positions. */
function doubleNegation(tokens: ReturnType<typeof getTokens>) {
  for (let index = 0; index + 2 < tokens.length; index++) {
    if (!isMinus(tokens[index].token) || !isMinus(tokens[index + 1].token))
      continue;
    const previous = tokens[index - 1];
    if (previous && OPERAND_ENDS.has(tokenKind(previous.token))) continue;
    const first = index + 2;
    const kind = tokenKind(tokens[first].token);
    let last = first;
    if (
      kind === 'LeftParenthesis' ||
      (kind === 'Ident' && tokens[first + 1]?.token === 'LeftParenthesis')
    ) {
      let depth = 0;
      last = kind === 'Ident' ? first + 1 : first;
      for (; last < tokens.length; last++) {
        const current = tokens[last].token;
        if (current === 'LeftParenthesis' || current === 'LeftBrace') depth++;
        else if (current === 'RightParenthesis' || current === 'RightBrace')
          if (--depth === 0) break;
      }
      if (last >= tokens.length) continue;
    } else if (!SINGLE_OPERANDS.has(kind)) continue;
    const following = tokens[last + 1];
    if (following && TIGHTER_THAN_NEGATION.has(tokenKind(following.token)))
      continue;
    return {
      start: tokens[index].start,
      operand: tokens[first].start,
      end: tokens[last].end,
    };
  }
}

/**
 * IronCalc drops an array under a double negation, so the common
 * `SUMPRODUCT(--(A1:A9="x"))` is 0, while `1*` keeps it. Excel evaluates
 * `--operand` and `(1*operand)` identically, including for text and errors.
 */
function doubleNegations(formula: string) {
  let text = formula;
  // One rewrite per pass, from fresh positions, so nested ones stay intact.
  for (let pass = 0; pass < 20 && /-\s*-/.test(text); pass++) {
    const site = doubleNegation(getTokens(text));
    if (!site) break;
    const characters = Array.from(text);
    const operand = characters.slice(site.operand, site.end).join('');
    characters.splice(site.start, site.end - site.start, `(1*${operand})`);
    text = characters.join('');
  }
  return text;
}

/** Arguments that IronCalc reads cell by cell, including every empty row of
 * a whole column, where empty rows cannot change the result. -1 is any. */
const SCANNED_ARGUMENTS: Record<string, number> = {
  VLOOKUP: 1,
  MATCH: 1,
  COUNTA: -1,
};
const scannedLookup = /(?:VLOOKUP|MATCH|COUNTA)\s*\(/i;

/**
 * Whole-column lookup tables such as `VLOOKUP(x,Data!A:D,2,FALSE)` make
 * IronCalc scan all 1,048,576 rows for every lookup, and approximate MATCH
 * returns the last row. Rows past a sheet's grid are always empty, so look
 * up within the grid instead.
 */
function boundWholeColumnLookups(
  formula: string,
  sheetName: string,
  rowCounts: Map<string, number>
) {
  if (!formula.includes(':') || !scannedLookup.test(formula)) return formula;
  const tokens = getTokens(formula);
  const characters = Array.from(formula);
  const frames: { name?: string; argument: number }[] = [];
  const replacements: { start: number; end: number; text: string }[] = [];
  let braces = 0;
  for (const [index, { token, start, end }] of tokens.entries()) {
    if (token === 'LeftBrace') braces++;
    else if (token === 'RightBrace') braces--;
    else if (token === 'LeftParenthesis') {
      const previous = tokens[index - 1]?.token;
      frames.push({
        argument: 0,
        ...(typeof previous === 'object' &&
          'Ident' in previous && { name: previous.Ident.toUpperCase() }),
      });
    } else if (token === 'RightParenthesis') frames.pop();
    else if (token === 'Comma' && !braces && frames.length)
      frames[frames.length - 1].argument++;
    else if (typeof token === 'object' && 'Range' in token) {
      const { sheet, left, right } = token.Range;
      const frame = frames.at(-1);
      const scanned =
        frame?.name && SCANNED_ARGUMENTS[frame.name.replace(/^_XLFN\./, '')];
      if (
        left.row !== 1 ||
        right.row !== ENGINE_COLUMN_HEIGHT ||
        scanned === undefined ||
        (scanned !== -1 && scanned !== frame?.argument)
      )
        continue;
      const rows = rowCounts.get((sheet || sheetName).toLowerCase());
      if (!rows) continue;
      const leading = characters.slice(start, end).join('').match(/^\s*/)?.[0];
      replacements.push({
        start,
        end,
        text: `${leading}${quotedSheetPrefix(sheet)}${left.absolute_column ? '$' : ''}${columnLabel(left.column)}$1:${right.absolute_column ? '$' : ''}${columnLabel(right.column)}$${rows}`,
      });
    }
  }
  for (const { start, end, text } of replacements.reverse())
    characters.splice(start, end - start, text);
  return replacements.length ? characters.join('') : formula;
}

/**
 * IronCalc returns #VALUE! for `@` on a range in another sheet. Read the cell
 * in the formula's row or column through INDEX instead, which is what
 * Excel's implicit intersection returns.
 */
function crossSheetIntersections(formula: string, sheetName: string) {
  if (!formula.includes('@')) return formula;
  const tokens = getTokens(formula);
  const characters = Array.from(formula);
  let changed = false;
  for (let index = tokens.length - 1; index > 0; index--) {
    const { token, end } = tokens[index];
    const at = tokens[index - 1];
    // The published token type predates the `@` operator.
    if ((at.token as unknown) !== 'At' || typeof token !== 'object') continue;
    if (
      'Reference' in token &&
      token.Reference.sheet &&
      token.Reference.sheet.toLowerCase() !== sheetName.toLowerCase()
    ) {
      // One cell intersects to itself.
      characters.splice(at.start, at.end - at.start);
      changed = true;
      continue;
    }
    if (
      !('Range' in token) ||
      !token.Range.sheet ||
      token.Range.sheet.toLowerCase() === sheetName.toLowerCase()
    )
      continue;
    const { sheet, left, right } = token.Range;
    const range = `${quotedSheetPrefix(sheet)}$${columnLabel(left.column)}$${left.row}:$${columnLabel(right.column)}$${right.row}`;
    const row = `ROW()-${left.row - 1}`;
    const column = `COLUMN()-${left.column - 1}`;
    const guards = [
      ...(left.row > 1 && left.row !== right.row ? [`ROW()<${left.row}`] : []),
      ...(left.column > 1 && left.column !== right.column
        ? [`COLUMN()<${left.column}`]
        : []),
    ];
    const read =
      left.column === right.column
        ? `INDEX(${range},${row})`
        : left.row === right.row
          ? `INDEX(${range},1,${column})`
          : `INDEX(${range},${row},${column})`;
    const text = guards.length
      ? `IF(OR(${guards.join(',')}),#VALUE!,${read})`
      : read;
    characters.splice(at.start, end - at.start, text);
    changed = true;
  }
  return changed ? characters.join('') : formula;
}

/** The engine input for a formula, with names IronCalc cannot read resolved. */
function resolveNames(
  value: string,
  sheetIndex: number,
  row: number,
  column: number,
  sheets: CalculationSheet[],
  names: DefinedNames,
  unsupported: (reason: string) => void
): string {
  if (!names.keys.size) return value;
  const expanding = new Set<string>();
  /** A local name's own sheet, or undefined for workbook-level definitions. */
  const lookup = (scope: number | undefined, name: string) => {
    const local = `${scope}:${name.toLowerCase()}`;
    return scope !== undefined && names.keys.has(local)
      ? local
      : `global:${name.toLowerCase()}`;
  };
  const replacement = (key: string, name: string): string | undefined => {
    if (names.invalid.has(key))
      unsupported(
        `The Excel name ${name} uses a definition this calculation engine does not support.`
      );
    const constant = names.constants.get(key);
    if (constant !== undefined) return `(${constant})`;
    const formula = names.formulas.get(key);
    if (formula === undefined) return;
    if (expanding.has(key)) {
      unsupported(`The Excel name ${name} refers to itself.`);
      return '(#REF!)';
    }
    expanding.add(key);
    const scope = key.startsWith('global:')
      ? undefined
      : Number(key.slice(0, key.indexOf(':')));
    const text = expand(shiftDefinition(formula, row, column), scope);
    expanding.delete(key);
    return `(${text})`;
  };
  const expand = (text: string, scope: number | undefined): string => {
    // Excel permits Sheet!LocalName, which IronCalc does not tokenize.
    // Skip double-quoted literals and resolve only known definitions;
    // ordinary sheet-qualified cell references stay unchanged.
    const qualified = !text.includes('!')
      ? text
      : text.replace(
          // A name never continues into a reference such as Sheet!B$23 or
          // Sheet!B:C, even when a workbook defines a name `B`.
          /"(?:[^"]|"")*"|'((?:[^']|'')+)'!([\p{L}_\\][\p{L}\p{N}_.\\]*)(?![$:(\p{L}\p{N}_.\\])|([\p{L}_\\][\p{L}\p{N}_.\\]*)!([\p{L}_\\][\p{L}\p{N}_.\\]*)(?![$:(\p{L}\p{N}_.\\])/gu,
          (
            match,
            quoted: string | undefined,
            quotedName: string | undefined,
            plain: string | undefined,
            plainName: string | undefined
          ) => {
            if (match.startsWith('"')) return match;
            const target = sheets.findIndex(
              (sheet) =>
                sheet.name.toLowerCase() ===
                (quoted?.replaceAll("''", "'") ?? plain ?? '').toLowerCase()
            );
            if (target < 0) return match;
            const name = quotedName ?? plainName ?? '';
            const key = lookup(target, name);
            const definition = names.definitions.get(key);
            if (definition === undefined) return match;
            return replacement(key, name) ?? `(${definition})`;
          }
        );
    if (!names.constants.size && !names.formulas.size && !names.invalid.size)
      return qualified;
    const characters = Array.from(qualified);
    const tokens = getTokens(qualified);
    for (let index = tokens.length - 1; index >= 0; index--) {
      const { token, start, end } = tokens[index];
      if (
        typeof token !== 'object' ||
        !('Ident' in token) ||
        tokens[index + 1]?.token === 'LeftParenthesis'
      )
        continue;
      const text = replacement(lookup(scope, token.Ident), token.Ident);
      if (text !== undefined) characters.splice(start, end - start, text);
    }
    return characters.join('');
  };
  const expanded = expand(value, sheetIndex);
  if (expanded.length > MAX_EXPANDED_FORMULA) {
    unsupported(
      'This formula uses Excel names whose definitions are too long to calculate.'
    );
    return '=NA()';
  }
  return expanded;
}

type EnteredSheet = {
  /** Every cell given to the engine. */
  entered: Map<string, { row: number; column: number }>;
  /** Formula cells, whose results any recalculation may change. */
  formulas: Map<string, { row: number; column: number }>;
  unsupported: Map<string, string>;
  /** Cells whose engine-inferred date or time format may decide their display. */
  inferable: Set<string>;
  /** Columns from A that carry the full-precision column style. */
  width: number;
  arrays: Map<string, { width: number; height: number }>;
  /** Each array formula's result cells beyond its anchor. */
  spills: Map<string, string[]>;
  /** Whether the engine has conditional formatting rules for the sheet. */
  conditionalRules: boolean;
  /** Cells conditional formatting changed in the last read. */
  conditional: Set<string>;
};

/** An engine model with every sheet entered, kept for incremental updates. */
type EngineWorkbook = {
  model: Model;
  sheets: CalculationSheet[];
  names: DefinedNames;
  rowCounts: Map<string, number>;
  /** Every sheet's tables, which structured references resolve against. */
  tables: WorkbookTable[];
  entered: EnteredSheet[];
};

/** New cells inherit this column format, so full-precision results can be
 * read without restyling every populated cell after evaluation. */
function styleColumns(
  model: Model,
  sheetIndex: number,
  entered: EnteredSheet,
  width: number
) {
  if (width <= entered.width) return;
  model.updateRangeStyle(
    {
      sheet: sheetIndex,
      row: 1,
      column: entered.width + 1,
      width: width - entered.width,
      height: ENGINE_COLUMN_HEIGHT,
    },
    'num_fmt',
    SCIENTIFIC_FORMAT
  );
  entered.width = width;
}

/** Style a sheet's columns and order its cells for entry. */
function prepareSheet(
  model: Model,
  sheet: CalculationSheet,
  sheetIndex: number
) {
  const entered: EnteredSheet = {
    entered: new Map(),
    formulas: new Map(),
    unsupported: new Map(),
    inferable: new Set(),
    width: 0,
    arrays: new Map(),
    spills: new Map(),
    conditionalRules: false,
    conditional: new Set(),
  };
  const literals: { address: string; row: number; column: number }[] = [];
  const formulas: typeof literals = [];
  let width = 1;
  for (const [address, cell] of Object.entries(sheet.cells)) {
    if (cell.value === '') continue;
    const position = parseCellAddress(address);
    if (!position || position.row >= sheet.rowCount) continue;
    width = Math.max(width, position.column + 1);
    (cell.value.startsWith('=') && cell.format !== 'text'
      ? formulas
      : literals
    ).push({ address, ...position });
  }
  for (const [anchor, range] of Object.entries(
    sheet.metadata?.arrayFormulas ?? {}
  )) {
    const [first, last = first] = range.split(':').map(parseCellAddress);
    if (!first || !last) continue;
    entered.arrays.set(anchor, {
      width: last.column - first.column + 1,
      height: last.row - first.row + 1,
    });
    width = Math.max(width, last.column + 1);
  }
  styleColumns(model, sheetIndex, entered, width);
  hideRows(model, sheetIndex, sheet.metadata?.hiddenRows);
  // IronCalc infers a formula's date format from the cells it references when
  // the formula is entered, so plain values go first and formulas follow in
  // grid order, which chained schedules usually read in.
  formulas.sort((a, b) => a.row - b.row || a.column - b.column);
  return { entered, order: [...literals, ...formulas] };
}

/** Hidden rows, which SUBTOTAL(101-111) and AGGREGATE can leave out. */
function hideRows(model: Model, sheetIndex: number, rows: number[] = []) {
  const sorted = [...rows].sort((a, b) => a - b);
  for (let start = 0; start < sorted.length; ) {
    let end = start;
    while (end + 1 < sorted.length && sorted[end + 1] <= sorted[end] + 1) end++;
    model.setRowsHidden(sheetIndex, sorted[start] + 1, sorted[end] + 1, true);
    start = end + 1;
  }
}

/**
 * Give one cell's source to the engine. `replace` first clears what an
 * earlier entry left: its content and any format the engine inferred.
 */
function enterCell(
  workbook: EngineWorkbook,
  sheetIndex: number,
  address: string,
  row: number,
  column: number,
  replace = false
) {
  const { model, sheets, names, rowCounts, tables } = workbook;
  const sheet = sheets[sheetIndex];
  const entered = workbook.entered[sheetIndex];
  const area = {
    sheet: sheetIndex,
    row: row + 1,
    column: column + 1,
    width: 1,
    height: 1,
  };
  if (replace) {
    entered.entered.delete(address);
    entered.formulas.delete(address);
    entered.unsupported.delete(address);
    entered.inferable.delete(address);
    model.rangeClearContents(
      sheetIndex,
      row + 1,
      column + 1,
      row + 1,
      column + 1
    );
    model.updateRangeStyle(area, 'num_fmt', SCIENTIFIC_FORMAT);
  }
  const cell = sheet.cells[address];
  if (!cell || cell.value === '' || row >= sheet.rowCount) return;
  const dateMention =
    cell.format === 'text' ? undefined : cellDateMention(cell.value);
  const value = dateMention
    ? String(dateMentionSerial(dateMention.date))
    : cellPlainText(cell.value);
  const literal =
    !dateMention && (cell.format === 'text' || value !== cell.value);
  if (value === '') return;
  entered.entered.set(address, { row, column });
  if (!literal && canInferFormat(cell, value, dateMention !== undefined))
    entered.inferable.add(address);
  const unsupported = (reason: string) => {
    entered.unsupported.set(address, reason);
  };
  let input = literal ? `'${value}` : value;
  if (!literal && value.startsWith('=')) {
    entered.formulas.set(address, { row, column });
    // A table or column that no longer exists reads as Excel's #REF!.
    input = resolveNames(
      resolveStructuredReferences(value, tables, sheet.name, row, column, {
        unresolved: '#REF!',
      }) ?? value,
      sheetIndex,
      row,
      column,
      sheets,
      names,
      unsupported
    );
    if (entered.unsupported.has(address)) input = '=NA()';
    else
      input = boundWholeColumnLookups(
        crossSheetIntersections(
          doubleNegations(hyperlinkText(input)),
          sheet.name
        ),
        sheet.name,
        rowCounts
      );
  }
  const array = entered.arrays.get(address);
  let enteredArray = false;
  if (array && input.startsWith('=') && !entered.unsupported.has(address)) {
    try {
      model.setUserArrayFormula(
        sheetIndex,
        row + 1,
        column + 1,
        array.width,
        array.height,
        input
      );
      enteredArray = true;
    } catch {
      // A conflicting range calculates as a dynamic formula instead.
    }
  }
  if (!enteredArray) model.setUserInput(sheetIndex, row + 1, column + 1, input);
  // A date pill or a number formatted as a date is a date value: formulas
  // referencing it inherit the format.
  if (dateMention || (cell.format === 'date' && numericLiteral.test(value)))
    model.updateRangeStyle(area, 'num_fmt', 'm/d/yyyy');
  // Typed exponents get a 3-digit scientific format that formulas would
  // inherit; numeric literals never need an inferred display format.
  else if (!literal && /e/i.test(value) && numericLiteral.test(value))
    model.updateRangeStyle(area, 'num_fmt', SCIENTIFIC_FORMAT);
}

/** One cell's current result, or undefined for an empty cell. */
function readCell(
  workbook: EngineWorkbook,
  sheetIndex: number,
  address: string,
  row: number,
  column: number,
  includeTypes: boolean
): CalculatedCell | undefined {
  const { model } = workbook;
  const entered = workbook.entered[sheetIndex];
  const cell = workbook.sheets[sheetIndex].cells[address] as
    | SpreadsheetCell
    | undefined;
  const empty = () =>
    cell?.value
      ? {
          display: '',
          ...(includeTypes && { type: 'text' as const, value: '' }),
        }
      : undefined;
  const cellType = model.getCellType(sheetIndex, row + 1, column + 1);
  if (cellType !== 1) {
    const display = model.getFormattedCellValue(
      sheetIndex,
      row + 1,
      column + 1
    );
    if (display === '') return empty();
    if (cellType === 16)
      return {
        display,
        error:
          entered.unsupported.get(address) ??
          errorDescriptions[display] ??
          `Formula error: ${display}`,
        ...(includeTypes && { type: 'error' as const, value: null }),
      };
    return {
      display,
      ...(includeTypes && {
        type: cellType === 4 ? ('boolean' as const) : ('text' as const),
        value: cellType === 4 ? display.toUpperCase() === 'TRUE' : display,
      }),
    };
  }
  // Numbers are read directly rather than formatted and parsed back, with
  // the fifteen significant digits Excel keeps. Empty cells also report as
  // numbers, without a value.
  const value = model.getCellNumber(sheetIndex, row + 1, column + 1);
  if (value === undefined) return empty();
  const number = Number(value.toPrecision(15));
  // The engine infers display formats for typed dates and for formulas that
  // refer to them, as Excel does.
  const engineFormat = entered.inferable.has(address)
    ? dateTimeFormat(model.getCellNumberFormat(sheetIndex, row + 1, column + 1))
    : undefined;
  // IronCalc also formats a difference of two dates as a date, so
  // formula results only count as dates from 2000 onwards; a day
  // count or a negative number stays a plain number.
  const dateLike =
    engineFormat !== 'date' ||
    !cell?.value.startsWith('=') ||
    number >= MIN_FORMULA_DATE_SERIAL;
  const shown =
    engineFormat && dateLike ? { ...cell!, format: engineFormat } : cell;
  let formatted = displayNumber(number, shown);
  const date = cell?.numberFormat
    ? dateTimeFormat(cell.numberFormat) === 'date'
    : shown?.format === 'date';
  let warning: string | undefined;
  if (cell?.numberFormat) {
    try {
      formatted = formatExcelNumber(cell.numberFormat, number);
    } catch {
      warning =
        'This Excel number format cannot be displayed in Macro. Its value and original format are retained for export.';
    }
  }
  return {
    display: formatted,
    ...(warning && { warning }),
    number,
    ...(date && { date }),
    ...(includeTypes && { type: 'number' as const, value: number }),
  };
}

/** Write or remove one result. */
type ResultWriter = (
  address: string,
  value: CalculatedCell | undefined
) => void;

/** Read array results, which have no persisted source cell beyond their
 * anchor, replacing the cells each anchor spilled into before. */
function readSpills(
  workbook: EngineWorkbook,
  sheetIndex: number,
  results: SpreadsheetCalculation,
  write: ResultWriter,
  includeTypes: boolean
) {
  const { model } = workbook;
  const sheet = workbook.sheets[sheetIndex];
  const entered = workbook.entered[sheetIndex];
  for (const children of entered.spills.values())
    for (const address of children)
      if (!entered.entered.has(address)) write(address, undefined);
  entered.spills.clear();
  for (const [anchorAddress, { row, column }] of entered.formulas) {
    const structure = model.getCellArrayStructure(
      sheetIndex,
      row + 1,
      column + 1
    );
    if (typeof structure !== 'object') continue;
    const size =
      'DynamicAnchor' in structure
        ? structure.DynamicAnchor
        : 'ArrayAnchor' in structure
          ? structure.ArrayAnchor
          : undefined;
    if (!size) continue;
    const [width, height] = size;
    const anchor = results[anchorAddress];
    if (anchor && (width > 1 || height > 1))
      write(anchorAddress, {
        ...anchor,
        spill: { rows: height, columns: width },
      });
    const children: string[] = [];
    for (let r = row; r < Math.min(sheet.rowCount, row + height); r++)
      for (let c = column; c < column + width; c++) {
        if (r === row && c === column) continue;
        const address = formatCellAddress(r, c);
        if (entered.entered.has(address)) continue;
        children.push(address);
        write(
          address,
          readCell(workbook, sheetIndex, address, r, c, includeTypes)
        );
      }
    if (children.length) entered.spills.set(anchorAddress, children);
  }
}

function buildWorkbook(sheets: CalculationSheet[]): EngineWorkbook {
  const model = calculationModel('Macro spreadsheet');
  try {
    const bounded = sheets.map((sheet) => ({
      ...sheet,
      rowCount: Math.min(
        SPREADSHEET_MAX_ROWS,
        Math.max(SPREADSHEET_ROWS, sheet.rowCount)
      ),
    }));
    model.pauseEvaluation();
    configureSheets(
      model,
      bounded.map((sheet) => sheet.name)
    );
    const workbook: EngineWorkbook = {
      model,
      sheets: bounded,
      names: defineNames(model, bounded),
      rowCounts: new Map(
        bounded.map((sheet) => [sheet.name.toLowerCase(), sheet.rowCount])
      ),
      tables: bounded.flatMap(
        (sheet) => sheet.tables ?? sheetTableLayouts(sheet)
      ),
      entered: [],
    };
    for (const [sheetIndex, sheet] of bounded.entries()) {
      const { entered, order } = prepareSheet(model, sheet, sheetIndex);
      workbook.entered.push(entered);
      for (const { address, row, column } of order)
        enterCell(workbook, sheetIndex, address, row, column);
    }
    // Rules may read any sheet, so they follow every cell.
    let conditionalCells = 0;
    for (const [sheetIndex, sheet] of bounded.entries()) {
      const covered = addConditionalFormats(
        model,
        sheetIndex,
        sheet.metadata?.conditionalFormats,
        sheet.rowCount,
        Math.max(26, workbook.entered[sheetIndex].width),
        1_000_000 - conditionalCells
      );
      conditionalCells += covered;
      workbook.entered[sheetIndex].conditionalRules = covered > 0;
    }
    // GETPIVOTDATA reads the cells of pivot tables as Excel laid them out.
    const pivots = bounded.flatMap((sheet, sheetIndex) =>
      (sheet.pivots ?? sheetPivotLayouts(sheet.metadata?.pivotTables)).map(
        (layout) => ({ ...layout, sheet: sheetIndex })
      )
    );
    if (pivots.length) model.setPivotTables(pivots);
    // SUBTOTAL(1-11) leaves out the rows a table's filter hides, as Excel's.
    const filtered = workbook.tables.flatMap((table) => {
      const sheet = bounded.findIndex((item) => item.name === table.sheet);
      return table.filtered && sheet >= 0
        ? [
            {
              sheet,
              range: [
                table.top + 1,
                table.left + 1,
                table.bottom + 1,
                table.right + 1,
              ] as [number, number, number, number],
            },
          ]
        : [];
    });
    if (filtered.length) model.setFilteredRanges(filtered);
    model.resumeEvaluation();
    model.evaluate();
    return workbook;
  } catch (error) {
    freeModel(model);
    throw error;
  }
}

function writer(results: SpreadsheetCalculation): ResultWriter {
  return (address, value) => {
    if (value) results[address] = value;
    else delete results[address];
  };
}

function readWorkbook(
  workbook: EngineWorkbook,
  includeTypes: boolean
): WorkbookCalculation {
  const results: WorkbookCalculation = {};
  for (const [sheetIndex, sheet] of workbook.sheets.entries()) {
    const values: SpreadsheetCalculation = {};
    const write = writer(values);
    for (const [address, { row, column }] of workbook.entered[sheetIndex]
      .entered)
      write(
        address,
        readCell(workbook, sheetIndex, address, row, column, includeTypes)
      );
    readSpills(workbook, sheetIndex, values, write, includeTypes);
    readConditional(workbook, sheetIndex, write, includeTypes);
    results[sheet.id] = values;
  }
  return results;
}

/**
 * Conditional formatting results: each matched cell's own result with its
 * appearance, a blank result for an empty matched cell, and their own results
 * again for cells that no longer match.
 */
function readConditional(
  workbook: EngineWorkbook,
  sheetIndex: number,
  write: ResultWriter,
  includeTypes: boolean
) {
  const entered = workbook.entered[sheetIndex];
  if (!entered.conditionalRules && !entered.conditional.size) return;
  const appearance = readConditionalAppearance(workbook.model, sheetIndex);
  for (const address of new Set([
    ...entered.conditional,
    ...appearance.keys(),
  ])) {
    const position = parseCellAddress(address);
    if (!position) continue;
    const own = readCell(
      workbook,
      sheetIndex,
      address,
      position.row,
      position.column,
      includeTypes
    );
    const look = appearance.get(address);
    write(
      address,
      look ? { ...(own ?? { display: '' }), conditional: look } : own
    );
  }
  entered.conditional = new Set(appearance.keys());
}

function sameCalculatedCell(left: CalculatedCell, right: CalculatedCell) {
  return (
    left.display === right.display &&
    left.number === right.number &&
    left.error === right.error &&
    left.warning === right.warning &&
    left.date === right.date &&
    left.type === right.type &&
    left.value === right.value &&
    left.spill?.rows === right.spill?.rows &&
    left.spill?.columns === right.spill?.columns &&
    JSON.stringify(left.conditional) === JSON.stringify(right.conditional)
  );
}

/** A workbook calculation that keeps its engine model, so an edit enters
 * only the changed cells instead of rebuilding a large workbook. */
export type WorkbookCalculationSession = {
  /** Calculate a whole workbook, replacing any loaded one. The returned
   * results stay current: updates change them in place. */
  load: (
    sheets: CalculationSheet[],
    options?: { includeTypes?: boolean }
  ) => WorkbookCalculation;
  /**
   * Apply changed cells (null clears a cell) to the loaded workbook,
   * recalculate, and return the results that changed (null for removed
   * ones). Sheets, names, row counts and array formulas must be unchanged
   * since `load`; after a failure, load the workbook again.
   */
  update: (
    changes: Record<string, Record<string, SpreadsheetCell | null>>,
    options?: { includeTypes?: boolean }
  ) => Record<string, Record<string, CalculatedCell | null>>;
  /**
   * Goal Seek on a copy of the loaded model. The loaded workbook and its
   * cached results stay as they were.
   */
  goalSeek: (request: GoalSeekRequest) => GoalSeekResult;
  dispose: () => void;
};

function goalSeekLabel(sheetName: string, address: string) {
  const quote = /[^A-Za-z0-9_]/.test(sheetName);
  const sheet = quote ? `'${sheetName.replaceAll("'", "''")}'` : sheetName;
  return `${sheet}!${address}`;
}

function isFormulaCell(cell: SpreadsheetCell | undefined) {
  return !!cell && cell.format !== 'text' && cell.value.startsWith('=');
}

/** Adjust one numeric cell until a formula cell reaches `request.goal`. */
function goalSeekOnModel(
  model: Model,
  sheets: CalculationSheet[],
  request: GoalSeekRequest
): GoalSeekResult {
  const setSheet = sheets.findIndex((sheet) => sheet.id === request.setSheetId);
  const changeSheet = sheets.findIndex(
    (sheet) => sheet.id === request.changeSheetId
  );
  if (setSheet < 0 || changeSheet < 0)
    return { status: 'invalid', message: 'Choose cells in this workbook.' };
  const setPosition = parseCellAddress(request.setAddress);
  const changePosition = parseCellAddress(request.changeAddress);
  if (!setPosition || !changePosition)
    return { status: 'invalid', message: 'Enter a cell such as B12.' };
  if (!Number.isFinite(request.goal))
    return { status: 'invalid', message: 'Enter a number for the goal.' };
  const setLabel = goalSeekLabel(sheets[setSheet].name, request.setAddress);
  const changeLabel = goalSeekLabel(
    sheets[changeSheet].name,
    request.changeAddress
  );
  if (setSheet === changeSheet && request.setAddress === request.changeAddress)
    return {
      status: 'invalid',
      message: 'Set cell and the changing cell have to be different cells.',
    };
  const setSource = sheets[setSheet].cells[request.setAddress];
  const changeSource = sheets[changeSheet].cells[request.changeAddress];
  if (!isFormulaCell(setSource))
    return {
      status: 'invalid',
      message: `${setLabel} needs a formula. Goal Seek changes another cell until that formula reaches the goal.`,
    };
  if (isFormulaCell(changeSource))
    return {
      status: 'invalid',
      message: `${changeLabel} contains a formula. Choose a cell that holds a number.`,
    };
  if (changeSource?.format === 'text' && changeSource.value !== '')
    return {
      status: 'invalid',
      message: `${changeLabel} is plain text. Choose a cell that holds a number.`,
    };
  if (
    setPosition.row >= sheets[setSheet].rowCount ||
    changePosition.row >= sheets[changeSheet].rowCount
  )
    return { status: 'invalid', message: 'That cell is outside the sheet.' };

  const setRow = setPosition.row + 1;
  const setColumn = setPosition.column + 1;
  const changeRow = changePosition.row + 1;
  const changeColumn = changePosition.column + 1;
  const readSet = () => {
    if (model.getCellType(setSheet, setRow, setColumn) !== 1) return;
    const value = model.getCellNumber(setSheet, setRow, setColumn);
    return value !== undefined && Number.isFinite(value) ? value : undefined;
  };
  if (
    changeSource &&
    changeSource.value !== '' &&
    model.getCellType(changeSheet, changeRow, changeColumn) !== 1
  )
    return {
      status: 'invalid',
      message: `${changeLabel} needs to hold a number.`,
    };
  let guess = 0;
  const current = model.getCellNumber(changeSheet, changeRow, changeColumn);
  if (current !== undefined && Number.isFinite(current)) guess = current;

  const evaluate = (input: number) => {
    model.pauseEvaluation();
    try {
      model.setUserInput(
        changeSheet,
        changeRow,
        changeColumn,
        goalSeekInput(input)
      );
    } catch {
      throw new Error('GOAL_SEEK_CHANGE_FAILED');
    } finally {
      model.resumeEvaluation();
    }
    model.evaluate();
    return readSet();
  };

  let outcome: ReturnType<typeof solveGoalSeek>;
  try {
    outcome = solveGoalSeek({ goal: request.goal, guess, evaluate });
  } catch (error) {
    if (error instanceof Error && error.message === 'GOAL_SEEK_CHANGE_FAILED')
      return {
        status: 'invalid',
        message: `Goal Seek could not change ${changeLabel}. Choose a cell that holds a number.`,
      };
    throw error;
  }
  if (!outcome || outcome.status === 'unchanged') {
    if (outcome?.status === 'unchanged')
      return {
        status: 'invalid',
        message: `Changing ${changeLabel} does not change ${setLabel}.`,
      };
    return {
      status: 'invalid',
      message: `The formula in ${setLabel} does not produce a number Goal Seek can adjust.`,
    };
  }
  const input = goalSeekInput(outcome.input);
  return {
    status: outcome.status,
    input,
    value: Number(input),
    result: outcome.result,
    evaluations: outcome.evaluations,
    setSheetId: request.setSheetId,
    setAddress: request.setAddress,
    changeSheetId: request.changeSheetId,
    changeAddress: request.changeAddress,
  };
}

function createWorkbookCalculationSession(): WorkbookCalculationSession {
  let workbook: EngineWorkbook | undefined;
  let results: WorkbookCalculation = {};
  const dispose = () => {
    if (workbook) freeModel(workbook.model);
    workbook = undefined;
    results = {};
  };
  return {
    load(sheets, options) {
      dispose();
      // Own the cell maps: updates change them in place.
      workbook = buildWorkbook(
        sheets.map((sheet) => ({ ...sheet, cells: { ...sheet.cells } }))
      );
      results = readWorkbook(workbook, options?.includeTypes ?? false);
      return results;
    },
    update(changes, options) {
      if (!workbook) throw new Error('No workbook is loaded.');
      const current = workbook;
      const includeTypes = options?.includeTypes ?? false;
      try {
        const literals: [number, string, number, number][] = [];
        const formulas: typeof literals = [];
        current.model.pauseEvaluation();
        for (const [id, cells] of Object.entries(changes)) {
          const sheetIndex = current.sheets.findIndex(
            (sheet) => sheet.id === id
          );
          if (sheetIndex < 0) throw new Error(`Unknown sheet ${id}.`);
          const sheet = current.sheets[sheetIndex];
          for (const [address, cell] of Object.entries(cells)) {
            const position = parseCellAddress(address);
            if (!position) continue;
            if (cell) sheet.cells[address] = cell;
            else delete sheet.cells[address];
            styleColumns(
              current.model,
              sheetIndex,
              current.entered[sheetIndex],
              position.column + 1
            );
            (cell?.value.startsWith('=') && cell.format !== 'text'
              ? formulas
              : literals
            ).push([sheetIndex, address, position.row, position.column]);
          }
        }
        formulas.sort((a, b) => a[2] - b[2] || a[3] - b[3]);
        for (const [sheetIndex, address, row, column] of [
          ...literals,
          ...formulas,
        ])
          enterCell(current, sheetIndex, address, row, column, true);
        current.model.resumeEvaluation();
        current.model.evaluate();
        const delta: Record<string, Record<string, CalculatedCell | null>> = {};
        for (const [sheetIndex, sheet] of current.sheets.entries()) {
          results[sheet.id] ??= {};
          const values = results[sheet.id];
          const before = new Map<string, CalculatedCell | undefined>();
          const write: ResultWriter = (address, value) => {
            if (!before.has(address)) before.set(address, values[address]);
            if (value) values[address] = value;
            else delete values[address];
          };
          const read = (address: string, row: number, column: number) =>
            write(
              address,
              readCell(current, sheetIndex, address, row, column, includeTypes)
            );
          // Literal results depend only on their own source; recalculation
          // can change any formula and array result.
          const entered = current.entered[sheetIndex];
          for (const address of Object.keys(changes[sheet.id] ?? {})) {
            const position = entered.entered.get(address);
            if (position) read(address, position.row, position.column);
            else write(address, undefined);
          }
          for (const [address, { row, column }] of entered.formulas)
            read(address, row, column);
          readSpills(current, sheetIndex, values, write, includeTypes);
          readConditional(current, sheetIndex, write, includeTypes);
          const changed: Record<string, CalculatedCell | null> = {};
          for (const [address, old] of before) {
            const now = values[address];
            if (!now) {
              if (old) changed[address] = null;
            } else if (!old || !sameCalculatedCell(old, now))
              changed[address] = now;
          }
          if (Object.keys(changed).length) delta[sheet.id] = changed;
        }
        return delta;
      } catch (error) {
        dispose();
        throw error;
      }
    },
    goalSeek(request) {
      if (!workbook)
        return {
          status: 'invalid',
          message: 'Calculate the sheet before using Goal Seek.',
        };
      let clone: Model | undefined;
      try {
        // A copy keeps trial values out of the workbook the editor is showing.
        clone = Model.fromBytes(workbook.model.toBytes(), 'en');
        return goalSeekOnModel(clone, workbook.sheets, request);
      } catch (error) {
        return {
          status: 'invalid',
          message: error instanceof Error ? error.message : 'Goal Seek failed.',
        };
      } finally {
        if (clone) freeModel(clone);
      }
    },
    dispose,
  };
}

/** A failed engine call can leave the model borrowed; never let releasing it
 * replace the original error. */
function freeModel(model: Model) {
  try {
    model.free();
  } catch {
    // The original failure is already propagating.
  }
}

/**
 * Load the calculation engine only when a spreadsheet mounts. Source text is
 * authoritative; results are derived locally and never written to the CRDT.
 */
export function createInitializedSpreadsheetCalculator(): SpreadsheetCalculator {
  let disposed = false;

  function calculateWorkbook(
    sheets: CalculationSheet[],
    options?: { includeTypes?: boolean }
  ): WorkbookCalculation {
    if (disposed) throw new Error('The spreadsheet calculator is disposed.');
    // Rebuild from authoritative input so deletion and remote edit order
    // cannot leave stale engine state. All sheets share one dependency graph.
    const workbook = buildWorkbook(sheets);
    try {
      return readWorkbook(workbook, options?.includeTypes ?? false);
    } finally {
      freeModel(workbook.model);
    }
  }

  return {
    changeAxis: changeWorkbookAxis,
    moveRange: moveWorkbookRange,
    session() {
      if (disposed) throw new Error('The spreadsheet calculator is disposed.');
      return createWorkbookCalculationSession();
    },
    complete(text, cursor, context) {
      if (disposed) throw new Error('The spreadsheet calculator is disposed.');
      const model = new Model('Formula help', 'en', 'UTC', 'en');
      try {
        if (context) configureSheets(model, context.sheetNames);
        return model.getFormulaCompletion(
          context?.activeSheet ?? 0,
          1,
          1,
          text,
          Array.from(text.slice(0, cursor)).length
        );
      } finally {
        model.free();
      }
    },
    calculate(cells, rowCount = SPREADSHEET_ROWS) {
      return calculateWorkbook([
        { id: 'sheet1', name: 'Sheet1', cells, rowCount },
      ]).sheet1;
    },
    calculateWorkbook,
    goalSeek(sheets, request) {
      if (disposed) throw new Error('The spreadsheet calculator is disposed.');
      const workbook = buildWorkbook(sheets);
      try {
        return goalSeekOnModel(workbook.model, workbook.sheets, request);
      } finally {
        freeModel(workbook.model);
      }
    },
    copy(copies, context) {
      if (disposed) throw new Error('The spreadsheet calculator is disposed.');
      const model = new Model('Macro copy', 'en', 'UTC', 'en');
      const edits: SpreadsheetCellEdits = {};
      const sheet = context?.activeSheet ?? 0;
      try {
        model.pauseEvaluation();
        if (context) configureSheets(model, context.sheetNames);
        model.setSelectedSheet(sheet);
        for (const { from, to, cell } of copies) {
          let value = cell.value;
          if (value.startsWith('=') && cell.format !== 'text') {
            // Use IronCalc's parser and displacement rules, including mixed
            // references, quoted strings, ranges, and sheet-qualified names.
            // Table references name columns, not cells, so they stay as written.
            const references = protectStructuredReferences(value);
            model.setUserInput(
              sheet,
              from.row + 1,
              from.column + 1,
              references.formula
            );
            model.setSelectedCell(from.row + 1, from.column + 1);
            const clipboard = model.copyToClipboard();
            model.setSelectedCell(to.row + 1, to.column + 1);
            model.pasteFromClipboard(
              sheet,
              clipboard.range,
              clipboard.data,
              false
            );
            value = references.restore(
              model.getCellContent(sheet, to.row + 1, to.column + 1)
            );
          }
          edits[formatCellAddress(to.row, to.column)] = {
            ...SPREADSHEET_DEFAULT_STYLE,
            ...cell,
            value,
          };
        }
        return edits;
      } finally {
        model.free();
      }
    },
    dispose() {
      disposed = true;
    },
  };
}