tdf#75372 rework sheet-copy names for converting global names to sheet-local

This during copying a sheet copies global names that from a copied
formula cell reference the copied sheet and converts the copied global
names to sheet-local names. References to the original sheet are updated
to point to the new sheet.

It works for names used in formula cells copied but needs enhancement to
pick up nested names, i.e. names used in names that do not reference the
sheet but the nested name does.

Change-Id: I1aa16cb28c9f7b3581bec289435492c21e6fcd73

9 files changed, 163 insertions, 58 deletions

diff --git a/sc/inc/column.hxx b/sc/inc/column.hxx
index aa1f4c1617d5..ba618d647d04 100644
--- a/sc/inc/column.hxx
+++ b/sc/inc/column.hxx
@@ -293,7 +293,8 @@ public:
 
     void CopyToColumn(
         sc::CopyToDocContext& rCxt, SCROW nRow1, SCROW nRow2, InsertDeleteFlags nFlags, bool bMarked,
-        ScColumn& rColumn, const ScMarkData* pMarkData = nullptr, bool bAsLink = false) const;
+        ScColumn& rColumn, const ScMarkData* pMarkData = nullptr, bool bAsLink = false,
+        bool bGlobalNamesToLocal = false ) const;
 
     void UndoToColumn(
         sc::CopyToDocContext& rCxt, SCROW nRow1, SCROW nRow2, InsertDeleteFlags nFlags, bool bMarked,
diff --git a/sc/inc/global.hxx b/sc/inc/global.hxx
index 14f4b0871d47..d06d58ba1873 100644
--- a/sc/inc/global.hxx
+++ b/sc/inc/global.hxx
@@ -241,6 +241,10 @@ const int SC_CLONECELL_NOCAPTION        = 0x0004;
 /** If set, absolute refs will not transformed to external references */
 const int SC_CLONECELL_NOMAKEABS_EXTERNAL = 0x0008;
 
+/** If set, global named expressions will be converted to sheet-local named
+    expressions. */
+const int SC_CLONECELL_NAMES_TO_LOCAL   = 0x0010;
+
 #ifndef DELETEZ
 #define DELETEZ(pPtr) { delete pPtr; pPtr = 0; }
 #endif
diff --git a/sc/inc/table.hxx b/sc/inc/table.hxx
index 6fac2cf05171..a8b30635fb4d 100644
--- a/sc/inc/table.hxx
+++ b/sc/inc/table.hxx
@@ -445,7 +445,8 @@ public:
     void CopyToTable(
         sc::CopyToDocContext& rCxt, SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
         InsertDeleteFlags nFlags, bool bMarked, ScTable* pDestTab,
-        const ScMarkData* pMarkData = nullptr, bool bAsLink = false, bool bColRowFlags = true );
+        const ScMarkData* pMarkData = nullptr, bool bAsLink = false, bool bColRowFlags = true,
+        bool bGlobalNamesToLocal = false );
 
     void UndoToTable(
         sc::CopyToDocContext& rCxt, SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
diff --git a/sc/inc/tokenarray.hxx b/sc/inc/tokenarray.hxx
index 18fc6e59b73e..b2b4edd896b3 100644
--- a/sc/inc/tokenarray.hxx
+++ b/sc/inc/tokenarray.hxx
@@ -141,6 +141,11 @@ public:
      */
     void AdjustSheetLocalNameReferences( SCTAB nOldTab, SCTAB nNewTab );
 
+    /** Returns true if the sheet nTab is referenced in code. Relative sheet
+        references are evaluated using nPosTab.
+     */
+    bool ReferencesSheet( SCTAB nTab, SCTAB nPosTab ) const;
+
     /**
      * Adjust all references in response to shifting of cells during cell
      * insertion and deletion.
diff --git a/sc/source/core/data/column.cxx b/sc/source/core/data/column.cxx
index 95411ebbb4eb..f7fa19f7d7e1 100644
--- a/sc/source/core/data/column.cxx
+++ b/sc/source/core/data/column.cxx
@@ -1372,6 +1372,7 @@ class CopyByCloneHandler
     InsertDeleteFlags mnCopyFlags;
 
     sc::StartListeningType meListenType;
+    int mnFormulaCellCloneFlags;
 
     void setDefaultAttrToDest(size_t nRow)
     {
@@ -1414,7 +1415,7 @@ class CopyByCloneHandler
         if (bForceFormula || bCloneFormula)
         {
             // Clone as formula cell.
-            ScFormulaCell* pCell = new ScFormulaCell(rSrcCell, mrDestCol.GetDoc(), aDestPos);
+            ScFormulaCell* pCell = new ScFormulaCell(rSrcCell, mrDestCol.GetDoc(), aDestPos, mnFormulaCellCloneFlags);
             pCell->SetDirtyVar();
             mrDestCol.SetFormulaCell(maDestPos, nRow, pCell, meListenType);
             setDefaultAttrToDest(nRow);
@@ -1485,13 +1486,14 @@ class CopyByCloneHandler
 
 public:
     CopyByCloneHandler(const ScColumn& rSrcCol, ScColumn& rDestCol, sc::ColumnBlockPosition* pDestPos,
-            InsertDeleteFlags nCopyFlags, svl::SharedStringPool* pSharedStringPool) :
+            InsertDeleteFlags nCopyFlags, svl::SharedStringPool* pSharedStringPool, bool bGlobalNamesToLocal) :
         mrSrcCol(rSrcCol),
         mrDestCol(rDestCol),
         mpDestPos(pDestPos),
         mpSharedStringPool(pSharedStringPool),
         mnCopyFlags(nCopyFlags),
-        meListenType(sc::SingleCellListening)
+        meListenType(sc::SingleCellListening),
+        mnFormulaCellCloneFlags(bGlobalNamesToLocal ? SC_CLONECELL_NAMES_TO_LOCAL : 0)
     {
         if (mpDestPos)
             maDestPos = *mpDestPos;
@@ -1624,7 +1626,7 @@ public:
 void ScColumn::CopyToColumn(
     sc::CopyToDocContext& rCxt,
     SCROW nRow1, SCROW nRow2, InsertDeleteFlags nFlags, bool bMarked, ScColumn& rColumn,
-    const ScMarkData* pMarkData, bool bAsLink) const
+    const ScMarkData* pMarkData, bool bAsLink, bool bGlobalNamesToLocal) const
 {
     if (bMarked)
     {
@@ -1682,7 +1684,7 @@ void ScColumn::CopyToColumn(
                 (pDocument->GetPool() != rColumn.pDocument->GetPool()) ?
                 &rColumn.pDocument->GetSharedStringPool() : nullptr;
             CopyByCloneHandler aFunc(*this, rColumn, rCxt.getBlockPosition(rColumn.nTab, rColumn.nCol), nFlags,
-                    pSharedStringPool);
+                    pSharedStringPool, bGlobalNamesToLocal);
             aFunc.setStartListening(rCxt.isStartListening());
             sc::ParseBlock(maCells.begin(), maCells, aFunc, nRow1, nRow2);
         }
diff --git a/sc/source/core/data/documen2.cxx b/sc/source/core/data/documen2.cxx
index 971ae283827c..0d7b3117bb5b 100644
--- a/sc/source/core/data/documen2.cxx
+++ b/sc/source/core/data/documen2.cxx
@@ -874,8 +874,9 @@ bool ScDocument::CopyTab( SCTAB nOldPos, SCTAB nNewPos, const ScMarkData* pOnlyM
     {
         SetNoListening( true );     // noch nicht bei CopyToTable/Insert
         sc::CopyToDocContext aCopyDocCxt(*this);
-        maTabs[nOldPos]->CopyToTable(aCopyDocCxt, 0, 0, MAXCOL, MAXROW, InsertDeleteFlags::ALL, (pOnlyMarked != nullptr),
-                                        maTabs[nNewPos], pOnlyMarked );
+        maTabs[nOldPos]->CopyToTable(aCopyDocCxt, 0, 0, MAXCOL, MAXROW, InsertDeleteFlags::ALL,
+                (pOnlyMarked != nullptr), maTabs[nNewPos], pOnlyMarked,
+                false /*bAsLink*/, true /*bColRowFlags*/, true /*bGlobalNamesToLocal*/ );
         maTabs[nNewPos]->SetTabBgColor(maTabs[nOldPos]->GetTabBgColor());
 
         SCTAB nDz = nNewPos - nOldPos;
