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
Created attachment 36827 [details] file created using 4.0.1
Created attachment 36828 [details] source file
Can you also share the source code that you use to produce the resulting file out of the source file?
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).
Created attachment 36907 [details] Test case
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>`.
in my example, the value should be as the 3 values in the sum are 1,2,3.
As a workaround, could you add this before saving the workbook (wb)? XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(xb); evaluator.evaluateAll();
the workaround helps to get the forumla calculated in both 4.1.0 and 4.1.1.
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.
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.
*** Bug 63339 has been marked as a duplicate of this bug. ***
*** Bug 64768 has been marked as a duplicate of this bug. ***