Bug 60924

Summary: New Excel 2016 Statements Not Supported
Product: POI Reporter: Adrian B. <barnes41>
Component: SS CommonAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: enhancement CC: matafagafo, pdg
Priority: P2    
Version: 4.1.1-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on: 64633    
Bug Blocks:    
Attachments: Example of 2016 SWITCH Statement
Example of 2016 IFS Statement
Example of 2016 MAXIFS Statement
Example of 2016 MINIFS Statement
Example of 2016 TEXTJOIN Statement
Example of 2016 CONCAT Statement
Patch for IFS and SWITCH

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.