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