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