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