tdf#113991 xls/xlsx export: emulate relative GLOBAL named ranges

The MS formats apparently require absolute sheet references in
named ranges. So, it can't directly use LO's GLOBAL relative ranges.
Instead, attempt to emulate that by duplicating the GLOBAL name
directly into every sheet. If the name already exists, then
(in theory) it would have overridden the global one anyway,
so just drop it in that case.

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

4 files changed, 59 insertions, 12 deletions

diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index cc5177f53980..046ce15783d8 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -3383,6 +3383,14 @@ void ScExportTest::testRelativeNamedExpressionsXLS()
     aPos = ScAddress(5,5,1);
     CPPUNIT_ASSERT_EQUAL(18.0, rDoc.GetValue(aPos));
     ASSERT_FORMULA_EQUAL(rDoc, aPos, "SUM(test_conflict)", nullptr);
+    // Sheet2:H3
+    aPos = ScAddress(7,2,1);
+    CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(aPos));
+    ASSERT_FORMULA_EQUAL(rDoc, aPos, "single_global_A3", nullptr);
+    // Sheet2:H6
+    aPos = ScAddress(7,5,1);
+    CPPUNIT_ASSERT_EQUAL(75.0, rDoc.GetValue(aPos));
+    ASSERT_FORMULA_EQUAL(rDoc, aPos, "SUM(A6:F6)", nullptr);
     xDocSh2->DoClose();
 }
 
diff --git a/sc/source/filter/excel/xeformula.cxx b/sc/source/filter/excel/xeformula.cxx
index 3cd1bd9a06bf..dc3297c45ee9 100644
--- a/sc/source/filter/excel/xeformula.cxx
+++ b/sc/source/filter/excel/xeformula.cxx
@@ -2089,7 +2089,7 @@ void XclExpFmlaCompImpl::ProcessDefinedName( const XclExpScToken& rTokData )
     SCTAB nTab = (nSheet < 0 ? SCTAB_GLOBAL : nSheet);
 
     XclExpNameManager& rNameMgr = GetNameManager();
-    sal_uInt16 nNameIdx = rNameMgr.InsertName(nTab, rTokData.mpScToken->GetIndex());
+    sal_uInt16 nNameIdx = rNameMgr.InsertName(nTab, rTokData.mpScToken->GetIndex(), GetCurrScTab());
     if( nNameIdx != 0 )
     {
         // global names always with tName token, local names dependent on config
diff --git a/sc/source/filter/excel/xename.cxx b/sc/source/filter/excel/xename.cxx
index d44246600241..62181ef8eee2 100644
--- a/sc/source/filter/excel/xename.cxx
+++ b/sc/source/filter/excel/xename.cxx
@@ -115,7 +115,7 @@ public:
     void                Initialize();
 
     /** Inserts the Calc name with the passed index and returns the Excel NAME index. */
-    sal_uInt16          InsertName( SCTAB nTab, sal_uInt16 nScNameIdx );
+    sal_uInt16          InsertName( SCTAB nTab, sal_uInt16 nScNameIdx, SCTAB nCurrTab );
 
     /** Inserts a new built-in defined name. */
     sal_uInt16          InsertBuiltInName( sal_Unicode cBuiltIn, const XclTokenArrayRef& xTokArr, SCTAB nScTab, const ScRangeList& aRangeList );
@@ -335,14 +335,17 @@ void XclExpName::WriteBody( XclExpStream& rStrm )
         mxTokArr->WriteArray( rStrm );  // token array without size
 }
 
-void lcl_EnsureAbs3DToken( const SCTAB nTab, formula::FormulaToken* pTok )
+/** Returns true (needed fixing) if FormulaToken was not absolute and 3D.
+    So, regardless of whether the fix was successful or not, true is still returned since a fix was required.*/
+bool lcl_EnsureAbs3DToken( const SCTAB nTab, formula::FormulaToken* pTok, const bool bFix = true )
 {
+    bool bFixRequired = false;
     if ( !pTok || ( pTok->GetType() != formula::svSingleRef && pTok->GetType() != formula::svDoubleRef ) )
-        return;
+        return bFixRequired;
 
     ScSingleRefData* pRef1 = pTok->GetSingleRef();
     if ( !pRef1 )
-        return;
+        return bFixRequired;
 
     ScSingleRefData* pRef2 = nullptr;
     if ( pTok->GetType() == formula::svDoubleRef )
@@ -350,6 +353,9 @@ void lcl_EnsureAbs3DToken( const SCTAB nTab, formula::FormulaToken* pTok )
 
     if ( pRef1->IsTabRel() || !pRef1->IsFlag3D() )
     {
+        bFixRequired = true;
+        if ( bFix )
+        {
             if ( pRef1->IsTabRel() && nTab != SCTAB_GLOBAL )
                 pRef1->SetAbsTab( nTab + pRef1->Tab() );  //XLS requirement
             if ( !pRef1->IsTabRel() )
@@ -358,16 +364,19 @@ void lcl_EnsureAbs3DToken( const SCTAB nTab, formula::FormulaToken* pTok )
                 if ( pRef2 && !pRef2->IsTabRel() )
                     pRef2->SetFlag3D( pRef2->Tab() != pRef1->Tab() );
             }
+        }
     }
 
     if ( pRef2 && pRef2->IsTabRel() && !pRef1->IsTabRel() )
     {
-        if ( nTab != SCTAB_GLOBAL )
+        bFixRequired = true;
+        if ( bFix && nTab != SCTAB_GLOBAL )
         {
             pRef2->SetAbsTab( nTab + pRef2->Tab() );
             pRef2->SetFlag3D( pRef2->Tab() != pRef1->Tab() );
         }
     }
