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
(cherry picked from commit 17d578ba91f9c78a0e41d19b58183d2214c0b7a4)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/169294
diff --git a/sc/inc/scfuncs.hrc b/sc/inc/scfuncs.hrc
index 9ce5e23..845de5f8 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 f0f3c41..4c077b2 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 e1752e0..2567f85 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 15e8284..73b25d5 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
