The Formula Engine — ReoGrid Docs

Formulas keep a dependency graph, so only the formulas depending on a changed value are recalculated. The sheet is never swept from end to end.

The basics

using unvell.ReoGrid.Core;

ws.SetNumber(0, 0, 10);
ws.SetNumber(1, 0, 20);
ws.SetFormula(2, 0, "SUM(A1:A2)");   // no leading '='

Console.WriteLine(ws.GetDisplayText(2, 0));   // 30

// changing A1 recalculates A3 automatically
ws.SetNumber(0, 0, 15);
Console.WriteLine(ws.GetDisplayText(2, 0));   // 35

No leading =. V4 assigned "=SUM(A1:A2)" as a value; V5 has a dedicated formula API, and the symbol is expressed by the API instead.

To handle a string the user typed — where a leading = is what decides whether it is a formula — use the control’s SetActiveCellInput(text).

Inspecting and clearing

bool has = ws.HasFormula(2, 0);
string? text = ws.GetFormula(2, 0);      // "SUM(A1:A2)"

ws.ClearFormula(2, 0);                   // clear the formula (no value is left behind)
ws.Recalculate();                        // recalculate the whole sheet

Recalculate() is only needed when you want to redo everything at once, such as right after loading an xlsx. Ordinary edits trigger dependency recalculation on their own.

Bulk inserts

// batch the recalculation when writing a lot
ws.Formulas.BeginBatch();
try
{
	for (int r = 0; r < 10_000; r++)
		ws.SetFormula(r, 2, $"A{r + 1}*B{r + 1}");
}
finally
{
	ws.Formulas.EndBatch();   // recalculated once, here
}

Without BeginBatch / EndBatch, every write recalculates the formulas depending on it. Always batch when inserting more than a few thousand.

How recalculation works

SetFormula parses the formula and registers the cells and ranges it reads in the dependency graph
When a cell’s value changes, the formulas depending on it are identified
They are evaluated in dependency order (topologically)

That ordering is what makes a chain like A1 → B1 → C1 come out correct in a single pass.

Circular references

A detected cycle produces #CIRCULAR!. Excel raises a warning dialog; V5 surfaces it as a value, because it has to work headless.

Following structural changes

References inside formulas shift automatically when rows and columns are inserted or deleted.

Operation	What happens
Insert a row above a reference	the reference moves (`A5` → `A7`)
Delete a referenced row	it becomes `#REF!`
An absolute reference `$A$1`	also moves (`$` means “does not move when copied”, which is a different question)
Rename a sheet	cross-sheet references follow
Delete a sheet	formulas referencing it become `#REF!`

Cross-sheet references

Another sheet is referenced as Sheet2!A1. When its value changes, dependent formulas on other sheets recalculate down the chain.

See Formula References for the details.

Counting formulas

int formulaCount = ws.Formulas.Count;
bool hasOne = ws.Formulas.HasFormula(2, 0);

Current limitations

Dynamic arrays (spill) are not supported. FILTER, SORT, UNIQUE, SEQUENCE, LET and LAMBDA are not implemented, because there is not yet a mechanism for one formula to spill its result across several cells.