summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go256
-rw-r--r--calc_test.go45
2 files changed, 294 insertions, 7 deletions
diff --git a/calc.go b/calc.go
index fd03918..1d10f62 100644
--- a/calc.go
+++ b/calc.go
@@ -74,6 +74,7 @@ const (
criteriaG
criteriaBeg
criteriaEnd
+ criteriaErr
)
// formulaCriteria defined formula criteria parser result.
@@ -142,6 +143,24 @@ func (fa formulaArg) ToNumber() formulaArg {
return newNumberFormulaArg(n)
}
+// ToBool returns a formula argument with boolean data type.
+func (fa formulaArg) ToBool() formulaArg {
+ var b bool
+ var err error
+ switch fa.Type {
+ case ArgString:
+ b, err = strconv.ParseBool(fa.String)
+ if err != nil {
+ return newErrorFormulaArg(formulaErrorVALUE, err.Error())
+ }
+ case ArgNumber:
+ if fa.Boolean && fa.Number == 1 {
+ b = true
+ }
+ }
+ return newBoolFormulaArg(b)
+}
+
// formulaFuncs is the type of the formula functions.
type formulaFuncs struct{}
@@ -312,6 +331,11 @@ func newMatrixFormulaArg(m [][]formulaArg) formulaArg {
return formulaArg{Type: ArgMatrix, Matrix: m}
}
+// newListFormulaArg create a list formula argument.
+func newListFormulaArg(l []formulaArg) formulaArg {
+ return formulaArg{Type: ArgList, List: l}
+}
+
// newBoolFormulaArg constructs a boolean formula argument.
func newBoolFormulaArg(b bool) formulaArg {
var n float64
@@ -321,11 +345,17 @@ func newBoolFormulaArg(b bool) formulaArg {
return formulaArg{Type: ArgNumber, Number: n, Boolean: true}
}
-// newErrorFormulaArg create an error formula argument of a given type with a specified error message.
+// newErrorFormulaArg create an error formula argument of a given type with a
+// specified error message.
func newErrorFormulaArg(formulaError, msg string) formulaArg {
return formulaArg{Type: ArgError, String: formulaError, Error: msg}
}
+// newEmptyFormulaArg create an empty formula argument.
+func newEmptyFormulaArg() formulaArg {
+ return formulaArg{Type: ArgEmpty}
+}
+
// evalInfixExp evaluate syntax analysis by given infix expression after
// lexical analysis. Evaluate an infix expression containing formulas by
// stacks:
@@ -428,6 +458,12 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error)
// current token is logical
if token.TType == efp.OperatorsInfix && token.TSubType == efp.TokenSubTypeLogical {
}
+ if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeLogical {
+ argsStack.Peek().(*list.List).PushBack(formulaArg{
+ String: token.TValue,
+ Type: ArgString,
+ })
+ }
// current token is text
if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText {
@@ -841,6 +877,15 @@ func (f *File) parseReference(sheet, reference string) (arg formulaArg, err erro
continue
}
if cr.Col, cr.Row, err = CellNameToCoordinates(tokens[0]); err != nil {
+ if cr.Col, err = ColumnNameToNumber(tokens[0]); err != nil {
+ return
+ }
+ cellRanges.PushBack(cellRange{
+ From: cellRef{Sheet: sheet, Col: cr.Col, Row: 1},
+ To: cellRef{Sheet: sheet, Col: cr.Col, Row: TotalRows},
+ })
+ cellRefs.Init()
+ arg, err = f.rangeResolver(cellRefs, cellRanges)
return
}
e := refs.Back()
@@ -3189,14 +3234,13 @@ func (fn *formulaFuncs) ISNUMBER(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "ISNUMBER requires 1 argument")
}
- token := argsList.Front().Value.(formulaArg)
- result := "FALSE"
+ token, result := argsList.Front().Value.(formulaArg), false
if token.Type == ArgString && token.String != "" {
if _, err := strconv.Atoi(token.String); err == nil {
- result = "TRUE"
+ result = true
}
}
- return newStringFormulaArg(result)
+ return newBoolFormulaArg(result)
}
// ISODD function tests if a supplied number (or numeric expression) evaluates
@@ -3529,3 +3573,205 @@ func (fn *formulaFuncs) CHOOSE(argsList *list.List) formulaArg {
}
return result
}
+
+// deepMatchRune finds whether the text deep matches/satisfies the pattern
+// string.
+func deepMatchRune(str, pattern []rune, simple bool) bool {
+ for len(pattern) > 0 {
+ switch pattern[0] {
+ default:
+ if len(str) == 0 || str[0] != pattern[0] {
+ return false
+ }
+ case '?':
+ if len(str) == 0 && !simple {
+ return false
+ }
+ case '*':
+ return deepMatchRune(str, pattern[1:], simple) ||
+ (len(str) > 0 && deepMatchRune(str[1:], pattern, simple))
+ }
+ str = str[1:]
+ pattern = pattern[1:]
+ }
+ return len(str) == 0 && len(pattern) == 0
+}
+
+// matchPattern finds whether the text matches or satisfies the pattern
+// string. The pattern supports '*' and '?' wildcards in the pattern string.
+func matchPattern(pattern, name string) (matched bool) {
+ if pattern == "" {
+ return name == pattern
+ }
+ if pattern == "*" {
+ return true
+ }
+ rname := make([]rune, 0, len(name))
+ rpattern := make([]rune, 0, len(pattern))
+ for _, r := range name {
+ rname = append(rname, r)
+ }
+ for _, r := range pattern {
+ rpattern = append(rpattern, r)
+ }
+ simple := false // Does extended wildcard '*' and '?' match.
+ return deepMatchRune(rname, rpattern, simple)
+}
+
+// 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 {
+ if lhs.Type != rhs.Type {
+ return criteriaErr
+ }
+ switch lhs.Type {
+ case ArgNumber:
+ if lhs.Number == rhs.Number {
+ return criteriaEq
+ }
+ if lhs.Number < rhs.Number {
+ return criteriaL
+ }
+ return criteriaG
+ case ArgString:
+ ls := lhs.String
+ rs := rhs.String
+ if !caseSensitive {
+ ls = strings.ToLower(ls)
+ rs = strings.ToLower(rs)
+ }
+ if exactMatch {
+ match := matchPattern(rs, ls)
+ if match {
+ return criteriaEq
+ }
+ return criteriaG
+ }
+ return byte(strings.Compare(ls, rs))
+ case ArgEmpty:
+ return criteriaEq
+ case ArgList:
+ return compareFormulaArgList(lhs, rhs, caseSensitive, exactMatch)
+ case ArgMatrix:
+ return compareFormulaArgMatrix(lhs, rhs, caseSensitive, exactMatch)
+ }
+ 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 {
+ if len(lhs.List) < len(rhs.List) {
+ return criteriaL
+ }
+ if len(lhs.List) > len(rhs.List) {
+ return criteriaG
+ }
+ for arg := range lhs.List {
+ criteria := compareFormulaArg(lhs.List[arg], rhs.List[arg], caseSensitive, exactMatch)
+ if criteria != criteriaEq {
+ return criteria
+ }
+ }
+ return criteriaEq
+}
+
+// compareFormulaArgMatrix compares the left-hand sides and the right-hand sides
+// matrix type formula arguments.
+func compareFormulaArgMatrix(lhs, rhs formulaArg, caseSensitive, exactMatch bool) byte {
+ if len(lhs.Matrix) < len(rhs.Matrix) {
+ return criteriaL
+ }
+ if len(lhs.Matrix) > len(rhs.Matrix) {
+ return criteriaG
+ }
+ for i := range lhs.Matrix {
+ left := lhs.Matrix[i]
+ right := lhs.Matrix[i]
+ if len(left) < len(right) {
+ return criteriaL
+ }
+ if len(left) > len(right) {
+ return criteriaG
+ }
+ for arg := range left {
+ criteria := compareFormulaArg(left[arg], right[arg], caseSensitive, exactMatch)
+ if criteria != criteriaEq {
+ return criteria
+ }
+ }
+ }
+ return criteriaEq
+}
+
+// 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:
+//
+// VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
+//
+func (fn *formulaFuncs) VLOOKUP(argsList *list.List) formulaArg {
+ if argsList.Len() < 3 {
+ return newErrorFormulaArg(formulaErrorVALUE, "VLOOKUP requires at least 3 arguments")
+ }
+ if argsList.Len() > 4 {
+ return newErrorFormulaArg(formulaErrorVALUE, "VLOOKUP requires at most 4 arguments")
+ }
+ lookupValue := argsList.Front().Value.(formulaArg)
+ tableArray := argsList.Front().Next().Value.(formulaArg)
+ if tableArray.Type != ArgMatrix {
+ return newErrorFormulaArg(formulaErrorVALUE, "VLOOKUP requires second argument of table array")
+ }
+ colIdx := argsList.Front().Next().Next().Value.(formulaArg).ToNumber()
+ if colIdx.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorVALUE, "VLOOKUP requires numeric col argument")
+ }
+ col, matchIdx, wasExact, exactMatch := int(colIdx.Number)-1, -1, false, false
+ if argsList.Len() == 4 {
+ rangeLookup := argsList.Back().Value.(formulaArg).ToBool()
+ if rangeLookup.Type == ArgError {
+ return newErrorFormulaArg(formulaErrorVALUE, rangeLookup.Error)
+ }
+ if rangeLookup.Number == 0 {
+ exactMatch = true
+ }
+ }
+start:
+ for idx, mtx := range tableArray.Matrix {
+ if len(mtx) == 0 {
+ continue
+ }
+ 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
+ }
+ switch compareFormulaArg(lhs, lookupValue, false, exactMatch) {
+ case criteriaL:
+ matchIdx = idx
+ case criteriaEq:
+ matchIdx = idx
+ wasExact = true
+ break start
+ }
+ }
+ if matchIdx == -1 {
+ return newErrorFormulaArg(formulaErrorNA, "VLOOKUP no result found")
+ }
+ mtx := tableArray.Matrix[matchIdx]
+ if col < 0 || col >= len(mtx) {
+ return newErrorFormulaArg(formulaErrorNA, "VLOOKUP has invalid column index")
+ }
+ if wasExact || !exactMatch {
+ return mtx[col]
+ }
+ return newErrorFormulaArg(formulaErrorNA, "VLOOKUP no result found")
+}
diff --git a/calc_test.go b/calc_test.go
index d3621c6..881077c 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -560,19 +560,26 @@ func TestCalcCellValue(t *testing.T) {
"=CHOOSE(4,\"red\",\"blue\",\"green\",\"brown\")": "brown",
"=CHOOSE(1,\"red\",\"blue\",\"green\",\"brown\")": "red",
"=SUM(CHOOSE(A2,A1,B1:B2,A1:A3,A1:A4))": "9",
+ // VLOOKUP
+ "=VLOOKUP(D2,D:D,1,FALSE)": "Jan",
+ "=VLOOKUP(D2,D:D,1,TRUE)": "Month", // should be Feb
+ "=VLOOKUP(INT(36693),F2:F2,1,FALSE)": "36693",
+ "=VLOOKUP(INT(F2),F3:F9,1)": "32080",
+ "=VLOOKUP(MUNIT(3),MUNIT(2),1)": "0", // should be 1
+ "=VLOOKUP(MUNIT(3),MUNIT(3),1)": "1",
}
for formula, expected := range mathCalc {
f := prepareData()
assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
result, err := f.CalcCellValue("Sheet1", "C1")
- assert.NoError(t, err)
+ assert.NoError(t, err, formula)
assert.Equal(t, expected, result, formula)
}
mathCalcError := map[string]string{
// ABS
"=ABS()": "ABS requires 1 numeric argument",
`=ABS("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax",
- "=ABS(~)": `cannot convert cell "~" to coordinates: invalid cell name "~"`,
+ "=ABS(~)": `invalid column name "~"`,
// ACOS
"=ACOS()": "ACOS requires 1 numeric argument",
`=ACOS("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax",
@@ -907,6 +914,19 @@ func TestCalcCellValue(t *testing.T) {
"=CHOOSE()": "CHOOSE requires 2 arguments",
"=CHOOSE(\"index_num\",0)": "CHOOSE requires first argument of type number",
"=CHOOSE(2,0)": "index_num should be <= to the number of values",
+ // VLOOKUP
+ "=VLOOKUP()": "VLOOKUP requires at least 3 arguments",
+ "=VLOOKUP(D2,D1,1,FALSE)": "VLOOKUP requires second argument of table array",
+ "=VLOOKUP(D2,D:D,FALSE,FALSE)": "VLOOKUP requires numeric col argument",
+ "=VLOOKUP(D2,D:D,1,FALSE,FALSE)": "VLOOKUP requires at most 4 arguments",
+ "=VLOOKUP(D2,D:D,1,2)": "strconv.ParseBool: parsing \"2\": invalid syntax",
+ "=VLOOKUP(D2,D10:D10,1,FALSE)": "VLOOKUP no result found",
+ "=VLOOKUP(D2,D:D,2,FALSE)": "VLOOKUP has invalid column index",
+ "=VLOOKUP(D2,C:C,1,FALSE)": "VLOOKUP no result found",
+ "=VLOOKUP(ISNUMBER(1),F3:F9,1)": "VLOOKUP no result found",
+ "=VLOOKUP(INT(1),E2:E9,1)": "VLOOKUP no result found",
+ "=VLOOKUP(MUNIT(2),MUNIT(3),1)": "VLOOKUP no result found",
+ "=VLOOKUP(A1:B2,B2:B3,1)": "VLOOKUP no result found",
}
for formula, expected := range mathCalcError {
f := prepareData()
@@ -1085,3 +1105,24 @@ func TestDet(t *testing.T) {
{4, 5, 6, 7},
}), float64(0))
}
+
+func TestCompareFormulaArg(t *testing.T) {
+ assert.Equal(t, compareFormulaArg(newEmptyFormulaArg(), newEmptyFormulaArg(), false, 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)
+
+ lhs = newListFormulaArg([]formulaArg{newBoolFormulaArg(true)})
+ rhs = newListFormulaArg([]formulaArg{newBoolFormulaArg(true)})
+ assert.Equal(t, compareFormulaArg(lhs, rhs, false, false), criteriaEq)
+
+ assert.Equal(t, compareFormulaArg(formulaArg{Type: ArgUnknown}, formulaArg{Type: ArgUnknown}, false, false), criteriaErr)
+}
+
+func TestMatchPattern(t *testing.T) {
+ assert.True(t, matchPattern("", ""))
+ assert.True(t, matchPattern("file/*", "file/abc/bcd/def"))
+ assert.True(t, matchPattern("*", ""))
+ assert.False(t, matchPattern("file/?", "file/abc/bcd/def"))
+}
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 08:40:22 +0000