recalculate method
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,
);
}
}