+    return bFixRequired;
 }
 
 XclExpNameManagerImpl::XclExpNameManagerImpl( const XclExpRoot& rRoot ) :
@@ -383,7 +392,7 @@ void XclExpNameManagerImpl::Initialize()
     CreateUserNames();
 }
 
-sal_uInt16 XclExpNameManagerImpl::InsertName( SCTAB nTab, sal_uInt16 nScNameIdx )
+sal_uInt16 XclExpNameManagerImpl::InsertName( SCTAB nTab, sal_uInt16 nScNameIdx, SCTAB nCurrTab )
 {
     sal_uInt16 nNameIdx = 0;
     const ScRangeData* pData = nullptr;
@@ -393,7 +402,15 @@ sal_uInt16 XclExpNameManagerImpl::InsertName( SCTAB nTab, sal_uInt16 nScNameIdx
 
     if (pData)
     {
-        nNameIdx = FindNamedExp( nTab, pData->GetName() );
+        bool bEmulateGlobalRelativeTable = false;
+        const ScTokenArray* pCode = pData->GetCode();
+        if ( pCode
+            && nTab == SCTAB_GLOBAL
+            && (pData->HasType( ScRangeData::Type::AbsPos ) || pData->HasType( ScRangeData::Type::AbsArea )) )
+        {
+            bEmulateGlobalRelativeTable = lcl_EnsureAbs3DToken( nTab, pCode->FirstToken(), /*bFix=*/false );
+        }
+        nNameIdx = FindNamedExp( bEmulateGlobalRelativeTable ? nCurrTab : nTab, pData->GetName() );
         if (!nNameIdx)
             nNameIdx = CreateName(nTab, *pData);
     }
@@ -691,13 +708,24 @@ void XclExpNameManagerImpl::CreateBuiltInNames()
 
 void XclExpNameManagerImpl::CreateUserNames()
 {
+    std::vector<ScRangeData*> vEmulateAsLocalRange;
     const ScRangeName& rNamedRanges = GetNamedRanges();
     ScRangeName::const_iterator itr = rNamedRanges.begin(), itrEnd = rNamedRanges.end();
     for (; itr != itrEnd; ++itr)
     {
         // skip definitions of shared formulas
         if (!FindNamedExp(SCTAB_GLOBAL, itr->second->GetName()))
-            CreateName(SCTAB_GLOBAL, *itr->second);
+        {
+            const ScTokenArray* pCode = itr->second->GetCode();
+            if ( pCode
+                && (itr->second->HasType( ScRangeData::Type::AbsPos ) || itr->second->HasType( ScRangeData::Type::AbsArea ))
+                && lcl_EnsureAbs3DToken( SCTAB_GLOBAL, pCode->FirstToken(), /*bFix=*/false ) )
+            {
+                vEmulateAsLocalRange.emplace_back(itr->second.get());
+            }
+            else
+                CreateName(SCTAB_GLOBAL, *itr->second);
+        }
     }
     //look at sheets containing local range names
     ScRangeName::TabNameCopyMap rLocalNames;
@@ -714,6 +742,17 @@ void XclExpNameManagerImpl::CreateUserNames()
                 CreateName(tabIt->first, *itr->second);
         }
     }
+
+    // Emulate relative global variables by creating a copy in each local range.
+    // Creating AFTER true local range names so that conflicting global names will be ignored.
+    for ( SCTAB nTab = 0; nTab < GetDoc().GetTableCount(); ++nTab )
+    {
+        for ( auto rangeDataItr : vEmulateAsLocalRange )
+        {
+            if ( !FindNamedExp(nTab, rangeDataItr->GetName()) )
+                CreateName(nTab, *rangeDataItr );
+        }
+    }
 }
 
 XclExpNameManager::XclExpNameManager( const XclExpRoot& rRoot ) :
@@ -731,9 +770,9 @@ void XclExpNameManager::Initialize()
     mxImpl->Initialize();
 }
 
-sal_uInt16 XclExpNameManager::InsertName( SCTAB nTab, sal_uInt16 nScNameIdx )
+sal_uInt16 XclExpNameManager::InsertName( SCTAB nTab, sal_uInt16 nScNameIdx, SCTAB nCurrTab )
 {
-    return mxImpl->InsertName( nTab, nScNameIdx );
+    return mxImpl->InsertName( nTab, nScNameIdx, nCurrTab );
 }
 
 sal_uInt16 XclExpNameManager::InsertBuiltInName( sal_Unicode cBuiltIn, const ScRange& rRange )
diff --git a/sc/source/filter/inc/xename.hxx b/sc/source/filter/inc/xename.hxx
index e1a3ba923829..a0bb336bb3e7 100644
--- a/sc/source/filter/inc/xename.hxx
+++ b/sc/source/filter/inc/xename.hxx
@@ -40,7 +40,7 @@ public:
     void                Initialize();
 
     /** Inserts the Calc name with the passed index and returns the Excel NAME index. */
-    sal_uInt16          InsertName( SCTAB nTab, sal_uInt16 nScNameIdx );
+    sal_uInt16          InsertName( SCTAB nTab, sal_uInt16 nScNameIdx, SCTAB nCurrTab );
 
     /** Inserts a new built-in defined name, referring to the passed sheet range. */
     sal_uInt16          InsertBuiltInName( sal_Unicode cBuiltIn, const ScRange& rRange );