Evaluate Formulas

UniOffice ships its own formula engine, so you can compute the value of a formula without Excel. There are two ways in. Workbook.RecalculateFormulas walks every sheet and fills in the cached value of every formula cell. formula.Evaluator evaluates one expression at a time against a sheet and hands you the result as a Go value, which is what you want when the answer is for your program rather than for the file.

formEv := formula.NewEvaluator()

// A live expression, evaluated against the sheet's data.
result := formEv.Eval(sheet.FormulaContext(), "SUM(A1:A3)")
fmt.Println("SUM(A1:A3) is", result.Value())

// Or the formula already stored in a cell, by referring to the cell.
sheet.Cell("A4").SetFormulaRaw("SUM(A1:A3)+SUM(A1:A3)")
a4 := formEv.Eval(sheet.FormulaContext(), "A4")
fmt.Println("A4 is", a4.Value())

The context comes from a sheet, not from the workbook, and it is what gives the evaluator access to live cell data. References into other sheets still resolve: the context looks the sheet up by name, so SUM(Sheet2!A1:A5) works from any sheet’s context and returns #NAME? when no sheet by that name exists.

Result.Value() is a string in every case. When you need the number, read ValueNumber after checking Type against formula.ResultTypeNumber. Booleans are numbers with IsBoolean set. A range or a multi-value function comes back as ResultTypeList or ResultTypeArray, where Value() gives you only the first element.

What the engine covers

formula.SupportedFunctions() returns the list, currently 231 entries, sorted and including the _xlfn.-prefixed spellings that Excel uses for functions added after 2007. Printing it is the fastest way to check a specific function against the version you have.

A function that is not in that list is not an error at write time. It becomes an error at evaluation time: the result is #VALUE! with ErrorMessage set to unknown function XLOOKUP. RecalculateFormulas treats every error result the same way, by leaving the cached value empty rather than writing the error into the cell. That is deliberate, and it means an unsupported function costs you the cached value but not the formula, and Excel computes it on open as if UniOffice had never touched it.

The grammar is likewise incomplete. A string the parser rejects comes back as #VALUE! with unable to parse formula ..., and gets the same blank-cached-value treatment.

Limitations

Circular references are detected rather than followed. Evaluating a cell that leads back to itself returns #VALUE! with recursion detected during evaluation of A1, so the workbook does not hang, and the cached value is left empty.

Each Eval call has a one second time limit. A formula that has not finished by then returns a number result of 0, matching what Excel does in the same situation. There is no way to raise the limit, so a very large SUMPRODUCT over a big sheet can quietly produce a zero.

An evaluator caches every cell it reads, keyed by sheet name and reference, and the cache is never invalidated. Change a cell after evaluating something that depends on it and the same evaluator will keep handing you the old value. Construct a new evaluator with formula.NewEvaluator() after any edit, which costs nothing.

Named ranges resolve through the same context, with one catch worth knowing before you rely on it. See the limitations in Name a Cell Range.

Run the example

The example prints how many functions are supported, then the whole list, and then evaluates the same sum three ways: by pulling a cell value through the context, by evaluating a live expression string, and by evaluating a formula stored in a cell.

git clone https://github.com/unidoc/unioffice-examples.git
cd unioffice-examples/spreadsheet/formula-evaluation
go run main.go

If this is your first time using UniOffice, follow the getting started guide to create an API key and set up your development environment.

Sample output

Currently support 231 functions
[ABS ACCRINTM ACOS ACOSH AMORDEGRC AMORLINC AND ASIN ASINH ATAN ATAN2 ATANH ...]
A1 is 1.2
SUM(A1:A3) is 5.8
A4 is 11.6