Related: tdf#127293 Add new Match_mode option for XLOOKUP and

XMATCH functions.

Introduce a value "3" for parameter Match_mode for Regular Expression mode
in XMATCH and XLOOKUP, to seperate the original Match_mode value "2",
which was used for both, Wildcard and Regular Expression mode as well.

Note: "The ODF TC will follow that in the specification of these functions and
at the same time specify, that the host dependent properties HOST-USE-REGULAR-EXPRESSIONS
and HOST-USE-WILDCARDS (file format table:use-regular-expressions and table:use-wildcards)
will be ignored by these functions."
https://issues.oasis-open.org/browse/OFFICE-4154

Also Microsoft Office will introduce this new value for Match_mode in XLOOKUP and XMATCH:
https://insider.microsoft365.com/en-us/blog/new-regular-expression-regex-functions-in-excel

Follow-up commit: 0ca20dca3349daa303b89251443f550491968a39
(Related: tdf#127293 Add unit test for xlookup regex search mode)

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

4 files changed, 18 insertions, 15 deletions

diff --git a/sc/inc/scfuncs.hrc b/sc/inc/scfuncs.hrc
index 9ce5e234c288..845de5f856fa 100644
--- a/sc/inc/scfuncs.hrc
+++ b/sc/inc/scfuncs.hrc
@@ -3390,7 +3390,7 @@ const TranslateId SC_OPCODE_X_LOOKUP_ARY[] =
     NC_("SC_OPCODE_X_LOOKUP", "Result if not found"),
     NC_("SC_OPCODE_X_LOOKUP", "If given, return given text, otherwise return #N/A."),
     NC_("SC_OPCODE_X_LOOKUP", "Match Mode"),
-    NC_("SC_OPCODE_X_LOOKUP", "0 - Exact match. Will return #N/A if no match. (default).\n-1 - Exact match or the next smaller item.\n1 - Exact match or the next larger item.\n2 - Wildcard or regular expression match."),
+    NC_("SC_OPCODE_X_LOOKUP", "0 - Exact match. Will return #N/A if no match. (default).\n-1 - Exact match or the next smaller item.\n1 - Exact match or the next larger item.\n2 - Wildcard match.\n3 - Regular expression match."),
     NC_("SC_OPCODE_X_LOOKUP", "Search Mode"),
     NC_("SC_OPCODE_X_LOOKUP", "1 - Search from the first value (default).\n-1 - Search from the last value (reverse).\n2 - Binary search values sorted in ascending order.\n-2 - Binary search values sorted in descending order.")
 };
@@ -3452,7 +3452,7 @@ const TranslateId SC_OPCODE_X_MATCH_ARY[] =
     NC_("SC_OPCODE_X_MATCH", "Search Array"),
     NC_("SC_OPCODE_X_MATCH", "The array or range to search."),
     NC_("SC_OPCODE_X_MATCH", "Match Mode"),
-    NC_("SC_OPCODE_X_MATCH", "0 - Exact match. Will return #N/A if no match. (default).\n-1 - Exact match or the next smaller item.\n1 - Exact match or the next larger item.\n2 - Wildcard or regular expression match."),
+    NC_("SC_OPCODE_X_MATCH", "0 - Exact match. Will return #N/A if no match. (default).\n-1 - Exact match or the next smaller item.\n1 - Exact match or the next larger item.\n2 - Wildcard match.\n3 - Regular expression match."),
     NC_("SC_OPCODE_X_MATCH", "Search Mode"),
     NC_("SC_OPCODE_X_MATCH", "1 - Search from the first value (default).\n-1 - Search from the last value (reverse).\n2 - Binary search values sorted in ascending order.\n-2 - Binary search values sorted in descending order.")
 };
diff --git a/sc/qa/unit/ucalc_formula2.cxx b/sc/qa/unit/ucalc_formula2.cxx
index f0f3c41ff2b4..4c077b2d4400 100644
--- a/sc/qa/unit/ucalc_formula2.cxx
+++ b/sc/qa/unit/ucalc_formula2.cxx
@@ -4651,13 +4651,13 @@ CPPUNIT_TEST_FIXTURE(TestFormula2, testRegexForXLOOKUP)
     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
 
     // Temporarily switch regex search mode.
-    bool bOldWildCard = false;
+    bool bOldRegex = false;
     ScDocOptions aDocOpt = m_pDoc->GetDocOptions();
