tdf#128425 Add Excel2021 function XMATCH to Calc

https://issues.oasis-open.org/browse/OFFICE-4155

Change-Id: I7791ab873cf62ce882feba75dc0d722bc5990f9a
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/163260
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>

9 files changed, 208 insertions, 26 deletions

diff --git a/sc/source/core/data/funcdesc.cxx b/sc/source/core/data/funcdesc.cxx
index a9c123d89768..1c704c9933d4 100644
--- a/sc/source/core/data/funcdesc.cxx
+++ b/sc/source/core/data/funcdesc.cxx
@@ -676,6 +676,7 @@ ScFunctionList::ScFunctionList( bool bEnglishFunctionNames )
         { SC_OPCODE_ISPMT, ENTRY(SC_OPCODE_ISPMT_ARY), 0, ID_FUNCTION_GRP_FINANCIAL, HID_FUNC_ISPMT, 4, { 0, 0, 0, 0 }, 0 },
         { SC_OPCODE_HYPERLINK, ENTRY(SC_OPCODE_HYPERLINK_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_HYPERLINK, 2, { 0, 1 }, 0 },
         { SC_OPCODE_X_LOOKUP, ENTRY(SC_OPCODE_X_LOOKUP_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_XLOOKUP_MS, 6, { 0, 0, 0, 1, 1, 1 }, 0 },
+        { SC_OPCODE_X_MATCH, ENTRY(SC_OPCODE_X_MATCH_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_XMATCH_MS, 4, { 0, 0, 1, 1 }, 0 },
         { SC_OPCODE_GET_PIVOT_DATA, ENTRY(SC_OPCODE_GET_PIVOT_DATA_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_GETPIVOTDATA, VAR_ARGS+2, { 0, 0, 1 }, 0 },
         { SC_OPCODE_EUROCONVERT, ENTRY(SC_OPCODE_EUROCONVERT_ARY), 0, ID_FUNCTION_GRP_MATH, HID_FUNC_EUROCONVERT, 5, { 0, 0, 0, 1, 1 }, 0 },
         { SC_OPCODE_NUMBERVALUE, ENTRY(SC_OPCODE_NUMBERVALUE_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_NUMBERVALUE, 3, { 0, 1, 1 }, 0 },
diff --git a/sc/source/core/data/queryiter.cxx b/sc/source/core/data/queryiter.cxx
index 8abaa2b93fdd..d931b9429925 100644
--- a/sc/source/core/data/queryiter.cxx
+++ b/sc/source/core/data/queryiter.cxx
@@ -206,8 +206,8 @@ void ScQueryCellIteratorBase< accessType, queryType >::PerformQuery()
                 if ( aCell.isEmpty())
                     return;
 
-                // XLookUp: Forward/asc/backward/desc search for best fit value, except if we have an exact match
-                if (nSearchOpCode == SC_OPCODE_X_LOOKUP &&
+                // XLookUp/XMatch: Forward/asc/backward/desc search for best fit value, except if we have an exact match
+                if ((nSearchOpCode == SC_OPCODE_X_LOOKUP || nSearchOpCode == SC_OPCODE_X_MATCH) &&
                     (rEntry.eOp == SC_LESS_EQUAL || rEntry.eOp == SC_GREATER_EQUAL) &&
                     (nBestFitCol != nCol || nBestFitRow != nRow))
                 {
@@ -315,8 +315,8 @@ void ScQueryCellIteratorBase< accessType, queryType >::InitPos()
                 // non-matching position using SC_LESS and the start position
                 // is the one after it.
                 lastRow = nRow;
-                // BinarySearch() looks for the first match for XLOOKUP
-                if (nSearchOpCode != SC_OPCODE_X_LOOKUP)
+                // BinarySearch() looks for the first match for XLOOKUP/XMATCH
+                if (nSearchOpCode != SC_OPCODE_X_LOOKUP && nSearchOpCode != SC_OPCODE_X_MATCH)
                 {
                     ScQueryOp saveOp = op;
                     op = SC_LESS;
@@ -342,7 +342,8 @@ void ScQueryCellIteratorBase< accessType, queryType >::InitPos()
             if( BinarySearch( nCol ))
                 lastRow = nRow;
         }
-        AccessBase::InitPosFinish(beforeRow, lastRow, (nSearchOpCode == SC_OPCODE_X_LOOKUP));
+        AccessBase::InitPosFinish(beforeRow, lastRow,
+            (nSearchOpCode == SC_OPCODE_X_LOOKUP || nSearchOpCode == SC_OPCODE_X_MATCH));
     }
 }
 
@@ -546,9 +547,9 @@ bool ScQueryCellIteratorBase< accessType, queryType >::BinarySearch( SCCOL col,
                 {
                     if (fLastInRangeValue <= nCellVal)
                     {
-                        if (nSearchOpCode  == SC_OPCODE_X_LOOKUP &&
-                            nSortedBinarySearch != nSearchbDesc &&
-                            fLastInRangeValue == nCellVal && aIndexer.getLowIndex() != i)
+                        if ((nSearchOpCode == SC_OPCODE_X_LOOKUP || nSearchOpCode == SC_OPCODE_X_MATCH) &&
+                            nSortedBinarySearch != nSearchbDesc && fLastInRangeValue == nCellVal &&
+                            aIndexer.getLowIndex() != i)
                             bDone = true;
                         else
                         {
@@ -596,9 +597,9 @@ bool ScQueryCellIteratorBase< accessType, queryType >::BinarySearch( SCCOL col,
                         aCellStr);
                 if (nTmp <= 0)
                 {
-                    if (nSearchOpCode == SC_OPCODE_X_LOOKUP &&
-                        nSortedBinarySearch != nSearchbDesc &&
-                        nTmp == 0 && aIndexer.getLowIndex() != i)
+                    if ((nSearchOpCode == SC_OPCODE_X_LOOKUP || nSearchOpCode == SC_OPCODE_X_MATCH) &&
+                        nSortedBinarySearch != nSearchbDesc && nTmp == 0 &&
+                        aIndexer.getLowIndex() != i)
                         bDone = true;
                     else
                     {
@@ -672,8 +673,8 @@ bool ScQueryCellIteratorBase< accessType, queryType >::BinarySearch( SCCOL col,
             {
                 found = i;
                 nLastInRange = i;
-                if (nSearchOpCode == SC_OPCODE_X_LOOKUP && (nSortedBinarySearch == nSearchbAscd &&
-                    (rEntry.eOp == SC_LESS_EQUAL || rEntry.eOp == SC_EQUAL)))
+                if ((nSearchOpCode == SC_OPCODE_X_LOOKUP || nSearchOpCode == SC_OPCODE_X_MATCH) &&
+                    (nSortedBinarySearch == nSearchbAscd && (rEntry.eOp == SC_LESS_EQUAL || rEntry.eOp == SC_EQUAL)))
                     bDone = true;
                 else // But keep searching to find the last matching one.
                     nLo = nMid + 1;
@@ -753,8 +754,9 @@ bool ScQueryCellIterator< accessType >::FindEqualOrSortedLastInRange( SCCOL& nFo
     nFoundCol = rDoc.MaxCol()+1;
     nFoundRow = rDoc.MaxRow()+1;
 
-    if (nSearchOpCode == SC_OPCODE_X_LOOKUP && nSortedBinarySearch == nBinarySearchDisabled)
-        SetStopOnMismatch( false ); // assume not sorted keys for XLookup
+    if ((nSearchOpCode == SC_OPCODE_X_LOOKUP || nSearchOpCode == SC_OPCODE_X_MATCH) &&
+        nSortedBinarySearch == nBinarySearchDisabled)
+        SetStopOnMismatch( false ); // assume not sorted keys for XLookup/XMatch
     else
         SetStopOnMismatch( true ); // assume sorted keys
 
@@ -767,8 +769,8 @@ bool ScQueryCellIterator< accessType >::FindEqualOrSortedLastInRange( SCCOL& nFo
         (bLiteral || maParam.GetEntry(0).GetQueryItem().meType == ScQueryEntry::ByValue) &&
         (maParam.GetEntry(0).eOp == SC_LESS_EQUAL || maParam.GetEntry(0).eOp == SC_GREATER_EQUAL);
 
-    // assume not sorted properly if we are using XLookup with forward or backward search
-    if (bBinary && nSearchOpCode == SC_OPCODE_X_LOOKUP &&
+    // assume not sorted properly if we are using XLookup/XMatch with forward or backward search
+    if (bBinary && (nSearchOpCode == SC_OPCODE_X_LOOKUP || nSearchOpCode == SC_OPCODE_X_MATCH) &&
         nSortedBinarySearch == nBinarySearchDisabled)
         bBinary = false;
 
@@ -862,7 +864,8 @@ bool ScQueryCellIterator< accessType >::FindEqualOrSortedLastInRange( SCCOL& nFo
             }
         }
     }
-    if ( IsEqualConditionFulfilled() && nSearchOpCode != SC_OPCODE_X_LOOKUP )
+    if (IsEqualConditionFulfilled() && (nSearchOpCode != SC_OPCODE_X_LOOKUP &&
+        nSearchOpCode != SC_OPCODE_X_MATCH))
     {
         // Position on last equal entry, except for XLOOKUP,
         // which looking for the first equal entry
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 57c69d6a3d02..75f70c78d647 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -541,7 +541,7 @@ private:
     inline void TreatDoubleError( double& rVal );
     // Lookup using ScLookupCache, @returns true if found and result address
     bool LookupQueryWithCache( ScAddress & o_rResultPos, const ScQueryParam & rParam,
-            const ScComplexRefData* refData, sal_Int8 nSearchMode, sal_Int16 nOpCode ) const;
+            const ScComplexRefData* refData, sal_Int8 nSearchMode, sal_uInt16 nOpCode ) const;
 
     void ScIfJump();
     void ScIfError( bool bNAonly );
@@ -671,6 +671,7 @@ private:
     void ScRow();
     void ScSheet();
     void ScMatch();
+    void ScXMatch();
     void IterateParametersIf( ScIterFuncIf );
     void ScCountIf();
     void ScSumIf();
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index fd7bab714805..c33e74ba9c3e 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -5075,6 +5075,178 @@ void ScInterpreter::ScMatch()
         PushIllegalParameter();
 }
 
+void ScInterpreter::ScXMatch()
+{
+    sal_uInt8 nParamCount = GetByte();
+    if (!MustHaveParamCount(nParamCount, 2, 4))
+        return;
+
+    VectorSearchArguments vsa;
+    vsa.nSearchOpCode = SC_OPCODE_X_MATCH;
+
+    // get search mode
+    if (nParamCount == 4)
+    {
+        sal_Int16 k = GetInt16();
+        if (k >= -2 && k <= 2 && k != 0)
+            vsa.eSearchMode = static_cast<SearchMode>(k);
+        else
+        {
+            PushIllegalParameter();
+            return;
+        }
+    }
+    else
+        vsa.eSearchMode = searchfwd;
+
+    // get match mode
+    if (nParamCount >= 3)
+    {
+        sal_Int16 k = GetInt16();
+        if (k >= -1 && k <= 2)
+            vsa.eMatchMode = static_cast<MatchMode>(k);
+        else
+        {
+            PushIllegalParameter();
+            return;
+        }
+    }
+    else
+        vsa.eMatchMode = exactorNA;
+
+    // get vector to be searched
+    switch (GetStackType())
+    {
+        case svSingleRef:
+        {
+            PopSingleRef(vsa.nCol1, vsa.nRow1, vsa.nTab1);
+            vsa.nCol2 = vsa.nCol1;
+            vsa.nRow2 = vsa.nRow1;
+            vsa.pMatSrc = nullptr;
+        }
+        break;
+        case svDoubleRef:
+        {
+            vsa.pMatSrc = nullptr;
+            SCTAB nTab2 = 0;
+            PopDoubleRef(vsa.nCol1, vsa.nRow1, vsa.nTab1, vsa.nCol2, vsa.nRow2, nTab2);
+            if (vsa.nTab1 != nTab2 || (vsa.nCol1 != vsa.nCol2 && vsa.nRow1 != vsa.nRow2))
+            {
+                PushIllegalParameter();
+                return;
+            }
+        }
+        break;
+        case svMatrix:
+        case svExternalDoubleRef:
+        {
+            if (GetStackType() == svMatrix)
+                vsa.pMatSrc = PopMatrix();
+            else
+                PopExternalDoubleRef(vsa.pMatSrc);
+
+            if (!vsa.pMatSrc)
+            {
+                PushIllegalParameter();
+                return;
+            }
+        }
+        break;
+        default:
+            PushIllegalParameter();
+            return;
+    }
+
+    // get search value
+    if (nGlobalError == FormulaError::NONE)
+    {
+        switch (GetStackType())
+        {
+            case svDouble:
+            {
+                vsa.isStringSearch = false;
+                vsa.fSearchVal = GetDouble();
+            }
+            break;
+            case svString:
+            {
+                vsa.isStringSearch = true;
+                vsa.sSearchStr = GetString();
+            }
+            break;
+            case svDoubleRef:
+            case svSingleRef:
+            {
+                ScAddress aAdr;
+                if (!PopDoubleRefOrSingleRef(aAdr))
+                {
+                    PushInt(0);
+                    return;
+                }
+                ScRefCellValue aCell(mrDoc, aAdr);
+                if (aCell.hasNumeric())
+                {
+                    vsa.isStringSearch = false;
+                    vsa.fSearchVal = GetCellValue(aAdr, aCell);
+                }
+                else
+                {
+                    vsa.isStringSearch = true;
+                    GetCellString(vsa.sSearchStr, aCell);
+                }
+            }
+            break;
+            case svExternalSingleRef:
+            {
+                ScExternalRefCache::TokenRef pToken;
+                PopExternalSingleRef(pToken);
+                if (nGlobalError != FormulaError::NONE)
+                {
+                    PushError(nGlobalError);
+                    return;
+                }
+                if (pToken->GetType() == svDouble)
+                {
+                    vsa.isStringSearch = false;
+                    vsa.fSearchVal = pToken->GetDouble();
+                }
+                else
+                {
+                    vsa.isStringSearch = true;
+                    vsa.sSearchStr = pToken->GetString();
+                }
+            }
+            break;
+            case svExternalDoubleRef:
+            case svMatrix:
+            {
+                ScMatValType nType = GetDoubleOrStringFromMatrix(
+                    vsa.fSearchVal, vsa.sSearchStr);
+                vsa.isStringSearch = ScMatrix::IsNonValueType(nType);
+            }
+            break;
+            default:
+            {
+                PushIllegalParameter();
+                return;
+            }
+        }
+
+        // execute search
+        if (SearchVectorForValue(vsa))
+            PushDouble(vsa.nIndex);
+        else
+        {
+            if (vsa.isResultNA)
+                PushNA();
+            else
+                return; // error occurred and has already been pushed
+        }
+    }
+    else
+        PushIllegalParameter();
+}
+
 namespace {
 
 bool isCellContentEmpty( const ScRefCellValue& rCell )
@@ -10741,8 +10913,8 @@ bool ScInterpreter::SearchVectorForValue( VectorSearchArguments& vsa )
             break;
 
         case wildcard :
-            // this mode can only used with XLOOKUP
-            if ( vsa.nSearchOpCode == SC_OPCODE_X_LOOKUP )
+            // this mode can only used with XLOOKUP/XMATCH
+            if ( vsa.nSearchOpCode == SC_OPCODE_X_LOOKUP || vsa.nSearchOpCode == SC_OPCODE_X_MATCH )
             {
                 rEntry.eOp = SC_EQUAL;
                 if ( vsa.isStringSearch )
@@ -10833,7 +11005,7 @@ bool ScInterpreter::SearchVectorForValue( VectorSearchArguments& vsa )
 
 static bool lcl_LookupQuery( ScAddress & o_rResultPos, ScDocument& rDoc, ScInterpreterContext& rContext,
         const ScQueryParam & rParam, const ScQueryEntry & rEntry, const ScFormulaCell* cell,
-        const ScComplexRefData* refData, sal_Int8 nSearchMode, sal_Int16 nOpCode )
+        const ScComplexRefData* refData, sal_Int8 nSearchMode, sal_uInt16 nOpCode )
 {
     if (rEntry.eOp != SC_EQUAL)
     {
@@ -10952,7 +11124,7 @@ static SCROW lcl_getPrevRowWithEmptyValueLookup( const ScLookupCache& rCache,
 
 bool ScInterpreter::LookupQueryWithCache( ScAddress & o_rResultPos,
         const ScQueryParam & rParam, const ScComplexRefData* refData,
-        sal_Int8 nSearchMode, sal_Int16 nOpCode ) const
+        sal_Int8 nSearchMode, sal_uInt16 nOpCode ) const
 {
     bool bFound = false;
     const ScQueryEntry& rEntry = rParam.GetEntry(0);
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 60190bac72b3..2069bedbb763 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4317,6 +4317,7 @@ StackVar ScInterpreter::Interpret()
                 case ocIndirect         : ScIndirect();                 break;
                 case ocAddress          : ScAddressFunc();              break;
                 case ocMatch            : ScMatch();                    break;
+                case ocXMatch           : ScXMatch();                   break;
                 case ocCountEmptyCells  : ScCountEmptyCells();          break;
                 case ocCountIf          : ScCountIf();                  break;
                 case ocSumIf            : ScSumIf();                    break;
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index e6a5cda2f19f..448731491154 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -264,7 +264,8 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocVarS,            {{ Reference                                            }, 1, Value }},
     { ocWhitespace,      {{ Bounds                                               }, 0, Bounds }},
     { ocWorkday_MS,      {{ Value, Value, Value, Reference                       }, 0, Value }},
-    { ocXLookup,         {{ Value, ReferenceOrForceArray, ReferenceOrForceArray, Value, Value, Value }, 0, Value }},
+    { ocXLookup,         {{ Value, ReferenceOrForceArray, ReferenceOrForceArray, Value, Value, Value }, 0, Reference }},
+    { ocXMatch,          {{ Value, ReferenceOrForceArray, Value, Value           }, 0, Value }},
     { ocXor,             {{ Reference                                            }, 1, Value }},
     { ocZTest,           {{ Reference, Value, Value                              }, 0, Value }},
     { ocZTest_MS,        {{ Reference, Value, Value                              }, 0, Value }},
diff --git a/sc/source/core/tool/token.cxx b/sc/source/core/tool/token.cxx
index 114bdae1497a..f57efce3414c 100644
--- a/sc/source/core/tool/token.cxx
+++ b/sc/source/core/tool/token.cxx
@@ -1388,6 +1388,7 @@ void ScTokenArray::CheckToken( const FormulaToken& r )
             case ocCount2:
             case ocVLookup:
             case ocXLookup:
+            case ocXMatch:
             case ocSLN:
             case ocIRR:
             case ocMIRR:
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index f0e5b09a0e4b..3968e33e794d 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -601,7 +601,8 @@ const XclFunctionInfo saFuncTable_2016[] =
  */
 const XclFunctionInfo saFuncTable_2021[] =
 {
-    EXC_FUNCENTRY_V_VR(  ocXLookup,    3,  6,  0,  "XLOOKUP" )
+    EXC_FUNCENTRY_V_VR(  ocXLookup,    3,  6,  0,  "XLOOKUP" ),
+    EXC_FUNCENTRY_V_VR(  ocXMatch,     2,  4,  0,  "XMATCH" )
 };
 
 
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 6ee06ff6b89b..6f4037712d1d 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -875,7 +875,8 @@ const FunctionData saFuncTable2016[] =
 /* FIXME: BIFF?? function identifiers available? Where to obtain? */
 const FunctionData saFuncTable2021[] =
 {
-    { "COM.MICROSOFT.XLOOKUP",             "XLOOKUP",             NOID,   NOID,   3,  6,  V, { VR, VA, VR }, FuncFlags::MACROCALL_NEW }
+    { "COM.MICROSOFT.XLOOKUP",             "XLOOKUP",             NOID,   NOID,   3,  6,  R, { VR, VA, VR }, FuncFlags::MACROCALL_NEW },
+    { "COM.MICROSOFT.XMATCH",              "XMATCH",              NOID,   NOID,   2,  4,  V, { VR, VA }, FuncFlags::MACROCALL_NEW }
 };