diff --git a/sc/source/core/data/formulacell.cxx b/sc/source/core/data/formulacell.cxx
index 6b727437921f..1939f218c676 100644
--- a/sc/source/core/data/formulacell.cxx
+++ b/sc/source/core/data/formulacell.cxx
@@ -420,93 +420,133 @@ bool lcl_isReference(const FormulaToken& rToken)
         rToken.GetType() == svDoubleRef;
 }
 
-void adjustRangeName(formula::FormulaToken* pToken, ScDocument& rNewDoc, const ScDocument* pOldDoc, const ScAddress& aNewPos, const ScAddress& aOldPos)
+ScRangeData* copyRangeName( const ScRangeData* pOldRangeData, ScDocument& rNewDoc, const ScDocument* pOldDoc,
+        const ScAddress& rNewPos, const ScAddress& rOldPos, bool bGlobalNamesToLocal,
+        SCTAB nOldSheet, SCTAB & nNewSheet)
+{
+    ScAddress aRangePos( pOldRangeData->GetPos());
+    if (nOldSheet < 0 && !bGlobalNamesToLocal)
+    {
+        nNewSheet = -1;
+    }
+    else
+    {
+        nNewSheet = rNewPos.Tab();
+        aRangePos.SetTab( nNewSheet);
+    }
+    ScRangeData* pRangeData = new ScRangeData(*pOldRangeData, &rNewDoc, &aRangePos);
+    pRangeData->SetIndex(0);    // needed for insert to assign a new index
+    ScTokenArray* pRangeNameToken = pRangeData->GetCode();
+    bool bSameDoc = (rNewDoc.GetPool() == const_cast<ScDocument*>(pOldDoc)->GetPool());
+    if (bSameDoc && nNewSheet >= 0)
+    {
+        if (bGlobalNamesToLocal && nOldSheet < 0)
+        {
+            nOldSheet = rOldPos.Tab();
+            if (rNewPos.Tab() <= nOldSheet)
+                // Sheet was inserted before and references already updated.
+                ++nOldSheet;
+        }
+        pRangeNameToken->AdjustSheetLocalNameReferences( nOldSheet, nNewSheet);
+    }
+    if (!bSameDoc)
+    {
+        pRangeNameToken->ReadjustAbsolute3DReferences(pOldDoc, &rNewDoc, pRangeData->GetPos(), true);
+        pRangeNameToken->AdjustAbsoluteRefs(pOldDoc, rOldPos, rNewPos, true);
+    }
+
+    bool bInserted;
+    if (nNewSheet < 0)
+        bInserted = rNewDoc.GetRangeName()->insert(pRangeData);
+    else
+        bInserted = rNewDoc.GetRangeName(nNewSheet)->insert(pRangeData);
+
+    return bInserted ? pRangeData : nullptr;
+}
+
+void adjustRangeName(formula::FormulaToken* pToken, ScDocument& rNewDoc, const ScDocument* pOldDoc,
+        const ScAddress& rNewPos, const ScAddress& rOldPos, bool bGlobalNamesToLocal)
 {
     bool bSameDoc = (rNewDoc.GetPool() == const_cast<ScDocument*>(pOldDoc)->GetPool());
     SCTAB nOldSheet = pToken->GetSheet();
-    if (bSameDoc && (nOldSheet < 0 || nOldSheet != aOldPos.Tab()))
-        // Same doc and global name or sheet-local name on other sheet stays
-        // the same.
+    if (bSameDoc && ((nOldSheet < 0 && !bGlobalNamesToLocal) || (nOldSheet >= 0 && nOldSheet != rOldPos.Tab())))
+        // Same doc and global name, if not copied to local name, or
+        // sheet-local name on other sheet stays the same.
         return;
 
+    SAL_WARN_IF( !bSameDoc && nOldSheet >= 0 && nOldSheet != rOldPos.Tab(),
+            "sc.core", "adjustRangeName - sheet-local name was on other sheet in other document");
+    /* TODO: can we do something about that? e.g. loop over sheets? */
+
     OUString aRangeName;
     int nOldIndex = pToken->GetIndex();
     ScRangeData* pOldRangeData = nullptr;
 
-    //search the name of the RangeName
+    // Search the name of the RangeName.
     if (nOldSheet >= 0)
     {
+        // XXX bGlobalNamesToLocal is also a synonym for copied sheet.
+        if (bGlobalNamesToLocal && bSameDoc && rNewPos.Tab() <= rOldPos.Tab())
+            // Sheet was already inserted before old position.
+            ++nOldSheet;
+
         const ScRangeName* pRangeName = pOldDoc->GetRangeName(nOldSheet);
         pOldRangeData = pRangeName ? pRangeName->findByIndex(nOldIndex) : nullptr;
         if (!pOldRangeData)
-            return;     //might be an error in the formula array
+            return;     // might be an error in the formula array
         aRangeName = pOldRangeData->GetUpperName();
     }
     else
     {
         pOldRangeData = pOldDoc->GetRangeName()->findByIndex(nOldIndex);
         if (!pOldRangeData)
-            return;     //might be an error in the formula array
+            return;     // might be an error in the formula array
         aRangeName = pOldRangeData->GetUpperName();
     }
 
-    SAL_WARN_IF( !bSameDoc && nOldSheet >= 0 && nOldSheet != aOldPos.Tab(),
-            "sc.core", "adjustRangeName - sheet-local name was on other sheet in other document");
-    /* TODO: can we do something about that? e.g. loop over sheets? */
-
-    //find corresponding range name in new document
-    //first search for local range name then global range names
-    SCTAB nNewSheet = aNewPos.Tab();
+    // Find corresponding range name in new document.
+    // First search for local range name then global range names.
+    SCTAB nNewSheet = rNewPos.Tab();
     ScRangeName* pRangeName = rNewDoc.GetRangeName(nNewSheet);
     ScRangeData* pRangeData = nullptr;
-    //search local range names
+    // Search local range names.
     if (pRangeName)
     {
         pRangeData = pRangeName->findByUpperName(aRangeName);
     }
-    //search global range names
-    if (!pRangeData)
+    // Search global range names.
+    if (!pRangeData && !bGlobalNamesToLocal)
     {
         nNewSheet = -1;
         pRangeName = rNewDoc.GetRangeName();
         if (pRangeName)
             pRangeData = pRangeName->findByUpperName(aRangeName);
     }
-    //if no range name was found copy it
+    // If no range name was found copy it.
     if (!pRangeData)
     {
-        ScAddress aRangePos( pOldRangeData->GetPos());
-        if (nOldSheet < 0)
-        {
-            nNewSheet = -1;
-        }
-        else
+        if (nOldSheet < 0 && bGlobalNamesToLocal)
         {
-            nNewSheet = aNewPos.Tab();
-            aRangePos.SetTab( nNewSheet);
-        }
-        pRangeData = new ScRangeData(*pOldRangeData, &rNewDoc, &aRangePos);
-        pRangeData->SetIndex(0);    // needed for insert to assign a new index
-        ScTokenArray* pRangeNameToken = pRangeData->GetCode();
-        if (bSameDoc && nNewSheet >= 0)
-        {
-            pRangeNameToken->AdjustSheetLocalNameReferences( nOldSheet, nNewSheet);
-        }
-        if (!bSameDoc)
-        {
-            pRangeNameToken->ReadjustAbsolute3DReferences(pOldDoc, &rNewDoc, pRangeData->GetPos(), true);
-            pRangeNameToken->AdjustAbsoluteRefs(pOldDoc, aOldPos, aNewPos, true);
+            // Copy only global names to local that reference the old sheet.
+            SCTAB nOldTab = rOldPos.Tab();
+            if (bSameDoc && rNewPos.Tab() <= nOldTab)
+            {
+                // Sheet was inserted before old position, references were
+                // already updated but rOldPos points to the old position,
+                // adjust to look for references.
+                ++nOldTab;
+            }
+            if (!pOldRangeData->GetCode()->ReferencesSheet( nOldTab, pOldRangeData->GetPos().Tab()))
+                return;
         }
 
-        bool bInserted;
-        if (nNewSheet < 0)
-            bInserted = rNewDoc.GetRangeName()->insert(pRangeData);
-        else
-            bInserted = rNewDoc.GetRangeName(nNewSheet)->insert(pRangeData);
-        if (!bInserted)
+        // Also may modify nNewSheet to be set below at the end.
+        pRangeData = copyRangeName( pOldRangeData, rNewDoc, pOldDoc, rNewPos, rOldPos, bGlobalNamesToLocal,
+                nOldSheet, nNewSheet);
+
+        if (!pRangeData)
         {
-            //if this happened we have a real problem
-            pRangeData = nullptr;
+            // If this happened we have a real problem.
             pToken->SetIndex(0);
             OSL_FAIL("inserting the range name should not fail");
             return;
@@ -918,12 +958,13 @@ ScFormulaCell::ScFormulaCell( const ScFormulaCell& rCell, ScDocument& rDoc, cons
     {
         if (!pDocument->IsClipboardSource() || aPos.Tab() != rCell.aPos.Tab())
         {
+            bool bGlobalNamesToLocal = ((nCloneFlags & SC_CLONECELL_NAMES_TO_LOCAL) != 0);
             formula::FormulaToken* pToken = nullptr;
             while((pToken = pCode->GetNextName())!= nullptr)
             {
                 OpCode eOpCode = pToken->GetOpCode();
                 if (eOpCode == ocName)
-                    adjustRangeName(pToken, rDoc, rCell.pDocument, aPos, rCell.aPos);
+                    adjustRangeName(pToken, rDoc, rCell.pDocument, aPos, rCell.aPos, bGlobalNamesToLocal);
                 else if (eOpCode == ocDBArea || eOpCode == ocTableRef)
                     adjustDBRange(pToken, rDoc, rCell.pDocument);
             }
diff --git a/sc/source/core/data/table2.cxx b/sc/source/core/data/table2.cxx
index e83ecfe5f30a..3d36d4d2c37e 100644
--- a/sc/source/core/data/table2.cxx
+++ b/sc/source/core/data/table2.cxx
@@ -1075,7 +1075,7 @@ void ScTable::StartListeningFormulaCells(
 void ScTable::CopyToTable(
     sc::CopyToDocContext& rCxt, SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
     InsertDeleteFlags nFlags, bool bMarked, ScTable* pDestTab, const ScMarkData* pMarkData,
-    bool bAsLink, bool bColRowFlags )
+    bool bAsLink, bool bColRowFlags, bool bGlobalNamesToLocal )
 {
     if (!ValidColRow(nCol1, nRow1) || !ValidColRow(nCol2, nRow2))
         return;
@@ -1086,7 +1086,7 @@ void ScTable::CopyToTable(
                 ~InsertDeleteFlags( InsertDeleteFlags::NOTE | InsertDeleteFlags::ADDNOTES));
         for (SCCOL i = nCol1; i <= nCol2; i++)
             aCol[i].CopyToColumn(rCxt, nRow1, nRow2, nTempFlags, bMarked,
-                                pDestTab->aCol[i], pMarkData, bAsLink);
+                                pDestTab->aCol[i], pMarkData, bAsLink, bGlobalNamesToLocal);
     }
 
     if (!bColRowFlags)      // Column widths/Row heights/Flags
diff --git a/sc/source/core/tool/token.cxx b/sc/source/core/tool/token.cxx
index f5e32879120b..a8f27bf77293 100644
--- a/sc/source/core/tool/token.cxx
+++ b/sc/source/core/tool/token.cxx
@@ -2523,6 +2523,56 @@ void ScTokenArray::AdjustSheetLocalNameReferences( SCTAB nOldTab, SCTAB nNewTab
     }
 }
 
+bool ScTokenArray::ReferencesSheet( SCTAB nTab, SCTAB nPosTab ) const
+{
+    TokenPointers aPtrs( pCode, nLen, pRPN, nRPN, false);
+    for (size_t j=0; j<2; ++j)
+    {
+        FormulaToken** pp = aPtrs.maPointerRange[j].mpStart;
+        FormulaToken** pEnd = aPtrs.maPointerRange[j].mpStop;
+        for (; pp != pEnd; ++pp)
+        {
+            FormulaToken* p = aPtrs.getHandledToken(j,pp);
+            if (!p)
+                continue;
+
+            switch ( p->GetType() )
+            {
+                case svDoubleRef :
+                    {
+                        ScComplexRefData& rRef = *p->GetDoubleRef();
+                        ScSingleRefData& rRef2 = rRef.Ref2;
+                        ScSingleRefData& rRef1 = rRef.Ref1;
+
+                        SCTAB nTab1 = (rRef1.IsTabRel() ? rRef1.Tab() + nPosTab : rRef1.Tab());
+                        SCTAB nTab2 = (rRef2.IsTabRel() ? rRef2.Tab() + nPosTab : rRef2.Tab());
+                        if (nTab1 <= nTab && nTab <= nTab2)
+                            return true;
+                    }
+                    break;
+                case svSingleRef :
+                    {
+                        ScSingleRefData& rRef = *p->GetSingleRef();
+                        if (rRef.IsTabRel())
+                        {
+                            if (rRef.Tab() + nPosTab == nTab)
+                                return true;
+                        }
+                        else
+                        {
+                            if (rRef.Tab() == nTab)
+                                return true;
+                        }
+                    }
+                    break;
+                default:
+                    ;
+            }
+        }
+    }
+    return false;
+}
+
 namespace {
 
 ScRange getSelectedRange( const sc::RefUpdateContext& rCxt )