sc/qa/unit/ucalc_sharedformula.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  */
   9
  10 #include "helper/debughelper.hxx"
  11 #include "helper/qahelper.hxx"
  12 #include <editutil.hxx>
  13 #include <formulacell.hxx>
  14 #include <cellvalue.hxx>
  15 #include <scopetools.hxx>
  16 #include <docfunc.hxx>
  17 #include <dbdocfun.hxx>
  18 #include <tokenstringcontext.hxx>
  19 #include <globalnames.hxx>
  20 #include <dbdata.hxx>
  21 #include <bcaslot.hxx>
  22 #include <undomanager.hxx>
  23
  24 #include <sfx2/docfile.hxx>
  25
  26 #include <memory>
  27 #include <string_view>
  28
  29 class TestSharedFormula : public ScUcalcTestBase
  30 {
  31 public:
  32     void testSharedFormulas();
  33     void testSharedFormulasRefUpdate();
  34     void testSharedFormulasRefUpdateMove();
  35     void testSharedFormulasRefUpdateMove2();
  36     void testSharedFormulasRefUpdateRange();
  37     void testSharedFormulasRefUpdateRangeDeleteRow();
  38     void testSharedFormulasRefUpdateExternal();
  39     void testSharedFormulasInsertRow();
  40     void testSharedFormulasDeleteRows();
  41     void testSharedFormulasDeleteColumns();
  42     void testSharedFormulasRefUpdateMoveSheets();
  43     void testSharedFormulasRefUpdateCopySheets();
  44     void testSharedFormulasRefUpdateDeleteSheets();
  45     void testSharedFormulasCopyPaste();
  46     void testSharedFormulaInsertColumn();
  47     void testSharedFormulaMoveBlock();
  48     void testSharedFormulaUpdateOnNamedRangeChange();
  49     void testSharedFormulaUpdateOnDBChange();
  50     void testSharedFormulaAbsCellListener();
  51     void testSharedFormulaUnshareAreaListeners();
  52     void testSharedFormulaListenerDeleteArea();
  53     void testSharedFormulaUpdateOnReplacement();
  54     void testSharedFormulaDeleteTopCell();
  55     void testSharedFormulaCutCopyMoveIntoRef();
  56     void testSharedFormulaCutCopyMoveWithRef();
  57     void testSharedFormulaCutCopyMoveWithinRun();
  58     void testSharedFormulaInsertShift();
  59
  60     CPPUNIT_TEST_SUITE(TestSharedFormula);
  61
  62     CPPUNIT_TEST(testSharedFormulas);
  63     CPPUNIT_TEST(testSharedFormulasRefUpdate);
  64     CPPUNIT_TEST(testSharedFormulasRefUpdateMove);
  65     CPPUNIT_TEST(testSharedFormulasRefUpdateMove2);
  66     CPPUNIT_TEST(testSharedFormulasRefUpdateRange);
  67     CPPUNIT_TEST(testSharedFormulasRefUpdateRangeDeleteRow);
  68     CPPUNIT_TEST(testSharedFormulasRefUpdateExternal);
  69     CPPUNIT_TEST(testSharedFormulasInsertRow);
  70     CPPUNIT_TEST(testSharedFormulasDeleteRows);
  71     CPPUNIT_TEST(testSharedFormulasDeleteColumns);
  72     CPPUNIT_TEST(testSharedFormulasRefUpdateMoveSheets);
  73     CPPUNIT_TEST(testSharedFormulasRefUpdateCopySheets);
  74     CPPUNIT_TEST(testSharedFormulasRefUpdateDeleteSheets);
  75     CPPUNIT_TEST(testSharedFormulasCopyPaste);
  76     CPPUNIT_TEST(testSharedFormulaInsertColumn);
  77     CPPUNIT_TEST(testSharedFormulaMoveBlock);
  78     CPPUNIT_TEST(testSharedFormulaUpdateOnNamedRangeChange);
  79     CPPUNIT_TEST(testSharedFormulaUpdateOnDBChange);
  80     CPPUNIT_TEST(testSharedFormulaAbsCellListener);
  81     CPPUNIT_TEST(testSharedFormulaUnshareAreaListeners);
  82     CPPUNIT_TEST(testSharedFormulaListenerDeleteArea);
  83     CPPUNIT_TEST(testSharedFormulaUpdateOnReplacement);
  84     CPPUNIT_TEST(testSharedFormulaDeleteTopCell);
  85     CPPUNIT_TEST(testSharedFormulaCutCopyMoveIntoRef);
  86     CPPUNIT_TEST(testSharedFormulaCutCopyMoveWithRef);
  87     CPPUNIT_TEST(testSharedFormulaCutCopyMoveWithinRun);
  88     CPPUNIT_TEST(testSharedFormulaInsertShift);
  89
  90     CPPUNIT_TEST_SUITE_END();
  91 };
  92
  93 void TestSharedFormula::testSharedFormulas()
  94 {
  95     m_pDoc->InsertTab(0, "Test");
  96
  97     ScAddress aPos(1, 9, 0); // B10
  98     m_pDoc->SetString(aPos, "=A10*2"); // Insert into B10.
  99     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(aPos);
 100     CPPUNIT_ASSERT_MESSAGE("Expected to be a non-shared cell.", pFC);
 101     CPPUNIT_ASSERT_MESSAGE("Expected to be a non-shared cell.", !pFC->IsShared());
 102
 103     aPos.SetRow(10); // B11
 104     m_pDoc->SetString(aPos, "=A11*2");
 105     pFC = m_pDoc->GetFormulaCell(aPos);
 106     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC);
 107     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC->IsShared());
 108     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), pFC->GetSharedTopRow());
 109     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 110     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 111
 112     aPos.SetRow(8); // B9
 113     m_pDoc->SetString(aPos, "=A9*2");
 114     pFC = m_pDoc->GetFormulaCell(aPos);
 115     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC);
 116     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC->IsShared());
 117     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
 118     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 119     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 120
 121     aPos.SetRow(12); // B13
 122     m_pDoc->SetString(aPos, "=A13*2");
 123     pFC = m_pDoc->GetFormulaCell(aPos);
 124     CPPUNIT_ASSERT_MESSAGE("This formula cell shouldn't be shared yet.", pFC);
 125     CPPUNIT_ASSERT_MESSAGE("This formula cell shouldn't be shared yet.", !pFC->IsShared());
 126
 127     // Insert a formula to B12, and B9:B13 should be shared.
 128     aPos.SetRow(11); // B12
 129     m_pDoc->SetString(aPos, "=A12*2");
 130     pFC = m_pDoc->GetFormulaCell(aPos);
 131     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC);
 132     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC->IsShared());
 133     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
 134     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(5), pFC->GetSharedLength());
 135     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 136
 137     // Insert formulas to B15:B16.
 138     aPos.SetRow(14); // B15
 139     m_pDoc->SetString(aPos, "=A15*2");
 140     aPos.SetRow(15); // B16
 141     m_pDoc->SetString(aPos, "=A16*2");
 142     pFC = m_pDoc->GetFormulaCell(aPos);
 143     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC);
 144     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC->IsShared());
 145     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(14), pFC->GetSharedTopRow());
 146     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 147     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 148
 149     // Insert a formula to B14, and B9:B16 should be shared.
 150     aPos.SetRow(13); // B14
 151     m_pDoc->SetString(aPos, "=A14*2");
 152     pFC = m_pDoc->GetFormulaCell(aPos);
 153     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC);
 154     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC->IsShared());
 155     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
 156     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
 157     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 158
 159     // Insert an incompatible formula to B12, to split the shared range to B9:B11 and B13:B16.
 160     aPos.SetRow(11); // B12
 161     m_pDoc->SetString(aPos, "=$A$1*4");
 162     pFC = m_pDoc->GetFormulaCell(aPos);
 163     CPPUNIT_ASSERT_MESSAGE("This cell shouldn't be shared.", pFC);
 164     CPPUNIT_ASSERT_MESSAGE("This cell shouldn't be shared.", !pFC->IsShared());
 165
 166     aPos.SetRow(8); // B9
 167     pFC = m_pDoc->GetFormulaCell(aPos);
 168     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC);
 169     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC->IsShared());
 170     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
 171     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 172     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 173
 174     aPos.SetRow(12); // B13
 175     pFC = m_pDoc->GetFormulaCell(aPos);
 176     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC);
 177     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC->IsShared());
 178     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(12), pFC->GetSharedTopRow());
 179     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
 180     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 181
 182     // Extend B13:B16 to B13:B20.
 183     aPos.SetRow(16); // B17
 184     m_pDoc->SetString(aPos, "=A17*2");
 185     aPos.IncRow();
 186     m_pDoc->SetString(aPos, "=A18*2");
 187     aPos.IncRow();
 188     m_pDoc->SetString(aPos, "=A19*2");
 189     aPos.IncRow();
 190     m_pDoc->SetString(aPos, "=A20*2");
 191     pFC = m_pDoc->GetFormulaCell(aPos);
 192     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC);
 193     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC->IsShared());
 194     // B13:B20 should be shared.
 195     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(12), pFC->GetSharedTopRow());
 196     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
 197     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 198
 199     // Empty B19. This should split it into B13:B18, and B20 non-shared.
 200     aPos.SetRow(18);
 201     m_pDoc->SetEmptyCell(aPos);
 202     CPPUNIT_ASSERT_EQUAL_MESSAGE("This cell should have been emptied.", CELLTYPE_NONE, m_pDoc->GetCellType(aPos));
 203     aPos.SetRow(12); // B13
 204     pFC = m_pDoc->GetFormulaCell(aPos);
 205     CPPUNIT_ASSERT(pFC);
 206     // B13:B18 should be shared.
 207     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(12), pFC->GetSharedTopRow());
 208     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), pFC->GetSharedLength());
 209     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 210     // B20 should be non-shared.
 211     aPos.SetRow(19); // B20
 212     pFC = m_pDoc->GetFormulaCell(aPos);
 213     CPPUNIT_ASSERT_MESSAGE("B20 should be a formula cell.", pFC);
 214     CPPUNIT_ASSERT_MESSAGE("This cell should be non-shared.", !pFC->IsShared());
 215
 216     // Empty B14, to make B13 non-shared and B15:B18 shared.
 217     aPos.SetRow(13); // B14
 218     m_pDoc->SetEmptyCell(aPos);
 219     aPos.SetRow(12); // B13
 220     pFC = m_pDoc->GetFormulaCell(aPos);
 221     // B13 should be non-shared.
 222     CPPUNIT_ASSERT_MESSAGE("B13 should be a formula cell.", pFC);
 223     CPPUNIT_ASSERT_MESSAGE("This cell should be non-shared.", !pFC->IsShared());
 224     // B15:B18 should be shared.
 225     aPos.SetRow(14); // B15
 226     pFC = m_pDoc->GetFormulaCell(aPos);
 227     CPPUNIT_ASSERT(pFC);
 228     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(14), pFC->GetSharedTopRow());
 229     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
 230     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 231
 232     // Set numeric value to B15, to make B16:B18 shared.
 233     aPos.SetRow(14);
 234     m_pDoc->SetValue(aPos, 1.2);
 235     aPos.SetRow(15);
 236     pFC = m_pDoc->GetFormulaCell(aPos);
 237     CPPUNIT_ASSERT(pFC);
 238     // B16:B18 should be shared.
 239     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(15), pFC->GetSharedTopRow());
 240     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 241     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 242
 243     // Set string value to B16 to make B17:B18 shared.
 244     aPos.SetRow(15);
 245     ScCellValue aCell(svl::SharedString("Test"));
 246     CPPUNIT_ASSERT_EQUAL_MESSAGE("This should be a string value.", CELLTYPE_STRING, aCell.getType());
 247     aCell.commit(*m_pDoc, aPos);
 248     CPPUNIT_ASSERT_EQUAL(aCell.getSharedString()->getString(), m_pDoc->GetString(aPos));
 249     aPos.SetRow(16);
 250     pFC = m_pDoc->GetFormulaCell(aPos);
 251     CPPUNIT_ASSERT(pFC);
 252     // B17:B18 should be shared.
 253     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(16), pFC->GetSharedTopRow());
 254     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 255     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 256
 257     // Set edit text to B17. Now B18 should be non-shared.
 258     ScFieldEditEngine& rEditEngine = m_pDoc->GetEditEngine();
 259     rEditEngine.SetTextCurrentDefaults("Edit Text");
 260     aPos.SetRow(16);
 261     m_pDoc->SetEditText(aPos, rEditEngine.CreateTextObject());
 262     CPPUNIT_ASSERT_EQUAL(CELLTYPE_EDIT, m_pDoc->GetCellType(aPos));
 263     aPos.SetRow(17);
 264     pFC = m_pDoc->GetFormulaCell(aPos);
 265     CPPUNIT_ASSERT_MESSAGE("B18 should be a formula cell.", pFC);
 266     CPPUNIT_ASSERT_MESSAGE("B18 should be non-shared.", !pFC->IsShared());
 267
 268     // Set up a new group for shared formulas in B2:B10.
 269     clearRange(m_pDoc, ScRange(0,0,0,2,100,0));
 270
 271     aPos.SetRow(1);
 272     m_pDoc->SetString(aPos, "=A2*10");
 273     aPos.IncRow();
 274     m_pDoc->SetString(aPos, "=A3*10");
 275     aPos.IncRow();
 276     m_pDoc->SetString(aPos, "=A4*10");
 277     aPos.IncRow();
 278     m_pDoc->SetString(aPos, "=A5*10");
 279     aPos.IncRow();
 280     m_pDoc->SetString(aPos, "=A6*10");
 281     aPos.IncRow();
 282     m_pDoc->SetString(aPos, "=A7*10");
 283     aPos.IncRow();
 284     m_pDoc->SetString(aPos, "=A8*10");
 285     aPos.IncRow();
 286     m_pDoc->SetString(aPos, "=A9*10");
 287     aPos.IncRow();
 288     m_pDoc->SetString(aPos, "=A10*10");
 289
 290     pFC = m_pDoc->GetFormulaCell(aPos);
 291     CPPUNIT_ASSERT_MESSAGE("B10 should be a formula cell.", pFC);
 292     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 293     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), pFC->GetSharedLength());
 294     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 295
 296     // Delete A4:B8. This should split the grouping to B2:B3 and B9:B10.
 297     clearRange(m_pDoc, ScRange(0,3,0,1,7,0));
 298     aPos.SetRow(1);
 299     pFC = m_pDoc->GetFormulaCell(aPos);
 300     CPPUNIT_ASSERT_MESSAGE("B2 should be a formula cell.", pFC);
 301     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 302     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 303     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 304
 305     aPos.SetRow(8);
 306     pFC = m_pDoc->GetFormulaCell(aPos);
 307     CPPUNIT_ASSERT_MESSAGE("B9 should be a formula cell.", pFC);
 308     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
 309     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 310     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 311
 312     // Delete rows 4:8 and shift row 9 and below up to row 4.  This should
 313     // re-merge the two into a group of B2:B5.
 314     m_pDoc->DeleteRow(ScRange(0,3,0,m_pDoc->MaxCol(),7,0));
 315     aPos.SetRow(1);
 316     pFC = m_pDoc->GetFormulaCell(aPos);
 317     CPPUNIT_ASSERT_MESSAGE("B2 should be a formula cell.", pFC);
 318     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 319     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
 320     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 321
 322     // Insert 2 rows at row 4, to split it into B2:B3 and B6:B7.
 323     m_pDoc->InsertRow(ScRange(0,3,0,m_pDoc->MaxCol(),4,0));
 324     pFC = m_pDoc->GetFormulaCell(aPos);
 325     CPPUNIT_ASSERT_MESSAGE("B2 should be a formula cell.", pFC);
 326     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 327     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 328     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 329
 330     aPos.SetRow(5);
 331     pFC = m_pDoc->GetFormulaCell(aPos);
 332     CPPUNIT_ASSERT_MESSAGE("B6 should be a formula cell.", pFC);
 333     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(5), pFC->GetSharedTopRow());
 334     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 335     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 336
 337     // Test implicit intersection with shared formulas.
 338     aPos.Set(2,0,0);
 339     {
 340         // Insert data in C1:D2 and formulas in E1:E2
 341         const std::vector<std::vector<const char*>> aData = {
 342             { "5", "1", "=C:C/D:D" },
 343             { "4", "2", "=C:C/D:D" }
 344         };
 345
 346         insertRangeData(m_pDoc, aPos, aData);
 347     }
 348     aPos.Set(4,1,0);
 349     pFC = m_pDoc->GetFormulaCell(aPos);
 350     CPPUNIT_ASSERT_MESSAGE("E2 should be a formula cell.", pFC);
 351     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 352     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 353     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
 354
 355     aPos.SetRow(0);
 356     CPPUNIT_ASSERT_EQUAL_MESSAGE("5/1=5", 5.0, m_pDoc->GetValue(aPos));
 357     aPos.SetRow(1);
 358     CPPUNIT_ASSERT_EQUAL_MESSAGE("4/2=2", 2.0, m_pDoc->GetValue(aPos));
 359
 360     m_pDoc->DeleteTab(0);
 361 }
 362
 363 void TestSharedFormula::testSharedFormulasRefUpdate()
 364 {
 365     m_pDoc->InsertTab(0, "Test");
 366
 367     sc::AutoCalcSwitch aACSwitch(*m_pDoc, false); // turn off auto calculation.
 368
 369     // Set values to A10:A12.
 370     m_pDoc->SetValue(ScAddress(0,9,0), 1);
 371     m_pDoc->SetValue(ScAddress(0,10,0), 2);
 372     m_pDoc->SetValue(ScAddress(0,11,0), 3);
 373
 374     {
 375         // Insert formulas that reference A10:A12 in B1:B3.
 376         const std::vector<std::vector<const char*>> aData = {
 377             { "=A10" },
 378             { "=A11" },
 379             { "=A12" }
 380         };
 381
 382         insertRangeData(m_pDoc, ScAddress(1,0,0), aData);
 383     }
 384
 385     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B1", OUString("=A10"), m_pDoc->GetFormula(1,0,0));
 386     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B2", OUString("=A11"), m_pDoc->GetFormula(1,1,0));
 387     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B3", OUString("=A12"), m_pDoc->GetFormula(1,2,0));
 388
 389     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 390     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC);
 391     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC->IsShared());
 392     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 393     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 394
 395     // Insert cells over A11:B11 to shift to right. This should split the B1:B3 grouping into 3.
 396     m_pDoc->InsertCol(ScRange(0,10,0,1,10,0));
 397     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B1", OUString("=A10"), m_pDoc->GetFormula(1,0,0));
 398     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B2", OUString("=C11"), m_pDoc->GetFormula(1,1,0));
 399     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B3", OUString("=A12"), m_pDoc->GetFormula(1,2,0));
 400
 401     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 402     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", pFC);
 403     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", !pFC->IsShared());
 404     pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
 405     CPPUNIT_ASSERT_MESSAGE("B2 should be a non-shared formula cell.", pFC);
 406     CPPUNIT_ASSERT_MESSAGE("B2 should be a non-shared formula cell.", !pFC->IsShared());
 407     pFC = m_pDoc->GetFormulaCell(ScAddress(1,2,0));
 408     CPPUNIT_ASSERT_MESSAGE("B3 should be a non-shared formula cell.", pFC);
 409     CPPUNIT_ASSERT_MESSAGE("B3 should be a non-shared formula cell.", !pFC->IsShared());
 410
 411     // Delete cells over A11:B11 to bring it back to the previous state.
 412     m_pDoc->DeleteCol(ScRange(0,10,0,1,10,0));
 413
 414     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B1", OUString("=A10"), m_pDoc->GetFormula(1,0,0));
 415     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B2", OUString("=A11"), m_pDoc->GetFormula(1,1,0));
 416     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B3", OUString("=A12"), m_pDoc->GetFormula(1,2,0));
 417
 418     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 419     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC);
 420     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC->IsShared());
 421     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 422     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 423
 424     // Insert cells over A11:A12 and shift down.
 425     m_pDoc->InsertRow(ScRange(0,10,0,0,11,0));
 426     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B1", OUString("=A10"), m_pDoc->GetFormula(1,0,0));
 427     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B2", OUString("=A13"), m_pDoc->GetFormula(1,1,0));
 428     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B3", OUString("=A14"), m_pDoc->GetFormula(1,2,0));
 429
 430     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 431     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", pFC);
 432     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", !pFC->IsShared());
 433     pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
 434     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC);
 435     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC->IsShared());
 436     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 437     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 438
 439     // Delete A11:A12 to bring it back to the way it was.
 440     m_pDoc->DeleteRow(ScRange(0,10,0,0,11,0));
 441
 442     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B1", OUString("=A10"), m_pDoc->GetFormula(1,0,0));
 443     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B2", OUString("=A11"), m_pDoc->GetFormula(1,1,0));
 444     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B3", OUString("=A12"), m_pDoc->GetFormula(1,2,0));
 445
 446     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 447     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC);
 448     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC->IsShared());
 449     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 450     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 451
 452     // Insert cells over A11:B11 to shift to right again.
 453     m_pDoc->InsertCol(ScRange(0,10,0,1,10,0));
 454     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B1", OUString("=A10"), m_pDoc->GetFormula(1,0,0));
 455     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B2", OUString("=C11"), m_pDoc->GetFormula(1,1,0));
 456     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B3", OUString("=A12"), m_pDoc->GetFormula(1,2,0));
 457
 458     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 459     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", pFC);
 460     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", !pFC->IsShared());
 461     pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
 462     CPPUNIT_ASSERT_MESSAGE("B2 should be a non-shared formula cell.", pFC);
 463     CPPUNIT_ASSERT_MESSAGE("B2 should be a non-shared formula cell.", !pFC->IsShared());
 464     pFC = m_pDoc->GetFormulaCell(ScAddress(1,2,0));
 465     CPPUNIT_ASSERT_MESSAGE("B3 should be a non-shared formula cell.", pFC);
 466     CPPUNIT_ASSERT_MESSAGE("B3 should be a non-shared formula cell.", !pFC->IsShared());
 467
 468     // Insert cells over A12:B12 to shift to right.
 469     m_pDoc->InsertCol(ScRange(0,11,0,1,11,0));
 470     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B1", OUString("=A10"), m_pDoc->GetFormula(1,0,0));
 471     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B2", OUString("=C11"), m_pDoc->GetFormula(1,1,0));
 472     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B3", OUString("=C12"), m_pDoc->GetFormula(1,2,0));
 473
 474     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 475     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", pFC);
 476     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", !pFC->IsShared());
 477     // B2 and B3 should be grouped.
 478     pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
 479     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC);
 480     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC->IsShared());
 481     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 482     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 483
 484     // Insert cells over A10:B10 to shift to right.
 485     m_pDoc->InsertCol(ScRange(0,9,0,1,9,0));
 486     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B1", OUString("=C10"), m_pDoc->GetFormula(1,0,0));
 487     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B2", OUString("=C11"), m_pDoc->GetFormula(1,1,0));
 488     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in B3", OUString("=C12"), m_pDoc->GetFormula(1,2,0));
 489
 490     // B1:B3 should be now grouped.
 491     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 492     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC);
 493     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC->IsShared());
 494     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 495     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 496
 497     m_pDoc->DeleteTab(0);
 498 }
 499
 500 void TestSharedFormula::testSharedFormulasRefUpdateMove()
 501 {
 502     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
 503     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
 504
 505     m_pDoc->InsertTab(0, "Test");
 506
 507     // Set values in B2:B4.
 508     for (SCROW i = 1; i <= 3; ++i)
 509         m_pDoc->SetValue(ScAddress(1,i,0), i);
 510
 511     // Make sure the values are really there.
 512     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,1,0)));
 513     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
 514     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,3,0)));
 515
 516     const std::vector<std::vector<const char*>> aData = {
 517         { "=RC[-1]" },
 518         { "=RC[-1]" },
 519         { "=RC[-1]" }
 520     };
 521
 522     // Set formulas in C2:C4 that reference B2:B4 individually.
 523     insertRangeData(m_pDoc, ScAddress(2,1,0), aData);
 524
 525     // Check the formula results.
 526     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,1,0)));
 527     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,2,0)));
 528     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 529
 530     // Move B2:B4 to B1:B3.
 531     bool bMoved = m_xDocShell->GetDocFunc().MoveBlock(ScRange(1,1,0,1,3,0), ScAddress(1,0,0), true, true, false, true);
 532     CPPUNIT_ASSERT(bMoved);
 533
 534     // Make sure the values have been moved for real.
 535     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
 536     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
 537     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
 538
 539     // The formulas should have been adjusted for the move.
 540     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=R[-1]C[-1]"), m_pDoc->GetFormula(2,1,0));
 541     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=R[-1]C[-1]"), m_pDoc->GetFormula(2,2,0));
 542     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=R[-1]C[-1]"), m_pDoc->GetFormula(2,3,0));
 543
 544     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
 545     CPPUNIT_ASSERT(pUndoMgr);
 546     pUndoMgr->Undo();
 547
 548     // The values should have moved back.
 549     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,1,0)));
 550     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
 551     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,3,0)));
 552
 553     // And the formulas should have been re-adjusted to their original references.
 554     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=RC[-1]"), m_pDoc->GetFormula(2,1,0));
 555     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=RC[-1]"), m_pDoc->GetFormula(2,2,0));
 556     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=RC[-1]"), m_pDoc->GetFormula(2,3,0));
 557
 558     m_pDoc->DeleteTab(0);
 559 }
 560
 561 void TestSharedFormula::testSharedFormulasRefUpdateMove2()
 562 {
 563     sc::AutoCalcSwitch aACSwitch(*m_pDoc, false); // turn auto calc off this time.
 564     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
 565
 566     m_pDoc->InsertTab(0, "Test");
 567
 568     // Set values in B2:B3, and E2:E3.
 569     for (SCROW i = 1; i <= 2; ++i)
 570     {
 571         m_pDoc->SetValue(ScAddress(1,i,0), i);
 572         m_pDoc->SetValue(ScAddress(4,i,0), i);
 573     }
 574
 575     // Make sure the values are really there.
 576     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,1,0)));
 577     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
 578     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(4,1,0)));
 579     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(4,2,0)));
 580
 581     {
 582         // Set formulas in C2:C3 that reference B2:B3 individually, and F2:F3 to E2:E3.
 583         const std::vector<std::vector<const char*>> aData = {
 584             { "=RC[-1]" },
 585             { "=RC[-1]" }
 586         };
 587
 588         insertRangeData(m_pDoc, ScAddress(2,1,0), aData);
 589         insertRangeData(m_pDoc, ScAddress(5,1,0), aData);
 590     }
 591
 592     m_pDoc->CalcFormulaTree(); // calculate manually.
 593
 594     // Check the formula results.
 595     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,1,0)));
 596     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,2,0)));
 597     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(5,1,0)));
 598     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(5,2,0)));
 599
 600     // Move B2:C3 to C3:D4.
 601     bool bMoved = m_xDocShell->GetDocFunc().MoveBlock(
 602         ScRange(1,1,0,2,2,0), ScAddress(2,2,0), true, true, false, true);
 603     CPPUNIT_ASSERT(bMoved);
 604
 605     // Make sure the range has been moved.
 606     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,2,0)));
 607     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 608
 609     // The formula cells should retain their results even with auto calc off
 610     // and without recalculation.
 611     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(3,2,0)));
 612     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3,3,0)));
 613
 614     // And these formulas in F2:F3 are unaffected, therefore should not change.
 615     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(5,1,0)));
 616     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(5,2,0)));
 617
 618     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
 619     CPPUNIT_ASSERT(pUndoMgr);
 620
 621     // Undo the move.
 622     pUndoMgr->Undo();
 623
 624     // Check the formula results.  The results should still be intact.
 625     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,1,0)));
 626     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,2,0)));
 627     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(5,1,0)));
 628     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(5,2,0)));
 629
 630     m_pDoc->DeleteTab(0);
 631 }
 632
 633 void TestSharedFormula::testSharedFormulasRefUpdateRange()
 634 {
 635     m_pDoc->InsertTab(0, "Test");
 636
 637     // Insert values to A3:A5.
 638     m_pDoc->SetValue(ScAddress(0,2,0), 1);
 639     m_pDoc->SetValue(ScAddress(0,3,0), 2);
 640     m_pDoc->SetValue(ScAddress(0,4,0), 3);
 641
 642     {
 643         // Insert formulas to B3:B5.
 644         const std::vector<std::vector<const char*>> aData = {
 645             { "=SUM($A$3:$A$5)" },
 646             { "=SUM($A$3:$A$5)" },
 647             { "=SUM($A$3:$A$5)" }
 648         };
 649
 650         insertRangeData(m_pDoc, ScAddress(1,2,0), aData);
 651     }
 652
 653     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=SUM($A$3:$A$5)"), m_pDoc->GetFormula(1,2,0));
 654     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=SUM($A$3:$A$5)"), m_pDoc->GetFormula(1,3,0));
 655     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=SUM($A$3:$A$5)"), m_pDoc->GetFormula(1,4,0));
 656
 657     // B3:B5 should be shared.
 658     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,2,0));
 659     CPPUNIT_ASSERT_MESSAGE("B3 should be shared.", pFC);
 660     CPPUNIT_ASSERT_MESSAGE("B3 should be shared.", pFC->IsShared());
 661     pFC = m_pDoc->GetFormulaCell(ScAddress(1,3,0));
 662     CPPUNIT_ASSERT_MESSAGE("B4 should be shared.", pFC);
 663     CPPUNIT_ASSERT_MESSAGE("B4 should be shared.", pFC->IsShared());
 664     pFC = m_pDoc->GetFormulaCell(ScAddress(1,4,0));
 665     CPPUNIT_ASSERT_MESSAGE("B3 should be shared.", pFC);
 666     CPPUNIT_ASSERT_MESSAGE("B3 should be shared.", pFC->IsShared());
 667
 668     // Insert 2 rows at row 1.
 669     m_pDoc->InsertRow(ScRange(0,0,0,m_pDoc->MaxCol(),1,0));
 670
 671     // B5:B7 should be shared.
 672     pFC = m_pDoc->GetFormulaCell(ScAddress(1,4,0));
 673     CPPUNIT_ASSERT_MESSAGE("B5 should be shared.", pFC);
 674     CPPUNIT_ASSERT_MESSAGE("B5 should be shared.", pFC->IsShared());
 675     pFC = m_pDoc->GetFormulaCell(ScAddress(1,5,0));
 676     CPPUNIT_ASSERT_MESSAGE("B6 should be shared.", pFC);
 677     CPPUNIT_ASSERT_MESSAGE("B6 should be shared.", pFC->IsShared());
 678     pFC = m_pDoc->GetFormulaCell(ScAddress(1,6,0));
 679     CPPUNIT_ASSERT_MESSAGE("B7 should be shared.", pFC);
 680     CPPUNIT_ASSERT_MESSAGE("B7 should be shared.", pFC->IsShared());
 681
 682     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedTopRow());
 683     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 684
 685     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=SUM($A$5:$A$7)"), m_pDoc->GetFormula(1,4,0));
 686     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=SUM($A$5:$A$7)"), m_pDoc->GetFormula(1,5,0));
 687     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=SUM($A$5:$A$7)"), m_pDoc->GetFormula(1,6,0));
 688
 689     m_pDoc->DeleteTab(0);
 690 }
 691
 692 namespace {
 693
 694 struct SortByArea
 695 {
 696     bool operator ()( const sc::AreaListener& rLeft, const sc::AreaListener& rRight ) const
 697     {
 698         if (rLeft.maArea.aStart.Tab() != rRight.maArea.aStart.Tab())
 699             return rLeft.maArea.aStart.Tab() < rRight.maArea.aStart.Tab();
 700
 701         if (rLeft.maArea.aStart.Col() != rRight.maArea.aStart.Col())
 702             return rLeft.maArea.aStart.Col() < rRight.maArea.aStart.Col();
 703
 704         if (rLeft.maArea.aStart.Row() != rRight.maArea.aStart.Row())
 705             return rLeft.maArea.aStart.Row() < rRight.maArea.aStart.Row();
 706
 707         if (rLeft.maArea.aEnd.Tab() != rRight.maArea.aEnd.Tab())
 708             return rLeft.maArea.aEnd.Tab() < rRight.maArea.aEnd.Tab();
 709
 710         if (rLeft.maArea.aEnd.Col() != rRight.maArea.aEnd.Col())
 711             return rLeft.maArea.aEnd.Col() < rRight.maArea.aEnd.Col();
 712
 713         return rLeft.maArea.aEnd.Row() < rRight.maArea.aEnd.Row();
 714     }
 715 };
 716
 717 }
 718
 719 void TestSharedFormula::testSharedFormulasRefUpdateRangeDeleteRow()
 720 {
 721     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
 722     m_pDoc->InsertTab(0, "Formula");
 723
 724     ScRange aWholeArea(0, 0, 0, 100, 100, 0); // Large enough for all references used in the test.
 725
 726     const std::vector<std::vector<const char*>> aData = {
 727         { "1", "2", "=SUM(A1:B1)" },
 728         { "3", "4", "=SUM(A2:B2)" },
 729         { nullptr, nullptr, nullptr },
 730         { "5", "6", "=SUM(A4:B4)" },
 731         { "7", "8", "=SUM(A5:B5)" }
 732     };
 733
 734     insertRangeData(m_pDoc, ScAddress(0,0,0), aData);
 735
 736     // Check initial formula values.
 737     CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(2,0,0)));
 738     CPPUNIT_ASSERT_EQUAL( 7.0, m_pDoc->GetValue(ScAddress(2,1,0)));
 739     CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 740     CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(ScAddress(2,4,0)));
 741
 742     // Check the area listener status.
 743     ScBroadcastAreaSlotMachine* pBASM = m_pDoc->GetBASM();
 744     CPPUNIT_ASSERT(pBASM);
 745     std::vector<sc::AreaListener> aListeners = pBASM->GetAllListeners(aWholeArea, sc::AreaOverlapType::Inside);
 746     std::sort(aListeners.begin(), aListeners.end(), SortByArea());
 747
 748     // This check makes only sense if group listeners are activated.
 749 #if !defined(USE_FORMULA_GROUP_LISTENER) || USE_FORMULA_GROUP_LISTENER
 750     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should only be 2 area listeners.", size_t(2), aListeners.size());
 751     // First one should be group-listening on A1:B2.
 752     CPPUNIT_ASSERT_EQUAL_MESSAGE("This listener should be listening on A1:B2.", ScRange(0,0,0,1,1,0), aListeners[0].maArea);
 753     CPPUNIT_ASSERT_MESSAGE("This listener should be group-listening.", aListeners[0].mbGroupListening);
 754     // Second one should be group-listening on A4:B5.
 755     CPPUNIT_ASSERT_EQUAL_MESSAGE("This listener should be listening on A1:B2.", ScRange(0,0,0,1,1,0), aListeners[0].maArea);
 756     CPPUNIT_ASSERT_MESSAGE("This listener should be group-listening.", aListeners[0].mbGroupListening);
 757 #endif
 758
 759     // Make sure that C1:C2 and C4:C5 are formula groups.
 760     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
 761     CPPUNIT_ASSERT(pFC);
 762     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 763     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 764
 765     pFC = m_pDoc->GetFormulaCell(ScAddress(2,3,0));
 766     CPPUNIT_ASSERT(pFC);
 767     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedTopRow());
 768     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 769
 770     // Delete row 3.  This will merge the two formula groups.
 771     ScDocFunc& rFunc = m_xDocShell->GetDocFunc();
 772     ScMarkData aMark(m_pDoc->GetSheetLimits());
 773     aMark.SelectOneTable(0);
 774     rFunc.DeleteCells(ScRange(0,2,0,m_pDoc->MaxCol(),2,0), &aMark, DelCellCmd::Rows, true);
 775
 776     // Make sure C1:C4 belong to the same group.
 777     pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
 778     CPPUNIT_ASSERT(pFC);
 779     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 780     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
 781
 782     // This check makes only sense if group listeners are activated.
 783 #if !defined(USE_FORMULA_GROUP_LISTENER) || USE_FORMULA_GROUP_LISTENER
 784     // We should only have one listener group-listening on A1:B4.
 785     aListeners = pBASM->GetAllListeners(aWholeArea, sc::AreaOverlapType::Inside);
 786     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should only be 1 area listener.", size_t(1), aListeners.size());
 787     CPPUNIT_ASSERT_EQUAL_MESSAGE("This listener should be listening on A1:B4.", ScRange(0,0,0,1,3,0), aListeners[0].maArea);
 788     CPPUNIT_ASSERT_MESSAGE("This listener should be group-listening.", aListeners[0].mbGroupListening);
 789 #endif
 790
 791     // Change the value of B4 and make sure the value of C4 changes.
 792     rFunc.SetValueCell(ScAddress(1,3,0), 100.0, false);
 793     CPPUNIT_ASSERT_EQUAL(107.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 794
 795     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
 796     CPPUNIT_ASSERT(pUndoMgr);
 797
 798     // Undo the value change in B4, and make sure C4 follows.
 799     pUndoMgr->Undo();
 800     CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 801
 802     // Undo the deletion of row 3.
 803     pUndoMgr->Undo();
 804
 805     // Make sure that C1:C2 and C4:C5 are formula groups again.
 806     pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
 807     CPPUNIT_ASSERT(pFC);
 808     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 809     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 810
 811     pFC = m_pDoc->GetFormulaCell(ScAddress(2,3,0));
 812     CPPUNIT_ASSERT(pFC);
 813     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedTopRow());
 814     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 815
 816     // Check the values of formula cells again.
 817     CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(2,0,0)));
 818     CPPUNIT_ASSERT_EQUAL( 7.0, m_pDoc->GetValue(ScAddress(2,1,0)));
 819     CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 820     CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(ScAddress(2,4,0)));
 821
 822     aListeners = pBASM->GetAllListeners(aWholeArea, sc::AreaOverlapType::Inside);
 823     std::sort(aListeners.begin(), aListeners.end(), SortByArea());
 824
 825     // This check makes only sense if group listeners are activated.
 826 #if !defined(USE_FORMULA_GROUP_LISTENER) || USE_FORMULA_GROUP_LISTENER
 827     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should only be 2 area listeners.", size_t(2), aListeners.size());
 828     // First one should be group-listening on A1:B2.
 829     CPPUNIT_ASSERT_EQUAL_MESSAGE("This listener should be listening on A1:B2.", ScRange(0,0,0,1,1,0), aListeners[0].maArea);
 830     CPPUNIT_ASSERT_MESSAGE("This listener should be group-listening.", aListeners[0].mbGroupListening);
 831     // Second one should be group-listening on A4:B5.
 832     CPPUNIT_ASSERT_EQUAL_MESSAGE("This listener should be listening on A1:B2.", ScRange(0,0,0,1,1,0), aListeners[0].maArea);
 833     CPPUNIT_ASSERT_MESSAGE("This listener should be group-listening.", aListeners[0].mbGroupListening);
 834 #endif
 835
 836     m_pDoc->DeleteTab(0);
 837 }
 838
 839 void TestSharedFormula::testSharedFormulasRefUpdateExternal()
 840 {
 841     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
 842     m_pDoc->InsertTab(0, "Formula");
 843
 844     // Launch an external document shell.
 845     ScDocShellRef xExtDocSh = new ScDocShell;
 846
 847     SfxMedium* pMed = new SfxMedium("file:///extdata.fake", StreamMode::STD_READWRITE);
 848     xExtDocSh->DoLoad(pMed);
 849     ScDocument& rExtDoc = xExtDocSh->GetDocument();
 850
 851     // Populate A1:A3.
 852     rExtDoc.InsertTab(0, "Data");
 853     rExtDoc.SetString(ScAddress(0,0,0), "A");
 854     rExtDoc.SetString(ScAddress(0,1,0), "B");
 855     rExtDoc.SetString(ScAddress(0,2,0), "C");
 856
 857     {
 858         // Insert formula cells in A7:A10 of the host document, referencing A1:A3
 859         // of the external document.
 860         const std::vector<std::vector<const char*>> aData = {
 861             { "='file:///extdata.fake'#$Data.A1" },
 862             { "='file:///extdata.fake'#$Data.A2" },
 863             { "='file:///extdata.fake'#$Data.A3" },
 864             { "=COUNTA('file:///extdata.fake'#$Data.A1:A3)" }
 865         };
 866
 867         insertRangeData(m_pDoc, ScAddress(0,6,0), aData);
 868     }
 869
 870     // Check the formula results.
 871     CPPUNIT_ASSERT_EQUAL(OUString("A"), m_pDoc->GetString(ScAddress(0,6,0)));
 872     CPPUNIT_ASSERT_EQUAL(OUString("B"), m_pDoc->GetString(ScAddress(0,7,0)));
 873     CPPUNIT_ASSERT_EQUAL(OUString("C"), m_pDoc->GetString(ScAddress(0,8,0)));
 874     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,9,0)));
 875
 876     // Check the formulas too.
 877     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A1"), m_pDoc->GetFormula(0,6,0));
 878     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A2"), m_pDoc->GetFormula(0,7,0));
 879     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A3"), m_pDoc->GetFormula(0,8,0));
 880     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=COUNTA('file:///extdata.fake'#$Data.A1:A3)"), m_pDoc->GetFormula(0,9,0));
 881
 882     // Delete rows 1 and 2. This should not change the references in the formula cells below.
 883     ScDocFunc& rDocFunc = m_xDocShell->GetDocFunc();
 884     ScMarkData aMark(m_pDoc->GetSheetLimits());
 885     aMark.SelectOneTable(0);
 886     rDocFunc.DeleteCells(ScRange(0,0,0,m_pDoc->MaxCol(),1,0), &aMark, DelCellCmd::CellsUp, true);
 887
 888     // Check the shifted formula cells now in A5:A8.
 889     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A1"), m_pDoc->GetFormula(0,4,0));
 890     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A2"), m_pDoc->GetFormula(0,5,0));
 891     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A3"), m_pDoc->GetFormula(0,6,0));
 892     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=COUNTA('file:///extdata.fake'#$Data.A1:A3)"), m_pDoc->GetFormula(0,7,0));
 893
 894     // Undo and check the formulas again.
 895     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
 896     CPPUNIT_ASSERT(pUndoMgr);
 897     pUndoMgr->Undo();
 898     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A1"), m_pDoc->GetFormula(0,6,0));
 899     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A2"), m_pDoc->GetFormula(0,7,0));
 900     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A3"), m_pDoc->GetFormula(0,8,0));
 901     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=COUNTA('file:///extdata.fake'#$Data.A1:A3)"), m_pDoc->GetFormula(0,9,0));
 902
 903     // Redo the row deletion and check the formulas again.
 904     pUndoMgr->Redo();
 905     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A1"), m_pDoc->GetFormula(0,4,0));
 906     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A2"), m_pDoc->GetFormula(0,5,0));
 907     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("='file:///extdata.fake'#$Data.A3"), m_pDoc->GetFormula(0,6,0));
 908     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=COUNTA('file:///extdata.fake'#$Data.A1:A3)"), m_pDoc->GetFormula(0,7,0));
 909
 910     xExtDocSh->DoClose();
 911
 912     m_pDoc->DeleteTab(0);
 913 }
 914
 915 void TestSharedFormula::testSharedFormulasInsertRow()
 916 {
 917     struct
 918     {
 919         bool checkContent( ScDocument* pDoc )
 920         {
 921             // B1:B2 and B4:B5 should point to $A$5.
 922             SCROW pRows[] = { 0, 1, 3, 4 };
 923             for (size_t i = 0; i < SAL_N_ELEMENTS(pRows); ++i)
 924             {
 925                 ScAddress aPos(1, pRows[i], 0);
 926                 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=$A$5"), pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
 927             }
 928
 929             // B1:B2 should be grouped.
 930             ScFormulaCell* pFC = pDoc->GetFormulaCell(ScAddress(1,0,0));
 931             if (!pFC || pFC->GetSharedTopRow() != 0 || pFC->GetSharedLength() != 2)
 932             {
 933                 cerr << "B1:B2 should be grouped." << endl;
 934                 return false;
 935             }
 936
 937             // B4:B5 should be grouped.
 938             pFC = pDoc->GetFormulaCell(ScAddress(1,3,0));
 939             if (!pFC || pFC->GetSharedTopRow() != 3 || pFC->GetSharedLength() != 2)
 940             {
 941                 cerr << "B4:B5 should be grouped." << endl;
 942                 return false;
 943             }
 944
 945             return true;
 946         }
 947
 948         bool checkContentUndo( ScDocument* pDoc )
 949         {
 950             for (SCROW i = 0; i <= 3; ++i)
 951             {
 952                 ScAddress aPos(1,i,0);
 953                 CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=$A$4"), pDoc->GetFormula(aPos.Col(), aPos.Row(), aPos.Tab()));
 954             }
 955
 956             // Ensure that B5 is empty.
 957             if (pDoc->GetCellType(ScAddress(1,4,0)) != CELLTYPE_NONE)
 958             {
 959                 cerr << "B5 should be empty." << endl;
 960                 return false;
 961             }
 962
 963             // B1:B4 should be grouped.
 964             ScFormulaCell* pFC = pDoc->GetFormulaCell(ScAddress(1,0,0));
 965             if (!pFC || pFC->GetSharedTopRow() != 0 || pFC->GetSharedLength() != 4)
 966             {
 967                 cerr << "B1:B4 should be grouped." << endl;
 968                 return false;
 969             }
 970
 971             return true;
 972         }
 973
 974     } aCheck;
 975
 976     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
 977     m_pDoc->InsertTab(0, "Test");
 978
 979     // Scenario inspired by fdo#76470.
 980
 981     // Set value to A4.
 982     m_pDoc->SetValue(ScAddress(0,3,0), 4.0);
 983
 984     {
 985         // Set formula cells in B1:B4 all referencing A4 as absolute reference.
 986         const std::vector<std::vector<const char*>> aData = {
 987             { "=$A$4" },
 988             { "=$A$4" },
 989             { "=$A$4" },
 990             { "=$A$4" }
 991         };
 992
 993         insertRangeData(m_pDoc, ScAddress(1,0,0), aData);
 994     }
 995
 996     // Insert a new row at row 3.
 997     ScDocFunc& rFunc = m_xDocShell->GetDocFunc();
 998     ScMarkData aMark(m_pDoc->GetSheetLimits());
 999     aMark.SelectOneTable(0);
1000     rFunc.InsertCells(ScRange(0,2,0,m_pDoc->MaxCol(),2,0), &aMark, INS_INSROWS_BEFORE, true, true);
1001
1002     bool bResult = aCheck.checkContent(m_pDoc);
1003     CPPUNIT_ASSERT_MESSAGE("Failed on the initial content check.", bResult);
1004
1005     // Undo and check its result.
1006     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1007     CPPUNIT_ASSERT(pUndoMgr);
1008     pUndoMgr->Undo();
1009
1010     bResult = aCheck.checkContentUndo(m_pDoc);
1011     CPPUNIT_ASSERT_MESSAGE("Failed on the content check after undo.", bResult);
1012
1013     // Redo and check its result.
1014     pUndoMgr->Redo();
1015     bResult = aCheck.checkContent(m_pDoc);
1016     CPPUNIT_ASSERT_MESSAGE("Failed on the content check after redo.", bResult);
1017
1018     m_pDoc->DeleteTab(0);
1019 }
1020
1021 void TestSharedFormula::testSharedFormulasDeleteRows()
1022 {
1023     m_pDoc->InsertTab(0, "Test");
1024     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1025
1026     {
1027         // Fill data cells A1:A20 and formula cells B1:B20.  Formulas in
1028         // B1:B10 and B11:B20 should be different.
1029         const std::vector<std::vector<const char*>> aData = {
1030             { "0", "=RC[-1]+1" },
1031             { "1", "=RC[-1]+1" },
1032             { "2", "=RC[-1]+1" },
1033             { "3", "=RC[-1]+1" },
1034             { "4", "=RC[-1]+1" },
1035             { "5", "=RC[-1]+1" },
1036             { "6", "=RC[-1]+1" },
1037             { "7", "=RC[-1]+1" },
1038             { "8", "=RC[-1]+1" },
1039             { "9", "=RC[-1]+1" },
1040             { "10", "=RC[-1]+11" },
1041             { "11", "=RC[-1]+11" },
1042             { "12", "=RC[-1]+11" },
1043             { "13", "=RC[-1]+11" },
1044             { "14", "=RC[-1]+11" },
1045             { "15", "=RC[-1]+11" },
1046             { "16", "=RC[-1]+11" },
1047             { "17", "=RC[-1]+11" },
1048             { "18", "=RC[-1]+11" },
1049             { "19", "=RC[-1]+11" }
1050         };
1051
1052         insertRangeData(m_pDoc, ScAddress(0,0,0), aData);
1053     }
1054
1055     // B1:B10 should be shared.
1056     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
1057     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC);
1058     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC->IsShared());
1059     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1060     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedLength());
1061     // B11:B20 should be shared.
1062     pFC = m_pDoc->GetFormulaCell(ScAddress(1,10,0));
1063     CPPUNIT_ASSERT_MESSAGE("1,10 must be a shared formula cell.", pFC);
1064     CPPUNIT_ASSERT_MESSAGE("1,10 must be a shared formula cell.", pFC->IsShared());
1065     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedTopRow());
1066     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedLength());
1067
1068     // Delete rows 9:12
1069     m_pDoc->DeleteRow(ScRange(0,8,0,m_pDoc->MaxCol(),11,0));
1070
1071     // B1:B8 should be shared.
1072     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
1073     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC);
1074     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC->IsShared());
1075     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1076     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
1077     // B9:B16 should be shared.
1078     pFC = m_pDoc->GetFormulaCell(ScAddress(1,8,0));
1079     CPPUNIT_ASSERT_MESSAGE("1,8 must be a shared formula cell.", pFC);
1080     CPPUNIT_ASSERT_MESSAGE("1,8 must be a shared formula cell.", pFC->IsShared());
1081     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
1082     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
1083
1084     // Delete row 3
1085     m_pDoc->DeleteRow(ScRange(0,2,0,m_pDoc->MaxCol(),2,0));
1086
1087     // B1:B7 should be shared.
1088     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
1089     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC);
1090     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC->IsShared());
1091     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1092     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(7), pFC->GetSharedLength());
1093     // B8:B15 should be shared.
1094     pFC = m_pDoc->GetFormulaCell(ScAddress(1,7,0));
1095     CPPUNIT_ASSERT_MESSAGE("1,7 must be a shared formula cell.", pFC);
1096     CPPUNIT_ASSERT_MESSAGE("1,7 must be a shared formula cell.", pFC->IsShared());
1097     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(7), pFC->GetSharedTopRow());
1098     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
1099
1100     // Delete row 5
1101     m_pDoc->DeleteRow(ScRange(0,4,0,m_pDoc->MaxCol(),4,0));
1102
1103     // B1:B6 should be shared.
1104     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
1105     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC);
1106     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC->IsShared());
1107     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1108     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), pFC->GetSharedLength());
1109     // B7:B14 should be shared.
1110     pFC = m_pDoc->GetFormulaCell(ScAddress(1,6,0));
1111     CPPUNIT_ASSERT_MESSAGE("1,6 must be a shared formula cell.", pFC);
1112     CPPUNIT_ASSERT_MESSAGE("1,6 must be a shared formula cell.", pFC->IsShared());
1113     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), pFC->GetSharedTopRow());
1114     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
1115 }
1116
1117 void TestSharedFormula::testSharedFormulasDeleteColumns()
1118 {
1119     using namespace formula;
1120
1121     m_pDoc->InsertTab(0, "Test");
1122
1123     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1124     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1125
1126     ScDocFunc& rFunc = m_xDocShell->GetDocFunc();
1127     ScMarkData aMark(m_pDoc->GetSheetLimits());
1128     aMark.SelectOneTable(0);
1129
1130     // First, test a single cell case.  A value in B1 and formula in C1.
1131     m_pDoc->SetValue(ScAddress(1,0,0), 11.0);
1132     m_pDoc->SetString(ScAddress(2,0,0), "=RC[-1]");
1133     CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(2,0,0)));
1134
1135     // Delete column B.
1136     rFunc.DeleteCells(ScRange(1,0,0,1,m_pDoc->MaxRow(),0), &aMark, DelCellCmd::CellsLeft, true);
1137     CPPUNIT_ASSERT_EQUAL(OUString("#REF!"), m_pDoc->GetString(ScAddress(1,0,0)));
1138
1139     // The reference should still point to row 1 but the column status should be set to 'deleted'.
1140     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
1141     CPPUNIT_ASSERT(pFC);
1142     const ScTokenArray* pCode = pFC->GetCode();
1143     CPPUNIT_ASSERT(pCode);
1144     CPPUNIT_ASSERT_EQUAL(sal_uInt16(1), pCode->GetLen());
1145     const FormulaToken* pToken = pCode->GetArray()[0];
1146     CPPUNIT_ASSERT_EQUAL(svSingleRef, pToken->GetType());
1147     const ScSingleRefData* pSRef = pToken->GetSingleRef();
1148     CPPUNIT_ASSERT(pSRef->IsColDeleted());
1149     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pSRef->toAbs(*m_pDoc, ScAddress(1,0,0)).Row());
1150
1151     // The formula string should show #REF! in lieu of the column position (only for Calc A1 syntax).
1152     sc::CompileFormulaContext aCFCxt(*m_pDoc, FormulaGrammar::GRAM_ENGLISH);
1153     CPPUNIT_ASSERT_EQUAL(OUString("=#REF!1"), pFC->GetFormula(aCFCxt));
1154
1155     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1156     CPPUNIT_ASSERT(pUndoMgr);
1157
1158     // Undo and make sure the deleted flag is gone.
1159     pUndoMgr->Undo();
1160     CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(2,0,0)));
1161     pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
1162     CPPUNIT_ASSERT(pFC);
1163     CPPUNIT_ASSERT_EQUAL(OUString("=B1"), pFC->GetFormula(aCFCxt));
1164
1165     // Clear row 1 and move over to a formula group case.
1166     clearRange(m_pDoc, ScRange(0,0,0,m_pDoc->MaxCol(),0,0));
1167
1168     // Fill A1:B2 with numbers, and C1:C2 with formula that reference those numbers.
1169     for (SCROW i = 0; i <= 1; ++i)
1170     {
1171         m_pDoc->SetValue(ScAddress(0,i,0), (i+1));
1172         m_pDoc->SetValue(ScAddress(1,i,0), (i+11));
1173         m_pDoc->SetString(ScAddress(2,i,0), "=RC[-2]+RC[-1]");
1174         double fCheck = m_pDoc->GetValue(ScAddress(0,i,0));
1175         fCheck += m_pDoc->GetValue(ScAddress(1,i,0));
1176         CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i,0)));
1177     }
1178
1179     // Delete column B.
1180     rFunc.DeleteCells(ScRange(1,0,0,1,m_pDoc->MaxRow(),0), &aMark, DelCellCmd::CellsLeft, true);
1181
1182     for (SCROW i = 0; i <= 1; ++i)
1183     {
1184         ScAddress aPos(1,i,0);
1185         CPPUNIT_ASSERT_EQUAL(OUString("#REF!"), m_pDoc->GetString(aPos));
1186     }
1187
1188     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0)); // B1
1189     CPPUNIT_ASSERT(pFC);
1190     CPPUNIT_ASSERT_EQUAL(OUString("=A1+#REF!1"), pFC->GetFormula(aCFCxt));
1191     pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0)); // B2
1192     CPPUNIT_ASSERT(pFC);
1193     CPPUNIT_ASSERT_EQUAL(OUString("=A2+#REF!2"), pFC->GetFormula(aCFCxt));
1194
1195     // Undo deletion of column B and check the results of C1:C2.
1196     pUndoMgr->Undo();
1197     for (SCROW i = 0; i <= 1; ++i)
1198     {
1199         double fCheck = m_pDoc->GetValue(ScAddress(0,i,0));
1200         fCheck += m_pDoc->GetValue(ScAddress(1,i,0));
1201         CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i,0)));
1202     }
1203
1204     m_pDoc->DeleteTab(0);
1205 }
1206
1207 void TestSharedFormula::testSharedFormulasRefUpdateMoveSheets()
1208 {
1209     m_pDoc->InsertTab(0, "Sheet1");
1210     m_pDoc->InsertTab(1, "Sheet2");
1211     m_pDoc->InsertTab(2, "Sheet3");
1212
1213     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // make sure auto calc is on.
1214
1215     // Switch to R1C1 for ease of repeated formula insertions.
1216     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1217
1218     // Fill numbers in A1:A8 on Sheet2.
1219     for (SCROW i = 0; i <= 7; ++i)
1220         m_pDoc->SetValue(ScAddress(0,i,1), i+1);
1221
1222     // Fill formula cells A1:A8 on Sheet1, to refer to the same cell address on Sheet2.
1223     for (SCROW i = 0; i <= 7; ++i)
1224         m_pDoc->SetString(ScAddress(0,i,0), "=Sheet2!RC");
1225
1226     // Check the results.
1227     for (SCROW i = 0; i <= 7; ++i)
1228         CPPUNIT_ASSERT_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(ScAddress(0,i,0)));
1229
1230     // Move Sheet3 to the leftmost position before Sheet1.
1231     m_pDoc->MoveTab(2, 0);
1232
1233     // Check sheet names.
1234     std::vector<OUString> aTabNames = m_pDoc->GetAllTableNames();
1235     CPPUNIT_ASSERT_MESSAGE("There should be at least 3 sheets.", aTabNames.size() >= 3);
1236     CPPUNIT_ASSERT_EQUAL(OUString("Sheet3"), aTabNames[0]);
1237     CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aTabNames[1]);
1238     CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aTabNames[2]);
1239
1240     // Check the results again on Sheet1.
1241     for (SCROW i = 0; i <= 7; ++i)
1242     {
1243         CPPUNIT_ASSERT_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(ScAddress(0,i,1)));
1244         CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula expression.", OUString("=Sheet2!RC"), m_pDoc->GetFormula(0,i,1));
1245     }
1246
1247     // Insert a new sheet at the left end.
1248     m_pDoc->InsertTab(0, "Sheet4");
1249
1250     // Check sheet names.
1251     aTabNames = m_pDoc->GetAllTableNames();
1252     CPPUNIT_ASSERT_MESSAGE("There should be at least 4 sheets.", aTabNames.size() >= 4);
1253     CPPUNIT_ASSERT_EQUAL(OUString("Sheet4"), aTabNames[0]);
1254     CPPUNIT_ASSERT_EQUAL(OUString("Sheet3"), aTabNames[1]);
1255     CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aTabNames[2]);
1256     CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aTabNames[3]);
1257
1258     // Check the results again on Sheet1.
1259     for (SCROW i = 0; i <= 7; ++i)
1260     {
1261         CPPUNIT_ASSERT_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(ScAddress(0,i,2)));
1262         CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula expression.", OUString("=Sheet2!RC"), m_pDoc->GetFormula(0,i,2));
1263     }
1264
1265     // Delete Sheet4.
1266     m_pDoc->DeleteTab(0);
1267
1268     // Check sheet names.
1269     aTabNames = m_pDoc->GetAllTableNames();
1270     CPPUNIT_ASSERT_MESSAGE("There should be at least 3 sheets.", aTabNames.size() >= 3);
1271     CPPUNIT_ASSERT_EQUAL(OUString("Sheet3"), aTabNames[0]);
1272     CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aTabNames[1]);
1273     CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aTabNames[2]);
1274
1275     // Check the results again on Sheet1.
1276     for (SCROW i = 0; i <= 7; ++i)
1277     {
1278         CPPUNIT_ASSERT_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(ScAddress(0,i,1)));
1279         CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula expression.", OUString("=Sheet2!RC"), m_pDoc->GetFormula(0,i,1));
1280     }
1281
1282     m_pDoc->DeleteTab(2);
1283     m_pDoc->DeleteTab(1);
1284     m_pDoc->DeleteTab(0);
1285 }
1286
1287 void TestSharedFormula::testSharedFormulasRefUpdateCopySheets()
1288 {
1289     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // make sure auto calc is on.
1290
1291     m_pDoc->InsertTab(0, "Sheet1");
1292     m_pDoc->InsertTab(1, "Sheet2");
1293
1294     m_pDoc->SetValue(ScAddress(0,0,1), 1.0); // A1 on Sheet2
1295     m_pDoc->SetValue(ScAddress(0,1,1), 2.0); // A2 on Sheet2
1296
1297     // Reference values on Sheet2, but use absolute sheet references.
1298     m_pDoc->SetString(ScAddress(0,0,0), "=$Sheet2.A1");
1299     m_pDoc->SetString(ScAddress(0,1,0), "=$Sheet2.A2");
1300
1301     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,0,0)));
1302     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1303
1304     // Copy Sheet1 and insert the copied sheet before the current Sheet1 position.
1305     m_pDoc->CopyTab(0, 0);
1306
1307     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=$Sheet2.A1"), m_pDoc->GetFormula(0,0,0));
1308     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=$Sheet2.A2"), m_pDoc->GetFormula(0,1,0));
1309
1310     // Check the values on the copied sheet.
1311     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,0,0)));
1312     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1313
1314     // Check the values on the original sheet.
1315     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,0,1)));
1316     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,1)));
1317
1318     m_pDoc->DeleteTab(2);
1319     m_pDoc->DeleteTab(1);
1320     m_pDoc->DeleteTab(0);
1321 }
1322
1323 void TestSharedFormula::testSharedFormulasRefUpdateDeleteSheets()
1324 {
1325     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // make sure auto calc is on.
1326
1327     m_pDoc->InsertTab(0, "Sheet1");
1328     m_pDoc->InsertTab(1, "Sheet2");
1329
1330     // Set values to B2:B4 on Sheet2.
1331     m_pDoc->SetValue(ScAddress(1,1,1), 1.0);
1332     m_pDoc->SetValue(ScAddress(1,2,1), 2.0);
1333     m_pDoc->SetValue(ScAddress(1,3,1), 3.0);
1334
1335     // Set formulas in A1:A3 on Sheet1 that reference B2:B4 on Sheet2.
1336     m_pDoc->SetString(ScAddress(0,0,0), "=Sheet2.B2");
1337     m_pDoc->SetString(ScAddress(0,1,0), "=Sheet2.B3");
1338     m_pDoc->SetString(ScAddress(0,2,0), "=Sheet2.B4");
1339
1340     // Check the formula results.
1341     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,0,0)));
1342     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1343     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1344
1345     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=Sheet2.B2"), m_pDoc->GetFormula(0,0,0));
1346     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=Sheet2.B3"), m_pDoc->GetFormula(0,1,0));
1347     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=Sheet2.B4"), m_pDoc->GetFormula(0,2,0));
1348
1349     // Delete Sheet2.
1350     ScDocFunc& rFunc = m_xDocShell->GetDocFunc();
1351     rFunc.DeleteTable(1, true);
1352
1353     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=#REF!.B2"), m_pDoc->GetFormula(0,0,0));
1354     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=#REF!.B3"), m_pDoc->GetFormula(0,1,0));
1355     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=#REF!.B4"), m_pDoc->GetFormula(0,2,0));
1356
1357     // Undo the deletion and make sure the formulas are back to the way they were.
1358     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1359     CPPUNIT_ASSERT(pUndoMgr);
1360     pUndoMgr->Undo();
1361
1362     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=Sheet2.B2"), m_pDoc->GetFormula(0,0,0));
1363     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=Sheet2.B3"), m_pDoc->GetFormula(0,1,0));
1364     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString("=Sheet2.B4"), m_pDoc->GetFormula(0,2,0));
1365
1366     // TODO: We can't test redo yet as ScUndoDeleteTab::Redo() relies on
1367     // view shell to do its thing.
1368
1369     m_pDoc->DeleteTab(1);
1370     m_pDoc->DeleteTab(0);
1371 }
1372
1373 void TestSharedFormula::testSharedFormulasCopyPaste()
1374 {
1375     m_pDoc->InsertTab(0, "Test");
1376     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1377
1378     // Fill formula cells B1:B10.
1379     for (SCROW i = 0; i <= 9; ++i)
1380         m_pDoc->SetString(1, i, 0, "=RC[-1]");
1381
1382     ScAddress aPos(1, 8, 0); // B9
1383     ScFormulaCell* pFC = m_pDoc->GetFormulaCell(aPos);
1384     CPPUNIT_ASSERT_MESSAGE("B9 should be a formula cell.", pFC);
1385     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1386     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedLength());
1387     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
1388
1389     // Copy formulas in B6:B9 to the clipboard doc.
1390     ScRange aSrcRange(1,5,0,1,8,0); // B6:B9
1391     ScDocument aClipDoc(SCDOCMODE_CLIP);
1392     copyToClip(m_pDoc, aSrcRange, &aClipDoc);
1393     pFC = aClipDoc.GetFormulaCell(aPos);
1394     CPPUNIT_ASSERT_MESSAGE("B9 in the clip doc should be a formula cell.", pFC);
1395     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(5), pFC->GetSharedTopRow());
1396     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
1397     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
1398
1399     // Paste them to C2:C10.
1400     ScRange aDestRange(2,1,0,2,9,0);
1401     pasteFromClip(m_pDoc, aDestRange, &aClipDoc);
1402     aPos.SetCol(2);
1403     aPos.SetRow(1);
1404     pFC = m_pDoc->GetFormulaCell(aPos);
1405     CPPUNIT_ASSERT_MESSAGE("C2 should be a formula cell.", pFC);
1406     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
1407     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), pFC->GetSharedLength());
1408     CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
1409
1410     ScRange aRange(1,0,0,1,9,0); // B1:B10
1411     ScDocument* pUndoDoc = new ScDocument(SCDOCMODE_UNDO);
1412     pUndoDoc->InitUndo(*m_pDoc, 0, 0, true, true);
1413     m_pDoc->CopyToDocument(aRange, InsertDeleteFlags::CONTENTS, false, *pUndoDoc);
1414     std::unique_ptr<ScUndoPaste> pUndo(createUndoPaste(*m_xDocShell, aRange, ScDocumentUniquePtr(pUndoDoc)));
1415
1416     // First, make sure the formula cells are shared in the undo document.
1417     aPos.SetCol(1);
1418     for (SCROW i = 0; i <= 9; ++i)
1419     {
1420         aPos.SetRow(i);
1421         pFC = pUndoDoc->GetFormulaCell(aPos);
1422         CPPUNIT_ASSERT_MESSAGE("Must be a formula cell.", pFC);
1423         CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1424         CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedLength());
1425         CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
1426     }
1427
1428     // Overwrite B1:B10.
1429     for (SCROW i = 0; i <= 9; ++i)
1430         m_pDoc->SetValue(ScAddress(1,i,0), i*10);
1431
1432     for (SCROW i = 0; i <= 9; ++i)
1433         CPPUNIT_ASSERT_EQUAL_MESSAGE("Numeric cell was expected.", CELLTYPE_VALUE, m_pDoc->GetCellType(ScAddress(1,i,0)));
1434
1435     // Undo the action to fill B1:B10 with formula cells again.
1436     pUndo->Undo();
1437
1438     aPos.SetCol(1);
1439     for (SCROW i = 0; i <= 9; ++i)
1440     {
1441         aPos.SetRow(i);
1442         pFC = m_pDoc->GetFormulaCell(aPos);
1443         CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
1444         CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1445         CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedLength());
1446         CPPUNIT_ASSERT_EQUAL_MESSAGE("The token is expected to be shared.", pFC->GetCode(), pFC->GetSharedCode());
1447     }
1448
1449     m_pDoc->DeleteTab(0);
1450 }
1451
1452 void TestSharedFormula::testSharedFormulaInsertColumn()
1453 {
1454     m_pDoc->InsertTab(0, "Test");
1455
1456     // Set shared formula group over H2:H3.
1457     m_pDoc->SetString(ScAddress(7,1,0), "=G3*B3");
1458     m_pDoc->SetString(ScAddress(7,2,0), "=G4*B4");
1459
1460     // Insert a single column at Column F. This used to crash before fdo#74041.
1461     m_pDoc->InsertCol(ScRange(5,0,0,5,m_pDoc->MaxRow(),0));
1462
1463     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=H3*B3"), m_pDoc->GetFormula(8,1,0));
1464     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=H4*B4"), m_pDoc->GetFormula(8,2,0));
1465
1466     m_pDoc->DeleteTab(0);
1467 }
1468
1469 void TestSharedFormula::testSharedFormulaMoveBlock()
1470 {
1471     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1472     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1473
1474     m_pDoc->InsertTab(0, "Test");
1475
1476     // Set values to A1:A3.
1477     m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
1478     m_pDoc->SetValue(ScAddress(0,1,0), 2.0);
1479     m_pDoc->SetValue(ScAddress(0,2,0), 3.0);
1480
1481     // Set formulas in B1:B3 to reference A1:A3.
1482     m_pDoc->SetString(ScAddress(1,0,0), "=RC[-1]");
1483     m_pDoc->SetString(ScAddress(1,1,0), "=RC[-1]");
1484     m_pDoc->SetString(ScAddress(1,2,0), "=RC[-1]");
1485
1486     ScRange aFormulaRange(1,0,0,1,2,0);
1487
1488     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1489     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1490     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1491
1492     clearFormulaCellChangedFlag(*m_pDoc, aFormulaRange);
1493
1494     // Move A1:A3 to D1:D3.
1495     ScDocFunc& rFunc = m_xDocShell->GetDocFunc();
1496     bool bMoved = rFunc.MoveBlock(ScRange(0,0,0,0,2,0), ScAddress(3,0,0), true, true, false, true);
1497     CPPUNIT_ASSERT(bMoved);
1498
1499     // The result should stay the same.
1500     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1501     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1502     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1503
1504     clearFormulaCellChangedFlag(*m_pDoc, aFormulaRange);
1505
1506     // Make sure these formula cells in B1:B3 have correct positions even after the move.
1507     std::vector<SCROW> aRows { 0, 1, 2 };
1508     bool bRes = checkFormulaPositions(*m_pDoc, 0, 1, aRows.data(), aRows.size());
1509     CPPUNIT_ASSERT(bRes);
1510
1511     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1512     CPPUNIT_ASSERT(pUndoMgr);
1513
1514     // Undo and check the result.
1515     pUndoMgr->Undo();
1516     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1517     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1518     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1519
1520     clearFormulaCellChangedFlag(*m_pDoc, aFormulaRange);
1521
1522     // Redo and check the result.
1523     pUndoMgr->Redo();
1524     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1525     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1526     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1527
1528     // Clear the range and start over.
1529     clearRange(m_pDoc, ScRange(0,0,0,m_pDoc->MaxCol(),m_pDoc->MaxRow(),0));
1530
1531     // Set values 1,2,3,4,5 to A1:A5.
1532     for (SCROW i = 0; i <= 4; ++i)
1533         m_pDoc->SetValue(ScAddress(0,i,0), (i+1));
1534
1535     // Set formulas to B1:B5.
1536     for (SCROW i = 0; i <= 4; ++i)
1537         m_pDoc->SetString(ScAddress(1,i,0), "=RC[-1]");
1538
1539     // Check the initial formula results.
1540     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1541     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1542     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1543     CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1544     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1545
1546     // Move A1:A2 to D2:D3.
1547     bMoved = rFunc.MoveBlock(ScRange(0,0,0,0,1,0), ScAddress(3,1,0), true, true, false, true);
1548     CPPUNIT_ASSERT(bMoved);
1549
1550     // Check the formula values again.  They should not change.
1551     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1552     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1553     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1554     CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1555     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1556
1557     pUndoMgr->Undo();
1558     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1559     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1560     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1561     CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1562     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1563
1564     pUndoMgr->Redo();
1565     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1566     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1567     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1568     CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1569     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1570
1571     m_pDoc->DeleteTab(0);
1572 }
1573
1574 void TestSharedFormula::testSharedFormulaUpdateOnNamedRangeChange()
1575 {
1576     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1577
1578     m_pDoc->InsertTab(0, "Test");
1579
1580     const char* const pName = "MyRange";
1581     const char* const pExpr1 = "$Test.$A$1:$A$3";
1582     const char* const pExpr2 = "$Test.$A$1:$A$4";
1583
1584     RangeNameDef aName;
1585     aName.mpName = pName;
1586     aName.mpExpr = pExpr1;
1587     aName.mnIndex = 1;
1588     std::unique_ptr<ScRangeName> pNames(new ScRangeName);
1589     bool bSuccess = insertRangeNames(m_pDoc, pNames.get(), &aName, &aName + 1);
1590     CPPUNIT_ASSERT(bSuccess);
1591     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pNames->size());
1592     m_pDoc->SetRangeName(std::move(pNames));
1593
1594     // Set values to A1:A4.
1595     m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
1596     m_pDoc->SetValue(ScAddress(0,1,0), 2.0);
1597     m_pDoc->SetValue(ScAddress(0,2,0), 3.0);
1598     m_pDoc->SetValue(ScAddress(0,3,0), 4.0);
1599
1600     // Set formula to B1:B3.
1601     m_pDoc->SetString(ScAddress(1,0,0), "=SUM(MyRange)");
1602     m_pDoc->SetString(ScAddress(1,1,0), "=SUM(MyRange)");
1603     m_pDoc->SetString(ScAddress(1,2,0), "=SUM(MyRange)");
1604
1605     // Set single formula with no named range to B5.
1606     m_pDoc->SetString(ScAddress(1,4,0), "=ROW()");
1607
1608     // Set shared formula with no named range to B7:B8.
1609     m_pDoc->SetString(ScAddress(1,6,0), "=ROW()");
1610     m_pDoc->SetString(ScAddress(1,7,0), "=ROW()");
1611
1612     // B1:B3 should be grouped.
1613     ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
1614     CPPUNIT_ASSERT(pFC);
1615     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1616     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
1617
1618     // B7:B8 should be grouped.
1619     pFC = m_pDoc->GetFormulaCell(ScAddress(1,6,0));
1620     CPPUNIT_ASSERT(pFC);
1621     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), pFC->GetSharedTopRow());
1622     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
1623
1624     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1625     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1626     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1627
1628     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1629     CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(1,6,0)));
1630     CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(ScAddress(1,7,0)));
1631
1632     // Set a single formula to C1.
1633     m_pDoc->SetString(ScAddress(2,0,0), "=AVERAGE(MyRange)");
1634     pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
1635     CPPUNIT_ASSERT(pFC);
1636     CPPUNIT_ASSERT_MESSAGE("C1 should not be shared.", !pFC->IsShared());
1637     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
1638
1639     // Update the range of MyRange.
1640     pNames.reset(new ScRangeName);
1641     aName.mpExpr = pExpr2;
1642     bSuccess = insertRangeNames(m_pDoc, pNames.get(), &aName, &aName + 1);
1643     CPPUNIT_ASSERT(bSuccess);
1644     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pNames->size());
1645     ScDocFunc& rFunc = m_xDocShell->GetDocFunc();
1646
1647     typedef std::map<OUString, ScRangeName> NameMapType;
1648     NameMapType aNewNames;
1649     OUString aScope(STR_GLOBAL_RANGE_NAME);
1650     aNewNames.insert(std::make_pair(aScope, std::move(*pNames)));
1651     rFunc.ModifyAllRangeNames(aNewNames);
1652
1653     // Check to make sure all displayed formulas are still good.
1654     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=SUM(MyRange)"), m_pDoc->GetFormula(1,0,0));
1655     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=SUM(MyRange)"), m_pDoc->GetFormula(1,1,0));
1656     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=SUM(MyRange)"), m_pDoc->GetFormula(1,2,0));
1657     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=ROW()"), m_pDoc->GetFormula(1,4,0));
1658     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=ROW()"), m_pDoc->GetFormula(1,6,0));
1659     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=ROW()"), m_pDoc->GetFormula(1,7,0));
1660     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", OUString("=AVERAGE(MyRange)"), m_pDoc->GetFormula(2,0,0));
1661
1662     // Check the calculation results as well.
1663     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1664     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1665     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1666     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1667     CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(1,6,0)));
1668     CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(ScAddress(1,7,0)));
1669     CPPUNIT_ASSERT_EQUAL(2.5, m_pDoc->GetValue(ScAddress(2,0,0)));
1670
1671     // Change the value of A4 and make sure the value change gets propagated.
1672     m_pDoc->SetValue(ScAddress(0,3,0), 0.0);
1673     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1674     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1675     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1676
1677     m_pDoc->DeleteTab(0);
1678 }
1679
1680 void TestSharedFormula::testSharedFormulaUpdateOnDBChange()
1681 {
1682     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1683
1684     m_pDoc->InsertTab(0, "RangeTest");
1685
1686     // Put 1, 2, 3, 4 in A1:A4.
1687     for (SCROW i = 0; i <= 3; ++i)
1688         m_pDoc->SetValue(ScAddress(0,i,0), (i+1));
1689
1690     ScDBCollection* pDBs = m_pDoc->GetDBCollection();
1691     CPPUNIT_ASSERT_MESSAGE("Failed to fetch DB collection object.", pDBs);
1692
1693     // Define database range 'MyRange' for A1:A2.
1694     std::unique_ptr<ScDBData> pData(new ScDBData("MyRange", 0, 0, 0, 0, 1));
1695     bool bInserted = pDBs->getNamedDBs().insert(std::move(pData));
1696     CPPUNIT_ASSERT_MESSAGE("Failed to insert a new database range.", bInserted);
1697
1698     // Insert in C2:C4 a group of formula cells that reference MyRange.
1699     for (SCROW i = 1; i <= 3; ++i)
1700         m_pDoc->SetString(ScAddress(2,i,0), "=SUM(MyRange)");
1701
1702     // Make sure C2:C4 is a formula group.
1703     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(2,1,0));
1704     CPPUNIT_ASSERT(pFC);
1705     CPPUNIT_ASSERT(pFC->IsSharedTop());
1706     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
1707
1708     // Check the initial formula results.
1709     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1710     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,2,0)));
1711     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,3,0)));
1712
1713     ScDBDocFunc aFunc(*m_xDocShell);
1714
1715     // Change the range referenced by MyRange to A1:A4.
1716     ScDBCollection aNewDBs(*m_pDoc);
1717     std::unique_ptr<ScDBData> pNewData(new ScDBData("MyRange", 0, 0, 0, 0, 3));
1718     bInserted = aNewDBs.getNamedDBs().insert(std::move(pNewData));
1719     CPPUNIT_ASSERT_MESSAGE("Failed to insert a new database range.", bInserted);
1720
1721     std::vector<ScRange> aDeleted;
1722     aFunc.ModifyAllDBData(aNewDBs, aDeleted);
1723
1724     // Check the updated formula results.
1725     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1726     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,2,0)));
1727     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,3,0)));
1728
1729     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1730     CPPUNIT_ASSERT(pUndoMgr);
1731
1732     // Undo and check the results.
1733     pUndoMgr->Undo();
1734     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1735     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,2,0)));
1736     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,3,0)));
1737
1738     // Redo and check the results.
1739     pUndoMgr->Redo();
1740     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1741     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,2,0)));
1742     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,3,0)));
1743
1744     m_pDoc->DeleteTab(0);
1745 }
1746
1747 void TestSharedFormula::testSharedFormulaAbsCellListener()
1748 {
1749     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1750
1751     m_pDoc->InsertTab(0, "Test");
1752
1753     m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
1754
1755     const std::vector<std::vector<const char*>> aData = {
1756         { "=$A$1" },
1757         { "=$A$1" },
1758         { "=$A$1" }
1759     };
1760
1761     insertRangeData(m_pDoc, ScAddress(1,0,0), aData);
1762
1763     // A1 should have 3 listeners listening into it.
1764     const SvtBroadcaster* pBC = m_pDoc->GetBroadcaster(ScAddress(0,0,0));
1765     CPPUNIT_ASSERT(pBC);
1766     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(3), pBC->GetAllListeners().size());
1767
1768     // Check the formula results.
1769     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1770     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1771     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1772
1773     // Change the value of A1 and make sure B1:B3 follows.
1774     m_pDoc->SetValue(ScAddress(0,0,0), 2.5);
1775
1776     CPPUNIT_ASSERT_EQUAL(2.5, m_pDoc->GetValue(ScAddress(1,0,0)));
1777     CPPUNIT_ASSERT_EQUAL(2.5, m_pDoc->GetValue(ScAddress(1,1,0)));
1778     CPPUNIT_ASSERT_EQUAL(2.5, m_pDoc->GetValue(ScAddress(1,2,0)));
1779
1780     m_pDoc->DeleteTab(0);
1781 }
1782
1783 static double checkNewValuesNotification( ScDocument* pDoc, const ScAddress& rOrgPos )
1784 {
1785     ScAddress aPos(rOrgPos);
1786     aPos.IncCol();
1787     pDoc->SetValues( aPos, {1024.0, 2048.0, 4096.0, 8192.0, 16384.0});
1788     aPos = rOrgPos;
1789     double fVal = 0.0;
1790     for (SCROW i=0; i < 5; ++i)
1791     {
1792         fVal += pDoc->GetValue(aPos);
1793         aPos.IncRow();
1794     }
1795     return fVal;
1796 }
1797
1798 void TestSharedFormula::testSharedFormulaUnshareAreaListeners()
1799 {
1800     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1801
1802     m_pDoc->InsertTab(0, "Test");
1803
1804     const std::vector<std::vector<const char*>> aData = {
1805         { "=SUM(B1:B2)", "1" },
1806         { "=SUM(B2:B3)", "2" },
1807         { "=SUM(B3:B4)", "4" },
1808         {             nullptr, "8" }
1809     };
1810
1811     insertRangeData(m_pDoc, ScAddress(0,0,0), aData);
1812
1813     // Check that A1:A3 is a formula group.
1814     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0,0,0));
1815     CPPUNIT_ASSERT(pFC);
1816     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1817     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
1818
1819     m_pDoc->SetValue(ScAddress(0,1,0), 23.0);   // unshare at A2
1820     m_pDoc->SetValue(ScAddress(1,1,0), 16.0);   // change value of B2
1821     m_pDoc->SetValue(ScAddress(1,2,0), 32.0);   // change value of B3
1822     // A1 and A3 should be recalculated.
1823     CPPUNIT_ASSERT_EQUAL(17.0, m_pDoc->GetValue(ScAddress(0,0,0)));
1824     CPPUNIT_ASSERT_EQUAL(40.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1825
1826     clearRange(m_pDoc, ScRange( 0,0,0, 1,3,0));
1827
1828     for (int nRun = 0; nRun < 7; ++nRun)
1829     {
1830         // Data in A2:C6
1831         const ScAddress aOrgPos(0,1,0);
1832         const std::vector<std::vector<const char*>> aData2 = {
1833             { "=SUM(B2:C2)",   "1",   "2" },
1834             { "=SUM(B3:C3)",   "4",   "8" },
1835             { "=SUM(B4:C4)",  "16",  "32" },
1836             { "=SUM(B5:C5)",  "64", "128" },
1837             { "=SUM(B6:C6)", "256", "512" },
1838         };
1839         insertRangeData(m_pDoc, aOrgPos, aData2);
1840
1841         // Check that A2:A6 is a formula group.
1842         pFC = m_pDoc->GetFormulaCell(aOrgPos);
1843         CPPUNIT_ASSERT(pFC);
1844         CPPUNIT_ASSERT_MESSAGE("A2", pFC->IsSharedTop());
1845         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aOrgPos.Row(), pFC->GetSharedTopRow());
1846         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(5), pFC->GetSharedLength());
1847
1848         // Overwrite and thus unshare formula in A3.
1849         // Check different code paths with different methods.
1850         ScAddress aPos(aOrgPos);
1851         aPos.IncRow(2);
1852         switch (nRun)
1853         {
1854             case 0:
1855                 // Directly set a different formula cell, which bypasses
1856                 // ScDocument::SetString(), mimicking formula input in view.
1857                 {
1858                     ScFormulaCell* pCell = new ScFormulaCell( *m_pDoc, aPos, "=B4");
1859                     ScDocFunc& rDocFunc = m_xDocShell->GetDocFunc();
1860                     rDocFunc.SetFormulaCell( aPos, pCell, false);
1861                 }
1862             break;
1863             case 1:
1864                 m_pDoc->SetString( aPos, "=B4");    // set formula
1865             break;
1866             case 2:
1867                 m_pDoc->SetString( aPos, "x");      // set string
1868             break;
1869             case 3:
1870                 m_pDoc->SetString( aPos, "4096");   // set number/numeric
1871             break;
1872             case 4:
1873                 m_pDoc->SetValue( aPos, 4096.0);    // set numeric
1874             break;
1875             case 5:
1876                 m_pDoc->SetValues( aPos, {4096.0}); // set numeric vector
1877             break;
1878             case 6:
1879                 // Set formula cell vector.
1880                 {
1881                     ScFormulaCell* pCell = new ScFormulaCell( *m_pDoc, aPos, "=B4");
1882                     std::vector<ScFormulaCell*> aCells { pCell };
1883                     m_pDoc->SetFormulaCells( aPos, aCells);
1884                 }
1885             break;
1886         }
1887
1888         // Check that A2:A3 and A5:A6 are two formula groups.
1889         aPos = aOrgPos;
1890         pFC = m_pDoc->GetFormulaCell(aPos);
1891         CPPUNIT_ASSERT(pFC);
1892         CPPUNIT_ASSERT_MESSAGE("A2", pFC->IsSharedTop());
1893         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
1894         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
1895         aPos.IncRow(3);
1896         pFC = m_pDoc->GetFormulaCell(aPos);
1897         CPPUNIT_ASSERT(pFC);
1898         CPPUNIT_ASSERT_MESSAGE("A5", pFC->IsSharedTop());
1899         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
1900         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
1901
1902         // Check that listeners were set up and formulas are updated when B2:B6
1903         // get new values input (tdf#123736).
1904         aPos = aOrgPos;
1905         aPos.IncCol();
1906         m_pDoc->SetValues( aPos, {1024.0, 2048.0, 4096.0, 8192.0, 16384.0});
1907
1908         aPos = aOrgPos;
1909         CPPUNIT_ASSERT_EQUAL(1026.0, m_pDoc->GetValue(aPos));
1910         aPos.IncRow();
1911         CPPUNIT_ASSERT_EQUAL(2056.0, m_pDoc->GetValue(aPos));
1912         aPos.IncRow();
1913         if (nRun != 2)  // if not string
1914             CPPUNIT_ASSERT_EQUAL(4096.0, m_pDoc->GetValue(aPos));
1915         aPos.IncRow();
1916         CPPUNIT_ASSERT_EQUAL(8320.0, m_pDoc->GetValue(aPos));
1917         aPos.IncRow();
1918         CPPUNIT_ASSERT_EQUAL(16896.0, m_pDoc->GetValue(aPos));
1919
1920         clearRange(m_pDoc, ScRange( 0,0,0, 2,5,0));
1921     }
1922
1923     // Check detach/regroup combinations of overlapping when setting formula
1924     // cell vectors.
1925     {
1926         // Fixed data in A3:C7, modified formula range A1:A9
1927         const ScAddress aOrgPos(0,2,0);
1928         ScAddress aPos( ScAddress::UNINITIALIZED);
1929         ScFormulaCell* pCell;
1930         std::vector<ScFormulaCell*> aCells;
1931         const std::vector<std::vector<const char*>> aData2 = {
1932             { "=SUM(B3:C3)",   "1",   "2" },
1933             { "=SUM(B4:C4)",   "4",   "8" },
1934             { "=SUM(B5:C5)",  "16",  "32" },
1935             { "=SUM(B6:C6)",  "64", "128" },
1936             { "=SUM(B7:C7)", "256", "512" },
1937         };
1938
1939         insertRangeData(m_pDoc, aOrgPos, aData2);
1940
1941         // Add grouping formulas in A1:A2, keep A3:A7
1942         aPos = ScAddress(0,0,0);
1943         std::vector<ScFormulaCell*>().swap( aCells);
1944         pCell = new ScFormulaCell( *m_pDoc, aPos, "=SUM(B1:C1)");
1945         aCells.push_back(pCell);
1946         aPos.IncRow();
1947         pCell = new ScFormulaCell( *m_pDoc, aPos, "=SUM(B2:C2)");
1948         aCells.push_back(pCell);
1949         aPos.IncRow(-1);
1950         m_pDoc->SetFormulaCells( aPos, aCells);
1951
1952         // Check it is one formula group.
1953         pFC = m_pDoc->GetFormulaCell(aPos);
1954         CPPUNIT_ASSERT(pFC);
1955         CPPUNIT_ASSERT_MESSAGE("A1", pFC->IsSharedTop());
1956         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
1957         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(7), pFC->GetSharedLength());
1958
1959         // Check notification of setting new values.
1960         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
1961
1962         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
1963
1964         insertRangeData(m_pDoc, aOrgPos, aData2);
1965
1966         // Add formulas in A1:A2, keep A3:A7
1967         aPos = ScAddress(0,0,0);
1968         std::vector<ScFormulaCell*>().swap( aCells);
1969         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B1+C1");
1970         aCells.push_back(pCell);
1971         aPos.IncRow();
1972         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B2+C2");
1973         aCells.push_back(pCell);
1974         aPos.IncRow(-1);
1975         m_pDoc->SetFormulaCells( aPos, aCells);
1976
1977         // Check formula groups.
1978         pFC = m_pDoc->GetFormulaCell(aPos);
1979         CPPUNIT_ASSERT(pFC);
1980         CPPUNIT_ASSERT_MESSAGE("A1", pFC->IsSharedTop());
1981         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
1982         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
1983         aPos.IncRow(2);
1984         pFC = m_pDoc->GetFormulaCell(aPos);
1985         CPPUNIT_ASSERT(pFC);
1986         CPPUNIT_ASSERT_MESSAGE("A3", pFC->IsSharedTop());
1987         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
1988         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(5), pFC->GetSharedLength());
1989
1990         // Check notification of setting new values.
1991         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
1992
1993         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
1994
1995         insertRangeData(m_pDoc, aOrgPos, aData2);
1996
1997         // Add formula in A2, overwrite A3, keep A4:A7
1998         aPos = ScAddress(0,1,0);
1999         std::vector<ScFormulaCell*>().swap( aCells);
2000         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B2+C2");
2001         aCells.push_back(pCell);
2002         aPos.IncRow();
2003         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B3+C3");
2004         aCells.push_back(pCell);
2005         aPos.IncRow(-1);
2006         m_pDoc->SetFormulaCells( aPos, aCells);
2007
2008         // Check formula groups.
2009         pFC = m_pDoc->GetFormulaCell(aPos);
2010         CPPUNIT_ASSERT(pFC);
2011         CPPUNIT_ASSERT_MESSAGE("A2", pFC->IsSharedTop());
2012         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2013         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2014         aPos.IncRow(2);
2015         pFC = m_pDoc->GetFormulaCell(aPos);
2016         CPPUNIT_ASSERT(pFC);
2017         CPPUNIT_ASSERT_MESSAGE("A4", pFC->IsSharedTop());
2018         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2019         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(4), pFC->GetSharedLength());
2020
2021         // Check notification of setting new values.
2022         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
2023
2024         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
2025
2026         insertRangeData(m_pDoc, aOrgPos, aData2);
2027
2028         // Overwrite A3:A4, keep A5:A7
2029         aPos = ScAddress(0,2,0);
2030         std::vector<ScFormulaCell*>().swap( aCells);
2031         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B3+C3");
2032         aCells.push_back(pCell);
2033         aPos.IncRow();
2034         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B4+C4");
2035         aCells.push_back(pCell);
2036         aPos.IncRow(-1);
2037         m_pDoc->SetFormulaCells( aPos, aCells);
2038
2039         // Check formula groups.
2040         aPos = aOrgPos;
2041         pFC = m_pDoc->GetFormulaCell(aPos);
2042         CPPUNIT_ASSERT(pFC);
2043         CPPUNIT_ASSERT_MESSAGE("A3", pFC->IsSharedTop());
2044         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2045         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2046         aPos.IncRow(2);
2047         pFC = m_pDoc->GetFormulaCell(aPos);
2048         CPPUNIT_ASSERT(pFC);
2049         CPPUNIT_ASSERT_MESSAGE("A5", pFC->IsSharedTop());
2050         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2051         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(3), pFC->GetSharedLength());
2052
2053         // Check notification of setting new values.
2054         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
2055
2056         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
2057
2058         insertRangeData(m_pDoc, aOrgPos, aData2);
2059
2060         // Keep A3, overwrite A4:A5, keep A6:A7
2061         aPos = ScAddress(0,3,0);
2062         std::vector<ScFormulaCell*>().swap( aCells);
2063         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B4+C4");
2064         aCells.push_back(pCell);
2065         aPos.IncRow();
2066         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B5+C5");
2067         aCells.push_back(pCell);
2068         aPos.IncRow(-1);
2069         m_pDoc->SetFormulaCells( aPos, aCells);
2070
2071         // Check formula groups.
2072         aPos = aOrgPos;
2073         pFC = m_pDoc->GetFormulaCell(aPos);
2074         CPPUNIT_ASSERT(pFC);
2075         CPPUNIT_ASSERT_MESSAGE("A3", !pFC->IsSharedTop());
2076         aPos.IncRow(1);
2077         pFC = m_pDoc->GetFormulaCell(aPos);
2078         CPPUNIT_ASSERT(pFC);
2079         CPPUNIT_ASSERT_MESSAGE("A4", pFC->IsSharedTop());
2080         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2081         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2082         aPos.IncRow(2);
2083         pFC = m_pDoc->GetFormulaCell(aPos);
2084         CPPUNIT_ASSERT(pFC);
2085         CPPUNIT_ASSERT_MESSAGE("A6", pFC->IsSharedTop());
2086         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2087         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2088
2089         // Check notification of setting new values.
2090         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
2091
2092         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
2093
2094         insertRangeData(m_pDoc, aOrgPos, aData2);
2095
2096         // Keep A3:A4, overwrite A5:A6, keep A7
2097         aPos = ScAddress(0,4,0);
2098         std::vector<ScFormulaCell*>().swap( aCells);
2099         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B5+C5");
2100         aCells.push_back(pCell);
2101         aPos.IncRow();
2102         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B6+C6");
2103         aCells.push_back(pCell);
2104         aPos.IncRow(-1);
2105         m_pDoc->SetFormulaCells( aPos, aCells);
2106
2107         // Check formula groups.
2108         aPos = aOrgPos;
2109         pFC = m_pDoc->GetFormulaCell(aPos);
2110         CPPUNIT_ASSERT(pFC);
2111         CPPUNIT_ASSERT_MESSAGE("A3", pFC->IsSharedTop());
2112         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2113         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2114         aPos.IncRow(2);
2115         pFC = m_pDoc->GetFormulaCell(aPos);
2116         CPPUNIT_ASSERT(pFC);
2117         CPPUNIT_ASSERT_MESSAGE("A5", pFC->IsSharedTop());
2118         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2119         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2120         aPos.IncRow(2);
2121         pFC = m_pDoc->GetFormulaCell(aPos);
2122         CPPUNIT_ASSERT(pFC);
2123         CPPUNIT_ASSERT_MESSAGE("A7", !pFC->IsSharedTop());
2124
2125         // Check notification of setting new values.
2126         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
2127
2128         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
2129
2130         insertRangeData(m_pDoc, aOrgPos, aData2);
2131
2132         // Keep A3:A5, overwrite A6:A7
2133         aPos = ScAddress(0,5,0);
2134         std::vector<ScFormulaCell*>().swap( aCells);
2135         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B6+C6");
2136         aCells.push_back(pCell);
2137         aPos.IncRow();
2138         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B7+C7");
2139         aCells.push_back(pCell);
2140         aPos.IncRow(-1);
2141         m_pDoc->SetFormulaCells( aPos, aCells);
2142
2143         // Check formula groups.
2144         aPos = aOrgPos;
2145         pFC = m_pDoc->GetFormulaCell(aPos);
2146         CPPUNIT_ASSERT(pFC);
2147         CPPUNIT_ASSERT_MESSAGE("A3", pFC->IsSharedTop());
2148         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2149         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(3), pFC->GetSharedLength());
2150         aPos.IncRow(3);
2151         pFC = m_pDoc->GetFormulaCell(aPos);
2152         CPPUNIT_ASSERT(pFC);
2153         CPPUNIT_ASSERT_MESSAGE("A6", pFC->IsSharedTop());
2154         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2155         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2156
2157         // Check notification of setting new values.
2158         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
2159
2160         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
2161
2162         insertRangeData(m_pDoc, aOrgPos, aData2);
2163
2164         // Keep A3:A6, overwrite A7, add A8
2165         aPos = ScAddress(0,6,0);
2166         std::vector<ScFormulaCell*>().swap( aCells);
2167         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B7+C7");
2168         aCells.push_back(pCell);
2169         aPos.IncRow();
2170         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B8+C8");
2171         aCells.push_back(pCell);
2172         aPos.IncRow(-1);
2173         m_pDoc->SetFormulaCells( aPos, aCells);
2174
2175         // Check formula groups.
2176         aPos = aOrgPos;
2177         pFC = m_pDoc->GetFormulaCell(aPos);
2178         CPPUNIT_ASSERT(pFC);
2179         CPPUNIT_ASSERT_MESSAGE("A3", pFC->IsSharedTop());
2180         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2181         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(4), pFC->GetSharedLength());
2182         aPos.IncRow(4);
2183         pFC = m_pDoc->GetFormulaCell(aPos);
2184         CPPUNIT_ASSERT(pFC);
2185         CPPUNIT_ASSERT_MESSAGE("A7", pFC->IsSharedTop());
2186         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2187         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2188
2189         // Check notification of setting new values.
2190         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
2191
2192         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
2193
2194         insertRangeData(m_pDoc, aOrgPos, aData2);
2195
2196         // Keep A3:A7, add A8:A9
2197         aPos = ScAddress(0,7,0);
2198         std::vector<ScFormulaCell*>().swap( aCells);
2199         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B8+C8");
2200         aCells.push_back(pCell);
2201         aPos.IncRow();
2202         pCell = new ScFormulaCell( *m_pDoc, aPos, "=B9+C9");
2203         aCells.push_back(pCell);
2204         aPos.IncRow(-1);
2205         m_pDoc->SetFormulaCells( aPos, aCells);
2206
2207         // Check formula groups.
2208         aPos = aOrgPos;
2209         pFC = m_pDoc->GetFormulaCell(aPos);
2210         CPPUNIT_ASSERT(pFC);
2211         CPPUNIT_ASSERT_MESSAGE("A3", pFC->IsSharedTop());
2212         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2213         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(5), pFC->GetSharedLength());
2214         aPos.IncRow(5);
2215         pFC = m_pDoc->GetFormulaCell(aPos);
2216         CPPUNIT_ASSERT(pFC);
2217         CPPUNIT_ASSERT_MESSAGE("A7", pFC->IsSharedTop());
2218         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2219         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2220
2221         // Check notification of setting new values.
2222         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
2223
2224         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
2225
2226         insertRangeData(m_pDoc, aOrgPos, aData2);
2227
2228         // Keep A3:A7, add grouping formulas in A8:A9
2229         aPos = ScAddress(0,7,0);
2230         std::vector<ScFormulaCell*>().swap( aCells);
2231         pCell = new ScFormulaCell( *m_pDoc, aPos, "=SUM(B8:C8)");
2232         aCells.push_back(pCell);
2233         aPos.IncRow();
2234         pCell = new ScFormulaCell( *m_pDoc, aPos, "=SUM(B9:C9)");
2235         aCells.push_back(pCell);
2236         aPos.IncRow(-1);
2237         m_pDoc->SetFormulaCells( aPos, aCells);
2238
2239         // Check it is one formula group.
2240         aPos = aOrgPos;
2241         pFC = m_pDoc->GetFormulaCell(aPos);
2242         CPPUNIT_ASSERT(pFC);
2243         CPPUNIT_ASSERT_MESSAGE("A1", pFC->IsSharedTop());
2244         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2245         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(7), pFC->GetSharedLength());
2246
2247         // Check notification of setting new values.
2248         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
2249
2250         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
2251
2252         insertRangeData(m_pDoc, aOrgPos, aData2);
2253
2254         // Overwrite grouping formulas in A4:A5
2255         aPos = ScAddress(0,3,0);
2256         std::vector<ScFormulaCell*>().swap( aCells);
2257         pCell = new ScFormulaCell( *m_pDoc, aPos, "=SUM(B4:C4)");
2258         aCells.push_back(pCell);
2259         aPos.IncRow();
2260         pCell = new ScFormulaCell( *m_pDoc, aPos, "=SUM(B5:C5)");
2261         aCells.push_back(pCell);
2262         aPos.IncRow(-1);
2263         m_pDoc->SetFormulaCells( aPos, aCells);
2264
2265         // Check it is one formula group.
2266         aPos = aOrgPos;
2267         pFC = m_pDoc->GetFormulaCell(aPos);
2268         CPPUNIT_ASSERT(pFC);
2269         CPPUNIT_ASSERT_MESSAGE("A1", pFC->IsSharedTop());
2270         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared top row.", aPos.Row(), pFC->GetSharedTopRow());
2271         CPPUNIT_ASSERT_EQUAL_MESSAGE("Shared length.", static_cast<SCROW>(5), pFC->GetSharedLength());
2272
2273         // Check notification of setting new values.
2274         CPPUNIT_ASSERT_EQUAL(32426.0, checkNewValuesNotification( m_pDoc, aOrgPos));
2275
2276         clearRange(m_pDoc, ScRange( 0,0,0, 2,8,0));
2277     }
2278
2279     m_pDoc->DeleteTab(0);
2280 }
2281
2282 void TestSharedFormula::testSharedFormulaListenerDeleteArea()
2283 {
2284     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2285
2286     m_pDoc->InsertTab(0, "Test0");
2287     m_pDoc->InsertTab(1, "Test1");
2288
2289     const std::vector<std::vector<const char*>> aData0 = {
2290         { "", "", "=Test1.C1" },
2291         { "", "", "=Test1.C2" }
2292     };
2293     const std::vector<std::vector<const char*>> aData1 = {
2294         { "=Test0.A1", "=Test0.B1", "=SUM(A1:B1)" },
2295         { "=Test0.A2", "=Test0.B2", "=SUM(A2:B2)" },
2296     };
2297
2298     insertRangeData(m_pDoc, ScAddress(0,0,0), aData0);
2299     insertRangeData(m_pDoc, ScAddress(0,0,1), aData1);
2300
2301     // Check that Test1.A1:A2 and Test1.B1:B2 are formula groups.
2302     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0,0,1));
2303     CPPUNIT_ASSERT(pFC);
2304     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
2305     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
2306
2307     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,1));
2308     CPPUNIT_ASSERT(pFC);
2309     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
2310     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
2311
2312     m_pDoc->SetValue(ScAddress(0,1,0), 1.0);   // change value of Test0.A2
2313     m_pDoc->SetValue(ScAddress(1,1,0), 2.0);   // change value of Test0.B2
2314     // Test0.C2 should be recalculated.
2315     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,1,0)));
2316
2317     // Delete Test0.B2
2318     clearRange(m_pDoc, ScRange(1,1,0));
2319     // Test0.C2 should be recalculated.
2320     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,1,0)));
2321
2322     m_pDoc->DeleteTab(1);
2323     m_pDoc->DeleteTab(0);
2324 }
2325
2326 void TestSharedFormula::testSharedFormulaUpdateOnReplacement()
2327 {
2328     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2329
2330     m_pDoc->InsertTab(0, "Test");
2331
2332     const std::vector<std::vector<const char*>> aData = {
2333         { "1"              },
2334         { "=SUM($A$1:$A1)" },
2335         { "=SUM($A$1:$A2)" },
2336         { "=SUM($A$1:$A3)" },
2337         { "=SUM($A$1:$A4)" },
2338         { "=SUM($A$1:$A5)" },
2339         { "=SUM($A$1:$A6)" },
2340         { "=SUM($A$1:$A7)" }
2341     };
2342
2343     insertRangeData(m_pDoc, ScAddress(0,0,0), aData);
2344
2345     // Check that A2:A8 is a formula group.
2346     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0,1,0));
2347     CPPUNIT_ASSERT(pFC);
2348     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
2349     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(7), pFC->GetSharedLength());
2350
2351     {   // Check initial results.
2352         ScAddress aPos(0,0,0);
2353         const double fResult[] = { 1.0, 1.0, 2.0, 4.0, 8.0, 16.0, 32.0, 64.0 };
2354         for (SCROW nRow = 1; nRow < 8; ++nRow)
2355         {
2356             aPos.SetRow(nRow);
2357             CPPUNIT_ASSERT_EQUAL( fResult[nRow], m_pDoc->GetValue( aPos));
2358         }
2359     }
2360
2361     // Set up an undo object for deleting A4.
2362     ScRange aUndoRange(0,3,0,0,3,0);
2363     ScMarkData aMark(m_pDoc->GetSheetLimits());
2364     aMark.SelectOneTable(0);
2365     aMark.SetMultiMarkArea(aUndoRange);
2366     ScDocumentUniquePtr pUndoDoc(new ScDocument(SCDOCMODE_UNDO));
2367     pUndoDoc->InitUndo(*m_pDoc, 0, 0);
2368     m_pDoc->CopyToDocument(aUndoRange, InsertDeleteFlags::CONTENTS, false, *pUndoDoc, &aMark);
2369     ScUndoDeleteContents aUndo(m_xDocShell.get(), aMark, aUndoRange, std::move(pUndoDoc), false, InsertDeleteFlags::CONTENTS, true);
2370
2371     // Delete A4.
2372     clearRange(m_pDoc, aUndoRange);
2373
2374     // Check that A2:A3 and A5:A8 are formula groups.
2375     pFC = m_pDoc->GetFormulaCell(ScAddress(0,1,0));
2376     CPPUNIT_ASSERT(pFC);
2377     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
2378     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
2379     pFC = m_pDoc->GetFormulaCell(ScAddress(0,4,0));
2380     CPPUNIT_ASSERT(pFC);
2381     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedTopRow());
2382     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
2383
2384     {   // Check results of A4 deleted.
2385         ScAddress aPos(0,0,0);
2386         const double fResult[] = { 1.0, 1.0, 2.0, 0.0, 4.0, 8.0, 16.0, 32.0 };
2387         for (SCROW nRow = 1; nRow < 8; ++nRow)
2388         {
2389             aPos.SetRow(nRow);
2390             CPPUNIT_ASSERT_EQUAL( fResult[nRow], m_pDoc->GetValue( aPos));
2391         }
2392     }
2393
2394     // Restore A4.
2395     aUndo.Undo();
2396
2397     // Check that A2:A8 is a formula group.
2398     pFC = m_pDoc->GetFormulaCell(ScAddress(0,1,0));
2399     CPPUNIT_ASSERT(pFC);
2400     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
2401     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(7), pFC->GetSharedLength());
2402
2403     {   // Check initial results.
2404         ScAddress aPos(0,0,0);
2405         const double fResult[] = { 1.0, 1.0, 2.0, 4.0, 8.0, 16.0, 32.0, 64.0 };
2406         for (SCROW nRow = 1; nRow < 8; ++nRow)
2407         {
2408             aPos.SetRow(nRow);
2409             CPPUNIT_ASSERT_EQUAL( fResult[nRow], m_pDoc->GetValue( aPos));
2410         }
2411     }
2412
2413     // Delete A4 using selection.
2414     m_pDoc->DeleteSelection(InsertDeleteFlags::ALL, aMark);
2415
2416     // Check that A2:A3 and A5:A8 are formula groups.
2417     pFC = m_pDoc->GetFormulaCell(ScAddress(0,1,0));
2418     CPPUNIT_ASSERT(pFC);
2419     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
2420     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
2421     pFC = m_pDoc->GetFormulaCell(ScAddress(0,4,0));
2422     CPPUNIT_ASSERT(pFC);
2423     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedTopRow());
2424     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
2425
2426     {   // Check results of A4 deleted.
2427         ScAddress aPos(0,0,0);
2428         const double fResult[] = { 1.0, 1.0, 2.0, 0.0, 4.0, 8.0, 16.0, 32.0 };
2429         for (SCROW nRow = 1; nRow < 8; ++nRow)
2430         {
2431             aPos.SetRow(nRow);
2432             CPPUNIT_ASSERT_EQUAL( fResult[nRow], m_pDoc->GetValue( aPos));
2433         }
2434     }
2435
2436     // Restore A4.
2437     aUndo.Undo();
2438
2439     // Check that A2:A8 is a formula group.
2440     pFC = m_pDoc->GetFormulaCell(ScAddress(0,1,0));
2441     CPPUNIT_ASSERT(pFC);
2442     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
2443     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(7), pFC->GetSharedLength());
2444
2445     {   // Check initial results.
2446         ScAddress aPos(0,0,0);
2447         const double fResult[] = { 1.0, 1.0, 2.0, 4.0, 8.0, 16.0, 32.0, 64.0 };
2448         for (SCROW nRow = 1; nRow < 8; ++nRow)
2449         {
2450             aPos.SetRow(nRow);
2451             CPPUNIT_ASSERT_EQUAL( fResult[nRow], m_pDoc->GetValue( aPos));
2452         }
2453     }
2454
2455     // Replace A4 with 0.
2456     m_pDoc->SetString( ScAddress(0,3,0), "0");
2457
2458     // Check that A2:A3 and A5:A8 are formula groups.
2459     pFC = m_pDoc->GetFormulaCell(ScAddress(0,1,0));
2460     CPPUNIT_ASSERT(pFC);
2461     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
2462     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
2463     pFC = m_pDoc->GetFormulaCell(ScAddress(0,4,0));
2464     CPPUNIT_ASSERT(pFC);
2465     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedTopRow());
2466     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
2467
2468     {   // Check results of A4 set to zero.
2469         ScAddress aPos(0,0,0);
2470         const double fResult[] = { 1.0, 1.0, 2.0, 0.0, 4.0, 8.0, 16.0, 32.0 };
2471         for (SCROW nRow = 1; nRow < 8; ++nRow)
2472         {
2473             aPos.SetRow(nRow);
2474             CPPUNIT_ASSERT_EQUAL( fResult[nRow], m_pDoc->GetValue( aPos));
2475         }
2476     }
2477
2478     m_pDoc->DeleteTab(0);
2479 }
2480
2481 void TestSharedFormula::testSharedFormulaDeleteTopCell()
2482 {
2483     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2484
2485     m_pDoc->InsertTab(0, "Test");
2486
2487     const std::vector<std::vector<const char*>> aData = {
2488         { "=SUM(B$1:B$2)", "1" },
2489         { "=SUM(B$1:B$2)", "2" }
2490     };
2491
2492     insertRangeData( m_pDoc, ScAddress(0,0,0), aData);
2493
2494     // Check that A1:A2 is a formula group.
2495     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell( ScAddress(0,0,0));
2496     CPPUNIT_ASSERT(pFC);
2497     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
2498     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
2499
2500     // Check results A1:A2.
2501     CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue( ScAddress(0,0,0)));
2502     CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue( ScAddress(0,1,0)));
2503
2504     // Delete cell A1.
2505     ScMarkData aMark(m_pDoc->GetSheetLimits());
2506     aMark.SelectOneTable(0);
2507     m_xDocShell->GetDocFunc().DeleteCell( ScAddress(0,0,0), aMark, InsertDeleteFlags::CONTENTS, false, /*bApi=*/ true);
2508     // Check it's gone.
2509     CPPUNIT_ASSERT(!m_pDoc->GetFormulaCell( ScAddress(0,0,0)));
2510
2511     // Check result A2.
2512     CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue( ScAddress(0,1,0)));
2513
2514     // Replace B1 with 4.
2515     m_pDoc->SetString( ScAddress(1,0,0), "4");
2516
2517     // Check result A2.
2518     CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue( ScAddress(0,1,0)));
2519
2520     m_pDoc->DeleteTab(0);
2521 }
2522
2523 void TestSharedFormula::testSharedFormulaCutCopyMoveIntoRef()
2524 {
2525     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2526
2527     // tdf#123714 case 1
2528     {
2529         m_pDoc->InsertTab(0, "Test");
2530
2531         // Data in A1:C3
2532         std::vector<std::vector<const char*>> aData = {
2533             { "=B1", "", "1" },
2534             { "=B2", "", "1" },
2535             { "=B3", "", ""  }
2536         };
2537         const ScAddress aOrgPos(0,0,0);
2538         insertRangeData( m_pDoc, aOrgPos, aData);
2539
2540         ScMarkData aMark(m_pDoc->GetSheetLimits());
2541         aMark.SelectOneTable(0);
2542
2543         // Set up clip document.
2544         ScDocument aClipDoc(SCDOCMODE_CLIP);
2545         aClipDoc.ResetClip(m_pDoc, &aMark);
2546         // Cut C1:C2 to clipboard.
2547         cutToClip(*m_xDocShell, ScRange(2,0,0, 2,1,0), &aClipDoc, false);
2548
2549         // Paste to B1:B2
2550         ScRange aPasteRange(1,0,0, 1,1,0);
2551         aMark.SetMarkArea(aPasteRange);
2552         m_pDoc->CopyFromClip( aPasteRange, aMark, InsertDeleteFlags::CONTENTS, nullptr, &aClipDoc);
2553
2554         // Check data in A1:A2 after Paste.
2555         ScAddress aPos(aOrgPos);
2556         CPPUNIT_ASSERT_EQUAL_MESSAGE("A1", 1.0, m_pDoc->GetValue(aPos));
2557         aPos.IncRow();
2558         CPPUNIT_ASSERT_EQUAL_MESSAGE("A2", 1.0, m_pDoc->GetValue(aPos));
2559
2560         m_pDoc->DeleteTab(0);
2561     }
2562
2563     // tdf#123714 case 2
2564     {
2565         m_pDoc->InsertTab(0, "Test");
2566
2567         // Data in A1:C3
2568         std::vector<std::vector<const char*>> aData = {
2569             {  "1",   "2", "=SUM(A1:B1)" },
2570             {  "4",   "8", "=SUM(A2:B2)" },
2571             { "16",  "32", "=SUM(A3:B3)" },
2572             { "64", "128", "=SUM(A4:B4)" },
2573         };
2574         const ScAddress aOrgPos(0,0,0);
2575         insertRangeData( m_pDoc, aOrgPos, aData);
2576
2577         ScAddress aPos;
2578         // Check results in C1:C4
2579         const double fVec0[] = { 3.0, 12.0, 48.0, 192.0 };
2580         aPos = ScAddress(2,0,0);
2581         for (SCROW i=0; i < 4; ++i)
2582         {
2583             CPPUNIT_ASSERT_EQUAL( fVec0[i], m_pDoc->GetValue(aPos));
2584             aPos.IncRow();
2585         }
2586
2587         ScMarkData aMark(m_pDoc->GetSheetLimits());
2588         aMark.SelectOneTable(0);
2589
2590         // Set up clip document.
2591         ScDocument aClipDoc(SCDOCMODE_CLIP);
2592         aClipDoc.ResetClip(m_pDoc, &aMark);
2593         // Cut B1:B2 to clipboard.
2594         cutToClip(*m_xDocShell, ScRange(1,0,0, 1,1,0), &aClipDoc, false);
2595
2596         // Check results in C1:C4 after Cut.
2597         const double fVec1[] = { 1.0, 4.0, 48.0, 192.0 };
2598         aPos = ScAddress(2,0,0);
2599         for (SCROW i=0; i < 4; ++i)
2600         {
2601             CPPUNIT_ASSERT_EQUAL( fVec1[i], m_pDoc->GetValue(aPos));
2602             aPos.IncRow();
2603         }
2604
2605         // Paste to B3:B4
2606         ScRange aPasteRange(1,2,0, 1,3,0);
2607         aMark.SetMarkArea(aPasteRange);
2608         m_pDoc->CopyFromClip( aPasteRange, aMark, InsertDeleteFlags::CONTENTS, nullptr, &aClipDoc);
2609
2610         // Check results in C1:C4 after Paste.
2611         const double fVec2[] = { 1.0, 4.0, 18.0, 72.0 };
2612         aPos = ScAddress(2,0,0);
2613         for (SCROW i=0; i < 4; ++i)
2614         {
2615             CPPUNIT_ASSERT_EQUAL( fVec2[i], m_pDoc->GetValue(aPos));
2616             aPos.IncRow();
2617         }
2618
2619         // Paste to B1:B2
2620         aPasteRange = ScRange(1,0,0, 1,1,0);
2621         aMark.SetMarkArea(aPasteRange);
2622         m_pDoc->CopyFromClip( aPasteRange, aMark, InsertDeleteFlags::CONTENTS, nullptr, &aClipDoc);
2623
2624         // Check results in C1:C4 after Paste.
2625         const double fVec3[] = { 3.0, 12.0, 18.0, 72.0 };
2626         aPos = ScAddress(2,0,0);
2627         for (SCROW i=0; i < 4; ++i)
2628         {
2629             CPPUNIT_ASSERT_EQUAL( fVec3[i], m_pDoc->GetValue(aPos));
2630             aPos.IncRow();
2631         }
2632
2633         m_pDoc->DeleteTab(0);
2634     }
2635 }
2636
2637 // tdf#121002
2638 void TestSharedFormula::testSharedFormulaCutCopyMoveWithRef()
2639 {
2640     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2641
2642     m_pDoc->InsertTab(0, "Test");
2643
2644     // Data in A1:C4
2645     std::vector<std::vector<const char*>> aData = {
2646         {  "",  "", "=SUM(A1:B1)" },
2647         {  "",  "", "=SUM(A2:B2)" },
2648         { "1", "2", "=SUM(A3:B3)" },
2649         { "4", "8", "=SUM(A4:B4)" }
2650     };
2651     const ScAddress aOrgPos(0,0,0);
2652     insertRangeData( m_pDoc, aOrgPos, aData);
2653
2654     ScMarkData aMark(m_pDoc->GetSheetLimits());
2655     aMark.SelectOneTable(0);
2656
2657     ScAddress aPos( ScAddress::UNINITIALIZED);
2658
2659     // Check results in C1:C4
2660     const double fVec0[] = { 0.0, 0.0, 3.0, 12.0 };
2661     aPos = ScAddress(2,0,0);
2662     for (SCROW i=0; i < 4; ++i)
2663     {
2664         CPPUNIT_ASSERT_EQUAL( fVec0[i], m_pDoc->GetValue(aPos));
2665         aPos.IncRow();
2666     }
2667
2668     // Set up clip document.
2669     ScDocument aClipDoc(SCDOCMODE_CLIP);
2670     aClipDoc.ResetClip(m_pDoc, &aMark);
2671     // Cut A3:B3 to clipboard.
2672     cutToClip(*m_xDocShell, ScRange(0,2,0, 1,2,0), &aClipDoc, false);
2673
2674     // Check results in C1:C4 after Cut.
2675     const double fVec1[] = { 0.0, 0.0, 0.0, 12.0 };
2676     aPos = ScAddress(2,0,0);
2677     for (SCROW i=0; i < 4; ++i)
2678     {
2679         CPPUNIT_ASSERT_EQUAL( fVec1[i], m_pDoc->GetValue(aPos));
2680         aPos.IncRow();
2681     }
2682
2683     // Paste to A1:B1
2684     ScRange aPasteRange(0,0,0, 1,0,0);
2685     aMark.SetMarkArea(aPasteRange);
2686     m_pDoc->CopyFromClip( aPasteRange, aMark, InsertDeleteFlags::CONTENTS, nullptr, &aClipDoc);
2687
2688     // Check results in C1:C4 after Paste.
2689     const double fVec2[] = { 3.0, 0.0, 3.0, 12.0 };
2690     aPos = ScAddress(2,0,0);
2691     for (SCROW i=0; i < 4; ++i)
2692     {
2693         CPPUNIT_ASSERT_EQUAL( fVec2[i], m_pDoc->GetValue(aPos));
2694         aPos.IncRow();
2695     }
2696
2697     // Check formulas in C1:C4 after Paste.
2698     const std::u16string_view sForm[] = { u"=SUM(A1:B1)", u"=SUM(A2:B2)", u"=SUM(A1:B1)", u"=SUM(A4:B4)" };
2699     for (SCROW i=0; i < 4; ++i)
2700     {
2701         OUString aFormula = m_pDoc->GetFormula( 2,i,0 );
2702         CPPUNIT_ASSERT_EQUAL( OUString(sForm[i]), aFormula);
2703     }
2704
2705     m_pDoc->DeleteTab(0);
2706 }
2707
2708 // tdf#120013
2709 void TestSharedFormula::testSharedFormulaCutCopyMoveWithinRun()
2710 {
2711     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2712
2713     m_pDoc->InsertTab(0, "Test");
2714
2715     // Data in C3:E9
2716     const std::vector<std::vector<const char*>> aData = {
2717         { "2200",     "", "=SUM(C$3:C3)-SUM(D$3:D3)" },
2718         {     "",     "", "=SUM(C$3:C4)-SUM(D$3:D4)" },
2719         {     "", "1900", "=SUM(C$3:C5)-SUM(D$3:D5)" },
2720         {     "",     "", "=SUM(C$3:C6)-SUM(D$3:D6)" },
2721         { "1600",     "", "=SUM(C$3:C7)-SUM(D$3:D7)" },
2722         {     "", "1000", "=SUM(C$3:C8)-SUM(D$3:D8)" },
2723         {     "",     "", "=SUM(C$3:C9)-SUM(D$3:D9)" }
2724     };
2725     const ScAddress aOrgPos(2,2,0);
2726     insertRangeData( m_pDoc, aOrgPos, aData);
2727
2728     // Check that E3:E9 is a formula group.
2729     const ScAddress aFormulaPos(4,2,0);
2730     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell( aFormulaPos);
2731     CPPUNIT_ASSERT(pFC);
2732     CPPUNIT_ASSERT_EQUAL_MESSAGE( "Shared formula top row.", aFormulaPos.Row(), pFC->GetSharedTopRow());
2733     CPPUNIT_ASSERT_EQUAL_MESSAGE( "Shared formula length.", static_cast<SCROW>(7), pFC->GetSharedLength());
2734
2735     ScAddress aPos( ScAddress::UNINITIALIZED);
2736
2737     // Check results in E3:E9
2738     const double fVec0[] = { 2200.0, 2200.0, 300.0, 300.0, 1900.0, 900.0, 900.0 };
2739     CPPUNIT_ASSERT_EQUAL_MESSAGE( "Number of checks mismatch.", SAL_N_ELEMENTS(fVec0), aData.size());
2740     aPos = aFormulaPos;
2741     for (size_t i=0; i < SAL_N_ELEMENTS(fVec0); ++i)
2742     {
2743         CPPUNIT_ASSERT_EQUAL_MESSAGE( "E3:E9", fVec0[i], m_pDoc->GetValue(aPos));
2744         aPos.IncRow();
2745     }
2746
2747     ScMarkData aMark(m_pDoc->GetSheetLimits());
2748     aMark.SelectOneTable(0);
2749
2750     // Set up clip document.
2751     ScDocument aClipDoc(SCDOCMODE_CLIP);
2752     aClipDoc.ResetClip(m_pDoc, &aMark);
2753     // Cut A8:D8 to clipboard.
2754     cutToClip(*m_xDocShell, ScRange(0,7,0, 3,7,0), &aClipDoc, false);
2755
2756     // Check results in E3:E9 after Cut.
2757     const double fVec1[] = { 2200.0, 2200.0, 300.0, 300.0, 1900.0, 1900.0, 1900.0 };
2758     CPPUNIT_ASSERT_EQUAL_MESSAGE( "Number of checks mismatch.", SAL_N_ELEMENTS(fVec1), aData.size());
2759     aPos = aFormulaPos;
2760     for (size_t i=0; i < SAL_N_ELEMENTS(fVec1); ++i)
2761     {
2762         CPPUNIT_ASSERT_EQUAL_MESSAGE( "E3:E9 after Cut.", fVec1[i], m_pDoc->GetValue(aPos));
2763         aPos.IncRow();
2764     }
2765
2766     // Paste to A4:D4
2767     ScRange aPasteRange(0,3,0, 3,3,0);
2768     aMark.SetMarkArea(aPasteRange);
2769     m_pDoc->CopyFromClip( aPasteRange, aMark, InsertDeleteFlags::CONTENTS, nullptr, &aClipDoc);
2770
2771     // Check results in E3:E9 after Paste.
2772     const double fVec2[] = { 2200.0, 1200.0, -700.0, -700.0, 900.0, 900.0, 900.0 };
2773     CPPUNIT_ASSERT_EQUAL_MESSAGE( "Number of checks mismatch.", SAL_N_ELEMENTS(fVec2), aData.size());
2774     aPos = aFormulaPos;
2775     for (size_t i=0; i < SAL_N_ELEMENTS(fVec2); ++i)
2776     {
2777         CPPUNIT_ASSERT_EQUAL_MESSAGE( "E3:E9 after Paste.", fVec2[i], m_pDoc->GetValue(aPos));
2778         aPos.IncRow();
2779     }
2780
2781     m_pDoc->DeleteTab(0);
2782 }
2783
2784 // tdf#129396
2785 void TestSharedFormula::testSharedFormulaInsertShift()
2786 {
2787     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
2788
2789     m_pDoc->InsertTab(0, "Test");
2790
2791     // Data in A1:C2
2792     const std::vector<std::vector<const char*>> aData = {
2793         { "1", "2", "=SUM(A1:B1)" },
2794         { "4", "8", "=SUM(A2:B2)" }
2795     };
2796     const ScAddress aOrgPos(0,0,0);
2797     insertRangeData( m_pDoc, aOrgPos, aData);
2798
2799     {
2800         // Check that C1:C2 is a formula group.
2801         const ScAddress aFormulaPos(2,0,0);
2802         const ScFormulaCell* pFC = m_pDoc->GetFormulaCell( aFormulaPos);
2803         CPPUNIT_ASSERT(pFC);
2804         CPPUNIT_ASSERT_EQUAL_MESSAGE( "Shared formula top row.", aFormulaPos.Row(), pFC->GetSharedTopRow());
2805         CPPUNIT_ASSERT_EQUAL_MESSAGE( "Shared formula length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2806     }
2807
2808     {
2809         // Check results in C1:C2
2810         ScAddress aPos( aOrgPos);
2811         aPos.SetCol(2);
2812         CPPUNIT_ASSERT_EQUAL_MESSAGE( "C1", 3.0, m_pDoc->GetValue(aPos));
2813         aPos.IncRow();
2814         CPPUNIT_ASSERT_EQUAL_MESSAGE( "C2", 12.0, m_pDoc->GetValue(aPos));
2815     }
2816
2817     const bool bSuccess = m_pDoc->InsertCol( 0, 0, 1, 0, 2, 1);
2818     CPPUNIT_ASSERT_MESSAGE( "InsertCol", bSuccess);
2819
2820     {
2821         // Check that D1:D2 is a formula group.
2822         const ScAddress aFormulaPos(3,0,0);
2823         const ScFormulaCell* pFC = m_pDoc->GetFormulaCell( aFormulaPos);
2824         CPPUNIT_ASSERT(pFC);
2825         CPPUNIT_ASSERT_EQUAL_MESSAGE( "Shared formula top row.", aFormulaPos.Row(), pFC->GetSharedTopRow());
2826         CPPUNIT_ASSERT_EQUAL_MESSAGE( "Shared formula length.", static_cast<SCROW>(2), pFC->GetSharedLength());
2827     }
2828
2829     {
2830         // Modify values in B1:B2
2831         ScAddress aPos( aOrgPos);
2832         aPos.SetCol(1);
2833         m_pDoc->SetValue(aPos, 16.0);
2834         aPos.IncRow();
2835         m_pDoc->SetValue(aPos, 32.0);
2836     }
2837
2838     {
2839         // Check results in D1:D2
2840         ScAddress aPos( aOrgPos);
2841         aPos.SetCol(3);
2842         CPPUNIT_ASSERT_EQUAL_MESSAGE( "D1", 17.0, m_pDoc->GetValue(aPos));
2843         aPos.IncRow();
2844         CPPUNIT_ASSERT_EQUAL_MESSAGE( "D2", 36.0, m_pDoc->GetValue(aPos));
2845     }
2846
2847     m_pDoc->DeleteTab(0);
2848 }
2849
2850 CPPUNIT_TEST_SUITE_REGISTRATION(TestSharedFormula);
2851
2852 CPPUNIT_PLUGIN_IMPLEMENT();
2853
2854 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */