formula

package
v1.0.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Oct 25, 2024 License: AGPL-3.0 Imports: 13 Imported by: 0

Documentation

Overview

Package formula provides formula parsing and evaluation. The lexer is implemented with a ragel grammar while the the parser is implemented with goyacc. The entire formula grammar is not implemented and not all functions are supported yet. For compatibility sake, upon failure to parse or execute a formula, gooxml leaves cached formula results blank allowing Excel to compute formulas upon load. This is similar to what most other Excel libraries do which leave all cached results blank instead of attempting to execute formulas.

The unit tests for this package are unique in that we can take advantage of "cached" formula results that Excel/LibreOffice write to the sheet. These are the computed results of a formula in string form. By comparing these values to the value computed by the gooxml evaluation of the formula, adding a new test means just adding a new formula to one of the reference sheets with Excel. During the unit test, we evaluate the formula and compare it to the value that Excel computed. If they're the same, the test passes.

Index

Constants

This section is empty.

Variables

View Source 
var InvalidReferenceContext = &ivr{}

InvalidReferenceContext is a Context that can be used when evaluating an invalid reference (e.g. referencing a non-existent sheet). It implements Context safely, but returns error results.

View Source 
var ReferenceInvalid = Reference{Type: ReferenceTypeInvalid}

Functions

func LexReader 

func LexReader(r io.Reader) chan *node

func ParseCellReference 

func ParseCellReference(s string) (col string, row uint32, err error)

TODO: move these somewhere to remove duplication

func RegisterFunction 

func RegisterFunction(name string, fn Function)

RegisterFunction registers a standard function.

func RegisterFunctionComplex 

func RegisterFunctionComplex(name string, fn FunctionComplex)

RegisterFunctionComplex registers a standard function.

func SupportedFunctions 

func SupportedFunctions() []string

SupportedFunctions returns a list of supported functions.

Types

type BinOpType 

type BinOpType byte

BinOpType is the binary operation operator type 

const (
	BinOpTypeUnknown BinOpType = iota
	BinOpTypePlus
	BinOpTypeMinus
	BinOpTypeMult
	BinOpTypeDiv
	BinOpTypeExp
	BinOpTypeLT
	BinOpTypeGT
	BinOpTypeEQ
	BinOpTypeLEQ
	BinOpTypeGEQ
	BinOpTypeNE
	BinOpTypeConcat // '&' in Excel
)

Operator type constants

func (BinOpType) String 

func (i BinOpType) String() string

type BinaryExpr 

type BinaryExpr struct {
	// contains filtered or unexported fields
}

BinaryExpr is a binary expression.

func (BinaryExpr) Eval 

func (b BinaryExpr) Eval(ctx Context, ev Evaluator) Result

Eval evaluates the binary expression using the context given.

func (BinaryExpr) Reference 

func (b BinaryExpr) Reference(ctx Context, ev Evaluator) Reference

type Bool 

type Bool struct {
	// contains filtered or unexported fields
}

func (Bool) Eval 

func (b Bool) Eval(ctx Context, ev Evaluator) Result

func (Bool) Reference 

func (b Bool) Reference(ctx Context, ev Evaluator) Reference

type CellRef 

type CellRef struct {
	// contains filtered or unexported fields
}

CellRef is a reference to a single cell

func (CellRef) Eval 

func (c CellRef) Eval(ctx Context, ev Evaluator) Result

Eval evaluates and returns the result of the cell reference.

func (CellRef) Reference 

func (c CellRef) Reference(ctx Context, ev Evaluator) Reference

type ConstArrayExpr 

type ConstArrayExpr struct {
	// contains filtered or unexported fields
}

func (ConstArrayExpr) Eval 

func (c ConstArrayExpr) Eval(ctx Context, ev Evaluator) Result

func (ConstArrayExpr) Reference 

func (c ConstArrayExpr) Reference(ctx Context, ev Evaluator) Reference

type Context 

type Context interface {
	// Cell returns the result of evaluating a cell.
	Cell(ref string, ev Evaluator) Result

	// Sheet returns an evaluation context for a given sheet name.  This is used
	// when evaluating cells that pull data from other sheets (e.g. ='Sheet 2'!A1)
	Sheet(name string) Context

	// NamedRange returns a named range.
	NamedRange(name string) Reference

	// SetOffset is used so that the Context can evaluate cell references
	// differently when they are not absolute (e.g. not like '$A$5').  See the
	// shared formula support in Cell for usage.
	SetOffset(col, row uint32)
}

Context is a formula execution context. Formula evaluation uses the context to retreive information from sheets.

type EmptyExpr 

type EmptyExpr struct {
}

func (EmptyExpr) Eval 

func (e EmptyExpr) Eval(ctx Context, ev Evaluator) Result

func (EmptyExpr) Reference 

func (e EmptyExpr) Reference(ctx Context, ev Evaluator) Reference

type Error 

type Error struct {
	// contains filtered or unexported fields
}

func (Error) Eval 

func (e Error) Eval(ctx Context, ev Evaluator) Result

func (Error) Reference 

func (e Error) Reference(ctx Context, ev Evaluator) Reference

type ErrorType 

type ErrorType byte

ErrorType is a formula evaluation error type. 

const (
	ErrorTypeValue ErrorType = iota
	ErrorTypeNull
	ErrorTypeRef
	ErrorTypeName
	ErrorTypeNum
	ErrorTypeNA
	ErrorTypeDivideByZero
)

ErrorType constants.

type Evaluator 

type Evaluator interface {
	Eval(ctx Context, formula string) Result
}

Evaluator is the interface for a formula evaluator. This is needed so we can pass it to the spreadsheet to let it evaluate formula cells before returning the results.

func NewEvaluator 

func NewEvaluator() Evaluator

type Expression 

type Expression interface {
	Eval(ctx Context, ev Evaluator) Result
	Reference(ctx Context, ev Evaluator) Reference
}

func NewBinaryExpr 

func NewBinaryExpr(lhs Expression, op BinOpType, rhs Expression) Expression

NewBinaryExpr constructs a new binary expression with a given operator.

func NewBool 

func NewBool(v string) Expression

func NewCellRef 

func NewCellRef(v string) Expression

NewCellRef constructs a new cell reference.

func NewConstArrayExpr 

func NewConstArrayExpr(data [][]Expression) Expression

func NewEmptyExpr 

func NewEmptyExpr() Expression

func NewError 

func NewError(v string) Expression

func NewFunction 

func NewFunction(name string, args []Expression) Expression

func NewNamedRangeRef 

func NewNamedRangeRef(v string) Expression

NewNamedRangeRef constructs a new named range reference.

func NewNegate 

func NewNegate(e Expression) Expression

func NewNumber 

func NewNumber(v string) Expression

func NewPrefixExpr 

func NewPrefixExpr(pfx, exp Expression) Expression

func NewRange 

func NewRange(from, to Expression) Expression

NewRange constructs a new range.

func NewSheetPrefixExpr 

func NewSheetPrefixExpr(s string) Expression

func NewString 

func NewString(v string) Expression

func Parse 

func Parse(r io.Reader) Expression

func ParseString 

func ParseString(s string) Expression

type Function 

type Function func(args []Result) Result

Function is a standard function whose result only depends on its arguments.

func LookupFunction 

func LookupFunction(name string) Function

LookupFunction looks up and returns a standard function or nil.

type FunctionCall 

type FunctionCall struct {
	// contains filtered or unexported fields
}

func (FunctionCall) Eval 

func (f FunctionCall) Eval(ctx Context, ev Evaluator) Result

func (FunctionCall) Reference 

func (f FunctionCall) Reference(ctx Context, ev Evaluator) Reference

type FunctionComplex 

type FunctionComplex func(ctx Context, ev Evaluator, args []Result) Result

FunctionComplex is a function whose result depends on its arguments and the context that it's in. As an example, INDIRECT is a complex function so that INDIRECT("A1") which returns the value of the "A1" cell in a sheet can use the context to reach into the sheet and pull out required values.

func LookupFunctionComplex 

func LookupFunctionComplex(name string) FunctionComplex

