summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go356
-rw-r--r--calc_test.go117
-rw-r--r--excelize_test.go11
3 files changed, 360 insertions, 124 deletions
diff --git a/calc.go b/calc.go
index 0ce5aec..682435a 100644
--- a/calc.go
+++ b/calc.go
@@ -57,6 +57,17 @@ const (
criteriaG
criteriaErr
criteriaRegexp
+
+ matchModeExact = 0
+ matchModeMinGreater = 1
+ matchModeMaxLess = -1
+ matchModeWildcard = 2
+
+ searchModeLinear = 1
+ searchModeReverseLinear = -1
+ searchModeAscBinary = 2
+ searchModeDescBinary = -2
+
maxFinancialIterations = 128
financialPercision = 1.0e-08
// Date and time format regular expressions
@@ -860,7 +871,7 @@ func (f *File) evalInfixExpFunc(sheet, cell string, token, nextToken efp.Token,
opftStack.Pop() // remove current function separator
opfStack.Pop()
if opfStack.Len() > 0 { // still in function stack
- if nextToken.TType == efp.TokenTypeOperatorInfix || opftStack.Len() > 1 {
+ if nextToken.TType == efp.TokenTypeOperatorInfix || (opftStack.Len() > 1 && opfdStack.Len() > 0) {
// mathematics calculate in formula function
opfdStack.Push(efp.Token{TValue: arg.Value(), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
} else {
@@ -5227,7 +5238,7 @@ func (fn *formulaFuncs) COUNTIF(argsList *list.List) formulaArg {
return newNumberFormulaArg(count)
}
-// formulaIfsMatch function returns cells reference array which match criterias.
+// formulaIfsMatch function returns cells reference array which match criteria.
func formulaIfsMatch(args []formulaArg) (cellRefs []cellRef) {
for i := 0; i < len(args)-1; i += 2 {
match := []cellRef{}
@@ -9013,7 +9024,7 @@ func matchPattern(pattern, name string) (matched bool) {
// compareFormulaArg compares the left-hand sides and the right-hand sides
// formula arguments by given conditions such as case sensitive, if exact
// match, and make compare result as formula criteria condition type.
-func compareFormulaArg(lhs, rhs formulaArg, caseSensitive, exactMatch bool) byte {
+func compareFormulaArg(lhs, rhs, matchMode formulaArg, caseSensitive bool) byte {
if lhs.Type != rhs.Type {
return criteriaErr
}
@@ -9031,35 +9042,26 @@ func compareFormulaArg(lhs, rhs formulaArg, caseSensitive, exactMatch bool) byte
if !caseSensitive {
ls, rs = strings.ToLower(ls), strings.ToLower(rs)
}
- if exactMatch {
- match := matchPattern(rs, ls)
- if match {
+ if matchMode.Number == matchModeWildcard {
+ if matchPattern(rs, ls) {
return criteriaEq
}
- return criteriaG
- }
- switch strings.Compare(ls, rs) {
- case 1:
- return criteriaG
- case -1:
- return criteriaL
- case 0:
- return criteriaEq
+
}
- return criteriaErr
+ return map[int]byte{1: criteriaG, -1: criteriaL, 0: criteriaEq}[strings.Compare(ls, rs)]
case ArgEmpty:
return criteriaEq
case ArgList:
- return compareFormulaArgList(lhs, rhs, caseSensitive, exactMatch)
+ return compareFormulaArgList(lhs, rhs, matchMode, caseSensitive)
case ArgMatrix:
- return compareFormulaArgMatrix(lhs, rhs, caseSensitive, exactMatch)
+ return compareFormulaArgMatrix(lhs, rhs, matchMode, caseSensitive)
}
return criteriaErr
}
// compareFormulaArgList compares the left-hand sides and the right-hand sides
// list type formula arguments.
-func compareFormulaArgList(lhs, rhs formulaArg, caseSensitive, exactMatch bool) byte {
+func compareFormulaArgList(lhs, rhs, matchMode formulaArg, caseSensitive bool) byte {
if len(lhs.List) < len(rhs.List) {
return criteriaL
}
@@ -9067,7 +9069,7 @@ func compareFormulaArgList(lhs, rhs formulaArg, caseSensitive, exactMatch bool)
return criteriaG
}
for arg := range lhs.List {
- criteria := compareFormulaArg(lhs.List[arg], rhs.List[arg], caseSensitive, exactMatch)
+ criteria := compareFormulaArg(lhs.List[arg], rhs.List[arg], matchMode, caseSensitive)
if criteria != criteriaEq {
return criteria
}
@@ -9077,7 +9079,7 @@ func compareFormulaArgList(lhs, rhs formulaArg, caseSensitive, exactMatch bool)
// compareFormulaArgMatrix compares the left-hand sides and the right-hand sides
// matrix type formula arguments.
-func compareFormulaArgMatrix(lhs, rhs formulaArg, caseSensitive, exactMatch bool) byte {
+func compareFormulaArgMatrix(lhs, rhs, matchMode formulaArg, caseSensitive bool) byte {
if len(lhs.Matrix) < len(rhs.Matrix) {
return criteriaL
}
@@ -9094,7 +9096,7 @@ func compareFormulaArgMatrix(lhs, rhs formulaArg, caseSensitive, exactMatch bool
return criteriaG
}
for arg := range left {
- criteria := compareFormulaArg(left[arg], right[arg], caseSensitive, exactMatch)
+ criteria := compareFormulaArg(left[arg], right[arg], matchMode, caseSensitive)
if criteria != criteriaEq {
return criteria
}
@@ -9190,7 +9192,7 @@ func (fn *formulaFuncs) COLUMNS(argsList *list.List) formulaArg {
// checkHVLookupArgs checking arguments, prepare extract mode, lookup value,
// and data for the formula functions HLOOKUP and VLOOKUP.
-func checkHVLookupArgs(name string, argsList *list.List) (idx, matchIdx int, wasExact, exactMatch bool, lookupValue, tableArray, errArg formulaArg) {
+func checkHVLookupArgs(name string, argsList *list.List) (idx int, lookupValue, tableArray, matchMode, errArg formulaArg) {
unit := map[string]string{
"HLOOKUP": "row",
"VLOOKUP": "col",
@@ -9214,7 +9216,7 @@ func checkHVLookupArgs(name string, argsList *list.List) (idx, matchIdx int, was
errArg = newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires numeric %s argument", name, unit))
return
}
- idx, matchIdx = int(arg.Number)-1, -1
+ idx, matchMode = int(arg.Number)-1, newNumberFormulaArg(matchModeMaxLess)
if argsList.Len() == 4 {
rangeLookup := argsList.Back().Value.(formulaArg).ToBool()
if rangeLookup.Type == ArgError {
@@ -9222,7 +9224,7 @@ func checkHVLookupArgs(name string, argsList *list.List) (idx, matchIdx int, was
return
}
if rangeLookup.Number == 0 {
- exactMatch = true
+ matchMode = newNumberFormulaArg(matchModeWildcard)
}
}
return
@@ -9235,35 +9237,16 @@ func checkHVLookupArgs(name string, argsList *list.List) (idx, matchIdx int, was
// HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
//
func (fn *formulaFuncs) HLOOKUP(argsList *list.List) formulaArg {
- rowIdx, matchIdx, wasExact, exactMatch,
- lookupValue, tableArray, errArg := checkHVLookupArgs("HLOOKUP", argsList)
+ rowIdx, lookupValue, tableArray, matchMode, errArg := checkHVLookupArgs("HLOOKUP", argsList)
if errArg.Type == ArgError {
return errArg
}
- row := tableArray.Matrix[0]
- if exactMatch || len(tableArray.Matrix) == TotalRows {
- start:
- for idx, mtx := range row {
- lhs := mtx
- switch lookupValue.Type {
- case ArgNumber:
- if !lookupValue.Boolean {
- lhs = mtx.ToNumber()
- if lhs.Type == ArgError {
- lhs = mtx
- }
- }
- case ArgMatrix:
- lhs = tableArray
- }
- if compareFormulaArg(lhs, lookupValue, false, exactMatch) == criteriaEq {
- matchIdx = idx
- wasExact = true
- break start
- }
- }
+ var matchIdx int
+ var wasExact bool
+ if matchMode.Number == matchModeWildcard || len(tableArray.Matrix) == TotalRows {
+ matchIdx, wasExact = lookupLinearSearch(false, lookupValue, tableArray, matchMode, newNumberFormulaArg(searchModeLinear))
} else {
- matchIdx, wasExact = hlookupBinarySearch(row, lookupValue)
+ matchIdx, wasExact = lookupBinarySearch(false, lookupValue, tableArray, matchMode, newNumberFormulaArg(searchModeAscBinary))
}
if matchIdx == -1 {
return newErrorFormulaArg(formulaErrorNA, "HLOOKUP no result found")
@@ -9271,8 +9254,8 @@ func (fn *formulaFuncs) HLOOKUP(argsList *list.List) formulaArg {
if rowIdx < 0 || rowIdx >= len(tableArray.Matrix) {
return newErrorFormulaArg(formulaErrorNA, "HLOOKUP has invalid row index")
}
- row = tableArray.Matrix[rowIdx]
- if wasExact || !exactMatch {
+ row := tableArray.Matrix[rowIdx]
+ if wasExact || matchMode.Number == matchModeWildcard {
return row[matchIdx]
}
return newErrorFormulaArg(formulaErrorNA, "HLOOKUP no result found")
@@ -9391,6 +9374,43 @@ func (fn *formulaFuncs) TRANSPOSE(argsList *list.List) formulaArg {
return newMatrixFormulaArg(mtx)
}
+// lookupLinearSearch sequentially checks each look value of the lookup array until
+// a match is found or the whole list has been searched.
+func lookupLinearSearch(vertical bool, lookupValue, lookupArray, matchMode, searchMode formulaArg) (int, bool) {
+ tableArray := []formulaArg{}
+ if vertical {
+ for _, row := range lookupArray.Matrix {
+ tableArray = append(tableArray, row[0])
+ }
+ } else {
+ tableArray = lookupArray.Matrix[0]
+ }
+ matchIdx, wasExact := -1, false
+start:
+ for i, cell := range tableArray {
+ lhs := cell
+ if lookupValue.Type == ArgNumber {
+ if lhs = cell.ToNumber(); lhs.Type == ArgError {
+ lhs = cell
+ }
+ } else if lookupValue.Type == ArgMatrix {
+ lhs = lookupArray
+ }
+ if compareFormulaArg(lhs, lookupValue, matchMode, false) == criteriaEq {
+ matchIdx = i
+ wasExact = true
+ if searchMode.Number == searchModeLinear {
+ break start
+ }
+ }
+ if matchMode.Number == matchModeMinGreater || matchMode.Number == matchModeMaxLess {
+ matchIdx = int(calcMatch(int(matchMode.Number), formulaCriteriaParser(lookupValue.Value()), tableArray).Number)
+ continue
+ }
+ }
+ return matchIdx, wasExact
+}
+
// VLOOKUP function 'looks up' a given value in the left-hand column of a
// data array (or table), and returns the corresponding value from another
// column of the array. The syntax of the function is:
@@ -9398,34 +9418,16 @@ func (fn *formulaFuncs) TRANSPOSE(argsList *list.List) formulaArg {
// VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
//
func (fn *formulaFuncs) VLOOKUP(argsList *list.List) formulaArg {
- colIdx, matchIdx, wasExact, exactMatch,
- lookupValue, tableArray, errArg := checkHVLookupArgs("VLOOKUP", argsList)
+ colIdx, lookupValue, tableArray, matchMode, errArg := checkHVLookupArgs("VLOOKUP", argsList)
if errArg.Type == ArgError {
return errArg
}
- if exactMatch || len(tableArray.Matrix) == TotalRows {
- start:
- for idx, mtx := range tableArray.Matrix {
- lhs := mtx[0]
- switch lookupValue.Type {
- case ArgNumber:
- if !lookupValue.Boolean {
- lhs = mtx[0].ToNumber()
- if lhs.Type == ArgError {
- lhs = mtx[0]
- }
- }
- case ArgMatrix:
- lhs = tableArray
- }
- if compareFormulaArg(lhs, lookupValue, false, exactMatch) == criteriaEq {
- matchIdx = idx
- wasExact = true
- break start
- }
- }
+ var matchIdx int
+ var wasExact bool
+ if matchMode.Number == matchModeWildcard || len(tableArray.Matrix) == TotalRows {
+ matchIdx, wasExact = lookupLinearSearch(true, lookupValue, tableArray, matchMode, newNumberFormulaArg(searchModeLinear))
} else {
- matchIdx, wasExact = vlookupBinarySearch(tableArray, lookupValue)
+ matchIdx, wasExact = lookupBinarySearch(true, lookupValue, tableArray, matchMode, newNumberFormulaArg(searchModeAscBinary))
}
if matchIdx == -1 {
return newErrorFormulaArg(formulaErrorNA, "VLOOKUP no result found")
@@ -9434,67 +9436,52 @@ func (fn *formulaFuncs) VLOOKUP(argsList *list.List) formulaArg {
if colIdx < 0 || colIdx >= len(mtx) {
return newErrorFormulaArg(formulaErrorNA, "VLOOKUP has invalid column index")
}
- if wasExact || !exactMatch {
+ if wasExact || matchMode.Number == matchModeWildcard {
return mtx[colIdx]
}
return newErrorFormulaArg(formulaErrorNA, "VLOOKUP no result found")
}
-// vlookupBinarySearch finds the position of a target value when range lookup
+// lookupBinarySearch finds the position of a target value when range lookup
// is TRUE, if the data of table array can't guarantee be sorted, it will
// return wrong result.
-func vlookupBinarySearch(tableArray, lookupValue formulaArg) (matchIdx int, wasExact bool) {
- var low, high, lastMatchIdx int = 0, len(tableArray.Matrix) - 1, -1
+func lookupBinarySearch(vertical bool, lookupValue, lookupArray, matchMode, searchMode formulaArg) (matchIdx int, wasExact bool) {
+ tableArray := []formulaArg{}
+ if vertical {
+ for _, row := range lookupArray.Matrix {
+ tableArray = append(tableArray, row[0])
+ }
+ } else {
+ tableArray = lookupArray.Matrix[0]
+ }
+ var low, high, lastMatchIdx int = 0, len(tableArray) - 1, -1
+ count := high
for low <= high {
mid := low + (high-low)/2
- mtx := tableArray.Matrix[mid]
- lhs := mtx[0]
- switch lookupValue.Type {
- case ArgNumber:
- if !lookupValue.Boolean {
- lhs = mtx[0].ToNumber()
- if lhs.Type == ArgError {
- lhs = mtx[0]
- }
+ cell := tableArray[mid]
+ lhs := cell
+ if lookupValue.Type == ArgNumber {
+ if lhs = cell.ToNumber(); lhs.Type == ArgError {
+ lhs = cell
}
- case ArgMatrix:
- lhs = tableArray
+ } else if lookupValue.Type == ArgMatrix && vertical {
+ lhs = lookupArray
}
- result := compareFormulaArg(lhs, lookupValue, false, false)
+ result := compareFormulaArg(lhs, lookupValue, matchMode, false)
if result == criteriaEq {
matchIdx, wasExact = mid, true
+ if searchMode.Number == searchModeDescBinary {
+ matchIdx = count - matchIdx
+ }
return
} else if result == criteriaG {
high = mid - 1
} else if result == criteriaL {
- matchIdx, low = mid, mid+1
+ matchIdx = mid
if lhs.Value() != "" {
lastMatchIdx = matchIdx
}
- } else {
- return -1, false
- }
- }
- matchIdx, wasExact = lastMatchIdx, true
- return
-}
-
-// vlookupBinarySearch finds the position of a target value when range lookup
-// is TRUE, if the data of table array can't guarantee be sorted, it will
-// return wrong result.
-func hlookupBinarySearch(row []formulaArg, lookupValue formulaArg) (matchIdx int, wasExact bool) {
- var low, high, lastMatchIdx int = 0, len(row) - 1, -1
- for low <= high {
- mid := low + (high-low)/2
- mtx := row[mid]
- result := compareFormulaArg(mtx, lookupValue, false, false)
- if result == criteriaEq {
- matchIdx, wasExact = mid, true
- return
- } else if result == criteriaG {
- high = mid - 1
- } else if result == criteriaL {
- low, lastMatchIdx = mid+1, mid
+ low = mid + 1
} else {
return -1, false
}
@@ -9542,7 +9529,7 @@ func iterateLookupArgs(lookupValue, lookupVector formulaArg) ([]formulaArg, int,
}
}
}
- compare := compareFormulaArg(lhs, col, false, false)
+ compare := compareFormulaArg(lhs, col, newNumberFormulaArg(matchModeMaxLess), false)
// Find exact match
if compare == criteriaEq {
matchIdx = idx
@@ -9584,6 +9571,139 @@ func (fn *formulaFuncs) index(array formulaArg, rowIdx, colIdx int) formulaArg {
return newListFormulaArg(cells)
}
+// validateMatchMode check the number of match mode if be equal to 0, 1, -1 or
+// 2.
+func validateMatchMode(mode float64) bool {
+ return mode == matchModeExact || mode == matchModeMinGreater || mode == matchModeMaxLess || mode == matchModeWildcard
+}
+
+// validateSearchMode check the number of search mode if be equal to 1, -1, 2
+// or -2.
+func validateSearchMode(mode float64) bool {
+ return mode == searchModeLinear || mode == searchModeReverseLinear || mode == searchModeAscBinary || mode == searchModeDescBinary
+}
+
+// prepareXlookupArgs checking and prepare arguments for the formula function
+// XLOOKUP.
+func (fn *formulaFuncs) prepareXlookupArgs(argsList *list.List) formulaArg {
+ if argsList.Len() < 3 {
+ return newErrorFormulaArg(formulaErrorVALUE, "XLOOKUP requires at least 3 arguments")
+ }
+ if argsList.Len() > 6 {
+ return newErrorFormulaArg(formulaErrorVALUE, "XLOOKUP allows at most 6 arguments")
+ }
+ lookupValue := argsList.Front().Value.(formulaArg)
+ lookupArray := argsList.Front().Next().Value.(formulaArg)
+ returnArray := argsList.Front().Next().Next().Value.(formulaArg)
+ ifNotFond := newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
+ matchMode, searchMode := newNumberFormulaArg(matchModeExact), newNumberFormulaArg(searchModeLinear)
+ if argsList.Len() > 3 {
+ ifNotFond = argsList.Front().Next().Next().Next().Value.(formulaArg)
+ }
+ if argsList.Len() > 4 {
+ if matchMode = argsList.Front().Next().Next().Next().Next().Value.(formulaArg).ToNumber(); matchMode.Type != ArgNumber {
+ return matchMode
+ }
+ }
+ if argsList.Len() > 5 {
+ if searchMode = argsList.Back().Value.(formulaArg).ToNumber(); searchMode.Type != ArgNumber {
+ return searchMode
+ }
+ }
+ if lookupArray.Type != ArgMatrix || returnArray.Type != ArgMatrix {
+ return newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
+ }
+ if !validateMatchMode(matchMode.Number) || !validateSearchMode(searchMode.Number) {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ return newListFormulaArg([]formulaArg{lookupValue, lookupArray, returnArray, ifNotFond, matchMode, searchMode})
+}
+
+// xlookup is an implementation of the formula function XLOOKUP.
+func (fn *formulaFuncs) xlookup(lookupRows, lookupCols, returnArrayRows, returnArrayCols, matchIdx int,
+ condition1, condition2, condition3, condition4 bool, returnArray formulaArg) formulaArg {
+ result := [][]formulaArg{}
+ for rowIdx, row := range returnArray.Matrix {
+ for colIdx, cell := range row {
+ if condition1 {
+ if condition2 {
+ result = append(result, []formulaArg{cell})
+ continue
+ }
+ if returnArrayRows > 1 && returnArrayCols > 1 {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ }
+ if condition3 {
+ if returnArrayCols != lookupCols {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ if colIdx == matchIdx {
+ result = append(result, []formulaArg{cell})
+ continue
+ }
+ }
+ if condition4 {
+ if returnArrayRows != lookupRows {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ if rowIdx == matchIdx {
+ if len(result) == 0 {
+ result = append(result, []formulaArg{cell})
+ continue
+ }
+ result[0] = append(result[0], cell)
+ }
+ }
+ }
+ }
+ array := newMatrixFormulaArg(result)
+ cells := array.ToList()
+ if len(cells) == 1 {
+ return cells[0]
+ }
+ return array
+}
+
+// XLOOKUP function searches a range or an array, and then returns the item
+// corresponding to the first match it finds. If no match exists, then
+// XLOOKUP can return the closest (approximate) match. The syntax of the
+// function is:
+//
+// XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
+//
+func (fn *formulaFuncs) XLOOKUP(argsList *list.List) formulaArg {
+ args := fn.prepareXlookupArgs(argsList)
+ if args.Type != ArgList {
+ return args
+ }
+ lookupValue, lookupArray, returnArray, ifNotFond, matchMode, searchMode := args.List[0], args.List[1], args.List[2], args.List[3], args.List[4], args.List[5]
+ lookupRows, lookupCols := len(lookupArray.Matrix), 0
+ if lookupRows > 0 {
+ lookupCols = len(lookupArray.Matrix[0])
+ }
+ if lookupRows != 1 && lookupCols != 1 {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ verticalLookup := lookupRows >= lookupCols
+ var matchIdx int
+ switch searchMode.Number {
+ case searchModeLinear, searchModeReverseLinear:
+ matchIdx, _ = lookupLinearSearch(verticalLookup, lookupValue, lookupArray, matchMode, searchMode)
+ default:
+ matchIdx, _ = lookupBinarySearch(verticalLookup, lookupValue, lookupArray, matchMode, searchMode)
+ }
+ if matchIdx == -1 {
+ return ifNotFond
+ }
+ returnArrayRows, returnArrayCols := len(returnArray.Matrix), len(returnArray.Matrix[0])
+ condition1 := lookupRows == 1 && lookupCols == 1
+ condition2 := returnArrayRows == 1 || returnArrayCols == 1
+ condition3 := lookupRows == 1 && lookupCols > 1
+ condition4 := lookupRows > 1 && lookupCols == 1
+ return fn.xlookup(lookupRows, lookupCols, returnArrayRows, returnArrayCols, matchIdx, condition1, condition2, condition3, condition4, returnArray)
+}
+
// INDEX function returns a reference to a cell that lies in a specified row
// and column of a range of cells. The syntax of the function is:
//
diff --git a/calc_test.go b/calc_test.go
index 97a7588..be2f185 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -3497,17 +3497,17 @@ func TestCalcToList(t *testing.T) {
}
func TestCalcCompareFormulaArg(t *testing.T) {
- assert.Equal(t, compareFormulaArg(newEmptyFormulaArg(), newEmptyFormulaArg(), false, false), criteriaEq)
+ assert.Equal(t, compareFormulaArg(newEmptyFormulaArg(), newEmptyFormulaArg(), newNumberFormulaArg(matchModeMaxLess), false), criteriaEq)
lhs := newListFormulaArg([]formulaArg{newEmptyFormulaArg()})
rhs := newListFormulaArg([]formulaArg{newEmptyFormulaArg(), newEmptyFormulaArg()})
- assert.Equal(t, compareFormulaArg(lhs, rhs, false, false), criteriaL)
- assert.Equal(t, compareFormulaArg(rhs, lhs, false, false), criteriaG)
+ assert.Equal(t, compareFormulaArg(lhs, rhs, newNumberFormulaArg(matchModeMaxLess), false), criteriaL)
+ assert.Equal(t, compareFormulaArg(rhs, lhs, newNumberFormulaArg(matchModeMaxLess), false), criteriaG)
lhs = newListFormulaArg([]formulaArg{newBoolFormulaArg(true)})
rhs = newListFormulaArg([]formulaArg{newBoolFormulaArg(true)})
- assert.Equal(t, compareFormulaArg(lhs, rhs, false, false), criteriaEq)
+ assert.Equal(t, compareFormulaArg(lhs, rhs, newNumberFormulaArg(matchModeMaxLess), false), criteriaEq)
- assert.Equal(t, compareFormulaArg(formulaArg{Type: ArgUnknown}, formulaArg{Type: ArgUnknown}, false, false), criteriaErr)
+ assert.Equal(t, compareFormulaArg(formulaArg{Type: ArgUnknown}, formulaArg{Type: ArgUnknown}, newNumberFormulaArg(matchModeMaxLess), false), criteriaErr)
}
func TestCalcMatchPattern(t *testing.T) {
@@ -3779,6 +3779,113 @@ func TestCalcXIRR(t *testing.T) {
}
}
+func TestCalcXLOOKUP(t *testing.T) {
+ cellData := [][]interface{}{
+ {},
+ {nil, nil, "Quarter", "Gross Profit", "Net profit", "Profit %"},
+ {nil, nil, "Qtr1", nil, 19342, 29.30},
+ {},
+ {nil, "Income Statement", "Qtr1", "Qtr2", "Qtr3", "Qtr4", "Total"},
+ {nil, "Total sales", 50000, 78200, 89500, 91250, 308.95},
+ {nil, "Cost of sales", -25000, -42050, -59450, -60450, -186950},
+ {nil, "Gross Profit", 25000, 36150, 30050, 30800, 122000},
+ {},
+ {nil, "Depreciation", -899, -791, -202, -412, -2304},
+ {nil, "Interest", -513, -853, -150, -956, -2472},
+ {nil, "Earnings before Tax", 23588, 34506, 29698, 29432, 117224},
+ {},
+ {nil, "Tax", -4246, -6211, -5346, -5298, 21100},
+ {},
+ {nil, "Net profit", 19342, 28295, 24352, 24134, 96124},
+ {nil, "Profit %", 0.293, 0.278, 0.234, 0.276, 0.269},
+ }
+ f := prepareCalcData(cellData)
+ formulaList := map[string]string{
+ "=SUM(XLOOKUP($C3,$C5:$C5,$C6:$C17,NA(),0,2))": "87272.293",
+ "=SUM(XLOOKUP($C3,$C5:$C5,$C6:$G6,NA(),0,-2))": "309258.95",
+ "=SUM(XLOOKUP($C3,$C5:$C5,$C6:$C17,NA(),0,-2))": "87272.293",
+ "=SUM(XLOOKUP($C3,$C5:$G5,$C6:$G17,NA(),0,2))": "87272.293",
+ "=SUM(XLOOKUP(D2,$B6:$B17,$C6:$G17,NA(),0,2))": "244000",
+ "=XLOOKUP(D2,$B6:$B17,C6:C17)": "25000",
+ "=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))": "25000",
+ "=XLOOKUP(\"*p*\",B2:B9,C2:C9,NA(),2)": "25000",
+ }
+ for formula, expected := range formulaList {
+ assert.NoError(t, f.SetCellFormula("Sheet1", "D3", formula))
+ result, err := f.CalcCellValue("Sheet1", "D3")
+ assert.NoError(t, err, formula)
+ assert.Equal(t, expected, result, formula)
+ }
+ calcError := map[string]string{
+ "=XLOOKUP()": "XLOOKUP requires at least 3 arguments",
+ "=XLOOKUP($C3,$C5:$C5,$C6:$C17,NA(),0,2,1)": "XLOOKUP allows at most 6 arguments",
+ "=XLOOKUP($C3,$C5,$C6,NA(),0,2)": "#N/A",
+ "=XLOOKUP($C3,$C4:$D5,$C6:$C17,NA(),0,2)": "#VALUE!",
+ "=XLOOKUP($C3,$C5:$C5,$C6:$G17,NA(),0,-2)": "#VALUE!",
+ "=XLOOKUP($C3,$C5:$G5,$C6:$F7,NA(),0,2)": "#VALUE!",
+ "=XLOOKUP(D2,$B6:$B17,$C6:$G16,NA(),0,2)": "#VALUE!",
+ "=XLOOKUP(D2,$B6:$B17,$C6:$G17,NA(),3,2)": "#VALUE!",
+ "=XLOOKUP(D2,$B6:$B17,$C6:$G17,NA(),0,0)": "#VALUE!",
+ "=XLOOKUP(D2,$B6:$B17,$C6:$G17,NA(),\"\",2)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=XLOOKUP(D2,$B6:$B17,$C6:$G17,NA(),0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ }
+ for formula, expected := range calcError {
+ assert.NoError(t, f.SetCellFormula("Sheet1", "D3", formula))
+ result, err := f.CalcCellValue("Sheet1", "D3")
+ assert.EqualError(t, err, expected, formula)
+ assert.Equal(t, "", result, formula)
+ }
+
+ cellData = [][]interface{}{
+ {"Salesperson", "Item", "Amont"},
+ {"B", "Apples", 30, 25, 15, 50, 45, 18},
+ {"L", "Oranges", 25, "D3", "E3"},
+ {"C", "Grapes", 15},
+ {"L", "Lemons", 50},
+ {"L", "Oranges", 45},
+ {"C", "Peaches", 18},
+ {"B", "Pears", 40},
+ {"B", "Apples", 55},
+ }
+ f = prepareCalcData(cellData)
+ formulaList = map[string]string{
+ // Test match mode with partial match (wildcards)
+ "=XLOOKUP(\"*p*\",B2:B9,C2:C9,NA(),2)": "30",
+ // Test match mode with approximate match in vertical (next larger item)
+ "=XLOOKUP(32,B2:B9,C2:C9,NA(),1)": "30",
+ // Test match mode with approximate match in horizontal (next larger item)
+ "=XLOOKUP(30,C2:F2,C3:F3,NA(),1)": "25",
+ // Test match mode with approximate match in vertical (next smaller item)
+ "=XLOOKUP(40,C2:C9,B2:B9,NA(),-1)": "Pears",
+ // Test match mode with approximate match in horizontal (next smaller item)
+ "=XLOOKUP(29,C2:F2,C3:F3,NA(),-1)": "D3",
+ // Test search mode
+ "=XLOOKUP(\"L\",A2:A9,C2:C9,NA(),0,1)": "25",
+ "=XLOOKUP(\"L\",A2:A9,C2:C9,NA(),0,-1)": "45",
+ "=XLOOKUP(\"L\",A2:A9,C2:C9,NA(),0,2)": "50",
+ "=XLOOKUP(\"L\",A2:A9,C2:C9,NA(),0,-2)": "45",
+ // Test match mode and search mode
+ "=XLOOKUP(29,C2:H2,C3:H3,NA(),-1,-1)": "D3",
+ "=XLOOKUP(29,C2:H2,C3:H3,NA(),-1,1)": "D3",
+ }
+ for formula, expected := range formulaList {
+ assert.NoError(t, f.SetCellFormula("Sheet1", "D3", formula))
+ result, err := f.CalcCellValue("Sheet1", "D3")
+ assert.NoError(t, err, formula)
+ assert.Equal(t, expected, result, formula)
+ }
+ calcError = map[string]string{
+ // Test match mode with exact match
+ "=XLOOKUP(\"*p*\",B2:B9,C2:C9,NA(),0)": "#N/A",
+ }
+ for formula, expected := range calcError {
+ assert.NoError(t, f.SetCellFormula("Sheet1", "D3", formula))
+ result, err := f.CalcCellValue("Sheet1", "D3")
+ assert.EqualError(t, err, expected, formula)
+ assert.Equal(t, "", result, formula)
+ }
+}
+
func TestCalcXNPV(t *testing.T) {
cellData := [][]interface{}{{nil, 0.05},
{"01/01/2016", -10000, nil},
diff --git a/excelize_test.go b/excelize_test.go
index a15e801..4c136b6 100644
--- a/excelize_test.go
+++ b/excelize_test.go
@@ -590,7 +590,16 @@ func TestSetCellStyleBorder(t *testing.T) {
var style int
// Test set border on overlapping area with vertical variants shading styles gradient fill.
- style, err = f.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":2},{"type":"top","color":"00FF00","style":12},{"type":"bottom","color":"FFFF00","style":5},{"type":"right","color":"FF0000","style":6},{"type":"diagonalDown","color":"A020F0","style":9},{"type":"diagonalUp","color":"A020F0","style":8}]}`)
+ style, err = f.NewStyle(&Style{
+ Border: []Border{
+ {Type: "left", Color: "0000FF", Style: 3},
+ {Type: "top", Color: "00FF00", Style: 4},
+ {Type: "bottom", Color: "FFFF00", Style: 5},
+ {Type: "right", Color: "FF0000", Style: 6},
+ {Type: "diagonalDown", Color: "A020F0", Style: 7},
+ {Type: "diagonalUp", Color: "A020F0", Style: 8},
+ },
+ })
if !assert.NoError(t, err) {
t.FailNow()
}
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 08:46:49 +0000