[Bug 61468] New: Regression: evaluateInCell results in incorrect value for some formulas

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 61468] New: Regression: evaluateInCell results in incorrect value for some formulas

Bugzilla from bugzilla@apache.org
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: [hidden email]
          Reporter: [hidden email]
  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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61468] Regression: evaluateInCell results in incorrect value for some formulas

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61468

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--- Comment #1 from [hidden email] ---
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61468] Regression: evaluateInCell results in incorrect value for some formulas

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61468

Greg Woolsey <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Hardware|Macintosh                   |All

--- Comment #2 from Greg Woolsey <[hidden email]> ---
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [Bug 61468] Regression: evaluateInCell results in incorrect value for some formulas

Greg Woolsey
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 <[hidden email]> wrote:

> https://bz.apache.org/bugzilla/show_bug.cgi?id=61468
>
> Greg Woolsey <[hidden email]> changed:
>
>            What    |Removed                     |Added
>
> ----------------------------------------------------------------------------
>            Hardware|Macintosh                   |All
>
> --- Comment #2 from Greg Woolsey <[hidden email]> ---
> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

[Bug 61468] Regression: evaluateInCell results in incorrect value for some formulas

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61468

Greg Woolsey <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|---                         |FIXED
             Status|NEW                         |RESOLVED

--- Comment #3 from Greg Woolsey <[hidden email]> ---
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: [hidden email]
For additional commands, e-mail: [hidden email]