Hi Emil, POI only has partial support for formulas. There are no guarantees that all formulas will be implemented or those that are implemented will work for all scenarios. One thing you could do is set `wb.setForceFormulaRecalculation(true);` so that Excel will recalculate all the formulas when you load the workbook into Excel.
If you would like to try to fix the issue in POI, code submissions are very welcome. As far as I am aware, none of the existing POI volunteers are working on improving formula support. Regards, PJ On Thursday 18 November 2021, 10:42:29 GMT+1, Emil Elmarsson <[email protected]> wrote: Hello, I am new here. I hope I'm not breaking any rules. I recently upgraded to POI 5.1.0. Great stuff! But unfortunately, I am getting some errors with functions with multiple return types. I have an IF formula that either returns a result from an INDEX formula or a boolean value (formula shown below). When debugging in IntelliJ I can see that it is marked as an array formula. *My formula:* > =IF(AND(D5;D6>0);IFERROR(INDEX(K4:K13;D6;1);FALSE);FALSE) > As you can see, I'm using a check to see that the row index (D6) is not zero. D5 is FALSE and D6 is 1 initially, so the formula should return FALSE, but instead I get a #VALUE. It always returns #VALUE even if I change the row index D6 to 0, 1, 2, etc. The range K4:K13 that I'm indexing is a simple list of numbers, 1, 2, 3, ..., 10. Below is a simple example, I have tried to reduce the code as much as possible, and reduced the Excel file to the bare minimum. Is there any way I could share this with you? Maybe as a zip file? Or as a git repo? > import org.apache.poi.ss.usermodel.*; > > import java.io.File; > > public class StandAlonePoiMultivaluedReturnExample { > > private static final String exampleFilePath = >"src/main/resources/test-index-iferror.xlsx"; > > public static void main(String[] args) throws Exception { > System.out.println("Working Directory = " + >System.getProperty("user.dir")); > File file = new File(exampleFilePath); > Workbook wb = WorkbookFactory.create(file); > FormulaEvaluator evaluator = >wb.getCreationHelper().createFormulaEvaluator(); > Cell formulaCellThatReturnsDifferentValues = >wb.getSheetAt(0).getRow(8).getCell(3); > boolean valueBeforeEvaluate = >formulaCellThatReturnsDifferentValues.getBooleanCellValue(); > CellType type = >evaluator.evaluateFormulaCell(formulaCellThatReturnsDifferentValues); > CellValue value = >evaluator.evaluate(formulaCellThatReturnsDifferentValues); > System.out.println("The value from Excel is ["+valueBeforeEvaluate + >"] but POI 5.1.0 returns the type as [" + type + "] and value: " + value); > } > } > > This program gives the output: > The value from Excel is [false] but POI 5.1.0 returns the type as [ERROR] > and value: org.apache.poi.ss.usermodel.CellValue [#VALUE!] Any advice? Thanks --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
