Bug 56859 - IF Function evaluation
Summary: IF Function evaluation
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.10-FINAL
Hardware: PC Windows XP
: P1 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-08-15 09:04 UTC by Tina Parro
Modified: 2016-10-09 11:27 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Tina Parro 2014-08-15 09:04:40 UTC
I have a cell with an IF statement. The condition inside the IF statement uses 2 different unsupported functions.

example: 

IF($J$5=1,SYD($B16,$D16,$C16,F$12),SLN($B16,$D16,$C16))

When I evaluated the cell, only 1 condition was evaluated by POI (which is the TRUE part of the IF statement). In the example, only the SYD function was detected.

Is there a way that POI will evaluate both conditions of the IF statement so that both unsupported functions will be detected?

I am using XLSX file so I'm using XSSFWorkbook.

This is my code in evaluating the cell:

XSSFFormulaEvaluator fe = new XSSFFormulaEvaluator(wb);
fe.evaluateFormulaCell(cell);
Comment 1 Nick Burch 2014-08-15 09:19:13 UTC
Can you try with 3.11 beta 1? There were some formula parsing changes in that, would be good to confirm if they've affected it or not
Comment 2 Tina Parro 2014-08-18 02:22:39 UTC
I tried downloading the 3.11 beta 1 version but the download page is returning an error

Not Found

The requested URL /pub/apache/poi/dev/bin/poi-bin-3.11-beta1-20140808.zip was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

I also tried the other mirror servers but it has the same error.

(In reply to Nick Burch from comment #1)
> Can you try with 3.11 beta 1? There were some formula parsing changes in
> that, would be good to confirm if they've affected it or not
Comment 3 Nick Burch 2014-08-18 07:54:00 UTC
Looks like a typo in the link on the download page, sorry :(

Can you try now?
Comment 4 Tina Parro 2014-08-19 03:24:12 UTC
I already installed 3.11 beta 1 but the result is still the same. I still got NotImplementedFunctionException but it only returned SYD as the cause of the error.

This is the returned error:

Exception: NotImplementedFunctionException
Cause: org.apache.poi.ss.formula.eval.NotImplementedFunctionException: SYD
Detail cause: Error evaluating cell Schedule!H9
Comment 5 Javen O'Neal 2016-10-09 11:27:51 UTC
If you only ask POI to parse a formula into Ptg tokens, it can create tokens for the 2 user-defined functions.
orm
You could do a FormulaParser.parse(String formula, ...), search for XNamePtgs (externally defined names), then try to evaluate the formula and if an evaluation error occurs, attach the names of the UDFs to that error message.

You could also write a Java function that is equivalent to the VBA code for user-defined functions so that POI could successfully evaluate a formula.

Without either of these, POI formula evaluation will be like any other code: an exception will be thrown on the first error. The behavior after the first error occurs is likely to be dependent on the application:
* exit immediately with a stack trace and a likely cause for the error
* propagate #ERROR, #NAN, #NAME, etc tokens throughout the formula evaluation process (this is what Excel does)
* continue with evaluation and accumulate subsequent errors (cascaded errors could make this list quite long, not to mention waste execution time or introduce infinite loops)

There has been some discussion on the mailing list and bugzilla on what POI's behavior should be. Mainly, how closely should POI try to match Excel's behavior versus provide the least surprising API for programmers (simple example: POI uses 0-based indexing).

That said, feel free to search through the POI user and dev mailing lists [1] and reopen the discussion. Hopefully one of the two methods, either parse before evaluating or registering Java UDFs, will meet your needs without any changes to POI.

[1] https://poi.apache.org/mailinglists.html