Bug 65467 - Excel function IFNA() is not defined
Summary: Excel function IFNA() is not defined
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 5.0.0-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-07-28 02:03 UTC by Ross Patterson
Modified: 2021-08-02 07:37 UTC (History)
0 users



Attachments
patch_2021-07-29.tar.gz (10.35 KB, patch)
2021-07-29 16:33 UTC, Ross Patterson
Details | Diff
patch_site_2021-07-29.tar.gz (8.85 KB, patch)
2021-07-29 21:19 UTC, Ross Patterson
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Ross Patterson 2021-07-28 02:03:09 UTC
The Excel IFNA() function isn't defined in POI.  I needed it, so I whipped up an implementation, but I'm having problems with testing it.  I've got a unit test (TestIfna.java), built similarly to lots of other function tests, and it passes.  But my attempt to test using IFNA() in a spreadsheet (TestIfnaFromSpreadsheet.java) acts as if the function isn't defined:

   Testcase: org.apache.poi.ss.formula.functions.TestIfnaFromSpreadsheet
   Test: processFunctionRow(String, String, HSSFSheet, int, HSSFFormulaEvaluator, int)[1] took 140 milli sec(s) FAILED: In IfnaTestCaseData.xls Ifna!B5 {=_xlfn.IFNA(E5,F5)} 'An N/A value' - exception in evaluator.evaluate(evalCell): 
   org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Ifna!B5 with inner exception:       
   org.apache.poi.ss.formula.eval.NotImplementedFunctionException: _xlfn.IFNA
   org.apache.poi.ss.formula.eval.NotImplementedFunctionException: _xlfn.IFNA
   ...

This is a surprise, because I've updated FunctionEval.java and functionMetadata.txt to define it.  I followed the "Developing Formula Evaluation" instructions at https://poi.apache.org/components/spreadsheet/eval-devguide.html, but I've clearly missed a step.  Where should I be looking?  Is there a decent example of a patch that adds a new function?
Comment 1 Nick Burch 2021-07-28 08:24:11 UTC
Functions prefixed with _xlfn are newer functions since the file format was fixed, placed into an extension space, see
https://support.microsoft.com/en-us/office/issue-an-xlfn-prefix-is-displayed-in-front-of-a-formula-882f1ef7-68fb-4fcd-8d54-9fbb77fd5025

You need to list the implementation of these functions in AnalysisToolPak, as that handles the extension namespace

See http://svn.apache.org/viewvc?view=revision&revision=1887656 for a similar text function getting implemented

If you have a bit of time to write up some instructions for the eval devguide once you get it working, that'd be great!
Comment 2 Ross Patterson 2021-07-28 17:10:26 UTC
Ah!  I thought the AnalysisToolPak support was for the actual ATP-supplied functions (https://support.microsoft.com/en-us/office/use-the-analysis-toolpak-to-perform-complex-data-analysis-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6), not all post-2007 functions.  That was the hint I needed.

I'll take a crack at improving the devguide, while I'm still new enough to see the problems :-)
Comment 3 Ross Patterson 2021-07-29 16:32:04 UTC
A patch to implement IFNA() and add unit tests for it is attached as patch_2021-07-29.tar.gz.  Comments and criticism are very welcome.
Comment 4 Ross Patterson 2021-07-29 16:33:05 UTC
Created attachment 37974 [details]
patch_2021-07-29.tar.gz
Comment 5 PJ Fanning 2021-07-29 16:57:25 UTC
thanks - merged with r1891876
Comment 6 Ross Patterson 2021-07-29 21:19:45 UTC
Created attachment 37975 [details]
patch_site_2021-07-29.tar.gz

patch_site_2021-07-29.tar.gz contains an update for the eval-devguide.xml.  I made a lot of small stylistic changes, but the real gist of the changes are what was previously the "Walkthrough of an "evaluate()" implementation." section.  I also updated the list of supported functions in Appendix A.  I'm very open to criticism, as I'm a programmer, not a writer.
Comment 7 Dominik Stadler 2021-08-02 07:37:33 UTC
Applied the changed eval-guide via #1891958, thanks for the contributions here!