Formulas

werkbook includes a compiled formula engine that supports 55+ Excel functions. Formulas are parsed, compiled to bytecode, and evaluated with automatic dependency tracking.

Setting Formulas

Set formulas without the leading =:

sheet.SetFormula("A5", "SUM(A1:A4)")
sheet.SetFormula("B5", "AVERAGE(B1:B4)")
sheet.SetFormula("C1", "IF(A1>100,\"High\",\"Low\")")

Reading Formula Results

GetValue on a formula cell returns the computed result. The formula is evaluated lazily on first access:

sheet.SetFormula("A1", "2+2")

v, _ := sheet.GetValue("A1")
fmt.Println(v.Raw()) // 4

To read the formula text itself:

f, _ := sheet.GetFormula("A1")
fmt.Println(f) // "2+2"

Dependency Tracking

werkbook maintains a dependency graph across all formulas. When a cell’s value changes, any formulas that reference it are marked dirty and re-evaluated on next access:

sheet.SetValue("A1", 10)
sheet.SetFormula("B1", "A1*2")

v, _ := sheet.GetValue("B1")
fmt.Println(v.Raw()) // 20

sheet.SetValue("A1", 50)
v, _ = sheet.GetValue("B1")
fmt.Println(v.Raw()) // 100 — automatically recalculated

Force Recalculation

To evaluate all dirty formulas at once:

wb.Recalculate()

This is useful before saving or when you need all formula results to be current.

Cross-Sheet References

Reference cells on other sheets using the Sheet!Cell syntax:

data := wb.Sheet("Data")
data.SetValue("A1", 100)
data.SetValue("A2", 200)

summary := wb.Sheet("Summary")
summary.SetFormula("A1", "SUM(Data!A1:Data!A2)")

v, _ := summary.GetValue("A1")
fmt.Println(v.Raw()) // 300

Supported Functions

werkbook supports 55+ functions organized by category:

See the full Formula Functions reference for details on each function.

Circular References

werkbook detects circular references and returns a #REF! error:

sheet.SetFormula("A1", "B1+1")
sheet.SetFormula("B1", "A1+1")

v, _ := sheet.GetValue("A1")
fmt.Println(v.Type)   // TypeError
fmt.Println(v.String) // "#REF!"

Operators

Formulas support the standard Excel operators: