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