And now I also found the root cause:
The bug is in Softmaker's Planmaker which saves the IFERROR function as "_XLFN.IFERROR" even if the chosen output format supports the function.
Still it would be nice if POI would be a little more lenient here!
________________________________________
From: Stephen Friedrich [***@mgm-tp.com]
Sent: Tuesday, October 16, 2018 10:08 AM
To: POI Users List
Subject: RE: IFERROR not implemented in POI 4.0?
Ok, I finally tracked it down in the source code.
See class org.apache.poi.ss.formula.atp.AnalysisToolPak:
public FreeRefFunction findFunction(String name) {
// functions that are available in Excel 2007+ have a prefix _xlfn.
// if you save such a .xlsx workbook as .xls
final String prefix = "_xlfn.";
// case-sensitive
if(name.startsWith(prefix)) name = name.substring(prefix.length());
// FIXME: inconsistent case-sensitivity
return _functionsByName.get(name.toUpperCase(Locale.ROOT));
}
If I change the check for the prefix to be case insensitive, then all is fine:
if(name.toLowerCase().startsWith(prefix)) name = name.substring(prefix.length());
I still don't understand how I got the "_XLFN." prefix in the first place (I am using the newer XLSX format)
or why it is uppercase in my case or why the code explicitly says "// case-sensitive" for the prefix check.
________________________________________
From: Stephen Friedrich [***@mgm-tp.com]
Sent: Monday, October 15, 2018 6:51 PM
To: POI Users List
Subject: Re: IFERROR not implemented in POI 4.0?
Thanks a lot for the answers.
Yes I am sure about the version. I made a new test project and it works with a new test excel but still fails for our real excel.
Problem is the real excel is both very complex and has lots of confidential client data.
I will try to track the bug down but it is not easy.
Outlook for Android<https://aka.ms/ghei36> herunterladen
On Fri, Oct 12, 2018 at 8:10 PM +0200, "Yegor Kozlov" <***@dinom.ru<mailto:***@dinom.ru>> wrote:
Are you sure you are using POI 4.0? IFERROR is implemented and we have
passing unit test for it.
Yegor
Post by Stephen FriedrichI am using POI 4.0 and if I understood it correctly, then IFERROR function
should have been implemented long ago, right?
Then why do I get this exception?
_XLFN.IFERROR
at
org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:56)
at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:146)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:534)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:275)
---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org