tdf#158185: fix Excel's Range.Find and Range.Replace wildcard recognition

VBAToRegexp is wrong, using Basic wildcards. The functionality introduced
in tdf#72196 matches the required wildcard repertoire.

This introduces a new WildcardEscapeCharacter property to SearchDescriptor
service. Interestingly, the default value there was backslash.

TODO: also fix Range.AutoFilter, and drop VBAToRegexp. To do that, a new
property is needed in service SheetFilterDescriptor, to use wildcards,
in addition to "UseRegularExpressions" property.

Change-Id: Idebcf00d4b7376e5d7feaf8ae800fb19d05689d4
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/159391
Tested-by: Jenkins
Reviewed-by: Mike Kaganski <mike.kaganski@collabora.com>

5 files changed, 29 insertions, 7 deletions

diff --git a/include/svl/srchitem.hxx b/include/svl/srchitem.hxx
index 516677e6e75b..c51d2d752e02 100644
--- a/include/svl/srchitem.hxx
+++ b/include/svl/srchitem.hxx
@@ -138,6 +138,9 @@ public:
     inline  bool            GetWildcard() const;
             void            SetWildcard( bool bVal );
 
+    inline  sal_Int32       GetWildcardEscapeCharacter() const;
+    inline  void            SetWildcardEscapeCharacter(sal_Int32 val);
+
             bool            GetPattern() const { return m_bPattern; }
             void            SetPattern(bool bNewPattern) { m_bPattern = bNewPattern; }
 
@@ -247,6 +250,16 @@ bool SvxSearchItem::GetWildcard() const
     return m_aSearchOpt.AlgorithmType2 == css::util::SearchAlgorithms2::WILDCARD ;
 }
 
+sal_Int32 SvxSearchItem::GetWildcardEscapeCharacter() const
+{
+    return m_aSearchOpt.WildcardEscapeCharacter;
+}
+
+void SvxSearchItem::SetWildcardEscapeCharacter(sal_Int32 val)
+{
+    m_aSearchOpt.WildcardEscapeCharacter = val;
+}
+
 bool SvxSearchItem::IsLEVRelaxed() const
 {
     return 0 != (m_aSearchOpt.searchFlag & css::util::SearchFlags::LEV_RELAXED);
diff --git a/offapi/com/sun/star/util/SearchDescriptor.idl b/offapi/com/sun/star/util/SearchDescriptor.idl
index 7a50b3892f09..c70b81fcdcbd 100644
--- a/offapi/com/sun/star/util/SearchDescriptor.idl
+++ b/offapi/com/sun/star/util/SearchDescriptor.idl
@@ -123,6 +123,12 @@ published service SearchDescriptor
      */
     [optional, property] boolean SearchWildcard;
 
+    /** Specifices the character used to escape special characters in wildcards.
+
+        @since LibreOffice 24.2
+     */
+    [optional, property] long WildcardEscapeCharacter;
+
 };
 
 
diff --git a/sc/inc/unonames.hxx b/sc/inc/unonames.hxx
index 5b8c65346764..5bc0fb2752dc 100644
--- a/sc/inc/unonames.hxx
+++ b/sc/inc/unonames.hxx
@@ -395,6 +395,7 @@ inline constexpr OUString SC_UNO_SRCHTYPE             = u"SearchType"_ustr;
 inline constexpr OUString SC_UNO_SRCHWORDS            = u"SearchWords"_ustr;
 inline constexpr OUString SC_UNO_SRCHFILTERED         = u"SearchFiltered"_ustr;
 inline constexpr OUString SC_UNO_SRCHFORMATTED        = u"SearchFormatted"_ustr;
+inline constexpr OUString SC_UNO_SRCHWCESCCHAR        = u"WildcardEscapeCharacter"_ustr;
 
 //  old (5.2) property names for page styles - for compatibility only!
 inline constexpr OUString OLD_UNO_PAGE_BACKCOLOR      = u"BackgroundColor"_ustr;
diff --git a/sc/source/ui/unoobj/srchuno.cxx b/sc/source/ui/unoobj/srchuno.cxx
index 0ecb6a103c04..a5b59a330cb4 100644
--- a/sc/source/ui/unoobj/srchuno.cxx
+++ b/sc/source/ui/unoobj/srchuno.cxx
@@ -49,6 +49,7 @@ static std::span<const SfxItemPropertyMapEntry> lcl_GetSearchPropertyMap()
         { SC_UNO_SRCHSTYLES,   0,      cppu::UnoType<bool>::get(),       0, 0},
         { SC_UNO_SRCHTYPE,     0,      cppu::UnoType<sal_Int16>::get(), 0, 0}, // enum TableSearch is gone
         { SC_UNO_SRCHWORDS,    0,      cppu::UnoType<bool>::get(),       0, 0},
+        { SC_UNO_SRCHWCESCCHAR, 0,     cppu::UnoType<sal_Int32>::get(), 0, 0 },
     };
     return aSearchPropertyMap_Impl;
 }
@@ -144,6 +145,7 @@ void SAL_CALL ScCellSearchObj::setPropertyValue(
     else if (aPropertyName == SC_UNO_SRCHTYPE)   pSearchItem->SetCellType( static_cast<SvxSearchCellType>(ScUnoHelpFunctions::GetInt16FromAny( aValue )) );
     else if (aPropertyName == SC_UNO_SRCHFILTERED) pSearchItem->SetSearchFiltered( ScUnoHelpFunctions::GetBoolFromAny(aValue) );
     else if (aPropertyName == SC_UNO_SRCHFORMATTED) pSearchItem->SetSearchFormatted( ScUnoHelpFunctions::GetBoolFromAny(aValue) );
+    else if (aPropertyName == SC_UNO_SRCHWCESCCHAR) pSearchItem->SetWildcardEscapeCharacter( ScUnoHelpFunctions::GetInt32FromAny(aValue) );
 }
 
 uno::Any SAL_CALL ScCellSearchObj::getPropertyValue( const OUString& aPropertyName )
@@ -166,6 +168,7 @@ uno::Any SAL_CALL ScCellSearchObj::getPropertyValue( const OUString& aPropertyNa
     else if (aPropertyName == SC_UNO_SRCHTYPE)   aRet <<= static_cast<sal_Int16>(pSearchItem->GetCellType());
     else if (aPropertyName == SC_UNO_SRCHFILTERED) aRet <<= pSearchItem->IsSearchFiltered();
     else if (aPropertyName == SC_UNO_SRCHFORMATTED) aRet <<= pSearchItem->IsSearchFormatted();
+    else if (aPropertyName == SC_UNO_SRCHWCESCCHAR) aRet <<= pSearchItem->GetWildcardEscapeCharacter();
 
     return aRet;
 }
diff --git a/sc/source/ui/vba/vbarange.cxx b/sc/source/ui/vba/vbarange.cxx
index 842450b3ea0b..f1ce525daa2d 100644
--- a/sc/source/ui/vba/vbarange.cxx
+++ b/sc/source/ui/vba/vbarange.cxx
@@ -3108,7 +3108,6 @@ ScVbaRange::Replace( const OUString& What, const OUString& Replacement, const un
     // sanity check required params
     if ( What.isEmpty()  )
         throw uno::RuntimeException("Range::Replace, missing params" );
-    OUString sWhat = VBAToRegexp( What);
     // #TODO #FIXME SearchFormat & ReplacesFormat are not processed
     // What do we do about MatchByte... we don't seem to support that
     const SvxSearchItem& globalSearchOptions = ScGlobal::GetSearchItem();
@@ -3120,8 +3119,9 @@ ScVbaRange::Replace( const OUString& What, const OUString& Replacement, const un
         uno::Reference< util::XReplaceDescriptor > xDescriptor =
             xReplace->createReplaceDescriptor();
 
-        xDescriptor->setSearchString( sWhat);
-        xDescriptor->setPropertyValue( SC_UNO_SRCHREGEXP, uno::Any( true ) );
+        xDescriptor->setSearchString(What);
+        xDescriptor->setPropertyValue(SC_UNO_SRCHWILDCARD, uno::Any(true));
+        xDescriptor->setPropertyValue(SC_UNO_SRCHWCESCCHAR, uno::Any(sal_Int32('~')));
         xDescriptor->setReplaceString( Replacement);
         if ( LookAt.hasValue() )
         {
@@ -3215,8 +3215,6 @@ ScVbaRange::Find( const uno::Any& What, const uno::Any& After, const uno::Any& L
     else
         throw uno::RuntimeException("Range::Find, missing search-for-what param" );
 
-    OUString sSearch = VBAToRegexp( sWhat );
-
     const SvxSearchItem& globalSearchOptions = ScGlobal::GetSearchItem();
     SvxSearchItem newOptions( globalSearchOptions );
 
@@ -3224,8 +3222,9 @@ ScVbaRange::Find( const uno::Any& What, const uno::Any& After, const uno::Any& L
     if( xSearch.is() )
     {
         uno::Reference< util::XSearchDescriptor > xDescriptor = xSearch->createSearchDescriptor();
-        xDescriptor->setSearchString( sSearch );
-        xDescriptor->setPropertyValue( SC_UNO_SRCHREGEXP, uno::Any( true ) );
+        xDescriptor->setSearchString(sWhat);
+        xDescriptor->setPropertyValue(SC_UNO_SRCHWILDCARD, uno::Any(true));
+        xDescriptor->setPropertyValue(SC_UNO_SRCHWCESCCHAR, uno::Any(sal_Int32('~')));
 
         uno::Reference< excel::XRange > xAfterRange;
         uno::Reference< table::XCellRange > xStartCell;