tdf#97831 [part] Add Excel 2016 functions to Calc

Functions IFS and SWITCH.

Change-Id: Ic43d42a933bcac883e9aa2213dd4ddeddf45abf0
Reviewed-on: https://gerrit.libreoffice.org/24424
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>

11 files changed, 283 insertions, 3 deletions

diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src
index b966192a57a0..acc6ba899dab 100644
--- a/formula/source/core/resource/core_resource.src
+++ b/formula/source/core/resource/core_resource.src
@@ -289,6 +289,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF
     String SC_OPCODE_CONCAT { Text = "CONCATENATE" ; };
     String SC_OPCODE_CONCAT_MS { Text = "COM.MICROSOFT.CONCAT" ; };
     String SC_OPCODE_TEXTJOIN_MS { Text = "COM.MICROSOFT.TEXTJOIN" ; };
+    String SC_OPCODE_IFS_MS { Text = "COM.MICROSOFT.IFS" ; };
+    String SC_OPCODE_SWITCH_MS { Text = "COM.MICROSOFT.SWITCH" ; };
     String SC_OPCODE_MAT_VALUE { Text = "MVALUE" ; };
     String SC_OPCODE_MAT_DET { Text = "MDETERM" ; };
     String SC_OPCODE_MAT_INV { Text = "MINVERSE" ; };
@@ -725,6 +727,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML
     String SC_OPCODE_CONCAT { Text = "CONCATENATE" ; };
     String SC_OPCODE_CONCAT_MS { Text = "_xlfn.CONCAT" ; };
     String SC_OPCODE_TEXTJOIN_MS { Text = "_xlfn.TEXTJOIN" ; };
+    String SC_OPCODE_IFS_MS { Text = "_xlfn.IFS" ; };
+    String SC_OPCODE_SWITCH_MS { Text = "_xlfn.SWITCH" ; };
     String SC_OPCODE_MAT_VALUE { Text = "MVALUE" ; };
     String SC_OPCODE_MAT_DET { Text = "MDETERM" ; };
     String SC_OPCODE_MAT_INV { Text = "MINVERSE" ; };
@@ -1161,6 +1165,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH
     String SC_OPCODE_CONCAT { Text = "CONCATENATE" ; };
     String SC_OPCODE_CONCAT_MS { Text = "CONCAT" ; };
     String SC_OPCODE_TEXTJOIN_MS { Text = "TEXTJOIN" ; };
+    String SC_OPCODE_IFS_MS { Text = "IFS" ; };
+    String SC_OPCODE_SWITCH_MS { Text = "SWITCH" ; };
     String SC_OPCODE_MAT_VALUE { Text = "MVALUE" ; };
     String SC_OPCODE_MAT_DET { Text = "MDETERM" ; };
     String SC_OPCODE_MAT_INV { Text = "MINVERSE" ; };
@@ -2335,6 +2341,14 @@ Resource RID_STRLIST_FUNCTION_NAMES
     {
         Text [ en-US ] = "TEXTJOIN" ;
     };
