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
