sticky end col/row anchor for range references, tdf#92779

In range references referring more than one column/row where the end
col/row points to the maximum column or row number that col/row is not
decremented when the range is shrunken. Incrementing an end col/row is
not done past the maximum column or row number, so such references do
not yield #REF! errors anymore. This is also done in named expressions
if the end col/row is an absolute reference.

Change-Id: Iafa2d62abd3e816a1c56c3166af92807e55b75ce
(cherry picked from commit cfecdd6199710921f8fd921f615203c9e34c551e)

6 files changed, 251 insertions, 32 deletions

diff --git a/sc/inc/address.hxx b/sc/inc/address.hxx
index aa700d99b6df..4bf5ee5404e4 100644
--- a/sc/inc/address.hxx
+++ b/sc/inc/address.hxx
@@ -542,6 +542,21 @@ public:
 
     ScRange Intersection( const ScRange& rOther ) const;
 
+    /// If maximum end column should not be adapted during reference update.
+    inline bool IsEndColSticky() const;
+    /// If maximum end row should not be adapted during reference update.
+    inline bool IsEndRowSticky() const;
+
+    /** Increment or decrement end column unless sticky or until it becomes
+        sticky. Checks if the range encompasses at least two columns so should
+        be called before adjusting the start column. */
+    void IncEndColSticky( SCsCOL nDelta );
+
+    /** Increment or decrement end row unless sticky or until it becomes
+        sticky. Checks if the range encompasses at least two rows so should
+        be called before adjusting the start row. */
+    void IncEndRowSticky( SCsROW nDelta );
+
     inline bool operator==( const ScRange& rRange ) const;
     inline bool operator!=( const ScRange& rRange ) const;
     inline bool operator<( const ScRange& rRange ) const;
@@ -562,6 +577,18 @@ inline void ScRange::GetVars( SCCOL& nCol1, SCROW& nRow1, SCTAB& nTab1,
     aEnd.GetVars( nCol2, nRow2, nTab2 );
 }
 
+inline bool ScRange::IsEndColSticky() const
+{
+    // Only in an actual column range, i.e. not if both columns are MAXCOL.
+    return aEnd.Col() == MAXCOL && aStart.Col() < aEnd.Col();
+}
+
+inline bool ScRange::IsEndRowSticky() const
+{
+    // Only in an actual row range, i.e. not if both rows are MAXROW.
+    return aEnd.Row() == MAXROW && aStart.Row() < aEnd.Row();
+}
+
 inline bool ScRange::operator==( const ScRange& rRange ) const
 {
     return ( (aStart == rRange.aStart) && (aEnd == rRange.aEnd) );
diff --git a/sc/inc/refdata.hxx b/sc/inc/refdata.hxx
index b96acb7a7eec..5f3e7626940a 100644
--- a/sc/inc/refdata.hxx
+++ b/sc/inc/refdata.hxx
@@ -180,6 +180,16 @@ struct ScComplexRefData
     ScComplexRefData& Extend( const ScSingleRefData & rRef, const ScAddress & rPos );
     ScComplexRefData& Extend( const ScComplexRefData & rRef, const ScAddress & rPos );
 
+    /** Increment or decrement end column unless or until sticky.
+        @see ScRange::IncEndColSticky()
+        @return TRUE if changed. */
+    bool IncEndColSticky( SCCOL nDelta, const ScAddress& rPos );
+
+    /** Increment or decrement end row unless or until sticky.
+        @see ScRange::IncEndRowSticky()
+        @return TRUE if changed. */
+    bool IncEndRowSticky( SCROW nDelta, const ScAddress& rPos );
+
 #if DEBUG_FORMULA_COMPILER
     void Dump( int nIndent = 0 ) const;
 #endif
diff --git a/sc/source/core/tool/address.cxx b/sc/source/core/tool/address.cxx
index f69a8221e3eb..b7f9a5b526fb 100644
--- a/sc/source/core/tool/address.cxx
+++ b/sc/source/core/tool/address.cxx
@@ -2125,13 +2125,67 @@ bool ScAddress::Move( SCsCOL dx, SCsROW dy, SCsTAB dz, ScDocument* pDoc )
 
 bool ScRange::Move( SCsCOL dx, SCsROW dy, SCsTAB dz, ScDocument* pDoc )
 {
+    bool bColRange = (aStart.Col() < aEnd.Col());
+    bool bRowRange = (aStart.Row() < aEnd.Row());
     if (dy && aStart.Row() == 0 && aEnd.Row() == MAXROW)
         dy = 0;     // Entire column not to be moved.
     if (dx && aStart.Col() == 0 && aEnd.Col() == MAXCOL)
         dx = 0;     // Entire row not to be moved.
-    bool b = aStart.Move( dx, dy, dz, pDoc );
-    b &= aEnd.Move( dx, dy, dz, pDoc );
-    return b;
+    bool b1 = aStart.Move( dx, dy, dz, pDoc );
+    if (dx && aEnd.Col() == MAXCOL)
+        dx = 0;     // End column sticky.
+    if (dy && aEnd.Row() == MAXROW)
+        dy = 0;     // End row sticky.
+    SCTAB nOldTab = aEnd.Tab();
+    bool b2 = aEnd.Move( dx, dy, dz, pDoc );
+    if (!b2)
+    {
+        // End column or row of a range may have become sticky.
+        bColRange = (!dx || (bColRange && aEnd.Col() == MAXCOL));
+        bRowRange = (!dy || (bRowRange && aEnd.Row() == MAXROW));
+        b2 = bColRange && bRowRange && (aEnd.Tab() - nOldTab == dz);
+    }
+    return b1 && b2;
+}
+
+void ScRange::IncEndColSticky( SCsCOL nDelta )
+{
+    SCCOL nCol = aEnd.Col();
+    if (aStart.Col() >= nCol)
+    {
+        // Less than two columns => not sticky.
+        aEnd.IncCol( nDelta);
+        return;
+    }
+
+    if (nCol == MAXCOL)
+        // already sticky
+        return;
+
+    if (nCol < MAXCOL)
+        aEnd.SetCol( ::std::min( static_cast<SCCOL>(nCol + nDelta), MAXCOL));
+    else
+        aEnd.IncCol( nDelta);   // was greater than MAXCOL, caller should know..
+}
+
+void ScRange::IncEndRowSticky( SCsROW nDelta )
+{
+    SCROW nRow = aEnd.Row();
+    if (aStart.Row() >= nRow)
+    {
+        // Less than two rows => not sticky.
+        aEnd.IncRow( nDelta);
+        return;
+    }
+
+    if (nRow == MAXROW)
+        // already sticky
+        return;
+
+    if (nRow < MAXROW)
+        aEnd.SetRow( ::std::min( static_cast<SCROW>(nRow + nDelta), MAXROW));
+    else
+        aEnd.IncRow( nDelta);   // was greater than MAXROW, caller should know..
 }
 
 OUString ScAddress::GetColRowString( bool bAbsolute,
diff --git a/sc/source/core/tool/refdata.cxx b/sc/source/core/tool/refdata.cxx
index 0878cb12d431..9811c08b72c1 100644
--- a/sc/source/core/tool/refdata.cxx
+++ b/sc/source/core/tool/refdata.cxx
@@ -478,6 +478,64 @@ void ScComplexRefData::PutInOrder( const ScAddress& rPos )
     ScSingleRefData::PutInOrder( Ref1, Ref2, rPos);
 }
 
+bool ScComplexRefData::IncEndColSticky( SCCOL nDelta, const ScAddress& rPos )
+{
+    SCCOL nCol1 = Ref1.IsColRel() ? Ref1.Col() + rPos.Col() : Ref1.Col();
+    SCCOL nCol2 = Ref2.IsColRel() ? Ref2.Col() + rPos.Col() : Ref2.Col();
+    if (nCol1 >= nCol2)
+    {
+        // Less than two columns => not sticky.
+        Ref2.IncCol( nDelta);
+        return true;
+    }
+
+    if (nCol2 == MAXCOL)
+        // already sticky
+        return false;
+
+    if (nCol2 < MAXCOL)
+    {
+        SCCOL nCol = ::std::min( static_cast<SCCOL>(nCol2 + nDelta), MAXCOL);
+        if (Ref2.IsColRel())
+            Ref2.SetRelCol( nCol - rPos.Col());
+        else
+            Ref2.SetAbsCol( nCol);
+    }
+    else
+        Ref2.IncCol( nDelta);   // was greater than MAXCOL, caller should know..
+
+    return true;
+}
+
+bool ScComplexRefData::IncEndRowSticky( SCROW nDelta, const ScAddress& rPos )
+{
+    SCROW nRow1 = Ref1.IsRowRel() ? Ref1.Row() + rPos.Row() : Ref1.Row();
+    SCROW nRow2 = Ref2.IsRowRel() ? Ref2.Row() + rPos.Row() : Ref2.Row();
+    if (nRow1 >= nRow2)
+    {
+        // Less than two rows => not sticky.
+        Ref2.IncRow( nDelta);
+        return true;
+    }
+
+    if (nRow2 == MAXROW)
+        // already sticky
+        return false;
+
+    if (nRow2 < MAXROW)
+    {
+        SCROW nRow = ::std::min( static_cast<SCROW>(nRow2 + nDelta), MAXROW);
+        if (Ref2.IsRowRel())
+            Ref2.SetRelRow( nRow - rPos.Row());
+        else
+            Ref2.SetAbsRow( nRow);
+    }
+    else
+        Ref2.IncRow( nDelta);   // was greater than MAXROW, caller should know..
+
+    return true;
+}
+
 #if DEBUG_FORMULA_COMPILER
 void ScComplexRefData::Dump( int nIndent ) const
 {
diff --git a/sc/source/core/tool/refupdat.cxx b/sc/source/core/tool/refupdat.cxx
index 8ca5373eb7e1..602a636c6118 100644
--- a/sc/source/core/tool/refupdat.cxx
+++ b/sc/source/core/tool/refupdat.cxx
@@ -231,6 +231,13 @@ ScRefUpdateRes ScRefUpdate::Update( ScDocument* pDoc, UpdateRefMode eUpdateRefMo
                 theCol1 = oldCol1;
                 theCol2 = oldCol2;
             }
+            else if (oldCol2 == MAXCOL && oldCol1 < MAXCOL)
+            {
+                // End was sticky, but start may have been moved. Only on range.
+                theCol2 = oldCol2;
+            }
+            // Else, if (bCut2 && theCol2 == MAXCOL) then end becomes sticky,
+            // but currently there's nothing to do.
         }
         if ( nDy && (theCol1 >= nCol1) && (theCol2 <= nCol2) &&
                     (theTab1 >= nTab1) && (theTab2 <= nTab2))
@@ -256,6 +263,13 @@ ScRefUpdateRes ScRefUpdate::Update( ScDocument* pDoc, UpdateRefMode eUpdateRefMo
                 theRow1 = oldRow1;
                 theRow2 = oldRow2;
             }
+            else if (oldRow2 == MAXROW && oldRow1 < MAXROW)
+            {
+                // End was sticky, but start may have been moved. Only on range.
+                theRow2 = oldRow2;
+            }
+            // Else, if (bCut2 && theRow2 == MAXROW) then end becomes sticky,
+            // but currently there's nothing to do.
         }
         if ( nDz && (theCol1 >= nCol1) && (theCol2 <= nCol2) &&
                     (theRow1 >= nRow1) && (theRow2 <= nRow2) )
diff --git a/sc/source/core/tool/token.cxx b/sc/source/core/tool/token.cxx
index 41be86a84fe0..49bbe2eccad9 100644
--- a/sc/source/core/tool/token.cxx
+++ b/sc/source/core/tool/token.cxx
@@ -2599,22 +2599,30 @@ bool shrinkRange( const sc::RefUpdateContext& rCxt, ScRange& rRefRange, const Sc
                 // The reference range is truncated on the left.
                 SCCOL nOffset = rDeletedRange.aStart.Col() - rRefRange.aStart.Col();
                 SCCOL nDelta = rRefRange.aStart.Col() - rDeletedRange.aEnd.Col() - 1;
+                rRefRange.IncEndColSticky(nDelta+nOffset);
                 rRefRange.aStart.IncCol(nOffset);
-                rRefRange.aEnd.IncCol(nDelta+nOffset);
             }
         }
         else if (rDeletedRange.aEnd.Col() < rRefRange.aEnd.Col())
         {
+            if (rRefRange.IsEndColSticky())
+                // Sticky end not affected.
+                return false;
+
             // Reference is deleted in the middle. Move the last column
             // position to the left.
             SCCOL nDelta = rDeletedRange.aStart.Col() - rDeletedRange.aEnd.Col() - 1;
-            rRefRange.aEnd.IncCol(nDelta);
+            rRefRange.IncEndColSticky(nDelta);
         }
         else
         {
+            if (rRefRange.IsEndColSticky())
+                // Sticky end not affected.
+                return false;
+
             // The reference range is truncated on the right.
             SCCOL nDelta = rDeletedRange.aStart.Col() - rRefRange.aEnd.Col() - 1;
-            rRefRange.aEnd.IncCol(nDelta);
+            rRefRange.IncEndColSticky(nDelta);
         }
         return true;
     }
@@ -2642,22 +2650,30 @@ bool shrinkRange( const sc::RefUpdateContext& rCxt, ScRange& rRefRange, const Sc
                 // The reference range is truncated on the top.
                 SCCOL nOffset = rDeletedRange.aStart.Row() - rRefRange.aStart.Row();
                 SCCOL nDelta = rRefRange.aStart.Row() - rDeletedRange.aEnd.Row() - 1;
+                rRefRange.IncEndRowSticky(nDelta+nOffset);
                 rRefRange.aStart.IncRow(nOffset);
-                rRefRange.aEnd.IncRow(nDelta+nOffset);
             }
         }
         else if (rDeletedRange.aEnd.Row() < rRefRange.aEnd.Row())
         {
+            if (rRefRange.IsEndRowSticky())
+                // Sticky end not affected.
+                return false;
+
             // Reference is deleted in the middle. Move the last row
             // position upward.
             SCCOL nDelta = rDeletedRange.aStart.Row() - rDeletedRange.aEnd.Row() - 1;
-            rRefRange.aEnd.IncRow(nDelta);
+            rRefRange.IncEndRowSticky(nDelta);
         }
         else
         {
+            if (rRefRange.IsEndRowSticky())
+                // Sticky end not affected.
+                return false;
+
             // The reference range is truncated on the bottom.
             SCCOL nDelta = rDeletedRange.aStart.Row() - rRefRange.aEnd.Row() - 1;
-            rRefRange.aEnd.IncRow(nDelta);
+            rRefRange.IncEndRowSticky(nDelta);
         }
         return true;
     }
@@ -2695,9 +2711,13 @@ bool expandRange( const sc::RefUpdateContext& rCxt, ScRange& rRefRange, const Sc
                 return false;
         }
 
+        if (rRefRange.IsEndColSticky())
+            // Sticky end not affected.
+            return false;
+
         // Move the last column position to the right.
         SCCOL nDelta = rSelectedRange.aEnd.Col() - rSelectedRange.aStart.Col() + 1;
-        rRefRange.aEnd.IncCol(nDelta);
+        rRefRange.IncEndColSticky(nDelta);
         return true;
     }
     else if (rCxt.mnRowDelta > 0)
@@ -2724,9 +2744,13 @@ bool expandRange( const sc::RefUpdateContext& rCxt, ScRange& rRefRange, const Sc
                 return false;
         }
 