+    String SC_OPCODE_IFS_MS
+    {
+        Text [ en-US ] = "IFS" ;
+    };
+    String SC_OPCODE_SWITCH_MS
+    {
+        Text [ en-US ] = "SWITCH" ;
+    };
     String SC_OPCODE_MAT_VALUE
     {
         Text [ en-US ] = "MVALUE" ;
diff --git a/include/formula/compiler.hrc b/include/formula/compiler.hrc
index 034b165e2c68..0bb83608e28b 100644
--- a/include/formula/compiler.hrc
+++ b/include/formula/compiler.hrc
@@ -497,7 +497,9 @@
 #define SC_OPCODE_FORECAST_LIN      486
 #define SC_OPCODE_CONCAT_MS         487
 #define SC_OPCODE_TEXTJOIN_MS       488
-#define SC_OPCODE_STOP_2_PAR        489     /* last function with two or more parameters' OpCode + 1 */
+#define SC_OPCODE_IFS_MS            489
+#define SC_OPCODE_SWITCH_MS         490
+#define SC_OPCODE_STOP_2_PAR        491     /* last function with two or more parameters' OpCode + 1 */
 
 #define SC_OPCODE_STOP_FUNCTION     SC_OPCODE_STOP_2_PAR            /* last function's OpCode + 1 */
 #define SC_OPCODE_LAST_OPCODE_ID    (SC_OPCODE_STOP_FUNCTION - 1)   /* last OpCode */
diff --git a/include/formula/opcode.hxx b/include/formula/opcode.hxx
index 1bf955fef939..facaaabfa89b 100644
--- a/include/formula/opcode.hxx
+++ b/include/formula/opcode.hxx
@@ -282,6 +282,8 @@ enum OpCode : sal_uInt16
         ocNominal           = SC_OPCODE_NOMINAL,
         ocSubTotal          = SC_OPCODE_SUB_TOTAL,
         ocRawSubtract       = SC_OPCODE_RAWSUBTRACT,
+        ocIfs_MS            = SC_OPCODE_IFS_MS,
+        ocSwitch_MS         = SC_OPCODE_SWITCH_MS,
     // Database functions
         ocDBSum             = SC_OPCODE_DB_SUM,
         ocDBCount           = SC_OPCODE_DB_COUNT,
diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index 29b22bb7f6a8..db24bed2e6d6 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -638,5 +638,7 @@
 #define HID_FUNC_FORECAST_LIN                                   "SC_HID_FUNC_FORECAST_LIN"
 #define HID_FUNC_CONCAT_MS                                      "SC_HID_FUNC_CONCAT_MS"
 #define HID_FUNC_TEXTJOIN_MS                                    "SC_HID_FUNC_TEXTJOIN_MS"
+#define HID_FUNC_IFS_MS                                         "SC_HID_FUNC_IFS_MS"
+#define HID_FUNC_SWITCH_MS                                      "SC_HID_FUNC_SWITCH_MS"
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 6265f08028b0..d3e7567b458c 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -2504,8 +2504,10 @@ void Test::testFunctionLists()
         "IF",
         "IFERROR",
         "IFNA",
+        "IFS",
         "NOT",
         "OR",
+        "SWITCH",
         "TRUE",
         "XOR",
         nullptr
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 588bc801d62a..d58bad0a1cb4 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -377,6 +377,8 @@ formula::StackVar GetStackType();
 // peek StackType of Parameter, Parameter 1 == TOS, 2 == TOS-1, ...
 formula::StackVar GetStackType( sal_uInt8 nParam );
 sal_uInt8 GetByte() { return cPar; }
+// reverse order of stack
+void ReverseStack( sal_uInt8 nParamCount );
 // generates a position-dependent SingleRef out of a DoubleRef
 bool DoubleRefToPosSingleRef( const ScRange& rRange, ScAddress& rAdr );
 double GetDoubleFromMatrix(const ScMatrixRef& pMat);
@@ -604,6 +606,8 @@ void ScRept();
 void ScConcat();
 void ScConcat_MS();
 void ScTextJoin_MS();
+void ScIfs_MS();
+void ScSwitch_MS();
 void ScExternal();
 void ScMissing();
 void ScMacro();
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 96063a980d6a..3cc7c9eead82 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -1848,6 +1848,20 @@ StackVar ScInterpreter::GetStackType( sal_uInt8 nParam )
     return eRes;
 }
 
+void ScInterpreter::ReverseStack( sal_uInt8 nParamCount )
+{
+    //reverse order of parameter stack
+    FormulaToken* p;
+    assert( sp >= nParamCount && " less stack elements than parameters");
+    short nStackParams = std::min<short>( sp, nParamCount);
+    for ( short i = 0; i < short( nStackParams / 2 ); i++ )
+    {
+        p = pStack[ sp - ( nStackParams - i ) ];
+        pStack[ sp - ( nStackParams - i ) ] = pStack[ sp - 1 - i ];
+        pStack[ sp - 1 - i ] = p;
+    }
+}
+
 bool ScInterpreter::DoubleRefToPosSingleRef( const ScRange& rRange, ScAddress& rAdr )
 {
     // Check for a singleton first - no implicit intersection for them.
@@ -3926,6 +3940,8 @@ StackVar ScInterpreter::Interpret()
                 case ocConcat           : ScConcat();                   break;
                 case ocConcat_MS        : ScConcat_MS();                break;
                 case ocTextJoin_MS      : ScTextJoin_MS();              break;
+                case ocIfs_MS           : ScIfs_MS();                   break;
+                case ocSwitch_MS        : ScSwitch_MS();                break;
                 case ocMatValue         : ScMatValue();                 break;
                 case ocMatrixUnit       : ScEMat();                     break;
                 case ocMatDet           : ScMatDet();                   break;
diff --git a/sc/source/core/tool/interpr8.cxx b/sc/source/core/tool/interpr8.cxx
index 352c89fc062c..771210e8517c 100644
--- a/sc/source/core/tool/interpr8.cxx
+++ b/sc/source/core/tool/interpr8.cxx
@@ -1865,4 +1865,166 @@ void ScInterpreter::ScTextJoin_MS()
     }
 }
 
