formula

package
v0.4.0 Latest Latest
Warning

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

Go to latest
Published: Sep 29, 2017 License: AGPL-3.0 Imports: 12 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 ColumnToIndex

func ColumnToIndex(col string) uint32

ColumnToIndex maps a column to a zero based index (e.g. A = 0, B = 1, AA = 26)

func IndexToColumn

func IndexToColumn(col uint32) string

IndexToColumn maps a column number to a coumn name (e.g. 0 = A, 1 = B, 26 = AA)

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
}

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 added in v0.4.0

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 added in v0.4.0

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 added in v0.4.0

func Count(args []Result) Result

Count implements the COUNT function.

func CountBlank added in v0.4.0

func CountBlank(args []Result) Result

CountBlank implements the COUNTBLANK function.

func Counta added in v0.4.0

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 added in v0.4.0

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 added in v0.4.0

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 added in v0.4.0

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 added in v0.4.0

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 added in v0.4.0

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 added in v0.4.0

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

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL