Bug 60924

Summary:	New Excel 2016 Statements Not Supported
Product:	POI	Reporter:	Adrian B. <barnes41>
Component:	SS Common	Assignee:	POI Developers List <dev>
Status:	NEW ---
Severity:	enhancement	CC:	pdg
Priority:	P2
Version:	4.1.1-FINAL
Target Milestone:	---
Hardware:	PC
OS:	All
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 3 Adrian B. 2017-03-27 22:32:23 UTC

Created attachment 34878 [details]
Example of 2016 SWITCH Statement

According to https://www.onmsft.com/news/excel-2016-gains-6-new-functions-to-simplify-formula-editing , there are 6 new formulas.

Here's the Microsoft documentation for each:

SWITCH - https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e

IFS - https://support.office.com/en-US/article/IFS-function-36329a26-37b2-467c-972b-4a39bd951d45

CONCAT - https://support.office.com/en-US/article/CONCAT-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2

TEXTJOIN - https://support.office.com/en-US/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

MAXIFS - https://support.office.com/en-us/article/MAXIFS-function-dfd611e6-da2c-488a-919b-9b6376b28883

MINIFS - https://support.office.com/en-us/article/MINIFS-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599

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.