+
+void ScInterpreter::ScIfs_MS()
+{
+    short nParamCount = GetByte();
+
+    ReverseStack( nParamCount );
+
+    bool bFinished = false;
+    while ( nParamCount > 0 && !bFinished && !nGlobalError )
+    {
+        bool bVal = GetBool();
+        nParamCount--;
+        if ( bVal )
+        {
+            // TRUE
+            if ( nParamCount < 1 )
+            {
+                // no parameter given for THEN
+                PushParameterExpected();
+                return;
+            }
+            bFinished = true;
+        }
+        else
+        {
+            // FALSE
+            if ( nParamCount >= 3 )
+            {
+                // ELSEIF path
+                Pop();
+                nParamCount--;
+            }
+            else
+            {
+                // no parameter given for ELSE
+                PushNA();
+                return;
+            }
+        }
+    }
+
+    if ( nGlobalError || !bFinished  )
+    {
+        if ( !bFinished )
+            PushNA(); // no true expression found
+        if ( nGlobalError )
+            PushNoValue(); // expression returned something other than true or false
+        return;
+    }
+
+    //push result :
+    FormulaTokenRef xToken( PopToken() );
+    if ( xToken )
+        PushTempToken( xToken.get() );
+    else
+        PushError( errUnknownStackVariable );
+}
+
+
+void ScInterpreter::ScSwitch_MS()
+{
+    short nParamCount = GetByte();
+
+    ReverseStack( nParamCount );
+
+    bool isValue = false;
+    double fRefVal = 0;
+    svl::SharedString aRefStr;
+    switch ( GetStackType() )
+    {
+        case svDouble:
+            isValue = true;
+            fRefVal = GetDouble();
+            break;
+        case svString:
+            isValue = false;
+            aRefStr = GetString();
+            break;
+        case svSingleRef :
+        case svDoubleRef :
+            {
+                ScAddress aAdr;
+                PopDoubleRefOrSingleRef( aAdr );
+                if ( nGlobalError )
+                    break;
+                ScRefCellValue aCell( *pDok, aAdr );
+                isValue = !( aCell.hasString() || aCell.hasEmptyValue() || aCell.isEmpty() );
+                if ( isValue )
+                    fRefVal = aCell.getValue();
+                else
+                    aRefStr = aCell.getString( pDok );
+            }
+            break;
+        case svExternalSingleRef:
+        case svExternalDoubleRef:
+        case svMatrix:
+            isValue = ScMatrix::IsValueType( GetDoubleOrStringFromMatrix( fRefVal, aRefStr ) );
+            break;
+        default :
+            PushIllegalArgument();
+            return;
+    }
+    nParamCount--;
+    bool bFinished = false;
+    while ( nParamCount > 1 && !bFinished && !nGlobalError )
+    {
+        double fVal = 0;
+        svl::SharedString aStr;
+        if ( isValue )
+            fVal = GetDouble();
+        else
+            aStr = GetString();
+        nParamCount--;
+        if ( ( isValue && rtl::math::approxEqual( fRefVal, fVal ) ) ||
+             ( !isValue && aRefStr.getDataIgnoreCase() == aStr.getDataIgnoreCase() ) )
+        {
+            // TRUE
+            if ( nParamCount < 1 )
+            {
+                // no parameter given for THEN
+                PushParameterExpected();
+                return;
+            }
+            bFinished = true;
+        }
+        else
+        {
+            // FALSE
+            if ( nParamCount >= 2 )
+            {
+                // ELSEIF path
+                Pop();
+                nParamCount--;
+                // if nParamCount equals 1: default value  to be returned
+                bFinished = ( nParamCount == 1 );
+            }
+            else
+            {
+                // no parameter given for ELSE
+                PushNA();
+                return;
+            }
+        }
+    }
+
+    if ( nGlobalError || !bFinished  )
+    {
+        if ( !bFinished )
+            PushNA(); // no true expression found
+        if ( nGlobalError )
+            PushNoValue(); // expression returned something other than true or false
+        return;
+    }
+
+    // push result
+    FormulaTokenRef xToken( PopToken() );
+    if ( xToken )
+        PushTempToken( xToken.get() );
+    else
+        PushError( errUnknownStackVariable );
+}
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index ad2ba9707ba3..dadb2e7d8bad 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -581,7 +581,9 @@ static const XclFunctionInfo saFuncTable_2016[] =
     EXC_FUNCENTRY_V_VR(  ocForecast_ETS_STA,    3,  6,  0,  "FORECAST.ETS.STAT" ),
     EXC_FUNCENTRY_V_VR(  ocForecast_LIN,        3,  3,  0,  "FORECAST.LINEAR" ),
     EXC_FUNCENTRY_V_VR(  ocConcat_MS,           1,  MX, 0,  "CONCAT" ),
