poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stephen Friedrich <Stephen.Friedr...@mgm-tp.com>
Subject RE: IFERROR not implemented in POI 4.0?
Date Tue, 16 Oct 2018 11:44:29 GMT
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 [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 [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" <yegor.kozlov@dinom.ru<mailto:yegor.kozlov@dinom.ru>>

Are you sure you are using POI 4.0? IFERROR is implemented and we have
passing unit test for it.


пт, 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:
>     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-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

View raw message