Line data Source code
1 : /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 : /*
3 : * This file is part of the LibreOffice project.
4 : *
5 : * This Source Code Form is subject to the terms of the Mozilla Public
6 : * License, v. 2.0. If a copy of the MPL was not distributed with this
7 : * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8 : */
9 :
10 : #include "ucalc.hxx"
11 : #include "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 2 : void Test::testSort()
31 : {
32 2 : m_pDoc->InsertTab(0, "test1");
33 :
34 2 : ScRange aDataRange;
35 2 : ScAddress aPos(0,0,0);
36 : {
37 : const char* aData[][2] = {
38 : { "2", "4" },
39 : { "4", "1" },
40 : { "1", "2" },
41 : { "1", "23" },
42 2 : };
43 :
44 2 : clearRange(m_pDoc, ScRange(0, 0, 0, 1, SAL_N_ELEMENTS(aData), 0));
45 2 : aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
46 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
47 : }
48 :
49 : // Insert note in cell B2.
50 2 : ScAddress rAddr(1, 1, 0);
51 2 : ScPostIt* pNote = m_pDoc->GetOrCreateNote(rAddr);
52 2 : pNote->SetText(rAddr, "Hello");
53 2 : pNote->SetAuthor("Jim Bob");
54 :
55 2 : ScSortParam aSortData;
56 2 : aSortData.nCol1 = 1;
57 2 : aSortData.nCol2 = 1;
58 2 : aSortData.nRow1 = 0;
59 2 : aSortData.nRow2 = 2;
60 2 : aSortData.maKeyState[0].bDoSort = true;
61 2 : aSortData.maKeyState[0].nField = 1;
62 2 : aSortData.maKeyState[0].bAscending = true;
63 :
64 2 : m_pDoc->Sort(0, aSortData, false, true, NULL, NULL);
65 :
66 2 : double nVal = m_pDoc->GetValue(1,0,0);
67 2 : ASSERT_DOUBLES_EQUAL(nVal, 1.0);
68 :
69 : // check that note is also moved after sorting
70 2 : pNote = m_pDoc->GetNote(1, 0, 0);
71 2 : CPPUNIT_ASSERT(pNote);
72 :
73 2 : 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 2 : };
86 :
87 2 : aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
88 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
89 : }
90 :
91 2 : aSortData.nCol1 = aDataRange.aStart.Col();
92 2 : aSortData.nCol2 = aDataRange.aEnd.Col();
93 2 : aSortData.nRow1 = aDataRange.aStart.Row();
94 2 : aSortData.nRow2 = aDataRange.aEnd.Row();
95 2 : aSortData.bHasHeader = true;
96 2 : aSortData.maKeyState[0].nField = 0;
97 2 : 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 2 : CPPUNIT_ASSERT_EQUAL(OUString("Title"), m_pDoc->GetString(aPos));
103 2 : aPos.IncRow();
104 2 : CPPUNIT_ASSERT_EQUAL(OUString("1"), m_pDoc->GetString(aPos));
105 2 : aPos.IncRow();
106 2 : CPPUNIT_ASSERT_EQUAL(OUString("9"), m_pDoc->GetString(aPos));
107 2 : aPos.IncRow();
108 2 : CPPUNIT_ASSERT_EQUAL(OUString("12"), m_pDoc->GetString(aPos));
109 2 : aPos.IncRow();
110 2 : CPPUNIT_ASSERT_EQUAL(OUString("123"), m_pDoc->GetString(aPos));
111 2 : aPos.IncRow();
112 2 : CPPUNIT_ASSERT_EQUAL(OUString("b"), m_pDoc->GetString(aPos));
113 2 : aPos.IncRow();
114 2 : CPPUNIT_ASSERT_EQUAL(CELLTYPE_NONE, m_pDoc->GetCellType(aPos));
115 :
116 2 : m_pDoc->DeleteTab(0);
117 2 : }
118 :
119 2 : void Test::testSortHorizontal()
120 : {
121 2 : SortRefUpdateSetter aUpdateSet;
122 :
123 4 : ScFormulaOptions aOptions;
124 2 : aOptions.SetFormulaSepArg(";");
125 2 : aOptions.SetFormulaSepArrayCol(";");
126 2 : aOptions.SetFormulaSepArrayRow("|");
127 2 : getDocShell().SetFormulaOptions(aOptions);
128 :
129 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
130 2 : 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 2 : };
141 :
142 : // Insert raw data into A1:D4.
143 2 : ScRange aDataRange = insertRangeData(m_pDoc, ScAddress(0,0,0), aData, SAL_N_ELEMENTS(aData));
144 2 : CPPUNIT_ASSERT_EQUAL(OUString("A1:D4"), aDataRange.Format(SCA_VALID));
145 :
146 : // Check the formula values.
147 2 : CPPUNIT_ASSERT_EQUAL(OUString("Yes-No"), m_pDoc->GetString(ScAddress(3,1,0)));
148 2 : CPPUNIT_ASSERT_EQUAL(OUString("No-No"), m_pDoc->GetString(ScAddress(3,2,0)));
149 2 : 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 2 : 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 3, 3));
154 :
155 : // Sort A1:D4 horizontally, ascending by row 1.
156 4 : ScDBDocFunc aFunc(getDocShell());
157 :
158 4 : ScSortParam aSortData;
159 2 : aSortData.nCol1 = 0;
160 2 : aSortData.nCol2 = 3;
161 2 : aSortData.nRow1 = 0;
162 2 : aSortData.nRow2 = 3;
163 2 : aSortData.bHasHeader = true;
164 2 : aSortData.bByRow = false; // Sort by column (in horizontal direction).
165 2 : aSortData.bIncludePattern = true;
166 2 : aSortData.maKeyState[0].bDoSort = true;
167 2 : aSortData.maKeyState[0].nField = 0;
168 2 : aSortData.maKeyState[0].bAscending = true;
169 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
170 2 : 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 2 : };
180 :
181 2 : bool bSuccess = checkOutput<4>(m_pDoc, aDataRange, aOutputCheck, "Sorted by column with formula");
182 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
183 : }
184 :
185 2 : if (!checkFormula(*m_pDoc, ScAddress(1,1,0), "CONCATENATE(C2;\"-\";D2)"))
186 0 : CPPUNIT_FAIL("Wrong formula!");
187 2 : if (!checkFormula(*m_pDoc, ScAddress(1,2,0), "CONCATENATE(C3;\"-\";D3)"))
188 0 : CPPUNIT_FAIL("Wrong formula!");
189 2 : if (!checkFormula(*m_pDoc, ScAddress(1,3,0), "CONCATENATE(C4;\"-\";D4)"))
190 0 : CPPUNIT_FAIL("Wrong formula!");
191 :
192 4 : m_pDoc->DeleteTab(0);
193 2 : }
194 :
195 2 : void Test::testSortHorizontalWholeColumn()
196 : {
197 2 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
198 2 : m_pDoc->InsertTab(0, "Sort");
199 :
200 : // 0 = empty cell
201 : const char* aData[][5] = {
202 : { "4", "2", "47", "a", "9" }
203 2 : };
204 :
205 : // Insert row data to C1:G1.
206 2 : ScRange aSortRange = insertRangeData(m_pDoc, ScAddress(2,0,0), aData, SAL_N_ELEMENTS(aData));
207 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(2,0,0)));
208 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3,0,0)));
209 2 : CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(4,0,0)));
210 2 : CPPUNIT_ASSERT_EQUAL(OUString("a"), m_pDoc->GetString(ScAddress(5,0,0)));
211 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(6,0,0)));
212 :
213 : // Extend the sort range to whole column.
214 2 : aSortRange.aEnd.SetRow(MAXROW);
215 :
216 2 : SCCOL nCol1 = aSortRange.aStart.Col();
217 2 : SCCOL nCol2 = aSortRange.aEnd.Col();
218 2 : SCROW nRow1 = aSortRange.aStart.Row();
219 2 : SCROW nRow2 = aSortRange.aEnd.Row();
220 :
221 : // Define C:G as sheet-local anonymous database range.
222 : m_pDoc->SetAnonymousDBData(
223 2 : 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 4 : ScDBDocFunc aFunc(getDocShell());
227 :
228 4 : ScSortParam aSortData;
229 2 : aSortData.nCol1 = nCol1;
230 2 : aSortData.nCol2 = nCol2;
231 2 : aSortData.nRow1 = nRow1;
232 2 : aSortData.nRow2 = nRow2;
233 2 : aSortData.bHasHeader = false;
234 2 : aSortData.bByRow = false; // Sort by column (in horizontal direction).
235 2 : aSortData.bIncludePattern = true;
236 2 : aSortData.maKeyState[0].bDoSort = true;
237 2 : aSortData.maKeyState[0].nField = 0;
238 2 : aSortData.maKeyState[0].bAscending = true;
239 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
240 2 : CPPUNIT_ASSERT(bSorted);
241 :
242 : // Check the sort result.
243 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
244 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(3,0,0)));
245 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
246 2 : CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(5,0,0)));
247 2 : CPPUNIT_ASSERT_EQUAL(OUString("a"), m_pDoc->GetString(ScAddress(6,0,0)));
248 :
249 : // Undo and check.
250 :
251 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
252 2 : CPPUNIT_ASSERT(pUndoMgr);
253 :
254 2 : pUndoMgr->Undo();
255 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(2,0,0)));
256 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3,0,0)));
257 2 : CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(4,0,0)));
258 2 : CPPUNIT_ASSERT_EQUAL(OUString("a"), m_pDoc->GetString(ScAddress(5,0,0)));
259 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(6,0,0)));
260 :
261 : // Redo and check.
262 2 : pUndoMgr->Redo();
263 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
264 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(3,0,0)));
265 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
266 2 : CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(5,0,0)));
267 2 : CPPUNIT_ASSERT_EQUAL(OUString("a"), m_pDoc->GetString(ScAddress(6,0,0)));
268 :
269 4 : m_pDoc->DeleteTab(0);
270 2 : }
271 :
272 2 : void Test::testSortSingleRow()
273 : {
274 : // This test case is from fdo#80462.
275 :
276 2 : m_pDoc->InsertTab(0, "Test");
277 :
278 : // Sort range consists of only one row.
279 2 : m_pDoc->SetString(ScAddress(0,0,0), "X");
280 2 : m_pDoc->SetString(ScAddress(1,0,0), "Y");
281 :
282 : // Define A1:B1 as sheet-local anonymous database range.
283 : m_pDoc->SetAnonymousDBData(
284 2 : 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 1, 0));
285 :
286 : // Sort A1:B1 horizontally, ascending by row 1.
287 2 : ScDBDocFunc aFunc(getDocShell());
288 :
289 4 : ScSortParam aSortData;
290 2 : aSortData.nCol1 = 0;
291 2 : aSortData.nCol2 = 1;
292 2 : aSortData.nRow1 = 0;
293 2 : aSortData.nRow2 = 0;
294 2 : aSortData.bHasHeader = true;
295 2 : aSortData.bByRow = true;
296 2 : aSortData.bIncludePattern = true;
297 2 : aSortData.maKeyState[0].bDoSort = true;
298 2 : aSortData.maKeyState[0].nField = 0;
299 2 : aSortData.maKeyState[0].bAscending = true;
300 :
301 : // Do the sorting. This should not crash.
302 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
303 2 : CPPUNIT_ASSERT(bSorted);
304 :
305 : // Another test case - single row horizontal sort with header column.
306 2 : clearSheet(m_pDoc, 0);
307 :
308 : // A1:G1
309 2 : m_pDoc->SetString(ScAddress(0,0,0), "Header");
310 2 : m_pDoc->SetValue(ScAddress(1,0,0), 1.0);
311 2 : m_pDoc->SetValue(ScAddress(2,0,0), 10.0);
312 2 : m_pDoc->SetValue(ScAddress(3,0,0), 3.0);
313 2 : m_pDoc->SetValue(ScAddress(4,0,0), 9.0);
314 2 : m_pDoc->SetValue(ScAddress(5,0,0), 12.0);
315 2 : 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 2 : 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 6, 0, false, true));
320 :
321 : // Update the sort data.
322 2 : aSortData.nCol1 = 0;
323 2 : aSortData.nCol2 = 6;
324 2 : aSortData.bByRow = false;
325 2 : bSorted = aFunc.Sort(0, aSortData, true, true, true);
326 2 : CPPUNIT_ASSERT(bSorted);
327 :
328 : // Check the result.
329 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
330 2 : CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
331 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
332 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,0,0)));
333 2 : CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
334 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(5,0,0)));
335 2 : CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(6,0,0)));
336 :
337 : // Undo and check.
338 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
339 2 : CPPUNIT_ASSERT(pUndoMgr);
340 2 : pUndoMgr->Undo();
341 :
342 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
343 2 : CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
344 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,0,0)));
345 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,0,0)));
346 2 : CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
347 2 : CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(5,0,0)));
348 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(6,0,0)));
349 :
350 : // Redo and check.
351 2 : pUndoMgr->Redo();
352 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
353 2 : CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
354 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
355 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,0,0)));
356 2 : CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
357 2 : CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(5,0,0)));
358 2 : CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(6,0,0)));
359 :
360 4 : m_pDoc->DeleteTab(0);
361 2 : }
362 :
363 : // regression test fo fdo#53814, sorting doens't work as expected
364 : // if cells in the sort are referenced by formulas
365 2 : void Test::testSortWithFormulaRefs()
366 : {
367 2 : SortRefUpdateSetter aUpdateSet;
368 :
369 2 : m_pDoc->InsertTab(0, "List1");
370 2 : 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 2 : };
380 :
381 : const char* aTextData[4] = {
382 : "bob",
383 : "tim",
384 : "brian",
385 : "larry",
386 2 : };
387 :
388 : const char* aResults[6] = {
389 : "bob",
390 : "brian",
391 : "larry",
392 : "tim",
393 : "",
394 : "",
395 2 : };
396 :
397 : // Insert data to sort in A2:A5 on the 1st sheet.
398 10 : for (SCROW i = 1; i <= 4; ++i)
399 8 : m_pDoc->SetString( 0, i, 0, OUString::createFromAscii(aTextData[i-1]) );
400 :
401 : // Insert forumulas in A1:A6 on the 2nd sheet.
402 14 : for (size_t i = 0; i < SAL_N_ELEMENTS(aFormulaData); ++i)
403 12 : 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 4 : ScSortParam aSortData;
407 2 : aSortData.nCol1 = 0;
408 2 : aSortData.nCol2 = 0;
409 2 : aSortData.nRow1 = 1;
410 2 : aSortData.nRow2 = 7;
411 2 : aSortData.maKeyState[0].bDoSort = true;
412 2 : aSortData.maKeyState[0].nField = 0;
413 :
414 2 : m_pDoc->Sort(0, aSortData, false, true, NULL, NULL);
415 :
416 14 : for (size_t i = 0; i < SAL_N_ELEMENTS(aResults); ++i)
417 : {
418 12 : OUString sResult = m_pDoc->GetString(0, i + 1, 0);
419 12 : CPPUNIT_ASSERT_EQUAL( OUString::createFromAscii( aResults[i] ), sResult );
420 12 : }
421 2 : m_pDoc->DeleteTab(1);
422 4 : m_pDoc->DeleteTab(0);
423 2 : }
424 :
425 2 : void Test::testSortWithStrings()
426 : {
427 2 : m_pDoc->InsertTab(0, "Test");
428 :
429 2 : ScFieldEditEngine& rEE = m_pDoc->GetEditEngine();
430 2 : rEE.SetText("Val1");
431 2 : m_pDoc->SetString(ScAddress(1,1,0), "Header");
432 2 : m_pDoc->SetString(ScAddress(1,2,0), "Val2");
433 2 : m_pDoc->SetEditText(ScAddress(1,3,0), rEE.CreateTextObject());
434 :
435 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(1,1,0)));
436 2 : CPPUNIT_ASSERT_EQUAL(OUString("Val2"), m_pDoc->GetString(ScAddress(1,2,0)));
437 2 : CPPUNIT_ASSERT_EQUAL(OUString("Val1"), m_pDoc->GetString(ScAddress(1,3,0)));
438 :
439 2 : ScSortParam aParam;
440 2 : aParam.nCol1 = 1;
441 2 : aParam.nCol2 = 1;
442 2 : aParam.nRow1 = 1;
443 2 : aParam.nRow2 = 3;
444 2 : aParam.bHasHeader = true;
445 2 : aParam.maKeyState[0].bDoSort = true;
446 2 : aParam.maKeyState[0].bAscending = true;
447 2 : aParam.maKeyState[0].nField = 1;
448 :
449 2 : m_pDoc->Sort(0, aParam, false, true, NULL, NULL);
450 :
451 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(1,1,0)));
452 2 : CPPUNIT_ASSERT_EQUAL(OUString("Val1"), m_pDoc->GetString(ScAddress(1,2,0)));
453 2 : CPPUNIT_ASSERT_EQUAL(OUString("Val2"), m_pDoc->GetString(ScAddress(1,3,0)));
454 :
455 2 : aParam.maKeyState[0].bAscending = false;
456 :
457 2 : m_pDoc->Sort(0, aParam, false, true, NULL, NULL);
458 :
459 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(1,1,0)));
460 2 : CPPUNIT_ASSERT_EQUAL(OUString("Val2"), m_pDoc->GetString(ScAddress(1,2,0)));
461 2 : CPPUNIT_ASSERT_EQUAL(OUString("Val1"), m_pDoc->GetString(ScAddress(1,3,0)));
462 :
463 2 : m_pDoc->DeleteTab(0);
464 2 : }
465 :
466 2 : void Test::testSortInFormulaGroup()
467 : {
468 2 : 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 2 : m_pDoc->InsertTab(0, "sorttest");
488 :
489 42 : for ( SCROW i = 0; i < (SCROW) SAL_N_ELEMENTS( aEntries ); ++i )
490 : m_pDoc->SetString( aEntries[i].nCol, aEntries[i].nRow, 0,
491 40 : OUString::createFromAscii( aEntries[i].pData) );
492 :
493 4 : ScSortParam aSortData;
494 2 : aSortData.nCol1 = 0;
495 2 : aSortData.nCol2 = 1;
496 2 : aSortData.nRow1 = 0;
497 2 : aSortData.nRow2 = 9;
498 2 : aSortData.maKeyState[0].bDoSort = true;
499 2 : aSortData.maKeyState[0].nField = 0;
500 2 : aSortData.maKeyState[0].bAscending = true;
501 :
502 2 : 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 42 : for ( SCROW i = 0; i < (SCROW) SAL_N_ELEMENTS( aEntries ); ++i )
522 : {
523 40 : 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 80 : CPPUNIT_ASSERT_MESSAGE("Mis-matching value after sort.",
528 40 : rtl::math::approxEqual(val, aResults[i].fValue));
529 : }
530 :
531 4 : m_pDoc->DeleteTab( 0 );
532 2 : }
533 :
534 2 : void Test::testSortWithCellFormats()
535 : {
536 : struct
537 : {
538 4 : bool isBold( const ScPatternAttr* pPat ) const
539 : {
540 4 : if (!pPat)
541 : {
542 0 : cerr << "Pattern is NULL!" << endl;
543 0 : return false;
544 : }
545 :
546 4 : const SfxPoolItem* pItem = NULL;
547 4 : if (!pPat->GetItemSet().HasItem(ATTR_FONT_WEIGHT, &pItem))
548 : {
549 0 : cerr << "Pattern does not have a font weight item, but it should." << endl;
550 0 : return false;
551 : }
552 :
553 4 : CPPUNIT_ASSERT(pItem);
554 :
555 4 : if (static_cast<const SvxWeightItem*>(pItem)->GetEnumValue() != WEIGHT_BOLD)
556 : {
557 0 : cerr << "Font weight should be bold." << endl;
558 0 : return false;
559 : }
560 :
561 4 : return true;
562 : }
563 :
564 4 : bool isItalic( const ScPatternAttr* pPat ) const
565 : {
566 4 : if (!pPat)
567 : {
568 0 : cerr << "Pattern is NULL!" << endl;
569 0 : return false;
570 : }
571 :
572 4 : const SfxPoolItem* pItem = NULL;
573 4 : if (!pPat->GetItemSet().HasItem(ATTR_FONT_POSTURE, &pItem))
574 : {
575 0 : cerr << "Pattern does not have a font posture item, but it should." << endl;
576 0 : return false;
577 : }
578 :
579 4 : CPPUNIT_ASSERT(pItem);
580 :
581 4 : if (static_cast<const SvxPostureItem*>(pItem)->GetEnumValue() != ITALIC_NORMAL)
582 : {
583 0 : cerr << "Italic should be applied.." << endl;
584 0 : return false;
585 : }
586 :
587 4 : return true;
588 : }
589 :
590 2 : bool isNormal( const ScPatternAttr* pPat ) const
591 : {
592 2 : if (!pPat)
593 : {
594 0 : cerr << "Pattern is NULL!" << endl;
595 0 : return false;
596 : }
597 :
598 2 : const SfxPoolItem* pItem = NULL;
599 2 : if (pPat->GetItemSet().HasItem(ATTR_FONT_WEIGHT, &pItem))
600 : {
601 : // Check if the font weight is applied.
602 0 : if (static_cast<const SvxWeightItem*>(pItem)->GetEnumValue() == WEIGHT_BOLD)
603 : {
604 0 : cerr << "This cell is bold, but shouldn't." << endl;
605 0 : return false;
606 : }
607 : }
608 :
609 2 : if (pPat->GetItemSet().HasItem(ATTR_FONT_POSTURE, &pItem))
610 : {
611 : // Check if the italics is applied.
612 0 : if (static_cast<const SvxPostureItem*>(pItem)->GetEnumValue() == ITALIC_NORMAL)
613 : {
614 0 : cerr << "This cell is bold, but shouldn't." << endl;
615 0 : return false;
616 : }
617 : }
618 :
619 2 : return true;
620 : }
621 :
622 : } aCheck;
623 :
624 2 : m_pDoc->InsertTab(0, "Test");
625 :
626 : // Insert some values into A1:A4.
627 2 : m_pDoc->SetString(ScAddress(0,0,0), "Header");
628 2 : m_pDoc->SetString(ScAddress(0,1,0), "Normal");
629 2 : m_pDoc->SetString(ScAddress(0,2,0), "Bold");
630 2 : m_pDoc->SetString(ScAddress(0,3,0), "Italic");
631 :
632 : // Set A3 bold and A4 italic.
633 2 : const ScPatternAttr* pPat = m_pDoc->GetPattern(ScAddress(0,2,0));
634 2 : CPPUNIT_ASSERT(pPat);
635 : {
636 2 : ScPatternAttr aNewPat(*pPat);
637 2 : SfxItemSet& rSet = aNewPat.GetItemSet();
638 2 : rSet.Put(SvxWeightItem(WEIGHT_BOLD, ATTR_FONT_WEIGHT));
639 2 : m_pDoc->ApplyPattern(0, 2, 0, aNewPat);
640 :
641 : // Make sure it's really in.
642 2 : bool bGood = aCheck.isBold(m_pDoc->GetPattern(ScAddress(0,2,0)));
643 2 : CPPUNIT_ASSERT_MESSAGE("A3 is not bold but it should.", bGood);
644 : }
645 :
646 2 : pPat = m_pDoc->GetPattern(ScAddress(0,3,0));
647 2 : CPPUNIT_ASSERT(pPat);
648 : {
649 2 : ScPatternAttr aNewPat(*pPat);
650 2 : SfxItemSet& rSet = aNewPat.GetItemSet();
651 2 : rSet.Put(SvxPostureItem(ITALIC_NORMAL, ATTR_FONT_POSTURE));
652 2 : m_pDoc->ApplyPattern(0, 3, 0, aNewPat);
653 :
654 2 : bool bGood = aCheck.isItalic(m_pDoc->GetPattern(ScAddress(0,3,0)));
655 2 : 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 2 : 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 0, 3));
661 :
662 : // Sort A1:A4 ascending with cell formats.
663 2 : ScDBDocFunc aFunc(getDocShell());
664 :
665 4 : ScSortParam aSortData;
666 2 : aSortData.nCol1 = 0;
667 2 : aSortData.nCol2 = 0;
668 2 : aSortData.nRow1 = 0;
669 2 : aSortData.nRow2 = 3;
670 2 : aSortData.bHasHeader = true;
671 2 : aSortData.bIncludePattern = true;
672 2 : aSortData.maKeyState[0].bDoSort = true;
673 2 : aSortData.maKeyState[0].nField = 0;
674 2 : aSortData.maKeyState[0].bAscending = true;
675 2 : bool bSorted = aFunc.Sort(0, aSortData, true, false, true);
676 2 : CPPUNIT_ASSERT(bSorted);
677 :
678 : // Check the sort result.
679 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
680 2 : CPPUNIT_ASSERT_EQUAL(OUString("Bold"), m_pDoc->GetString(ScAddress(0,1,0)));
681 2 : CPPUNIT_ASSERT_EQUAL(OUString("Italic"), m_pDoc->GetString(ScAddress(0,2,0)));
682 2 : CPPUNIT_ASSERT_EQUAL(OUString("Normal"), m_pDoc->GetString(ScAddress(0,3,0)));
683 :
684 : // A2 should be bold now.
685 2 : bool bBold = aCheck.isBold(m_pDoc->GetPattern(ScAddress(0,1,0)));
686 2 : CPPUNIT_ASSERT_MESSAGE("A2 should be bold after the sort.", bBold);
687 :
688 : // and A3 should be italic.
689 2 : bool bItalic = aCheck.isItalic(m_pDoc->GetPattern(ScAddress(0,2,0)));
690 2 : CPPUNIT_ASSERT_MESSAGE("A3 should be italic.", bItalic);
691 :
692 : // A4 should have neither bold nor italic.
693 2 : bool bNormal = aCheck.isNormal(m_pDoc->GetPattern(ScAddress(0,3,0)));
694 2 : CPPUNIT_ASSERT_MESSAGE("A4 should be neither bold nor italic.", bNormal);
695 :
696 4 : m_pDoc->DeleteTab(0);
697 2 : }
698 :
699 2 : void Test::testSortRefUpdate()
700 : {
701 2 : SortTypeSetter aSortTypeSet(true);
702 :
703 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
704 4 : FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
705 :
706 2 : m_pDoc->InsertTab(0, "Sort");
707 :
708 : // Set values to sort in column A.
709 2 : m_pDoc->SetString(ScAddress(0,0,0), "Header");
710 :
711 2 : double aValues[] = { 4.0, 36.0, 14.0, 29.0, 98.0, 78.0, 0.0, 99.0, 1.0 };
712 2 : size_t nCount = SAL_N_ELEMENTS(aValues);
713 20 : for (size_t i = 0; i < nCount; ++i)
714 18 : m_pDoc->SetValue(ScAddress(0,i+1,0), aValues[i]);
715 :
716 : // Set formulas to reference these values in column C.
717 2 : m_pDoc->SetString(ScAddress(2,0,0), "Formula");
718 20 : for (size_t i = 0; i < nCount; ++i)
719 18 : m_pDoc->SetString(ScAddress(2,1+i,0), "=RC[-2]");
720 :
721 : // Check the values in column C.
722 20 : for (size_t i = 0; i < nCount; ++i)
723 : {
724 18 : double fCheck = aValues[i];
725 18 : CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
726 : }
727 :
728 4 : ScDBDocFunc aFunc(getDocShell());
729 :
730 : // Define A1:A10 as sheet-local anonymous database range, else sort wouldn't run.
731 : m_pDoc->SetAnonymousDBData(
732 2 : 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 0, 9));
733 :
734 : // Sort A1:A10 (with a header row).
735 4 : ScSortParam aSortData;
736 2 : aSortData.nCol1 = 0;
737 2 : aSortData.nCol2 = 0;
738 2 : aSortData.nRow1 = 0;
739 2 : aSortData.nRow2 = 9;
740 2 : aSortData.bHasHeader = true;
741 2 : aSortData.maKeyState[0].bDoSort = true;
742 2 : aSortData.maKeyState[0].nField = 0;
743 2 : aSortData.maKeyState[0].bAscending = true;
744 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
745 2 : CPPUNIT_ASSERT(bSorted);
746 :
747 2 : 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 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
751 20 : for (size_t i = 0; i < nCount; ++i)
752 : {
753 18 : double fCheck = aSorted[i];
754 18 : 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 2 : m_pDoc->CalcAll(); // just in case...
759 20 : for (size_t i = 0; i < nCount; ++i)
760 : {
761 18 : double fCheck = aValues[i];
762 18 : CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
763 : }
764 :
765 : // C2 should now point to A4.
766 2 : if (!checkFormula(*m_pDoc, ScAddress(2,1,0), "R[2]C[-2]"))
767 0 : CPPUNIT_FAIL("Wrong formula in C2!");
768 :
769 : // Undo the sort.
770 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
771 2 : pUndoMgr->Undo();
772 :
773 : // Check the undo result.
774 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
775 20 : for (size_t i = 0; i < nCount; ++i)
776 : {
777 18 : double fCheck = aValues[i];
778 18 : CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(0,i+1,0)));
779 : }
780 :
781 : // Values in column C should still be unaltered.
782 2 : m_pDoc->CalcAll(); // just in case...
783 20 : for (size_t i = 0; i < nCount; ++i)
784 : {
785 18 : double fCheck = aValues[i];
786 18 : CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
787 : }
788 :
789 : // C2 should now point to A2.
790 2 : if (!checkFormula(*m_pDoc, ScAddress(2,1,0), "RC[-2]"))
791 0 : CPPUNIT_FAIL("Wrong formula in C2!");
792 :
793 : // Redo.
794 2 : pUndoMgr->Redo();
795 :
796 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
797 20 : for (size_t i = 0; i < nCount; ++i)
798 : {
799 18 : double fCheck = aSorted[i];
800 18 : 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 2 : m_pDoc->CalcAll(); // just in case...
805 20 : for (size_t i = 0; i < nCount; ++i)
806 : {
807 18 : double fCheck = aValues[i];
808 18 : CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
809 : }
810 :
811 : // C2 should now point to A4.
812 2 : if (!checkFormula(*m_pDoc, ScAddress(2,1,0), "R[2]C[-2]"))
813 0 : CPPUNIT_FAIL("Wrong formula in C2!");
814 :
815 : // Undo again.
816 2 : pUndoMgr->Undo();
817 :
818 : // Formulas in column C should all be "RC[-2]" again.
819 20 : for (size_t i = 0; i < nCount; ++i)
820 18 : m_pDoc->SetString(ScAddress(2,1+i,0), "=RC[-2]");
821 :
822 : // Turn off reference update on sort.
823 2 : aSortTypeSet.changeTo(false);
824 :
825 2 : bSorted = aFunc.Sort(0, aSortData, true, true, true);
826 2 : CPPUNIT_ASSERT(bSorted);
827 :
828 : // Check the sort result again.
829 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
830 20 : for (size_t i = 0; i < nCount; ++i)
831 : {
832 18 : double fCheck = aSorted[i];
833 18 : 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 20 : for (size_t i = 0; i < nCount; ++i)
838 18 : 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 2 : m_pDoc->CalcAll(); // just in case...
842 20 : for (size_t i = 0; i < nCount; ++i)
843 : {
844 18 : double fCheck = aSorted[i];
845 18 : CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0))); // column C
846 : }
847 :
848 4 : m_pDoc->DeleteTab(0);
849 2 : }
850 :
851 2 : void Test::testSortRefUpdate2()
852 : {
853 2 : SortRefUpdateSetter aUpdateSet;
854 :
855 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
856 4 : FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
857 :
858 2 : 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 2 : };
869 :
870 12 : for (SCROW i = 0; aData[i][0]; ++i)
871 : {
872 10 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
873 10 : m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
874 : }
875 :
876 : // Check the values in B2:B5.
877 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,1,0)));
878 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
879 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
880 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,4,0)));
881 :
882 4 : ScDBDocFunc aFunc(getDocShell());
883 :
884 : // Define A1:B5 as sheet-local anonymous database range, else sort wouldn't run.
885 : m_pDoc->SetAnonymousDBData(
886 2 : 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 4 : ScSortParam aSortData;
890 2 : aSortData.nCol1 = 0;
891 2 : aSortData.nCol2 = 1;
892 2 : aSortData.nRow1 = 0;
893 2 : aSortData.nRow2 = 4;
894 2 : aSortData.bHasHeader = true;
895 2 : aSortData.maKeyState[0].bDoSort = true;
896 2 : aSortData.maKeyState[0].nField = 0;
897 2 : aSortData.maKeyState[0].bAscending = true;
898 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
899 2 : CPPUNIT_ASSERT(bSorted);
900 :
901 : // Check the sort result in column A.
902 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,0)));
903 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(0,2,0)));
904 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,3,0)));
905 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(0,4,0)));
906 :
907 : // and column B.
908 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
909 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,2,0)));
910 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
911 2 : 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 10 : for (SCROW i = 1; i <= 4; ++i)
915 : {
916 8 : if (!checkFormula(*m_pDoc, ScAddress(1,i,0), "RC[-1]"))
917 0 : CPPUNIT_FAIL("Wrong formula!");
918 : }
919 :
920 : // Undo and check the result in column B.
921 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
922 2 : pUndoMgr->Undo();
923 :
924 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,1,0)));
925 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
926 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
927 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,4,0)));
928 :
929 : // and redo.
930 2 : pUndoMgr->Redo();
931 :
932 2 : CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
933 2 : CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,2,0)));
934 2 : CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
935 2 : CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,4,0)));
936 :
937 4 : m_pDoc->DeleteTab(0);
938 2 : }
939 :
940 2 : void Test::testSortRefUpdate3()
941 : {
942 2 : SortRefUpdateSetter aUpdateSet;
943 :
944 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
945 2 : 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 2 : };
956 :
957 14 : for (SCROW i = 0; pData[i]; ++i)
958 12 : m_pDoc->SetString(ScAddress(0,i,0), OUString::createFromAscii(pData[i]));
959 :
960 : // Check the initial values.
961 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
962 2 : CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
963 2 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,2,0)));
964 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
965 2 : CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,4,0)));
966 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,5,0)));
967 :
968 4 : ScDBDocFunc aFunc(getDocShell());
969 :
970 : // Sort A1:A6.
971 : m_pDoc->SetAnonymousDBData(
972 2 : 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 4 : ScSortParam aSortData;
976 2 : aSortData.nCol1 = 0;
977 2 : aSortData.nCol2 = 0;
978 2 : aSortData.nRow1 = 0;
979 2 : aSortData.nRow2 = 5;
980 2 : aSortData.bHasHeader = true;
981 2 : aSortData.maKeyState[0].bDoSort = true;
982 2 : aSortData.maKeyState[0].nField = 0;
983 2 : aSortData.maKeyState[0].bAscending = true;
984 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
985 2 : CPPUNIT_ASSERT(bSorted);
986 :
987 : // Check the sorted values.
988 2 : m_pDoc->CalcAll();
989 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
990 2 : CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
991 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,2,0)));
992 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,3,0)));
993 2 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,4,0)));
994 2 : CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,5,0)));
995 :
996 : // Make sure the formula cells have been adjusted correctly.
997 2 : if (!checkFormula(*m_pDoc, ScAddress(0,3,0), "A2+A3"))
998 0 : CPPUNIT_FAIL("Wrong formula in A4.");
999 2 : if (!checkFormula(*m_pDoc, ScAddress(0,4,0), "A2+10"))
1000 0 : CPPUNIT_FAIL("Wrong formula in A5.");
1001 2 : if (!checkFormula(*m_pDoc, ScAddress(0,5,0), "A3+10"))
1002 0 : CPPUNIT_FAIL("Wrong formula in A6.");
1003 :
1004 : // Undo and check the result.
1005 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1006 2 : pUndoMgr->Undo();
1007 2 : m_pDoc->CalcAll();
1008 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
1009 2 : CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1010 2 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1011 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1012 2 : CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1013 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1014 :
1015 : // Redo and check the result.
1016 2 : pUndoMgr->Redo();
1017 2 : m_pDoc->CalcAll();
1018 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
1019 2 : CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1020 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1021 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1022 2 : CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1023 2 : CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1024 :
1025 4 : m_pDoc->DeleteTab(0);
1026 2 : }
1027 :
1028 : // Derived from fdo#79441 https://bugs.freedesktop.org/attachment.cgi?id=100144
1029 : // testRefInterne.ods
1030 2 : void Test::testSortRefUpdate4()
1031 : {
1032 2 : SortRefUpdateSetter aUpdateSet;
1033 :
1034 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1035 2 : m_pDoc->InsertTab(0, "Sort");
1036 2 : m_pDoc->InsertTab(1, "Lesson1");
1037 2 : m_pDoc->InsertTab(2, "Lesson2");
1038 :
1039 2 : ScRange aLesson1Range;
1040 : {
1041 : const char* aData[][2] = {
1042 : { "Name", "Note" },
1043 : { "Student1", "1" },
1044 : { "Student2", "2" },
1045 : { "Student3", "3" },
1046 : { "Student4", "4" },
1047 : { "Student5", "5" },
1048 2 : };
1049 :
1050 2 : SCTAB nTab = 1;
1051 2 : ScAddress aPos(0,0,nTab);
1052 2 : clearRange(m_pDoc, ScRange(0, 0, nTab, 1, SAL_N_ELEMENTS(aData), nTab));
1053 2 : aLesson1Range = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1054 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aLesson1Range.aStart == aPos);
1055 : }
1056 :
1057 2 : ScRange aLesson2Range;
1058 : {
1059 : const char* aData[][2] = {
1060 : { "Name", "Note" },
1061 : { "=Lesson1.A2", "3" },
1062 : { "=Lesson1.A3", "4" },
1063 : { "=Lesson1.A4", "9" },
1064 : { "=Lesson1.A5", "6" },
1065 : { "=Lesson1.A6", "3" },
1066 2 : };
1067 :
1068 2 : SCTAB nTab = 2;
1069 2 : ScAddress aPos(0,0,nTab);
1070 2 : clearRange(m_pDoc, ScRange(0, 0, nTab, 1, SAL_N_ELEMENTS(aData), nTab));
1071 2 : aLesson2Range = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1072 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aLesson2Range.aStart == aPos);
1073 : }
1074 :
1075 2 : ScRange aSortRange;
1076 : {
1077 : const char* aData[][4] = {
1078 : { "Name", "Lesson1", "Lesson2", "Average" },
1079 : { "=Lesson1.A2", "=Lesson1.B2", "=Lesson2.B2", "=AVERAGE(B2:C2)" },
1080 : { "=Lesson1.A3", "=Lesson1.B3", "=Lesson2.B3", "=AVERAGE(B3:C3)" },
1081 : { "=Lesson1.A4", "=Lesson1.B4", "=Lesson2.B4", "=AVERAGE(B4:C4)" },
1082 : { "=Lesson1.A5", "=Lesson1.B5", "=Lesson2.B5", "=AVERAGE(B5:C5)" },
1083 : { "=Lesson1.A6", "=Lesson1.B6", "=Lesson2.B6", "=AVERAGE(B6:C6)" },
1084 2 : };
1085 :
1086 2 : SCTAB nTab = 0;
1087 2 : ScAddress aPos(0,0,nTab);
1088 2 : clearRange(m_pDoc, ScRange(0, 0, nTab, 1, SAL_N_ELEMENTS(aData), nTab));
1089 2 : aSortRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1090 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aSortRange.aStart == aPos);
1091 : }
1092 :
1093 4 : ScDBDocFunc aFunc(getDocShell());
1094 :
1095 : // Sort A1:D6 by column D (Average, with a row header).
1096 : {
1097 2 : ScSortParam aSortData;
1098 2 : aSortData.nCol1 = aSortRange.aStart.Col();
1099 2 : aSortData.nCol2 = aSortRange.aEnd.Col();
1100 2 : aSortData.nRow1 = aSortRange.aStart.Row();
1101 2 : aSortData.nRow2 = aSortRange.aEnd.Row();
1102 2 : aSortData.bHasHeader = true;
1103 2 : aSortData.maKeyState[0].bDoSort = true; // sort on
1104 2 : aSortData.maKeyState[0].nField = 3; // Average
1105 2 : aSortData.maKeyState[0].bAscending = false; // descending
1106 :
1107 2 : m_pDoc->SetAnonymousDBData( 0, new ScDBData( STR_DB_LOCAL_NONAME, aSortRange.aStart.Tab(),
1108 4 : aSortData.nCol1, aSortData.nRow1, aSortData.nCol2, aSortData.nRow2));
1109 :
1110 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1111 2 : CPPUNIT_ASSERT(bSorted);
1112 :
1113 : // Check the sorted values.
1114 2 : m_pDoc->CalcAll();
1115 2 : CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
1116 2 : CPPUNIT_ASSERT_EQUAL(OUString("Student3"), m_pDoc->GetString(ScAddress(0,1,0)));
1117 2 : CPPUNIT_ASSERT_EQUAL(OUString("Student4"), m_pDoc->GetString(ScAddress(0,2,0)));
1118 2 : CPPUNIT_ASSERT_EQUAL(OUString("Student5"), m_pDoc->GetString(ScAddress(0,3,0)));
1119 2 : CPPUNIT_ASSERT_EQUAL(OUString("Student2"), m_pDoc->GetString(ScAddress(0,4,0)));
1120 2 : CPPUNIT_ASSERT_EQUAL(OUString("Student1"), m_pDoc->GetString(ScAddress(0,5,0)));
1121 2 : CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,1,0)));
1122 2 : CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,2,0)));
1123 2 : CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,3,0)));
1124 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,4,0)));
1125 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,5,0)));
1126 :
1127 : // Make sure the formula cells have been adjusted correctly.
1128 : const char* aCheck[][4] = {
1129 : // Name Lesson1 Lesson2 Average
1130 : { "Lesson1.A4", "Lesson1.B4", "Lesson2.B4", "AVERAGE(B2:C2)" },
1131 : { "Lesson1.A5", "Lesson1.B5", "Lesson2.B5", "AVERAGE(B3:C3)" },
1132 : { "Lesson1.A6", "Lesson1.B6", "Lesson2.B6", "AVERAGE(B4:C4)" },
1133 : { "Lesson1.A3", "Lesson1.B3", "Lesson2.B3", "AVERAGE(B5:C5)" },
1134 : { "Lesson1.A2", "Lesson1.B2", "Lesson2.B2", "AVERAGE(B6:C6)" },
1135 2 : };
1136 12 : for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aCheck)); ++nRow)
1137 : {
1138 50 : for (SCCOL nCol=0; nCol < 4; ++nCol)
1139 : {
1140 40 : if (!checkFormula(*m_pDoc, ScAddress(nCol,nRow+1,0), aCheck[nRow][nCol]))
1141 0 : CPPUNIT_FAIL(OString("Wrong formula in " + OString('A'+nCol) + OString::number(nRow+2) + ".").getStr());
1142 : }
1143 : }
1144 :
1145 : // Undo and check the result.
1146 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1147 2 : pUndoMgr->Undo();
1148 2 : m_pDoc->CalcAll();
1149 2 : CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
1150 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,1,0)));
1151 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,2,0)));
1152 2 : CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,3,0)));
1153 2 : CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,4,0)));
1154 2 : CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,5,0)));
1155 :
1156 : // Redo and check the result.
1157 2 : pUndoMgr->Redo();
1158 2 : m_pDoc->CalcAll();
1159 2 : CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
1160 2 : CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,1,0)));
1161 2 : CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,2,0)));
1162 2 : CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,3,0)));
1163 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,4,0)));
1164 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,5,0)));
1165 : }
1166 :
1167 : // Sort A2:AMJ6 by column A (Name, without header).
1168 : {
1169 2 : ScSortParam aSortData;
1170 2 : aSortData.nCol1 = 0;
1171 2 : aSortData.nCol2 = MAXCOL;
1172 2 : aSortData.nRow1 = aSortRange.aStart.Row()+1;
1173 2 : aSortData.nRow2 = aSortRange.aEnd.Row();
1174 2 : aSortData.bHasHeader = false;
1175 2 : aSortData.maKeyState[0].bDoSort = true; // sort on
1176 2 : aSortData.maKeyState[0].nField = 0; // Name
1177 2 : aSortData.maKeyState[0].bAscending = false; // descending
1178 :
1179 2 : m_pDoc->SetAnonymousDBData( 0, new ScDBData( STR_DB_LOCAL_NONAME, aSortRange.aStart.Tab(),
1180 4 : aSortData.nCol1, aSortData.nRow1, aSortData.nCol2, aSortData.nRow2));
1181 :
1182 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1183 2 : CPPUNIT_ASSERT(bSorted);
1184 :
1185 : // Check the sorted values.
1186 2 : m_pDoc->CalcAll();
1187 2 : CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
1188 2 : CPPUNIT_ASSERT_EQUAL(OUString("Student5"), m_pDoc->GetString(ScAddress(0,1,0)));
1189 2 : CPPUNIT_ASSERT_EQUAL(OUString("Student4"), m_pDoc->GetString(ScAddress(0,2,0)));
1190 2 : CPPUNIT_ASSERT_EQUAL(OUString("Student3"), m_pDoc->GetString(ScAddress(0,3,0)));
1191 2 : CPPUNIT_ASSERT_EQUAL(OUString("Student2"), m_pDoc->GetString(ScAddress(0,4,0)));
1192 2 : CPPUNIT_ASSERT_EQUAL(OUString("Student1"), m_pDoc->GetString(ScAddress(0,5,0)));
1193 2 : CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,1,0)));
1194 2 : CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,2,0)));
1195 2 : CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,3,0)));
1196 2 : CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,4,0)));
1197 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,5,0)));
1198 :
1199 : // Make sure the formula cells have been adjusted correctly.
1200 : const char* aCheck[][4] = {
1201 : // Name Lesson1 Lesson2 Average
1202 : { "Lesson1.A6", "Lesson1.B6", "Lesson2.B6", "AVERAGE(B2:C2)" },
1203 : { "Lesson1.A5", "Lesson1.B5", "Lesson2.B5", "AVERAGE(B3:C3)" },
1204 : { "Lesson1.A4", "Lesson1.B4", "Lesson2.B4", "AVERAGE(B4:C4)" },
1205 : { "Lesson1.A3", "Lesson1.B3", "Lesson2.B3", "AVERAGE(B5:C5)" },
1206 : { "Lesson1.A2", "Lesson1.B2", "Lesson2.B2", "AVERAGE(B6:C6)" },
1207 2 : };
1208 12 : for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aCheck)); ++nRow)
1209 : {
1210 50 : for (SCCOL nCol=0; nCol < 4; ++nCol)
1211 : {
1212 40 : if (!checkFormula(*m_pDoc, ScAddress(nCol,nRow+1,0), aCheck[nRow][nCol]))
1213 0 : CPPUNIT_FAIL(OString("Wrong formula in " + OString('A'+nCol) + OString::number(nRow+2) + ".").getStr());
1214 : }
1215 2 : }
1216 : }
1217 :
1218 2 : m_pDoc->DeleteTab(2);
1219 2 : m_pDoc->DeleteTab(1);
1220 4 : m_pDoc->DeleteTab(0);
1221 2 : }
1222 :
1223 : // Make sure the refupdate works also with volatile cells, see fdo#83067
1224 : /* FIXME: this test is not roll-over-midnight safe and will fail then! We may
1225 : * want to have something different, but due to the nature of volatile
1226 : * functions it's not that easy to come up with something reproducible staying
1227 : * stable over sorts.. ;-) Check for time and don't run test a few seconds
1228 : * before midnight, ermm.. */
1229 2 : void Test::testSortRefUpdate5()
1230 : {
1231 2 : SortRefUpdateSetter aUpdateSet;
1232 :
1233 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1234 2 : m_pDoc->InsertTab(0, "Sort");
1235 :
1236 : double aValCheck[][3] = {
1237 : // Result, Unsorted order, Sorted result.
1238 : { 0, 4, 0 },
1239 : { 0, 1, 0 },
1240 : { 0, 3, 0 },
1241 : { 0, 2, 0 },
1242 2 : };
1243 2 : ScRange aSortRange;
1244 : {
1245 : const char* aData[][3] = {
1246 : { "Date", "Volatile", "Order" },
1247 : { "1999-05-05", "=TODAY()-$A2", "4" },
1248 : { "1994-10-18", "=TODAY()-$A3", "1" },
1249 : { "1996-06-30", "=TODAY()-$A4", "3" },
1250 : { "1995-11-21", "=TODAY()-$A5", "2" },
1251 2 : };
1252 :
1253 2 : SCTAB nTab = 0;
1254 2 : ScAddress aPos(0,0,nTab);
1255 2 : clearRange(m_pDoc, ScRange(0, 0, nTab, 2, SAL_N_ELEMENTS(aData), nTab));
1256 2 : aSortRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1257 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aSortRange.aStart == aPos);
1258 :
1259 : // Actual results and expected sorted results.
1260 10 : for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
1261 : {
1262 8 : double fVal = m_pDoc->GetValue(ScAddress(1,nRow+1,0));
1263 8 : aValCheck[nRow][0] = fVal;
1264 8 : aValCheck[static_cast<size_t>(aValCheck[nRow][1])-1][2] = fVal;
1265 : }
1266 : }
1267 :
1268 4 : ScDBDocFunc aFunc(getDocShell());
1269 :
1270 : // Sort A1:B5.
1271 2 : m_pDoc->SetAnonymousDBData( 0, new ScDBData( STR_DB_LOCAL_NONAME, aSortRange.aStart.Tab(),
1272 4 : aSortRange.aStart.Col(), aSortRange.aStart.Row(), aSortRange.aEnd.Col(), aSortRange.aEnd.Row()));
1273 :
1274 : // Sort by column A.
1275 4 : ScSortParam aSortData;
1276 2 : aSortData.nCol1 = aSortRange.aStart.Col();
1277 2 : aSortData.nCol2 = aSortRange.aEnd.Col();
1278 2 : aSortData.nRow1 = aSortRange.aStart.Row();
1279 2 : aSortData.nRow2 = aSortRange.aEnd.Row();
1280 2 : aSortData.bHasHeader = true;
1281 2 : aSortData.maKeyState[0].bDoSort = true; // sort on
1282 2 : aSortData.maKeyState[0].nField = 0; // Date
1283 2 : aSortData.maKeyState[0].bAscending = true; // ascending
1284 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1285 2 : CPPUNIT_ASSERT(bSorted);
1286 :
1287 : // Check the sorted values.
1288 2 : m_pDoc->CalcAll();
1289 10 : for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
1290 : {
1291 8 : size_t i = static_cast<size_t>(m_pDoc->GetValue(ScAddress(2,nRow+1,0))); // order 1..4
1292 8 : CPPUNIT_ASSERT_EQUAL( static_cast<size_t>(nRow+1), i);
1293 8 : CPPUNIT_ASSERT_EQUAL( aValCheck[i-1][2], m_pDoc->GetValue(ScAddress(1,nRow+1,0)));
1294 : }
1295 :
1296 : // Make sure the formula cells have been adjusted correctly.
1297 : const char* aFormulaCheck[] = {
1298 : // Volatile
1299 : "TODAY()-$A2",
1300 : "TODAY()-$A3",
1301 : "TODAY()-$A4",
1302 : "TODAY()-$A5",
1303 2 : };
1304 10 : for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aFormulaCheck)); ++nRow)
1305 : {
1306 8 : if (!checkFormula(*m_pDoc, ScAddress(1,nRow+1,0), aFormulaCheck[nRow]))
1307 0 : CPPUNIT_FAIL(OString("Wrong formula in B" + OString::number(nRow+2) + ".").getStr());
1308 : }
1309 :
1310 : // Undo and check the result.
1311 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1312 2 : pUndoMgr->Undo();
1313 2 : m_pDoc->CalcAll();
1314 10 : for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
1315 : {
1316 8 : CPPUNIT_ASSERT_EQUAL( aValCheck[nRow][0], m_pDoc->GetValue(ScAddress(1,nRow+1,0)));
1317 8 : CPPUNIT_ASSERT_EQUAL( aValCheck[nRow][1], m_pDoc->GetValue(ScAddress(2,nRow+1,0)));
1318 : }
1319 :
1320 : // Redo and check the result.
1321 2 : pUndoMgr->Redo();
1322 2 : m_pDoc->CalcAll();
1323 10 : for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
1324 : {
1325 8 : size_t i = static_cast<size_t>(m_pDoc->GetValue(ScAddress(2,nRow+1,0))); // order 1..4
1326 8 : CPPUNIT_ASSERT_EQUAL( static_cast<size_t>(nRow+1), i);
1327 8 : CPPUNIT_ASSERT_EQUAL( aValCheck[i-1][2], m_pDoc->GetValue(ScAddress(1,nRow+1,0)));
1328 : }
1329 :
1330 4 : m_pDoc->DeleteTab(0);
1331 2 : }
1332 :
1333 2 : void Test::testSortRefUpdate6()
1334 : {
1335 2 : SortRefNoUpdateSetter aUpdateSet;
1336 :
1337 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1338 2 : m_pDoc->InsertTab(0, "Sort");
1339 :
1340 : const char* aData[][3] = {
1341 : { "Order", "Value", "1" },
1342 : { "9", "1", "=C1+B2" },
1343 : { "1", "2", "=C2+B3" },
1344 : { "8", "3", "=C3+B4" },
1345 2 : };
1346 :
1347 2 : ScAddress aPos(0,0,0);
1348 2 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1349 2 : CPPUNIT_ASSERT(aDataRange.aStart == aPos);
1350 :
1351 : {
1352 : // Expected output table content. 0 = empty cell
1353 : const char* aOutputCheck[][3] = {
1354 : { "Order", "Value", "1" },
1355 : { "9", "1", "2" },
1356 : { "1", "2", "4" },
1357 : { "8", "3", "7" },
1358 2 : };
1359 :
1360 2 : bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "Initial value");
1361 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1362 : }
1363 :
1364 4 : ScDBDocFunc aFunc(getDocShell());
1365 :
1366 : // Sort A1:C4.
1367 : m_pDoc->SetAnonymousDBData(
1368 2 : 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 2, 3));
1369 :
1370 : // Sort A1:A6 by column A (with a row header).
1371 4 : ScSortParam aSortData;
1372 2 : aSortData.nCol1 = 0;
1373 2 : aSortData.nCol2 = 2;
1374 2 : aSortData.nRow1 = 0;
1375 2 : aSortData.nRow2 = 3;
1376 2 : aSortData.bHasHeader = true;
1377 2 : aSortData.maKeyState[0].bDoSort = true;
1378 2 : aSortData.maKeyState[0].nField = 0;
1379 2 : aSortData.maKeyState[0].bAscending = true;
1380 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1381 2 : CPPUNIT_ASSERT(bSorted);
1382 :
1383 : {
1384 : // Expected output table content. 0 = empty cell
1385 : const char* aOutputCheck[][3] = {
1386 : { "Order", "Value", "1" },
1387 : { "1", "2", "3" },
1388 : { "8", "3", "6" },
1389 : { "9", "1", "7" },
1390 2 : };
1391 :
1392 2 : bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "Sorted without reference update");
1393 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1394 : }
1395 :
1396 : // Make sure that the formulas in C2:C4 are not adjusted.
1397 2 : if (!checkFormula(*m_pDoc, ScAddress(2,1,0), "C1+B2"))
1398 0 : CPPUNIT_FAIL("Wrong formula!");
1399 2 : if (!checkFormula(*m_pDoc, ScAddress(2,2,0), "C2+B3"))
1400 0 : CPPUNIT_FAIL("Wrong formula!");
1401 2 : if (!checkFormula(*m_pDoc, ScAddress(2,3,0), "C3+B4"))
1402 0 : CPPUNIT_FAIL("Wrong formula!");
1403 :
1404 : // Undo and check.
1405 2 : SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
1406 2 : CPPUNIT_ASSERT(pUndoMgr);
1407 :
1408 2 : pUndoMgr->Undo();
1409 :
1410 : {
1411 : // Expected output table content. 0 = empty cell
1412 : const char* aOutputCheck[][3] = {
1413 : { "Order", "Value", "1" },
1414 : { "9", "1", "2" },
1415 : { "1", "2", "4" },
1416 : { "8", "3", "7" },
1417 2 : };
1418 :
1419 2 : bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "After undo");
1420 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1421 : }
1422 :
1423 : // Redo and check.
1424 2 : pUndoMgr->Redo();
1425 : {
1426 : // Expected output table content. 0 = empty cell
1427 : const char* aOutputCheck[][3] = {
1428 : { "Order", "Value", "1" },
1429 : { "1", "2", "3" },
1430 : { "8", "3", "6" },
1431 : { "9", "1", "7" },
1432 2 : };
1433 :
1434 2 : bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "After redo");
1435 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1436 : }
1437 :
1438 : // Change the value of C1 and make sure the formula broadcasting chain still works.
1439 2 : ScDocFunc& rFunc = getDocShell().GetDocFunc();
1440 2 : rFunc.SetValueCell(ScAddress(2,0,0), 11.0, false);
1441 : {
1442 : // Expected output table content. 0 = empty cell
1443 : const char* aOutputCheck[][3] = {
1444 : { "Order", "Value", "11" },
1445 : { "1", "2", "13" },
1446 : { "8", "3", "16" },
1447 : { "9", "1", "17" },
1448 2 : };
1449 :
1450 2 : bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "Change the header value");
1451 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1452 : }
1453 :
1454 : // Undo and check.
1455 2 : pUndoMgr->Undo();
1456 : {
1457 : // Expected output table content. 0 = empty cell
1458 : const char* aOutputCheck[][3] = {
1459 : { "Order", "Value", "1" },
1460 : { "1", "2", "3" },
1461 : { "8", "3", "6" },
1462 : { "9", "1", "7" },
1463 2 : };
1464 :
1465 2 : bool bSuccess = checkOutput<3>(m_pDoc, aDataRange, aOutputCheck, "After undo of header value change");
1466 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1467 : }
1468 :
1469 4 : m_pDoc->DeleteTab(0);
1470 2 : }
1471 :
1472 2 : void Test::testSortOutOfPlaceResult()
1473 : {
1474 2 : m_pDoc->InsertTab(0, "Sort");
1475 2 : m_pDoc->InsertTab(1, "Result");
1476 :
1477 : const char* pData[] = {
1478 : "Header",
1479 : "1",
1480 : "23",
1481 : "2",
1482 : "9",
1483 : "-2",
1484 : 0 // terminator
1485 2 : };
1486 :
1487 : // source data in A1:A6.
1488 14 : for (SCROW i = 0; pData[i]; ++i)
1489 12 : m_pDoc->SetString(ScAddress(0,i,0), OUString::createFromAscii(pData[i]));
1490 :
1491 : // Check the initial values.
1492 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
1493 2 : CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1494 2 : CPPUNIT_ASSERT_EQUAL(23.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1495 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1496 2 : CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1497 2 : CPPUNIT_ASSERT_EQUAL(-2.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1498 :
1499 2 : ScDBDocFunc aFunc(getDocShell());
1500 :
1501 : // Sort A1:A6, and set the result to C2:C7
1502 : m_pDoc->SetAnonymousDBData(
1503 2 : 0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 0, 5));
1504 :
1505 4 : ScSortParam aSortData;
1506 2 : aSortData.nCol1 = 0;
1507 2 : aSortData.nCol2 = 0;
1508 2 : aSortData.nRow1 = 0;
1509 2 : aSortData.nRow2 = 5;
1510 2 : aSortData.bHasHeader = true;
1511 2 : aSortData.bInplace = false;
1512 2 : aSortData.nDestTab = 1;
1513 2 : aSortData.nDestCol = 2;
1514 2 : aSortData.nDestRow = 1;
1515 2 : aSortData.maKeyState[0].bDoSort = true;
1516 2 : aSortData.maKeyState[0].nField = 0;
1517 2 : aSortData.maKeyState[0].bAscending = true;
1518 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1519 2 : CPPUNIT_ASSERT(bSorted);
1520 :
1521 : // Source data still intact.
1522 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(0,0,0)));
1523 2 : CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1524 2 : CPPUNIT_ASSERT_EQUAL(23.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1525 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1526 2 : CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1527 2 : CPPUNIT_ASSERT_EQUAL(-2.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1528 :
1529 : // Sort result in C2:C7 on sheet "Result".
1530 2 : CPPUNIT_ASSERT_EQUAL(OUString("Header"), m_pDoc->GetString(ScAddress(2,1,1)));
1531 2 : CPPUNIT_ASSERT_EQUAL(-2.0, m_pDoc->GetValue(ScAddress(2,2,1)));
1532 2 : CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(2,3,1)));
1533 2 : CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(2,4,1)));
1534 2 : CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(2,5,1)));
1535 2 : CPPUNIT_ASSERT_EQUAL(23.0, m_pDoc->GetValue(ScAddress(2,6,1)));
1536 :
1537 2 : m_pDoc->DeleteTab(1);
1538 4 : m_pDoc->DeleteTab(0);
1539 2 : }
1540 :
1541 2 : void Test::testSortPartialFormulaGroup()
1542 : {
1543 2 : SortRefUpdateSetter aUpdateSet;
1544 :
1545 4 : sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
1546 4 : FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1547 :
1548 2 : m_pDoc->InsertTab(0, "Sort");
1549 :
1550 : // Set up the sheet.
1551 : const char* aData[][2] = {
1552 : { "F1", "F2" },
1553 : { "43", "=RC[-1]" },
1554 : { "50", "=RC[-1]" },
1555 : { "8", "=RC[-1]" },
1556 : { "47", "=RC[-1]" },
1557 : { "28", "=RC[-1]" },
1558 : { 0, 0 } // terminator
1559 2 : };
1560 :
1561 : // A1:B6.
1562 14 : for (SCROW i = 0; aData[i][0]; ++i)
1563 : {
1564 12 : m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
1565 12 : m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
1566 : }
1567 :
1568 : // Check the initial condition.
1569 12 : for (SCROW i = 1; i <= 5; ++i)
1570 : // A2:A6 should equal B2:B6.
1571 10 : CPPUNIT_ASSERT_EQUAL(m_pDoc->GetValue(ScAddress(0,i,0)), m_pDoc->GetValue(ScAddress(1,i,0)));
1572 :
1573 2 : const ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(1,1,0));
1574 2 : CPPUNIT_ASSERT(pFC);
1575 2 : CPPUNIT_ASSERT_MESSAGE("This formula cell should be the first in a group.", pFC->IsSharedTop());
1576 2 : CPPUNIT_ASSERT_MESSAGE("Incorrect formula group length.", pFC->GetSharedLength() == 5);
1577 :
1578 4 : ScDBDocFunc aFunc(getDocShell());
1579 :
1580 : // Sort only B2:B4. This caused crash at one point (c.f. fdo#81617).
1581 :
1582 2 : m_pDoc->SetAnonymousDBData(0, new ScDBData(STR_DB_LOCAL_NONAME, 0, 1, 1, 1, 3));
1583 :
1584 4 : ScSortParam aSortData;
1585 2 : aSortData.nCol1 = 1;
1586 2 : aSortData.nCol2 = 1;
1587 2 : aSortData.nRow1 = 1;
1588 2 : aSortData.nRow2 = 3;
1589 2 : aSortData.bHasHeader = false;
1590 2 : aSortData.bInplace = true;
1591 2 : aSortData.maKeyState[0].bDoSort = true;
1592 2 : aSortData.maKeyState[0].nField = 0;
1593 2 : aSortData.maKeyState[0].bAscending = true;
1594 2 : bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
1595 2 : CPPUNIT_ASSERT(bSorted);
1596 :
1597 2 : m_pDoc->CalcAll(); // just in case...
1598 :
1599 : // Check the cell values after the partial sort.
1600 :
1601 : // Column A
1602 2 : CPPUNIT_ASSERT_EQUAL(43.0, m_pDoc->GetValue(ScAddress(0,1,0)));
1603 2 : CPPUNIT_ASSERT_EQUAL(50.0, m_pDoc->GetValue(ScAddress(0,2,0)));
1604 2 : CPPUNIT_ASSERT_EQUAL( 8.0, m_pDoc->GetValue(ScAddress(0,3,0)));
1605 2 : CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(0,4,0)));
1606 2 : CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(0,5,0)));
1607 :
1608 : // Column B
1609 2 : CPPUNIT_ASSERT_EQUAL( 8.0, m_pDoc->GetValue(ScAddress(1,1,0)));
1610 2 : CPPUNIT_ASSERT_EQUAL(43.0, m_pDoc->GetValue(ScAddress(1,2,0)));
1611 2 : CPPUNIT_ASSERT_EQUAL(50.0, m_pDoc->GetValue(ScAddress(1,3,0)));
1612 2 : CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(1,4,0)));
1613 2 : CPPUNIT_ASSERT_EQUAL(28.0, m_pDoc->GetValue(ScAddress(1,5,0)));
1614 :
1615 4 : m_pDoc->DeleteTab(0);
1616 8 : }
1617 :
1618 : /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
|