-    EXC_FUNCENTRY_V_VR(  ocTextJoin_MS,         3,  MX, 0,  "TEXTJOIN" )
+    EXC_FUNCENTRY_V_VR(  ocTextJoin_MS,         3,  MX, 0,  "TEXTJOIN" ),
+    EXC_FUNCENTRY_V_VR(  ocIfs_MS,              2,  MX, 0,  "IFS" ),
+    EXC_FUNCENTRY_V_VR(  ocSwitch_MS,           3,  MX, 0,  "SWITCH" )
 };
 
 #define EXC_FUNCENTRY_ODF( opcode, minparam, maxparam, flags, asciiname ) \
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 6195808982ea..c4636305bcaf 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -911,7 +911,9 @@ static const FunctionData saFuncTable2016[] =
     { "COM.MICROSOFT.FORECAST.ETS.STAT",        "FORECAST.ETS.STAT",        NOID,   NOID,   3,  6,  V, { VR, VA, VR }, FUNCFLAG_MACROCALL_NEW },
     { "COM.MICROSOFT.FORECAST.LINEAR",          "FORECAST.LINEAR",          NOID,   NOID,   3,  3,  V, { VR, VA }, FUNCFLAG_MACROCALL_NEW },
     { "COM.MICROSOFT.CONCAT",                   "CONCAT",                   NOID,   NOID,   1,  MX, V, { VR }, FUNCFLAG_MACROCALL_NEW },
-    { "COM.MICROSOFT.TEXTJOIN",                 "TEXTJOIN",                 NOID,   NOID,   3,  MX, V, { VR }, FUNCFLAG_MACROCALL_NEW }
+    { "COM.MICROSOFT.TEXTJOIN",                 "TEXTJOIN",                 NOID,   NOID,   3,  MX, V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.IFS",                      "IFS",                      NOID,   NOID,   2,  MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.SWITCH",                   "SWITCH",                   NOID,   NOID,   3,  MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW }
 };
 
 
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index ae82ad4e173e..04c9388985c2 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -11591,6 +11591,78 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
             Text [ en-US ] = "Text and/or cell ranges for the concatenation." ;
         };
     };
+     // -=*# Resource for function IFS #*=-
+    Resource SC_OPCODE_IFS_MS
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Checks 1 or more conditions and returns a value corresponding to the first true condition." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_LOGIC;
+            HID_FUNC_IFS_MS;
+            PAIRED_VAR_ARGS;  0; 0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "Test" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "Any value or expression which can be either TRUE or FALSE." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "result" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The result of the function if test is TRUE." ;
+        };
+    };
+     // -=*# Resource for function SWITCH #*=-
+    Resource SC_OPCODE_SWITCH_MS
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Checks 1 or more conditions and returns a value corresponding to the first true condition." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_LOGIC;
+            HID_FUNC_SWITCH_MS;
+            PAIRED_VAR_ARGS + 1;  0;  0;  0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "expression" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "Value that will be compared against value1-valueN." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "value " ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "Value that will be compared against expression." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "result" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "Value to return when corresponding value argument matches expression." ;
+        };
+    };
      // -=*# Resource for function EXACT #*=-
     Resource SC_OPCODE_EXACT
     {