Reduce cyclomatic complexities for the formula calculate function and update documentation for the API: `MergeCell` and `GetCellValue`

5 files changed, 178 insertions, 121 deletions

diff --git a/calc.go b/calc.go
index 3bb81b8..18605db 100644
--- a/calc.go
+++ b/calc.go
@@ -4240,17 +4240,42 @@ func (fn *formulaFuncs) STDEVA(argsList *list.List) formulaArg {
 	return fn.stdev(true, argsList)
 }
 
-// stdev is an implementation of the formula function STDEV and STDEVA.
-func (fn *formulaFuncs) stdev(stdeva bool, argsList *list.List) formulaArg {
-	pow := func(result, count float64, n, m formulaArg) (float64, float64) {
-		if result == -1 {
-			result = math.Pow((n.Number - m.Number), 2)
-		} else {
-			result += math.Pow((n.Number - m.Number), 2)
+// calcStdevPow is part of the implementation stdev.
+func calcStdevPow(result, count float64, n, m formulaArg) (float64, float64) {
+	if result == -1 {
+		result = math.Pow((n.Number - m.Number), 2)
+	} else {
+		result += math.Pow((n.Number - m.Number), 2)
+	}
+	count++
+	return result, count
+}
+
+// calcStdev is part of the implementation stdev.
+func calcStdev(stdeva bool, result, count float64, mean, token formulaArg) (float64, float64) {
+	for _, row := range token.ToList() {
+		if row.Type == ArgNumber || row.Type == ArgString {
+			if !stdeva && (row.Value() == "TRUE" || row.Value() == "FALSE") {
+				continue
+			} else if stdeva && (row.Value() == "TRUE" || row.Value() == "FALSE") {
+				num := row.ToBool()
+				if num.Type == ArgNumber {
+					result, count = calcStdevPow(result, count, num, mean)
+					continue
+				}
+			} else {
+				num := row.ToNumber()
+				if num.Type == ArgNumber {
+					result, count = calcStdevPow(result, count, num, mean)
+				}
+			}
 		}
-		count++
-		return result, count
 	}
+	return result, count
+}
+
+// stdev is an implementation of the formula function STDEV and STDEVA.
+func (fn *formulaFuncs) stdev(stdeva bool, argsList *list.List) formulaArg {
 	count, result := -1.0, -1.0
 	var mean formulaArg
 	if stdeva {
@@ -4267,34 +4292,17 @@ func (fn *formulaFuncs) stdev(stdeva bool, argsList *list.List) formulaArg {
 			} else if stdeva && (token.Value() == "TRUE" || token.Value() == "FALSE") {
 				num := token.ToBool()
 				if num.Type == ArgNumber {
-					result, count = pow(result, count, num, mean)
+					result, count = calcStdevPow(result, count, num, mean)
 					continue
 				}
 			} else {
 				num := token.ToNumber()
 				if num.Type == ArgNumber {
-					result, count = pow(result, count, num, mean)
+					result, count = calcStdevPow(result, count, num, mean)
 				}
 			}
 		case ArgList, ArgMatrix:
-			for _, row := range token.ToList() {
-				if row.Type == ArgNumber || row.Type == ArgString {
-					if !stdeva && (row.Value() == "TRUE" || row.Value() == "FALSE") {
-						continue
-					} else if stdeva && (row.Value() == "TRUE" || row.Value() == "FALSE") {
-						num := row.ToBool()
-						if num.Type == ArgNumber {
-							result, count = pow(result, count, num, mean)
-							continue
-						}
-					} else {
-						num := row.ToNumber()
-						if num.Type == ArgNumber {
-							result, count = pow(result, count, num, mean)
-						}
-					}
-				}
-			}
+			result, count = calcStdev(stdeva, result, count, mean, token)
 		}
 	}
 	if count > 0 && result >= 0 {
@@ -4568,6 +4576,18 @@ func (fn *formulaFuncs) AVERAGEA(argsList *list.List) formulaArg {
 	return newNumberFormulaArg(sum / count)
 }
 
+// calcStringCountSum is part of the implementation countSum.
+func calcStringCountSum(countText bool, count, sum float64, num, arg formulaArg) (float64, float64) {
+	if countText && num.Type == ArgError && arg.String != "" {
+		count++
+	}
+	if num.Type == ArgNumber {
+		sum += num.Number
+		count++
+	}
+	return count, sum
+}
+
 // countSum get count and sum for a formula arguments array.
 func (fn *formulaFuncs) countSum(countText bool, args []formulaArg) (count, sum float64) {
 	for _, arg := range args {
@@ -4589,13 +4609,7 @@ func (fn *formulaFuncs) countSum(countText bool, args []formulaArg) (count, sum
 				}
 			}
 			num := arg.ToNumber()
-			if countText && num.Type == ArgError && arg.String != "" {
-				count++
-			}
-			if num.Type == ArgNumber {
-				sum += num.Number
-				count++
-			}
+			count, sum = calcStringCountSum(countText, count, sum, num, arg)
 		case ArgList, ArgMatrix:
 			cnt, summary := fn.countSum(countText, arg.ToList())
 			sum += summary
@@ -5148,6 +5162,33 @@ func (fn *formulaFuncs) MAXA(argsList *list.List) formulaArg {
 	return fn.max(true, argsList)
 }
 
+// calcListMatrixMax is part of the implementation max.
+func calcListMatrixMax(maxa bool, max float64, arg formulaArg) float64 {
+	for _, row := range arg.ToList() {
+		switch row.Type {
+		case ArgString:
+			if !maxa && (row.Value() == "TRUE" || row.Value() == "FALSE") {
+				continue
+			} else {
+				num := row.ToBool()
+				if num.Type == ArgNumber && num.Number > max {
+					max = num.Number
+					continue
+				}
+			}
+			num := row.ToNumber()
+			if num.Type != ArgError && num.Number > max {
+				max = num.Number
+			}
+		case ArgNumber:
+			if row.Number > max {
+				max = row.Number
+			}
+		}
+	}
+	return max
+}
+
 // max is an implementation of the formula function MAX and MAXA.
 func (fn *formulaFuncs) max(maxa bool, argsList *list.List) formulaArg {
 	max := -math.MaxFloat64
@@ -5173,28 +5214,7 @@ func (fn *formulaFuncs) max(maxa bool, argsList *list.List) formulaArg {
 				max = arg.Number
 			}
 		case ArgList, ArgMatrix:
-			for _, row := range arg.ToList() {
-				switch row.Type {
-				case ArgString:
-					if !maxa && (row.Value() == "TRUE" || row.Value() == "FALSE") {
-						continue
-					} else {
-						num := row.ToBool()
-						if num.Type == ArgNumber && num.Number > max {
-							max = num.Number
-							continue
-						}
-					}
-					num := row.ToNumber()
-					if num.Type != ArgError && num.Number > max {
-						max = num.Number
-					}
-				case ArgNumber:
-					if row.Number > max {
-						max = row.Number
-					}
-				}
-			}
+			max = calcListMatrixMax(maxa, max, arg)
 		case ArgError:
 			return arg
 		}
@@ -5277,6 +5297,33 @@ func (fn *formulaFuncs) MINA(argsList *list.List) formulaArg {
 	return fn.min(true, argsList)
 }
 
+// calcListMatrixMin is part of the implementation min.
+func calcListMatrixMin(mina bool, min float64, arg formulaArg) float64 {
+	for _, row := range arg.ToList() {
+		switch row.Type {
+		case ArgString:
+			if !mina && (row.Value() == "TRUE" || row.Value() == "FALSE") {
+				continue
+			} else {
+				num := row.ToBool()
+				if num.Type == ArgNumber && num.Number < min {
+					min = num.Number
+					continue
+				}
+			}
+			num := row.ToNumber()
+			if num.Type != ArgError && num.Number < min {
+				min = num.Number
+			}
+		case ArgNumber:
+			if row.Number < min {
+				min = row.Number
+			}
+		}
+	}
+	return min
+}
+
 // min is an implementation of the formula function MIN and MINA.
 func (fn *formulaFuncs) min(mina bool, argsList *list.List) formulaArg {
 	min := math.MaxFloat64
@@ -5302,28 +5349,7 @@ func (fn *formulaFuncs) min(mina bool, argsList *list.List) formulaArg {
 				min = arg.Number
 			}
 		case ArgList, ArgMatrix:
-			for _, row := range arg.ToList() {
-				switch row.Type {
-				case ArgString:
-					if !mina && (row.Value() == "TRUE" || row.Value() == "FALSE") {
-						continue
-					} else {
-						num := row.ToBool()
-						if num.Type == ArgNumber && num.Number < min {
-							min = num.Number
-							continue
-						}
-					}
-					num := row.ToNumber()
-					if num.Type != ArgError && num.Number < min {
-						min = num.Number
-					}
-				case ArgNumber:
-					if row.Number < min {
-						min = row.Number
-					}
-				}
-			}
+			min = calcListMatrixMin(mina, min, arg)
 		case ArgError:
 			return arg
 		}
@@ -6930,16 +6956,9 @@ func (fn *formulaFuncs) COLUMN(argsList *list.List) formulaArg {
 	return newNumberFormulaArg(float64(col))
 }
 
-// COLUMNS function receives an Excel range and returns the number of columns
-// that are contained within the range. The syntax of the function is:
-//
-//    COLUMNS(array)
-//
-func (fn *formulaFuncs) COLUMNS(argsList *list.List) formulaArg {
-	if argsList.Len() != 1 {
-		return newErrorFormulaArg(formulaErrorVALUE, "COLUMNS requires 1 argument")
-	}
-	var min, max int
+// calcColumnsMinMax calculation min and max value for given formula arguments
+// sequence of the formula function COLUMNS.
+func calcColumnsMinMax(argsList *list.List) (min, max int) {
 	if argsList.Front().Value.(formulaArg).cellRanges != nil && argsList.Front().Value.(formulaArg).cellRanges.Len() > 0 {
 		crs := argsList.Front().Value.(formulaArg).cellRanges
 		for cr := crs.Front(); cr != nil; cr = cr.Next() {
@@ -6974,6 +6993,19 @@ func (fn *formulaFuncs) COLUMNS(argsList *list.List) formulaArg {
 			}
 		}
 	}
+	return
+}
+
+// COLUMNS function receives an Excel range and returns the number of columns
+// that are contained within the range. The syntax of the function is:
+//
+//    COLUMNS(array)
+//
+func (fn *formulaFuncs) COLUMNS(argsList *list.List) formulaArg {
+	if argsList.Len() != 1 {
+		return newErrorFormulaArg(formulaErrorVALUE, "COLUMNS requires 1 argument")
+	}
+	min, max := calcColumnsMinMax(argsList)
 	if max == TotalColumns {
 		return newNumberFormulaArg(float64(TotalColumns))
 	}
@@ -7272,16 +7304,9 @@ func (fn *formulaFuncs) ROW(argsList *list.List) formulaArg {
 	return newNumberFormulaArg(float64(row))
 }
 
-// ROWS function takes an Excel range and returns the number of rows that are
-// contained within the range. The syntax of the function is:
-//
-//    ROWS(array)
-//
-func (fn *formulaFuncs) ROWS(argsList *list.List) formulaArg {
-	if argsList.Len() != 1 {
-		return newErrorFormulaArg(formulaErrorVALUE, "ROWS requires 1 argument")
-	}
-	var min, max int
+// calcRowsMinMax calculation min and max value for given formula arguments
+// sequence of the formula function ROWS.
+func calcRowsMinMax(argsList *list.List) (min, max int) {
 	if argsList.Front().Value.(formulaArg).cellRanges != nil && argsList.Front().Value.(formulaArg).cellRanges.Len() > 0 {
 		crs := argsList.Front().Value.(formulaArg).cellRanges
 		for cr := crs.Front(); cr != nil; cr = cr.Next() {
@@ -7316,6 +7341,19 @@ func (fn *formulaFuncs) ROWS(argsList *list.List) formulaArg {
 			}
 		}
 	}
+	return
+}
+
+// ROWS function takes an Excel range and returns the number of rows that are
+// contained within the range. The syntax of the function is:
+//
+//    ROWS(array)
+//
+func (fn *formulaFuncs) ROWS(argsList *list.List) formulaArg {
+	if argsList.Len() != 1 {
+		return newErrorFormulaArg(formulaErrorVALUE, "ROWS requires 1 argument")
+	}
+	min, max := calcRowsMinMax(argsList)
 	if max == TotalRows {
 		return newStringFormulaArg(strconv.Itoa(TotalRows))
 	}
@@ -7419,6 +7457,11 @@ func (fn *formulaFuncs) cumip(name string, argsList *list.List) formulaArg {
 	return newNumberFormulaArg(num)
 }
 
+// calcDbArgsCompare implements common arguments comparison for DB and DDB.
+func calcDbArgsCompare(cost, salvage, life, period formulaArg) bool {
+	return (cost.Number <= 0) || ((salvage.Number / cost.Number) < 0) || (life.Number <= 0) || (period.Number < 1)
+}
+
 // DB function calculates the depreciation of an asset, using the Fixed
 // Declining Balance Method, for each period of the asset's lifetime. The
 // syntax of the function is:
@@ -7457,7 +7500,7 @@ func (fn *formulaFuncs) DB(argsList *list.List) formulaArg {
 	if cost.Number == 0 {
 		return newNumberFormulaArg(0)
 	}
-	if (cost.Number <= 0) || ((salvage.Number / cost.Number) < 0) || (life.Number <= 0) || (period.Number < 1) || (month.Number < 1) {
+	if calcDbArgsCompare(cost, salvage, life, period) || (month.Number < 1) {
 		return newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
 	}
 	dr := 1 - math.Pow(salvage.Number/cost.Number, 1/life.Number)
@@ -7514,7 +7557,7 @@ func (fn *formulaFuncs) DDB(argsList *list.List) formulaArg {
 	if cost.Number == 0 {
 		return newNumberFormulaArg(0)
 	}
-	if (cost.Number <= 0) || ((salvage.Number / cost.Number) < 0) || (life.Number <= 0) || (period.Number < 1) || (factor.Number <= 0.0) || (period.Number > life.Number) {
+	if calcDbArgsCompare(cost, salvage, life, period) || (factor.Number <= 0.0) || (period.Number > life.Number) {
 		return newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
 	}
 	pd, depreciation := 0.0, 0.0
@@ -7680,6 +7723,24 @@ func (fn *formulaFuncs) IPMT(argsList *list.List) formulaArg {
 	return fn.ipmt("IPMT", argsList)
 }
 
+// calcIpmt is part of the implementation ipmt.
+func calcIpmt(name string, typ, per, pmt, pv, rate formulaArg) formulaArg {
+	capital, interest, principal := pv.Number, 0.0, 0.0
+	for i := 1; i <= int(per.Number); i++ {
+		if typ.Number != 0 && i == 1 {
+			interest = 0
+		} else {
+			interest = -capital * rate.Number
+		}
+		principal = pmt.Number - interest
+		capital += principal
+	}
+	if name == "IPMT" {
+		return newNumberFormulaArg(interest)
+	}
+	return newNumberFormulaArg(principal)
+}
+
 // ipmt is an implementation of the formula function IPMT and PPMT.
 func (fn *formulaFuncs) ipmt(name string, argsList *list.List) formulaArg {
 	if argsList.Len() < 4 {
@@ -7727,20 +7788,8 @@ func (fn *formulaFuncs) ipmt(name string, argsList *list.List) formulaArg {
 	args.PushBack(pv)
 	args.PushBack(fv)
 	args.PushBack(typ)
-	pmt, capital, interest, principal := fn.PMT(args), pv.Number, 0.0, 0.0
-	for i := 1; i <= int(per.Number); i++ {
-		if typ.Number != 0 && i == 1 {
-			interest = 0
-		} else {
-			interest = -capital * rate.Number
-		}
-		principal = pmt.Number - interest
-		capital += principal
-	}
-	if name == "IPMT" {
-		return newNumberFormulaArg(interest)
-	}
-	return newNumberFormulaArg(principal)
+	pmt := fn.PMT(args)
+	return calcIpmt(name, typ, per, pmt, pv, rate)
 }
 
 // IRR function returns the Internal Rate of Return for a supplied series of
diff --git a/cell.go b/cell.go
index ad94038..f44e877 100644
--- a/cell.go
+++ b/cell.go
@@ -34,7 +34,8 @@ const (
 // GetCellValue provides a function to get formatted value from cell by given
 // worksheet name and axis in spreadsheet file. If it is possible to apply a
 // format to the cell value, it will do so, if not then an error will be
-// returned, along with the raw value of the cell.
+// returned, along with the raw value of the cell. All cells value will be
+// same in a merged range.
 func (f *File) GetCellValue(sheet, axis string) (string, error) {
 	return f.getCellStringFunc(sheet, axis, func(x *xlsxWorksheet, c *xlsxC) (string, bool, error) {
 		val, err := c.getValueFrom(f, f.sharedStringsReader())
diff --git a/lib_test.go b/lib_test.go
index 2e0e506..025bc85 100644
--- a/lib_test.go
+++ b/lib_test.go
@@ -237,6 +237,10 @@ func TestBytesReplace(t *testing.T) {
 	assert.EqualValues(t, s, bytesReplace(s, []byte{}, []byte{}, 0))
 }
 
+func TestGetRootElement(t *testing.T) {
+	assert.Equal(t, 0, len(getRootElement(xml.NewDecoder(strings.NewReader("")))))
+}
+
 func TestSetIgnorableNameSpace(t *testing.T) {
 	f := NewFile()
 	f.xmlAttr["xml_path"] = []xml.Attr{{}}
diff --git a/merge.go b/merge.go
index 1f8974e..7769b89 100644
--- a/merge.go
+++ b/merge.go
@@ -17,7 +17,9 @@ import (
 )
 
 // MergeCell provides a function to merge cells by given coordinate area and
-// sheet name. For example create a merged cell of D3:E9 on Sheet1:
+// sheet name. Merging cells only keeps the upper-left cell value, and
+// discards the other values. For example create a merged cell of D3:E9 on
+// Sheet1:
 //
 //    err := f.MergeCell("Sheet1", "D3", "E9")
 //
diff --git a/sparkline.go b/sparkline.go
index 5326c60..917383d 100644
--- a/sparkline.go
+++ b/sparkline.go
@@ -524,10 +524,11 @@ func (f *File) appendSparkline(ws *xlsxWorksheet, group *xlsxX14SparklineGroup,
 			if sparklineGroupBytes, err = xml.Marshal(group); err != nil {
 				return
 			}
-			groups = &xlsxX14SparklineGroups{
-				XMLNSXM: NameSpaceSpreadSheetExcel2006Main.Value,
-				Content: decodeSparklineGroups.Content + string(sparklineGroupBytes),
+			if groups == nil {
+				groups = &xlsxX14SparklineGroups{}
 			}
+			groups.XMLNSXM = NameSpaceSpreadSheetExcel2006Main.Value
+			groups.Content = decodeSparklineGroups.Content + string(sparklineGroupBytes)
 			if sparklineGroupsBytes, err = xml.Marshal(groups); err != nil {
 				return
 			}