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
diff --git a/sc/inc/address.hxx b/sc/inc/address.hxx
index aa700d9..4bf5ee5 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 b96acb7..5f3e762 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 f69a822..b7f9a5b 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 0878cb1..9811c08 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 8ca5373..602a636 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 41be86a..49bbe2e 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;