LookupFunctionComplex looks up and returns a complex function or nil.

type Lexer 

type Lexer struct {
	// contains filtered or unexported fields
}

func NewLexer 

func NewLexer() *Lexer

func (*Lexer) Next 

func (l *Lexer) Next() *node

type NamedRangeRef 

type NamedRangeRef struct {
	// contains filtered or unexported fields
}

NamedRangeRef is a reference to a named range

func (NamedRangeRef) Eval 

func (n NamedRangeRef) Eval(ctx Context, ev Evaluator) Result

Eval evaluates and returns the result of the NamedRangeRef reference.

func (NamedRangeRef) Reference 

func (n NamedRangeRef) Reference(ctx Context, ev Evaluator) Reference

type Negate 

type Negate struct {
	// contains filtered or unexported fields
}

func (Negate) Eval 

func (n Negate) Eval(ctx Context, ev Evaluator) Result

func (Negate) Reference 

func (n Negate) Reference(ctx Context, ev Evaluator) Reference

type Number 

type Number struct {
	// contains filtered or unexported fields
}

func (Number) Eval 

func (n Number) Eval(ctx Context, ev Evaluator) Result

func (Number) Reference 

func (n Number) Reference(ctx Context, ev Evaluator) Reference

type PrefixExpr 

type PrefixExpr struct {
	// contains filtered or unexported fields
}

func (PrefixExpr) Eval 

func (p PrefixExpr) Eval(ctx Context, ev Evaluator) Result

func (PrefixExpr) Reference 

func (p PrefixExpr) Reference(ctx Context, ev Evaluator) Reference

type Range 

type Range struct {
	// contains filtered or unexported fields
}

Range is a range expression that when evaluated returns a list of Results.

func (Range) Eval 

func (r Range) Eval(ctx Context, ev Evaluator) Result

Eval evaluates the range returning a list of results or an error.

func (Range) Reference 

func (r Range) Reference(ctx Context, ev Evaluator) Reference

type Reference 

type Reference struct {
	Type  ReferenceType
	Value string
}

func MakeRangeReference 

func MakeRangeReference(ref string) Reference

type ReferenceType 

type ReferenceType byte

ReferenceType is a type of reference 

const (
	ReferenceTypeInvalid ReferenceType = iota
	ReferenceTypeCell
	ReferenceTypeNamedRange
	ReferenceTypeRange
	ReferenceTypeSheet
)

func (ReferenceType) String 

func (i ReferenceType) String() string

type Result 

type Result struct {
	ValueNumber  float64
	ValueString  string
	ValueList    []Result
	ValueArray   [][]Result
	ErrorMessage string
	Type         ResultType

	Ref Reference
}

Result is the result of a formula or cell evaluation .

func And 

func And(args []Result) Result

And is an implementation of the Excel AND() function.

func Arabic 

func Arabic(args []Result) Result

Arabic implements the Excel ARABIC function which parses roman numerals. It accepts one numeric argument.

func Atan2 

func Atan2(args []Result) Result

Atan2 implements the Excel ATAN2 function. It accepts two numeric arguments, and the arguments are (x,y), reversed from normal to match Excel's behaviour.

func Average 

func Average(args []Result) Result

Average implements the AVERAGE function. It differs slightly from Excel (and agrees with LibreOffice) in that boolean values are counted. As an example, AVERAGE of two cells containing TRUE & FALSE is 0.5 in LibreOffice and #DIV/0! in Excel. gooxml will return 0.5 in this case.

func Averagea 

func Averagea(args []Result) Result

Averagea implements the AVERAGEA function, AVERAGEA counts cells that contain text as a zero where AVERAGE ignores them entirely.

func Base 

func Base(args []Result) Result

Base is an implementation of the Excel BASE function that returns a string form of an integer in a specified base and of a minimum length with padded zeros.

func Ceiling 

func Ceiling(args []Result) Result

Ceiling is an implementation of the CEILING function which returns the ceiling of a number.

func CeilingMath 

func CeilingMath(args []Result) Result

