Formula Evaluation

One of the goals of gooxml is to be able to create pivot tables entirely from Go. Since some valutes may be computed by formulas, on the road to pivot tables, we will need to first stop by formula evaluation town.

There are several good resources covering the contents of Excel formulas including “A Grammar for Spreadsheet Formulas Evaluated on Two Large Datasets”. We won’t be using the grammar as written in the paper, but some data points from the paper are very useful:

  • most Excel formulas are simple
  • almost all formulas use data from other cells
  • a surprising number of referring mechanisms are used by less than 1% of the formulas

Extrapolating from this a bit, we believe that implementing a large subset of the formula grammar and functions will cover the vast majority of formula use cases. Uncommon use cases can then be handled on a case-by-case basis as they should rarely occur.

In our initial testing, we’ve already discovered the Excel and LibreOffice have much different opinions regarding what is considered valid input for some functions. We think that focusing on replicating Excel’s behavior for formulas with valid inputs gets us the most return on time invested in implementation.

The code is in our main repository at gooxml/spreadsheet/formula. There you’l find a ragel input (lexer.rl), goyacc grammar (grammar.y) that when generated produce the lexer and parser. If you’re looking for a non-trivial ragel+goyacc+go example, it’s probably worth looking into.

And lastly a little example showing formula exceution including retrieving data from a sheet:

 
package main

import (
	"fmt"

	"baliance.com/gooxml/spreadsheet/formula"
	"baliance.com/gooxml/spreadsheet"
)

func main() {
	ss := spreadsheet.New()
	sheet := ss.AddSheet()
	sheet.Cell("A1").SetNumber(1.2)
	sheet.Cell("A2").SetNumber(2.3)
	sheet.Cell("A3").SetNumber(2.3)

	formEv := formula.NewEvaluator()

	// the formula context allows the formula evaluator to pull data from a
	// sheet
	a1Cell := sheet.FormulaContext().Cell("A1", formEv)
	fmt.Println("A1 is", a1Cell.Value())

	// So that when evaluating formulas, live workbook data is used. Formulas
	// can be evaluated directly in the context of a sheet.
	result := formEv.Eval(sheet.FormulaContext(), "SUM(A1:A3)")
	fmt.Println("SUM(A1:A3) is", result.Value())

	// Or, stored in a cell and the cell evaulated.
	sheet.Cell("A4").SetFormulaRaw("SUM(A1:A3)+SUM(A1:A3)")
	a4Value := formEv.Eval(sheet.FormulaContext(), "A4")
	fmt.Println("A4 is", a4Value.Value())

}