sc/inc/scfuncs.hrc | 4 ++--
sc/qa/unit/ucalc_formula2.cxx | 14 +++++++-------
sc/source/core/inc/interpre.hxx | 2 +-
sc/source/core/tool/interpr1.cxx | 13 ++++++++-----
4 files changed, 18 insertions(+), 15 deletions(-)
New commits:
commit 607b99ea5b1b1e46622262cc5cfbeea01178d751
Author: Balazs Varga <balazs.varga.ext...@allotropia.de>
AuthorDate: Thu Jun 20 14:07:57 2024 +0200
Commit: Balazs Varga <balazs.varga.ext...@allotropia.de>
CommitDate: Mon Jun 24 11:39:19 2024 +0200
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.ext...@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 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).
-1 - Exact match or the next smaller item.
1 - Exact match or the next larger item.
2 - Wildcard or regular expression match."),
+ NC_("SC_OPCODE_X_LOOKUP", "0 - Exact match. Will return #N/A if no match.
(default).
-1 - Exact match or the next smaller item.
1 - Exact match or the next larger item.
2 - Wildcard match.
3 - Regular expression match."),
NC_("SC_OPCODE_X_LOOKUP", "Search Mode"),
NC_("SC_OPCODE_X_LOOKUP", "1 - Search from the first value (default).
-1 - Search from the last value (reverse).
2 - Binary search values sorted in ascending order.
-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).
-1 - Exact match or the next smaller item.
1 - Exact match or the next larger item.
2 - Wildcard or regular expression match."),
+ NC_("SC_OPCODE_X_MATCH", "0 - Exact match. Will return #N/A if no match.
(default).
-1 - Exact match or the next smaller item.
1 - Exact match or the next larger item.
2 - Wildcard match.
3 - Regular expression match."),
NC_("SC_OPCODE_X_MATCH", "Search Mode"),
NC_("SC_OPCODE_X_MATCH", "1 - Search from the first value (default).
-1 - Search from the last value (reverse).
2 - Binary search values sorted in ascending order.
-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
