as an aside, i ran into a similar issue a couple years ago. the problem then was trying to force an entire spreadsheet to update, which failed because evaluation was tripping over functions that were not implemented. the workaround i used was to instead, do targeted re-evaluation of specific cells that i knew were harboring functions defined in the POI library.
this may provide a way through, IF you are tripping over a similar issue Stanton Fisque principal technologist latticeware.com portland, oregon > On Nov 28, 2023, at 09:15 AM, bugzi...@apache.org wrote: > > 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 > --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org For additional commands, e-mail: dev-h...@poi.apache.org
