Bug 60924 - New Excel 2016 Statements Not Supported
Summary: New Excel 2016 Statements Not Supported
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 4.1.1-FINAL
Hardware: PC All
: P2 enhancement with 9 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-03-27 17:24 UTC by Adrian B.
Modified: 2020-03-27 10:16 UTC (History)
1 user (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.