Flatten Formulas

Flattening replaces formulas with their results. The formula text is removed, the cell keeps the value the formula produced, and the recipient of the file sees numbers with nothing behind them. That is what you want when a workbook is a report rather than a model: nobody can re-run the calculation, change an input, or read the pricing logic out of the cells.

It is not the same as evaluating, which is the operation that fills in the cached values while leaving the formulas in place.

There is no single call for it. Flattening is a loop over the cells, and the shape of it is this:

formEv := formula.NewEvaluator()
for _, sheet := range ss.Sheets() {
    ctx := sheet.FormulaContext()
    for _, row := range sheet.Rows() {
        for _, cell := range row.Cells() {
            res := ctx.Cell(cell.Reference(), formEv)
            cached := cell.GetCachedFormulaResult()
            cell.Clear()
            setValue(cell, res, cached)
        }
    }
}

ctx.Cell evaluates the cell if it holds a formula and returns its value if it does not, so the loop does not need to check. Clear empties the value and the formula but leaves the style index alone, which is why the flattened sheet keeps its formatting. The example copies and reapplies the cell style anyway; that part is belt and braces.

setValue in the example is where the formula.Result is turned back into a cell value, and it has to switch on the result type because SetNumber, SetString, SetBool and SetError are separate calls. List and array results are unwrapped to their first element.

Limitations

Errors are burned in. RecalculateFormulas leaves the cached value blank when a formula fails, so Excel recomputes it on open; the flattening loop calls SetError instead, which writes #VALUE! into the cell as its permanent value. A workbook using a function the engine does not implement therefore flattens to a sheet full of errors, and the formula that would have let Excel fix it is gone. Check what Evaluate Formulas says about coverage before flattening someone else’s workbook, and keep the original.

The example’s boolean branch reads the input file’s cached value rather than the computed result, so it depends on the input having been written by something that caches results, such as Excel. An input with blank cached values flattens every boolean to TRUE.

Iterating row.Cells() materializes the gaps: a row whose first cell is B7 comes back with an empty A7 in front of it, and that empty cell is then written to the output file. It is harmless, and it makes the flattened file slightly larger than the original.

Nothing outside the cells is rewritten. Defined names, charts and conditional formatting rules still reference the ranges they always did, which is usually what you want, since the values are still there.

Run the example

The example opens formulas.xlsx, flattens every sheet, and writes values.xlsx, timing the run and printing memory usage on the way out. The input has formulas of several result types, including a cross-sheet SUM(Sheet4!C1:C6), so it exercises more of the engine than the size of the file suggests.

git clone https://github.com/unidoc/unioffice-examples.git
cd unioffice-examples/spreadsheet/flatten
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.