tdf#113991 xls/xlsx export: no relative sheet in named ranges

MSO apparently requires absolute sheet references
in named ranges.

XLS and XLSX compose the range in entirely different ways.
XLS doesn't use the sSymbol result at all, while XLSX is
entirely dependent upon it.

XLS doesn't require the range to be 3D in order to be absolute,
but XLSX and ODS do require that, so it only makes sense to ensure
that 3D is always enabled when the range changes to absolute
in order to enhance inter-operability.

GLOBAL names will be handled in a followup commit.

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

1 files changed, 58 insertions, 5 deletions

diff --git a/sc/source/filter/excel/xename.cxx b/sc/source/filter/excel/xename.cxx
index 84ab353ee2d8..c55840e2532e 100644
--- a/sc/source/filter/excel/xename.cxx
+++ b/sc/source/filter/excel/xename.cxx
@@ -23,6 +23,7 @@
 
 #include <document.hxx>
 #include <rangenam.hxx>
+#include <tokenarray.hxx>
 #include <dbdata.hxx>
 #include <xehelper.hxx>
 #include <xelink.hxx>
@@ -334,6 +335,41 @@ void XclExpName::WriteBody( XclExpStream& rStrm )
         mxTokArr->WriteArray( rStrm );  // token array without size
 }
 
+void lcl_EnsureAbs3DToken( const SCTAB nTab, formula::FormulaToken* pTok )
+{
+    if ( !pTok || ( pTok->GetType() != formula::svSingleRef && pTok->GetType() != formula::svDoubleRef ) )
+        return;
+
+    ScSingleRefData* pRef1 = pTok->GetSingleRef();
+    if ( !pRef1 )
+        return;
+
+    ScSingleRefData* pRef2 = nullptr;
+    if ( pTok->GetType() == formula::svDoubleRef )
+        pRef2 = pTok->GetSingleRef2();
+
+    if ( pRef1->IsTabRel() || !pRef1->IsFlag3D() )
+    {
+            if ( pRef1->IsTabRel() && nTab != SCTAB_GLOBAL )
+                pRef1->SetAbsTab( nTab + pRef1->Tab() );  //XLS requirement
+            if ( !pRef1->IsTabRel() )
+            {
+                pRef1->SetFlag3D( true );  //XLSX requirement
+                if ( pRef2 && !pRef2->IsTabRel() )
+                    pRef2->SetFlag3D( pRef2->Tab() != pRef1->Tab() );
+            }
+    }
+
+    if ( pRef2 && pRef2->IsTabRel() && !pRef1->IsTabRel() )
+    {
+        if ( nTab != SCTAB_GLOBAL )
+        {
+            pRef2->SetAbsTab( nTab + pRef2->Tab() );
+            pRef2->SetFlag3D( pRef2->Tab() != pRef1->Tab() );
+        }
+    }
+}
+
 XclExpNameManagerImpl::XclExpNameManagerImpl( const XclExpRoot& rRoot ) :
     XclExpRoot( rRoot ),
     mnFirstUserIdx( 0 )
@@ -543,12 +579,29 @@ sal_uInt16 XclExpNameManagerImpl::CreateName( SCTAB nTab, const ScRangeData& rRa
         This may cause recursive creation of other defined names. */
     if( const ScTokenArray* pScTokArr = const_cast< ScRangeData& >( rRangeData ).GetCode() )
     {
-        XclTokenArrayRef xTokArr = GetFormulaCompiler().CreateFormula( EXC_FMLATYPE_NAME, *pScTokArr );
-        xName->SetTokenArray( xTokArr );
-
+        XclTokenArrayRef xTokArr;
         OUString sSymbol;
-        rRangeData.GetSymbol( sSymbol, ((GetOutput() == EXC_OUTPUT_BINARY) ?
-                    formula::FormulaGrammar::GRAM_ENGLISH_XL_A1 : formula::FormulaGrammar::GRAM_OOXML));
+        // MSO requires named ranges to have absolute sheet references
+        if ( rRangeData.HasType( ScRangeData::Type::AbsPos ) || rRangeData.HasType( ScRangeData::Type::AbsArea ) )
+        {
+            // Don't modify the actual document; use a temporary copy to create the export formulas.
+            std::unique_ptr<ScTokenArray> pTokenCopy( pScTokArr->Clone() );
+            lcl_EnsureAbs3DToken( nTab, pTokenCopy.get()->FirstToken() );
+
+            xTokArr = GetFormulaCompiler().CreateFormula( EXC_FMLATYPE_NAME, *pTokenCopy.get() );
+            if ( GetOutput() != EXC_OUTPUT_BINARY )
+            {
+                ScCompiler aComp( &GetDocRef(), rRangeData.GetPos(), *pTokenCopy.get(), formula::FormulaGrammar::GRAM_OOXML );
+                aComp.CreateStringFromTokenArray( sSymbol );
+            }
+        }
+        else
+        {
+            xTokArr = GetFormulaCompiler().CreateFormula( EXC_FMLATYPE_NAME, *pScTokArr );
+            rRangeData.GetSymbol( sSymbol, ((GetOutput() == EXC_OUTPUT_BINARY) ?
+                     formula::FormulaGrammar::GRAM_ENGLISH_XL_A1 : formula::FormulaGrammar::GRAM_OOXML));
+        }
+        xName->SetTokenArray( xTokArr );
         xName->SetSymbol( sSymbol );
 
         /*  Try to replace by existing built-in name - complete token array is