recalculate method

void recalculate({
  1. Iterable<String>? changed,
})

Recomputes every formula cell in the workbook and stores each result as the formula's cached value (its <v>), so a saved file shows results without the spreadsheet app having to recalculate.

Opt-in; nothing here runs during normal read/write. Dependencies (incl. across sheets), ranges, defined names, and custom functions resolve on demand, and each cell is computed once. A self-referential formula resolves to #CIRC; an unparseable one to #ERROR!.

A formula whose result is an array (a dynamic-array function such as FILTER or SEQUENCE, or a range like =A1:A3) spills: the anchor cell keeps the formula (written as <f t="array" ref="...">) and the other cells of the spill range receive the computed values. The anchor reports the range via FormulaCellValue.spillRange.

Spilling matches Excel:

  • If any cell the array would fill is already occupied (by a value or another formula), the array cannot spill: the anchor result becomes #SPILL! and the blocking cells are left untouched (never overwritten).
  • Cells a formula spilled into on a previous recalculate are cleared before it recomputes, so an array that shrinks leaves no stale values. A literal a caller wrote inside a prior spill range is treated as part of that spill and cleared; a formula placed there is kept and instead makes the re-spill resolve to #SPILL!.

Pass changed (A1 references, optionally sheet-qualified, for example ['A1', "Sheet2!B3"], and ranges like 'A1:A9') to recompute incrementally: only the formulas that transitively depend on those cells are recomputed, instead of the whole workbook. The result always matches a full recalculate. A formula that uses a dynamic reference (INDIRECT / OFFSET) or a volatile function (NOW / TODAY / RAND) always recomputes; if none of the changed references can be parsed, a full recalculate is performed so nothing is left stale. With changed omitted (the default) every formula is recomputed, exactly as before.

Implementation

void recalculate({Iterable<String>? changed}) {
  parser._ensureAllSheetsParsed();
  final ctx = _FormulaContext(this);

  // Every formula cell in the workbook.
  final all = <(Data, String)>[];
  for (final entry in _sheetMap.entries) {
    for (final row in entry.value._sheetData.values) {
      for (final data in row.values) {
        if (data.value is FormulaCellValue) all.add((data, entry.key));
      }
    }
  }

  // Recompute all of them, or (when the caller says what changed) only the
  // formulas transitively affected by those cells.
  final targets = changed == null
      ? all
      : _incrementalTargets(ctx, all, changed);

  // The spill range each target produced on a prior recalculate, so we can
  // clear the cells it owns before recomputing.
  final priorSpills = <(String, String)>[]; // (sheetName, spill ref)
  for (final (data, name) in targets) {
    final index = data.cellIndex;
    final ref =
        (data.value as FormulaCellValue)._arrayRef ??
        _spillAnchors[(name, index.columnIndex, index.rowIndex)];
    if (ref != null) priorSpills.add((name, ref));
  }

  // Clear each prior spill's non-anchor literals so a shrunk array leaves no
  // ghosts and an anchor never collides with its own previous range.
  for (final (name, ref) in priorSpills) {
    final sheet = _sheetMap[name];
    final box = _spillBox(ref);
    if (sheet == null || box == null) continue;
    final (c0, r0, c1, r1) = box;
    for (var r = r0; r <= r1; r++) {
      for (var c = c0; c <= c1; c++) {
        if (r == r0 && c == c0) continue; // keep the anchor
        if (sheet._sheetData[r]?[c]?.value is FormulaCellValue) continue;
        sheet._removeCell(r, c);
      }
    }
  }

  // Evaluate every formula once against the cleared grid.
  final evaluated = <(Data, String, _EvalValue)>[];
  for (final (data, name) in targets) {
    final index = data.cellIndex;
    evaluated.add((
      data,
      name,
      ctx.cellValue(name, index.columnIndex, index.rowIndex),
    ));
  }

  final anchorWrites = <(Data, FormulaCellValue)>[];
  final spills = <(String, int, int, CellValue)>[]; // sheet, row, col, value
  // Cells claimed by a committed spill this pass, so two arrays can't overlap
  // (the later one resolves to #SPILL!).
  final claimed = <(String, int, int)>{};

  for (final (data, name, raw) in evaluated) {
    final formula = (data.value as FormulaCellValue).formula;
    final index = data.cellIndex;
    final c0 = index.columnIndex;
    final r0 = index.rowIndex;
    final rows = raw is _ArrayVal ? raw.rows.length : 1;
    final cols = raw is _ArrayVal && raw.rows.isNotEmpty
        ? raw.rows.first.length
        : 1;

    if (raw is! _ArrayVal || (rows <= 1 && cols <= 1)) {
      _spillAnchors.remove((name, c0, r0));
      final (cached, type) = _cachedFor(_evalToCell(raw));
      anchorWrites.add((
        data,
        FormulaCellValue._typed(formula, cached, type),
      ));
      continue;
    }

    // Blocked if any non-anchor target cell holds a value or formula, or was
    // claimed by another array this pass.
    final sheet = _sheetMap[name];
    var blocked = false;
    for (var r = 0; r < rows && !blocked; r++) {
      for (var c = 0; c < cols; c++) {
        if (r == 0 && c == 0) continue;
        final rr = r0 + r, cc = c0 + c;
        if (sheet?._sheetData[rr]?[cc]?.value != null ||
            claimed.contains((name, rr, cc))) {
          blocked = true;
          break;
        }
      }
    }
    if (blocked) {
      _spillAnchors.remove((name, c0, r0));
      final (cached, type) = _cachedFor(_spillError);
      anchorWrites.add((
        data,
        FormulaCellValue._typed(formula, cached, type),
      ));
      continue;
    }

    final ref = getSpanCellId(c0, r0, c0 + cols - 1, r0 + rows - 1);
    _spillAnchors[(name, c0, r0)] = ref;
    final (cached, type) = _cachedFor(_evalToCell(raw.rows[0][0]));
    anchorWrites.add((
      data,
      FormulaCellValue._typed(formula, cached, type, arrayRef: ref),
    ));
    for (var r = 0; r < rows; r++) {
      for (var c = 0; c < cols; c++) {
        if (r == 0 && c == 0) continue;
        final rr = r0 + r, cc = c0 + c;
        claimed.add((name, rr, cc));
        spills.add((name, rr, cc, _evalToCell(raw.rows[r][c])));
      }
    }
  }

  for (final (data, value) in anchorWrites) {
    data._value = value;
  }
  for (final (name, row, col, value) in spills) {
    final sheet = _sheetMap[name];
    if (sheet == null) continue;
    sheet.updateCell(
      CellIndex.indexByColumnRow(columnIndex: col, rowIndex: row),
      value,
    );
  }
}