From 48fc4c08a2a80f7826d20bf3fd5a018f8e6f3185 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 3 May 2020 18:44:43 +0800 Subject: init formula calculation engine, ref #65 and #599 --- calc.go | 605 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 605 insertions(+) create mode 100644 calc.go (limited to 'calc.go') diff --git a/calc.go b/calc.go new file mode 100644 index 0000000..d962fd4 --- /dev/null +++ b/calc.go @@ -0,0 +1,605 @@ +// Copyright 2016 - 2020 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX / XLSM / XLTM files. Supports reading and writing +// spreadsheet documents generated by Microsoft Exce™ 2007 and later. Supports +// complex components by high compatibility, and provided streaming API for +// generating or reading data from a worksheet with huge amounts of data. This +// library needs Go version 1.10 or later. + +package excelize + +import ( + "container/list" + "errors" + "fmt" + "math" + "reflect" + "strconv" + "strings" + + "github.com/xuri/efp" +) + +// Excel formula errors +const ( + formulaErrorDIV = "#DIV/0!" + formulaErrorNAME = "#NAME?" + formulaErrorNA = "#N/A" + formulaErrorNUM = "#NUM!" + formulaErrorVALUE = "#VALUE!" + formulaErrorREF = "#REF!" + formulaErrorNULL = "#NULL" + formulaErrorSPILL = "#SPILL!" + formulaErrorCALC = "#CALC!" + formulaErrorGETTINGDATA = "#GETTING_DATA" +) + +// cellRef defines the structure of a cell reference +type cellRef struct { + Col int + Row int + Sheet string +} + +// cellRef defines the structure of a cell range +type cellRange struct { + From cellRef + To cellRef +} + +type formulaFuncs struct{} + +// CalcCellValue provides a function to get calculated cell value. This +// feature is currently in beta. Array formula, table formula and some other +// formulas are not supported currently. +func (f *File) CalcCellValue(sheet, cell string) (result string, err error) { + var ( + formula string + token efp.Token + ) + if formula, err = f.GetCellFormula(sheet, cell); err != nil { + return + } + ps := efp.ExcelParser() + tokens := ps.Parse(formula) + if tokens == nil { + return + } + if token, err = f.evalInfixExp(sheet, tokens); err != nil { + return + } + result = token.TValue + return +} + +// 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] + if token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix { + pri = 3 + } + if token.TSubType == efp.TokenSubTypeStart && token.TType == efp.TokenTypeSubexpression { // ( + pri = 0 + } + return +} + +// evalInfixExp evaluate syntax analysis by given infix expression after +// lexical analysis. Evaluate an infix expression containing formulas by +// stacks: +// +// opd - Operand +// opt - Operator +// opf - Operation formula +// opfd - Operand of the operation formula +// opft - Operator of the operation formula +// args - Arguments of the operation formula +// +func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) { + var err error + opdStack, optStack, opfStack, opfdStack, opftStack, argsStack := NewStack(), NewStack(), NewStack(), NewStack(), NewStack(), NewStack() + for i := 0; i < len(tokens); i++ { + token := tokens[i] + + // out of function stack + if opfStack.Len() == 0 { + if err = f.parseToken(sheet, token, opdStack, optStack); err != nil { + return efp.Token{}, err + } + } + + // function start + if token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStart { + opfStack.Push(token) + continue + } + + // in function stack, walk 2 token at once + if opfStack.Len() > 0 { + var nextToken efp.Token + if i+1 < len(tokens) { + nextToken = tokens[i+1] + } + + // current token is args or range, skip next token, order required: parse reference first + if token.TSubType == efp.TokenSubTypeRange { + if !opftStack.Empty() { + // parse reference: must reference at here + result, err := f.parseReference(sheet, token.TValue) + if err != nil { + return efp.Token{TValue: formulaErrorNAME}, err + } + if len(result) != 1 { + return efp.Token{}, errors.New(formulaErrorVALUE) + } + opfdStack.Push(efp.Token{ + TType: efp.TokenTypeOperand, + TSubType: efp.TokenSubTypeNumber, + TValue: result[0], + }) + continue + } + if nextToken.TType == efp.TokenTypeArgument || nextToken.TType == efp.TokenTypeFunction { + // parse reference: reference or range at here + result, err := f.parseReference(sheet, token.TValue) + if err != nil { + return efp.Token{TValue: formulaErrorNAME}, err + } + for _, val := range result { + argsStack.Push(efp.Token{ + TType: efp.TokenTypeOperand, + TSubType: efp.TokenSubTypeNumber, + TValue: val, + }) + } + if len(result) == 0 { + return efp.Token{}, errors.New(formulaErrorVALUE) + } + continue + } + } + + // check current token is opft + if err = f.parseToken(sheet, token, opfdStack, opftStack); err != nil { + return efp.Token{}, err + } + + // current token is arg + if token.TType == efp.TokenTypeArgument { + for !opftStack.Empty() { + // calculate trigger + topOpt := opftStack.Peek().(efp.Token) + if err := calculate(opfdStack, topOpt); err != nil { + return efp.Token{}, err + } + opftStack.Pop() + } + if !opfdStack.Empty() { + argsStack.Push(opfdStack.Pop()) + } + continue + } + + // current token is function stop + if token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStop { + for !opftStack.Empty() { + // calculate trigger + topOpt := opftStack.Peek().(efp.Token) + if err := calculate(opfdStack, topOpt); err != nil { + return efp.Token{}, err + } + opftStack.Pop() + } + + // push opfd to args + if opfdStack.Len() > 0 { + argsStack.Push(opfdStack.Pop()) + } + // call formula function to evaluate + result, err := callFuncByName(&formulaFuncs{}, opfStack.Peek().(efp.Token).TValue, []reflect.Value{reflect.ValueOf(argsStack)}) + if err != nil { + return efp.Token{}, err + } + opfStack.Pop() + if opfStack.Len() > 0 { // still in function stack + opfdStack.Push(efp.Token{TValue: result, TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + } else { + opdStack.Push(efp.Token{TValue: result, TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + } + } + } + } + for optStack.Len() != 0 { + topOpt := optStack.Peek().(efp.Token) + if err = calculate(opdStack, topOpt); err != nil { + return efp.Token{}, err + } + optStack.Pop() + } + return opdStack.Peek().(efp.Token), err +} + +// calculate evaluate basic arithmetic operations. +func calculate(opdStack *Stack, opt efp.Token) error { + if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorPrefix { + opd := opdStack.Pop().(efp.Token) + opdVal, err := strconv.ParseFloat(opd.TValue, 64) + if err != nil { + return err + } + result := 0 - opdVal + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + } + if opt.TValue == "+" { + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal + rOpdVal + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + } + if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorInfix { + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal - rOpdVal + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + } + if opt.TValue == "*" { + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal * rOpdVal + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + } + if opt.TValue == "/" { + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal / rOpdVal + if rOpdVal == 0 { + return errors.New(formulaErrorDIV) + } + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + } + return nil +} + +// parseToken parse basic arithmetic operator priority and evaluate based on +// operators and operands. +func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Stack) error { + // parse reference: must reference at here + if token.TSubType == efp.TokenSubTypeRange { + result, err := f.parseReference(sheet, token.TValue) + if err != nil { + return errors.New(formulaErrorNAME) + } + if len(result) != 1 { + return errors.New(formulaErrorVALUE) + } + token.TValue = result[0] + token.TType = efp.TokenTypeOperand + token.TSubType = efp.TokenSubTypeNumber + } + if (token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix) || token.TValue == "+" || token.TValue == "-" || token.TValue == "*" || token.TValue == "/" { + if optStack.Len() == 0 { + optStack.Push(token) + } else { + tokenPriority := getPriority(token) + topOpt := optStack.Peek().(efp.Token) + topOptPriority := getPriority(topOpt) + if tokenPriority > topOptPriority { + optStack.Push(token) + } else { + for tokenPriority <= topOptPriority { + optStack.Pop() + if err := calculate(opdStack, topOpt); err != nil { + return err + } + if optStack.Len() > 0 { + topOpt = optStack.Peek().(efp.Token) + topOptPriority = getPriority(topOpt) + continue + } + break + } + optStack.Push(token) + } + } + } + if token.TType == efp.TokenTypeSubexpression && token.TSubType == efp.TokenSubTypeStart { // ( + optStack.Push(token) + } + if token.TType == efp.TokenTypeSubexpression && token.TSubType == efp.TokenSubTypeStop { // ) + for optStack.Peek().(efp.Token).TSubType != efp.TokenSubTypeStart && optStack.Peek().(efp.Token).TType != efp.TokenTypeSubexpression { // != ( + topOpt := optStack.Peek().(efp.Token) + if err := calculate(opdStack, topOpt); err != nil { + return err + } + optStack.Pop() + } + optStack.Pop() + } + // opd + if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeNumber { + opdStack.Push(token) + } + return nil +} + +// parseReference parse reference and extract values by given reference +// characters and default sheet name. +func (f *File) parseReference(sheet, reference string) (result []string, err error) { + reference = strings.Replace(reference, "$", "", -1) + refs, cellRanges, cellRefs := list.New(), list.New(), list.New() + for _, ref := range strings.Split(reference, ":") { + tokens := strings.Split(ref, "!") + cr := cellRef{} + if len(tokens) == 2 { // have a worksheet name + cr.Sheet = tokens[0] + if cr.Col, cr.Row, err = CellNameToCoordinates(tokens[1]); err != nil { + return + } + if refs.Len() > 0 { + e := refs.Back() + cellRefs.PushBack(e.Value.(cellRef)) + refs.Remove(e) + } + refs.PushBack(cr) + continue + } + if cr.Col, cr.Row, err = CellNameToCoordinates(tokens[0]); err != nil { + return + } + e := refs.Back() + if e == nil { + cr.Sheet = sheet + refs.PushBack(cr) + continue + } + cellRanges.PushBack(cellRange{ + From: e.Value.(cellRef), + To: cr, + }) + refs.Remove(e) + } + if refs.Len() > 0 { + e := refs.Back() + cellRefs.PushBack(e.Value.(cellRef)) + refs.Remove(e) + } + + result, err = f.rangeResolver(cellRefs, cellRanges) + return +} + +// rangeResolver extract value as string from given reference and range list. +// This function will not ignore the empty cell. Note that the result of 3D +// range references may be different from Excel in some cases, for example, +// A1:A2:A2:B3 in Excel will include B2, but we wont. +func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, err error) { + filter := map[string]string{} + // extract value from ranges + for temp := cellRanges.Front(); temp != nil; temp = temp.Next() { + cr := temp.Value.(cellRange) + if cr.From.Sheet != cr.To.Sheet { + err = errors.New(formulaErrorVALUE) + } + rng := []int{cr.From.Col, cr.From.Row, cr.To.Col, cr.To.Row} + sortCoordinates(rng) + for col := rng[0]; col <= rng[2]; col++ { + for row := rng[1]; row <= rng[3]; row++ { + var cell string + if cell, err = CoordinatesToCellName(col, row); err != nil { + return + } + if filter[cell], err = f.GetCellValue(cr.From.Sheet, cell); err != nil { + return + } + } + } + } + // extract value from references + for temp := cellRefs.Front(); temp != nil; temp = temp.Next() { + cr := temp.Value.(cellRef) + var cell string + if cell, err = CoordinatesToCellName(cr.Col, cr.Row); err != nil { + return + } + if filter[cell], err = f.GetCellValue(cr.Sheet, cell); err != nil { + return + } + } + + for _, val := range filter { + result = append(result, val) + } + return +} + +// callFuncByName calls the no error or only error return function with +// reflect by given receiver, name and parameters. +func callFuncByName(receiver interface{}, name string, params []reflect.Value) (result string, err error) { + function := reflect.ValueOf(receiver).MethodByName(name) + if function.IsValid() { + rt := function.Call(params) + if len(rt) == 0 { + return + } + if !rt[1].IsNil() { + err = rt[1].Interface().(error) + return + } + result = rt[0].Interface().(string) + return + } + err = fmt.Errorf("not support %s function", name) + return +} + +// Math and Trigonometric functions + +// SUM function adds together a supplied set of numbers and returns the sum of +// these values. The syntax of the function is: +// +// SUM(number1,[number2],...) +// +func (fn *formulaFuncs) SUM(argsStack *Stack) (result string, err error) { + var val float64 + var sum float64 + for !argsStack.Empty() { + token := argsStack.Pop().(efp.Token) + if token.TValue == "" { + continue + } + val, err = strconv.ParseFloat(token.TValue, 64) + if err != nil { + return + } + sum += val + } + result = fmt.Sprintf("%g", sum) + return +} + +// PRODUCT function returns the product (multiplication) of a supplied set of numerical values. +// The syntax of the function is: +// +// PRODUCT(number1,[number2],...) +// +func (fn *formulaFuncs) PRODUCT(argsStack *Stack) (result string, err error) { + var ( + val float64 + product float64 = 1 + ) + for !argsStack.Empty() { + token := argsStack.Pop().(efp.Token) + if token.TValue == "" { + continue + } + val, err = strconv.ParseFloat(token.TValue, 64) + if err != nil { + return + } + product = product * val + } + result = fmt.Sprintf("%g", product) + return +} + +// PRODUCT function calculates a given number, raised to a supplied power. +// The syntax of the function is: +// +// POWER(number,power) +// +func (fn *formulaFuncs) POWER(argsStack *Stack) (result string, err error) { + if argsStack.Len() != 2 { + err = errors.New("POWER requires 2 numeric arguments") + return + } + var x, y float64 + y, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + if err != nil { + return + } + x, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + if err != nil { + return + } + if x == 0 && y == 0 { + err = errors.New(formulaErrorNUM) + return + } + if x == 0 && y < 0 { + err = errors.New(formulaErrorDIV) + return + } + result = fmt.Sprintf("%g", math.Pow(x, y)) + return +} + +// SQRT function calculates the positive square root of a supplied number. +// The syntax of the function is: +// +// SQRT(number) +// +func (fn *formulaFuncs) SQRT(argsStack *Stack) (result string, err error) { + if argsStack.Len() != 1 { + err = errors.New("SQRT requires 1 numeric arguments") + return + } + var val float64 + val, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + if err != nil { + return + } + if val < 0 { + err = errors.New(formulaErrorNUM) + return + } + result = fmt.Sprintf("%g", math.Sqrt(val)) + return +} + +// QUOTIENT function returns the integer portion of a division between two supplied numbers. +// The syntax of the function is: +// +// QUOTIENT(numerator,denominator) +// +func (fn *formulaFuncs) QUOTIENT(argsStack *Stack) (result string, err error) { + if argsStack.Len() != 2 { + err = errors.New("QUOTIENT requires 2 numeric arguments") + return + } + var x, y float64 + y, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + if err != nil { + return + } + x, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + if err != nil { + return + } + if y == 0 { + err = errors.New(formulaErrorDIV) + return + } + result = fmt.Sprintf("%g", math.Trunc(x/y)) + return +} -- cgit v1.2.1 From bdf05386408d439fda7cd495105f59cb2eaf8099 Mon Sep 17 00:00:00 2001 From: xuri Date: Mon, 4 May 2020 13:40:04 +0800 Subject: fn: ABS, GCD, LCM, POWER, PRODUCT, SIGN, SQRT, SUM, QUOTIENT --- calc.go | 209 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++------- 1 file changed, 188 insertions(+), 21 deletions(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index d962fd4..5ebdcf7 100644 --- a/calc.go +++ b/calc.go @@ -412,7 +412,7 @@ func (f *File) parseReference(sheet, reference string) (result []string, err err // rangeResolver extract value as string from given reference and range list. // This function will not ignore the empty cell. Note that the result of 3D // range references may be different from Excel in some cases, for example, -// A1:A2:A2:B3 in Excel will include B2, but we wont. +// A1:A2:A2:B3 in Excel will include B1, but we wont. func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, err error) { filter := map[string]string{} // extract value from ranges @@ -475,14 +475,58 @@ func callFuncByName(receiver interface{}, name string, params []reflect.Value) ( // Math and Trigonometric functions -// SUM function adds together a supplied set of numbers and returns the sum of -// these values. The syntax of the function is: +// ABS function returns the absolute value of any supplied number. The syntax +// of the function is: // -// SUM(number1,[number2],...) +// ABS(number) // -func (fn *formulaFuncs) SUM(argsStack *Stack) (result string, err error) { +func (fn *formulaFuncs) ABS(argsStack *Stack) (result string, err error) { + if argsStack.Len() != 1 { + err = errors.New("ABS requires 1 numeric arguments") + return + } var val float64 - var sum float64 + val, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + if err != nil { + return + } + result = fmt.Sprintf("%g", math.Abs(val)) + return +} + +// gcd returns the greatest common divisor of two supplied integers. +func gcd(x, y float64) float64 { + x, y = math.Trunc(x), math.Trunc(y) + if x == 0 { + return y + } + if y == 0 { + return x + } + for x != y { + if x > y { + x = x - y + } else { + y = y - x + } + } + return x +} + +// GCD function returns the greatest common divisor of two or more supplied +// integers.The syntax of the function is: +// +// GCD(number1,[number2],...) +// +func (fn *formulaFuncs) GCD(argsStack *Stack) (result string, err error) { + if argsStack.Len() == 0 { + err = errors.New("GCD requires at least 1 argument") + return + } + var ( + val float64 + nums = []float64{} + ) for !argsStack.Empty() { token := argsStack.Pop().(efp.Token) if token.TValue == "" { @@ -492,21 +536,51 @@ func (fn *formulaFuncs) SUM(argsStack *Stack) (result string, err error) { if err != nil { return } - sum += val + nums = append(nums, val) } - result = fmt.Sprintf("%g", sum) + if nums[0] < 0 { + err = errors.New("GCD only accepts positive arguments") + return + } + if len(nums) == 1 { + result = fmt.Sprintf("%g", nums[0]) + return + } + cd := nums[0] + for i := 1; i < len(nums); i++ { + if nums[i] < 0 { + err = errors.New("GCD only accepts positive arguments") + return + } + cd = gcd(cd, nums[i]) + } + result = fmt.Sprintf("%g", cd) return } -// PRODUCT function returns the product (multiplication) of a supplied set of numerical values. -// The syntax of the function is: +// lcm returns the least common multiple of two supplied integers. +func lcm(a, b float64) float64 { + a = math.Trunc(a) + b = math.Trunc(b) + if a == 0 && b == 0 { + return 0 + } + return a * b / gcd(a, b) +} + +// LCM function returns the least common multiple of two or more supplied +// integers. The syntax of the function is: // -// PRODUCT(number1,[number2],...) +// LCM(number1,[number2],...) // -func (fn *formulaFuncs) PRODUCT(argsStack *Stack) (result string, err error) { +func (fn *formulaFuncs) LCM(argsStack *Stack) (result string, err error) { + if argsStack.Len() == 0 { + err = errors.New("LCM requires at least 1 argument") + return + } var ( - val float64 - product float64 = 1 + val float64 + nums = []float64{} ) for !argsStack.Empty() { token := argsStack.Pop().(efp.Token) @@ -517,13 +591,29 @@ func (fn *formulaFuncs) PRODUCT(argsStack *Stack) (result string, err error) { if err != nil { return } - product = product * val + nums = append(nums, val) } - result = fmt.Sprintf("%g", product) + if nums[0] < 0 { + err = errors.New("LCM only accepts positive arguments") + return + } + if len(nums) == 1 { + result = fmt.Sprintf("%g", nums[0]) + return + } + cm := nums[0] + for i := 1; i < len(nums); i++ { + if nums[i] < 0 { + err = errors.New("LCM only accepts positive arguments") + return + } + cm = lcm(cm, nums[i]) + } + result = fmt.Sprintf("%g", cm) return } -// PRODUCT function calculates a given number, raised to a supplied power. +// POWER function calculates a given number, raised to a supplied power. // The syntax of the function is: // // POWER(number,power) @@ -554,8 +644,62 @@ func (fn *formulaFuncs) POWER(argsStack *Stack) (result string, err error) { return } -// SQRT function calculates the positive square root of a supplied number. -// The syntax of the function is: +// PRODUCT function returns the product (multiplication) of a supplied set of +// numerical values. The syntax of the function is: +// +// PRODUCT(number1,[number2],...) +// +func (fn *formulaFuncs) PRODUCT(argsStack *Stack) (result string, err error) { + var ( + val float64 + product float64 = 1 + ) + for !argsStack.Empty() { + token := argsStack.Pop().(efp.Token) + if token.TValue == "" { + continue + } + val, err = strconv.ParseFloat(token.TValue, 64) + if err != nil { + return + } + product = product * val + } + result = fmt.Sprintf("%g", product) + return +} + +// SIGN function returns the arithmetic sign (+1, -1 or 0) of a supplied +// number. I.e. if the number is positive, the Sign function returns +1, if +// the number is negative, the function returns -1 and if the number is 0 +// (zero), the function returns 0. The syntax of the function is: +// +// SIGN(number) +// +func (fn *formulaFuncs) SIGN(argsStack *Stack) (result string, err error) { + if argsStack.Len() != 1 { + err = errors.New("SIGN requires 1 numeric arguments") + return + } + var val float64 + val, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + if err != nil { + return + } + if val < 0 { + result = "-1" + return + } + if val > 0 { + result = "1" + return + } + result = "0" + return +} + +// SQRT function calculates the positive square root of a supplied number. The +// syntax of the function is: // // SQRT(number) // @@ -577,8 +721,31 @@ func (fn *formulaFuncs) SQRT(argsStack *Stack) (result string, err error) { return } -// QUOTIENT function returns the integer portion of a division between two supplied numbers. -// The syntax of the function is: +// SUM function adds together a supplied set of numbers and returns the sum of +// these values. The syntax of the function is: +// +// SUM(number1,[number2],...) +// +func (fn *formulaFuncs) SUM(argsStack *Stack) (result string, err error) { + var val float64 + var sum float64 + for !argsStack.Empty() { + token := argsStack.Pop().(efp.Token) + if token.TValue == "" { + continue + } + val, err = strconv.ParseFloat(token.TValue, 64) + if err != nil { + return + } + sum += val + } + result = fmt.Sprintf("%g", sum) + return +} + +// QUOTIENT function returns the integer portion of a division between two +// supplied numbers. The syntax of the function is: // // QUOTIENT(numerator,denominator) // -- cgit v1.2.1 From 789adf9202b4bc04e74ea8fe72138f1942b2cc0c Mon Sep 17 00:00:00 2001 From: xuri Date: Mon, 4 May 2020 18:18:05 +0800 Subject: fn: ACOS, ACOSH, ACOT, ACOTH, ARABIC, ASIN, ASINH, ATANH, ATAN2, BASE --- calc.go | 362 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++------- 1 file changed, 325 insertions(+), 37 deletions(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index 5ebdcf7..7c912eb 100644 --- a/calc.go +++ b/calc.go @@ -102,11 +102,17 @@ func getPriority(token efp.Token) (pri int) { // opf - Operation formula // opfd - Operand of the operation formula // opft - Operator of the operation formula +// +// Evaluate arguments of the operation formula by list: +// // args - Arguments of the operation formula // +// TODO: handle subtypes: Nothing, Text, Logical, Error, Concatenation, Intersection, Union +// func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) { var err error - opdStack, optStack, opfStack, opfdStack, opftStack, argsStack := NewStack(), NewStack(), NewStack(), NewStack(), NewStack(), NewStack() + opdStack, optStack, opfStack, opfdStack, opftStack := NewStack(), NewStack(), NewStack(), NewStack(), NewStack() + argsList := list.New() for i := 0; i < len(tokens); i++ { token := tokens[i] @@ -155,7 +161,7 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) return efp.Token{TValue: formulaErrorNAME}, err } for _, val := range result { - argsStack.Push(efp.Token{ + argsList.PushBack(efp.Token{ TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber, TValue: val, @@ -184,11 +190,20 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) opftStack.Pop() } if !opfdStack.Empty() { - argsStack.Push(opfdStack.Pop()) + argsList.PushBack(opfdStack.Pop()) } continue } + // current token is logical + if token.TType == efp.OperatorsInfix && token.TSubType == efp.TokenSubTypeLogical { + } + + // current token is text + if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText { + argsList.PushBack(token) + } + // current token is function stop if token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStop { for !opftStack.Empty() { @@ -202,13 +217,14 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) // push opfd to args if opfdStack.Len() > 0 { - argsStack.Push(opfdStack.Pop()) + argsList.PushBack(opfdStack.Pop()) } // call formula function to evaluate - result, err := callFuncByName(&formulaFuncs{}, opfStack.Peek().(efp.Token).TValue, []reflect.Value{reflect.ValueOf(argsStack)}) + result, err := callFuncByName(&formulaFuncs{}, strings.ReplaceAll(opfStack.Peek().(efp.Token).TValue, "_xlfn.", ""), []reflect.Value{reflect.ValueOf(argsList)}) if err != nil { return efp.Token{}, err } + argsList.Init() opfStack.Pop() if opfStack.Len() > 0 { // still in function stack opfdStack.Push(efp.Token{TValue: result, TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) @@ -480,13 +496,13 @@ func callFuncByName(receiver interface{}, name string, params []reflect.Value) ( // // ABS(number) // -func (fn *formulaFuncs) ABS(argsStack *Stack) (result string, err error) { - if argsStack.Len() != 1 { +func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { err = errors.New("ABS requires 1 numeric arguments") return } var val float64 - val, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) if err != nil { return } @@ -494,6 +510,236 @@ func (fn *formulaFuncs) ABS(argsStack *Stack) (result string, err error) { return } +// ACOS function calculates the arccosine (i.e. the inverse cosine) of a given +// number, and returns an angle, in radians, between 0 and π. The syntax of +// the function is: +// +// ACOS(number) +// +func (fn *formulaFuncs) ACOS(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ACOS requires 1 numeric arguments") + return + } + var val float64 + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + result = fmt.Sprintf("%g", math.Acos(val)) + return +} + +// ACOSH function calculates the inverse hyperbolic cosine of a supplied number. +// of the function is: +// +// ACOSH(number) +// +func (fn *formulaFuncs) ACOSH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ACOSH requires 1 numeric arguments") + return + } + var val float64 + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + result = fmt.Sprintf("%g", math.Acosh(val)) + return +} + +// ACOT function calculates the arccotangent (i.e. the inverse cotangent) of a +// given number, and returns an angle, in radians, between 0 and π. The syntax +// of the function is: +// +// ACOT(number) +// +func (fn *formulaFuncs) ACOT(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ACOT requires 1 numeric arguments") + return + } + var val float64 + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + result = fmt.Sprintf("%g", math.Pi/2-math.Atan(val)) + return +} + +// ACOTH function calculates the hyperbolic arccotangent (coth) of a supplied +// value. The syntax of the function is: +// +// ACOTH(number) +// +func (fn *formulaFuncs) ACOTH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ACOTH requires 1 numeric arguments") + return + } + var val float64 + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + result = fmt.Sprintf("%g", math.Atanh(1/val)) + return +} + +// ARABIC function converts a Roman numeral into an Arabic numeral. The syntax +// of the function is: +// +// ARABIC(text) +// +func (fn *formulaFuncs) ARABIC(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ARABIC requires 1 numeric arguments") + return + } + val, last, prefix := 0.0, 0.0, 1.0 + for _, char := range argsList.Front().Value.(efp.Token).TValue { + digit := 0.0 + switch char { + case '-': + prefix = -1 + continue + case 'I': + digit = 1 + case 'V': + digit = 5 + case 'X': + digit = 10 + case 'L': + digit = 50 + case 'C': + digit = 100 + case 'D': + digit = 500 + case 'M': + digit = 1000 + } + val += digit + switch { + case last == digit && (last == 5 || last == 50 || last == 500): + result = formulaErrorVALUE + return + case 2*last == digit: + result = formulaErrorVALUE + return + } + if last < digit { + val -= 2 * last + } + last = digit + } + result = fmt.Sprintf("%g", prefix*val) + return +} + +// ASIN function calculates the arcsine (i.e. the inverse sine) of a given +// number, and returns an angle, in radians, between -π/2 and π/2. The syntax +// of the function is: +// +// ASIN(number) +// +func (fn *formulaFuncs) ASIN(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ASIN requires 1 numeric arguments") + return + } + var val float64 + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + result = fmt.Sprintf("%g", math.Asin(val)) + return +} + +// ASINH function calculates the inverse hyperbolic sine of a supplied number. +// The syntax of the function is: +// +// ASINH(number) +// +func (fn *formulaFuncs) ASINH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ASINH requires 1 numeric arguments") + return + } + var val float64 + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + result = fmt.Sprintf("%g", math.Asinh(val)) + return +} + +// ATAN function calculates the arctangent (i.e. the inverse tangent) of a +// given number, and returns an angle, in radians, between -π/2 and +π/2. The +// syntax of the function is: +// +// ATAN(number) +// +func (fn *formulaFuncs) ATAN(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ATAN requires 1 numeric arguments") + return + } + var val float64 + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + result = fmt.Sprintf("%g", math.Atan(val)) + return +} + +// ATANH function calculates the inverse hyperbolic tangent of a supplied +// number. The syntax of the function is: +// +// ATANH(number) +// +func (fn *formulaFuncs) ATANH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ATANH requires 1 numeric arguments") + return + } + var val float64 + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + result = fmt.Sprintf("%g", math.Atanh(val)) + return +} + +// ATAN2 function calculates the arctangent (i.e. the inverse tangent) of a +// given set of x and y coordinates, and returns an angle, in radians, between +// -π/2 and +π/2. The syntax of the function is: +// +// ATAN2(x_num,y_num) +// +func (fn *formulaFuncs) ATAN2(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("ATAN2 requires 2 numeric arguments") + return + } + var x, y float64 + x, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + y, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + result = fmt.Sprintf("%g", math.Atan2(x, y)) + return +} + // gcd returns the greatest common divisor of two supplied integers. func gcd(x, y float64) float64 { x, y = math.Trunc(x), math.Trunc(y) @@ -513,13 +759,55 @@ func gcd(x, y float64) float64 { return x } +// BASE function converts a number into a supplied base (radix), and returns a +// text representation of the calculated value. The syntax of the function is: +// +// BASE(number,radix,[min_length]) +// +func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) { + if argsList.Len() < 2 { + err = errors.New("BASE requires at least 2 arguments") + return + } + if argsList.Len() > 3 { + err = errors.New("BASE allows at most 3 arguments") + return + } + var number float64 + var radix, minLength int + number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + radix, err = strconv.Atoi(argsList.Front().Next().Value.(efp.Token).TValue) + if err != nil { + return + } + if radix < 2 || radix > 36 { + err = errors.New("radix must be an integer ≥ 2 and ≤ 36") + return + } + if argsList.Len() > 2 { + minLength, err = strconv.Atoi(argsList.Back().Value.(efp.Token).TValue) + if err != nil { + return + } + } + result = strconv.FormatInt(int64(number), radix) + if len(result) < minLength { + result = strings.Repeat("0", minLength-len(result)) + result + } + result = strings.ToUpper(result) + return +} + // GCD function returns the greatest common divisor of two or more supplied -// integers.The syntax of the function is: +// integers. The syntax of the function is: // // GCD(number1,[number2],...) // -func (fn *formulaFuncs) GCD(argsStack *Stack) (result string, err error) { - if argsStack.Len() == 0 { +func (fn *formulaFuncs) GCD(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { err = errors.New("GCD requires at least 1 argument") return } @@ -527,8 +815,8 @@ func (fn *formulaFuncs) GCD(argsStack *Stack) (result string, err error) { val float64 nums = []float64{} ) - for !argsStack.Empty() { - token := argsStack.Pop().(efp.Token) + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(efp.Token) if token.TValue == "" { continue } @@ -573,8 +861,8 @@ func lcm(a, b float64) float64 { // // LCM(number1,[number2],...) // -func (fn *formulaFuncs) LCM(argsStack *Stack) (result string, err error) { - if argsStack.Len() == 0 { +func (fn *formulaFuncs) LCM(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { err = errors.New("LCM requires at least 1 argument") return } @@ -582,8 +870,8 @@ func (fn *formulaFuncs) LCM(argsStack *Stack) (result string, err error) { val float64 nums = []float64{} ) - for !argsStack.Empty() { - token := argsStack.Pop().(efp.Token) + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(efp.Token) if token.TValue == "" { continue } @@ -618,17 +906,17 @@ func (fn *formulaFuncs) LCM(argsStack *Stack) (result string, err error) { // // POWER(number,power) // -func (fn *formulaFuncs) POWER(argsStack *Stack) (result string, err error) { - if argsStack.Len() != 2 { +func (fn *formulaFuncs) POWER(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { err = errors.New("POWER requires 2 numeric arguments") return } var x, y float64 - y, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + x, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) if err != nil { return } - x, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + y, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64) if err != nil { return } @@ -649,13 +937,13 @@ func (fn *formulaFuncs) POWER(argsStack *Stack) (result string, err error) { // // PRODUCT(number1,[number2],...) // -func (fn *formulaFuncs) PRODUCT(argsStack *Stack) (result string, err error) { +func (fn *formulaFuncs) PRODUCT(argsList *list.List) (result string, err error) { var ( val float64 product float64 = 1 ) - for !argsStack.Empty() { - token := argsStack.Pop().(efp.Token) + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(efp.Token) if token.TValue == "" { continue } @@ -676,13 +964,13 @@ func (fn *formulaFuncs) PRODUCT(argsStack *Stack) (result string, err error) { // // SIGN(number) // -func (fn *formulaFuncs) SIGN(argsStack *Stack) (result string, err error) { - if argsStack.Len() != 1 { +func (fn *formulaFuncs) SIGN(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { err = errors.New("SIGN requires 1 numeric arguments") return } var val float64 - val, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) if err != nil { return } @@ -703,13 +991,13 @@ func (fn *formulaFuncs) SIGN(argsStack *Stack) (result string, err error) { // // SQRT(number) // -func (fn *formulaFuncs) SQRT(argsStack *Stack) (result string, err error) { - if argsStack.Len() != 1 { +func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { err = errors.New("SQRT requires 1 numeric arguments") return } var val float64 - val, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) if err != nil { return } @@ -726,11 +1014,11 @@ func (fn *formulaFuncs) SQRT(argsStack *Stack) (result string, err error) { // // SUM(number1,[number2],...) // -func (fn *formulaFuncs) SUM(argsStack *Stack) (result string, err error) { +func (fn *formulaFuncs) SUM(argsList *list.List) (result string, err error) { var val float64 var sum float64 - for !argsStack.Empty() { - token := argsStack.Pop().(efp.Token) + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(efp.Token) if token.TValue == "" { continue } @@ -749,17 +1037,17 @@ func (fn *formulaFuncs) SUM(argsStack *Stack) (result string, err error) { // // QUOTIENT(numerator,denominator) // -func (fn *formulaFuncs) QUOTIENT(argsStack *Stack) (result string, err error) { - if argsStack.Len() != 2 { +func (fn *formulaFuncs) QUOTIENT(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { err = errors.New("QUOTIENT requires 2 numeric arguments") return } var x, y float64 - y, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + x, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) if err != nil { return } - x, err = strconv.ParseFloat(argsStack.Pop().(efp.Token).TValue, 64) + y, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64) if err != nil { return } -- cgit v1.2.1 From 6f796b88e68e927c71e51e22278f4d43b935e00a Mon Sep 17 00:00:00 2001 From: xuri Date: Mon, 4 May 2020 21:22:11 +0800 Subject: fn: CEILING, CEILING.MATH --- calc.go | 101 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 100 insertions(+), 1 deletion(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index 7c912eb..568f044 100644 --- a/calc.go +++ b/calc.go @@ -220,7 +220,9 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) argsList.PushBack(opfdStack.Pop()) } // call formula function to evaluate - result, err := callFuncByName(&formulaFuncs{}, strings.ReplaceAll(opfStack.Peek().(efp.Token).TValue, "_xlfn.", ""), []reflect.Value{reflect.ValueOf(argsList)}) + result, err := callFuncByName(&formulaFuncs{}, strings.NewReplacer( + "_xlfn", "", ".", "").Replace(opfStack.Peek().(efp.Token).TValue), + []reflect.Value{reflect.ValueOf(argsList)}) if err != nil { return efp.Token{}, err } @@ -801,6 +803,103 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) { return } +// CEILING function rounds a supplied number away from zero, to the nearest +// multiple of a given number. The syntax of the function is: +// +// CEILING(number,significance) +// +func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("CEILING requires at least 1 argument") + return + } + if argsList.Len() > 2 { + err = errors.New("CEILING allows at most 2 arguments") + return + } + var number, significance float64 + number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + significance = 1 + if number < 0 { + significance = -1 + } + if argsList.Len() > 1 { + significance, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + } + if significance < 0 && number > 0 { + err = errors.New("negative sig to CEILING invalid") + return + } + if argsList.Len() == 1 { + result = fmt.Sprintf("%g", math.Ceil(number)) + return + } + number, res := math.Modf(number / significance) + if res > 0 { + number++ + } + result = fmt.Sprintf("%g", number*significance) + return +} + +// CEILINGMATH function rounds a supplied number up to a supplied multiple of +// significance. The syntax of the function is: +// +// CEILING.MATH(number,[significance],[mode]) +// +func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("CEILING.MATH requires at least 1 argument") + return + } + if argsList.Len() > 3 { + err = errors.New("CEILING.MATH allows at most 3 arguments") + return + } + var number, significance, mode float64 = 0, 1, 1 + number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + if number < 0 { + significance = -1 + } + if argsList.Len() > 1 { + significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + } + if argsList.Len() == 1 { + result = fmt.Sprintf("%g", math.Ceil(number)) + return + } + if argsList.Len() > 2 { + mode, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64) + if err != nil { + return + } + } + val, res := math.Modf(number / significance) + _, _ = res, mode + if res != 0 { + if number > 0 { + val++ + } else if mode < 0 { + val-- + } + } + + result = fmt.Sprintf("%g", val*significance) + return +} + // GCD function returns the greatest common divisor of two or more supplied // integers. The syntax of the function is: // -- cgit v1.2.1 From 5c82f2269dfa82c5be3afd7ac140aacbf2221829 Mon Sep 17 00:00:00 2001 From: xuri Date: Tue, 5 May 2020 17:27:19 +0800 Subject: #65 fn: CEILING.PRECISE, COMBIN, COMBINA, COS, COSH, COT, COTH, CSC --- calc.go | 319 ++++++++++++++++++++++++++++++++++++++++++++++++++-------------- 1 file changed, 252 insertions(+), 67 deletions(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index 568f044..ed25a58 100644 --- a/calc.go +++ b/calc.go @@ -504,8 +504,7 @@ func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } result = fmt.Sprintf("%g", math.Abs(val)) @@ -524,8 +523,7 @@ func (fn *formulaFuncs) ACOS(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } result = fmt.Sprintf("%g", math.Acos(val)) @@ -543,8 +541,7 @@ func (fn *formulaFuncs) ACOSH(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } result = fmt.Sprintf("%g", math.Acosh(val)) @@ -563,8 +560,7 @@ func (fn *formulaFuncs) ACOT(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } result = fmt.Sprintf("%g", math.Pi/2-math.Atan(val)) @@ -582,8 +578,7 @@ func (fn *formulaFuncs) ACOTH(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } result = fmt.Sprintf("%g", math.Atanh(1/val)) @@ -652,8 +647,7 @@ func (fn *formulaFuncs) ASIN(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } result = fmt.Sprintf("%g", math.Asin(val)) @@ -671,8 +665,7 @@ func (fn *formulaFuncs) ASINH(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } result = fmt.Sprintf("%g", math.Asinh(val)) @@ -691,8 +684,7 @@ func (fn *formulaFuncs) ATAN(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } result = fmt.Sprintf("%g", math.Atan(val)) @@ -710,8 +702,7 @@ func (fn *formulaFuncs) ATANH(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } result = fmt.Sprintf("%g", math.Atanh(val)) @@ -730,12 +721,10 @@ func (fn *formulaFuncs) ATAN2(argsList *list.List) (result string, err error) { return } var x, y float64 - x, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64) - if err != nil { + if x, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { return } - y, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if y, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } result = fmt.Sprintf("%g", math.Atan2(x, y)) @@ -777,12 +766,10 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) { } var number float64 var radix, minLength int - number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } - radix, err = strconv.Atoi(argsList.Front().Next().Value.(efp.Token).TValue) - if err != nil { + if radix, err = strconv.Atoi(argsList.Front().Next().Value.(efp.Token).TValue); err != nil { return } if radix < 2 || radix > 36 { @@ -790,8 +777,7 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) { return } if argsList.Len() > 2 { - minLength, err = strconv.Atoi(argsList.Back().Value.(efp.Token).TValue) - if err != nil { + if minLength, err = strconv.Atoi(argsList.Back().Value.(efp.Token).TValue); err != nil { return } } @@ -817,18 +803,15 @@ func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error) err = errors.New("CEILING allows at most 2 arguments") return } - var number, significance float64 - number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + var number, significance float64 = 0, 1 + if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } - significance = 1 if number < 0 { significance = -1 } if argsList.Len() > 1 { - significance, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64) - if err != nil { + if significance, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { return } } @@ -863,16 +846,14 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err return } var number, significance, mode float64 = 0, 1, 1 - number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } if number < 0 { significance = -1 } if argsList.Len() > 1 { - significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(efp.Token).TValue, 64) - if err != nil { + if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(efp.Token).TValue, 64); err != nil { return } } @@ -881,13 +862,11 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err return } if argsList.Len() > 2 { - mode, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64) - if err != nil { + if mode, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { return } } val, res := math.Modf(number / significance) - _, _ = res, mode if res != 0 { if number > 0 { val++ @@ -895,11 +874,231 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err val-- } } + result = fmt.Sprintf("%g", val*significance) + return +} +// CEILINGPRECISE function rounds a supplied number up (regardless of the +// number's sign), to the nearest multiple of a given number. The syntax of +// the function is: +// +// CEILING.PRECISE(number,[significance]) +// +func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("CEILING.PRECISE requires at least 1 argument") + return + } + if argsList.Len() > 2 { + err = errors.New("CEILING.PRECISE allows at most 2 arguments") + return + } + var number, significance float64 = 0, 1 + if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + return + } + if number < 0 { + significance = -1 + } + if argsList.Len() == 1 { + result = fmt.Sprintf("%g", math.Ceil(number)) + return + } + if argsList.Len() > 1 { + if significance, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + return + } + significance = math.Abs(significance) + if significance == 0 { + result = "0" + return + } + } + val, res := math.Modf(number / significance) + if res != 0 { + if number > 0 { + val++ + } + } result = fmt.Sprintf("%g", val*significance) return } +// COMBIN function calculates the number of combinations (in any order) of a +// given number objects from a set. The syntax of the function is: +// +// COMBIN(number,number_chosen) +// +func (fn *formulaFuncs) COMBIN(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("COMBIN requires 2 argument") + return + } + var number, chosen, val float64 = 0, 0, 1 + if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + return + } + if chosen, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + return + } + number, chosen = math.Trunc(number), math.Trunc(chosen) + if chosen > number { + err = errors.New("COMBIN requires number >= number_chosen") + return + } + if chosen == number || chosen == 0 { + result = "1" + return + } + for c := float64(1); c <= chosen; c++ { + val *= (number + 1 - c) / c + } + result = fmt.Sprintf("%g", math.Ceil(val)) + return +} + +// COMBINA function calculates the number of combinations, with repetitions, +// of a given number objects from a set. The syntax of the function is: +// +// COMBINA(number,number_chosen) +// +func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("COMBINA requires 2 argument") + return + } + var number, chosen float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + return + } + if chosen, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + return + } + number, chosen = math.Trunc(number), math.Trunc(chosen) + if number < chosen { + err = errors.New("COMBINA requires number > number_chosen") + return + } + if number == 0 { + result = "0" + return + } + args := list.New() + args.PushBack(efp.Token{ + TValue: fmt.Sprintf("%g", number+chosen-1), + TType: efp.TokenTypeOperand, + TSubType: efp.TokenSubTypeNumber, + }) + args.PushBack(efp.Token{ + TValue: fmt.Sprintf("%g", number-1), + TType: efp.TokenTypeOperand, + TSubType: efp.TokenSubTypeNumber, + }) + return fn.COMBIN(args) +} + +// COS function calculates the cosine of a given angle. The syntax of the +// function is: +// +// COS(number) +// +func (fn *formulaFuncs) COS(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("COS requires 1 numeric arguments") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Cos(val)) + return +} + +// COSH function calculates the hyperbolic cosine (cosh) of a supplied number. +// The syntax of the function is: +// +// COSH(number) +// +func (fn *formulaFuncs) COSH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("COSH requires 1 numeric arguments") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Cosh(val)) + return +} + +// COT function calculates the cotangent of a given angle. The syntax of the +// function is: +// +// COT(number) +// +func (fn *formulaFuncs) COT(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("COT requires 1 numeric arguments") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + return + } + if val == 0 { + err = errors.New(formulaErrorNAME) + return + } + result = fmt.Sprintf("%g", math.Tan(val)) + return +} + +// COTH function calculates the hyperbolic cotangent (coth) of a supplied +// angle. The syntax of the function is: +// +// COTH(number) +// +func (fn *formulaFuncs) COTH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("COTH requires 1 numeric arguments") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + return + } + if val == 0 { + err = errors.New(formulaErrorNAME) + return + } + result = fmt.Sprintf("%g", math.Tanh(val)) + return +} + +// CSC function calculates the cosecant of a given angle. The syntax of the +// function is: +// +// CSC(number) +// +func (fn *formulaFuncs) CSC(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("CSC requires 1 numeric arguments") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + return + } + if val == 0 { + err = errors.New(formulaErrorNAME) + return + } + result = fmt.Sprintf("%g", 1/math.Sin(val)) + return +} + // GCD function returns the greatest common divisor of two or more supplied // integers. The syntax of the function is: // @@ -919,8 +1118,7 @@ func (fn *formulaFuncs) GCD(argsList *list.List) (result string, err error) { if token.TValue == "" { continue } - val, err = strconv.ParseFloat(token.TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(token.TValue, 64); err != nil { return } nums = append(nums, val) @@ -974,8 +1172,7 @@ func (fn *formulaFuncs) LCM(argsList *list.List) (result string, err error) { if token.TValue == "" { continue } - val, err = strconv.ParseFloat(token.TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(token.TValue, 64); err != nil { return } nums = append(nums, val) @@ -1011,12 +1208,10 @@ func (fn *formulaFuncs) POWER(argsList *list.List) (result string, err error) { return } var x, y float64 - x, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if x, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } - y, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64) - if err != nil { + if y, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { return } if x == 0 && y == 0 { @@ -1037,17 +1232,13 @@ func (fn *formulaFuncs) POWER(argsList *list.List) (result string, err error) { // PRODUCT(number1,[number2],...) // func (fn *formulaFuncs) PRODUCT(argsList *list.List) (result string, err error) { - var ( - val float64 - product float64 = 1 - ) + var val, product float64 = 0, 1 for arg := argsList.Front(); arg != nil; arg = arg.Next() { token := arg.Value.(efp.Token) if token.TValue == "" { continue } - val, err = strconv.ParseFloat(token.TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(token.TValue, 64); err != nil { return } product = product * val @@ -1069,8 +1260,7 @@ func (fn *formulaFuncs) SIGN(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } if val < 0 { @@ -1096,8 +1286,7 @@ func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) { return } var val float64 - val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } if val < 0 { @@ -1114,15 +1303,13 @@ func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) { // SUM(number1,[number2],...) // func (fn *formulaFuncs) SUM(argsList *list.List) (result string, err error) { - var val float64 - var sum float64 + var val, sum float64 for arg := argsList.Front(); arg != nil; arg = arg.Next() { token := arg.Value.(efp.Token) if token.TValue == "" { continue } - val, err = strconv.ParseFloat(token.TValue, 64) - if err != nil { + if val, err = strconv.ParseFloat(token.TValue, 64); err != nil { return } sum += val @@ -1142,12 +1329,10 @@ func (fn *formulaFuncs) QUOTIENT(argsList *list.List) (result string, err error) return } var x, y float64 - x, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64) - if err != nil { + if x, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { return } - y, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64) - if err != nil { + if y, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { return } if y == 0 { -- cgit v1.2.1 From 97e3f4ce6822bea6d65961c0399f7563450b69b4 Mon Sep 17 00:00:00 2001 From: xuri Date: Wed, 6 May 2020 00:01:31 +0800 Subject: #65 fn: CSCH, DECIMAL, DEGREES, EVEN, EXP, FACT, FACTDOUBLE, FLOOR, FLOOR.MATH, FLOOR.PRECISE, INT, ISO.CEILING, LN, LOG, LOG10, MDETERM --- calc.go | 719 +++++++++++++++++++++++++++++++++++++++++++++++++++++++--------- 1 file changed, 621 insertions(+), 98 deletions(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index ed25a58..2ab3d61 100644 --- a/calc.go +++ b/calc.go @@ -37,19 +37,26 @@ const ( formulaErrorGETTINGDATA = "#GETTING_DATA" ) -// cellRef defines the structure of a cell reference +// cellRef defines the structure of a cell reference. type cellRef struct { Col int Row int Sheet string } -// cellRef defines the structure of a cell range +// cellRef defines the structure of a cell range. type cellRange struct { From cellRef To cellRef } +// formulaArg is the argument of a formula or function. +type formulaArg struct { + Value string + Matrix []string +} + +// formulaFuncs is the type of the formula functions. type formulaFuncs struct{} // CalcCellValue provides a function to get calculated cell value. This @@ -140,7 +147,7 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) if token.TSubType == efp.TokenSubTypeRange { if !opftStack.Empty() { // parse reference: must reference at here - result, err := f.parseReference(sheet, token.TValue) + result, _, err := f.parseReference(sheet, token.TValue) if err != nil { return efp.Token{TValue: formulaErrorNAME}, err } @@ -156,16 +163,16 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) } if nextToken.TType == efp.TokenTypeArgument || nextToken.TType == efp.TokenTypeFunction { // parse reference: reference or range at here - result, err := f.parseReference(sheet, token.TValue) + result, matrix, err := f.parseReference(sheet, token.TValue) if err != nil { return efp.Token{TValue: formulaErrorNAME}, err } - for _, val := range result { - argsList.PushBack(efp.Token{ - TType: efp.TokenTypeOperand, - TSubType: efp.TokenSubTypeNumber, - TValue: val, - }) + for idx, val := range result { + arg := formulaArg{Value: val} + if idx < len(matrix) { + arg.Matrix = matrix[idx] + } + argsList.PushBack(arg) } if len(result) == 0 { return efp.Token{}, errors.New(formulaErrorVALUE) @@ -190,7 +197,9 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) opftStack.Pop() } if !opfdStack.Empty() { - argsList.PushBack(opfdStack.Pop()) + argsList.PushBack(formulaArg{ + Value: opfdStack.Pop().(efp.Token).TValue, + }) } continue } @@ -201,7 +210,9 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) // current token is text if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText { - argsList.PushBack(token) + argsList.PushBack(formulaArg{ + Value: token.TValue, + }) } // current token is function stop @@ -217,7 +228,9 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) // push opfd to args if opfdStack.Len() > 0 { - argsList.PushBack(opfdStack.Pop()) + argsList.PushBack(formulaArg{ + Value: opfdStack.Pop().(efp.Token).TValue, + }) } // call formula function to evaluate result, err := callFuncByName(&formulaFuncs{}, strings.NewReplacer( @@ -324,7 +337,7 @@ func calculate(opdStack *Stack, opt efp.Token) error { func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Stack) error { // parse reference: must reference at here if token.TSubType == efp.TokenSubTypeRange { - result, err := f.parseReference(sheet, token.TValue) + result, _, err := f.parseReference(sheet, token.TValue) if err != nil { return errors.New(formulaErrorNAME) } @@ -383,7 +396,7 @@ func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Sta // parseReference parse reference and extract values by given reference // characters and default sheet name. -func (f *File) parseReference(sheet, reference string) (result []string, err error) { +func (f *File) parseReference(sheet, reference string) (result []string, matrix [][]string, err error) { reference = strings.Replace(reference, "$", "", -1) refs, cellRanges, cellRefs := list.New(), list.New(), list.New() for _, ref := range strings.Split(reference, ":") { @@ -423,7 +436,7 @@ func (f *File) parseReference(sheet, reference string) (result []string, err err refs.Remove(e) } - result, err = f.rangeResolver(cellRefs, cellRanges) + result, matrix, err = f.rangeResolver(cellRefs, cellRanges) return } @@ -431,7 +444,7 @@ func (f *File) parseReference(sheet, reference string) (result []string, err err // This function will not ignore the empty cell. Note that the result of 3D // range references may be different from Excel in some cases, for example, // A1:A2:A2:B3 in Excel will include B1, but we wont. -func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, err error) { +func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, matrix [][]string, err error) { filter := map[string]string{} // extract value from ranges for temp := cellRanges.Front(); temp != nil; temp = temp.Next() { @@ -441,16 +454,21 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, } rng := []int{cr.From.Col, cr.From.Row, cr.To.Col, cr.To.Row} sortCoordinates(rng) - for col := rng[0]; col <= rng[2]; col++ { - for row := rng[1]; row <= rng[3]; row++ { - var cell string + matrix = [][]string{} + for row := rng[1]; row <= rng[3]; row++ { + var matrixRow = []string{} + for col := rng[0]; col <= rng[2]; col++ { + var cell, value string if cell, err = CoordinatesToCellName(col, row); err != nil { return } - if filter[cell], err = f.GetCellValue(cr.From.Sheet, cell); err != nil { + if value, err = f.GetCellValue(cr.From.Sheet, cell); err != nil { return } + filter[cell] = value + matrixRow = append(matrixRow, value) } + matrix = append(matrix, matrixRow) } } // extract value from references @@ -500,11 +518,11 @@ func callFuncByName(receiver interface{}, name string, params []reflect.Value) ( // func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("ABS requires 1 numeric arguments") + err = errors.New("ABS requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Abs(val)) @@ -519,11 +537,11 @@ func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) ACOS(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("ACOS requires 1 numeric arguments") + err = errors.New("ACOS requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Acos(val)) @@ -537,11 +555,11 @@ func (fn *formulaFuncs) ACOS(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) ACOSH(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("ACOSH requires 1 numeric arguments") + err = errors.New("ACOSH requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Acosh(val)) @@ -556,11 +574,11 @@ func (fn *formulaFuncs) ACOSH(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) ACOT(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("ACOT requires 1 numeric arguments") + err = errors.New("ACOT requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Pi/2-math.Atan(val)) @@ -574,11 +592,11 @@ func (fn *formulaFuncs) ACOT(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) ACOTH(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("ACOTH requires 1 numeric arguments") + err = errors.New("ACOTH requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Atanh(1/val)) @@ -592,11 +610,11 @@ func (fn *formulaFuncs) ACOTH(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) ARABIC(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("ARABIC requires 1 numeric arguments") + err = errors.New("ARABIC requires 1 numeric argument") return } val, last, prefix := 0.0, 0.0, 1.0 - for _, char := range argsList.Front().Value.(efp.Token).TValue { + for _, char := range argsList.Front().Value.(formulaArg).Value { digit := 0.0 switch char { case '-': @@ -643,11 +661,11 @@ func (fn *formulaFuncs) ARABIC(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) ASIN(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("ASIN requires 1 numeric arguments") + err = errors.New("ASIN requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Asin(val)) @@ -661,11 +679,11 @@ func (fn *formulaFuncs) ASIN(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) ASINH(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("ASINH requires 1 numeric arguments") + err = errors.New("ASINH requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Asinh(val)) @@ -680,11 +698,11 @@ func (fn *formulaFuncs) ASINH(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) ATAN(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("ATAN requires 1 numeric arguments") + err = errors.New("ATAN requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Atan(val)) @@ -698,11 +716,11 @@ func (fn *formulaFuncs) ATAN(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) ATANH(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("ATANH requires 1 numeric arguments") + err = errors.New("ATANH requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Atanh(val)) @@ -721,10 +739,10 @@ func (fn *formulaFuncs) ATAN2(argsList *list.List) (result string, err error) { return } var x, y float64 - if x, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + if x, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { return } - if y, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if y, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Atan2(x, y)) @@ -766,10 +784,10 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) { } var number float64 var radix, minLength int - if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } - if radix, err = strconv.Atoi(argsList.Front().Next().Value.(efp.Token).TValue); err != nil { + if radix, err = strconv.Atoi(argsList.Front().Next().Value.(formulaArg).Value); err != nil { return } if radix < 2 || radix > 36 { @@ -777,7 +795,7 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) { return } if argsList.Len() > 2 { - if minLength, err = strconv.Atoi(argsList.Back().Value.(efp.Token).TValue); err != nil { + if minLength, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); err != nil { return } } @@ -804,14 +822,14 @@ func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error) return } var number, significance float64 = 0, 1 - if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } if number < 0 { significance = -1 } if argsList.Len() > 1 { - if significance, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { return } } @@ -846,14 +864,14 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err return } var number, significance, mode float64 = 0, 1, 1 - if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } if number < 0 { significance = -1 } if argsList.Len() > 1 { - if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(efp.Token).TValue, 64); err != nil { + if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(formulaArg).Value, 64); err != nil { return } } @@ -862,7 +880,7 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err return } if argsList.Len() > 2 { - if mode, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + if mode, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { return } } @@ -894,7 +912,7 @@ func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err return } var number, significance float64 = 0, 1 - if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } if number < 0 { @@ -905,7 +923,7 @@ func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err return } if argsList.Len() > 1 { - if significance, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { return } significance = math.Abs(significance) @@ -935,10 +953,10 @@ func (fn *formulaFuncs) COMBIN(argsList *list.List) (result string, err error) { return } var number, chosen, val float64 = 0, 0, 1 - if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } - if chosen, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { return } number, chosen = math.Trunc(number), math.Trunc(chosen) @@ -968,10 +986,10 @@ func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error) return } var number, chosen float64 - if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } - if chosen, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { return } number, chosen = math.Trunc(number), math.Trunc(chosen) @@ -984,15 +1002,11 @@ func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error) return } args := list.New() - args.PushBack(efp.Token{ - TValue: fmt.Sprintf("%g", number+chosen-1), - TType: efp.TokenTypeOperand, - TSubType: efp.TokenSubTypeNumber, + args.PushBack(formulaArg{ + Value: fmt.Sprintf("%g", number+chosen-1), }) - args.PushBack(efp.Token{ - TValue: fmt.Sprintf("%g", number-1), - TType: efp.TokenTypeOperand, - TSubType: efp.TokenSubTypeNumber, + args.PushBack(formulaArg{ + Value: fmt.Sprintf("%g", number-1), }) return fn.COMBIN(args) } @@ -1004,11 +1018,11 @@ func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error) // func (fn *formulaFuncs) COS(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("COS requires 1 numeric arguments") + err = errors.New("COS requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Cos(val)) @@ -1022,11 +1036,11 @@ func (fn *formulaFuncs) COS(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) COSH(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("COSH requires 1 numeric arguments") + err = errors.New("COSH requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } result = fmt.Sprintf("%g", math.Cosh(val)) @@ -1040,11 +1054,11 @@ func (fn *formulaFuncs) COSH(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) COT(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("COT requires 1 numeric arguments") + err = errors.New("COT requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } if val == 0 { @@ -1062,11 +1076,11 @@ func (fn *formulaFuncs) COT(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) COTH(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("COTH requires 1 numeric arguments") + err = errors.New("COTH requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } if val == 0 { @@ -1084,11 +1098,11 @@ func (fn *formulaFuncs) COTH(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) CSC(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("CSC requires 1 numeric arguments") + err = errors.New("CSC requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } if val == 0 { @@ -1099,6 +1113,297 @@ func (fn *formulaFuncs) CSC(argsList *list.List) (result string, err error) { return } +// CSCH function calculates the hyperbolic cosecant (csch) of a supplied +// angle. The syntax of the function is: +// +// CSCH(number) +// +func (fn *formulaFuncs) CSCH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("CSCH requires 1 numeric argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if val == 0 { + err = errors.New(formulaErrorNAME) + return + } + result = fmt.Sprintf("%g", 1/math.Sinh(val)) + return +} + +// DECIMAL function converts a text representation of a number in a specified +// base, into a decimal value. The syntax of the function is: +// +// DECIMAL(text,radix) +// +func (fn *formulaFuncs) DECIMAL(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("DECIMAL requires 2 numeric arguments") + return + } + var text = argsList.Front().Value.(formulaArg).Value + var radix int + if radix, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); err != nil { + return + } + if len(text) > 2 && (strings.HasPrefix(text, "0x") || strings.HasPrefix(text, "0X")) { + text = text[2:] + } + val, err := strconv.ParseInt(text, radix, 64) + if err != nil { + err = errors.New(formulaErrorNUM) + return + } + result = fmt.Sprintf("%g", float64(val)) + return +} + +// DEGREES function converts radians into degrees. The syntax of the function +// is: +// +// DEGREES(angle) +// +func (fn *formulaFuncs) DEGREES(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("DEGREES requires 1 numeric argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if val == 0 { + err = errors.New(formulaErrorNAME) + return + } + result = fmt.Sprintf("%g", 180.0/math.Pi*val) + return +} + +// EVEN function rounds a supplied number away from zero (i.e. rounds a +// positive number up and a negative number down), to the next even number. +// The syntax of the function is: +// +// EVEN(number) +// +func (fn *formulaFuncs) EVEN(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("EVEN requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + sign := math.Signbit(number) + m, frac := math.Modf(number / 2) + val := m * 2 + if frac != 0 { + if !sign { + val += 2 + } else { + val -= 2 + } + } + result = fmt.Sprintf("%g", val) + return +} + +// EXP function calculates the value of the mathematical constant e, raised to +// the power of a given number. The syntax of the function is: +// +// EXP(number) +// +func (fn *formulaFuncs) EXP(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("EXP requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = strings.ToUpper(fmt.Sprintf("%g", math.Exp(number))) + return +} + +// fact returns the factorial of a supplied number. +func fact(number float64) float64 { + val := float64(1) + for i := float64(2); i <= number; i++ { + val *= i + } + return val +} + +// FACT function returns the factorial of a supplied number. The syntax of the +// function is: +// +// FACT(number) +// +func (fn *formulaFuncs) FACT(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("FACT requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if number < 0 { + err = errors.New(formulaErrorNUM) + } + result = strings.ToUpper(fmt.Sprintf("%g", fact(number))) + return +} + +// FACTDOUBLE function returns the double factorial of a supplied number. The +// syntax of the function is: +// +// FACTDOUBLE(number) +// +func (fn *formulaFuncs) FACTDOUBLE(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("FACTDOUBLE requires 1 numeric argument") + return + } + var number, val float64 = 0, 1 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if number < 0 { + err = errors.New(formulaErrorNUM) + } + for i := math.Trunc(number); i > 1; i -= 2 { + val *= i + } + result = strings.ToUpper(fmt.Sprintf("%g", val)) + return +} + +// FLOOR function rounds a supplied number towards zero to the nearest +// multiple of a specified significance. The syntax of the function is: +// +// FLOOR(number,significance) +// +func (fn *formulaFuncs) FLOOR(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("FLOOR requires 2 numeric arguments") + return + } + var number, significance float64 = 0, 1 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + if significance < 0 && number >= 0 { + err = errors.New(formulaErrorNUM) + } + val := number + val, res := math.Modf(val / significance) + if res != 0 { + if number < 0 && res < 0 { + val-- + } + } + result = strings.ToUpper(fmt.Sprintf("%g", val*significance)) + return +} + +// FLOORMATH function rounds a supplied number down to a supplied multiple of +// significance. The syntax of the function is: +// +// FLOOR.MATH(number,[significance],[mode]) +// +func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("FLOOR.MATH requires at least 1 argument") + return + } + if argsList.Len() > 3 { + err = errors.New("FLOOR.MATH allows at most 3 arguments") + return + } + var number, significance, mode float64 = 0, 1, 1 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if number < 0 { + significance = -1 + } + if argsList.Len() > 1 { + if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(formulaArg).Value, 64); err != nil { + return + } + } + if argsList.Len() == 1 { + result = fmt.Sprintf("%g", math.Floor(number)) + return + } + if argsList.Len() > 2 { + if mode, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + } + val, res := math.Modf(number / significance) + if res != 0 && number < 0 && mode > 0 { + val-- + } + result = fmt.Sprintf("%g", val*significance) + return +} + +// FLOORPRECISE function rounds a supplied number down to a supplied multiple +// of significance. The syntax of the function is: +// +// FLOOR.PRECISE(number,[significance]) +// +func (fn *formulaFuncs) FLOORPRECISE(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("FLOOR.PRECISE requires at least 1 argument") + return + } + if argsList.Len() > 2 { + err = errors.New("FLOOR.PRECISE allows at most 2 arguments") + return + } + var number, significance float64 = 0, 1 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if number < 0 { + significance = -1 + } + if argsList.Len() == 1 { + result = fmt.Sprintf("%g", math.Floor(number)) + return + } + if argsList.Len() > 1 { + if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + significance = math.Abs(significance) + if significance == 0 { + result = "0" + return + } + } + val, res := math.Modf(number / significance) + if res != 0 { + if number < 0 { + val-- + } + } + result = fmt.Sprintf("%g", val*significance) + return +} + // GCD function returns the greatest common divisor of two or more supplied // integers. The syntax of the function is: // @@ -1114,11 +1419,11 @@ func (fn *formulaFuncs) GCD(argsList *list.List) (result string, err error) { nums = []float64{} ) for arg := argsList.Front(); arg != nil; arg = arg.Next() { - token := arg.Value.(efp.Token) - if token.TValue == "" { + token := arg.Value.(formulaArg).Value + if token == "" { continue } - if val, err = strconv.ParseFloat(token.TValue, 64); err != nil { + if val, err = strconv.ParseFloat(token, 64); err != nil { return } nums = append(nums, val) @@ -1143,6 +1448,74 @@ func (fn *formulaFuncs) GCD(argsList *list.List) (result string, err error) { return } +// INT function truncates a supplied number down to the closest integer. The +// syntax of the function is: +// +// INT(number) +// +func (fn *formulaFuncs) INT(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("INT requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + val, frac := math.Modf(number) + if frac < 0 { + val-- + } + result = fmt.Sprintf("%g", val) + return +} + +// ISOCEILING function rounds a supplied number up (regardless of the number's +// sign), to the nearest multiple of a supplied significance. The syntax of +// the function is: +// +// ISO.CEILING(number,[significance]) +// +func (fn *formulaFuncs) ISOCEILING(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("ISO.CEILING requires at least 1 argument") + return + } + if argsList.Len() > 2 { + err = errors.New("ISO.CEILING allows at most 2 arguments") + return + } + var number, significance float64 = 0, 1 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if number < 0 { + significance = -1 + } + if argsList.Len() == 1 { + result = fmt.Sprintf("%g", math.Ceil(number)) + return + } + if argsList.Len() > 1 { + if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + significance = math.Abs(significance) + if significance == 0 { + result = "0" + return + } + } + val, res := math.Modf(number / significance) + if res != 0 { + if number > 0 { + val++ + } + } + result = fmt.Sprintf("%g", val*significance) + return +} + // lcm returns the least common multiple of two supplied integers. func lcm(a, b float64) float64 { a = math.Trunc(a) @@ -1168,11 +1541,11 @@ func (fn *formulaFuncs) LCM(argsList *list.List) (result string, err error) { nums = []float64{} ) for arg := argsList.Front(); arg != nil; arg = arg.Next() { - token := arg.Value.(efp.Token) - if token.TValue == "" { + token := arg.Value.(formulaArg).Value + if token == "" { continue } - if val, err = strconv.ParseFloat(token.TValue, 64); err != nil { + if val, err = strconv.ParseFloat(token, 64); err != nil { return } nums = append(nums, val) @@ -1197,6 +1570,151 @@ func (fn *formulaFuncs) LCM(argsList *list.List) (result string, err error) { return } +// LN function calculates the natural logarithm of a given number. The syntax +// of the function is: +// +// LN(number) +// +func (fn *formulaFuncs) LN(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("LN requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Log(number)) + return +} + +// LOG function calculates the logarithm of a given number, to a supplied +// base. The syntax of the function is: +// +// LOG(number,[base]) +// +func (fn *formulaFuncs) LOG(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("LOG requires at least 1 argument") + return + } + if argsList.Len() > 2 { + err = errors.New("LOG allows at most 2 arguments") + return + } + var number, base float64 = 0, 10 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if argsList.Len() > 1 { + if base, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + } + if number == 0 { + err = errors.New(formulaErrorNUM) + return + } + if base == 0 { + err = errors.New(formulaErrorNUM) + return + } + if base == 1 { + err = errors.New(formulaErrorDIV) + return + } + result = fmt.Sprintf("%g", math.Log(number)/math.Log(base)) + return +} + +// LOG10 function calculates the base 10 logarithm of a given number. The +// syntax of the function is: +// +// LOG10(number) +// +func (fn *formulaFuncs) LOG10(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("LOG10 requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Log10(number)) + return +} + +func minor(sqMtx [][]float64, idx int) [][]float64 { + ret := [][]float64{} + for i := range sqMtx { + if i == 0 { + continue + } + row := []float64{} + for j := range sqMtx { + if j == idx { + continue + } + row = append(row, sqMtx[i][j]) + } + ret = append(ret, row) + } + return ret +} + +// det determinant of the 2x2 matrix. +func det(sqMtx [][]float64) float64 { + if len(sqMtx) == 2 { + m00 := sqMtx[0][0] + m01 := sqMtx[0][1] + m10 := sqMtx[1][0] + m11 := sqMtx[1][1] + return m00*m11 - m10*m01 + } + var res, sgn float64 = 0, 1 + for j := range sqMtx { + res += sgn * sqMtx[0][j] * det(minor(sqMtx, j)) + sgn *= -1 + } + return res +} + +// MDETERM calculates the determinant of a square matrix. The +// syntax of the function is: +// +// MDETERM(array) +// +func (fn *formulaFuncs) MDETERM(argsList *list.List) (result string, err error) { + var num float64 + var rows int + var numMtx = [][]float64{} + var strMtx = [][]string{} + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + if len(arg.Value.(formulaArg).Matrix) == 0 { + break + } + strMtx = append(strMtx, arg.Value.(formulaArg).Matrix) + rows++ + } + for _, row := range strMtx { + if len(row) != rows { + err = errors.New(formulaErrorVALUE) + return + } + numRow := []float64{} + for _, ele := range row { + if num, err = strconv.ParseFloat(ele, 64); err != nil { + return + } + numRow = append(numRow, num) + } + numMtx = append(numMtx, numRow) + } + result = fmt.Sprintf("%g", det(numMtx)) + return +} + // POWER function calculates a given number, raised to a supplied power. // The syntax of the function is: // @@ -1208,10 +1726,10 @@ func (fn *formulaFuncs) POWER(argsList *list.List) (result string, err error) { return } var x, y float64 - if x, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } - if y, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { return } if x == 0 && y == 0 { @@ -1234,11 +1752,11 @@ func (fn *formulaFuncs) POWER(argsList *list.List) (result string, err error) { func (fn *formulaFuncs) PRODUCT(argsList *list.List) (result string, err error) { var val, product float64 = 0, 1 for arg := argsList.Front(); arg != nil; arg = arg.Next() { - token := arg.Value.(efp.Token) - if token.TValue == "" { + token := arg.Value.(formulaArg) + if token.Value == "" { continue } - if val, err = strconv.ParseFloat(token.TValue, 64); err != nil { + if val, err = strconv.ParseFloat(token.Value, 64); err != nil { return } product = product * val @@ -1256,11 +1774,11 @@ func (fn *formulaFuncs) PRODUCT(argsList *list.List) (result string, err error) // func (fn *formulaFuncs) SIGN(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("SIGN requires 1 numeric arguments") + err = errors.New("SIGN requires 1 numeric argument") return } var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } if val < 0 { @@ -1282,18 +1800,23 @@ func (fn *formulaFuncs) SIGN(argsList *list.List) (result string, err error) { // func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) { if argsList.Len() != 1 { - err = errors.New("SQRT requires 1 numeric arguments") + err = errors.New("SQRT requires 1 numeric argument") return } - var val float64 - if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + var res float64 + var value = argsList.Front().Value.(formulaArg).Value + if value == "" { + result = "0" return } - if val < 0 { + if res, err = strconv.ParseFloat(value, 64); err != nil { + return + } + if res < 0 { err = errors.New(formulaErrorNUM) return } - result = fmt.Sprintf("%g", math.Sqrt(val)) + result = fmt.Sprintf("%g", math.Sqrt(res)) return } @@ -1305,11 +1828,11 @@ func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) { func (fn *formulaFuncs) SUM(argsList *list.List) (result string, err error) { var val, sum float64 for arg := argsList.Front(); arg != nil; arg = arg.Next() { - token := arg.Value.(efp.Token) - if token.TValue == "" { + token := arg.Value.(formulaArg) + if token.Value == "" { continue } - if val, err = strconv.ParseFloat(token.TValue, 64); err != nil { + if val, err = strconv.ParseFloat(token.Value, 64); err != nil { return } sum += val @@ -1329,10 +1852,10 @@ func (fn *formulaFuncs) QUOTIENT(argsList *list.List) (result string, err error) return } var x, y float64 - if x, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil { + if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } - if y, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil { + if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { return } if y == 0 { -- cgit v1.2.1 From de34ecaacee83633977298d0424e8eb5eaacb876 Mon Sep 17 00:00:00 2001 From: xuri Date: Thu, 7 May 2020 00:15:54 +0800 Subject: #65 fn: MOD, MROUND, MULTINOMIAL, MUNIT, ODD, PI, RADIANS, RAND, RANDBETWEEN, ROMAN --- calc.go | 338 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++----- 1 file changed, 313 insertions(+), 25 deletions(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index 2ab3d61..fb94e27 100644 --- a/calc.go +++ b/calc.go @@ -12,13 +12,16 @@ package excelize import ( + "bytes" "container/list" "errors" "fmt" "math" + "math/rand" "reflect" "strconv" "strings" + "time" "github.com/xuri/efp" ) @@ -1715,6 +1718,168 @@ func (fn *formulaFuncs) MDETERM(argsList *list.List) (result string, err error) return } +// MOD function returns the remainder of a division between two supplied +// numbers. The syntax of the function is: +// +// MOD(number,divisor) +// +func (fn *formulaFuncs) MOD(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("MOD requires 2 numeric arguments") + return + } + var number, divisor float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if divisor, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + if divisor == 0 { + err = errors.New(formulaErrorDIV) + return + } + trunc, rem := math.Modf(number / divisor) + if rem < 0 { + trunc-- + } + result = fmt.Sprintf("%g", number-divisor*trunc) + return +} + +// MROUND function rounds a supplied number up or down to the nearest multiple +// of a given number. The syntax of the function is: +// +// MOD(number,multiple) +// +func (fn *formulaFuncs) MROUND(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("MROUND requires 2 numeric arguments") + return + } + var number, multiple float64 = 0, 1 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if multiple, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + if multiple == 0 { + err = errors.New(formulaErrorNUM) + return + } + if multiple < 0 && number > 0 || + multiple > 0 && number < 0 { + err = errors.New(formulaErrorNUM) + return + } + number, res := math.Modf(number / multiple) + if math.Trunc(res+0.5) > 0 { + number++ + } + result = fmt.Sprintf("%g", number*multiple) + return +} + +// MULTINOMIAL function calculates the ratio of the factorial of a sum of +// supplied values to the product of factorials of those values. The syntax of +// the function is: +// +// MULTINOMIAL(number1,[number2],...) +// +func (fn *formulaFuncs) MULTINOMIAL(argsList *list.List) (result string, err error) { + var val, num, denom float64 = 0, 0, 1 + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg) + if token.Value == "" { + continue + } + if val, err = strconv.ParseFloat(token.Value, 64); err != nil { + return + } + num += val + denom *= fact(val) + } + result = fmt.Sprintf("%g", fact(num)/denom) + return +} + +// MUNIT function returns the unit matrix for a specified dimension. The +// syntax of the function is: +// +// MUNIT(dimension) +// +func (fn *formulaFuncs) MUNIT(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("MUNIT requires 1 numeric argument") + return + } + var dimension int + if dimension, err = strconv.Atoi(argsList.Front().Value.(formulaArg).Value); err != nil { + return + } + matrix := make([][]float64, 0, dimension) + for i := 0; i < dimension; i++ { + row := make([]float64, dimension) + for j := 0; j < dimension; j++ { + if i == j { + row[j] = float64(1.0) + } else { + row[j] = float64(0.0) + } + } + matrix = append(matrix, row) + } + return +} + +// ODD function ounds a supplied number away from zero (i.e. rounds a positive +// number up and a negative number down), to the next odd number. The syntax +// of the function is: +// +// ODD(number) +// +func (fn *formulaFuncs) ODD(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ODD requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if number == 0 { + result = "1" + return + } + sign := math.Signbit(number) + m, frac := math.Modf((number - 1) / 2) + val := m*2 + 1 + if frac != 0 { + if !sign { + val += 2 + } else { + val -= 2 + } + } + result = fmt.Sprintf("%g", val) + return +} + +// PI function returns the value of the mathematical constant π (pi), accurate +// to 15 digits (14 decimal places). The syntax of the function is: +// +// PI() +// +func (fn *formulaFuncs) PI(argsList *list.List) (result string, err error) { + if argsList.Len() != 0 { + err = errors.New("PI accepts no arguments") + return + } + result = fmt.Sprintf("%g", math.Pi) + return +} + // POWER function calculates a given number, raised to a supplied power. // The syntax of the function is: // @@ -1765,6 +1930,154 @@ func (fn *formulaFuncs) PRODUCT(argsList *list.List) (result string, err error) return } +// QUOTIENT function returns the integer portion of a division between two +// supplied numbers. The syntax of the function is: +// +// QUOTIENT(numerator,denominator) +// +func (fn *formulaFuncs) QUOTIENT(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("QUOTIENT requires 2 numeric arguments") + return + } + var x, y float64 + if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + if y == 0 { + err = errors.New(formulaErrorDIV) + return + } + result = fmt.Sprintf("%g", math.Trunc(x/y)) + return +} + +// RADIANS function converts radians into degrees. The syntax of the function is: +// +// RADIANS(angle) +// +func (fn *formulaFuncs) RADIANS(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("RADIANS requires 1 numeric argument") + return + } + var angle float64 + if angle, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Pi/180.0*angle) + return +} + +// RAND function generates a random real number between 0 and 1. The syntax of +// the function is: +// +// RAND() +// +func (fn *formulaFuncs) RAND(argsList *list.List) (result string, err error) { + if argsList.Len() != 0 { + err = errors.New("RAND accepts no arguments") + return + } + result = fmt.Sprintf("%g", rand.New(rand.NewSource(time.Now().UnixNano())).Float64()) + return +} + +// RANDBETWEEN function generates a random integer between two supplied +// integers. The syntax of the function is: +// +// RANDBETWEEN(bottom,top) +// +func (fn *formulaFuncs) RANDBETWEEN(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("RANDBETWEEN requires 2 numeric arguments") + return + } + var bottom, top int64 + if bottom, err = strconv.ParseInt(argsList.Front().Value.(formulaArg).Value, 10, 64); err != nil { + return + } + if top, err = strconv.ParseInt(argsList.Back().Value.(formulaArg).Value, 10, 64); err != nil { + return + } + if top < bottom { + err = errors.New(formulaErrorNUM) + return + } + result = fmt.Sprintf("%g", float64(rand.New(rand.NewSource(time.Now().UnixNano())).Int63n(top-bottom+1)+bottom)) + return +} + +// romanNumerals defined a numeral system that originated in ancient Rome and +// remained the usual way of writing numbers throughout Europe well into the +// Late Middle Ages. +type romanNumerals struct { + n float64 + s string +} + +var romanTable = [][]romanNumerals{{{1000, "M"}, {900, "CM"}, {500, "D"}, {400, "CD"}, {100, "C"}, {90, "XC"}, {50, "L"}, {40, "XL"}, {10, "X"}, {9, "IX"}, {5, "V"}, {4, "IV"}, {1, "I"}}, + {{1000, "M"}, {950, "LM"}, {900, "CM"}, {500, "D"}, {450, "LD"}, {400, "CD"}, {100, "C"}, {95, "VC"}, {90, "XC"}, {50, "L"}, {45, "VL"}, {40, "XL"}, {10, "X"}, {9, "IX"}, {5, "V"}, {4, "IV"}, {1, "I"}}, + {{1000, "M"}, {990, "XM"}, {950, "LM"}, {900, "CM"}, {500, "D"}, {490, "XD"}, {450, "LD"}, {400, "CD"}, {100, "C"}, {99, "IC"}, {90, "XC"}, {50, "L"}, {45, "VL"}, {40, "XL"}, {10, "X"}, {9, "IX"}, {5, "V"}, {4, "IV"}, {1, "I"}}, + {{1000, "M"}, {995, "VM"}, {990, "XM"}, {950, "LM"}, {900, "CM"}, {500, "D"}, {495, "VD"}, {490, "XD"}, {450, "LD"}, {400, "CD"}, {100, "C"}, {99, "IC"}, {90, "XC"}, {50, "L"}, {45, "VL"}, {40, "XL"}, {10, "X"}, {9, "IX"}, {5, "V"}, {4, "IV"}, {1, "I"}}, + {{1000, "M"}, {999, "IM"}, {995, "VM"}, {990, "XM"}, {950, "LM"}, {900, "CM"}, {500, "D"}, {499, "ID"}, {495, "VD"}, {490, "XD"}, {450, "LD"}, {400, "CD"}, {100, "C"}, {99, "IC"}, {90, "XC"}, {50, "L"}, {45, "VL"}, {40, "XL"}, {10, "X"}, {9, "IX"}, {5, "V"}, {4, "IV"}, {1, "I"}}} + +// ROMAN function converts an arabic number to Roman. I.e. for a supplied +// integer, the function returns a text string depicting the roman numeral +// form of the number. The syntax of the function is: +// +// ROMAN(number,[form]) +// +func (fn *formulaFuncs) ROMAN(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("ROMAN requires at least 1 argument") + return + } + if argsList.Len() > 2 { + err = errors.New("ROMAN allows at most 2 arguments") + return + } + var number float64 + var form int + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if argsList.Len() > 1 { + if form, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); err != nil { + return + } + if form < 0 { + form = 0 + } else if form > 4 { + form = 4 + } + } + decimalTable := romanTable[0] + switch form { + case 1: + decimalTable = romanTable[1] + case 2: + decimalTable = romanTable[2] + case 3: + decimalTable = romanTable[3] + case 4: + decimalTable = romanTable[4] + } + val := math.Trunc(number) + buf := bytes.Buffer{} + for _, r := range decimalTable { + for val >= r.n { + buf.WriteString(r.s) + val -= r.n + } + } + result = buf.String() + return +} + // SIGN function returns the arithmetic sign (+1, -1 or 0) of a supplied // number. I.e. if the number is positive, the Sign function returns +1, if // the number is negative, the function returns -1 and if the number is 0 @@ -1840,28 +2153,3 @@ func (fn *formulaFuncs) SUM(argsList *list.List) (result string, err error) { result = fmt.Sprintf("%g", sum) return } - -// QUOTIENT function returns the integer portion of a division between two -// supplied numbers. The syntax of the function is: -// -// QUOTIENT(numerator,denominator) -// -func (fn *formulaFuncs) QUOTIENT(argsList *list.List) (result string, err error) { - if argsList.Len() != 2 { - err = errors.New("QUOTIENT requires 2 numeric arguments") - return - } - var x, y float64 - if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { - return - } - if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { - return - } - if y == 0 { - err = errors.New(formulaErrorDIV) - return - } - result = fmt.Sprintf("%g", math.Trunc(x/y)) - return -} -- cgit v1.2.1 From 08185c398a0bf352662efa64ebb17c8746556969 Mon Sep 17 00:00:00 2001 From: xuri Date: Fri, 8 May 2020 00:31:17 +0800 Subject: #65, fn: ROUND, ROUNDDOWN, ROUNDUP, SEC, SECH, SIN, SINH, SQRTPI, TAN, TANH, TRUNC --- calc.go | 260 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 260 insertions(+) (limited to 'calc.go') diff --git a/calc.go b/calc.go index fb94e27..cc39ba5 100644 --- a/calc.go +++ b/calc.go @@ -2078,6 +2078,141 @@ func (fn *formulaFuncs) ROMAN(argsList *list.List) (result string, err error) { return } +type roundMode byte + +const ( + closest roundMode = iota + down + up +) + +// round rounds a supplied number up or down. +func (fn *formulaFuncs) round(number, digits float64, mode roundMode) float64 { + significance := 1.0 + if digits > 0 { + significance = math.Pow(1/10.0, digits) + } else { + significance = math.Pow(10.0, -digits) + } + val, res := math.Modf(number / significance) + switch mode { + case closest: + const eps = 0.499999999 + if res >= eps { + val++ + } else if res <= -eps { + val-- + } + case down: + case up: + if res > 0 { + val++ + } else if res < 0 { + val-- + } + } + return val * significance +} + +// ROUND function rounds a supplied number up or down, to a specified number +// of decimal places. The syntax of the function is: +// +// ROUND(number,num_digits) +// +func (fn *formulaFuncs) ROUND(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("ROUND requires 2 numeric arguments") + return + } + var number, digits float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", fn.round(number, digits, closest)) + return +} + +// ROUNDDOWN function rounds a supplied number down towards zero, to a +// specified number of decimal places. The syntax of the function is: +// +// ROUNDDOWN(number,num_digits) +// +func (fn *formulaFuncs) ROUNDDOWN(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("ROUNDDOWN requires 2 numeric arguments") + return + } + var number, digits float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", fn.round(number, digits, down)) + return +} + +// ROUNDUP function rounds a supplied number up, away from zero, to a +// specified number of decimal places. The syntax of the function is: +// +// ROUNDUP(number,num_digits) +// +func (fn *formulaFuncs) ROUNDUP(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("ROUNDUP requires 2 numeric arguments") + return + } + var number, digits float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", fn.round(number, digits, up)) + return +} + +// SEC function calculates the secant of a given angle. The syntax of the +// function is: +// +// SEC(number) +// +func (fn *formulaFuncs) SEC(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SEC requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Cos(number)) + return +} + +// SECH function calculates the hyperbolic secant (sech) of a supplied angle. +// The syntax of the function is: +// +// SECH(number) +// +func (fn *formulaFuncs) SECH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SECH requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", 1/math.Cosh(number)) + return +} + // SIGN function returns the arithmetic sign (+1, -1 or 0) of a supplied // number. I.e. if the number is positive, the Sign function returns +1, if // the number is negative, the function returns -1 and if the number is 0 @@ -2106,6 +2241,42 @@ func (fn *formulaFuncs) SIGN(argsList *list.List) (result string, err error) { return } +// SIN function calculates the sine of a given angle. The syntax of the +// function is: +// +// SIN(number) +// +func (fn *formulaFuncs) SIN(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SIN requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Sin(number)) + return +} + +// SINH function calculates the hyperbolic sine (sinh) of a supplied number. +// The syntax of the function is: +// +// SINH(number) +// +func (fn *formulaFuncs) SINH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SINH requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Sinh(number)) + return +} + // SQRT function calculates the positive square root of a supplied number. The // syntax of the function is: // @@ -2133,6 +2304,24 @@ func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) { return } +// SQRTPI function returns the square root of a supplied number multiplied by +// the mathematical constant, π. The syntax of the function is: +// +// SQRTPI(number) +// +func (fn *formulaFuncs) SQRTPI(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SQRTPI requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Sqrt(number*math.Pi)) + return +} + // SUM function adds together a supplied set of numbers and returns the sum of // these values. The syntax of the function is: // @@ -2153,3 +2342,74 @@ func (fn *formulaFuncs) SUM(argsList *list.List) (result string, err error) { result = fmt.Sprintf("%g", sum) return } + +// TAN function calculates the tangent of a given angle. The syntax of the +// function is: +// +// TAN(number) +// +func (fn *formulaFuncs) TAN(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("TAN requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Tan(number)) + return +} + +// TANH function calculates the hyperbolic tangent (tanh) of a supplied +// number. The syntax of the function is: +// +// TANH(number) +// +func (fn *formulaFuncs) TANH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("TANH requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Tanh(number)) + return +} + +// TRUNC function truncates a supplied number to a specified number of decimal +// places. The syntax of the function is: +// +// TRUNC(number,[number_digits]) +// +func (fn *formulaFuncs) TRUNC(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("TRUNC requires at least 1 argument") + return + } + var number, digits, adjust, rtrim float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if argsList.Len() > 1 { + if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + digits = math.Floor(digits) + } + adjust = math.Pow(10, digits) + x := int((math.Abs(number) - math.Abs(float64(int(number)))) * adjust) + if x != 0 { + if rtrim, err = strconv.ParseFloat(strings.TrimRight(strconv.Itoa(x), "0"), 64); err != nil { + return + } + } + if (digits > 0) && (rtrim < adjust/10) { + result = fmt.Sprintf("%g", number) + return + } + result = fmt.Sprintf("%g", float64(int(number*adjust))/adjust) + return +} -- cgit v1.2.1 From 4188dc7a4a650200c697fd47ad28ba346bf786a6 Mon Sep 17 00:00:00 2001 From: xuri Date: Sat, 9 May 2020 00:32:36 +0800 Subject: - fn: SUMSQ - resolve ineffectual assignment - handle exception with invalid formula - update range resolver --- calc.go | 123 ++++++++++++++++++++++++++++++++++++++++++++++++++++------------ 1 file changed, 100 insertions(+), 23 deletions(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index cc39ba5..f85d16a 100644 --- a/calc.go +++ b/calc.go @@ -259,12 +259,18 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) } optStack.Pop() } + if opdStack.Len() == 0 { + return efp.Token{}, errors.New("formula not valid") + } return opdStack.Peek().(efp.Token), err } // calculate evaluate basic arithmetic operations. func calculate(opdStack *Stack, opt efp.Token) error { if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorPrefix { + if opdStack.Len() < 1 { + return errors.New("formula not valid") + } opd := opdStack.Pop().(efp.Token) opdVal, err := strconv.ParseFloat(opd.TValue, 64) if err != nil { @@ -274,6 +280,9 @@ func calculate(opdStack *Stack, opt efp.Token) error { opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } if opt.TValue == "+" { + 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) @@ -288,6 +297,9 @@ func calculate(opdStack *Stack, opt efp.Token) error { opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorInfix { + 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) @@ -302,6 +314,9 @@ func calculate(opdStack *Stack, opt efp.Token) error { opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } if opt.TValue == "*" { + 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) @@ -316,6 +331,9 @@ func calculate(opdStack *Stack, opt efp.Token) error { opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } if opt.TValue == "/" { + 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) @@ -444,12 +462,13 @@ func (f *File) parseReference(sheet, reference string) (result []string, matrix } // rangeResolver extract value as string from given reference and range list. -// This function will not ignore the empty cell. Note that the result of 3D -// range references may be different from Excel in some cases, for example, -// A1:A2:A2:B3 in Excel will include B1, but we wont. +// This function will not ignore the empty cell. For example, +// A1:A2:A2:B3 will be reference A1:B3. func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, matrix [][]string, err error) { + var fromRow, toRow, fromCol, toCol int = 1, 1, 1, 1 + var sheet string filter := map[string]string{} - // extract value from ranges + // prepare value range for temp := cellRanges.Front(); temp != nil; temp = temp.Next() { cr := temp.Value.(cellRange) if cr.From.Sheet != cr.To.Sheet { @@ -457,22 +476,59 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, } rng := []int{cr.From.Col, cr.From.Row, cr.To.Col, cr.To.Row} sortCoordinates(rng) - matrix = [][]string{} - for row := rng[1]; row <= rng[3]; row++ { + if cr.From.Row < fromRow { + fromRow = cr.From.Row + } + if cr.From.Col < fromCol { + fromCol = cr.From.Col + } + if cr.To.Row > fromRow { + toRow = cr.To.Row + } + if cr.To.Col > toCol { + toCol = cr.To.Col + } + if cr.From.Sheet != "" { + sheet = cr.From.Sheet + } + } + for temp := cellRefs.Front(); temp != nil; temp = temp.Next() { + cr := temp.Value.(cellRef) + if cr.Sheet != "" { + sheet = cr.Sheet + } + if cr.Row < fromRow { + fromRow = cr.Row + } + if cr.Col < fromCol { + fromCol = cr.Col + } + if cr.Row > fromRow { + toRow = cr.Row + } + if cr.Col > toCol { + toCol = cr.Col + } + } + // extract value from ranges + if cellRanges.Len() > 0 { + for row := fromRow; row <= toRow; row++ { var matrixRow = []string{} - for col := rng[0]; col <= rng[2]; col++ { + for col := fromCol; col <= toCol; col++ { var cell, value string if cell, err = CoordinatesToCellName(col, row); err != nil { return } - if value, err = f.GetCellValue(cr.From.Sheet, cell); err != nil { + if value, err = f.GetCellValue(sheet, cell); err != nil { return } filter[cell] = value matrixRow = append(matrixRow, value) + result = append(result, value) } matrix = append(matrix, matrixRow) } + return } // extract value from references for temp := cellRefs.Front(); temp != nil; temp = temp.Next() { @@ -824,7 +880,7 @@ func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error) err = errors.New("CEILING allows at most 2 arguments") return } - var number, significance float64 = 0, 1 + number, significance, res := 0.0, 1.0, 0.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -844,7 +900,7 @@ func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error) result = fmt.Sprintf("%g", math.Ceil(number)) return } - number, res := math.Modf(number / significance) + number, res = math.Modf(number / significance) if res > 0 { number++ } @@ -866,7 +922,7 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err err = errors.New("CEILING.MATH allows at most 3 arguments") return } - var number, significance, mode float64 = 0, 1, 1 + number, significance, mode := 0.0, 1.0, 1.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -914,7 +970,7 @@ func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err err = errors.New("CEILING.PRECISE allows at most 2 arguments") return } - var number, significance float64 = 0, 1 + number, significance := 0.0, 1.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -955,7 +1011,7 @@ func (fn *formulaFuncs) COMBIN(argsList *list.List) (result string, err error) { err = errors.New("COMBIN requires 2 argument") return } - var number, chosen, val float64 = 0, 0, 1 + number, chosen, val := 0.0, 0.0, 1.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -1274,7 +1330,7 @@ func (fn *formulaFuncs) FACTDOUBLE(argsList *list.List) (result string, err erro err = errors.New("FACTDOUBLE requires 1 numeric argument") return } - var number, val float64 = 0, 1 + number, val := 0.0, 1.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -1298,7 +1354,7 @@ func (fn *formulaFuncs) FLOOR(argsList *list.List) (result string, err error) { err = errors.New("FLOOR requires 2 numeric arguments") return } - var number, significance float64 = 0, 1 + var number, significance float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -1333,7 +1389,7 @@ func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error err = errors.New("FLOOR.MATH allows at most 3 arguments") return } - var number, significance, mode float64 = 0, 1, 1 + number, significance, mode := 0.0, 1.0, 1.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -1376,7 +1432,7 @@ func (fn *formulaFuncs) FLOORPRECISE(argsList *list.List) (result string, err er err = errors.New("FLOOR.PRECISE allows at most 2 arguments") return } - var number, significance float64 = 0, 1 + var number, significance float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -1488,7 +1544,7 @@ func (fn *formulaFuncs) ISOCEILING(argsList *list.List) (result string, err erro err = errors.New("ISO.CEILING allows at most 2 arguments") return } - var number, significance float64 = 0, 1 + var number, significance float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -1605,7 +1661,7 @@ func (fn *formulaFuncs) LOG(argsList *list.List) (result string, err error) { err = errors.New("LOG allows at most 2 arguments") return } - var number, base float64 = 0, 10 + number, base := 0.0, 10.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -1757,7 +1813,7 @@ func (fn *formulaFuncs) MROUND(argsList *list.List) (result string, err error) { err = errors.New("MROUND requires 2 numeric arguments") return } - var number, multiple float64 = 0, 1 + var number, multiple float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { return } @@ -1788,7 +1844,7 @@ func (fn *formulaFuncs) MROUND(argsList *list.List) (result string, err error) { // MULTINOMIAL(number1,[number2],...) // func (fn *formulaFuncs) MULTINOMIAL(argsList *list.List) (result string, err error) { - var val, num, denom float64 = 0, 0, 1 + val, num, denom := 0.0, 0.0, 1.0 for arg := argsList.Front(); arg != nil; arg = arg.Next() { token := arg.Value.(formulaArg) if token.Value == "" { @@ -1915,7 +1971,7 @@ func (fn *formulaFuncs) POWER(argsList *list.List) (result string, err error) { // PRODUCT(number1,[number2],...) // func (fn *formulaFuncs) PRODUCT(argsList *list.List) (result string, err error) { - var val, product float64 = 0, 1 + val, product := 0.0, 1.0 for arg := argsList.Front(); arg != nil; arg = arg.Next() { token := arg.Value.(formulaArg) if token.Value == "" { @@ -2088,7 +2144,7 @@ const ( // round rounds a supplied number up or down. func (fn *formulaFuncs) round(number, digits float64, mode roundMode) float64 { - significance := 1.0 + var significance float64 if digits > 0 { significance = math.Pow(1/10.0, digits) } else { @@ -2343,6 +2399,27 @@ func (fn *formulaFuncs) SUM(argsList *list.List) (result string, err error) { return } +// SUMSQ function returns the sum of squares of a supplied set of values. The +// syntax of the function is: +// +// SUMSQ(number1,[number2],...) +// +func (fn *formulaFuncs) SUMSQ(argsList *list.List) (result string, err error) { + var val, sq float64 + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg) + if token.Value == "" { + continue + } + if val, err = strconv.ParseFloat(token.Value, 64); err != nil { + return + } + sq += val * val + } + result = fmt.Sprintf("%g", sq) + return +} + // TAN function calculates the tangent of a given angle. The syntax of the // function is: // -- cgit v1.2.1 From 882abb80988b7c50286dd2e6c6589fab10662db6 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 10 May 2020 16:56:08 +0800 Subject: - formula engine: reduce cyclomatic complexity - styles: allow empty and default cell formats, #628 --- calc.go | 303 +++++++++++++++++++++++++++++++++++++--------------------------- 1 file changed, 176 insertions(+), 127 deletions(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index f85d16a..3d4a8be 100644 --- a/calc.go +++ b/calc.go @@ -63,8 +63,8 @@ type formulaArg struct { type formulaFuncs struct{} // CalcCellValue provides a function to get calculated cell value. This -// feature is currently in beta. Array formula, table formula and some other -// formulas are not supported currently. +// feature is currently in working processing. Array formula, table formula +// and some other formulas are not supported currently. func (f *File) CalcCellValue(sheet, cell string) (result string, err error) { var ( formula string @@ -265,6 +265,89 @@ 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") + } + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal + rOpdVal + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcAdd 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) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal - rOpdVal + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcAdd 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) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal * rOpdVal + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcAdd 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) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal / rOpdVal + if rOpdVal == 0 { + return errors.New(formulaErrorDIV) + } + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + // calculate evaluate basic arithmetic operations. func calculate(opdStack *Stack, opt efp.Token) error { if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorPrefix { @@ -279,80 +362,69 @@ 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 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) - if err != nil { + if err := calcAdd(opdStack); err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) - if err != nil { - return err - } - result := lOpdVal + rOpdVal - opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorInfix { - 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) - if err != nil { + if err := calcSubtract(opdStack); err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) - if err != nil { - return err - } - result := lOpdVal - rOpdVal - opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } if opt.TValue == "*" { - 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) - if err != nil { + if err := calcMultiply(opdStack); err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) - if err != nil { - return err - } - result := lOpdVal * rOpdVal - opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } if opt.TValue == "/" { - 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) - if err != nil { + if err := calcDivide(opdStack); err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) - if err != nil { - return err - } - result := lOpdVal / rOpdVal - if rOpdVal == 0 { - return errors.New(formulaErrorDIV) - } - opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } return nil } +// parseOperatorPrefixToken parse operator prefix token. +func (f *File) parseOperatorPrefixToken(optStack, opdStack *Stack, token efp.Token) (err error) { + if optStack.Len() == 0 { + optStack.Push(token) + } else { + tokenPriority := getPriority(token) + topOpt := optStack.Peek().(efp.Token) + topOptPriority := getPriority(topOpt) + if tokenPriority > topOptPriority { + optStack.Push(token) + } else { + for tokenPriority <= topOptPriority { + optStack.Pop() + if err = calculate(opdStack, topOpt); err != nil { + return + } + if optStack.Len() > 0 { + topOpt = optStack.Peek().(efp.Token) + topOptPriority = getPriority(topOpt) + continue + } + break + } + optStack.Push(token) + } + } + return +} + +// 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 == "/" { + return true + } + return false +} + // parseToken parse basic arithmetic operator priority and evaluate based on // operators and operands. func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Stack) error { @@ -369,30 +441,9 @@ func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Sta token.TType = efp.TokenTypeOperand token.TSubType = efp.TokenSubTypeNumber } - if (token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix) || token.TValue == "+" || token.TValue == "-" || token.TValue == "*" || token.TValue == "/" { - if optStack.Len() == 0 { - optStack.Push(token) - } else { - tokenPriority := getPriority(token) - topOpt := optStack.Peek().(efp.Token) - topOptPriority := getPriority(topOpt) - if tokenPriority > topOptPriority { - optStack.Push(token) - } else { - for tokenPriority <= topOptPriority { - optStack.Pop() - if err := calculate(opdStack, topOpt); err != nil { - return err - } - if optStack.Len() > 0 { - topOpt = optStack.Peek().(efp.Token) - topOptPriority = getPriority(topOpt) - continue - } - break - } - optStack.Push(token) - } + if isOperatorPrefixToken(token) { + if err := f.parseOperatorPrefixToken(optStack, opdStack, token); err != nil { + return err } } if token.TType == efp.TokenTypeSubexpression && token.TSubType == efp.TokenSubTypeStart { // ( @@ -461,11 +512,44 @@ func (f *File) parseReference(sheet, reference string) (result []string, matrix return } +// prepareValueRange prepare value range. +func prepareValueRange(cr cellRange, valueRange []int) { + if cr.From.Row < valueRange[0] { + valueRange[0] = cr.From.Row + } + if cr.From.Col < valueRange[2] { + valueRange[2] = cr.From.Col + } + if cr.To.Row > valueRange[0] { + valueRange[1] = cr.To.Row + } + if cr.To.Col > valueRange[3] { + valueRange[3] = cr.To.Col + } +} + +// prepareValueRef prepare value reference. +func prepareValueRef(cr cellRef, valueRange []int) { + if cr.Row < valueRange[0] { + valueRange[0] = cr.Row + } + if cr.Col < valueRange[2] { + valueRange[2] = cr.Col + } + if cr.Row > valueRange[0] { + valueRange[1] = cr.Row + } + if cr.Col > valueRange[3] { + valueRange[3] = cr.Col + } +} + // rangeResolver extract value as string from given reference and range list. -// This function will not ignore the empty cell. For example, -// A1:A2:A2:B3 will be reference A1:B3. +// This function will not ignore the empty cell. For example, A1:A2:A2:B3 will +// be reference A1:B3. func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, matrix [][]string, err error) { - var fromRow, toRow, fromCol, toCol int = 1, 1, 1, 1 + // value range order: from row, to row, from column, to column + valueRange := []int{1, 1, 1, 1} var sheet string filter := map[string]string{} // prepare value range @@ -476,18 +560,7 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, } rng := []int{cr.From.Col, cr.From.Row, cr.To.Col, cr.To.Row} sortCoordinates(rng) - if cr.From.Row < fromRow { - fromRow = cr.From.Row - } - if cr.From.Col < fromCol { - fromCol = cr.From.Col - } - if cr.To.Row > fromRow { - toRow = cr.To.Row - } - if cr.To.Col > toCol { - toCol = cr.To.Col - } + prepareValueRange(cr, valueRange) if cr.From.Sheet != "" { sheet = cr.From.Sheet } @@ -497,24 +570,13 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, if cr.Sheet != "" { sheet = cr.Sheet } - if cr.Row < fromRow { - fromRow = cr.Row - } - if cr.Col < fromCol { - fromCol = cr.Col - } - if cr.Row > fromRow { - toRow = cr.Row - } - if cr.Col > toCol { - toCol = cr.Col - } + prepareValueRef(cr, valueRange) } // extract value from ranges if cellRanges.Len() > 0 { - for row := fromRow; row <= toRow; row++ { + for row := valueRange[0]; row <= valueRange[1]; row++ { var matrixRow = []string{} - for col := fromCol; col <= toCol; col++ { + for col := valueRange[2]; col <= valueRange[3]; col++ { var cell, value string if cell, err = CoordinatesToCellName(col, row); err != nil { return @@ -672,28 +734,15 @@ func (fn *formulaFuncs) ARABIC(argsList *list.List) (result string, err error) { err = errors.New("ARABIC requires 1 numeric argument") return } + charMap := map[rune]float64{'I': 1, 'V': 5, 'X': 10, 'L': 50, 'C': 100, 'D': 500, 'M': 1000} val, last, prefix := 0.0, 0.0, 1.0 for _, char := range argsList.Front().Value.(formulaArg).Value { digit := 0.0 - switch char { - case '-': + if char == '-' { prefix = -1 continue - case 'I': - digit = 1 - case 'V': - digit = 5 - case 'X': - digit = 10 - case 'L': - digit = 50 - case 'C': - digit = 100 - case 'D': - digit = 500 - case 'M': - digit = 1000 } + digit, _ = charMap[char] val += digit switch { case last == digit && (last == 5 || last == 50 || last == 500): @@ -850,7 +899,7 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) { return } if radix < 2 || radix > 36 { - err = errors.New("radix must be an integer ≥ 2 and ≤ 36") + err = errors.New("radix must be an integer >= 2 and <= 36") return } if argsList.Len() > 2 { -- cgit v1.2.1 From 0feb819d4c08ab52e806214b23d673001bd9fe3e Mon Sep 17 00:00:00 2001 From: xuri Date: Mon, 11 May 2020 00:01:22 +0800 Subject: updated test and go.mod --- calc.go | 141 ++++++++++++++++++++++++++++++++++++++++++++++++++++------------ 1 file changed, 116 insertions(+), 25 deletions(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index 3d4a8be..61b6dac 100644 --- a/calc.go +++ b/calc.go @@ -644,6 +644,7 @@ func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Abs(val)) @@ -663,6 +664,7 @@ func (fn *formulaFuncs) ACOS(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Acos(val)) @@ -681,6 +683,7 @@ func (fn *formulaFuncs) ACOSH(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Acosh(val)) @@ -700,6 +703,7 @@ func (fn *formulaFuncs) ACOT(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Pi/2-math.Atan(val)) @@ -718,6 +722,7 @@ func (fn *formulaFuncs) ACOTH(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Atanh(1/val)) @@ -774,6 +779,7 @@ func (fn *formulaFuncs) ASIN(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Asin(val)) @@ -792,6 +798,7 @@ func (fn *formulaFuncs) ASINH(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Asinh(val)) @@ -811,6 +818,7 @@ func (fn *formulaFuncs) ATAN(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Atan(val)) @@ -829,6 +837,7 @@ func (fn *formulaFuncs) ATANH(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Atanh(val)) @@ -848,34 +857,17 @@ func (fn *formulaFuncs) ATAN2(argsList *list.List) (result string, err error) { } var x, y float64 if x, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if y, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Atan2(x, y)) return } -// gcd returns the greatest common divisor of two supplied integers. -func gcd(x, y float64) float64 { - x, y = math.Trunc(x), math.Trunc(y) - if x == 0 { - return y - } - if y == 0 { - return x - } - for x != y { - if x > y { - x = x - y - } else { - y = y - x - } - } - return x -} - // BASE function converts a number into a supplied base (radix), and returns a // text representation of the calculated value. The syntax of the function is: // @@ -893,9 +885,11 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) { var number float64 var radix, minLength int if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if radix, err = strconv.Atoi(argsList.Front().Next().Value.(formulaArg).Value); err != nil { + err = errors.New(formulaErrorVALUE) return } if radix < 2 || radix > 36 { @@ -904,6 +898,7 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) { } if argsList.Len() > 2 { if minLength, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); err != nil { + err = errors.New(formulaErrorVALUE) return } } @@ -931,6 +926,7 @@ func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error) } number, significance, res := 0.0, 1.0, 0.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if number < 0 { @@ -938,6 +934,7 @@ func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error) } if argsList.Len() > 1 { if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } } @@ -973,6 +970,7 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err } number, significance, mode := 0.0, 1.0, 1.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if number < 0 { @@ -980,6 +978,7 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err } if argsList.Len() > 1 { if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } } @@ -989,6 +988,7 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err } if argsList.Len() > 2 { if mode, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } } @@ -1021,6 +1021,7 @@ func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err } number, significance := 0.0, 1.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if number < 0 { @@ -1032,6 +1033,7 @@ func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err } if argsList.Len() > 1 { if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } significance = math.Abs(significance) @@ -1062,9 +1064,11 @@ func (fn *formulaFuncs) COMBIN(argsList *list.List) (result string, err error) { } number, chosen, val := 0.0, 0.0, 1.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } number, chosen = math.Trunc(number), math.Trunc(chosen) @@ -1095,9 +1099,11 @@ func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error) } var number, chosen float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } number, chosen = math.Trunc(number), math.Trunc(chosen) @@ -1131,6 +1137,7 @@ func (fn *formulaFuncs) COS(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Cos(val)) @@ -1149,6 +1156,7 @@ func (fn *formulaFuncs) COSH(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Cosh(val)) @@ -1167,10 +1175,11 @@ func (fn *formulaFuncs) COT(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if val == 0 { - err = errors.New(formulaErrorNAME) + err = errors.New(formulaErrorDIV) return } result = fmt.Sprintf("%g", math.Tan(val)) @@ -1189,10 +1198,11 @@ func (fn *formulaFuncs) COTH(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if val == 0 { - err = errors.New(formulaErrorNAME) + err = errors.New(formulaErrorDIV) return } result = fmt.Sprintf("%g", math.Tanh(val)) @@ -1211,10 +1221,11 @@ func (fn *formulaFuncs) CSC(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if val == 0 { - err = errors.New(formulaErrorNAME) + err = errors.New(formulaErrorDIV) return } result = fmt.Sprintf("%g", 1/math.Sin(val)) @@ -1233,10 +1244,11 @@ func (fn *formulaFuncs) CSCH(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if val == 0 { - err = errors.New(formulaErrorNAME) + err = errors.New(formulaErrorDIV) return } result = fmt.Sprintf("%g", 1/math.Sinh(val)) @@ -1256,6 +1268,7 @@ func (fn *formulaFuncs) DECIMAL(argsList *list.List) (result string, err error) var text = argsList.Front().Value.(formulaArg).Value var radix int if radix, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); err != nil { + err = errors.New(formulaErrorVALUE) return } if len(text) > 2 && (strings.HasPrefix(text, "0x") || strings.HasPrefix(text, "0X")) { @@ -1263,7 +1276,7 @@ func (fn *formulaFuncs) DECIMAL(argsList *list.List) (result string, err error) } val, err := strconv.ParseInt(text, radix, 64) if err != nil { - err = errors.New(formulaErrorNUM) + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", float64(val)) @@ -1282,10 +1295,11 @@ func (fn *formulaFuncs) DEGREES(argsList *list.List) (result string, err error) } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if val == 0 { - err = errors.New(formulaErrorNAME) + err = errors.New(formulaErrorDIV) return } result = fmt.Sprintf("%g", 180.0/math.Pi*val) @@ -1305,6 +1319,7 @@ func (fn *formulaFuncs) EVEN(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } sign := math.Signbit(number) @@ -1333,6 +1348,7 @@ func (fn *formulaFuncs) EXP(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = strings.ToUpper(fmt.Sprintf("%g", math.Exp(number))) @@ -1360,6 +1376,7 @@ func (fn *formulaFuncs) FACT(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if number < 0 { @@ -1381,10 +1398,12 @@ func (fn *formulaFuncs) FACTDOUBLE(argsList *list.List) (result string, err erro } number, val := 0.0, 1.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if number < 0 { err = errors.New(formulaErrorNUM) + return } for i := math.Trunc(number); i > 1; i -= 2 { val *= i @@ -1405,13 +1424,16 @@ func (fn *formulaFuncs) FLOOR(argsList *list.List) (result string, err error) { } var number, significance float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if significance < 0 && number >= 0 { err = errors.New(formulaErrorNUM) + return } val := number val, res := math.Modf(val / significance) @@ -1440,6 +1462,7 @@ func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error } number, significance, mode := 0.0, 1.0, 1.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if number < 0 { @@ -1447,6 +1470,7 @@ func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error } if argsList.Len() > 1 { if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } } @@ -1456,6 +1480,7 @@ func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error } if argsList.Len() > 2 { if mode, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } } @@ -1483,6 +1508,7 @@ func (fn *formulaFuncs) FLOORPRECISE(argsList *list.List) (result string, err er } var number, significance float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if number < 0 { @@ -1494,6 +1520,7 @@ func (fn *formulaFuncs) FLOORPRECISE(argsList *list.List) (result string, err er } if argsList.Len() > 1 { if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } significance = math.Abs(significance) @@ -1512,6 +1539,25 @@ func (fn *formulaFuncs) FLOORPRECISE(argsList *list.List) (result string, err er return } +// gcd returns the greatest common divisor of two supplied integers. +func gcd(x, y float64) float64 { + x, y = math.Trunc(x), math.Trunc(y) + if x == 0 { + return y + } + if y == 0 { + return x + } + for x != y { + if x > y { + x = x - y + } else { + y = y - x + } + } + return x +} + // GCD function returns the greatest common divisor of two or more supplied // integers. The syntax of the function is: // @@ -1532,6 +1578,7 @@ func (fn *formulaFuncs) GCD(argsList *list.List) (result string, err error) { continue } if val, err = strconv.ParseFloat(token, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } nums = append(nums, val) @@ -1568,6 +1615,7 @@ func (fn *formulaFuncs) INT(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } val, frac := math.Modf(number) @@ -1595,6 +1643,7 @@ func (fn *formulaFuncs) ISOCEILING(argsList *list.List) (result string, err erro } var number, significance float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if number < 0 { @@ -1606,6 +1655,7 @@ func (fn *formulaFuncs) ISOCEILING(argsList *list.List) (result string, err erro } if argsList.Len() > 1 { if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } significance = math.Abs(significance) @@ -1654,6 +1704,7 @@ func (fn *formulaFuncs) LCM(argsList *list.List) (result string, err error) { continue } if val, err = strconv.ParseFloat(token, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } nums = append(nums, val) @@ -1690,6 +1741,7 @@ func (fn *formulaFuncs) LN(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Log(number)) @@ -1712,10 +1764,12 @@ func (fn *formulaFuncs) LOG(argsList *list.List) (result string, err error) { } number, base := 0.0, 10.0 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if argsList.Len() > 1 { if base, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } } @@ -1747,6 +1801,7 @@ func (fn *formulaFuncs) LOG10(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Log10(number)) @@ -1835,9 +1890,11 @@ func (fn *formulaFuncs) MOD(argsList *list.List) (result string, err error) { } var number, divisor float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if divisor, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if divisor == 0 { @@ -1864,9 +1921,11 @@ func (fn *formulaFuncs) MROUND(argsList *list.List) (result string, err error) { } var number, multiple float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if multiple, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if multiple == 0 { @@ -1900,6 +1959,7 @@ func (fn *formulaFuncs) MULTINOMIAL(argsList *list.List) (result string, err err continue } if val, err = strconv.ParseFloat(token.Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } num += val @@ -1921,6 +1981,7 @@ func (fn *formulaFuncs) MUNIT(argsList *list.List) (result string, err error) { } var dimension int if dimension, err = strconv.Atoi(argsList.Front().Value.(formulaArg).Value); err != nil { + err = errors.New(formulaErrorVALUE) return } matrix := make([][]float64, 0, dimension) @@ -1951,6 +2012,7 @@ func (fn *formulaFuncs) ODD(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if number == 0 { @@ -1997,9 +2059,11 @@ func (fn *formulaFuncs) POWER(argsList *list.List) (result string, err error) { } var x, y float64 if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if x == 0 && y == 0 { @@ -2027,6 +2091,7 @@ func (fn *formulaFuncs) PRODUCT(argsList *list.List) (result string, err error) continue } if val, err = strconv.ParseFloat(token.Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } product = product * val @@ -2047,9 +2112,11 @@ func (fn *formulaFuncs) QUOTIENT(argsList *list.List) (result string, err error) } var x, y float64 if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if y == 0 { @@ -2071,6 +2138,7 @@ func (fn *formulaFuncs) RADIANS(argsList *list.List) (result string, err error) } var angle float64 if angle, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Pi/180.0*angle) @@ -2103,9 +2171,11 @@ func (fn *formulaFuncs) RANDBETWEEN(argsList *list.List) (result string, err err } var bottom, top int64 if bottom, err = strconv.ParseInt(argsList.Front().Value.(formulaArg).Value, 10, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if top, err = strconv.ParseInt(argsList.Back().Value.(formulaArg).Value, 10, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if top < bottom { @@ -2148,10 +2218,12 @@ func (fn *formulaFuncs) ROMAN(argsList *list.List) (result string, err error) { var number float64 var form int if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if argsList.Len() > 1 { if form, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); err != nil { + err = errors.New(formulaErrorVALUE) return } if form < 0 { @@ -2231,9 +2303,11 @@ func (fn *formulaFuncs) ROUND(argsList *list.List) (result string, err error) { } var number, digits float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", fn.round(number, digits, closest)) @@ -2252,9 +2326,11 @@ func (fn *formulaFuncs) ROUNDDOWN(argsList *list.List) (result string, err error } var number, digits float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", fn.round(number, digits, down)) @@ -2273,9 +2349,11 @@ func (fn *formulaFuncs) ROUNDUP(argsList *list.List) (result string, err error) } var number, digits float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", fn.round(number, digits, up)) @@ -2294,6 +2372,7 @@ func (fn *formulaFuncs) SEC(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Cos(number)) @@ -2312,6 +2391,7 @@ func (fn *formulaFuncs) SECH(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", 1/math.Cosh(number)) @@ -2332,6 +2412,7 @@ func (fn *formulaFuncs) SIGN(argsList *list.List) (result string, err error) { } var val float64 if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if val < 0 { @@ -2358,6 +2439,7 @@ func (fn *formulaFuncs) SIN(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Sin(number)) @@ -2376,6 +2458,7 @@ func (fn *formulaFuncs) SINH(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Sinh(number)) @@ -2399,6 +2482,7 @@ func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) { return } if res, err = strconv.ParseFloat(value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if res < 0 { @@ -2421,6 +2505,7 @@ func (fn *formulaFuncs) SQRTPI(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Sqrt(number*math.Pi)) @@ -2440,6 +2525,7 @@ func (fn *formulaFuncs) SUM(argsList *list.List) (result string, err error) { continue } if val, err = strconv.ParseFloat(token.Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } sum += val @@ -2461,6 +2547,7 @@ func (fn *formulaFuncs) SUMSQ(argsList *list.List) (result string, err error) { continue } if val, err = strconv.ParseFloat(token.Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } sq += val * val @@ -2481,6 +2568,7 @@ func (fn *formulaFuncs) TAN(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Tan(number)) @@ -2499,6 +2587,7 @@ func (fn *formulaFuncs) TANH(argsList *list.List) (result string, err error) { } var number float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } result = fmt.Sprintf("%g", math.Tanh(number)) @@ -2517,10 +2606,12 @@ func (fn *formulaFuncs) TRUNC(argsList *list.List) (result string, err error) { } var number, digits, adjust, rtrim float64 if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } if argsList.Len() > 1 { if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + err = errors.New(formulaErrorVALUE) return } digits = math.Floor(digits) -- cgit v1.2.1 From 2efc7107ff30dc7f1e1a798082616ee488f99489 Mon Sep 17 00:00:00 2001 From: xuri Date: Thu, 21 May 2020 22:57:58 +0800 Subject: - transform the range to the matrix on the first arg of the formula - typo fix - reset cell with and height when insert picture into merged cell with autofit --- calc.go | 22 ++++++++++------------ 1 file changed, 10 insertions(+), 12 deletions(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index 61b6dac..bff7866 100644 --- a/calc.go +++ b/calc.go @@ -56,7 +56,7 @@ type cellRange struct { // formulaArg is the argument of a formula or function. type formulaArg struct { Value string - Matrix []string + Matrix [][]string } // formulaFuncs is the type of the formula functions. @@ -172,8 +172,8 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) } for idx, val := range result { arg := formulaArg{Value: val} - if idx < len(matrix) { - arg.Matrix = matrix[idx] + if idx == 0 { + arg.Matrix = matrix } argsList.PushBack(arg) } @@ -1850,17 +1850,13 @@ func det(sqMtx [][]float64) float64 { // func (fn *formulaFuncs) MDETERM(argsList *list.List) (result string, err error) { var num float64 - var rows int var numMtx = [][]float64{} - var strMtx = [][]string{} - for arg := argsList.Front(); arg != nil; arg = arg.Next() { - if len(arg.Value.(formulaArg).Matrix) == 0 { - break - } - strMtx = append(strMtx, arg.Value.(formulaArg).Matrix) - rows++ + var strMtx = argsList.Front().Value.(formulaArg).Matrix + if argsList.Len() < 1 { + return } - for _, row := range strMtx { + var rows = len(strMtx) + for _, row := range argsList.Front().Value.(formulaArg).Matrix { if len(row) != rows { err = errors.New(formulaErrorVALUE) return @@ -2630,3 +2626,5 @@ func (fn *formulaFuncs) TRUNC(argsList *list.List) (result string, err error) { result = fmt.Sprintf("%g", float64(int(number*adjust))/adjust) return } + +// Statistical functions -- cgit v1.2.1