From 882abb80988b7c50286dd2e6c6589fab10662db6 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 10 May 2020 16:56:08 +0800 Subject: - formula engine: reduce cyclomatic complexity - styles: allow empty and default cell formats, #628 --- calc.go | 303 +++++++++++++++++++++++++++++++++++++--------------------------- 1 file changed, 176 insertions(+), 127 deletions(-) (limited to 'calc.go') diff --git a/calc.go b/calc.go index f85d16a..3d4a8be 100644 --- a/calc.go +++ b/calc.go @@ -63,8 +63,8 @@ type formulaArg struct { type formulaFuncs struct{} // CalcCellValue provides a function to get calculated cell value. This -// feature is currently in beta. Array formula, table formula and some other -// formulas are not supported currently. +// feature is currently in working processing. Array formula, table formula +// and some other formulas are not supported currently. func (f *File) CalcCellValue(sheet, cell string) (result string, err error) { var ( formula string @@ -265,6 +265,89 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) return opdStack.Peek().(efp.Token), err } +// calcAdd evaluate addition arithmetic operations. +func calcAdd(opdStack *Stack) error { + if opdStack.Len() < 2 { + return errors.New("formula not valid") + } + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal + rOpdVal + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcAdd evaluate subtraction arithmetic operations. +func calcSubtract(opdStack *Stack) error { + if opdStack.Len() < 2 { + return errors.New("formula not valid") + } + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal - rOpdVal + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcAdd evaluate multiplication arithmetic operations. +func calcMultiply(opdStack *Stack) error { + if opdStack.Len() < 2 { + return errors.New("formula not valid") + } + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal * rOpdVal + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + +// calcAdd evaluate division arithmetic operations. +func calcDivide(opdStack *Stack) error { + if opdStack.Len() < 2 { + return errors.New("formula not valid") + } + rOpd := opdStack.Pop().(efp.Token) + lOpd := opdStack.Pop().(efp.Token) + lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) + if err != nil { + return err + } + rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) + if err != nil { + return err + } + result := lOpdVal / rOpdVal + if rOpdVal == 0 { + return errors.New(formulaErrorDIV) + } + opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + return nil +} + // calculate evaluate basic arithmetic operations. func calculate(opdStack *Stack, opt efp.Token) error { if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorPrefix { @@ -279,80 +362,69 @@ func calculate(opdStack *Stack, opt efp.Token) error { result := 0 - opdVal opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } + if opt.TValue == "+" { - if opdStack.Len() < 2 { - return errors.New("formula not valid") - } - rOpd := opdStack.Pop().(efp.Token) - lOpd := opdStack.Pop().(efp.Token) - lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) - if err != nil { + if err := calcAdd(opdStack); err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) - if err != nil { - return err - } - result := lOpdVal + rOpdVal - opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorInfix { - if opdStack.Len() < 2 { - return errors.New("formula not valid") - } - rOpd := opdStack.Pop().(efp.Token) - lOpd := opdStack.Pop().(efp.Token) - lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) - if err != nil { + if err := calcSubtract(opdStack); err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) - if err != nil { - return err - } - result := lOpdVal - rOpdVal - opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } if opt.TValue == "*" { - if opdStack.Len() < 2 { - return errors.New("formula not valid") - } - rOpd := opdStack.Pop().(efp.Token) - lOpd := opdStack.Pop().(efp.Token) - lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) - if err != nil { + if err := calcMultiply(opdStack); err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) - if err != nil { - return err - } - result := lOpdVal * rOpdVal - opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } if opt.TValue == "/" { - if opdStack.Len() < 2 { - return errors.New("formula not valid") - } - rOpd := opdStack.Pop().(efp.Token) - lOpd := opdStack.Pop().(efp.Token) - lOpdVal, err := strconv.ParseFloat(lOpd.TValue, 64) - if err != nil { + if err := calcDivide(opdStack); err != nil { return err } - rOpdVal, err := strconv.ParseFloat(rOpd.TValue, 64) - if err != nil { - return err - } - result := lOpdVal / rOpdVal - if rOpdVal == 0 { - return errors.New(formulaErrorDIV) - } - opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) } return nil } +// parseOperatorPrefixToken parse operator prefix token. +func (f *File) parseOperatorPrefixToken(optStack, opdStack *Stack, token efp.Token) (err error) { + if optStack.Len() == 0 { + optStack.Push(token) + } else { + tokenPriority := getPriority(token) + topOpt := optStack.Peek().(efp.Token) + topOptPriority := getPriority(topOpt) + if tokenPriority > topOptPriority { + optStack.Push(token) + } else { + for tokenPriority <= topOptPriority { + optStack.Pop() + if err = calculate(opdStack, topOpt); err != nil { + return + } + if optStack.Len() > 0 { + topOpt = optStack.Peek().(efp.Token) + topOptPriority = getPriority(topOpt) + continue + } + break + } + optStack.Push(token) + } + } + return +} + +// isOperatorPrefixToken determine if the token is parse operator prefix +// token. +func isOperatorPrefixToken(token efp.Token) bool { + if (token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix) || + token.TValue == "+" || token.TValue == "-" || token.TValue == "*" || token.TValue == "/" { + return true + } + return false +} + // parseToken parse basic arithmetic operator priority and evaluate based on // operators and operands. func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Stack) error { @@ -369,30 +441,9 @@ func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Sta token.TType = efp.TokenTypeOperand token.TSubType = efp.TokenSubTypeNumber } - if (token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix) || token.TValue == "+" || token.TValue == "-" || token.TValue == "*" || token.TValue == "/" { - if optStack.Len() == 0 { - optStack.Push(token) - } else { - tokenPriority := getPriority(token) - topOpt := optStack.Peek().(efp.Token) - topOptPriority := getPriority(topOpt) - if tokenPriority > topOptPriority { - optStack.Push(token) - } else { - for tokenPriority <= topOptPriority { - optStack.Pop() - if err := calculate(opdStack, topOpt); err != nil { - return err - } - if optStack.Len() > 0 { - topOpt = optStack.Peek().(efp.Token) - topOptPriority = getPriority(topOpt) - continue - } - break - } - optStack.Push(token) - } + if isOperatorPrefixToken(token) { + if err := f.parseOperatorPrefixToken(optStack, opdStack, token); err != nil { + return err } } if token.TType == efp.TokenTypeSubexpression && token.TSubType == efp.TokenSubTypeStart { // ( @@ -461,11 +512,44 @@ func (f *File) parseReference(sheet, reference string) (result []string, matrix return } +// prepareValueRange prepare value range. +func prepareValueRange(cr cellRange, valueRange []int) { + if cr.From.Row < valueRange[0] { + valueRange[0] = cr.From.Row + } + if cr.From.Col < valueRange[2] { + valueRange[2] = cr.From.Col + } + if cr.To.Row > valueRange[0] { + valueRange[1] = cr.To.Row + } + if cr.To.Col > valueRange[3] { + valueRange[3] = cr.To.Col + } +} + +// prepareValueRef prepare value reference. +func prepareValueRef(cr cellRef, valueRange []int) { + if cr.Row < valueRange[0] { + valueRange[0] = cr.Row + } + if cr.Col < valueRange[2] { + valueRange[2] = cr.Col + } + if cr.Row > valueRange[0] { + valueRange[1] = cr.Row + } + if cr.Col > valueRange[3] { + valueRange[3] = cr.Col + } +} + // rangeResolver extract value as string from given reference and range list. -// This function will not ignore the empty cell. For example, -// A1:A2:A2:B3 will be reference A1:B3. +// This function will not ignore the empty cell. For example, A1:A2:A2:B3 will +// be reference A1:B3. func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, matrix [][]string, err error) { - var fromRow, toRow, fromCol, toCol int = 1, 1, 1, 1 + // value range order: from row, to row, from column, to column + valueRange := []int{1, 1, 1, 1} var sheet string filter := map[string]string{} // prepare value range @@ -476,18 +560,7 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, } rng := []int{cr.From.Col, cr.From.Row, cr.To.Col, cr.To.Row} sortCoordinates(rng) - if cr.From.Row < fromRow { - fromRow = cr.From.Row - } - if cr.From.Col < fromCol { - fromCol = cr.From.Col - } - if cr.To.Row > fromRow { - toRow = cr.To.Row - } - if cr.To.Col > toCol { - toCol = cr.To.Col - } + prepareValueRange(cr, valueRange) if cr.From.Sheet != "" { sheet = cr.From.Sheet } @@ -497,24 +570,13 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, if cr.Sheet != "" { sheet = cr.Sheet } - if cr.Row < fromRow { - fromRow = cr.Row - } - if cr.Col < fromCol { - fromCol = cr.Col - } - if cr.Row > fromRow { - toRow = cr.Row - } - if cr.Col > toCol { - toCol = cr.Col - } + prepareValueRef(cr, valueRange) } // extract value from ranges if cellRanges.Len() > 0 { - for row := fromRow; row <= toRow; row++ { + for row := valueRange[0]; row <= valueRange[1]; row++ { var matrixRow = []string{} - for col := fromCol; col <= toCol; col++ { + for col := valueRange[2]; col <= valueRange[3]; col++ { var cell, value string if cell, err = CoordinatesToCellName(col, row); err != nil { return @@ -672,28 +734,15 @@ func (fn *formulaFuncs) ARABIC(argsList *list.List) (result string, err error) { err = errors.New("ARABIC requires 1 numeric argument") return } + charMap := map[rune]float64{'I': 1, 'V': 5, 'X': 10, 'L': 50, 'C': 100, 'D': 500, 'M': 1000} val, last, prefix := 0.0, 0.0, 1.0 for _, char := range argsList.Front().Value.(formulaArg).Value { digit := 0.0 - switch char { - case '-': + if char == '-' { prefix = -1 continue - case 'I': - digit = 1 - case 'V': - digit = 5 - case 'X': - digit = 10 - case 'L': - digit = 50 - case 'C': - digit = 100 - case 'D': - digit = 500 - case 'M': - digit = 1000 } + digit, _ = charMap[char] val += digit switch { case last == digit && (last == 5 || last == 50 || last == 500): @@ -850,7 +899,7 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) { return } if radix < 2 || radix > 36 { - err = errors.New("radix must be an integer ≥ 2 and ≤ 36") + err = errors.New("radix must be an integer >= 2 and <= 36") return } if argsList.Len() > 2 { -- cgit v1.2.1