+        if (rRefRange.IsEndRowSticky())
+            // Sticky end not affected.
+            return false;
+
         // Move the last row position down.
         SCROW nDelta = rSelectedRange.aEnd.Row() - rSelectedRange.aStart.Row() + 1;
-        rRefRange.aEnd.IncRow(nDelta);
+        rRefRange.IncEndRowSticky(nDelta);
         return true;
     }
     return false;
@@ -2767,9 +2791,13 @@ bool expandRangeByEdge( const sc::RefUpdateContext& rCxt, ScRange& rRefRange, co
             // Selected range is not immediately adjacent. Bail out.
             return false;
 
+        if (rRefRange.IsEndColSticky())
+            // Sticky end not affected.
+            return false;
+
         // Move the last column position to the right.
         SCCOL nDelta = rSelectedRange.aEnd.Col() - rSelectedRange.aStart.Col() + 1;
-        rRefRange.aEnd.IncCol(nDelta);
+        rRefRange.IncEndColSticky(nDelta);
         return true;
     }
     else if (rCxt.mnRowDelta > 0)
@@ -2790,9 +2818,13 @@ bool expandRangeByEdge( const sc::RefUpdateContext& rCxt, ScRange& rRefRange, co
             // Selected range is not immediately adjacent. Bail out.
             return false;
 
+        if (rRefRange.IsEndRowSticky())
+            // Sticky end not affected.
+            return false;
+
         // Move the last row position down.
         SCROW nDelta = rSelectedRange.aEnd.Row() - rSelectedRange.aStart.Row() + 1;
-        rRefRange.aEnd.IncRow(nDelta);
+        rRefRange.IncEndRowSticky(nDelta);
         return true;
     }
 
