IFERROR not implemented in POI 4.0?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

IFERROR not implemented in POI 4.0?

Stephen Friedrich-2
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)

Reply | Threaded
Open this post in threaded view
|

Re: IFERROR not implemented in POI 4.0?

pj.fanning
possibly https://bz.apache.org/bugzilla/show_bug.cgi?id=56499



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: IFERROR not implemented in POI 4.0?

Yegor Kozlov-4
In reply to this post by Stephen Friedrich-2
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 <[hidden email]>:

> 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)
>
>
Reply | Threaded
Open this post in threaded view
|

Re: IFERROR not implemented in POI 4.0?

Stephen Friedrich-2
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" <[hidden email]<mailto:[hidden email]>> 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)
>
>

Reply | Threaded
Open this post in threaded view
|

RE: IFERROR not implemented in POI 4.0?

Stephen Friedrich-2
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 [[hidden email]]
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" <[hidden email]<mailto:[hidden email]>> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: IFERROR not implemented in POI 4.0?

Stephen Friedrich-2
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 [[hidden email]]
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 [[hidden email]]
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" <[hidden email]<mailto:[hidden email]>> 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: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: IFERROR not implemented in POI 4.0?

Yegor Kozlov-4
Can you please create a bug and attach a minimal excel file  and test case
to reproduce the problem?

Yegor

вт, 16 окт. 2018 г., 14:44 Stephen Friedrich <[hidden email]>:

> 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 [[hidden email]]
> 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 [[hidden email]]
> 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" <
> [hidden email]<mailto:[hidden email]>> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>