diff options
| -rw-r--r-- | calc.go | 1867 | ||||
| -rw-r--r-- | calc_test.go | 438 | ||||
| -rw-r--r-- | excelize.go | 8 | ||||
| -rw-r--r-- | lib.go | 46 | ||||
| -rw-r--r-- | sheet.go | 28 | ||||
| -rw-r--r-- | xmlChartSheet.go | 4 |
6 files changed, 2372 insertions, 19 deletions
@@ -0,0 +1,1867 @@ +// 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 +} + +// 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 +// 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 +// +// 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 := NewStack(), NewStack(), NewStack(), NewStack(), NewStack() + argsList := list.New() + 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, matrix, err := f.parseReference(sheet, token.TValue) + if err != nil { + return efp.Token{TValue: formulaErrorNAME}, err + } + 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) + } + 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() { + argsList.PushBack(formulaArg{ + Value: opfdStack.Pop().(efp.Token).TValue, + }) + } + 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(formulaArg{ + Value: token.TValue, + }) + } + + // 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 { + argsList.PushBack(formulaArg{ + Value: opfdStack.Pop().(efp.Token).TValue, + }) + } + // call formula function to evaluate + 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 + } + argsList.Init() + 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, matrix [][]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, matrix, 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 B1, but we wont. +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() { + 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) + 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 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 + 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 + +// ABS function returns the absolute value of any supplied number. The syntax +// of the function is: +// +// ABS(number) +// +func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ABS requires 1 numeric argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Abs(val)) + 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 argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); 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 argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); 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 argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); 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 argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); 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 argument") + return + } + val, last, prefix := 0.0, 0.0, 1.0 + for _, char := range argsList.Front().Value.(formulaArg).Value { + 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 argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); 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 argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); 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 argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); 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 argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); 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 + if x, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + if y, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); 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) + if x == 0 { + return y + } + if y == 0 { + return x + } + for x != y { + if x > y { + x = x - y + } else { + y = y - x + } + } + return x +} + +// BASE function converts a number into a supplied base (radix), and returns a +// text representation of the calculated value. The syntax of the function is: +// +// 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 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if radix, err = strconv.Atoi(argsList.Front().Next().Value.(formulaArg).Value); err != nil { + return + } + if radix < 2 || radix > 36 { + err = errors.New("radix must be an integer ≥ 2 and ≤ 36") + return + } + if argsList.Len() > 2 { + if minLength, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); 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 +} + +// 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 = 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 { + if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); 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 + 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.Ceil(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 { + if number > 0 { + val++ + } else if mode < 0 { + 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.(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 +} + +// 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.(formulaArg).Value, 64); err != nil { + return + } + if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 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.(formulaArg).Value, 64); err != nil { + return + } + if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 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(formulaArg{ + Value: fmt.Sprintf("%g", number+chosen-1), + }) + args.PushBack(formulaArg{ + Value: fmt.Sprintf("%g", number-1), + }) + 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 argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 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 argument") + return + } + var val float64 + if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 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 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", 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 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", 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 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.Sin(val)) + 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: +// +// GCD(number1,[number2],...) +// +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 + } + var ( + val float64 + nums = []float64{} + ) + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg).Value + if token == "" { + continue + } + if val, err = strconv.ParseFloat(token, 64); err != nil { + return + } + nums = append(nums, val) + } + 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 +} + +// 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) + 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: +// +// LCM(number1,[number2],...) +// +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 + } + var ( + val float64 + nums = []float64{} + ) + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg).Value + if token == "" { + continue + } + if val, err = strconv.ParseFloat(token, 64); err != nil { + return + } + nums = append(nums, val) + } + 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 +} + +// 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: +// +// POWER(number,power) +// +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 + if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + if 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 +} + +// 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(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.(formulaArg) + if token.Value == "" { + continue + } + if val, err = strconv.ParseFloat(token.Value, 64); 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(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SIGN 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 { + 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) +// +func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SQRT requires 1 numeric argument") + return + } + var res float64 + var value = argsList.Front().Value.(formulaArg).Value + if value == "" { + result = "0" + return + } + if res, err = strconv.ParseFloat(value, 64); err != nil { + return + } + if res < 0 { + err = errors.New(formulaErrorNUM) + return + } + result = fmt.Sprintf("%g", math.Sqrt(res)) + return +} + +// 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(argsList *list.List) (result string, err error) { + var val, sum float64 + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg) + if token.Value == "" { + continue + } + if val, err = strconv.ParseFloat(token.Value, 64); err != nil { + return + } + 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) +// +func (fn *formulaFuncs) QUOTIENT(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("QUOTIENT requires 2 numeric arguments") + return + } + var x, y float64 + if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + if y == 0 { + err = errors.New(formulaErrorDIV) + return + } + result = fmt.Sprintf("%g", math.Trunc(x/y)) + return +} diff --git a/calc_test.go b/calc_test.go new file mode 100644 index 0000000..c66de8c --- /dev/null +++ b/calc_test.go @@ -0,0 +1,438 @@ +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) + f.SetCellValue("Sheet1", "B2", 5) + return f + } + + 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", + // 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", + // 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", + // _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", + // _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 + "=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", + "=(-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", + // 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{ + // ABS + "=ABS()": "ABS requires 1 numeric argument", + "=ABS(~)": `cannot convert cell "~" to coordinates: invalid cell name "~"`, + // ACOS + "=ACOS()": "ACOS requires 1 numeric argument", + // ACOSH + "=ACOSH()": "ACOSH requires 1 numeric argument", + // _xlfn.ACOT + "=_xlfn.ACOT()": "ACOT requires 1 numeric argument", + // _xlfn.ACOTH + "=_xlfn.ACOTH()": "ACOTH requires 1 numeric argument", + // _xlfn.ARABIC + "=_xlfn.ARABIC()": "ARABIC requires 1 numeric argument", + // ASIN + "=ASIN()": "ASIN requires 1 numeric argument", + // ASINH + "=ASINH()": "ASINH requires 1 numeric argument", + // ATAN + "=ATAN()": "ATAN requires 1 numeric argument", + // ATANH + "=ATANH()": "ATANH requires 1 numeric argument", + // 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", + // 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", + // _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 argument", + // COSH + "=COSH()": "COSH requires 1 numeric argument", + // _xlfn.COT + "=COT()": "COT requires 1 numeric argument", + // _xlfn.COTH + "=COTH()": "COTH requires 1 numeric argument", + // _xlfn.CSC + "=_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 argument", + // SQRT + "=SQRT(-1)": "#NUM!", + "=SQRT(1,2)": "SQRT requires 1 numeric argument", + // 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{ + // MDETERM + "=MDETERM(A1:B2)": "-3", + // PRODUCT + "=PRODUCT(Sheet1!A1:Sheet1!A1:A2,A2)": "4", + // SUM + "=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", + } + 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{ + // MDETERM + "=MDETERM(A1:B3)": "#VALUE!", + // SUM + "=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 @@ -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 +} @@ -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"` |