@@ -3272,7 +3304,8 @@ void ScTokenArray::MoveReferenceRowReorder( const ScAddress& rPos, SCTAB nTab, S
 namespace {
 
 bool adjustSingleRefInName(
-    ScSingleRefData& rRef, const sc::RefUpdateContext& rCxt, const ScAddress& rPos )
+    ScSingleRefData& rRef, const sc::RefUpdateContext& rCxt, const ScAddress& rPos,
+    ScComplexRefData* pEndOfComplex )
 {
     ScAddress aAbs = rRef.toAbs(rPos);
 
@@ -3292,8 +3325,16 @@ bool adjustSingleRefInName(
         // Adjust absolute column reference.
         if (rCxt.maRange.aStart.Col() <= rRef.Col() && rRef.Col() <= rCxt.maRange.aEnd.Col())
         {
-            rRef.IncCol(rCxt.mnColDelta);
-            bChanged = true;
+            if (pEndOfComplex)
+            {
+                if (pEndOfComplex->IncEndColSticky( rCxt.mnColDelta, rPos))
+                    bChanged = true;
+            }
+            else
+            {
+                rRef.IncCol(rCxt.mnColDelta);
+                bChanged = true;
+            }
         }
     }
 
@@ -3302,8 +3343,16 @@ bool adjustSingleRefInName(
         // Adjust absolute row reference.
         if (rCxt.maRange.aStart.Row() <= rRef.Row() && rRef.Row() <= rCxt.maRange.aEnd.Row())
         {
-            rRef.IncRow(rCxt.mnRowDelta);
-            bChanged = true;
+            if (pEndOfComplex)
+            {
+                if (pEndOfComplex->IncEndRowSticky( rCxt.mnRowDelta, rPos))
+                    bChanged = true;
+            }
+            else
+            {
+                rRef.IncRow(rCxt.mnRowDelta);
+                bChanged = true;
+            }
         }
     }
 
@@ -3330,20 +3379,21 @@ bool adjustDoubleRefInName(
         {
             // Selection intersects the referenced range. Only expand the
             // bottom position.
-            rRef.Ref2.IncRow(rCxt.mnRowDelta);
+            rRef.IncEndRowSticky(rCxt.mnRowDelta, rPos);
             return true;
         }
     }
 
     if ((rCxt.mnRowDelta && rRef.IsEntireCol()) || (rCxt.mnColDelta && rRef.IsEntireRow()))
     {
-        // References to entire col/row are not to be adjusted in the other axis.
         sc::RefUpdateContext aCxt( rCxt.mrDoc);
         // We only need a few parameters of RefUpdateContext.
         aCxt.maRange = rCxt.maRange;
         aCxt.mnColDelta = rCxt.mnColDelta;
         aCxt.mnRowDelta = rCxt.mnRowDelta;
         aCxt.mnTabDelta = rCxt.mnTabDelta;
+
+        // References to entire col/row are not to be adjusted in the other axis.
         if (aCxt.mnRowDelta && rRef.IsEntireCol())
             aCxt.mnRowDelta = 0;
         if (aCxt.mnColDelta && rRef.IsEntireRow())
@@ -3352,18 +3402,20 @@ bool adjustDoubleRefInName(
             // early bailout
             return bRefChanged;
 
-        if (adjustSingleRefInName(rRef.Ref1, aCxt, rPos))
+        // Ref2 before Ref1 for sticky ends.
+        if (adjustSingleRefInName(rRef.Ref2, aCxt, rPos, &rRef))
             bRefChanged = true;
 
-        if (adjustSingleRefInName(rRef.Ref2, aCxt, rPos))
+        if (adjustSingleRefInName(rRef.Ref1, aCxt, rPos, nullptr))
             bRefChanged = true;
     }
     else
     {
-        if (adjustSingleRefInName(rRef.Ref1, rCxt, rPos))
+        // Ref2 before Ref1 for sticky ends.
+        if (adjustSingleRefInName(rRef.Ref2, rCxt, rPos, &rRef))
             bRefChanged = true;
 
-        if (adjustSingleRefInName(rRef.Ref2, rCxt, rPos))
+        if (adjustSingleRefInName(rRef.Ref1, rCxt, rPos, nullptr))
             bRefChanged = true;
     }
 
@@ -3396,7 +3448,7 @@ sc::RefUpdateResult ScTokenArray::AdjustReferenceInName(
                 case svSingleRef:
                     {
                         ScSingleRefData& rRef = *p->GetSingleRef();
-                        if (adjustSingleRefInName(rRef, rCxt, rPos))
+                        if (adjustSingleRefInName(rRef, rCxt, rPos, nullptr))
                             aRes.mbReferenceModified = true;
                     }
                     break;
@@ -3449,19 +3501,23 @@ sc::RefUpdateResult ScTokenArray::AdjustReferenceInName(
 
                             if (aAbs.aStart.Row() < aDeleted.aStart.Row())
                             {
-                                if (aDeleted.aEnd.Row() < aAbs.aEnd.Row())
-                                    // Deleted in the middle.  Make the reference shorter.
-                                    rRef.Ref2.IncRow(rCxt.mnRowDelta);
-                                else
-                                    // Deleted at tail end.  Cut off the lower part.
-                                    rRef.Ref2.SetAbsRow(aDeleted.aStart.Row()-1);
+                                if (!aAbs.IsEndRowSticky())
+                                {
+                                    if (aDeleted.aEnd.Row() < aAbs.aEnd.Row())
+                                        // Deleted in the middle.  Make the reference shorter.
+                                        rRef.Ref2.IncRow(rCxt.mnRowDelta);
+                                    else
+                                        // Deleted at tail end.  Cut off the lower part.
+                                        rRef.Ref2.SetAbsRow(aDeleted.aStart.Row()-1);
+                                }
                             }
                             else
                             {
                                 // Deleted at the top.  Cut the top off and shift up.
                                 rRef.Ref1.SetAbsRow(aDeleted.aEnd.Row()+1);
                                 rRef.Ref1.IncRow(rCxt.mnRowDelta);
-                                rRef.Ref2.IncRow(rCxt.mnRowDelta);
+                                if (!aAbs.IsEndRowSticky())
+                                    rRef.Ref2.IncRow(rCxt.mnRowDelta);
                             }
 
                             aRes.mbReferenceModified = true;