Line data Source code
1 : /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 : /*
3 : * Version: MPL 1.1 / GPLv3+ / LGPLv3+
4 : *
5 : * The contents of this file are subject to the Mozilla Public License Version
6 : * 1.1 (the "License"); you may not use this file except in compliance with
7 : * the License. You may obtain a copy of the License at
8 : * http://www.mozilla.org/MPL/
9 : *
10 : * Software distributed under the License is distributed on an "AS IS" basis,
11 : * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
12 : * for the specific language governing rights and limitations under the
13 : * License.
14 : *
15 : * The Initial Developer of the Original Code is
16 : * Novell, Inc.
17 : * Portions created by the Initial Developer are Copyright (C) 2010 the
18 : * Initial Developer. All Rights Reserved.
19 : *
20 : * Contributor(s): Michael Meeks <michael.meeks@novell.com>
21 : * Kohei Yoshida <kyoshida@novell.com>
22 : *
23 : * Alternatively, the contents of this file may be used under the terms of
24 : * either the GNU General Public License Version 3 or later (the "GPLv3+"), or
25 : * the GNU Lesser General Public License Version 3 or later (the "LGPLv3+"),
26 : * in which case the provisions of the GPLv3+ or the LGPLv3+ are applicable
27 : * instead of those above.
28 : */
29 : #include <sal/config.h>
30 : #include <test/bootstrapfixture.hxx>
31 :
32 : #include <rtl/strbuf.hxx>
33 : #include <osl/file.hxx>
34 :
35 : #include "scdll.hxx"
36 : #include "document.hxx"
37 : #include "stringutil.hxx"
38 : #include "scmatrix.hxx"
39 : #include "drwlayer.hxx"
40 : #include "scitems.hxx"
41 : #include "reffind.hxx"
42 : #include "markdata.hxx"
43 : #include "clipparam.hxx"
44 : #include "refundo.hxx"
45 : #include "undoblk.hxx"
46 : #include "undotab.hxx"
47 : #include "queryentry.hxx"
48 : #include "postit.hxx"
49 : #include "attrib.hxx"
50 : #include "dbdata.hxx"
51 : #include "reftokenhelper.hxx"
52 : #include "userdat.hxx"
53 :
54 : #include "docsh.hxx"
55 : #include "docfunc.hxx"
56 : #include "dbdocfun.hxx"
57 : #include "funcdesc.hxx"
58 : #include "externalrefmgr.hxx"
59 :
60 : #include "dpshttab.hxx"
61 : #include "dpobject.hxx"
62 : #include "dpsave.hxx"
63 : #include "dpdimsave.hxx"
64 : #include "dpcache.hxx"
65 : #include "dpfilteredcache.hxx"
66 : #include "calcconfig.hxx"
67 : #include "interpre.hxx"
68 :
69 : #include "formula/IFunctionDescription.hxx"
70 :
71 : #include <basegfx/polygon/b2dpolygon.hxx>
72 : #include <editeng/boxitem.hxx>
73 :
74 : #include <svx/svdograf.hxx>
75 : #include <svx/svdpage.hxx>
76 : #include <svx/svdocirc.hxx>
77 : #include <svx/svdopath.hxx>
78 :
79 : #include <sfx2/docfile.hxx>
80 :
81 : #include <com/sun/star/sheet/DataPilotFieldOrientation.hpp>
82 : #include <com/sun/star/sheet/DataPilotFieldGroupBy.hpp>
83 : #include <com/sun/star/sheet/GeneralFunction.hpp>
84 :
85 : #include <iostream>
86 : #include <sstream>
87 : #include <vector>
88 :
89 : #define CALC_DEBUG_OUTPUT 0
90 :
91 : #include "helper/debughelper.hxx"
92 : #include "helper/qahelper.hxx"
93 :
94 : const int indeterminate = 2;
95 :
96 : using namespace ::com::sun::star;
97 : using ::rtl::OUString;
98 : using ::rtl::OUStringBuffer;
99 : using ::std::cout;
100 : using ::std::cerr;
101 : using ::std::endl;
102 : using ::std::vector;
103 :
104 : namespace {
105 :
106 108 : class Test : public test::BootstrapFixture {
107 : public:
108 : Test();
109 :
110 : virtual void setUp();
111 : virtual void tearDown();
112 :
113 : void testCollator();
114 : void testRangeList();
115 : void testInput();
116 : void testCellFunctions();
117 :
118 : /**
119 : * Make sure the SHEETS function gets properly updated during sheet
120 : * insertion and removal.
121 : */
122 : void testSheetsFunc();
123 : void testVolatileFunc();
124 : void testFormulaDepTracking();
125 : void testFuncParam();
126 : void testNamedRange();
127 : void testCSV();
128 : void testMatrix();
129 : void testEnterMixedMatrix();
130 :
131 : /**
132 : * Basic test for pivot tables.
133 : */
134 : void testPivotTable();
135 :
136 : /**
137 : * Test against unwanted automatic format detection on field names and
138 : * field members in pivot tables.
139 : */
140 : void testPivotTableLabels();
141 :
142 : /**
143 : * Make sure that we set cells displaying date values numeric cells,
144 : * rather than text cells. Grouping by date or number functionality
145 : * depends on this.
146 : */
147 : void testPivotTableDateLabels();
148 :
149 : /**
150 : * Test for pivot table's filtering functionality by page fields.
151 : */
152 : void testPivotTableFilters();
153 :
154 : /**
155 : * Test for pivot table's named source range.
156 : */
157 : void testPivotTableNamedSource();
158 :
159 : /**
160 : * Test for pivot table cache. Each dimension in the pivot cache stores
161 : * only unique values that are sorted in ascending order.
162 : */
163 : void testPivotTableCache();
164 :
165 : /**
166 : * Test for pivot table containing data fields that reference the same
167 : * source field but different functions.
168 : */
169 : void testPivotTableDuplicateDataFields();
170 :
171 : void testPivotTableNormalGrouping();
172 : void testPivotTableNumberGrouping();
173 : void testPivotTableDateGrouping();
174 : void testPivotTableEmptyRows();
175 : void testPivotTableTextNumber();
176 :
177 : /**
178 : * Test for checking that pivot table treats strings in a case insensitive
179 : * manner.
180 : */
181 : void testPivotTableCaseInsensitiveStrings();
182 :
183 : /**
184 : * Test for pivot table's handling of double-precision numbers that are
185 : * very close together.
186 : */
187 : void testPivotTableNumStability();
188 :
189 : void testSheetCopy();
190 : void testSheetMove();
191 : void testExternalRef();
192 : void testExternalRefFunctions();
193 : void testDataArea();
194 : void testAutofilter();
195 : void testCopyPaste();
196 : void testMergedCells();
197 : void testUpdateReference();
198 :
199 : /**
200 : * Make sure the sheet streams are invalidated properly.
201 : */
202 : void testStreamValid();
203 :
204 : /**
205 : * Test built-in cell functions to make sure their categories and order
206 : * are correct.
207 : */
208 : void testFunctionLists();
209 :
210 : void testGraphicsInGroup();
211 : void testGraphicsOnSheetMove();
212 :
213 : void testPostIts();
214 :
215 : /**
216 : * Test toggling relative/absolute flag of cell and cell range references.
217 : * This corresponds with hitting Shift-F4 while the cursor is on a formula
218 : * cell.
219 : */
220 : void testToggleRefFlag();
221 :
222 : /**
223 : * Test to make sure correct precedent / dependent cells are obtained when
224 : * preparing to jump to them.
225 : */
226 : void testJumpToPrecedentsDependents();
227 :
228 : void testSetBackgroundColor();
229 : void testRenameTable();
230 :
231 : void testAutoFill();
232 : void testCopyPasteFormulas();
233 : void testCopyPasteFormulasExternalDoc();
234 :
235 : void testFindAreaPosRowDown();
236 : void testFindAreaPosColRight();
237 : void testSort();
238 : void testSortWithFormulaRefs();
239 : void testShiftCells();
240 : void testDeleteRow();
241 : void testDeleteCol();
242 :
243 2 : CPPUNIT_TEST_SUITE(Test);
244 1 : CPPUNIT_TEST(testCollator);
245 1 : CPPUNIT_TEST(testRangeList);
246 1 : CPPUNIT_TEST(testInput);
247 1 : CPPUNIT_TEST(testCellFunctions);
248 1 : CPPUNIT_TEST(testSheetsFunc);
249 1 : CPPUNIT_TEST(testVolatileFunc);
250 1 : CPPUNIT_TEST(testFormulaDepTracking);
251 1 : CPPUNIT_TEST(testFuncParam);
252 1 : CPPUNIT_TEST(testNamedRange);
253 1 : CPPUNIT_TEST(testCSV);
254 1 : CPPUNIT_TEST(testMatrix);
255 1 : CPPUNIT_TEST(testEnterMixedMatrix);
256 1 : CPPUNIT_TEST(testPivotTable);
257 1 : CPPUNIT_TEST(testPivotTableLabels);
258 1 : CPPUNIT_TEST(testPivotTableDateLabels);
259 1 : CPPUNIT_TEST(testPivotTableFilters);
260 1 : CPPUNIT_TEST(testPivotTableNamedSource);
261 1 : CPPUNIT_TEST(testPivotTableCache);
262 1 : CPPUNIT_TEST(testPivotTableDuplicateDataFields);
263 1 : CPPUNIT_TEST(testPivotTableNormalGrouping);
264 1 : CPPUNIT_TEST(testPivotTableNumberGrouping);
265 1 : CPPUNIT_TEST(testPivotTableDateGrouping);
266 1 : CPPUNIT_TEST(testPivotTableEmptyRows);
267 1 : CPPUNIT_TEST(testPivotTableTextNumber);
268 1 : CPPUNIT_TEST(testPivotTableCaseInsensitiveStrings);
269 1 : CPPUNIT_TEST(testPivotTableNumStability);
270 1 : CPPUNIT_TEST(testSheetCopy);
271 1 : CPPUNIT_TEST(testSheetMove);
272 1 : CPPUNIT_TEST(testExternalRef);
273 1 : CPPUNIT_TEST(testExternalRefFunctions);
274 1 : CPPUNIT_TEST(testDataArea);
275 1 : CPPUNIT_TEST(testGraphicsInGroup);
276 1 : CPPUNIT_TEST(testGraphicsOnSheetMove);
277 1 : CPPUNIT_TEST(testPostIts);
278 1 : CPPUNIT_TEST(testStreamValid);
279 1 : CPPUNIT_TEST(testFunctionLists);
280 1 : CPPUNIT_TEST(testToggleRefFlag);
281 1 : CPPUNIT_TEST(testAutofilter);
282 1 : CPPUNIT_TEST(testCopyPaste);
283 1 : CPPUNIT_TEST(testMergedCells);
284 1 : CPPUNIT_TEST(testUpdateReference);
285 1 : CPPUNIT_TEST(testJumpToPrecedentsDependents);
286 1 : CPPUNIT_TEST(testSetBackgroundColor);
287 1 : CPPUNIT_TEST(testRenameTable);
288 1 : CPPUNIT_TEST(testAutoFill);
289 1 : CPPUNIT_TEST(testCopyPasteFormulas);
290 1 : CPPUNIT_TEST(testCopyPasteFormulasExternalDoc);
291 1 : CPPUNIT_TEST(testFindAreaPosRowDown);
292 1 : CPPUNIT_TEST(testFindAreaPosColRight);
293 1 : CPPUNIT_TEST(testSort);
294 1 : CPPUNIT_TEST(testSortWithFormulaRefs);
295 1 : CPPUNIT_TEST(testShiftCells);
296 1 : CPPUNIT_TEST(testDeleteRow);
297 1 : CPPUNIT_TEST(testDeleteCol);
298 2 : CPPUNIT_TEST_SUITE_END();
299 :
300 : private:
301 : ScDocument *m_pDoc;
302 : ScDocShellRef m_xDocShRef;
303 : };
304 :
305 15 : void clearRange(ScDocument* pDoc, const ScRange& rRange)
306 : {
307 15 : ScMarkData aMarkData;
308 15 : aMarkData.SetMarkArea(rRange);
309 : pDoc->DeleteArea(
310 15 : rRange.aStart.Col(), rRange.aStart.Row(),
311 30 : rRange.aEnd.Col(), rRange.aEnd.Row(), aMarkData, IDF_CONTENTS);
312 15 : }
313 :
314 23 : void printRange(ScDocument* pDoc, const ScRange& rRange, const char* pCaption)
315 : {
316 23 : SCROW nRow1 = rRange.aStart.Row(), nRow2 = rRange.aEnd.Row();
317 23 : SCCOL nCol1 = rRange.aStart.Col(), nCol2 = rRange.aEnd.Col();
318 23 : SheetPrinter printer(nRow2 - nRow1 + 1, nCol2 - nCol1 + 1);
319 246 : for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
320 : {
321 857 : for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
322 : {
323 634 : rtl::OUString aVal;
324 634 : pDoc->GetString(nCol, nRow, rRange.aStart.Tab(), aVal);
325 634 : printer.set(nRow-nRow1, nCol-nCol1, aVal);
326 634 : }
327 : }
328 23 : printer.print(pCaption);
329 23 : }
330 :
331 : template<size_t _Size>
332 12 : ScRange insertRangeData(ScDocument* pDoc, const ScAddress& rPos, const char* aData[][_Size], size_t nRowCount)
333 : {
334 47 : for (size_t i = 0; i < _Size; ++i)
335 : {
336 371 : for (size_t j = 0; j < nRowCount; ++j)
337 : {
338 336 : SCCOL nCol = i + rPos.Col();
339 336 : SCROW nRow = j + rPos.Row();
340 336 : pDoc->SetString(nCol, nRow, rPos.Tab(), OUString(aData[j][i], strlen(aData[j][i]), RTL_TEXTENCODING_UTF8));
341 : }
342 : }
343 :
344 12 : ScRange aRange(rPos);
345 12 : aRange.aEnd.SetCol(rPos.Col()+_Size-1);
346 12 : aRange.aEnd.SetRow(rPos.Row()+nRowCount-1);
347 12 : printRange(pDoc, aRange, "Range data content");
348 12 : return aRange;
349 : }
350 :
351 : /**
352 : * Temporarily switch on/off auto calculation mode.
353 : */
354 : class AutoCalcSwitch
355 : {
356 : ScDocument* mpDoc;
357 : bool mbOldValue;
358 : public:
359 2 : AutoCalcSwitch(ScDocument* pDoc, bool bAutoCalc) : mpDoc(pDoc), mbOldValue(pDoc->GetAutoCalc())
360 : {
361 2 : mpDoc->SetAutoCalc(bAutoCalc);
362 2 : }
363 :
364 2 : ~AutoCalcSwitch()
365 : {
366 2 : mpDoc->SetAutoCalc(mbOldValue);
367 2 : }
368 : };
369 :
370 : /**
371 : * Temporarily set formula grammar.
372 : */
373 : class FormulaGrammarSwitch
374 : {
375 : ScDocument* mpDoc;
376 : formula::FormulaGrammar::Grammar meOldGrammar;
377 : public:
378 2 : FormulaGrammarSwitch(ScDocument* pDoc, formula::FormulaGrammar::Grammar eGrammar) :
379 2 : mpDoc(pDoc), meOldGrammar(pDoc->GetGrammar())
380 : {
381 2 : mpDoc->SetGrammar(eGrammar);
382 2 : }
383 :
384 2 : ~FormulaGrammarSwitch()
385 : {
386 2 : mpDoc->SetGrammar(meOldGrammar);
387 2 : }
388 : };
389 :
390 54 : Test::Test()
391 54 : : m_pDoc(0)
392 : {
393 54 : }
394 :
395 54 : void Test::setUp()
396 : {
397 54 : BootstrapFixture::setUp();
398 :
399 54 : ScDLL::Init();
400 : m_xDocShRef = new ScDocShell(
401 : SFXMODEL_STANDARD |
402 : SFXMODEL_DISABLE_EMBEDDED_SCRIPTS |
403 54 : SFXMODEL_DISABLE_DOCUMENT_RECOVERY);
404 :
405 54 : m_pDoc = m_xDocShRef->GetDocument();
406 54 : }
407 :
408 54 : void Test::tearDown()
409 : {
410 54 : m_xDocShRef.Clear();
411 54 : BootstrapFixture::tearDown();
412 54 : }
413 :
414 1 : void Test::testCollator()
415 : {
416 1 : OUString s1("A");
417 1 : OUString s2("B");
418 1 : CollatorWrapper* p = ScGlobal::GetCollator();
419 1 : sal_Int32 nRes = p->compareString(s1, s2);
420 1 : CPPUNIT_ASSERT_MESSAGE("these strings are supposed to be different!", nRes != 0);
421 1 : }
422 :
423 1 : void Test::testRangeList()
424 : {
425 1 : m_pDoc->InsertTab(0, "foo");
426 :
427 1 : ScRangeList aRL;
428 1 : aRL.Append(ScRange(1,1,0,3,10,0));
429 1 : CPPUNIT_ASSERT_MESSAGE("List should have one range.", aRL.size() == 1);
430 1 : const ScRange* p = aRL[0];
431 1 : CPPUNIT_ASSERT_MESSAGE("Failed to get the range object.", p);
432 1 : CPPUNIT_ASSERT_MESSAGE("Wrong range.", p->aStart == ScAddress(1,1,0) && p->aEnd == ScAddress(3,10,0));
433 :
434 : // TODO: Add more tests here.
435 :
436 1 : m_pDoc->DeleteTab(0);
437 1 : }
438 :
439 1 : void Test::testInput()
440 : {
441 1 : rtl::OUString aTabName("foo");
442 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
443 1 : m_pDoc->InsertTab (0, aTabName));
444 :
445 1 : OUString numstr("'10.5");
446 1 : OUString str("'apple'");
447 1 : OUString test;
448 :
449 1 : m_pDoc->SetString(0, 0, 0, numstr);
450 1 : m_pDoc->GetString(0, 0, 0, test);
451 1 : bool bTest = test == "10.5";
452 1 : CPPUNIT_ASSERT_MESSAGE("String number should have the first apostrophe stripped.", bTest);
453 1 : m_pDoc->SetString(0, 0, 0, str);
454 1 : m_pDoc->GetString(0, 0, 0, test);
455 1 : bTest = test == "'apple'";
456 1 : CPPUNIT_ASSERT_MESSAGE("Text content should have retained the first apostrophe.", bTest);
457 :
458 : // Customized string handling policy.
459 1 : ScSetStringParam aParam;
460 1 : aParam.mbDetectNumberFormat = false;
461 1 : aParam.meSetTextNumFormat = ScSetStringParam::Always;
462 1 : aParam.mbHandleApostrophe = false;
463 1 : m_pDoc->SetString(0, 0, 0, "000123", &aParam);
464 1 : m_pDoc->GetString(0, 0, 0, test);
465 1 : CPPUNIT_ASSERT_MESSAGE("Text content should have been treated as string, not number.", test == "000123");
466 :
467 1 : m_pDoc->DeleteTab(0);
468 1 : }
469 :
470 1 : void testFuncSUM(ScDocument* pDoc)
471 : {
472 1 : double val = 1;
473 : double result;
474 1 : pDoc->SetValue (0, 0, 0, val);
475 1 : pDoc->SetValue (0, 1, 0, val);
476 1 : pDoc->SetString (0, 2, 0, rtl::OUString("=SUM(A1:A2)"));
477 1 : pDoc->CalcAll();
478 1 : pDoc->GetValue (0, 2, 0, result);
479 1 : CPPUNIT_ASSERT_MESSAGE ("calculation failed", result == 2.0);
480 1 : }
481 :
482 1 : void testFuncPRODUCT(ScDocument* pDoc)
483 : {
484 1 : double val = 1;
485 : double result;
486 1 : pDoc->SetValue(0, 0, 0, val);
487 1 : val = 2;
488 1 : pDoc->SetValue(0, 1, 0, val);
489 1 : val = 3;
490 1 : pDoc->SetValue(0, 2, 0, val);
491 1 : pDoc->SetString(0, 3, 0, OUString("=PRODUCT(A1:A3)"));
492 1 : pDoc->CalcAll();
493 1 : pDoc->GetValue(0, 3, 0, result);
494 1 : CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT failed", result == 6.0);
495 :
496 1 : pDoc->SetString(0, 4, 0, OUString("=PRODUCT({1;2;3})"));
497 1 : pDoc->CalcAll();
498 1 : pDoc->GetValue(0, 4, 0, result);
499 1 : CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT with inline array failed", result == 6.0);
500 1 : }
501 :
502 1 : void testFuncN(ScDocument* pDoc)
503 : {
504 : double result;
505 :
506 : // Clear the area first.
507 1 : clearRange(pDoc, ScRange(0, 0, 0, 1, 20, 0));
508 :
509 : // Put values to reference.
510 1 : double val = 0;
511 1 : pDoc->SetValue(0, 0, 0, val);
512 1 : pDoc->SetString(0, 2, 0, OUString("Text"));
513 1 : val = 1;
514 1 : pDoc->SetValue(0, 3, 0, val);
515 1 : val = -1;
516 1 : pDoc->SetValue(0, 4, 0, val);
517 1 : val = 12.3;
518 1 : pDoc->SetValue(0, 5, 0, val);
519 1 : pDoc->SetString(0, 6, 0, OUString("'12.3"));
520 :
521 : // Cell references
522 1 : pDoc->SetString(1, 0, 0, OUString("=N(A1)"));
523 1 : pDoc->SetString(1, 1, 0, OUString("=N(A2)"));
524 1 : pDoc->SetString(1, 2, 0, OUString("=N(A3)"));
525 1 : pDoc->SetString(1, 3, 0, OUString("=N(A4)"));
526 1 : pDoc->SetString(1, 4, 0, OUString("=N(A5)"));
527 1 : pDoc->SetString(1, 5, 0, OUString("=N(A6)"));
528 1 : pDoc->SetString(1, 6, 0, OUString("=N(A9)"));
529 :
530 : // In-line values
531 1 : pDoc->SetString(1, 7, 0, OUString("=N(0)"));
532 1 : pDoc->SetString(1, 8, 0, OUString("=N(1)"));
533 1 : pDoc->SetString(1, 9, 0, OUString("=N(-1)"));
534 1 : pDoc->SetString(1, 10, 0, OUString("=N(123)"));
535 1 : pDoc->SetString(1, 11, 0, OUString("=N(\"\")"));
536 1 : pDoc->SetString(1, 12, 0, OUString("=N(\"12\")"));
537 1 : pDoc->SetString(1, 13, 0, OUString("=N(\"foo\")"));
538 :
539 : // Range references
540 1 : pDoc->SetString(2, 2, 0, OUString("=N(A1:A8)"));
541 1 : pDoc->SetString(2, 3, 0, OUString("=N(A1:A8)"));
542 1 : pDoc->SetString(2, 4, 0, OUString("=N(A1:A8)"));
543 1 : pDoc->SetString(2, 5, 0, OUString("=N(A1:A8)"));
544 :
545 : // Calculate and check the results.
546 1 : pDoc->CalcAll();
547 : double checks1[] = {
548 : 0, 0, 0, 1, -1, 12.3, 0, // cell reference
549 : 0, 1, -1, 123, 0, 0, 0 // in-line values
550 1 : };
551 15 : for (size_t i = 0; i < SAL_N_ELEMENTS(checks1); ++i)
552 : {
553 14 : pDoc->GetValue(1, i, 0, result);
554 14 : bool bGood = result == checks1[i];
555 14 : if (!bGood)
556 : {
557 0 : cerr << "row " << (i+1) << ": expected=" << checks1[i] << " actual=" << result << endl;
558 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
559 : }
560 : }
561 : double checks2[] = {
562 : 0, 1, -1, 12.3 // range references
563 1 : };
564 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(checks2); ++i)
565 : {
566 4 : pDoc->GetValue(1, i+2, 0, result);
567 4 : bool bGood = result == checks2[i];
568 4 : if (!bGood)
569 : {
570 0 : cerr << "row " << (i+2+1) << ": expected=" << checks2[i] << " actual=" << result << endl;
571 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
572 : }
573 : }
574 1 : }
575 :
576 1 : void testFuncCOUNTIF(ScDocument* pDoc)
577 : {
578 : // COUNTIF (test case adopted from OOo i#36381)
579 :
580 : // Empty A1:A39 first.
581 1 : clearRange(pDoc, ScRange(0, 0, 0, 0, 40, 0));
582 :
583 : // Raw data (rows 1 through 9)
584 : const char* aData[] = {
585 : "1999",
586 : "2000",
587 : "0",
588 : "0",
589 : "0",
590 : "2002",
591 : "2001",
592 : "X",
593 : "2002"
594 1 : };
595 :
596 1 : SCROW nRows = SAL_N_ELEMENTS(aData);
597 10 : for (SCROW i = 0; i < nRows; ++i)
598 9 : pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
599 :
600 1 : printRange(pDoc, ScRange(0, 0, 0, 0, 8, 0), "data range for COUNTIF");
601 :
602 : // formulas and results
603 : struct {
604 : const char* pFormula; double fResult;
605 : } aChecks[] = {
606 : { "=COUNTIF(A1:A12;1999)", 1 },
607 : { "=COUNTIF(A1:A12;2002)", 2 },
608 : { "=COUNTIF(A1:A12;1998)", 0 },
609 : { "=COUNTIF(A1:A12;\">=1999\")", 5 },
610 : { "=COUNTIF(A1:A12;\">1999\")", 4 },
611 : { "=COUNTIF(A1:A12;\"<2001\")", 5 },
612 : { "=COUNTIF(A1:A12;\">0\")", 5 },
613 : { "=COUNTIF(A1:A12;\">=0\")", 8 },
614 : { "=COUNTIF(A1:A12;0)", 3 },
615 : { "=COUNTIF(A1:A12;\"X\")", 1 },
616 : { "=COUNTIF(A1:A12;)", 3 }
617 1 : };
618 :
619 1 : nRows = SAL_N_ELEMENTS(aChecks);
620 12 : for (SCROW i = 0; i < nRows; ++i)
621 : {
622 11 : SCROW nRow = 20 + i;
623 11 : pDoc->SetString(0, nRow, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
624 : }
625 1 : pDoc->CalcAll();
626 :
627 12 : for (SCROW i = 0; i < nRows; ++i)
628 : {
629 : double result;
630 11 : SCROW nRow = 20 + i;
631 11 : pDoc->GetValue(0, nRow, 0, result);
632 11 : bool bGood = result == aChecks[i].fResult;
633 11 : if (!bGood)
634 : {
635 0 : cerr << "row " << (nRow+1) << ": formula" << aChecks[i].pFormula
636 0 : << " expected=" << aChecks[i].fResult << " actual=" << result << endl;
637 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for COUNTIF", false);
638 : }
639 : }
640 :
641 : // Don't count empty strings when searching for a number.
642 :
643 : // Clear A1:A2.
644 1 : clearRange(pDoc, ScRange(0, 0, 0, 0, 1, 0));
645 :
646 1 : pDoc->SetString(0, 0, 0, rtl::OUString("=\"\""));
647 1 : pDoc->SetString(0, 1, 0, rtl::OUString("=COUNTIF(A1;1)"));
648 1 : pDoc->CalcAll();
649 :
650 1 : double result = pDoc->GetValue(0, 1, 0);
651 1 : CPPUNIT_ASSERT_MESSAGE("We shouldn't count empty string as valid number.", result == 0.0);
652 1 : }
653 :
654 1 : void testFuncVLOOKUP(ScDocument* pDoc)
655 : {
656 : // VLOOKUP
657 :
658 : // Clear A1:F40.
659 1 : clearRange(pDoc, ScRange(0, 0, 0, 5, 39, 0));
660 :
661 : // Raw data
662 : const char* aData[][2] = {
663 : { "Key", "Val" },
664 : { "10", "3" },
665 : { "20", "4" },
666 : { "30", "5" },
667 : { "40", "6" },
668 : { "50", "7" },
669 : { "60", "8" },
670 : { "70", "9" },
671 : { "B", "10" },
672 : { "B", "11" },
673 : { "C", "12" },
674 : { "D", "13" },
675 : { "E", "14" },
676 : { "F", "15" },
677 : { 0, 0 } // terminator
678 1 : };
679 :
680 : // Insert raw data into A1:B14.
681 15 : for (SCROW i = 0; aData[i][0]; ++i)
682 : {
683 14 : pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i][0]));
684 14 : pDoc->SetString(1, i, 0, rtl::OUString::createFromAscii(aData[i][1]));
685 : }
686 :
687 1 : printRange(pDoc, ScRange(0, 0, 0, 1, 13, 0), "raw data for VLOOKUP");
688 :
689 : // Formula data
690 : struct {
691 : const char* pLookup; const char* pFormula; const char* pRes;
692 : } aChecks[] = {
693 : { "Lookup", "Formula", 0 },
694 : { "12", "=VLOOKUP(D2;A2:B14;2;1)", "3" },
695 : { "29", "=VLOOKUP(D3;A2:B14;2;1)", "4" },
696 : { "31", "=VLOOKUP(D4;A2:B14;2;1)", "5" },
697 : { "45", "=VLOOKUP(D5;A2:B14;2;1)", "6" },
698 : { "56", "=VLOOKUP(D6;A2:B14;2;1)", "7" },
699 : { "65", "=VLOOKUP(D7;A2:B14;2;1)", "8" },
700 : { "78", "=VLOOKUP(D8;A2:B14;2;1)", "9" },
701 : { "Andy", "=VLOOKUP(D9;A2:B14;2;1)", "#N/A" },
702 : { "Bruce", "=VLOOKUP(D10;A2:B14;2;1)", "11" },
703 : { "Charlie", "=VLOOKUP(D11;A2:B14;2;1)", "12" },
704 : { "David", "=VLOOKUP(D12;A2:B14;2;1)", "13" },
705 : { "Edward", "=VLOOKUP(D13;A2:B14;2;1)", "14" },
706 : { "Frank", "=VLOOKUP(D14;A2:B14;2;1)", "15" },
707 : { "Henry", "=VLOOKUP(D15;A2:B14;2;1)", "15" },
708 : { "100", "=VLOOKUP(D16;A2:B14;2;1)", "9" },
709 : { "1000", "=VLOOKUP(D17;A2:B14;2;1)", "9" },
710 : { "Zena", "=VLOOKUP(D18;A2:B14;2;1)", "15" }
711 1 : };
712 :
713 : // Insert formula data into D1:E18.
714 19 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
715 : {
716 18 : pDoc->SetString(3, i, 0, rtl::OUString::createFromAscii(aChecks[i].pLookup));
717 18 : pDoc->SetString(4, i, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
718 : }
719 1 : pDoc->CalcAll();
720 1 : printRange(pDoc, ScRange(3, 0, 0, 4, 17, 0), "formula data for VLOOKUP");
721 :
722 : // Verify results.
723 19 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
724 : {
725 18 : if (i == 0)
726 : // Skip the header row.
727 1 : continue;
728 :
729 17 : rtl::OUString aRes;
730 17 : pDoc->GetString(4, i, 0, aRes);
731 17 : bool bGood = aRes.equalsAscii(aChecks[i].pRes);
732 17 : if (!bGood)
733 : {
734 0 : cerr << "row " << (i+1) << ": lookup value='" << aChecks[i].pLookup
735 0 : << "' expected='" << aChecks[i].pRes << "' actual='" << aRes << "'" << endl;
736 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for VLOOKUP", false);
737 : }
738 17 : }
739 1 : }
740 :
741 : struct NumStrCheck {
742 : double fVal;
743 : const char* pRes;
744 : };
745 :
746 : struct StrStrCheck {
747 : const char* pVal;
748 : const char* pRes;
749 : };
750 :
751 : template<size_t _DataSize, size_t _FormulaSize, int _Type>
752 2 : void runTestMATCH(ScDocument* pDoc, const char* aData[_DataSize], StrStrCheck aChecks[_FormulaSize])
753 : {
754 2 : size_t nDataSize = _DataSize;
755 26 : for (size_t i = 0; i < nDataSize; ++i)
756 24 : pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
757 :
758 33 : for (size_t i = 0; i < _FormulaSize; ++i)
759 : {
760 31 : pDoc->SetString(1, i, 0, rtl::OUString::createFromAscii(aChecks[i].pVal));
761 :
762 31 : rtl::OUStringBuffer aBuf;
763 31 : aBuf.appendAscii("=MATCH(B");
764 31 : aBuf.append(static_cast<sal_Int32>(i+1));
765 31 : aBuf.appendAscii(";A1:A");
766 31 : aBuf.append(static_cast<sal_Int32>(nDataSize));
767 31 : aBuf.appendAscii(";");
768 31 : aBuf.append(static_cast<sal_Int32>(_Type));
769 31 : aBuf.appendAscii(")");
770 31 : rtl::OUString aFormula = aBuf.makeStringAndClear();
771 31 : pDoc->SetString(2, i, 0, aFormula);
772 : }
773 :
774 2 : pDoc->CalcAll();
775 2 : printRange(pDoc, ScRange(0, 0, 0, 2, _FormulaSize-1, 0), "MATCH");
776 :
777 : // verify the results.
778 33 : for (size_t i = 0; i < _FormulaSize; ++i)
779 : {
780 31 : rtl::OUString aStr;
781 31 : pDoc->GetString(2, i, 0, aStr);
782 31 : if (!aStr.equalsAscii(aChecks[i].pRes))
783 : {
784 0 : cerr << "row " << (i+1) << ": expected='" << aChecks[i].pRes << "' actual='" << aStr << "'"
785 : << " criterion='" << aChecks[i].pVal << "'" << endl;
786 0 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for MATCH", false);
787 : }
788 : }
789 2 : }
790 :
791 1 : void testFuncMATCH(ScDocument* pDoc)
792 : {
793 1 : clearRange(pDoc, ScRange(0, 0, 0, 4, 40, 0));
794 : {
795 : // Ascending in-exact match
796 :
797 : // data range (A1:A9)
798 : const char* aData[] = {
799 : "1",
800 : "2",
801 : "3",
802 : "4",
803 : "5",
804 : "6",
805 : "7",
806 : "8",
807 : "9",
808 : "B",
809 : "B",
810 : "C",
811 1 : };
812 :
813 : // formula (B1:C12)
814 : StrStrCheck aChecks[] = {
815 : { "0.8", "#N/A" },
816 : { "1.2", "1" },
817 : { "2.3", "2" },
818 : { "3.9", "3" },
819 : { "4.1", "4" },
820 : { "5.99", "5" },
821 : { "6.1", "6" },
822 : { "7.2", "7" },
823 : { "8.569", "8" },
824 : { "9.59", "9" },
825 : { "10", "9" },
826 : { "100", "9" },
827 : { "Andy", "#N/A" },
828 : { "Bruce", "11" },
829 : { "Charlie", "12" }
830 1 : };
831 :
832 1 : runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),1>(pDoc, aData, aChecks);
833 : }
834 :
835 : {
836 : // Descending in-exact match
837 :
838 : // data range (A1:A9)
839 : const char* aData[] = {
840 : "D",
841 : "C",
842 : "B",
843 : "9",
844 : "8",
845 : "7",
846 : "6",
847 : "5",
848 : "4",
849 : "3",
850 : "2",
851 : "1"
852 1 : };
853 :
854 : // formula (B1:C12)
855 : StrStrCheck aChecks[] = {
856 : { "10", "#N/A" },
857 : { "8.9", "4" },
858 : { "7.8", "5" },
859 : { "6.7", "6" },
860 : { "5.5", "7" },
861 : { "4.6", "8" },
862 : { "3.3", "9" },
863 : { "2.2", "10" },
864 : { "1.1", "11" },
865 : { "0.8", "12" },
866 : { "0", "12" },
867 : { "-2", "12" },
868 : { "Andy", "3" },
869 : { "Bruce", "2" },
870 : { "Charlie", "1" },
871 : { "David", "#N/A" }
872 1 : };
873 :
874 1 : runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),-1>(pDoc, aData, aChecks);
875 : }
876 1 : }
877 :
878 1 : void testFuncCELL(ScDocument* pDoc)
879 : {
880 1 : clearRange(pDoc, ScRange(0, 0, 0, 2, 20, 0)); // Clear A1:C21.
881 :
882 : {
883 1 : const char* pContent = "Some random text";
884 1 : pDoc->SetString(2, 9, 0, rtl::OUString::createFromAscii(pContent)); // Set this value to C10.
885 1 : double val = 1.2;
886 1 : pDoc->SetValue(2, 0, 0, val); // Set numeric value to C1;
887 :
888 : // We don't test: FILENAME, FORMAT, WIDTH, PROTECT, PREFIX
889 : StrStrCheck aChecks[] = {
890 : { "=CELL(\"COL\";C10)", "3" },
891 : { "=CELL(\"ROW\";C10)", "10" },
892 : { "=CELL(\"SHEET\";C10)", "1" },
893 : { "=CELL(\"ADDRESS\";C10)", "$C$10" },
894 : { "=CELL(\"CONTENTS\";C10)", pContent },
895 : { "=CELL(\"COLOR\";C10)", "0" },
896 : { "=CELL(\"TYPE\";C9)", "b" },
897 : { "=CELL(\"TYPE\";C10)", "l" },
898 : { "=CELL(\"TYPE\";C1)", "v" },
899 : { "=CELL(\"PARENTHESES\";C10)", "0" }
900 1 : };
901 :
902 11 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
903 10 : pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aChecks[i].pVal));
904 1 : pDoc->CalcAll();
905 :
906 11 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
907 : {
908 10 : rtl::OUString aVal = pDoc->GetString(0, i, 0);
909 10 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for CELL", aVal.equalsAscii(aChecks[i].pRes));
910 10 : }
911 : }
912 1 : }
913 :
914 : /** See also test case document fdo#44456 sheet cpearson */
915 1 : void testFuncDATEDIF( ScDocument* pDoc )
916 : {
917 : const char* aData[][5] = {
918 : { "2007-01-01", "2007-01-10", "d", "9", "=DATEDIF(A1;B1;C1)" } ,
919 : { "2007-01-01", "2007-01-31", "m", "0", "=DATEDIF(A2;B2;C2)" } ,
920 : { "2007-01-01", "2007-02-01", "m", "1", "=DATEDIF(A3;B3;C3)" } ,
921 : { "2007-01-01", "2007-02-28", "m", "1", "=DATEDIF(A4;B4;C4)" } ,
922 : { "2007-01-01", "2007-12-31", "d", "364", "=DATEDIF(A5;B5;C5)" } ,
923 : { "2007-01-01", "2007-01-31", "y", "0", "=DATEDIF(A6;B6;C6)" } ,
924 : { "2007-01-01", "2008-07-01", "d", "547", "=DATEDIF(A7;B7;C7)" } ,
925 : { "2007-01-01", "2008-07-01", "m", "18", "=DATEDIF(A8;B8;C8)" } ,
926 : { "2007-01-01", "2008-07-01", "ym", "6", "=DATEDIF(A9;B9;C9)" } ,
927 : { "2007-01-01", "2008-07-01", "yd", "182", "=DATEDIF(A10;B10;C10)" } ,
928 : { "2008-01-01", "2009-07-01", "yd", "181", "=DATEDIF(A11;B11;C11)" } ,
929 : { "2007-01-01", "2007-01-31", "md", "30", "=DATEDIF(A12;B12;C12)" } ,
930 : { "2007-02-01", "2009-03-01", "md", "0", "=DATEDIF(A13;B13;C13)" } ,
931 : { "2008-02-01", "2009-03-01", "md", "0", "=DATEDIF(A14;B14;C14)" } ,
932 : { "2007-01-02", "2007-01-01", "md", "Err:502", "=DATEDIF(A15;B15;C15)" } // fail date1 > date2
933 1 : };
934 :
935 1 : clearRange( pDoc, ScRange(0, 0, 0, 4, SAL_N_ELEMENTS(aData), 0));
936 1 : ScAddress aPos(0,0,0);
937 1 : ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
938 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
939 :
940 1 : pDoc->CalcAll();
941 :
942 16 : for (size_t i = 0; i < SAL_N_ELEMENTS(aData); ++i)
943 : {
944 15 : rtl::OUString aVal = pDoc->GetString( 4, i, 0);
945 : //std::cout << "row "<< i << ": " << rtl::OUStringToOString( aVal, RTL_TEXTENCODING_UTF8).getStr() << ", expected " << aData[i][3] << std::endl;
946 15 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for DATEDIF", aVal.equalsAscii( aData[i][3]));
947 15 : }
948 1 : }
949 :
950 1 : void testFuncINDIRECT(ScDocument* pDoc)
951 : {
952 1 : clearRange(pDoc, ScRange(0, 0, 0, 0, 10, 0)); // Clear A1:A11
953 1 : rtl::OUString aTabName;
954 1 : bool bGood = pDoc->GetName(0, aTabName);
955 1 : CPPUNIT_ASSERT_MESSAGE("failed to get sheet name.", bGood);
956 :
957 1 : rtl::OUString aTest = "Test", aRefErr = "#REF!";
958 1 : pDoc->SetString(0, 10, 0, aTest);
959 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", pDoc->GetString(0,10,0) == aTest);
960 :
961 1 : rtl::OUString aPrefix = "=INDIRECT(\"";
962 :
963 1 : rtl::OUString aFormula = aPrefix + aTabName + ".A11\")"; // Calc A1
964 1 : pDoc->SetString(0, 0, 0, aFormula);
965 1 : aFormula = aPrefix + aTabName + "!A11\")"; // Excel A1
966 1 : pDoc->SetString(0, 1, 0, aFormula);
967 1 : aFormula = aPrefix + aTabName + "!R11C1\")"; // Excel R1C1
968 1 : pDoc->SetString(0, 2, 0, aFormula);
969 1 : aFormula = aPrefix + aTabName + "!R11C1\";0)"; // Excel R1C1 (forced)
970 1 : pDoc->SetString(0, 3, 0, aFormula);
971 :
972 1 : pDoc->CalcAll();
973 : {
974 : // Default is to use the current formula syntax, which is Calc A1.
975 : const rtl::OUString* aChecks[] = {
976 : &aTest, &aRefErr, &aRefErr, &aTest
977 1 : };
978 :
979 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
980 : {
981 4 : rtl::OUString aVal = pDoc->GetString(0, i, 0);
982 4 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
983 4 : }
984 : }
985 :
986 1 : ScCalcConfig aConfig;
987 1 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_OOO;
988 1 : ScInterpreter::SetGlobalConfig(aConfig);
989 1 : pDoc->CalcAll();
990 : {
991 : // Explicit Calc A1 syntax
992 : const rtl::OUString* aChecks[] = {
993 : &aTest, &aRefErr, &aRefErr, &aTest
994 1 : };
995 :
996 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
997 : {
998 4 : rtl::OUString aVal = pDoc->GetString(0, i, 0);
999 4 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
1000 4 : }
1001 : }
1002 :
1003 1 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_A1;
1004 1 : ScInterpreter::SetGlobalConfig(aConfig);
1005 1 : pDoc->CalcAll();
1006 : {
1007 : // Excel A1 syntax
1008 : const rtl::OUString* aChecks[] = {
1009 : &aRefErr, &aTest, &aRefErr, &aTest
1010 1 : };
1011 :
1012 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1013 : {
1014 4 : rtl::OUString aVal = pDoc->GetString(0, i, 0);
1015 4 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
1016 4 : }
1017 : }
1018 :
1019 1 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_R1C1;
1020 1 : ScInterpreter::SetGlobalConfig(aConfig);
1021 1 : pDoc->CalcAll();
1022 : {
1023 : // Excel R1C1 syntax
1024 : const rtl::OUString* aChecks[] = {
1025 : &aRefErr, &aRefErr, &aTest, &aTest
1026 1 : };
1027 :
1028 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1029 : {
1030 4 : rtl::OUString aVal = pDoc->GetString(0, i, 0);
1031 4 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
1032 4 : }
1033 1 : }
1034 1 : }
1035 :
1036 1 : void Test::testCellFunctions()
1037 : {
1038 1 : rtl::OUString aTabName("foo");
1039 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1040 1 : m_pDoc->InsertTab (0, aTabName));
1041 :
1042 1 : testFuncSUM(m_pDoc);
1043 1 : testFuncPRODUCT(m_pDoc);
1044 1 : testFuncN(m_pDoc);
1045 1 : testFuncCOUNTIF(m_pDoc);
1046 1 : testFuncVLOOKUP(m_pDoc);
1047 1 : testFuncMATCH(m_pDoc);
1048 1 : testFuncCELL(m_pDoc);
1049 1 : testFuncDATEDIF(m_pDoc);
1050 1 : testFuncINDIRECT(m_pDoc);
1051 :
1052 1 : m_pDoc->DeleteTab(0);
1053 1 : }
1054 :
1055 1 : void Test::testSheetsFunc()
1056 : {
1057 1 : rtl::OUString aTabName1("test1");
1058 1 : rtl::OUString aTabName2("test2");
1059 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1060 1 : m_pDoc->InsertTab (SC_TAB_APPEND, aTabName1));
1061 :
1062 1 : m_pDoc->SetString(0, 0, 0, OUString("=SHEETS()"));
1063 1 : m_pDoc->CalcFormulaTree(false, false);
1064 : double original;
1065 1 : m_pDoc->GetValue(0, 0, 0, original);
1066 :
1067 2 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() should equal the number of sheets, but doesn't.",
1068 1 : static_cast<SCTAB>(original) == m_pDoc->GetTableCount());
1069 :
1070 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1071 1 : m_pDoc->InsertTab (SC_TAB_APPEND, aTabName2));
1072 :
1073 : double modified;
1074 1 : m_pDoc->GetValue(0, 0, 0, modified);
1075 2 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() did not get updated after sheet insertion.",
1076 1 : modified - original == 1.0);
1077 :
1078 1 : SCTAB nTabCount = m_pDoc->GetTableCount();
1079 1 : m_pDoc->DeleteTab(--nTabCount);
1080 :
1081 1 : m_pDoc->GetValue(0, 0, 0, modified);
1082 2 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() did not get updated after sheet removal.",
1083 1 : modified - original == 0.0);
1084 :
1085 1 : m_pDoc->DeleteTab(--nTabCount);
1086 1 : }
1087 :
1088 1 : void Test::testVolatileFunc()
1089 : {
1090 1 : rtl::OUString aTabName("foo");
1091 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1092 1 : m_pDoc->InsertTab (0, aTabName));
1093 :
1094 1 : double val = 1;
1095 1 : m_pDoc->SetValue(0, 0, 0, val);
1096 1 : m_pDoc->SetString(0, 1, 0, OUString("=IF(A1>0;NOW();0"));
1097 : double now1;
1098 1 : m_pDoc->GetValue(0, 1, 0, now1);
1099 1 : CPPUNIT_ASSERT_MESSAGE("Value of NOW() should be positive.", now1 > 0.0);
1100 :
1101 1 : val = 0;
1102 1 : m_pDoc->SetValue(0, 0, 0, val);
1103 1 : m_pDoc->CalcFormulaTree(false, false);
1104 : double zero;
1105 1 : m_pDoc->GetValue(0, 1, 0, zero);
1106 1 : CPPUNIT_ASSERT_MESSAGE("Result should equal the 3rd parameter of IF, which is zero.", zero == 0.0);
1107 :
1108 1 : val = 1;
1109 1 : m_pDoc->SetValue(0, 0, 0, val);
1110 1 : m_pDoc->CalcFormulaTree(false, false);
1111 : double now2;
1112 1 : m_pDoc->GetValue(0, 1, 0, now2);
1113 1 : CPPUNIT_ASSERT_MESSAGE("Result should be the value of NOW() again.", (now2 - now1) >= 0.0);
1114 :
1115 1 : m_pDoc->DeleteTab(0);
1116 1 : }
1117 :
1118 1 : void Test::testFormulaDepTracking()
1119 : {
1120 1 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet", m_pDoc->InsertTab (0, "foo"));
1121 :
1122 1 : AutoCalcSwitch aACSwitch(m_pDoc, true); // turn on auto calculation.
1123 :
1124 : // B2 listens on D2.
1125 1 : m_pDoc->SetString(1, 1, 0, "=D2");
1126 1 : double val = -999.0; // dummy initial value
1127 1 : m_pDoc->GetValue(1, 1, 0, val);
1128 1 : CPPUNIT_ASSERT_MESSAGE("Referencing an empty cell should yield zero.", val == 0.0);
1129 :
1130 : // Changing the value of D2 should trigger recalculation of B2.
1131 1 : m_pDoc->SetValue(3, 1, 0, 1.1);
1132 1 : m_pDoc->GetValue(1, 1, 0, val);
1133 1 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on value change.", val == 1.1);
1134 :
1135 : // And again.
1136 1 : m_pDoc->SetValue(3, 1, 0, 2.2);
1137 1 : m_pDoc->GetValue(1, 1, 0, val);
1138 1 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on value change.", val == 2.2);
1139 :
1140 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 10, 10, 0));
1141 :
1142 : // Now, let's test the range dependency tracking.
1143 :
1144 : // B2 listens on D2:E6.
1145 1 : m_pDoc->SetString(1, 1, 0, "=SUM(D2:E6)");
1146 1 : m_pDoc->GetValue(1, 1, 0, val);
1147 1 : CPPUNIT_ASSERT_MESSAGE("Summing an empty range should yield zero.", val == 0.0);
1148 :
1149 : // Set value to E3. This should trigger recalc on B2.
1150 1 : m_pDoc->SetValue(4, 2, 0, 2.4);
1151 1 : m_pDoc->GetValue(1, 1, 0, val);
1152 1 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", val == 2.4);
1153 :
1154 : // Set value to D5 to trigger recalc again. Note that this causes an
1155 : // addition of 1.2 + 2.4 which is subject to binary floating point
1156 : // rounding error. We need to use approxEqual to assess its value.
1157 :
1158 1 : m_pDoc->SetValue(3, 4, 0, 1.2);
1159 1 : m_pDoc->GetValue(1, 1, 0, val);
1160 1 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 3.6));
1161 :
1162 : // Change the value of D2 (boundary case).
1163 1 : m_pDoc->SetValue(3, 1, 0, 1.0);
1164 1 : m_pDoc->GetValue(1, 1, 0, val);
1165 1 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 4.6));
1166 :
1167 : // Change the value of E6 (another boundary case).
1168 1 : m_pDoc->SetValue(4, 5, 0, 2.0);
1169 1 : m_pDoc->GetValue(1, 1, 0, val);
1170 1 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 6.6));
1171 :
1172 : // Change the value of D6 (another boundary case).
1173 1 : m_pDoc->SetValue(3, 5, 0, 3.0);
1174 1 : m_pDoc->GetValue(1, 1, 0, val);
1175 1 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 9.6));
1176 :
1177 : // Change the value of E2 (another boundary case).
1178 1 : m_pDoc->SetValue(4, 1, 0, 0.4);
1179 1 : m_pDoc->GetValue(1, 1, 0, val);
1180 1 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 10.0));
1181 :
1182 : // Change the existing non-empty value cell (E2).
1183 1 : m_pDoc->SetValue(4, 1, 0, 2.4);
1184 1 : m_pDoc->GetValue(1, 1, 0, val);
1185 1 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 12.0));
1186 :
1187 1 : clearRange(m_pDoc, ScRange(0, 0, 0, 10, 10, 0));
1188 :
1189 : // Now, column-based dependency tracking. We now switch to the R1C1
1190 : // syntax which is easier to use for repeated relative references.
1191 :
1192 1 : FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1193 :
1194 1 : val = 0.0;
1195 10 : for (SCROW nRow = 1; nRow <= 9; ++nRow)
1196 : {
1197 : // Static value in column 1.
1198 9 : m_pDoc->SetValue(0, nRow, 0, ++val);
1199 :
1200 : // Formula in column 2 that references cell to the left.
1201 9 : m_pDoc->SetString(1, nRow, 0, "=RC[-1]");
1202 :
1203 : // Formula in column 3 that references cell to the left.
1204 9 : m_pDoc->SetString(2, nRow, 0, "=RC[-1]*2");
1205 : }
1206 :
1207 : // Check formula values.
1208 1 : val = 0.0;
1209 10 : for (SCROW nRow = 1; nRow <= 9; ++nRow)
1210 : {
1211 9 : ++val;
1212 9 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(1, nRow, 0) == val);
1213 9 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(2, nRow, 0) == val*2.0);
1214 : }
1215 :
1216 : // Intentionally insert a formula in column 1. This will break column 1's
1217 : // uniformity of consisting only of static value cells.
1218 1 : m_pDoc->SetString(0, 4, 0, "=R2C3");
1219 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(0, 4, 0) == 2.0);
1220 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(1, 4, 0) == 2.0);
1221 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(2, 4, 0) == 4.0);
1222 :
1223 1 : m_pDoc->DeleteTab(0);
1224 1 : }
1225 :
1226 1 : void Test::testFuncParam()
1227 : {
1228 1 : rtl::OUString aTabName("foo");
1229 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1230 1 : m_pDoc->InsertTab (0, aTabName));
1231 :
1232 : // First, the normal case, with no missing parameters.
1233 1 : m_pDoc->SetString(0, 0, 0, OUString("=AVERAGE(1;2;3)"));
1234 1 : m_pDoc->CalcFormulaTree(false, false);
1235 : double val;
1236 1 : m_pDoc->GetValue(0, 0, 0, val);
1237 1 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 2);
1238 :
1239 : // Now function with missing parameters. Missing values should be treated
1240 : // as zeros.
1241 1 : m_pDoc->SetString(0, 0, 0, OUString("=AVERAGE(1;;;)"));
1242 1 : m_pDoc->CalcFormulaTree(false, false);
1243 1 : m_pDoc->GetValue(0, 0, 0, val);
1244 1 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 0.25);
1245 :
1246 : // Conversion of string to numeric argument.
1247 1 : m_pDoc->SetString(0, 0, 0, OUString("=\"\"+3")); // empty string
1248 1 : m_pDoc->SetString(0, 1, 0, OUString("=\" \"+3")); // only blank
1249 1 : m_pDoc->SetString(0, 2, 0, OUString("=\" 4 \"+3")); // number in blanks
1250 1 : m_pDoc->SetString(0, 3, 0, OUString("=\" x \"+3")); // non-numeric => #VALUE! error
1251 :
1252 1 : rtl::OUString aVal;
1253 1 : ScCalcConfig aConfig;
1254 :
1255 : // With "Empty string as zero" option.
1256 1 : aConfig.mbEmptyStringAsZero = true;
1257 1 : ScInterpreter::SetGlobalConfig(aConfig);
1258 1 : m_pDoc->CalcAll();
1259 1 : m_pDoc->GetValue(0, 0, 0, val);
1260 1 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 3);
1261 1 : m_pDoc->GetValue(0, 1, 0, val);
1262 1 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 3);
1263 1 : m_pDoc->GetValue(0, 2, 0, val);
1264 1 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 7);
1265 1 : aVal = m_pDoc->GetString( 0, 3, 0);
1266 1 : CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1267 :
1268 : // Without "Empty string as zero" option.
1269 1 : aConfig.mbEmptyStringAsZero = false;
1270 1 : ScInterpreter::SetGlobalConfig(aConfig);
1271 1 : m_pDoc->CalcAll();
1272 1 : aVal = m_pDoc->GetString( 0, 0, 0);
1273 1 : CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1274 1 : aVal = m_pDoc->GetString( 0, 1, 0);
1275 1 : CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1276 1 : m_pDoc->GetValue(0, 2, 0, val);
1277 1 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 7);
1278 1 : aVal = m_pDoc->GetString( 0, 3, 0);
1279 1 : CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1280 :
1281 1 : m_pDoc->DeleteTab(0);
1282 1 : }
1283 :
1284 1 : void Test::testNamedRange()
1285 : {
1286 : struct {
1287 : const char* pName; const char* pExpr; sal_uInt16 nIndex;
1288 : } aNames[] = {
1289 : { "Divisor", "$Sheet1.$A$1:$A$1048576", 1 },
1290 : { "MyRange1", "$Sheet1.$A$1:$A$100", 2 },
1291 : { "MyRange2", "$Sheet1.$B$1:$B$100", 3 },
1292 : { "MyRange3", "$Sheet1.$C$1:$C$100", 4 }
1293 1 : };
1294 :
1295 1 : rtl::OUString aTabName("Sheet1");
1296 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1297 1 : m_pDoc->InsertTab (0, aTabName));
1298 :
1299 1 : m_pDoc->SetValue (0, 0, 0, 101);
1300 :
1301 1 : ScAddress aA1(0, 0, 0);
1302 1 : ScRangeName* pNewRanges = new ScRangeName();
1303 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
1304 : {
1305 : ScRangeData* pNew = new ScRangeData(
1306 : m_pDoc,
1307 : rtl::OUString::createFromAscii(aNames[i].pName),
1308 : rtl::OUString::createFromAscii(aNames[i].pExpr),
1309 4 : aA1, 0, formula::FormulaGrammar::GRAM_ENGLISH);
1310 4 : pNew->SetIndex(aNames[i].nIndex);
1311 4 : bool bSuccess = pNewRanges->insert(pNew);
1312 4 : CPPUNIT_ASSERT_MESSAGE ("insertion failed", bSuccess);
1313 : }
1314 :
1315 : // Make sure the index lookup does the right thing.
1316 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
1317 : {
1318 4 : const ScRangeData* p = pNewRanges->findByIndex(aNames[i].nIndex);
1319 4 : CPPUNIT_ASSERT_MESSAGE("lookup of range name by index failed.", p);
1320 4 : rtl::OUString aName = p->GetName();
1321 4 : CPPUNIT_ASSERT_MESSAGE("wrong range name is retrieved.", aName.equalsAscii(aNames[i].pName));
1322 4 : }
1323 :
1324 : // Test usage in formula expression.
1325 1 : m_pDoc->SetRangeName(pNewRanges);
1326 1 : m_pDoc->SetString (1, 0, 0, rtl::OUString("=A1/Divisor"));
1327 1 : m_pDoc->CalcAll();
1328 :
1329 : double result;
1330 1 : m_pDoc->GetValue (1, 0, 0, result);
1331 1 : CPPUNIT_ASSERT_MESSAGE ("calculation failed", result == 1.0);
1332 :
1333 : // Test copy-ability of range names.
1334 1 : ScRangeName* pCopiedRanges = new ScRangeName(*pNewRanges);
1335 1 : m_pDoc->SetRangeName(pCopiedRanges);
1336 : // Make sure the index lookup still works.
1337 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
1338 : {
1339 4 : const ScRangeData* p = pCopiedRanges->findByIndex(aNames[i].nIndex);
1340 4 : CPPUNIT_ASSERT_MESSAGE("lookup of range name by index failed with the copied instance.", p);
1341 4 : rtl::OUString aName = p->GetName();
1342 4 : CPPUNIT_ASSERT_MESSAGE("wrong range name is retrieved with the copied instance.", aName.equalsAscii(aNames[i].pName));
1343 4 : }
1344 :
1345 1 : m_pDoc->SetRangeName(NULL); // Delete the names.
1346 1 : m_pDoc->DeleteTab(0);
1347 1 : }
1348 :
1349 1 : void Test::testCSV()
1350 : {
1351 1 : const int English = 0, European = 1;
1352 : struct {
1353 : const char *pStr; int eSep; bool bResult; double nValue;
1354 : } aTests[] = {
1355 : { "foo", English, false, 0.0 },
1356 : { "1.0", English, true, 1.0 },
1357 : { "1,0", English, false, 0.0 },
1358 : { "1.0", European, false, 0.0 },
1359 : { "1.000", European, true, 1000.0 },
1360 : { "1,000", European, true, 1.0 },
1361 : { "1.000", English, true, 1.0 },
1362 : { "1,000", English, true, 1000.0 },
1363 : { " 1.0", English, true, 1.0 },
1364 : { " 1.0 ", English, true, 1.0 },
1365 : { "1.0 ", European, false, 0.0 },
1366 : { "1.000", European, true, 1000.0 },
1367 : { "1137.999", English, true, 1137.999 },
1368 : { "1.000.00", European, false, 0.0 }
1369 1 : };
1370 15 : for (sal_uInt32 i = 0; i < SAL_N_ELEMENTS(aTests); i++) {
1371 14 : rtl::OUString aStr(aTests[i].pStr, strlen (aTests[i].pStr), RTL_TEXTENCODING_UTF8);
1372 14 : double nValue = 0.0;
1373 : bool bResult = ScStringUtil::parseSimpleNumber
1374 : (aStr, aTests[i].eSep == English ? '.' : ',',
1375 : aTests[i].eSep == English ? ',' : '.',
1376 14 : nValue);
1377 14 : CPPUNIT_ASSERT_MESSAGE ("CSV numeric detection failure", bResult == aTests[i].bResult);
1378 14 : CPPUNIT_ASSERT_MESSAGE ("CSV numeric value failure", nValue == aTests[i].nValue);
1379 14 : }
1380 1 : }
1381 :
1382 : template<typename Evaluator>
1383 4 : void checkMatrixElements(const ScMatrix& rMat)
1384 : {
1385 : SCSIZE nC, nR;
1386 4 : rMat.GetDimensions(nC, nR);
1387 : Evaluator aEval;
1388 32 : for (SCSIZE i = 0; i < nC; ++i)
1389 : {
1390 508 : for (SCSIZE j = 0; j < nR; ++j)
1391 : {
1392 480 : aEval(i, j, rMat.Get(i, j));
1393 : }
1394 : }
1395 4 : }
1396 :
1397 : struct AllZeroMatrix
1398 : {
1399 40 : void operator() (SCSIZE /*nCol*/, SCSIZE /*nRow*/, const ScMatrixValue& rVal) const
1400 : {
1401 40 : CPPUNIT_ASSERT_MESSAGE("element is not of numeric type", rVal.nType == SC_MATVAL_VALUE);
1402 40 : CPPUNIT_ASSERT_MESSAGE("element value must be zero", rVal.fVal == 0.0);
1403 40 : }
1404 : };
1405 :
1406 : struct PartiallyFilledZeroMatrix
1407 : {
1408 40 : void operator() (SCSIZE nCol, SCSIZE nRow, const ScMatrixValue& rVal) const
1409 : {
1410 40 : CPPUNIT_ASSERT_MESSAGE("element is not of numeric type", rVal.nType == SC_MATVAL_VALUE);
1411 40 : if (1 <= nCol && nCol <= 2 && 2 <= nRow && nRow <= 8)
1412 : {
1413 14 : CPPUNIT_ASSERT_MESSAGE("element value must be 3.0", rVal.fVal == 3.0);
1414 : }
1415 : else
1416 : {
1417 26 : CPPUNIT_ASSERT_MESSAGE("element value must be zero", rVal.fVal == 0.0);
1418 : }
1419 40 : }
1420 : };
1421 :
1422 : struct AllEmptyMatrix
1423 : {
1424 200 : void operator() (SCSIZE /*nCol*/, SCSIZE /*nRow*/, const ScMatrixValue& rVal) const
1425 : {
1426 200 : CPPUNIT_ASSERT_MESSAGE("element is not of empty type", rVal.nType == SC_MATVAL_EMPTY);
1427 200 : CPPUNIT_ASSERT_MESSAGE("value of \"empty\" element is expected to be zero", rVal.fVal == 0.0);
1428 200 : }
1429 : };
1430 :
1431 : struct PartiallyFilledEmptyMatrix
1432 : {
1433 200 : void operator() (SCSIZE nCol, SCSIZE nRow, const ScMatrixValue& rVal) const
1434 : {
1435 200 : if (nCol == 1 && nRow == 1)
1436 : {
1437 1 : CPPUNIT_ASSERT_MESSAGE("element is not of boolean type", rVal.nType == SC_MATVAL_BOOLEAN);
1438 1 : CPPUNIT_ASSERT_MESSAGE("element value is not what is expected", rVal.fVal == 1.0);
1439 : }
1440 199 : else if (nCol == 4 && nRow == 5)
1441 : {
1442 1 : CPPUNIT_ASSERT_MESSAGE("element is not of value type", rVal.nType == SC_MATVAL_VALUE);
1443 1 : CPPUNIT_ASSERT_MESSAGE("element value is not what is expected", rVal.fVal == -12.5);
1444 : }
1445 198 : else if (nCol == 8 && nRow == 2)
1446 : {
1447 1 : CPPUNIT_ASSERT_MESSAGE("element is not of value type", rVal.nType == SC_MATVAL_STRING);
1448 1 : CPPUNIT_ASSERT_MESSAGE("element value is not what is expected", rVal.aStr == "Test");
1449 : }
1450 197 : else if (nCol == 8 && nRow == 11)
1451 : {
1452 1 : CPPUNIT_ASSERT_MESSAGE("element is not of empty path type", rVal.nType == SC_MATVAL_EMPTYPATH);
1453 1 : CPPUNIT_ASSERT_MESSAGE("value of \"empty\" element is expected to be zero", rVal.fVal == 0.0);
1454 : }
1455 : else
1456 : {
1457 196 : CPPUNIT_ASSERT_MESSAGE("element is not of empty type", rVal.nType == SC_MATVAL_EMPTY);
1458 196 : CPPUNIT_ASSERT_MESSAGE("value of \"empty\" element is expected to be zero", rVal.fVal == 0.0);
1459 : }
1460 200 : }
1461 : };
1462 :
1463 1 : void Test::testMatrix()
1464 : {
1465 1 : ScMatrixRef pMat;
1466 :
1467 : // First, test the zero matrix type.
1468 1 : pMat = new ScMatrix(0, 0, 0.0);
1469 : SCSIZE nC, nR;
1470 1 : pMat->GetDimensions(nC, nR);
1471 1 : CPPUNIT_ASSERT_MESSAGE("matrix is not empty", nC == 0 && nR == 0);
1472 1 : pMat->Resize(4, 10, 0.0);
1473 1 : pMat->GetDimensions(nC, nR);
1474 1 : CPPUNIT_ASSERT_MESSAGE("matrix size is not as expected", nC == 4 && nR == 10);
1475 2 : CPPUNIT_ASSERT_MESSAGE("both 'and' and 'or' should evaluate to false",
1476 1 : !pMat->And() && !pMat->Or());
1477 :
1478 : // Resizing into a larger matrix should fill the void space with zeros.
1479 1 : checkMatrixElements<AllZeroMatrix>(*pMat);
1480 :
1481 1 : pMat->FillDouble(3.0, 1, 2, 2, 8);
1482 1 : checkMatrixElements<PartiallyFilledZeroMatrix>(*pMat);
1483 1 : CPPUNIT_ASSERT_MESSAGE("matrix is expected to be numeric", pMat->IsNumeric());
1484 2 : CPPUNIT_ASSERT_MESSAGE("partially non-zero matrix should evaluate false on 'and' and true on 'or",
1485 1 : !pMat->And() && pMat->Or());
1486 1 : pMat->FillDouble(5.0, 0, 0, nC-1, nR-1);
1487 2 : CPPUNIT_ASSERT_MESSAGE("fully non-zero matrix should evaluate true both on 'and' and 'or",
1488 1 : pMat->And() && pMat->Or());
1489 :
1490 : // Test the AND and OR evaluations.
1491 1 : pMat = new ScMatrix(2, 2, 0.0);
1492 :
1493 : // Only some of the elements are non-zero.
1494 1 : pMat->PutBoolean(true, 0, 0);
1495 1 : pMat->PutDouble(1.0, 1, 1);
1496 1 : CPPUNIT_ASSERT_MESSAGE("incorrect OR result", pMat->Or());
1497 1 : CPPUNIT_ASSERT_MESSAGE("incorrect AND result", !pMat->And());
1498 :
1499 : // All of the elements are non-zero.
1500 1 : pMat->PutBoolean(true, 0, 1);
1501 1 : pMat->PutDouble(2.3, 1, 0);
1502 1 : CPPUNIT_ASSERT_MESSAGE("incorrect OR result", pMat->Or());
1503 1 : CPPUNIT_ASSERT_MESSAGE("incorrect AND result", pMat->And());
1504 :
1505 : // Now test the emtpy matrix type.
1506 1 : pMat = new ScMatrix(10, 20);
1507 1 : pMat->GetDimensions(nC, nR);
1508 1 : CPPUNIT_ASSERT_MESSAGE("matrix size is not as expected", nC == 10 && nR == 20);
1509 1 : checkMatrixElements<AllEmptyMatrix>(*pMat);
1510 :
1511 1 : pMat->PutBoolean(true, 1, 1);
1512 1 : pMat->PutDouble(-12.5, 4, 5);
1513 1 : rtl::OUString aStr("Test");
1514 1 : pMat->PutString(aStr, 8, 2);
1515 1 : pMat->PutEmptyPath(8, 11);
1516 1 : checkMatrixElements<PartiallyFilledEmptyMatrix>(*pMat);
1517 1 : }
1518 :
1519 1 : void Test::testEnterMixedMatrix()
1520 : {
1521 1 : m_pDoc->InsertTab(0, "foo");
1522 :
1523 : // Insert the source values in A1:B2.
1524 1 : m_pDoc->SetString(0, 0, 0, "A");
1525 1 : m_pDoc->SetString(1, 0, 0, "B");
1526 1 : double val = 1.0;
1527 1 : m_pDoc->SetValue(0, 1, 0, val);
1528 1 : val = 2.0;
1529 1 : m_pDoc->SetValue(1, 1, 0, val);
1530 :
1531 : // Create a matrix range in A4:B5 referencing A1:B2.
1532 1 : ScMarkData aMark;
1533 1 : aMark.SelectOneTable(0);
1534 1 : m_pDoc->InsertMatrixFormula(0, 3, 1, 4, aMark, "=A1:B2", NULL);
1535 :
1536 1 : CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(0,0,0), m_pDoc->GetString(0,3,0));
1537 1 : CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(1,0,0), m_pDoc->GetString(1,3,0));
1538 1 : CPPUNIT_ASSERT_EQUAL(m_pDoc->GetValue(0,1,0), m_pDoc->GetValue(0,4,0));
1539 1 : CPPUNIT_ASSERT_EQUAL(m_pDoc->GetValue(1,1,0), m_pDoc->GetValue(1,4,0));
1540 :
1541 1 : m_pDoc->DeleteTab(0);
1542 1 : }
1543 :
1544 : namespace {
1545 :
1546 : struct DPFieldDef
1547 : {
1548 : const char* pName;
1549 : sheet::DataPilotFieldOrientation eOrient;
1550 :
1551 : /**
1552 : * Function for data field. It's used only for data field. When 0, the
1553 : * default function (SUM) is used.
1554 : */
1555 : int eFunc;
1556 : };
1557 :
1558 : template<size_t _Size>
1559 5 : ScRange insertDPSourceData(ScDocument* pDoc, DPFieldDef aFields[], size_t nFieldCount, const char* aData[][_Size], size_t nDataCount)
1560 : {
1561 : // Insert field names in row 0.
1562 22 : for (size_t i = 0; i < nFieldCount; ++i)
1563 17 : pDoc->SetString(static_cast<SCCOL>(i), 0, 0, OUString(aFields[i].pName, strlen(aFields[i].pName), RTL_TEXTENCODING_UTF8));
1564 :
1565 : // Insert data into row 1 and downward.
1566 31 : for (size_t i = 0; i < nDataCount; ++i)
1567 : {
1568 26 : SCROW nRow = static_cast<SCROW>(i) + 1;
1569 120 : for (size_t j = 0; j < nFieldCount; ++j)
1570 : {
1571 94 : SCCOL nCol = static_cast<SCCOL>(j);
1572 94 : pDoc->SetString(
1573 : nCol, nRow, 0, OUString(aData[i][j], strlen(aData[i][j]), RTL_TEXTENCODING_UTF8));
1574 : }
1575 : }
1576 :
1577 5 : SCROW nRow1 = 0, nRow2 = 0;
1578 5 : SCCOL nCol1 = 0, nCol2 = 0;
1579 5 : pDoc->GetDataArea(0, nCol1, nRow1, nCol2, nRow2, true, false);
1580 5 : CPPUNIT_ASSERT_MESSAGE("Data is expected to start from (col=0,row=0).", nCol1 == 0 && nRow1 == 0);
1581 5 : CPPUNIT_ASSERT_MESSAGE("Unexpected data range.",
1582 : nCol2 == static_cast<SCCOL>(nFieldCount - 1) && nRow2 == static_cast<SCROW>(nDataCount));
1583 :
1584 5 : ScRange aSrcRange(nCol1, nRow1, 0, nCol2, nRow2, 0);
1585 5 : printRange(pDoc, aSrcRange, "Data sheet content");
1586 5 : return aSrcRange;
1587 : }
1588 :
1589 : template<size_t _Size>
1590 24 : bool checkDPTableOutput(ScDocument* pDoc, const ScRange& aOutRange, const char* aOutputCheck[][_Size], const char* pCaption)
1591 : {
1592 24 : bool bResult = true;
1593 24 : const ScAddress& s = aOutRange.aStart;
1594 24 : const ScAddress& e = aOutRange.aEnd;
1595 24 : SheetPrinter printer(e.Row() - s.Row() + 1, e.Col() - s.Col() + 1);
1596 24 : SCROW nOutRowSize = e.Row() - s.Row() + 1;
1597 24 : SCCOL nOutColSize = e.Col() - s.Col() + 1;
1598 191 : for (SCROW nRow = 0; nRow < nOutRowSize; ++nRow)
1599 : {
1600 697 : for (SCCOL nCol = 0; nCol < nOutColSize; ++nCol)
1601 : {
1602 530 : OUString aVal;
1603 530 : pDoc->GetString(nCol + s.Col(), nRow + s.Row(), s.Tab(), aVal);
1604 530 : printer.set(nRow, nCol, aVal);
1605 530 : const char* p = aOutputCheck[nRow][nCol];
1606 530 : if (p)
1607 : {
1608 399 : OUString aCheckVal = OUString::createFromAscii(p);
1609 399 : bool bEqual = aCheckVal.equals(aVal);
1610 399 : if (!bEqual)
1611 : {
1612 0 : cout << "Expected: " << aCheckVal << " Actual: " << aVal << endl;
1613 0 : bResult = false;
1614 : }
1615 : }
1616 131 : else if (!aVal.isEmpty())
1617 : {
1618 0 : cout << "Empty cell expected" << endl;
1619 0 : bResult = false;
1620 : }
1621 : }
1622 : }
1623 24 : printer.print(pCaption);
1624 24 : return bResult;
1625 : }
1626 :
1627 14 : ScDPObject* createDPFromSourceDesc(
1628 : ScDocument* pDoc, const ScSheetSourceDesc& rDesc, DPFieldDef aFields[], size_t nFieldCount,
1629 : bool bFilterButton)
1630 : {
1631 14 : ScDPObject* pDPObj = new ScDPObject(pDoc);
1632 14 : pDPObj->SetSheetDesc(rDesc);
1633 14 : pDPObj->SetOutRange(ScAddress(0, 0, 1));
1634 :
1635 14 : ScDPSaveData aSaveData;
1636 : // Set data pilot table output options.
1637 14 : aSaveData.SetIgnoreEmptyRows(false);
1638 14 : aSaveData.SetRepeatIfEmpty(false);
1639 14 : aSaveData.SetColumnGrand(true);
1640 14 : aSaveData.SetRowGrand(true);
1641 14 : aSaveData.SetFilterButton(bFilterButton);
1642 14 : aSaveData.SetDrillDown(true);
1643 :
1644 : // Check the sanity of the source range.
1645 14 : const ScRange& rSrcRange = rDesc.GetSourceRange();
1646 14 : SCROW nRow1 = rSrcRange.aStart.Row();
1647 14 : SCROW nRow2 = rSrcRange.aEnd.Row();
1648 14 : CPPUNIT_ASSERT_MESSAGE("source range contains no data!", nRow2 - nRow1 > 1);
1649 :
1650 : // Set the dimension information.
1651 51 : for (size_t i = 0; i < nFieldCount; ++i)
1652 : {
1653 37 : OUString aDimName = rtl::OUString::createFromAscii(aFields[i].pName);
1654 37 : ScDPSaveDimension* pDim = aSaveData.GetNewDimensionByName(aDimName);
1655 37 : pDim->SetOrientation(static_cast<sal_uInt16>(aFields[i].eOrient));
1656 37 : pDim->SetUsedHierarchy(0);
1657 :
1658 37 : if (aFields[i].eOrient == sheet::DataPilotFieldOrientation_DATA)
1659 : {
1660 16 : sheet::GeneralFunction eFunc = sheet::GeneralFunction_SUM;
1661 16 : if (aFields[i].eFunc)
1662 9 : eFunc = static_cast<sheet::GeneralFunction>(aFields[i].eFunc);
1663 :
1664 16 : pDim->SetFunction(eFunc);
1665 16 : pDim->SetReferenceValue(NULL);
1666 : }
1667 : else
1668 : {
1669 21 : sheet::DataPilotFieldSortInfo aSortInfo;
1670 21 : aSortInfo.IsAscending = true;
1671 21 : aSortInfo.Mode = 2;
1672 21 : pDim->SetSortInfo(&aSortInfo);
1673 :
1674 21 : sheet::DataPilotFieldLayoutInfo aLayInfo;
1675 21 : aLayInfo.LayoutMode = 0;
1676 21 : aLayInfo.AddEmptyLines = false;
1677 21 : pDim->SetLayoutInfo(&aLayInfo);
1678 21 : sheet::DataPilotFieldAutoShowInfo aShowInfo;
1679 21 : aShowInfo.IsEnabled = false;
1680 21 : aShowInfo.ShowItemsMode = 0;
1681 21 : aShowInfo.ItemCount = 0;
1682 21 : pDim->SetAutoShowInfo(&aShowInfo);
1683 : }
1684 37 : }
1685 :
1686 : // Don't forget the data layout dimension.
1687 14 : ScDPSaveDimension* pDim = aSaveData.GetDataLayoutDimension();
1688 14 : pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
1689 14 : pDim->SetShowEmpty(true);
1690 :
1691 14 : pDPObj->SetSaveData(aSaveData);
1692 14 : pDPObj->SetAlive(true);
1693 14 : pDPObj->InvalidateData();
1694 :
1695 14 : return pDPObj;
1696 : }
1697 :
1698 13 : ScDPObject* createDPFromRange(
1699 : ScDocument* pDoc, const ScRange& rRange, DPFieldDef aFields[], size_t nFieldCount,
1700 : bool bFilterButton)
1701 : {
1702 13 : ScSheetSourceDesc aSheetDesc(pDoc);
1703 13 : aSheetDesc.SetSourceRange(rRange);
1704 13 : return createDPFromSourceDesc(pDoc, aSheetDesc, aFields, nFieldCount, bFilterButton);
1705 : }
1706 :
1707 22 : ScRange refresh(ScDPObject* pDPObj)
1708 : {
1709 22 : bool bOverFlow = false;
1710 22 : ScRange aOutRange = pDPObj->GetNewOutputRange(bOverFlow);
1711 22 : CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverFlow);
1712 :
1713 22 : pDPObj->Output(aOutRange.aStart);
1714 22 : aOutRange = pDPObj->GetOutRange();
1715 22 : return aOutRange;
1716 : }
1717 :
1718 4 : ScRange refreshGroups(ScDPCollection* pDPs, ScDPObject* pDPObj)
1719 : {
1720 : // We need to first create group data in the cache, then the group data in
1721 : // the object.
1722 4 : std::set<ScDPObject*> aRefs;
1723 4 : bool bSuccess = pDPs->ReloadGroupsInCache(pDPObj, aRefs);
1724 4 : CPPUNIT_ASSERT_MESSAGE("Failed to reload group data in cache.", bSuccess);
1725 4 : CPPUNIT_ASSERT_MESSAGE("There should be only one table linked to this cache.", aRefs.size() == 1);
1726 4 : pDPObj->ReloadGroupTableData();
1727 :
1728 4 : return refresh(pDPObj);
1729 : }
1730 :
1731 : }
1732 :
1733 1 : void Test::testPivotTable()
1734 : {
1735 1 : m_pDoc->InsertTab(0, OUString("Data"));
1736 1 : m_pDoc->InsertTab(1, OUString("Table"));
1737 :
1738 : // Dimension definition
1739 : DPFieldDef aFields[] = {
1740 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
1741 : { "Group", sheet::DataPilotFieldOrientation_COLUMN, 0 },
1742 : { "Score", sheet::DataPilotFieldOrientation_DATA, 0 }
1743 1 : };
1744 :
1745 : // Raw data
1746 : const char* aData[][3] = {
1747 : { "Andy", "A", "30" },
1748 : { "Bruce", "A", "20" },
1749 : { "Charlie", "B", "45" },
1750 : { "David", "B", "12" },
1751 : { "Edward", "C", "8" },
1752 : { "Frank", "C", "15" },
1753 1 : };
1754 :
1755 1 : size_t nFieldCount = SAL_N_ELEMENTS(aFields);
1756 1 : size_t nDataCount = SAL_N_ELEMENTS(aData);
1757 :
1758 1 : ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
1759 1 : SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
1760 1 : SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
1761 :
1762 : ScDPObject* pDPObj = createDPFromRange(
1763 1 : m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
1764 :
1765 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1766 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
1767 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
1768 2 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
1769 1 : pDPs->GetCount() == 1);
1770 1 : pDPObj->SetName(pDPs->CreateNewName());
1771 :
1772 1 : bool bOverFlow = false;
1773 1 : ScRange aOutRange = pDPObj->GetNewOutputRange(bOverFlow);
1774 1 : CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverFlow);
1775 :
1776 1 : pDPObj->Output(aOutRange.aStart);
1777 1 : aOutRange = pDPObj->GetOutRange();
1778 : {
1779 : // Expected output table content. 0 = empty cell
1780 : const char* aOutputCheck[][5] = {
1781 : { "Sum - Score", "Group", 0, 0, 0 },
1782 : { "Name", "A", "B", "C", "Total Result" },
1783 : { "Andy", "30", 0, 0, "30" },
1784 : { "Bruce", "20", 0, 0, "20" },
1785 : { "Charlie", 0, "45", 0, "45" },
1786 : { "David", 0, "12", 0, "12" },
1787 : { "Edward", 0, 0, "8", "8" },
1788 : { "Frank", 0, 0, "15", "15" },
1789 : { "Total Result", "50", "57", "23", "130" }
1790 1 : };
1791 :
1792 1 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
1793 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1794 : }
1795 1 : CPPUNIT_ASSERT_MESSAGE("There should be only one data cache.", pDPs->GetSheetCaches().size() == 1);
1796 :
1797 : // Update the cell values.
1798 1 : double aData2[] = { 100, 200, 300, 400, 500, 600 };
1799 7 : for (size_t i = 0; i < SAL_N_ELEMENTS(aData2); ++i)
1800 : {
1801 6 : SCROW nRow = i + 1;
1802 6 : m_pDoc->SetValue(2, nRow, 0, aData2[i]);
1803 : }
1804 :
1805 1 : printRange(m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), "Data sheet content (modified)");
1806 :
1807 : // Now, create a copy of the datapilot object for the updated table, but
1808 : // don't reload the cache which should force the copy to use the old data
1809 : // from the cache.
1810 1 : ScDPObject* pDPObj2 = new ScDPObject(*pDPObj);
1811 1 : pDPs->InsertNewTable(pDPObj2);
1812 :
1813 1 : aOutRange = pDPObj2->GetOutRange();
1814 1 : pDPObj2->ClearTableData();
1815 1 : pDPObj2->Output(aOutRange.aStart);
1816 : {
1817 : // Expected output table content. 0 = empty cell
1818 : const char* aOutputCheck[][5] = {
1819 : { "Sum - Score", "Group", 0, 0, 0 },
1820 : { "Name", "A", "B", "C", "Total Result" },
1821 : { "Andy", "30", 0, 0, "30" },
1822 : { "Bruce", "20", 0, 0, "20" },
1823 : { "Charlie", 0, "45", 0, "45" },
1824 : { "David", 0, "12", 0, "12" },
1825 : { "Edward", 0, 0, "8", "8" },
1826 : { "Frank", 0, 0, "15", "15" },
1827 : { "Total Result", "50", "57", "23", "130" }
1828 1 : };
1829 :
1830 1 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (from old cache)");
1831 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1832 : }
1833 :
1834 1 : CPPUNIT_ASSERT_MESSAGE("There should be only one data cache.", pDPs->GetSheetCaches().size() == 1);
1835 :
1836 : // Free the first datapilot object after the 2nd one gets reloaded, to
1837 : // prevent the data cache from being deleted before the reload.
1838 1 : pDPs->FreeTable(pDPObj);
1839 :
1840 1 : CPPUNIT_ASSERT_MESSAGE("There should be only one data cache.", pDPs->GetSheetCaches().size() == 1);
1841 :
1842 : // This time clear the cache to refresh the data from the source range.
1843 1 : CPPUNIT_ASSERT_MESSAGE("This datapilot should be based on sheet data.", pDPObj2->IsSheetData());
1844 1 : std::set<ScDPObject*> aRefs;
1845 1 : sal_uLong nErrId = pDPs->ReloadCache(pDPObj2, aRefs);
1846 1 : CPPUNIT_ASSERT_MESSAGE("Cache reload failed.", nErrId == 0);
1847 2 : CPPUNIT_ASSERT_MESSAGE("Reloading a cache shouldn't remove any cache.",
1848 1 : pDPs->GetSheetCaches().size() == 1);
1849 :
1850 1 : pDPObj2->ClearTableData();
1851 1 : pDPObj2->Output(aOutRange.aStart);
1852 :
1853 : {
1854 : // Expected output table content. 0 = empty cell
1855 : const char* aOutputCheck[][5] = {
1856 : { "Sum - Score", "Group", 0, 0, 0 },
1857 : { "Name", "A", "B", "C", "Total Result" },
1858 : { "Andy", "100", 0, 0, "100" },
1859 : { "Bruce", "200", 0, 0, "200" },
1860 : { "Charlie", 0, "300", 0, "300" },
1861 : { "David", 0, "400", 0, "400" },
1862 : { "Edward", 0, 0, "500", "500" },
1863 : { "Frank", 0, 0, "600", "600" },
1864 : { "Total Result", "300", "700", "1100", "2100" }
1865 1 : };
1866 :
1867 1 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (refreshed)");
1868 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1869 : }
1870 :
1871 1 : CPPUNIT_ASSERT_MESSAGE("Cache should be here.", pDPs->GetSheetCaches().hasCache(aSrcRange));
1872 :
1873 : // Swap the two sheets.
1874 1 : m_pDoc->MoveTab(1, 0);
1875 2 : CPPUNIT_ASSERT_MESSAGE("Swapping the sheets shouldn't remove the cache.",
1876 1 : pDPs->GetSheetCaches().size() == 1);
1877 1 : CPPUNIT_ASSERT_MESSAGE("Cache should have moved.", !pDPs->GetSheetCaches().hasCache(aSrcRange));
1878 1 : aSrcRange.aStart.SetTab(1);
1879 1 : aSrcRange.aEnd.SetTab(1);
1880 1 : CPPUNIT_ASSERT_MESSAGE("Cache should be here.", pDPs->GetSheetCaches().hasCache(aSrcRange));
1881 :
1882 1 : pDPs->FreeTable(pDPObj2);
1883 2 : CPPUNIT_ASSERT_MESSAGE("There shouldn't be any data pilot table stored with the document.",
1884 1 : pDPs->GetCount() == 0);
1885 :
1886 2 : CPPUNIT_ASSERT_MESSAGE("There shouldn't be any more data cache.",
1887 1 : pDPs->GetSheetCaches().size() == 0);
1888 :
1889 : // Insert a brand new pivot table object once again, but this time, don't
1890 : // create the output to avoid creating a data cache.
1891 1 : m_pDoc->DeleteTab(1);
1892 1 : m_pDoc->InsertTab(1, OUString("Table"));
1893 :
1894 : pDPObj = createDPFromRange(
1895 1 : m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
1896 1 : bSuccess = pDPs->InsertNewTable(pDPObj);
1897 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
1898 2 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
1899 1 : pDPs->GetCount() == 1);
1900 1 : pDPObj->SetName(pDPs->CreateNewName());
1901 2 : CPPUNIT_ASSERT_MESSAGE("Data cache shouldn't exist yet before creating the table output.",
1902 1 : pDPs->GetSheetCaches().size() == 0);
1903 :
1904 : // Now, "refresh" the table. This should still return a reference to self
1905 : // even with the absence of data cache.
1906 1 : aRefs.clear();
1907 1 : pDPs->ReloadCache(pDPObj, aRefs);
1908 2 : CPPUNIT_ASSERT_MESSAGE("It should return the same object as a reference.",
1909 1 : aRefs.size() == 1 && *aRefs.begin() == pDPObj);
1910 :
1911 1 : pDPs->FreeTable(pDPObj);
1912 :
1913 1 : m_pDoc->DeleteTab(1);
1914 1 : m_pDoc->DeleteTab(0);
1915 1 : }
1916 :
1917 1 : void Test::testPivotTableLabels()
1918 : {
1919 1 : m_pDoc->InsertTab(0, OUString("Data"));
1920 1 : m_pDoc->InsertTab(1, OUString("Table"));
1921 :
1922 : // Dimension definition
1923 : DPFieldDef aFields[] = {
1924 : { "Software", sheet::DataPilotFieldOrientation_ROW, 0 },
1925 : { "Version", sheet::DataPilotFieldOrientation_COLUMN, 0 },
1926 : { "1.2.3", sheet::DataPilotFieldOrientation_DATA, 0 }
1927 1 : };
1928 :
1929 : // Raw data
1930 : const char* aData[][3] = {
1931 : { "LibreOffice", "3.3.0", "30" },
1932 : { "LibreOffice", "3.3.1", "20" },
1933 : { "LibreOffice", "3.4.0", "45" },
1934 1 : };
1935 :
1936 1 : size_t nFieldCount = SAL_N_ELEMENTS(aFields);
1937 1 : size_t nDataCount = SAL_N_ELEMENTS(aData);
1938 :
1939 1 : ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
1940 1 : SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
1941 1 : SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
1942 :
1943 : ScDPObject* pDPObj = createDPFromRange(
1944 1 : m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
1945 :
1946 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1947 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
1948 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
1949 2 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
1950 1 : pDPs->GetCount() == 1);
1951 1 : pDPObj->SetName(pDPs->CreateNewName());
1952 :
1953 1 : ScRange aOutRange = refresh(pDPObj);
1954 : {
1955 : // Expected output table content. 0 = empty cell
1956 : const char* aOutputCheck[][5] = {
1957 : { "Sum - 1.2.3", "Version", 0, 0, 0 },
1958 : { "Software", "3.3.0", "3.3.1", "3.4.0", "Total Result" },
1959 : { "LibreOffice", "30", "20", "45", "95" },
1960 : { "Total Result", "30", "20", "45", "95" }
1961 1 : };
1962 :
1963 1 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
1964 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1965 : }
1966 :
1967 1 : pDPs->FreeTable(pDPObj);
1968 :
1969 1 : m_pDoc->DeleteTab(1);
1970 1 : m_pDoc->DeleteTab(0);
1971 1 : }
1972 :
1973 1 : void Test::testPivotTableDateLabels()
1974 : {
1975 1 : m_pDoc->InsertTab(0, OUString("Data"));
1976 1 : m_pDoc->InsertTab(1, OUString("Table"));
1977 :
1978 : // Dimension definition
1979 : DPFieldDef aFields[] = {
1980 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
1981 : { "Date", sheet::DataPilotFieldOrientation_COLUMN, 0 },
1982 : { "Value", sheet::DataPilotFieldOrientation_DATA, 0 }
1983 1 : };
1984 :
1985 : // Raw data
1986 : const char* aData[][3] = {
1987 : { "Zena", "2011-1-1", "30" },
1988 : { "Yodel", "2011-1-2", "20" },
1989 : { "Xavior", "2011-1-3", "45" }
1990 1 : };
1991 :
1992 1 : size_t nFieldCount = SAL_N_ELEMENTS(aFields);
1993 1 : size_t nDataCount = SAL_N_ELEMENTS(aData);
1994 :
1995 1 : ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
1996 1 : SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
1997 1 : SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
1998 :
1999 : ScDPObject* pDPObj = createDPFromRange(
2000 1 : m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
2001 :
2002 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2003 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2004 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
2005 2 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2006 1 : pDPs->GetCount() == 1);
2007 1 : pDPObj->SetName(pDPs->CreateNewName());
2008 :
2009 1 : ScRange aOutRange = refresh(pDPObj);
2010 : {
2011 : // Expected output table content. 0 = empty cell
2012 : const char* aOutputCheck[][5] = {
2013 : { "Sum - Value", "Date", 0, 0, 0 },
2014 : { "Name", "2011-01-01", "2011-01-02", "2011-01-03", "Total Result" },
2015 : { "Xavior", 0, 0, "45", "45" },
2016 : { "Yodel", 0, "20", 0, "20" },
2017 : { "Zena", "30", 0, 0, "30" },
2018 : { "Total Result", "30", "20", "45", "95" }
2019 1 : };
2020 :
2021 1 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2022 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2023 : }
2024 :
2025 : {
2026 : const char* aChecks[] = {
2027 : "2011-01-01", "2011-01-02", "2011-01-03"
2028 1 : };
2029 :
2030 : // Make sure those cells that contain dates are numeric.
2031 1 : SCROW nRow = aOutRange.aStart.Row() + 1;
2032 1 : nCol1 = aOutRange.aStart.Col() + 1;
2033 1 : nCol2 = nCol1 + 2;
2034 4 : for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
2035 : {
2036 3 : OUString aVal = m_pDoc->GetString(nCol, nRow, 1);
2037 3 : CPPUNIT_ASSERT_MESSAGE("Cell value is not as expected.", aVal.equalsAscii(aChecks[nCol-nCol1]));
2038 6 : CPPUNIT_ASSERT_MESSAGE("This cell contains a date value and is supposed to be numeric.",
2039 3 : m_pDoc->HasValueData(nCol, nRow, 1));
2040 3 : }
2041 : }
2042 :
2043 1 : pDPs->FreeTable(pDPObj);
2044 :
2045 1 : m_pDoc->DeleteTab(1);
2046 1 : m_pDoc->DeleteTab(0);
2047 1 : }
2048 :
2049 1 : void Test::testPivotTableFilters()
2050 : {
2051 1 : m_pDoc->InsertTab(0, OUString("Data"));
2052 1 : m_pDoc->InsertTab(1, OUString("Table"));
2053 :
2054 : // Dimension definition
2055 : DPFieldDef aFields[] = {
2056 : { "Name", sheet::DataPilotFieldOrientation_HIDDEN, 0 },
2057 : { "Group1", sheet::DataPilotFieldOrientation_HIDDEN, 0 },
2058 : { "Group2", sheet::DataPilotFieldOrientation_PAGE, 0 },
2059 : { "Val1", sheet::DataPilotFieldOrientation_DATA, 0 },
2060 : { "Val2", sheet::DataPilotFieldOrientation_DATA, 0 }
2061 1 : };
2062 :
2063 : // Raw data
2064 : const char* aData[][5] = {
2065 : { "A", "1", "A", "1", "10" },
2066 : { "B", "1", "A", "1", "10" },
2067 : { "C", "1", "B", "1", "10" },
2068 : { "D", "1", "B", "1", "10" },
2069 : { "E", "2", "A", "1", "10" },
2070 : { "F", "2", "A", "1", "10" },
2071 : { "G", "2", "B", "1", "10" },
2072 : { "H", "2", "B", "1", "10" }
2073 1 : };
2074 :
2075 1 : size_t nFieldCount = SAL_N_ELEMENTS(aFields);
2076 1 : size_t nDataCount = SAL_N_ELEMENTS(aData);
2077 :
2078 1 : ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
2079 1 : SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
2080 1 : SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
2081 :
2082 : ScDPObject* pDPObj = createDPFromRange(
2083 1 : m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, true);
2084 :
2085 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2086 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2087 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
2088 2 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2089 1 : pDPs->GetCount() == 1);
2090 1 : pDPObj->SetName(pDPs->CreateNewName());
2091 :
2092 1 : ScRange aOutRange = refresh(pDPObj);
2093 : {
2094 : // Expected output table content. 0 = empty cell
2095 : const char* aOutputCheck[][2] = {
2096 : { "Filter", 0 },
2097 : { "Group2", "- all -" },
2098 : { 0, 0 },
2099 : { "Data", 0 },
2100 : { "Sum - Val1", "8" },
2101 : { "Sum - Val2", "80" }
2102 1 : };
2103 :
2104 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (unfiltered)");
2105 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2106 : }
2107 :
2108 1 : AutoCalcSwitch aACSwitch(m_pDoc, true); // turn on auto calculation.
2109 :
2110 1 : ScAddress aFormulaAddr = aOutRange.aEnd;
2111 1 : aFormulaAddr.IncRow(2);
2112 2 : m_pDoc->SetString(aFormulaAddr.Col(), aFormulaAddr.Row(), aFormulaAddr.Tab(),
2113 3 : rtl::OUString("=B6"));
2114 1 : double fTest = m_pDoc->GetValue(aFormulaAddr);
2115 1 : CPPUNIT_ASSERT_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", fTest == 80.0);
2116 :
2117 : // Set current page of 'Group2' to 'A'.
2118 1 : pDPObj->BuildAllDimensionMembers();
2119 1 : ScDPSaveData aSaveData(*pDPObj->GetSaveData());
2120 : ScDPSaveDimension* pPageDim = aSaveData.GetDimensionByName(
2121 1 : OUString("Group2"));
2122 1 : CPPUNIT_ASSERT_MESSAGE("Dimension not found", pPageDim);
2123 1 : OUString aPage("A");
2124 1 : pPageDim->SetCurrentPage(&aPage);
2125 1 : pDPObj->SetSaveData(aSaveData);
2126 1 : aOutRange = refresh(pDPObj);
2127 : {
2128 : // Expected output table content. 0 = empty cell
2129 : const char* aOutputCheck[][2] = {
2130 : { "Filter", 0 },
2131 : { "Group2", "A" },
2132 : { 0, 0 },
2133 : { "Data", 0 },
2134 : { "Sum - Val1", "4" },
2135 : { "Sum - Val2", "40" }
2136 1 : };
2137 :
2138 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by page)");
2139 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2140 : }
2141 :
2142 1 : fTest = m_pDoc->GetValue(aFormulaAddr);
2143 1 : CPPUNIT_ASSERT_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", fTest == 40.0);
2144 :
2145 : // Set query filter.
2146 1 : ScSheetSourceDesc aDesc(*pDPObj->GetSheetDesc());
2147 1 : ScQueryParam aQueryParam(aDesc.GetQueryParam());
2148 1 : CPPUNIT_ASSERT_MESSAGE("There should be at least one query entry.", aQueryParam.GetEntryCount() > 0);
2149 1 : ScQueryEntry& rEntry = aQueryParam.GetEntry(0);
2150 1 : rEntry.bDoQuery = true;
2151 1 : rEntry.nField = 1; // Group1
2152 1 : rEntry.GetQueryItem().mfVal = 1;
2153 1 : aDesc.SetQueryParam(aQueryParam);
2154 1 : pDPObj->SetSheetDesc(aDesc);
2155 1 : aOutRange = refresh(pDPObj);
2156 : {
2157 : // Expected output table content. 0 = empty cell
2158 : const char* aOutputCheck[][2] = {
2159 : { "Filter", 0 },
2160 : { "Group2", "A" },
2161 : { 0, 0 },
2162 : { "Data", 0 },
2163 : { "Sum - Val1", "2" },
2164 : { "Sum - Val2", "20" }
2165 1 : };
2166 :
2167 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by query)");
2168 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2169 : }
2170 :
2171 1 : fTest = m_pDoc->GetValue(aFormulaAddr);
2172 1 : CPPUNIT_ASSERT_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", fTest == 20.0);
2173 :
2174 : // Set the current page of 'Group2' back to '- all -'. The query filter
2175 : // should still be in effect.
2176 1 : pPageDim->SetCurrentPage(NULL); // Remove the page.
2177 1 : pDPObj->SetSaveData(aSaveData);
2178 1 : aOutRange = refresh(pDPObj);
2179 : {
2180 : // Expected output table content. 0 = empty cell
2181 : const char* aOutputCheck[][2] = {
2182 : { "Filter", 0 },
2183 : { "Group2", "- all -" },
2184 : { 0, 0 },
2185 : { "Data", 0 },
2186 : { "Sum - Val1", "4" },
2187 : { "Sum - Val2", "40" }
2188 1 : };
2189 :
2190 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by page)");
2191 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2192 : }
2193 :
2194 :
2195 1 : pDPs->FreeTable(pDPObj);
2196 2 : CPPUNIT_ASSERT_MESSAGE("There shouldn't be any data pilot table stored with the document.",
2197 1 : pDPs->GetCount() == 0);
2198 :
2199 1 : m_pDoc->DeleteTab(1);
2200 1 : m_pDoc->DeleteTab(0);
2201 1 : }
2202 :
2203 1 : void Test::testPivotTableNamedSource()
2204 : {
2205 1 : m_pDoc->InsertTab(0, OUString("Data"));
2206 1 : m_pDoc->InsertTab(1, OUString("Table"));
2207 :
2208 : // Dimension definition
2209 : DPFieldDef aFields[] = {
2210 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
2211 : { "Group", sheet::DataPilotFieldOrientation_COLUMN, 0 },
2212 : { "Score", sheet::DataPilotFieldOrientation_DATA, 0 }
2213 1 : };
2214 :
2215 : // Raw data
2216 : const char* aData[][3] = {
2217 : { "Andy", "A", "30" },
2218 : { "Bruce", "A", "20" },
2219 : { "Charlie", "B", "45" },
2220 : { "David", "B", "12" },
2221 : { "Edward", "C", "8" },
2222 : { "Frank", "C", "15" },
2223 1 : };
2224 :
2225 1 : size_t nFieldCount = SAL_N_ELEMENTS(aFields);
2226 1 : size_t nDataCount = SAL_N_ELEMENTS(aData);
2227 :
2228 : // Insert the raw data.
2229 1 : ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
2230 1 : rtl::OUString aRangeStr;
2231 1 : aSrcRange.Format(aRangeStr, SCR_ABS_3D, m_pDoc);
2232 :
2233 : // Name this range.
2234 1 : rtl::OUString aRangeName("MyData");
2235 1 : ScRangeName* pNames = m_pDoc->GetRangeName();
2236 1 : CPPUNIT_ASSERT_MESSAGE("Failed to get global range name container.", pNames);
2237 : ScRangeData* pName = new ScRangeData(
2238 1 : m_pDoc, aRangeName, aRangeStr);
2239 1 : bool bSuccess = pNames->insert(pName);
2240 1 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bSuccess);
2241 :
2242 1 : ScSheetSourceDesc aSheetDesc(m_pDoc);
2243 1 : aSheetDesc.SetRangeName(aRangeName);
2244 1 : ScDPObject* pDPObj = createDPFromSourceDesc(m_pDoc, aSheetDesc, aFields, nFieldCount, false);
2245 1 : CPPUNIT_ASSERT_MESSAGE("Failed to create a new pivot table object.", pDPObj);
2246 :
2247 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2248 1 : bSuccess = pDPs->InsertNewTable(pDPObj);
2249 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2250 2 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2251 1 : pDPs->GetCount() == 1);
2252 1 : pDPObj->SetName(pDPs->CreateNewName());
2253 :
2254 1 : ScRange aOutRange = refresh(pDPObj);
2255 : {
2256 : // Expected output table content. 0 = empty cell
2257 : const char* aOutputCheck[][5] = {
2258 : { "Sum - Score", "Group", 0, 0, 0 },
2259 : { "Name", "A", "B", "C", "Total Result" },
2260 : { "Andy", "30", 0, 0, "30" },
2261 : { "Bruce", "20", 0, 0, "20" },
2262 : { "Charlie", 0, "45", 0, "45" },
2263 : { "David", 0, "12", 0, "12" },
2264 : { "Edward", 0, 0, "8", "8" },
2265 : { "Frank", 0, 0, "15", "15" },
2266 : { "Total Result", "50", "57", "23", "130" }
2267 1 : };
2268 :
2269 1 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2270 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2271 : }
2272 :
2273 2 : CPPUNIT_ASSERT_MESSAGE("There should be one named range data cache.",
2274 1 : pDPs->GetNameCaches().size() == 1 && pDPs->GetSheetCaches().size() == 0);
2275 :
2276 : // Move the table with pivot table to the left of the source data sheet.
2277 1 : m_pDoc->MoveTab(1, 0);
2278 1 : rtl::OUString aTabName;
2279 1 : m_pDoc->GetName(0, aTabName);
2280 1 : CPPUNIT_ASSERT_MESSAGE( "Wrong sheet name.", aTabName == "Table" );
2281 2 : CPPUNIT_ASSERT_MESSAGE("Pivot table output is on the wrong sheet!",
2282 1 : pDPObj->GetOutRange().aStart.Tab() == 0);
2283 :
2284 2 : CPPUNIT_ASSERT_MESSAGE("Moving the pivot table to another sheet shouldn't have changed the cache state.",
2285 1 : pDPs->GetNameCaches().size() == 1 && pDPs->GetSheetCaches().size() == 0);
2286 :
2287 1 : const ScSheetSourceDesc* pDesc = pDPObj->GetSheetDesc();
2288 1 : CPPUNIT_ASSERT_MESSAGE("Sheet source description doesn't exist.", pDesc);
2289 2 : CPPUNIT_ASSERT_MESSAGE("Named source range has been altered unexpectedly!",
2290 1 : pDesc->GetRangeName().equals(aRangeName));
2291 :
2292 1 : CPPUNIT_ASSERT_MESSAGE("Cache should exist.", pDPs->GetNameCaches().hasCache(aRangeName));
2293 :
2294 1 : pDPs->FreeTable(pDPObj);
2295 1 : CPPUNIT_ASSERT_MESSAGE("There should be no more tables.", pDPs->GetCount() == 0);
2296 2 : CPPUNIT_ASSERT_MESSAGE("There shouldn't be any more cache stored.",
2297 1 : pDPs->GetNameCaches().size() == 0);
2298 :
2299 1 : pNames->clear();
2300 1 : m_pDoc->DeleteTab(1);
2301 1 : m_pDoc->DeleteTab(0);
2302 1 : }
2303 :
2304 1 : void Test::testPivotTableCache()
2305 : {
2306 1 : m_pDoc->InsertTab(0, OUString("Data"));
2307 :
2308 : // Raw data
2309 : const char* aData[][3] = {
2310 : { "F1", "F2", "F3" },
2311 : { "Z", "A", "30" },
2312 : { "R", "A", "20" },
2313 : { "A", "B", "45" },
2314 : { "F", "B", "12" },
2315 : { "Y", "C", "8" },
2316 : { "12", "C", "15" },
2317 1 : };
2318 :
2319 1 : ScAddress aPos(1,1,0);
2320 1 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2321 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2322 :
2323 1 : ScDPCache aCache(m_pDoc);
2324 1 : aCache.InitFromDoc(m_pDoc, aDataRange);
2325 1 : long nDimCount = aCache.GetColumnCount();
2326 1 : CPPUNIT_ASSERT_MESSAGE("wrong dimension count.", nDimCount == 3);
2327 1 : rtl::OUString aDimName = aCache.GetDimensionName(0);
2328 1 : CPPUNIT_ASSERT_MESSAGE("wrong dimension name", aDimName.equalsAscii("F1"));
2329 1 : aDimName = aCache.GetDimensionName(1);
2330 1 : CPPUNIT_ASSERT_MESSAGE("wrong dimension name", aDimName.equalsAscii("F2"));
2331 1 : aDimName = aCache.GetDimensionName(2);
2332 1 : CPPUNIT_ASSERT_MESSAGE("wrong dimension name", aDimName.equalsAscii("F3"));
2333 :
2334 : // In each dimension, member ID values also represent their sort order (in
2335 : // source dimensions only, not in group dimensions). Value items are
2336 : // sorted before string ones. Also, no duplicate dimension members should
2337 : // exist.
2338 :
2339 : // Dimension 0 - a mix of strings and values.
2340 1 : long nMemCount = aCache.GetDimMemberCount(0);
2341 1 : CPPUNIT_ASSERT_MESSAGE("wrong dimension member count", nMemCount == 6);
2342 1 : const ScDPItemData* pItem = aCache.GetItemDataById(0, 0);
2343 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2344 : pItem->GetType() == ScDPItemData::Value &&
2345 1 : pItem->GetValue() == 12);
2346 1 : pItem = aCache.GetItemDataById(0, 1);
2347 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2348 : pItem->GetType() == ScDPItemData::String &&
2349 1 : pItem->GetString().equalsAscii("A"));
2350 1 : pItem = aCache.GetItemDataById(0, 2);
2351 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2352 : pItem->GetType() == ScDPItemData::String &&
2353 1 : pItem->GetString().equalsAscii("F"));
2354 1 : pItem = aCache.GetItemDataById(0, 3);
2355 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2356 : pItem->GetType() == ScDPItemData::String &&
2357 1 : pItem->GetString().equalsAscii("R"));
2358 1 : pItem = aCache.GetItemDataById(0, 4);
2359 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2360 : pItem->GetType() == ScDPItemData::String &&
2361 1 : pItem->GetString().equalsAscii("Y"));
2362 1 : pItem = aCache.GetItemDataById(0, 5);
2363 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2364 : pItem->GetType() == ScDPItemData::String &&
2365 1 : pItem->GetString().equalsAscii("Z"));
2366 1 : pItem = aCache.GetItemDataById(0, 6);
2367 1 : CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
2368 :
2369 : // Dimension 1 - duplicate values in source.
2370 1 : nMemCount = aCache.GetDimMemberCount(1);
2371 1 : CPPUNIT_ASSERT_MESSAGE("wrong dimension member count", nMemCount == 3);
2372 1 : pItem = aCache.GetItemDataById(1, 0);
2373 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2374 : pItem->GetType() == ScDPItemData::String &&
2375 1 : pItem->GetString().equalsAscii("A"));
2376 1 : pItem = aCache.GetItemDataById(1, 1);
2377 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2378 : pItem->GetType() == ScDPItemData::String &&
2379 1 : pItem->GetString().equalsAscii("B"));
2380 1 : pItem = aCache.GetItemDataById(1, 2);
2381 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2382 : pItem->GetType() == ScDPItemData::String &&
2383 1 : pItem->GetString().equalsAscii("C"));
2384 1 : pItem = aCache.GetItemDataById(1, 3);
2385 1 : CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
2386 :
2387 : // Dimension 2 - values only.
2388 1 : nMemCount = aCache.GetDimMemberCount(2);
2389 1 : CPPUNIT_ASSERT_MESSAGE("wrong dimension member count", nMemCount == 6);
2390 1 : pItem = aCache.GetItemDataById(2, 0);
2391 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2392 : pItem->GetType() == ScDPItemData::Value &&
2393 1 : pItem->GetValue() == 8);
2394 1 : pItem = aCache.GetItemDataById(2, 1);
2395 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2396 : pItem->GetType() == ScDPItemData::Value &&
2397 1 : pItem->GetValue() == 12);
2398 1 : pItem = aCache.GetItemDataById(2, 2);
2399 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2400 : pItem->GetType() == ScDPItemData::Value &&
2401 1 : pItem->GetValue() == 15);
2402 1 : pItem = aCache.GetItemDataById(2, 3);
2403 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2404 : pItem->GetType() == ScDPItemData::Value &&
2405 1 : pItem->GetValue() == 20);
2406 1 : pItem = aCache.GetItemDataById(2, 4);
2407 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2408 : pItem->GetType() == ScDPItemData::Value &&
2409 1 : pItem->GetValue() == 30);
2410 1 : pItem = aCache.GetItemDataById(2, 5);
2411 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2412 : pItem->GetType() == ScDPItemData::Value &&
2413 1 : pItem->GetValue() == 45);
2414 1 : pItem = aCache.GetItemDataById(2, 6);
2415 1 : CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
2416 :
2417 : {
2418 : // Check the integrity of the source data.
2419 1 : ScDPItemData aTest;
2420 : long nDim;
2421 :
2422 : {
2423 : // Dimension 0: Z, R, A, F, Y, 12
2424 1 : nDim = 0;
2425 1 : const char* aChecks[] = { "Z", "R", "A", "F", "Y" };
2426 6 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2427 : {
2428 5 : pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
2429 5 : aTest.SetString(rtl::OUString::createFromAscii(aChecks[i]));
2430 5 : CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2431 : }
2432 :
2433 1 : pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, 5, false));
2434 1 : aTest.SetValue(12);
2435 1 : CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2436 : }
2437 :
2438 : {
2439 : // Dimension 1: A, A, B, B, C, C
2440 1 : nDim = 1;
2441 1 : const char* aChecks[] = { "A", "A", "B", "B", "C", "C" };
2442 7 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2443 : {
2444 6 : pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
2445 6 : aTest.SetString(rtl::OUString::createFromAscii(aChecks[i]));
2446 6 : CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2447 : }
2448 : }
2449 :
2450 : {
2451 : // Dimension 2: 30, 20, 45, 12, 8, 15
2452 1 : nDim = 2;
2453 1 : double aChecks[] = { 30, 20, 45, 12, 8, 15 };
2454 7 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2455 : {
2456 6 : pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
2457 6 : aTest.SetValue(aChecks[i]);
2458 6 : CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2459 : }
2460 1 : }
2461 : }
2462 :
2463 : // Now, on to testing the filtered cache.
2464 :
2465 : {
2466 : // Non-filtered cache - everything should be visible.
2467 1 : ScDPFilteredCache aFilteredCache(aCache);
2468 1 : aFilteredCache.fillTable();
2469 :
2470 1 : sal_Int32 nRows = aFilteredCache.getRowSize();
2471 1 : CPPUNIT_ASSERT_MESSAGE("Wrong dimension.", nRows == 6 && aFilteredCache.getColSize() == 3);
2472 :
2473 7 : for (sal_Int32 i = 0; i < nRows; ++i)
2474 : {
2475 6 : if (!aFilteredCache.isRowActive(i))
2476 : {
2477 0 : std::ostringstream os;
2478 0 : os << "Row " << i << " should be visible but it isn't.";
2479 0 : CPPUNIT_ASSERT_MESSAGE(os.str().c_str(), false);
2480 : }
2481 1 : }
2482 : }
2483 :
2484 : // TODO : Add test for filtered caches.
2485 :
2486 1 : m_pDoc->DeleteTab(0);
2487 1 : }
2488 :
2489 1 : void Test::testPivotTableDuplicateDataFields()
2490 : {
2491 1 : m_pDoc->InsertTab(0, OUString("Data"));
2492 1 : m_pDoc->InsertTab(1, OUString("Table"));
2493 :
2494 : // Raw data
2495 : const char* aData[][2] = {
2496 : { "Name", "Value" },
2497 : { "A", "45" },
2498 : { "A", "5" },
2499 : { "A", "41" },
2500 : { "A", "49" },
2501 : { "A", "4" },
2502 : { "B", "33" },
2503 : { "B", "84" },
2504 : { "B", "74" },
2505 : { "B", "8" },
2506 : { "B", "68" }
2507 1 : };
2508 :
2509 : // Dimension definition
2510 : DPFieldDef aFields[] = {
2511 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
2512 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
2513 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_COUNT }
2514 1 : };
2515 :
2516 1 : ScAddress aPos(2,2,0);
2517 1 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2518 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2519 :
2520 : ScDPObject* pDPObj = createDPFromRange(
2521 1 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2522 :
2523 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2524 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2525 :
2526 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2527 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2528 1 : pDPs->GetCount(), static_cast<size_t>(1));
2529 1 : pDPObj->SetName(pDPs->CreateNewName());
2530 :
2531 1 : ScRange aOutRange = refresh(pDPObj);
2532 : {
2533 : // Expected output table content. 0 = empty cell
2534 : const char* aOutputCheck[][3] = {
2535 : { "Name", "Data", 0 },
2536 : { "A", "Sum - Value", "144" },
2537 : { 0, "Count - Value", "5" },
2538 : { "B", "Sum - Value", "267" },
2539 : { 0, "Count - Value", "5" },
2540 : { "Total Sum - Value", 0, "411" },
2541 : { "Total Count - Value", 0, "10" },
2542 1 : };
2543 :
2544 1 : bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2545 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2546 : }
2547 :
2548 : // Move the data layout dimension from row to column.
2549 1 : ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2550 1 : CPPUNIT_ASSERT_MESSAGE("No save data!?", pSaveData);
2551 1 : ScDPSaveDimension* pDataLayout = pSaveData->GetDataLayoutDimension();
2552 1 : CPPUNIT_ASSERT_MESSAGE("No data layout dimension.", pDataLayout);
2553 1 : pDataLayout->SetOrientation(sheet::DataPilotFieldOrientation_COLUMN);
2554 1 : pDPObj->SetSaveData(*pSaveData);
2555 :
2556 : // Refresh the table output.
2557 1 : aOutRange = refresh(pDPObj);
2558 : {
2559 : // Expected output table content. 0 = empty cell
2560 : const char* aOutputCheck[][3] = {
2561 : { 0, "Data", 0 },
2562 : { "Name", "Sum - Value", "Count - Value" },
2563 : { "A", "144", "5" },
2564 : { "B", "267", "5" },
2565 : { "Total Result", "411", "10" }
2566 1 : };
2567 :
2568 1 : bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2569 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2570 : }
2571 :
2572 1 : ScPivotParam aParam;
2573 1 : pDPObj->FillLabelData(aParam);
2574 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be exactly 4 labels (2 original, 1 data layout, and 1 duplicate dimensions).",
2575 1 : aParam.maLabelArray.size(), static_cast<size_t>(4));
2576 :
2577 1 : pDPs->FreeTable(pDPObj);
2578 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2579 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2580 1 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
2581 :
2582 1 : m_pDoc->DeleteTab(1);
2583 1 : m_pDoc->DeleteTab(0);
2584 1 : }
2585 :
2586 1 : void Test::testPivotTableNormalGrouping()
2587 : {
2588 1 : m_pDoc->InsertTab(0, OUString("Data"));
2589 1 : m_pDoc->InsertTab(1, OUString("Table"));
2590 :
2591 : // Raw data
2592 : const char* aData[][2] = {
2593 : { "Name", "Value" },
2594 : { "A", "1" },
2595 : { "B", "2" },
2596 : { "C", "3" },
2597 : { "D", "4" },
2598 : { "E", "5" },
2599 : { "F", "6" },
2600 : { "G", "7" }
2601 1 : };
2602 :
2603 : // Dimension definition
2604 : DPFieldDef aFields[] = {
2605 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
2606 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
2607 1 : };
2608 :
2609 1 : ScAddress aPos(1,1,0);
2610 1 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2611 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2612 :
2613 : ScDPObject* pDPObj = createDPFromRange(
2614 1 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2615 :
2616 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2617 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2618 :
2619 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2620 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2621 1 : pDPs->GetCount(), static_cast<size_t>(1));
2622 1 : pDPObj->SetName(pDPs->CreateNewName());
2623 :
2624 1 : ScRange aOutRange = refresh(pDPObj);
2625 : {
2626 : // Expected output table content. 0 = empty cell
2627 : const char* aOutputCheck[][2] = {
2628 : { "Name", 0 },
2629 : { "A", "1" },
2630 : { "B", "2" },
2631 : { "C", "3" },
2632 : { "D", "4" },
2633 : { "E", "5" },
2634 : { "F", "6" },
2635 : { "G", "7" },
2636 : { "Total Result", "28" }
2637 1 : };
2638 :
2639 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Initial output without grouping");
2640 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2641 : }
2642 :
2643 1 : ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2644 1 : CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
2645 1 : ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
2646 1 : CPPUNIT_ASSERT_MESSAGE("Failed to create dimension data.", pDimData);
2647 :
2648 1 : rtl::OUString aGroupPrefix("Group");
2649 1 : rtl::OUString aBaseDimName("Name");
2650 : rtl::OUString aGroupDimName =
2651 1 : pDimData->CreateGroupDimName(aBaseDimName, *pDPObj, false, NULL);
2652 :
2653 : {
2654 : // Group A, B and C together.
2655 1 : ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
2656 1 : rtl::OUString aGroupName = aGroupDim.CreateGroupName(aGroupPrefix);
2657 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected group name", aGroupName.equalsAscii("Group1"));
2658 :
2659 1 : ScDPSaveGroupItem aGroup(aGroupName);
2660 1 : aGroup.AddElement(rtl::OUString("A"));
2661 1 : aGroup.AddElement(rtl::OUString("B"));
2662 1 : aGroup.AddElement(rtl::OUString("C"));
2663 1 : aGroupDim.AddGroupItem(aGroup);
2664 1 : pDimData->AddGroupDimension(aGroupDim);
2665 :
2666 1 : ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
2667 1 : pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
2668 1 : pSaveData->SetPosition(pDim, 0); // Set it before the base dimension.
2669 : }
2670 :
2671 1 : pDPObj->SetSaveData(*pSaveData);
2672 1 : aOutRange = refreshGroups(pDPs, pDPObj);
2673 : {
2674 : // Expected output table content. 0 = empty cell
2675 : const char* aOutputCheck[][3] = {
2676 : { "Name2", "Name", 0 },
2677 : { "D", "D", "4" },
2678 : { "E", "E", "5" },
2679 : { "F", "F", "6" },
2680 : { "G", "G", "7" },
2681 : { "Group1", "A", "1" },
2682 : { 0, "B", "2" },
2683 : { 0, "C", "3" },
2684 : { "Total Result", 0, "28" }
2685 1 : };
2686 :
2687 1 : bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "A, B, C grouped by Group1.");
2688 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2689 : }
2690 :
2691 1 : pSaveData = pDPObj->GetSaveData();
2692 1 : pDimData = pSaveData->GetDimensionData();
2693 :
2694 : {
2695 : // Group D, E, F together.
2696 1 : ScDPSaveGroupDimension* pGroupDim = pDimData->GetGroupDimAccForBase(aBaseDimName);
2697 1 : CPPUNIT_ASSERT_MESSAGE("There should be an existing group dimension.", pGroupDim);
2698 1 : rtl::OUString aGroupName = pGroupDim->CreateGroupName(aGroupPrefix);
2699 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected group name", aGroupName.equalsAscii("Group2"));
2700 :
2701 1 : ScDPSaveGroupItem aGroup(aGroupName);
2702 1 : aGroup.AddElement(rtl::OUString("D"));
2703 1 : aGroup.AddElement(rtl::OUString("E"));
2704 1 : aGroup.AddElement(rtl::OUString("F"));
2705 1 : pGroupDim->AddGroupItem(aGroup);
2706 : }
2707 :
2708 1 : pDPObj->SetSaveData(*pSaveData);
2709 1 : aOutRange = refreshGroups(pDPs, pDPObj);
2710 : {
2711 : // Expected output table content. 0 = empty cell
2712 : const char* aOutputCheck[][3] = {
2713 : { "Name2", "Name", 0 },
2714 : { "G", "G", "7" },
2715 : { "Group1", "A", "1" },
2716 : { 0, "B", "2" },
2717 : { 0, "C", "3" },
2718 : { "Group2", "D", "4" },
2719 : { 0, "E", "5" },
2720 : { 0, "F", "6" },
2721 : { "Total Result", 0, "28" }
2722 1 : };
2723 :
2724 1 : bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "D, E, F grouped by Group2.");
2725 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2726 : }
2727 :
2728 1 : pDPs->FreeTable(pDPObj);
2729 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2730 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2731 1 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
2732 :
2733 1 : m_pDoc->DeleteTab(1);
2734 1 : m_pDoc->DeleteTab(0);
2735 1 : }
2736 :
2737 1 : void Test::testPivotTableNumberGrouping()
2738 : {
2739 1 : m_pDoc->InsertTab(0, OUString("Data"));
2740 1 : m_pDoc->InsertTab(1, OUString("Table"));
2741 :
2742 : // Raw data
2743 : const char* aData[][2] = {
2744 : { "Order", "Score" },
2745 : { "43", "171" },
2746 : { "18", "20" },
2747 : { "69", "159" },
2748 : { "95", "19" },
2749 : { "96", "163" },
2750 : { "46", "70" },
2751 : { "22", "36" },
2752 : { "81", "49" },
2753 : { "54", "61" },
2754 : { "39", "62" },
2755 : { "86", "17" },
2756 : { "34", "0" },
2757 : { "30", "25" },
2758 : { "24", "103" },
2759 : { "16", "59" },
2760 : { "24", "119" },
2761 : { "15", "86" },
2762 : { "69", "170" }
2763 1 : };
2764 :
2765 : // Dimension definition
2766 : DPFieldDef aFields[] = {
2767 : { "Order", sheet::DataPilotFieldOrientation_ROW, 0 },
2768 : { "Score", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
2769 1 : };
2770 :
2771 1 : ScAddress aPos(1,1,0);
2772 1 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2773 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2774 :
2775 : ScDPObject* pDPObj = createDPFromRange(
2776 1 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2777 :
2778 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2779 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2780 :
2781 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2782 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2783 1 : pDPs->GetCount(), static_cast<size_t>(1));
2784 1 : pDPObj->SetName(pDPs->CreateNewName());
2785 :
2786 1 : ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2787 1 : CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
2788 1 : ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
2789 1 : CPPUNIT_ASSERT_MESSAGE("No dimension data !?", pDimData);
2790 :
2791 : {
2792 1 : ScDPNumGroupInfo aInfo;
2793 1 : aInfo.mbEnable = true;
2794 1 : aInfo.mbAutoStart = false;
2795 1 : aInfo.mbAutoEnd = false;
2796 1 : aInfo.mbDateValues = false;
2797 1 : aInfo.mbIntegerOnly = true;
2798 1 : aInfo.mfStart = 30;
2799 1 : aInfo.mfEnd = 60;
2800 1 : aInfo.mfStep = 10;
2801 1 : ScDPSaveNumGroupDimension aGroup(rtl::OUString("Order"), aInfo);
2802 1 : pDimData->AddNumGroupDimension(aGroup);
2803 : }
2804 :
2805 1 : pDPObj->SetSaveData(*pSaveData);
2806 1 : ScRange aOutRange = refreshGroups(pDPs, pDPObj);
2807 : {
2808 : // Expected output table content. 0 = empty cell
2809 : const char* aOutputCheck[][2] = {
2810 : { "Order", 0 },
2811 : { "<30", "423" },
2812 : { "30-39", "87" },
2813 : { "40-49", "241" },
2814 : { "50-60", "61" },
2815 : { ">60", "577" },
2816 : { "Total Result", "1389" }
2817 1 : };
2818 :
2819 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Order grouped by numbers");
2820 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2821 : }
2822 :
2823 1 : pDPs->FreeTable(pDPObj);
2824 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2825 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2826 1 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
2827 :
2828 1 : m_pDoc->DeleteTab(1);
2829 1 : m_pDoc->DeleteTab(0);
2830 1 : }
2831 :
2832 1 : void Test::testPivotTableDateGrouping()
2833 : {
2834 1 : m_pDoc->InsertTab(0, OUString("Data"));
2835 1 : m_pDoc->InsertTab(1, OUString("Table"));
2836 :
2837 : // Raw data
2838 : const char* aData[][2] = {
2839 : { "Date", "Value" },
2840 : { "2011-01-01", "1" },
2841 : { "2011-03-02", "2" },
2842 : { "2012-01-04", "3" },
2843 : { "2012-02-23", "4" },
2844 : { "2012-02-24", "5" },
2845 : { "2012-03-15", "6" },
2846 : { "2011-09-03", "7" },
2847 : { "2012-12-25", "8" }
2848 1 : };
2849 :
2850 : // Dimension definition
2851 : DPFieldDef aFields[] = {
2852 : { "Date", sheet::DataPilotFieldOrientation_ROW, 0 },
2853 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
2854 1 : };
2855 :
2856 1 : ScAddress aPos(1,1,0);
2857 1 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2858 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2859 :
2860 : ScDPObject* pDPObj = createDPFromRange(
2861 1 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2862 :
2863 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2864 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2865 :
2866 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2867 2 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2868 1 : pDPs->GetCount() == 1);
2869 1 : pDPObj->SetName(pDPs->CreateNewName());
2870 :
2871 1 : ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2872 1 : CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
2873 1 : ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
2874 1 : CPPUNIT_ASSERT_MESSAGE("No dimension data !?", pDimData);
2875 :
2876 1 : rtl::OUString aBaseDimName("Date");
2877 :
2878 1 : ScDPNumGroupInfo aInfo;
2879 1 : aInfo.mbEnable = true;
2880 1 : aInfo.mbAutoStart = true;
2881 1 : aInfo.mbAutoEnd = true;
2882 : {
2883 : // Turn the Date dimension into months. The first of the date
2884 : // dimensions is always a number-group dimension which replaces the
2885 : // original dimension.
2886 1 : ScDPSaveNumGroupDimension aGroup(aBaseDimName, aInfo, sheet::DataPilotFieldGroupBy::MONTHS);
2887 1 : pDimData->AddNumGroupDimension(aGroup);
2888 : }
2889 :
2890 : {
2891 : // Add quarter dimension. This will be an additional dimension.
2892 : rtl::OUString aGroupDimName =
2893 : pDimData->CreateDateGroupDimName(
2894 1 : sheet::DataPilotFieldGroupBy::QUARTERS, *pDPObj, true, NULL);
2895 1 : ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
2896 1 : aGroupDim.SetDateInfo(aInfo, sheet::DataPilotFieldGroupBy::QUARTERS);
2897 1 : pDimData->AddGroupDimension(aGroupDim);
2898 :
2899 : // Set orientation.
2900 1 : ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
2901 1 : pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
2902 1 : pSaveData->SetPosition(pDim, 0); // set it to the left end.
2903 : }
2904 :
2905 : {
2906 : // Add year dimension. This is a new dimension also.
2907 : rtl::OUString aGroupDimName =
2908 : pDimData->CreateDateGroupDimName(
2909 1 : sheet::DataPilotFieldGroupBy::YEARS, *pDPObj, true, NULL);
2910 1 : ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
2911 1 : aGroupDim.SetDateInfo(aInfo, sheet::DataPilotFieldGroupBy::YEARS);
2912 1 : pDimData->AddGroupDimension(aGroupDim);
2913 :
2914 : // Set orientation.
2915 1 : ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
2916 1 : pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
2917 1 : pSaveData->SetPosition(pDim, 0); // set it to the left end.
2918 : }
2919 :
2920 1 : pDPObj->SetSaveData(*pSaveData);
2921 1 : ScRange aOutRange = refreshGroups(pDPs, pDPObj);
2922 : {
2923 : // Expected output table content. 0 = empty cell
2924 : const char* aOutputCheck[][4] = {
2925 : { "Years", "Quarters", "Date", 0 },
2926 : { "2011", "Q1", "Jan", "1" },
2927 : { 0, 0, "Mar", "2" },
2928 : { 0, "Q3", "Sep", "7" },
2929 : { "2012", "Q1", "Jan", "3" },
2930 : { 0, 0, "Feb", "9" },
2931 : { 0, 0, "Mar", "6" },
2932 : { 0, "Q4", "Dec", "8" },
2933 : { "Total Result", 0, 0, "36" },
2934 1 : };
2935 :
2936 1 : bSuccess = checkDPTableOutput<4>(m_pDoc, aOutRange, aOutputCheck, "Years, quarters and months date groups.");
2937 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2938 : }
2939 :
2940 : {
2941 : // Let's hide year 2012.
2942 1 : pSaveData = pDPObj->GetSaveData();
2943 1 : ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(rtl::OUString("Years"));
2944 1 : CPPUNIT_ASSERT_MESSAGE("Years dimension should exist.", pDim);
2945 1 : ScDPSaveMember* pMem = pDim->GetMemberByName(rtl::OUString("2012"));
2946 1 : CPPUNIT_ASSERT_MESSAGE("Member should exist.", pMem);
2947 1 : pMem->SetIsVisible(false);
2948 : }
2949 1 : pDPObj->SetSaveData(*pSaveData);
2950 1 : pDPObj->ReloadGroupTableData();
2951 1 : pDPObj->InvalidateData();
2952 :
2953 1 : aOutRange = refresh(pDPObj);
2954 : {
2955 : // Expected output table content. 0 = empty cell
2956 : const char* aOutputCheck[][4] = {
2957 : { "Years", "Quarters", "Date", 0 },
2958 : { "2011", "Q1", "Jan", "1" },
2959 : { 0, 0, "Mar", "2" },
2960 : { 0, "Q3", "Sep", "7" },
2961 : { "Total Result", 0, 0, "10" },
2962 1 : };
2963 :
2964 1 : bSuccess = checkDPTableOutput<4>(m_pDoc, aOutRange, aOutputCheck, "Year 2012 data now hidden");
2965 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2966 : }
2967 :
2968 : // Remove all date grouping. The source dimension "Date" has two
2969 : // external dimensions ("Years" and "Quarters") and one internal ("Date"
2970 : // the same name but different hierarchy). Remove all of them.
2971 1 : pSaveData = pDPObj->GetSaveData();
2972 1 : pSaveData->RemoveAllGroupDimensions(aBaseDimName);
2973 1 : pDPObj->SetSaveData(*pSaveData);
2974 1 : pDPObj->ReloadGroupTableData();
2975 1 : pDPObj->InvalidateData();
2976 :
2977 1 : aOutRange = refresh(pDPObj);
2978 : {
2979 : // Expected output table content. 0 = empty cell
2980 : const char* aOutputCheck[][2] = {
2981 : { "Date", 0 },
2982 : { "2011-01-01", "1" },
2983 : { "2011-03-02", "2" },
2984 : { "2011-09-03", "7" },
2985 : { "2012-01-04", "3" },
2986 : { "2012-02-23", "4" },
2987 : { "2012-02-24", "5" },
2988 : { "2012-03-15", "6" },
2989 : { "2012-12-25", "8" },
2990 : { "Total Result", "36" }
2991 1 : };
2992 :
2993 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Remove all date grouping.");
2994 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2995 : }
2996 :
2997 1 : pDPs->FreeTable(pDPObj);
2998 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2999 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3000 1 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3001 :
3002 1 : m_pDoc->DeleteTab(1);
3003 1 : m_pDoc->DeleteTab(0);
3004 1 : }
3005 :
3006 1 : void Test::testPivotTableEmptyRows()
3007 : {
3008 1 : m_pDoc->InsertTab(0, OUString("Data"));
3009 1 : m_pDoc->InsertTab(1, OUString("Table"));
3010 :
3011 : // Raw data
3012 : const char* aData[][2] = {
3013 : { "Name", "Value" },
3014 : { "A", "1" },
3015 : { "B", "2" },
3016 : { "C", "3" },
3017 : { "D", "4" },
3018 1 : };
3019 :
3020 : // Dimension definition
3021 : DPFieldDef aFields[] = {
3022 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3023 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3024 1 : };
3025 :
3026 1 : ScAddress aPos(1,1,0);
3027 1 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
3028 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
3029 :
3030 : // Extend the range downward to include some trailing empty rows.
3031 1 : aDataRange.aEnd.IncRow(2);
3032 :
3033 : ScDPObject* pDPObj = createDPFromRange(
3034 1 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3035 :
3036 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3037 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
3038 :
3039 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3040 2 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3041 1 : pDPs->GetCount() == 1);
3042 1 : pDPObj->SetName(pDPs->CreateNewName());
3043 :
3044 1 : ScRange aOutRange = refresh(pDPObj);
3045 :
3046 : {
3047 : // Expected output table content. 0 = empty cell
3048 : const char* aOutputCheck[][2] = {
3049 : { "Name", 0 },
3050 : { "A", "1" },
3051 : { "B", "2" },
3052 : { "C", "3" },
3053 : { "D", "4" },
3054 : { "(empty)", 0 },
3055 : { "Total Result", "10" },
3056 1 : };
3057 :
3058 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Include empty rows");
3059 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3060 : }
3061 :
3062 : // This time, ignore empty rows.
3063 1 : ScDPSaveData* pSaveData = pDPObj->GetSaveData();
3064 1 : CPPUNIT_ASSERT_MESSAGE("Save data doesn't exist.", pSaveData);
3065 1 : pSaveData->SetIgnoreEmptyRows(true);
3066 1 : pDPObj->ClearTableData();
3067 1 : aOutRange = refresh(pDPObj);
3068 :
3069 : {
3070 : // Expected output table content. 0 = empty cell
3071 : const char* aOutputCheck[][2] = {
3072 : { "Name", 0 },
3073 : { "A", "1" },
3074 : { "B", "2" },
3075 : { "C", "3" },
3076 : { "D", "4" },
3077 : { "Total Result", "10" },
3078 1 : };
3079 :
3080 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Ignore empty rows");
3081 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3082 : }
3083 :
3084 : // Modify the source to remove member 'A', then refresh the table.
3085 1 : m_pDoc->SetString(1, 2, 0, "B");
3086 :
3087 1 : std::set<ScDPObject*> aRefs;
3088 1 : sal_uLong nErr = pDPs->ReloadCache(pDPObj, aRefs);
3089 1 : CPPUNIT_ASSERT_MESSAGE("Failed to reload cache.", !nErr);
3090 2 : CPPUNIT_ASSERT_MESSAGE("There should only be one pivot table linked to this cache.",
3091 1 : aRefs.size() == 1 && *aRefs.begin() == pDPObj);
3092 :
3093 1 : pDPObj->ClearTableData();
3094 1 : aOutRange = refresh(pDPObj);
3095 :
3096 : {
3097 : // Expected output table content. 0 = empty cell
3098 : const char* aOutputCheck[][2] = {
3099 : { "Name", 0 },
3100 : { "B", "3" },
3101 : { "C", "3" },
3102 : { "D", "4" },
3103 : { "Total Result", "10" },
3104 1 : };
3105 :
3106 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Ignore empty rows");
3107 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3108 : }
3109 :
3110 1 : pDPs->FreeTable(pDPObj);
3111 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3112 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3113 1 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3114 :
3115 1 : m_pDoc->DeleteTab(1);
3116 1 : m_pDoc->DeleteTab(0);
3117 1 : }
3118 :
3119 1 : void Test::testPivotTableTextNumber()
3120 : {
3121 1 : m_pDoc->InsertTab(0, OUString("Data"));
3122 1 : m_pDoc->InsertTab(1, OUString("Table"));
3123 :
3124 : // Raw data
3125 : const char* aData[][2] = {
3126 : { "Name", "Value" },
3127 : { "0001", "1" },
3128 : { "0002", "2" },
3129 : { "0003", "3" },
3130 : { "0004", "4" },
3131 1 : };
3132 :
3133 : // Dimension definition
3134 : DPFieldDef aFields[] = {
3135 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3136 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3137 1 : };
3138 :
3139 : // Insert raw data such that the first column values are entered as text.
3140 1 : size_t nRowCount = SAL_N_ELEMENTS(aData);
3141 6 : for (size_t nRow = 0; nRow < nRowCount; ++nRow)
3142 : {
3143 5 : ScSetStringParam aParam;
3144 5 : aParam.mbDetectNumberFormat = false;
3145 5 : aParam.meSetTextNumFormat = ScSetStringParam::Always;
3146 5 : m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aData[nRow][0]), &aParam);
3147 5 : aParam.meSetTextNumFormat = ScSetStringParam::Never;
3148 5 : m_pDoc->SetString(1, nRow, 0, OUString::createFromAscii(aData[nRow][1]), &aParam);
3149 :
3150 5 : if (nRow == 0)
3151 : // Don't check the header row.
3152 1 : continue;
3153 :
3154 : // Check the data rows.
3155 4 : CPPUNIT_ASSERT_MESSAGE("This cell is supposed to be text.", m_pDoc->HasStringData(0, nRow, 0));
3156 4 : CPPUNIT_ASSERT_MESSAGE("This cell is supposed to be numeric.", m_pDoc->HasValueData(1, nRow, 0));
3157 : }
3158 :
3159 1 : ScRange aDataRange(0, 0, 0, 1, 4, 0);
3160 :
3161 : ScDPObject* pDPObj = createDPFromRange(
3162 1 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3163 :
3164 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3165 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
3166 :
3167 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3168 2 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3169 1 : pDPs->GetCount() == 1);
3170 1 : pDPObj->SetName(pDPs->CreateNewName());
3171 :
3172 1 : ScRange aOutRange = refresh(pDPObj);
3173 :
3174 : {
3175 : // Expected output table content. 0 = empty cell
3176 : const char* aOutputCheck[][2] = {
3177 : { "Name", 0 },
3178 : { "0001", "1" },
3179 : { "0002", "2" },
3180 : { "0003", "3" },
3181 : { "0004", "4" },
3182 : { "Total Result", "10" },
3183 1 : };
3184 :
3185 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Text number field members");
3186 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3187 : }
3188 :
3189 1 : pDPs->FreeTable(pDPObj);
3190 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3191 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3192 1 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3193 :
3194 1 : m_pDoc->DeleteTab(1);
3195 1 : m_pDoc->DeleteTab(0);
3196 1 : }
3197 :
3198 1 : void Test::testPivotTableCaseInsensitiveStrings()
3199 : {
3200 1 : m_pDoc->InsertTab(0, OUString("Data"));
3201 1 : m_pDoc->InsertTab(1, OUString("Table"));
3202 :
3203 : // Raw data
3204 : const char* aData[][2] = {
3205 : { "Name", "Value" },
3206 : { "A", "1" },
3207 : { "a", "2" },
3208 1 : };
3209 :
3210 : // Dimension definition
3211 : DPFieldDef aFields[] = {
3212 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3213 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3214 1 : };
3215 :
3216 1 : ScAddress aPos(1,1,0);
3217 1 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
3218 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
3219 :
3220 : ScDPObject* pDPObj = createDPFromRange(
3221 1 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3222 :
3223 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3224 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
3225 :
3226 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3227 2 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3228 1 : pDPs->GetCount() == 1);
3229 1 : pDPObj->SetName(pDPs->CreateNewName());
3230 :
3231 1 : ScRange aOutRange = refresh(pDPObj);
3232 :
3233 : {
3234 : // Expected output table content. 0 = empty cell
3235 : const char* aOutputCheck[][2] = {
3236 : { "Name", 0 },
3237 : { "A", "3" },
3238 : { "Total Result", "3" },
3239 1 : };
3240 :
3241 1 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Case insensitive strings");
3242 1 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3243 : }
3244 :
3245 1 : pDPs->FreeTable(pDPObj);
3246 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3247 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3248 1 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3249 :
3250 1 : m_pDoc->DeleteTab(1);
3251 1 : m_pDoc->DeleteTab(0);
3252 1 : }
3253 :
3254 1 : void Test::testPivotTableNumStability()
3255 : {
3256 1 : FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
3257 :
3258 : // Raw Data
3259 : const char* aData[][4] = {
3260 : { "Name", "Time Start", "Time End", "Total" },
3261 : { "Sam", "07:48 AM", "09:00 AM", "=RC[-1]-RC[-2]" },
3262 : { "Sam", "09:00 AM", "10:30 AM", "=RC[-1]-RC[-2]" },
3263 : { "Sam", "10:30 AM", "12:30 PM", "=RC[-1]-RC[-2]" },
3264 : { "Sam", "12:30 PM", "01:00 PM", "=RC[-1]-RC[-2]" },
3265 : { "Sam", "01:00 PM", "01:30 PM", "=RC[-1]-RC[-2]" },
3266 : { "Sam", "01:30 PM", "02:00 PM", "=RC[-1]-RC[-2]" },
3267 : { "Sam", "02:00 PM", "07:15 PM", "=RC[-1]-RC[-2]" },
3268 : { "Sam", "07:47 AM", "09:00 AM", "=RC[-1]-RC[-2]" },
3269 : { "Sam", "09:00 AM", "10:00 AM", "=RC[-1]-RC[-2]" },
3270 : { "Sam", "10:00 AM", "11:00 AM", "=RC[-1]-RC[-2]" },
3271 : { "Sam", "11:00 AM", "11:30 AM", "=RC[-1]-RC[-2]" },
3272 : { "Sam", "11:30 AM", "12:45 PM", "=RC[-1]-RC[-2]" },
3273 : { "Sam", "12:45 PM", "01:15 PM", "=RC[-1]-RC[-2]" },
3274 : { "Sam", "01:15 PM", "02:30 PM", "=RC[-1]-RC[-2]" },
3275 : { "Sam", "02:30 PM", "02:45 PM", "=RC[-1]-RC[-2]" },
3276 : { "Sam", "02:45 PM", "04:30 PM", "=RC[-1]-RC[-2]" },
3277 : { "Sam", "04:30 PM", "06:00 PM", "=RC[-1]-RC[-2]" },
3278 : { "Sam", "06:00 PM", "07:15 PM", "=RC[-1]-RC[-2]" },
3279 : { "Mike", "06:15 AM", "08:30 AM", "=RC[-1]-RC[-2]" },
3280 : { "Mike", "08:30 AM", "10:03 AM", "=RC[-1]-RC[-2]" },
3281 : { "Mike", "10:03 AM", "12:00 PM", "=RC[-1]-RC[-2]" },
3282 : { "Dennis", "11:00 AM", "01:00 PM", "=RC[-1]-RC[-2]" },
3283 : { "Dennis", "01:00 PM", "02:00 PM", "=RC[-1]-RC[-2]" }
3284 1 : };
3285 :
3286 : // Dimension definition
3287 : DPFieldDef aFields[] = {
3288 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3289 : { "Total", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3290 1 : };
3291 :
3292 1 : m_pDoc->InsertTab(0, OUString("Data"));
3293 1 : m_pDoc->InsertTab(1, OUString("Table"));
3294 :
3295 1 : size_t nRowCount = SAL_N_ELEMENTS(aData);
3296 1 : ScAddress aPos(1,1,0);
3297 1 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, nRowCount);
3298 :
3299 : // Insert formulas to manually calculate sums for each name.
3300 1 : m_pDoc->SetString(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+1, aDataRange.aStart.Tab(), "=SUMIF(R[-23]C:R[-1]C;\"Dennis\";R[-23]C[3]:R[-1]C[3])");
3301 1 : m_pDoc->SetString(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+2, aDataRange.aStart.Tab(), "=SUMIF(R[-24]C:R[-2]C;\"Mike\";R[-24]C[3]:R[-2]C[3])");
3302 1 : m_pDoc->SetString(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+3, aDataRange.aStart.Tab(), "=SUMIF(R[-25]C:R[-3]C;\"Sam\";R[-25]C[3]:R[-3]C[3])");
3303 :
3304 1 : m_pDoc->CalcAll();
3305 :
3306 : // Get correct sum values.
3307 1 : double fDennisTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+1, aDataRange.aStart.Tab());
3308 1 : double fMikeTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+2, aDataRange.aStart.Tab());
3309 1 : double fSamTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+3, aDataRange.aStart.Tab());
3310 :
3311 : ScDPObject* pDPObj = createDPFromRange(
3312 1 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3313 :
3314 1 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3315 1 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
3316 :
3317 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3318 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
3319 1 : pDPs->GetCount(), static_cast<size_t>(1));
3320 1 : pDPObj->SetName(pDPs->CreateNewName());
3321 :
3322 1 : ScRange aOutRange = refresh(pDPObj);
3323 :
3324 : // Manually check the total value for each name.
3325 : //
3326 : // +--------------+----------------+
3327 : // | Name | |
3328 : // +--------------+----------------+
3329 : // | Dennis | <Dennis total> |
3330 : // +--------------+----------------+
3331 : // | Mike | <Miks total> |
3332 : // +--------------+----------------+
3333 : // | Sam | <Sam total> |
3334 : // +--------------+----------------+
3335 : // | Total Result | ... |
3336 : // +--------------+----------------+
3337 :
3338 1 : aPos = aOutRange.aStart;
3339 1 : aPos.IncCol();
3340 1 : aPos.IncRow();
3341 1 : double fTest = m_pDoc->GetValue(aPos);
3342 1 : CPPUNIT_ASSERT_MESSAGE("Incorrect value for Dennis.", rtl::math::approxEqual(fTest, fDennisTotal));
3343 1 : aPos.IncRow();
3344 1 : fTest = m_pDoc->GetValue(aPos);
3345 1 : CPPUNIT_ASSERT_MESSAGE("Incorrect value for Mike.", rtl::math::approxEqual(fTest, fMikeTotal));
3346 1 : aPos.IncRow();
3347 1 : fTest = m_pDoc->GetValue(aPos);
3348 1 : CPPUNIT_ASSERT_MESSAGE("Incorrect value for Sam.", rtl::math::approxEqual(fTest, fSamTotal));
3349 :
3350 1 : pDPs->FreeTable(pDPObj);
3351 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3352 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3353 1 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3354 :
3355 1 : m_pDoc->DeleteTab(1);
3356 1 : m_pDoc->DeleteTab(0);
3357 1 : }
3358 :
3359 1 : void Test::testSheetCopy()
3360 : {
3361 1 : OUString aTabName("TestTab");
3362 1 : m_pDoc->InsertTab(0, aTabName);
3363 1 : CPPUNIT_ASSERT_MESSAGE("document should have one sheet to begin with.", m_pDoc->GetTableCount() == 1);
3364 : SCROW nRow1, nRow2;
3365 1 : bool bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3366 1 : CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3367 :
3368 : // Copy and test the result.
3369 1 : m_pDoc->CopyTab(0, 1);
3370 1 : CPPUNIT_ASSERT_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount() == 2);
3371 1 : bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3372 1 : CPPUNIT_ASSERT_MESSAGE("copied sheet should also have all rows visible as the original.", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3373 1 : m_pDoc->DeleteTab(1);
3374 :
3375 1 : m_pDoc->SetRowHidden(5, 10, 0, true);
3376 1 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3377 1 : CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3378 1 : bHidden = m_pDoc->RowHidden(5, 0, &nRow1, &nRow2);
3379 1 : CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3380 1 : bHidden = m_pDoc->RowHidden(11, 0, &nRow1, &nRow2);
3381 1 : CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3382 :
3383 : // Copy the sheet once again.
3384 1 : m_pDoc->CopyTab(0, 1);
3385 1 : CPPUNIT_ASSERT_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount() == 2);
3386 1 : bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3387 1 : CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3388 1 : bHidden = m_pDoc->RowHidden(5, 1, &nRow1, &nRow2);
3389 1 : CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3390 1 : bHidden = m_pDoc->RowHidden(11, 1, &nRow1, &nRow2);
3391 1 : CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3392 1 : m_pDoc->DeleteTab(1);
3393 1 : m_pDoc->DeleteTab(0);
3394 1 : }
3395 :
3396 1 : void Test::testSheetMove()
3397 : {
3398 1 : OUString aTabName("TestTab1");
3399 1 : m_pDoc->InsertTab(0, aTabName);
3400 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("document should have one sheet to begin with.", m_pDoc->GetTableCount(), static_cast<SCTAB>(1));
3401 : SCROW nRow1, nRow2;
3402 1 : bool bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3403 1 : CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3404 :
3405 : //test if inserting before another sheet works
3406 1 : m_pDoc->InsertTab(0, OUString("TestTab2"));
3407 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("document should have two sheets", m_pDoc->GetTableCount(), static_cast<SCTAB>(2));
3408 1 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3409 1 : CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3410 :
3411 : // Move and test the result.
3412 1 : m_pDoc->MoveTab(0, 1);
3413 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount(), static_cast<SCTAB>(2));
3414 1 : bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3415 1 : CPPUNIT_ASSERT_MESSAGE("copied sheet should also have all rows visible as the original.", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3416 1 : rtl::OUString aName;
3417 1 : m_pDoc->GetName(0, aName);
3418 1 : CPPUNIT_ASSERT_MESSAGE( "sheets should have changed places", aName == "TestTab1" );
3419 :
3420 1 : m_pDoc->SetRowHidden(5, 10, 0, true);
3421 1 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3422 1 : CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3423 1 : bHidden = m_pDoc->RowHidden(5, 0, &nRow1, &nRow2);
3424 1 : CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3425 1 : bHidden = m_pDoc->RowHidden(11, 0, &nRow1, &nRow2);
3426 1 : CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3427 :
3428 : // Move the sheet once again.
3429 1 : m_pDoc->MoveTab(1, 0);
3430 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount(), static_cast<SCTAB>(2));
3431 1 : bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3432 1 : CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3433 1 : bHidden = m_pDoc->RowHidden(5, 1, &nRow1, &nRow2);
3434 1 : CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3435 1 : bHidden = m_pDoc->RowHidden(11, 1, &nRow1, &nRow2);
3436 1 : CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3437 1 : m_pDoc->GetName(0, aName);
3438 1 : CPPUNIT_ASSERT_MESSAGE( "sheets should have changed places", aName == "TestTab2" );
3439 1 : m_pDoc->DeleteTab(1);
3440 1 : m_pDoc->DeleteTab(0);
3441 1 : }
3442 :
3443 5 : ScDocShell* findLoadedDocShellByName(const OUString& rName)
3444 : {
3445 5 : TypeId aType(TYPE(ScDocShell));
3446 5 : ScDocShell* pShell = static_cast<ScDocShell*>(SfxObjectShell::GetFirst(&aType, false));
3447 175 : while (pShell)
3448 : {
3449 168 : SfxMedium* pMedium = pShell->GetMedium();
3450 168 : if (pMedium)
3451 : {
3452 4 : OUString aName = pMedium->GetName();
3453 4 : if (aName.equals(rName))
3454 3 : return pShell;
3455 : }
3456 165 : pShell = static_cast<ScDocShell*>(SfxObjectShell::GetNext(*pShell, &aType, false));
3457 : }
3458 2 : return NULL;
3459 : }
3460 :
3461 2 : ScRange getCachedRange(const ScExternalRefCache::TableTypeRef& pCacheTab)
3462 : {
3463 2 : ScRange aRange;
3464 :
3465 2 : vector<SCROW> aRows;
3466 2 : pCacheTab->getAllRows(aRows);
3467 2 : vector<SCROW>::const_iterator itrRow = aRows.begin(), itrRowEnd = aRows.end();
3468 2 : bool bFirst = true;
3469 11 : for (; itrRow != itrRowEnd; ++itrRow)
3470 : {
3471 9 : SCROW nRow = *itrRow;
3472 9 : vector<SCCOL> aCols;
3473 9 : pCacheTab->getAllCols(nRow, aCols);
3474 9 : vector<SCCOL>::const_iterator itrCol = aCols.begin(), itrColEnd = aCols.end();
3475 27 : for (; itrCol != itrColEnd; ++itrCol)
3476 : {
3477 18 : SCCOL nCol = *itrCol;
3478 18 : if (bFirst)
3479 : {
3480 2 : aRange.aStart = ScAddress(nCol, nRow, 0);
3481 2 : aRange.aEnd = aRange.aStart;
3482 2 : bFirst = false;
3483 : }
3484 : else
3485 : {
3486 16 : if (nCol < aRange.aStart.Col())
3487 0 : aRange.aStart.SetCol(nCol);
3488 16 : else if (aRange.aEnd.Col() < nCol)
3489 2 : aRange.aEnd.SetCol(nCol);
3490 :
3491 16 : if (nRow < aRange.aStart.Row())
3492 0 : aRange.aStart.SetRow(nRow);
3493 16 : else if (aRange.aEnd.Row() < nRow)
3494 7 : aRange.aEnd.SetRow(nRow);
3495 : }
3496 : }
3497 9 : }
3498 2 : return aRange;
3499 : }
3500 :
3501 1 : void Test::testExternalRef()
3502 : {
3503 1 : ScDocShellRef xExtDocSh = new ScDocShell;
3504 1 : OUString aExtDocName("file:///extdata.fake");
3505 1 : OUString aExtSh1Name("Data1");
3506 1 : OUString aExtSh2Name("Data2");
3507 1 : OUString aExtSh3Name("Data3");
3508 1 : SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
3509 1 : xExtDocSh->DoInitNew(pMed);
3510 2 : CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
3511 1 : findLoadedDocShellByName(aExtDocName) != NULL);
3512 :
3513 : // Populate the external source document.
3514 1 : ScDocument* pExtDoc = xExtDocSh->GetDocument();
3515 1 : pExtDoc->InsertTab(0, aExtSh1Name);
3516 1 : pExtDoc->InsertTab(1, aExtSh2Name);
3517 1 : pExtDoc->InsertTab(2, aExtSh3Name);
3518 :
3519 1 : OUString name("Name");
3520 1 : OUString value("Value");
3521 1 : OUString andy("Andy");
3522 1 : OUString bruce("Bruce");
3523 1 : OUString charlie("Charlie");
3524 1 : OUString david("David");
3525 1 : OUString edward("Edward");
3526 1 : OUString frank("Frank");
3527 1 : OUString george("George");
3528 1 : OUString henry("Henry");
3529 :
3530 : // Sheet 1
3531 1 : pExtDoc->SetString(0, 0, 0, name);
3532 1 : pExtDoc->SetString(0, 1, 0, andy);
3533 1 : pExtDoc->SetString(0, 2, 0, bruce);
3534 1 : pExtDoc->SetString(0, 3, 0, charlie);
3535 1 : pExtDoc->SetString(0, 4, 0, david);
3536 1 : pExtDoc->SetString(1, 0, 0, value);
3537 1 : double val = 10;
3538 1 : pExtDoc->SetValue(1, 1, 0, val);
3539 1 : val = 11;
3540 1 : pExtDoc->SetValue(1, 2, 0, val);
3541 1 : val = 12;
3542 1 : pExtDoc->SetValue(1, 3, 0, val);
3543 1 : val = 13;
3544 1 : pExtDoc->SetValue(1, 4, 0, val);
3545 :
3546 : // Sheet 2 remains empty.
3547 :
3548 : // Sheet 3
3549 1 : pExtDoc->SetString(0, 0, 2, name);
3550 1 : pExtDoc->SetString(0, 1, 2, edward);
3551 1 : pExtDoc->SetString(0, 2, 2, frank);
3552 1 : pExtDoc->SetString(0, 3, 2, george);
3553 1 : pExtDoc->SetString(0, 4, 2, henry);
3554 1 : pExtDoc->SetString(1, 0, 2, value);
3555 1 : val = 99;
3556 1 : pExtDoc->SetValue(1, 1, 2, val);
3557 1 : val = 98;
3558 1 : pExtDoc->SetValue(1, 2, 2, val);
3559 1 : val = 97;
3560 1 : pExtDoc->SetValue(1, 3, 2, val);
3561 1 : val = 96;
3562 1 : pExtDoc->SetValue(1, 4, 2, val);
3563 :
3564 : // Test external refernces on the main document while the external
3565 : // document is still in memory.
3566 1 : OUString test;
3567 1 : m_pDoc->InsertTab(0, OUString("Test Sheet"));
3568 1 : m_pDoc->SetString(0, 0, 0, OUString("='file:///extdata.fake'#Data1.A1"));
3569 1 : m_pDoc->GetString(0, 0, 0, test);
3570 1 : CPPUNIT_ASSERT_MESSAGE("Value is different from the original", test.equals(name));
3571 :
3572 : // After the initial access to the external document, the external ref
3573 : // manager should create sheet cache entries for *all* sheets from that
3574 : // document. Note that the doc may have more than 3 sheets but ensure
3575 : // that the first 3 are what we expect.
3576 1 : ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
3577 1 : sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
3578 1 : vector<OUString> aTabNames;
3579 1 : pRefMgr->getAllCachedTableNames(nFileId, aTabNames);
3580 1 : CPPUNIT_ASSERT_MESSAGE("There should be at least 3 sheets.", aTabNames.size() >= 3);
3581 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[0].equals(aExtSh1Name));
3582 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[1].equals(aExtSh2Name));
3583 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[2].equals(aExtSh3Name));
3584 :
3585 1 : m_pDoc->SetString(1, 0, 0, OUString("='file:///extdata.fake'#Data1.B1"));
3586 1 : m_pDoc->GetString(1, 0, 0, test);
3587 1 : CPPUNIT_ASSERT_MESSAGE("Value is different from the original", test.equals(value));
3588 :
3589 1 : m_pDoc->SetString(0, 1, 0, OUString("='file:///extdata.fake'#Data1.A2"));
3590 1 : m_pDoc->SetString(0, 2, 0, OUString("='file:///extdata.fake'#Data1.A3"));
3591 1 : m_pDoc->SetString(0, 3, 0, OUString("='file:///extdata.fake'#Data1.A4"));
3592 1 : m_pDoc->SetString(0, 4, 0, OUString("='file:///extdata.fake'#Data1.A5"));
3593 1 : m_pDoc->SetString(0, 5, 0, OUString("='file:///extdata.fake'#Data1.A6"));
3594 :
3595 : {
3596 : // Referencing an empty cell should display '0'.
3597 1 : const char* pChecks[] = { "Andy", "Bruce", "Charlie", "David", "0" };
3598 6 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3599 : {
3600 5 : m_pDoc->GetString(0, static_cast<SCROW>(i+1), 0, test);
3601 5 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3602 : }
3603 : }
3604 1 : m_pDoc->SetString(1, 1, 0, OUString("='file:///extdata.fake'#Data1.B2"));
3605 1 : m_pDoc->SetString(1, 2, 0, OUString("='file:///extdata.fake'#Data1.B3"));
3606 1 : m_pDoc->SetString(1, 3, 0, OUString("='file:///extdata.fake'#Data1.B4"));
3607 1 : m_pDoc->SetString(1, 4, 0, OUString("='file:///extdata.fake'#Data1.B5"));
3608 1 : m_pDoc->SetString(1, 5, 0, OUString("='file:///extdata.fake'#Data1.B6"));
3609 : {
3610 1 : double pChecks[] = { 10, 11, 12, 13, 0 };
3611 6 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3612 : {
3613 5 : m_pDoc->GetValue(1, static_cast<SCROW>(i+1), 0, val);
3614 5 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", val == pChecks[i]);
3615 : }
3616 : }
3617 :
3618 1 : m_pDoc->SetString(2, 0, 0, OUString("='file:///extdata.fake'#Data3.A1"));
3619 1 : m_pDoc->SetString(2, 1, 0, OUString("='file:///extdata.fake'#Data3.A2"));
3620 1 : m_pDoc->SetString(2, 2, 0, OUString("='file:///extdata.fake'#Data3.A3"));
3621 1 : m_pDoc->SetString(2, 3, 0, OUString("='file:///extdata.fake'#Data3.A4"));
3622 : {
3623 1 : const char* pChecks[] = { "Name", "Edward", "Frank", "George" };
3624 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3625 : {
3626 4 : m_pDoc->GetString(2, static_cast<SCROW>(i), 0, test);
3627 4 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3628 : }
3629 : }
3630 :
3631 1 : m_pDoc->SetString(3, 0, 0, OUString("='file:///extdata.fake'#Data3.B1"));
3632 1 : m_pDoc->SetString(3, 1, 0, OUString("='file:///extdata.fake'#Data3.B2"));
3633 1 : m_pDoc->SetString(3, 2, 0, OUString("='file:///extdata.fake'#Data3.B3"));
3634 1 : m_pDoc->SetString(3, 3, 0, OUString("='file:///extdata.fake'#Data3.B4"));
3635 : {
3636 1 : const char* pChecks[] = { "Value", "99", "98", "97" };
3637 5 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3638 : {
3639 4 : m_pDoc->GetString(3, static_cast<SCROW>(i), 0, test);
3640 4 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3641 : }
3642 : }
3643 :
3644 : // At this point, all accessed cell data from the external document should
3645 : // have been cached.
3646 : ScExternalRefCache::TableTypeRef pCacheTab = pRefMgr->getCacheTable(
3647 1 : nFileId, aExtSh1Name, false);
3648 1 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 1 should exist.", pCacheTab.get() != NULL);
3649 1 : ScRange aCachedRange = getCachedRange(pCacheTab);
3650 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected cached data range.",
3651 : aCachedRange.aStart.Col() == 0 && aCachedRange.aEnd.Col() == 1 &&
3652 1 : aCachedRange.aStart.Row() == 0 && aCachedRange.aEnd.Row() == 4);
3653 :
3654 : // Sheet2 is not referenced at all; the cache table shouldn't even exist.
3655 1 : pCacheTab = pRefMgr->getCacheTable(nFileId, aExtSh2Name, false);
3656 1 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 2 should *not* exist.", pCacheTab.get() == NULL);
3657 :
3658 : // Sheet3's row 5 is not referenced; it should not be cached.
3659 1 : pCacheTab = pRefMgr->getCacheTable(nFileId, aExtSh3Name, false);
3660 1 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 3 should exist.", pCacheTab.get() != NULL);
3661 1 : aCachedRange = getCachedRange(pCacheTab);
3662 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected cached data range.",
3663 : aCachedRange.aStart.Col() == 0 && aCachedRange.aEnd.Col() == 1 &&
3664 1 : aCachedRange.aStart.Row() == 0 && aCachedRange.aEnd.Row() == 3);
3665 :
3666 : // Unload the external document shell.
3667 1 : xExtDocSh->DoClose();
3668 2 : CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
3669 1 : findLoadedDocShellByName(aExtDocName) == NULL);
3670 :
3671 1 : m_pDoc->DeleteTab(0);
3672 1 : }
3673 :
3674 1 : void testExtRefFuncT(ScDocument* pDoc, ScDocument* pExtDoc)
3675 : {
3676 1 : clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
3677 1 : clearRange(pExtDoc, ScRange(0, 0, 0, 1, 9, 0));
3678 :
3679 1 : pExtDoc->SetString(0, 0, 0, rtl::OUString("'1.2"));
3680 1 : pExtDoc->SetString(0, 1, 0, rtl::OUString("Foo"));
3681 1 : pExtDoc->SetValue(0, 2, 0, 12.3);
3682 1 : pDoc->SetString(0, 0, 0, rtl::OUString("=T('file:///extdata.fake'#Data.A1)"));
3683 1 : pDoc->SetString(0, 1, 0, rtl::OUString("=T('file:///extdata.fake'#Data.A2)"));
3684 1 : pDoc->SetString(0, 2, 0, rtl::OUString("=T('file:///extdata.fake'#Data.A3)"));
3685 1 : pDoc->CalcAll();
3686 :
3687 1 : rtl::OUString aRes = pDoc->GetString(0, 0, 0);
3688 1 : CPPUNIT_ASSERT_MESSAGE( "Unexpected result with T.", aRes == "1.2" );
3689 1 : aRes = pDoc->GetString(0, 1, 0);
3690 1 : CPPUNIT_ASSERT_MESSAGE( "Unexpected result with T.", aRes == "Foo" );
3691 1 : aRes = pDoc->GetString(0, 2, 0);
3692 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected result with T.", aRes.isEmpty());
3693 1 : }
3694 :
3695 1 : void Test::testExternalRefFunctions()
3696 : {
3697 1 : ScDocShellRef xExtDocSh = new ScDocShell;
3698 1 : OUString aExtDocName("file:///extdata.fake");
3699 1 : SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
3700 1 : xExtDocSh->DoInitNew(pMed);
3701 2 : CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
3702 1 : findLoadedDocShellByName(aExtDocName) != NULL);
3703 :
3704 1 : ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
3705 1 : CPPUNIT_ASSERT_MESSAGE("external reference manager doesn't exist.", pRefMgr);
3706 1 : sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
3707 1 : const OUString* pFileName = pRefMgr->getExternalFileName(nFileId);
3708 2 : CPPUNIT_ASSERT_MESSAGE("file name registration has somehow failed.",
3709 1 : pFileName && pFileName->equals(aExtDocName));
3710 :
3711 : // Populate the external source document.
3712 1 : ScDocument* pExtDoc = xExtDocSh->GetDocument();
3713 1 : pExtDoc->InsertTab(0, rtl::OUString("Data"));
3714 1 : double val = 1;
3715 1 : pExtDoc->SetValue(0, 0, 0, val);
3716 : // leave cell B1 empty.
3717 1 : val = 2;
3718 1 : pExtDoc->SetValue(0, 1, 0, val);
3719 1 : pExtDoc->SetValue(1, 1, 0, val);
3720 1 : val = 3;
3721 1 : pExtDoc->SetValue(0, 2, 0, val);
3722 1 : pExtDoc->SetValue(1, 2, 0, val);
3723 1 : val = 4;
3724 1 : pExtDoc->SetValue(0, 3, 0, val);
3725 1 : pExtDoc->SetValue(1, 3, 0, val);
3726 :
3727 1 : m_pDoc->InsertTab(0, rtl::OUString("Test"));
3728 :
3729 : struct {
3730 : const char* pFormula; double fResult;
3731 : } aChecks[] = {
3732 : { "=SUM('file:///extdata.fake'#Data.A1:A4)", 10 },
3733 : { "=SUM('file:///extdata.fake'#Data.B1:B4)", 9 },
3734 : { "=AVERAGE('file:///extdata.fake'#Data.A1:A4)", 2.5 },
3735 : { "=AVERAGE('file:///extdata.fake'#Data.B1:B4)", 3 },
3736 : { "=COUNT('file:///extdata.fake'#Data.A1:A4)", 4 },
3737 : { "=COUNT('file:///extdata.fake'#Data.B1:B4)", 3 }
3738 1 : };
3739 :
3740 7 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3741 : {
3742 6 : m_pDoc->SetString(0, 0, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
3743 6 : m_pDoc->CalcAll();
3744 6 : m_pDoc->GetValue(0, 0, 0, val);
3745 6 : CPPUNIT_ASSERT_MESSAGE("unexpected result involving external ranges.", val == aChecks[i].fResult);
3746 : }
3747 :
3748 1 : pRefMgr->clearCache(nFileId);
3749 1 : testExtRefFuncT(m_pDoc, pExtDoc);
3750 :
3751 : // Unload the external document shell.
3752 1 : xExtDocSh->DoClose();
3753 2 : CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
3754 1 : findLoadedDocShellByName(aExtDocName) == NULL);
3755 :
3756 1 : m_pDoc->DeleteTab(0);
3757 1 : }
3758 :
3759 1 : void Test::testDataArea()
3760 : {
3761 1 : m_pDoc->InsertTab(0, OUString("Data"));
3762 :
3763 : // Totally empty sheet should be rightfully considered empty in all accounts.
3764 1 : CPPUNIT_ASSERT_MESSAGE("Sheet is expected to be empty.", m_pDoc->IsPrintEmpty(0, 0, 0, 100, 100));
3765 1 : CPPUNIT_ASSERT_MESSAGE("Sheet is expected to be empty.", m_pDoc->IsBlockEmpty(0, 0, 0, 100, 100));
3766 :
3767 : // Now, set borders in some cells....
3768 1 : ::editeng::SvxBorderLine aLine(NULL, 50, table::BorderLineStyle::SOLID);
3769 1 : SvxBoxItem aBorderItem(ATTR_BORDER);
3770 1 : aBorderItem.SetLine(&aLine, BOX_LINE_LEFT);
3771 1 : aBorderItem.SetLine(&aLine, BOX_LINE_RIGHT);
3772 101 : for (SCROW i = 0; i < 100; ++i)
3773 : // Set borders from row 1 to 100.
3774 100 : m_pDoc->ApplyAttr(0, i, 0, aBorderItem);
3775 :
3776 : // Now the sheet is considered non-empty for printing purposes, but still
3777 : // be empty in all the other cases.
3778 2 : CPPUNIT_ASSERT_MESSAGE("Empty sheet with borders should be printable.",
3779 1 : !m_pDoc->IsPrintEmpty(0, 0, 0, 100, 100));
3780 2 : CPPUNIT_ASSERT_MESSAGE("But it should still be considered empty in all the other cases.",
3781 1 : m_pDoc->IsBlockEmpty(0, 0, 0, 100, 100));
3782 :
3783 : // Adding a real cell content should turn the block non-empty.
3784 1 : m_pDoc->SetString(0, 0, 0, OUString("Some text"));
3785 2 : CPPUNIT_ASSERT_MESSAGE("Now the block should not be empty with a real cell content.",
3786 1 : !m_pDoc->IsBlockEmpty(0, 0, 0, 100, 100));
3787 :
3788 : // TODO: Add more tests for normal data area calculation.
3789 :
3790 1 : m_pDoc->DeleteTab(0);
3791 1 : }
3792 :
3793 1 : void Test::testStreamValid()
3794 : {
3795 1 : m_pDoc->InsertTab(0, OUString("Sheet1"));
3796 1 : m_pDoc->InsertTab(1, OUString("Sheet2"));
3797 1 : m_pDoc->InsertTab(2, OUString("Sheet3"));
3798 1 : m_pDoc->InsertTab(3, OUString("Sheet4"));
3799 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("We should have 4 sheet instances.", m_pDoc->GetTableCount(), static_cast<SCTAB>(4));
3800 :
3801 1 : OUString a1("A1");
3802 1 : OUString a2("A2");
3803 1 : OUString test;
3804 :
3805 : // Put values into Sheet1.
3806 1 : m_pDoc->SetString(0, 0, 0, a1);
3807 1 : m_pDoc->SetString(0, 1, 0, a2);
3808 1 : m_pDoc->GetString(0, 0, 0, test);
3809 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet1.A1", test.equals(a1));
3810 1 : m_pDoc->GetString(0, 1, 0, test);
3811 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet1.A2", test.equals(a2));
3812 :
3813 : // Put formulas into Sheet2 to Sheet4 to reference values from Sheet1.
3814 1 : m_pDoc->SetString(0, 0, 1, OUString("=Sheet1.A1"));
3815 1 : m_pDoc->SetString(0, 1, 1, OUString("=Sheet1.A2"));
3816 1 : m_pDoc->SetString(0, 0, 2, OUString("=Sheet1.A1"));
3817 1 : m_pDoc->SetString(0, 0, 3, OUString("=Sheet1.A2"));
3818 :
3819 1 : m_pDoc->GetString(0, 0, 1, test);
3820 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet2.A1", test.equals(a1));
3821 1 : m_pDoc->GetString(0, 1, 1, test);
3822 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet2.A2", test.equals(a2));
3823 1 : m_pDoc->GetString(0, 0, 2, test);
3824 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet3.A1", test.equals(a1));
3825 1 : m_pDoc->GetString(0, 0, 3, test);
3826 1 : CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet3.A1", test.equals(a2));
3827 :
3828 : // Set all sheet streams valid after all the initial cell values are in
3829 : // place. In reality we need to have real XML streams stored in order to
3830 : // claim they are valid, but we are just testing the flag values here.
3831 1 : m_pDoc->SetStreamValid(0, true);
3832 1 : m_pDoc->SetStreamValid(1, true);
3833 1 : m_pDoc->SetStreamValid(2, true);
3834 1 : m_pDoc->SetStreamValid(3, true);
3835 1 : CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(0));
3836 1 : CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(1));
3837 1 : CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(2));
3838 1 : CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(3));
3839 :
3840 : // Now, insert a new row at row 2 position on Sheet1. This will move cell
3841 : // A2 downward but cell A1 remains unmoved.
3842 1 : m_pDoc->InsertRow(0, 0, MAXCOL, 0, 1, 2);
3843 1 : m_pDoc->GetString(0, 0, 0, test);
3844 1 : CPPUNIT_ASSERT_MESSAGE("Cell A1 should not have moved.", test.equals(a1));
3845 1 : m_pDoc->GetString(0, 3, 0, test);
3846 1 : CPPUNIT_ASSERT_MESSAGE("the old cell A2 should now be at A4.", test.equals(a2));
3847 1 : const ScBaseCell* pCell = m_pDoc->GetCell(ScAddress(0, 1, 0));
3848 1 : CPPUNIT_ASSERT_MESSAGE("Cell A2 should be empty.", pCell == NULL);
3849 1 : pCell = m_pDoc->GetCell(ScAddress(0, 2, 0));
3850 1 : CPPUNIT_ASSERT_MESSAGE("Cell A3 should be empty.", pCell == NULL);
3851 :
3852 : // After the move, Sheet1, Sheet2, and Sheet4 should have their stream
3853 : // invalidated, whereas Sheet3's stream should still be valid.
3854 1 : CPPUNIT_ASSERT_MESSAGE("Stream should have been invalidated.", !m_pDoc->IsStreamValid(0));
3855 1 : CPPUNIT_ASSERT_MESSAGE("Stream should have been invalidated.", !m_pDoc->IsStreamValid(1));
3856 1 : CPPUNIT_ASSERT_MESSAGE("Stream should have been invalidated.", !m_pDoc->IsStreamValid(3));
3857 1 : CPPUNIT_ASSERT_MESSAGE("Stream should still be valid.", m_pDoc->IsStreamValid(2));
3858 :
3859 1 : m_pDoc->DeleteTab(3);
3860 1 : m_pDoc->DeleteTab(2);
3861 1 : m_pDoc->DeleteTab(1);
3862 1 : m_pDoc->DeleteTab(0);
3863 1 : }
3864 :
3865 1 : void Test::testFunctionLists()
3866 : {
3867 : const char* aDataBase[] = {
3868 : "DAVERAGE",
3869 : "DCOUNT",
3870 : "DCOUNTA",
3871 : "DGET",
3872 : "DMAX",
3873 : "DMIN",
3874 : "DPRODUCT",
3875 : "DSTDEV",
3876 : "DSTDEVP",
3877 : "DSUM",
3878 : "DVAR",
3879 : "DVARP",
3880 : 0
3881 1 : };
3882 :
3883 : const char* aDateTime[] = {
3884 : "DATE",
3885 : "DATEDIF",
3886 : "DATEVALUE",
3887 : "DAY",
3888 : "DAYS",
3889 : "DAYS360",
3890 : "EASTERSUNDAY",
3891 : "HOUR",
3892 : "MINUTE",
3893 : "MONTH",
3894 : "NOW",
3895 : "SECOND",
3896 : "TIME",
3897 : "TIMEVALUE",
3898 : "TODAY",
3899 : "WEEKDAY",
3900 : "WEEKNUM",
3901 : "YEAR",
3902 : 0
3903 1 : };
3904 :
3905 : const char* aFinancial[] = {
3906 : "CUMIPMT",
3907 : "CUMPRINC",
3908 : "DB",
3909 : "DDB",
3910 : "DURATION",
3911 : "EFFECTIVE",
3912 : "FV",
3913 : "IPMT",
3914 : "IRR",
3915 : "ISPMT",
3916 : "MIRR",
3917 : "NOMINAL",
3918 : "NPER",
3919 : "NPV",
3920 : "PMT",
3921 : "PPMT",
3922 : "PV",
3923 : "RATE",
3924 : "RRI",
3925 : "SLN",
3926 : "SYD",
3927 : "VDB",
3928 : 0
3929 1 : };
3930 :
3931 : const char* aInformation[] = {
3932 : "CELL",
3933 : "CURRENT",
3934 : "FORMULA",
3935 : "INFO",
3936 : "ISBLANK",
3937 : "ISERR",
3938 : "ISERROR",
3939 : "ISFORMULA",
3940 : "ISLOGICAL",
3941 : "ISNA",
3942 : "ISNONTEXT",
3943 : "ISNUMBER",
3944 : "ISREF",
3945 : "ISTEXT",
3946 : "N",
3947 : "NA",
3948 : "TYPE",
3949 : 0
3950 1 : };
3951 :
3952 : const char* aLogical[] = {
3953 : "AND",
3954 : "FALSE",
3955 : "IF",
3956 : "NOT",
3957 : "OR",
3958 : "TRUE",
3959 : "XOR",
3960 : 0
3961 1 : };
3962 :
3963 : const char* aMathematical[] = {
3964 : "ABS",
3965 : "ACOS",
3966 : "ACOSH",
3967 : "ACOT",
3968 : "ACOTH",
3969 : "ASIN",
3970 : "ASINH",
3971 : "ATAN",
3972 : "ATAN2",
3973 : "ATANH",
3974 : "AVERAGEIF",
3975 : "AVERAGEIFS",
3976 : "BITAND",
3977 : "BITLSHIFT",
3978 : "BITOR",
3979 : "BITRSHIFT",
3980 : "BITXOR",
3981 : "CEILING",
3982 : "COMBIN",
3983 : "COMBINA",
3984 : "CONVERT",
3985 : "COS",
3986 : "COSH",
3987 : "COT",
3988 : "COTH",
3989 : "COUNTBLANK",
3990 : "COUNTIF",
3991 : "COUNTIFS",
3992 : "CSC",
3993 : "CSCH",
3994 : "DEGREES",
3995 : "EUROCONVERT",
3996 : "EVEN",
3997 : "EXP",
3998 : "FACT",
3999 : "FLOOR",
4000 : "GCD",
4001 : "INT",
4002 : "ISEVEN",
4003 : "ISODD",
4004 : "LCM",
4005 : "LN",
4006 : "LOG",
4007 : "LOG10",
4008 : "MOD",
4009 : "ODD",
4010 : "PI",
4011 : "POWER",
4012 : "PRODUCT",
4013 : "RADIANS",
4014 : "RAND",
4015 : "ROUND",
4016 : "ROUNDDOWN",
4017 : "ROUNDUP",
4018 : "SEC",
4019 : "SECH",
4020 : "SIGN",
4021 : "SIN",
4022 : "SINH",
4023 : "SQRT",
4024 : "SUBTOTAL",
4025 : "SUM",
4026 : "SUMIF",
4027 : "SUMIFS",
4028 : "SUMSQ",
4029 : "TAN",
4030 : "TANH",
4031 : "TRUNC",
4032 : 0
4033 1 : };
4034 :
4035 : const char* aArray[] = {
4036 : "FREQUENCY",
4037 : "GROWTH",
4038 : "LINEST",
4039 : "LOGEST",
4040 : "MDETERM",
4041 : "MINVERSE",
4042 : "MMULT",
4043 : "MUNIT",
4044 : "SUMPRODUCT",
4045 : "SUMX2MY2",
4046 : "SUMX2PY2",
4047 : "SUMXMY2",
4048 : "TRANSPOSE",
4049 : "TREND",
4050 : 0
4051 1 : };
4052 :
4053 : const char* aStatistical[] = {
4054 : "AVEDEV",
4055 : "AVERAGE",
4056 : "AVERAGEA",
4057 : "B",
4058 : "BETADIST",
4059 : "BETAINV",
4060 : "BINOMDIST",
4061 : "CHIDIST",
4062 : "CHIINV",
4063 : "CHISQDIST",
4064 : "CHISQINV",
4065 : "CHITEST",
4066 : "CONFIDENCE",
4067 : "CORREL",
4068 : "COUNT",
4069 : "COUNTA",
4070 : "COVAR",
4071 : "CRITBINOM",
4072 : "DEVSQ",
4073 : "EXPONDIST",
4074 : "FDIST",
4075 : "FINV",
4076 : "FISHER",
4077 : "FISHERINV",
4078 : "FORECAST",
4079 : "FTEST",
4080 : "GAMMA",
4081 : "GAMMADIST",
4082 : "GAMMAINV",
4083 : "GAMMALN",
4084 : "GAUSS",
4085 : "GEOMEAN",
4086 : "HARMEAN",
4087 : "HYPGEOMDIST",
4088 : "INTERCEPT",
4089 : "KURT",
4090 : "LARGE",
4091 : "LOGINV",
4092 : "LOGNORMDIST",
4093 : "MAX",
4094 : "MAXA",
4095 : "MEDIAN",
4096 : "MIN",
4097 : "MINA",
4098 : "MODE",
4099 : "NEGBINOMDIST",
4100 : "NORMDIST",
4101 : "NORMINV",
4102 : "NORMSDIST",
4103 : "NORMSINV",
4104 : "PEARSON",
4105 : "PERCENTILE",
4106 : "PERCENTRANK",
4107 : "PERMUT",
4108 : "PERMUTATIONA",
4109 : "PHI",
4110 : "POISSON",
4111 : "PROB",
4112 : "QUARTILE",
4113 : "RANK",
4114 : "RSQ",
4115 : "SKEW",
4116 : "SLOPE",
4117 : "SMALL",
4118 : "STANDARDIZE",
4119 : "STDEV",
4120 : "STDEVA",
4121 : "STDEVP",
4122 : "STDEVPA",
4123 : "STEYX",
4124 : "TDIST",
4125 : "TINV",
4126 : "TRIMMEAN",
4127 : "TTEST",
4128 : "VAR",
4129 : "VARA",
4130 : "VARP",
4131 : "VARPA",
4132 : "WEIBULL",
4133 : "ZTEST",
4134 : 0
4135 1 : };
4136 :
4137 : const char* aSpreadsheet[] = {
4138 : "ADDRESS",
4139 : "AREAS",
4140 : "CHOOSE",
4141 : "COLUMN",
4142 : "COLUMNS",
4143 : "DDE",
4144 : "ERRORTYPE",
4145 : "GETPIVOTDATA",
4146 : "HLOOKUP",
4147 : "HYPERLINK",
4148 : "INDEX",
4149 : "INDIRECT",
4150 : "LOOKUP",
4151 : "MATCH",
4152 : "OFFSET",
4153 : "ROW",
4154 : "ROWS",
4155 : "SHEET",
4156 : "SHEETS",
4157 : "STYLE",
4158 : "VLOOKUP",
4159 : 0
4160 1 : };
4161 :
4162 : const char* aText[] = {
4163 : "ARABIC",
4164 : "ASC",
4165 : "BAHTTEXT",
4166 : "BASE",
4167 : "CHAR",
4168 : "CLEAN",
4169 : "CODE",
4170 : "CONCATENATE",
4171 : "DECIMAL",
4172 : "DOLLAR",
4173 : "EXACT",
4174 : "FIND",
4175 : "FIXED",
4176 : "JIS",
4177 : "LEFT",
4178 : "LEN",
4179 : "LOWER",
4180 : "MID",
4181 : "PROPER",
4182 : "REPLACE",
4183 : "REPT",
4184 : "RIGHT",
4185 : "ROMAN",
4186 : "SEARCH",
4187 : "SUBSTITUTE",
4188 : "T",
4189 : "TEXT",
4190 : "TRIM",
4191 : "UNICHAR",
4192 : "UNICODE",
4193 : "UPPER",
4194 : "VALUE",
4195 : 0
4196 1 : };
4197 :
4198 : struct {
4199 : const char* Category; const char** Functions;
4200 : } aTests[] = {
4201 : { "Database", aDataBase },
4202 : { "Date&Time", aDateTime },
4203 : { "Financial", aFinancial },
4204 : { "Information", aInformation },
4205 : { "Logical", aLogical },
4206 : { "Mathematical", aMathematical },
4207 : { "Array", aArray },
4208 : { "Statistical", aStatistical },
4209 : { "Spreadsheet", aSpreadsheet },
4210 : { "Text", aText },
4211 : { "Add-in", 0 },
4212 : { 0, 0 }
4213 1 : };
4214 :
4215 1 : ScFunctionMgr* pFuncMgr = ScGlobal::GetStarCalcFunctionMgr();
4216 1 : sal_uInt32 n = pFuncMgr->getCount();
4217 12 : for (sal_uInt32 i = 0; i < n; ++i)
4218 : {
4219 11 : const formula::IFunctionCategory* pCat = pFuncMgr->getCategory(i);
4220 11 : CPPUNIT_ASSERT_MESSAGE("Unexpected category name", pCat->getName().equalsAscii(aTests[i].Category));
4221 11 : sal_uInt32 nFuncCount = pCat->getCount();
4222 302 : for (sal_uInt32 j = 0; j < nFuncCount; ++j)
4223 : {
4224 291 : const formula::IFunctionDescription* pFunc = pCat->getFunction(j);
4225 291 : CPPUNIT_ASSERT_MESSAGE("Unexpected function name", pFunc->getFunctionName().equalsAscii(aTests[i].Functions[j]));
4226 : }
4227 : }
4228 1 : }
4229 :
4230 1 : void Test::testGraphicsInGroup()
4231 : {
4232 1 : OUString aTabName("TestTab");
4233 1 : m_pDoc->InsertTab(0, aTabName);
4234 1 : CPPUNIT_ASSERT_MESSAGE("document should have one sheet to begin with.", m_pDoc->GetTableCount() == 1);
4235 : SCROW nRow1, nRow2;
4236 1 : bool bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4237 1 : CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
4238 :
4239 1 : m_pDoc->InitDrawLayer();
4240 1 : ScDrawLayer *pDrawLayer = m_pDoc->GetDrawLayer();
4241 1 : CPPUNIT_ASSERT_MESSAGE("must have a draw layer", pDrawLayer != NULL);
4242 1 : SdrPage* pPage = pDrawLayer->GetPage(0);
4243 1 : CPPUNIT_ASSERT_MESSAGE("must have a draw page", pPage != NULL);
4244 :
4245 : {
4246 : //Add a square
4247 1 : Rectangle aOrigRect(2,2,100,100);
4248 1 : SdrRectObj *pObj = new SdrRectObj(aOrigRect);
4249 1 : pPage->InsertObject(pObj);
4250 1 : const Rectangle &rNewRect = pObj->GetLogicRect();
4251 1 : CPPUNIT_ASSERT_MESSAGE("must have equal position and size", aOrigRect == rNewRect);
4252 :
4253 1 : ScDrawLayer::SetPageAnchored(*pObj);
4254 :
4255 : //Use a range of rows guaranteed to include all of the square
4256 1 : m_pDoc->ShowRows(0, 100, 0, false);
4257 1 : m_pDoc->SetDrawPageSize(0);
4258 1 : CPPUNIT_ASSERT_MESSAGE("Should not change when page anchored", aOrigRect == rNewRect);
4259 1 : m_pDoc->ShowRows(0, 100, 0, true);
4260 1 : m_pDoc->SetDrawPageSize(0);
4261 1 : CPPUNIT_ASSERT_MESSAGE("Should not change when page anchored", aOrigRect == rNewRect);
4262 :
4263 1 : ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4264 1 : CPPUNIT_ASSERT_MESSAGE("That shouldn't change size or positioning", aOrigRect == rNewRect);
4265 :
4266 1 : m_pDoc->ShowRows(0, 100, 0, false);
4267 1 : m_pDoc->SetDrawPageSize(0);
4268 2 : CPPUNIT_ASSERT_MESSAGE("Left and Right should be unchanged",
4269 1 : aOrigRect.Left() == rNewRect.Left() && aOrigRect.Right() == rNewRect.Right());
4270 2 : CPPUNIT_ASSERT_MESSAGE("Height should be minimum allowed height",
4271 1 : (rNewRect.Bottom() - rNewRect.Top()) <= 1);
4272 1 : m_pDoc->ShowRows(0, 100, 0, true);
4273 1 : m_pDoc->SetDrawPageSize(0);
4274 1 : CPPUNIT_ASSERT_MESSAGE("Should not change when page anchored", aOrigRect == rNewRect);
4275 : }
4276 :
4277 : {
4278 : // Add a circle.
4279 1 : Rectangle aOrigRect = Rectangle(10,10,210,210); // 200 x 200
4280 1 : SdrCircObj* pObj = new SdrCircObj(OBJ_CIRC, aOrigRect);
4281 1 : pPage->InsertObject(pObj);
4282 1 : const Rectangle& rNewRect = pObj->GetLogicRect();
4283 2 : CPPUNIT_ASSERT_MESSAGE("Position and size of the circle shouldn't change when inserted into the page.",
4284 1 : aOrigRect == rNewRect);
4285 :
4286 1 : ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4287 2 : CPPUNIT_ASSERT_MESSAGE("Size changed when cell anchored. Not good.",
4288 1 : aOrigRect == rNewRect);
4289 :
4290 : // Insert 2 rows at the top. This should push the circle object down.
4291 1 : m_pDoc->InsertRow(0, 0, MAXCOL, 0, 0, 2);
4292 1 : m_pDoc->SetDrawPageSize(0);
4293 :
4294 : // Make sure the size of the circle is still identical.
4295 2 : CPPUNIT_ASSERT_MESSAGE("Size of the circle has changed, but shouldn't!",
4296 1 : aOrigRect.GetSize() == rNewRect.GetSize());
4297 :
4298 : // Delete 2 rows at the top. This should bring the circle object to its original position.
4299 1 : m_pDoc->DeleteRow(0, 0, MAXCOL, 0, 0, 2);
4300 1 : m_pDoc->SetDrawPageSize(0);
4301 1 : CPPUNIT_ASSERT_MESSAGE("Failed to move back to its original position.", aOrigRect == rNewRect);
4302 : }
4303 :
4304 : {
4305 : // Add a line.
4306 1 : basegfx::B2DPolygon aTempPoly;
4307 1 : Point aStartPos(10,300), aEndPos(110,200); // bottom-left to top-right.
4308 1 : Rectangle aOrigRect(10,200,110,300); // 100 x 100
4309 1 : aTempPoly.append(basegfx::B2DPoint(aStartPos.X(), aStartPos.Y()));
4310 1 : aTempPoly.append(basegfx::B2DPoint(aEndPos.X(), aEndPos.Y()));
4311 1 : SdrPathObj* pObj = new SdrPathObj(OBJ_LINE, basegfx::B2DPolyPolygon(aTempPoly));
4312 1 : pObj->NbcSetLogicRect(aOrigRect);
4313 1 : pPage->InsertObject(pObj);
4314 1 : const Rectangle& rNewRect = pObj->GetLogicRect();
4315 1 : CPPUNIT_ASSERT_MESSAGE("Size differ.", aOrigRect == rNewRect);
4316 :
4317 1 : ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4318 2 : CPPUNIT_ASSERT_MESSAGE("Size changed when cell-anchored. Not good.",
4319 1 : aOrigRect == rNewRect);
4320 :
4321 : // Insert 2 rows at the top and delete them immediately.
4322 1 : m_pDoc->InsertRow(0, 0, MAXCOL, 0, 0, 2);
4323 1 : m_pDoc->DeleteRow(0, 0, MAXCOL, 0, 0, 2);
4324 1 : m_pDoc->SetDrawPageSize(0);
4325 2 : CPPUNIT_ASSERT_MESSAGE("Size of a line object changed after row insertion and removal.",
4326 1 : aOrigRect == rNewRect);
4327 :
4328 1 : sal_Int32 n = pObj->GetPointCount();
4329 1 : CPPUNIT_ASSERT_MESSAGE("There should be exactly 2 points in a line object.", n == 2);
4330 2 : CPPUNIT_ASSERT_MESSAGE("Line shape has changed.",
4331 2 : aStartPos == pObj->GetPoint(0) && aEndPos == pObj->GetPoint(1));
4332 : }
4333 :
4334 1 : m_pDoc->DeleteTab(0);
4335 1 : }
4336 :
4337 1 : void Test::testGraphicsOnSheetMove()
4338 : {
4339 1 : m_pDoc->InsertTab(0, rtl::OUString("Tab1"));
4340 1 : m_pDoc->InsertTab(1, rtl::OUString("Tab2"));
4341 1 : CPPUNIT_ASSERT_MESSAGE("There should be only 2 sheets to begin with", m_pDoc->GetTableCount() == 2);
4342 :
4343 1 : m_pDoc->InitDrawLayer();
4344 1 : ScDrawLayer* pDrawLayer = m_pDoc->GetDrawLayer();
4345 1 : CPPUNIT_ASSERT_MESSAGE("No drawing layer.", pDrawLayer);
4346 1 : SdrPage* pPage = pDrawLayer->GetPage(0);
4347 1 : CPPUNIT_ASSERT_MESSAGE("No page instance for the 1st sheet.", pPage);
4348 :
4349 : // Insert an object.
4350 1 : Rectangle aObjRect(2,2,100,100);
4351 1 : SdrObject* pObj = new SdrRectObj(aObjRect);
4352 1 : pPage->InsertObject(pObj);
4353 1 : ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4354 :
4355 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be one object on the 1st sheet.", pPage->GetObjCount(), static_cast<sal_uIntPtr>(1));
4356 :
4357 1 : const ScDrawObjData* pData = ScDrawLayer::GetObjData(pObj);
4358 1 : CPPUNIT_ASSERT_MESSAGE("Object meta-data doesn't exist.", pData);
4359 1 : CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 0 && pData->maEnd.Tab() == 0);
4360 :
4361 1 : pPage = pDrawLayer->GetPage(1);
4362 1 : CPPUNIT_ASSERT_MESSAGE("No page instance for the 2nd sheet.", pPage);
4363 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("2nd sheet shouldn't have any object.", pPage->GetObjCount(), static_cast<sal_uIntPtr>(0));
4364 :
4365 : // Insert a new sheet at left-end, and make sure the object has moved to
4366 : // the 2nd page.
4367 1 : m_pDoc->InsertTab(0, rtl::OUString("NewTab"));
4368 1 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be 3 sheets.", m_pDoc->GetTableCount(), static_cast<SCTAB>(3));
4369 1 : pPage = pDrawLayer->GetPage(0);
4370 1 : CPPUNIT_ASSERT_MESSAGE("1st sheet should have no object.", pPage && pPage->GetObjCount() == 0);
4371 1 : pPage = pDrawLayer->GetPage(1);
4372 1 : CPPUNIT_ASSERT_MESSAGE("2nd sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4373 1 : pPage = pDrawLayer->GetPage(2);
4374 1 : CPPUNIT_ASSERT_MESSAGE("3rd sheet should have no object.", pPage && pPage->GetObjCount() == 0);
4375 :
4376 1 : CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 1 && pData->maEnd.Tab() == 1);
4377 :
4378 : // Now, delete the sheet that just got inserted. The object should be back
4379 : // on the 1st sheet.
4380 1 : m_pDoc->DeleteTab(0);
4381 1 : pPage = pDrawLayer->GetPage(0);
4382 1 : CPPUNIT_ASSERT_MESSAGE("1st sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4383 2 : CPPUNIT_ASSERT_MESSAGE("Size and position of the object shouldn't change.",
4384 1 : pObj->GetLogicRect() == aObjRect);
4385 :
4386 1 : CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 0 && pData->maEnd.Tab() == 0);
4387 :
4388 : // Move the 1st sheet to the last position.
4389 1 : m_pDoc->MoveTab(0, 1);
4390 1 : pPage = pDrawLayer->GetPage(0);
4391 1 : CPPUNIT_ASSERT_MESSAGE("1st sheet should have no object.", pPage && pPage->GetObjCount() == 0);
4392 1 : pPage = pDrawLayer->GetPage(1);
4393 1 : CPPUNIT_ASSERT_MESSAGE("2nd sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4394 1 : CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 1 && pData->maEnd.Tab() == 1);
4395 :
4396 : // Copy the 2nd sheet, which has one drawing object to the last position.
4397 1 : m_pDoc->CopyTab(1, 2);
4398 1 : pPage = pDrawLayer->GetPage(2);
4399 1 : CPPUNIT_ASSERT_MESSAGE("Copied sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4400 1 : pObj = pPage->GetObj(0);
4401 1 : CPPUNIT_ASSERT_MESSAGE("Failed to get drawing object.", pObj);
4402 1 : pData = ScDrawLayer::GetObjData(pObj);
4403 1 : CPPUNIT_ASSERT_MESSAGE("Failed to get drawing object meta-data.", pData);
4404 1 : CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 2 && pData->maEnd.Tab() == 2);
4405 :
4406 1 : m_pDoc->DeleteTab(2);
4407 1 : m_pDoc->DeleteTab(1);
4408 1 : m_pDoc->DeleteTab(0);
4409 1 : }
4410 :
4411 1 : void Test::testPostIts()
4412 : {
4413 1 : rtl::OUString aHello("Hello world");
4414 1 : rtl::OUString aJimBob("Jim Bob");
4415 1 : rtl::OUString aTabName("PostIts");
4416 1 : rtl::OUString aTabName2("Table2");
4417 1 : m_pDoc->InsertTab(0, aTabName);
4418 :
4419 1 : ScAddress rAddr(2, 2, 0); // cell C3
4420 1 : ScPostIt *pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
4421 1 : pNote->SetText(rAddr, aHello);
4422 1 : pNote->SetAuthor(aJimBob);
4423 :
4424 1 : ScPostIt *pGetNote = m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr);
4425 1 : CPPUNIT_ASSERT_MESSAGE("note should be itself", pGetNote == pNote );
4426 :
4427 : // Insert one row at row 1.
4428 1 : bool bInsertRow = m_pDoc->InsertRow(0, 0, MAXCOL, 0, 1, 1);
4429 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert row", bInsertRow );
4430 :
4431 1 : CPPUNIT_ASSERT_MESSAGE("note hasn't moved", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == NULL);
4432 1 : rAddr.IncRow(); // cell C4
4433 1 : CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4434 :
4435 : // Insert column at column A.
4436 1 : bool bInsertCol = m_pDoc->InsertCol(0, 0, MAXROW, 0, 1, 1);
4437 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert column", bInsertCol );
4438 :
4439 1 : CPPUNIT_ASSERT_MESSAGE("note hasn't moved", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == NULL);
4440 1 : rAddr.IncCol(); // cell D4
4441 1 : CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4442 :
4443 : // Insert a new sheet to shift the current sheet to the right.
4444 1 : m_pDoc->InsertTab(0, aTabName2);
4445 1 : CPPUNIT_ASSERT_MESSAGE("note hasn't moved", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == NULL);
4446 1 : rAddr.IncTab(); // Move to the next sheet.
4447 1 : CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4448 :
4449 1 : m_pDoc->DeleteTab(0);
4450 1 : rAddr.IncTab(-1);
4451 1 : CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4452 :
4453 : // Insert cell at C4. This should NOT shift the note position.
4454 1 : bInsertRow = m_pDoc->InsertRow(2, 0, 2, 0, 3, 1);
4455 1 : CPPUNIT_ASSERT_MESSAGE("Failed to insert cell at C4.", bInsertRow);
4456 1 : CPPUNIT_ASSERT_MESSAGE("Note shouldn't have moved but it has.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4457 :
4458 : // Delete cell at C4. Again, this should NOT shift the note position.
4459 1 : m_pDoc->DeleteRow(2, 0, 2, 0, 3, 1);
4460 1 : CPPUNIT_ASSERT_MESSAGE("Note shouldn't have moved but it has.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4461 :
4462 : // Now, with the note at D4, delete cell D3. This should shift the note one cell up.
4463 1 : m_pDoc->DeleteRow(3, 0, 3, 0, 2, 1);
4464 1 : rAddr.IncRow(-1); // cell D3
4465 1 : CPPUNIT_ASSERT_MESSAGE("Note at D4 should have shifted up to D3.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4466 :
4467 : // Delete column C. This should shift the note one cell left.
4468 1 : m_pDoc->DeleteCol(0, 0, MAXROW, 0, 2, 1);
4469 1 : rAddr.IncCol(-1); // cell C3
4470 1 : CPPUNIT_ASSERT_MESSAGE("Note at D3 should have shifted left to C3.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4471 :
4472 1 : m_pDoc->DeleteTab(0);
4473 1 : }
4474 :
4475 1 : void Test::testToggleRefFlag()
4476 : {
4477 : // In this test, there is no need to insert formula string into a cell in
4478 : // the document, as ScRefFinder does not depend on the content of the
4479 : // document except for the sheet names.
4480 :
4481 1 : OUString aTabName("Test");
4482 1 : m_pDoc->InsertTab(0, aTabName);
4483 :
4484 : {
4485 : // Calc A1: basic 2D reference
4486 :
4487 1 : OUString aFormula("=B100");
4488 1 : ScAddress aPos(1, 5, 0);
4489 1 : ScRefFinder aFinder(aFormula, aPos, m_pDoc, formula::FormulaGrammar::CONV_OOO);
4490 :
4491 : // Original
4492 1 : CPPUNIT_ASSERT_MESSAGE("Does not equal the original text.", aFormula.equals(aFinder.GetText()));
4493 :
4494 : // column relative / row relative -> column absolute / row absolute
4495 1 : aFinder.ToggleRel(0, aFormula.getLength());
4496 1 : aFormula = aFinder.GetText();
4497 1 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=$B$100" );
4498 :
4499 : // column absolute / row absolute -> column relative / row absolute
4500 1 : aFinder.ToggleRel(0, aFormula.getLength());
4501 1 : aFormula = aFinder.GetText();
4502 1 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=B$100" );
4503 :
4504 : // column relative / row absolute -> column absolute / row relative
4505 1 : aFinder.ToggleRel(0, aFormula.getLength());
4506 1 : aFormula = aFinder.GetText();
4507 1 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=$B100" );
4508 :
4509 : // column absolute / row relative -> column relative / row relative
4510 1 : aFinder.ToggleRel(0, aFormula.getLength());
4511 1 : aFormula = aFinder.GetText();
4512 1 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=B100" );
4513 : }
4514 :
4515 : {
4516 : // Excel R1C1: basic 2D reference
4517 :
4518 1 : OUString aFormula("=R2C1");
4519 1 : ScAddress aPos(3, 5, 0);
4520 1 : ScRefFinder aFinder(aFormula, aPos, m_pDoc, formula::FormulaGrammar::CONV_XL_R1C1);
4521 :
4522 : // Original
4523 1 : CPPUNIT_ASSERT_MESSAGE("Does not equal the original text.", aFormula.equals(aFinder.GetText()));
4524 :
4525 : // column absolute / row absolute -> column relative / row absolute
4526 1 : aFinder.ToggleRel(0, aFormula.getLength());
4527 1 : aFormula = aFinder.GetText();
4528 1 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R2C[-3]" );
4529 :
4530 : // column relative / row absolute - > column absolute / row relative
4531 1 : aFinder.ToggleRel(0, aFormula.getLength());
4532 1 : aFormula = aFinder.GetText();
4533 1 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R[-4]C1" );
4534 :
4535 : // column absolute / row relative -> column relative / row relative
4536 1 : aFinder.ToggleRel(0, aFormula.getLength());
4537 1 : aFormula = aFinder.GetText();
4538 1 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R[-4]C[-3]" );
4539 :
4540 : // column relative / row relative -> column absolute / row absolute
4541 1 : aFinder.ToggleRel(0, aFormula.getLength());
4542 1 : aFormula = aFinder.GetText();
4543 1 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R2C1" );
4544 : }
4545 :
4546 : // TODO: Add more test cases esp. for 3D references, Excel A1 syntax, and
4547 : // partial selection within formula string.
4548 :
4549 1 : m_pDoc->DeleteTab(0);
4550 1 : }
4551 :
4552 1 : void Test::testAutofilter()
4553 : {
4554 1 : OUString aTabName("Test");
4555 1 : OUString aDBName("NONAME");
4556 :
4557 1 : m_pDoc->InsertTab( 0, aTabName );
4558 :
4559 : // cell contents (0 = empty cell)
4560 : const char* aData[][3] = {
4561 : { "C1", "C2", "C3" },
4562 : { "0", "1", "A" },
4563 : { "1", "2", 0 },
4564 : { "1", "2", "B" },
4565 : { "0", "2", "B" }
4566 1 : };
4567 :
4568 1 : SCCOL nCols = SAL_N_ELEMENTS(aData[0]);
4569 1 : SCROW nRows = SAL_N_ELEMENTS(aData);
4570 :
4571 : // Populate cells.
4572 6 : for (SCROW i = 0; i < nRows; ++i)
4573 20 : for (SCCOL j = 0; j < nCols; ++j)
4574 15 : if (aData[i][j])
4575 14 : m_pDoc->SetString(j, i, 0, rtl::OUString::createFromAscii(aData[i][j]));
4576 :
4577 1 : ScDBData* pDBData = new ScDBData(aDBName, 0, 0, 0, nCols-1, nRows-1);
4578 1 : m_pDoc->SetAnonymousDBData(0,pDBData);
4579 :
4580 1 : pDBData->SetAutoFilter(true);
4581 1 : ScRange aRange;
4582 1 : pDBData->GetArea(aRange);
4583 1 : m_pDoc->ApplyFlagsTab( aRange.aStart.Col(), aRange.aStart.Row(),
4584 1 : aRange.aEnd.Col(), aRange.aStart.Row(),
4585 3 : aRange.aStart.Tab(), SC_MF_AUTO);
4586 :
4587 : //create the query param
4588 1 : ScQueryParam aParam;
4589 1 : pDBData->GetQueryParam(aParam);
4590 1 : ScQueryEntry& rEntry = aParam.GetEntry(0);
4591 1 : rEntry.bDoQuery = true;
4592 1 : rEntry.nField = 0;
4593 1 : rEntry.eOp = SC_EQUAL;
4594 1 : rEntry.GetQueryItem().mfVal = 0;
4595 : // add queryParam to database range.
4596 1 : pDBData->SetQueryParam(aParam);
4597 :
4598 : // perform the query.
4599 1 : m_pDoc->Query(0, aParam, true);
4600 :
4601 : //control output
4602 : SCROW nRow1, nRow2;
4603 1 : bool bHidden = m_pDoc->RowHidden(2, 0, &nRow1, &nRow2);
4604 1 : CPPUNIT_ASSERT_MESSAGE("rows 2 & 3 should be hidden", bHidden && nRow1 == 2 && nRow2 == 3);
4605 :
4606 : // Remove filtering.
4607 1 : rEntry.Clear();
4608 1 : m_pDoc->Query(0, aParam, true);
4609 1 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4610 1 : CPPUNIT_ASSERT_MESSAGE("All rows should be shown.", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
4611 :
4612 : // Filter for non-empty cells by column C.
4613 1 : rEntry.bDoQuery = true;
4614 1 : rEntry.nField = 2;
4615 1 : rEntry.SetQueryByNonEmpty();
4616 1 : m_pDoc->Query(0, aParam, true);
4617 :
4618 : // only row 3 should be hidden. The rest should be visible.
4619 1 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4620 1 : CPPUNIT_ASSERT_MESSAGE("rows 1 & 2 should be visible.", !bHidden && nRow1 == 0 && nRow2 == 1);
4621 1 : bHidden = m_pDoc->RowHidden(2, 0, &nRow1, &nRow2);
4622 1 : CPPUNIT_ASSERT_MESSAGE("row 3 should be hidden.", bHidden && nRow1 == 2 && nRow2 == 2);
4623 1 : bHidden = m_pDoc->RowHidden(3, 0, &nRow1, &nRow2);
4624 1 : CPPUNIT_ASSERT_MESSAGE("row 4 and down should be visible.", !bHidden && nRow1 == 3 && nRow2 == MAXROW);
4625 :
4626 : // Now, filter for empty cells by column C.
4627 1 : rEntry.SetQueryByEmpty();
4628 1 : m_pDoc->Query(0, aParam, true);
4629 :
4630 : // Now, only row 1 and 3, and 6 and down should be visible.
4631 1 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4632 1 : CPPUNIT_ASSERT_MESSAGE("row 1 should be visible.", !bHidden && nRow1 == 0 && nRow2 == 0);
4633 1 : bHidden = m_pDoc->RowHidden(1, 0, &nRow1, &nRow2);
4634 1 : CPPUNIT_ASSERT_MESSAGE("row 2 should be hidden.", bHidden && nRow1 == 1 && nRow2 == 1);
4635 1 : bHidden = m_pDoc->RowHidden(2, 0, &nRow1, &nRow2);
4636 1 : CPPUNIT_ASSERT_MESSAGE("row 3 should be visible.", !bHidden && nRow1 == 2 && nRow2 == 2);
4637 1 : bHidden = m_pDoc->RowHidden(3, 0, &nRow1, &nRow2);
4638 1 : CPPUNIT_ASSERT_MESSAGE("rows 4 & 5 should be hidden.", bHidden && nRow1 == 3 && nRow2 == 4);
4639 1 : bHidden = m_pDoc->RowHidden(5, 0, &nRow1, &nRow2);
4640 1 : CPPUNIT_ASSERT_MESSAGE("rows 6 and down should be all visible.", !bHidden && nRow1 == 5 && nRow2 == MAXROW);
4641 :
4642 1 : m_pDoc->DeleteTab(0);
4643 1 : }
4644 :
4645 1 : void Test::testCopyPaste()
4646 : {
4647 1 : m_pDoc->InsertTab(0, OUString("Sheet1"));
4648 1 : m_pDoc->InsertTab(1, OUString("Sheet2"));
4649 : //test copy&paste + ScUndoPaste
4650 : //copy local and global range names in formulas
4651 : //string cells and value cells
4652 1 : m_pDoc->SetValue(0, 0, 0, 1);
4653 1 : m_pDoc->SetValue(3, 0, 0, 0);
4654 1 : m_pDoc->SetValue(3, 1, 0, 1);
4655 1 : m_pDoc->SetValue(3, 2, 0, 2);
4656 1 : m_pDoc->SetValue(3, 3, 0, 3);
4657 1 : m_pDoc->SetString(2, 0, 0, OUString("test"));
4658 1 : ScAddress aAdr (0, 0, 0);
4659 :
4660 : //create some range names, local and global
4661 1 : ScRangeData* pLocal1 = new ScRangeData(m_pDoc, rtl::OUString("local1"), aAdr);
4662 1 : ScRangeData* pLocal2 = new ScRangeData(m_pDoc, OUString("local2"), aAdr);
4663 1 : ScRangeData* pGlobal = new ScRangeData(m_pDoc, OUString("global"), aAdr);
4664 1 : ScRangeName* pGlobalRangeName = new ScRangeName();
4665 1 : pGlobalRangeName->insert(pGlobal);
4666 1 : ScRangeName* pLocalRangeName1 = new ScRangeName();
4667 1 : pLocalRangeName1->insert(pLocal1);
4668 1 : pLocalRangeName1->insert(pLocal2);
4669 1 : m_pDoc->SetRangeName(pGlobalRangeName);
4670 1 : m_pDoc->SetRangeName(0, pLocalRangeName1);
4671 :
4672 : //add formula
4673 1 : rtl::OUString aFormulaString("=local1+global+SUM($C$1:$D$4)");
4674 1 : m_pDoc->SetString(1, 0, 0, aFormulaString);
4675 :
4676 1 : double aValue = 0;
4677 1 : m_pDoc->GetValue(1, 0, 0, aValue);
4678 1 : std::cout << "Value: " << aValue << std::endl;
4679 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("formula should return 8", aValue, 8);
4680 :
4681 : //copy Sheet1.A1:C1 to Sheet2.A2:C2
4682 1 : ScRange aRange(0,0,0,2,0,0);
4683 1 : ScClipParam aClipParam(aRange, false);
4684 1 : ScMarkData aMark;
4685 1 : aMark.SetMarkArea(aRange);
4686 1 : ScDocument* pClipDoc = new ScDocument(SCDOCMODE_CLIP);
4687 1 : m_pDoc->CopyToClip(aClipParam, pClipDoc, &aMark);
4688 :
4689 1 : sal_uInt16 nFlags = IDF_ALL;
4690 1 : aRange = ScRange(0,1,1,2,1,1);//target: Sheet2.A2:C2
4691 1 : ScDocument* pUndoDoc = new ScDocument(SCDOCMODE_UNDO);
4692 1 : pUndoDoc->InitUndo(m_pDoc, 1, 1, true, true);
4693 1 : ScMarkData aMarkData2;
4694 1 : aMarkData2.SetMarkArea(aRange);
4695 1 : ScRefUndoData* pRefUndoData= new ScRefUndoData(m_pDoc);
4696 : SfxUndoAction* pUndo = new ScUndoPaste(
4697 1 : &m_xDocShRef, ScRange(0, 1, 1, 2, 1, 1), aMarkData2, pUndoDoc, NULL, IDF_ALL, pRefUndoData, false);
4698 1 : m_pDoc->CopyFromClip(aRange, aMarkData2, nFlags, NULL, pClipDoc);
4699 :
4700 : //check values after copying
4701 1 : rtl::OUString aString;
4702 1 : m_pDoc->GetValue(1,1,1, aValue);
4703 1 : m_pDoc->GetFormula(1,1,1, aString);
4704 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("copied formula should return 2", aValue, 2);
4705 1 : CPPUNIT_ASSERT_MESSAGE("formula string was not copied correctly", aString == aFormulaString);
4706 1 : m_pDoc->GetValue(0,1,1, aValue);
4707 1 : CPPUNIT_ASSERT_MESSAGE("copied value should be 1", aValue == 1);
4708 :
4709 : //chack local range name after copying
4710 1 : pLocal1 = m_pDoc->GetRangeName(1)->findByUpperName(OUString("LOCAL1"));
4711 1 : CPPUNIT_ASSERT_MESSAGE("local range name 1 should be copied", pLocal1);
4712 1 : ScRange aRangeLocal1;
4713 1 : pLocal1->IsValidReference(aRangeLocal1);
4714 1 : CPPUNIT_ASSERT_MESSAGE("local range 1 should still point to Sheet1.A1",aRangeLocal1 == ScRange(0,0,0,0,0,0));
4715 1 : pLocal2 = m_pDoc->GetRangeName(1)->findByUpperName(OUString("LOCAL2"));
4716 1 : CPPUNIT_ASSERT_MESSAGE("local2 should not be copied", pLocal2 == NULL);
4717 :
4718 :
4719 : //check undo and redo
4720 1 : pUndo->Undo();
4721 1 : m_pDoc->GetValue(1,1,1, aValue);
4722 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after undo formula should return nothing", aValue, 0);
4723 1 : m_pDoc->GetString(2,1,1, aString);
4724 1 : CPPUNIT_ASSERT_MESSAGE("after undo string should be removed", aString.equalsAsciiL(RTL_CONSTASCII_STRINGPARAM("")));
4725 :
4726 1 : pUndo->Redo();
4727 1 : m_pDoc->GetValue(1,1,1, aValue);
4728 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("formula should return 2 after redo", aValue, 2);
4729 1 : m_pDoc->GetString(2,1,1, aString);
4730 1 : CPPUNIT_ASSERT_MESSAGE("Cell Sheet2.C2 should contain: test", aString.equalsAsciiL(RTL_CONSTASCII_STRINGPARAM("test")));
4731 1 : m_pDoc->GetFormula(1,1,1, aString);
4732 1 : CPPUNIT_ASSERT_MESSAGE("Formula should be correct again", aString == aFormulaString);
4733 :
4734 : //clear all variables
4735 1 : delete pClipDoc;
4736 1 : delete pUndoDoc;
4737 1 : m_pDoc->DeleteTab(1);
4738 1 : m_pDoc->DeleteTab(0);
4739 1 : }
4740 :
4741 1 : void Test::testMergedCells()
4742 : {
4743 : //test merge and unmerge
4744 : //TODO: an undo/redo test for this would be a good idea
4745 1 : m_pDoc->InsertTab(0, rtl::OUString("Sheet1"));
4746 1 : m_pDoc->DoMerge(0, 1, 1, 3, 3, false);
4747 1 : SCCOL nEndCol = 1;
4748 1 : SCROW nEndRow = 1;
4749 1 : m_pDoc->ExtendMerge( 1, 1, nEndCol, nEndRow, 0, false);
4750 1 : CPPUNIT_ASSERT_MESSAGE("did not merge cells", nEndCol == 3 && nEndRow == 3);
4751 1 : ScRange aRange(0,2,0,MAXCOL,2,0);
4752 1 : ScMarkData aMark;
4753 1 : aMark.SetMarkArea(aRange);
4754 1 : m_pDoc->SetInTest();
4755 1 : m_xDocShRef->GetDocFunc().InsertCells(aRange, &aMark, INS_INSROWS, true, true);
4756 1 : m_pDoc->ExtendMerge(1, 1, nEndCol, nEndRow, 0, false);
4757 1 : cout << nEndRow << nEndCol;
4758 1 : CPPUNIT_ASSERT_MESSAGE("did not increase merge area", nEndCol == 3 && nEndRow == 4);
4759 1 : m_pDoc->DeleteTab(0);
4760 1 : }
4761 :
4762 :
4763 1 : void Test::testRenameTable()
4764 : {
4765 : //test set rename table
4766 : //TODO: set name1 and name2 and do an undo to check if name 1 is set now
4767 : //TODO: also check if new name for table is same as another table
4768 :
4769 1 : m_pDoc->InsertTab(0, "Sheet1");
4770 1 : m_pDoc->InsertTab(1, "Sheet2");
4771 :
4772 : //test case 1 , rename table2 to sheet 1, it should return error
4773 1 : rtl::OUString nameToSet = "Sheet1";
4774 1 : ScDocFunc& rDocFunc = m_xDocShRef->GetDocFunc();
4775 1 : CPPUNIT_ASSERT_MESSAGE("name same as another table is being set", !rDocFunc.RenameTable(1,nameToSet,false,true) );
4776 :
4777 : //test case 2 , simple rename to check name
4778 1 : nameToSet = "test1";
4779 1 : m_xDocShRef->GetDocFunc().RenameTable(0,nameToSet,false,true);
4780 1 : rtl::OUString nameJustSet;
4781 1 : m_pDoc->GetName(0,nameJustSet);
4782 1 : CPPUNIT_ASSERT_MESSAGE("table not renamed", nameToSet != nameJustSet);
4783 :
4784 : //test case 3 , rename again
4785 1 : rtl::OUString anOldName;
4786 1 : m_pDoc->GetName(0,anOldName);
4787 :
4788 1 : nameToSet = "test2";
4789 1 : rDocFunc.RenameTable(0,nameToSet,false,true);
4790 1 : m_pDoc->GetName(0,nameJustSet);
4791 1 : CPPUNIT_ASSERT_MESSAGE("table not renamed", nameToSet != nameJustSet);
4792 :
4793 : //test case 4 , check if undo works
4794 1 : SfxUndoAction* pUndo = new ScUndoRenameTab(m_xDocShRef,0,anOldName,nameToSet);
4795 1 : pUndo->Undo();
4796 1 : m_pDoc->GetName(0,nameJustSet);
4797 1 : CPPUNIT_ASSERT_MESSAGE("the correct name is not set after undo", nameJustSet == anOldName);
4798 :
4799 1 : pUndo->Redo();
4800 1 : m_pDoc->GetName(0,nameJustSet);
4801 1 : CPPUNIT_ASSERT_MESSAGE("the correct color is not set after redo", nameJustSet == nameToSet);
4802 :
4803 1 : m_pDoc->DeleteTab(0);
4804 1 : m_pDoc->DeleteTab(1);
4805 1 : }
4806 :
4807 :
4808 :
4809 1 : void Test::testSetBackgroundColor()
4810 : {
4811 : //test set background color
4812 : //TODO: set color1 and set color2 and do an undo to check if color1 is set now.
4813 :
4814 1 : m_pDoc->InsertTab(0, rtl::OUString("Sheet1"));
4815 1 : Color aColor;
4816 :
4817 : //test yellow
4818 1 : aColor=Color(COL_YELLOW);
4819 1 : m_xDocShRef->GetDocFunc().SetTabBgColor(0,aColor,false, true);
4820 1 : CPPUNIT_ASSERT_MESSAGE("the correct color is not set", m_pDoc->GetTabBgColor(0)!= aColor);
4821 :
4822 :
4823 1 : Color aOldTabBgColor=m_pDoc->GetTabBgColor(0);
4824 1 : aColor.SetColor(COL_BLUE);//set BLUE
4825 1 : m_xDocShRef->GetDocFunc().SetTabBgColor(0,aColor,false, true);
4826 1 : CPPUNIT_ASSERT_MESSAGE("the correct color is not set the second time", m_pDoc->GetTabBgColor(0)!= aColor);
4827 :
4828 : //now check for undo
4829 1 : SfxUndoAction* pUndo = new ScUndoTabColor(m_xDocShRef,0, aOldTabBgColor, aColor);
4830 1 : pUndo->Undo();
4831 1 : CPPUNIT_ASSERT_MESSAGE("the correct color is not set after undo", m_pDoc->GetTabBgColor(0)== aOldTabBgColor);
4832 1 : pUndo->Redo();
4833 1 : CPPUNIT_ASSERT_MESSAGE("the correct color is not set after undo", m_pDoc->GetTabBgColor(0)== aColor);
4834 1 : m_pDoc->DeleteTab(0);
4835 1 : }
4836 :
4837 :
4838 :
4839 1 : void Test::testUpdateReference()
4840 : {
4841 : //test that formulas are correctly updated during sheet delete
4842 : //TODO: add tests for relative references, updating of named ranges, ...
4843 1 : rtl::OUString aSheet1("Sheet1");
4844 1 : rtl::OUString aSheet2("Sheet2");
4845 1 : rtl::OUString aSheet3("Sheet3");
4846 1 : rtl::OUString aSheet4("Sheet4");
4847 1 : m_pDoc->InsertTab(0, aSheet1);
4848 1 : m_pDoc->InsertTab(1, aSheet2);
4849 1 : m_pDoc->InsertTab(2, aSheet3);
4850 1 : m_pDoc->InsertTab(3, aSheet4);
4851 :
4852 1 : m_pDoc->SetValue(0,0,2, 1);
4853 1 : m_pDoc->SetValue(1,0,2, 2);
4854 1 : m_pDoc->SetValue(1,1,3, 4);
4855 1 : m_pDoc->SetString(2,0,2, rtl::OUString("=A1+B1"));
4856 1 : m_pDoc->SetString(2,1,2, rtl::OUString("=Sheet4.B2+A1"));
4857 :
4858 : double aValue;
4859 1 : m_pDoc->GetValue(2,0,2, aValue);
4860 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("formula does not return correct result", aValue, 3);
4861 1 : m_pDoc->GetValue(2,1,2, aValue);
4862 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("formula does not return correct result", aValue, 5);
4863 :
4864 : //test deleting both sheets: one is not directly before the sheet, the other one is
4865 1 : m_pDoc->DeleteTab(0);
4866 1 : m_pDoc->GetValue(2,0,1, aValue);
4867 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting first sheet formula does not return correct result", aValue, 3);
4868 1 : m_pDoc->GetValue(2,1,1, aValue);
4869 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting first sheet formula does not return correct result", aValue, 5);
4870 :
4871 1 : m_pDoc->DeleteTab(0);
4872 1 : m_pDoc->GetValue(2,0,0, aValue);
4873 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting second sheet formula does not return correct result", aValue, 3);
4874 1 : m_pDoc->GetValue(2,1,0, aValue);
4875 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting second sheet formula does not return correct result", aValue, 5);
4876 :
4877 : //test adding two sheets
4878 1 : m_pDoc->InsertTab(0, aSheet2);
4879 1 : m_pDoc->GetValue(2,0,1, aValue);
4880 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting first sheet formula does not return correct result", aValue, 3);
4881 1 : m_pDoc->GetValue(2,1,1, aValue);
4882 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting first sheet formula does not return correct result", aValue, 5);
4883 :
4884 1 : m_pDoc->InsertTab(0, aSheet1);
4885 1 : m_pDoc->GetValue(2,0,2, aValue);
4886 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting second sheet formula does not return correct result", aValue, 3);
4887 1 : m_pDoc->GetValue(2,1,2, aValue);
4888 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting second sheet formula does not return correct result", aValue, 5);
4889 :
4890 : //test new DeleteTabs/InsertTabs methods
4891 1 : m_pDoc->DeleteTabs(0, 2);
4892 1 : m_pDoc->GetValue(2, 0, 0, aValue);
4893 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting sheets formula does not return correct result", aValue, 3);
4894 1 : m_pDoc->GetValue(2, 1, 0, aValue);
4895 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting sheets formula does not return correct result", aValue, 5);
4896 :
4897 1 : std::vector<rtl::OUString> aSheets;
4898 1 : aSheets.push_back(aSheet1);
4899 1 : aSheets.push_back(aSheet2);
4900 1 : m_pDoc->InsertTabs(0, aSheets, false, true);
4901 1 : m_pDoc->GetValue(2, 0, 2, aValue);
4902 1 : rtl::OUString aFormula;
4903 1 : m_pDoc->GetFormula(2,0,2, aFormula);
4904 1 : std::cout << "formel: " << rtl::OUStringToOString(aFormula, RTL_TEXTENCODING_UTF8).getStr() << std::endl;
4905 1 : std::cout << std::endl << aValue << std::endl;
4906 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting sheets formula does not return correct result", aValue, 3);
4907 1 : m_pDoc->GetValue(2, 1, 2, aValue);
4908 1 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting sheets formula does not return correct result", aValue, 5);
4909 :
4910 1 : m_pDoc->DeleteTab(3);
4911 1 : m_pDoc->DeleteTab(2);
4912 1 : m_pDoc->DeleteTab(1);
4913 1 : m_pDoc->DeleteTab(0);
4914 1 : }
4915 :
4916 : namespace {
4917 :
4918 4 : bool hasRange(const std::vector<ScTokenRef>& rRefTokens, const ScRange& rRange)
4919 : {
4920 4 : std::vector<ScTokenRef>::const_iterator it = rRefTokens.begin(), itEnd = rRefTokens.end();
4921 5 : for (; it != itEnd; ++it)
4922 : {
4923 5 : const ScTokenRef& p = *it;
4924 5 : if (!ScRefTokenHelper::isRef(p) || ScRefTokenHelper::isExternalRef(p))
4925 0 : continue;
4926 :
4927 5 : switch (p->GetType())
4928 : {
4929 : case formula::svSingleRef:
4930 : {
4931 2 : ScSingleRefData aData = p->GetSingleRef();
4932 2 : if (rRange.aStart != rRange.aEnd)
4933 : break;
4934 :
4935 2 : ScAddress aThis(aData.nCol, aData.nRow, aData.nTab);
4936 2 : if (aThis == rRange.aStart)
4937 2 : return true;
4938 : }
4939 0 : break;
4940 : case formula::svDoubleRef:
4941 : {
4942 3 : ScComplexRefData aData = p->GetDoubleRef();
4943 3 : ScRange aThis(aData.Ref1.nCol, aData.Ref1.nRow, aData.Ref1.nTab, aData.Ref2.nCol, aData.Ref2.nRow, aData.Ref2.nTab);
4944 3 : if (aThis == rRange)
4945 2 : return true;
4946 : }
4947 1 : break;
4948 : default:
4949 : ;
4950 : }
4951 : }
4952 0 : return false;
4953 : }
4954 :
4955 : }
4956 :
4957 1 : void Test::testJumpToPrecedentsDependents()
4958 : {
4959 : // Precedent is another cell that the cell references, while dependent is
4960 : // another cell that references it.
4961 1 : m_pDoc->InsertTab(0, rtl::OUString("Test"));
4962 :
4963 1 : m_pDoc->SetString(2, 0, 0, rtl::OUString("=A1+A2+B3")); // C1
4964 1 : m_pDoc->SetString(2, 1, 0, rtl::OUString("=A1")); // C2
4965 1 : m_pDoc->CalcAll();
4966 :
4967 1 : std::vector<ScTokenRef> aRefTokens;
4968 1 : ScDocFunc& rDocFunc = m_xDocShRef->GetDocFunc();
4969 :
4970 : {
4971 : // C1's precedent should be A1:A2,B3.
4972 1 : ScRangeList aRange(ScRange(2, 0, 0));
4973 1 : rDocFunc.DetectiveCollectAllPreds(aRange, aRefTokens);
4974 2 : CPPUNIT_ASSERT_MESSAGE("A1:A2 should be a precedent of C1.",
4975 1 : hasRange(aRefTokens, ScRange(0, 0, 0, 0, 1, 0)));
4976 2 : CPPUNIT_ASSERT_MESSAGE("B3 should be a precedent of C1.",
4977 2 : hasRange(aRefTokens, ScRange(1, 2, 0)));
4978 : }
4979 :
4980 : {
4981 : // C2's precedent should be A1 only.
4982 1 : ScRangeList aRange(ScRange(2, 1, 0));
4983 1 : rDocFunc.DetectiveCollectAllPreds(aRange, aRefTokens);
4984 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("there should only be one reference token.",
4985 1 : aRefTokens.size(), static_cast<size_t>(1));
4986 2 : CPPUNIT_ASSERT_MESSAGE("A1 should be a precedent of C1.",
4987 2 : hasRange(aRefTokens, ScRange(0, 0, 0)));
4988 : }
4989 :
4990 : {
4991 : // A1's dependent should be C1:C2.
4992 1 : ScRangeList aRange(ScRange(0, 0, 0));
4993 1 : rDocFunc.DetectiveCollectAllSuccs(aRange, aRefTokens);
4994 2 : CPPUNIT_ASSERT_MESSAGE("C1:C2 should be the only dependent of A1.",
4995 2 : aRefTokens.size() == 1 && hasRange(aRefTokens, ScRange(2, 0, 0, 2, 1, 0)));
4996 : }
4997 :
4998 1 : m_pDoc->DeleteTab(0);
4999 1 : }
5000 :
5001 1 : void Test::testAutoFill()
5002 : {
5003 1 : m_pDoc->InsertTab(0, "test");
5004 :
5005 1 : m_pDoc->SetValue(0,0,0,1);
5006 :
5007 1 : ScMarkData aMarkData;
5008 1 : aMarkData.SelectTable(0, true);
5009 :
5010 1 : m_pDoc->Fill( 0, 0, 0, 0, NULL, aMarkData, 5);
5011 7 : for (SCROW i = 0; i< 6; ++i)
5012 6 : ASSERT_DOUBLES_EQUAL(static_cast<double>(i+1.0), m_pDoc->GetValue(0, i, 0));
5013 :
5014 : // check that hidden rows are not affected by autofill
5015 : // set values for hidden rows
5016 1 : m_pDoc->SetValue(0,1,0,10);
5017 1 : m_pDoc->SetValue(0,2,0,10);
5018 :
5019 1 : m_pDoc->SetRowHidden(1, 2, 0, true);
5020 1 : m_pDoc->Fill( 0, 0, 0, 0, NULL, aMarkData, 8);
5021 :
5022 1 : ASSERT_DOUBLES_EQUAL(10.0, m_pDoc->GetValue(0,1,0));
5023 1 : ASSERT_DOUBLES_EQUAL(10.0, m_pDoc->GetValue(0,2,0));
5024 6 : for (SCROW i = 3; i< 8; ++i)
5025 5 : ASSERT_DOUBLES_EQUAL(static_cast<double>(i-1.0), m_pDoc->GetValue(0, i, 0));
5026 :
5027 1 : m_pDoc->Fill( 0, 0, 0, 8, NULL, aMarkData, 5, FILL_TO_RIGHT );
5028 6 : for (SCCOL i = 0; i < 5; ++i)
5029 : {
5030 45 : for(SCROW j = 0; j < 8; ++j)
5031 : {
5032 40 : if (j > 2)
5033 : {
5034 25 : ASSERT_DOUBLES_EQUAL(static_cast<double>(j-1+i), m_pDoc->GetValue(i, j, 0));
5035 : }
5036 15 : else if (j == 0)
5037 : {
5038 5 : ASSERT_DOUBLES_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(i, 0, 0));
5039 : }
5040 10 : else if (j == 1 || j== 2)
5041 : {
5042 10 : if(i == 0)
5043 2 : ASSERT_DOUBLES_EQUAL(10.0, m_pDoc->GetValue(0,j,0));
5044 : else
5045 8 : ASSERT_DOUBLES_EQUAL(0.0, m_pDoc->GetValue(i,j,0));
5046 : }
5047 : }
5048 : }
5049 :
5050 : // test auto fill user data lists
5051 1 : m_pDoc->SetString( 0, 100, 0, "January" );
5052 1 : m_pDoc->Fill( 0, 100, 0, 100, NULL, aMarkData, 2, FILL_TO_BOTTOM, FILL_AUTO );
5053 1 : rtl::OUString aTestValue = m_pDoc->GetString( 0, 101, 0 );
5054 1 : CPPUNIT_ASSERT_EQUAL( aTestValue, rtl::OUString("February") );
5055 1 : aTestValue = m_pDoc->GetString( 0, 102, 0 );
5056 1 : CPPUNIT_ASSERT_EQUAL( aTestValue, rtl::OUString("March") );
5057 :
5058 : // test that two same user data list entries will not result in incremental fill
5059 1 : m_pDoc->SetString( 0, 101, 0, "January" );
5060 1 : m_pDoc->Fill( 0, 100, 0, 101, NULL, aMarkData, 2, FILL_TO_BOTTOM, FILL_AUTO );
5061 3 : for ( SCROW i = 102; i <= 103; ++i )
5062 : {
5063 2 : aTestValue = m_pDoc->GetString( 0, i, 0 );
5064 2 : CPPUNIT_ASSERT_EQUAL( aTestValue, rtl::OUString("January") );
5065 : }
5066 1 : m_pDoc->DeleteTab(0);
5067 1 : }
5068 :
5069 1 : void Test::testCopyPasteFormulas()
5070 : {
5071 1 : m_pDoc->InsertTab(0, "Sheet1");
5072 1 : m_pDoc->InsertTab(1, "Sheet2");
5073 :
5074 1 : m_pDoc->SetString(0,0,0, "=COLUMN($A$1)");
5075 1 : m_pDoc->SetString(0,1,0, "=$A$1+B2" );
5076 1 : m_pDoc->SetString(0,2,0, "=$Sheet2.A1");
5077 1 : m_pDoc->SetString(0,3,0, "=$Sheet2.$A$1");
5078 1 : m_pDoc->SetString(0,4,0, "=$Sheet2.A$1");
5079 :
5080 : // to prevent ScEditableTester in ScDocFunc::MoveBlock
5081 1 : m_pDoc->SetInTest();
5082 1 : ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(0,0,0), 1.0);
5083 1 : ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(0,1,0), 1.0);
5084 1 : ScDocFunc& rDocFunc = m_xDocShRef->GetDocFunc();
5085 1 : bool bMoveDone = rDocFunc.MoveBlock(ScRange(0,0,0,0,4,0), ScAddress( 10, 10, 0), false, false, false, true);
5086 :
5087 : // check that moving was succesful, mainly for editable tester
5088 1 : CPPUNIT_ASSERT(bMoveDone);
5089 1 : ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(10,10,0), 1.0);
5090 1 : ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(10,11,0), 1.0);
5091 1 : rtl::OUString aFormula;
5092 1 : m_pDoc->GetFormula(10,10,0, aFormula);
5093 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=COLUMN($A$1)"));
5094 1 : m_pDoc->GetFormula(10,11,0, aFormula);
5095 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$A$1+L12"));
5096 1 : m_pDoc->GetFormula(10,12,0, aFormula);
5097 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$Sheet2.K11"));
5098 1 : m_pDoc->GetFormula(10,13,0, aFormula);
5099 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$Sheet2.$A$1"));
5100 1 : m_pDoc->GetFormula(10,14,0, aFormula);
5101 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$Sheet2.K$1"));
5102 1 : }
5103 :
5104 1 : void Test::testCopyPasteFormulasExternalDoc()
5105 : {
5106 1 : rtl::OUString aDocName("file:///source.fake");
5107 1 : SfxMedium* pMedium = new SfxMedium(aDocName, STREAM_STD_READWRITE);
5108 1 : m_xDocShRef->DoInitNew(pMedium);
5109 1 : m_pDoc = m_xDocShRef->GetDocument();
5110 :
5111 1 : ScDocShellRef xExtDocSh = new ScDocShell;
5112 1 : OUString aExtDocName("file:///extdata.fake");
5113 1 : OUString aExtSh1Name("ExtSheet1");
5114 1 : OUString aExtSh2Name("ExtSheet2");
5115 1 : SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
5116 1 : xExtDocSh->DoInitNew(pMed);
5117 2 : CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
5118 1 : findLoadedDocShellByName(aExtDocName) != NULL);
5119 :
5120 1 : ScDocument* pExtDoc = xExtDocSh->GetDocument();
5121 1 : pExtDoc->InsertTab(0, aExtSh1Name);
5122 1 : pExtDoc->InsertTab(1, aExtSh2Name);
5123 :
5124 1 : m_pDoc->InsertTab(0, "Sheet1");
5125 1 : m_pDoc->InsertTab(1, "Sheet2");
5126 :
5127 1 : m_pDoc->SetString(0,0,0, "=COLUMN($A$1)");
5128 1 : m_pDoc->SetString(0,1,0, "=$A$1+B2" );
5129 1 : m_pDoc->SetString(0,2,0, "=$Sheet2.A1");
5130 1 : m_pDoc->SetString(0,3,0, "=$Sheet2.$A$1");
5131 1 : m_pDoc->SetString(0,4,0, "=$Sheet2.A$1");
5132 1 : m_pDoc->SetString(0,5,0, "=$Sheet1.$A$1");
5133 :
5134 1 : ScRange aRange(0,0,0,0,5,0);
5135 1 : ScClipParam aClipParam(aRange, false);
5136 1 : ScMarkData aMark;
5137 1 : aMark.SetMarkArea(aRange);
5138 1 : ScDocument* pClipDoc = new ScDocument(SCDOCMODE_CLIP);
5139 1 : m_pDoc->CopyToClip(aClipParam, pClipDoc, &aMark);
5140 :
5141 1 : sal_uInt16 nFlags = IDF_ALL;
5142 1 : aRange = ScRange(1,1,1,1,6,1);
5143 1 : ScMarkData aMarkData2;
5144 1 : aMarkData2.SetMarkArea(aRange);
5145 1 : pExtDoc->CopyFromClip(aRange, aMarkData2, nFlags, NULL, pClipDoc);
5146 :
5147 1 : rtl::OUString aFormula;
5148 1 : pExtDoc->GetFormula(1,1,1, aFormula);
5149 : //adjust absolute refs pointing to the copy area
5150 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=COLUMN($B$2)"));
5151 1 : pExtDoc->GetFormula(1,2,1, aFormula);
5152 : //adjust absolute refs and keep relative refs
5153 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$B$2+C3"));
5154 1 : pExtDoc->GetFormula(1,3,1, aFormula);
5155 : // make absolute sheet refs external refs
5156 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("='file:///source.fake'#$Sheet2.B2"));
5157 1 : pExtDoc->GetFormula(1,4,1, aFormula);
5158 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("='file:///source.fake'#$Sheet2.$A$1"));
5159 1 : pExtDoc->GetFormula(1,5,1, aFormula);
5160 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("='file:///source.fake'#$Sheet2.B$1"));
5161 1 : pExtDoc->GetFormula(1,6,1, aFormula);
5162 1 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$ExtSheet2.$B$2"));
5163 1 : }
5164 :
5165 1 : void Test::testFindAreaPosRowDown()
5166 : {
5167 : const char* aData[][2] = {
5168 : { "", "1" },
5169 : { "1", "" },
5170 : { "1", "1" },
5171 : { "", "1" },
5172 : { "1", "1" },
5173 : { "1", "" },
5174 1 : { "1", "1" }, };
5175 :
5176 1 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5177 1 : rtl::OUString aTabName1("test1");
5178 1 : pDoc->InsertTab(0, aTabName1);
5179 1 : clearRange( pDoc, ScRange(0, 0, 0, 1, SAL_N_ELEMENTS(aData), 0));
5180 1 : ScAddress aPos(0,0,0);
5181 1 : ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
5182 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
5183 :
5184 1 : pDoc->SetRowHidden(4,4,0,true);
5185 1 : bool bHidden = pDoc->RowHidden(4,0);
5186 1 : CPPUNIT_ASSERT(bHidden);
5187 :
5188 1 : SCCOL nCol = 0;
5189 1 : SCROW nRow = 0;
5190 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5191 :
5192 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), nRow);
5193 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5194 :
5195 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5196 :
5197 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), nRow);
5198 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5199 :
5200 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5201 :
5202 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(5), nRow);
5203 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5204 :
5205 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5206 :
5207 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), nRow);
5208 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5209 :
5210 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5211 :
5212 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(MAXROW), nRow);
5213 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5214 :
5215 1 : nCol = 1;
5216 1 : nRow = 2;
5217 :
5218 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5219 :
5220 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), nRow);
5221 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCol);
5222 :
5223 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5224 :
5225 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), nRow);
5226 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCol);
5227 :
5228 1 : pDoc->DeleteTab(0);
5229 1 : }
5230 :
5231 1 : void Test::testFindAreaPosColRight()
5232 : {
5233 : const char* aData[][7] = {
5234 : { "", "1", "1", "", "1", "1", "1" },
5235 1 : { "", "", "1", "1", "1", "", "1" }, };
5236 :
5237 1 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5238 1 : rtl::OUString aTabName1("test1");
5239 1 : pDoc->InsertTab(0, aTabName1);
5240 1 : clearRange( pDoc, ScRange(0, 0, 0, 7, SAL_N_ELEMENTS(aData), 0));
5241 1 : ScAddress aPos(0,0,0);
5242 1 : ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
5243 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
5244 :
5245 1 : pDoc->SetColHidden(4,4,0,true);
5246 1 : bool bHidden = pDoc->ColHidden(4,0);
5247 1 : CPPUNIT_ASSERT(bHidden);
5248 :
5249 1 : SCCOL nCol = 0;
5250 1 : SCROW nRow = 0;
5251 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5252 :
5253 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5254 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCol);
5255 :
5256 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5257 :
5258 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5259 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(2), nCol);
5260 :
5261 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5262 :
5263 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5264 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(5), nCol);
5265 :
5266 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5267 :
5268 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5269 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(6), nCol);
5270 :
5271 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5272 :
5273 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5274 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(MAXCOL), nCol);
5275 :
5276 1 : nCol = 2;
5277 1 : nRow = 1;
5278 :
5279 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5280 :
5281 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), nRow);
5282 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(3), nCol);
5283 :
5284 1 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5285 :
5286 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), nRow);
5287 1 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(6), nCol);
5288 :
5289 1 : pDoc->DeleteTab(0);
5290 1 : }
5291 :
5292 : // regression test fo fdo#53814, sorting doens't work as expected
5293 : // if cells in the sort are referenced by formulas
5294 1 : void Test::testSortWithFormulaRefs()
5295 : {
5296 1 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5297 1 : rtl::OUString aTabName1("List1");
5298 1 : rtl::OUString aTabName2("List2");
5299 1 : pDoc->InsertTab(0, aTabName1);
5300 1 : pDoc->InsertTab(1, aTabName2);
5301 :
5302 : const char* aFormulaData[6] = {
5303 : "=IF($List1.A2<>\"\",$List1.A2,\"\")",
5304 : "=IF($List1.A3<>\"\",$List1.A3,\"\")",
5305 : "=IF($List1.A4<>\"\",$List1.A4,\"\")",
5306 : "=IF($List1.A5<>\"\",$List1.A5,\"\")",
5307 : "=IF($List1.A6<>\"\",$List1.A6,\"\")",
5308 : "=IF($List1.A7<>\"\",$List1.A7,\"\")",
5309 1 : };
5310 :
5311 : const char* aTextData[4] = {
5312 : "bob",
5313 : "tim",
5314 : "brian",
5315 : "larry",
5316 1 : };
5317 :
5318 : const char* aResults[ 6 ] = {
5319 : "bob",
5320 : "brian",
5321 : "larry",
5322 : "tim",
5323 : "",
5324 : "",
5325 1 : };
5326 : // insert data to sort
5327 1 : SCROW nStart = 1, nEnd = 4;
5328 5 : for ( SCROW i = nStart; i <= nEnd; ++i )
5329 4 : pDoc->SetString( 0, i, 0, rtl::OUString::createFromAscii(aTextData[i-1]) );
5330 : // insert forumulas
5331 1 : nStart = 0;
5332 1 : nEnd = SAL_N_ELEMENTS(aFormulaData);
5333 7 : for ( SCROW i = nStart; i < nEnd; ++i )
5334 6 : pDoc->SetString( 0, i, 1, rtl::OUString::createFromAscii(aFormulaData[i]) );
5335 :
5336 1 : ScSortParam aSortData;
5337 1 : aSortData.nCol1 = 0;
5338 1 : aSortData.nCol2 = 0;
5339 1 : aSortData.nRow1 = 1;
5340 1 : aSortData.nRow2 = 7;
5341 1 : aSortData.maKeyState[0].bDoSort = true;
5342 1 : aSortData.maKeyState[0].nField = 0;
5343 :
5344 1 : pDoc->Sort(0, aSortData, false, NULL);
5345 :
5346 1 : nEnd = SAL_N_ELEMENTS( aResults );
5347 7 : for ( SCROW i = nStart; i < nEnd; ++i )
5348 : {
5349 6 : rtl::OUString sResult;
5350 6 : pDoc->GetString( 0, i + 1, 0, sResult );
5351 6 : CPPUNIT_ASSERT_EQUAL( rtl::OUString::createFromAscii( aResults[ i ] ), sResult );
5352 6 : }
5353 1 : pDoc->DeleteTab(0);
5354 1 : pDoc->DeleteTab(1);
5355 1 : }
5356 :
5357 1 : void Test::testSort()
5358 : {
5359 1 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5360 1 : rtl::OUString aTabName1("test1");
5361 1 : pDoc->InsertTab(0, aTabName1);
5362 :
5363 : const char* aData[][2] = {
5364 : { "2", "4" },
5365 : { "4", "1" },
5366 : { "1", "2" }
5367 1 : };
5368 :
5369 1 : clearRange( pDoc, ScRange(0, 0, 0, 1, SAL_N_ELEMENTS(aData), 0));
5370 1 : ScAddress aPos(0,0,0);
5371 1 : ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
5372 1 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
5373 :
5374 1 : rtl::OUString aHello("Hello");
5375 1 : rtl::OUString aJimBob("Jim Bob");
5376 1 : ScAddress rAddr(1, 1, 0);
5377 1 : ScPostIt* pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
5378 1 : pNote->SetText(rAddr, aHello);
5379 1 : pNote->SetAuthor(aJimBob);
5380 :
5381 1 : ScSortParam aSortData;
5382 1 : aSortData.nCol1 = 1;
5383 1 : aSortData.nCol2 = 1;
5384 1 : aSortData.nRow1 = 0;
5385 1 : aSortData.nRow2 = 2;
5386 1 : aSortData.maKeyState[0].bDoSort = true;
5387 1 : aSortData.maKeyState[0].nField = 1;
5388 :
5389 1 : pDoc->Sort(0, aSortData, false, NULL);
5390 1 : double nVal = pDoc->GetValue(1,0,0);
5391 1 : ASSERT_DOUBLES_EQUAL(nVal, 1.0);
5392 :
5393 : // check that note is also moved
5394 1 : pNote = m_pDoc->GetNotes(0)->findByAddress( 1, 0 );
5395 1 : CPPUNIT_ASSERT(pNote);
5396 :
5397 1 : pDoc->DeleteTab(0);
5398 1 : }
5399 :
5400 1 : void Test::testShiftCells()
5401 : {
5402 1 : m_pDoc->InsertTab(0, "foo");
5403 :
5404 1 : OUString aTestVal("Some Text");
5405 :
5406 : // Text into cell E5.
5407 1 : m_pDoc->SetString(4, 3, 0, aTestVal);
5408 :
5409 : // Insert cell at D5. This should shift the string cell to right.
5410 1 : m_pDoc->InsertCol(3, 0, 3, 0, 3, 1);
5411 1 : OUString aStr = m_pDoc->GetString(5, 3, 0);
5412 1 : CPPUNIT_ASSERT_MESSAGE("We should have a string cell here.", aStr == aTestVal);
5413 1 : CPPUNIT_ASSERT_MESSAGE("D5 is supposed to be blank.", m_pDoc->IsBlockEmpty(0, 3, 4, 3, 4));
5414 :
5415 : // Delete cell D5, to shift the text cell back into D5.
5416 1 : m_pDoc->DeleteCol(3, 0, 3, 0, 3, 1);
5417 1 : aStr = m_pDoc->GetString(4, 3, 0);
5418 1 : CPPUNIT_ASSERT_MESSAGE("We should have a string cell here.", aStr == aTestVal);
5419 1 : CPPUNIT_ASSERT_MESSAGE("E5 is supposed to be blank.", m_pDoc->IsBlockEmpty(0, 4, 4, 4, 4));
5420 :
5421 1 : m_pDoc->DeleteTab(0);
5422 1 : }
5423 :
5424 1 : void Test::testDeleteRow()
5425 : {
5426 1 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5427 1 : rtl::OUString aSheet1("Sheet1");
5428 1 : pDoc->InsertTab(0, aSheet1);
5429 :
5430 1 : rtl::OUString aHello("Hello");
5431 1 : rtl::OUString aJimBob("Jim Bob");
5432 1 : ScAddress rAddr(1, 1, 0);
5433 1 : ScPostIt* pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
5434 1 : pNote->SetText(rAddr, aHello);
5435 1 : pNote->SetAuthor(aJimBob);
5436 :
5437 1 : pDoc->DeleteRow(0, 0, MAXCOL, 0, 1, 1);
5438 :
5439 1 : CPPUNIT_ASSERT(m_pDoc->GetNotes(0)->empty());
5440 1 : pDoc->DeleteTab(0);
5441 1 : }
5442 :
5443 1 : void Test::testDeleteCol()
5444 : {
5445 1 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5446 1 : rtl::OUString aSheet1("Sheet1");
5447 1 : pDoc->InsertTab(0, aSheet1);
5448 :
5449 1 : rtl::OUString aHello("Hello");
5450 1 : rtl::OUString aJimBob("Jim Bob");
5451 1 : ScAddress rAddr(1, 1, 0);
5452 1 : ScPostIt* pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
5453 1 : pNote->SetText(rAddr, aHello);
5454 1 : pNote->SetAuthor(aJimBob);
5455 :
5456 1 : pDoc->DeleteCol(0, 0, MAXROW, 0, 1, 1);
5457 :
5458 1 : CPPUNIT_ASSERT(m_pDoc->GetNotes(0)->empty());
5459 1 : pDoc->DeleteTab(0);
5460 1 : }
5461 :
5462 1 : CPPUNIT_TEST_SUITE_REGISTRATION(Test);
5463 :
5464 : }
5465 :
5466 4 : CPPUNIT_PLUGIN_IMPLEMENT();
5467 :
5468 : /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
|
