Resolves: tdf#113977 implement REGEX() spreadsheet function

REGEX( Text ; Expression [ ; Replacement ] )

Using ICU regular expressions
http://userguide.icu-project.org/strings/regexp

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

6 files changed, 63 insertions, 3 deletions

diff --git a/sc/source/core/data/funcdesc.cxx b/sc/source/core/data/funcdesc.cxx
index 6f31ab7cf234..5c530b3c6009 100644
--- a/sc/source/core/data/funcdesc.cxx
+++ b/sc/source/core/data/funcdesc.cxx
@@ -807,7 +807,8 @@ ScFunctionList::ScFunctionList()
         { SC_OPCODE_ROUNDSIG, ENTRY(SC_OPCODE_ROUNDSIG_ARY), 0, ID_FUNCTION_GRP_MATH, HID_FUNC_ROUNDSIG, 2, { 0, 0 } },
         { SC_OPCODE_REPLACEB, ENTRY(SC_OPCODE_REPLACEB_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_REPLACEB, 4, { 0, 0, 0, 0 } },
         { SC_OPCODE_FINDB, ENTRY(SC_OPCODE_FINDB_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_FINDB, 3, { 0, 0, 1 } },
-        { SC_OPCODE_SEARCHB, ENTRY(SC_OPCODE_SEARCHB_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_SEARCHB, 3, { 0, 0, 1 } }
+        { SC_OPCODE_SEARCHB, ENTRY(SC_OPCODE_SEARCHB_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_SEARCHB, 3, { 0, 0, 1 } },
+        { SC_OPCODE_REGEX, ENTRY(SC_OPCODE_REGEX_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_REGEX, 3, { 0, 0, 1 } }
     };
 
     ScFuncDesc* pDesc = nullptr;
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 1a93baa9c364..c09f91405923 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -665,6 +665,7 @@ private:
     void ScText();
     void ScSubstitute();
     void ScRept();
+    void ScRegex();
     void ScConcat();
     void ScConcat_MS();
     void ScTextJoin_MS();
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index dc23b55a43dc..6708bdce7913 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -9222,6 +9222,61 @@ void ScInterpreter::ScSearch()
     }
 }
 
+void ScInterpreter::ScRegex()
+{
+    sal_uInt8 nParamCount = GetByte();
+    if (MustHaveParamCount( nParamCount, 2, 3))
+    {
+        bool bReplacement = false;
+        OUString aReplacement;
+        if (nParamCount == 3)
+        {
+            // A missing argument is not an empty string to replace the match.
+            if (IsMissing())
+                Pop();
+            else
+            {
+                aReplacement = GetString().getString();
+                bReplacement = true;
+            }
+        }
+
+        OUString aExpression = GetString().getString();
+        OUString aText = GetString().getString();
+
+        if (nGlobalError != FormulaError::NONE)
+        {
+            PushError( nGlobalError);
+            return;
+        }
+
+        sal_Int32 nPos = 0;
+        sal_Int32 nEndPos = aText.getLength();
+        utl::SearchParam aParam( aExpression, utl::SearchParam::SearchType::Regexp);
+        css::util::SearchResult aResult;
+        utl::TextSearch aSearch( aParam, *ScGlobal::pCharClass);
+        const bool bMatch = aSearch.SearchForward( aText, &nPos, &nEndPos, &aResult);
+        if (!bMatch)
+            PushNoValue();
+        else
+        {
+            assert(aResult.subRegExpressions >= 1);
+            if (!bReplacement)
+                PushString( aText.copy( aResult.startOffset[0], aResult.endOffset[0] - aResult.startOffset[0]));
+            else
+            {
+                /* TODO: global replacement of multiple occurrences, introduce
+                 * extra parameter with flag 'g'? Loop over positions after
+                 * nEndPos until none left? How to keep the offsets in sync
+                 * after replacement? That should be done by
+                 * ReplaceBackReferences(). */
+                aSearch.ReplaceBackReferences( aReplacement, aText, aResult);
+                PushString( aReplacement);
+            }
+        }
+    }
+}
+
 void ScInterpreter::ScMid()
 {
     if ( MustHaveParamCount( GetByte(), 3 ) )
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index f5ca631979e6..d044295ee4d3 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4215,6 +4215,7 @@ StackVar ScInterpreter::Interpret()
                 case ocMid              : ScMid();                      break;
                 case ocText             : ScText();                     break;
                 case ocSubstitute       : ScSubstitute();               break;
+                case ocRegex            : ScRegex();                    break;
                 case ocRept             : ScRept();                     break;
                 case ocConcat           : ScConcat();                   break;
                 case ocConcat_MS        : ScConcat_MS();                break;
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 644a806dc8f3..2af552318efc 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -639,7 +639,8 @@ static const XclFunctionInfo saFuncTable_OOoLO[] =
     EXC_FUNCENTRY_OOO( ocForecast_ETS_MUL, 3,  6,  0,  "ORG.LIBREOFFICE.FORECAST.ETS.MULT" ),
     EXC_FUNCENTRY_OOO( ocForecast_ETS_PIM, 3,  7,  0,  "ORG.LIBREOFFICE.FORECAST.ETS.PI.MULT" ),
     EXC_FUNCENTRY_OOO( ocForecast_ETS_STM, 3,  6,  0,  "ORG.LIBREOFFICE.FORECAST.ETS.STAT.MULT" ),
-    EXC_FUNCENTRY_OOO( ocRoundSig,      2,  2,  0,  "ORG.LIBREOFFICE.ROUNDSIG" )
+    EXC_FUNCENTRY_OOO( ocRoundSig,      2,  2,  0,  "ORG.LIBREOFFICE.ROUNDSIG" ),
+    EXC_FUNCENTRY_OOO( ocRegex,         2,  3,  0,  "ORG.LIBREOFFICE.REGEX" )
 };
 
 #undef EXC_FUNCENTRY_OOO_IBR
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 57bfb35b65eb..be09c75a6251 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -910,7 +910,8 @@ static const FunctionData saFuncTableOOoLO[] =
     { "ORG.LIBREOFFICE.FORECAST.ETS.MULT",      "ORG.LIBREOFFICE.FORECAST.ETS.MULT",      NOID,   NOID,   3,  6,  V, { VR, VA, VR }, FuncFlags::MACROCALL_NEW },
     { "ORG.LIBREOFFICE.FORECAST.ETS.PI.MULT",   "ORG.LIBREOFFICE.FORECAST.ETS.PI.MULT",   NOID,   NOID,   4,  7,  V, { VR, VA, VR }, FuncFlags::MACROCALL_NEW },
     { "ORG.LIBREOFFICE.FORECAST.ETS.STAT.MULT", "ORG.LIBREOFFICE.FORECAST.ETS.STAT.MULT", NOID,   NOID,   3,  6,  V, { VR, VA, VR }, FuncFlags::MACROCALL_NEW },
-    { "ORG.LIBREOFFICE.ROUNDSIG",  "ORG.LIBREOFFICE.ROUNDSIG",  NOID,   NOID,   2,  2,  V, { RX }, FuncFlags::MACROCALL_NEW }
+    { "ORG.LIBREOFFICE.ROUNDSIG",   "ORG.LIBREOFFICE.ROUNDSIG", NOID, NOID,  2,  2,  V, { RX }, FuncFlags::MACROCALL_NEW },
+    { "ORG.LIBREOFFICE.REGEX",      "ORG.LIBREOFFICE.REGEX", NOID, NOID,  2,  3,  V, { RX }, FuncFlags::MACROCALL_NEW }
 
 };