CeilingMath implements _xlfn.CEILING.MATH which rounds numbers to the nearest multiple of the second argument, toward or away from zero as specified by the third argument.

func CeilingPrecise 

func CeilingPrecise(args []Result) Result

CeilingPrecise is an implementation of the CEILING.PRECISE function which returns the ceiling of a number.

func Char 

func Char(args []Result) Result

Char is an implementation of the Excel CHAR function that takes an integer in the range [0,255] and returns the corresponding ASCII character.

func Clean 

func Clean(args []Result) Result

Clean is an implementation of the Excel CLEAN function that removes unprintable characters.

func Code 

func Code(args []Result) Result

Code is an implementation of the Excel CODE function that returns the first character of the string as a number.

func Combin 

func Combin(args []Result) Result

Combin is an implementation of the Excel COMBINA function whic returns the number of combinations.

func Combina 

func Combina(args []Result) Result

Combina is an implementation of the Excel COMBINA function whic returns the number of combinations with repetitions.

func Concatenate 

func Concatenate(args []Result) Result

Concatenate is an implementation of the Excel CONCATENATE() function.

func Count 

func Count(args []Result) Result

Count implements the COUNT function.

func CountBlank 

func CountBlank(args []Result) Result

CountBlank implements the COUNTBLANK function.

func Counta 

func Counta(args []Result) Result

Counta implements the COUNTA function.

func Decimal 

func Decimal(args []Result) Result

Decimal is an implementation of the Excel function DECIMAL() that parses a string in a given base and returns the numeric result.

func Degrees 

func Degrees(args []Result) Result

Degrees is an implementation of the Excel function DEGREES() that converts radians to degrees.

func Even 

func Even(args []Result) Result

Even is an implementation of the Excel EVEN() that rounds a number to the nearest even integer.

func Exact 

func Exact(args []Result) Result

Exact is an implementation of the Excel EXACT() which compares two strings.

func Fact 

func Fact(args []Result) Result

Fact is an implementation of the excel FACT function which returns the factorial of a positive numeric input.

func FactDouble 

func FactDouble(args []Result) Result

FactDouble is an implementation of the excel FACTDOUBLE function which returns the double factorial of a positive numeric input.

func False 

func False(args []Result) Result

False is an implementation of the Excel FALSE() function. It takes no arguments.

func Floor 

func Floor(args []Result) Result

Floor is an implementation of the FlOOR function.

func FloorMath 

func FloorMath(args []Result) Result

FloorMath implements _xlfn.FLOOR.MATH which rounds numbers down to the nearest multiple of the second argument, toward or away from zero as specified by the third argument.

func FloorPrecise 

func FloorPrecise(args []Result) Result

FloorPrecise is an implementation of the FlOOR.PRECISE function.

func GCD 

func GCD(args []Result) Result

GCD implements the Excel GCD() function which returns the greatest common divisor of a range of numbers.

func HLookup 

func HLookup(args []Result) Result

HLookup implements the HLOOKUP function that returns a matching value from a row in an array.

func If 

func If(args []Result) Result

If is an implementation of the Excel IF() function. It takes one, two or three arguments.

func IfError 

func IfError(args []Result) Result

IfError is an implementation of the Excel IFERROR() function. It takes two arguments.

func IfNA 

func IfNA(args []Result) Result

IfNA is an implementation of the Excel IFNA() function. It takes two arguments.

func Index 

func Index(args []Result) Result

Index implements the Excel INDEX function

func Indirect 

func Indirect(ctx Context, ev Evaluator, args []Result) Result

Indirect is an implementation of the Excel INDIRECT function that returns the contents of a cell.

func Int 

func Int(args []Result) Result

Int is an implementation of the Excel INT() function that rounds a number down to an integer.

func LCM 

func LCM(args []Result) Result

LCM implements the Excel LCM() function which returns the least common multiple of a range of numbers.

func Left 

func Left(args []Result) Result

Left implements the Excel LEFT(string,[n]) function which returns the leftmost n characters.

func Len 

func Len(args []Result) Result

Len is an implementation of the Excel LEN function that returns length of a string

func Log 

func Log(args []Result) Result

