Uploaded image for project: 'ZK Spreadsheet'
  1. ZK Spreadsheet
  2. ZSS-261

Math formula SERIESSUM evaluating error

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: 2.5.0, 3.0.0 RC
    • Fix Version/s: 3.0.0 RC
    • Component/s: ZSS Component
    • Labels:

      Description

      problem description

      by automatic test the Math formula SERIESSUM cannot be evaluated to a correct result.

      java.lang.AssertionError: =SERIESSUM(B105,0,2,B106:B109) failed, at row 104 in xsheet: formula-math
      Expected: "0.7071"
           but: was "Error evaluating cell 'formula-math'!B104"
      	at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
      	at org.junit.Assert.assertThat(Assert.java:865)
      	at org.junit.rules.ErrorCollector$1.call(ErrorCollector.java:65)
      	at org.junit.rules.ErrorCollector.checkSucceeds(ErrorCollector.java:78)
      	at org.junit.rules.ErrorCollector.checkThat(ErrorCollector.java:63)
      	at zss.test.FormulaTest.testFormulaByRangesInSheet(FormulaTest.java:158)
      	at zss.test.FormulaTest.testMath(FormulaTest.java:51)
      	...
      
      

      Exception:

      嚴重的: >>org.zkoss.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'first-error'!F20
      >>org.zkoss.poi.ss.formula.eval.NotImplementedException: SERIESSUM
      >>	at org.zkoss.poi.ss.formula.atp.AnalysisToolPak$NotImplemented.evaluate(AnalysisToolPak.java:40)
      >>	at org.zkoss.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:65)
      >>	at org.zkoss.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
      >>	at org.zkoss.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:539)
      >>	at org.zkoss.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:316)
      >>	at org.zkoss.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:255)
      >>	at org.zkoss.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:266)
      >>	at org.zkoss.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluate(XSSFFormulaEvaluator.java:119)
      >>	at org.zkoss.zss.model.sys.impl.BookHelper.evaluate(BookHelper.java:476)
      >>	at org.zkoss.zss.model.sys.impl.BookHelper.reevaluate(BookHelper.java:303)
      >>	at org.zkoss.zss.model.sys.impl.BookHelper.reevaluateAndNotify(BookHelper.java:462)
      >>	at org.zkoss.zss.model.sys.impl.XRangeImpl.reevaluateAndNotify(XRangeImpl.java:421)
      >>	at org.zkoss.zss.model.sys.impl.XRangeImpl.setEditText(XRangeImpl.java:385)
      >>	at org.zkoss.zss.api.impl.RangeImpl.setCellEditText(RangeImpl.java:604)
      >>	at org.zkoss.zss.ui.Spreadsheet.processStopEditing0(Spreadsheet.java:4381)
      >>	at org.zkoss.zss.ui.Spreadsheet.processStopEditing(Spreadsheet.java:4344)
      >>	at org.zkoss.zss.ui.Spreadsheet.access$42(Spreadsheet.java:4342)
      >>	at org.zkoss.zss.ui.Spreadsheet$2.onEvent(Spreadsheet.java:318)
      

      reproducing steps

      run automatic test case FormulaTest of the project zss.test of zkspreadsheet

        Issue Links

          Activity

          Hide
          hawk hawk added a comment -

          Root Cause:

          POI throws NotImplementedException when evaluating those unsupported formulas.

          Solution:

          Add a static formula in ZK UDF finder to shadow those unsupported formulas to avoid throwing the exception. Because user defined formula resolving order is
          1. ZK UDF finder
          2. POI UDF finder
          3. Tolerant UDF finder

          If Spreadsheet has found a formula in a UDF finder, it won't continue to search other UDF finders.

          Show
          hawk hawk added a comment - Root Cause: POI throws NotImplementedException when evaluating those unsupported formulas. Solution: Add a static formula in ZK UDF finder to shadow those unsupported formulas to avoid throwing the exception. Because user defined formula resolving order is 1. ZK UDF finder 2. POI UDF finder 3. Tolerant UDF finder If Spreadsheet has found a formula in a UDF finder, it won't continue to search other UDF finders.

            People

            • Assignee:
              hawk hawk
              Reporter:
              hawk hawk
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: