Hello,
Thanks for the reply!
I understand.
I tried different versions and this seems to work in earlier versions, for
example in POI 4.0.0 and 4.0.1 I get this result with the same program and
Excel file:
The value from Excel is [false] and POI 4.0.1 returns the type as [BOOLEAN]
> and value: org.apache.poi.ss.usermodel.CellValue [FALSE]
But in any version post 4.0.1 I get this result:
> The value from Excel is [false] and POI 4.1.0 returns the type as [ERROR]
> and value: org.apache.poi.ss.usermodel.CellValue [#VALUE!]
Where would I look to try and fix this?
Thanks for your help
On Thu, 18 Nov 2021 at 12:13, PJ Fanning <fannin...@yahoo.com.invalid>
wrote:
> 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 <
> emil.elmars...@molnify.com> 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: user-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
