Bug 61468 - Regression: evaluateInCell results in incorrect value for some formulas
Summary: Regression: evaluateInCell results in incorrect value for some formulas
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.17-dev
Hardware: All All
: P2 regression (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-08-29 12:35 UTC by viktor
Modified: 2017-08-29 17:16 UTC (History)
0 users



Attachments
simple monthly budget (18.26 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-08-29 12:35 UTC, viktor
Details

Note You need to log in before you can comment on or make changes to this bug.
Description viktor 2017-08-29 12:35:58 UTC
Created attachment 35266 [details]
simple monthly budget

With the attached spreadsheet POI 3.17-beta1 incorrectly evaluates the formula in the cell  E5 to "3500.0". It should be "3750.0" (that's what Excel shows). 

The version 3.15 works correctly. The issue appears in 3.16 and 3.17-beta1.
Comment 1 viktor 2017-08-29 12:44:47 UTC
UPD: the cell is E9 (not E5 as stated above). The formula which is incorrectly evaluated is `=TotalMonthlyIncome`.

The following code snippet produces different results in 3.15 and 3.17:
InputStream input = Main.class.getClassLoader().getResourceAsStream("simple-monthly-budget.xlsx");
Workbook workbook = WorkbookFactory.create(input);
Cell cell = workbook.getSheetAt(0).getRow(8).getCell(4);
System.out.println("E9 numeric value (before evaluating formula): " + cell.getNumericCellValue());
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
formulaEvaluator.evaluateInCell(cell);
System.out.println("E9 numeric value (after evaluating formula): " + cell.getNumericCellValue());

In 3.15:
E9 numeric value (before evaluating formula): 3750.0
E9 numeric value (after evaluating formula): 3750.0

In 3.17:
E9 numeric value (before evaluating formula): 3750.0
E9 numeric value (after evaluating formula): 3500.0
Comment 2 Greg Woolsey 2017-08-29 15:57:15 UTC
Thank you for this report and test case.  I suspect I broke this while adding some functionality I needed for my Vaadin Spreadsheet app, so I'll look into it.
Comment 3 Greg Woolsey 2017-08-29 17:16:16 UTC
Fixed in r1806623.

The table syntax evaluation is incorrectly assuming a table footer row now, when there isn't one, and the last row's value is left out of the SUM() calculation.

It turns out this is because 

org.apache.poi.ss.formula.FormulaParser.ParseStructuredReference(String)

makes the incorrect API assumption noted in the JavaDocs recently added for XSSFTable.isHasTotalsRow():

Note: This is misleading. The Spec indicates this is true if the totals row has _ever_ been shown, not whether or not it is currently displayed. Use getTotalsRowCount() > 0 to decide whether or not the totals row is visible.

I have no idea why MS thought this was helpful or desirable behavior, but this is experimentally exactly how Excel uses this field, per the OOXML spec.

All POI callers of isHasTotalsRow() have been updated to use getTotalsRowCount() instead.  this failing file has been added as a unit test, and now passes.