Log implements the Excel LOG function which returns the log of a number. By default the result is base 10, however the second argument to the function can specify a different base.

func Lookup 

func Lookup(args []Result) Result

Lookup implements the LOOKUP function that returns a matching value from a column, or from the same index in a second column.

func Lower 

func Lower(args []Result) Result

Lower is an implementation of the Excel LOWER function that returns a lower case version of a string.

func MDeterm 

func MDeterm(args []Result) Result

MDeterm is an implementation of the Excel MDETERM which finds the determinant of a matrix.

func MakeArrayResult 

func MakeArrayResult(arr [][]Result) Result

MakeArrayResult constructs an array result (matrix).

func MakeBoolResult 

func MakeBoolResult(b bool) Result

MakeBoolResult constructs a boolean result (internally a number).

func MakeEmptyResult 

func MakeEmptyResult() Result

MakeEmptyResult is ued when parsing an empty argument.

func MakeErrorResult 

func MakeErrorResult(msg string) Result

MakeErrorResult constructs a #VALUE! error with a given extra error message. The error message is for debugging formula evaluation only and is not stored in the sheet.

func MakeErrorResultType 

func MakeErrorResultType(t ErrorType, msg string) Result

MakeErrorResultType makes an error result of a given type with a specified debug message

func MakeListResult 

func MakeListResult(list []Result) Result

MakeListResult constructs a list result.

func MakeNumberResult 

func MakeNumberResult(v float64) Result

MakeNumberResult constructs a number result.

func MakeStringResult 

func MakeStringResult(s string) Result

MakeStringResult constructs a string result.

func Max 

func Max(args []Result) Result

Max is an implementation of the Excel MAX() function.

func Median 

func Median(args []Result) Result

Median implements the MEDIAN function that returns the median of a range of values.

func Min 

func Min(args []Result) Result

Min is an implementation of the Excel MIN() function.

func Mod 

func Mod(args []Result) Result

Mod is an implementation of the Excel MOD function which returns the remainder after division. It requires two numeric argumnts.

func Mround 

func Mround(args []Result) Result

Mround is an implementation of the Excel MROUND function. It is not a generic rounding function and has some oddities to match Excel's behavior.

func Multinomial 

func Multinomial(args []Result) Result

Multinomial implements the excel MULTINOMIAL function.

func Munit 

func Munit(args []Result) Result

Munit is an implementation of the Excel MUNIT function that returns an identity matrix.

func NA 

func NA(args []Result) Result

NA is an implementation of the Excel NA() function that just returns the #N/A! error.

func Not 

func Not(args []Result) Result

Not is an implementation of the Excel NOT() function and takes a single argument.

func Odd 

func Odd(args []Result) Result

Odd is an implementation of the Excel ODD() that rounds a number to the nearest odd integer.

func Offset 

func Offset(ctx Context, ev Evaluator, args []Result) Result

func Or 

func Or(args []Result) Result

Or is an implementation of the Excel OR() function and takes a variable number of arguments.

func Pi 

func Pi(args []Result) Result

Pi is an implementation of the Excel Pi() function that just returns the Pi constant.

func Power 

func Power(args []Result) Result

Power is an implementation of the Excel POWER function that raises a number to a power. It requires two numeric arguments.

func Product 

func Product(args []Result) Result

Product is an implementation of the Excel PRODUCT() function.

func Proper 

func Proper(args []Result) Result

Proper is an implementation of the Excel PROPER function that returns a copy of the string with each word capitalized.

func Quotient 

func Quotient(args []Result) Result

Quotient is an implementation of the Excel QUOTIENT function that returns the integer portion of division.

func Radians 

func Radians(args []Result) Result

Radians is an implementation of the Excel function RADIANS() that converts degrees to radians.

func Rand 

func Rand(args []Result) Result

Rand is an implementation of the Excel RAND() function that returns random numbers in the range [0,1).

func RandBetween 

func RandBetween(args []Result) Result

RandBetween is an implementation of the Excel RANDBETWEEN() function that returns a random integer in the range specified.

func Rept 

func Rept(args []Result) Result

Rept is an implementation of the Excel REPT function that returns n copies of a string. 

func Right(args []Result) Result

Right implements the Excel RIGHT(string,[n]) function which returns the rightmost n characters.

func Roman 

func Roman(args []Result) Result

Roman is an implementation of the Excel ROMAN function that convers numbers to roman numerals in one of 5 formats.

func Round 

func Round(args []Result) Result

Round is an implementation of the Excel ROUND function that rounds a number to a specified number of digits.

func RoundDown 

func RoundDown(args []Result) Result

RoundDown is an implementation of the Excel ROUNDDOWN function that rounds a number down to a specified number of digits.

func RoundUp 

func RoundUp(args []Result) Result

RoundUp is an implementation of the Excel ROUNDUP function that rounds a number up to a specified number of digits.

func SeriesSum 

func SeriesSum(args []Result) Result

SeriesSum implements the Excel SERIESSUM function.

func Sign 

func Sign(args []Result) Result

func Sum 

func Sum(args []Result) Result

Sum is an implementation of the Excel SUM() function.

func SumProduct 

func SumProduct(args []Result) Result

SumProduct is an implementation of the Excel SUMPRODUCT() function.

func SumSquares 

func SumSquares(args []Result) Result

SumSquares is an implementation of the Excel SUMSQ() function.

func T 

func T(args []Result) Result

T is an implementation of the Excel T function that returns whether the argument is text.

func Transpose 

func Transpose(args []Result) Result

Transpose implements the TRANSPOSE function that transposes a cell range.

func Trim 

func Trim(args []Result) Result

Trim is an implementation of the Excel TRIM function that removes leading, trailing and consecutive spaces.

func True 

func True(args []Result) Result

True is an implementation of the Excel TRUE() function. It takes no arguments.

func Trunc 

func Trunc(args []Result) Result

func Unicode 

func Unicode(args []Result) Result

func Upper 

func Upper(args []Result) Result

Upper is an implementation of the Excel UPPER function that returns a upper case version of a string.

func VLookup 

func VLookup(args []Result) Result

VLookup implements the VLOOKUP function that returns a matching value from a column in an array.

func Xor 

func Xor(args []Result) Result

Xor is an implementation of the Excel XOR() function and takes a variable number of arguments. It's odd to say the least. If any argument is numeric, it returns true if the number of non-zero numeric arguments is odd and false otherwise. If no argument is numeric, it returns an error.

func (Result) AsNumber 

func (r Result) AsNumber() Result

AsNumber attempts to intepret a string cell value as a number. Upon success, it returns a new number result, upon failure it returns the original result. This is used as functions return strings that can then act like number (e.g. LEFT(1.2345,3) + LEFT(1.2345,3) = 2.4)

func (Result) AsString 

func (r Result) AsString() Result

func (Result) ListValues 

func (r Result) ListValues() []Result

ListValues converts an array to a list or returns a lists values. This is used for functions that can accept an array, but don't care about ordering to reuse the list function logic.

func (Result) String 

func (r Result) String() string

func (Result) Value 

func (r Result) Value() string

Value returns a string version of the result.

type ResultType 

type ResultType byte

ResultType is the type of the result 

const (
	ResultTypeUnknown ResultType = iota
	ResultTypeNumber
	ResultTypeString
	ResultTypeList
	ResultTypeArray
	ResultTypeError
	ResultTypeEmpty
)

ResultType constants.

func (ResultType) String 

func (i ResultType) String() string

type SheetPrefixExpr 

type SheetPrefixExpr struct {
	// contains filtered or unexported fields
}

func (SheetPrefixExpr) Eval 

func (s SheetPrefixExpr) Eval(ctx Context, ev Evaluator) Result

func (SheetPrefixExpr) Reference 

func (s SheetPrefixExpr) Reference(ctx Context, ev Evaluator) Reference

type String 

type String struct {
	// contains filtered or unexported fields
}

func (String) Eval 

func (s String) Eval(ctx Context, ev Evaluator) Result

func (String) Reference 

func (s String) Reference(ctx Context, ev Evaluator) Reference

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.