-    if (!aDocOpt.IsFormulaRegexEnabled())
+    if (aDocOpt.IsFormulaRegexEnabled())
     {
-        aDocOpt.SetFormulaRegexEnabled(true);
+        aDocOpt.SetFormulaRegexEnabled(false);
         m_pDoc->SetDocOptions(aDocOpt);
-        bOldWildCard = true;
+        bOldRegex = true;
     }
 
     m_pDoc->InsertTab(0, u"Test1"_ustr);
@@ -4677,7 +4677,7 @@ CPPUNIT_TEST_FIXTURE(TestFormula2, testRegexForXLOOKUP)
     insertRangeData(m_pDoc, ScAddress(0, 0, 0), aData); // A1:B11
     m_pDoc->SetString(4, 14, 0, u"^bo.*"_ustr); // E15 - search regex string
 
-    m_pDoc->SetFormula(ScAddress(5, 14, 0), u"=XLOOKUP(E15;A$2:A$11;B$2:B$11;;2)"_ustr,
+    m_pDoc->SetFormula(ScAddress(5, 14, 0), u"=XLOOKUP(E15;A$2:A$11;B$2:B$11;;3)"_ustr,
                        formula::FormulaGrammar::GRAM_NATIVE_UI); // F15
 
     // Without the fix in place, this test would have failed with
@@ -4686,9 +4686,9 @@ CPPUNIT_TEST_FIXTURE(TestFormula2, testRegexForXLOOKUP)
     CPPUNIT_ASSERT_EQUAL(10.81, m_pDoc->GetValue(5, 14, 0));
 
     // Switch back to wildcard mode if necessary.
-    if (bOldWildCard)
+    if (bOldRegex)
     {
-        aDocOpt.SetFormulaWildcardsEnabled(true);
+        aDocOpt.SetFormulaRegexEnabled(true);
         m_pDoc->SetDocOptions(aDocOpt);
     }
     m_pDoc->DeleteTab(0);
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index e1752e086d82..2567f85a7847 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -56,7 +56,7 @@ struct ScInterpreterContext;
 class ScJumpMatrix;
 struct ScRefCellValue;
 
-enum MatchMode{ exactorNA=0, exactorS=-1, exactorG=1, wildcard=2 };
+enum MatchMode{ exactorNA=0, exactorS=-1, exactorG=1, wildcard=2, regex=3 };
 enum SearchMode{ searchfwd=1, searchrev=-1, searchbasc=2, searchbdesc=-2 };
 
 struct VectorSearchArguments
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 15e82847581e..73b25d541fa4 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -5183,7 +5183,7 @@ void ScInterpreter::ScXMatch()
     if (nParamCount >= 3)
     {
         sal_Int16 k = GetInt16();
-        if (k >= -1 && k <= 2)
+        if (k >= -1 && k <= 3)
             vsa.eMatchMode = static_cast<MatchMode>(k);
         else
         {
@@ -7937,7 +7937,7 @@ void ScInterpreter::ScXLookup()
     if ( nParamCount >= 5 )
     {
         sal_Int16 k = GetInt16();
-        if ( k >= -1 && k <= 2 )
+        if ( k >= -1 && k <= 3 )
             vsa.eMatchMode = static_cast<MatchMode>(k);
         else
         {
@@ -11907,10 +11907,11 @@ bool ScInterpreter::SearchVectorForValue( VectorSearchArguments& vsa )
             break;
 
         case wildcard :
+        case regex :
             // this mode can only used with XLOOKUP/XMATCH
             if ( vsa.nSearchOpCode == SC_OPCODE_X_LOOKUP || vsa.nSearchOpCode == SC_OPCODE_X_MATCH )
             {
-                // Wildcard search mode with binary search is not allowed
+                // Wildcard/Regex search mode with binary search is not allowed
                 if (vsa.eSearchMode == searchbasc || vsa.eSearchMode == searchbdesc)
                 {
                     PushNoValue();
@@ -11920,10 +11921,12 @@ bool ScInterpreter::SearchVectorForValue( VectorSearchArguments& vsa )
                 rEntry.eOp = SC_EQUAL;
                 if ( vsa.isStringSearch )
                 {
-                    if ( mrDoc.IsInVBAMode() )
+                    if (vsa.eMatchMode == wildcard && MayBeWildcard(vsa.sSearchStr.getString()))
                         rParam.eSearchType = utl::SearchParam::SearchType::Wildcard;
+                    else if (vsa.eMatchMode == regex && MayBeRegExp(vsa.sSearchStr.getString()))
+                        rParam.eSearchType = utl::SearchParam::SearchType::Regexp;
                     else
-                        rParam.eSearchType = DetectSearchType(vsa.sSearchStr.getString(), mrDoc);
+                        rParam.eSearchType = utl::SearchParam::SearchType::Normal;
                 }
             }
             else