diff options
Diffstat (limited to 'calc.go')
| -rw-r--r-- | calc.go | 296 |
1 files changed, 232 insertions, 64 deletions
@@ -93,21 +93,36 @@ type formulaArg struct { // formulaFuncs is the type of the formula functions. type formulaFuncs struct{} +// tokenPriority defined basic arithmetic operator priority. +var tokenPriority = map[string]int{ + "^": 5, + "*": 4, + "/": 4, + "+": 3, + "-": 3, + "=": 2, + "<": 2, + "<=": 2, + ">": 2, + ">=": 2, + "&": 1, +} + // CalcCellValue provides a function to get calculated cell value. This // feature is currently in working processing. Array formula, table formula // and some other formulas are not supported currently. // // Supported formulas: // -// ABS, ACOS, ACOSH, ACOT, ACOTH, ARABIC, ASIN, ASINH, ATAN2, ATANH, BASE, -// CEILING, CEILING.MATH, CEILING.PRECISE, COMBIN, COMBINA, COS, COSH, COT, -// COTH, COUNTA, CSC, CSCH, DECIMAL, DEGREES, EVEN, EXP, FACT, FACTDOUBLE, -// FLOOR, FLOOR.MATH, FLOOR.PRECISE, GCD, INT, ISBLANK, ISERR, ISERROR, -// ISEVEN, ISNA, ISNONTEXT, ISNUMBER, ISO.CEILING, ISODD, LCM, LN, LOG, -// LOG10, MDETERM, MEDIAN, MOD, MROUND, MULTINOMIAL, MUNIT, NA, ODD, PI, -// POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANDBETWEEN, ROUND, ROUNDDOWN, -// ROUNDUP, SEC, SECH, SIGN, SIN, SINH, SQRT, SQRTPI, SUM, SUMIF, SUMSQ, -// TAN, TANH, TRUNC +// ABS, ACOS, ACOSH, ACOT, ACOTH, AND, ARABIC, ASIN, ASINH, ATAN2, ATANH, +// BASE, CEILING, CEILING.MATH, CEILING.PRECISE, COMBIN, COMBINA, COS, +// COSH, COT, COTH, COUNTA, CSC, CSCH, DECIMAL, DEGREES, EVEN, EXP, FACT, +// FACTDOUBLE, FLOOR, FLOOR.MATH, FLOOR.PRECISE, GCD, INT, ISBLANK, ISERR, +// ISERROR, ISEVEN, ISNA, ISNONTEXT, ISNUMBER, ISO.CEILING, ISODD, LCM, +// LN, LOG, LOG10, MDETERM, MEDIAN, MOD, MROUND, MULTINOMIAL, MUNIT, NA, +// ODD, OR, PI, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANDBETWEEN, +// ROUND, ROUNDDOWN, ROUNDUP, SEC, SECH, SIGN, SIN, SINH, SQRT, SQRTPI, +// SUM, SUMIF, SUMSQ, TAN, TANH, TRUNC // func (f *File) CalcCellValue(sheet, cell string) (result string, err error) { var ( @@ -131,15 +146,9 @@ func (f *File) CalcCellValue(sheet, cell string) (result string, err error) { // getPriority calculate arithmetic operator priority. func getPriority(token efp.Token) (pri int) { - var priority = map[string]int{ - "*": 2, - "/": 2, - "+": 1, - "-": 1, - } - pri, _ = priority[token.TValue] + pri, _ = tokenPriority[token.TValue] if token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix { - pri = 3 + pri = 6 } if token.TSubType == efp.TokenSubTypeStart && token.TType == efp.TokenTypeSubexpression { // ( pri = 0 @@ -306,18 +315,96 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) return opdStack.Peek().(efp.Token), err } -// calcAdd evaluate addition arithmetic operations. -func calcAdd(opdStack *Stack) error { - if opdStack.Len() < 2 { - return errors.New("formula not valid") +// calcPow evaluate exponentiation arithmetic operations. +func calcPow(rOpd, lOpd string, opdStack *Stack) error { + lOpdVal, err := strconv.ParseFloat(lOpd, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd, 64) + if err != nil { + return err + } + result := math.Pow(lOpdVal, rOpdVal) + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcEq evaluate equal arithmetic operations. +func calcEq(rOpd, lOpd string, opdStack *Stack) error { + opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpd == lOpd)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcL evaluate less than arithmetic operations. +func calcL(rOpd, lOpd string, opdStack *Stack) error { + lOpdVal, err := strconv.ParseFloat(lOpd, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd, 64) + if err != nil { + return err + } + opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpdVal > lOpdVal)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcLe evaluate less than or equal arithmetic operations. +func calcLe(rOpd, lOpd string, opdStack *Stack) error { + lOpdVal, err := strconv.ParseFloat(lOpd, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd, 64) + if err != nil { + return err } - rOpd := opdStack.Pop().(efp.Token) - lOpd := opdStack.Pop().(efp.Token) - lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) + opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpdVal >= lOpdVal)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcG evaluate greater than or equal arithmetic operations. +func calcG(rOpd, lOpd string, opdStack *Stack) error { + lOpdVal, err := strconv.ParseFloat(lOpd, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd, 64) + if err != nil { + return err + } + opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpdVal < lOpdVal)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcGe evaluate greater than or equal arithmetic operations. +func calcGe(rOpd, lOpd string, opdStack *Stack) error { + lOpdVal, err := strconv.ParseFloat(lOpd, 64) if err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + rOpdVal, err := strconv.ParseFloat(rOpd, 64) + if err != nil { + return err + } + opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpdVal <= lOpdVal)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcSplice evaluate splice '&' operations. +func calcSplice(rOpd, lOpd string, opdStack *Stack) error { + opdStack.Push(efp.Token{TValue: lOpd + rOpd, TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcAdd evaluate addition arithmetic operations. +func calcAdd(rOpd, lOpd string, opdStack *Stack) error { + lOpdVal, err := strconv.ParseFloat(lOpd, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd, 64) if err != nil { return err } @@ -327,17 +414,12 @@ func calcAdd(opdStack *Stack) error { } // calcSubtract evaluate subtraction arithmetic operations. -func calcSubtract(opdStack *Stack) error { - if opdStack.Len() < 2 { - return errors.New("formula not valid") - } - rOpd := opdStack.Pop().(efp.Token) - lOpd := opdStack.Pop().(efp.Token) - lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) +func calcSubtract(rOpd, lOpd string, opdStack *Stack) error { + lOpdVal, err := strconv.ParseFloat(lOpd, 64) if err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + rOpdVal, err := strconv.ParseFloat(rOpd, 64) if err != nil { return err } @@ -347,17 +429,12 @@ func calcSubtract(opdStack *Stack) error { } // calcMultiply evaluate multiplication arithmetic operations. -func calcMultiply(opdStack *Stack) error { - if opdStack.Len() < 2 { - return errors.New("formula not valid") - } - rOpd := opdStack.Pop().(efp.Token) - lOpd := opdStack.Pop().(efp.Token) - lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) +func calcMultiply(rOpd, lOpd string, opdStack *Stack) error { + lOpdVal, err := strconv.ParseFloat(lOpd, 64) if err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + rOpdVal, err := strconv.ParseFloat(rOpd, 64) if err != nil { return err } @@ -366,18 +443,13 @@ func calcMultiply(opdStack *Stack) error { return nil } -// calcDivide evaluate division arithmetic operations. -func calcDivide(opdStack *Stack) error { - if opdStack.Len() < 2 { - return errors.New("formula not valid") - } - rOpd := opdStack.Pop().(efp.Token) - lOpd := opdStack.Pop().(efp.Token) - lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) +// calcDiv evaluate division arithmetic operations. +func calcDiv(rOpd, lOpd string, opdStack *Stack) error { + lOpdVal, err := strconv.ParseFloat(lOpd, 64) if err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + rOpdVal, err := strconv.ParseFloat(rOpd, 64) if err != nil { return err } @@ -403,24 +475,36 @@ func calculate(opdStack *Stack, opt efp.Token) error { result := 0 - opdVal opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } - - if opt.TValue == "+" { - if err := calcAdd(opdStack); err != nil { - return err - } + tokenCalcFunc := map[string]func(rOpd, lOpd string, opdStack *Stack) error{ + "^": calcPow, + "*": calcMultiply, + "/": calcDiv, + "+": calcAdd, + "=": calcEq, + "<": calcL, + "<=": calcLe, + ">": calcG, + ">=": calcGe, + "&": calcSplice, } if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorInfix { - if err := calcSubtract(opdStack); err != nil { - return err + if opdStack.Len() < 2 { + return errors.New("formula not valid") } - } - if opt.TValue == "*" { - if err := calcMultiply(opdStack); err != nil { + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + if err := calcSubtract(rOpd.TValue, lOpd.TValue, opdStack); err != nil { return err } } - if opt.TValue == "/" { - if err := calcDivide(opdStack); err != nil { + fn, ok := tokenCalcFunc[opt.TValue] + if ok { + if opdStack.Len() < 2 { + return errors.New("formula not valid") + } + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + if err := fn(rOpd.TValue, lOpd.TValue, opdStack); err != nil { return err } } @@ -459,8 +543,8 @@ func (f *File) parseOperatorPrefixToken(optStack, opdStack *Stack, token efp.Tok // isOperatorPrefixToken determine if the token is parse operator prefix // token. func isOperatorPrefixToken(token efp.Token) bool { - if (token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix) || - token.TValue == "+" || token.TValue == "-" || token.TValue == "*" || token.TValue == "/" { + _, ok := tokenPriority[token.TValue] + if (token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix) || ok { return true } return false @@ -3140,3 +3224,87 @@ func (fn *formulaFuncs) NA(argsList *list.List) (result string, err error) { result = formulaErrorNA return } + +// Logical Functions + +// AND function tests a number of supplied conditions and returns TRUE or +// FALSE. +func (fn *formulaFuncs) AND(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("AND requires at least 1 argument") + return + } + if argsList.Len() > 30 { + err = errors.New("AND accepts at most 30 arguments") + return + } + var and = true + var val float64 + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg) + switch token.Type { + case ArgUnknown: + continue + case ArgString: + if token.String == "TRUE" { + continue + } + if token.String == "FALSE" { + result = token.String + return + } + if val, err = strconv.ParseFloat(token.String, 64); err != nil { + err = errors.New(formulaErrorVALUE) + return + } + and = and && (val != 0) + case ArgMatrix: + // TODO + err = errors.New(formulaErrorVALUE) + return + } + } + result = strings.ToUpper(strconv.FormatBool(and)) + return +} + +// OR function tests a number of supplied conditions and returns either TRUE +// or FALSE. +func (fn *formulaFuncs) OR(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("OR requires at least 1 argument") + return + } + if argsList.Len() > 30 { + err = errors.New("OR accepts at most 30 arguments") + return + } + var or bool + var val float64 + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg) + switch token.Type { + case ArgUnknown: + continue + case ArgString: + if token.String == "FALSE" { + continue + } + if token.String == "TRUE" { + or = true + continue + } + if val, err = strconv.ParseFloat(token.String, 64); err != nil { + err = errors.New(formulaErrorVALUE) + return + } + or = val != 0 + case ArgMatrix: + // TODO + err = errors.New(formulaErrorVALUE) + return + } + } + result = strings.ToUpper(strconv.FormatBool(or)) + return +} |