unit test for sticky end col/row anchors, tdf#92779

Change-Id: I78584e37e5944327db9cc5b6472a9e7ea972b37e

1 files changed, 184 insertions, 0 deletions

diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index 841ae6000295..c42df7d58052 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -1545,6 +1545,190 @@ void Test::testFormulaRefUpdateRange()
     if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "SUM($C$6:$F$9)"))
         CPPUNIT_FAIL("Wrong formula in A3.");
 
+    m_pDoc->InsertTab(1, "StickyRange");
+
+    // A3:A18 all possible combinations of relative and absolute addressing,
+    // leaving one row above and below unreferenced.
+    ScAddress aPos(0,2,1);
+    m_pDoc->SetString( aPos, "=B2:B1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=B2:B$1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=B2:$B1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=B2:$B$1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=B$2:B1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=B$2:B$1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=B$2:$B1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=B$2:$B$1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=$B2:B1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=$B2:B$1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=$B2:$B1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=$B2:$B$1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=$B$2:B1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=$B$2:B$1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=$B$2:$B1048575");
+    aPos.IncRow();
+    m_pDoc->SetString( aPos, "=$B$2:$B$1048575");
+    aPos.IncRow();
+    // A19 reference to two cells on one row.
+    m_pDoc->SetString( aPos, "=B1048575:C1048575");
+    aPos.IncRow();
+
+    // Insert 2 rows in the middle to shift bottom reference down and make it
+    // sticky.
+    m_pDoc->InsertRow( ScRange( aPos, ScAddress(MAXCOL,aPos.Row()+1,1)));
+
+    // A3:A18 must not result in #REF! anywhere.
+    bool bCheck = true;
+    aPos.Set(0,2,1);
+    bCheck &= checkFormula(*m_pDoc, aPos, "B2:B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B2:B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B2:$B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B2:$B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B$2:B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B$2:B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B$2:$B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B$2:$B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B2:B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B2:B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B2:$B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B2:$B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B$2:B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B$2:B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B$2:$B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B$2:$B$1048576");
+    aPos.IncRow();
+    if (!bCheck)
+        CPPUNIT_FAIL("Wrong reference in A3:A18 after insertion.");
+
+    // A19 reference to one row shifted out should be #REF!
+    bCheck &= checkFormula(*m_pDoc, aPos, "B#REF!:C#REF!");
+    if (!bCheck)
+        CPPUNIT_FAIL("Wrong reference in A19 after insertion.");
+    // A19 enter reference to last row.
+    m_pDoc->SetString( aPos, "=B1048576:C1048576");
+    aPos.IncRow();
+
+    // Delete row 1 to shift top reference up, bottom reference stays sticky.
+    m_pDoc->DeleteRow(ScRange(0,0,1,MAXCOL,0,1));
+
+    // Check sticky bottom references and display of entire column references,
+    // now in A2:A17.
+    bCheck = true;
+    aPos.Set(0,1,1);
+    bCheck &= checkFormula(*m_pDoc, aPos, "B:B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B1:B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B:$B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B1:$B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B$1:B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B:B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B$1:$B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B:$B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B:B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B1:B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B:$B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B1:$B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B$1:B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B:B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B$1:$B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B:$B");
+    aPos.IncRow();
+    if (!bCheck)
+        CPPUNIT_FAIL("Wrong reference in A2:A17 after deletion.");
+
+    // A18 reference to one last row should be shifted up.
+    bCheck &= checkFormula(*m_pDoc, aPos, "B1048575:C1048575");
+    if (!bCheck)
+        CPPUNIT_FAIL("Wrong reference in A18 after deletion.");
+    aPos.IncRow();
+
+    // Insert 4 rows in the middle.
+    m_pDoc->InsertRow( ScRange( aPos, ScAddress(MAXCOL,aPos.Row()+3,1)));
+    // Delete 2 rows in the middle.
+    m_pDoc->DeleteRow( ScRange( aPos, ScAddress(MAXCOL,aPos.Row()+1,1)));
+
+    // References in A2:A17 must still be the same.
+    bCheck = true;
+    aPos.Set(0,1,1);
+    bCheck &= checkFormula(*m_pDoc, aPos, "B:B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B1:B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B:$B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B1:$B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B$1:B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B:B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B$1:$B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "B:$B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B:B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B1:B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B:$B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B1:$B$1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B$1:B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B:B");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B$1:$B1048576");
+    aPos.IncRow();
+    bCheck &= checkFormula(*m_pDoc, aPos, "$B:$B");
+    aPos.IncRow();
+    if (!bCheck)
+        CPPUNIT_FAIL("Wrong reference in A2:A17 after deletion.");
+
+    m_pDoc->DeleteTab(1);
+
     m_pDoc->DeleteTab(0);
 }