https://bz.apache.org/bugzilla/show_bug.cgi?id=68258
Bug ID: 68258
Summary: Nested functions in IF formulas don't get evaluated
Product: POI
Version: unspecified
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: spy...@neotalogic.com
Target Milestone: ---
Created attachment 39419
--> https://bz.apache.org/bugzilla/attachment.cgi?id=39419&action=edit
Spreadsheet with nested IF formulas
I'm facing a problem that seems similar to bugs 55747 and 55324. In particular,
I'm trying to set values for a few cells in the attached Spreadsheet and then
evaluate all formulas in the worksheet. All formula results come back as
"#VALUE!". When I set the "setForceFormulaRecalculation" flag to TRUE, upon
opening the saved Spreadsheet, Excel evaluates everything properly.
Relevant information:
Scala version 2.13.8
Java runtime: openjdk version "1.8.0_362" 64 bit
org.apache.poi.poi version 5.2.5 (also reproduced in 5.2.4 and 5.2.2)
org.apache.poi.poi-ooxml version 5.2.5 (also reproduced in 5.2.4 and 5.2.2)
I'm trying to set values for the following cells:
Loan amount - E3
Annual interest rate - E4
Loan period in years - E5
Number of payments per year - E6
Start date of loan - E7
Optional extra payments - E9
And I want to read back the results after computing all formulas for the
following cells:
Scheduled payment - I3
Scheduled number of payments - I4
Actual number of payments - I5
Total early payments - I6
Total interest - I7
LENDER NAME - "LenderName"
Note that reading the "LenderName" cell value works just fine.
What I've tried in terms of evaluation:
val wb = XSSFWorkbookFactory.createWorkbook(pkg)
Method 1:
1. Set all desired cell values in wb
2. Evaluate all formulas after all values have been set:
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb)
Method 2:
1. Create a single evaluator for the wb: val evaluator =
wb.getCreationHelper.createFormulaEvaluator
2. Set all desired cell values in wb
3. Evaluate all formulas after all values have been set: evaluator.evaluateAll
Method 3:
1. Create a single evaluator for the wb: val evaluator =
wb.getCreationHelper.createFormulaEvaluator
2. Set all desired cell values in wb
3. For each cell after setting its value, call notifyUpdateCell
4. Evaluate all formulas after all values have been set: evaluator.evaluateAll
Method 4:
1. Create a single evaluator for the wb: val evaluator =
wb.getCreationHelper.createFormulaEvaluator
2. Set all desired cell values in wb
3. For each cell after setting its value, call notifySetFormula
4. Evaluate all formulas after all values have been set: evaluator.evaluateAll
Method 5:
1. Set all desired cell values in wb
2. For each cell after setting its value, call notifyUpdateCell
3. Evaluate all formulas after all values have been set:
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb)
Method 6:
1. Set all desired cell values in wb
2. For each cell after setting its value, call notifySetFormula
3. Evaluate all formulas after all values have been set:
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb)
Note that I'm always setting wb.setForceFormulaRecalculation(true) before
evaluating all formulas (in case it matters)
I think I've tried all possible combinations this might work as indicated in
the documentation. I've even tried using evaluator.evaluateInCell as an
extension of methods 3, 4, 5 and 6 whenever setting the value for a cell, just
in case (I've tried both creating a new evaluator for every cell, or re-using
the same one), but nothing worked.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org
