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 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ calc_test.go | 116 +++++++++++ excelize.go | 8 +- lib.go | 46 +++++ sheet.go | 28 +-- xmlChartSheet.go | 4 +- 6 files changed, 788 insertions(+), 19 deletions(-) create mode 100644 calc.go create mode 100644 calc_test.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 +} diff --git a/calc_test.go b/calc_test.go new file mode 100644 index 0000000..0ebe37e --- /dev/null +++ b/calc_test.go @@ -0,0 +1,116 @@ +package excelize + +import ( + "testing" + + "github.com/stretchr/testify/assert" +) + +func TestCalcCellValue(t *testing.T) { + prepareData := func() *File { + f := NewFile() + f.SetCellValue("Sheet1", "A1", 1) + f.SetCellValue("Sheet1", "A2", 2) + f.SetCellValue("Sheet1", "A3", 3) + f.SetCellValue("Sheet1", "A4", 0) + f.SetCellValue("Sheet1", "B1", 4) + return f + } + + mathCalc := map[string]string{ + "=SUM(1,2)": "3", + "=SUM(1,2+3)": "6", + "=SUM(SUM(1,2),2)": "5", + "=(-2-SUM(-4+7))*5": "-25", + "SUM(1,2,3,4,5,6,7)": "28", + "=SUM(1,2)+SUM(1,2)": "6", + "=1+SUM(SUM(1,2*3),4)": "12", + "=1+SUM(SUM(1,-2*3),4)": "0", + "=(-2-SUM(-4*(7+7)))*5": "270", + "=SUM(SUM(1+2/1)*2-3/2,2)": "6.5", + "=((3+5*2)+3)/5+(-6)/4*2+3": "3.2", + "=1+SUM(SUM(1,2*3),4)*-4/2+5+(4+2)*3": "2", + "=1+SUM(SUM(1,2*3),4)*4/3+5+(4+2)*3": "38.666666666666664", + // POWER + "=POWER(4,2)": "16", + // SQRT + "=SQRT(4)": "2", + // QUOTIENT + "=QUOTIENT(5, 2)": "2", + "=QUOTIENT(4.5, 3.1)": "1", + "=QUOTIENT(-10, 3)": "-3", + } + for formula, expected := range mathCalc { + f := prepareData() + assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula)) + result, err := f.CalcCellValue("Sheet1", "C1") + assert.NoError(t, err) + assert.Equal(t, expected, result) + } + mathCalcError := map[string]string{ + // POWER + "=POWER(0,0)": "#NUM!", + "=POWER(0,-1)": "#DIV/0!", + "=POWER(1)": "POWER requires 2 numeric arguments", + // SQRT + "=SQRT(-1)": "#NUM!", + "=SQRT(1,2)": "SQRT requires 1 numeric arguments", + // QUOTIENT + "=QUOTIENT(1,0)": "#DIV/0!", + "=QUOTIENT(1)": "QUOTIENT requires 2 numeric arguments", + } + for formula, expected := range mathCalcError { + f := prepareData() + assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula)) + result, err := f.CalcCellValue("Sheet1", "C1") + assert.EqualError(t, err, expected) + assert.Equal(t, "", result) + } + + referenceCalc := map[string]string{ + "=A1/A3": "0.3333333333333333", + "=SUM(A1:A2)": "3", + "=SUM(Sheet1!A1,A2)": "3", + "=(-2-SUM(-4+A2))*5": "0", + "=SUM(Sheet1!A1:Sheet1!A1:A2,A2)": "5", + "=SUM(A1,A2,A3)*SUM(2,3)": "30", + "=1+SUM(SUM(A1+A2/A3)*(2-3),2)": "1.3333333333333335", + "=A1/A2/SUM(A1:A2:B1)": "0.07142857142857142", + "=A1/A2/SUM(A1:A2:B1)*A3": "0.21428571428571427", + // PRODUCT + "=PRODUCT(Sheet1!A1:Sheet1!A1:A2,A2)": "4", + } + for formula, expected := range referenceCalc { + f := prepareData() + assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula)) + result, err := f.CalcCellValue("Sheet1", "C1") + assert.NoError(t, err) + assert.Equal(t, expected, result) + } + + referenceCalcError := map[string]string{ + "=1+SUM(SUM(A1+A2/A4)*(2-3),2)": "#DIV/0!", + } + for formula, expected := range referenceCalcError { + f := prepareData() + assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula)) + result, err := f.CalcCellValue("Sheet1", "C1") + assert.EqualError(t, err, expected) + assert.Equal(t, "", result) + } + + // Test get calculated cell value on not formula cell. + f := prepareData() + result, err := f.CalcCellValue("Sheet1", "A1") + assert.NoError(t, err) + assert.Equal(t, "", result) + // Test get calculated cell value on not exists worksheet. + f = prepareData() + _, err = f.CalcCellValue("SheetN", "A1") + assert.EqualError(t, err, "sheet SheetN is not exist") + // Test get calculated cell value with not support formula. + f = prepareData() + assert.NoError(t, f.SetCellFormula("Sheet1", "A1", "=UNSUPPORT(A1)")) + _, err = f.CalcCellValue("Sheet1", "A1") + assert.EqualError(t, err, "not support UNSUPPORT function") +} diff --git a/excelize.go b/excelize.go index 73bc1b5..04e2e85 100644 --- a/excelize.go +++ b/excelize.go @@ -3,9 +3,11 @@ // the LICENSE file. // Package excelize providing a set of functions that allow you to write to -// and read from XLSX files. Support reads and writes XLSX file generated by -// Microsoft Excel™ 2007 and later. Support save file without losing original -// charts of XLSX. This library needs Go version 1.10 or later. +// 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. // // See https://xuri.me/excelize for more information about this package. package excelize diff --git a/lib.go b/lib.go index 83cdb4a..79c7cd4 100644 --- a/lib.go +++ b/lib.go @@ -12,6 +12,7 @@ package excelize import ( "archive/zip" "bytes" + "container/list" "fmt" "io" "log" @@ -305,3 +306,48 @@ func genSheetPasswd(plaintext string) string { password ^= 0xCE4B return strings.ToUpper(strconv.FormatInt(password, 16)) } + +// Stack defined an abstract data type that serves as a collection of elements. +type Stack struct { + list *list.List +} + +// NewStack create a new stack. +func NewStack() *Stack { + list := list.New() + return &Stack{list} +} + +// Push a value onto the top of the stack. +func (stack *Stack) Push(value interface{}) { + stack.list.PushBack(value) +} + +// Pop the top item of the stack and return it. +func (stack *Stack) Pop() interface{} { + e := stack.list.Back() + if e != nil { + stack.list.Remove(e) + return e.Value + } + return nil +} + +// Peek view the top item on the stack. +func (stack *Stack) Peek() interface{} { + e := stack.list.Back() + if e != nil { + return e.Value + } + return nil +} + +// Len return the number of items in the stack. +func (stack *Stack) Len() int { + return stack.list.Len() +} + +// Empty the stack. +func (stack *Stack) Empty() bool { + return stack.list.Len() == 0 +} diff --git a/sheet.go b/sheet.go index 8c7f754..fa858af 100644 --- a/sheet.go +++ b/sheet.go @@ -211,10 +211,10 @@ func replaceRelationshipsBytes(content []byte) []byte { return bytesReplace(content, oldXmlns, newXmlns, -1) } -// SetActiveSheet provides function to set default active worksheet of XLSX by -// given index. Note that active index is different from the index returned by -// function GetSheetMap(). It should be greater or equal to 0 and less than -// total worksheet numbers. +// SetActiveSheet provides a function to set the default active sheet of the +// workbook by a given index. Note that the active index is different from the +// ID returned by function GetSheetMap(). It should be greater or equal to 0 +// and less than the total worksheet numbers. func (f *File) SetActiveSheet(index int) { if index < 0 { index = 0 @@ -327,9 +327,9 @@ func (f *File) getSheetNameByID(ID int) string { return "" } -// GetSheetName provides a function to get worksheet name of XLSX by given -// worksheet index. If given sheet index is invalid, will return an empty -// string. +// GetSheetName provides a function to get the sheet name of the workbook by +// the given sheet index. If the given sheet index is invalid, it will return +// an empty string. func (f *File) GetSheetName(index int) (name string) { for idx, sheet := range f.GetSheetList() { if idx == index { @@ -352,9 +352,9 @@ func (f *File) getSheetID(name string) int { return ID } -// GetSheetIndex provides a function to get worksheet index of XLSX by given -// sheet name. If given worksheet name is invalid, will return an integer type -// value -1. +// GetSheetIndex provides a function to get a sheet index of the workbook by +// the given sheet name. If the given sheet name is invalid, it will return an +// integer type value -1. func (f *File) GetSheetIndex(name string) int { var idx = -1 for index, sheet := range f.GetSheetList() { @@ -365,8 +365,8 @@ func (f *File) GetSheetIndex(name string) int { return idx } -// GetSheetMap provides a function to get worksheet, chartsheet and -// dialogsheet ID and name map of XLSX. For example: +// GetSheetMap provides a function to get worksheets, chart sheets, dialog +// sheets ID and name map of the workbook. For example: // // f, err := excelize.OpenFile("Book1.xlsx") // if err != nil { @@ -387,8 +387,8 @@ func (f *File) GetSheetMap() map[int]string { return sheetMap } -// GetSheetList provides a function to get worksheet, chartsheet and -// dialogsheet name list of workbook. +// GetSheetList provides a function to get worksheets, chart sheets, and +// dialog sheets name list of the workbook. func (f *File) GetSheetList() (list []string) { wb := f.workbookReader() if wb != nil { diff --git a/xmlChartSheet.go b/xmlChartSheet.go index fae5a16..30a0693 100644 --- a/xmlChartSheet.go +++ b/xmlChartSheet.go @@ -51,7 +51,7 @@ type xlsxChartsheetView struct { XMLName xml.Name `xml:"sheetView"` TabSelectedAttr bool `xml:"tabSelected,attr,omitempty"` ZoomScaleAttr uint32 `xml:"zoomScale,attr,omitempty"` - WorkbookViewIdAttr uint32 `xml:"workbookViewId,attr"` + WorkbookViewIDAttr uint32 `xml:"workbookViewId,attr"` ZoomToFitAttr bool `xml:"zoomToFit,attr,omitempty"` ExtLst []*xlsxExtLst `xml:"extLst"` } @@ -78,7 +78,7 @@ type xlsxCustomChartsheetViews struct { // xlsxCustomChartsheetView defines custom view properties for chart sheets. type xlsxCustomChartsheetView struct { XMLName xml.Name `xml:"customChartsheetView"` - GuidAttr string `xml:"guid,attr"` + GUIDAttr string `xml:"guid,attr"` ScaleAttr uint32 `xml:"scale,attr,omitempty"` StateAttr string `xml:"state,attr,omitempty"` ZoomToFitAttr bool `xml:"zoomToFit,attr,omitempty"` -- 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 +++++++++++++++++++++++++++++++++++++++++++++++++++++------ calc_test.go | 51 +++++++++++++-- 2 files changed, 233 insertions(+), 27 deletions(-) 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) // diff --git a/calc_test.go b/calc_test.go index 0ebe37e..84fa955 100644 --- a/calc_test.go +++ b/calc_test.go @@ -18,6 +18,35 @@ func TestCalcCellValue(t *testing.T) { } mathCalc := map[string]string{ + // ABS + "=ABS(-1)": "1", + "=ABS(-6.5)": "6.5", + "=ABS(6.5)": "6.5", + "=ABS(0)": "0", + "=ABS(2-4.5)": "2.5", + // GCD + "=GCD(1,5)": "1", + "=GCD(15,10,25)": "5", + "=GCD(0,8,12)": "4", + "=GCD(7,2)": "1", + // LCM + "=LCM(1,5)": "5", + "=LCM(15,10,25)": "150", + "=LCM(1,8,12)": "24", + "=LCM(7,2)": "14", + // POWER + "=POWER(4,2)": "16", + // PRODUCT + "=PRODUCT(3,6)": "18", + // SIGN + "=SIGN(9.5)": "1", + "=SIGN(-9.5)": "-1", + "=SIGN(0)": "0", + "=SIGN(0.00000001)": "1", + "=SIGN(6-7)": "-1", + // SQRT + "=SQRT(4)": "2", + // SUM "=SUM(1,2)": "3", "=SUM(1,2+3)": "6", "=SUM(SUM(1,2),2)": "5", @@ -31,10 +60,6 @@ func TestCalcCellValue(t *testing.T) { "=((3+5*2)+3)/5+(-6)/4*2+3": "3.2", "=1+SUM(SUM(1,2*3),4)*-4/2+5+(4+2)*3": "2", "=1+SUM(SUM(1,2*3),4)*4/3+5+(4+2)*3": "38.666666666666664", - // POWER - "=POWER(4,2)": "16", - // SQRT - "=SQRT(4)": "2", // QUOTIENT "=QUOTIENT(5, 2)": "2", "=QUOTIENT(4.5, 3.1)": "1", @@ -48,10 +73,23 @@ func TestCalcCellValue(t *testing.T) { assert.Equal(t, expected, result) } mathCalcError := map[string]string{ + // ABS + "=ABS(1,2)": "ABS requires 1 numeric arguments", + "=ABS(~)": `cannot convert cell "~" to coordinates: invalid cell name "~"`, + // GCD + "=GCD()": "GCD requires at least 1 argument", + "=GCD(-1)": "GCD only accepts positive arguments", + "=GCD(1,-1)": "GCD only accepts positive arguments", + // LCM + "=LCM()": "LCM requires at least 1 argument", + "=LCM(-1)": "LCM only accepts positive arguments", + "=LCM(1,-1)": "LCM only accepts positive arguments", // POWER "=POWER(0,0)": "#NUM!", "=POWER(0,-1)": "#DIV/0!", "=POWER(1)": "POWER requires 2 numeric arguments", + // SIGN + "=SIGN()": "SIGN requires 1 numeric arguments", // SQRT "=SQRT(-1)": "#NUM!", "=SQRT(1,2)": "SQRT requires 1 numeric arguments", @@ -68,6 +106,9 @@ func TestCalcCellValue(t *testing.T) { } referenceCalc := map[string]string{ + // PRODUCT + "=PRODUCT(Sheet1!A1:Sheet1!A1:A2,A2)": "4", + // SUM "=A1/A3": "0.3333333333333333", "=SUM(A1:A2)": "3", "=SUM(Sheet1!A1,A2)": "3", @@ -77,8 +118,6 @@ func TestCalcCellValue(t *testing.T) { "=1+SUM(SUM(A1+A2/A3)*(2-3),2)": "1.3333333333333335", "=A1/A2/SUM(A1:A2:B1)": "0.07142857142857142", "=A1/A2/SUM(A1:A2:B1)*A3": "0.21428571428571427", - // PRODUCT - "=PRODUCT(Sheet1!A1:Sheet1!A1:A2,A2)": "4", } for formula, expected := range referenceCalc { f := prepareData() -- 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 +++++++++++++++++++++++++++++++++++++++++++++++++++++------ calc_test.go | 71 +++++++++++- 2 files changed, 394 insertions(+), 39 deletions(-) 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 } diff --git a/calc_test.go b/calc_test.go index 84fa955..bb8ae8a 100644 --- a/calc_test.go +++ b/calc_test.go @@ -24,6 +24,49 @@ func TestCalcCellValue(t *testing.T) { "=ABS(6.5)": "6.5", "=ABS(0)": "0", "=ABS(2-4.5)": "2.5", + // ACOS + "=ACOS(-1)": "3.141592653589793", + "=ACOS(0)": "1.5707963267948966", + // ACOSH + "=ACOSH(1)": "0", + "=ACOSH(2.5)": "1.566799236972411", + "=ACOSH(5)": "2.2924316695611777", + // ACOT + "=_xlfn.ACOT(1)": "0.7853981633974483", + "=_xlfn.ACOT(-2)": "2.677945044588987", + "=_xlfn.ACOT(0)": "1.5707963267948966", + // ACOTH + "=_xlfn.ACOTH(-5)": "-0.2027325540540822", + "=_xlfn.ACOTH(1.1)": "1.5222612188617113", + "=_xlfn.ACOTH(2)": "0.5493061443340548", + // ARABIC + `=_xlfn.ARABIC("IV")`: "4", + `=_xlfn.ARABIC("-IV")`: "-4", + `=_xlfn.ARABIC("MCXX")`: "1120", + `=_xlfn.ARABIC("")`: "0", + // ASIN + "=ASIN(-1)": "-1.5707963267948966", + "=ASIN(0)": "0", + // ASINH + "=ASINH(0)": "0", + "=ASINH(-0.5)": "-0.48121182505960347", + "=ASINH(2)": "1.4436354751788103", + // ATAN + "=ATAN(-1)": "-0.7853981633974483", + "=ATAN(0)": "0", + "=ATAN(1)": "0.7853981633974483", + // ATANH + "=ATANH(-0.8)": "-1.0986122886681098", + "=ATANH(0)": "0", + "=ATANH(0.5)": "0.5493061443340548", + // ATAN2 + "=ATAN2(1,1)": "0.7853981633974483", + "=ATAN2(1,-1)": "-0.7853981633974483", + "=ATAN2(4,0)": "0", + // BASE + "=BASE(12,2)": "1100", + "=BASE(12,2,8)": "00001100", + "=BASE(100000,16)": "186A0", // GCD "=GCD(1,5)": "1", "=GCD(15,10,25)": "5", @@ -74,8 +117,32 @@ func TestCalcCellValue(t *testing.T) { } mathCalcError := map[string]string{ // ABS - "=ABS(1,2)": "ABS requires 1 numeric arguments", - "=ABS(~)": `cannot convert cell "~" to coordinates: invalid cell name "~"`, + "=ABS()": "ABS requires 1 numeric arguments", + "=ABS(~)": `cannot convert cell "~" to coordinates: invalid cell name "~"`, + // ACOS + "=ACOS()": "ACOS requires 1 numeric arguments", + // ACOSH + "=ACOSH()": "ACOSH requires 1 numeric arguments", + // ACOT + "=_xlfn.ACOT()": "ACOT requires 1 numeric arguments", + // ACOTH + "=_xlfn.ACOTH()": "ACOTH requires 1 numeric arguments", + // ARABIC + "_xlfn.ARABIC()": "ARABIC requires 1 numeric arguments", + // ASIN + "=ASIN()": "ASIN requires 1 numeric arguments", + // ASINH + "=ASINH()": "ASINH requires 1 numeric arguments", + // ATAN + "=ATAN()": "ATAN requires 1 numeric arguments", + // ATANH + "=ATANH()": "ATANH requires 1 numeric arguments", + // ATAN2 + "=ATAN2()": "ATAN2 requires 2 numeric arguments", + // BASE + "=BASE()": "BASE requires at least 2 arguments", + "=BASE(1,2,3,4)": "BASE allows at most 3 arguments", + "=BASE(1,1)": "radix must be an integer ≥ 2 and ≤ 36", // GCD "=GCD()": "GCD requires at least 1 argument", "=GCD(-1)": "GCD only accepts positive arguments", -- 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 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++- calc_test.go | 29 +++++++++++++++-- 2 files changed, 126 insertions(+), 4 deletions(-) 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: // diff --git a/calc_test.go b/calc_test.go index bb8ae8a..a14cc0c 100644 --- a/calc_test.go +++ b/calc_test.go @@ -67,6 +67,22 @@ func TestCalcCellValue(t *testing.T) { "=BASE(12,2)": "1100", "=BASE(12,2,8)": "00001100", "=BASE(100000,16)": "186A0", + // CEILING + "=CEILING(22.25,0.1)": "22.3", + "=CEILING(22.25,0.5)": "22.5", + "=CEILING(22.25,1)": "23", + "=CEILING(22.25,10)": "30", + "=CEILING(22.25,20)": "40", + "=CEILING(-22.25,-0.1)": "-22.3", + "=CEILING(-22.25,-1)": "-23", + "=CEILING(-22.25,-5)": "-25", + // _xlfn.CEILING.MATH + "=_xlfn.CEILING.MATH(15.25,1)": "16", + "=_xlfn.CEILING.MATH(15.25,0.1)": "15.3", + "=_xlfn.CEILING.MATH(15.25,5)": "20", + "=_xlfn.CEILING.MATH(-15.25,1)": "-15", + "=_xlfn.CEILING.MATH(-15.25,1,1)": "-15", // should be 16 + "=_xlfn.CEILING.MATH(-15.25,10)": "-10", // GCD "=GCD(1,5)": "1", "=GCD(15,10,25)": "5", @@ -123,11 +139,11 @@ func TestCalcCellValue(t *testing.T) { "=ACOS()": "ACOS requires 1 numeric arguments", // ACOSH "=ACOSH()": "ACOSH requires 1 numeric arguments", - // ACOT + // _xlfn.ACOT "=_xlfn.ACOT()": "ACOT requires 1 numeric arguments", - // ACOTH + // _xlfn.ACOTH "=_xlfn.ACOTH()": "ACOTH requires 1 numeric arguments", - // ARABIC + // _xlfn.ARABIC "_xlfn.ARABIC()": "ARABIC requires 1 numeric arguments", // ASIN "=ASIN()": "ASIN requires 1 numeric arguments", @@ -143,6 +159,13 @@ func TestCalcCellValue(t *testing.T) { "=BASE()": "BASE requires at least 2 arguments", "=BASE(1,2,3,4)": "BASE allows at most 3 arguments", "=BASE(1,1)": "radix must be an integer ≥ 2 and ≤ 36", + // CEILING + "=CEILING()": "CEILING requires at least 1 argument", + "=CEILING(1,2,3)": "CEILING allows at most 2 arguments", + "=CEILING(1,-1)": "negative sig to CEILING invalid", + // _xlfn.CEILING.MATH + "=_xlfn.CEILING.MATH()": "CEILING.MATH requires at least 1 argument", + "=_xlfn.CEILING.MATH(1,2,3,4)": "CEILING.MATH allows at most 3 arguments", // GCD "=GCD()": "GCD requires at least 1 argument", "=GCD(-1)": "GCD only accepts positive arguments", -- 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 ++++++++++++++++++++++++++++++++++++++++++++++------------- calc_test.go | 61 +++++++++++- 2 files changed, 312 insertions(+), 68 deletions(-) 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 { diff --git a/calc_test.go b/calc_test.go index a14cc0c..5d441ff 100644 --- a/calc_test.go +++ b/calc_test.go @@ -83,6 +83,44 @@ func TestCalcCellValue(t *testing.T) { "=_xlfn.CEILING.MATH(-15.25,1)": "-15", "=_xlfn.CEILING.MATH(-15.25,1,1)": "-15", // should be 16 "=_xlfn.CEILING.MATH(-15.25,10)": "-10", + // _xlfn.CEILING.PRECISE + "=_xlfn.CEILING.PRECISE(22.25,0.1)": "22.3", + "=_xlfn.CEILING.PRECISE(22.25,0.5)": "22.5", + "=_xlfn.CEILING.PRECISE(22.25,1)": "23", + "=_xlfn.CEILING.PRECISE(22.25)": "23", + "=_xlfn.CEILING.PRECISE(22.25,10)": "30", + "=_xlfn.CEILING.PRECISE(22.25,0)": "0", + "=_xlfn.CEILING.PRECISE(-22.25,1)": "-22", + "=_xlfn.CEILING.PRECISE(-22.25,-1)": "-22", + "=_xlfn.CEILING.PRECISE(-22.25,5)": "-20", + // COMBIN + "=COMBIN(6,1)": "6", + "=COMBIN(6,2)": "15", + "=COMBIN(6,3)": "20", + "=COMBIN(6,4)": "15", + "=COMBIN(6,5)": "6", + "=COMBIN(6,6)": "1", + // _xlfn.COMBINA + "=_xlfn.COMBINA(6,1)": "6", + "=_xlfn.COMBINA(6,2)": "21", + "=_xlfn.COMBINA(6,3)": "56", + "=_xlfn.COMBINA(6,4)": "126", + "=_xlfn.COMBINA(6,5)": "252", + "=_xlfn.COMBINA(6,6)": "462", + // COS + "=COS(0.785398163)": "0.707106781467586", + "=COS(0)": "1", + // COSH + "=COSH(0)": "1", + "=COSH(0.5)": "1.1276259652063807", + "=COSH(-2)": "3.7621956910836314", + // _xlfn.COT + "_xlfn.COT(0.785398163397448)": "0.9999999999999992", + // _xlfn.COTH + "_xlfn.COTH(-3.14159265358979)": "-0.9962720762207499", + // _xlfn.CSC + "_xlfn.CSC(-6)": "3.5788995472544056", + "_xlfn.CSC(1.5707963267949)": "1", // GCD "=GCD(1,5)": "1", "=GCD(15,10,25)": "5", @@ -144,7 +182,7 @@ func TestCalcCellValue(t *testing.T) { // _xlfn.ACOTH "=_xlfn.ACOTH()": "ACOTH requires 1 numeric arguments", // _xlfn.ARABIC - "_xlfn.ARABIC()": "ARABIC requires 1 numeric arguments", + "=_xlfn.ARABIC()": "ARABIC requires 1 numeric arguments", // ASIN "=ASIN()": "ASIN requires 1 numeric arguments", // ASINH @@ -166,6 +204,27 @@ func TestCalcCellValue(t *testing.T) { // _xlfn.CEILING.MATH "=_xlfn.CEILING.MATH()": "CEILING.MATH requires at least 1 argument", "=_xlfn.CEILING.MATH(1,2,3,4)": "CEILING.MATH allows at most 3 arguments", + // _xlfn.CEILING.PRECISE + "=_xlfn.CEILING.PRECISE()": "CEILING.PRECISE requires at least 1 argument", + "=_xlfn.CEILING.PRECISE(1,2,3)": "CEILING.PRECISE allows at most 2 arguments", + // COMBIN + "=COMBIN()": "COMBIN requires 2 argument", + "=COMBIN(-1,1)": "COMBIN requires number >= number_chosen", + // _xlfn.COMBINA + "=_xlfn.COMBINA()": "COMBINA requires 2 argument", + "=_xlfn.COMBINA(-1,1)": "COMBINA requires number > number_chosen", + "=_xlfn.COMBINA(-1,-1)": "COMBIN requires number >= number_chosen", + // COS + "=COS()": "COS requires 1 numeric arguments", + // COSH + "=COSH()": "COSH requires 1 numeric arguments", + // _xlfn.COT + "=COT()": "COT requires 1 numeric arguments", + // _xlfn.COTH + "=COTH()": "COTH requires 1 numeric arguments", + // _xlfn.CSC + "_xlfn.CSC()": "CSC requires 1 numeric arguments", + "_xlfn.CSC(0)": "#NAME?", // GCD "=GCD()": "GCD requires at least 1 argument", "=GCD(-1)": "GCD only accepts positive arguments", -- 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 +++++++++++++++++++++++++++++++++++++++++++++++++++-------- calc_test.go | 178 +++++++++++++-- 2 files changed, 777 insertions(+), 120 deletions(-) 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 { diff --git a/calc_test.go b/calc_test.go index 5d441ff..c66de8c 100644 --- a/calc_test.go +++ b/calc_test.go @@ -14,6 +14,7 @@ func TestCalcCellValue(t *testing.T) { f.SetCellValue("Sheet1", "A3", 3) f.SetCellValue("Sheet1", "A4", 0) f.SetCellValue("Sheet1", "B1", 4) + f.SetCellValue("Sheet1", "B2", 5) return f } @@ -115,22 +116,108 @@ func TestCalcCellValue(t *testing.T) { "=COSH(0.5)": "1.1276259652063807", "=COSH(-2)": "3.7621956910836314", // _xlfn.COT - "_xlfn.COT(0.785398163397448)": "0.9999999999999992", + "=_xlfn.COT(0.785398163397448)": "0.9999999999999992", // _xlfn.COTH - "_xlfn.COTH(-3.14159265358979)": "-0.9962720762207499", + "=_xlfn.COTH(-3.14159265358979)": "-0.9962720762207499", // _xlfn.CSC - "_xlfn.CSC(-6)": "3.5788995472544056", - "_xlfn.CSC(1.5707963267949)": "1", + "=_xlfn.CSC(-6)": "3.5788995472544056", + "=_xlfn.CSC(1.5707963267949)": "1", + // _xlfn.CSCH + "=_xlfn.CSCH(-3.14159265358979)": "-0.08658953753004724", + // _xlfn.DECIMAL + `=_xlfn.DECIMAL("1100",2)`: "12", + `=_xlfn.DECIMAL("186A0",16)`: "100000", + `=_xlfn.DECIMAL("31L0",32)`: "100000", + `=_xlfn.DECIMAL("70122",8)`: "28754", + // DEGREES + "=DEGREES(1)": "57.29577951308232", + "=DEGREES(2.5)": "143.2394487827058", + // EVEN + "=EVEN(23)": "24", + "=EVEN(2.22)": "4", + "=EVEN(0)": "0", + "=EVEN(-0.3)": "-2", + "=EVEN(-11)": "-12", + "=EVEN(-4)": "-4", + // EXP + "=EXP(100)": "2.6881171418161356E+43", + "=EXP(0.1)": "1.1051709180756477", + "=EXP(0)": "1", + "=EXP(-5)": "0.006737946999085467", + // FACT + "=FACT(3)": "6", + "=FACT(6)": "720", + "=FACT(10)": "3.6288E+06", + // FACTDOUBLE + "=FACTDOUBLE(5)": "15", + "=FACTDOUBLE(8)": "384", + "=FACTDOUBLE(13)": "135135", + // FLOOR + "=FLOOR(26.75,0.1)": "26.700000000000003", + "=FLOOR(26.75,0.5)": "26.5", + "=FLOOR(26.75,1)": "26", + "=FLOOR(26.75,10)": "20", + "=FLOOR(26.75,20)": "20", + "=FLOOR(-26.75,-0.1)": "-26.700000000000003", + "=FLOOR(-26.75,-1)": "-26", + "=FLOOR(-26.75,-5)": "-25", + // _xlfn.FLOOR.MATH + "=_xlfn.FLOOR.MATH(58.55)": "58", + "=_xlfn.FLOOR.MATH(58.55,0.1)": "58.5", + "=_xlfn.FLOOR.MATH(58.55,5)": "55", + "=_xlfn.FLOOR.MATH(58.55,1,1)": "58", + "=_xlfn.FLOOR.MATH(-58.55,1)": "-59", + "=_xlfn.FLOOR.MATH(-58.55,1,-1)": "-58", + "=_xlfn.FLOOR.MATH(-58.55,1,1)": "-59", // should be -58 + "=_xlfn.FLOOR.MATH(-58.55,10)": "-60", + // _xlfn.FLOOR.PRECISE + "=_xlfn.FLOOR.PRECISE(26.75,0.1)": "26.700000000000003", + "=_xlfn.FLOOR.PRECISE(26.75,0.5)": "26.5", + "=_xlfn.FLOOR.PRECISE(26.75,1)": "26", + "=_xlfn.FLOOR.PRECISE(26.75)": "26", + "=_xlfn.FLOOR.PRECISE(26.75,10)": "20", + "=_xlfn.FLOOR.PRECISE(26.75,0)": "0", + "=_xlfn.FLOOR.PRECISE(-26.75,1)": "-27", + "=_xlfn.FLOOR.PRECISE(-26.75,-1)": "-27", + "=_xlfn.FLOOR.PRECISE(-26.75,-5)": "-30", // GCD "=GCD(1,5)": "1", "=GCD(15,10,25)": "5", "=GCD(0,8,12)": "4", "=GCD(7,2)": "1", + // INT + "=INT(100.9)": "100", + "=INT(5.22)": "5", + "=INT(5.99)": "5", + "=INT(-6.1)": "-7", + "=INT(-100.9)": "-101", + // ISO.CEILING + "=ISO.CEILING(22.25)": "23", + "=ISO.CEILING(22.25,1)": "23", + "=ISO.CEILING(22.25,0.1)": "22.3", + "=ISO.CEILING(22.25,10)": "30", + "=ISO.CEILING(-22.25,1)": "-22", + "=ISO.CEILING(-22.25,0.1)": "-22.200000000000003", + "=ISO.CEILING(-22.25,5)": "-20", // LCM "=LCM(1,5)": "5", "=LCM(15,10,25)": "150", "=LCM(1,8,12)": "24", "=LCM(7,2)": "14", + // LN + "=LN(1)": "0", + "=LN(100)": "4.605170185988092", + "=LN(0.5)": "-0.6931471805599453", + // LOG + "=LOG(64,2)": "6", + "=LOG(100)": "2", + "=LOG(4,0.5)": "-2", + "=LOG(500)": "2.6989700043360183", + // LOG10 + "=LOG10(100)": "2", + "=LOG10(1000)": "3", + "=LOG10(0.001)": "-3", + "=LOG10(25)": "1.3979400086720375", // POWER "=POWER(4,2)": "16", // PRODUCT @@ -171,26 +258,26 @@ func TestCalcCellValue(t *testing.T) { } mathCalcError := map[string]string{ // ABS - "=ABS()": "ABS requires 1 numeric arguments", + "=ABS()": "ABS requires 1 numeric argument", "=ABS(~)": `cannot convert cell "~" to coordinates: invalid cell name "~"`, // ACOS - "=ACOS()": "ACOS requires 1 numeric arguments", + "=ACOS()": "ACOS requires 1 numeric argument", // ACOSH - "=ACOSH()": "ACOSH requires 1 numeric arguments", + "=ACOSH()": "ACOSH requires 1 numeric argument", // _xlfn.ACOT - "=_xlfn.ACOT()": "ACOT requires 1 numeric arguments", + "=_xlfn.ACOT()": "ACOT requires 1 numeric argument", // _xlfn.ACOTH - "=_xlfn.ACOTH()": "ACOTH requires 1 numeric arguments", + "=_xlfn.ACOTH()": "ACOTH requires 1 numeric argument", // _xlfn.ARABIC - "=_xlfn.ARABIC()": "ARABIC requires 1 numeric arguments", + "=_xlfn.ARABIC()": "ARABIC requires 1 numeric argument", // ASIN - "=ASIN()": "ASIN requires 1 numeric arguments", + "=ASIN()": "ASIN requires 1 numeric argument", // ASINH - "=ASINH()": "ASINH requires 1 numeric arguments", + "=ASINH()": "ASINH requires 1 numeric argument", // ATAN - "=ATAN()": "ATAN requires 1 numeric arguments", + "=ATAN()": "ATAN requires 1 numeric argument", // ATANH - "=ATANH()": "ATANH requires 1 numeric arguments", + "=ATANH()": "ATANH requires 1 numeric argument", // ATAN2 "=ATAN2()": "ATAN2 requires 2 numeric arguments", // BASE @@ -215,33 +302,75 @@ func TestCalcCellValue(t *testing.T) { "=_xlfn.COMBINA(-1,1)": "COMBINA requires number > number_chosen", "=_xlfn.COMBINA(-1,-1)": "COMBIN requires number >= number_chosen", // COS - "=COS()": "COS requires 1 numeric arguments", + "=COS()": "COS requires 1 numeric argument", // COSH - "=COSH()": "COSH requires 1 numeric arguments", + "=COSH()": "COSH requires 1 numeric argument", // _xlfn.COT - "=COT()": "COT requires 1 numeric arguments", + "=COT()": "COT requires 1 numeric argument", // _xlfn.COTH - "=COTH()": "COTH requires 1 numeric arguments", + "=COTH()": "COTH requires 1 numeric argument", // _xlfn.CSC - "_xlfn.CSC()": "CSC requires 1 numeric arguments", - "_xlfn.CSC(0)": "#NAME?", + "=_xlfn.CSC()": "CSC requires 1 numeric argument", + "=_xlfn.CSC(0)": "#NAME?", + // _xlfn.CSCH + "=_xlfn.CSCH()": "CSCH requires 1 numeric argument", + "=_xlfn.CSCH(0)": "#NAME?", + // _xlfn.DECIMAL + "=_xlfn.DECIMAL()": "DECIMAL requires 2 numeric arguments", + `=_xlfn.DECIMAL("2000", 2)`: "#NUM!", + // DEGREES + "=DEGREES()": "DEGREES requires 1 numeric argument", + // EVEN + "=EVEN()": "EVEN requires 1 numeric argument", + // EXP + "=EXP()": "EXP requires 1 numeric argument", + // FACT + "=FACT()": "FACT requires 1 numeric argument", + "=FACT(-1)": "#NUM!", + // FACTDOUBLE + "=FACTDOUBLE()": "FACTDOUBLE requires 1 numeric argument", + "=FACTDOUBLE(-1)": "#NUM!", + // FLOOR + "=FLOOR()": "FLOOR requires 2 numeric arguments", + "=FLOOR(1,-1)": "#NUM!", + // _xlfn.FLOOR.MATH + "=_xlfn.FLOOR.MATH()": "FLOOR.MATH requires at least 1 argument", + "=_xlfn.FLOOR.MATH(1,2,3,4)": "FLOOR.MATH allows at most 3 arguments", + // _xlfn.FLOOR.PRECISE + "=_xlfn.FLOOR.PRECISE()": "FLOOR.PRECISE requires at least 1 argument", + "=_xlfn.FLOOR.PRECISE(1,2,3)": "FLOOR.PRECISE allows at most 2 arguments", // GCD "=GCD()": "GCD requires at least 1 argument", "=GCD(-1)": "GCD only accepts positive arguments", "=GCD(1,-1)": "GCD only accepts positive arguments", + // INT + "=INT()": "INT requires 1 numeric argument", + // ISO.CEILING + "=ISO.CEILING()": "ISO.CEILING requires at least 1 argument", + "=ISO.CEILING(1,2,3)": "ISO.CEILING allows at most 2 arguments", // LCM "=LCM()": "LCM requires at least 1 argument", "=LCM(-1)": "LCM only accepts positive arguments", "=LCM(1,-1)": "LCM only accepts positive arguments", + // LN + "=LN()": "LN requires 1 numeric argument", + // LOG + "=LOG()": "LOG requires at least 1 argument", + "=LOG(1,2,3)": "LOG allows at most 2 arguments", + "=LOG(0,0)": "#NUM!", + "=LOG(1,0)": "#NUM!", + "=LOG(1,1)": "#DIV/0!", + // LOG10 + "=LOG10()": "LOG10 requires 1 numeric argument", // POWER "=POWER(0,0)": "#NUM!", "=POWER(0,-1)": "#DIV/0!", "=POWER(1)": "POWER requires 2 numeric arguments", // SIGN - "=SIGN()": "SIGN requires 1 numeric arguments", + "=SIGN()": "SIGN requires 1 numeric argument", // SQRT "=SQRT(-1)": "#NUM!", - "=SQRT(1,2)": "SQRT requires 1 numeric arguments", + "=SQRT(1,2)": "SQRT requires 1 numeric argument", // QUOTIENT "=QUOTIENT(1,0)": "#DIV/0!", "=QUOTIENT(1)": "QUOTIENT requires 2 numeric arguments", @@ -255,6 +384,8 @@ func TestCalcCellValue(t *testing.T) { } referenceCalc := map[string]string{ + // MDETERM + "=MDETERM(A1:B2)": "-3", // PRODUCT "=PRODUCT(Sheet1!A1:Sheet1!A1:A2,A2)": "4", // SUM @@ -277,6 +408,9 @@ func TestCalcCellValue(t *testing.T) { } referenceCalcError := map[string]string{ + // MDETERM + "=MDETERM(A1:B3)": "#VALUE!", + // SUM "=1+SUM(SUM(A1+A2/A4)*(2-3),2)": "#DIV/0!", } for formula, expected := range referenceCalcError { -- cgit v1.2.1