Bug 63845 - Forumla not evaluated in 4.1.0
Summary: Forumla not evaluated in 4.1.0
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.1.0-FINAL
Hardware: PC All
: P2 regression with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks: 63339
  Show dependency tree
 
Reported: 2019-10-14 19:04 UTC by Joern Muehlencord
Modified: 2020-03-29 08:19 UTC (History)
1 user (show)



Attachments
file created using 4.1.0, cell formulas are not calculated when opening the file (7.02 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-10-14 19:04 UTC, Joern Muehlencord
Details
file created using 4.0.1 (7.02 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-10-14 19:05 UTC, Joern Muehlencord
Details
source file (8.97 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-10-14 19:06 UTC, Joern Muehlencord
Details
Test case (1.68 KB, text/x-java)
2019-12-01 13:38 UTC, Joern Muehlencord
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Joern Muehlencord 2019-10-14 19:04:43 UTC
Created attachment 36826 [details]
file created using 4.1.0, cell formulas are not calculated when opening the file

Cell formulas are not evaluated when created with 4.1.0

E.g. cell.setCellFormula("SUM(A3:A5)"); opens as "0" when opening the excel file. Same source code compiled with 4.0.1 works and shows correct value when opening.

If editing the formula (F2) sheet and Enter w/o any change, the formula will be updated to the correct value. Trying to force call formula calculation from menu does not help to update the cell value. 

Same error / same behavior occurs when copying existing cells to new cells. 

In the attached example cell A11 is the cell.setCellFormlua mentioned above. Column D is copied from column A using the FormulaParser for the formula cells. As mentioned, the same code works for 4.0.1 but is broken in 4.1.0
Comment 1 Joern Muehlencord 2019-10-14 19:05:44 UTC
Created attachment 36827 [details]
file created using 4.0.1
Comment 2 Joern Muehlencord 2019-10-14 19:06:36 UTC
Created attachment 36828 [details]
source file
Comment 3 Dominik Stadler 2019-11-16 16:58:23 UTC
Can you also share the source code that you use to produce the resulting file out of the source file?
Comment 4 Joern Muehlencord 2019-12-01 13:37:44 UTC
As written, it is basically just 

Cell cell = row.createCell (0, CellType.FORMULA); 
cell.setCellFormula("SUM(A3:A5)");

See attached test test class (I skipped the copy column part just to keep the test class simple).
Comment 5 Joern Muehlencord 2019-12-01 13:38:33 UTC
Created attachment 36907 [details]
Test case
Comment 6 PJ Fanning 2019-12-01 16:22:13 UTC
Comment on attachment 36826 [details]
file created using 4.1.0, cell formulas are not calculated when opening the file

there does appear to a diff in behaviour between poi-4.0.0 and poi-4.1.1.

in a similar test, poi-4.1.1 wrote `<c r="B3" t="n"><f>SUM(A1:A3)</f><v>0.0</v></c>` but poi-4.0.0 wrote `<c r="B3"><f>SUM(A1:A3)</f></c>`.
Comment 7 PJ Fanning 2019-12-01 16:23:23 UTC
in my example, the value should be as the 3 values in the sum are 1,2,3.
Comment 8 PJ Fanning 2019-12-01 16:30:45 UTC
As a workaround, could you add this before saving the workbook (wb)?

  XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(xb);
  evaluator.evaluateAll();
Comment 9 Joern Muehlencord 2019-12-02 07:44:08 UTC
the workaround helps to get the forumla calculated in both 4.1.0 and 4.1.1.
Comment 10 Dominik Stadler 2019-12-15 12:50:05 UTC
Using Git bisect points at the following commit causing this:

r1852246

Date:   Sat Jan 26 19:41:25 2019 +0000

    made call Cell.setCellType(CellType.FORMULA) illegall. Deprecated Cell.setCellType(). Purged all redundant calls from project.
Comment 11 Dominik Stadler 2020-03-29 08:19:59 UTC
I applied a fix via r1875837 which hopefully fixes this, unfortunately it is a quite complicated how formula values are handled across the different types of spreadsheets, please verify this locally to ensure it is fixed for you before we roll this into a release.