You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2017/08/29 12:35:58 UTC
[Bug 61468] New: Regression: evaluateInCell results in incorrect
value for some formulas
https://bz.apache.org/bugzilla/show_bug.cgi?id=61468
Bug ID: 61468
Summary: Regression: evaluateInCell results in incorrect value
for some formulas
Product: POI
Version: 3.17-dev
Hardware: Macintosh
Status: NEW
Severity: regression
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: viktor@vaadin.com
Target Milestone: ---
Created attachment 35266
--> https://bz.apache.org/bugzilla/attachment.cgi?id=35266&action=edit
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61468] Regression: evaluateInCell results in incorrect value
for some formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61468
Greg Woolsey <gw...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |FIXED
Status|NEW |RESOLVED
--- Comment #3 from Greg Woolsey <gw...@apache.org> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: [Bug 61468] Regression: evaluateInCell results in incorrect value
for some formulas
Posted by Greg Woolsey <gr...@gmail.com>.
FYI, if this does turn out to be a regression bug I'd like the fix in 3.17,
or my desired Vaadin patch timeline will be delayed by a full release cycle
:( Looking into it now, as well as what unit test(s) are needed to catch
it.
On Tue, Aug 29, 2017 at 8:57 AM <bu...@apache.org> wrote:
> https://bz.apache.org/bugzilla/show_bug.cgi?id=61468
>
> Greg Woolsey <gw...@apache.org> changed:
>
> What |Removed |Added
>
> ----------------------------------------------------------------------------
> Hardware|Macintosh |All
>
> --- Comment #2 from Greg Woolsey <gw...@apache.org> ---
> 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.
>
> --
> You are receiving this mail because:
> You are the assignee for the bug.
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>
[Bug 61468] Regression: evaluateInCell results in incorrect value
for some formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61468
Greg Woolsey <gw...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Hardware|Macintosh |All
--- Comment #2 from Greg Woolsey <gw...@apache.org> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61468] Regression: evaluateInCell results in incorrect value
for some formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61468
viktor@vaadin.com changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--- Comment #1 from viktor@vaadin.com ---
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
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org