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 216 : 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 4 : CPPUNIT_TEST_SUITE(Test);
244 2 : CPPUNIT_TEST(testCollator);
245 2 : CPPUNIT_TEST(testRangeList);
246 2 : CPPUNIT_TEST(testInput);
247 2 : CPPUNIT_TEST(testCellFunctions);
248 2 : CPPUNIT_TEST(testSheetsFunc);
249 2 : CPPUNIT_TEST(testVolatileFunc);
250 2 : CPPUNIT_TEST(testFormulaDepTracking);
251 2 : CPPUNIT_TEST(testFuncParam);
252 2 : CPPUNIT_TEST(testNamedRange);
253 2 : CPPUNIT_TEST(testCSV);
254 2 : CPPUNIT_TEST(testMatrix);
255 2 : CPPUNIT_TEST(testEnterMixedMatrix);
256 2 : CPPUNIT_TEST(testPivotTable);
257 2 : CPPUNIT_TEST(testPivotTableLabels);
258 2 : CPPUNIT_TEST(testPivotTableDateLabels);
259 2 : CPPUNIT_TEST(testPivotTableFilters);
260 2 : CPPUNIT_TEST(testPivotTableNamedSource);
261 2 : CPPUNIT_TEST(testPivotTableCache);
262 2 : CPPUNIT_TEST(testPivotTableDuplicateDataFields);
263 2 : CPPUNIT_TEST(testPivotTableNormalGrouping);
264 2 : CPPUNIT_TEST(testPivotTableNumberGrouping);
265 2 : CPPUNIT_TEST(testPivotTableDateGrouping);
266 2 : CPPUNIT_TEST(testPivotTableEmptyRows);
267 2 : CPPUNIT_TEST(testPivotTableTextNumber);
268 2 : CPPUNIT_TEST(testPivotTableCaseInsensitiveStrings);
269 2 : CPPUNIT_TEST(testPivotTableNumStability);
270 2 : CPPUNIT_TEST(testSheetCopy);
271 2 : CPPUNIT_TEST(testSheetMove);
272 2 : CPPUNIT_TEST(testExternalRef);
273 2 : CPPUNIT_TEST(testExternalRefFunctions);
274 2 : CPPUNIT_TEST(testDataArea);
275 2 : CPPUNIT_TEST(testGraphicsInGroup);
276 2 : CPPUNIT_TEST(testGraphicsOnSheetMove);
277 2 : CPPUNIT_TEST(testPostIts);
278 2 : CPPUNIT_TEST(testStreamValid);
279 2 : CPPUNIT_TEST(testFunctionLists);
280 2 : CPPUNIT_TEST(testToggleRefFlag);
281 2 : CPPUNIT_TEST(testAutofilter);
282 2 : CPPUNIT_TEST(testCopyPaste);
283 2 : CPPUNIT_TEST(testMergedCells);
284 2 : CPPUNIT_TEST(testUpdateReference);
285 2 : CPPUNIT_TEST(testJumpToPrecedentsDependents);
286 2 : CPPUNIT_TEST(testSetBackgroundColor);
287 2 : CPPUNIT_TEST(testRenameTable);
288 2 : CPPUNIT_TEST(testAutoFill);
289 2 : CPPUNIT_TEST(testCopyPasteFormulas);
290 2 : CPPUNIT_TEST(testCopyPasteFormulasExternalDoc);
291 2 : CPPUNIT_TEST(testFindAreaPosRowDown);
292 2 : CPPUNIT_TEST(testFindAreaPosColRight);
293 2 : CPPUNIT_TEST(testSort);
294 2 : CPPUNIT_TEST(testSortWithFormulaRefs);
295 2 : CPPUNIT_TEST(testShiftCells);
296 2 : CPPUNIT_TEST(testDeleteRow);
297 2 : CPPUNIT_TEST(testDeleteCol);
298 4 : CPPUNIT_TEST_SUITE_END();
299 :
300 : private:
301 : ScDocument *m_pDoc;
302 : ScDocShellRef m_xDocShRef;
303 : };
304 :
305 30 : void clearRange(ScDocument* pDoc, const ScRange& rRange)
306 : {
307 30 : ScMarkData aMarkData;
308 30 : aMarkData.SetMarkArea(rRange);
309 : pDoc->DeleteArea(
310 30 : rRange.aStart.Col(), rRange.aStart.Row(),
311 60 : rRange.aEnd.Col(), rRange.aEnd.Row(), aMarkData, IDF_CONTENTS);
312 30 : }
313 :
314 46 : void printRange(ScDocument* pDoc, const ScRange& rRange, const char* pCaption)
315 : {
316 46 : SCROW nRow1 = rRange.aStart.Row(), nRow2 = rRange.aEnd.Row();
317 46 : SCCOL nCol1 = rRange.aStart.Col(), nCol2 = rRange.aEnd.Col();
318 46 : SheetPrinter printer(nRow2 - nRow1 + 1, nCol2 - nCol1 + 1);
319 492 : for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
320 : {
321 1714 : for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
322 : {
323 1268 : rtl::OUString aVal;
324 1268 : pDoc->GetString(nCol, nRow, rRange.aStart.Tab(), aVal);
325 1268 : printer.set(nRow-nRow1, nCol-nCol1, aVal);
326 1268 : }
327 : }
328 46 : printer.print(pCaption);
329 46 : }
330 :
331 : template<size_t _Size>
332 24 : ScRange insertRangeData(ScDocument* pDoc, const ScAddress& rPos, const char* aData[][_Size], size_t nRowCount)
333 : {
334 94 : for (size_t i = 0; i < _Size; ++i)
335 : {
336 742 : for (size_t j = 0; j < nRowCount; ++j)
337 : {
338 672 : SCCOL nCol = i + rPos.Col();
339 672 : SCROW nRow = j + rPos.Row();
340 672 : pDoc->SetString(nCol, nRow, rPos.Tab(), OUString(aData[j][i], strlen(aData[j][i]), RTL_TEXTENCODING_UTF8));
341 : }
342 : }
343 :
344 24 : ScRange aRange(rPos);
345 24 : aRange.aEnd.SetCol(rPos.Col()+_Size-1);
346 24 : aRange.aEnd.SetRow(rPos.Row()+nRowCount-1);
347 24 : printRange(pDoc, aRange, "Range data content");
348 24 : 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 4 : AutoCalcSwitch(ScDocument* pDoc, bool bAutoCalc) : mpDoc(pDoc), mbOldValue(pDoc->GetAutoCalc())
360 : {
361 4 : mpDoc->SetAutoCalc(bAutoCalc);
362 4 : }
363 :
364 4 : ~AutoCalcSwitch()
365 : {
366 4 : mpDoc->SetAutoCalc(mbOldValue);
367 4 : }
368 : };
369 :
370 : /**
371 : * Temporarily set formula grammar.
372 : */
373 : class FormulaGrammarSwitch
374 : {
375 : ScDocument* mpDoc;
376 : formula::FormulaGrammar::Grammar meOldGrammar;
377 : public:
378 4 : FormulaGrammarSwitch(ScDocument* pDoc, formula::FormulaGrammar::Grammar eGrammar) :
379 4 : mpDoc(pDoc), meOldGrammar(pDoc->GetGrammar())
380 : {
381 4 : mpDoc->SetGrammar(eGrammar);
382 4 : }
383 :
384 4 : ~FormulaGrammarSwitch()
385 : {
386 4 : mpDoc->SetGrammar(meOldGrammar);
387 4 : }
388 : };
389 :
390 108 : Test::Test()
391 108 : : m_pDoc(0)
392 : {
393 108 : }
394 :
395 108 : void Test::setUp()
396 : {
397 108 : BootstrapFixture::setUp();
398 :
399 108 : ScDLL::Init();
400 : m_xDocShRef = new ScDocShell(
401 : SFXMODEL_STANDARD |
402 : SFXMODEL_DISABLE_EMBEDDED_SCRIPTS |
403 108 : SFXMODEL_DISABLE_DOCUMENT_RECOVERY);
404 :
405 108 : m_pDoc = m_xDocShRef->GetDocument();
406 108 : }
407 :
408 108 : void Test::tearDown()
409 : {
410 108 : m_xDocShRef.Clear();
411 108 : BootstrapFixture::tearDown();
412 108 : }
413 :
414 2 : void Test::testCollator()
415 : {
416 2 : OUString s1("A");
417 2 : OUString s2("B");
418 2 : CollatorWrapper* p = ScGlobal::GetCollator();
419 2 : sal_Int32 nRes = p->compareString(s1, s2);
420 2 : CPPUNIT_ASSERT_MESSAGE("these strings are supposed to be different!", nRes != 0);
421 2 : }
422 :
423 2 : void Test::testRangeList()
424 : {
425 2 : m_pDoc->InsertTab(0, "foo");
426 :
427 2 : ScRangeList aRL;
428 2 : aRL.Append(ScRange(1,1,0,3,10,0));
429 2 : CPPUNIT_ASSERT_MESSAGE("List should have one range.", aRL.size() == 1);
430 2 : const ScRange* p = aRL[0];
431 2 : CPPUNIT_ASSERT_MESSAGE("Failed to get the range object.", p);
432 2 : 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 2 : m_pDoc->DeleteTab(0);
437 2 : }
438 :
439 2 : void Test::testInput()
440 : {
441 2 : rtl::OUString aTabName("foo");
442 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
443 2 : m_pDoc->InsertTab (0, aTabName));
444 :
445 2 : OUString numstr("'10.5");
446 2 : OUString str("'apple'");
447 2 : OUString test;
448 :
449 2 : m_pDoc->SetString(0, 0, 0, numstr);
450 2 : m_pDoc->GetString(0, 0, 0, test);
451 2 : bool bTest = test == "10.5";
452 2 : CPPUNIT_ASSERT_MESSAGE("String number should have the first apostrophe stripped.", bTest);
453 2 : m_pDoc->SetString(0, 0, 0, str);
454 2 : m_pDoc->GetString(0, 0, 0, test);
455 2 : bTest = test == "'apple'";
456 2 : CPPUNIT_ASSERT_MESSAGE("Text content should have retained the first apostrophe.", bTest);
457 :
458 : // Customized string handling policy.
459 2 : ScSetStringParam aParam;
460 2 : aParam.mbDetectNumberFormat = false;
461 2 : aParam.meSetTextNumFormat = ScSetStringParam::Always;
462 2 : aParam.mbHandleApostrophe = false;
463 2 : m_pDoc->SetString(0, 0, 0, "000123", &aParam);
464 2 : m_pDoc->GetString(0, 0, 0, test);
465 2 : CPPUNIT_ASSERT_MESSAGE("Text content should have been treated as string, not number.", test == "000123");
466 :
467 2 : m_pDoc->DeleteTab(0);
468 2 : }
469 :
470 2 : void testFuncSUM(ScDocument* pDoc)
471 : {
472 2 : double val = 1;
473 : double result;
474 2 : pDoc->SetValue (0, 0, 0, val);
475 2 : pDoc->SetValue (0, 1, 0, val);
476 2 : pDoc->SetString (0, 2, 0, rtl::OUString("=SUM(A1:A2)"));
477 2 : pDoc->CalcAll();
478 2 : pDoc->GetValue (0, 2, 0, result);
479 2 : CPPUNIT_ASSERT_MESSAGE ("calculation failed", result == 2.0);
480 2 : }
481 :
482 2 : void testFuncPRODUCT(ScDocument* pDoc)
483 : {
484 2 : double val = 1;
485 : double result;
486 2 : pDoc->SetValue(0, 0, 0, val);
487 2 : val = 2;
488 2 : pDoc->SetValue(0, 1, 0, val);
489 2 : val = 3;
490 2 : pDoc->SetValue(0, 2, 0, val);
491 2 : pDoc->SetString(0, 3, 0, OUString("=PRODUCT(A1:A3)"));
492 2 : pDoc->CalcAll();
493 2 : pDoc->GetValue(0, 3, 0, result);
494 2 : CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT failed", result == 6.0);
495 :
496 2 : pDoc->SetString(0, 4, 0, OUString("=PRODUCT({1;2;3})"));
497 2 : pDoc->CalcAll();
498 2 : pDoc->GetValue(0, 4, 0, result);
499 2 : CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT with inline array failed", result == 6.0);
500 2 : }
501 :
502 2 : void testFuncN(ScDocument* pDoc)
503 : {
504 : double result;
505 :
506 : // Clear the area first.
507 2 : clearRange(pDoc, ScRange(0, 0, 0, 1, 20, 0));
508 :
509 : // Put values to reference.
510 2 : double val = 0;
511 2 : pDoc->SetValue(0, 0, 0, val);
512 2 : pDoc->SetString(0, 2, 0, OUString("Text"));
513 2 : val = 1;
514 2 : pDoc->SetValue(0, 3, 0, val);
515 2 : val = -1;
516 2 : pDoc->SetValue(0, 4, 0, val);
517 2 : val = 12.3;
518 2 : pDoc->SetValue(0, 5, 0, val);
519 2 : pDoc->SetString(0, 6, 0, OUString("'12.3"));
520 :
521 : // Cell references
522 2 : pDoc->SetString(1, 0, 0, OUString("=N(A1)"));
523 2 : pDoc->SetString(1, 1, 0, OUString("=N(A2)"));
524 2 : pDoc->SetString(1, 2, 0, OUString("=N(A3)"));
525 2 : pDoc->SetString(1, 3, 0, OUString("=N(A4)"));
526 2 : pDoc->SetString(1, 4, 0, OUString("=N(A5)"));
527 2 : pDoc->SetString(1, 5, 0, OUString("=N(A6)"));
528 2 : pDoc->SetString(1, 6, 0, OUString("=N(A9)"));
529 :
530 : // In-line values
531 2 : pDoc->SetString(1, 7, 0, OUString("=N(0)"));
532 2 : pDoc->SetString(1, 8, 0, OUString("=N(1)"));
533 2 : pDoc->SetString(1, 9, 0, OUString("=N(-1)"));
534 2 : pDoc->SetString(1, 10, 0, OUString("=N(123)"));
535 2 : pDoc->SetString(1, 11, 0, OUString("=N(\"\")"));
536 2 : pDoc->SetString(1, 12, 0, OUString("=N(\"12\")"));
537 2 : pDoc->SetString(1, 13, 0, OUString("=N(\"foo\")"));
538 :
539 : // Range references
540 2 : pDoc->SetString(2, 2, 0, OUString("=N(A1:A8)"));
541 2 : pDoc->SetString(2, 3, 0, OUString("=N(A1:A8)"));
542 2 : pDoc->SetString(2, 4, 0, OUString("=N(A1:A8)"));
543 2 : pDoc->SetString(2, 5, 0, OUString("=N(A1:A8)"));
544 :
545 : // Calculate and check the results.
546 2 : 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 2 : };
551 30 : for (size_t i = 0; i < SAL_N_ELEMENTS(checks1); ++i)
552 : {
553 28 : pDoc->GetValue(1, i, 0, result);
554 28 : bool bGood = result == checks1[i];
555 28 : 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 2 : };
564 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(checks2); ++i)
565 : {
566 8 : pDoc->GetValue(1, i+2, 0, result);
567 8 : bool bGood = result == checks2[i];
568 8 : 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 2 : }
575 :
576 2 : void testFuncCOUNTIF(ScDocument* pDoc)
577 : {
578 : // COUNTIF (test case adopted from OOo i#36381)
579 :
580 : // Empty A1:A39 first.
581 2 : 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 2 : };
595 :
596 2 : SCROW nRows = SAL_N_ELEMENTS(aData);
597 20 : for (SCROW i = 0; i < nRows; ++i)
598 18 : pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
599 :
600 2 : 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 2 : };
618 :
619 2 : nRows = SAL_N_ELEMENTS(aChecks);
620 24 : for (SCROW i = 0; i < nRows; ++i)
621 : {
622 22 : SCROW nRow = 20 + i;
623 22 : pDoc->SetString(0, nRow, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
624 : }
625 2 : pDoc->CalcAll();
626 :
627 24 : for (SCROW i = 0; i < nRows; ++i)
628 : {
629 : double result;
630 22 : SCROW nRow = 20 + i;
631 22 : pDoc->GetValue(0, nRow, 0, result);
632 22 : bool bGood = result == aChecks[i].fResult;
633 22 : 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 2 : clearRange(pDoc, ScRange(0, 0, 0, 0, 1, 0));
645 :
646 2 : pDoc->SetString(0, 0, 0, rtl::OUString("=\"\""));
647 2 : pDoc->SetString(0, 1, 0, rtl::OUString("=COUNTIF(A1;1)"));
648 2 : pDoc->CalcAll();
649 :
650 2 : double result = pDoc->GetValue(0, 1, 0);
651 2 : CPPUNIT_ASSERT_MESSAGE("We shouldn't count empty string as valid number.", result == 0.0);
652 2 : }
653 :
654 2 : void testFuncVLOOKUP(ScDocument* pDoc)
655 : {
656 : // VLOOKUP
657 :
658 : // Clear A1:F40.
659 2 : 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 2 : };
679 :
680 : // Insert raw data into A1:B14.
681 30 : for (SCROW i = 0; aData[i][0]; ++i)
682 : {
683 28 : pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i][0]));
684 28 : pDoc->SetString(1, i, 0, rtl::OUString::createFromAscii(aData[i][1]));
685 : }
686 :
687 2 : 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 2 : };
712 :
713 : // Insert formula data into D1:E18.
714 38 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
715 : {
716 36 : pDoc->SetString(3, i, 0, rtl::OUString::createFromAscii(aChecks[i].pLookup));
717 36 : pDoc->SetString(4, i, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
718 : }
719 2 : pDoc->CalcAll();
720 2 : printRange(pDoc, ScRange(3, 0, 0, 4, 17, 0), "formula data for VLOOKUP");
721 :
722 : // Verify results.
723 38 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
724 : {
725 36 : if (i == 0)
726 : // Skip the header row.
727 2 : continue;
728 :
729 34 : rtl::OUString aRes;
730 34 : pDoc->GetString(4, i, 0, aRes);
731 34 : bool bGood = aRes.equalsAscii(aChecks[i].pRes);
732 34 : 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 34 : }
739 2 : }
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 4 : void runTestMATCH(ScDocument* pDoc, const char* aData[_DataSize], StrStrCheck aChecks[_FormulaSize])
753 : {
754 4 : size_t nDataSize = _DataSize;
755 52 : for (size_t i = 0; i < nDataSize; ++i)
756 48 : pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
757 :
758 66 : for (size_t i = 0; i < _FormulaSize; ++i)
759 : {
760 62 : pDoc->SetString(1, i, 0, rtl::OUString::createFromAscii(aChecks[i].pVal));
761 :
762 62 : rtl::OUStringBuffer aBuf;
763 62 : aBuf.appendAscii("=MATCH(B");
764 62 : aBuf.append(static_cast<sal_Int32>(i+1));
765 62 : aBuf.appendAscii(";A1:A");
766 62 : aBuf.append(static_cast<sal_Int32>(nDataSize));
767 62 : aBuf.appendAscii(";");
768 62 : aBuf.append(static_cast<sal_Int32>(_Type));
769 62 : aBuf.appendAscii(")");
770 62 : rtl::OUString aFormula = aBuf.makeStringAndClear();
771 62 : pDoc->SetString(2, i, 0, aFormula);
772 : }
773 :
774 4 : pDoc->CalcAll();
775 4 : printRange(pDoc, ScRange(0, 0, 0, 2, _FormulaSize-1, 0), "MATCH");
776 :
777 : // verify the results.
778 66 : for (size_t i = 0; i < _FormulaSize; ++i)
779 : {
780 62 : rtl::OUString aStr;
781 62 : pDoc->GetString(2, i, 0, aStr);
782 62 : 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 4 : }
790 :
791 2 : void testFuncMATCH(ScDocument* pDoc)
792 : {
793 2 : 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 2 : };
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 2 : };
831 :
832 2 : 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 2 : };
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 2 : };
873 :
874 2 : runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),-1>(pDoc, aData, aChecks);
875 : }
876 2 : }
877 :
878 2 : void testFuncCELL(ScDocument* pDoc)
879 : {
880 2 : clearRange(pDoc, ScRange(0, 0, 0, 2, 20, 0)); // Clear A1:C21.
881 :
882 : {
883 2 : const char* pContent = "Some random text";
884 2 : pDoc->SetString(2, 9, 0, rtl::OUString::createFromAscii(pContent)); // Set this value to C10.
885 2 : double val = 1.2;
886 2 : 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 2 : };
901 :
902 22 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
903 20 : pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aChecks[i].pVal));
904 2 : pDoc->CalcAll();
905 :
906 22 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
907 : {
908 20 : rtl::OUString aVal = pDoc->GetString(0, i, 0);
909 20 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for CELL", aVal.equalsAscii(aChecks[i].pRes));
910 20 : }
911 : }
912 2 : }
913 :
914 : /** See also test case document fdo#44456 sheet cpearson */
915 2 : 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 2 : };
934 :
935 2 : clearRange( pDoc, ScRange(0, 0, 0, 4, SAL_N_ELEMENTS(aData), 0));
936 2 : ScAddress aPos(0,0,0);
937 2 : ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
938 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
939 :
940 2 : pDoc->CalcAll();
941 :
942 32 : for (size_t i = 0; i < SAL_N_ELEMENTS(aData); ++i)
943 : {
944 30 : 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 30 : CPPUNIT_ASSERT_MESSAGE("Unexpected result for DATEDIF", aVal.equalsAscii( aData[i][3]));
947 30 : }
948 2 : }
949 :
950 2 : void testFuncINDIRECT(ScDocument* pDoc)
951 : {
952 2 : clearRange(pDoc, ScRange(0, 0, 0, 0, 10, 0)); // Clear A1:A11
953 2 : rtl::OUString aTabName;
954 2 : bool bGood = pDoc->GetName(0, aTabName);
955 2 : CPPUNIT_ASSERT_MESSAGE("failed to get sheet name.", bGood);
956 :
957 2 : rtl::OUString aTest = "Test", aRefErr = "#REF!";
958 2 : pDoc->SetString(0, 10, 0, aTest);
959 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", pDoc->GetString(0,10,0) == aTest);
960 :
961 2 : rtl::OUString aPrefix = "=INDIRECT(\"";
962 :
963 2 : rtl::OUString aFormula = aPrefix + aTabName + ".A11\")"; // Calc A1
964 2 : pDoc->SetString(0, 0, 0, aFormula);
965 2 : aFormula = aPrefix + aTabName + "!A11\")"; // Excel A1
966 2 : pDoc->SetString(0, 1, 0, aFormula);
967 2 : aFormula = aPrefix + aTabName + "!R11C1\")"; // Excel R1C1
968 2 : pDoc->SetString(0, 2, 0, aFormula);
969 2 : aFormula = aPrefix + aTabName + "!R11C1\";0)"; // Excel R1C1 (forced)
970 2 : pDoc->SetString(0, 3, 0, aFormula);
971 :
972 2 : 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 2 : };
978 :
979 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
980 : {
981 8 : rtl::OUString aVal = pDoc->GetString(0, i, 0);
982 8 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
983 8 : }
984 : }
985 :
986 2 : ScCalcConfig aConfig;
987 2 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_OOO;
988 2 : ScInterpreter::SetGlobalConfig(aConfig);
989 2 : pDoc->CalcAll();
990 : {
991 : // Explicit Calc A1 syntax
992 : const rtl::OUString* aChecks[] = {
993 : &aTest, &aRefErr, &aRefErr, &aTest
994 2 : };
995 :
996 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
997 : {
998 8 : rtl::OUString aVal = pDoc->GetString(0, i, 0);
999 8 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
1000 8 : }
1001 : }
1002 :
1003 2 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_A1;
1004 2 : ScInterpreter::SetGlobalConfig(aConfig);
1005 2 : pDoc->CalcAll();
1006 : {
1007 : // Excel A1 syntax
1008 : const rtl::OUString* aChecks[] = {
1009 : &aRefErr, &aTest, &aRefErr, &aTest
1010 2 : };
1011 :
1012 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1013 : {
1014 8 : rtl::OUString aVal = pDoc->GetString(0, i, 0);
1015 8 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
1016 8 : }
1017 : }
1018 :
1019 2 : aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_R1C1;
1020 2 : ScInterpreter::SetGlobalConfig(aConfig);
1021 2 : pDoc->CalcAll();
1022 : {
1023 : // Excel R1C1 syntax
1024 : const rtl::OUString* aChecks[] = {
1025 : &aRefErr, &aRefErr, &aTest, &aTest
1026 2 : };
1027 :
1028 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1029 : {
1030 8 : rtl::OUString aVal = pDoc->GetString(0, i, 0);
1031 8 : CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
1032 8 : }
1033 2 : }
1034 2 : }
1035 :
1036 2 : void Test::testCellFunctions()
1037 : {
1038 2 : rtl::OUString aTabName("foo");
1039 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1040 2 : m_pDoc->InsertTab (0, aTabName));
1041 :
1042 2 : testFuncSUM(m_pDoc);
1043 2 : testFuncPRODUCT(m_pDoc);
1044 2 : testFuncN(m_pDoc);
1045 2 : testFuncCOUNTIF(m_pDoc);
1046 2 : testFuncVLOOKUP(m_pDoc);
1047 2 : testFuncMATCH(m_pDoc);
1048 2 : testFuncCELL(m_pDoc);
1049 2 : testFuncDATEDIF(m_pDoc);
1050 2 : testFuncINDIRECT(m_pDoc);
1051 :
1052 2 : m_pDoc->DeleteTab(0);
1053 2 : }
1054 :
1055 2 : void Test::testSheetsFunc()
1056 : {
1057 2 : rtl::OUString aTabName1("test1");
1058 2 : rtl::OUString aTabName2("test2");
1059 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1060 2 : m_pDoc->InsertTab (SC_TAB_APPEND, aTabName1));
1061 :
1062 2 : m_pDoc->SetString(0, 0, 0, OUString("=SHEETS()"));
1063 2 : m_pDoc->CalcFormulaTree(false, false);
1064 : double original;
1065 2 : m_pDoc->GetValue(0, 0, 0, original);
1066 :
1067 4 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() should equal the number of sheets, but doesn't.",
1068 2 : static_cast<SCTAB>(original) == m_pDoc->GetTableCount());
1069 :
1070 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1071 2 : m_pDoc->InsertTab (SC_TAB_APPEND, aTabName2));
1072 :
1073 : double modified;
1074 2 : m_pDoc->GetValue(0, 0, 0, modified);
1075 4 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() did not get updated after sheet insertion.",
1076 2 : modified - original == 1.0);
1077 :
1078 2 : SCTAB nTabCount = m_pDoc->GetTableCount();
1079 2 : m_pDoc->DeleteTab(--nTabCount);
1080 :
1081 2 : m_pDoc->GetValue(0, 0, 0, modified);
1082 4 : CPPUNIT_ASSERT_MESSAGE("result of SHEETS() did not get updated after sheet removal.",
1083 2 : modified - original == 0.0);
1084 :
1085 2 : m_pDoc->DeleteTab(--nTabCount);
1086 2 : }
1087 :
1088 2 : void Test::testVolatileFunc()
1089 : {
1090 2 : rtl::OUString aTabName("foo");
1091 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1092 2 : m_pDoc->InsertTab (0, aTabName));
1093 :
1094 2 : double val = 1;
1095 2 : m_pDoc->SetValue(0, 0, 0, val);
1096 2 : m_pDoc->SetString(0, 1, 0, OUString("=IF(A1>0;NOW();0"));
1097 : double now1;
1098 2 : m_pDoc->GetValue(0, 1, 0, now1);
1099 2 : CPPUNIT_ASSERT_MESSAGE("Value of NOW() should be positive.", now1 > 0.0);
1100 :
1101 2 : val = 0;
1102 2 : m_pDoc->SetValue(0, 0, 0, val);
1103 2 : m_pDoc->CalcFormulaTree(false, false);
1104 : double zero;
1105 2 : m_pDoc->GetValue(0, 1, 0, zero);
1106 2 : CPPUNIT_ASSERT_MESSAGE("Result should equal the 3rd parameter of IF, which is zero.", zero == 0.0);
1107 :
1108 2 : val = 1;
1109 2 : m_pDoc->SetValue(0, 0, 0, val);
1110 2 : m_pDoc->CalcFormulaTree(false, false);
1111 : double now2;
1112 2 : m_pDoc->GetValue(0, 1, 0, now2);
1113 2 : CPPUNIT_ASSERT_MESSAGE("Result should be the value of NOW() again.", (now2 - now1) >= 0.0);
1114 :
1115 2 : m_pDoc->DeleteTab(0);
1116 2 : }
1117 :
1118 2 : void Test::testFormulaDepTracking()
1119 : {
1120 2 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet", m_pDoc->InsertTab (0, "foo"));
1121 :
1122 2 : AutoCalcSwitch aACSwitch(m_pDoc, true); // turn on auto calculation.
1123 :
1124 : // B2 listens on D2.
1125 2 : m_pDoc->SetString(1, 1, 0, "=D2");
1126 2 : double val = -999.0; // dummy initial value
1127 2 : m_pDoc->GetValue(1, 1, 0, val);
1128 2 : 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 2 : m_pDoc->SetValue(3, 1, 0, 1.1);
1132 2 : m_pDoc->GetValue(1, 1, 0, val);
1133 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on value change.", val == 1.1);
1134 :
1135 : // And again.
1136 2 : m_pDoc->SetValue(3, 1, 0, 2.2);
1137 2 : m_pDoc->GetValue(1, 1, 0, val);
1138 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on value change.", val == 2.2);
1139 :
1140 2 : 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 2 : m_pDoc->SetString(1, 1, 0, "=SUM(D2:E6)");
1146 2 : m_pDoc->GetValue(1, 1, 0, val);
1147 2 : 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 2 : m_pDoc->SetValue(4, 2, 0, 2.4);
1151 2 : m_pDoc->GetValue(1, 1, 0, val);
1152 2 : 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 2 : m_pDoc->SetValue(3, 4, 0, 1.2);
1159 2 : m_pDoc->GetValue(1, 1, 0, val);
1160 2 : 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 2 : m_pDoc->SetValue(3, 1, 0, 1.0);
1164 2 : m_pDoc->GetValue(1, 1, 0, val);
1165 2 : 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 2 : m_pDoc->SetValue(4, 5, 0, 2.0);
1169 2 : m_pDoc->GetValue(1, 1, 0, val);
1170 2 : 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 2 : m_pDoc->SetValue(3, 5, 0, 3.0);
1174 2 : m_pDoc->GetValue(1, 1, 0, val);
1175 2 : 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 2 : m_pDoc->SetValue(4, 1, 0, 0.4);
1179 2 : m_pDoc->GetValue(1, 1, 0, val);
1180 2 : 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 2 : m_pDoc->SetValue(4, 1, 0, 2.4);
1184 2 : m_pDoc->GetValue(1, 1, 0, val);
1185 2 : CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 12.0));
1186 :
1187 2 : 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 2 : FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1193 :
1194 2 : val = 0.0;
1195 20 : for (SCROW nRow = 1; nRow <= 9; ++nRow)
1196 : {
1197 : // Static value in column 1.
1198 18 : m_pDoc->SetValue(0, nRow, 0, ++val);
1199 :
1200 : // Formula in column 2 that references cell to the left.
1201 18 : m_pDoc->SetString(1, nRow, 0, "=RC[-1]");
1202 :
1203 : // Formula in column 3 that references cell to the left.
1204 18 : m_pDoc->SetString(2, nRow, 0, "=RC[-1]*2");
1205 : }
1206 :
1207 : // Check formula values.
1208 2 : val = 0.0;
1209 20 : for (SCROW nRow = 1; nRow <= 9; ++nRow)
1210 : {
1211 18 : ++val;
1212 18 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(1, nRow, 0) == val);
1213 18 : 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 2 : m_pDoc->SetString(0, 4, 0, "=R2C3");
1219 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(0, 4, 0) == 2.0);
1220 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(1, 4, 0) == 2.0);
1221 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(2, 4, 0) == 4.0);
1222 :
1223 2 : m_pDoc->DeleteTab(0);
1224 2 : }
1225 :
1226 2 : void Test::testFuncParam()
1227 : {
1228 2 : rtl::OUString aTabName("foo");
1229 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1230 2 : m_pDoc->InsertTab (0, aTabName));
1231 :
1232 : // First, the normal case, with no missing parameters.
1233 2 : m_pDoc->SetString(0, 0, 0, OUString("=AVERAGE(1;2;3)"));
1234 2 : m_pDoc->CalcFormulaTree(false, false);
1235 : double val;
1236 2 : m_pDoc->GetValue(0, 0, 0, val);
1237 2 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 2);
1238 :
1239 : // Now function with missing parameters. Missing values should be treated
1240 : // as zeros.
1241 2 : m_pDoc->SetString(0, 0, 0, OUString("=AVERAGE(1;;;)"));
1242 2 : m_pDoc->CalcFormulaTree(false, false);
1243 2 : m_pDoc->GetValue(0, 0, 0, val);
1244 2 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 0.25);
1245 :
1246 : // Conversion of string to numeric argument.
1247 2 : m_pDoc->SetString(0, 0, 0, OUString("=\"\"+3")); // empty string
1248 2 : m_pDoc->SetString(0, 1, 0, OUString("=\" \"+3")); // only blank
1249 2 : m_pDoc->SetString(0, 2, 0, OUString("=\" 4 \"+3")); // number in blanks
1250 2 : m_pDoc->SetString(0, 3, 0, OUString("=\" x \"+3")); // non-numeric => #VALUE! error
1251 :
1252 2 : rtl::OUString aVal;
1253 2 : ScCalcConfig aConfig;
1254 :
1255 : // With "Empty string as zero" option.
1256 2 : aConfig.mbEmptyStringAsZero = true;
1257 2 : ScInterpreter::SetGlobalConfig(aConfig);
1258 2 : m_pDoc->CalcAll();
1259 2 : m_pDoc->GetValue(0, 0, 0, val);
1260 2 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 3);
1261 2 : m_pDoc->GetValue(0, 1, 0, val);
1262 2 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 3);
1263 2 : m_pDoc->GetValue(0, 2, 0, val);
1264 2 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 7);
1265 2 : aVal = m_pDoc->GetString( 0, 3, 0);
1266 2 : CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1267 :
1268 : // Without "Empty string as zero" option.
1269 2 : aConfig.mbEmptyStringAsZero = false;
1270 2 : ScInterpreter::SetGlobalConfig(aConfig);
1271 2 : m_pDoc->CalcAll();
1272 2 : aVal = m_pDoc->GetString( 0, 0, 0);
1273 2 : CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1274 2 : aVal = m_pDoc->GetString( 0, 1, 0);
1275 2 : CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1276 2 : m_pDoc->GetValue(0, 2, 0, val);
1277 2 : CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 7);
1278 2 : aVal = m_pDoc->GetString( 0, 3, 0);
1279 2 : CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1280 :
1281 2 : m_pDoc->DeleteTab(0);
1282 2 : }
1283 :
1284 2 : 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 2 : };
1294 :
1295 2 : rtl::OUString aTabName("Sheet1");
1296 4 : CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1297 2 : m_pDoc->InsertTab (0, aTabName));
1298 :
1299 2 : m_pDoc->SetValue (0, 0, 0, 101);
1300 :
1301 2 : ScAddress aA1(0, 0, 0);
1302 2 : ScRangeName* pNewRanges = new ScRangeName();
1303 10 : 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 8 : aA1, 0, formula::FormulaGrammar::GRAM_ENGLISH);
1310 8 : pNew->SetIndex(aNames[i].nIndex);
1311 8 : bool bSuccess = pNewRanges->insert(pNew);
1312 8 : CPPUNIT_ASSERT_MESSAGE ("insertion failed", bSuccess);
1313 : }
1314 :
1315 : // Make sure the index lookup does the right thing.
1316 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
1317 : {
1318 8 : const ScRangeData* p = pNewRanges->findByIndex(aNames[i].nIndex);
1319 8 : CPPUNIT_ASSERT_MESSAGE("lookup of range name by index failed.", p);
1320 8 : rtl::OUString aName = p->GetName();
1321 8 : CPPUNIT_ASSERT_MESSAGE("wrong range name is retrieved.", aName.equalsAscii(aNames[i].pName));
1322 8 : }
1323 :
1324 : // Test usage in formula expression.
1325 2 : m_pDoc->SetRangeName(pNewRanges);
1326 2 : m_pDoc->SetString (1, 0, 0, rtl::OUString("=A1/Divisor"));
1327 2 : m_pDoc->CalcAll();
1328 :
1329 : double result;
1330 2 : m_pDoc->GetValue (1, 0, 0, result);
1331 2 : CPPUNIT_ASSERT_MESSAGE ("calculation failed", result == 1.0);
1332 :
1333 : // Test copy-ability of range names.
1334 2 : ScRangeName* pCopiedRanges = new ScRangeName(*pNewRanges);
1335 2 : m_pDoc->SetRangeName(pCopiedRanges);
1336 : // Make sure the index lookup still works.
1337 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
1338 : {
1339 8 : const ScRangeData* p = pCopiedRanges->findByIndex(aNames[i].nIndex);
1340 8 : CPPUNIT_ASSERT_MESSAGE("lookup of range name by index failed with the copied instance.", p);
1341 8 : rtl::OUString aName = p->GetName();
1342 8 : CPPUNIT_ASSERT_MESSAGE("wrong range name is retrieved with the copied instance.", aName.equalsAscii(aNames[i].pName));
1343 8 : }
1344 :
1345 2 : m_pDoc->SetRangeName(NULL); // Delete the names.
1346 2 : m_pDoc->DeleteTab(0);
1347 2 : }
1348 :
1349 2 : void Test::testCSV()
1350 : {
1351 2 : 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 2 : };
1370 30 : for (sal_uInt32 i = 0; i < SAL_N_ELEMENTS(aTests); i++) {
1371 28 : rtl::OUString aStr(aTests[i].pStr, strlen (aTests[i].pStr), RTL_TEXTENCODING_UTF8);
1372 28 : double nValue = 0.0;
1373 : bool bResult = ScStringUtil::parseSimpleNumber
1374 : (aStr, aTests[i].eSep == English ? '.' : ',',
1375 : aTests[i].eSep == English ? ',' : '.',
1376 28 : nValue);
1377 28 : CPPUNIT_ASSERT_MESSAGE ("CSV numeric detection failure", bResult == aTests[i].bResult);
1378 28 : CPPUNIT_ASSERT_MESSAGE ("CSV numeric value failure", nValue == aTests[i].nValue);
1379 28 : }
1380 2 : }
1381 :
1382 : template<typename Evaluator>
1383 8 : void checkMatrixElements(const ScMatrix& rMat)
1384 : {
1385 : SCSIZE nC, nR;
1386 8 : rMat.GetDimensions(nC, nR);
1387 : Evaluator aEval;
1388 64 : for (SCSIZE i = 0; i < nC; ++i)
1389 : {
1390 1016 : for (SCSIZE j = 0; j < nR; ++j)
1391 : {
1392 960 : aEval(i, j, rMat.Get(i, j));
1393 : }
1394 : }
1395 8 : }
1396 :
1397 : struct AllZeroMatrix
1398 : {
1399 80 : void operator() (SCSIZE /*nCol*/, SCSIZE /*nRow*/, const ScMatrixValue& rVal) const
1400 : {
1401 80 : CPPUNIT_ASSERT_MESSAGE("element is not of numeric type", rVal.nType == SC_MATVAL_VALUE);
1402 80 : CPPUNIT_ASSERT_MESSAGE("element value must be zero", rVal.fVal == 0.0);
1403 80 : }
1404 : };
1405 :
1406 : struct PartiallyFilledZeroMatrix
1407 : {
1408 80 : void operator() (SCSIZE nCol, SCSIZE nRow, const ScMatrixValue& rVal) const
1409 : {
1410 80 : CPPUNIT_ASSERT_MESSAGE("element is not of numeric type", rVal.nType == SC_MATVAL_VALUE);
1411 80 : if (1 <= nCol && nCol <= 2 && 2 <= nRow && nRow <= 8)
1412 : {
1413 28 : CPPUNIT_ASSERT_MESSAGE("element value must be 3.0", rVal.fVal == 3.0);
1414 : }
1415 : else
1416 : {
1417 52 : CPPUNIT_ASSERT_MESSAGE("element value must be zero", rVal.fVal == 0.0);
1418 : }
1419 80 : }
1420 : };
1421 :
1422 : struct AllEmptyMatrix
1423 : {
1424 400 : void operator() (SCSIZE /*nCol*/, SCSIZE /*nRow*/, const ScMatrixValue& rVal) const
1425 : {
1426 400 : CPPUNIT_ASSERT_MESSAGE("element is not of empty type", rVal.nType == SC_MATVAL_EMPTY);
1427 400 : CPPUNIT_ASSERT_MESSAGE("value of \"empty\" element is expected to be zero", rVal.fVal == 0.0);
1428 400 : }
1429 : };
1430 :
1431 : struct PartiallyFilledEmptyMatrix
1432 : {
1433 400 : void operator() (SCSIZE nCol, SCSIZE nRow, const ScMatrixValue& rVal) const
1434 : {
1435 400 : if (nCol == 1 && nRow == 1)
1436 : {
1437 2 : CPPUNIT_ASSERT_MESSAGE("element is not of boolean type", rVal.nType == SC_MATVAL_BOOLEAN);
1438 2 : CPPUNIT_ASSERT_MESSAGE("element value is not what is expected", rVal.fVal == 1.0);
1439 : }
1440 398 : else if (nCol == 4 && nRow == 5)
1441 : {
1442 2 : CPPUNIT_ASSERT_MESSAGE("element is not of value type", rVal.nType == SC_MATVAL_VALUE);
1443 2 : CPPUNIT_ASSERT_MESSAGE("element value is not what is expected", rVal.fVal == -12.5);
1444 : }
1445 396 : else if (nCol == 8 && nRow == 2)
1446 : {
1447 2 : CPPUNIT_ASSERT_MESSAGE("element is not of value type", rVal.nType == SC_MATVAL_STRING);
1448 2 : CPPUNIT_ASSERT_MESSAGE("element value is not what is expected", rVal.aStr == "Test");
1449 : }
1450 394 : else if (nCol == 8 && nRow == 11)
1451 : {
1452 2 : CPPUNIT_ASSERT_MESSAGE("element is not of empty path type", rVal.nType == SC_MATVAL_EMPTYPATH);
1453 2 : CPPUNIT_ASSERT_MESSAGE("value of \"empty\" element is expected to be zero", rVal.fVal == 0.0);
1454 : }
1455 : else
1456 : {
1457 392 : CPPUNIT_ASSERT_MESSAGE("element is not of empty type", rVal.nType == SC_MATVAL_EMPTY);
1458 392 : CPPUNIT_ASSERT_MESSAGE("value of \"empty\" element is expected to be zero", rVal.fVal == 0.0);
1459 : }
1460 400 : }
1461 : };
1462 :
1463 2 : void Test::testMatrix()
1464 : {
1465 2 : ScMatrixRef pMat;
1466 :
1467 : // First, test the zero matrix type.
1468 2 : pMat = new ScMatrix(0, 0, 0.0);
1469 : SCSIZE nC, nR;
1470 2 : pMat->GetDimensions(nC, nR);
1471 2 : CPPUNIT_ASSERT_MESSAGE("matrix is not empty", nC == 0 && nR == 0);
1472 2 : pMat->Resize(4, 10, 0.0);
1473 2 : pMat->GetDimensions(nC, nR);
1474 2 : CPPUNIT_ASSERT_MESSAGE("matrix size is not as expected", nC == 4 && nR == 10);
1475 4 : CPPUNIT_ASSERT_MESSAGE("both 'and' and 'or' should evaluate to false",
1476 2 : !pMat->And() && !pMat->Or());
1477 :
1478 : // Resizing into a larger matrix should fill the void space with zeros.
1479 2 : checkMatrixElements<AllZeroMatrix>(*pMat);
1480 :
1481 2 : pMat->FillDouble(3.0, 1, 2, 2, 8);
1482 2 : checkMatrixElements<PartiallyFilledZeroMatrix>(*pMat);
1483 2 : CPPUNIT_ASSERT_MESSAGE("matrix is expected to be numeric", pMat->IsNumeric());
1484 4 : CPPUNIT_ASSERT_MESSAGE("partially non-zero matrix should evaluate false on 'and' and true on 'or",
1485 2 : !pMat->And() && pMat->Or());
1486 2 : pMat->FillDouble(5.0, 0, 0, nC-1, nR-1);
1487 4 : CPPUNIT_ASSERT_MESSAGE("fully non-zero matrix should evaluate true both on 'and' and 'or",
1488 2 : pMat->And() && pMat->Or());
1489 :
1490 : // Test the AND and OR evaluations.
1491 2 : pMat = new ScMatrix(2, 2, 0.0);
1492 :
1493 : // Only some of the elements are non-zero.
1494 2 : pMat->PutBoolean(true, 0, 0);
1495 2 : pMat->PutDouble(1.0, 1, 1);
1496 2 : CPPUNIT_ASSERT_MESSAGE("incorrect OR result", pMat->Or());
1497 2 : CPPUNIT_ASSERT_MESSAGE("incorrect AND result", !pMat->And());
1498 :
1499 : // All of the elements are non-zero.
1500 2 : pMat->PutBoolean(true, 0, 1);
1501 2 : pMat->PutDouble(2.3, 1, 0);
1502 2 : CPPUNIT_ASSERT_MESSAGE("incorrect OR result", pMat->Or());
1503 2 : CPPUNIT_ASSERT_MESSAGE("incorrect AND result", pMat->And());
1504 :
1505 : // Now test the emtpy matrix type.
1506 2 : pMat = new ScMatrix(10, 20);
1507 2 : pMat->GetDimensions(nC, nR);
1508 2 : CPPUNIT_ASSERT_MESSAGE("matrix size is not as expected", nC == 10 && nR == 20);
1509 2 : checkMatrixElements<AllEmptyMatrix>(*pMat);
1510 :
1511 2 : pMat->PutBoolean(true, 1, 1);
1512 2 : pMat->PutDouble(-12.5, 4, 5);
1513 2 : rtl::OUString aStr("Test");
1514 2 : pMat->PutString(aStr, 8, 2);
1515 2 : pMat->PutEmptyPath(8, 11);
1516 2 : checkMatrixElements<PartiallyFilledEmptyMatrix>(*pMat);
1517 2 : }
1518 :
1519 2 : void Test::testEnterMixedMatrix()
1520 : {
1521 2 : m_pDoc->InsertTab(0, "foo");
1522 :
1523 : // Insert the source values in A1:B2.
1524 2 : m_pDoc->SetString(0, 0, 0, "A");
1525 2 : m_pDoc->SetString(1, 0, 0, "B");
1526 2 : double val = 1.0;
1527 2 : m_pDoc->SetValue(0, 1, 0, val);
1528 2 : val = 2.0;
1529 2 : m_pDoc->SetValue(1, 1, 0, val);
1530 :
1531 : // Create a matrix range in A4:B5 referencing A1:B2.
1532 2 : ScMarkData aMark;
1533 2 : aMark.SelectOneTable(0);
1534 2 : m_pDoc->InsertMatrixFormula(0, 3, 1, 4, aMark, "=A1:B2", NULL);
1535 :
1536 2 : CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(0,0,0), m_pDoc->GetString(0,3,0));
1537 2 : CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(1,0,0), m_pDoc->GetString(1,3,0));
1538 2 : CPPUNIT_ASSERT_EQUAL(m_pDoc->GetValue(0,1,0), m_pDoc->GetValue(0,4,0));
1539 2 : CPPUNIT_ASSERT_EQUAL(m_pDoc->GetValue(1,1,0), m_pDoc->GetValue(1,4,0));
1540 :
1541 2 : m_pDoc->DeleteTab(0);
1542 2 : }
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 10 : 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 44 : for (size_t i = 0; i < nFieldCount; ++i)
1563 34 : 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 62 : for (size_t i = 0; i < nDataCount; ++i)
1567 : {
1568 52 : SCROW nRow = static_cast<SCROW>(i) + 1;
1569 240 : for (size_t j = 0; j < nFieldCount; ++j)
1570 : {
1571 188 : SCCOL nCol = static_cast<SCCOL>(j);
1572 188 : pDoc->SetString(
1573 : nCol, nRow, 0, OUString(aData[i][j], strlen(aData[i][j]), RTL_TEXTENCODING_UTF8));
1574 : }
1575 : }
1576 :
1577 10 : SCROW nRow1 = 0, nRow2 = 0;
1578 10 : SCCOL nCol1 = 0, nCol2 = 0;
1579 10 : pDoc->GetDataArea(0, nCol1, nRow1, nCol2, nRow2, true, false);
1580 10 : CPPUNIT_ASSERT_MESSAGE("Data is expected to start from (col=0,row=0).", nCol1 == 0 && nRow1 == 0);
1581 10 : CPPUNIT_ASSERT_MESSAGE("Unexpected data range.",
1582 : nCol2 == static_cast<SCCOL>(nFieldCount - 1) && nRow2 == static_cast<SCROW>(nDataCount));
1583 :
1584 10 : ScRange aSrcRange(nCol1, nRow1, 0, nCol2, nRow2, 0);
1585 10 : printRange(pDoc, aSrcRange, "Data sheet content");
1586 10 : return aSrcRange;
1587 : }
1588 :
1589 : template<size_t _Size>
1590 48 : bool checkDPTableOutput(ScDocument* pDoc, const ScRange& aOutRange, const char* aOutputCheck[][_Size], const char* pCaption)
1591 : {
1592 48 : bool bResult = true;
1593 48 : const ScAddress& s = aOutRange.aStart;
1594 48 : const ScAddress& e = aOutRange.aEnd;
1595 48 : SheetPrinter printer(e.Row() - s.Row() + 1, e.Col() - s.Col() + 1);
1596 48 : SCROW nOutRowSize = e.Row() - s.Row() + 1;
1597 48 : SCCOL nOutColSize = e.Col() - s.Col() + 1;
1598 382 : for (SCROW nRow = 0; nRow < nOutRowSize; ++nRow)
1599 : {
1600 1394 : for (SCCOL nCol = 0; nCol < nOutColSize; ++nCol)
1601 : {
1602 1060 : OUString aVal;
1603 1060 : pDoc->GetString(nCol + s.Col(), nRow + s.Row(), s.Tab(), aVal);
1604 1060 : printer.set(nRow, nCol, aVal);
1605 1060 : const char* p = aOutputCheck[nRow][nCol];
1606 1060 : if (p)
1607 : {
1608 798 : OUString aCheckVal = OUString::createFromAscii(p);
1609 798 : bool bEqual = aCheckVal.equals(aVal);
1610 798 : if (!bEqual)
1611 : {
1612 0 : cout << "Expected: " << aCheckVal << " Actual: " << aVal << endl;
1613 0 : bResult = false;
1614 : }
1615 : }
1616 262 : else if (!aVal.isEmpty())
1617 : {
1618 0 : cout << "Empty cell expected" << endl;
1619 0 : bResult = false;
1620 : }
1621 : }
1622 : }
1623 48 : printer.print(pCaption);
1624 48 : return bResult;
1625 : }
1626 :
1627 28 : ScDPObject* createDPFromSourceDesc(
1628 : ScDocument* pDoc, const ScSheetSourceDesc& rDesc, DPFieldDef aFields[], size_t nFieldCount,
1629 : bool bFilterButton)
1630 : {
1631 28 : ScDPObject* pDPObj = new ScDPObject(pDoc);
1632 28 : pDPObj->SetSheetDesc(rDesc);
1633 28 : pDPObj->SetOutRange(ScAddress(0, 0, 1));
1634 :
1635 28 : ScDPSaveData aSaveData;
1636 : // Set data pilot table output options.
1637 28 : aSaveData.SetIgnoreEmptyRows(false);
1638 28 : aSaveData.SetRepeatIfEmpty(false);
1639 28 : aSaveData.SetColumnGrand(true);
1640 28 : aSaveData.SetRowGrand(true);
1641 28 : aSaveData.SetFilterButton(bFilterButton);
1642 28 : aSaveData.SetDrillDown(true);
1643 :
1644 : // Check the sanity of the source range.
1645 28 : const ScRange& rSrcRange = rDesc.GetSourceRange();
1646 28 : SCROW nRow1 = rSrcRange.aStart.Row();
1647 28 : SCROW nRow2 = rSrcRange.aEnd.Row();
1648 28 : CPPUNIT_ASSERT_MESSAGE("source range contains no data!", nRow2 - nRow1 > 1);
1649 :
1650 : // Set the dimension information.
1651 102 : for (size_t i = 0; i < nFieldCount; ++i)
1652 : {
1653 74 : OUString aDimName = rtl::OUString::createFromAscii(aFields[i].pName);
1654 74 : ScDPSaveDimension* pDim = aSaveData.GetNewDimensionByName(aDimName);
1655 74 : pDim->SetOrientation(static_cast<sal_uInt16>(aFields[i].eOrient));
1656 74 : pDim->SetUsedHierarchy(0);
1657 :
1658 74 : if (aFields[i].eOrient == sheet::DataPilotFieldOrientation_DATA)
1659 : {
1660 32 : sheet::GeneralFunction eFunc = sheet::GeneralFunction_SUM;
1661 32 : if (aFields[i].eFunc)
1662 18 : eFunc = static_cast<sheet::GeneralFunction>(aFields[i].eFunc);
1663 :
1664 32 : pDim->SetFunction(eFunc);
1665 32 : pDim->SetReferenceValue(NULL);
1666 : }
1667 : else
1668 : {
1669 42 : sheet::DataPilotFieldSortInfo aSortInfo;
1670 42 : aSortInfo.IsAscending = true;
1671 42 : aSortInfo.Mode = 2;
1672 42 : pDim->SetSortInfo(&aSortInfo);
1673 :
1674 42 : sheet::DataPilotFieldLayoutInfo aLayInfo;
1675 42 : aLayInfo.LayoutMode = 0;
1676 42 : aLayInfo.AddEmptyLines = false;
1677 42 : pDim->SetLayoutInfo(&aLayInfo);
1678 42 : sheet::DataPilotFieldAutoShowInfo aShowInfo;
1679 42 : aShowInfo.IsEnabled = false;
1680 42 : aShowInfo.ShowItemsMode = 0;
1681 42 : aShowInfo.ItemCount = 0;
1682 42 : pDim->SetAutoShowInfo(&aShowInfo);
1683 : }
1684 74 : }
1685 :
1686 : // Don't forget the data layout dimension.
1687 28 : ScDPSaveDimension* pDim = aSaveData.GetDataLayoutDimension();
1688 28 : pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
1689 28 : pDim->SetShowEmpty(true);
1690 :
1691 28 : pDPObj->SetSaveData(aSaveData);
1692 28 : pDPObj->SetAlive(true);
1693 28 : pDPObj->InvalidateData();
1694 :
1695 28 : return pDPObj;
1696 : }
1697 :
1698 26 : ScDPObject* createDPFromRange(
1699 : ScDocument* pDoc, const ScRange& rRange, DPFieldDef aFields[], size_t nFieldCount,
1700 : bool bFilterButton)
1701 : {
1702 26 : ScSheetSourceDesc aSheetDesc(pDoc);
1703 26 : aSheetDesc.SetSourceRange(rRange);
1704 26 : return createDPFromSourceDesc(pDoc, aSheetDesc, aFields, nFieldCount, bFilterButton);
1705 : }
1706 :
1707 44 : ScRange refresh(ScDPObject* pDPObj)
1708 : {
1709 44 : bool bOverFlow = false;
1710 44 : ScRange aOutRange = pDPObj->GetNewOutputRange(bOverFlow);
1711 44 : CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverFlow);
1712 :
1713 44 : pDPObj->Output(aOutRange.aStart);
1714 44 : aOutRange = pDPObj->GetOutRange();
1715 44 : return aOutRange;
1716 : }
1717 :
1718 8 : 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 8 : std::set<ScDPObject*> aRefs;
1723 8 : bool bSuccess = pDPs->ReloadGroupsInCache(pDPObj, aRefs);
1724 8 : CPPUNIT_ASSERT_MESSAGE("Failed to reload group data in cache.", bSuccess);
1725 8 : CPPUNIT_ASSERT_MESSAGE("There should be only one table linked to this cache.", aRefs.size() == 1);
1726 8 : pDPObj->ReloadGroupTableData();
1727 :
1728 8 : return refresh(pDPObj);
1729 : }
1730 :
1731 : }
1732 :
1733 2 : void Test::testPivotTable()
1734 : {
1735 2 : m_pDoc->InsertTab(0, OUString("Data"));
1736 2 : 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 2 : };
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 2 : };
1754 :
1755 2 : size_t nFieldCount = SAL_N_ELEMENTS(aFields);
1756 2 : size_t nDataCount = SAL_N_ELEMENTS(aData);
1757 :
1758 2 : ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
1759 2 : SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
1760 2 : SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
1761 :
1762 : ScDPObject* pDPObj = createDPFromRange(
1763 2 : m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
1764 :
1765 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1766 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
1767 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
1768 4 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
1769 2 : pDPs->GetCount() == 1);
1770 2 : pDPObj->SetName(pDPs->CreateNewName());
1771 :
1772 2 : bool bOverFlow = false;
1773 2 : ScRange aOutRange = pDPObj->GetNewOutputRange(bOverFlow);
1774 2 : CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverFlow);
1775 :
1776 2 : pDPObj->Output(aOutRange.aStart);
1777 2 : 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 2 : };
1791 :
1792 2 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
1793 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1794 : }
1795 2 : CPPUNIT_ASSERT_MESSAGE("There should be only one data cache.", pDPs->GetSheetCaches().size() == 1);
1796 :
1797 : // Update the cell values.
1798 2 : double aData2[] = { 100, 200, 300, 400, 500, 600 };
1799 14 : for (size_t i = 0; i < SAL_N_ELEMENTS(aData2); ++i)
1800 : {
1801 12 : SCROW nRow = i + 1;
1802 12 : m_pDoc->SetValue(2, nRow, 0, aData2[i]);
1803 : }
1804 :
1805 2 : 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 2 : ScDPObject* pDPObj2 = new ScDPObject(*pDPObj);
1811 2 : pDPs->InsertNewTable(pDPObj2);
1812 :
1813 2 : aOutRange = pDPObj2->GetOutRange();
1814 2 : pDPObj2->ClearTableData();
1815 2 : 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 2 : };
1829 :
1830 2 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (from old cache)");
1831 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1832 : }
1833 :
1834 2 : 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 2 : pDPs->FreeTable(pDPObj);
1839 :
1840 2 : 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 2 : CPPUNIT_ASSERT_MESSAGE("This datapilot should be based on sheet data.", pDPObj2->IsSheetData());
1844 2 : std::set<ScDPObject*> aRefs;
1845 2 : sal_uLong nErrId = pDPs->ReloadCache(pDPObj2, aRefs);
1846 2 : CPPUNIT_ASSERT_MESSAGE("Cache reload failed.", nErrId == 0);
1847 4 : CPPUNIT_ASSERT_MESSAGE("Reloading a cache shouldn't remove any cache.",
1848 2 : pDPs->GetSheetCaches().size() == 1);
1849 :
1850 2 : pDPObj2->ClearTableData();
1851 2 : 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 2 : };
1866 :
1867 2 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (refreshed)");
1868 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1869 : }
1870 :
1871 2 : CPPUNIT_ASSERT_MESSAGE("Cache should be here.", pDPs->GetSheetCaches().hasCache(aSrcRange));
1872 :
1873 : // Swap the two sheets.
1874 2 : m_pDoc->MoveTab(1, 0);
1875 4 : CPPUNIT_ASSERT_MESSAGE("Swapping the sheets shouldn't remove the cache.",
1876 2 : pDPs->GetSheetCaches().size() == 1);
1877 2 : CPPUNIT_ASSERT_MESSAGE("Cache should have moved.", !pDPs->GetSheetCaches().hasCache(aSrcRange));
1878 2 : aSrcRange.aStart.SetTab(1);
1879 2 : aSrcRange.aEnd.SetTab(1);
1880 2 : CPPUNIT_ASSERT_MESSAGE("Cache should be here.", pDPs->GetSheetCaches().hasCache(aSrcRange));
1881 :
1882 2 : pDPs->FreeTable(pDPObj2);
1883 4 : CPPUNIT_ASSERT_MESSAGE("There shouldn't be any data pilot table stored with the document.",
1884 2 : pDPs->GetCount() == 0);
1885 :
1886 4 : CPPUNIT_ASSERT_MESSAGE("There shouldn't be any more data cache.",
1887 2 : 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 2 : m_pDoc->DeleteTab(1);
1892 2 : m_pDoc->InsertTab(1, OUString("Table"));
1893 :
1894 : pDPObj = createDPFromRange(
1895 2 : m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
1896 2 : bSuccess = pDPs->InsertNewTable(pDPObj);
1897 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
1898 4 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
1899 2 : pDPs->GetCount() == 1);
1900 2 : pDPObj->SetName(pDPs->CreateNewName());
1901 4 : CPPUNIT_ASSERT_MESSAGE("Data cache shouldn't exist yet before creating the table output.",
1902 2 : 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 2 : aRefs.clear();
1907 2 : pDPs->ReloadCache(pDPObj, aRefs);
1908 4 : CPPUNIT_ASSERT_MESSAGE("It should return the same object as a reference.",
1909 2 : aRefs.size() == 1 && *aRefs.begin() == pDPObj);
1910 :
1911 2 : pDPs->FreeTable(pDPObj);
1912 :
1913 2 : m_pDoc->DeleteTab(1);
1914 2 : m_pDoc->DeleteTab(0);
1915 2 : }
1916 :
1917 2 : void Test::testPivotTableLabels()
1918 : {
1919 2 : m_pDoc->InsertTab(0, OUString("Data"));
1920 2 : 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 2 : };
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 2 : };
1935 :
1936 2 : size_t nFieldCount = SAL_N_ELEMENTS(aFields);
1937 2 : size_t nDataCount = SAL_N_ELEMENTS(aData);
1938 :
1939 2 : ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
1940 2 : SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
1941 2 : SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
1942 :
1943 : ScDPObject* pDPObj = createDPFromRange(
1944 2 : m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
1945 :
1946 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1947 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
1948 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
1949 4 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
1950 2 : pDPs->GetCount() == 1);
1951 2 : pDPObj->SetName(pDPs->CreateNewName());
1952 :
1953 2 : 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 2 : };
1962 :
1963 2 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
1964 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1965 : }
1966 :
1967 2 : pDPs->FreeTable(pDPObj);
1968 :
1969 2 : m_pDoc->DeleteTab(1);
1970 2 : m_pDoc->DeleteTab(0);
1971 2 : }
1972 :
1973 2 : void Test::testPivotTableDateLabels()
1974 : {
1975 2 : m_pDoc->InsertTab(0, OUString("Data"));
1976 2 : 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 2 : };
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 2 : };
1991 :
1992 2 : size_t nFieldCount = SAL_N_ELEMENTS(aFields);
1993 2 : size_t nDataCount = SAL_N_ELEMENTS(aData);
1994 :
1995 2 : ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
1996 2 : SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
1997 2 : SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
1998 :
1999 : ScDPObject* pDPObj = createDPFromRange(
2000 2 : m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
2001 :
2002 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2003 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2004 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
2005 4 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2006 2 : pDPs->GetCount() == 1);
2007 2 : pDPObj->SetName(pDPs->CreateNewName());
2008 :
2009 2 : 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 2 : };
2020 :
2021 2 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2022 2 : 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 2 : };
2029 :
2030 : // Make sure those cells that contain dates are numeric.
2031 2 : SCROW nRow = aOutRange.aStart.Row() + 1;
2032 2 : nCol1 = aOutRange.aStart.Col() + 1;
2033 2 : nCol2 = nCol1 + 2;
2034 8 : for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
2035 : {
2036 6 : OUString aVal = m_pDoc->GetString(nCol, nRow, 1);
2037 6 : CPPUNIT_ASSERT_MESSAGE("Cell value is not as expected.", aVal.equalsAscii(aChecks[nCol-nCol1]));
2038 12 : CPPUNIT_ASSERT_MESSAGE("This cell contains a date value and is supposed to be numeric.",
2039 6 : m_pDoc->HasValueData(nCol, nRow, 1));
2040 6 : }
2041 : }
2042 :
2043 2 : pDPs->FreeTable(pDPObj);
2044 :
2045 2 : m_pDoc->DeleteTab(1);
2046 2 : m_pDoc->DeleteTab(0);
2047 2 : }
2048 :
2049 2 : void Test::testPivotTableFilters()
2050 : {
2051 2 : m_pDoc->InsertTab(0, OUString("Data"));
2052 2 : 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 2 : };
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 2 : };
2074 :
2075 2 : size_t nFieldCount = SAL_N_ELEMENTS(aFields);
2076 2 : size_t nDataCount = SAL_N_ELEMENTS(aData);
2077 :
2078 2 : ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
2079 2 : SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
2080 2 : SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
2081 :
2082 : ScDPObject* pDPObj = createDPFromRange(
2083 2 : m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, true);
2084 :
2085 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2086 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2087 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
2088 4 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2089 2 : pDPs->GetCount() == 1);
2090 2 : pDPObj->SetName(pDPs->CreateNewName());
2091 :
2092 2 : 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 2 : };
2103 :
2104 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (unfiltered)");
2105 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2106 : }
2107 :
2108 2 : AutoCalcSwitch aACSwitch(m_pDoc, true); // turn on auto calculation.
2109 :
2110 2 : ScAddress aFormulaAddr = aOutRange.aEnd;
2111 2 : aFormulaAddr.IncRow(2);
2112 4 : m_pDoc->SetString(aFormulaAddr.Col(), aFormulaAddr.Row(), aFormulaAddr.Tab(),
2113 6 : rtl::OUString("=B6"));
2114 2 : double fTest = m_pDoc->GetValue(aFormulaAddr);
2115 2 : 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 2 : pDPObj->BuildAllDimensionMembers();
2119 2 : ScDPSaveData aSaveData(*pDPObj->GetSaveData());
2120 : ScDPSaveDimension* pPageDim = aSaveData.GetDimensionByName(
2121 2 : OUString("Group2"));
2122 2 : CPPUNIT_ASSERT_MESSAGE("Dimension not found", pPageDim);
2123 2 : OUString aPage("A");
2124 2 : pPageDim->SetCurrentPage(&aPage);
2125 2 : pDPObj->SetSaveData(aSaveData);
2126 2 : 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 2 : };
2137 :
2138 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by page)");
2139 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2140 : }
2141 :
2142 2 : fTest = m_pDoc->GetValue(aFormulaAddr);
2143 2 : CPPUNIT_ASSERT_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", fTest == 40.0);
2144 :
2145 : // Set query filter.
2146 2 : ScSheetSourceDesc aDesc(*pDPObj->GetSheetDesc());
2147 2 : ScQueryParam aQueryParam(aDesc.GetQueryParam());
2148 2 : CPPUNIT_ASSERT_MESSAGE("There should be at least one query entry.", aQueryParam.GetEntryCount() > 0);
2149 2 : ScQueryEntry& rEntry = aQueryParam.GetEntry(0);
2150 2 : rEntry.bDoQuery = true;
2151 2 : rEntry.nField = 1; // Group1
2152 2 : rEntry.GetQueryItem().mfVal = 1;
2153 2 : aDesc.SetQueryParam(aQueryParam);
2154 2 : pDPObj->SetSheetDesc(aDesc);
2155 2 : 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 2 : };
2166 :
2167 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by query)");
2168 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2169 : }
2170 :
2171 2 : fTest = m_pDoc->GetValue(aFormulaAddr);
2172 2 : 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 2 : pPageDim->SetCurrentPage(NULL); // Remove the page.
2177 2 : pDPObj->SetSaveData(aSaveData);
2178 2 : 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 2 : };
2189 :
2190 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by page)");
2191 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2192 : }
2193 :
2194 :
2195 2 : pDPs->FreeTable(pDPObj);
2196 4 : CPPUNIT_ASSERT_MESSAGE("There shouldn't be any data pilot table stored with the document.",
2197 2 : pDPs->GetCount() == 0);
2198 :
2199 2 : m_pDoc->DeleteTab(1);
2200 2 : m_pDoc->DeleteTab(0);
2201 2 : }
2202 :
2203 2 : void Test::testPivotTableNamedSource()
2204 : {
2205 2 : m_pDoc->InsertTab(0, OUString("Data"));
2206 2 : 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 2 : };
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 2 : };
2224 :
2225 2 : size_t nFieldCount = SAL_N_ELEMENTS(aFields);
2226 2 : size_t nDataCount = SAL_N_ELEMENTS(aData);
2227 :
2228 : // Insert the raw data.
2229 2 : ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
2230 2 : rtl::OUString aRangeStr;
2231 2 : aSrcRange.Format(aRangeStr, SCR_ABS_3D, m_pDoc);
2232 :
2233 : // Name this range.
2234 2 : rtl::OUString aRangeName("MyData");
2235 2 : ScRangeName* pNames = m_pDoc->GetRangeName();
2236 2 : CPPUNIT_ASSERT_MESSAGE("Failed to get global range name container.", pNames);
2237 : ScRangeData* pName = new ScRangeData(
2238 2 : m_pDoc, aRangeName, aRangeStr);
2239 2 : bool bSuccess = pNames->insert(pName);
2240 2 : CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bSuccess);
2241 :
2242 2 : ScSheetSourceDesc aSheetDesc(m_pDoc);
2243 2 : aSheetDesc.SetRangeName(aRangeName);
2244 2 : ScDPObject* pDPObj = createDPFromSourceDesc(m_pDoc, aSheetDesc, aFields, nFieldCount, false);
2245 2 : CPPUNIT_ASSERT_MESSAGE("Failed to create a new pivot table object.", pDPObj);
2246 :
2247 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2248 2 : bSuccess = pDPs->InsertNewTable(pDPObj);
2249 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2250 4 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2251 2 : pDPs->GetCount() == 1);
2252 2 : pDPObj->SetName(pDPs->CreateNewName());
2253 :
2254 2 : 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 2 : };
2268 :
2269 2 : bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2270 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2271 : }
2272 :
2273 4 : CPPUNIT_ASSERT_MESSAGE("There should be one named range data cache.",
2274 2 : 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 2 : m_pDoc->MoveTab(1, 0);
2278 2 : rtl::OUString aTabName;
2279 2 : m_pDoc->GetName(0, aTabName);
2280 2 : CPPUNIT_ASSERT_MESSAGE( "Wrong sheet name.", aTabName == "Table" );
2281 4 : CPPUNIT_ASSERT_MESSAGE("Pivot table output is on the wrong sheet!",
2282 2 : pDPObj->GetOutRange().aStart.Tab() == 0);
2283 :
2284 4 : CPPUNIT_ASSERT_MESSAGE("Moving the pivot table to another sheet shouldn't have changed the cache state.",
2285 2 : pDPs->GetNameCaches().size() == 1 && pDPs->GetSheetCaches().size() == 0);
2286 :
2287 2 : const ScSheetSourceDesc* pDesc = pDPObj->GetSheetDesc();
2288 2 : CPPUNIT_ASSERT_MESSAGE("Sheet source description doesn't exist.", pDesc);
2289 4 : CPPUNIT_ASSERT_MESSAGE("Named source range has been altered unexpectedly!",
2290 2 : pDesc->GetRangeName().equals(aRangeName));
2291 :
2292 2 : CPPUNIT_ASSERT_MESSAGE("Cache should exist.", pDPs->GetNameCaches().hasCache(aRangeName));
2293 :
2294 2 : pDPs->FreeTable(pDPObj);
2295 2 : CPPUNIT_ASSERT_MESSAGE("There should be no more tables.", pDPs->GetCount() == 0);
2296 4 : CPPUNIT_ASSERT_MESSAGE("There shouldn't be any more cache stored.",
2297 2 : pDPs->GetNameCaches().size() == 0);
2298 :
2299 2 : pNames->clear();
2300 2 : m_pDoc->DeleteTab(1);
2301 2 : m_pDoc->DeleteTab(0);
2302 2 : }
2303 :
2304 2 : void Test::testPivotTableCache()
2305 : {
2306 2 : 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 2 : };
2318 :
2319 2 : ScAddress aPos(1,1,0);
2320 2 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2321 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2322 :
2323 2 : ScDPCache aCache(m_pDoc);
2324 2 : aCache.InitFromDoc(m_pDoc, aDataRange);
2325 2 : long nDimCount = aCache.GetColumnCount();
2326 2 : CPPUNIT_ASSERT_MESSAGE("wrong dimension count.", nDimCount == 3);
2327 2 : rtl::OUString aDimName = aCache.GetDimensionName(0);
2328 2 : CPPUNIT_ASSERT_MESSAGE("wrong dimension name", aDimName.equalsAscii("F1"));
2329 2 : aDimName = aCache.GetDimensionName(1);
2330 2 : CPPUNIT_ASSERT_MESSAGE("wrong dimension name", aDimName.equalsAscii("F2"));
2331 2 : aDimName = aCache.GetDimensionName(2);
2332 2 : 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 2 : long nMemCount = aCache.GetDimMemberCount(0);
2341 2 : CPPUNIT_ASSERT_MESSAGE("wrong dimension member count", nMemCount == 6);
2342 2 : const ScDPItemData* pItem = aCache.GetItemDataById(0, 0);
2343 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2344 : pItem->GetType() == ScDPItemData::Value &&
2345 2 : pItem->GetValue() == 12);
2346 2 : pItem = aCache.GetItemDataById(0, 1);
2347 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2348 : pItem->GetType() == ScDPItemData::String &&
2349 2 : pItem->GetString().equalsAscii("A"));
2350 2 : pItem = aCache.GetItemDataById(0, 2);
2351 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2352 : pItem->GetType() == ScDPItemData::String &&
2353 2 : pItem->GetString().equalsAscii("F"));
2354 2 : pItem = aCache.GetItemDataById(0, 3);
2355 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2356 : pItem->GetType() == ScDPItemData::String &&
2357 2 : pItem->GetString().equalsAscii("R"));
2358 2 : pItem = aCache.GetItemDataById(0, 4);
2359 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2360 : pItem->GetType() == ScDPItemData::String &&
2361 2 : pItem->GetString().equalsAscii("Y"));
2362 2 : pItem = aCache.GetItemDataById(0, 5);
2363 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2364 : pItem->GetType() == ScDPItemData::String &&
2365 2 : pItem->GetString().equalsAscii("Z"));
2366 2 : pItem = aCache.GetItemDataById(0, 6);
2367 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
2368 :
2369 : // Dimension 1 - duplicate values in source.
2370 2 : nMemCount = aCache.GetDimMemberCount(1);
2371 2 : CPPUNIT_ASSERT_MESSAGE("wrong dimension member count", nMemCount == 3);
2372 2 : pItem = aCache.GetItemDataById(1, 0);
2373 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2374 : pItem->GetType() == ScDPItemData::String &&
2375 2 : pItem->GetString().equalsAscii("A"));
2376 2 : pItem = aCache.GetItemDataById(1, 1);
2377 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2378 : pItem->GetType() == ScDPItemData::String &&
2379 2 : pItem->GetString().equalsAscii("B"));
2380 2 : pItem = aCache.GetItemDataById(1, 2);
2381 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2382 : pItem->GetType() == ScDPItemData::String &&
2383 2 : pItem->GetString().equalsAscii("C"));
2384 2 : pItem = aCache.GetItemDataById(1, 3);
2385 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
2386 :
2387 : // Dimension 2 - values only.
2388 2 : nMemCount = aCache.GetDimMemberCount(2);
2389 2 : CPPUNIT_ASSERT_MESSAGE("wrong dimension member count", nMemCount == 6);
2390 2 : pItem = aCache.GetItemDataById(2, 0);
2391 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2392 : pItem->GetType() == ScDPItemData::Value &&
2393 2 : pItem->GetValue() == 8);
2394 2 : pItem = aCache.GetItemDataById(2, 1);
2395 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2396 : pItem->GetType() == ScDPItemData::Value &&
2397 2 : pItem->GetValue() == 12);
2398 2 : pItem = aCache.GetItemDataById(2, 2);
2399 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2400 : pItem->GetType() == ScDPItemData::Value &&
2401 2 : pItem->GetValue() == 15);
2402 2 : pItem = aCache.GetItemDataById(2, 3);
2403 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2404 : pItem->GetType() == ScDPItemData::Value &&
2405 2 : pItem->GetValue() == 20);
2406 2 : pItem = aCache.GetItemDataById(2, 4);
2407 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2408 : pItem->GetType() == ScDPItemData::Value &&
2409 2 : pItem->GetValue() == 30);
2410 2 : pItem = aCache.GetItemDataById(2, 5);
2411 4 : CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2412 : pItem->GetType() == ScDPItemData::Value &&
2413 2 : pItem->GetValue() == 45);
2414 2 : pItem = aCache.GetItemDataById(2, 6);
2415 2 : CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
2416 :
2417 : {
2418 : // Check the integrity of the source data.
2419 2 : ScDPItemData aTest;
2420 : long nDim;
2421 :
2422 : {
2423 : // Dimension 0: Z, R, A, F, Y, 12
2424 2 : nDim = 0;
2425 2 : const char* aChecks[] = { "Z", "R", "A", "F", "Y" };
2426 12 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2427 : {
2428 10 : pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
2429 10 : aTest.SetString(rtl::OUString::createFromAscii(aChecks[i]));
2430 10 : CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2431 : }
2432 :
2433 2 : pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, 5, false));
2434 2 : aTest.SetValue(12);
2435 2 : CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2436 : }
2437 :
2438 : {
2439 : // Dimension 1: A, A, B, B, C, C
2440 2 : nDim = 1;
2441 2 : const char* aChecks[] = { "A", "A", "B", "B", "C", "C" };
2442 14 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2443 : {
2444 12 : pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
2445 12 : aTest.SetString(rtl::OUString::createFromAscii(aChecks[i]));
2446 12 : CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2447 : }
2448 : }
2449 :
2450 : {
2451 : // Dimension 2: 30, 20, 45, 12, 8, 15
2452 2 : nDim = 2;
2453 2 : double aChecks[] = { 30, 20, 45, 12, 8, 15 };
2454 14 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2455 : {
2456 12 : pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
2457 12 : aTest.SetValue(aChecks[i]);
2458 12 : CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2459 : }
2460 2 : }
2461 : }
2462 :
2463 : // Now, on to testing the filtered cache.
2464 :
2465 : {
2466 : // Non-filtered cache - everything should be visible.
2467 2 : ScDPFilteredCache aFilteredCache(aCache);
2468 2 : aFilteredCache.fillTable();
2469 :
2470 2 : sal_Int32 nRows = aFilteredCache.getRowSize();
2471 2 : CPPUNIT_ASSERT_MESSAGE("Wrong dimension.", nRows == 6 && aFilteredCache.getColSize() == 3);
2472 :
2473 14 : for (sal_Int32 i = 0; i < nRows; ++i)
2474 : {
2475 12 : 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 2 : }
2482 : }
2483 :
2484 : // TODO : Add test for filtered caches.
2485 :
2486 2 : m_pDoc->DeleteTab(0);
2487 2 : }
2488 :
2489 2 : void Test::testPivotTableDuplicateDataFields()
2490 : {
2491 2 : m_pDoc->InsertTab(0, OUString("Data"));
2492 2 : 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 2 : };
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 2 : };
2515 :
2516 2 : ScAddress aPos(2,2,0);
2517 2 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2518 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2519 :
2520 : ScDPObject* pDPObj = createDPFromRange(
2521 2 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2522 :
2523 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2524 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2525 :
2526 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2527 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2528 2 : pDPs->GetCount(), static_cast<size_t>(1));
2529 2 : pDPObj->SetName(pDPs->CreateNewName());
2530 :
2531 2 : 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 2 : };
2543 :
2544 2 : bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2545 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2546 : }
2547 :
2548 : // Move the data layout dimension from row to column.
2549 2 : ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2550 2 : CPPUNIT_ASSERT_MESSAGE("No save data!?", pSaveData);
2551 2 : ScDPSaveDimension* pDataLayout = pSaveData->GetDataLayoutDimension();
2552 2 : CPPUNIT_ASSERT_MESSAGE("No data layout dimension.", pDataLayout);
2553 2 : pDataLayout->SetOrientation(sheet::DataPilotFieldOrientation_COLUMN);
2554 2 : pDPObj->SetSaveData(*pSaveData);
2555 :
2556 : // Refresh the table output.
2557 2 : 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 2 : };
2567 :
2568 2 : bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2569 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2570 : }
2571 :
2572 2 : ScPivotParam aParam;
2573 2 : pDPObj->FillLabelData(aParam);
2574 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be exactly 4 labels (2 original, 1 data layout, and 1 duplicate dimensions).",
2575 2 : aParam.maLabelArray.size(), static_cast<size_t>(4));
2576 :
2577 2 : pDPs->FreeTable(pDPObj);
2578 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2579 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2580 2 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
2581 :
2582 2 : m_pDoc->DeleteTab(1);
2583 2 : m_pDoc->DeleteTab(0);
2584 2 : }
2585 :
2586 2 : void Test::testPivotTableNormalGrouping()
2587 : {
2588 2 : m_pDoc->InsertTab(0, OUString("Data"));
2589 2 : 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 2 : };
2602 :
2603 : // Dimension definition
2604 : DPFieldDef aFields[] = {
2605 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
2606 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
2607 2 : };
2608 :
2609 2 : ScAddress aPos(1,1,0);
2610 2 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2611 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2612 :
2613 : ScDPObject* pDPObj = createDPFromRange(
2614 2 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2615 :
2616 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2617 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2618 :
2619 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2620 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2621 2 : pDPs->GetCount(), static_cast<size_t>(1));
2622 2 : pDPObj->SetName(pDPs->CreateNewName());
2623 :
2624 2 : 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 2 : };
2638 :
2639 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Initial output without grouping");
2640 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2641 : }
2642 :
2643 2 : ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2644 2 : CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
2645 2 : ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
2646 2 : CPPUNIT_ASSERT_MESSAGE("Failed to create dimension data.", pDimData);
2647 :
2648 2 : rtl::OUString aGroupPrefix("Group");
2649 2 : rtl::OUString aBaseDimName("Name");
2650 : rtl::OUString aGroupDimName =
2651 2 : pDimData->CreateGroupDimName(aBaseDimName, *pDPObj, false, NULL);
2652 :
2653 : {
2654 : // Group A, B and C together.
2655 2 : ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
2656 2 : rtl::OUString aGroupName = aGroupDim.CreateGroupName(aGroupPrefix);
2657 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected group name", aGroupName.equalsAscii("Group1"));
2658 :
2659 2 : ScDPSaveGroupItem aGroup(aGroupName);
2660 2 : aGroup.AddElement(rtl::OUString("A"));
2661 2 : aGroup.AddElement(rtl::OUString("B"));
2662 2 : aGroup.AddElement(rtl::OUString("C"));
2663 2 : aGroupDim.AddGroupItem(aGroup);
2664 2 : pDimData->AddGroupDimension(aGroupDim);
2665 :
2666 2 : ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
2667 2 : pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
2668 2 : pSaveData->SetPosition(pDim, 0); // Set it before the base dimension.
2669 : }
2670 :
2671 2 : pDPObj->SetSaveData(*pSaveData);
2672 2 : 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 2 : };
2686 :
2687 2 : bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "A, B, C grouped by Group1.");
2688 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2689 : }
2690 :
2691 2 : pSaveData = pDPObj->GetSaveData();
2692 2 : pDimData = pSaveData->GetDimensionData();
2693 :
2694 : {
2695 : // Group D, E, F together.
2696 2 : ScDPSaveGroupDimension* pGroupDim = pDimData->GetGroupDimAccForBase(aBaseDimName);
2697 2 : CPPUNIT_ASSERT_MESSAGE("There should be an existing group dimension.", pGroupDim);
2698 2 : rtl::OUString aGroupName = pGroupDim->CreateGroupName(aGroupPrefix);
2699 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected group name", aGroupName.equalsAscii("Group2"));
2700 :
2701 2 : ScDPSaveGroupItem aGroup(aGroupName);
2702 2 : aGroup.AddElement(rtl::OUString("D"));
2703 2 : aGroup.AddElement(rtl::OUString("E"));
2704 2 : aGroup.AddElement(rtl::OUString("F"));
2705 2 : pGroupDim->AddGroupItem(aGroup);
2706 : }
2707 :
2708 2 : pDPObj->SetSaveData(*pSaveData);
2709 2 : 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 2 : };
2723 :
2724 2 : bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "D, E, F grouped by Group2.");
2725 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2726 : }
2727 :
2728 2 : pDPs->FreeTable(pDPObj);
2729 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2730 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2731 2 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
2732 :
2733 2 : m_pDoc->DeleteTab(1);
2734 2 : m_pDoc->DeleteTab(0);
2735 2 : }
2736 :
2737 2 : void Test::testPivotTableNumberGrouping()
2738 : {
2739 2 : m_pDoc->InsertTab(0, OUString("Data"));
2740 2 : 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 2 : };
2764 :
2765 : // Dimension definition
2766 : DPFieldDef aFields[] = {
2767 : { "Order", sheet::DataPilotFieldOrientation_ROW, 0 },
2768 : { "Score", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
2769 2 : };
2770 :
2771 2 : ScAddress aPos(1,1,0);
2772 2 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2773 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2774 :
2775 : ScDPObject* pDPObj = createDPFromRange(
2776 2 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2777 :
2778 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2779 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2780 :
2781 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2782 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2783 2 : pDPs->GetCount(), static_cast<size_t>(1));
2784 2 : pDPObj->SetName(pDPs->CreateNewName());
2785 :
2786 2 : ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2787 2 : CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
2788 2 : ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
2789 2 : CPPUNIT_ASSERT_MESSAGE("No dimension data !?", pDimData);
2790 :
2791 : {
2792 2 : ScDPNumGroupInfo aInfo;
2793 2 : aInfo.mbEnable = true;
2794 2 : aInfo.mbAutoStart = false;
2795 2 : aInfo.mbAutoEnd = false;
2796 2 : aInfo.mbDateValues = false;
2797 2 : aInfo.mbIntegerOnly = true;
2798 2 : aInfo.mfStart = 30;
2799 2 : aInfo.mfEnd = 60;
2800 2 : aInfo.mfStep = 10;
2801 2 : ScDPSaveNumGroupDimension aGroup(rtl::OUString("Order"), aInfo);
2802 2 : pDimData->AddNumGroupDimension(aGroup);
2803 : }
2804 :
2805 2 : pDPObj->SetSaveData(*pSaveData);
2806 2 : 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 2 : };
2818 :
2819 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Order grouped by numbers");
2820 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2821 : }
2822 :
2823 2 : pDPs->FreeTable(pDPObj);
2824 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2825 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2826 2 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
2827 :
2828 2 : m_pDoc->DeleteTab(1);
2829 2 : m_pDoc->DeleteTab(0);
2830 2 : }
2831 :
2832 2 : void Test::testPivotTableDateGrouping()
2833 : {
2834 2 : m_pDoc->InsertTab(0, OUString("Data"));
2835 2 : 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 2 : };
2849 :
2850 : // Dimension definition
2851 : DPFieldDef aFields[] = {
2852 : { "Date", sheet::DataPilotFieldOrientation_ROW, 0 },
2853 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
2854 2 : };
2855 :
2856 2 : ScAddress aPos(1,1,0);
2857 2 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2858 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2859 :
2860 : ScDPObject* pDPObj = createDPFromRange(
2861 2 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2862 :
2863 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2864 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
2865 :
2866 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2867 4 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2868 2 : pDPs->GetCount() == 1);
2869 2 : pDPObj->SetName(pDPs->CreateNewName());
2870 :
2871 2 : ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2872 2 : CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
2873 2 : ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
2874 2 : CPPUNIT_ASSERT_MESSAGE("No dimension data !?", pDimData);
2875 :
2876 2 : rtl::OUString aBaseDimName("Date");
2877 :
2878 2 : ScDPNumGroupInfo aInfo;
2879 2 : aInfo.mbEnable = true;
2880 2 : aInfo.mbAutoStart = true;
2881 2 : 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 2 : ScDPSaveNumGroupDimension aGroup(aBaseDimName, aInfo, sheet::DataPilotFieldGroupBy::MONTHS);
2887 2 : pDimData->AddNumGroupDimension(aGroup);
2888 : }
2889 :
2890 : {
2891 : // Add quarter dimension. This will be an additional dimension.
2892 : rtl::OUString aGroupDimName =
2893 : pDimData->CreateDateGroupDimName(
2894 2 : sheet::DataPilotFieldGroupBy::QUARTERS, *pDPObj, true, NULL);
2895 2 : ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
2896 2 : aGroupDim.SetDateInfo(aInfo, sheet::DataPilotFieldGroupBy::QUARTERS);
2897 2 : pDimData->AddGroupDimension(aGroupDim);
2898 :
2899 : // Set orientation.
2900 2 : ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
2901 2 : pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
2902 2 : 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 2 : sheet::DataPilotFieldGroupBy::YEARS, *pDPObj, true, NULL);
2910 2 : ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
2911 2 : aGroupDim.SetDateInfo(aInfo, sheet::DataPilotFieldGroupBy::YEARS);
2912 2 : pDimData->AddGroupDimension(aGroupDim);
2913 :
2914 : // Set orientation.
2915 2 : ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
2916 2 : pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
2917 2 : pSaveData->SetPosition(pDim, 0); // set it to the left end.
2918 : }
2919 :
2920 2 : pDPObj->SetSaveData(*pSaveData);
2921 2 : 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 2 : };
2935 :
2936 2 : bSuccess = checkDPTableOutput<4>(m_pDoc, aOutRange, aOutputCheck, "Years, quarters and months date groups.");
2937 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2938 : }
2939 :
2940 : {
2941 : // Let's hide year 2012.
2942 2 : pSaveData = pDPObj->GetSaveData();
2943 2 : ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(rtl::OUString("Years"));
2944 2 : CPPUNIT_ASSERT_MESSAGE("Years dimension should exist.", pDim);
2945 2 : ScDPSaveMember* pMem = pDim->GetMemberByName(rtl::OUString("2012"));
2946 2 : CPPUNIT_ASSERT_MESSAGE("Member should exist.", pMem);
2947 2 : pMem->SetIsVisible(false);
2948 : }
2949 2 : pDPObj->SetSaveData(*pSaveData);
2950 2 : pDPObj->ReloadGroupTableData();
2951 2 : pDPObj->InvalidateData();
2952 :
2953 2 : 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 2 : };
2963 :
2964 2 : bSuccess = checkDPTableOutput<4>(m_pDoc, aOutRange, aOutputCheck, "Year 2012 data now hidden");
2965 2 : 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 2 : pSaveData = pDPObj->GetSaveData();
2972 2 : pSaveData->RemoveAllGroupDimensions(aBaseDimName);
2973 2 : pDPObj->SetSaveData(*pSaveData);
2974 2 : pDPObj->ReloadGroupTableData();
2975 2 : pDPObj->InvalidateData();
2976 :
2977 2 : 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 2 : };
2992 :
2993 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Remove all date grouping.");
2994 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2995 : }
2996 :
2997 2 : pDPs->FreeTable(pDPObj);
2998 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2999 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3000 2 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3001 :
3002 2 : m_pDoc->DeleteTab(1);
3003 2 : m_pDoc->DeleteTab(0);
3004 2 : }
3005 :
3006 2 : void Test::testPivotTableEmptyRows()
3007 : {
3008 2 : m_pDoc->InsertTab(0, OUString("Data"));
3009 2 : 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 2 : };
3019 :
3020 : // Dimension definition
3021 : DPFieldDef aFields[] = {
3022 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3023 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3024 2 : };
3025 :
3026 2 : ScAddress aPos(1,1,0);
3027 2 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
3028 2 : 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 2 : aDataRange.aEnd.IncRow(2);
3032 :
3033 : ScDPObject* pDPObj = createDPFromRange(
3034 2 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3035 :
3036 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3037 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
3038 :
3039 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3040 4 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3041 2 : pDPs->GetCount() == 1);
3042 2 : pDPObj->SetName(pDPs->CreateNewName());
3043 :
3044 2 : 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 2 : };
3057 :
3058 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Include empty rows");
3059 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3060 : }
3061 :
3062 : // This time, ignore empty rows.
3063 2 : ScDPSaveData* pSaveData = pDPObj->GetSaveData();
3064 2 : CPPUNIT_ASSERT_MESSAGE("Save data doesn't exist.", pSaveData);
3065 2 : pSaveData->SetIgnoreEmptyRows(true);
3066 2 : pDPObj->ClearTableData();
3067 2 : 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 2 : };
3079 :
3080 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Ignore empty rows");
3081 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3082 : }
3083 :
3084 : // Modify the source to remove member 'A', then refresh the table.
3085 2 : m_pDoc->SetString(1, 2, 0, "B");
3086 :
3087 2 : std::set<ScDPObject*> aRefs;
3088 2 : sal_uLong nErr = pDPs->ReloadCache(pDPObj, aRefs);
3089 2 : CPPUNIT_ASSERT_MESSAGE("Failed to reload cache.", !nErr);
3090 4 : CPPUNIT_ASSERT_MESSAGE("There should only be one pivot table linked to this cache.",
3091 2 : aRefs.size() == 1 && *aRefs.begin() == pDPObj);
3092 :
3093 2 : pDPObj->ClearTableData();
3094 2 : 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 2 : };
3105 :
3106 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Ignore empty rows");
3107 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3108 : }
3109 :
3110 2 : pDPs->FreeTable(pDPObj);
3111 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3112 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3113 2 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3114 :
3115 2 : m_pDoc->DeleteTab(1);
3116 2 : m_pDoc->DeleteTab(0);
3117 2 : }
3118 :
3119 2 : void Test::testPivotTableTextNumber()
3120 : {
3121 2 : m_pDoc->InsertTab(0, OUString("Data"));
3122 2 : 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 2 : };
3132 :
3133 : // Dimension definition
3134 : DPFieldDef aFields[] = {
3135 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3136 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3137 2 : };
3138 :
3139 : // Insert raw data such that the first column values are entered as text.
3140 2 : size_t nRowCount = SAL_N_ELEMENTS(aData);
3141 12 : for (size_t nRow = 0; nRow < nRowCount; ++nRow)
3142 : {
3143 10 : ScSetStringParam aParam;
3144 10 : aParam.mbDetectNumberFormat = false;
3145 10 : aParam.meSetTextNumFormat = ScSetStringParam::Always;
3146 10 : m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aData[nRow][0]), &aParam);
3147 10 : aParam.meSetTextNumFormat = ScSetStringParam::Never;
3148 10 : m_pDoc->SetString(1, nRow, 0, OUString::createFromAscii(aData[nRow][1]), &aParam);
3149 :
3150 10 : if (nRow == 0)
3151 : // Don't check the header row.
3152 2 : continue;
3153 :
3154 : // Check the data rows.
3155 8 : CPPUNIT_ASSERT_MESSAGE("This cell is supposed to be text.", m_pDoc->HasStringData(0, nRow, 0));
3156 8 : CPPUNIT_ASSERT_MESSAGE("This cell is supposed to be numeric.", m_pDoc->HasValueData(1, nRow, 0));
3157 : }
3158 :
3159 2 : ScRange aDataRange(0, 0, 0, 1, 4, 0);
3160 :
3161 : ScDPObject* pDPObj = createDPFromRange(
3162 2 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3163 :
3164 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3165 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
3166 :
3167 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3168 4 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3169 2 : pDPs->GetCount() == 1);
3170 2 : pDPObj->SetName(pDPs->CreateNewName());
3171 :
3172 2 : 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 2 : };
3184 :
3185 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Text number field members");
3186 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3187 : }
3188 :
3189 2 : pDPs->FreeTable(pDPObj);
3190 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3191 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3192 2 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3193 :
3194 2 : m_pDoc->DeleteTab(1);
3195 2 : m_pDoc->DeleteTab(0);
3196 2 : }
3197 :
3198 2 : void Test::testPivotTableCaseInsensitiveStrings()
3199 : {
3200 2 : m_pDoc->InsertTab(0, OUString("Data"));
3201 2 : 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 2 : };
3209 :
3210 : // Dimension definition
3211 : DPFieldDef aFields[] = {
3212 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3213 : { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3214 2 : };
3215 :
3216 2 : ScAddress aPos(1,1,0);
3217 2 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
3218 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
3219 :
3220 : ScDPObject* pDPObj = createDPFromRange(
3221 2 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3222 :
3223 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3224 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
3225 :
3226 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3227 4 : CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3228 2 : pDPs->GetCount() == 1);
3229 2 : pDPObj->SetName(pDPs->CreateNewName());
3230 :
3231 2 : 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 2 : };
3240 :
3241 2 : bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Case insensitive strings");
3242 2 : CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3243 : }
3244 :
3245 2 : pDPs->FreeTable(pDPObj);
3246 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3247 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3248 2 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3249 :
3250 2 : m_pDoc->DeleteTab(1);
3251 2 : m_pDoc->DeleteTab(0);
3252 2 : }
3253 :
3254 2 : void Test::testPivotTableNumStability()
3255 : {
3256 2 : 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 2 : };
3285 :
3286 : // Dimension definition
3287 : DPFieldDef aFields[] = {
3288 : { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3289 : { "Total", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3290 2 : };
3291 :
3292 2 : m_pDoc->InsertTab(0, OUString("Data"));
3293 2 : m_pDoc->InsertTab(1, OUString("Table"));
3294 :
3295 2 : size_t nRowCount = SAL_N_ELEMENTS(aData);
3296 2 : ScAddress aPos(1,1,0);
3297 2 : ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, nRowCount);
3298 :
3299 : // Insert formulas to manually calculate sums for each name.
3300 2 : 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 2 : 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 2 : 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 2 : m_pDoc->CalcAll();
3305 :
3306 : // Get correct sum values.
3307 2 : double fDennisTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+1, aDataRange.aStart.Tab());
3308 2 : double fMikeTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+2, aDataRange.aStart.Tab());
3309 2 : double fSamTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+3, aDataRange.aStart.Tab());
3310 :
3311 : ScDPObject* pDPObj = createDPFromRange(
3312 2 : m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3313 :
3314 2 : ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3315 2 : bool bSuccess = pDPs->InsertNewTable(pDPObj);
3316 :
3317 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3318 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
3319 2 : pDPs->GetCount(), static_cast<size_t>(1));
3320 2 : pDPObj->SetName(pDPs->CreateNewName());
3321 :
3322 2 : 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 2 : aPos = aOutRange.aStart;
3339 2 : aPos.IncCol();
3340 2 : aPos.IncRow();
3341 2 : double fTest = m_pDoc->GetValue(aPos);
3342 2 : CPPUNIT_ASSERT_MESSAGE("Incorrect value for Dennis.", rtl::math::approxEqual(fTest, fDennisTotal));
3343 2 : aPos.IncRow();
3344 2 : fTest = m_pDoc->GetValue(aPos);
3345 2 : CPPUNIT_ASSERT_MESSAGE("Incorrect value for Mike.", rtl::math::approxEqual(fTest, fMikeTotal));
3346 2 : aPos.IncRow();
3347 2 : fTest = m_pDoc->GetValue(aPos);
3348 2 : CPPUNIT_ASSERT_MESSAGE("Incorrect value for Sam.", rtl::math::approxEqual(fTest, fSamTotal));
3349 :
3350 2 : pDPs->FreeTable(pDPObj);
3351 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3352 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3353 2 : pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3354 :
3355 2 : m_pDoc->DeleteTab(1);
3356 2 : m_pDoc->DeleteTab(0);
3357 2 : }
3358 :
3359 2 : void Test::testSheetCopy()
3360 : {
3361 2 : OUString aTabName("TestTab");
3362 2 : m_pDoc->InsertTab(0, aTabName);
3363 2 : CPPUNIT_ASSERT_MESSAGE("document should have one sheet to begin with.", m_pDoc->GetTableCount() == 1);
3364 : SCROW nRow1, nRow2;
3365 2 : bool bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3366 2 : CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3367 :
3368 : // Copy and test the result.
3369 2 : m_pDoc->CopyTab(0, 1);
3370 2 : CPPUNIT_ASSERT_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount() == 2);
3371 2 : bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3372 2 : CPPUNIT_ASSERT_MESSAGE("copied sheet should also have all rows visible as the original.", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3373 2 : m_pDoc->DeleteTab(1);
3374 :
3375 2 : m_pDoc->SetRowHidden(5, 10, 0, true);
3376 2 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3377 2 : CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3378 2 : bHidden = m_pDoc->RowHidden(5, 0, &nRow1, &nRow2);
3379 2 : CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3380 2 : bHidden = m_pDoc->RowHidden(11, 0, &nRow1, &nRow2);
3381 2 : CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3382 :
3383 : // Copy the sheet once again.
3384 2 : m_pDoc->CopyTab(0, 1);
3385 2 : CPPUNIT_ASSERT_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount() == 2);
3386 2 : bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3387 2 : CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3388 2 : bHidden = m_pDoc->RowHidden(5, 1, &nRow1, &nRow2);
3389 2 : CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3390 2 : bHidden = m_pDoc->RowHidden(11, 1, &nRow1, &nRow2);
3391 2 : CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3392 2 : m_pDoc->DeleteTab(1);
3393 2 : m_pDoc->DeleteTab(0);
3394 2 : }
3395 :
3396 2 : void Test::testSheetMove()
3397 : {
3398 2 : OUString aTabName("TestTab1");
3399 2 : m_pDoc->InsertTab(0, aTabName);
3400 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("document should have one sheet to begin with.", m_pDoc->GetTableCount(), static_cast<SCTAB>(1));
3401 : SCROW nRow1, nRow2;
3402 2 : bool bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3403 2 : 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 2 : m_pDoc->InsertTab(0, OUString("TestTab2"));
3407 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("document should have two sheets", m_pDoc->GetTableCount(), static_cast<SCTAB>(2));
3408 2 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3409 2 : CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3410 :
3411 : // Move and test the result.
3412 2 : m_pDoc->MoveTab(0, 1);
3413 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount(), static_cast<SCTAB>(2));
3414 2 : bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3415 2 : CPPUNIT_ASSERT_MESSAGE("copied sheet should also have all rows visible as the original.", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3416 2 : rtl::OUString aName;
3417 2 : m_pDoc->GetName(0, aName);
3418 2 : CPPUNIT_ASSERT_MESSAGE( "sheets should have changed places", aName == "TestTab1" );
3419 :
3420 2 : m_pDoc->SetRowHidden(5, 10, 0, true);
3421 2 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3422 2 : CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3423 2 : bHidden = m_pDoc->RowHidden(5, 0, &nRow1, &nRow2);
3424 2 : CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3425 2 : bHidden = m_pDoc->RowHidden(11, 0, &nRow1, &nRow2);
3426 2 : CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3427 :
3428 : // Move the sheet once again.
3429 2 : m_pDoc->MoveTab(1, 0);
3430 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount(), static_cast<SCTAB>(2));
3431 2 : bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3432 2 : CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3433 2 : bHidden = m_pDoc->RowHidden(5, 1, &nRow1, &nRow2);
3434 2 : CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3435 2 : bHidden = m_pDoc->RowHidden(11, 1, &nRow1, &nRow2);
3436 2 : CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3437 2 : m_pDoc->GetName(0, aName);
3438 2 : CPPUNIT_ASSERT_MESSAGE( "sheets should have changed places", aName == "TestTab2" );
3439 2 : m_pDoc->DeleteTab(1);
3440 2 : m_pDoc->DeleteTab(0);
3441 2 : }
3442 :
3443 10 : ScDocShell* findLoadedDocShellByName(const OUString& rName)
3444 : {
3445 10 : TypeId aType(TYPE(ScDocShell));
3446 10 : ScDocShell* pShell = static_cast<ScDocShell*>(SfxObjectShell::GetFirst(&aType, false));
3447 350 : while (pShell)
3448 : {
3449 336 : SfxMedium* pMedium = pShell->GetMedium();
3450 336 : if (pMedium)
3451 : {
3452 8 : OUString aName = pMedium->GetName();
3453 8 : if (aName.equals(rName))
3454 6 : return pShell;
3455 : }
3456 330 : pShell = static_cast<ScDocShell*>(SfxObjectShell::GetNext(*pShell, &aType, false));
3457 : }
3458 4 : return NULL;
3459 : }
3460 :
3461 4 : ScRange getCachedRange(const ScExternalRefCache::TableTypeRef& pCacheTab)
3462 : {
3463 4 : ScRange aRange;
3464 :
3465 4 : vector<SCROW> aRows;
3466 4 : pCacheTab->getAllRows(aRows);
3467 4 : vector<SCROW>::const_iterator itrRow = aRows.begin(), itrRowEnd = aRows.end();
3468 4 : bool bFirst = true;
3469 22 : for (; itrRow != itrRowEnd; ++itrRow)
3470 : {
3471 18 : SCROW nRow = *itrRow;
3472 18 : vector<SCCOL> aCols;
3473 18 : pCacheTab->getAllCols(nRow, aCols);
3474 18 : vector<SCCOL>::const_iterator itrCol = aCols.begin(), itrColEnd = aCols.end();
3475 54 : for (; itrCol != itrColEnd; ++itrCol)
3476 : {
3477 36 : SCCOL nCol = *itrCol;
3478 36 : if (bFirst)
3479 : {
3480 4 : aRange.aStart = ScAddress(nCol, nRow, 0);
3481 4 : aRange.aEnd = aRange.aStart;
3482 4 : bFirst = false;
3483 : }
3484 : else
3485 : {
3486 32 : if (nCol < aRange.aStart.Col())
3487 0 : aRange.aStart.SetCol(nCol);
3488 32 : else if (aRange.aEnd.Col() < nCol)
3489 4 : aRange.aEnd.SetCol(nCol);
3490 :
3491 32 : if (nRow < aRange.aStart.Row())
3492 0 : aRange.aStart.SetRow(nRow);
3493 32 : else if (aRange.aEnd.Row() < nRow)
3494 14 : aRange.aEnd.SetRow(nRow);
3495 : }
3496 : }
3497 18 : }
3498 4 : return aRange;
3499 : }
3500 :
3501 2 : void Test::testExternalRef()
3502 : {
3503 2 : ScDocShellRef xExtDocSh = new ScDocShell;
3504 2 : OUString aExtDocName("file:///extdata.fake");
3505 2 : OUString aExtSh1Name("Data1");
3506 2 : OUString aExtSh2Name("Data2");
3507 2 : OUString aExtSh3Name("Data3");
3508 2 : SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
3509 2 : xExtDocSh->DoInitNew(pMed);
3510 4 : CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
3511 2 : findLoadedDocShellByName(aExtDocName) != NULL);
3512 :
3513 : // Populate the external source document.
3514 2 : ScDocument* pExtDoc = xExtDocSh->GetDocument();
3515 2 : pExtDoc->InsertTab(0, aExtSh1Name);
3516 2 : pExtDoc->InsertTab(1, aExtSh2Name);
3517 2 : pExtDoc->InsertTab(2, aExtSh3Name);
3518 :
3519 2 : OUString name("Name");
3520 2 : OUString value("Value");
3521 2 : OUString andy("Andy");
3522 2 : OUString bruce("Bruce");
3523 2 : OUString charlie("Charlie");
3524 2 : OUString david("David");
3525 2 : OUString edward("Edward");
3526 2 : OUString frank("Frank");
3527 2 : OUString george("George");
3528 2 : OUString henry("Henry");
3529 :
3530 : // Sheet 1
3531 2 : pExtDoc->SetString(0, 0, 0, name);
3532 2 : pExtDoc->SetString(0, 1, 0, andy);
3533 2 : pExtDoc->SetString(0, 2, 0, bruce);
3534 2 : pExtDoc->SetString(0, 3, 0, charlie);
3535 2 : pExtDoc->SetString(0, 4, 0, david);
3536 2 : pExtDoc->SetString(1, 0, 0, value);
3537 2 : double val = 10;
3538 2 : pExtDoc->SetValue(1, 1, 0, val);
3539 2 : val = 11;
3540 2 : pExtDoc->SetValue(1, 2, 0, val);
3541 2 : val = 12;
3542 2 : pExtDoc->SetValue(1, 3, 0, val);
3543 2 : val = 13;
3544 2 : pExtDoc->SetValue(1, 4, 0, val);
3545 :
3546 : // Sheet 2 remains empty.
3547 :
3548 : // Sheet 3
3549 2 : pExtDoc->SetString(0, 0, 2, name);
3550 2 : pExtDoc->SetString(0, 1, 2, edward);
3551 2 : pExtDoc->SetString(0, 2, 2, frank);
3552 2 : pExtDoc->SetString(0, 3, 2, george);
3553 2 : pExtDoc->SetString(0, 4, 2, henry);
3554 2 : pExtDoc->SetString(1, 0, 2, value);
3555 2 : val = 99;
3556 2 : pExtDoc->SetValue(1, 1, 2, val);
3557 2 : val = 98;
3558 2 : pExtDoc->SetValue(1, 2, 2, val);
3559 2 : val = 97;
3560 2 : pExtDoc->SetValue(1, 3, 2, val);
3561 2 : val = 96;
3562 2 : 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 2 : OUString test;
3567 2 : m_pDoc->InsertTab(0, OUString("Test Sheet"));
3568 2 : m_pDoc->SetString(0, 0, 0, OUString("='file:///extdata.fake'#Data1.A1"));
3569 2 : m_pDoc->GetString(0, 0, 0, test);
3570 2 : 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 2 : ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
3577 2 : sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
3578 2 : vector<OUString> aTabNames;
3579 2 : pRefMgr->getAllCachedTableNames(nFileId, aTabNames);
3580 2 : CPPUNIT_ASSERT_MESSAGE("There should be at least 3 sheets.", aTabNames.size() >= 3);
3581 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[0].equals(aExtSh1Name));
3582 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[1].equals(aExtSh2Name));
3583 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[2].equals(aExtSh3Name));
3584 :
3585 2 : m_pDoc->SetString(1, 0, 0, OUString("='file:///extdata.fake'#Data1.B1"));
3586 2 : m_pDoc->GetString(1, 0, 0, test);
3587 2 : CPPUNIT_ASSERT_MESSAGE("Value is different from the original", test.equals(value));
3588 :
3589 2 : m_pDoc->SetString(0, 1, 0, OUString("='file:///extdata.fake'#Data1.A2"));
3590 2 : m_pDoc->SetString(0, 2, 0, OUString("='file:///extdata.fake'#Data1.A3"));
3591 2 : m_pDoc->SetString(0, 3, 0, OUString("='file:///extdata.fake'#Data1.A4"));
3592 2 : m_pDoc->SetString(0, 4, 0, OUString("='file:///extdata.fake'#Data1.A5"));
3593 2 : m_pDoc->SetString(0, 5, 0, OUString("='file:///extdata.fake'#Data1.A6"));
3594 :
3595 : {
3596 : // Referencing an empty cell should display '0'.
3597 2 : const char* pChecks[] = { "Andy", "Bruce", "Charlie", "David", "0" };
3598 12 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3599 : {
3600 10 : m_pDoc->GetString(0, static_cast<SCROW>(i+1), 0, test);
3601 10 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3602 : }
3603 : }
3604 2 : m_pDoc->SetString(1, 1, 0, OUString("='file:///extdata.fake'#Data1.B2"));
3605 2 : m_pDoc->SetString(1, 2, 0, OUString("='file:///extdata.fake'#Data1.B3"));
3606 2 : m_pDoc->SetString(1, 3, 0, OUString("='file:///extdata.fake'#Data1.B4"));
3607 2 : m_pDoc->SetString(1, 4, 0, OUString("='file:///extdata.fake'#Data1.B5"));
3608 2 : m_pDoc->SetString(1, 5, 0, OUString("='file:///extdata.fake'#Data1.B6"));
3609 : {
3610 2 : double pChecks[] = { 10, 11, 12, 13, 0 };
3611 12 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3612 : {
3613 10 : m_pDoc->GetValue(1, static_cast<SCROW>(i+1), 0, val);
3614 10 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", val == pChecks[i]);
3615 : }
3616 : }
3617 :
3618 2 : m_pDoc->SetString(2, 0, 0, OUString("='file:///extdata.fake'#Data3.A1"));
3619 2 : m_pDoc->SetString(2, 1, 0, OUString("='file:///extdata.fake'#Data3.A2"));
3620 2 : m_pDoc->SetString(2, 2, 0, OUString("='file:///extdata.fake'#Data3.A3"));
3621 2 : m_pDoc->SetString(2, 3, 0, OUString("='file:///extdata.fake'#Data3.A4"));
3622 : {
3623 2 : const char* pChecks[] = { "Name", "Edward", "Frank", "George" };
3624 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3625 : {
3626 8 : m_pDoc->GetString(2, static_cast<SCROW>(i), 0, test);
3627 8 : CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3628 : }
3629 : }
3630 :
3631 2 : m_pDoc->SetString(3, 0, 0, OUString("='file:///extdata.fake'#Data3.B1"));
3632 2 : m_pDoc->SetString(3, 1, 0, OUString("='file:///extdata.fake'#Data3.B2"));
3633 2 : m_pDoc->SetString(3, 2, 0, OUString("='file:///extdata.fake'#Data3.B3"));
3634 2 : m_pDoc->SetString(3, 3, 0, OUString("='file:///extdata.fake'#Data3.B4"));
3635 : {
3636 2 : const char* pChecks[] = { "Value", "99", "98", "97" };
3637 10 : for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3638 : {
3639 8 : m_pDoc->GetString(3, static_cast<SCROW>(i), 0, test);
3640 8 : 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 2 : nFileId, aExtSh1Name, false);
3648 2 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 1 should exist.", pCacheTab.get() != NULL);
3649 2 : ScRange aCachedRange = getCachedRange(pCacheTab);
3650 4 : CPPUNIT_ASSERT_MESSAGE("Unexpected cached data range.",
3651 : aCachedRange.aStart.Col() == 0 && aCachedRange.aEnd.Col() == 1 &&
3652 2 : aCachedRange.aStart.Row() == 0 && aCachedRange.aEnd.Row() == 4);
3653 :
3654 : // Sheet2 is not referenced at all; the cache table shouldn't even exist.
3655 2 : pCacheTab = pRefMgr->getCacheTable(nFileId, aExtSh2Name, false);
3656 2 : 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 2 : pCacheTab = pRefMgr->getCacheTable(nFileId, aExtSh3Name, false);
3660 2 : CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 3 should exist.", pCacheTab.get() != NULL);
3661 2 : aCachedRange = getCachedRange(pCacheTab);
3662 4 : CPPUNIT_ASSERT_MESSAGE("Unexpected cached data range.",
3663 : aCachedRange.aStart.Col() == 0 && aCachedRange.aEnd.Col() == 1 &&
3664 2 : aCachedRange.aStart.Row() == 0 && aCachedRange.aEnd.Row() == 3);
3665 :
3666 : // Unload the external document shell.
3667 2 : xExtDocSh->DoClose();
3668 4 : CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
3669 2 : findLoadedDocShellByName(aExtDocName) == NULL);
3670 :
3671 2 : m_pDoc->DeleteTab(0);
3672 2 : }
3673 :
3674 2 : void testExtRefFuncT(ScDocument* pDoc, ScDocument* pExtDoc)
3675 : {
3676 2 : clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
3677 2 : clearRange(pExtDoc, ScRange(0, 0, 0, 1, 9, 0));
3678 :
3679 2 : pExtDoc->SetString(0, 0, 0, rtl::OUString("'1.2"));
3680 2 : pExtDoc->SetString(0, 1, 0, rtl::OUString("Foo"));
3681 2 : pExtDoc->SetValue(0, 2, 0, 12.3);
3682 2 : pDoc->SetString(0, 0, 0, rtl::OUString("=T('file:///extdata.fake'#Data.A1)"));
3683 2 : pDoc->SetString(0, 1, 0, rtl::OUString("=T('file:///extdata.fake'#Data.A2)"));
3684 2 : pDoc->SetString(0, 2, 0, rtl::OUString("=T('file:///extdata.fake'#Data.A3)"));
3685 2 : pDoc->CalcAll();
3686 :
3687 2 : rtl::OUString aRes = pDoc->GetString(0, 0, 0);
3688 2 : CPPUNIT_ASSERT_MESSAGE( "Unexpected result with T.", aRes == "1.2" );
3689 2 : aRes = pDoc->GetString(0, 1, 0);
3690 2 : CPPUNIT_ASSERT_MESSAGE( "Unexpected result with T.", aRes == "Foo" );
3691 2 : aRes = pDoc->GetString(0, 2, 0);
3692 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected result with T.", aRes.isEmpty());
3693 2 : }
3694 :
3695 2 : void Test::testExternalRefFunctions()
3696 : {
3697 2 : ScDocShellRef xExtDocSh = new ScDocShell;
3698 2 : OUString aExtDocName("file:///extdata.fake");
3699 2 : SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
3700 2 : xExtDocSh->DoInitNew(pMed);
3701 4 : CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
3702 2 : findLoadedDocShellByName(aExtDocName) != NULL);
3703 :
3704 2 : ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
3705 2 : CPPUNIT_ASSERT_MESSAGE("external reference manager doesn't exist.", pRefMgr);
3706 2 : sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
3707 2 : const OUString* pFileName = pRefMgr->getExternalFileName(nFileId);
3708 4 : CPPUNIT_ASSERT_MESSAGE("file name registration has somehow failed.",
3709 2 : pFileName && pFileName->equals(aExtDocName));
3710 :
3711 : // Populate the external source document.
3712 2 : ScDocument* pExtDoc = xExtDocSh->GetDocument();
3713 2 : pExtDoc->InsertTab(0, rtl::OUString("Data"));
3714 2 : double val = 1;
3715 2 : pExtDoc->SetValue(0, 0, 0, val);
3716 : // leave cell B1 empty.
3717 2 : val = 2;
3718 2 : pExtDoc->SetValue(0, 1, 0, val);
3719 2 : pExtDoc->SetValue(1, 1, 0, val);
3720 2 : val = 3;
3721 2 : pExtDoc->SetValue(0, 2, 0, val);
3722 2 : pExtDoc->SetValue(1, 2, 0, val);
3723 2 : val = 4;
3724 2 : pExtDoc->SetValue(0, 3, 0, val);
3725 2 : pExtDoc->SetValue(1, 3, 0, val);
3726 :
3727 2 : 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 2 : };
3739 :
3740 14 : for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
3741 : {
3742 12 : m_pDoc->SetString(0, 0, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
3743 12 : m_pDoc->CalcAll();
3744 12 : m_pDoc->GetValue(0, 0, 0, val);
3745 12 : CPPUNIT_ASSERT_MESSAGE("unexpected result involving external ranges.", val == aChecks[i].fResult);
3746 : }
3747 :
3748 2 : pRefMgr->clearCache(nFileId);
3749 2 : testExtRefFuncT(m_pDoc, pExtDoc);
3750 :
3751 : // Unload the external document shell.
3752 2 : xExtDocSh->DoClose();
3753 4 : CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
3754 2 : findLoadedDocShellByName(aExtDocName) == NULL);
3755 :
3756 2 : m_pDoc->DeleteTab(0);
3757 2 : }
3758 :
3759 2 : void Test::testDataArea()
3760 : {
3761 2 : m_pDoc->InsertTab(0, OUString("Data"));
3762 :
3763 : // Totally empty sheet should be rightfully considered empty in all accounts.
3764 2 : CPPUNIT_ASSERT_MESSAGE("Sheet is expected to be empty.", m_pDoc->IsPrintEmpty(0, 0, 0, 100, 100));
3765 2 : 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 2 : ::editeng::SvxBorderLine aLine(NULL, 50, table::BorderLineStyle::SOLID);
3769 2 : SvxBoxItem aBorderItem(ATTR_BORDER);
3770 2 : aBorderItem.SetLine(&aLine, BOX_LINE_LEFT);
3771 2 : aBorderItem.SetLine(&aLine, BOX_LINE_RIGHT);
3772 202 : for (SCROW i = 0; i < 100; ++i)
3773 : // Set borders from row 1 to 100.
3774 200 : 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 4 : CPPUNIT_ASSERT_MESSAGE("Empty sheet with borders should be printable.",
3779 2 : !m_pDoc->IsPrintEmpty(0, 0, 0, 100, 100));
3780 4 : CPPUNIT_ASSERT_MESSAGE("But it should still be considered empty in all the other cases.",
3781 2 : m_pDoc->IsBlockEmpty(0, 0, 0, 100, 100));
3782 :
3783 : // Adding a real cell content should turn the block non-empty.
3784 2 : m_pDoc->SetString(0, 0, 0, OUString("Some text"));
3785 4 : CPPUNIT_ASSERT_MESSAGE("Now the block should not be empty with a real cell content.",
3786 2 : !m_pDoc->IsBlockEmpty(0, 0, 0, 100, 100));
3787 :
3788 : // TODO: Add more tests for normal data area calculation.
3789 :
3790 2 : m_pDoc->DeleteTab(0);
3791 2 : }
3792 :
3793 2 : void Test::testStreamValid()
3794 : {
3795 2 : m_pDoc->InsertTab(0, OUString("Sheet1"));
3796 2 : m_pDoc->InsertTab(1, OUString("Sheet2"));
3797 2 : m_pDoc->InsertTab(2, OUString("Sheet3"));
3798 2 : m_pDoc->InsertTab(3, OUString("Sheet4"));
3799 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("We should have 4 sheet instances.", m_pDoc->GetTableCount(), static_cast<SCTAB>(4));
3800 :
3801 2 : OUString a1("A1");
3802 2 : OUString a2("A2");
3803 2 : OUString test;
3804 :
3805 : // Put values into Sheet1.
3806 2 : m_pDoc->SetString(0, 0, 0, a1);
3807 2 : m_pDoc->SetString(0, 1, 0, a2);
3808 2 : m_pDoc->GetString(0, 0, 0, test);
3809 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet1.A1", test.equals(a1));
3810 2 : m_pDoc->GetString(0, 1, 0, test);
3811 2 : 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 2 : m_pDoc->SetString(0, 0, 1, OUString("=Sheet1.A1"));
3815 2 : m_pDoc->SetString(0, 1, 1, OUString("=Sheet1.A2"));
3816 2 : m_pDoc->SetString(0, 0, 2, OUString("=Sheet1.A1"));
3817 2 : m_pDoc->SetString(0, 0, 3, OUString("=Sheet1.A2"));
3818 :
3819 2 : m_pDoc->GetString(0, 0, 1, test);
3820 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet2.A1", test.equals(a1));
3821 2 : m_pDoc->GetString(0, 1, 1, test);
3822 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet2.A2", test.equals(a2));
3823 2 : m_pDoc->GetString(0, 0, 2, test);
3824 2 : CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet3.A1", test.equals(a1));
3825 2 : m_pDoc->GetString(0, 0, 3, test);
3826 2 : 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 2 : m_pDoc->SetStreamValid(0, true);
3832 2 : m_pDoc->SetStreamValid(1, true);
3833 2 : m_pDoc->SetStreamValid(2, true);
3834 2 : m_pDoc->SetStreamValid(3, true);
3835 2 : CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(0));
3836 2 : CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(1));
3837 2 : CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(2));
3838 2 : 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 2 : m_pDoc->InsertRow(0, 0, MAXCOL, 0, 1, 2);
3843 2 : m_pDoc->GetString(0, 0, 0, test);
3844 2 : CPPUNIT_ASSERT_MESSAGE("Cell A1 should not have moved.", test.equals(a1));
3845 2 : m_pDoc->GetString(0, 3, 0, test);
3846 2 : CPPUNIT_ASSERT_MESSAGE("the old cell A2 should now be at A4.", test.equals(a2));
3847 2 : const ScBaseCell* pCell = m_pDoc->GetCell(ScAddress(0, 1, 0));
3848 2 : CPPUNIT_ASSERT_MESSAGE("Cell A2 should be empty.", pCell == NULL);
3849 2 : pCell = m_pDoc->GetCell(ScAddress(0, 2, 0));
3850 2 : 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 2 : CPPUNIT_ASSERT_MESSAGE("Stream should have been invalidated.", !m_pDoc->IsStreamValid(0));
3855 2 : CPPUNIT_ASSERT_MESSAGE("Stream should have been invalidated.", !m_pDoc->IsStreamValid(1));
3856 2 : CPPUNIT_ASSERT_MESSAGE("Stream should have been invalidated.", !m_pDoc->IsStreamValid(3));
3857 2 : CPPUNIT_ASSERT_MESSAGE("Stream should still be valid.", m_pDoc->IsStreamValid(2));
3858 :
3859 2 : m_pDoc->DeleteTab(3);
3860 2 : m_pDoc->DeleteTab(2);
3861 2 : m_pDoc->DeleteTab(1);
3862 2 : m_pDoc->DeleteTab(0);
3863 2 : }
3864 :
3865 2 : 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 2 : };
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 2 : };
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 2 : };
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 2 : };
3951 :
3952 : const char* aLogical[] = {
3953 : "AND",
3954 : "FALSE",
3955 : "IF",
3956 : "NOT",
3957 : "OR",
3958 : "TRUE",
3959 : "XOR",
3960 : 0
3961 2 : };
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 : "BITAND",
3975 : "BITLSHIFT",
3976 : "BITOR",
3977 : "BITRSHIFT",
3978 : "BITXOR",
3979 : "CEILING",
3980 : "COMBIN",
3981 : "COMBINA",
3982 : "CONVERT",
3983 : "COS",
3984 : "COSH",
3985 : "COT",
3986 : "COTH",
3987 : "COUNTBLANK",
3988 : "COUNTIF",
3989 : "CSC",
3990 : "CSCH",
3991 : "DEGREES",
3992 : "EUROCONVERT",
3993 : "EVEN",
3994 : "EXP",
3995 : "FACT",
3996 : "FLOOR",
3997 : "GCD",
3998 : "INT",
3999 : "ISEVEN",
4000 : "ISODD",
4001 : "LCM",
4002 : "LN",
4003 : "LOG",
4004 : "LOG10",
4005 : "MOD",
4006 : "ODD",
4007 : "PI",
4008 : "POWER",
4009 : "PRODUCT",
4010 : "RADIANS",
4011 : "RAND",
4012 : "ROUND",
4013 : "ROUNDDOWN",
4014 : "ROUNDUP",
4015 : "SEC",
4016 : "SECH",
4017 : "SIGN",
4018 : "SIN",
4019 : "SINH",
4020 : "SQRT",
4021 : "SUBTOTAL",
4022 : "SUM",
4023 : "SUMIF",
4024 : "SUMSQ",
4025 : "TAN",
4026 : "TANH",
4027 : "TRUNC",
4028 : 0
4029 2 : };
4030 :
4031 : const char* aArray[] = {
4032 : "FREQUENCY",
4033 : "GROWTH",
4034 : "LINEST",
4035 : "LOGEST",
4036 : "MDETERM",
4037 : "MINVERSE",
4038 : "MMULT",
4039 : "MUNIT",
4040 : "SUMPRODUCT",
4041 : "SUMX2MY2",
4042 : "SUMX2PY2",
4043 : "SUMXMY2",
4044 : "TRANSPOSE",
4045 : "TREND",
4046 : 0
4047 2 : };
4048 :
4049 : const char* aStatistical[] = {
4050 : "AVEDEV",
4051 : "AVERAGE",
4052 : "AVERAGEA",
4053 : "B",
4054 : "BETADIST",
4055 : "BETAINV",
4056 : "BINOMDIST",
4057 : "CHIDIST",
4058 : "CHIINV",
4059 : "CHISQDIST",
4060 : "CHISQINV",
4061 : "CHITEST",
4062 : "CONFIDENCE",
4063 : "CORREL",
4064 : "COUNT",
4065 : "COUNTA",
4066 : "COVAR",
4067 : "CRITBINOM",
4068 : "DEVSQ",
4069 : "EXPONDIST",
4070 : "FDIST",
4071 : "FINV",
4072 : "FISHER",
4073 : "FISHERINV",
4074 : "FORECAST",
4075 : "FTEST",
4076 : "GAMMA",
4077 : "GAMMADIST",
4078 : "GAMMAINV",
4079 : "GAMMALN",
4080 : "GAUSS",
4081 : "GEOMEAN",
4082 : "HARMEAN",
4083 : "HYPGEOMDIST",
4084 : "INTERCEPT",
4085 : "KURT",
4086 : "LARGE",
4087 : "LOGINV",
4088 : "LOGNORMDIST",
4089 : "MAX",
4090 : "MAXA",
4091 : "MEDIAN",
4092 : "MIN",
4093 : "MINA",
4094 : "MODE",
4095 : "NEGBINOMDIST",
4096 : "NORMDIST",
4097 : "NORMINV",
4098 : "NORMSDIST",
4099 : "NORMSINV",
4100 : "PEARSON",
4101 : "PERCENTILE",
4102 : "PERCENTRANK",
4103 : "PERMUT",
4104 : "PERMUTATIONA",
4105 : "PHI",
4106 : "POISSON",
4107 : "PROB",
4108 : "QUARTILE",
4109 : "RANK",
4110 : "RSQ",
4111 : "SKEW",
4112 : "SLOPE",
4113 : "SMALL",
4114 : "STANDARDIZE",
4115 : "STDEV",
4116 : "STDEVA",
4117 : "STDEVP",
4118 : "STDEVPA",
4119 : "STEYX",
4120 : "TDIST",
4121 : "TINV",
4122 : "TRIMMEAN",
4123 : "TTEST",
4124 : "VAR",
4125 : "VARA",
4126 : "VARP",
4127 : "VARPA",
4128 : "WEIBULL",
4129 : "ZTEST",
4130 : 0
4131 2 : };
4132 :
4133 : const char* aSpreadsheet[] = {
4134 : "ADDRESS",
4135 : "AREAS",
4136 : "CHOOSE",
4137 : "COLUMN",
4138 : "COLUMNS",
4139 : "DDE",
4140 : "ERRORTYPE",
4141 : "GETPIVOTDATA",
4142 : "HLOOKUP",
4143 : "HYPERLINK",
4144 : "INDEX",
4145 : "INDIRECT",
4146 : "LOOKUP",
4147 : "MATCH",
4148 : "OFFSET",
4149 : "ROW",
4150 : "ROWS",
4151 : "SHEET",
4152 : "SHEETS",
4153 : "STYLE",
4154 : "VLOOKUP",
4155 : 0
4156 2 : };
4157 :
4158 : const char* aText[] = {
4159 : "ARABIC",
4160 : "ASC",
4161 : "BAHTTEXT",
4162 : "BASE",
4163 : "CHAR",
4164 : "CLEAN",
4165 : "CODE",
4166 : "CONCATENATE",
4167 : "DECIMAL",
4168 : "DOLLAR",
4169 : "EXACT",
4170 : "FIND",
4171 : "FIXED",
4172 : "JIS",
4173 : "LEFT",
4174 : "LEN",
4175 : "LOWER",
4176 : "MID",
4177 : "PROPER",
4178 : "REPLACE",
4179 : "REPT",
4180 : "RIGHT",
4181 : "ROMAN",
4182 : "SEARCH",
4183 : "SUBSTITUTE",
4184 : "T",
4185 : "TEXT",
4186 : "TRIM",
4187 : "UNICHAR",
4188 : "UNICODE",
4189 : "UPPER",
4190 : "VALUE",
4191 : 0
4192 2 : };
4193 :
4194 : struct {
4195 : const char* Category; const char** Functions;
4196 : } aTests[] = {
4197 : { "Database", aDataBase },
4198 : { "Date&Time", aDateTime },
4199 : { "Financial", aFinancial },
4200 : { "Information", aInformation },
4201 : { "Logical", aLogical },
4202 : { "Mathematical", aMathematical },
4203 : { "Array", aArray },
4204 : { "Statistical", aStatistical },
4205 : { "Spreadsheet", aSpreadsheet },
4206 : { "Text", aText },
4207 : { "Add-in", 0 },
4208 : { 0, 0 }
4209 2 : };
4210 :
4211 2 : ScFunctionMgr* pFuncMgr = ScGlobal::GetStarCalcFunctionMgr();
4212 2 : sal_uInt32 n = pFuncMgr->getCount();
4213 24 : for (sal_uInt32 i = 0; i < n; ++i)
4214 : {
4215 22 : const formula::IFunctionCategory* pCat = pFuncMgr->getCategory(i);
4216 22 : CPPUNIT_ASSERT_MESSAGE("Unexpected category name", pCat->getName().equalsAscii(aTests[i].Category));
4217 22 : sal_uInt32 nFuncCount = pCat->getCount();
4218 596 : for (sal_uInt32 j = 0; j < nFuncCount; ++j)
4219 : {
4220 574 : const formula::IFunctionDescription* pFunc = pCat->getFunction(j);
4221 574 : CPPUNIT_ASSERT_MESSAGE("Unexpected function name", pFunc->getFunctionName().equalsAscii(aTests[i].Functions[j]));
4222 : }
4223 : }
4224 2 : }
4225 :
4226 2 : void Test::testGraphicsInGroup()
4227 : {
4228 2 : OUString aTabName("TestTab");
4229 2 : m_pDoc->InsertTab(0, aTabName);
4230 2 : CPPUNIT_ASSERT_MESSAGE("document should have one sheet to begin with.", m_pDoc->GetTableCount() == 1);
4231 : SCROW nRow1, nRow2;
4232 2 : bool bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4233 2 : CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
4234 :
4235 2 : m_pDoc->InitDrawLayer();
4236 2 : ScDrawLayer *pDrawLayer = m_pDoc->GetDrawLayer();
4237 2 : CPPUNIT_ASSERT_MESSAGE("must have a draw layer", pDrawLayer != NULL);
4238 2 : SdrPage* pPage = pDrawLayer->GetPage(0);
4239 2 : CPPUNIT_ASSERT_MESSAGE("must have a draw page", pPage != NULL);
4240 :
4241 : {
4242 : //Add a square
4243 2 : Rectangle aOrigRect(2,2,100,100);
4244 2 : SdrRectObj *pObj = new SdrRectObj(aOrigRect);
4245 2 : pPage->InsertObject(pObj);
4246 2 : const Rectangle &rNewRect = pObj->GetLogicRect();
4247 2 : CPPUNIT_ASSERT_MESSAGE("must have equal position and size", aOrigRect == rNewRect);
4248 :
4249 2 : ScDrawLayer::SetPageAnchored(*pObj);
4250 :
4251 : //Use a range of rows guaranteed to include all of the square
4252 2 : m_pDoc->ShowRows(0, 100, 0, false);
4253 2 : m_pDoc->SetDrawPageSize(0);
4254 2 : CPPUNIT_ASSERT_MESSAGE("Should not change when page anchored", aOrigRect == rNewRect);
4255 2 : m_pDoc->ShowRows(0, 100, 0, true);
4256 2 : m_pDoc->SetDrawPageSize(0);
4257 2 : CPPUNIT_ASSERT_MESSAGE("Should not change when page anchored", aOrigRect == rNewRect);
4258 :
4259 2 : ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4260 2 : CPPUNIT_ASSERT_MESSAGE("That shouldn't change size or positioning", aOrigRect == rNewRect);
4261 :
4262 2 : m_pDoc->ShowRows(0, 100, 0, false);
4263 2 : m_pDoc->SetDrawPageSize(0);
4264 4 : CPPUNIT_ASSERT_MESSAGE("Left and Right should be unchanged",
4265 2 : aOrigRect.Left() == rNewRect.Left() && aOrigRect.Right() == rNewRect.Right());
4266 4 : CPPUNIT_ASSERT_MESSAGE("Height should be minimum allowed height",
4267 2 : (rNewRect.Bottom() - rNewRect.Top()) <= 1);
4268 2 : m_pDoc->ShowRows(0, 100, 0, true);
4269 2 : m_pDoc->SetDrawPageSize(0);
4270 2 : CPPUNIT_ASSERT_MESSAGE("Should not change when page anchored", aOrigRect == rNewRect);
4271 : }
4272 :
4273 : {
4274 : // Add a circle.
4275 2 : Rectangle aOrigRect = Rectangle(10,10,210,210); // 200 x 200
4276 2 : SdrCircObj* pObj = new SdrCircObj(OBJ_CIRC, aOrigRect);
4277 2 : pPage->InsertObject(pObj);
4278 2 : const Rectangle& rNewRect = pObj->GetLogicRect();
4279 4 : CPPUNIT_ASSERT_MESSAGE("Position and size of the circle shouldn't change when inserted into the page.",
4280 2 : aOrigRect == rNewRect);
4281 :
4282 2 : ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4283 4 : CPPUNIT_ASSERT_MESSAGE("Size changed when cell anchored. Not good.",
4284 2 : aOrigRect == rNewRect);
4285 :
4286 : // Insert 2 rows at the top. This should push the circle object down.
4287 2 : m_pDoc->InsertRow(0, 0, MAXCOL, 0, 0, 2);
4288 2 : m_pDoc->SetDrawPageSize(0);
4289 :
4290 : // Make sure the size of the circle is still identical.
4291 4 : CPPUNIT_ASSERT_MESSAGE("Size of the circle has changed, but shouldn't!",
4292 2 : aOrigRect.GetSize() == rNewRect.GetSize());
4293 :
4294 : // Delete 2 rows at the top. This should bring the circle object to its original position.
4295 2 : m_pDoc->DeleteRow(0, 0, MAXCOL, 0, 0, 2);
4296 2 : m_pDoc->SetDrawPageSize(0);
4297 2 : CPPUNIT_ASSERT_MESSAGE("Failed to move back to its original position.", aOrigRect == rNewRect);
4298 : }
4299 :
4300 : {
4301 : // Add a line.
4302 2 : basegfx::B2DPolygon aTempPoly;
4303 2 : Point aStartPos(10,300), aEndPos(110,200); // bottom-left to top-right.
4304 2 : Rectangle aOrigRect(10,200,110,300); // 100 x 100
4305 2 : aTempPoly.append(basegfx::B2DPoint(aStartPos.X(), aStartPos.Y()));
4306 2 : aTempPoly.append(basegfx::B2DPoint(aEndPos.X(), aEndPos.Y()));
4307 2 : SdrPathObj* pObj = new SdrPathObj(OBJ_LINE, basegfx::B2DPolyPolygon(aTempPoly));
4308 2 : pObj->NbcSetLogicRect(aOrigRect);
4309 2 : pPage->InsertObject(pObj);
4310 2 : const Rectangle& rNewRect = pObj->GetLogicRect();
4311 2 : CPPUNIT_ASSERT_MESSAGE("Size differ.", aOrigRect == rNewRect);
4312 :
4313 2 : ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4314 4 : CPPUNIT_ASSERT_MESSAGE("Size changed when cell-anchored. Not good.",
4315 2 : aOrigRect == rNewRect);
4316 :
4317 : // Insert 2 rows at the top and delete them immediately.
4318 2 : m_pDoc->InsertRow(0, 0, MAXCOL, 0, 0, 2);
4319 2 : m_pDoc->DeleteRow(0, 0, MAXCOL, 0, 0, 2);
4320 2 : m_pDoc->SetDrawPageSize(0);
4321 4 : CPPUNIT_ASSERT_MESSAGE("Size of a line object changed after row insertion and removal.",
4322 2 : aOrigRect == rNewRect);
4323 :
4324 2 : sal_Int32 n = pObj->GetPointCount();
4325 2 : CPPUNIT_ASSERT_MESSAGE("There should be exactly 2 points in a line object.", n == 2);
4326 4 : CPPUNIT_ASSERT_MESSAGE("Line shape has changed.",
4327 4 : aStartPos == pObj->GetPoint(0) && aEndPos == pObj->GetPoint(1));
4328 : }
4329 :
4330 2 : m_pDoc->DeleteTab(0);
4331 2 : }
4332 :
4333 2 : void Test::testGraphicsOnSheetMove()
4334 : {
4335 2 : m_pDoc->InsertTab(0, rtl::OUString("Tab1"));
4336 2 : m_pDoc->InsertTab(1, rtl::OUString("Tab2"));
4337 2 : CPPUNIT_ASSERT_MESSAGE("There should be only 2 sheets to begin with", m_pDoc->GetTableCount() == 2);
4338 :
4339 2 : m_pDoc->InitDrawLayer();
4340 2 : ScDrawLayer* pDrawLayer = m_pDoc->GetDrawLayer();
4341 2 : CPPUNIT_ASSERT_MESSAGE("No drawing layer.", pDrawLayer);
4342 2 : SdrPage* pPage = pDrawLayer->GetPage(0);
4343 2 : CPPUNIT_ASSERT_MESSAGE("No page instance for the 1st sheet.", pPage);
4344 :
4345 : // Insert an object.
4346 2 : Rectangle aObjRect(2,2,100,100);
4347 2 : SdrObject* pObj = new SdrRectObj(aObjRect);
4348 2 : pPage->InsertObject(pObj);
4349 2 : ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4350 :
4351 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be one object on the 1st sheet.", pPage->GetObjCount(), static_cast<sal_uIntPtr>(1));
4352 :
4353 2 : const ScDrawObjData* pData = ScDrawLayer::GetObjData(pObj);
4354 2 : CPPUNIT_ASSERT_MESSAGE("Object meta-data doesn't exist.", pData);
4355 2 : CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 0 && pData->maEnd.Tab() == 0);
4356 :
4357 2 : pPage = pDrawLayer->GetPage(1);
4358 2 : CPPUNIT_ASSERT_MESSAGE("No page instance for the 2nd sheet.", pPage);
4359 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("2nd sheet shouldn't have any object.", pPage->GetObjCount(), static_cast<sal_uIntPtr>(0));
4360 :
4361 : // Insert a new sheet at left-end, and make sure the object has moved to
4362 : // the 2nd page.
4363 2 : m_pDoc->InsertTab(0, rtl::OUString("NewTab"));
4364 2 : CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be 3 sheets.", m_pDoc->GetTableCount(), static_cast<SCTAB>(3));
4365 2 : pPage = pDrawLayer->GetPage(0);
4366 2 : CPPUNIT_ASSERT_MESSAGE("1st sheet should have no object.", pPage && pPage->GetObjCount() == 0);
4367 2 : pPage = pDrawLayer->GetPage(1);
4368 2 : CPPUNIT_ASSERT_MESSAGE("2nd sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4369 2 : pPage = pDrawLayer->GetPage(2);
4370 2 : CPPUNIT_ASSERT_MESSAGE("3rd sheet should have no object.", pPage && pPage->GetObjCount() == 0);
4371 :
4372 2 : CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 1 && pData->maEnd.Tab() == 1);
4373 :
4374 : // Now, delete the sheet that just got inserted. The object should be back
4375 : // on the 1st sheet.
4376 2 : m_pDoc->DeleteTab(0);
4377 2 : pPage = pDrawLayer->GetPage(0);
4378 2 : CPPUNIT_ASSERT_MESSAGE("1st sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4379 4 : CPPUNIT_ASSERT_MESSAGE("Size and position of the object shouldn't change.",
4380 2 : pObj->GetLogicRect() == aObjRect);
4381 :
4382 2 : CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 0 && pData->maEnd.Tab() == 0);
4383 :
4384 : // Move the 1st sheet to the last position.
4385 2 : m_pDoc->MoveTab(0, 1);
4386 2 : pPage = pDrawLayer->GetPage(0);
4387 2 : CPPUNIT_ASSERT_MESSAGE("1st sheet should have no object.", pPage && pPage->GetObjCount() == 0);
4388 2 : pPage = pDrawLayer->GetPage(1);
4389 2 : CPPUNIT_ASSERT_MESSAGE("2nd sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4390 2 : CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 1 && pData->maEnd.Tab() == 1);
4391 :
4392 : // Copy the 2nd sheet, which has one drawing object to the last position.
4393 2 : m_pDoc->CopyTab(1, 2);
4394 2 : pPage = pDrawLayer->GetPage(2);
4395 2 : CPPUNIT_ASSERT_MESSAGE("Copied sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4396 2 : pObj = pPage->GetObj(0);
4397 2 : CPPUNIT_ASSERT_MESSAGE("Failed to get drawing object.", pObj);
4398 2 : pData = ScDrawLayer::GetObjData(pObj);
4399 2 : CPPUNIT_ASSERT_MESSAGE("Failed to get drawing object meta-data.", pData);
4400 2 : CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 2 && pData->maEnd.Tab() == 2);
4401 :
4402 2 : m_pDoc->DeleteTab(2);
4403 2 : m_pDoc->DeleteTab(1);
4404 2 : m_pDoc->DeleteTab(0);
4405 2 : }
4406 :
4407 2 : void Test::testPostIts()
4408 : {
4409 2 : rtl::OUString aHello("Hello world");
4410 2 : rtl::OUString aJimBob("Jim Bob");
4411 2 : rtl::OUString aTabName("PostIts");
4412 2 : rtl::OUString aTabName2("Table2");
4413 2 : m_pDoc->InsertTab(0, aTabName);
4414 :
4415 2 : ScAddress rAddr(2, 2, 0); // cell C3
4416 2 : ScPostIt *pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
4417 2 : pNote->SetText(rAddr, aHello);
4418 2 : pNote->SetAuthor(aJimBob);
4419 :
4420 2 : ScPostIt *pGetNote = m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr);
4421 2 : CPPUNIT_ASSERT_MESSAGE("note should be itself", pGetNote == pNote );
4422 :
4423 : // Insert one row at row 1.
4424 2 : bool bInsertRow = m_pDoc->InsertRow(0, 0, MAXCOL, 0, 1, 1);
4425 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert row", bInsertRow );
4426 :
4427 2 : CPPUNIT_ASSERT_MESSAGE("note hasn't moved", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == NULL);
4428 2 : rAddr.IncRow(); // cell C4
4429 2 : CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4430 :
4431 : // Insert column at column A.
4432 2 : bool bInsertCol = m_pDoc->InsertCol(0, 0, MAXROW, 0, 1, 1);
4433 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert column", bInsertCol );
4434 :
4435 2 : CPPUNIT_ASSERT_MESSAGE("note hasn't moved", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == NULL);
4436 2 : rAddr.IncCol(); // cell D4
4437 2 : CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4438 :
4439 : // Insert a new sheet to shift the current sheet to the right.
4440 2 : m_pDoc->InsertTab(0, aTabName2);
4441 2 : CPPUNIT_ASSERT_MESSAGE("note hasn't moved", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == NULL);
4442 2 : rAddr.IncTab(); // Move to the next sheet.
4443 2 : CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4444 :
4445 2 : m_pDoc->DeleteTab(0);
4446 2 : rAddr.IncTab(-1);
4447 2 : CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4448 :
4449 : // Insert cell at C4. This should NOT shift the note position.
4450 2 : bInsertRow = m_pDoc->InsertRow(2, 0, 2, 0, 3, 1);
4451 2 : CPPUNIT_ASSERT_MESSAGE("Failed to insert cell at C4.", bInsertRow);
4452 2 : CPPUNIT_ASSERT_MESSAGE("Note shouldn't have moved but it has.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4453 :
4454 : // Delete cell at C4. Again, this should NOT shift the note position.
4455 2 : m_pDoc->DeleteRow(2, 0, 2, 0, 3, 1);
4456 2 : CPPUNIT_ASSERT_MESSAGE("Note shouldn't have moved but it has.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4457 :
4458 : // Now, with the note at D4, delete cell D3. This should shift the note one cell up.
4459 2 : m_pDoc->DeleteRow(3, 0, 3, 0, 2, 1);
4460 2 : rAddr.IncRow(-1); // cell D3
4461 2 : CPPUNIT_ASSERT_MESSAGE("Note at D4 should have shifted up to D3.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4462 :
4463 : // Delete column C. This should shift the note one cell left.
4464 2 : m_pDoc->DeleteCol(0, 0, MAXROW, 0, 2, 1);
4465 2 : rAddr.IncCol(-1); // cell C3
4466 2 : CPPUNIT_ASSERT_MESSAGE("Note at D3 should have shifted left to C3.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4467 :
4468 2 : m_pDoc->DeleteTab(0);
4469 2 : }
4470 :
4471 2 : void Test::testToggleRefFlag()
4472 : {
4473 : // In this test, there is no need to insert formula string into a cell in
4474 : // the document, as ScRefFinder does not depend on the content of the
4475 : // document except for the sheet names.
4476 :
4477 2 : OUString aTabName("Test");
4478 2 : m_pDoc->InsertTab(0, aTabName);
4479 :
4480 : {
4481 : // Calc A1: basic 2D reference
4482 :
4483 2 : OUString aFormula("=B100");
4484 2 : ScAddress aPos(1, 5, 0);
4485 2 : ScRefFinder aFinder(aFormula, aPos, m_pDoc, formula::FormulaGrammar::CONV_OOO);
4486 :
4487 : // Original
4488 2 : CPPUNIT_ASSERT_MESSAGE("Does not equal the original text.", aFormula.equals(aFinder.GetText()));
4489 :
4490 : // column relative / row relative -> column absolute / row absolute
4491 2 : aFinder.ToggleRel(0, aFormula.getLength());
4492 2 : aFormula = aFinder.GetText();
4493 2 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=$B$100" );
4494 :
4495 : // column absolute / row absolute -> column relative / row absolute
4496 2 : aFinder.ToggleRel(0, aFormula.getLength());
4497 2 : aFormula = aFinder.GetText();
4498 2 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=B$100" );
4499 :
4500 : // column relative / row absolute -> column absolute / row relative
4501 2 : aFinder.ToggleRel(0, aFormula.getLength());
4502 2 : aFormula = aFinder.GetText();
4503 2 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=$B100" );
4504 :
4505 : // column absolute / row relative -> column relative / row relative
4506 2 : aFinder.ToggleRel(0, aFormula.getLength());
4507 2 : aFormula = aFinder.GetText();
4508 2 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=B100" );
4509 : }
4510 :
4511 : {
4512 : // Excel R1C1: basic 2D reference
4513 :
4514 2 : OUString aFormula("=R2C1");
4515 2 : ScAddress aPos(3, 5, 0);
4516 2 : ScRefFinder aFinder(aFormula, aPos, m_pDoc, formula::FormulaGrammar::CONV_XL_R1C1);
4517 :
4518 : // Original
4519 2 : CPPUNIT_ASSERT_MESSAGE("Does not equal the original text.", aFormula.equals(aFinder.GetText()));
4520 :
4521 : // column absolute / row absolute -> column relative / row absolute
4522 2 : aFinder.ToggleRel(0, aFormula.getLength());
4523 2 : aFormula = aFinder.GetText();
4524 2 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R2C[-3]" );
4525 :
4526 : // column relative / row absolute - > column absolute / row relative
4527 2 : aFinder.ToggleRel(0, aFormula.getLength());
4528 2 : aFormula = aFinder.GetText();
4529 2 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R[-4]C1" );
4530 :
4531 : // column absolute / row relative -> column relative / row relative
4532 2 : aFinder.ToggleRel(0, aFormula.getLength());
4533 2 : aFormula = aFinder.GetText();
4534 2 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R[-4]C[-3]" );
4535 :
4536 : // column relative / row relative -> column absolute / row absolute
4537 2 : aFinder.ToggleRel(0, aFormula.getLength());
4538 2 : aFormula = aFinder.GetText();
4539 2 : CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R2C1" );
4540 : }
4541 :
4542 : // TODO: Add more test cases esp. for 3D references, Excel A1 syntax, and
4543 : // partial selection within formula string.
4544 :
4545 2 : m_pDoc->DeleteTab(0);
4546 2 : }
4547 :
4548 2 : void Test::testAutofilter()
4549 : {
4550 2 : OUString aTabName("Test");
4551 2 : OUString aDBName("NONAME");
4552 :
4553 2 : m_pDoc->InsertTab( 0, aTabName );
4554 :
4555 : // cell contents (0 = empty cell)
4556 : const char* aData[][3] = {
4557 : { "C1", "C2", "C3" },
4558 : { "0", "1", "A" },
4559 : { "1", "2", 0 },
4560 : { "1", "2", "B" },
4561 : { "0", "2", "B" }
4562 2 : };
4563 :
4564 2 : SCCOL nCols = SAL_N_ELEMENTS(aData[0]);
4565 2 : SCROW nRows = SAL_N_ELEMENTS(aData);
4566 :
4567 : // Populate cells.
4568 12 : for (SCROW i = 0; i < nRows; ++i)
4569 40 : for (SCCOL j = 0; j < nCols; ++j)
4570 30 : if (aData[i][j])
4571 28 : m_pDoc->SetString(j, i, 0, rtl::OUString::createFromAscii(aData[i][j]));
4572 :
4573 2 : ScDBData* pDBData = new ScDBData(aDBName, 0, 0, 0, nCols-1, nRows-1);
4574 2 : m_pDoc->SetAnonymousDBData(0,pDBData);
4575 :
4576 2 : pDBData->SetAutoFilter(true);
4577 2 : ScRange aRange;
4578 2 : pDBData->GetArea(aRange);
4579 2 : m_pDoc->ApplyFlagsTab( aRange.aStart.Col(), aRange.aStart.Row(),
4580 2 : aRange.aEnd.Col(), aRange.aStart.Row(),
4581 6 : aRange.aStart.Tab(), SC_MF_AUTO);
4582 :
4583 : //create the query param
4584 2 : ScQueryParam aParam;
4585 2 : pDBData->GetQueryParam(aParam);
4586 2 : ScQueryEntry& rEntry = aParam.GetEntry(0);
4587 2 : rEntry.bDoQuery = true;
4588 2 : rEntry.nField = 0;
4589 2 : rEntry.eOp = SC_EQUAL;
4590 2 : rEntry.GetQueryItem().mfVal = 0;
4591 : // add queryParam to database range.
4592 2 : pDBData->SetQueryParam(aParam);
4593 :
4594 : // perform the query.
4595 2 : m_pDoc->Query(0, aParam, true);
4596 :
4597 : //control output
4598 : SCROW nRow1, nRow2;
4599 2 : bool bHidden = m_pDoc->RowHidden(2, 0, &nRow1, &nRow2);
4600 2 : CPPUNIT_ASSERT_MESSAGE("rows 2 & 3 should be hidden", bHidden && nRow1 == 2 && nRow2 == 3);
4601 :
4602 : // Remove filtering.
4603 2 : rEntry.Clear();
4604 2 : m_pDoc->Query(0, aParam, true);
4605 2 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4606 2 : CPPUNIT_ASSERT_MESSAGE("All rows should be shown.", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
4607 :
4608 : // Filter for non-empty cells by column C.
4609 2 : rEntry.bDoQuery = true;
4610 2 : rEntry.nField = 2;
4611 2 : rEntry.SetQueryByNonEmpty();
4612 2 : m_pDoc->Query(0, aParam, true);
4613 :
4614 : // only row 3 should be hidden. The rest should be visible.
4615 2 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4616 2 : CPPUNIT_ASSERT_MESSAGE("rows 1 & 2 should be visible.", !bHidden && nRow1 == 0 && nRow2 == 1);
4617 2 : bHidden = m_pDoc->RowHidden(2, 0, &nRow1, &nRow2);
4618 2 : CPPUNIT_ASSERT_MESSAGE("row 3 should be hidden.", bHidden && nRow1 == 2 && nRow2 == 2);
4619 2 : bHidden = m_pDoc->RowHidden(3, 0, &nRow1, &nRow2);
4620 2 : CPPUNIT_ASSERT_MESSAGE("row 4 and down should be visible.", !bHidden && nRow1 == 3 && nRow2 == MAXROW);
4621 :
4622 : // Now, filter for empty cells by column C.
4623 2 : rEntry.SetQueryByEmpty();
4624 2 : m_pDoc->Query(0, aParam, true);
4625 :
4626 : // Now, only row 1 and 3, and 6 and down should be visible.
4627 2 : bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4628 2 : CPPUNIT_ASSERT_MESSAGE("row 1 should be visible.", !bHidden && nRow1 == 0 && nRow2 == 0);
4629 2 : bHidden = m_pDoc->RowHidden(1, 0, &nRow1, &nRow2);
4630 2 : CPPUNIT_ASSERT_MESSAGE("row 2 should be hidden.", bHidden && nRow1 == 1 && nRow2 == 1);
4631 2 : bHidden = m_pDoc->RowHidden(2, 0, &nRow1, &nRow2);
4632 2 : CPPUNIT_ASSERT_MESSAGE("row 3 should be visible.", !bHidden && nRow1 == 2 && nRow2 == 2);
4633 2 : bHidden = m_pDoc->RowHidden(3, 0, &nRow1, &nRow2);
4634 2 : CPPUNIT_ASSERT_MESSAGE("rows 4 & 5 should be hidden.", bHidden && nRow1 == 3 && nRow2 == 4);
4635 2 : bHidden = m_pDoc->RowHidden(5, 0, &nRow1, &nRow2);
4636 2 : CPPUNIT_ASSERT_MESSAGE("rows 6 and down should be all visible.", !bHidden && nRow1 == 5 && nRow2 == MAXROW);
4637 :
4638 2 : m_pDoc->DeleteTab(0);
4639 2 : }
4640 :
4641 2 : void Test::testCopyPaste()
4642 : {
4643 2 : m_pDoc->InsertTab(0, OUString("Sheet1"));
4644 2 : m_pDoc->InsertTab(1, OUString("Sheet2"));
4645 : //test copy&paste + ScUndoPaste
4646 : //copy local and global range names in formulas
4647 : //string cells and value cells
4648 2 : m_pDoc->SetValue(0, 0, 0, 1);
4649 2 : m_pDoc->SetValue(3, 0, 0, 0);
4650 2 : m_pDoc->SetValue(3, 1, 0, 1);
4651 2 : m_pDoc->SetValue(3, 2, 0, 2);
4652 2 : m_pDoc->SetValue(3, 3, 0, 3);
4653 2 : m_pDoc->SetString(2, 0, 0, OUString("test"));
4654 2 : ScAddress aAdr (0, 0, 0);
4655 :
4656 : //create some range names, local and global
4657 2 : ScRangeData* pLocal1 = new ScRangeData(m_pDoc, rtl::OUString("local1"), aAdr);
4658 2 : ScRangeData* pLocal2 = new ScRangeData(m_pDoc, OUString("local2"), aAdr);
4659 2 : ScRangeData* pGlobal = new ScRangeData(m_pDoc, OUString("global"), aAdr);
4660 2 : ScRangeName* pGlobalRangeName = new ScRangeName();
4661 2 : pGlobalRangeName->insert(pGlobal);
4662 2 : ScRangeName* pLocalRangeName1 = new ScRangeName();
4663 2 : pLocalRangeName1->insert(pLocal1);
4664 2 : pLocalRangeName1->insert(pLocal2);
4665 2 : m_pDoc->SetRangeName(pGlobalRangeName);
4666 2 : m_pDoc->SetRangeName(0, pLocalRangeName1);
4667 :
4668 : //add formula
4669 2 : rtl::OUString aFormulaString("=local1+global+SUM($C$1:$D$4)");
4670 2 : m_pDoc->SetString(1, 0, 0, aFormulaString);
4671 :
4672 2 : double aValue = 0;
4673 2 : m_pDoc->GetValue(1, 0, 0, aValue);
4674 2 : std::cout << "Value: " << aValue << std::endl;
4675 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("formula should return 8", aValue, 8);
4676 :
4677 : //copy Sheet1.A1:C1 to Sheet2.A2:C2
4678 2 : ScRange aRange(0,0,0,2,0,0);
4679 2 : ScClipParam aClipParam(aRange, false);
4680 2 : ScMarkData aMark;
4681 2 : aMark.SetMarkArea(aRange);
4682 2 : ScDocument* pClipDoc = new ScDocument(SCDOCMODE_CLIP);
4683 2 : m_pDoc->CopyToClip(aClipParam, pClipDoc, &aMark);
4684 :
4685 2 : sal_uInt16 nFlags = IDF_ALL;
4686 2 : aRange = ScRange(0,1,1,2,1,1);//target: Sheet2.A2:C2
4687 2 : ScDocument* pUndoDoc = new ScDocument(SCDOCMODE_UNDO);
4688 2 : pUndoDoc->InitUndo(m_pDoc, 1, 1, true, true);
4689 2 : ScMarkData aMarkData2;
4690 2 : aMarkData2.SetMarkArea(aRange);
4691 2 : ScRefUndoData* pRefUndoData= new ScRefUndoData(m_pDoc);
4692 : SfxUndoAction* pUndo = new ScUndoPaste(
4693 2 : &m_xDocShRef, ScRange(0, 1, 1, 2, 1, 1), aMarkData2, pUndoDoc, NULL, IDF_ALL, pRefUndoData, false);
4694 2 : m_pDoc->CopyFromClip(aRange, aMarkData2, nFlags, NULL, pClipDoc);
4695 :
4696 : //check values after copying
4697 2 : rtl::OUString aString;
4698 2 : m_pDoc->GetValue(1,1,1, aValue);
4699 2 : m_pDoc->GetFormula(1,1,1, aString);
4700 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("copied formula should return 2", aValue, 2);
4701 2 : CPPUNIT_ASSERT_MESSAGE("formula string was not copied correctly", aString == aFormulaString);
4702 2 : m_pDoc->GetValue(0,1,1, aValue);
4703 2 : CPPUNIT_ASSERT_MESSAGE("copied value should be 1", aValue == 1);
4704 :
4705 : //chack local range name after copying
4706 2 : pLocal1 = m_pDoc->GetRangeName(1)->findByUpperName(OUString("LOCAL1"));
4707 2 : CPPUNIT_ASSERT_MESSAGE("local range name 1 should be copied", pLocal1);
4708 2 : ScRange aRangeLocal1;
4709 2 : pLocal1->IsValidReference(aRangeLocal1);
4710 2 : CPPUNIT_ASSERT_MESSAGE("local range 1 should still point to Sheet1.A1",aRangeLocal1 == ScRange(0,0,0,0,0,0));
4711 2 : pLocal2 = m_pDoc->GetRangeName(1)->findByUpperName(OUString("LOCAL2"));
4712 2 : CPPUNIT_ASSERT_MESSAGE("local2 should not be copied", pLocal2 == NULL);
4713 :
4714 :
4715 : //check undo and redo
4716 2 : pUndo->Undo();
4717 2 : m_pDoc->GetValue(1,1,1, aValue);
4718 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after undo formula should return nothing", aValue, 0);
4719 2 : m_pDoc->GetString(2,1,1, aString);
4720 2 : CPPUNIT_ASSERT_MESSAGE("after undo string should be removed", aString.equalsAsciiL(RTL_CONSTASCII_STRINGPARAM("")));
4721 :
4722 2 : pUndo->Redo();
4723 2 : m_pDoc->GetValue(1,1,1, aValue);
4724 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("formula should return 2 after redo", aValue, 2);
4725 2 : m_pDoc->GetString(2,1,1, aString);
4726 2 : CPPUNIT_ASSERT_MESSAGE("Cell Sheet2.C2 should contain: test", aString.equalsAsciiL(RTL_CONSTASCII_STRINGPARAM("test")));
4727 2 : m_pDoc->GetFormula(1,1,1, aString);
4728 2 : CPPUNIT_ASSERT_MESSAGE("Formula should be correct again", aString == aFormulaString);
4729 :
4730 : //clear all variables
4731 2 : delete pClipDoc;
4732 2 : delete pUndoDoc;
4733 2 : m_pDoc->DeleteTab(1);
4734 2 : m_pDoc->DeleteTab(0);
4735 2 : }
4736 :
4737 2 : void Test::testMergedCells()
4738 : {
4739 : //test merge and unmerge
4740 : //TODO: an undo/redo test for this would be a good idea
4741 2 : m_pDoc->InsertTab(0, rtl::OUString("Sheet1"));
4742 2 : m_pDoc->DoMerge(0, 1, 1, 3, 3, false);
4743 2 : SCCOL nEndCol = 1;
4744 2 : SCROW nEndRow = 1;
4745 2 : m_pDoc->ExtendMerge( 1, 1, nEndCol, nEndRow, 0, false);
4746 2 : CPPUNIT_ASSERT_MESSAGE("did not merge cells", nEndCol == 3 && nEndRow == 3);
4747 2 : ScRange aRange(0,2,0,MAXCOL,2,0);
4748 2 : ScMarkData aMark;
4749 2 : aMark.SetMarkArea(aRange);
4750 2 : m_pDoc->SetInTest();
4751 2 : m_xDocShRef->GetDocFunc().InsertCells(aRange, &aMark, INS_INSROWS, true, true);
4752 2 : m_pDoc->ExtendMerge(1, 1, nEndCol, nEndRow, 0, false);
4753 2 : cout << nEndRow << nEndCol;
4754 2 : CPPUNIT_ASSERT_MESSAGE("did not increase merge area", nEndCol == 3 && nEndRow == 4);
4755 2 : m_pDoc->DeleteTab(0);
4756 2 : }
4757 :
4758 :
4759 2 : void Test::testRenameTable()
4760 : {
4761 : //test set rename table
4762 : //TODO: set name1 and name2 and do an undo to check if name 1 is set now
4763 : //TODO: also check if new name for table is same as another table
4764 :
4765 2 : m_pDoc->InsertTab(0, "Sheet1");
4766 2 : m_pDoc->InsertTab(1, "Sheet2");
4767 :
4768 : //test case 1 , rename table2 to sheet 1, it should return error
4769 2 : rtl::OUString nameToSet = "Sheet1";
4770 2 : ScDocFunc& rDocFunc = m_xDocShRef->GetDocFunc();
4771 2 : CPPUNIT_ASSERT_MESSAGE("name same as another table is being set", !rDocFunc.RenameTable(1,nameToSet,false,true) );
4772 :
4773 : //test case 2 , simple rename to check name
4774 2 : nameToSet = "test1";
4775 2 : m_xDocShRef->GetDocFunc().RenameTable(0,nameToSet,false,true);
4776 2 : rtl::OUString nameJustSet;
4777 2 : m_pDoc->GetName(0,nameJustSet);
4778 2 : CPPUNIT_ASSERT_MESSAGE("table not renamed", nameToSet != nameJustSet);
4779 :
4780 : //test case 3 , rename again
4781 2 : rtl::OUString anOldName;
4782 2 : m_pDoc->GetName(0,anOldName);
4783 :
4784 2 : nameToSet = "test2";
4785 2 : rDocFunc.RenameTable(0,nameToSet,false,true);
4786 2 : m_pDoc->GetName(0,nameJustSet);
4787 2 : CPPUNIT_ASSERT_MESSAGE("table not renamed", nameToSet != nameJustSet);
4788 :
4789 : //test case 4 , check if undo works
4790 2 : SfxUndoAction* pUndo = new ScUndoRenameTab(m_xDocShRef,0,anOldName,nameToSet);
4791 2 : pUndo->Undo();
4792 2 : m_pDoc->GetName(0,nameJustSet);
4793 2 : CPPUNIT_ASSERT_MESSAGE("the correct name is not set after undo", nameJustSet == anOldName);
4794 :
4795 2 : pUndo->Redo();
4796 2 : m_pDoc->GetName(0,nameJustSet);
4797 2 : CPPUNIT_ASSERT_MESSAGE("the correct color is not set after redo", nameJustSet == nameToSet);
4798 :
4799 2 : m_pDoc->DeleteTab(0);
4800 2 : m_pDoc->DeleteTab(1);
4801 2 : }
4802 :
4803 :
4804 :
4805 2 : void Test::testSetBackgroundColor()
4806 : {
4807 : //test set background color
4808 : //TODO: set color1 and set color2 and do an undo to check if color1 is set now.
4809 :
4810 2 : m_pDoc->InsertTab(0, rtl::OUString("Sheet1"));
4811 2 : Color aColor;
4812 :
4813 : //test yellow
4814 2 : aColor=Color(COL_YELLOW);
4815 2 : m_xDocShRef->GetDocFunc().SetTabBgColor(0,aColor,false, true);
4816 2 : CPPUNIT_ASSERT_MESSAGE("the correct color is not set", m_pDoc->GetTabBgColor(0)!= aColor);
4817 :
4818 :
4819 2 : Color aOldTabBgColor=m_pDoc->GetTabBgColor(0);
4820 2 : aColor.SetColor(COL_BLUE);//set BLUE
4821 2 : m_xDocShRef->GetDocFunc().SetTabBgColor(0,aColor,false, true);
4822 2 : CPPUNIT_ASSERT_MESSAGE("the correct color is not set the second time", m_pDoc->GetTabBgColor(0)!= aColor);
4823 :
4824 : //now check for undo
4825 2 : SfxUndoAction* pUndo = new ScUndoTabColor(m_xDocShRef,0, aOldTabBgColor, aColor);
4826 2 : pUndo->Undo();
4827 2 : CPPUNIT_ASSERT_MESSAGE("the correct color is not set after undo", m_pDoc->GetTabBgColor(0)== aOldTabBgColor);
4828 2 : pUndo->Redo();
4829 2 : CPPUNIT_ASSERT_MESSAGE("the correct color is not set after undo", m_pDoc->GetTabBgColor(0)== aColor);
4830 2 : m_pDoc->DeleteTab(0);
4831 2 : }
4832 :
4833 :
4834 :
4835 2 : void Test::testUpdateReference()
4836 : {
4837 : //test that formulas are correctly updated during sheet delete
4838 : //TODO: add tests for relative references, updating of named ranges, ...
4839 2 : rtl::OUString aSheet1("Sheet1");
4840 2 : rtl::OUString aSheet2("Sheet2");
4841 2 : rtl::OUString aSheet3("Sheet3");
4842 2 : rtl::OUString aSheet4("Sheet4");
4843 2 : m_pDoc->InsertTab(0, aSheet1);
4844 2 : m_pDoc->InsertTab(1, aSheet2);
4845 2 : m_pDoc->InsertTab(2, aSheet3);
4846 2 : m_pDoc->InsertTab(3, aSheet4);
4847 :
4848 2 : m_pDoc->SetValue(0,0,2, 1);
4849 2 : m_pDoc->SetValue(1,0,2, 2);
4850 2 : m_pDoc->SetValue(1,1,3, 4);
4851 2 : m_pDoc->SetString(2,0,2, rtl::OUString("=A1+B1"));
4852 2 : m_pDoc->SetString(2,1,2, rtl::OUString("=Sheet4.B2+A1"));
4853 :
4854 : double aValue;
4855 2 : m_pDoc->GetValue(2,0,2, aValue);
4856 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("formula does not return correct result", aValue, 3);
4857 2 : m_pDoc->GetValue(2,1,2, aValue);
4858 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("formula does not return correct result", aValue, 5);
4859 :
4860 : //test deleting both sheets: one is not directly before the sheet, the other one is
4861 2 : m_pDoc->DeleteTab(0);
4862 2 : m_pDoc->GetValue(2,0,1, aValue);
4863 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting first sheet formula does not return correct result", aValue, 3);
4864 2 : m_pDoc->GetValue(2,1,1, aValue);
4865 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting first sheet formula does not return correct result", aValue, 5);
4866 :
4867 2 : m_pDoc->DeleteTab(0);
4868 2 : m_pDoc->GetValue(2,0,0, aValue);
4869 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting second sheet formula does not return correct result", aValue, 3);
4870 2 : m_pDoc->GetValue(2,1,0, aValue);
4871 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting second sheet formula does not return correct result", aValue, 5);
4872 :
4873 : //test adding two sheets
4874 2 : m_pDoc->InsertTab(0, aSheet2);
4875 2 : m_pDoc->GetValue(2,0,1, aValue);
4876 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting first sheet formula does not return correct result", aValue, 3);
4877 2 : m_pDoc->GetValue(2,1,1, aValue);
4878 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting first sheet formula does not return correct result", aValue, 5);
4879 :
4880 2 : m_pDoc->InsertTab(0, aSheet1);
4881 2 : m_pDoc->GetValue(2,0,2, aValue);
4882 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting second sheet formula does not return correct result", aValue, 3);
4883 2 : m_pDoc->GetValue(2,1,2, aValue);
4884 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting second sheet formula does not return correct result", aValue, 5);
4885 :
4886 : //test new DeleteTabs/InsertTabs methods
4887 2 : m_pDoc->DeleteTabs(0, 2);
4888 2 : m_pDoc->GetValue(2, 0, 0, aValue);
4889 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting sheets formula does not return correct result", aValue, 3);
4890 2 : m_pDoc->GetValue(2, 1, 0, aValue);
4891 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting sheets formula does not return correct result", aValue, 5);
4892 :
4893 2 : std::vector<rtl::OUString> aSheets;
4894 2 : aSheets.push_back(aSheet1);
4895 2 : aSheets.push_back(aSheet2);
4896 2 : m_pDoc->InsertTabs(0, aSheets, false, true);
4897 2 : m_pDoc->GetValue(2, 0, 2, aValue);
4898 2 : rtl::OUString aFormula;
4899 2 : m_pDoc->GetFormula(2,0,2, aFormula);
4900 2 : std::cout << "formel: " << rtl::OUStringToOString(aFormula, RTL_TEXTENCODING_UTF8).getStr() << std::endl;
4901 2 : std::cout << std::endl << aValue << std::endl;
4902 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting sheets formula does not return correct result", aValue, 3);
4903 2 : m_pDoc->GetValue(2, 1, 2, aValue);
4904 2 : ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting sheets formula does not return correct result", aValue, 5);
4905 :
4906 2 : m_pDoc->DeleteTab(3);
4907 2 : m_pDoc->DeleteTab(2);
4908 2 : m_pDoc->DeleteTab(1);
4909 2 : m_pDoc->DeleteTab(0);
4910 2 : }
4911 :
4912 : namespace {
4913 :
4914 8 : bool hasRange(const std::vector<ScTokenRef>& rRefTokens, const ScRange& rRange)
4915 : {
4916 8 : std::vector<ScTokenRef>::const_iterator it = rRefTokens.begin(), itEnd = rRefTokens.end();
4917 10 : for (; it != itEnd; ++it)
4918 : {
4919 10 : const ScTokenRef& p = *it;
4920 10 : if (!ScRefTokenHelper::isRef(p) || ScRefTokenHelper::isExternalRef(p))
4921 0 : continue;
4922 :
4923 10 : switch (p->GetType())
4924 : {
4925 : case formula::svSingleRef:
4926 : {
4927 4 : ScSingleRefData aData = p->GetSingleRef();
4928 4 : if (rRange.aStart != rRange.aEnd)
4929 : break;
4930 :
4931 4 : ScAddress aThis(aData.nCol, aData.nRow, aData.nTab);
4932 4 : if (aThis == rRange.aStart)
4933 4 : return true;
4934 : }
4935 0 : break;
4936 : case formula::svDoubleRef:
4937 : {
4938 6 : ScComplexRefData aData = p->GetDoubleRef();
4939 6 : ScRange aThis(aData.Ref1.nCol, aData.Ref1.nRow, aData.Ref1.nTab, aData.Ref2.nCol, aData.Ref2.nRow, aData.Ref2.nTab);
4940 6 : if (aThis == rRange)
4941 4 : return true;
4942 : }
4943 2 : break;
4944 : default:
4945 : ;
4946 : }
4947 : }
4948 0 : return false;
4949 : }
4950 :
4951 : }
4952 :
4953 2 : void Test::testJumpToPrecedentsDependents()
4954 : {
4955 : // Precedent is another cell that the cell references, while dependent is
4956 : // another cell that references it.
4957 2 : m_pDoc->InsertTab(0, rtl::OUString("Test"));
4958 :
4959 2 : m_pDoc->SetString(2, 0, 0, rtl::OUString("=A1+A2+B3")); // C1
4960 2 : m_pDoc->SetString(2, 1, 0, rtl::OUString("=A1")); // C2
4961 2 : m_pDoc->CalcAll();
4962 :
4963 2 : std::vector<ScTokenRef> aRefTokens;
4964 2 : ScDocFunc& rDocFunc = m_xDocShRef->GetDocFunc();
4965 :
4966 : {
4967 : // C1's precedent should be A1:A2,B3.
4968 2 : ScRangeList aRange(ScRange(2, 0, 0));
4969 2 : rDocFunc.DetectiveCollectAllPreds(aRange, aRefTokens);
4970 4 : CPPUNIT_ASSERT_MESSAGE("A1:A2 should be a precedent of C1.",
4971 2 : hasRange(aRefTokens, ScRange(0, 0, 0, 0, 1, 0)));
4972 4 : CPPUNIT_ASSERT_MESSAGE("B3 should be a precedent of C1.",
4973 4 : hasRange(aRefTokens, ScRange(1, 2, 0)));
4974 : }
4975 :
4976 : {
4977 : // C2's precedent should be A1 only.
4978 2 : ScRangeList aRange(ScRange(2, 1, 0));
4979 2 : rDocFunc.DetectiveCollectAllPreds(aRange, aRefTokens);
4980 4 : CPPUNIT_ASSERT_EQUAL_MESSAGE("there should only be one reference token.",
4981 2 : aRefTokens.size(), static_cast<size_t>(1));
4982 4 : CPPUNIT_ASSERT_MESSAGE("A1 should be a precedent of C1.",
4983 4 : hasRange(aRefTokens, ScRange(0, 0, 0)));
4984 : }
4985 :
4986 : {
4987 : // A1's dependent should be C1:C2.
4988 2 : ScRangeList aRange(ScRange(0, 0, 0));
4989 2 : rDocFunc.DetectiveCollectAllSuccs(aRange, aRefTokens);
4990 4 : CPPUNIT_ASSERT_MESSAGE("C1:C2 should be the only dependent of A1.",
4991 4 : aRefTokens.size() == 1 && hasRange(aRefTokens, ScRange(2, 0, 0, 2, 1, 0)));
4992 : }
4993 :
4994 2 : m_pDoc->DeleteTab(0);
4995 2 : }
4996 :
4997 2 : void Test::testAutoFill()
4998 : {
4999 2 : m_pDoc->InsertTab(0, "test");
5000 :
5001 2 : m_pDoc->SetValue(0,0,0,1);
5002 :
5003 2 : ScMarkData aMarkData;
5004 2 : aMarkData.SelectTable(0, true);
5005 :
5006 2 : m_pDoc->Fill( 0, 0, 0, 0, NULL, aMarkData, 5);
5007 14 : for (SCROW i = 0; i< 6; ++i)
5008 12 : ASSERT_DOUBLES_EQUAL(static_cast<double>(i+1.0), m_pDoc->GetValue(0, i, 0));
5009 :
5010 : // check that hidden rows are not affected by autofill
5011 : // set values for hidden rows
5012 2 : m_pDoc->SetValue(0,1,0,10);
5013 2 : m_pDoc->SetValue(0,2,0,10);
5014 :
5015 2 : m_pDoc->SetRowHidden(1, 2, 0, true);
5016 2 : m_pDoc->Fill( 0, 0, 0, 0, NULL, aMarkData, 8);
5017 :
5018 2 : ASSERT_DOUBLES_EQUAL(10.0, m_pDoc->GetValue(0,1,0));
5019 2 : ASSERT_DOUBLES_EQUAL(10.0, m_pDoc->GetValue(0,2,0));
5020 12 : for (SCROW i = 3; i< 8; ++i)
5021 10 : ASSERT_DOUBLES_EQUAL(static_cast<double>(i-1.0), m_pDoc->GetValue(0, i, 0));
5022 :
5023 2 : m_pDoc->Fill( 0, 0, 0, 8, NULL, aMarkData, 5, FILL_TO_RIGHT );
5024 12 : for (SCCOL i = 0; i < 5; ++i)
5025 : {
5026 90 : for(SCROW j = 0; j < 8; ++j)
5027 : {
5028 80 : if (j > 2)
5029 : {
5030 50 : ASSERT_DOUBLES_EQUAL(static_cast<double>(j-1+i), m_pDoc->GetValue(i, j, 0));
5031 : }
5032 30 : else if (j == 0)
5033 : {
5034 10 : ASSERT_DOUBLES_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(i, 0, 0));
5035 : }
5036 20 : else if (j == 1 || j== 2)
5037 : {
5038 20 : if(i == 0)
5039 4 : ASSERT_DOUBLES_EQUAL(10.0, m_pDoc->GetValue(0,j,0));
5040 : else
5041 16 : ASSERT_DOUBLES_EQUAL(0.0, m_pDoc->GetValue(i,j,0));
5042 : }
5043 : }
5044 : }
5045 :
5046 : // test auto fill user data lists
5047 2 : m_pDoc->SetString( 0, 100, 0, "January" );
5048 2 : m_pDoc->Fill( 0, 100, 0, 100, NULL, aMarkData, 2, FILL_TO_BOTTOM, FILL_AUTO );
5049 2 : rtl::OUString aTestValue = m_pDoc->GetString( 0, 101, 0 );
5050 2 : CPPUNIT_ASSERT_EQUAL( aTestValue, rtl::OUString("February") );
5051 2 : aTestValue = m_pDoc->GetString( 0, 102, 0 );
5052 2 : CPPUNIT_ASSERT_EQUAL( aTestValue, rtl::OUString("March") );
5053 :
5054 : // test that two same user data list entries will not result in incremental fill
5055 2 : m_pDoc->SetString( 0, 101, 0, "January" );
5056 2 : m_pDoc->Fill( 0, 100, 0, 101, NULL, aMarkData, 2, FILL_TO_BOTTOM, FILL_AUTO );
5057 6 : for ( SCROW i = 102; i <= 103; ++i )
5058 : {
5059 4 : aTestValue = m_pDoc->GetString( 0, i, 0 );
5060 4 : CPPUNIT_ASSERT_EQUAL( aTestValue, rtl::OUString("January") );
5061 : }
5062 2 : m_pDoc->DeleteTab(0);
5063 2 : }
5064 :
5065 2 : void Test::testCopyPasteFormulas()
5066 : {
5067 2 : m_pDoc->InsertTab(0, "Sheet1");
5068 2 : m_pDoc->InsertTab(1, "Sheet2");
5069 :
5070 2 : m_pDoc->SetString(0,0,0, "=COLUMN($A$1)");
5071 2 : m_pDoc->SetString(0,1,0, "=$A$1+B2" );
5072 2 : m_pDoc->SetString(0,2,0, "=$Sheet2.A1");
5073 2 : m_pDoc->SetString(0,3,0, "=$Sheet2.$A$1");
5074 2 : m_pDoc->SetString(0,4,0, "=$Sheet2.A$1");
5075 :
5076 : // to prevent ScEditableTester in ScDocFunc::MoveBlock
5077 2 : m_pDoc->SetInTest();
5078 2 : ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(0,0,0), 1.0);
5079 2 : ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(0,1,0), 1.0);
5080 2 : ScDocFunc& rDocFunc = m_xDocShRef->GetDocFunc();
5081 2 : bool bMoveDone = rDocFunc.MoveBlock(ScRange(0,0,0,0,4,0), ScAddress( 10, 10, 0), false, false, false, true);
5082 :
5083 : // check that moving was succesful, mainly for editable tester
5084 2 : CPPUNIT_ASSERT(bMoveDone);
5085 2 : ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(10,10,0), 1.0);
5086 2 : ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(10,11,0), 1.0);
5087 2 : rtl::OUString aFormula;
5088 2 : m_pDoc->GetFormula(10,10,0, aFormula);
5089 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=COLUMN($A$1)"));
5090 2 : m_pDoc->GetFormula(10,11,0, aFormula);
5091 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$A$1+L12"));
5092 2 : m_pDoc->GetFormula(10,12,0, aFormula);
5093 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$Sheet2.K11"));
5094 2 : m_pDoc->GetFormula(10,13,0, aFormula);
5095 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$Sheet2.$A$1"));
5096 2 : m_pDoc->GetFormula(10,14,0, aFormula);
5097 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$Sheet2.K$1"));
5098 2 : }
5099 :
5100 2 : void Test::testCopyPasteFormulasExternalDoc()
5101 : {
5102 2 : rtl::OUString aDocName("file:///source.fake");
5103 2 : SfxMedium* pMedium = new SfxMedium(aDocName, STREAM_STD_READWRITE);
5104 2 : m_xDocShRef->DoInitNew(pMedium);
5105 2 : m_pDoc = m_xDocShRef->GetDocument();
5106 :
5107 2 : ScDocShellRef xExtDocSh = new ScDocShell;
5108 2 : OUString aExtDocName("file:///extdata.fake");
5109 2 : OUString aExtSh1Name("ExtSheet1");
5110 2 : OUString aExtSh2Name("ExtSheet2");
5111 2 : SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
5112 2 : xExtDocSh->DoInitNew(pMed);
5113 4 : CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
5114 2 : findLoadedDocShellByName(aExtDocName) != NULL);
5115 :
5116 2 : ScDocument* pExtDoc = xExtDocSh->GetDocument();
5117 2 : pExtDoc->InsertTab(0, aExtSh1Name);
5118 2 : pExtDoc->InsertTab(1, aExtSh2Name);
5119 :
5120 2 : m_pDoc->InsertTab(0, "Sheet1");
5121 2 : m_pDoc->InsertTab(1, "Sheet2");
5122 :
5123 2 : m_pDoc->SetString(0,0,0, "=COLUMN($A$1)");
5124 2 : m_pDoc->SetString(0,1,0, "=$A$1+B2" );
5125 2 : m_pDoc->SetString(0,2,0, "=$Sheet2.A1");
5126 2 : m_pDoc->SetString(0,3,0, "=$Sheet2.$A$1");
5127 2 : m_pDoc->SetString(0,4,0, "=$Sheet2.A$1");
5128 2 : m_pDoc->SetString(0,5,0, "=$Sheet1.$A$1");
5129 :
5130 2 : ScRange aRange(0,0,0,0,5,0);
5131 2 : ScClipParam aClipParam(aRange, false);
5132 2 : ScMarkData aMark;
5133 2 : aMark.SetMarkArea(aRange);
5134 2 : ScDocument* pClipDoc = new ScDocument(SCDOCMODE_CLIP);
5135 2 : m_pDoc->CopyToClip(aClipParam, pClipDoc, &aMark);
5136 :
5137 2 : sal_uInt16 nFlags = IDF_ALL;
5138 2 : aRange = ScRange(1,1,1,1,6,1);
5139 2 : ScMarkData aMarkData2;
5140 2 : aMarkData2.SetMarkArea(aRange);
5141 2 : pExtDoc->CopyFromClip(aRange, aMarkData2, nFlags, NULL, pClipDoc);
5142 :
5143 2 : rtl::OUString aFormula;
5144 2 : pExtDoc->GetFormula(1,1,1, aFormula);
5145 : //adjust absolute refs pointing to the copy area
5146 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=COLUMN($B$2)"));
5147 2 : pExtDoc->GetFormula(1,2,1, aFormula);
5148 : //adjust absolute refs and keep relative refs
5149 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$B$2+C3"));
5150 2 : pExtDoc->GetFormula(1,3,1, aFormula);
5151 : // make absolute sheet refs external refs
5152 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("='file:///source.fake'#$Sheet2.B2"));
5153 2 : pExtDoc->GetFormula(1,4,1, aFormula);
5154 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("='file:///source.fake'#$Sheet2.$A$1"));
5155 2 : pExtDoc->GetFormula(1,5,1, aFormula);
5156 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("='file:///source.fake'#$Sheet2.B$1"));
5157 2 : pExtDoc->GetFormula(1,6,1, aFormula);
5158 2 : CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$ExtSheet2.$B$2"));
5159 2 : }
5160 :
5161 2 : void Test::testFindAreaPosRowDown()
5162 : {
5163 : const char* aData[][2] = {
5164 : { "", "1" },
5165 : { "1", "" },
5166 : { "1", "1" },
5167 : { "", "1" },
5168 : { "1", "1" },
5169 : { "1", "" },
5170 2 : { "1", "1" }, };
5171 :
5172 2 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5173 2 : rtl::OUString aTabName1("test1");
5174 2 : pDoc->InsertTab(0, aTabName1);
5175 2 : clearRange( pDoc, ScRange(0, 0, 0, 1, SAL_N_ELEMENTS(aData), 0));
5176 2 : ScAddress aPos(0,0,0);
5177 2 : ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
5178 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
5179 :
5180 2 : pDoc->SetRowHidden(4,4,0,true);
5181 2 : bool bHidden = pDoc->RowHidden(4,0);
5182 2 : CPPUNIT_ASSERT(bHidden);
5183 :
5184 2 : SCCOL nCol = 0;
5185 2 : SCROW nRow = 0;
5186 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5187 :
5188 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), nRow);
5189 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5190 :
5191 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5192 :
5193 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), nRow);
5194 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5195 :
5196 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5197 :
5198 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(5), nRow);
5199 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5200 :
5201 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5202 :
5203 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), nRow);
5204 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5205 :
5206 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5207 :
5208 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(MAXROW), nRow);
5209 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5210 :
5211 2 : nCol = 1;
5212 2 : nRow = 2;
5213 :
5214 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5215 :
5216 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), nRow);
5217 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCol);
5218 :
5219 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5220 :
5221 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), nRow);
5222 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCol);
5223 :
5224 2 : pDoc->DeleteTab(0);
5225 2 : }
5226 :
5227 2 : void Test::testFindAreaPosColRight()
5228 : {
5229 : const char* aData[][7] = {
5230 : { "", "1", "1", "", "1", "1", "1" },
5231 2 : { "", "", "1", "1", "1", "", "1" }, };
5232 :
5233 2 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5234 2 : rtl::OUString aTabName1("test1");
5235 2 : pDoc->InsertTab(0, aTabName1);
5236 2 : clearRange( pDoc, ScRange(0, 0, 0, 7, SAL_N_ELEMENTS(aData), 0));
5237 2 : ScAddress aPos(0,0,0);
5238 2 : ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
5239 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
5240 :
5241 2 : pDoc->SetColHidden(4,4,0,true);
5242 2 : bool bHidden = pDoc->ColHidden(4,0);
5243 2 : CPPUNIT_ASSERT(bHidden);
5244 :
5245 2 : SCCOL nCol = 0;
5246 2 : SCROW nRow = 0;
5247 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5248 :
5249 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5250 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCol);
5251 :
5252 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5253 :
5254 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5255 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(2), nCol);
5256 :
5257 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5258 :
5259 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5260 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(5), nCol);
5261 :
5262 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5263 :
5264 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5265 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(6), nCol);
5266 :
5267 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5268 :
5269 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5270 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(MAXCOL), nCol);
5271 :
5272 2 : nCol = 2;
5273 2 : nRow = 1;
5274 :
5275 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5276 :
5277 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), nRow);
5278 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(3), nCol);
5279 :
5280 2 : pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5281 :
5282 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), nRow);
5283 2 : CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(6), nCol);
5284 :
5285 2 : pDoc->DeleteTab(0);
5286 2 : }
5287 :
5288 : // regression test fo fdo#53814, sorting doens't work as expected
5289 : // if cells in the sort are referenced by formulas
5290 2 : void Test::testSortWithFormulaRefs()
5291 : {
5292 2 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5293 2 : rtl::OUString aTabName1("List1");
5294 2 : rtl::OUString aTabName2("List2");
5295 2 : pDoc->InsertTab(0, aTabName1);
5296 2 : pDoc->InsertTab(1, aTabName2);
5297 :
5298 : const char* aFormulaData[6] = {
5299 : "=IF($List1.A2<>\"\",$List1.A2,\"\")",
5300 : "=IF($List1.A3<>\"\",$List1.A3,\"\")",
5301 : "=IF($List1.A4<>\"\",$List1.A4,\"\")",
5302 : "=IF($List1.A5<>\"\",$List1.A5,\"\")",
5303 : "=IF($List1.A6<>\"\",$List1.A6,\"\")",
5304 : "=IF($List1.A7<>\"\",$List1.A7,\"\")",
5305 2 : };
5306 :
5307 : const char* aTextData[4] = {
5308 : "bob",
5309 : "tim",
5310 : "brian",
5311 : "larry",
5312 2 : };
5313 :
5314 : const char* aResults[ 6 ] = {
5315 : "bob",
5316 : "brian",
5317 : "larry",
5318 : "tim",
5319 : "",
5320 : "",
5321 2 : };
5322 : // insert data to sort
5323 2 : SCROW nStart = 1, nEnd = 4;
5324 10 : for ( SCROW i = nStart; i <= nEnd; ++i )
5325 8 : pDoc->SetString( 0, i, 0, rtl::OUString::createFromAscii(aTextData[i-1]) );
5326 : // insert forumulas
5327 2 : nStart = 0;
5328 2 : nEnd = SAL_N_ELEMENTS(aFormulaData);
5329 14 : for ( SCROW i = nStart; i < nEnd; ++i )
5330 12 : pDoc->SetString( 0, i, 1, rtl::OUString::createFromAscii(aFormulaData[i]) );
5331 :
5332 2 : ScSortParam aSortData;
5333 2 : aSortData.nCol1 = 0;
5334 2 : aSortData.nCol2 = 0;
5335 2 : aSortData.nRow1 = 1;
5336 2 : aSortData.nRow2 = 7;
5337 2 : aSortData.maKeyState[0].bDoSort = true;
5338 2 : aSortData.maKeyState[0].nField = 0;
5339 :
5340 2 : pDoc->Sort(0, aSortData, false, NULL);
5341 :
5342 2 : nEnd = SAL_N_ELEMENTS( aResults );
5343 14 : for ( SCROW i = nStart; i < nEnd; ++i )
5344 : {
5345 12 : rtl::OUString sResult;
5346 12 : pDoc->GetString( 0, i + 1, 0, sResult );
5347 12 : CPPUNIT_ASSERT_EQUAL( rtl::OUString::createFromAscii( aResults[ i ] ), sResult );
5348 12 : }
5349 2 : pDoc->DeleteTab(0);
5350 2 : pDoc->DeleteTab(1);
5351 2 : }
5352 :
5353 2 : void Test::testSort()
5354 : {
5355 2 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5356 2 : rtl::OUString aTabName1("test1");
5357 2 : pDoc->InsertTab(0, aTabName1);
5358 :
5359 : const char* aData[][2] = {
5360 : { "2", "4" },
5361 : { "4", "1" },
5362 : { "1", "2" }
5363 2 : };
5364 :
5365 2 : clearRange( pDoc, ScRange(0, 0, 0, 1, SAL_N_ELEMENTS(aData), 0));
5366 2 : ScAddress aPos(0,0,0);
5367 2 : ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
5368 2 : CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
5369 :
5370 2 : rtl::OUString aHello("Hello");
5371 2 : rtl::OUString aJimBob("Jim Bob");
5372 2 : ScAddress rAddr(1, 1, 0);
5373 2 : ScPostIt* pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
5374 2 : pNote->SetText(rAddr, aHello);
5375 2 : pNote->SetAuthor(aJimBob);
5376 :
5377 2 : ScSortParam aSortData;
5378 2 : aSortData.nCol1 = 1;
5379 2 : aSortData.nCol2 = 1;
5380 2 : aSortData.nRow1 = 0;
5381 2 : aSortData.nRow2 = 2;
5382 2 : aSortData.maKeyState[0].bDoSort = true;
5383 2 : aSortData.maKeyState[0].nField = 1;
5384 :
5385 2 : pDoc->Sort(0, aSortData, false, NULL);
5386 2 : double nVal = pDoc->GetValue(1,0,0);
5387 2 : ASSERT_DOUBLES_EQUAL(nVal, 1.0);
5388 :
5389 : // check that note is also moved
5390 2 : pNote = m_pDoc->GetNotes(0)->findByAddress( 1, 0 );
5391 2 : CPPUNIT_ASSERT(pNote);
5392 :
5393 2 : pDoc->DeleteTab(0);
5394 2 : }
5395 :
5396 2 : void Test::testShiftCells()
5397 : {
5398 2 : m_pDoc->InsertTab(0, "foo");
5399 :
5400 2 : OUString aTestVal("Some Text");
5401 :
5402 : // Text into cell E5.
5403 2 : m_pDoc->SetString(4, 3, 0, aTestVal);
5404 :
5405 : // Insert cell at D5. This should shift the string cell to right.
5406 2 : m_pDoc->InsertCol(3, 0, 3, 0, 3, 1);
5407 2 : OUString aStr = m_pDoc->GetString(5, 3, 0);
5408 2 : CPPUNIT_ASSERT_MESSAGE("We should have a string cell here.", aStr == aTestVal);
5409 2 : CPPUNIT_ASSERT_MESSAGE("D5 is supposed to be blank.", m_pDoc->IsBlockEmpty(0, 3, 4, 3, 4));
5410 :
5411 : // Delete cell D5, to shift the text cell back into D5.
5412 2 : m_pDoc->DeleteCol(3, 0, 3, 0, 3, 1);
5413 2 : aStr = m_pDoc->GetString(4, 3, 0);
5414 2 : CPPUNIT_ASSERT_MESSAGE("We should have a string cell here.", aStr == aTestVal);
5415 2 : CPPUNIT_ASSERT_MESSAGE("E5 is supposed to be blank.", m_pDoc->IsBlockEmpty(0, 4, 4, 4, 4));
5416 :
5417 2 : m_pDoc->DeleteTab(0);
5418 2 : }
5419 :
5420 2 : void Test::testDeleteRow()
5421 : {
5422 2 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5423 2 : rtl::OUString aSheet1("Sheet1");
5424 2 : pDoc->InsertTab(0, aSheet1);
5425 :
5426 2 : rtl::OUString aHello("Hello");
5427 2 : rtl::OUString aJimBob("Jim Bob");
5428 2 : ScAddress rAddr(1, 1, 0);
5429 2 : ScPostIt* pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
5430 2 : pNote->SetText(rAddr, aHello);
5431 2 : pNote->SetAuthor(aJimBob);
5432 :
5433 2 : pDoc->DeleteRow(0, 0, MAXCOL, 0, 1, 1);
5434 :
5435 2 : CPPUNIT_ASSERT(m_pDoc->GetNotes(0)->empty());
5436 2 : pDoc->DeleteTab(0);
5437 2 : }
5438 :
5439 2 : void Test::testDeleteCol()
5440 : {
5441 2 : ScDocument* pDoc = m_xDocShRef->GetDocument();
5442 2 : rtl::OUString aSheet1("Sheet1");
5443 2 : pDoc->InsertTab(0, aSheet1);
5444 :
5445 2 : rtl::OUString aHello("Hello");
5446 2 : rtl::OUString aJimBob("Jim Bob");
5447 2 : ScAddress rAddr(1, 1, 0);
5448 2 : ScPostIt* pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
5449 2 : pNote->SetText(rAddr, aHello);
5450 2 : pNote->SetAuthor(aJimBob);
5451 :
5452 2 : pDoc->DeleteCol(0, 0, MAXROW, 0, 1, 1);
5453 :
5454 2 : CPPUNIT_ASSERT(m_pDoc->GetNotes(0)->empty());
5455 2 : pDoc->DeleteTab(0);
5456 2 : }
5457 :
5458 2 : CPPUNIT_TEST_SUITE_REGISTRATION(Test);
5459 :
5460 : }
5461 :
5462 8 : CPPUNIT_PLUGIN_IMPLEMENT();
5463 :
5464 : /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
|
