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

4 files changed, 46 insertions, 11 deletions

diff --git a/sc/source/filter/excel/xecontent.cxx b/sc/source/filter/excel/xecontent.cxx
index 9cb6248fdd57..af1bfae9a03d 100644
--- a/sc/source/filter/excel/xecontent.cxx
+++ b/sc/source/filter/excel/xecontent.cxx
@@ -410,10 +410,14 @@ XclExpHyperlink::XclExpHyperlink( const XclExpRoot& rRoot, const SvxURLField& rU
     {
         OUString aTextMark( rUrl.copy( 1 ) );
 
-        sal_Int32 nSepPos = aTextMark.indexOf( '.' );
+        sal_Int32 nSepPos = aTextMark.lastIndexOf( '.' );
         if(nSepPos != -1)
-        {
             aTextMark = aTextMark.replaceAt( nSepPos, 1, "!" );
+        else
+            nSepPos = aTextMark.lastIndexOf( '!' );
+
+        if(nSepPos != -1)
+        {
             OUString aSheetName( aTextMark.copy(0, nSepPos));
 
             if ( aSheetName.indexOf(' ') != -1 && aSheetName[0] != '\'')
diff --git a/sc/source/filter/excel/xicontent.cxx b/sc/source/filter/excel/xicontent.cxx
index 6b0f688a8f9e..1e009ba91271 100644
--- a/sc/source/filter/excel/xicontent.cxx
+++ b/sc/source/filter/excel/xicontent.cxx
@@ -332,9 +332,24 @@ OUString XclImpHyperlink::ReadEmbeddedData( XclImpStream& rStrm )
         if( xTextMark.get() )
         {
             if( xLongName->isEmpty() )
-                xTextMark.reset( new OUString( xTextMark->replace( '!', '.' ) ) );
-            xLongName.reset( new OUString( *xLongName + "#" ) );
-            xLongName.reset( new OUString( *xLongName + *xTextMark  ) );
+            {
+                sal_Int32 nSepPos = xTextMark->lastIndexOf( '!' );
+                if( nSepPos > 0 )
+                {
+                    // 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 < xTextMark->getLength() - 1)
+                    {
+                        ScRange aRange;
+                        if ((aRange.ParseAny( xTextMark->copy( nSepPos + 1 ), nullptr,
+                                        formula::FormulaGrammar::CONV_XL_R1C1) & SCA_VALID) != SCA_VALID)
+                            xTextMark.reset( new OUString( xTextMark->replaceAt( nSepPos, 1, OUString( '.' ))));
+                    }
+                }
+            }
+            xLongName.reset( new OUString( *xLongName + "#" + *xTextMark ) );
         }
         return( *xLongName );
     }
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 );