You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Emil Elmarsson <em...@molnify.com> on 2021/11/18 09:42:01 UTC
Error with multiple return types in POI 5.1.0
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
Re: Error with multiple return types in POI 5.1.0
Posted by PJ Fanning <fa...@yahoo.com.INVALID>.
Could you log an issue in https://bz.apache.org/bugzilla/ ? If you include a reproducible test case then someone might look at it. If you don't include good info on how to reproduce the issue, the issue will likely be ignored.
On Thursday 18 November 2021, 13:04:38 GMT+1, Emil Elmarsson <em...@molnify.com> wrote:
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 <fa...@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.elmarsson@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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Error with multiple return types in POI 5.1.0
Posted by Emil Elmarsson <em...@molnify.com>.
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 <fa...@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.elmarsson@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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
Re: Error with multiple return types in POI 5.1.0
Posted by PJ Fanning <fa...@yahoo.com.INVALID>.
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 <em...@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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org