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