Resolves: tdf#84713 do not substitute separator in R1C1 notation hyperlinks

During import, in hyperlinks all Sheet!xxx were converted to Sheet.xxx
to fit CalcA1 notation, but in this case Sheet!R1C1 was used and
Sheet.R1C1 is not a valid address notation, so the hyperlink didn't
work. Do not attempt to convert R1C1 notation, the hyperlink handler
does handle all known notations. On Excel export, handle that the
separator can be both, '.' and '!'.

Change-Id: I8428b2240912f42fd6789d249c90982127ee7c01

2 files changed, 22 insertions, 6 deletions

diff --git a/sc/source/filter/oox/worksheetbuffer.cxx b/sc/source/filter/oox/worksheetbuffer.cxx
index 00d789ca6d37..6c3a0dd12182 100644
--- a/sc/source/filter/oox/worksheetbuffer.cxx
+++ b/sc/source/filter/oox/worksheetbuffer.cxx
@@ -117,14 +117,22 @@ OUString WorksheetBuffer::getCalcSheetName( sal_Int32 nWorksheet ) const
 
 void WorksheetBuffer::convertSheetNameRef( OUString& sSheetNameRef ) const
 {
-    // convert '#SheetName!A1' to '#SheetName.A1'
     if( sSheetNameRef.startsWith("#") )
     {
         sal_Int32 nSepPos = sSheetNameRef.lastIndexOf( '!' );
         if( nSepPos > 0 )
         {
-            // replace the exclamation mark with a period
-            sSheetNameRef = sSheetNameRef.replaceAt( nSepPos, 1, OUString( '.' ) );
+            // Do not attempt to blindly convert '#SheetName!A1' to
+            // '#SheetName.A1', it can be #SheetName!R1C1 as well. Hyperlink
+            // handler has to handle all, but prefer '#SheetName.A1' if
+            // possible.
+            if (nSepPos < sSheetNameRef.getLength() - 1)
+            {
+                ScRange aRange;
+                if ((aRange.ParseAny( sSheetNameRef.copy( nSepPos + 1 ), nullptr,
+                                formula::FormulaGrammar::CONV_XL_R1C1) & SCA_VALID) != SCA_VALID)
+                    sSheetNameRef = sSheetNameRef.replaceAt( nSepPos, 1, OUString( '.' ) );
+            }
             // #i66592# convert sheet names that have been renamed on import
             OUString aSheetName = sSheetNameRef.copy( 1, nSepPos - 1 );
             OUString aCalcName = getCalcSheetName( aSheetName );
diff --git a/sc/source/filter/oox/worksheethelper.cxx b/sc/source/filter/oox/worksheethelper.cxx
index 3dcaf1cea886..bf2d009ebd60 100644
--- a/sc/source/filter/oox/worksheethelper.cxx
+++ b/sc/source/filter/oox/worksheethelper.cxx
@@ -992,14 +992,22 @@ OUString WorksheetGlobals::getHyperlinkUrl( const HyperlinkModel& rHyperlink ) c
         aUrlBuffer.append( '#' ).append( rHyperlink.maLocation );
     OUString aUrl = aUrlBuffer.makeStringAndClear();
 
-    // convert '#SheetName!A1' to '#SheetName.A1'
     if( aUrl.startsWith("#") )
     {
         sal_Int32 nSepPos = aUrl.lastIndexOf( '!' );
         if( nSepPos > 0 )
         {
-            // replace the exclamation mark with a period
-            aUrl = aUrl.replaceAt( nSepPos, 1, OUString( '.' ) );
+            // Do not attempt to blindly convert '#SheetName!A1' to
+            // '#SheetName.A1', it can be #SheetName!R1C1 as well. Hyperlink
+            // handler has to handle all, but prefer '#SheetName.A1' if
+            // possible.
+            if (nSepPos < aUrl.getLength() - 1)
+            {
+                ScRange aRange;
+                if ((aRange.ParseAny( aUrl.copy( nSepPos + 1 ), nullptr,
+                                formula::FormulaGrammar::CONV_XL_R1C1) & SCA_VALID) != SCA_VALID)
+                    aUrl = aUrl.replaceAt( nSepPos, 1, OUString( '.' ) );
+            }
             // #i66592# convert sheet names that have been renamed on import
             OUString aSheetName = aUrl.copy( 1, nSepPos - 1 );
             OUString aCalcName = getWorksheets().getCalcSheetName( aSheetName );