Bug 60924 - New Excel 2016 Statements Not Supported
Summary: New Excel 2016 Statements Not Supported
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 4.1.1-FINAL
Hardware: PC All
: P2 enhancement with 10 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on: 64633
Blocks:
  Show dependency tree
 
Reported: 2017-03-27 17:24 UTC by Adrian B.
Modified: 2021-12-02 19:33 UTC (History)
3 users (show)



Attachments
Example of 2016 SWITCH Statement (9.58 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-03-27 22:32 UTC, Adrian B.
Details
Example of 2016 IFS Statement (8.68 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-03-27 22:32 UTC, Adrian B.
Details
Example of 2016 MAXIFS Statement (8.67 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-03-27 22:33 UTC, Adrian B.
Details
Example of 2016 MINIFS Statement (8.67 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-03-27 22:33 UTC, Adrian B.
Details
Example of 2016 TEXTJOIN Statement (8.88 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-03-27 22:34 UTC, Adrian B.
Details
Example of 2016 CONCAT Statement (8.75 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-03-27 22:34 UTC, Adrian B.
Details
Patch for IFS and SWITCH (17.06 KB, patch)
2018-06-05 19:45 UTC, Pieter Degraeuwe
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Adrian B. 2017-03-27 17:24:26 UTC
Version: First seen with 3.10-FINAL, however skimming the code it appears to still be a problem in 3.15-FINAL.

Excel 2016 added a new SWITCH statement, and likely others.  Running evaluateAll() on the workbook results in the following stack trace.

org.apache.poi.ss.formula.FormulaParseException: Name '_xlfn.SWITCH' is completely unknown in the current workbook
	at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:920)
	at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:558)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:429)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467)
	at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
	at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:148)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:286)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:151)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:324)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:315)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:252)

For anyone seeing this bug and looking for a workaround, follow the instructions at http://poi.apache.org/spreadsheet/user-defined-functions.html to implement a custom function with the name '_xlfn.SWITCH'.
Comment 1 Javen O'Neal 2017-03-27 18:24:01 UTC
Official documentation for SWITCH function: https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e
Comment 2 Javen O'Neal 2017-03-27 18:27:35 UTC
I don't have Excel 2016 to be able to create a workbook to test this. Could you upload a valid, blank Excel 2016 file with a switch function and populate any cells necessary to verify the function evaluation is correct?
Comment 4 Adrian B. 2017-03-27 22:32:58 UTC
Created attachment 34879 [details]
Example of 2016 IFS Statement
Comment 5 Adrian B. 2017-03-27 22:33:20 UTC
Created attachment 34880 [details]
Example of 2016 MAXIFS Statement
Comment 6 Adrian B. 2017-03-27 22:33:40 UTC
Created attachment 34881 [details]
Example of 2016 MINIFS Statement
Comment 7 Adrian B. 2017-03-27 22:34:05 UTC
Created attachment 34882 [details]
Example of 2016 TEXTJOIN Statement
Comment 8 Adrian B. 2017-03-27 22:34:24 UTC
Created attachment 34883 [details]
Example of 2016 CONCAT Statement
Comment 9 Pieter Degraeuwe 2018-06-01 09:14:20 UTC
Don't know why This bug is still on 'NEEDINFO'.

I did already write an IFS and SWITCH function (and their Tests)
Should I add the patch here?
Comment 10 Nick Burch 2018-06-01 10:04:54 UTC
(In reply to Pieter Degraeuwe from comment #9)
> Don't know why This bug is still on 'NEEDINFO'.
> 
> I did already write an IFS and SWITCH function (and their Tests)
> Should I add the patch here?

Please either attach the patch and unit tests here, or open a pull request against our Github mirror, whatever is easiest for you!
Comment 11 Pieter Degraeuwe 2018-06-05 19:45:01 UTC
Created attachment 35955 [details]
Patch for IFS and SWITCH

Patch contains (I tink) working implementations for IFS and SWITCH

please be gentle: I'm a newbie here at poi ;-) 

So, it could be that the implementations are not that great. 
Comments are welcome!
Comment 12 leonyip 2019-12-10 06:06:35 UTC
I've loaded with version 4.1.1 library. When I evaluateCell contain MINIFS, the NotImplemented error occur.
Comment 13 SomersetDevLad 2020-03-27 10:16:24 UTC
Apologise as I'm new to this, so this may be the wrong bug but there is also the new XLOOKUP function added in Excel 2019.
Comment 14 PJ Fanning 2021-07-28 14:10:20 UTC
thanks Pieter - added patch using r1891856

will close this - additional functions will need new issues
Comment 15 PJ Fanning 2021-12-02 19:33:50 UTC
added partial implementation of xlookup using https://github.com/apache/poi/pull/243 - still lots of scenarios to be done - if anything is intetested in helping out