?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
bump product version to 5.0.4.1
[LibreOffice.git] / sc / qa / unit / ucalc_sort.cxx
blob76909f99330cb076df2f3e3b6c7f11e9b939a457
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 "helper/sorthelper.hxx"
12
13 #include <postit.hxx>
14 #include <sortparam.hxx>
15 #include <dbdata.hxx>
16 #include <patattr.hxx>
17 #include <formulacell.hxx>
18 #include <scopetools.hxx>
19 #include <globalnames.hxx>
20 #include <dbdocfun.hxx>
21 #include <docfunc.hxx>
22 #include <scitems.hxx>
23 #include <editutil.hxx>
24
25 #include <sal/config.h>
26 #include <editeng/wghtitem.hxx>
27 #include <editeng/postitem.hxx>
28 #include <test/bootstrapfixture.hxx>
29
30 void Test::testSort()
31 {
32 m_pDoc->InsertTab(0, "test1");
33
34 ScRange aDataRange;
35 ScAddress aPos(0,0,0);
36 {
37 const char* aData[][2] = {
38 { "2", "4" },
39 { "4", "1" },
40 { "1", "2" },
41 { "1", "23" },
42 };
43
44 clearRange(m_pDoc, ScRange(0, 0, 0, 1, SAL_N_ELEMENTS(aData), 0));
45 aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
46 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
47 }
48
49 // Insert note in cell B2.
50 ScAddress rAddr(1, 1, 0);
51 ScPostIt* pNote = m_pDoc->GetOrCreateNote(rAddr);
52 pNote->SetText(rAddr, "Hello");
53 pNote->SetAuthor("Jim Bob");
54
55 ScSortParam aSortData;
56 aSortData.nCol1 = 1;
57 aSortData.nCol2 = 1;
58 aSortData.nRow1 = 0;
59 aSortData.nRow2 = 2;
60 aSortData.maKeyState[0].bDoSort = true;
61 aSortData.maKeyState[0].nField = 1;
62 aSortData.maKeyState[0].bAscending = true;
63
64 m_pDoc->Sort(0, aSortData, false, true, NULL, NULL);
65
66 double nVal = m_pDoc->GetValue(1,0,0);
67 ASSERT_DOUBLES_EQUAL(nVal, 1.0);
68
69 // check that note is also moved after sorting
70 pNote = m_pDoc->GetNote(1, 0, 0);
71 CPPUNIT_ASSERT(pNote);
72
73 clearRange(m_pDoc, ScRange(0, 0, 0, 1, 9, 0)); // Clear A1:B10.
74 {
75 // 0 = empty cell
76 const char* aData[][1] = {
77 { "Title" },
78 { 0 },
79 { 0 },
80 { "12" },
81 { "b" },
82 { "1" },
83 { "9" },
84 { "123" }
85 };
86
87 aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
88 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
89 }
90
91 aSortData.nCol1 = aDataRange.aStart.Col();
92 aSortData.nCol2 = aDataRange.aEnd.Col();
93 aSortData.nRow1 = aDataRange.aStart.Row();
94 aSortData.nRow2 = aDataRange.aEnd.Row();
95 aSortData.bHasHeader = true;
96 aSortData.maKeyState[0].nField = 0;
97 m_pDoc->Sort(0, aSortData, false, true, NULL, NULL);
98
99 // Title should stay at the top, numbers should be sorted numerically,
100 // numbers always come before strings, and empty cells always occur at the
101 // end.
102 CPPUNIT_ASSERT_EQUAL(OUString("Title"), m_pDoc->GetString(aPos));
103 aPos.IncRow();
104 CPPUNIT_ASSERT_EQUAL(OUString("1"), m_pDoc->GetString(aPos));
105 aPos.IncRow();
106 CPPUNIT_ASSERT_EQUAL(OUString("9"), m_pDoc->GetString(aPos));
107 aPos.IncRow();
108 CPPUNIT_ASSERT_EQUAL(OUString("12"), m_pDoc->GetString(aPos));
109 aPos.IncRow();
110 CPPUNIT_ASSERT_EQUAL(OUString("123"), m_pDoc->GetString(aPos));
111 aPos.IncRow();
112 CPPUNIT_ASSERT_EQUAL(OUString("b"), m_pDoc->GetString(aPos));
113 aPos.IncRow();
114 CPPUNIT_ASSERT_EQUAL(CELLTYPE_NONE, m_pDoc->GetCellType(aPos));
115
116 m_pDoc->DeleteTab(0);
117 }
118
119 void Test::testSortHorizontal()
120 {
121 SortRefUpdateSetter aUpdateSet;
122
123 ScFormulaOptions aOptions;
124 aOptions.SetFormulaSepArg(";");
125 aOptions.SetFormulaSepArrayCol(";");
126 aOptions.SetFormulaSepArrayRow("|");
127 getDocShell().SetFormulaOptions(aOptions);
128
129 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
130 m_pDoc->InsertTab(0, "Sort");
131
132 // Test case from fdo#78079.
133
134 // 0 = empty cell
135 const char* aData[][4] = {
136 { "table", "has UNIQUE", "Publish to EC2", "flag" },
137 { "w2gi.mobilehit", "Yes", "No", "=CONCATENATE(B2;\"-\";C2)" },
138 { "w2gi.visitors", "No", "No", "=CONCATENATE(B3;\"-\";C3)" },
139 { "w2gi.pagedimension", "Yes", "Yes", "=CONCATENATE(B4;\"-\";C4)" },
140 };
141
142 // Insert raw data into A1:D4.
143 ScRange aDataRange = insertRangeData(m_pDoc, ScAddress(0,0,0), aData, SAL_N_ELEMENTS(aData));
144 CPPUNIT_ASSERT_EQUAL(OUString("A1:D4"), aDataRange.Format(SCA_VALID));
145
146 // Check the formula values.
147 CPPUNIT_ASSERT_EQUAL(OUString("Yes-No"), m_pDoc->GetString(ScAddress(3,1,0)));
148 CPPUNIT_ASSERT_EQUAL(OUString("No-No"), m_pDoc->GetString(ScAddress(3,2,0)));
149 CPPUNIT_ASSERT_EQUAL(OUString("Yes-Yes"), m_pDoc->GetString(ScAddress(3,3,0)));
150
151 // Define A1:D4 as sheet-local anonymous database range.
152 m_pDoc->SetAnonymousDBData(
153 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 3, 3));
154
155 // Sort A1:D4 horizontally, ascending by row 1.
156 ScDBDocFunc aFunc(getDocShell());
157
158 ScSortParam aSortData;
159 aSortData.nCol1 = 0;
160 aSortData.nCol2 = 3;
161 aSortData.nRow1 = 0;
162 aSortData.nRow2 = 3;
163 aSortData.bHasHeader = true;
164 aSortData.bByRow = false; // Sort by column (in horizontal direction).
165 aSortData.bIncludePattern = true;
166 aSortData.maKeyState[0].bDoSort = true;
167 aSortData.maKeyState[0].nField = 0;
168 aSortData.maKeyState[0].bAscending = true;
169 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
170 CPPUNIT_ASSERT(bSorted);
171
172 {
173 // Expected output table content. 0 = empty cell
174 const char* aOutputCheck[][4] = {
175 { "table", "flag", "has UNIQUE", "Publish to EC2" },
176 { "w2gi.mobilehit", "Yes-No", "Yes", "No" },
177 { "w2gi.visitors", "No-No", "No", "No" },
178 { "w2gi.pagedimension", "Yes-Yes", "Yes", "Yes" },
179 };
180
181 bool bSuccess = checkOutput<4>(m_pDoc, aDataRange, aOutputCheck, "Sorted by column with formula");
182 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
183 }
184
185 if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "CONCATENATE(C2;\"-\";D2)"))
186 CPPUNIT_FAIL("Wrong formula!");
187 if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "CONCATENATE(C3;\"-\";D3)"))
188 CPPUNIT_FAIL("Wrong formula!");
189 if (!checkFormula(*m_pDoc, ScAddress(1,3,0), "CONCATENATE(C4;\"-\";D4)"))
190 CPPUNIT_FAIL("Wrong formula!");
191
192 m_pDoc->DeleteTab(0);
193 }
194
195 void Test::testSortHorizontalWholeColumn()
196 {
197 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
198 m_pDoc->InsertTab(0, "Sort");
199
200 // 0 = empty cell
201 const char* aData[][5] = {
202 { "4", "2", "47", "a", "9" }
203 };
204
205 // Insert row data to C1:G1.
206 ScRange aSortRange = insertRangeData(m_pDoc, ScAddress(2,0,0), aData, SAL_N_ELEMENTS(aData));
207 CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(2,0,0)));
208 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3,0,0)));
209 CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(4,0,0)));
210 CPPUNIT_ASSERT_EQUAL(OUString("a"), m_pDoc->GetString(ScAddress(5,0,0)));
211 CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(6,0,0)));
212
213 // Extend the sort range to whole column.
214 aSortRange.aEnd.SetRow(MAXROW);
215
216 SCCOL nCol1 = aSortRange.aStart.Col();
217 SCCOL nCol2 = aSortRange.aEnd.Col();
218 SCROW nRow1 = aSortRange.aStart.Row();
219 SCROW nRow2 = aSortRange.aEnd.Row();
220
221 // Define C:G as sheet-local anonymous database range.
222 m_pDoc->SetAnonymousDBData(
223 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, nCol1, nRow1, nCol2, nRow2, false, false));
224
225 // Sort C:G horizontally ascending by row 1.
226 ScDBDocFunc aFunc(getDocShell());
227
228 ScSortParam aSortData;
229 aSortData.nCol1 = nCol1;
230 aSortData.nCol2 = nCol2;
231 aSortData.nRow1 = nRow1;
232 aSortData.nRow2 = nRow2;
233 aSortData.bHasHeader = false;
234 aSortData.bByRow = false; // Sort by column (in horizontal direction).
235 aSortData.bIncludePattern = true;
236 aSortData.maKeyState[0].bDoSort = true;
237 aSortData.maKeyState[0].nField = 0;
238 aSortData.maKeyState[0].bAscending = true;
239 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
240 CPPUNIT_ASSERT(bSorted);
241
242 // Check the sort result.
243 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
244 CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(3,0,0)));
245 CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
246 CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(5,0,0)));
247 CPPUNIT_ASSERT_EQUAL(OUString("a"), m_pDoc->GetString(ScAddress(6,0,0)));
248
249 // Undo and check.
250
251 SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
252 CPPUNIT_ASSERT(pUndoMgr);
253
254 pUndoMgr->Undo();
255 CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(2,0,0)));
256 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3,0,0)));
257 CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(4,0,0)));
258 CPPUNIT_ASSERT_EQUAL(OUString("a"), m_pDoc->GetString(ScAddress(5,0,0)));
259 CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(6,0,0)));
260
261 // Redo and check.
262 pUndoMgr->Redo();
263 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
264 CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(3,0,0)));
265 CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
266 CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(5,0,0)));
267 CPPUNIT_ASSERT_EQUAL(OUString("a"), m_pDoc->GetString(ScAddress(6,0,0)));
268
269 m_pDoc->DeleteTab(0);
270 }
271
272 void Test::testSortSingleRow()
273 {
274 // This test case is from fdo#80462.
275
276 m_pDoc->InsertTab(0, "Test");
277
278 // Sort range consists of only one row.
279 m_pDoc->SetString(ScAddress(0,0,0), "X");
280 m_pDoc->SetString(ScAddress(1,0,0), "Y");
281
282 // Define A1:B1 as sheet-local anonymous database range.
283 m_pDoc->SetAnonymousDBData(
284 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 1, 0));
285
286 // Sort A1:B1 horizontally, ascending by row 1.
287 ScDBDocFunc aFunc(getDocShell());
288
289 ScSortParam aSortData;
290 aSortData.nCol1 = 0;
291 aSortData.nCol2 = 1;
292 aSortData.nRow1 = 0;
293 aSortData.nRow2 = 0;
294 aSortData.bHasHeader = true;
295 aSortData.bByRow = true;
296 aSortData.bIncludePattern = true;
297 aSortData.maKeyState[0].bDoSort = true;
298 aSortData.maKeyState[0].nField = 0;
299 aSortData.maKeyState[0].bAscending = true;
300
301 // Do the sorting. This should not crash.
302 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
303 CPPUNIT_ASSERT(bSorted);
304
305 // Another test case - single row horizontal sort with header column.
306 clearSheet(m_pDoc, 0);
307
308 // A1:G1
309 m_pDoc->SetString(ScAddress(0,0,0), "Header");
310 m_pDoc->SetValue(ScAddress(1,0,0), 1.0);
311 m_pDoc->SetValue(ScAddress(2,0,0), 10.0);
312 m_pDoc->SetValue(ScAddress(3,0,0), 3.0);
313 m_pDoc->SetValue(ScAddress(4,0,0), 9.0);
314 m_pDoc->SetValue(ScAddress(5,0,0), 12.0);
315 m_pDoc->SetValue(ScAddress(6,0,0), 2.0);
316
317 // Define A1:G1 as sheet-local anonymous database range.
318 m_pDoc->SetAnonymousDBData(
319 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 6, 0, false, true));
320
321 // Update the sort data.
322 aSortData.nCol1 = 0;
323 aSortData.nCol2 = 6;
324 aSortData.bByRow = false;
325 bSorted = aFunc.Sort(0, aSortData, true, true, true);
326 CPPUNIT_ASSERT(bSorted);
327
328 // Check the result.
329 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
330 CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
331 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
332 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,0,0)));
333 CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
334 CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(5,0,0)));
335 CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(6,0,0)));
336
337 // Undo and check.
338 SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
339 CPPUNIT_ASSERT(pUndoMgr);
340 pUndoMgr->Undo();
341
342 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
343 CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
344 CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,0,0)));
345 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,0,0)));
346 CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
347 CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(5,0,0)));
348 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(6,0,0)));
349
350 // Redo and check.
351 pUndoMgr->Redo();
352 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
353 CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
354 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
355 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,0,0)));
356 CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
357 CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(5,0,0)));
358 CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(6,0,0)));
359
360 m_pDoc->DeleteTab(0);
361 }
362
363 // regression test fo fdo#53814, sorting doesn't work as expected
364 // if cells in the sort are referenced by formulas
365 void Test::testSortWithFormulaRefs()
366 {
367 SortRefUpdateSetter aUpdateSet;
368
369 m_pDoc->InsertTab(0, "List1");
370 m_pDoc->InsertTab(1, "List2");
371
372 const char* aFormulaData[6] = {
373 "=IF($List1.A2<>\"\";$List1.A2;\"\")",
374 "=IF($List1.A3<>\"\";$List1.A3;\"\")",
375 "=IF($List1.A4<>\"\";$List1.A4;\"\")",
376 "=IF($List1.A5<>\"\";$List1.A5;\"\")",
377 "=IF($List1.A6<>\"\";$List1.A6;\"\")",
378 "=IF($List1.A7<>\"\";$List1.A7;\"\")",
379 };
380
381 const char* aTextData[4] = {
382 "bob",
383 "tim",
384 "brian",
385 "larry",
386 };
387
388 const char* aResults[6] = {
389 "bob",
390 "brian",
391 "larry",
392 "tim",
393 "",
394 "",
395 };
396
397 // Insert data to sort in A2:A5 on the 1st sheet.
398 for (SCROW i = 1; i <= 4; ++i)
399 m_pDoc->SetString( 0, i, 0, OUString::createFromAscii(aTextData[i-1]) );
400
401 // Insert forumulas in A1:A6 on the 2nd sheet.
402 for (size_t i = 0; i < SAL_N_ELEMENTS(aFormulaData); ++i)
403 m_pDoc->SetString( 0, i, 1, OUString::createFromAscii(aFormulaData[i]) );
404
405 // Sort data in A2:A8 on the 1st sheet. No column header.
406 ScSortParam aSortData;
407 aSortData.nCol1 = 0;
408 aSortData.nCol2 = 0;
409 aSortData.nRow1 = 1;
410 aSortData.nRow2 = 7;
411 aSortData.maKeyState[0].bDoSort = true;
412 aSortData.maKeyState[0].nField = 0;
413
414 m_pDoc->Sort(0, aSortData, false, true, NULL, NULL);
415
416 for (size_t i = 0; i < SAL_N_ELEMENTS(aResults); ++i)
417 {
418 OUString sResult = m_pDoc->GetString(0, i + 1, 0);
419 CPPUNIT_ASSERT_EQUAL( OUString::createFromAscii( aResults[i] ), sResult );
420 }
421 m_pDoc->DeleteTab(1);
422 m_pDoc->DeleteTab(0);
423 }
424
425 void Test::testSortWithStrings()
426 {
427 m_pDoc->InsertTab(0, "Test");
428
429 ScFieldEditEngine& rEE = m_pDoc->GetEditEngine();
430 rEE.SetText("Val1");
431 m_pDoc->SetString(ScAddress(1,1,0), "Header");
432 m_pDoc->SetString(ScAddress(1,2,0), "Val2");
433 m_pDoc->SetEditText(ScAddress(1,3,0), rEE.CreateTextObject());
434
435 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(1,1,0)));
436 CPPUNIT_ASSERT_EQUAL(OUString("Val2"), m_pDoc->GetString(ScAddress(1,2,0)));
437 CPPUNIT_ASSERT_EQUAL(OUString("Val1"), m_pDoc->GetString(ScAddress(1,3,0)));
438
439 ScSortParam aParam;
440 aParam.nCol1 = 1;
441 aParam.nCol2 = 1;
442 aParam.nRow1 = 1;
443 aParam.nRow2 = 3;
444 aParam.bHasHeader = true;
445 aParam.maKeyState[0].bDoSort = true;
446 aParam.maKeyState[0].bAscending = true;
447 aParam.maKeyState[0].nField = 1;
448
449 m_pDoc->Sort(0, aParam, false, true, NULL, NULL);
450
451 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(1,1,0)));
452 CPPUNIT_ASSERT_EQUAL(OUString("Val1"), m_pDoc->GetString(ScAddress(1,2,0)));
453 CPPUNIT_ASSERT_EQUAL(OUString("Val2"), m_pDoc->GetString(ScAddress(1,3,0)));
454
455 aParam.maKeyState[0].bAscending = false;
456
457 m_pDoc->Sort(0, aParam, false, true, NULL, NULL);
458
459 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(1,1,0)));
460 CPPUNIT_ASSERT_EQUAL(OUString("Val2"), m_pDoc->GetString(ScAddress(1,2,0)));
461 CPPUNIT_ASSERT_EQUAL(OUString("Val1"), m_pDoc->GetString(ScAddress(1,3,0)));
462
463 m_pDoc->DeleteTab(0);
464 }
465
466 void Test::testSortInFormulaGroup()
467 {
468 SortRefUpdateSetter aUpdateSet;
469
470 static struct {
471 SCCOL nCol;
472 SCROW nRow;
473 const char *pData;
474 } aEntries[] = {
475 { 0, 0, "3" }, { 1, 0, "=A1" },
476 { 0, 1, "1" }, { 1, 1, "=A2" },
477 { 0, 2, "20" }, { 1, 2, "=A3" },
478 { 0, 3, "10" }, { 1, 3, "=A4+1" }, // swap across groups
479 { 0, 4, "2" }, { 1, 4, "=A5+1" },
480 { 0, 5, "101" }, { 1, 5, "=A6" }, // swap inside contiguious group
481 { 0, 6, "100" }, { 1, 6, "=A7" },
482 { 0, 7, "102" }, { 1, 7, "=A8" },
483 { 0, 8, "104" }, { 1, 8, "=A9" },
484 { 0, 9, "103" }, { 1, 9, "=A10" },
485 };
486
487 m_pDoc->InsertTab(0, "sorttest");
488
489 for ( SCROW i = 0; i < (SCROW) SAL_N_ELEMENTS( aEntries ); ++i )
490 m_pDoc->SetString( aEntries[i].nCol, aEntries[i].nRow, 0,
491 OUString::createFromAscii( aEntries[i].pData) );
492
493 ScSortParam aSortData;
494 aSortData.nCol1 = 0;
495 aSortData.nCol2 = 1;
496 aSortData.nRow1 = 0;
497 aSortData.nRow2 = 9;
498 aSortData.maKeyState[0].bDoSort = true;
499 aSortData.maKeyState[0].nField = 0;
500 aSortData.maKeyState[0].bAscending = true;
501
502 m_pDoc->Sort(0, aSortData, false, true, NULL, NULL);
503
504 static struct {
505 SCCOL nCol;
506 SCROW nRow;
507 double fValue;
508 } aResults[] = {
509 { 0, 0, 1.0 }, { 1, 0, 1.0 },
510 { 0, 1, 2.0 }, { 1, 1, 3.0 },
511 { 0, 2, 3.0 }, { 1, 2, 3.0 },
512 { 0, 3, 10.0 }, { 1, 3, 11.0 },
513 { 0, 4, 20.0 }, { 1, 4, 20.0 },
514 { 0, 5, 100.0 }, { 1, 5, 100.0 },
515 { 0, 6, 101.0 }, { 1, 6, 101.0 },
516 { 0, 7, 102.0 }, { 1, 7, 102.0 },
517 { 0, 8, 103.0 }, { 1, 8, 103.0 },
518 { 0, 9, 104.0 }, { 1, 9, 104.0 },
519 };
520
521 for ( SCROW i = 0; i < (SCROW) SAL_N_ELEMENTS( aEntries ); ++i )
522 {
523 double val = m_pDoc->GetValue( aEntries[i].nCol, aEntries[i].nRow, 0 );
524 // fprintf(stderr, "value at %d %d is %g = %g\n",
525 // (int)aResults[i].nRow, (int)aResults[i].nCol,
526 // val, aResults[i].fValue);
527 CPPUNIT_ASSERT_MESSAGE("Mis-matching value after sort.",
528 rtl::math::approxEqual(val, aResults[i].fValue));
529 }
530
531 m_pDoc->DeleteTab( 0 );
532 }
533
534 void Test::testSortWithCellFormats()
535 {
536 struct
537 {
538 bool isBold( const ScPatternAttr* pPat ) const
539 {
540 if (!pPat)
541 {
542 cerr << "Pattern is NULL!" << endl;
543 return false;
544 }
545
546 const SfxPoolItem* pItem = NULL;
547 if (!pPat->GetItemSet().HasItem(ATTR_FONT_WEIGHT, &pItem))
548 {
549 cerr << "Pattern does not have a font weight item, but it should." << endl;
550 return false;
551 }
552
553 CPPUNIT_ASSERT(pItem);
554
555 if (static_cast<const SvxWeightItem*>(pItem)->GetEnumValue() != WEIGHT_BOLD)
556 {
557 cerr << "Font weight should be bold." << endl;
558 return false;
559 }
560
561 return true;
562 }
563
564 bool isItalic( const ScPatternAttr* pPat ) const
565 {
566 if (!pPat)
567 {
568 cerr << "Pattern is NULL!" << endl;
569 return false;
570 }
571
572 const SfxPoolItem* pItem = NULL;
573 if (!pPat->GetItemSet().HasItem(ATTR_FONT_POSTURE, &pItem))
574 {
575 cerr << "Pattern does not have a font posture item, but it should." << endl;
576 return false;
577 }
578
579 CPPUNIT_ASSERT(pItem);
580
581 if (static_cast<const SvxPostureItem*>(pItem)->GetEnumValue() != ITALIC_NORMAL)
582 {
583 cerr << "Italic should be applied.." << endl;
584 return false;
585 }
586
587 return true;
588 }
589
590 bool isNormal( const ScPatternAttr* pPat ) const
591 {
592 if (!pPat)
593 {
594 cerr << "Pattern is NULL!" << endl;
595 return false;
596 }
597
598 const SfxPoolItem* pItem = NULL;
599 if (pPat->GetItemSet().HasItem(ATTR_FONT_WEIGHT, &pItem))
600 {
601 // Check if the font weight is applied.
602 if (static_cast<const SvxWeightItem*>(pItem)->GetEnumValue() == WEIGHT_BOLD)
603 {
604 cerr << "This cell is bold, but shouldn't." << endl;
605 return false;
606 }
607 }
608
609 if (pPat->GetItemSet().HasItem(ATTR_FONT_POSTURE, &pItem))
610 {
611 // Check if the italics is applied.
612 if (static_cast<const SvxPostureItem*>(pItem)->GetEnumValue() == ITALIC_NORMAL)
613 {
614 cerr << "This cell is bold, but shouldn't." << endl;
615 return false;
616 }
617 }
618
619 return true;
620 }
621
622 } aCheck;
623
624 m_pDoc->InsertTab(0, "Test");
625
626 // Insert some values into A1:A4.
627 m_pDoc->SetString(ScAddress(0,0,0), "Header");
628 m_pDoc->SetString(ScAddress(0,1,0), "Normal");
629 m_pDoc->SetString(ScAddress(0,2,0), "Bold");
630 m_pDoc->SetString(ScAddress(0,3,0), "Italic");
631
632 // Set A3 bold and A4 italic.
633 const ScPatternAttr* pPat = m_pDoc->GetPattern(ScAddress(0,2,0));
634 CPPUNIT_ASSERT(pPat);
635 {
636 ScPatternAttr aNewPat(*pPat);
637 SfxItemSet& rSet = aNewPat.GetItemSet();
638 rSet.Put(SvxWeightItem(WEIGHT_BOLD, ATTR_FONT_WEIGHT));
639 m_pDoc->ApplyPattern(0, 2, 0, aNewPat);
640
641 // Make sure it's really in.
642 bool bGood = aCheck.isBold(m_pDoc->GetPattern(ScAddress(0,2,0)));
643 CPPUNIT_ASSERT_MESSAGE("A3 is not bold but it should.", bGood);
644 }
645
646 pPat = m_pDoc->GetPattern(ScAddress(0,3,0));
647 CPPUNIT_ASSERT(pPat);
648 {
649 ScPatternAttr aNewPat(*pPat);
650 SfxItemSet& rSet = aNewPat.GetItemSet();
651 rSet.Put(SvxPostureItem(ITALIC_NORMAL, ATTR_FONT_POSTURE));
652 m_pDoc->ApplyPattern(0, 3, 0, aNewPat);
653
654 bool bGood = aCheck.isItalic(m_pDoc->GetPattern(ScAddress(0,3,0)));
655 CPPUNIT_ASSERT_MESSAGE("A4 is not italic but it should.", bGood);
656 }
657
658 // Define A1:A4 as sheet-local anonymous database range, else sort wouldn't run.
659 m_pDoc->SetAnonymousDBData(
660 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 0, 3));
661
662 // Sort A1:A4 ascending with cell formats.
663 ScDBDocFunc aFunc(getDocShell());
664
665 ScSortParam aSortData;
666 aSortData.nCol1 = 0;
667 aSortData.nCol2 = 0;
668 aSortData.nRow1 = 0;
669 aSortData.nRow2 = 3;
670 aSortData.bHasHeader = true;
671 aSortData.bIncludePattern = true;
672 aSortData.maKeyState[0].bDoSort = true;
673 aSortData.maKeyState[0].nField = 0;
674 aSortData.maKeyState[0].bAscending = true;
675 bool bSorted = aFunc.Sort(0, aSortData, true, false, true);
676 CPPUNIT_ASSERT(bSorted);
677
678 // Check the sort result.
679 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
680 CPPUNIT_ASSERT_EQUAL(OUString("Bold"), m_pDoc->GetString(ScAddress(0,1,0)));
681 CPPUNIT_ASSERT_EQUAL(OUString("Italic"), m_pDoc->GetString(ScAddress(0,2,0)));
682 CPPUNIT_ASSERT_EQUAL(OUString("Normal"), m_pDoc->GetString(ScAddress(0,3,0)));
683
684 // A2 should be bold now.
685 bool bBold = aCheck.isBold(m_pDoc->GetPattern(ScAddress(0,1,0)));
686 CPPUNIT_ASSERT_MESSAGE("A2 should be bold after the sort.", bBold);
687
688 // and A3 should be italic.
689 bool bItalic = aCheck.isItalic(m_pDoc->GetPattern(ScAddress(0,2,0)));
690 CPPUNIT_ASSERT_MESSAGE("A3 should be italic.", bItalic);
691
692 // A4 should have neither bold nor italic.
693 bool bNormal = aCheck.isNormal(m_pDoc->GetPattern(ScAddress(0,3,0)));
694 CPPUNIT_ASSERT_MESSAGE("A4 should be neither bold nor italic.", bNormal);
695
696 m_pDoc->DeleteTab(0);
697 }
698
699 void Test::testSortRefUpdate()
700 {
701 SortTypeSetter aSortTypeSet(true);
702
703 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
704 FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
705
706 m_pDoc->InsertTab(0, "Sort");
707
708 // Set values to sort in column A.
709 m_pDoc->SetString(ScAddress(0,0,0), "Header");
710
711 double aValues[] = { 4.0, 36.0, 14.0, 29.0, 98.0, 78.0, 0.0, 99.0, 1.0 };
712 size_t nCount = SAL_N_ELEMENTS(aValues);
713 for (size_t i = 0; i < nCount; ++i)
714 m_pDoc->SetValue(ScAddress(0,i+1,0), aValues[i]);
715
716 // Set formulas to reference these values in column C.
717 m_pDoc->SetString(ScAddress(2,0,0), "Formula");
718 for (size_t i = 0; i < nCount; ++i)
719 m_pDoc->SetString(ScAddress(2,1+i,0), "=RC[-2]");
720
721 // Check the values in column C.
722 for (size_t i = 0; i < nCount; ++i)
723 {
724 double fCheck = aValues[i];
725 CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
726 }
727
728 ScDBDocFunc aFunc(getDocShell());
729
730 // Define A1:A10 as sheet-local anonymous database range, else sort wouldn't run.
731 m_pDoc->SetAnonymousDBData(
732 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 0, 9));
733
734 // Sort A1:A10 (with a header row).
735 ScSortParam aSortData;
736 aSortData.nCol1 = 0;
737 aSortData.nCol2 = 0;
738 aSortData.nRow1 = 0;
739 aSortData.nRow2 = 9;
740 aSortData.bHasHeader = true;
741 aSortData.maKeyState[0].bDoSort = true;
742 aSortData.maKeyState[0].nField = 0;
743 aSortData.maKeyState[0].bAscending = true;
744 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
745 CPPUNIT_ASSERT(bSorted);
746
747 double aSorted[] = { 0.0, 1.0, 4.0, 14.0, 29.0, 36.0, 78.0, 98.0, 99.0 };
748
749 // Check the sort result.
750 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
751 for (size_t i = 0; i < nCount; ++i)
752 {
753 double fCheck = aSorted[i];
754 CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(0,i+1,0)));
755 }
756
757 // Sorting should not alter the values in column C.
758 m_pDoc->CalcAll(); // just in case...
759 for (size_t i = 0; i < nCount; ++i)
760 {
761 double fCheck = aValues[i];
762 CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
763 }
764
765 // C2 should now point to A4.
766 if (!checkFormula(*m_pDoc, ScAddress(2,1,0), "R[2]C[-2]"))
767 CPPUNIT_FAIL("Wrong formula in C2!");
768
769 // Undo the sort.
770 SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
771 pUndoMgr->Undo();
772
773 // Check the undo result.
774 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
775 for (size_t i = 0; i < nCount; ++i)
776 {
777 double fCheck = aValues[i];
778 CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(0,i+1,0)));
779 }
780
781 // Values in column C should still be unaltered.
782 m_pDoc->CalcAll(); // just in case...
783 for (size_t i = 0; i < nCount; ++i)
784 {
785 double fCheck = aValues[i];
786 CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
787 }
788
789 // C2 should now point to A2.
790 if (!checkFormula(*m_pDoc, ScAddress(2,1,0), "RC[-2]"))
791 CPPUNIT_FAIL("Wrong formula in C2!");
792
793 // Redo.
794 pUndoMgr->Redo();
795
796 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
797 for (size_t i = 0; i < nCount; ++i)
798 {
799 double fCheck = aSorted[i];
800 CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(0,i+1,0)));
801 }
802
803 // Sorting should not alter the values in column C.
804 m_pDoc->CalcAll(); // just in case...
805 for (size_t i = 0; i < nCount; ++i)
806 {
807 double fCheck = aValues[i];
808 CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
809 }
810
811 // C2 should now point to A4.
812 if (!checkFormula(*m_pDoc, ScAddress(2,1,0), "R[2]C[-2]"))
813 CPPUNIT_FAIL("Wrong formula in C2!");
814
815 // Undo again.
816 pUndoMgr->Undo();
817
818 // Formulas in column C should all be "RC[-2]" again.
819 for (size_t i = 0; i < nCount; ++i)
820 m_pDoc->SetString(ScAddress(2,1+i,0), "=RC[-2]");
821
822 // Turn off reference update on sort.
823 SortTypeSetter::changeTo(false);
824
825 bSorted = aFunc.Sort(0, aSortData, true, true, true);
826 CPPUNIT_ASSERT(bSorted);
827
828 // Check the sort result again.
829 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
830 for (size_t i = 0; i < nCount; ++i)
831 {
832 double fCheck = aSorted[i];
833 CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(0,i+1,0)));
834 }
835
836 // Formulas in column C should all remain "RC[-2]".
837 for (size_t i = 0; i < nCount; ++i)
838 m_pDoc->SetString(ScAddress(2,1+i,0), "=RC[-2]");
839
840 // The values in column C should now be the same as sorted values in column A.
841 m_pDoc->CalcAll(); // just in case...
842 for (size_t i = 0; i < nCount; ++i)
843 {
844 double fCheck = aSorted[i];
845 CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0))); // column C
846 }
847
848 m_pDoc->DeleteTab(0);
849 }
850
851 void Test::testSortRefUpdate2()
852 {
853 SortRefUpdateSetter aUpdateSet;
854
855 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
856 FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
857
858 m_pDoc->InsertTab(0, "Sort");
859
860 // Set up the sheet.
861 const char* aData[][2] = {
862 { "F1", "F2" },
863 { "9", "=RC[-1]" },
864 { "2", "=RC[-1]" },
865 { "6", "=RC[-1]" },
866 { "4", "=RC[-1]" },
867 { 0, 0 } // terminator
868 };
869
870 for (SCROW i = 0; aData[i][0]; ++i)
871 {
872 m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
873 m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
874 }
875
876 // Check the values in B2:B5.
877 CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,1,0)));
878 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
879 CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
880 CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,4,0)));
881
882 ScDBDocFunc aFunc(getDocShell());
883
884 // Define A1:B5 as sheet-local anonymous database range, else sort wouldn't run.
885 m_pDoc->SetAnonymousDBData(
886 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 1, 4));
887
888 // Sort A1:B5 by column A (with a row header).
889 ScSortParam aSortData;
890 aSortData.nCol1 = 0;
891 aSortData.nCol2 = 1;
892 aSortData.nRow1 = 0;
893 aSortData.nRow2 = 4;
894 aSortData.bHasHeader = true;
895 aSortData.maKeyState[0].bDoSort = true;
896 aSortData.maKeyState[0].nField = 0;
897 aSortData.maKeyState[0].bAscending = true;
898 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
899 CPPUNIT_ASSERT(bSorted);
900
901 // Check the sort result in column A.
902 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,0)));
903 CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(0,2,0)));
904 CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,3,0)));
905 CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(0,4,0)));
906
907 // and column B.
908 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
909 CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,2,0)));
910 CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
911 CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,4,0)));
912
913 // Formulas in column B should still point to their respective left neighbor cell.
914 for (SCROW i = 1; i <= 4; ++i)
915 {
916 if (!checkFormula(*m_pDoc, ScAddress(1,i,0), "RC[-1]"))
917 CPPUNIT_FAIL("Wrong formula!");
918 }
919
920 // Undo and check the result in column B.
921 SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
922 pUndoMgr->Undo();
923
924 CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,1,0)));
925 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
926 CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
927 CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,4,0)));
928
929 // and redo.
930 pUndoMgr->Redo();
931
932 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
933 CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,2,0)));
934 CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
935 CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,4,0)));
936
937 m_pDoc->DeleteTab(0);
938 }
939
940 void Test::testSortRefUpdate3()
941 {
942 SortRefUpdateSetter aUpdateSet;
943
944 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
945 m_pDoc->InsertTab(0, "Sort");
946
947 const char* pData[] = {
948 "Header",
949 "1",
950 "=A2+10",
951 "2",
952 "=A4+10",
953 "=A2+A4",
954 0 // terminator
955 };
956
957 for (SCROW i = 0; pData[i]; ++i)
958 m_pDoc->SetString(ScAddress(0,i,0), OUString::createFromAscii(pData[i]));
959
960 // Check the initial values.
961 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
962 CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
963 CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,2,0)));
964 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
965 CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,4,0)));
966 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,5,0)));
967
968 ScDBDocFunc aFunc(getDocShell());
969
970 // Sort A1:A6.
971 m_pDoc->SetAnonymousDBData(
972 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 0, 5));
973
974 // Sort A1:A6 by column A (with a row header).
975 ScSortParam aSortData;
976 aSortData.nCol1 = 0;
977 aSortData.nCol2 = 0;
978 aSortData.nRow1 = 0;
979 aSortData.nRow2 = 5;
980 aSortData.bHasHeader = true;
981 aSortData.maKeyState[0].bDoSort = true;
982 aSortData.maKeyState[0].nField = 0;
983 aSortData.maKeyState[0].bAscending = true;
984 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
985 CPPUNIT_ASSERT(bSorted);
986
987 // Check the sorted values.
988 m_pDoc->CalcAll();
989 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
990 CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
991 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,2,0)));
992 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,3,0)));
993 CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,4,0)));
994 CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,5,0)));
995
996 // Make sure the formula cells have been adjusted correctly.
997 if (!checkFormula(*m_pDoc, ScAddress(0,3,0), "A2+A3"))
998 CPPUNIT_FAIL("Wrong formula in A4.");
999 if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "A2+10"))
1000 CPPUNIT_FAIL("Wrong formula in A5.");
1001 if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "A3+10"))
1002 CPPUNIT_FAIL("Wrong formula in A6.");
1003
1004 // Undo and check the result.
1005 SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1006 pUndoMgr->Undo();
1007 m_pDoc->CalcAll();
1008 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
1009 CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1010 CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1011 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1012 CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1013 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1014
1015 // Redo and check the result.
1016 pUndoMgr->Redo();
1017 m_pDoc->CalcAll();
1018 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
1019 CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1020 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1021 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1022 CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1023 CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1024
1025 m_pDoc->DeleteTab(0);
1026 }
1027
1028 // Derived from fdo#79441 https://bugs.freedesktop.org/attachment.cgi?id=100144
1029 // testRefInterne.ods
1030 void Test::testSortRefUpdate4()
1031 {
1032 // This test has to work in both update reference modes.
1033 {
1034 SortRefNoUpdateSetter aUpdateSet;
1035 testSortRefUpdate4_Impl();
1036 }
1037 {
1038 SortRefUpdateSetter aUpdateSet;
1039 testSortRefUpdate4_Impl();
1040 }
1041 }
1042
1043 void Test::testSortRefUpdate4_Impl()
1044 {
1045 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1046 m_pDoc->InsertTab(0, "Sort");
1047 m_pDoc->InsertTab(1, "Lesson1");
1048 m_pDoc->InsertTab(2, "Lesson2");
1049
1050 ScRange aLesson1Range;
1051 {
1052 const char* aData[][2] = {
1053 { "Name", "Note" },
1054 { "Student1", "1" },
1055 { "Student2", "2" },
1056 { "Student3", "3" },
1057 { "Student4", "4" },
1058 { "Student5", "5" },
1059 };
1060
1061 SCTAB nTab = 1;
1062 ScAddress aPos(0,0,nTab);
1063 clearRange(m_pDoc, ScRange(0, 0, nTab, 1, SAL_N_ELEMENTS(aData), nTab));
1064 aLesson1Range = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1065 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aLesson1Range.aStart == aPos);
1066 }
1067
1068 ScRange aLesson2Range;
1069 {
1070 const char* aData[][2] = {
1071 { "Name", "Note" },
1072 { "=Lesson1.A2", "3" },
1073 { "=Lesson1.A3", "4" },
1074 { "=Lesson1.A4", "9" },
1075 { "=Lesson1.A5", "6" },
1076 { "=Lesson1.A6", "3" },
1077 };
1078
1079 SCTAB nTab = 2;
1080 ScAddress aPos(0,0,nTab);
1081 clearRange(m_pDoc, ScRange(0, 0, nTab, 1, SAL_N_ELEMENTS(aData), nTab));
1082 aLesson2Range = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1083 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aLesson2Range.aStart == aPos);
1084 }
1085
1086 ScRange aSortRange;
1087 {
1088 const char* aData[][4] = {
1089 { "Name", "Lesson1", "Lesson2", "Average" },
1090 { "=Lesson1.A2", "=Lesson1.B2", "=Lesson2.B2", "=AVERAGE(B2:C2)" },
1091 { "=Lesson1.A3", "=Lesson1.B3", "=Lesson2.B3", "=AVERAGE(B3:C3)" },
1092 { "=Lesson1.A4", "=Lesson1.B4", "=Lesson2.B4", "=AVERAGE(B4:C4)" },
1093 { "=Lesson1.A5", "=Lesson1.B5", "=Lesson2.B5", "=AVERAGE(B5:C5)" },
1094 { "=Lesson1.A6", "=Lesson1.B6", "=Lesson2.B6", "=AVERAGE(B6:C6)" },
1095 };
1096
1097 SCTAB nTab = 0;
1098 ScAddress aPos(0,0,nTab);
1099 clearRange(m_pDoc, ScRange(0, 0, nTab, 1, SAL_N_ELEMENTS(aData), nTab));
1100 aSortRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1101 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aSortRange.aStart == aPos);
1102 }
1103
1104 ScDBDocFunc aFunc(getDocShell());
1105
1106 // Sort A1:D6 by column D (Average, with a row header).
1107 {
1108 ScSortParam aSortData;
1109 aSortData.nCol1 = aSortRange.aStart.Col();
1110 aSortData.nCol2 = aSortRange.aEnd.Col();
1111 aSortData.nRow1 = aSortRange.aStart.Row();
1112 aSortData.nRow2 = aSortRange.aEnd.Row();
1113 aSortData.bHasHeader = true;
1114 aSortData.maKeyState[0].bDoSort = true; // sort on
1115 aSortData.maKeyState[0].nField = 3; // Average
1116 aSortData.maKeyState[0].bAscending = false; // descending
1117
1118 m_pDoc->SetAnonymousDBData( 0, new ScDBData( STR_DB_LOCAL_NONAME, aSortRange.aStart.Tab(),
1119 aSortData.nCol1, aSortData.nRow1, aSortData.nCol2, aSortData.nRow2));
1120
1121 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1122 CPPUNIT_ASSERT(bSorted);
1123
1124 // Check the sorted values.
1125 m_pDoc->CalcAll();
1126 CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
1127 CPPUNIT_ASSERT_EQUAL(OUString("Student3"), m_pDoc->GetString(ScAddress(0,1,0)));
1128 CPPUNIT_ASSERT_EQUAL(OUString("Student4"), m_pDoc->GetString(ScAddress(0,2,0)));
1129 CPPUNIT_ASSERT_EQUAL(OUString("Student5"), m_pDoc->GetString(ScAddress(0,3,0)));
1130 CPPUNIT_ASSERT_EQUAL(OUString("Student2"), m_pDoc->GetString(ScAddress(0,4,0)));
1131 CPPUNIT_ASSERT_EQUAL(OUString("Student1"), m_pDoc->GetString(ScAddress(0,5,0)));
1132 CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,1,0)));
1133 CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,2,0)));
1134 CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,3,0)));
1135 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,4,0)));
1136 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,5,0)));
1137
1138 // Make sure the formula cells have been adjusted correctly.
1139 const char* aCheck[][4] = {
1140 // Name Lesson1 Lesson2 Average
1141 { "Lesson1.A4", "Lesson1.B4", "Lesson2.B4", "AVERAGE(B2:C2)" },
1142 { "Lesson1.A5", "Lesson1.B5", "Lesson2.B5", "AVERAGE(B3:C3)" },
1143 { "Lesson1.A6", "Lesson1.B6", "Lesson2.B6", "AVERAGE(B4:C4)" },
1144 { "Lesson1.A3", "Lesson1.B3", "Lesson2.B3", "AVERAGE(B5:C5)" },
1145 { "Lesson1.A2", "Lesson1.B2", "Lesson2.B2", "AVERAGE(B6:C6)" },
1146 };
1147 for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aCheck)); ++nRow)
1148 {
1149 for (SCCOL nCol=0; nCol < 4; ++nCol)
1150 {
1151 if (!checkFormula(*m_pDoc, ScAddress(nCol,nRow+1,0), aCheck[nRow][nCol]))
1152 CPPUNIT_FAIL(OString("Wrong formula in " + OString('A'+nCol) + OString::number(nRow+2) + ".").getStr());
1153 }
1154 }
1155
1156 // Undo and check the result.
1157 SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1158 pUndoMgr->Undo();
1159 m_pDoc->CalcAll();
1160 CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
1161 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,1,0)));
1162 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,2,0)));
1163 CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,3,0)));
1164 CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,4,0)));
1165 CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,5,0)));
1166
1167 // Redo and check the result.
1168 pUndoMgr->Redo();
1169 m_pDoc->CalcAll();
1170 CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
1171 CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,1,0)));
1172 CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,2,0)));
1173 CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,3,0)));
1174 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,4,0)));
1175 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,5,0)));
1176 }
1177
1178 // Sort A2:AMJ6 by column A (Name, without header).
1179 {
1180 ScSortParam aSortData;
1181 aSortData.nCol1 = 0;
1182 aSortData.nCol2 = MAXCOL;
1183 aSortData.nRow1 = aSortRange.aStart.Row()+1;
1184 aSortData.nRow2 = aSortRange.aEnd.Row();
1185 aSortData.bHasHeader = false;
1186 aSortData.maKeyState[0].bDoSort = true; // sort on
1187 aSortData.maKeyState[0].nField = 0; // Name
1188 aSortData.maKeyState[0].bAscending = false; // descending
1189
1190 m_pDoc->SetAnonymousDBData( 0, new ScDBData( STR_DB_LOCAL_NONAME, aSortRange.aStart.Tab(),
1191 aSortData.nCol1, aSortData.nRow1, aSortData.nCol2, aSortData.nRow2));
1192
1193 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1194 CPPUNIT_ASSERT(bSorted);
1195
1196 // Check the sorted values.
1197 m_pDoc->CalcAll();
1198 CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
1199 CPPUNIT_ASSERT_EQUAL(OUString("Student5"), m_pDoc->GetString(ScAddress(0,1,0)));
1200 CPPUNIT_ASSERT_EQUAL(OUString("Student4"), m_pDoc->GetString(ScAddress(0,2,0)));
1201 CPPUNIT_ASSERT_EQUAL(OUString("Student3"), m_pDoc->GetString(ScAddress(0,3,0)));
1202 CPPUNIT_ASSERT_EQUAL(OUString("Student2"), m_pDoc->GetString(ScAddress(0,4,0)));
1203 CPPUNIT_ASSERT_EQUAL(OUString("Student1"), m_pDoc->GetString(ScAddress(0,5,0)));
1204 CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,1,0)));
1205 CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,2,0)));
1206 CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,3,0)));
1207 CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,4,0)));
1208 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,5,0)));
1209
1210 // Make sure the formula cells have been adjusted correctly.
1211 const char* aCheck[][4] = {
1212 // Name Lesson1 Lesson2 Average
1213 { "Lesson1.A6", "Lesson1.B6", "Lesson2.B6", "AVERAGE(B2:C2)" },
1214 { "Lesson1.A5", "Lesson1.B5", "Lesson2.B5", "AVERAGE(B3:C3)" },
1215 { "Lesson1.A4", "Lesson1.B4", "Lesson2.B4", "AVERAGE(B4:C4)" },
1216 { "Lesson1.A3", "Lesson1.B3", "Lesson2.B3", "AVERAGE(B5:C5)" },
1217 { "Lesson1.A2", "Lesson1.B2", "Lesson2.B2", "AVERAGE(B6:C6)" },
1218 };
1219 for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aCheck)); ++nRow)
1220 {
1221 for (SCCOL nCol=0; nCol < 4; ++nCol)
1222 {
1223 if (!checkFormula(*m_pDoc, ScAddress(nCol,nRow+1,0), aCheck[nRow][nCol]))
1224 CPPUNIT_FAIL(OString("Wrong formula in " + OString('A'+nCol) + OString::number(nRow+2) + ".").getStr());
1225 }
1226 }
1227 }
1228
1229 m_pDoc->DeleteTab(2);
1230 m_pDoc->DeleteTab(1);
1231 m_pDoc->DeleteTab(0);
1232 }
1233
1234 // Make sure the refupdate works also with volatile cells, see fdo#83067
1235 /* FIXME: this test is not roll-over-midnight safe and will fail then! We may
1236 * want to have something different, but due to the nature of volatile
1237 * functions it's not that easy to come up with something reproducible staying
1238 * stable over sorts.. ;-) Check for time and don't run test a few seconds
1239 * before midnight, ermm.. */
1240 void Test::testSortRefUpdate5()
1241 {
1242 SortRefUpdateSetter aUpdateSet;
1243
1244 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1245 m_pDoc->InsertTab(0, "Sort");
1246
1247 double aValCheck[][3] = {
1248 // Result, Unsorted order, Sorted result.
1249 { 0, 4, 0 },
1250 { 0, 1, 0 },
1251 { 0, 3, 0 },
1252 { 0, 2, 0 },
1253 };
1254 ScRange aSortRange;
1255 {
1256 const char* aData[][3] = {
1257 { "Date", "Volatile", "Order" },
1258 { "1999-05-05", "=TODAY()-$A2", "4" },
1259 { "1994-10-18", "=TODAY()-$A3", "1" },
1260 { "1996-06-30", "=TODAY()-$A4", "3" },
1261 { "1995-11-21", "=TODAY()-$A5", "2" },
1262 };
1263
1264 SCTAB nTab = 0;
1265 ScAddress aPos(0,0,nTab);
1266 clearRange(m_pDoc, ScRange(0, 0, nTab, 2, SAL_N_ELEMENTS(aData), nTab));
1267 aSortRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1268 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aSortRange.aStart == aPos);
1269
1270 // Actual results and expected sorted results.
1271 for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
1272 {
1273 double fVal = m_pDoc->GetValue(ScAddress(1,nRow+1,0));
1274 aValCheck[nRow][0] = fVal;
1275 aValCheck[static_cast<size_t>(aValCheck[nRow][1])-1][2] = fVal;
1276 }
1277 }
1278
1279 ScDBDocFunc aFunc(getDocShell());
1280
1281 // Sort A1:B5.
1282 m_pDoc->SetAnonymousDBData( 0, new ScDBData( STR_DB_LOCAL_NONAME, aSortRange.aStart.Tab(),
1283 aSortRange.aStart.Col(), aSortRange.aStart.Row(), aSortRange.aEnd.Col(), aSortRange.aEnd.Row()));
1284
1285 // Sort by column A.
1286 ScSortParam aSortData;
1287 aSortData.nCol1 = aSortRange.aStart.Col();
1288 aSortData.nCol2 = aSortRange.aEnd.Col();
1289 aSortData.nRow1 = aSortRange.aStart.Row();
1290 aSortData.nRow2 = aSortRange.aEnd.Row();
1291 aSortData.bHasHeader = true;
1292 aSortData.maKeyState[0].bDoSort = true; // sort on
1293 aSortData.maKeyState[0].nField = 0; // Date
1294 aSortData.maKeyState[0].bAscending = true; // ascending
1295 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1296 CPPUNIT_ASSERT(bSorted);
1297
1298 // Check the sorted values.
1299 m_pDoc->CalcAll();
1300 for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
1301 {
1302 size_t i = static_cast<size_t>(m_pDoc->GetValue(ScAddress(2,nRow+1,0))); // order 1..4
1303 CPPUNIT_ASSERT_EQUAL( static_cast<size_t>(nRow+1), i);
1304 CPPUNIT_ASSERT_EQUAL( aValCheck[i-1][2], m_pDoc->GetValue(ScAddress(1,nRow+1,0)));
1305 }
1306
1307 // Make sure the formula cells have been adjusted correctly.
1308 const char* aFormulaCheck[] = {
1309 // Volatile
1310 "TODAY()-$A2",
1311 "TODAY()-$A3",
1312 "TODAY()-$A4",
1313 "TODAY()-$A5",
1314 };
1315 for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aFormulaCheck)); ++nRow)
1316 {
1317 if (!checkFormula(*m_pDoc, ScAddress(1,nRow+1,0), aFormulaCheck[nRow]))
1318 CPPUNIT_FAIL(OString("Wrong formula in B" + OString::number(nRow+2) + ".").getStr());
1319 }
1320
1321 // Undo and check the result.
1322 SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1323 pUndoMgr->Undo();
1324 m_pDoc->CalcAll();
1325 for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
1326 {
1327 CPPUNIT_ASSERT_EQUAL( aValCheck[nRow][0], m_pDoc->GetValue(ScAddress(1,nRow+1,0)));
1328 CPPUNIT_ASSERT_EQUAL( aValCheck[nRow][1], m_pDoc->GetValue(ScAddress(2,nRow+1,0)));
1329 }
1330
1331 // Redo and check the result.
1332 pUndoMgr->Redo();
1333 m_pDoc->CalcAll();
1334 for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
1335 {
1336 size_t i = static_cast<size_t>(m_pDoc->GetValue(ScAddress(2,nRow+1,0))); // order 1..4
1337 CPPUNIT_ASSERT_EQUAL( static_cast<size_t>(nRow+1), i);
1338 CPPUNIT_ASSERT_EQUAL( aValCheck[i-1][2], m_pDoc->GetValue(ScAddress(1,nRow+1,0)));
1339 }
1340
1341 m_pDoc->DeleteTab(0);
1342 }
1343
1344 void Test::testSortRefUpdate6()
1345 {
1346 SortRefNoUpdateSetter aUpdateSet;
1347
1348 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1349 m_pDoc->InsertTab(0, "Sort");
1350
1351 const char* aData[][3] = {
1352 { "Order", "Value", "1" },
1353 { "9", "1", "=C1+B2" },
1354 { "1", "2", "=C2+B3" },
1355 { "8", "3", "=C3+B4" },
1356 };
1357
1358 ScAddress aPos(0,0,0);
1359 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1360 CPPUNIT_ASSERT(aDataRange.aStart == aPos);
1361
1362 {
1363 // Expected output table content. 0 = empty cell
1364 const char* aOutputCheck[][3] = {
1365 { "Order", "Value", "1" },
1366 { "9", "1", "2" },
1367 { "1", "2", "4" },
1368 { "8", "3", "7" },
1369 };
1370
1371 bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "Initial value");
1372 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1373 }
1374
1375 ScDBDocFunc aFunc(getDocShell());
1376
1377 // Sort A1:C4.
1378 m_pDoc->SetAnonymousDBData(
1379 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 2, 3));
1380
1381 // Sort A1:A6 by column A (with a row header).
1382 ScSortParam aSortData;
1383 aSortData.nCol1 = 0;
1384 aSortData.nCol2 = 2;
1385 aSortData.nRow1 = 0;
1386 aSortData.nRow2 = 3;
1387 aSortData.bHasHeader = true;
1388 aSortData.maKeyState[0].bDoSort = true;
1389 aSortData.maKeyState[0].nField = 0;
1390 aSortData.maKeyState[0].bAscending = true;
1391 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1392 CPPUNIT_ASSERT(bSorted);
1393
1394 {
1395 // Expected output table content. 0 = empty cell
1396 const char* aOutputCheck[][3] = {
1397 { "Order", "Value", "1" },
1398 { "1", "2", "3" },
1399 { "8", "3", "6" },
1400 { "9", "1", "7" },
1401 };
1402
1403 bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "Sorted without reference update");
1404 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1405 }
1406
1407 // Make sure that the formulas in C2:C4 are not adjusted.
1408 if (!checkFormula(*m_pDoc, ScAddress(2,1,0), "C1+B2"))
1409 CPPUNIT_FAIL("Wrong formula!");
1410 if (!checkFormula(*m_pDoc, ScAddress(2,2,0), "C2+B3"))
1411 CPPUNIT_FAIL("Wrong formula!");
1412 if (!checkFormula(*m_pDoc, ScAddress(2,3,0), "C3+B4"))
1413 CPPUNIT_FAIL("Wrong formula!");
1414
1415 // Undo and check.
1416 SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1417 CPPUNIT_ASSERT(pUndoMgr);
1418
1419 pUndoMgr->Undo();
1420
1421 {
1422 // Expected output table content. 0 = empty cell
1423 const char* aOutputCheck[][3] = {
1424 { "Order", "Value", "1" },
1425 { "9", "1", "2" },
1426 { "1", "2", "4" },
1427 { "8", "3", "7" },
1428 };
1429
1430 bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "After undo");
1431 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1432 }
1433
1434 // Redo and check.
1435 pUndoMgr->Redo();
1436 {
1437 // Expected output table content. 0 = empty cell
1438 const char* aOutputCheck[][3] = {
1439 { "Order", "Value", "1" },
1440 { "1", "2", "3" },
1441 { "8", "3", "6" },
1442 { "9", "1", "7" },
1443 };
1444
1445 bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "After redo");
1446 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1447 }
1448
1449 // Change the value of C1 and make sure the formula broadcasting chain still works.
1450 ScDocFunc& rFunc = getDocShell().GetDocFunc();
1451 rFunc.SetValueCell(ScAddress(2,0,0), 11.0, false);
1452 {
1453 // Expected output table content. 0 = empty cell
1454 const char* aOutputCheck[][3] = {
1455 { "Order", "Value", "11" },
1456 { "1", "2", "13" },
1457 { "8", "3", "16" },
1458 { "9", "1", "17" },
1459 };
1460
1461 bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "Change the header value");
1462 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1463 }
1464
1465 // Undo and check.
1466 pUndoMgr->Undo();
1467 {
1468 // Expected output table content. 0 = empty cell
1469 const char* aOutputCheck[][3] = {
1470 { "Order", "Value", "1" },
1471 { "1", "2", "3" },
1472 { "8", "3", "6" },
1473 { "9", "1", "7" },
1474 };
1475
1476 bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "After undo of header value change");
1477 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1478 }
1479
1480 m_pDoc->DeleteTab(0);
1481 }
1482
1483 // fdo#86762 check that broadcasters are sorted correctly and empty cell is
1484 // broadcasted.
1485 void Test::testSortBroadcaster()
1486 {
1487 SortRefNoUpdateSetter aUpdateSet;
1488
1489 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1490 m_pDoc->InsertTab(0, "Sort");
1491
1492 {
1493 const char* aData[][7] = {
1494 { "1", 0, 0, "=B1", "=$B$1", "=SUM(A1:B1)", "=SUM($A$1:$B$1)" },
1495 { "2", "8", 0, "=B2", "=$B$2", "=SUM(A2:B2)", "=SUM($A$2:$B$2)" },
1496 };
1497
1498 ScAddress aPos(0,0,0);
1499 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1500 CPPUNIT_ASSERT(aDataRange.aStart == aPos);
1501
1502 {
1503 // Expected output table content. 0 = empty cell
1504 const char* aOutputCheck[][7] = {
1505 { "1", 0, 0, "0", "0", "1", "1" },
1506 { "2", "8", 0, "8", "8", "10", "10" },
1507 };
1508
1509 bool bSuccess = checkOutput<7>(m_pDoc, aDataRange, aOutputCheck, "Initial value");
1510 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1511 }
1512
1513 // Sort A1:B2.
1514 m_pDoc->SetAnonymousDBData(
1515 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 1, 1));
1516
1517 ScDBDocFunc aFunc(getDocShell());
1518
1519 // Sort A1:B2 by column A descending.
1520 ScSortParam aSortData;
1521 aSortData.nCol1 = 0;
1522 aSortData.nCol2 = 1;
1523 aSortData.nRow1 = 0;
1524 aSortData.nRow2 = 1;
1525 aSortData.bHasHeader = false;
1526 aSortData.bByRow = true;
1527 aSortData.maKeyState[0].bDoSort = true;
1528 aSortData.maKeyState[0].nField = 0;
1529 aSortData.maKeyState[0].bAscending = false;
1530 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1531 CPPUNIT_ASSERT(bSorted);
1532
1533 {
1534 // Expected output table content. 0 = empty cell
1535 const char* aOutputCheck[][7] = {
1536 { "2", "8", 0, "8", "8", "10", "10" },
1537 { "1", 0, 0, "0", "0", "1", "1" },
1538 };
1539
1540 bool bSuccess = checkOutput<7>(m_pDoc, aDataRange, aOutputCheck, "Sorted without reference update");
1541 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1542 }
1543
1544 // Make sure that the formulas in D1:G2 are not adjusted.
1545 if (!checkFormula(*m_pDoc, ScAddress(3,0,0), "B1"))
1546 CPPUNIT_FAIL("Wrong formula!");
1547 if (!checkFormula(*m_pDoc, ScAddress(3,1,0), "B2"))
1548 CPPUNIT_FAIL("Wrong formula!");
1549 if (!checkFormula(*m_pDoc, ScAddress(4,0,0), "$B$1"))
1550 CPPUNIT_FAIL("Wrong formula!");
1551 if (!checkFormula(*m_pDoc, ScAddress(4,1,0), "$B$2"))
1552 CPPUNIT_FAIL("Wrong formula!");
1553 if (!checkFormula(*m_pDoc, ScAddress(5,0,0), "SUM(A1:B1)"))
1554 CPPUNIT_FAIL("Wrong formula!");
1555 if (!checkFormula(*m_pDoc, ScAddress(5,1,0), "SUM(A2:B2)"))
1556 CPPUNIT_FAIL("Wrong formula!");
1557 if (!checkFormula(*m_pDoc, ScAddress(6,0,0), "SUM($A$1:$B$1)"))
1558 CPPUNIT_FAIL("Wrong formula!");
1559 if (!checkFormula(*m_pDoc, ScAddress(6,1,0), "SUM($A$2:$B$2)"))
1560 CPPUNIT_FAIL("Wrong formula!");
1561
1562 // Enter new value and check that it is broadcasted. First in empty cell.
1563 m_pDoc->SetString(1,1,0, "16");
1564 double nVal = m_pDoc->GetValue(3,1,0);
1565 ASSERT_DOUBLES_EQUAL( 16.0, nVal);
1566 nVal = m_pDoc->GetValue(4,1,0);
1567 ASSERT_DOUBLES_EQUAL( 16.0, nVal);
1568 nVal = m_pDoc->GetValue(5,1,0);
1569 ASSERT_DOUBLES_EQUAL( 17.0, nVal);
1570 nVal = m_pDoc->GetValue(6,1,0);
1571 ASSERT_DOUBLES_EQUAL( 17.0, nVal);
1572
1573 // Enter new value and check that it is broadcasted. Now overwriting data.
1574 m_pDoc->SetString(1,0,0, "32");
1575 nVal = m_pDoc->GetValue(3,0,0);
1576 ASSERT_DOUBLES_EQUAL( 32.0, nVal);
1577 nVal = m_pDoc->GetValue(4,0,0);
1578 ASSERT_DOUBLES_EQUAL( 32.0, nVal);
1579 nVal = m_pDoc->GetValue(5,0,0);
1580 ASSERT_DOUBLES_EQUAL( 34.0, nVal);
1581 nVal = m_pDoc->GetValue(6,0,0);
1582 ASSERT_DOUBLES_EQUAL( 34.0, nVal);
1583 }
1584
1585 // The same for sort by column. Start data at A5.
1586
1587 {
1588 const char* aData[][2] = {
1589 { "1", "2" },
1590 { 0, "8" },
1591 { 0, 0 },
1592 { "=A6", "=B6" },
1593 { "=$A$6", "=$B$6" },
1594 { "=SUM(A5:A6)", "=SUM(B5:B6)" },
1595 { "=SUM($A$5:$A$6)", "=SUM($B$5:$B$6)" },
1596 };
1597
1598 ScAddress aPos(0,4,0);
1599 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1600 CPPUNIT_ASSERT(aDataRange.aStart == aPos);
1601
1602 {
1603 // Expected output table content. 0 = empty cell
1604 const char* aOutputCheck[][2] = {
1605 { "1", "2" },
1606 { 0, "8" },
1607 { 0, 0 },
1608 { "0", "8" },
1609 { "0", "8" },
1610 { "1", "10" },
1611 { "1", "10" },
1612 };
1613
1614 bool bSuccess = checkOutput<2>(m_pDoc, aDataRange, aOutputCheck, "Initial value");
1615 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1616 }
1617
1618 // Sort A5:B6.
1619 m_pDoc->SetAnonymousDBData(
1620 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 4, 1, 5));
1621
1622 ScDBDocFunc aFunc(getDocShell());
1623
1624 // Sort A5:B6 by row 5 descending.
1625 ScSortParam aSortData;
1626 aSortData.nCol1 = 0;
1627 aSortData.nCol2 = 1;
1628 aSortData.nRow1 = 4;
1629 aSortData.nRow2 = 5;
1630 aSortData.bHasHeader = false;
1631 aSortData.bByRow = false;
1632 aSortData.maKeyState[0].bDoSort = true;
1633 aSortData.maKeyState[0].nField = 0;
1634 aSortData.maKeyState[0].bAscending = false;
1635 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1636 CPPUNIT_ASSERT(bSorted);
1637
1638 {
1639 // Expected output table content. 0 = empty cell
1640 const char* aOutputCheck[][2] = {
1641 { "2", "1" },
1642 { "8", 0 },
1643 { 0, 0 },
1644 { "8", "0" },
1645 { "8", "0" },
1646 { "10", "1" },
1647 { "10", "1" },
1648 };
1649
1650 bool bSuccess = checkOutput<2>(m_pDoc, aDataRange, aOutputCheck, "Sorted without reference update");
1651 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1652 }
1653
1654 // Make sure that the formulas in A8:B11 are not adjusted.
1655 if (!checkFormula(*m_pDoc, ScAddress(0,7,0), "A6"))
1656 CPPUNIT_FAIL("Wrong formula!");
1657 if (!checkFormula(*m_pDoc, ScAddress(1,7,0), "B6"))
1658 CPPUNIT_FAIL("Wrong formula!");
1659 if (!checkFormula(*m_pDoc, ScAddress(0,8,0), "$A$6"))
1660 CPPUNIT_FAIL("Wrong formula!");
1661 if (!checkFormula(*m_pDoc, ScAddress(1,8,0), "$B$6"))
1662 CPPUNIT_FAIL("Wrong formula!");
1663 if (!checkFormula(*m_pDoc, ScAddress(0,9,0), "SUM(A5:A6)"))
1664 CPPUNIT_FAIL("Wrong formula!");
1665 if (!checkFormula(*m_pDoc, ScAddress(1,9,0), "SUM(B5:B6)"))
1666 CPPUNIT_FAIL("Wrong formula!");
1667 if (!checkFormula(*m_pDoc, ScAddress(0,10,0), "SUM($A$5:$A$6)"))
1668 CPPUNIT_FAIL("Wrong formula!");
1669 if (!checkFormula(*m_pDoc, ScAddress(1,10,0), "SUM($B$5:$B$6)"))
1670 CPPUNIT_FAIL("Wrong formula!");
1671
1672 // Enter new value and check that it is broadcasted. First in empty cell.
1673 m_pDoc->SetString(1,5,0, "16");
1674 double nVal = m_pDoc->GetValue(1,7,0);
1675 ASSERT_DOUBLES_EQUAL(nVal, 16.0);
1676 nVal = m_pDoc->GetValue(1,8,0);
1677 ASSERT_DOUBLES_EQUAL(nVal, 16.0);
1678 nVal = m_pDoc->GetValue(1,9,0);
1679 ASSERT_DOUBLES_EQUAL(nVal, 17.0);
1680 nVal = m_pDoc->GetValue(1,10,0);
1681 ASSERT_DOUBLES_EQUAL(nVal, 17.0);
1682
1683 // Enter new value and check that it is broadcasted. Now overwriting data.
1684 m_pDoc->SetString(0,5,0, "32");
1685 nVal = m_pDoc->GetValue(0,7,0);
1686 ASSERT_DOUBLES_EQUAL(nVal, 32.0);
1687 nVal = m_pDoc->GetValue(0,8,0);
1688 ASSERT_DOUBLES_EQUAL(nVal, 32.0);
1689 nVal = m_pDoc->GetValue(0,9,0);
1690 ASSERT_DOUBLES_EQUAL(nVal, 34.0);
1691 nVal = m_pDoc->GetValue(0,10,0);
1692 ASSERT_DOUBLES_EQUAL(nVal, 34.0);
1693 }
1694
1695 m_pDoc->DeleteTab(0);
1696 }
1697
1698 void Test::testSortOutOfPlaceResult()
1699 {
1700 m_pDoc->InsertTab(0, "Sort");
1701 m_pDoc->InsertTab(1, "Result");
1702
1703 const char* pData[] = {
1704 "Header",
1705 "1",
1706 "23",
1707 "2",
1708 "9",
1709 "-2",
1710 0 // terminator
1711 };
1712
1713 // source data in A1:A6.
1714 for (SCROW i = 0; pData[i]; ++i)
1715 m_pDoc->SetString(ScAddress(0,i,0), OUString::createFromAscii(pData[i]));
1716
1717 // Check the initial values.
1718 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
1719 CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1720 CPPUNIT_ASSERT_EQUAL(23.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1721 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1722 CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1723 CPPUNIT_ASSERT_EQUAL(-2.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1724
1725 ScDBDocFunc aFunc(getDocShell());
1726
1727 // Sort A1:A6, and set the result to C2:C7
1728 m_pDoc->SetAnonymousDBData(
1729 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 0, 5));
1730
1731 ScSortParam aSortData;
1732 aSortData.nCol1 = 0;
1733 aSortData.nCol2 = 0;
1734 aSortData.nRow1 = 0;
1735 aSortData.nRow2 = 5;
1736 aSortData.bHasHeader = true;
1737 aSortData.bInplace = false;
1738 aSortData.nDestTab = 1;
1739 aSortData.nDestCol = 2;
1740 aSortData.nDestRow = 1;
1741 aSortData.maKeyState[0].bDoSort = true;
1742 aSortData.maKeyState[0].nField = 0;
1743 aSortData.maKeyState[0].bAscending = true;
1744 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1745 CPPUNIT_ASSERT(bSorted);
1746
1747 // Source data still intact.
1748 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
1749 CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1750 CPPUNIT_ASSERT_EQUAL(23.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1751 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1752 CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1753 CPPUNIT_ASSERT_EQUAL(-2.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1754
1755 // Sort result in C2:C7 on sheet "Result".
1756 CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(2,1,1)));
1757 CPPUNIT_ASSERT_EQUAL(-2.0, m_pDoc->GetValue(ScAddress(2,2,1)));
1758 CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(2,3,1)));
1759 CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(2,4,1)));
1760 CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(2,5,1)));
1761 CPPUNIT_ASSERT_EQUAL(23.0, m_pDoc->GetValue(ScAddress(2,6,1)));
1762
1763 m_pDoc->DeleteTab(1);
1764 m_pDoc->DeleteTab(0);
1765 }
1766
1767 void Test::testSortPartialFormulaGroup()
1768 {
1769 SortRefUpdateSetter aUpdateSet;
1770
1771 sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1772 FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1773
1774 m_pDoc->InsertTab(0, "Sort");
1775
1776 // Set up the sheet.
1777 const char* aData[][2] = {
1778 { "F1", "F2" },
1779 { "43", "=RC[-1]" },
1780 { "50", "=RC[-1]" },
1781 { "8", "=RC[-1]" },
1782 { "47", "=RC[-1]" },
1783 { "28", "=RC[-1]" },
1784 { 0, 0 } // terminator
1785 };
1786
1787 // A1:B6.
1788 for (SCROW i = 0; aData[i][0]; ++i)
1789 {
1790 m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
1791 m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
1792 }
1793
1794 // Check the initial condition.
1795 for (SCROW i = 1; i <= 5; ++i)
1796 // A2:A6 should equal B2:B6.
1797 CPPUNIT_ASSERT_EQUAL(m_pDoc->GetValue(ScAddress(0,i,0)), m_pDoc->GetValue(ScAddress(1,i,0)));
1798
1799 const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
1800 CPPUNIT_ASSERT(pFC);
1801 CPPUNIT_ASSERT_MESSAGE("This formula cell should be the first in a group.", pFC->IsSharedTop());
1802 CPPUNIT_ASSERT_MESSAGE("Incorrect formula group length.", pFC->GetSharedLength() == 5);
1803
1804 ScDBDocFunc aFunc(getDocShell());
1805
1806 // Sort only B2:B4. This caused crash at one point (c.f. fdo#81617).
1807
1808 m_pDoc->SetAnonymousDBData(0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 1, 1, 1, 3));
1809
1810 ScSortParam aSortData;
1811 aSortData.nCol1 = 1;
1812 aSortData.nCol2 = 1;
1813 aSortData.nRow1 = 1;
1814 aSortData.nRow2 = 3;
1815 aSortData.bHasHeader = false;
1816 aSortData.bInplace = true;
1817 aSortData.maKeyState[0].bDoSort = true;
1818 aSortData.maKeyState[0].nField = 0;
1819 aSortData.maKeyState[0].bAscending = true;
1820 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1821 CPPUNIT_ASSERT(bSorted);
1822
1823 m_pDoc->CalcAll(); // just in case...
1824
1825 // Check the cell values after the partial sort.
1826
1827 // Column A
1828 CPPUNIT_ASSERT_EQUAL(43.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1829 CPPUNIT_ASSERT_EQUAL(50.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1830 CPPUNIT_ASSERT_EQUAL( 8.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1831 CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1832 CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1833
1834 // Column B
1835 CPPUNIT_ASSERT_EQUAL( 8.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1836 CPPUNIT_ASSERT_EQUAL(43.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1837 CPPUNIT_ASSERT_EQUAL(50.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1838 CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1839 CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(1,5,0)));
1840
1841 m_pDoc->DeleteTab(0);
1842 }
1843
1844 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
FREE OFFICE SUITE