summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go520
-rw-r--r--calc_test.go33
2 files changed, 393 insertions, 160 deletions
diff --git a/calc.go b/calc.go
index bff7866..6bb15de 100644
--- a/calc.go
+++ b/calc.go
@@ -19,6 +19,7 @@ import (
"math"
"math/rand"
"reflect"
+ "regexp"
"strconv"
"strings"
"time"
@@ -53,10 +54,39 @@ type cellRange struct {
To cellRef
}
+// formula criteria condition enumeration.
+const (
+ _ byte = iota
+ criteriaEq
+ criteriaLe
+ criteriaGe
+ criteriaL
+ criteriaG
+ criteriaBeg
+ criteriaEnd
+)
+
+// formulaCriteria defined formula criteria parser result.
+type formulaCriteria struct {
+ Type byte
+ Condition string
+}
+
+// ArgType is the type if formula argument type.
+type ArgType byte
+
+// Formula argument types enumeration.
+const (
+ ArgUnknown ArgType = iota
+ ArgString
+ ArgMatrix
+)
+
// formulaArg is the argument of a formula or function.
type formulaArg struct {
- Value string
- Matrix [][]string
+ String string
+ Matrix [][]formulaArg
+ Type ArgType
}
// formulaFuncs is the type of the formula functions.
@@ -150,36 +180,30 @@ 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
}
- if len(result) != 1 {
+ if result.Type != ArgString {
return efp.Token{}, errors.New(formulaErrorVALUE)
}
opfdStack.Push(efp.Token{
TType: efp.TokenTypeOperand,
TSubType: efp.TokenSubTypeNumber,
- TValue: result[0],
+ TValue: result.String,
})
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)
+ result, 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 == 0 {
- arg.Matrix = matrix
- }
- argsList.PushBack(arg)
- }
- if len(result) == 0 {
+ if result.Type == ArgUnknown {
return efp.Token{}, errors.New(formulaErrorVALUE)
}
+ argsList.PushBack(result)
continue
}
}
@@ -201,7 +225,8 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error)
}
if !opfdStack.Empty() {
argsList.PushBack(formulaArg{
- Value: opfdStack.Pop().(efp.Token).TValue,
+ String: opfdStack.Pop().(efp.Token).TValue,
+ Type: ArgString,
})
}
continue
@@ -214,7 +239,8 @@ 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(formulaArg{
- Value: token.TValue,
+ String: token.TValue,
+ Type: ArgString,
})
}
@@ -232,7 +258,8 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error)
// push opfd to args
if opfdStack.Len() > 0 {
argsList.PushBack(formulaArg{
- Value: opfdStack.Pop().(efp.Token).TValue,
+ String: opfdStack.Pop().(efp.Token).TValue,
+ Type: ArgString,
})
}
// call formula function to evaluate
@@ -430,14 +457,14 @@ func isOperatorPrefixToken(token efp.Token) bool {
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)
}
- if len(result) != 1 {
+ if result.Type != ArgString {
return errors.New(formulaErrorVALUE)
}
- token.TValue = result[0]
+ token.TValue = result.String
token.TType = efp.TokenTypeOperand
token.TSubType = efp.TokenSubTypeNumber
}
@@ -468,7 +495,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, matrix [][]string, err error) {
+func (f *File) parseReference(sheet, reference string) (arg formulaArg, err error) {
reference = strings.Replace(reference, "$", "", -1)
refs, cellRanges, cellRefs := list.New(), list.New(), list.New()
for _, ref := range strings.Split(reference, ":") {
@@ -507,39 +534,38 @@ func (f *File) parseReference(sheet, reference string) (result []string, matrix
cellRefs.PushBack(e.Value.(cellRef))
refs.Remove(e)
}
-
- result, matrix, err = f.rangeResolver(cellRefs, cellRanges)
+ arg, err = f.rangeResolver(cellRefs, cellRanges)
return
}
// prepareValueRange prepare value range.
func prepareValueRange(cr cellRange, valueRange []int) {
- if cr.From.Row < valueRange[0] {
+ if cr.From.Row < valueRange[0] || valueRange[0] == 0 {
valueRange[0] = cr.From.Row
}
- if cr.From.Col < valueRange[2] {
+ if cr.From.Col < valueRange[2] || valueRange[2] == 0 {
valueRange[2] = cr.From.Col
}
- if cr.To.Row > valueRange[0] {
+ if cr.To.Row > valueRange[1] || valueRange[1] == 0 {
valueRange[1] = cr.To.Row
}
- if cr.To.Col > valueRange[3] {
+ if cr.To.Col > valueRange[3] || valueRange[3] == 0 {
valueRange[3] = cr.To.Col
}
}
// prepareValueRef prepare value reference.
func prepareValueRef(cr cellRef, valueRange []int) {
- if cr.Row < valueRange[0] {
+ if cr.Row < valueRange[0] || valueRange[0] == 0 {
valueRange[0] = cr.Row
}
- if cr.Col < valueRange[2] {
+ if cr.Col < valueRange[2] || valueRange[2] == 0 {
valueRange[2] = cr.Col
}
- if cr.Row > valueRange[0] {
+ if cr.Row > valueRange[1] || valueRange[1] == 0 {
valueRange[1] = cr.Row
}
- if cr.Col > valueRange[3] {
+ if cr.Col > valueRange[3] || valueRange[3] == 0 {
valueRange[3] = cr.Col
}
}
@@ -547,11 +573,10 @@ func prepareValueRef(cr cellRef, valueRange []int) {
// rangeResolver extract value as string from given reference and range list.
// This function will not ignore the empty cell. For example, A1:A2:A2:B3 will
// be reference A1:B3.
-func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, matrix [][]string, err error) {
+func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (arg formulaArg, err error) {
// value range order: from row, to row, from column, to column
- valueRange := []int{1, 1, 1, 1}
+ valueRange := []int{0, 0, 0, 0}
var sheet string
- filter := map[string]string{}
// prepare value range
for temp := cellRanges.Front(); temp != nil; temp = temp.Next() {
cr := temp.Value.(cellRange)
@@ -560,6 +585,7 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string,
}
rng := []int{cr.From.Col, cr.From.Row, cr.To.Col, cr.To.Row}
sortCoordinates(rng)
+ cr.From.Col, cr.From.Row, cr.To.Col, cr.To.Row = rng[0], rng[1], rng[2], rng[3]
prepareValueRange(cr, valueRange)
if cr.From.Sheet != "" {
sheet = cr.From.Sheet
@@ -574,8 +600,9 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string,
}
// extract value from ranges
if cellRanges.Len() > 0 {
+ arg.Type = ArgMatrix
for row := valueRange[0]; row <= valueRange[1]; row++ {
- var matrixRow = []string{}
+ var matrixRow = []formulaArg{}
for col := valueRange[2]; col <= valueRange[3]; col++ {
var cell, value string
if cell, err = CoordinatesToCellName(col, row); err != nil {
@@ -584,11 +611,12 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string,
if value, err = f.GetCellValue(sheet, cell); err != nil {
return
}
- filter[cell] = value
- matrixRow = append(matrixRow, value)
- result = append(result, value)
+ matrixRow = append(matrixRow, formulaArg{
+ String: value,
+ Type: ArgString,
+ })
}
- matrix = append(matrix, matrixRow)
+ arg.Matrix = append(arg.Matrix, matrixRow)
}
return
}
@@ -599,13 +627,10 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string,
if cell, err = CoordinatesToCellName(cr.Col, cr.Row); err != nil {
return
}
- if filter[cell], err = f.GetCellValue(cr.Sheet, cell); err != nil {
+ if arg.String, err = f.GetCellValue(cr.Sheet, cell); err != nil {
return
}
- }
-
- for _, val := range filter {
- result = append(result, val)
+ arg.Type = ArgString
}
return
}
@@ -630,6 +655,90 @@ func callFuncByName(receiver interface{}, name string, params []reflect.Value) (
return
}
+// formulaCriteriaParser parse formula criteria.
+func formulaCriteriaParser(exp string) (fc *formulaCriteria) {
+ fc = &formulaCriteria{}
+ if exp == "" {
+ return
+ }
+ if match := regexp.MustCompile(`^([0-9]+)$`).FindStringSubmatch(exp); len(match) > 1 {
+ fc.Type, fc.Condition = criteriaEq, match[1]
+ return
+ }
+ if match := regexp.MustCompile(`^=(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
+ fc.Type, fc.Condition = criteriaEq, match[1]
+ return
+ }
+ if match := regexp.MustCompile(`^<(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
+ fc.Type, fc.Condition = criteriaLe, match[1]
+ return
+ }
+ if match := regexp.MustCompile(`^>(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
+ fc.Type, fc.Condition = criteriaGe, match[1]
+ return
+ }
+ if match := regexp.MustCompile(`^<=(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
+ fc.Type, fc.Condition = criteriaL, match[1]
+ return
+ }
+ if match := regexp.MustCompile(`^>=(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
+ fc.Type, fc.Condition = criteriaG, match[1]
+ return
+ }
+ if strings.Contains(exp, "*") {
+ if strings.HasPrefix(exp, "*") {
+ fc.Type, fc.Condition = criteriaEnd, strings.TrimPrefix(exp, "*")
+ }
+ if strings.HasSuffix(exp, "*") {
+ fc.Type, fc.Condition = criteriaBeg, strings.TrimSuffix(exp, "*")
+ }
+ return
+ }
+ fc.Type, fc.Condition = criteriaEq, exp
+ return
+}
+
+// formulaCriteriaEval evaluate formula criteria expression.
+func formulaCriteriaEval(val string, criteria *formulaCriteria) (result bool, err error) {
+ var value, expected float64
+ var prepareValue = func(val, cond string) (value float64, expected float64, err error) {
+ value, _ = strconv.ParseFloat(val, 64)
+ if expected, err = strconv.ParseFloat(criteria.Condition, 64); err != nil {
+ return
+ }
+ return
+ }
+ switch criteria.Type {
+ case criteriaEq:
+ return val == criteria.Condition, err
+ case criteriaLe:
+ if value, expected, err = prepareValue(val, criteria.Condition); err != nil {
+ return
+ }
+ return value <= expected, err
+ case criteriaGe:
+ if value, expected, err = prepareValue(val, criteria.Condition); err != nil {
+ return
+ }
+ return value >= expected, err
+ case criteriaL:
+ if value, expected, err = prepareValue(val, criteria.Condition); err != nil {
+ return
+ }
+ return value < expected, err
+ case criteriaG:
+ if value, expected, err = prepareValue(val, criteria.Condition); err != nil {
+ return
+ }
+ return value > expected, err
+ case criteriaBeg:
+ return strings.HasPrefix(val, criteria.Condition), err
+ case criteriaEnd:
+ return strings.HasSuffix(val, criteria.Condition), err
+ }
+ return
+}
+
// Math and Trigonometric functions
// ABS function returns the absolute value of any supplied number. The syntax
@@ -643,7 +752,7 @@ func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -663,7 +772,7 @@ func (fn *formulaFuncs) ACOS(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -682,7 +791,7 @@ func (fn *formulaFuncs) ACOSH(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -702,7 +811,7 @@ func (fn *formulaFuncs) ACOT(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -721,7 +830,7 @@ func (fn *formulaFuncs) ACOTH(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -741,7 +850,7 @@ func (fn *formulaFuncs) ARABIC(argsList *list.List) (result string, err error) {
}
charMap := map[rune]float64{'I': 1, 'V': 5, 'X': 10, 'L': 50, 'C': 100, 'D': 500, 'M': 1000}
val, last, prefix := 0.0, 0.0, 1.0
- for _, char := range argsList.Front().Value.(formulaArg).Value {
+ for _, char := range argsList.Front().Value.(formulaArg).String {
digit := 0.0
if char == '-' {
prefix = -1
@@ -778,7 +887,7 @@ func (fn *formulaFuncs) ASIN(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -797,7 +906,7 @@ func (fn *formulaFuncs) ASINH(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -817,7 +926,7 @@ func (fn *formulaFuncs) ATAN(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -836,7 +945,7 @@ func (fn *formulaFuncs) ATANH(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -856,11 +965,11 @@ func (fn *formulaFuncs) ATAN2(argsList *list.List) (result string, err error) {
return
}
var x, y float64
- if x, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if x, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if y, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if y, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -884,11 +993,11 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) {
}
var number float64
var radix, minLength int
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if radix, err = strconv.Atoi(argsList.Front().Next().Value.(formulaArg).Value); err != nil {
+ if radix, err = strconv.Atoi(argsList.Front().Next().Value.(formulaArg).String); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -897,7 +1006,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.(formulaArg).Value); err != nil {
+ if minLength, err = strconv.Atoi(argsList.Back().Value.(formulaArg).String); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -925,7 +1034,7 @@ func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error)
return
}
number, significance, res := 0.0, 1.0, 0.0
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -933,7 +1042,7 @@ func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error)
significance = -1
}
if argsList.Len() > 1 {
- if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -969,7 +1078,7 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err
return
}
number, significance, mode := 0.0, 1.0, 1.0
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -977,7 +1086,7 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err
significance = -1
}
if argsList.Len() > 1 {
- if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(formulaArg).Value, 64); err != nil {
+ if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -987,7 +1096,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.(formulaArg).Value, 64); err != nil {
+ if mode, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1020,7 +1129,7 @@ func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err
return
}
number, significance := 0.0, 1.0
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1032,7 +1141,7 @@ func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err
return
}
if argsList.Len() > 1 {
- if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1063,11 +1172,11 @@ func (fn *formulaFuncs) COMBIN(argsList *list.List) (result string, err error) {
return
}
number, chosen, val := 0.0, 0.0, 1.0
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1098,11 +1207,11 @@ func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error)
return
}
var number, chosen float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1117,10 +1226,12 @@ func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error)
}
args := list.New()
args.PushBack(formulaArg{
- Value: fmt.Sprintf("%g", number+chosen-1),
+ String: fmt.Sprintf("%g", number+chosen-1),
+ Type: ArgString,
})
args.PushBack(formulaArg{
- Value: fmt.Sprintf("%g", number-1),
+ String: fmt.Sprintf("%g", number-1),
+ Type: ArgString,
})
return fn.COMBIN(args)
}
@@ -1136,7 +1247,7 @@ func (fn *formulaFuncs) COS(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1155,7 +1266,7 @@ func (fn *formulaFuncs) COSH(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1174,7 +1285,7 @@ func (fn *formulaFuncs) COT(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1197,7 +1308,7 @@ func (fn *formulaFuncs) COTH(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1220,7 +1331,7 @@ func (fn *formulaFuncs) CSC(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1243,7 +1354,7 @@ func (fn *formulaFuncs) CSCH(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1265,9 +1376,9 @@ func (fn *formulaFuncs) DECIMAL(argsList *list.List) (result string, err error)
err = errors.New("DECIMAL requires 2 numeric arguments")
return
}
- var text = argsList.Front().Value.(formulaArg).Value
+ var text = argsList.Front().Value.(formulaArg).String
var radix int
- if radix, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); err != nil {
+ if radix, err = strconv.Atoi(argsList.Back().Value.(formulaArg).String); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1294,7 +1405,7 @@ func (fn *formulaFuncs) DEGREES(argsList *list.List) (result string, err error)
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1318,7 +1429,7 @@ func (fn *formulaFuncs) EVEN(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1347,7 +1458,7 @@ func (fn *formulaFuncs) EXP(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1375,7 +1486,7 @@ func (fn *formulaFuncs) FACT(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1397,7 +1508,7 @@ func (fn *formulaFuncs) FACTDOUBLE(argsList *list.List) (result string, err erro
return
}
number, val := 0.0, 1.0
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1423,11 +1534,11 @@ func (fn *formulaFuncs) FLOOR(argsList *list.List) (result string, err error) {
return
}
var number, significance float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1461,7 +1572,7 @@ func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error
return
}
number, significance, mode := 0.0, 1.0, 1.0
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1469,7 +1580,7 @@ func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error
significance = -1
}
if argsList.Len() > 1 {
- if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(formulaArg).Value, 64); err != nil {
+ if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1479,7 +1590,7 @@ func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error
return
}
if argsList.Len() > 2 {
- if mode, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if mode, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1507,7 +1618,7 @@ func (fn *formulaFuncs) FLOORPRECISE(argsList *list.List) (result string, err er
return
}
var number, significance float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1519,7 +1630,7 @@ func (fn *formulaFuncs) FLOORPRECISE(argsList *list.List) (result string, err er
return
}
if argsList.Len() > 1 {
- if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1573,7 +1684,7 @@ 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.(formulaArg).Value
+ token := arg.Value.(formulaArg).String
if token == "" {
continue
}
@@ -1614,7 +1725,7 @@ func (fn *formulaFuncs) INT(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1642,7 +1753,7 @@ func (fn *formulaFuncs) ISOCEILING(argsList *list.List) (result string, err erro
return
}
var number, significance float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1654,7 +1765,7 @@ func (fn *formulaFuncs) ISOCEILING(argsList *list.List) (result string, err erro
return
}
if argsList.Len() > 1 {
- if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1699,7 +1810,7 @@ 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.(formulaArg).Value
+ token := arg.Value.(formulaArg).String
if token == "" {
continue
}
@@ -1740,7 +1851,7 @@ func (fn *formulaFuncs) LN(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1763,12 +1874,12 @@ func (fn *formulaFuncs) LOG(argsList *list.List) (result string, err error) {
return
}
number, base := 0.0, 10.0
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
if argsList.Len() > 1 {
- if base, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if base, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1800,7 +1911,7 @@ func (fn *formulaFuncs) LOG10(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1863,7 +1974,7 @@ func (fn *formulaFuncs) MDETERM(argsList *list.List) (result string, err error)
}
numRow := []float64{}
for _, ele := range row {
- if num, err = strconv.ParseFloat(ele, 64); err != nil {
+ if num, err = strconv.ParseFloat(ele.String, 64); err != nil {
return
}
numRow = append(numRow, num)
@@ -1885,11 +1996,11 @@ func (fn *formulaFuncs) MOD(argsList *list.List) (result string, err error) {
return
}
var number, divisor float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if divisor, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if divisor, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1916,11 +2027,11 @@ func (fn *formulaFuncs) MROUND(argsList *list.List) (result string, err error) {
return
}
var number, multiple float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if multiple, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if multiple, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1951,10 +2062,10 @@ func (fn *formulaFuncs) MULTINOMIAL(argsList *list.List) (result string, err err
val, num, denom := 0.0, 0.0, 1.0
for arg := argsList.Front(); arg != nil; arg = arg.Next() {
token := arg.Value.(formulaArg)
- if token.Value == "" {
+ if token.String == "" {
continue
}
- if val, err = strconv.ParseFloat(token.Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(token.String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -1976,7 +2087,7 @@ func (fn *formulaFuncs) MUNIT(argsList *list.List) (result string, err error) {
return
}
var dimension int
- if dimension, err = strconv.Atoi(argsList.Front().Value.(formulaArg).Value); err != nil {
+ if dimension, err = strconv.Atoi(argsList.Front().Value.(formulaArg).String); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2007,7 +2118,7 @@ func (fn *formulaFuncs) ODD(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2054,11 +2165,11 @@ func (fn *formulaFuncs) POWER(argsList *list.List) (result string, err error) {
return
}
var x, y float64
- if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2083,14 +2194,32 @@ func (fn *formulaFuncs) PRODUCT(argsList *list.List) (result string, err error)
val, product := 0.0, 1.0
for arg := argsList.Front(); arg != nil; arg = arg.Next() {
token := arg.Value.(formulaArg)
- if token.Value == "" {
+ switch token.Type {
+ case ArgUnknown:
continue
+ case ArgString:
+ if token.String == "" {
+ continue
+ }
+ if val, err = strconv.ParseFloat(token.String, 64); err != nil {
+ err = errors.New(formulaErrorVALUE)
+ return
+ }
+ product = product * val
+ case ArgMatrix:
+ for _, row := range token.Matrix {
+ for _, value := range row {
+ if value.String == "" {
+ continue
+ }
+ if val, err = strconv.ParseFloat(value.String, 64); err != nil {
+ err = errors.New(formulaErrorVALUE)
+ return
+ }
+ product = product * val
+ }
+ }
}
- if val, err = strconv.ParseFloat(token.Value, 64); err != nil {
- err = errors.New(formulaErrorVALUE)
- return
- }
- product = product * val
}
result = fmt.Sprintf("%g", product)
return
@@ -2107,11 +2236,11 @@ func (fn *formulaFuncs) QUOTIENT(argsList *list.List) (result string, err error)
return
}
var x, y float64
- if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2133,7 +2262,7 @@ func (fn *formulaFuncs) RADIANS(argsList *list.List) (result string, err error)
return
}
var angle float64
- if angle, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if angle, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2166,11 +2295,11 @@ func (fn *formulaFuncs) RANDBETWEEN(argsList *list.List) (result string, err err
return
}
var bottom, top int64
- if bottom, err = strconv.ParseInt(argsList.Front().Value.(formulaArg).Value, 10, 64); err != nil {
+ if bottom, err = strconv.ParseInt(argsList.Front().Value.(formulaArg).String, 10, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if top, err = strconv.ParseInt(argsList.Back().Value.(formulaArg).Value, 10, 64); err != nil {
+ if top, err = strconv.ParseInt(argsList.Back().Value.(formulaArg).String, 10, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2213,12 +2342,12 @@ func (fn *formulaFuncs) ROMAN(argsList *list.List) (result string, err error) {
}
var number float64
var form int
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
if argsList.Len() > 1 {
- if form, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); err != nil {
+ if form, err = strconv.Atoi(argsList.Back().Value.(formulaArg).String); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2298,11 +2427,11 @@ func (fn *formulaFuncs) ROUND(argsList *list.List) (result string, err error) {
return
}
var number, digits float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2321,11 +2450,11 @@ func (fn *formulaFuncs) ROUNDDOWN(argsList *list.List) (result string, err error
return
}
var number, digits float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2344,11 +2473,11 @@ func (fn *formulaFuncs) ROUNDUP(argsList *list.List) (result string, err error)
return
}
var number, digits float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
- if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2367,7 +2496,7 @@ func (fn *formulaFuncs) SEC(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2386,7 +2515,7 @@ func (fn *formulaFuncs) SECH(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2407,7 +2536,7 @@ func (fn *formulaFuncs) SIGN(argsList *list.List) (result string, err error) {
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2434,7 +2563,7 @@ func (fn *formulaFuncs) SIN(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2453,7 +2582,7 @@ func (fn *formulaFuncs) SINH(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2472,7 +2601,7 @@ func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) {
return
}
var res float64
- var value = argsList.Front().Value.(formulaArg).Value
+ var value = argsList.Front().Value.(formulaArg).String
if value == "" {
result = "0"
return
@@ -2500,7 +2629,7 @@ func (fn *formulaFuncs) SQRTPI(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2517,14 +2646,79 @@ 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 == "" {
+ switch token.Type {
+ case ArgUnknown:
continue
+ case ArgString:
+ if token.String == "" {
+ continue
+ }
+ if val, err = strconv.ParseFloat(token.String, 64); err != nil {
+ err = errors.New(formulaErrorVALUE)
+ return
+ }
+ sum += val
+ case ArgMatrix:
+ for _, row := range token.Matrix {
+ for _, value := range row {
+ if value.String == "" {
+ continue
+ }
+ if val, err = strconv.ParseFloat(value.String, 64); err != nil {
+ err = errors.New(formulaErrorVALUE)
+ return
+ }
+ sum += val
+ }
+ }
}
- if val, err = strconv.ParseFloat(token.Value, 64); err != nil {
- err = errors.New(formulaErrorVALUE)
- return
+ }
+ result = fmt.Sprintf("%g", sum)
+ return
+}
+
+// SUMIF function finds the values in a supplied array, that satisfy a given
+// criteria, and returns the sum of the corresponding values in a second
+// supplied array. The syntax of the function is:
+//
+// SUMIF(range,criteria,[sum_range])
+//
+func (fn *formulaFuncs) SUMIF(argsList *list.List) (result string, err error) {
+ if argsList.Len() < 2 {
+ err = errors.New("SUMIF requires at least 2 argument")
+ return
+ }
+ var criteria = formulaCriteriaParser(argsList.Front().Next().Value.(formulaArg).String)
+ var rangeMtx = argsList.Front().Value.(formulaArg).Matrix
+ var sumRange [][]formulaArg
+ if argsList.Len() == 3 {
+ sumRange = argsList.Back().Value.(formulaArg).Matrix
+ }
+ var sum, val float64
+ for rowIdx, row := range rangeMtx {
+ for colIdx, col := range row {
+ var ok bool
+ fromVal := col.String
+ if col.String == "" {
+ continue
+ }
+ if ok, err = formulaCriteriaEval(fromVal, criteria); err != nil {
+ return
+ }
+ if ok {
+ if argsList.Len() == 3 {
+ if len(sumRange) <= rowIdx || len(sumRange[rowIdx]) <= colIdx {
+ continue
+ }
+ fromVal = sumRange[rowIdx][colIdx].String
+ }
+ if val, err = strconv.ParseFloat(fromVal, 64); err != nil {
+ err = errors.New(formulaErrorVALUE)
+ return
+ }
+ sum += val
+ }
}
- sum += val
}
result = fmt.Sprintf("%g", sum)
return
@@ -2539,14 +2733,30 @@ func (fn *formulaFuncs) SUMSQ(argsList *list.List) (result string, err error) {
var val, sq float64
for arg := argsList.Front(); arg != nil; arg = arg.Next() {
token := arg.Value.(formulaArg)
- if token.Value == "" {
- continue
- }
- if val, err = strconv.ParseFloat(token.Value, 64); err != nil {
- err = errors.New(formulaErrorVALUE)
- return
+ switch token.Type {
+ case ArgString:
+ if token.String == "" {
+ continue
+ }
+ if val, err = strconv.ParseFloat(token.String, 64); err != nil {
+ err = errors.New(formulaErrorVALUE)
+ return
+ }
+ sq += val * val
+ case ArgMatrix:
+ for _, row := range token.Matrix {
+ for _, value := range row {
+ if value.String == "" {
+ continue
+ }
+ if val, err = strconv.ParseFloat(value.String, 64); err != nil {
+ err = errors.New(formulaErrorVALUE)
+ return
+ }
+ sq += val * val
+ }
+ }
}
- sq += val * val
}
result = fmt.Sprintf("%g", sq)
return
@@ -2563,7 +2773,7 @@ func (fn *formulaFuncs) TAN(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2582,7 +2792,7 @@ func (fn *formulaFuncs) TANH(argsList *list.List) (result string, err error) {
return
}
var number float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2601,12 +2811,12 @@ func (fn *formulaFuncs) TRUNC(argsList *list.List) (result string, err error) {
return
}
var number, digits, adjust, rtrim float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
if argsList.Len() > 1 {
- if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
err = errors.New(formulaErrorVALUE)
return
}
@@ -2628,3 +2838,5 @@ func (fn *formulaFuncs) TRUNC(argsList *list.List) (result string, err error) {
}
// Statistical functions
+
+// Information functions
diff --git a/calc_test.go b/calc_test.go
index 7592078..3639af7 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -1,20 +1,32 @@
package excelize
import (
+ "path/filepath"
"testing"
"github.com/stretchr/testify/assert"
)
func TestCalcCellValue(t *testing.T) {
+ cellData := [][]interface{}{
+ {1, 4, nil, "Month", "Team", "Sales"},
+ {2, 5, nil, "Jan", "North 1", 36693},
+ {3, nil, nil, "Jan", "North 2", 22100},
+ {0, nil, nil, "Jan", "South 1", 53321},
+ {nil, nil, nil, "Jan", "South 2", 34440},
+ {nil, nil, nil, "Feb", "North 1", 29889},
+ {nil, nil, nil, "Feb", "North 2", 50090},
+ {nil, nil, nil, "Feb", "South 1", 32080},
+ {nil, nil, nil, "Feb", "South 2", 45500},
+ }
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)
+ for r, row := range cellData {
+ for c, value := range row {
+ cell, _ := CoordinatesToCellName(c+1, r+1)
+ assert.NoError(t, f.SetCellValue("Sheet1", cell, value))
+ }
+ }
return f
}
@@ -348,6 +360,12 @@ 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",
+ // SUMIF
+ `=SUMIF(F1:F5, ">100")`: "146554",
+ `=SUMIF(D3:D7,"Jan",F2:F5)`: "112114",
+ `=SUMIF(D2:D9,"Feb",F2:F9)`: "157559",
+ `=SUMIF(E2:E9,"North 1",F2:F9)`: "66582",
+ `=SUMIF(E2:E9,"North*",F2:F9)`: "138772",
// SUMSQ
"=SUMSQ(A1:A4)": "14",
"=SUMSQ(A1,B1,A2,B2,6)": "82",
@@ -627,6 +645,8 @@ func TestCalcCellValue(t *testing.T) {
"=SUM(1*)": "formula not valid",
"=SUM(1/)": "formula not valid",
`=SUM("X")`: "#VALUE!",
+ // SUMIF
+ "=SUMIF()": "SUMIF requires at least 2 argument",
// SUMSQ
`=SUMSQ("X")`: "#VALUE!",
// TAN
@@ -711,4 +731,5 @@ func TestCalcCellValue(t *testing.T) {
assert.NoError(t, f.SetCellFormula("Sheet1", "A1", "=UNSUPPORT(A1)"))
_, err = f.CalcCellValue("Sheet1", "A1")
assert.EqualError(t, err, "not support UNSUPPORT function")
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestCalcCellValue.xlsx")))
}
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-19 18:15:24 +0000