[Bug 60924] New: Excel 2016 SWITCH Statement Not Supported

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

            Bug ID: 60924
           Summary: Excel 2016 SWITCH Statement Not Supported
           Product: POI
           Version: unspecified
          Hardware: PC
            Status: NEW
          Severity: enhancement
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

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'.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

Javen O'Neal <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--- Comment #1 from Javen O'Neal <[hidden email]> ---
Official documentation for SWITCH function:
https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

Javen O'Neal <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO

--- Comment #2 from Javen O'Neal <[hidden email]> ---
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?

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

--- Comment #3 from Adrian B. <[hidden email]> ---
Created attachment 34878
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34878&action=edit
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

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

--- Comment #4 from Adrian B. <[hidden email]> ---
Created attachment 34879
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34879&action=edit
Example of 2016 IFS Statement

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

--- Comment #5 from Adrian B. <[hidden email]> ---
Created attachment 34880
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34880&action=edit
Example of 2016 MAXIFS Statement

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

--- Comment #6 from Adrian B. <[hidden email]> ---
Created attachment 34881
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34881&action=edit
Example of 2016 MINIFS Statement

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

--- Comment #7 from Adrian B. <[hidden email]> ---
Created attachment 34882
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34882&action=edit
Example of 2016 TEXTJOIN Statement

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

--- Comment #8 from Adrian B. <[hidden email]> ---
Created attachment 34883
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34883&action=edit
Example of 2016 CONCAT Statement

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

Adrian B. <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|Excel 2016 SWITCH Statement |New Excel 2016 Statements
                   |Not Supported               |Not Supported

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

Pieter Degraeuwe <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

--- Comment #9 from Pieter Degraeuwe <[hidden email]> ---
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?

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

--- Comment #10 from Nick Burch <[hidden email]> ---
(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!

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

--- Comment #11 from Pieter Degraeuwe <[hidden email]> ---
Created attachment 35955
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35955&action=edit
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!

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Version|unspecified                 |4.1.1-FINAL

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--- Comment #12 from [hidden email] ---
I've loaded with version 4.1.1 library. When I evaluateCell contain MINIFS, the
NotImplemented error occur.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

--- Comment #13 from [hidden email] ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Depends on|                            |64633


Referenced Bugs:

https://bz.apache.org/bugzilla/show_bug.cgi?id=64633
[Bug 64633] [PATCH] Support of function TEXTJOIN
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

Matafagafo <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60924

Anthony V <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]