Go language library for reading and writing Microsoft Excel™ (XLAM / XLSM / XLSX / XLTM / XLTX) spreadsheets
| 修订版 | a65c5846e45fece382f72465f9e858c788dfcfef (tree) |
|---|---|
| 时间 | 2022-07-10 19:14:48 |
| 作者 |  xuri <xuri.me@gmai...> |
| Commiter | xuri |
This closes #1262, support for dependence formulas calculation
- Add export option MaxCalcIterations for specifies the maximum iterations for iterative calculation
- Update unit test for the database formula functions
calc.go (diff) calc_test.go (diff) excelize.go (diff)| @@ -26,6 +26,7 @@ import ( | ||
| 26 | 26 | "sort" |
| 27 | 27 | "strconv" |
| 28 | 28 | "strings" |
| 29 | + "sync" | |
| 29 | 30 | "time" |
| 30 | 31 | "unicode" |
| 31 | 32 | "unsafe" |
| @@ -193,6 +194,13 @@ var ( | ||
| 193 | 194 | } |
| 194 | 195 | ) |
| 195 | 196 | |
| 197 | +// calcContext defines the formula execution context. | |
| 198 | +type calcContext struct { | |
| 199 | + sync.Mutex | |
| 200 | + entry string | |
| 201 | + iterations map[string]uint | |
| 202 | +} | |
| 203 | + | |
| 196 | 204 | // cellRef defines the structure of a cell reference. |
| 197 | 205 | type cellRef struct { |
| 198 | 206 | Col int |
| @@ -312,6 +320,7 @@ func (fa formulaArg) ToList() []formulaArg { | ||
| 312 | 320 | // formulaFuncs is the type of the formula functions. |
| 313 | 321 | type formulaFuncs struct { |
| 314 | 322 | f *File |
| 323 | + ctx *calcContext | |
| 315 | 324 | sheet, cell string |
| 316 | 325 | } |
| 317 | 326 |
| @@ -758,6 +767,13 @@ type formulaFuncs struct { | ||
| 758 | 767 | // ZTEST |
| 759 | 768 | // |
| 760 | 769 | func (f *File) CalcCellValue(sheet, cell string) (result string, err error) { |
| 770 | + return f.calcCellValue(&calcContext{ | |
| 771 | + entry: fmt.Sprintf("%s!%s", sheet, cell), | |
| 772 | + iterations: make(map[string]uint), | |
| 773 | + }, sheet, cell) | |
| 774 | +} | |
| 775 | + | |
| 776 | +func (f *File) calcCellValue(ctx *calcContext, sheet, cell string) (result string, err error) { | |
| 761 | 777 | var ( |
| 762 | 778 | formula string |
| 763 | 779 | token formulaArg |
| @@ -770,7 +786,7 @@ func (f *File) CalcCellValue(sheet, cell string) (result string, err error) { | ||
| 770 | 786 | if tokens == nil { |
| 771 | 787 | return |
| 772 | 788 | } |
| 773 | - if token, err = f.evalInfixExp(sheet, cell, tokens); err != nil { | |
| 789 | + if token, err = f.evalInfixExp(ctx, sheet, cell, tokens); err != nil { | |
| 774 | 790 | return |
| 775 | 791 | } |
| 776 | 792 | result = token.Value() |
| @@ -850,7 +866,7 @@ func newEmptyFormulaArg() formulaArg { | ||
| 850 | 866 | // |
| 851 | 867 | // TODO: handle subtypes: Nothing, Text, Logical, Error, Concatenation, Intersection, Union |
| 852 | 868 | // |
| 853 | -func (f *File) evalInfixExp(sheet, cell string, tokens []efp.Token) (formulaArg, error) { | |
| 869 | +func (f *File) evalInfixExp(ctx *calcContext, sheet, cell string, tokens []efp.Token) (formulaArg, error) { | |
| 854 | 870 | var err error |
| 855 | 871 | opdStack, optStack, opfStack, opfdStack, opftStack, argsStack := NewStack(), NewStack(), NewStack(), NewStack(), NewStack(), NewStack() |
| 856 | 872 | var inArray, inArrayRow bool |
| @@ -860,7 +876,7 @@ func (f *File) evalInfixExp(sheet, cell string, tokens []efp.Token) (formulaArg, | ||
| 860 | 876 | |
| 861 | 877 | // out of function stack |
| 862 | 878 | if opfStack.Len() == 0 { |
| 863 | - if err = f.parseToken(sheet, token, opdStack, optStack); err != nil { | |
| 879 | + if err = f.parseToken(ctx, sheet, token, opdStack, optStack); err != nil { | |
| 864 | 880 | return newEmptyFormulaArg(), err |
| 865 | 881 | } |
| 866 | 882 | } |
| @@ -896,7 +912,7 @@ func (f *File) evalInfixExp(sheet, cell string, tokens []efp.Token) (formulaArg, | ||
| 896 | 912 | token.TValue = refTo |
| 897 | 913 | } |
| 898 | 914 | // parse reference: must reference at here |
| 899 | - result, err := f.parseReference(sheet, token.TValue) | |
| 915 | + result, err := f.parseReference(ctx, sheet, token.TValue) | |
| 900 | 916 | if err != nil { |
| 901 | 917 | return result, err |
| 902 | 918 | } |
| @@ -912,7 +928,7 @@ func (f *File) evalInfixExp(sheet, cell string, tokens []efp.Token) (formulaArg, | ||
| 912 | 928 | if refTo != "" { |
| 913 | 929 | token.TValue = refTo |
| 914 | 930 | } |
| 915 | - result, err := f.parseReference(sheet, token.TValue) | |
| 931 | + result, err := f.parseReference(ctx, sheet, token.TValue) | |
| 916 | 932 | if err != nil { |
| 917 | 933 | return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE), err |
| 918 | 934 | } |
| @@ -938,7 +954,7 @@ func (f *File) evalInfixExp(sheet, cell string, tokens []efp.Token) (formulaArg, | ||
| 938 | 954 | } |
| 939 | 955 | |
| 940 | 956 | // check current token is opft |
| 941 | - if err = f.parseToken(sheet, token, opfdStack, opftStack); err != nil { | |
| 957 | + if err = f.parseToken(ctx, sheet, token, opfdStack, opftStack); err != nil { | |
| 942 | 958 | return newEmptyFormulaArg(), err |
| 943 | 959 | } |
| 944 | 960 |
| @@ -975,7 +991,7 @@ func (f *File) evalInfixExp(sheet, cell string, tokens []efp.Token) (formulaArg, | ||
| 975 | 991 | arrayRow, inArray = []formulaArg{}, false |
| 976 | 992 | continue |
| 977 | 993 | } |
| 978 | - if err = f.evalInfixExpFunc(sheet, cell, token, nextToken, opfStack, opdStack, opftStack, opfdStack, argsStack); err != nil { | |
| 994 | + if err = f.evalInfixExpFunc(ctx, sheet, cell, token, nextToken, opfStack, opdStack, opftStack, opfdStack, argsStack); err != nil { | |
| 979 | 995 | return newEmptyFormulaArg(), err |
| 980 | 996 | } |
| 981 | 997 | } |
| @@ -994,13 +1010,13 @@ func (f *File) evalInfixExp(sheet, cell string, tokens []efp.Token) (formulaArg, | ||
| 994 | 1010 | } |
| 995 | 1011 | |
| 996 | 1012 | // evalInfixExpFunc evaluate formula function in the infix expression. |
| 997 | -func (f *File) evalInfixExpFunc(sheet, cell string, token, nextToken efp.Token, opfStack, opdStack, opftStack, opfdStack, argsStack *Stack) error { | |
| 1013 | +func (f *File) evalInfixExpFunc(ctx *calcContext, sheet, cell string, token, nextToken efp.Token, opfStack, opdStack, opftStack, opfdStack, argsStack *Stack) error { | |
| 998 | 1014 | if !isFunctionStopToken(token) { |
| 999 | 1015 | return nil |
| 1000 | 1016 | } |
| 1001 | 1017 | prepareEvalInfixExp(opfStack, opftStack, opfdStack, argsStack) |
| 1002 | 1018 | // call formula function to evaluate |
| 1003 | - arg := callFuncByName(&formulaFuncs{f: f, sheet: sheet, cell: cell}, strings.NewReplacer( | |
| 1019 | + arg := callFuncByName(&formulaFuncs{f: f, sheet: sheet, cell: cell, ctx: ctx}, strings.NewReplacer( | |
| 1004 | 1020 | "_xlfn.", "", ".", "dot").Replace(opfStack.Peek().(efp.Token).TValue), |
| 1005 | 1021 | []reflect.Value{reflect.ValueOf(argsStack.Peek().(*list.List))}) |
| 1006 | 1022 | if arg.Type == ArgError && opfStack.Len() == 1 { |
| @@ -1337,14 +1353,14 @@ func tokenToFormulaArg(token efp.Token) formulaArg { | ||
| 1337 | 1353 | |
| 1338 | 1354 | // parseToken parse basic arithmetic operator priority and evaluate based on |
| 1339 | 1355 | // operators and operands. |
| 1340 | -func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Stack) error { | |
| 1356 | +func (f *File) parseToken(ctx *calcContext, sheet string, token efp.Token, opdStack, optStack *Stack) error { | |
| 1341 | 1357 | // parse reference: must reference at here |
| 1342 | 1358 | if token.TSubType == efp.TokenSubTypeRange { |
| 1343 | 1359 | refTo := f.getDefinedNameRefTo(token.TValue, sheet) |
| 1344 | 1360 | if refTo != "" { |
| 1345 | 1361 | token.TValue = refTo |
| 1346 | 1362 | } |
| 1347 | - result, err := f.parseReference(sheet, token.TValue) | |
| 1363 | + result, err := f.parseReference(ctx, sheet, token.TValue) | |
| 1348 | 1364 | if err != nil { |
| 1349 | 1365 | return errors.New(formulaErrorNAME) |
| 1350 | 1366 | } |
| @@ -1386,7 +1402,7 @@ func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Sta | ||
| 1386 | 1402 | |
| 1387 | 1403 | // parseReference parse reference and extract values by given reference |
| 1388 | 1404 | // characters and default sheet name. |
| 1389 | -func (f *File) parseReference(sheet, reference string) (arg formulaArg, err error) { | |
| 1405 | +func (f *File) parseReference(ctx *calcContext, sheet, reference string) (arg formulaArg, err error) { | |
| 1390 | 1406 | reference = strings.ReplaceAll(reference, "$", "") |
| 1391 | 1407 | refs, cellRanges, cellRefs := list.New(), list.New(), list.New() |
| 1392 | 1408 | for _, ref := range strings.Split(reference, ":") { |
| @@ -1430,7 +1446,7 @@ func (f *File) parseReference(sheet, reference string) (arg formulaArg, err erro | ||
| 1430 | 1446 | To: cellRef{Sheet: sheet, Col: cr.Col, Row: TotalRows}, |
| 1431 | 1447 | }) |
| 1432 | 1448 | cellRefs.Init() |
| 1433 | - arg, err = f.rangeResolver(cellRefs, cellRanges) | |
| 1449 | + arg, err = f.rangeResolver(ctx, cellRefs, cellRanges) | |
| 1434 | 1450 | return |
| 1435 | 1451 | } |
| 1436 | 1452 | e := refs.Back() |
| @@ -1450,7 +1466,7 @@ func (f *File) parseReference(sheet, reference string) (arg formulaArg, err erro | ||
| 1450 | 1466 | cellRefs.PushBack(e.Value.(cellRef)) |
| 1451 | 1467 | refs.Remove(e) |
| 1452 | 1468 | } |
| 1453 | - arg, err = f.rangeResolver(cellRefs, cellRanges) | |
| 1469 | + arg, err = f.rangeResolver(ctx, cellRefs, cellRanges) | |
| 1454 | 1470 | return |
| 1455 | 1471 | } |
| 1456 | 1472 |
| @@ -1486,10 +1502,27 @@ func prepareValueRef(cr cellRef, valueRange []int) { | ||
| 1486 | 1502 | } |
| 1487 | 1503 | } |
| 1488 | 1504 | |
| 1505 | +// cellResolver calc cell value by given worksheet name, cell reference and context. | |
| 1506 | +func (f *File) cellResolver(ctx *calcContext, sheet, cell string) (string, error) { | |
| 1507 | + var value string | |
| 1508 | + ref := fmt.Sprintf("%s!%s", sheet, cell) | |
| 1509 | + if formula, _ := f.GetCellFormula(sheet, cell); len(formula) != 0 { | |
| 1510 | + ctx.Lock() | |
| 1511 | + if ctx.entry != ref && ctx.iterations[ref] <= f.options.MaxCalcIterations { | |
| 1512 | + ctx.iterations[ref]++ | |
| 1513 | + ctx.Unlock() | |
| 1514 | + value, _ = f.calcCellValue(ctx, sheet, cell) | |
| 1515 | + return value, nil | |
| 1516 | + } | |
| 1517 | + ctx.Unlock() | |
| 1518 | + } | |
| 1519 | + return f.GetCellValue(sheet, cell, Options{RawCellValue: true}) | |
| 1520 | +} | |
| 1521 | + | |
| 1489 | 1522 | // rangeResolver extract value as string from given reference and range list. |
| 1490 | 1523 | // This function will not ignore the empty cell. For example, A1:A2:A2:B3 will |
| 1491 | 1524 | // be reference A1:B3. |
| 1492 | -func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (arg formulaArg, err error) { | |
| 1525 | +func (f *File) rangeResolver(ctx *calcContext, cellRefs, cellRanges *list.List) (arg formulaArg, err error) { | |
| 1493 | 1526 | arg.cellRefs, arg.cellRanges = cellRefs, cellRanges |
| 1494 | 1527 | // value range order: from row, to row, from column, to column |
| 1495 | 1528 | valueRange := []int{0, 0, 0, 0} |
| @@ -1525,7 +1558,7 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (arg formulaArg, e | ||
| 1525 | 1558 | if cell, err = CoordinatesToCellName(col, row); err != nil { |
| 1526 | 1559 | return |
| 1527 | 1560 | } |
| 1528 | - if value, err = f.GetCellValue(sheet, cell, Options{RawCellValue: true}); err != nil { | |
| 1561 | + if value, err = f.cellResolver(ctx, sheet, cell); err != nil { | |
| 1529 | 1562 | return |
| 1530 | 1563 | } |
| 1531 | 1564 | matrixRow = append(matrixRow, formulaArg{ |
| @@ -1544,7 +1577,7 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (arg formulaArg, e | ||
| 1544 | 1577 | if cell, err = CoordinatesToCellName(cr.Col, cr.Row); err != nil { |
| 1545 | 1578 | return |
| 1546 | 1579 | } |
| 1547 | - if arg.String, err = f.GetCellValue(cr.Sheet, cell, Options{RawCellValue: true}); err != nil { | |
| 1580 | + if arg.String, err = f.cellResolver(ctx, cr.Sheet, cell); err != nil { | |
| 1548 | 1581 | return |
| 1549 | 1582 | } |
| 1550 | 1583 | arg.Type = ArgString |
| @@ -15092,7 +15125,7 @@ func (fn *formulaFuncs) INDIRECT(argsList *list.List) formulaArg { | ||
| 15092 | 15125 | } |
| 15093 | 15126 | return newStringFormulaArg(value) |
| 15094 | 15127 | } |
| 15095 | - arg, _ := fn.f.parseReference(fn.sheet, fromRef+":"+toRef) | |
| 15128 | + arg, _ := fn.f.parseReference(fn.ctx, fn.sheet, fromRef+":"+toRef) | |
| 15096 | 15129 | return arg |
| 15097 | 15130 | } |
| 15098 | 15131 |
| @@ -4606,8 +4606,8 @@ func TestCalcCOVAR(t *testing.T) { | ||
| 4606 | 4606 | func TestCalcDatabase(t *testing.T) { |
| 4607 | 4607 | cellData := [][]interface{}{ |
| 4608 | 4608 | {"Tree", "Height", "Age", "Yield", "Profit", "Height"}, |
| 4609 | - {"=Apple", ">1000%", nil, nil, nil, "<16"}, | |
| 4610 | - {"=Pear"}, | |
| 4609 | + {nil, ">1000%", nil, nil, nil, "<16"}, | |
| 4610 | + {}, | |
| 4611 | 4611 | {"Tree", "Height", "Age", "Yield", "Profit"}, |
| 4612 | 4612 | {"Apple", 18, 20, 14, 105}, |
| 4613 | 4613 | {"Pear", 12, 12, 10, 96}, |
| @@ -63,6 +63,9 @@ type charsetTranscoderFn func(charset string, input io.Reader) (rdr io.Reader, e | ||
| 63 | 63 | |
| 64 | 64 | // Options define the options for open and reading spreadsheet. |
| 65 | 65 | // |
| 66 | +// MaxCalcIterations specifies the maximum iterations for iterative | |
| 67 | +// calculation, the default value is 0. | |
| 68 | +// | |
| 66 | 69 | // Password specifies the password of the spreadsheet in plain text. |
| 67 | 70 | // |
| 68 | 71 | // RawCellValue specifies if apply the number format for the cell value or get |
| @@ -78,6 +81,7 @@ type charsetTranscoderFn func(charset string, input io.Reader) (rdr io.Reader, e | ||
| 78 | 81 | // should be less than or equal to UnzipSizeLimit, the default value is |
| 79 | 82 | // 16MB. |
| 80 | 83 | type Options struct { |
| 84 | + MaxCalcIterations uint | |
| 81 | 85 | Password string |
| 82 | 86 | RawCellValue bool |
| 83 | 87 | UnzipSizeLimit int64 |
Fork