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