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 [[email protected]] 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 [[email protected]] 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" <[email protected]<mailto:[email protected]>> wrote: Are you sure you are using POI 4.0? IFERROR is implemented and we have passing unit test for it. Yegor пт, 12 окт. 2018 г., 17:11 Stephen Friedrich : > I 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? > > Caused by: org.apache.poi.ss.formula.eval.NotImplementedFunctionException: > _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: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
