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);
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
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
Looks like a typo in the link on the download page, sorry :( Can you try now?
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
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