Created attachment 39419 [details] 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.
wb.setForceFormulaRecalculation(true) makes Excel evaluate the formulas when it opens the workbook. To be clear, noone is working on improving POI formula evaluation. I would recommend forgetting that POI has FormulaEvaluator support. Just use `wb.setForceFormulaRecalculation(true)` and `cell.setCellFormula(...)`. Write the results to an xslx file and open it in Excel. Excel will evaluate the formulas. You are welcome to submit patches to POI but it is unlikely that someone will try to enhance POI for you.
(In reply to PJ Fanning from comment #1) > wb.setForceFormulaRecalculation(true) makes Excel evaluate the formulas when > it opens the workbook. > > To be clear, noone is working on improving POI formula evaluation. > > I would recommend forgetting that POI has FormulaEvaluator support. Just use > `wb.setForceFormulaRecalculation(true)` and `cell.setCellFormula(...)`. > Write the results to an xslx file and open it in Excel. Excel will evaluate > the formulas. > > You are welcome to submit patches to POI but it is unlikely that someone > will try to enhance POI for you. Thanks for the very prompt response on this! We're already using `wb.setForceFormulaRecalculation(true)` for one class of usecases in our platform, where we want to provide users with the spreadsheet after values have been mapped, so that they can download and continue working on it. However, there's another class of usecases where users can use our platform to provide values to a Spreadsheet, have an Excel engine evaluate it for them, and return back only values for the cells/columns/whatever they are interested at. We used to do this with an older version of Sharepoint and Excel Services, but that's not supported/available by Microsoft anymore, hence our turn to POI. In any case, your idea of opening the .xslx file with Excel so that formulas can be computed before proceeding could be of value - we can try to add that extra step in the evaluation process so that all cells in the resulting .xslx file can have their "final" (post formula evaluation) values. Do you happen to know whether OpenOffice has good enough support for formulas? Thanks again for your prompt response on this!
POI and OpenOffice are not affiliated with each other. You will need to try out OpenOffice yourself.