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