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 "ucalc.hxx"
  11 #include "editutil.hxx"
  12 #include "formulacell.hxx"
  13 #include "cellvalue.hxx"
  14 #include "docsh.hxx"
  15 #include "clipparam.hxx"
  16 #include "undoblk.hxx"
  17 #include "scopetools.hxx"
  18 #include <docfunc.hxx>
  19 #include <dbdocfun.hxx>
  20 #include <tokenarray.hxx>
  21 #include <tokenstringcontext.hxx>
  22 #include <globalnames.hxx>
  23 #include <dbdata.hxx>
  24 #include <bcaslot.hxx>
  25 #include <sharedformula.hxx>
  26
  27 #include <svl/sharedstring.hxx>
  28
  29 #include <formula/grammar.hxx>
  30
  31 void Test::testSharedFormulas()
  32 {
  33     m_pDoc->InsertTab(0, "Test");
  34
  35     ScAddress aPos(1, 9, 0); // B10
  36     m_pDoc->SetString(aPos, "=A10*2"); // Insert into B10.
  37     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(aPos);
  38     CPPUNIT_ASSERT_MESSAGE("Expected to be a non-shared cell.", pFC && !pFC->IsShared());
  39
  40     aPos.SetRow(10); // B11
  41     m_pDoc->SetString(aPos, "=A11*2");
  42     pFC = m_pDoc->GetFormulaCell(aPos);
  43     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC && pFC->IsShared());
  44     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), pFC->GetSharedTopRow());
  45     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
  46     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
  47
  48     aPos.SetRow(8); // B9
  49     m_pDoc->SetString(aPos, "=A9*2");
  50     pFC = m_pDoc->GetFormulaCell(aPos);
  51     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC && pFC->IsShared());
  52     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
  53     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
  54     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
  55
  56     aPos.SetRow(12); // B13
  57     m_pDoc->SetString(aPos, "=A13*2");
  58     pFC = m_pDoc->GetFormulaCell(aPos);
  59     CPPUNIT_ASSERT_MESSAGE("This formula cell shouldn't be shared yet.", pFC && !pFC->IsShared());
  60
  61     // Insert a formula to B12, and B9:B13 should be shared.
  62     aPos.SetRow(11); // B12
  63     m_pDoc->SetString(aPos, "=A12*2");
  64     pFC = m_pDoc->GetFormulaCell(aPos);
  65     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC && pFC->IsShared());
  66     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
  67     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(5), pFC->GetSharedLength());
  68     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
  69
  70     // Insert formulas to B15:B16.
  71     aPos.SetRow(14); // B15
  72     m_pDoc->SetString(aPos, "=A15*2");
  73     aPos.SetRow(15); // B16
  74     m_pDoc->SetString(aPos, "=A16*2");
  75     pFC = m_pDoc->GetFormulaCell(aPos);
  76     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC && pFC->IsShared());
  77     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(14), pFC->GetSharedTopRow());
  78     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
  79     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
  80
  81     // Insert a formula to B14, and B9:B16 should be shared.
  82     aPos.SetRow(13); // B14
  83     m_pDoc->SetString(aPos, "=A14*2");
  84     pFC = m_pDoc->GetFormulaCell(aPos);
  85     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC && pFC->IsShared());
  86     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
  87     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
  88     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
  89
  90     // Insert an incompatible formula to B12, to split the shared range to B9:B11 and B13:B16.
  91     aPos.SetRow(11); // B12
  92     m_pDoc->SetString(aPos, "=$A$1*4");
  93     pFC = m_pDoc->GetFormulaCell(aPos);
  94     CPPUNIT_ASSERT_MESSAGE("This cell shouldn't be shared.", pFC && !pFC->IsShared());
  95
  96     aPos.SetRow(8); // B9
  97     pFC = m_pDoc->GetFormulaCell(aPos);
  98     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC && pFC->IsShared());
  99     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
 100     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 101     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 102
 103     aPos.SetRow(12); // B13
 104     pFC = m_pDoc->GetFormulaCell(aPos);
 105     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC && pFC->IsShared());
 106     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(12), pFC->GetSharedTopRow());
 107     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
 108     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 109
 110     // Extend B13:B16 to B13:B20.
 111     aPos.SetRow(16); // B17
 112     m_pDoc->SetString(aPos, "=A17*2");
 113     aPos.IncRow();
 114     m_pDoc->SetString(aPos, "=A18*2");
 115     aPos.IncRow();
 116     m_pDoc->SetString(aPos, "=A19*2");
 117     aPos.IncRow();
 118     m_pDoc->SetString(aPos, "=A20*2");
 119     pFC = m_pDoc->GetFormulaCell(aPos);
 120     CPPUNIT_ASSERT_MESSAGE("This cell is expected to be a shared formula cell.", pFC && pFC->IsShared());
 121     // B13:B20 shuld be shared.
 122     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(12), pFC->GetSharedTopRow());
 123     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
 124     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 125
 126     // Empty B19. This should split it into B13:B18, and B20 non-shared.
 127     aPos.SetRow(18);
 128     m_pDoc->SetEmptyCell(aPos);
 129     CPPUNIT_ASSERT_MESSAGE("This cell should have been emptied.", m_pDoc->GetCellType(aPos) == CELLTYPE_NONE);
 130     aPos.SetRow(12); // B13
 131     pFC = m_pDoc->GetFormulaCell(aPos);
 132     CPPUNIT_ASSERT(pFC);
 133     // B13:B18 should be shared.
 134     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(12), pFC->GetSharedTopRow());
 135     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), pFC->GetSharedLength());
 136     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 137     // B20 shold be non-shared.
 138     aPos.SetRow(19); // B20
 139     pFC = m_pDoc->GetFormulaCell(aPos);
 140     CPPUNIT_ASSERT_MESSAGE("B20 should be a formula cell.", pFC);
 141     CPPUNIT_ASSERT_MESSAGE("This cell should be non-shared.", !pFC->IsShared());
 142
 143     // Empty B14, to make B13 non-shared and B15:B18 shared.
 144     aPos.SetRow(13); // B14
 145     m_pDoc->SetEmptyCell(aPos);
 146     aPos.SetRow(12); // B13
 147     pFC = m_pDoc->GetFormulaCell(aPos);
 148     // B13 should be non-shared.
 149     CPPUNIT_ASSERT_MESSAGE("B13 should be a formula cell.", pFC);
 150     CPPUNIT_ASSERT_MESSAGE("This cell should be non-shared.", !pFC->IsShared());
 151     // B15:B18 should be shared.
 152     aPos.SetRow(14); // B15
 153     pFC = m_pDoc->GetFormulaCell(aPos);
 154     CPPUNIT_ASSERT(pFC);
 155     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(14), pFC->GetSharedTopRow());
 156     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
 157     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 158
 159     // Set numeric value to B15, to make B16:B18 shared.
 160     aPos.SetRow(14);
 161     m_pDoc->SetValue(aPos, 1.2);
 162     aPos.SetRow(15);
 163     pFC = m_pDoc->GetFormulaCell(aPos);
 164     CPPUNIT_ASSERT(pFC);
 165     // B16:B18 should be shared.
 166     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(15), pFC->GetSharedTopRow());
 167     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 168     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 169
 170     // Set string value to B16 to make B17:B18 shared.
 171     aPos.SetRow(15);
 172     ScCellValue aCell(svl::SharedString("Test"));
 173     CPPUNIT_ASSERT_MESSAGE("This should be a string value.", aCell.meType == CELLTYPE_STRING);
 174     aCell.commit(*m_pDoc, aPos);
 175     CPPUNIT_ASSERT_EQUAL(aCell.mpString->getString(), m_pDoc->GetString(aPos));
 176     aPos.SetRow(16);
 177     pFC = m_pDoc->GetFormulaCell(aPos);
 178     CPPUNIT_ASSERT(pFC);
 179     // B17:B18 should be shared.
 180     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(16), pFC->GetSharedTopRow());
 181     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 182     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 183
 184     // Set edit text to B17. Now B18 should be non-shared.
 185     ScFieldEditEngine& rEditEngine = m_pDoc->GetEditEngine();
 186     rEditEngine.SetText("Edit Text");
 187     aPos.SetRow(16);
 188     m_pDoc->SetEditText(aPos, rEditEngine.CreateTextObject());
 189     CPPUNIT_ASSERT_EQUAL(CELLTYPE_EDIT, m_pDoc->GetCellType(aPos));
 190     aPos.SetRow(17);
 191     pFC = m_pDoc->GetFormulaCell(aPos);
 192     CPPUNIT_ASSERT_MESSAGE("B18 should be a formula cell.", pFC);
 193     CPPUNIT_ASSERT_MESSAGE("B18 should be non-shared.", !pFC->IsShared());
 194
 195     // Set up a new group for shared formulas in B2:B10.
 196     clearRange(m_pDoc, ScRange(0,0,0,2,100,0));
 197
 198     aPos.SetRow(1);
 199     m_pDoc->SetString(aPos, "=A2*10");
 200     aPos.IncRow();
 201     m_pDoc->SetString(aPos, "=A3*10");
 202     aPos.IncRow();
 203     m_pDoc->SetString(aPos, "=A4*10");
 204     aPos.IncRow();
 205     m_pDoc->SetString(aPos, "=A5*10");
 206     aPos.IncRow();
 207     m_pDoc->SetString(aPos, "=A6*10");
 208     aPos.IncRow();
 209     m_pDoc->SetString(aPos, "=A7*10");
 210     aPos.IncRow();
 211     m_pDoc->SetString(aPos, "=A8*10");
 212     aPos.IncRow();
 213     m_pDoc->SetString(aPos, "=A9*10");
 214     aPos.IncRow();
 215     m_pDoc->SetString(aPos, "=A10*10");
 216
 217     pFC = m_pDoc->GetFormulaCell(aPos);
 218     CPPUNIT_ASSERT_MESSAGE("B10 should be a formula cell.", pFC);
 219     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 220     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), pFC->GetSharedLength());
 221     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 222
 223     // Delete A4:B8. This should splite the grouping to B2:B3 and B9:B10.
 224     clearRange(m_pDoc, ScRange(0,3,0,1,7,0));
 225     aPos.SetRow(1);
 226     pFC = m_pDoc->GetFormulaCell(aPos);
 227     CPPUNIT_ASSERT_MESSAGE("B2 should be a formula cell.", pFC);
 228     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 229     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 230     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 231
 232     aPos.SetRow(8);
 233     pFC = m_pDoc->GetFormulaCell(aPos);
 234     CPPUNIT_ASSERT_MESSAGE("B9 should be a formula cell.", pFC);
 235     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
 236     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 237     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 238
 239     // Delete rows 4:8 and shift row 9 and below up to row 4.  This should
 240     // re-merge the two into a group of B2:B5.
 241     m_pDoc->DeleteRow(ScRange(0,3,0,MAXCOL,7,0));
 242     aPos.SetRow(1);
 243     pFC = m_pDoc->GetFormulaCell(aPos);
 244     CPPUNIT_ASSERT_MESSAGE("B2 should be a formula cell.", pFC);
 245     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 246     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
 247     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 248
 249     // Insert 2 rows at row 4, to split it into B2:B3 and B6:B7.
 250     m_pDoc->InsertRow(ScRange(0,3,0,MAXCOL,4,0));
 251     pFC = m_pDoc->GetFormulaCell(aPos);
 252     CPPUNIT_ASSERT_MESSAGE("B2 should be a formula cell.", pFC);
 253     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 254     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 255     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 256
 257     aPos.SetRow(5);
 258     pFC = m_pDoc->GetFormulaCell(aPos);
 259     CPPUNIT_ASSERT_MESSAGE("B6 should be a formula cell.", pFC);
 260     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(5), pFC->GetSharedTopRow());
 261     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 262     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
 263
 264     m_pDoc->DeleteTab(0);
 265 }
 266
 267 void Test::testSharedFormulasRefUpdate()
 268 {
 269     m_pDoc->InsertTab(0, "Test");
 270
 271     sc::AutoCalcSwitch aACSwitch(*m_pDoc, false); // turn off auto calculation.
 272
 273     // Set values to A10:A12.
 274     m_pDoc->SetValue(ScAddress(0,9,0), 1);
 275     m_pDoc->SetValue(ScAddress(0,10,0), 2);
 276     m_pDoc->SetValue(ScAddress(0,11,0), 3);
 277
 278     {
 279         // Insert formulas that reference A10:A12 in B1:B3.
 280         const char* pData[][1] = {
 281             { "=A10" },
 282             { "=A11" },
 283             { "=A12" }
 284         };
 285
 286         insertRangeData(m_pDoc, ScAddress(1,0,0), pData, SAL_N_ELEMENTS(pData));
 287     }
 288
 289     if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "A10"))
 290         CPPUNIT_FAIL("Wrong formula in B1");
 291     if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "A11"))
 292         CPPUNIT_FAIL("Wrong formula in B2");
 293     if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "A12"))
 294         CPPUNIT_FAIL("Wrong formula in B3");
 295
 296     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 297     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC && pFC->IsShared());
 298     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 299     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 300
 301     // Insert cells over A11:B11 to shift to right. This should split the B1:B3 grouping into 3.
 302     m_pDoc->InsertCol(ScRange(0,10,0,1,10,0));
 303     if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "A10"))
 304         CPPUNIT_FAIL("Wrong formula in B1");
 305     if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "C11"))
 306         CPPUNIT_FAIL("Wrong formula in B2");
 307     if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "A12"))
 308         CPPUNIT_FAIL("Wrong formula in B3");
 309
 310     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 311     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", pFC && !pFC->IsShared());
 312     pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
 313     CPPUNIT_ASSERT_MESSAGE("B2 should be a non-shared formula cell.", pFC && !pFC->IsShared());
 314     pFC = m_pDoc->GetFormulaCell(ScAddress(1,2,0));
 315     CPPUNIT_ASSERT_MESSAGE("B3 should be a non-shared formula cell.", pFC && !pFC->IsShared());
 316
 317     // Delelte cells over A11:B11 to bring it back to the previous state.
 318     m_pDoc->DeleteCol(ScRange(0,10,0,1,10,0));
 319
 320     if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "A10"))
 321         CPPUNIT_FAIL("Wrong formula in B1");
 322     if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "A11"))
 323         CPPUNIT_FAIL("Wrong formula in B2");
 324     if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "A12"))
 325         CPPUNIT_FAIL("Wrong formula in B3");
 326
 327     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 328     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC && pFC->IsShared());
 329     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 330     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 331
 332     // Insert cells over A11:A12 and shift down.
 333     m_pDoc->InsertRow(ScRange(0,10,0,0,11,0));
 334     if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "A10"))
 335         CPPUNIT_FAIL("Wrong formula in B1");
 336     if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "A13"))
 337         CPPUNIT_FAIL("Wrong formula in B2");
 338     if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "A14"))
 339         CPPUNIT_FAIL("Wrong formula in B3");
 340
 341     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 342     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", pFC && !pFC->IsShared());
 343     pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
 344     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC && pFC->IsShared());
 345     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 346     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 347
 348     // Delete A11:A12 to bring it back to the way it was.
 349     m_pDoc->DeleteRow(ScRange(0,10,0,0,11,0));
 350
 351     if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "A10"))
 352         CPPUNIT_FAIL("Wrong formula in B1");
 353     if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "A11"))
 354         CPPUNIT_FAIL("Wrong formula in B2");
 355     if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "A12"))
 356         CPPUNIT_FAIL("Wrong formula in B3");
 357
 358     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 359     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC && pFC->IsShared());
 360     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 361     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 362
 363     // Insert cells over A11:B11 to shift to right again.
 364     m_pDoc->InsertCol(ScRange(0,10,0,1,10,0));
 365     if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "A10"))
 366         CPPUNIT_FAIL("Wrong formula in B1");
 367     if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "C11"))
 368         CPPUNIT_FAIL("Wrong formula in B2");
 369     if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "A12"))
 370         CPPUNIT_FAIL("Wrong formula in B3");
 371
 372     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 373     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", pFC && !pFC->IsShared());
 374     pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
 375     CPPUNIT_ASSERT_MESSAGE("B2 should be a non-shared formula cell.", pFC && !pFC->IsShared());
 376     pFC = m_pDoc->GetFormulaCell(ScAddress(1,2,0));
 377     CPPUNIT_ASSERT_MESSAGE("B3 should be a non-shared formula cell.", pFC && !pFC->IsShared());
 378
 379     // Insert cells over A12:B12 to shift to right.
 380     m_pDoc->InsertCol(ScRange(0,11,0,1,11,0));
 381     if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "A10"))
 382         CPPUNIT_FAIL("Wrong formula in B1");
 383     if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "C11"))
 384         CPPUNIT_FAIL("Wrong formula in B2");
 385     if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "C12"))
 386         CPPUNIT_FAIL("Wrong formula in B3");
 387
 388     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 389     CPPUNIT_ASSERT_MESSAGE("B1 should be a non-shared formula cell.", pFC && !pFC->IsShared());
 390     // B2 and B3 should be grouped.
 391     pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
 392     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC && pFC->IsShared());
 393     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
 394     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 395
 396     // Insert cells over A10:B10 to shift to right.
 397     m_pDoc->InsertCol(ScRange(0,9,0,1,9,0));
 398     if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "C10"))
 399         CPPUNIT_FAIL("Wrong formula in B1");
 400     if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "C11"))
 401         CPPUNIT_FAIL("Wrong formula in B2");
 402     if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "C12"))
 403         CPPUNIT_FAIL("Wrong formula in B3");
 404
 405     // B1:B3 should be now grouped.
 406     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 407     CPPUNIT_ASSERT_MESSAGE("This must be a shared formula cell.", pFC && pFC->IsShared());
 408     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 409     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 410
 411     m_pDoc->DeleteTab(0);
 412 }
 413
 414 void Test::testSharedFormulasRefUpdateMove()
 415 {
 416     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
 417     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
 418
 419     m_pDoc->InsertTab(0, "Test");
 420
 421     // Set values in B2:B4.
 422     for (SCROW i = 1; i <= 3; ++i)
 423         m_pDoc->SetValue(ScAddress(1,i,0), i);
 424
 425     // Make sure the values are really there.
 426     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,1,0)));
 427     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
 428     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,3,0)));
 429
 430     const char* aData[][1] = {
 431         { "=RC[-1]" },
 432         { "=RC[-1]" },
 433         { "=RC[-1]" }
 434     };
 435
 436     // Set formulas in C2:C4 that reference B2:B4 individually.
 437     insertRangeData(m_pDoc, ScAddress(2,1,0), aData, SAL_N_ELEMENTS(aData));
 438
 439     // Check the formula results.
 440     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,1,0)));
 441     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,2,0)));
 442     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 443
 444     // Move B2:B4 to B1:B3.
 445     bool bMoved = getDocShell().GetDocFunc().MoveBlock(ScRange(1,1,0,1,3,0), ScAddress(1,0,0), true, true, false, true);
 446     CPPUNIT_ASSERT(bMoved);
 447
 448     // Make sure the values have been moved for real.
 449     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
 450     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
 451     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
 452
 453     // The formulas should have been adjusted for the move.
 454     CPPUNIT_ASSERT(checkFormula(*m_pDoc, ScAddress(2,1,0), "R[-1]C[-1]"));
 455     CPPUNIT_ASSERT(checkFormula(*m_pDoc, ScAddress(2,2,0), "R[-1]C[-1]"));
 456     CPPUNIT_ASSERT(checkFormula(*m_pDoc, ScAddress(2,3,0), "R[-1]C[-1]"));
 457
 458     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
 459     CPPUNIT_ASSERT(pUndoMgr);
 460     pUndoMgr->Undo();
 461
 462     // The values should have moved back.
 463     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,1,0)));
 464     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
 465     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,3,0)));
 466
 467     // And the formulas should have been re-adjusted to their original references.
 468     CPPUNIT_ASSERT(checkFormula(*m_pDoc, ScAddress(2,1,0), "RC[-1]"));
 469     CPPUNIT_ASSERT(checkFormula(*m_pDoc, ScAddress(2,2,0), "RC[-1]"));
 470     CPPUNIT_ASSERT(checkFormula(*m_pDoc, ScAddress(2,3,0), "RC[-1]"));
 471
 472     m_pDoc->DeleteTab(0);
 473 }
 474
 475 void Test::testSharedFormulasRefUpdateMove2()
 476 {
 477     sc::AutoCalcSwitch aACSwitch(*m_pDoc, false); // turn auto calc off this time.
 478     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
 479
 480     m_pDoc->InsertTab(0, "Test");
 481
 482     // Set values in B2:B3, and E2:E3.
 483     for (SCROW i = 1; i <= 2; ++i)
 484     {
 485         m_pDoc->SetValue(ScAddress(1,i,0), i);
 486         m_pDoc->SetValue(ScAddress(4,i,0), i);
 487     }
 488
 489     // Make sure the values are really there.
 490     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,1,0)));
 491     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
 492     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(4,1,0)));
 493     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(4,2,0)));
 494
 495     {
 496         // Set formulas in C2:C3 that reference B2:B3 individually, and F2:F3 to E2:E3.
 497         const char* pData[][1] = {
 498             { "=RC[-1]" },
 499             { "=RC[-1]" }
 500         };
 501
 502         insertRangeData(m_pDoc, ScAddress(2,1,0), pData, SAL_N_ELEMENTS(pData));
 503         insertRangeData(m_pDoc, ScAddress(5,1,0), pData, SAL_N_ELEMENTS(pData));
 504     }
 505
 506     m_pDoc->CalcFormulaTree(); // calculate manually.
 507
 508     // Check the formula results.
 509     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,1,0)));
 510     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,2,0)));
 511     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(5,1,0)));
 512     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(5,2,0)));
 513
 514     // Move B2:C3 to C3:D4.
 515     bool bMoved = getDocShell().GetDocFunc().MoveBlock(
 516         ScRange(1,1,0,2,2,0), ScAddress(2,2,0), true, true, false, true);
 517     CPPUNIT_ASSERT(bMoved);
 518
 519     // Make sure the range has been moved.
 520     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,2,0)));
 521     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 522
 523     // The formula cells should retain their results even with auto calc off
 524     // and without recalculation.
 525     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(3,2,0)));
 526     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3,3,0)));
 527
 528     // And these formulas in F2:F3 are unaffected, therefore should not change.
 529     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(5,1,0)));
 530     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(5,2,0)));
 531
 532     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
 533     CPPUNIT_ASSERT(pUndoMgr);
 534
 535     // Undo the move.
 536     pUndoMgr->Undo();
 537
 538     // Check the formula results.  The results should still be intact.
 539     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(2,1,0)));
 540     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,2,0)));
 541     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(5,1,0)));
 542     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(5,2,0)));
 543
 544     m_pDoc->DeleteTab(0);
 545 }
 546
 547 void Test::testSharedFormulasRefUpdateRange()
 548 {
 549     m_pDoc->InsertTab(0, "Test");
 550
 551     // Insert values to A3:A5.
 552     m_pDoc->SetValue(ScAddress(0,2,0), 1);
 553     m_pDoc->SetValue(ScAddress(0,3,0), 2);
 554     m_pDoc->SetValue(ScAddress(0,4,0), 3);
 555
 556     {
 557         // Insert formulas to B3:B5.
 558         const char* pData[][1] = {
 559             { "=SUM($A$3:$A$5)" },
 560             { "=SUM($A$3:$A$5)" },
 561             { "=SUM($A$3:$A$5)" }
 562         };
 563
 564         insertRangeData(m_pDoc, ScAddress(1,2,0), pData, SAL_N_ELEMENTS(pData));
 565     }
 566
 567     if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "SUM($A$3:$A$5)"))
 568         CPPUNIT_FAIL("Wrong formula");
 569     if (!checkFormula(*m_pDoc, ScAddress(1,3,0), "SUM($A$3:$A$5)"))
 570         CPPUNIT_FAIL("Wrong formula");
 571     if (!checkFormula(*m_pDoc, ScAddress(1,4,0), "SUM($A$3:$A$5)"))
 572         CPPUNIT_FAIL("Wrong formula");
 573
 574     // B3:B5 should be shared.
 575     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,2,0));
 576     CPPUNIT_ASSERT_MESSAGE("B3 should be shared.", pFC && pFC->IsShared());
 577     pFC = m_pDoc->GetFormulaCell(ScAddress(1,3,0));
 578     CPPUNIT_ASSERT_MESSAGE("B4 should be shared.", pFC && pFC->IsShared());
 579     pFC = m_pDoc->GetFormulaCell(ScAddress(1,4,0));
 580     CPPUNIT_ASSERT_MESSAGE("B3 should be shared.", pFC && pFC->IsShared());
 581
 582     // Insert 2 rows at row 1.
 583     m_pDoc->InsertRow(ScRange(0,0,0,MAXCOL,1,0));
 584
 585     // B5:B7 should be shared.
 586     pFC = m_pDoc->GetFormulaCell(ScAddress(1,4,0));
 587     CPPUNIT_ASSERT_MESSAGE("B5 should be shared.", pFC && pFC->IsShared());
 588     pFC = m_pDoc->GetFormulaCell(ScAddress(1,5,0));
 589     CPPUNIT_ASSERT_MESSAGE("B6 should be shared.", pFC && pFC->IsShared());
 590     pFC = m_pDoc->GetFormulaCell(ScAddress(1,6,0));
 591     CPPUNIT_ASSERT_MESSAGE("B7 should be shared.", pFC && pFC->IsShared());
 592
 593     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedTopRow());
 594     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
 595
 596     if (!checkFormula(*m_pDoc, ScAddress(1,4,0), "SUM($A$5:$A$7)"))
 597         CPPUNIT_FAIL("Wrong formula");
 598     if (!checkFormula(*m_pDoc, ScAddress(1,5,0), "SUM($A$5:$A$7)"))
 599         CPPUNIT_FAIL("Wrong formula");
 600     if (!checkFormula(*m_pDoc, ScAddress(1,6,0), "SUM($A$5:$A$7)"))
 601         CPPUNIT_FAIL("Wrong formula");
 602
 603     m_pDoc->DeleteTab(0);
 604 }
 605
 606 void Test::testSharedFormulasRefUpdateRangeDeleteRow()
 607 {
 608     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
 609     m_pDoc->InsertTab(0, "Formula");
 610
 611     ScRange aWholeArea(0, 0, 0, 100, 100, 0); // Large enough for all references used in the test.
 612
 613     const char* aData[][3] = {
 614         { "1", "2", "=SUM(A1:B1)" },
 615         { "3", "4", "=SUM(A2:B2)" },
 616         { 0, 0, 0 },
 617         { "5", "6", "=SUM(A4:B4)" },
 618         { "7", "8", "=SUM(A5:B5)" }
 619     };
 620
 621     insertRangeData(m_pDoc, ScAddress(0,0,0), aData, SAL_N_ELEMENTS(aData));
 622
 623     // Check initial formula values.
 624     CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(2,0,0)));
 625     CPPUNIT_ASSERT_EQUAL( 7.0, m_pDoc->GetValue(ScAddress(2,1,0)));
 626     CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 627     CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(ScAddress(2,4,0)));
 628
 629     // Check the area listener status.
 630     ScBroadcastAreaSlotMachine* pBASM = m_pDoc->GetBASM();
 631     CPPUNIT_ASSERT(pBASM);
 632     std::vector<sc::AreaListener> aListeners = pBASM->GetAllListeners(aWholeArea, sc::AreaInside);
 633     std::sort(aListeners.begin(), aListeners.end(), sc::AreaListener::SortByArea());
 634
 635     // This check makes only sense if group listeners are activated.
 636 #if !defined(USE_FORMULA_GROUP_LISTENER) || USE_FORMULA_GROUP_LISTENER
 637     CPPUNIT_ASSERT_MESSAGE("There should only be 2 area listeners.", aListeners.size() == 2);
 638     // First one should be group-listening on A1:B2.
 639     CPPUNIT_ASSERT_MESSAGE("This listener should be listening on A1:B2.", aListeners[0].maArea == ScRange(0,0,0,1,1,0));
 640     CPPUNIT_ASSERT_MESSAGE("This listener should be group-listening.", aListeners[0].mbGroupListening);
 641     // Second one should be group-listening on A4:B5.
 642     CPPUNIT_ASSERT_MESSAGE("This listener should be listening on A1:B2.", aListeners[0].maArea == ScRange(0,0,0,1,1,0));
 643     CPPUNIT_ASSERT_MESSAGE("This listener should be group-listening.", aListeners[0].mbGroupListening);
 644 #endif
 645
 646     // Make sure that C1:C2 and C4:C5 are formula groups.
 647     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
 648     CPPUNIT_ASSERT(pFC);
 649     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 650     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 651
 652     pFC = m_pDoc->GetFormulaCell(ScAddress(2,3,0));
 653     CPPUNIT_ASSERT(pFC);
 654     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedTopRow());
 655     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
 656
 657     // Delete row 3.  This will merge the two formula groups.
 658     ScDocFunc& rFunc = getDocShell().GetDocFunc();
 659     ScMarkData aMark;
 660     aMark.SelectOneTable(0);
 661     rFunc.DeleteCells(ScRange(0,2,0,MAXCOL,2,0), &aMark, DEL_DELROWS, true, true);
 662
 663     // Make sure C1:C4 belong to the same group.
 664     pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
 665     CPPUNIT_ASSERT(pFC);
 666     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 667     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
 668
 669     // This check makes only sense if group listeners are activated.
 670 #if !defined(USE_FORMULA_GROUP_LISTENER) || USE_FORMULA_GROUP_LISTENER
 671     // We should only have one listener group-listening on A1:B4.
 672     aListeners = pBASM->GetAllListeners(aWholeArea, sc::AreaInside);
 673     CPPUNIT_ASSERT_MESSAGE("There should only be 1 area listener.", aListeners.size() == 1);
 674     CPPUNIT_ASSERT_MESSAGE("This listener should be listening on A1:B4.", aListeners[0].maArea == ScRange(0,0,0,1,3,0));
 675     CPPUNIT_ASSERT_MESSAGE("This listener should be group-listening.", aListeners[0].mbGroupListening);
 676 #endif
 677
 678     // Change the value of B4 and make sure the value of C4 changes.
 679     rFunc.SetValueCell(ScAddress(1,3,0), 100.0, false);
 680     CPPUNIT_ASSERT_EQUAL(107.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 681
 682     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
 683     CPPUNIT_ASSERT(pUndoMgr);
 684
 685     // Undo the value change in B4, and make sure C4 follows.
 686     pUndoMgr->Undo();
 687     CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 688
 689     // Undo the deletion of row 3.
 690     pUndoMgr->Undo();
 691
 692     // Check the values of formula cells again.
 693     CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(2,0,0)));
 694     CPPUNIT_ASSERT_EQUAL( 7.0, m_pDoc->GetValue(ScAddress(2,1,0)));
 695     CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(2,3,0)));
 696     CPPUNIT_ASSERT_EQUAL(15.0, m_pDoc->GetValue(ScAddress(2,4,0)));
 697
 698     aListeners = pBASM->GetAllListeners(aWholeArea, sc::AreaInside);
 699     std::sort(aListeners.begin(), aListeners.end(), sc::AreaListener::SortByArea());
 700
 701     // This check makes only sense if group listeners are activated.
 702 #if !defined(USE_FORMULA_GROUP_LISTENER) || USE_FORMULA_GROUP_LISTENER
 703     CPPUNIT_ASSERT_MESSAGE("There should only be 2 area listeners.", aListeners.size() == 2);
 704     // First one should be group-listening on A1:B2.
 705     CPPUNIT_ASSERT_MESSAGE("This listener should be listening on A1:B2.", aListeners[0].maArea == ScRange(0,0,0,1,1,0));
 706     CPPUNIT_ASSERT_MESSAGE("This listener should be group-listening.", aListeners[0].mbGroupListening);
 707     // Second one should be group-listening on A4:B5.
 708     CPPUNIT_ASSERT_MESSAGE("This listener should be listening on A1:B2.", aListeners[0].maArea == ScRange(0,0,0,1,1,0));
 709     CPPUNIT_ASSERT_MESSAGE("This listener should be group-listening.", aListeners[0].mbGroupListening);
 710 #endif
 711
 712     m_pDoc->DeleteTab(0);
 713 }
 714
 715 void Test::testSharedFormulasRefUpdateExternal()
 716 {
 717     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
 718     m_pDoc->InsertTab(0, "Formula");
 719
 720     // Launch an external document shell.
 721     ScDocShellRef xExtDocSh = new ScDocShell;
 722     OUString aExtDocName("file:///extdata.fake");
 723
 724     SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
 725     xExtDocSh->DoInitNew(pMed);
 726     ScDocument& rExtDoc = xExtDocSh->GetDocument();
 727
 728     // Populate A1:A3.
 729     rExtDoc.InsertTab(0, "Data");
 730     rExtDoc.SetString(ScAddress(0,0,0), "A");
 731     rExtDoc.SetString(ScAddress(0,1,0), "B");
 732     rExtDoc.SetString(ScAddress(0,2,0), "C");
 733
 734     {
 735         // Insert formula cells in A7:A10 of the host document, referencing A1:A3
 736         // of the external document.
 737         const char* pData[][1] = {
 738             { "='file:///extdata.fake'#$Data.A1" },
 739             { "='file:///extdata.fake'#$Data.A2" },
 740             { "='file:///extdata.fake'#$Data.A3" },
 741             { "=COUNTA('file:///extdata.fake'#$Data.A1:A3)" }
 742         };
 743
 744         insertRangeData(m_pDoc, ScAddress(0,6,0), pData, SAL_N_ELEMENTS(pData));
 745     }
 746
 747     // Check the formula results.
 748     CPPUNIT_ASSERT_EQUAL(OUString("A"), m_pDoc->GetString(ScAddress(0,6,0)));
 749     CPPUNIT_ASSERT_EQUAL(OUString("B"), m_pDoc->GetString(ScAddress(0,7,0)));
 750     CPPUNIT_ASSERT_EQUAL(OUString("C"), m_pDoc->GetString(ScAddress(0,8,0)));
 751     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,9,0)));
 752
 753     // Check the formulas too.
 754     if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "'file:///extdata.fake'#$Data.A1"))
 755         CPPUNIT_FAIL("Wrong formula!");
 756     if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "'file:///extdata.fake'#$Data.A2"))
 757         CPPUNIT_FAIL("Wrong formula!");
 758     if (!checkFormula(*m_pDoc, ScAddress(0,8,0), "'file:///extdata.fake'#$Data.A3"))
 759         CPPUNIT_FAIL("Wrong formula!");
 760     if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "COUNTA('file:///extdata.fake'#$Data.A1:A3)"))
 761         CPPUNIT_FAIL("Wrong formula!");
 762
 763     // Delete rows 1 and 2. This should not change the references in the formula cells below.
 764     ScDocFunc& rDocFunc = getDocShell().GetDocFunc();
 765     ScMarkData aMark;
 766     aMark.SelectOneTable(0);
 767     rDocFunc.DeleteCells(ScRange(0,0,0,MAXCOL,1,0), &aMark, DEL_CELLSUP, true, true);
 768
 769     // Check the shifted formula cells now in A5:A8.
 770     if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "'file:///extdata.fake'#$Data.A1"))
 771         CPPUNIT_FAIL("Wrong formula!");
 772     if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "'file:///extdata.fake'#$Data.A2"))
 773         CPPUNIT_FAIL("Wrong formula!");
 774     if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "'file:///extdata.fake'#$Data.A3"))
 775         CPPUNIT_FAIL("Wrong formula!");
 776     if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "COUNTA('file:///extdata.fake'#$Data.A1:A3)"))
 777         CPPUNIT_FAIL("Wrong formula!");
 778
 779     // Undo and check the formulas again.
 780     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
 781     CPPUNIT_ASSERT(pUndoMgr);
 782     pUndoMgr->Undo();
 783     if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "'file:///extdata.fake'#$Data.A1"))
 784         CPPUNIT_FAIL("Wrong formula!");
 785     if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "'file:///extdata.fake'#$Data.A2"))
 786         CPPUNIT_FAIL("Wrong formula!");
 787     if (!checkFormula(*m_pDoc, ScAddress(0,8,0), "'file:///extdata.fake'#$Data.A3"))
 788         CPPUNIT_FAIL("Wrong formula!");
 789     if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "COUNTA('file:///extdata.fake'#$Data.A1:A3)"))
 790         CPPUNIT_FAIL("Wrong formula!");
 791
 792     // Redo the row deletion and check the formulas again.
 793     pUndoMgr->Redo();
 794     if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "'file:///extdata.fake'#$Data.A1"))
 795         CPPUNIT_FAIL("Wrong formula!");
 796     if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "'file:///extdata.fake'#$Data.A2"))
 797         CPPUNIT_FAIL("Wrong formula!");
 798     if (!checkFormula(*m_pDoc, ScAddress(0,6,0), "'file:///extdata.fake'#$Data.A3"))
 799         CPPUNIT_FAIL("Wrong formula!");
 800     if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "COUNTA('file:///extdata.fake'#$Data.A1:A3)"))
 801         CPPUNIT_FAIL("Wrong formula!");
 802
 803     xExtDocSh->DoClose();
 804
 805     m_pDoc->DeleteTab(0);
 806 }
 807
 808 void Test::testSharedFormulasInsertRow()
 809 {
 810     struct
 811     {
 812         bool checkContent( ScDocument* pDoc )
 813         {
 814             // B1:B2 and B4:B5 should point to $A$5.
 815             SCROW pRows[] = { 0, 1, 3, 4 };
 816             for (size_t i = 0, n = SAL_N_ELEMENTS(pRows); i < n; ++i)
 817             {
 818                 ScAddress aPos(1, pRows[i], 0);
 819                 if (!checkFormula(*pDoc, aPos, "$A$5"))
 820                 {
 821                     cerr << "Wrong formula!" << endl;
 822                     return false;
 823                 }
 824             }
 825
 826             // B1:B2 should be grouped.
 827             ScFormulaCell* pFC = pDoc->GetFormulaCell(ScAddress(1,0,0));
 828             if (!pFC || pFC->GetSharedTopRow() != 0 || pFC->GetSharedLength() != 2)
 829             {
 830                 cerr << "B1:B2 should be grouped." << endl;
 831                 return false;
 832             }
 833
 834             // B4:B5 should be grouped.
 835             pFC = pDoc->GetFormulaCell(ScAddress(1,3,0));
 836             if (!pFC || pFC->GetSharedTopRow() != 3 || pFC->GetSharedLength() != 2)
 837             {
 838                 cerr << "B4:B5 should be grouped." << endl;
 839                 return false;
 840             }
 841
 842             return true;
 843         }
 844
 845         bool checkContentUndo( ScDocument* pDoc )
 846         {
 847             for (SCROW i = 0; i <= 3; ++i)
 848             {
 849                 ScAddress aPos(1,i,0);
 850                 if (!checkFormula(*pDoc, aPos, "$A$4"))
 851                 {
 852                     cerr << "Wrong formula!" << endl;
 853                     return false;
 854                 }
 855             }
 856
 857             // Ensure that B5 is empty.
 858             if (pDoc->GetCellType(ScAddress(1,4,0)) != CELLTYPE_NONE)
 859             {
 860                 cerr << "B5 should be empty." << endl;
 861                 return false;
 862             }
 863
 864             // B1:B4 should be grouped.
 865             ScFormulaCell* pFC = pDoc->GetFormulaCell(ScAddress(1,0,0));
 866             if (!pFC || pFC->GetSharedTopRow() != 0 || pFC->GetSharedLength() != 4)
 867             {
 868                 cerr << "B1:B4 should be grouped." << endl;
 869                 return false;
 870             }
 871
 872             return true;
 873         }
 874
 875     } aCheck;
 876
 877     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
 878     m_pDoc->InsertTab(0, "Test");
 879
 880     // Scenario inspired by fdo#76470.
 881
 882     // Set value to A4.
 883     m_pDoc->SetValue(ScAddress(0,3,0), 4.0);
 884
 885     {
 886         // Set formula cells in B1:B4 all referencing A4 as absolute reference.
 887         const char* pData[][1] = {
 888             { "=$A$4" },
 889             { "=$A$4" },
 890             { "=$A$4" },
 891             { "=$A$4" }
 892         };
 893
 894         insertRangeData(m_pDoc, ScAddress(1,0,0), pData, SAL_N_ELEMENTS(pData));
 895     }
 896
 897     // Insert a new row at row 3.
 898     ScDocFunc& rFunc = getDocShell().GetDocFunc();
 899     ScMarkData aMark;
 900     aMark.SelectOneTable(0);
 901     rFunc.InsertCells(ScRange(0,2,0,MAXCOL,2,0), &aMark, INS_INSROWS, true, true, false);
 902
 903     bool bResult = aCheck.checkContent(m_pDoc);
 904     CPPUNIT_ASSERT_MESSAGE("Failed on the initial content check.", bResult);
 905
 906     // Undo and check its result.
 907     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
 908     CPPUNIT_ASSERT(pUndoMgr);
 909     pUndoMgr->Undo();
 910
 911     bResult = aCheck.checkContentUndo(m_pDoc);
 912     CPPUNIT_ASSERT_MESSAGE("Failed on the content check after undo.", bResult);
 913
 914     // Redo and check its result.
 915     pUndoMgr->Redo();
 916     bResult = aCheck.checkContent(m_pDoc);
 917     CPPUNIT_ASSERT_MESSAGE("Failed on the content check after redo.", bResult);
 918
 919     m_pDoc->DeleteTab(0);
 920 }
 921
 922 void Test::testSharedFormulasDeleteRows()
 923 {
 924     m_pDoc->InsertTab(0, "Test");
 925     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
 926
 927     {
 928         // Fill data cells A1:A20 and formula cells B1:B20.  Formulas in
 929         // B1:B10 and B11:B20 should be different.
 930         const char* pData[][2] = {
 931             { "0", "=RC[-1]+1" },
 932             { "1", "=RC[-1]+1" },
 933             { "2", "=RC[-1]+1" },
 934             { "3", "=RC[-1]+1" },
 935             { "4", "=RC[-1]+1" },
 936             { "5", "=RC[-1]+1" },
 937             { "6", "=RC[-1]+1" },
 938             { "7", "=RC[-1]+1" },
 939             { "8", "=RC[-1]+1" },
 940             { "9", "=RC[-1]+1" },
 941             { "10", "=RC[-1]+11" },
 942             { "11", "=RC[-1]+11" },
 943             { "12", "=RC[-1]+11" },
 944             { "13", "=RC[-1]+11" },
 945             { "14", "=RC[-1]+11" },
 946             { "15", "=RC[-1]+11" },
 947             { "16", "=RC[-1]+11" },
 948             { "17", "=RC[-1]+11" },
 949             { "18", "=RC[-1]+11" },
 950             { "19", "=RC[-1]+11" }
 951         };
 952
 953         insertRangeData(m_pDoc, ScAddress(0,0,0), pData, SAL_N_ELEMENTS(pData));
 954     }
 955
 956     // B1:B10 should be shared.
 957     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 958     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC && pFC->IsShared());
 959     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 960     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedLength());
 961     // B11:B20 should be shared.
 962     pFC = m_pDoc->GetFormulaCell(ScAddress(1,10,0));
 963     CPPUNIT_ASSERT_MESSAGE("1,10 must be a shared formula cell.", pFC && pFC->IsShared());
 964     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedTopRow());
 965     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedLength());
 966
 967     // Delete rows 9:12
 968     m_pDoc->DeleteRow(ScRange(0,8,0,MAXCOL,11,0));
 969
 970     // B1:B8 should be shared.
 971     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 972     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC && pFC->IsShared());
 973     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 974     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
 975     // B9:B16 should be shared.
 976     pFC = m_pDoc->GetFormulaCell(ScAddress(1,8,0));
 977     CPPUNIT_ASSERT_MESSAGE("1,8 must be a shared formula cell.", pFC && pFC->IsShared());
 978     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedTopRow());
 979     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
 980
 981     // Delete row 3
 982     m_pDoc->DeleteRow(ScRange(0,2,0,MAXCOL,2,0));
 983
 984     // B1:B7 should be shared.
 985     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
 986     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC && pFC->IsShared());
 987     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
 988     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(7), pFC->GetSharedLength());
 989     // B8:B15 should be shared.
 990     pFC = m_pDoc->GetFormulaCell(ScAddress(1,7,0));
 991     CPPUNIT_ASSERT_MESSAGE("1,7 must be a shared formula cell.", pFC && pFC->IsShared());
 992     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(7), pFC->GetSharedTopRow());
 993     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
 994
 995     // Delete row 5
 996     m_pDoc->DeleteRow(ScRange(0,4,0,MAXCOL,4,0));
 997
 998     // B1:B6 should be shared.
 999     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
1000     CPPUNIT_ASSERT_MESSAGE("1,0 must be a shared formula cell.", pFC && pFC->IsShared());
1001     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1002     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), pFC->GetSharedLength());
1003     // B7:B14 should be shared.
1004     pFC = m_pDoc->GetFormulaCell(ScAddress(1,6,0));
1005     CPPUNIT_ASSERT_MESSAGE("1,6 must be a shared formula cell.", pFC && pFC->IsShared());
1006     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), pFC->GetSharedTopRow());
1007     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(8), pFC->GetSharedLength());
1008 }
1009
1010 void Test::testSharedFormulasDeleteColumns()
1011 {
1012     using namespace formula;
1013
1014     m_pDoc->InsertTab(0, "Test");
1015
1016     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1017     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1018
1019     ScDocFunc& rFunc = getDocShell().GetDocFunc();
1020     ScMarkData aMark;
1021     aMark.SelectOneTable(0);
1022
1023     // First, test a single cell case.  A value in B1 and formula in C1.
1024     m_pDoc->SetValue(ScAddress(1,0,0), 11.0);
1025     m_pDoc->SetString(ScAddress(2,0,0), "=RC[-1]");
1026     CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(2,0,0)));
1027
1028     // Delete column B.
1029     rFunc.DeleteCells(ScRange(1,0,0,1,MAXROW,0), &aMark, DEL_CELLSLEFT, true, true);
1030     CPPUNIT_ASSERT_EQUAL(OUString("#REF!"), m_pDoc->GetString(ScAddress(1,0,0)));
1031
1032     // The reference should still point to row 1 but the column status should be set to 'deleted'.
1033     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
1034     CPPUNIT_ASSERT(pFC);
1035     const ScTokenArray* pCode = pFC->GetCode();
1036     CPPUNIT_ASSERT(pCode && pCode->GetLen() == 1);
1037     const FormulaToken* pToken = pCode->GetArray()[0];
1038     CPPUNIT_ASSERT(pToken->GetType() == svSingleRef);
1039     const ScSingleRefData* pSRef = pToken->GetSingleRef();
1040     CPPUNIT_ASSERT(pSRef->IsColDeleted());
1041     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pSRef->toAbs(ScAddress(1,0,0)).Row());
1042
1043     // The formula string should show #REF! in lieu of the column position (only for Calc A1 syntax).
1044     sc::CompileFormulaContext aCFCxt(m_pDoc, FormulaGrammar::GRAM_ENGLISH);
1045     CPPUNIT_ASSERT_EQUAL(OUString("=#REF!1"), pFC->GetFormula(aCFCxt));
1046
1047     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1048     CPPUNIT_ASSERT(pUndoMgr);
1049
1050     // Undo and make sure the deleted flag is gone.
1051     pUndoMgr->Undo();
1052     CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(2,0,0)));
1053     pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
1054     CPPUNIT_ASSERT(pFC);
1055     CPPUNIT_ASSERT_EQUAL(OUString("=B1"), pFC->GetFormula(aCFCxt));
1056
1057     // Clear row 1 and move over to a formula group case.
1058     clearRange(m_pDoc, ScRange(0,0,0,MAXCOL,0,0));
1059
1060     // Fill A1:B2 with numbers, and C1:C2 with formula that reference those numbers.
1061     for (SCROW i = 0; i <= 1; ++i)
1062     {
1063         m_pDoc->SetValue(ScAddress(0,i,0), (i+1));
1064         m_pDoc->SetValue(ScAddress(1,i,0), (i+11));
1065         m_pDoc->SetString(ScAddress(2,i,0), "=RC[-2]+RC[-1]");
1066         double fCheck = m_pDoc->GetValue(ScAddress(0,i,0));
1067         fCheck += m_pDoc->GetValue(ScAddress(1,i,0));
1068         CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i,0)));
1069     }
1070
1071     // Delete column B.
1072     rFunc.DeleteCells(ScRange(1,0,0,1,MAXROW,0), &aMark, DEL_CELLSLEFT, true, true);
1073
1074     for (SCROW i = 0; i <= 1; ++i)
1075     {
1076         ScAddress aPos(1,i,0);
1077         CPPUNIT_ASSERT_EQUAL(OUString("#REF!"), m_pDoc->GetString(aPos));
1078     }
1079
1080     pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0)); // B1
1081     CPPUNIT_ASSERT(pFC);
1082     CPPUNIT_ASSERT_EQUAL(OUString("=A1+#REF!1"), pFC->GetFormula(aCFCxt));
1083     pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0)); // B2
1084     CPPUNIT_ASSERT(pFC);
1085     CPPUNIT_ASSERT_EQUAL(OUString("=A2+#REF!2"), pFC->GetFormula(aCFCxt));
1086
1087     // Undo deletion of column B and check the results of C1:C2.
1088     pUndoMgr->Undo();
1089     for (SCROW i = 0; i <= 1; ++i)
1090     {
1091         double fCheck = m_pDoc->GetValue(ScAddress(0,i,0));
1092         fCheck += m_pDoc->GetValue(ScAddress(1,i,0));
1093         CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i,0)));
1094     }
1095
1096     m_pDoc->DeleteTab(0);
1097 }
1098
1099 void Test::testSharedFormulasRefUpdateMoveSheets()
1100 {
1101     m_pDoc->InsertTab(0, "Sheet1");
1102     m_pDoc->InsertTab(1, "Sheet2");
1103     m_pDoc->InsertTab(2, "Sheet3");
1104
1105     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // make sure auto calc is on.
1106
1107     // Switch to R1C1 for ease of repeated formula insertions.
1108     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1109
1110     // Fill numbers in A1:A8 on Sheet2.
1111     for (SCROW i = 0; i <= 7; ++i)
1112         m_pDoc->SetValue(ScAddress(0,i,1), i+1);
1113
1114     // Fill formula cells A1:A8 on Sheet1, to refer to the same cell address on Sheet2.
1115     for (SCROW i = 0; i <= 7; ++i)
1116         m_pDoc->SetString(ScAddress(0,i,0), "=Sheet2!RC");
1117
1118     // Check the results.
1119     for (SCROW i = 0; i <= 7; ++i)
1120         CPPUNIT_ASSERT_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(ScAddress(0,i,0)));
1121
1122     // Move Sheet3 to the leftmost position before Sheet1.
1123     m_pDoc->MoveTab(2, 0);
1124
1125     // Check sheet names.
1126     std::vector<OUString> aTabNames = m_pDoc->GetAllTableNames();
1127     CPPUNIT_ASSERT_MESSAGE("There should be at least 3 sheets.", aTabNames.size() >= 3);
1128     CPPUNIT_ASSERT_EQUAL(OUString("Sheet3"), aTabNames[0]);
1129     CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aTabNames[1]);
1130     CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aTabNames[2]);
1131
1132     // Check the results again on Sheet1.
1133     for (SCROW i = 0; i <= 7; ++i)
1134     {
1135         CPPUNIT_ASSERT_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(ScAddress(0,i,1)));
1136         if (!checkFormula(*m_pDoc, ScAddress(0,i,1), "Sheet2!RC"))
1137             CPPUNIT_FAIL("Wrong formula expression.");
1138     }
1139
1140     // Insert a new sheet at the left end.
1141     m_pDoc->InsertTab(0, "Sheet4");
1142
1143     // Check sheet names.
1144     aTabNames = m_pDoc->GetAllTableNames();
1145     CPPUNIT_ASSERT_MESSAGE("There should be at least 4 sheets.", aTabNames.size() >= 4);
1146     CPPUNIT_ASSERT_EQUAL(OUString("Sheet4"), aTabNames[0]);
1147     CPPUNIT_ASSERT_EQUAL(OUString("Sheet3"), aTabNames[1]);
1148     CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aTabNames[2]);
1149     CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aTabNames[3]);
1150
1151     // Check the results again on Sheet1.
1152     for (SCROW i = 0; i <= 7; ++i)
1153     {
1154         CPPUNIT_ASSERT_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(ScAddress(0,i,2)));
1155         if (!checkFormula(*m_pDoc, ScAddress(0,i,2), "Sheet2!RC"))
1156             CPPUNIT_FAIL("Wrong formula expression.");
1157     }
1158
1159     // Delete Sheet4.
1160     m_pDoc->DeleteTab(0);
1161
1162     // Check sheet names.
1163     aTabNames = m_pDoc->GetAllTableNames();
1164     CPPUNIT_ASSERT_MESSAGE("There should be at least 3 sheets.", aTabNames.size() >= 3);
1165     CPPUNIT_ASSERT_EQUAL(OUString("Sheet3"), aTabNames[0]);
1166     CPPUNIT_ASSERT_EQUAL(OUString("Sheet1"), aTabNames[1]);
1167     CPPUNIT_ASSERT_EQUAL(OUString("Sheet2"), aTabNames[2]);
1168
1169     // Check the results again on Sheet1.
1170     for (SCROW i = 0; i <= 7; ++i)
1171     {
1172         CPPUNIT_ASSERT_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(ScAddress(0,i,1)));
1173         if (!checkFormula(*m_pDoc, ScAddress(0,i,1), "Sheet2!RC"))
1174             CPPUNIT_FAIL("Wrong formula expression.");
1175     }
1176
1177     m_pDoc->DeleteTab(2);
1178     m_pDoc->DeleteTab(1);
1179     m_pDoc->DeleteTab(0);
1180 }
1181
1182 void Test::testSharedFormulasRefUpdateCopySheets()
1183 {
1184     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // make sure auto calc is on.
1185
1186     m_pDoc->InsertTab(0, "Sheet1");
1187     m_pDoc->InsertTab(1, "Sheet2");
1188
1189     m_pDoc->SetValue(ScAddress(0,0,1), 1.0); // A1 on Sheet2
1190     m_pDoc->SetValue(ScAddress(0,1,1), 2.0); // A2 on Sheet2
1191
1192     // Reference values on Sheet2, but use absolute sheet references.
1193     m_pDoc->SetString(ScAddress(0,0,0), "=$Sheet2.A1");
1194     m_pDoc->SetString(ScAddress(0,1,0), "=$Sheet2.A2");
1195
1196     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,0,0)));
1197     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1198
1199     // Copy Sheet1 and insert the copied sheet before the current Sheet1 position.
1200     m_pDoc->CopyTab(0, 0);
1201
1202     if (!checkFormula(*m_pDoc, ScAddress(0,0,0), "$Sheet2.A1"))
1203         CPPUNIT_FAIL("Wrong formula");
1204
1205     if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "$Sheet2.A2"))
1206         CPPUNIT_FAIL("Wrong formula");
1207
1208     // Check the values on the copied sheet.
1209     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,0,0)));
1210     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1211
1212     // Check the values on the original sheet.
1213     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,0,1)));
1214     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,1)));
1215
1216     m_pDoc->DeleteTab(2);
1217     m_pDoc->DeleteTab(1);
1218     m_pDoc->DeleteTab(0);
1219 }
1220
1221 void Test::testSharedFormulasRefUpdateDeleteSheets()
1222 {
1223     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // make sure auto calc is on.
1224
1225     m_pDoc->InsertTab(0, "Sheet1");
1226     m_pDoc->InsertTab(1, "Sheet2");
1227
1228     // Set values to B2:B4 on Sheet2.
1229     m_pDoc->SetValue(ScAddress(1,1,1), 1.0);
1230     m_pDoc->SetValue(ScAddress(1,2,1), 2.0);
1231     m_pDoc->SetValue(ScAddress(1,3,1), 3.0);
1232
1233     // Set formulas in A1:A3 on Sheet1 that reference B2:B4 on Sheet2.
1234     m_pDoc->SetString(ScAddress(0,0,0), "=Sheet2.B2");
1235     m_pDoc->SetString(ScAddress(0,1,0), "=Sheet2.B3");
1236     m_pDoc->SetString(ScAddress(0,2,0), "=Sheet2.B4");
1237
1238     // Check the formula results.
1239     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(0,0,0)));
1240     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1241     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1242
1243     if (!checkFormula(*m_pDoc, ScAddress(0,0,0), "Sheet2.B2"))
1244         CPPUNIT_FAIL("Wrong formula");
1245     if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "Sheet2.B3"))
1246         CPPUNIT_FAIL("Wrong formula");
1247     if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "Sheet2.B4"))
1248         CPPUNIT_FAIL("Wrong formula");
1249
1250     // Delete Sheet2.
1251     ScDocFunc& rFunc = getDocShell().GetDocFunc();
1252     rFunc.DeleteTable(1, true, true);
1253
1254     if (!checkFormula(*m_pDoc, ScAddress(0,0,0), "#REF!.B2"))
1255         CPPUNIT_FAIL("Wrong formula");
1256     if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "#REF!.B3"))
1257         CPPUNIT_FAIL("Wrong formula");
1258     if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "#REF!.B4"))
1259         CPPUNIT_FAIL("Wrong formula");
1260
1261     // Undo the deletion and make sure the formulas are back to the way they were.
1262     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1263     CPPUNIT_ASSERT(pUndoMgr);
1264     pUndoMgr->Undo();
1265
1266     if (!checkFormula(*m_pDoc, ScAddress(0,0,0), "Sheet2.B2"))
1267         CPPUNIT_FAIL("Wrong formula");
1268     if (!checkFormula(*m_pDoc, ScAddress(0,1,0), "Sheet2.B3"))
1269         CPPUNIT_FAIL("Wrong formula");
1270     if (!checkFormula(*m_pDoc, ScAddress(0,2,0), "Sheet2.B4"))
1271         CPPUNIT_FAIL("Wrong formula");
1272
1273     // TODO: We can't test redo yet as ScUndoDeleteTab::Redo() relies on
1274     // view shell to do its thing.
1275
1276     m_pDoc->DeleteTab(1);
1277     m_pDoc->DeleteTab(0);
1278 }
1279
1280 void Test::testSharedFormulasCopyPaste()
1281 {
1282     m_pDoc->InsertTab(0, "Test");
1283     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1284
1285     // Fill formula cells B1:B10.
1286     for (SCROW i = 0; i <= 9; ++i)
1287         m_pDoc->SetString(1, i, 0, "=RC[-1]");
1288
1289     ScAddress aPos(1, 8, 0); // B9
1290     ScFormulaCell* pFC = m_pDoc->GetFormulaCell(aPos);
1291     CPPUNIT_ASSERT_MESSAGE("B9 should be a formula cell.", pFC);
1292     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1293     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedLength());
1294     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
1295
1296     // Copy formulas in B6:B9 to the clipboard doc.
1297     ScRange aSrcRange(1,5,0,1,8,0); // B6:B9
1298     ScDocument aClipDoc(SCDOCMODE_CLIP);
1299     copyToClip(m_pDoc, aSrcRange, &aClipDoc);
1300     pFC = aClipDoc.GetFormulaCell(aPos);
1301     CPPUNIT_ASSERT_MESSAGE("B9 in the clip doc should be a formula cell.", pFC);
1302     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(5), pFC->GetSharedTopRow());
1303     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
1304     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
1305
1306     // Paste them to C2:C10.
1307     ScRange aDestRange(2,1,0,2,9,0);
1308     pasteFromClip(m_pDoc, aDestRange, &aClipDoc);
1309     aPos.SetCol(2);
1310     aPos.SetRow(1);
1311     pFC = m_pDoc->GetFormulaCell(aPos);
1312     CPPUNIT_ASSERT_MESSAGE("C2 should be a formula cell.", pFC);
1313     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
1314     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), pFC->GetSharedLength());
1315     CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
1316
1317     ScRange aRange(1,0,0,1,9,0); // B1:B10
1318     ScDocument* pUndoDoc = new ScDocument(SCDOCMODE_UNDO);
1319     pUndoDoc->InitUndo(m_pDoc, 0, 0, true, true);
1320     m_pDoc->CopyToDocument(aRange, IDF_CONTENTS, false, pUndoDoc);
1321     boost::scoped_ptr<ScUndoPaste> pUndo(createUndoPaste(getDocShell(), aRange, pUndoDoc));
1322
1323     // First, make sure the formula cells are shared in the undo document.
1324     aPos.SetCol(1);
1325     for (SCROW i = 0; i <= 9; ++i)
1326     {
1327         aPos.SetRow(i);
1328         pFC = pUndoDoc->GetFormulaCell(aPos);
1329         CPPUNIT_ASSERT_MESSAGE("Must be a formula cell.", pFC);
1330         CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1331         CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedLength());
1332         CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
1333     }
1334
1335     // Overwrite B1:B10.
1336     for (SCROW i = 0; i <= 9; ++i)
1337         m_pDoc->SetValue(ScAddress(1,i,0), i*10);
1338
1339     for (SCROW i = 0; i <= 9; ++i)
1340         CPPUNIT_ASSERT_MESSAGE("Numeric cell was expected.", m_pDoc->GetCellType(ScAddress(1,i,0)) == CELLTYPE_VALUE);
1341
1342     // Undo the action to fill B1:B10 with formula cells again.
1343     pUndo->Undo();
1344
1345     aPos.SetCol(1);
1346     for (SCROW i = 0; i <= 9; ++i)
1347     {
1348         aPos.SetRow(i);
1349         pFC = m_pDoc->GetFormulaCell(aPos);
1350         CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pFC);
1351         CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1352         CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(10), pFC->GetSharedLength());
1353         CPPUNIT_ASSERT_MESSAGE("The token is expected to be shared.", pFC->GetCode() == pFC->GetSharedCode());
1354     }
1355
1356     m_pDoc->DeleteTab(0);
1357 }
1358
1359 void Test::testSharedFormulaInsertColumn()
1360 {
1361     m_pDoc->InsertTab(0, "Test");
1362
1363     // Set shared formula group over H2:H3.
1364     m_pDoc->SetString(ScAddress(7,1,0), "=G3*B3");
1365     m_pDoc->SetString(ScAddress(7,2,0), "=G4*B4");
1366
1367     // Insert a single column at Column F. This used to crash before fdo#74041.
1368     m_pDoc->InsertCol(ScRange(5,0,0,5,MAXROW,0));
1369
1370     if (!checkFormula(*m_pDoc, ScAddress(8,1,0), "H3*B3"))
1371         CPPUNIT_FAIL("Wrong formula!");
1372
1373     if (!checkFormula(*m_pDoc, ScAddress(8,2,0), "H4*B4"))
1374         CPPUNIT_FAIL("Wrong formula!");
1375
1376     m_pDoc->DeleteTab(0);
1377 }
1378
1379 void Test::testSharedFormulaMoveBlock()
1380 {
1381     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1382     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1383
1384     m_pDoc->InsertTab(0, "Test");
1385
1386     // Set values to A1:A3.
1387     m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
1388     m_pDoc->SetValue(ScAddress(0,1,0), 2.0);
1389     m_pDoc->SetValue(ScAddress(0,2,0), 3.0);
1390
1391     // Set formulas in B1:B3 to reference A1:A3.
1392     m_pDoc->SetString(ScAddress(1,0,0), "=RC[-1]");
1393     m_pDoc->SetString(ScAddress(1,1,0), "=RC[-1]");
1394     m_pDoc->SetString(ScAddress(1,2,0), "=RC[-1]");
1395
1396     ScRange aFormulaRange(1,0,0,1,2,0);
1397
1398     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1399     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1400     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1401
1402     clearFormulaCellChangedFlag(*m_pDoc, aFormulaRange);
1403
1404     // Move A1:A3 to D1:D3.
1405     ScDocFunc& rFunc = getDocShell().GetDocFunc();
1406     rFunc.MoveBlock(ScRange(0,0,0,0,2,0), ScAddress(3,0,0), true, true, false, true);
1407
1408     // The result should stay the same.
1409     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1410     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1411     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1412
1413     clearFormulaCellChangedFlag(*m_pDoc, aFormulaRange);
1414
1415     // Make sure these formula cells in B1:B3 have correct positions even after the move.
1416     std::vector<SCROW> aRows;
1417     aRows.push_back(0);
1418     aRows.push_back(1);
1419     aRows.push_back(2);
1420     bool bRes = checkFormulaPositions(*m_pDoc, 0, 1, &aRows[0], aRows.size());
1421     CPPUNIT_ASSERT(bRes);
1422
1423     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1424     CPPUNIT_ASSERT(pUndoMgr);
1425
1426     // Undo and check the result.
1427     pUndoMgr->Undo();
1428     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1429     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1430     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1431
1432     clearFormulaCellChangedFlag(*m_pDoc, aFormulaRange);
1433
1434     // Redo and check the result.
1435     pUndoMgr->Redo();
1436     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1437     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1438     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1439
1440     // Clear the range and start over.
1441     clearRange(m_pDoc, ScRange(0,0,0,MAXCOL,MAXROW,0));
1442
1443     // Set values 1,2,3,4,5 to A1:A5.
1444     for (SCROW i = 0; i <= 4; ++i)
1445         m_pDoc->SetValue(ScAddress(0,i,0), (i+1));
1446
1447     // Set formulas to B1:B5.
1448     for (SCROW i = 0; i <= 4; ++i)
1449         m_pDoc->SetString(ScAddress(1,i,0), "=RC[-1]");
1450
1451     // Check the initial formula results.
1452     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1453     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1454     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1455     CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1456     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1457
1458     // Move A1:A2 to D2:D3.
1459     rFunc.MoveBlock(ScRange(0,0,0,0,1,0), ScAddress(3,1,0), true, true, false, true);
1460
1461     // Check the formula values again.  They should not change.
1462     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1463     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1464     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1465     CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1466     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1467
1468     pUndoMgr->Undo();
1469     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1470     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1471     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1472     CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1473     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1474
1475     pUndoMgr->Redo();
1476     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1477     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1478     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1479     CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1480     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1481
1482     m_pDoc->DeleteTab(0);
1483 }
1484
1485 void Test::testSharedFormulaUpdateOnNamedRangeChange()
1486 {
1487     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1488
1489     m_pDoc->InsertTab(0, "Test");
1490
1491     const char* pName = "MyRange";
1492     const char* pExpr1 = "$Test.$A$1:$A$3";
1493     const char* pExpr2 = "$Test.$A$1:$A$4";
1494
1495     RangeNameDef aName;
1496     aName.mpName = pName;
1497     aName.mpExpr = pExpr1;
1498     aName.mnIndex = 1;
1499     ScRangeName* pNames = new ScRangeName;
1500     bool bSuccess = insertRangeNames(m_pDoc, pNames, &aName, &aName + 1);
1501     CPPUNIT_ASSERT(bSuccess);
1502     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pNames->size());
1503     m_pDoc->SetRangeName(pNames);
1504
1505     // Set values to A1:A4.
1506     m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
1507     m_pDoc->SetValue(ScAddress(0,1,0), 2.0);
1508     m_pDoc->SetValue(ScAddress(0,2,0), 3.0);
1509     m_pDoc->SetValue(ScAddress(0,3,0), 4.0);
1510
1511     // Set formula to B1:B3.
1512     m_pDoc->SetString(ScAddress(1,0,0), "=SUM(MyRange)");
1513     m_pDoc->SetString(ScAddress(1,1,0), "=SUM(MyRange)");
1514     m_pDoc->SetString(ScAddress(1,2,0), "=SUM(MyRange)");
1515
1516     // Set single formula with no named range to B5.
1517     m_pDoc->SetString(ScAddress(1,4,0), "=ROW()");
1518
1519     // Set shared formula with no named range to B7:B8.
1520     m_pDoc->SetString(ScAddress(1,6,0), "=ROW()");
1521     m_pDoc->SetString(ScAddress(1,7,0), "=ROW()");
1522
1523     // B1:B3 should be grouped.
1524     ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,0,0));
1525     CPPUNIT_ASSERT(pFC);
1526     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1527     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
1528
1529     // B7:B8 should be grouped.
1530     pFC = m_pDoc->GetFormulaCell(ScAddress(1,6,0));
1531     CPPUNIT_ASSERT(pFC);
1532     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), pFC->GetSharedTopRow());
1533     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), pFC->GetSharedLength());
1534
1535     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1536     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1537     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1538
1539     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1540     CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(1,6,0)));
1541     CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(ScAddress(1,7,0)));
1542
1543     // Set a single formula to C1.
1544     m_pDoc->SetString(ScAddress(2,0,0), "=AVERAGE(MyRange)");
1545     pFC = m_pDoc->GetFormulaCell(ScAddress(2,0,0));
1546     CPPUNIT_ASSERT(pFC);
1547     CPPUNIT_ASSERT_MESSAGE("C1 should not be shared.", !pFC->IsShared());
1548     CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
1549
1550     // Update the range of MyRange.
1551     pNames = new ScRangeName;
1552     aName.mpExpr = pExpr2;
1553     bSuccess = insertRangeNames(m_pDoc, pNames, &aName, &aName + 1);
1554     CPPUNIT_ASSERT(bSuccess);
1555     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pNames->size());
1556     ScDocFunc& rFunc = getDocShell().GetDocFunc();
1557
1558     typedef boost::ptr_map<OUString, ScRangeName> NameMapType;
1559     NameMapType aNewNames;
1560     OUString aScope(STR_GLOBAL_RANGE_NAME);
1561     aNewNames.insert(aScope, pNames);
1562     rFunc.ModifyAllRangeNames(aNewNames);
1563
1564     // Check to make sure all displayed formulas are still good.
1565     if (!checkFormula(*m_pDoc, ScAddress(1,0,0), "SUM(MyRange)"))
1566         CPPUNIT_FAIL("Wrong formula!");
1567     if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "SUM(MyRange)"))
1568         CPPUNIT_FAIL("Wrong formula!");
1569     if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "SUM(MyRange)"))
1570         CPPUNIT_FAIL("Wrong formula!");
1571     if (!checkFormula(*m_pDoc, ScAddress(1,4,0), "ROW()"))
1572         CPPUNIT_FAIL("Wrong formula!");
1573     if (!checkFormula(*m_pDoc, ScAddress(1,6,0), "ROW()"))
1574         CPPUNIT_FAIL("Wrong formula!");
1575     if (!checkFormula(*m_pDoc, ScAddress(1,7,0), "ROW()"))
1576         CPPUNIT_FAIL("Wrong formula!");
1577     if (!checkFormula(*m_pDoc, ScAddress(2,0,0), "AVERAGE(MyRange)"))
1578         CPPUNIT_FAIL("Wrong formula!");
1579
1580     // Check the calculation results as well.
1581     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1582     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1583     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1584     CPPUNIT_ASSERT_EQUAL(5.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1585     CPPUNIT_ASSERT_EQUAL(7.0, m_pDoc->GetValue(ScAddress(1,6,0)));
1586     CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(ScAddress(1,7,0)));
1587     CPPUNIT_ASSERT_EQUAL(2.5, m_pDoc->GetValue(ScAddress(2,0,0)));
1588
1589     // Change the value of A4 and make sure the value change gets propagated.
1590     m_pDoc->SetValue(ScAddress(0,3,0), 0.0);
1591     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1592     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1593     CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1594
1595     m_pDoc->DeleteTab(0);
1596 }
1597
1598 void Test::testSharedFormulaUpdateOnDBChange()
1599 {
1600     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1601
1602     m_pDoc->InsertTab(0, "RangeTest");
1603
1604     // Put 1, 2, 3, 4 in A1:A4.
1605     for (SCROW i = 0; i <= 3; ++i)
1606         m_pDoc->SetValue(ScAddress(0,i,0), (i+1));
1607
1608     ScDBCollection* pDBs = m_pDoc->GetDBCollection();
1609     CPPUNIT_ASSERT_MESSAGE("Failed to fetch DB collection object.", pDBs);
1610
1611     // Define database range 'MyRange' for A1:A2.
1612     ScDBData* pData = new ScDBData("MyRange", 0, 0, 0, 0, 1);
1613     bool bInserted = pDBs->getNamedDBs().insert(pData);
1614     if (!bInserted)
1615         delete pData;
1616     CPPUNIT_ASSERT_MESSAGE("Failed to insert a new database range.", bInserted);
1617
1618     // Insert in C2:C4 a group of formula cells that reference MyRange.
1619     for (SCROW i = 1; i <= 3; ++i)
1620         m_pDoc->SetString(ScAddress(2,i,0), "=SUM(MyRange)");
1621
1622     // Make sure C2:C4 is a formula group.
1623     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(2,1,0));
1624     CPPUNIT_ASSERT(pFC);
1625     CPPUNIT_ASSERT(pFC->IsSharedTop());
1626     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
1627
1628     // Check the initial formula results.
1629     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1630     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,2,0)));
1631     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,3,0)));
1632
1633     ScDBDocFunc aFunc(getDocShell());
1634
1635     // Change the range referenced by MyRange to A1:A4.
1636     ScDBCollection aNewDBs(m_pDoc);
1637     ScDBData* pNewData = new ScDBData("MyRange", 0, 0, 0, 0, 3);
1638     bInserted = aNewDBs.getNamedDBs().insert(pNewData);
1639     if (!bInserted)
1640         delete pNewData;
1641     CPPUNIT_ASSERT_MESSAGE("Failed to insert a new database range.", bInserted);
1642
1643     std::vector<ScRange> aDeleted;
1644     aFunc.ModifyAllDBData(aNewDBs, aDeleted);
1645
1646     // Check the updated formula results.
1647     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1648     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,2,0)));
1649     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,3,0)));
1650
1651     SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1652     CPPUNIT_ASSERT(pUndoMgr);
1653
1654     // Undo and check the results.
1655     pUndoMgr->Undo();
1656     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1657     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,2,0)));
1658     CPPUNIT_ASSERT_EQUAL(3.0, m_pDoc->GetValue(ScAddress(2,3,0)));
1659
1660     // Redo and check the results.
1661     pUndoMgr->Redo();
1662     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,1,0)));
1663     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,2,0)));
1664     CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,3,0)));
1665
1666     m_pDoc->DeleteTab(0);
1667 }
1668
1669 void Test::testSharedFormulaAbsCellListener()
1670 {
1671     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1672
1673     m_pDoc->InsertTab(0, "Test");
1674
1675     m_pDoc->SetValue(ScAddress(0,0,0), 1.0);
1676
1677     const char* pData[][1] = {
1678         { "=$A$1" },
1679         { "=$A$1" },
1680         { "=$A$1" }
1681     };
1682
1683     insertRangeData(m_pDoc, ScAddress(1,0,0), pData, SAL_N_ELEMENTS(pData));
1684
1685     // A1 should have 3 listeners listening into it.
1686     const SvtBroadcaster* pBC = m_pDoc->GetBroadcaster(ScAddress(0,0,0));
1687     CPPUNIT_ASSERT(pBC);
1688     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(3), pBC->GetAllListeners().size());
1689
1690     // Check the formula results.
1691     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
1692     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1693     CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1694
1695     // Change the value of A1 and make sure B1:B3 follows.
1696     m_pDoc->SetValue(ScAddress(0,0,0), 2.5);
1697
1698     CPPUNIT_ASSERT_EQUAL(2.5, m_pDoc->GetValue(ScAddress(1,0,0)));
1699     CPPUNIT_ASSERT_EQUAL(2.5, m_pDoc->GetValue(ScAddress(1,1,0)));
1700     CPPUNIT_ASSERT_EQUAL(2.5, m_pDoc->GetValue(ScAddress(1,2,0)));
1701
1702     m_pDoc->DeleteTab(0);
1703 }
1704
1705 void Test::testSharedFormulaUnshareAreaListeners()
1706 {
1707     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calc.
1708
1709     m_pDoc->InsertTab(0, "Test");
1710
1711     const char* pData[][2] = {
1712         { "=SUM(B1:B2)", "1" },
1713         { "=SUM(B2:B3)", "2" },
1714         { "=SUM(B3:B4)", "4" },
1715         {             0, "8" }
1716     };
1717
1718     insertRangeData(m_pDoc, ScAddress(0,0,0), pData, SAL_N_ELEMENTS(pData));
1719
1720     // Check that A1:A3 is a formula group.
1721     const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0,0,0));
1722     CPPUNIT_ASSERT(pFC);
1723     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), pFC->GetSharedTopRow());
1724     CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
1725
1726     m_pDoc->SetValue(ScAddress(0,1,0), 23.0);   // unshare at A2
1727     m_pDoc->SetValue(ScAddress(1,1,0), 16.0);   // change value of B2
1728     m_pDoc->SetValue(ScAddress(1,2,0), 32.0);   // change value of B3
1729     // A1 and A3 should be recalculated.
1730     CPPUNIT_ASSERT_EQUAL(17.0, m_pDoc->GetValue(ScAddress(0,0,0)));
1731     CPPUNIT_ASSERT_EQUAL(40.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1732
1733     m_pDoc->DeleteTab(0);
1734 }
1735
1736 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */