Impress Remote 1.0.5, tag sdremote-1.0.5
[LibreOffice.git] / sc / qa / unit / ucalc.cxx
blob11ca7fe116969b50092d478b57fbc8a2b0cea0cc
1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 /*
3 * Version: MPL 1.1 / GPLv3+ / LGPLv3+
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/
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.
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.
20 * Contributor(s): Michael Meeks <michael.meeks@novell.com>
21 * Kohei Yoshida <kyoshida@novell.com>
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.
29 #include <sal/config.h>
30 #include <test/bootstrapfixture.hxx>
32 #include <rtl/strbuf.hxx>
33 #include <osl/file.hxx>
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"
54 #include "docsh.hxx"
55 #include "docfunc.hxx"
56 #include "dbdocfun.hxx"
57 #include "funcdesc.hxx"
58 #include "externalrefmgr.hxx"
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"
69 #include "formula/IFunctionDescription.hxx"
71 #include <basegfx/polygon/b2dpolygon.hxx>
72 #include <editeng/boxitem.hxx>
74 #include <svx/svdograf.hxx>
75 #include <svx/svdpage.hxx>
76 #include <svx/svdocirc.hxx>
77 #include <svx/svdopath.hxx>
79 #include <sfx2/docfile.hxx>
81 #include <com/sun/star/sheet/DataPilotFieldOrientation.hpp>
82 #include <com/sun/star/sheet/DataPilotFieldGroupBy.hpp>
83 #include <com/sun/star/sheet/DataPilotFieldReference.hpp>
84 #include <com/sun/star/sheet/DataPilotFieldReferenceType.hpp>
85 #include <com/sun/star/sheet/DataPilotFieldReferenceItemType.hpp>
86 #include <com/sun/star/sheet/GeneralFunction.hpp>
88 #include <iostream>
89 #include <sstream>
90 #include <vector>
92 #define CALC_DEBUG_OUTPUT 0
94 #include "helper/debughelper.hxx"
95 #include "helper/qahelper.hxx"
97 const int indeterminate = 2;
99 using namespace ::com::sun::star;
100 using ::rtl::OUString;
101 using ::rtl::OUStringBuffer;
102 using ::std::cout;
103 using ::std::cerr;
104 using ::std::endl;
105 using ::std::vector;
107 namespace {
109 class Test : public test::BootstrapFixture {
110 public:
111 Test();
113 virtual void setUp();
114 virtual void tearDown();
116 void testCollator();
117 void testRangeList();
118 void testInput();
119 void testCellFunctions();
120 void testCopyToDocument();
122 * Make sure the SHEETS function gets properly updated during sheet
123 * insertion and removal.
125 void testSheetsFunc();
126 void testVolatileFunc();
129 * Basic test for formula dependency tracking.
131 void testFormulaDepTracking();
134 * Another test for formula dependency tracking, inspired by fdo#56278.
136 void testFormulaDepTracking2();
137 void testFuncParam();
138 void testNamedRange();
139 void testCSV();
140 void testMatrix();
141 void testEnterMixedMatrix();
144 * Basic test for pivot tables.
146 void testPivotTable();
149 * Test against unwanted automatic format detection on field names and
150 * field members in pivot tables.
152 void testPivotTableLabels();
155 * Make sure that we set cells displaying date values numeric cells,
156 * rather than text cells. Grouping by date or number functionality
157 * depends on this.
159 void testPivotTableDateLabels();
162 * Test for pivot table's filtering functionality by page fields.
164 void testPivotTableFilters();
167 * Test for pivot table's named source range.
169 void testPivotTableNamedSource();
172 * Test for pivot table cache. Each dimension in the pivot cache stores
173 * only unique values that are sorted in ascending order.
175 void testPivotTableCache();
178 * Test for pivot table containing data fields that reference the same
179 * source field but different functions.
181 void testPivotTableDuplicateDataFields();
183 void testPivotTableNormalGrouping();
184 void testPivotTableNumberGrouping();
185 void testPivotTableDateGrouping();
186 void testPivotTableEmptyRows();
187 void testPivotTableTextNumber();
190 * Test for checking that pivot table treats strings in a case insensitive
191 * manner.
193 void testPivotTableCaseInsensitiveStrings();
196 * Test for pivot table's handling of double-precision numbers that are
197 * very close together.
199 void testPivotTableNumStability();
202 * Test for pivot table that include field with various non-default field
203 * refrences.
205 void testPivotTableFieldReference();
207 void testSheetCopy();
208 void testSheetMove();
209 void testExternalRef();
210 void testExternalRefFunctions();
211 void testDataArea();
212 void testAutofilter();
213 void testCopyPaste();
214 void testMergedCells();
215 void testUpdateReference();
218 * Make sure the sheet streams are invalidated properly.
220 void testStreamValid();
223 * Test built-in cell functions to make sure their categories and order
224 * are correct.
226 void testFunctionLists();
228 void testGraphicsInGroup();
229 void testGraphicsOnSheetMove();
231 void testPostIts();
234 * Test toggling relative/absolute flag of cell and cell range references.
235 * This corresponds with hitting Shift-F4 while the cursor is on a formula
236 * cell.
238 void testToggleRefFlag();
241 * Test to make sure correct precedent / dependent cells are obtained when
242 * preparing to jump to them.
244 void testJumpToPrecedentsDependents();
246 void testSetBackgroundColor();
247 void testRenameTable();
249 void testAutoFill();
250 void testCopyPasteFormulas();
251 void testCopyPasteFormulasExternalDoc();
253 void testFindAreaPosRowDown();
254 void testFindAreaPosColRight();
255 void testSort();
256 void testSortWithFormulaRefs();
257 void testShiftCells();
258 void testDeleteRow();
259 void testDeleteCol();
261 CPPUNIT_TEST_SUITE(Test);
262 CPPUNIT_TEST(testCollator);
263 CPPUNIT_TEST(testRangeList);
264 CPPUNIT_TEST(testInput);
265 CPPUNIT_TEST(testCellFunctions);
266 CPPUNIT_TEST(testCopyToDocument);
267 CPPUNIT_TEST(testSheetsFunc);
268 CPPUNIT_TEST(testVolatileFunc);
269 CPPUNIT_TEST(testFormulaDepTracking);
270 CPPUNIT_TEST(testFormulaDepTracking2);
271 CPPUNIT_TEST(testFuncParam);
272 CPPUNIT_TEST(testNamedRange);
273 CPPUNIT_TEST(testCSV);
274 CPPUNIT_TEST(testMatrix);
275 CPPUNIT_TEST(testEnterMixedMatrix);
276 CPPUNIT_TEST(testPivotTable);
277 CPPUNIT_TEST(testPivotTableLabels);
278 CPPUNIT_TEST(testPivotTableDateLabels);
279 CPPUNIT_TEST(testPivotTableFilters);
280 CPPUNIT_TEST(testPivotTableNamedSource);
281 CPPUNIT_TEST(testPivotTableCache);
282 CPPUNIT_TEST(testPivotTableDuplicateDataFields);
283 CPPUNIT_TEST(testPivotTableNormalGrouping);
284 CPPUNIT_TEST(testPivotTableNumberGrouping);
285 CPPUNIT_TEST(testPivotTableDateGrouping);
286 CPPUNIT_TEST(testPivotTableEmptyRows);
287 CPPUNIT_TEST(testPivotTableTextNumber);
288 CPPUNIT_TEST(testPivotTableCaseInsensitiveStrings);
289 CPPUNIT_TEST(testPivotTableNumStability);
290 CPPUNIT_TEST(testPivotTableFieldReference);
291 CPPUNIT_TEST(testSheetCopy);
292 CPPUNIT_TEST(testSheetMove);
293 CPPUNIT_TEST(testExternalRef);
294 CPPUNIT_TEST(testExternalRefFunctions);
295 CPPUNIT_TEST(testDataArea);
296 CPPUNIT_TEST(testGraphicsInGroup);
297 CPPUNIT_TEST(testGraphicsOnSheetMove);
298 CPPUNIT_TEST(testPostIts);
299 CPPUNIT_TEST(testStreamValid);
300 CPPUNIT_TEST(testFunctionLists);
301 CPPUNIT_TEST(testToggleRefFlag);
302 CPPUNIT_TEST(testAutofilter);
303 CPPUNIT_TEST(testCopyPaste);
304 CPPUNIT_TEST(testMergedCells);
305 CPPUNIT_TEST(testUpdateReference);
306 CPPUNIT_TEST(testJumpToPrecedentsDependents);
307 CPPUNIT_TEST(testSetBackgroundColor);
308 CPPUNIT_TEST(testRenameTable);
309 CPPUNIT_TEST(testAutoFill);
310 CPPUNIT_TEST(testCopyPasteFormulas);
311 CPPUNIT_TEST(testCopyPasteFormulasExternalDoc);
312 CPPUNIT_TEST(testFindAreaPosRowDown);
313 CPPUNIT_TEST(testFindAreaPosColRight);
314 CPPUNIT_TEST(testSort);
315 CPPUNIT_TEST(testSortWithFormulaRefs);
316 CPPUNIT_TEST(testShiftCells);
317 CPPUNIT_TEST(testDeleteRow);
318 CPPUNIT_TEST(testDeleteCol);
319 CPPUNIT_TEST_SUITE_END();
321 private:
322 ScDocument *m_pDoc;
323 ScDocShellRef m_xDocShRef;
326 void clearRange(ScDocument* pDoc, const ScRange& rRange)
328 ScMarkData aMarkData;
329 aMarkData.SetMarkArea(rRange);
330 pDoc->DeleteArea(
331 rRange.aStart.Col(), rRange.aStart.Row(),
332 rRange.aEnd.Col(), rRange.aEnd.Row(), aMarkData, IDF_CONTENTS);
335 void printRange(ScDocument* pDoc, const ScRange& rRange, const char* pCaption)
337 SCROW nRow1 = rRange.aStart.Row(), nRow2 = rRange.aEnd.Row();
338 SCCOL nCol1 = rRange.aStart.Col(), nCol2 = rRange.aEnd.Col();
339 SheetPrinter printer(nRow2 - nRow1 + 1, nCol2 - nCol1 + 1);
340 for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
342 for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
344 rtl::OUString aVal;
345 pDoc->GetString(nCol, nRow, rRange.aStart.Tab(), aVal);
346 printer.set(nRow-nRow1, nCol-nCol1, aVal);
349 printer.print(pCaption);
352 template<size_t _Size>
353 ScRange insertRangeData(ScDocument* pDoc, const ScAddress& rPos, const char* aData[][_Size], size_t nRowCount)
355 for (size_t i = 0; i < _Size; ++i)
357 for (size_t j = 0; j < nRowCount; ++j)
359 SCCOL nCol = i + rPos.Col();
360 SCROW nRow = j + rPos.Row();
361 pDoc->SetString(nCol, nRow, rPos.Tab(), OUString(aData[j][i], strlen(aData[j][i]), RTL_TEXTENCODING_UTF8));
365 ScRange aRange(rPos);
366 aRange.aEnd.SetCol(rPos.Col()+_Size-1);
367 aRange.aEnd.SetRow(rPos.Row()+nRowCount-1);
368 printRange(pDoc, aRange, "Range data content");
369 return aRange;
373 * Temporarily switch on/off auto calculation mode.
375 class AutoCalcSwitch
377 ScDocument* mpDoc;
378 bool mbOldValue;
379 public:
380 AutoCalcSwitch(ScDocument* pDoc, bool bAutoCalc) : mpDoc(pDoc), mbOldValue(pDoc->GetAutoCalc())
382 mpDoc->SetAutoCalc(bAutoCalc);
385 ~AutoCalcSwitch()
387 mpDoc->SetAutoCalc(mbOldValue);
392 * Temporarily set formula grammar.
394 class FormulaGrammarSwitch
396 ScDocument* mpDoc;
397 formula::FormulaGrammar::Grammar meOldGrammar;
398 public:
399 FormulaGrammarSwitch(ScDocument* pDoc, formula::FormulaGrammar::Grammar eGrammar) :
400 mpDoc(pDoc), meOldGrammar(pDoc->GetGrammar())
402 mpDoc->SetGrammar(eGrammar);
405 ~FormulaGrammarSwitch()
407 mpDoc->SetGrammar(meOldGrammar);
411 Test::Test()
412 : m_pDoc(0)
416 void Test::setUp()
418 BootstrapFixture::setUp();
420 ScDLL::Init();
421 m_xDocShRef = new ScDocShell(
422 SFXMODEL_STANDARD |
423 SFXMODEL_DISABLE_EMBEDDED_SCRIPTS |
424 SFXMODEL_DISABLE_DOCUMENT_RECOVERY);
426 m_pDoc = m_xDocShRef->GetDocument();
429 void Test::tearDown()
431 m_xDocShRef.Clear();
432 BootstrapFixture::tearDown();
435 void Test::testCollator()
437 OUString s1("A");
438 OUString s2("B");
439 CollatorWrapper* p = ScGlobal::GetCollator();
440 sal_Int32 nRes = p->compareString(s1, s2);
441 CPPUNIT_ASSERT_MESSAGE("these strings are supposed to be different!", nRes != 0);
444 void Test::testRangeList()
446 m_pDoc->InsertTab(0, "foo");
448 ScRangeList aRL;
449 aRL.Append(ScRange(1,1,0,3,10,0));
450 CPPUNIT_ASSERT_MESSAGE("List should have one range.", aRL.size() == 1);
451 const ScRange* p = aRL[0];
452 CPPUNIT_ASSERT_MESSAGE("Failed to get the range object.", p);
453 CPPUNIT_ASSERT_MESSAGE("Wrong range.", p->aStart == ScAddress(1,1,0) && p->aEnd == ScAddress(3,10,0));
455 // TODO: Add more tests here.
457 m_pDoc->DeleteTab(0);
460 void Test::testInput()
462 rtl::OUString aTabName("foo");
463 CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
464 m_pDoc->InsertTab (0, aTabName));
466 OUString numstr("'10.5");
467 OUString str("'apple'");
468 OUString test;
470 m_pDoc->SetString(0, 0, 0, numstr);
471 m_pDoc->GetString(0, 0, 0, test);
472 bool bTest = test == "10.5";
473 CPPUNIT_ASSERT_MESSAGE("String number should have the first apostrophe stripped.", bTest);
474 m_pDoc->SetString(0, 0, 0, str);
475 m_pDoc->GetString(0, 0, 0, test);
476 bTest = test == "'apple'";
477 CPPUNIT_ASSERT_MESSAGE("Text content should have retained the first apostrophe.", bTest);
479 // Customized string handling policy.
480 ScSetStringParam aParam;
481 aParam.mbDetectNumberFormat = false;
482 aParam.meSetTextNumFormat = ScSetStringParam::Always;
483 aParam.mbHandleApostrophe = false;
484 m_pDoc->SetString(0, 0, 0, "000123", &aParam);
485 m_pDoc->GetString(0, 0, 0, test);
486 CPPUNIT_ASSERT_MESSAGE("Text content should have been treated as string, not number.", test == "000123");
488 m_pDoc->DeleteTab(0);
491 void testFuncSUM(ScDocument* pDoc)
493 double val = 1;
494 double result;
495 pDoc->SetValue (0, 0, 0, val);
496 pDoc->SetValue (0, 1, 0, val);
497 pDoc->SetString (0, 2, 0, rtl::OUString("=SUM(A1:A2)"));
498 pDoc->CalcAll();
499 pDoc->GetValue (0, 2, 0, result);
500 CPPUNIT_ASSERT_MESSAGE ("calculation failed", result == 2.0);
503 void testFuncPRODUCT(ScDocument* pDoc)
505 double val = 1;
506 double result;
507 pDoc->SetValue(0, 0, 0, val);
508 val = 2;
509 pDoc->SetValue(0, 1, 0, val);
510 val = 3;
511 pDoc->SetValue(0, 2, 0, val);
512 pDoc->SetString(0, 3, 0, OUString("=PRODUCT(A1:A3)"));
513 pDoc->CalcAll();
514 pDoc->GetValue(0, 3, 0, result);
515 CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT failed", result == 6.0);
517 pDoc->SetString(0, 4, 0, OUString("=PRODUCT({1;2;3})"));
518 pDoc->CalcAll();
519 pDoc->GetValue(0, 4, 0, result);
520 CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT with inline array failed", result == 6.0);
523 void testFuncN(ScDocument* pDoc)
525 double result;
527 // Clear the area first.
528 clearRange(pDoc, ScRange(0, 0, 0, 1, 20, 0));
530 // Put values to reference.
531 double val = 0;
532 pDoc->SetValue(0, 0, 0, val);
533 pDoc->SetString(0, 2, 0, OUString("Text"));
534 val = 1;
535 pDoc->SetValue(0, 3, 0, val);
536 val = -1;
537 pDoc->SetValue(0, 4, 0, val);
538 val = 12.3;
539 pDoc->SetValue(0, 5, 0, val);
540 pDoc->SetString(0, 6, 0, OUString("'12.3"));
542 // Cell references
543 pDoc->SetString(1, 0, 0, OUString("=N(A1)"));
544 pDoc->SetString(1, 1, 0, OUString("=N(A2)"));
545 pDoc->SetString(1, 2, 0, OUString("=N(A3)"));
546 pDoc->SetString(1, 3, 0, OUString("=N(A4)"));
547 pDoc->SetString(1, 4, 0, OUString("=N(A5)"));
548 pDoc->SetString(1, 5, 0, OUString("=N(A6)"));
549 pDoc->SetString(1, 6, 0, OUString("=N(A9)"));
551 // In-line values
552 pDoc->SetString(1, 7, 0, OUString("=N(0)"));
553 pDoc->SetString(1, 8, 0, OUString("=N(1)"));
554 pDoc->SetString(1, 9, 0, OUString("=N(-1)"));
555 pDoc->SetString(1, 10, 0, OUString("=N(123)"));
556 pDoc->SetString(1, 11, 0, OUString("=N(\"\")"));
557 pDoc->SetString(1, 12, 0, OUString("=N(\"12\")"));
558 pDoc->SetString(1, 13, 0, OUString("=N(\"foo\")"));
560 // Range references
561 pDoc->SetString(2, 2, 0, OUString("=N(A1:A8)"));
562 pDoc->SetString(2, 3, 0, OUString("=N(A1:A8)"));
563 pDoc->SetString(2, 4, 0, OUString("=N(A1:A8)"));
564 pDoc->SetString(2, 5, 0, OUString("=N(A1:A8)"));
566 // Calculate and check the results.
567 pDoc->CalcAll();
568 double checks1[] = {
569 0, 0, 0, 1, -1, 12.3, 0, // cell reference
570 0, 1, -1, 123, 0, 0, 0 // in-line values
572 for (size_t i = 0; i < SAL_N_ELEMENTS(checks1); ++i)
574 pDoc->GetValue(1, i, 0, result);
575 bool bGood = result == checks1[i];
576 if (!bGood)
578 cerr << "row " << (i+1) << ": expected=" << checks1[i] << " actual=" << result << endl;
579 CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
582 double checks2[] = {
583 0, 1, -1, 12.3 // range references
585 for (size_t i = 0; i < SAL_N_ELEMENTS(checks2); ++i)
587 pDoc->GetValue(1, i+2, 0, result);
588 bool bGood = result == checks2[i];
589 if (!bGood)
591 cerr << "row " << (i+2+1) << ": expected=" << checks2[i] << " actual=" << result << endl;
592 CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
597 void testFuncCOUNTIF(ScDocument* pDoc)
599 // COUNTIF (test case adopted from OOo i#36381)
601 // Empty A1:A39 first.
602 clearRange(pDoc, ScRange(0, 0, 0, 0, 40, 0));
604 // Raw data (rows 1 through 9)
605 const char* aData[] = {
606 "1999",
607 "2000",
608 "0",
609 "0",
610 "0",
611 "2002",
612 "2001",
613 "X",
614 "2002"
617 SCROW nRows = SAL_N_ELEMENTS(aData);
618 for (SCROW i = 0; i < nRows; ++i)
619 pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
621 printRange(pDoc, ScRange(0, 0, 0, 0, 8, 0), "data range for COUNTIF");
623 // formulas and results
624 struct {
625 const char* pFormula; double fResult;
626 } aChecks[] = {
627 { "=COUNTIF(A1:A12;1999)", 1 },
628 { "=COUNTIF(A1:A12;2002)", 2 },
629 { "=COUNTIF(A1:A12;1998)", 0 },
630 { "=COUNTIF(A1:A12;\">=1999\")", 5 },
631 { "=COUNTIF(A1:A12;\">1999\")", 4 },
632 { "=COUNTIF(A1:A12;\"<2001\")", 5 },
633 { "=COUNTIF(A1:A12;\">0\")", 5 },
634 { "=COUNTIF(A1:A12;\">=0\")", 8 },
635 { "=COUNTIF(A1:A12;0)", 3 },
636 { "=COUNTIF(A1:A12;\"X\")", 1 },
637 { "=COUNTIF(A1:A12;)", 3 }
640 nRows = SAL_N_ELEMENTS(aChecks);
641 for (SCROW i = 0; i < nRows; ++i)
643 SCROW nRow = 20 + i;
644 pDoc->SetString(0, nRow, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
646 pDoc->CalcAll();
648 for (SCROW i = 0; i < nRows; ++i)
650 double result;
651 SCROW nRow = 20 + i;
652 pDoc->GetValue(0, nRow, 0, result);
653 bool bGood = result == aChecks[i].fResult;
654 if (!bGood)
656 cerr << "row " << (nRow+1) << ": formula" << aChecks[i].pFormula
657 << " expected=" << aChecks[i].fResult << " actual=" << result << endl;
658 CPPUNIT_ASSERT_MESSAGE("Unexpected result for COUNTIF", false);
662 // Don't count empty strings when searching for a number.
664 // Clear A1:A2.
665 clearRange(pDoc, ScRange(0, 0, 0, 0, 1, 0));
667 pDoc->SetString(0, 0, 0, rtl::OUString("=\"\""));
668 pDoc->SetString(0, 1, 0, rtl::OUString("=COUNTIF(A1;1)"));
669 pDoc->CalcAll();
671 double result = pDoc->GetValue(0, 1, 0);
672 CPPUNIT_ASSERT_MESSAGE("We shouldn't count empty string as valid number.", result == 0.0);
675 void testFuncIFERROR(ScDocument* pDoc)
677 // IFERROR/IFNA (fdo#56124)
679 // Empty A1:A39 first.
680 clearRange(pDoc, ScRange(0, 0, 0, 0, 40, 0));
682 // Raw data (rows 1 through 12)
683 const char* aData[] = {
684 "1",
685 "e",
686 "=SQRT(4)",
687 "=SQRT(-2)",
688 "=A4",
689 "=1/0",
690 "=NA()",
691 "bar",
692 "4",
693 "gee",
694 "=1/0",
695 "23"
698 SCROW nRows = SAL_N_ELEMENTS(aData);
699 for (SCROW i = 0; i < nRows; ++i)
700 pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
702 printRange(pDoc, ScRange(0, 0, 0, 0, nRows-1, 0), "data range for IFERROR/IFNA");
704 // formulas and results
705 struct {
706 const char* pFormula; const char* pResult;
707 } aChecks[] = {
708 { "=IFERROR(A1;9)", "1" },
709 { "=IFERROR(A2;9)", "e" },
710 { "=IFERROR(A3;9)", "2" },
711 { "=IFERROR(A4;-7)", "-7" },
712 { "=IFERROR(A5;-7)", "-7" },
713 { "=IFERROR(A6;-7)", "-7" },
714 { "=IFERROR(A7;-7)", "-7" },
715 { "=IFNA(A6;9)", "#DIV/0!" },
716 { "=IFNA(A7;-7)", "-7" },
717 { "=IFNA(VLOOKUP(\"4\";A8:A10;1;0);-2)", "4" },
718 { "=IFNA(VLOOKUP(\"fop\";A8:A10;1;0);-2)", "-2" },
719 { "{=IFERROR(3*A11:A12;1998)}[0]", "1998" }, // um.. this is not the correct way to insert a
720 { "{=IFERROR(3*A11:A12;1998)}[1]", "69" } // matrix formula, just a place holder, see below
723 nRows = SAL_N_ELEMENTS(aChecks);
724 for (SCROW i = 0; i < nRows-2; ++i)
726 SCROW nRow = 20 + i;
727 pDoc->SetString(0, nRow, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
730 // Create a matrix range in last two rows of the range above, actual data
731 // of the placeholders.
732 ScMarkData aMark;
733 aMark.SelectOneTable(0);
734 pDoc->InsertMatrixFormula(0, 20 + nRows-2, 0, 20 + nRows-1, aMark, "=IFERROR(3*A11:A12;1998)", NULL);
736 pDoc->CalcAll();
738 for (SCROW i = 0; i < nRows; ++i)
740 rtl::OUString aResult;
741 SCROW nRow = 20 + i;
742 pDoc->GetString(0, nRow, 0, aResult);
743 CPPUNIT_ASSERT_EQUAL_MESSAGE(
744 aChecks[i].pFormula, OUString::createFromAscii( aChecks[i].pResult), aResult);
748 void testFuncVLOOKUP(ScDocument* pDoc)
750 // VLOOKUP
752 // Clear A1:F40.
753 clearRange(pDoc, ScRange(0, 0, 0, 5, 39, 0));
755 // Raw data
756 const char* aData[][2] = {
757 { "Key", "Val" },
758 { "10", "3" },
759 { "20", "4" },
760 { "30", "5" },
761 { "40", "6" },
762 { "50", "7" },
763 { "60", "8" },
764 { "70", "9" },
765 { "B", "10" },
766 { "B", "11" },
767 { "C", "12" },
768 { "D", "13" },
769 { "E", "14" },
770 { "F", "15" },
771 { 0, 0 } // terminator
774 // Insert raw data into A1:B14.
775 for (SCROW i = 0; aData[i][0]; ++i)
777 pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i][0]));
778 pDoc->SetString(1, i, 0, rtl::OUString::createFromAscii(aData[i][1]));
781 printRange(pDoc, ScRange(0, 0, 0, 1, 13, 0), "raw data for VLOOKUP");
783 // Formula data
784 struct {
785 const char* pLookup; const char* pFormula; const char* pRes;
786 } aChecks[] = {
787 { "Lookup", "Formula", 0 },
788 { "12", "=VLOOKUP(D2;A2:B14;2;1)", "3" },
789 { "29", "=VLOOKUP(D3;A2:B14;2;1)", "4" },
790 { "31", "=VLOOKUP(D4;A2:B14;2;1)", "5" },
791 { "45", "=VLOOKUP(D5;A2:B14;2;1)", "6" },
792 { "56", "=VLOOKUP(D6;A2:B14;2;1)", "7" },
793 { "65", "=VLOOKUP(D7;A2:B14;2;1)", "8" },
794 { "78", "=VLOOKUP(D8;A2:B14;2;1)", "9" },
795 { "Andy", "=VLOOKUP(D9;A2:B14;2;1)", "#N/A" },
796 { "Bruce", "=VLOOKUP(D10;A2:B14;2;1)", "11" },
797 { "Charlie", "=VLOOKUP(D11;A2:B14;2;1)", "12" },
798 { "David", "=VLOOKUP(D12;A2:B14;2;1)", "13" },
799 { "Edward", "=VLOOKUP(D13;A2:B14;2;1)", "14" },
800 { "Frank", "=VLOOKUP(D14;A2:B14;2;1)", "15" },
801 { "Henry", "=VLOOKUP(D15;A2:B14;2;1)", "15" },
802 { "100", "=VLOOKUP(D16;A2:B14;2;1)", "9" },
803 { "1000", "=VLOOKUP(D17;A2:B14;2;1)", "9" },
804 { "Zena", "=VLOOKUP(D18;A2:B14;2;1)", "15" }
807 // Insert formula data into D1:E18.
808 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
810 pDoc->SetString(3, i, 0, rtl::OUString::createFromAscii(aChecks[i].pLookup));
811 pDoc->SetString(4, i, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
813 pDoc->CalcAll();
814 printRange(pDoc, ScRange(3, 0, 0, 4, 17, 0), "formula data for VLOOKUP");
816 // Verify results.
817 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
819 if (i == 0)
820 // Skip the header row.
821 continue;
823 rtl::OUString aRes;
824 pDoc->GetString(4, i, 0, aRes);
825 bool bGood = aRes.equalsAscii(aChecks[i].pRes);
826 if (!bGood)
828 cerr << "row " << (i+1) << ": lookup value='" << aChecks[i].pLookup
829 << "' expected='" << aChecks[i].pRes << "' actual='" << aRes << "'" << endl;
830 CPPUNIT_ASSERT_MESSAGE("Unexpected result for VLOOKUP", false);
835 struct NumStrCheck {
836 double fVal;
837 const char* pRes;
840 struct StrStrCheck {
841 const char* pVal;
842 const char* pRes;
845 template<size_t _DataSize, size_t _FormulaSize, int _Type>
846 void runTestMATCH(ScDocument* pDoc, const char* aData[_DataSize], StrStrCheck aChecks[_FormulaSize])
848 size_t nDataSize = _DataSize;
849 for (size_t i = 0; i < nDataSize; ++i)
850 pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
852 for (size_t i = 0; i < _FormulaSize; ++i)
854 pDoc->SetString(1, i, 0, rtl::OUString::createFromAscii(aChecks[i].pVal));
856 rtl::OUStringBuffer aBuf;
857 aBuf.appendAscii("=MATCH(B");
858 aBuf.append(static_cast<sal_Int32>(i+1));
859 aBuf.appendAscii(";A1:A");
860 aBuf.append(static_cast<sal_Int32>(nDataSize));
861 aBuf.appendAscii(";");
862 aBuf.append(static_cast<sal_Int32>(_Type));
863 aBuf.appendAscii(")");
864 rtl::OUString aFormula = aBuf.makeStringAndClear();
865 pDoc->SetString(2, i, 0, aFormula);
868 pDoc->CalcAll();
869 printRange(pDoc, ScRange(0, 0, 0, 2, _FormulaSize-1, 0), "MATCH");
871 // verify the results.
872 for (size_t i = 0; i < _FormulaSize; ++i)
874 rtl::OUString aStr;
875 pDoc->GetString(2, i, 0, aStr);
876 if (!aStr.equalsAscii(aChecks[i].pRes))
878 cerr << "row " << (i+1) << ": expected='" << aChecks[i].pRes << "' actual='" << aStr << "'"
879 << " criterion='" << aChecks[i].pVal << "'" << endl;
880 CPPUNIT_ASSERT_MESSAGE("Unexpected result for MATCH", false);
885 void testFuncMATCH(ScDocument* pDoc)
887 clearRange(pDoc, ScRange(0, 0, 0, 4, 40, 0));
889 // Ascending in-exact match
891 // data range (A1:A9)
892 const char* aData[] = {
893 "1",
894 "2",
895 "3",
896 "4",
897 "5",
898 "6",
899 "7",
900 "8",
901 "9",
902 "B",
903 "B",
904 "C",
907 // formula (B1:C12)
908 StrStrCheck aChecks[] = {
909 { "0.8", "#N/A" },
910 { "1.2", "1" },
911 { "2.3", "2" },
912 { "3.9", "3" },
913 { "4.1", "4" },
914 { "5.99", "5" },
915 { "6.1", "6" },
916 { "7.2", "7" },
917 { "8.569", "8" },
918 { "9.59", "9" },
919 { "10", "9" },
920 { "100", "9" },
921 { "Andy", "#N/A" },
922 { "Bruce", "11" },
923 { "Charlie", "12" }
926 runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),1>(pDoc, aData, aChecks);
930 // Descending in-exact match
932 // data range (A1:A9)
933 const char* aData[] = {
934 "D",
935 "C",
936 "B",
937 "9",
938 "8",
939 "7",
940 "6",
941 "5",
942 "4",
943 "3",
944 "2",
948 // formula (B1:C12)
949 StrStrCheck aChecks[] = {
950 { "10", "#N/A" },
951 { "8.9", "4" },
952 { "7.8", "5" },
953 { "6.7", "6" },
954 { "5.5", "7" },
955 { "4.6", "8" },
956 { "3.3", "9" },
957 { "2.2", "10" },
958 { "1.1", "11" },
959 { "0.8", "12" },
960 { "0", "12" },
961 { "-2", "12" },
962 { "Andy", "3" },
963 { "Bruce", "2" },
964 { "Charlie", "1" },
965 { "David", "#N/A" }
968 runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),-1>(pDoc, aData, aChecks);
972 void testFuncCELL(ScDocument* pDoc)
974 clearRange(pDoc, ScRange(0, 0, 0, 2, 20, 0)); // Clear A1:C21.
977 const char* pContent = "Some random text";
978 pDoc->SetString(2, 9, 0, rtl::OUString::createFromAscii(pContent)); // Set this value to C10.
979 double val = 1.2;
980 pDoc->SetValue(2, 0, 0, val); // Set numeric value to C1;
982 // We don't test: FILENAME, FORMAT, WIDTH, PROTECT, PREFIX
983 StrStrCheck aChecks[] = {
984 { "=CELL(\"COL\";C10)", "3" },
985 { "=CELL(\"ROW\";C10)", "10" },
986 { "=CELL(\"SHEET\";C10)", "1" },
987 { "=CELL(\"ADDRESS\";C10)", "$C$10" },
988 { "=CELL(\"CONTENTS\";C10)", pContent },
989 { "=CELL(\"COLOR\";C10)", "0" },
990 { "=CELL(\"TYPE\";C9)", "b" },
991 { "=CELL(\"TYPE\";C10)", "l" },
992 { "=CELL(\"TYPE\";C1)", "v" },
993 { "=CELL(\"PARENTHESES\";C10)", "0" }
996 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
997 pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aChecks[i].pVal));
998 pDoc->CalcAll();
1000 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1002 rtl::OUString aVal = pDoc->GetString(0, i, 0);
1003 CPPUNIT_ASSERT_MESSAGE("Unexpected result for CELL", aVal.equalsAscii(aChecks[i].pRes));
1008 /** See also test case document fdo#44456 sheet cpearson */
1009 void testFuncDATEDIF( ScDocument* pDoc )
1011 const char* aData[][5] = {
1012 { "2007-01-01", "2007-01-10", "d", "9", "=DATEDIF(A1;B1;C1)" } ,
1013 { "2007-01-01", "2007-01-31", "m", "0", "=DATEDIF(A2;B2;C2)" } ,
1014 { "2007-01-01", "2007-02-01", "m", "1", "=DATEDIF(A3;B3;C3)" } ,
1015 { "2007-01-01", "2007-02-28", "m", "1", "=DATEDIF(A4;B4;C4)" } ,
1016 { "2007-01-01", "2007-12-31", "d", "364", "=DATEDIF(A5;B5;C5)" } ,
1017 { "2007-01-01", "2007-01-31", "y", "0", "=DATEDIF(A6;B6;C6)" } ,
1018 { "2007-01-01", "2008-07-01", "d", "547", "=DATEDIF(A7;B7;C7)" } ,
1019 { "2007-01-01", "2008-07-01", "m", "18", "=DATEDIF(A8;B8;C8)" } ,
1020 { "2007-01-01", "2008-07-01", "ym", "6", "=DATEDIF(A9;B9;C9)" } ,
1021 { "2007-01-01", "2008-07-01", "yd", "182", "=DATEDIF(A10;B10;C10)" } ,
1022 { "2008-01-01", "2009-07-01", "yd", "181", "=DATEDIF(A11;B11;C11)" } ,
1023 { "2007-01-01", "2007-01-31", "md", "30", "=DATEDIF(A12;B12;C12)" } ,
1024 { "2007-02-01", "2009-03-01", "md", "0", "=DATEDIF(A13;B13;C13)" } ,
1025 { "2008-02-01", "2009-03-01", "md", "0", "=DATEDIF(A14;B14;C14)" } ,
1026 { "2007-01-02", "2007-01-01", "md", "Err:502", "=DATEDIF(A15;B15;C15)" } // fail date1 > date2
1029 clearRange( pDoc, ScRange(0, 0, 0, 4, SAL_N_ELEMENTS(aData), 0));
1030 ScAddress aPos(0,0,0);
1031 ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
1032 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
1034 pDoc->CalcAll();
1036 for (size_t i = 0; i < SAL_N_ELEMENTS(aData); ++i)
1038 rtl::OUString aVal = pDoc->GetString( 4, i, 0);
1039 //std::cout << "row "<< i << ": " << rtl::OUStringToOString( aVal, RTL_TEXTENCODING_UTF8).getStr() << ", expected " << aData[i][3] << std::endl;
1040 CPPUNIT_ASSERT_MESSAGE("Unexpected result for DATEDIF", aVal.equalsAscii( aData[i][3]));
1044 void testFuncINDIRECT(ScDocument* pDoc)
1046 clearRange(pDoc, ScRange(0, 0, 0, 0, 10, 0)); // Clear A1:A11
1047 rtl::OUString aTabName;
1048 bool bGood = pDoc->GetName(0, aTabName);
1049 CPPUNIT_ASSERT_MESSAGE("failed to get sheet name.", bGood);
1051 rtl::OUString aTest = "Test", aRefErr = "#REF!";
1052 pDoc->SetString(0, 10, 0, aTest);
1053 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", pDoc->GetString(0,10,0) == aTest);
1055 rtl::OUString aPrefix = "=INDIRECT(\"";
1057 rtl::OUString aFormula = aPrefix + aTabName + ".A11\")"; // Calc A1
1058 pDoc->SetString(0, 0, 0, aFormula);
1059 aFormula = aPrefix + aTabName + "!A11\")"; // Excel A1
1060 pDoc->SetString(0, 1, 0, aFormula);
1061 aFormula = aPrefix + aTabName + "!R11C1\")"; // Excel R1C1
1062 pDoc->SetString(0, 2, 0, aFormula);
1063 aFormula = aPrefix + aTabName + "!R11C1\";0)"; // Excel R1C1 (forced)
1064 pDoc->SetString(0, 3, 0, aFormula);
1066 pDoc->CalcAll();
1068 // Default is to use the current formula syntax, which is Calc A1.
1069 const rtl::OUString* aChecks[] = {
1070 &aTest, &aRefErr, &aRefErr, &aTest
1073 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1075 rtl::OUString aVal = pDoc->GetString(0, i, 0);
1076 CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
1080 ScCalcConfig aConfig;
1081 aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_OOO;
1082 ScInterpreter::SetGlobalConfig(aConfig);
1083 pDoc->CalcAll();
1085 // Explicit Calc A1 syntax
1086 const rtl::OUString* aChecks[] = {
1087 &aTest, &aRefErr, &aRefErr, &aTest
1090 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1092 rtl::OUString aVal = pDoc->GetString(0, i, 0);
1093 CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
1097 aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_A1;
1098 ScInterpreter::SetGlobalConfig(aConfig);
1099 pDoc->CalcAll();
1101 // Excel A1 syntax
1102 const rtl::OUString* aChecks[] = {
1103 &aRefErr, &aTest, &aRefErr, &aTest
1106 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1108 rtl::OUString aVal = pDoc->GetString(0, i, 0);
1109 CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
1113 aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_R1C1;
1114 ScInterpreter::SetGlobalConfig(aConfig);
1115 pDoc->CalcAll();
1117 // Excel R1C1 syntax
1118 const rtl::OUString* aChecks[] = {
1119 &aRefErr, &aRefErr, &aTest, &aTest
1122 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1124 rtl::OUString aVal = pDoc->GetString(0, i, 0);
1125 CPPUNIT_ASSERT_MESSAGE("Wrong value!", aVal == *aChecks[i]);
1130 void Test::testCellFunctions()
1132 rtl::OUString aTabName("foo");
1133 CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1134 m_pDoc->InsertTab (0, aTabName));
1136 testFuncSUM(m_pDoc);
1137 testFuncPRODUCT(m_pDoc);
1138 testFuncN(m_pDoc);
1139 testFuncCOUNTIF(m_pDoc);
1140 testFuncIFERROR(m_pDoc);
1141 testFuncVLOOKUP(m_pDoc);
1142 testFuncMATCH(m_pDoc);
1143 testFuncCELL(m_pDoc);
1144 testFuncDATEDIF(m_pDoc);
1145 testFuncINDIRECT(m_pDoc);
1147 m_pDoc->DeleteTab(0);
1150 void Test::testCopyToDocument()
1152 CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet", m_pDoc->InsertTab (0, "src"));
1154 m_pDoc->SetString(0, 0, 0, "Header");
1155 m_pDoc->SetString(0, 1, 0, "1");
1156 m_pDoc->SetString(0, 2, 0, "2");
1157 m_pDoc->SetString(0, 3, 0, "3");
1158 m_pDoc->SetString(0, 4, 0, "=4/2");
1159 m_pDoc->CalcAll();
1161 // Copy statically to another document.
1163 ScDocument aDestDoc(SCDOCMODE_DOCUMENT);
1164 aDestDoc.InsertTab(0, "src");
1165 m_pDoc->CopyStaticToDocument(ScRange(0,1,0,0,3,0), 0, &aDestDoc); // Copy A2:A4
1166 m_pDoc->CopyStaticToDocument(ScAddress(0,0,0), 0, &aDestDoc); // Copy A1
1167 m_pDoc->CopyStaticToDocument(ScRange(0,4,0,0,7,0), 0, &aDestDoc); // Copy A5:A8
1169 CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(0,0,0), aDestDoc.GetString(0,0,0));
1170 CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(0,1,0), aDestDoc.GetString(0,1,0));
1171 CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(0,2,0), aDestDoc.GetString(0,2,0));
1172 CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(0,3,0), aDestDoc.GetString(0,3,0));
1173 CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(0,4,0), aDestDoc.GetString(0,4,0));
1175 m_pDoc->DeleteTab(0);
1178 void Test::testSheetsFunc()
1180 rtl::OUString aTabName1("test1");
1181 rtl::OUString aTabName2("test2");
1182 CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1183 m_pDoc->InsertTab (SC_TAB_APPEND, aTabName1));
1185 m_pDoc->SetString(0, 0, 0, OUString("=SHEETS()"));
1186 m_pDoc->CalcFormulaTree(false, false);
1187 double original;
1188 m_pDoc->GetValue(0, 0, 0, original);
1190 CPPUNIT_ASSERT_MESSAGE("result of SHEETS() should equal the number of sheets, but doesn't.",
1191 static_cast<SCTAB>(original) == m_pDoc->GetTableCount());
1193 CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1194 m_pDoc->InsertTab (SC_TAB_APPEND, aTabName2));
1196 double modified;
1197 m_pDoc->GetValue(0, 0, 0, modified);
1198 CPPUNIT_ASSERT_MESSAGE("result of SHEETS() did not get updated after sheet insertion.",
1199 modified - original == 1.0);
1201 SCTAB nTabCount = m_pDoc->GetTableCount();
1202 m_pDoc->DeleteTab(--nTabCount);
1204 m_pDoc->GetValue(0, 0, 0, modified);
1205 CPPUNIT_ASSERT_MESSAGE("result of SHEETS() did not get updated after sheet removal.",
1206 modified - original == 0.0);
1208 m_pDoc->DeleteTab(--nTabCount);
1211 void Test::testVolatileFunc()
1213 rtl::OUString aTabName("foo");
1214 CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1215 m_pDoc->InsertTab (0, aTabName));
1217 double val = 1;
1218 m_pDoc->SetValue(0, 0, 0, val);
1219 m_pDoc->SetString(0, 1, 0, OUString("=IF(A1>0;NOW();0"));
1220 double now1;
1221 m_pDoc->GetValue(0, 1, 0, now1);
1222 CPPUNIT_ASSERT_MESSAGE("Value of NOW() should be positive.", now1 > 0.0);
1224 val = 0;
1225 m_pDoc->SetValue(0, 0, 0, val);
1226 m_pDoc->CalcFormulaTree(false, false);
1227 double zero;
1228 m_pDoc->GetValue(0, 1, 0, zero);
1229 CPPUNIT_ASSERT_MESSAGE("Result should equal the 3rd parameter of IF, which is zero.", zero == 0.0);
1231 val = 1;
1232 m_pDoc->SetValue(0, 0, 0, val);
1233 m_pDoc->CalcFormulaTree(false, false);
1234 double now2;
1235 m_pDoc->GetValue(0, 1, 0, now2);
1236 CPPUNIT_ASSERT_MESSAGE("Result should be the value of NOW() again.", (now2 - now1) >= 0.0);
1238 m_pDoc->DeleteTab(0);
1241 void Test::testFormulaDepTracking()
1243 CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet", m_pDoc->InsertTab (0, "foo"));
1245 AutoCalcSwitch aACSwitch(m_pDoc, true); // turn on auto calculation.
1247 // B2 listens on D2.
1248 m_pDoc->SetString(1, 1, 0, "=D2");
1249 double val = -999.0; // dummy initial value
1250 m_pDoc->GetValue(1, 1, 0, val);
1251 CPPUNIT_ASSERT_MESSAGE("Referencing an empty cell should yield zero.", val == 0.0);
1253 // Changing the value of D2 should trigger recalculation of B2.
1254 m_pDoc->SetValue(3, 1, 0, 1.1);
1255 m_pDoc->GetValue(1, 1, 0, val);
1256 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on value change.", val == 1.1);
1258 // And again.
1259 m_pDoc->SetValue(3, 1, 0, 2.2);
1260 m_pDoc->GetValue(1, 1, 0, val);
1261 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on value change.", val == 2.2);
1263 clearRange(m_pDoc, ScRange(0, 0, 0, 10, 10, 0));
1265 // Now, let's test the range dependency tracking.
1267 // B2 listens on D2:E6.
1268 m_pDoc->SetString(1, 1, 0, "=SUM(D2:E6)");
1269 m_pDoc->GetValue(1, 1, 0, val);
1270 CPPUNIT_ASSERT_MESSAGE("Summing an empty range should yield zero.", val == 0.0);
1272 // Set value to E3. This should trigger recalc on B2.
1273 m_pDoc->SetValue(4, 2, 0, 2.4);
1274 m_pDoc->GetValue(1, 1, 0, val);
1275 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", val == 2.4);
1277 // Set value to D5 to trigger recalc again. Note that this causes an
1278 // addition of 1.2 + 2.4 which is subject to binary floating point
1279 // rounding error. We need to use approxEqual to assess its value.
1281 m_pDoc->SetValue(3, 4, 0, 1.2);
1282 m_pDoc->GetValue(1, 1, 0, val);
1283 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 3.6));
1285 // Change the value of D2 (boundary case).
1286 m_pDoc->SetValue(3, 1, 0, 1.0);
1287 m_pDoc->GetValue(1, 1, 0, val);
1288 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 4.6));
1290 // Change the value of E6 (another boundary case).
1291 m_pDoc->SetValue(4, 5, 0, 2.0);
1292 m_pDoc->GetValue(1, 1, 0, val);
1293 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 6.6));
1295 // Change the value of D6 (another boundary case).
1296 m_pDoc->SetValue(3, 5, 0, 3.0);
1297 m_pDoc->GetValue(1, 1, 0, val);
1298 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 9.6));
1300 // Change the value of E2 (another boundary case).
1301 m_pDoc->SetValue(4, 1, 0, 0.4);
1302 m_pDoc->GetValue(1, 1, 0, val);
1303 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 10.0));
1305 // Change the existing non-empty value cell (E2).
1306 m_pDoc->SetValue(4, 1, 0, 2.4);
1307 m_pDoc->GetValue(1, 1, 0, val);
1308 CPPUNIT_ASSERT_MESSAGE("Failed to recalculate on single value change.", rtl::math::approxEqual(val, 12.0));
1310 clearRange(m_pDoc, ScRange(0, 0, 0, 10, 10, 0));
1312 // Now, column-based dependency tracking. We now switch to the R1C1
1313 // syntax which is easier to use for repeated relative references.
1315 FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1317 val = 0.0;
1318 for (SCROW nRow = 1; nRow <= 9; ++nRow)
1320 // Static value in column 1.
1321 m_pDoc->SetValue(0, nRow, 0, ++val);
1323 // Formula in column 2 that references cell to the left.
1324 m_pDoc->SetString(1, nRow, 0, "=RC[-1]");
1326 // Formula in column 3 that references cell to the left.
1327 m_pDoc->SetString(2, nRow, 0, "=RC[-1]*2");
1330 // Check formula values.
1331 val = 0.0;
1332 for (SCROW nRow = 1; nRow <= 9; ++nRow)
1334 ++val;
1335 CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(1, nRow, 0) == val);
1336 CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(2, nRow, 0) == val*2.0);
1339 // Intentionally insert a formula in column 1. This will break column 1's
1340 // uniformity of consisting only of static value cells.
1341 m_pDoc->SetString(0, 4, 0, "=R2C3");
1342 CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(0, 4, 0) == 2.0);
1343 CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(1, 4, 0) == 2.0);
1344 CPPUNIT_ASSERT_MESSAGE("Unexpected formula value.", m_pDoc->GetValue(2, 4, 0) == 4.0);
1346 m_pDoc->DeleteTab(0);
1349 void Test::testFormulaDepTracking2()
1351 CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet", m_pDoc->InsertTab (0, "foo"));
1353 AutoCalcSwitch aACSwitch(m_pDoc, true); // turn on auto calculation.
1355 double val = 2.0;
1356 m_pDoc->SetValue(0, 0, 0, val);
1357 val = 4.0;
1358 m_pDoc->SetValue(1, 0, 0, val);
1359 val = 5.0;
1360 m_pDoc->SetValue(0, 1, 0, val);
1361 m_pDoc->SetString(2, 0, 0, "=A1/B1");
1362 m_pDoc->SetString(1, 1, 0, "=B1*C1");
1364 CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(1, 1, 0)); // B2 should equal 2.
1366 clearRange(m_pDoc, ScAddress(2, 0, 0)); // Delete C1.
1368 CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(1, 1, 0)); // B2 should now equal 0.
1370 m_pDoc->DeleteTab(0);
1373 void Test::testFuncParam()
1375 rtl::OUString aTabName("foo");
1376 CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1377 m_pDoc->InsertTab (0, aTabName));
1379 // First, the normal case, with no missing parameters.
1380 m_pDoc->SetString(0, 0, 0, OUString("=AVERAGE(1;2;3)"));
1381 m_pDoc->CalcFormulaTree(false, false);
1382 double val;
1383 m_pDoc->GetValue(0, 0, 0, val);
1384 CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 2);
1386 // Now function with missing parameters. Missing values should be treated
1387 // as zeros.
1388 m_pDoc->SetString(0, 0, 0, OUString("=AVERAGE(1;;;)"));
1389 m_pDoc->CalcFormulaTree(false, false);
1390 m_pDoc->GetValue(0, 0, 0, val);
1391 CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 0.25);
1393 // Conversion of string to numeric argument.
1394 m_pDoc->SetString(0, 0, 0, OUString("=\"\"+3")); // empty string
1395 m_pDoc->SetString(0, 1, 0, OUString("=\" \"+3")); // only blank
1396 m_pDoc->SetString(0, 2, 0, OUString("=\" 4 \"+3")); // number in blanks
1397 m_pDoc->SetString(0, 3, 0, OUString("=\" x \"+3")); // non-numeric => #VALUE! error
1399 rtl::OUString aVal;
1400 ScCalcConfig aConfig;
1402 // With "Empty string as zero" option.
1403 aConfig.mbEmptyStringAsZero = true;
1404 ScInterpreter::SetGlobalConfig(aConfig);
1405 m_pDoc->CalcAll();
1406 m_pDoc->GetValue(0, 0, 0, val);
1407 CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 3);
1408 m_pDoc->GetValue(0, 1, 0, val);
1409 CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 3);
1410 m_pDoc->GetValue(0, 2, 0, val);
1411 CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 7);
1412 aVal = m_pDoc->GetString( 0, 3, 0);
1413 CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1415 // Without "Empty string as zero" option.
1416 aConfig.mbEmptyStringAsZero = false;
1417 ScInterpreter::SetGlobalConfig(aConfig);
1418 m_pDoc->CalcAll();
1419 aVal = m_pDoc->GetString( 0, 0, 0);
1420 CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1421 aVal = m_pDoc->GetString( 0, 1, 0);
1422 CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1423 m_pDoc->GetValue(0, 2, 0, val);
1424 CPPUNIT_ASSERT_MESSAGE("incorrect result", val == 7);
1425 aVal = m_pDoc->GetString( 0, 3, 0);
1426 CPPUNIT_ASSERT_MESSAGE("incorrect result", aVal == "#VALUE!");
1428 m_pDoc->DeleteTab(0);
1431 void Test::testNamedRange()
1433 struct {
1434 const char* pName; const char* pExpr; sal_uInt16 nIndex;
1435 } aNames[] = {
1436 { "Divisor", "$Sheet1.$A$1:$A$1048576", 1 },
1437 { "MyRange1", "$Sheet1.$A$1:$A$100", 2 },
1438 { "MyRange2", "$Sheet1.$B$1:$B$100", 3 },
1439 { "MyRange3", "$Sheet1.$C$1:$C$100", 4 }
1442 rtl::OUString aTabName("Sheet1");
1443 CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
1444 m_pDoc->InsertTab (0, aTabName));
1446 m_pDoc->SetValue (0, 0, 0, 101);
1448 ScAddress aA1(0, 0, 0);
1449 ScRangeName* pNewRanges = new ScRangeName();
1450 for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
1452 ScRangeData* pNew = new ScRangeData(
1453 m_pDoc,
1454 rtl::OUString::createFromAscii(aNames[i].pName),
1455 rtl::OUString::createFromAscii(aNames[i].pExpr),
1456 aA1, 0, formula::FormulaGrammar::GRAM_ENGLISH);
1457 pNew->SetIndex(aNames[i].nIndex);
1458 bool bSuccess = pNewRanges->insert(pNew);
1459 CPPUNIT_ASSERT_MESSAGE ("insertion failed", bSuccess);
1462 // Make sure the index lookup does the right thing.
1463 for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
1465 const ScRangeData* p = pNewRanges->findByIndex(aNames[i].nIndex);
1466 CPPUNIT_ASSERT_MESSAGE("lookup of range name by index failed.", p);
1467 rtl::OUString aName = p->GetName();
1468 CPPUNIT_ASSERT_MESSAGE("wrong range name is retrieved.", aName.equalsAscii(aNames[i].pName));
1471 // Test usage in formula expression.
1472 m_pDoc->SetRangeName(pNewRanges);
1473 m_pDoc->SetString (1, 0, 0, rtl::OUString("=A1/Divisor"));
1474 m_pDoc->CalcAll();
1476 double result;
1477 m_pDoc->GetValue (1, 0, 0, result);
1478 CPPUNIT_ASSERT_MESSAGE ("calculation failed", result == 1.0);
1480 // Test copy-ability of range names.
1481 ScRangeName* pCopiedRanges = new ScRangeName(*pNewRanges);
1482 m_pDoc->SetRangeName(pCopiedRanges);
1483 // Make sure the index lookup still works.
1484 for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
1486 const ScRangeData* p = pCopiedRanges->findByIndex(aNames[i].nIndex);
1487 CPPUNIT_ASSERT_MESSAGE("lookup of range name by index failed with the copied instance.", p);
1488 rtl::OUString aName = p->GetName();
1489 CPPUNIT_ASSERT_MESSAGE("wrong range name is retrieved with the copied instance.", aName.equalsAscii(aNames[i].pName));
1492 m_pDoc->SetRangeName(NULL); // Delete the names.
1493 m_pDoc->DeleteTab(0);
1496 void Test::testCSV()
1498 const int English = 0, European = 1;
1499 struct {
1500 const char *pStr; int eSep; bool bResult; double nValue;
1501 } aTests[] = {
1502 { "foo", English, false, 0.0 },
1503 { "1.0", English, true, 1.0 },
1504 { "1,0", English, false, 0.0 },
1505 { "1.0", European, false, 0.0 },
1506 { "1.000", European, true, 1000.0 },
1507 { "1,000", European, true, 1.0 },
1508 { "1.000", English, true, 1.0 },
1509 { "1,000", English, true, 1000.0 },
1510 { " 1.0", English, true, 1.0 },
1511 { " 1.0 ", English, true, 1.0 },
1512 { "1.0 ", European, false, 0.0 },
1513 { "1.000", European, true, 1000.0 },
1514 { "1137.999", English, true, 1137.999 },
1515 { "1.000.00", European, false, 0.0 }
1517 for (sal_uInt32 i = 0; i < SAL_N_ELEMENTS(aTests); i++) {
1518 rtl::OUString aStr(aTests[i].pStr, strlen (aTests[i].pStr), RTL_TEXTENCODING_UTF8);
1519 double nValue = 0.0;
1520 bool bResult = ScStringUtil::parseSimpleNumber
1521 (aStr, aTests[i].eSep == English ? '.' : ',',
1522 aTests[i].eSep == English ? ',' : '.',
1523 nValue);
1524 CPPUNIT_ASSERT_MESSAGE ("CSV numeric detection failure", bResult == aTests[i].bResult);
1525 CPPUNIT_ASSERT_MESSAGE ("CSV numeric value failure", nValue == aTests[i].nValue);
1529 template<typename Evaluator>
1530 void checkMatrixElements(const ScMatrix& rMat)
1532 SCSIZE nC, nR;
1533 rMat.GetDimensions(nC, nR);
1534 Evaluator aEval;
1535 for (SCSIZE i = 0; i < nC; ++i)
1537 for (SCSIZE j = 0; j < nR; ++j)
1539 aEval(i, j, rMat.Get(i, j));
1544 struct AllZeroMatrix
1546 void operator() (SCSIZE /*nCol*/, SCSIZE /*nRow*/, const ScMatrixValue& rVal) const
1548 CPPUNIT_ASSERT_MESSAGE("element is not of numeric type", rVal.nType == SC_MATVAL_VALUE);
1549 CPPUNIT_ASSERT_MESSAGE("element value must be zero", rVal.fVal == 0.0);
1553 struct PartiallyFilledZeroMatrix
1555 void operator() (SCSIZE nCol, SCSIZE nRow, const ScMatrixValue& rVal) const
1557 CPPUNIT_ASSERT_MESSAGE("element is not of numeric type", rVal.nType == SC_MATVAL_VALUE);
1558 if (1 <= nCol && nCol <= 2 && 2 <= nRow && nRow <= 8)
1560 CPPUNIT_ASSERT_MESSAGE("element value must be 3.0", rVal.fVal == 3.0);
1562 else
1564 CPPUNIT_ASSERT_MESSAGE("element value must be zero", rVal.fVal == 0.0);
1569 struct AllEmptyMatrix
1571 void operator() (SCSIZE /*nCol*/, SCSIZE /*nRow*/, const ScMatrixValue& rVal) const
1573 CPPUNIT_ASSERT_MESSAGE("element is not of empty type", rVal.nType == SC_MATVAL_EMPTY);
1574 CPPUNIT_ASSERT_MESSAGE("value of \"empty\" element is expected to be zero", rVal.fVal == 0.0);
1578 struct PartiallyFilledEmptyMatrix
1580 void operator() (SCSIZE nCol, SCSIZE nRow, const ScMatrixValue& rVal) const
1582 if (nCol == 1 && nRow == 1)
1584 CPPUNIT_ASSERT_MESSAGE("element is not of boolean type", rVal.nType == SC_MATVAL_BOOLEAN);
1585 CPPUNIT_ASSERT_MESSAGE("element value is not what is expected", rVal.fVal == 1.0);
1587 else if (nCol == 4 && nRow == 5)
1589 CPPUNIT_ASSERT_MESSAGE("element is not of value type", rVal.nType == SC_MATVAL_VALUE);
1590 CPPUNIT_ASSERT_MESSAGE("element value is not what is expected", rVal.fVal == -12.5);
1592 else if (nCol == 8 && nRow == 2)
1594 CPPUNIT_ASSERT_MESSAGE("element is not of value type", rVal.nType == SC_MATVAL_STRING);
1595 CPPUNIT_ASSERT_MESSAGE("element value is not what is expected", rVal.aStr == "Test");
1597 else if (nCol == 8 && nRow == 11)
1599 CPPUNIT_ASSERT_MESSAGE("element is not of empty path type", rVal.nType == SC_MATVAL_EMPTYPATH);
1600 CPPUNIT_ASSERT_MESSAGE("value of \"empty\" element is expected to be zero", rVal.fVal == 0.0);
1602 else
1604 CPPUNIT_ASSERT_MESSAGE("element is not of empty type", rVal.nType == SC_MATVAL_EMPTY);
1605 CPPUNIT_ASSERT_MESSAGE("value of \"empty\" element is expected to be zero", rVal.fVal == 0.0);
1610 void Test::testMatrix()
1612 ScMatrixRef pMat;
1614 // First, test the zero matrix type.
1615 pMat = new ScMatrix(0, 0, 0.0);
1616 SCSIZE nC, nR;
1617 pMat->GetDimensions(nC, nR);
1618 CPPUNIT_ASSERT_MESSAGE("matrix is not empty", nC == 0 && nR == 0);
1619 pMat->Resize(4, 10, 0.0);
1620 pMat->GetDimensions(nC, nR);
1621 CPPUNIT_ASSERT_MESSAGE("matrix size is not as expected", nC == 4 && nR == 10);
1622 CPPUNIT_ASSERT_MESSAGE("both 'and' and 'or' should evaluate to false",
1623 !pMat->And() && !pMat->Or());
1625 // Resizing into a larger matrix should fill the void space with zeros.
1626 checkMatrixElements<AllZeroMatrix>(*pMat);
1628 pMat->FillDouble(3.0, 1, 2, 2, 8);
1629 checkMatrixElements<PartiallyFilledZeroMatrix>(*pMat);
1630 CPPUNIT_ASSERT_MESSAGE("matrix is expected to be numeric", pMat->IsNumeric());
1631 CPPUNIT_ASSERT_MESSAGE("partially non-zero matrix should evaluate false on 'and' and true on 'or",
1632 !pMat->And() && pMat->Or());
1633 pMat->FillDouble(5.0, 0, 0, nC-1, nR-1);
1634 CPPUNIT_ASSERT_MESSAGE("fully non-zero matrix should evaluate true both on 'and' and 'or",
1635 pMat->And() && pMat->Or());
1637 // Test the AND and OR evaluations.
1638 pMat = new ScMatrix(2, 2, 0.0);
1640 // Only some of the elements are non-zero.
1641 pMat->PutBoolean(true, 0, 0);
1642 pMat->PutDouble(1.0, 1, 1);
1643 CPPUNIT_ASSERT_MESSAGE("incorrect OR result", pMat->Or());
1644 CPPUNIT_ASSERT_MESSAGE("incorrect AND result", !pMat->And());
1646 // All of the elements are non-zero.
1647 pMat->PutBoolean(true, 0, 1);
1648 pMat->PutDouble(2.3, 1, 0);
1649 CPPUNIT_ASSERT_MESSAGE("incorrect OR result", pMat->Or());
1650 CPPUNIT_ASSERT_MESSAGE("incorrect AND result", pMat->And());
1652 // Now test the emtpy matrix type.
1653 pMat = new ScMatrix(10, 20);
1654 pMat->GetDimensions(nC, nR);
1655 CPPUNIT_ASSERT_MESSAGE("matrix size is not as expected", nC == 10 && nR == 20);
1656 checkMatrixElements<AllEmptyMatrix>(*pMat);
1658 pMat->PutBoolean(true, 1, 1);
1659 pMat->PutDouble(-12.5, 4, 5);
1660 rtl::OUString aStr("Test");
1661 pMat->PutString(aStr, 8, 2);
1662 pMat->PutEmptyPath(8, 11);
1663 checkMatrixElements<PartiallyFilledEmptyMatrix>(*pMat);
1665 // Test resizing.
1666 pMat = new ScMatrix(0, 0);
1667 pMat->Resize(2, 2, 1.5);
1668 pMat->PutEmpty(1, 1);
1670 CPPUNIT_ASSERT_EQUAL(1.5, pMat->GetDouble(0, 0));
1671 CPPUNIT_ASSERT_EQUAL(1.5, pMat->GetDouble(0, 1));
1672 CPPUNIT_ASSERT_EQUAL(1.5, pMat->GetDouble(1, 0));
1673 CPPUNIT_ASSERT_MESSAGE("PutEmpty() call failed.", pMat->IsEmpty(1, 1));
1676 void Test::testEnterMixedMatrix()
1678 m_pDoc->InsertTab(0, "foo");
1680 // Insert the source values in A1:B2.
1681 m_pDoc->SetString(0, 0, 0, "A");
1682 m_pDoc->SetString(1, 0, 0, "B");
1683 double val = 1.0;
1684 m_pDoc->SetValue(0, 1, 0, val);
1685 val = 2.0;
1686 m_pDoc->SetValue(1, 1, 0, val);
1688 // Create a matrix range in A4:B5 referencing A1:B2.
1689 ScMarkData aMark;
1690 aMark.SelectOneTable(0);
1691 m_pDoc->InsertMatrixFormula(0, 3, 1, 4, aMark, "=A1:B2", NULL);
1693 CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(0,0,0), m_pDoc->GetString(0,3,0));
1694 CPPUNIT_ASSERT_EQUAL(m_pDoc->GetString(1,0,0), m_pDoc->GetString(1,3,0));
1695 CPPUNIT_ASSERT_EQUAL(m_pDoc->GetValue(0,1,0), m_pDoc->GetValue(0,4,0));
1696 CPPUNIT_ASSERT_EQUAL(m_pDoc->GetValue(1,1,0), m_pDoc->GetValue(1,4,0));
1698 m_pDoc->DeleteTab(0);
1701 namespace {
1703 struct DPFieldDef
1705 const char* pName;
1706 sheet::DataPilotFieldOrientation eOrient;
1709 * Function for data field. It's used only for data field. When 0, the
1710 * default function (SUM) is used.
1712 int eFunc;
1715 template<size_t _Size>
1716 ScRange insertDPSourceData(ScDocument* pDoc, DPFieldDef aFields[], size_t nFieldCount, const char* aData[][_Size], size_t nDataCount)
1718 // Insert field names in row 0.
1719 for (size_t i = 0; i < nFieldCount; ++i)
1720 pDoc->SetString(static_cast<SCCOL>(i), 0, 0, OUString(aFields[i].pName, strlen(aFields[i].pName), RTL_TEXTENCODING_UTF8));
1722 // Insert data into row 1 and downward.
1723 for (size_t i = 0; i < nDataCount; ++i)
1725 SCROW nRow = static_cast<SCROW>(i) + 1;
1726 for (size_t j = 0; j < nFieldCount; ++j)
1728 SCCOL nCol = static_cast<SCCOL>(j);
1729 pDoc->SetString(
1730 nCol, nRow, 0, OUString(aData[i][j], strlen(aData[i][j]), RTL_TEXTENCODING_UTF8));
1734 SCROW nRow1 = 0, nRow2 = 0;
1735 SCCOL nCol1 = 0, nCol2 = 0;
1736 pDoc->GetDataArea(0, nCol1, nRow1, nCol2, nRow2, true, false);
1737 CPPUNIT_ASSERT_MESSAGE("Data is expected to start from (col=0,row=0).", nCol1 == 0 && nRow1 == 0);
1738 CPPUNIT_ASSERT_MESSAGE("Unexpected data range.",
1739 nCol2 == static_cast<SCCOL>(nFieldCount - 1) && nRow2 == static_cast<SCROW>(nDataCount));
1741 ScRange aSrcRange(nCol1, nRow1, 0, nCol2, nRow2, 0);
1742 printRange(pDoc, aSrcRange, "Data sheet content");
1743 return aSrcRange;
1746 template<size_t _Size>
1747 bool checkDPTableOutput(ScDocument* pDoc, const ScRange& aOutRange, const char* aOutputCheck[][_Size], const char* pCaption)
1749 bool bResult = true;
1750 const ScAddress& s = aOutRange.aStart;
1751 const ScAddress& e = aOutRange.aEnd;
1752 SheetPrinter printer(e.Row() - s.Row() + 1, e.Col() - s.Col() + 1);
1753 SCROW nOutRowSize = e.Row() - s.Row() + 1;
1754 SCCOL nOutColSize = e.Col() - s.Col() + 1;
1755 for (SCROW nRow = 0; nRow < nOutRowSize; ++nRow)
1757 for (SCCOL nCol = 0; nCol < nOutColSize; ++nCol)
1759 OUString aVal;
1760 pDoc->GetString(nCol + s.Col(), nRow + s.Row(), s.Tab(), aVal);
1761 printer.set(nRow, nCol, aVal);
1762 const char* p = aOutputCheck[nRow][nCol];
1763 if (p)
1765 OUString aCheckVal = OUString::createFromAscii(p);
1766 bool bEqual = aCheckVal.equals(aVal);
1767 if (!bEqual)
1769 cout << "Expected: " << aCheckVal << " Actual: " << aVal << endl;
1770 bResult = false;
1773 else if (!aVal.isEmpty())
1775 cout << "Empty cell expected" << endl;
1776 bResult = false;
1780 printer.print(pCaption);
1781 return bResult;
1784 ScDPObject* createDPFromSourceDesc(
1785 ScDocument* pDoc, const ScSheetSourceDesc& rDesc, DPFieldDef aFields[], size_t nFieldCount,
1786 bool bFilterButton)
1788 ScDPObject* pDPObj = new ScDPObject(pDoc);
1789 pDPObj->SetSheetDesc(rDesc);
1790 pDPObj->SetOutRange(ScAddress(0, 0, 1));
1792 ScDPSaveData aSaveData;
1793 // Set data pilot table output options.
1794 aSaveData.SetIgnoreEmptyRows(false);
1795 aSaveData.SetRepeatIfEmpty(false);
1796 aSaveData.SetColumnGrand(true);
1797 aSaveData.SetRowGrand(true);
1798 aSaveData.SetFilterButton(bFilterButton);
1799 aSaveData.SetDrillDown(true);
1801 // Check the sanity of the source range.
1802 const ScRange& rSrcRange = rDesc.GetSourceRange();
1803 SCROW nRow1 = rSrcRange.aStart.Row();
1804 SCROW nRow2 = rSrcRange.aEnd.Row();
1805 CPPUNIT_ASSERT_MESSAGE("source range contains no data!", nRow2 - nRow1 > 1);
1807 // Set the dimension information.
1808 for (size_t i = 0; i < nFieldCount; ++i)
1810 OUString aDimName = rtl::OUString::createFromAscii(aFields[i].pName);
1811 ScDPSaveDimension* pDim = aSaveData.GetNewDimensionByName(aDimName);
1812 pDim->SetOrientation(static_cast<sal_uInt16>(aFields[i].eOrient));
1813 pDim->SetUsedHierarchy(0);
1815 if (aFields[i].eOrient == sheet::DataPilotFieldOrientation_DATA)
1817 sheet::GeneralFunction eFunc = sheet::GeneralFunction_SUM;
1818 if (aFields[i].eFunc)
1819 eFunc = static_cast<sheet::GeneralFunction>(aFields[i].eFunc);
1821 pDim->SetFunction(eFunc);
1822 pDim->SetReferenceValue(NULL);
1824 else
1826 sheet::DataPilotFieldSortInfo aSortInfo;
1827 aSortInfo.IsAscending = true;
1828 aSortInfo.Mode = 2;
1829 pDim->SetSortInfo(&aSortInfo);
1831 sheet::DataPilotFieldLayoutInfo aLayInfo;
1832 aLayInfo.LayoutMode = 0;
1833 aLayInfo.AddEmptyLines = false;
1834 pDim->SetLayoutInfo(&aLayInfo);
1835 sheet::DataPilotFieldAutoShowInfo aShowInfo;
1836 aShowInfo.IsEnabled = false;
1837 aShowInfo.ShowItemsMode = 0;
1838 aShowInfo.ItemCount = 0;
1839 pDim->SetAutoShowInfo(&aShowInfo);
1843 // Don't forget the data layout dimension.
1844 ScDPSaveDimension* pDim = aSaveData.GetDataLayoutDimension();
1845 pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
1846 pDim->SetShowEmpty(true);
1848 pDPObj->SetSaveData(aSaveData);
1849 pDPObj->SetAlive(true);
1850 pDPObj->InvalidateData();
1852 return pDPObj;
1855 ScDPObject* createDPFromRange(
1856 ScDocument* pDoc, const ScRange& rRange, DPFieldDef aFields[], size_t nFieldCount,
1857 bool bFilterButton)
1859 ScSheetSourceDesc aSheetDesc(pDoc);
1860 aSheetDesc.SetSourceRange(rRange);
1861 return createDPFromSourceDesc(pDoc, aSheetDesc, aFields, nFieldCount, bFilterButton);
1864 ScRange refresh(ScDPObject* pDPObj)
1866 bool bOverFlow = false;
1867 ScRange aOutRange = pDPObj->GetNewOutputRange(bOverFlow);
1868 CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverFlow);
1870 pDPObj->Output(aOutRange.aStart);
1871 aOutRange = pDPObj->GetOutRange();
1872 return aOutRange;
1875 ScRange refreshGroups(ScDPCollection* pDPs, ScDPObject* pDPObj)
1877 // We need to first create group data in the cache, then the group data in
1878 // the object.
1879 std::set<ScDPObject*> aRefs;
1880 bool bSuccess = pDPs->ReloadGroupsInCache(pDPObj, aRefs);
1881 CPPUNIT_ASSERT_MESSAGE("Failed to reload group data in cache.", bSuccess);
1882 CPPUNIT_ASSERT_MESSAGE("There should be only one table linked to this cache.", aRefs.size() == 1);
1883 pDPObj->ReloadGroupTableData();
1885 return refresh(pDPObj);
1890 void Test::testPivotTable()
1892 m_pDoc->InsertTab(0, OUString("Data"));
1893 m_pDoc->InsertTab(1, OUString("Table"));
1895 // Dimension definition
1896 DPFieldDef aFields[] = {
1897 { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
1898 { "Group", sheet::DataPilotFieldOrientation_COLUMN, 0 },
1899 { "Score", sheet::DataPilotFieldOrientation_DATA, 0 }
1902 // Raw data
1903 const char* aData[][3] = {
1904 { "Andy", "A", "30" },
1905 { "Bruce", "A", "20" },
1906 { "Charlie", "B", "45" },
1907 { "David", "B", "12" },
1908 { "Edward", "C", "8" },
1909 { "Frank", "C", "15" },
1912 size_t nFieldCount = SAL_N_ELEMENTS(aFields);
1913 size_t nDataCount = SAL_N_ELEMENTS(aData);
1915 ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
1916 SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
1917 SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
1919 ScDPObject* pDPObj = createDPFromRange(
1920 m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
1922 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1923 bool bSuccess = pDPs->InsertNewTable(pDPObj);
1924 CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
1925 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
1926 pDPs->GetCount() == 1);
1927 pDPObj->SetName(pDPs->CreateNewName());
1929 bool bOverFlow = false;
1930 ScRange aOutRange = pDPObj->GetNewOutputRange(bOverFlow);
1931 CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverFlow);
1933 pDPObj->Output(aOutRange.aStart);
1934 aOutRange = pDPObj->GetOutRange();
1936 // Expected output table content. 0 = empty cell
1937 const char* aOutputCheck[][5] = {
1938 { "Sum - Score", "Group", 0, 0, 0 },
1939 { "Name", "A", "B", "C", "Total Result" },
1940 { "Andy", "30", 0, 0, "30" },
1941 { "Bruce", "20", 0, 0, "20" },
1942 { "Charlie", 0, "45", 0, "45" },
1943 { "David", 0, "12", 0, "12" },
1944 { "Edward", 0, 0, "8", "8" },
1945 { "Frank", 0, 0, "15", "15" },
1946 { "Total Result", "50", "57", "23", "130" }
1949 bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
1950 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1952 CPPUNIT_ASSERT_MESSAGE("There should be only one data cache.", pDPs->GetSheetCaches().size() == 1);
1954 // Update the cell values.
1955 double aData2[] = { 100, 200, 300, 400, 500, 600 };
1956 for (size_t i = 0; i < SAL_N_ELEMENTS(aData2); ++i)
1958 SCROW nRow = i + 1;
1959 m_pDoc->SetValue(2, nRow, 0, aData2[i]);
1962 printRange(m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), "Data sheet content (modified)");
1964 // Now, create a copy of the datapilot object for the updated table, but
1965 // don't reload the cache which should force the copy to use the old data
1966 // from the cache.
1967 ScDPObject* pDPObj2 = new ScDPObject(*pDPObj);
1968 pDPs->InsertNewTable(pDPObj2);
1970 aOutRange = pDPObj2->GetOutRange();
1971 pDPObj2->ClearTableData();
1972 pDPObj2->Output(aOutRange.aStart);
1974 // Expected output table content. 0 = empty cell
1975 const char* aOutputCheck[][5] = {
1976 { "Sum - Score", "Group", 0, 0, 0 },
1977 { "Name", "A", "B", "C", "Total Result" },
1978 { "Andy", "30", 0, 0, "30" },
1979 { "Bruce", "20", 0, 0, "20" },
1980 { "Charlie", 0, "45", 0, "45" },
1981 { "David", 0, "12", 0, "12" },
1982 { "Edward", 0, 0, "8", "8" },
1983 { "Frank", 0, 0, "15", "15" },
1984 { "Total Result", "50", "57", "23", "130" }
1987 bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (from old cache)");
1988 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1991 CPPUNIT_ASSERT_MESSAGE("There should be only one data cache.", pDPs->GetSheetCaches().size() == 1);
1993 // Free the first datapilot object after the 2nd one gets reloaded, to
1994 // prevent the data cache from being deleted before the reload.
1995 pDPs->FreeTable(pDPObj);
1997 CPPUNIT_ASSERT_MESSAGE("There should be only one data cache.", pDPs->GetSheetCaches().size() == 1);
1999 // This time clear the cache to refresh the data from the source range.
2000 CPPUNIT_ASSERT_MESSAGE("This datapilot should be based on sheet data.", pDPObj2->IsSheetData());
2001 std::set<ScDPObject*> aRefs;
2002 sal_uLong nErrId = pDPs->ReloadCache(pDPObj2, aRefs);
2003 CPPUNIT_ASSERT_MESSAGE("Cache reload failed.", nErrId == 0);
2004 CPPUNIT_ASSERT_MESSAGE("Reloading a cache shouldn't remove any cache.",
2005 pDPs->GetSheetCaches().size() == 1);
2007 pDPObj2->ClearTableData();
2008 pDPObj2->Output(aOutRange.aStart);
2011 // Expected output table content. 0 = empty cell
2012 const char* aOutputCheck[][5] = {
2013 { "Sum - Score", "Group", 0, 0, 0 },
2014 { "Name", "A", "B", "C", "Total Result" },
2015 { "Andy", "100", 0, 0, "100" },
2016 { "Bruce", "200", 0, 0, "200" },
2017 { "Charlie", 0, "300", 0, "300" },
2018 { "David", 0, "400", 0, "400" },
2019 { "Edward", 0, 0, "500", "500" },
2020 { "Frank", 0, 0, "600", "600" },
2021 { "Total Result", "300", "700", "1100", "2100" }
2024 bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (refreshed)");
2025 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2028 CPPUNIT_ASSERT_MESSAGE("Cache should be here.", pDPs->GetSheetCaches().hasCache(aSrcRange));
2030 // Swap the two sheets.
2031 m_pDoc->MoveTab(1, 0);
2032 CPPUNIT_ASSERT_MESSAGE("Swapping the sheets shouldn't remove the cache.",
2033 pDPs->GetSheetCaches().size() == 1);
2034 CPPUNIT_ASSERT_MESSAGE("Cache should have moved.", !pDPs->GetSheetCaches().hasCache(aSrcRange));
2035 aSrcRange.aStart.SetTab(1);
2036 aSrcRange.aEnd.SetTab(1);
2037 CPPUNIT_ASSERT_MESSAGE("Cache should be here.", pDPs->GetSheetCaches().hasCache(aSrcRange));
2039 pDPs->FreeTable(pDPObj2);
2040 CPPUNIT_ASSERT_MESSAGE("There shouldn't be any data pilot table stored with the document.",
2041 pDPs->GetCount() == 0);
2043 CPPUNIT_ASSERT_MESSAGE("There shouldn't be any more data cache.",
2044 pDPs->GetSheetCaches().size() == 0);
2046 // Insert a brand new pivot table object once again, but this time, don't
2047 // create the output to avoid creating a data cache.
2048 m_pDoc->DeleteTab(1);
2049 m_pDoc->InsertTab(1, OUString("Table"));
2051 pDPObj = createDPFromRange(
2052 m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
2053 bSuccess = pDPs->InsertNewTable(pDPObj);
2054 CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
2055 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2056 pDPs->GetCount() == 1);
2057 pDPObj->SetName(pDPs->CreateNewName());
2058 CPPUNIT_ASSERT_MESSAGE("Data cache shouldn't exist yet before creating the table output.",
2059 pDPs->GetSheetCaches().size() == 0);
2061 // Now, "refresh" the table. This should still return a reference to self
2062 // even with the absence of data cache.
2063 aRefs.clear();
2064 pDPs->ReloadCache(pDPObj, aRefs);
2065 CPPUNIT_ASSERT_MESSAGE("It should return the same object as a reference.",
2066 aRefs.size() == 1 && *aRefs.begin() == pDPObj);
2068 pDPs->FreeTable(pDPObj);
2070 m_pDoc->DeleteTab(1);
2071 m_pDoc->DeleteTab(0);
2074 void Test::testPivotTableLabels()
2076 m_pDoc->InsertTab(0, OUString("Data"));
2077 m_pDoc->InsertTab(1, OUString("Table"));
2079 // Dimension definition
2080 DPFieldDef aFields[] = {
2081 { "Software", sheet::DataPilotFieldOrientation_ROW, 0 },
2082 { "Version", sheet::DataPilotFieldOrientation_COLUMN, 0 },
2083 { "1.2.3", sheet::DataPilotFieldOrientation_DATA, 0 }
2086 // Raw data
2087 const char* aData[][3] = {
2088 { "LibreOffice", "3.3.0", "30" },
2089 { "LibreOffice", "3.3.1", "20" },
2090 { "LibreOffice", "3.4.0", "45" },
2093 size_t nFieldCount = SAL_N_ELEMENTS(aFields);
2094 size_t nDataCount = SAL_N_ELEMENTS(aData);
2096 ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
2097 SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
2098 SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
2100 ScDPObject* pDPObj = createDPFromRange(
2101 m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
2103 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2104 bool bSuccess = pDPs->InsertNewTable(pDPObj);
2105 CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
2106 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2107 pDPs->GetCount() == 1);
2108 pDPObj->SetName(pDPs->CreateNewName());
2110 ScRange aOutRange = refresh(pDPObj);
2112 // Expected output table content. 0 = empty cell
2113 const char* aOutputCheck[][5] = {
2114 { "Sum - 1.2.3", "Version", 0, 0, 0 },
2115 { "Software", "3.3.0", "3.3.1", "3.4.0", "Total Result" },
2116 { "LibreOffice", "30", "20", "45", "95" },
2117 { "Total Result", "30", "20", "45", "95" }
2120 bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2121 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2124 pDPs->FreeTable(pDPObj);
2126 m_pDoc->DeleteTab(1);
2127 m_pDoc->DeleteTab(0);
2130 void Test::testPivotTableDateLabels()
2132 m_pDoc->InsertTab(0, OUString("Data"));
2133 m_pDoc->InsertTab(1, OUString("Table"));
2135 // Dimension definition
2136 DPFieldDef aFields[] = {
2137 { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
2138 { "Date", sheet::DataPilotFieldOrientation_COLUMN, 0 },
2139 { "Value", sheet::DataPilotFieldOrientation_DATA, 0 }
2142 // Raw data
2143 const char* aData[][3] = {
2144 { "Zena", "2011-1-1", "30" },
2145 { "Yodel", "2011-1-2", "20" },
2146 { "Xavior", "2011-1-3", "45" }
2149 size_t nFieldCount = SAL_N_ELEMENTS(aFields);
2150 size_t nDataCount = SAL_N_ELEMENTS(aData);
2152 ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
2153 SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
2154 SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
2156 ScDPObject* pDPObj = createDPFromRange(
2157 m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
2159 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2160 bool bSuccess = pDPs->InsertNewTable(pDPObj);
2161 CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
2162 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2163 pDPs->GetCount() == 1);
2164 pDPObj->SetName(pDPs->CreateNewName());
2166 ScRange aOutRange = refresh(pDPObj);
2168 // Expected output table content. 0 = empty cell
2169 const char* aOutputCheck[][5] = {
2170 { "Sum - Value", "Date", 0, 0, 0 },
2171 { "Name", "2011-01-01", "2011-01-02", "2011-01-03", "Total Result" },
2172 { "Xavior", 0, 0, "45", "45" },
2173 { "Yodel", 0, "20", 0, "20" },
2174 { "Zena", "30", 0, 0, "30" },
2175 { "Total Result", "30", "20", "45", "95" }
2178 bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2179 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2183 const char* aChecks[] = {
2184 "2011-01-01", "2011-01-02", "2011-01-03"
2187 // Make sure those cells that contain dates are numeric.
2188 SCROW nRow = aOutRange.aStart.Row() + 1;
2189 nCol1 = aOutRange.aStart.Col() + 1;
2190 nCol2 = nCol1 + 2;
2191 for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
2193 OUString aVal = m_pDoc->GetString(nCol, nRow, 1);
2194 CPPUNIT_ASSERT_MESSAGE("Cell value is not as expected.", aVal.equalsAscii(aChecks[nCol-nCol1]));
2195 CPPUNIT_ASSERT_MESSAGE("This cell contains a date value and is supposed to be numeric.",
2196 m_pDoc->HasValueData(nCol, nRow, 1));
2200 pDPs->FreeTable(pDPObj);
2202 m_pDoc->DeleteTab(1);
2203 m_pDoc->DeleteTab(0);
2206 void Test::testPivotTableFilters()
2208 m_pDoc->InsertTab(0, OUString("Data"));
2209 m_pDoc->InsertTab(1, OUString("Table"));
2211 // Dimension definition
2212 DPFieldDef aFields[] = {
2213 { "Name", sheet::DataPilotFieldOrientation_HIDDEN, 0 },
2214 { "Group1", sheet::DataPilotFieldOrientation_HIDDEN, 0 },
2215 { "Group2", sheet::DataPilotFieldOrientation_PAGE, 0 },
2216 { "Val1", sheet::DataPilotFieldOrientation_DATA, 0 },
2217 { "Val2", sheet::DataPilotFieldOrientation_DATA, 0 }
2220 // Raw data
2221 const char* aData[][5] = {
2222 { "A", "1", "A", "1", "10" },
2223 { "B", "1", "A", "1", "10" },
2224 { "C", "1", "B", "1", "10" },
2225 { "D", "1", "B", "1", "10" },
2226 { "E", "2", "A", "1", "10" },
2227 { "F", "2", "A", "1", "10" },
2228 { "G", "2", "B", "1", "10" },
2229 { "H", "2", "B", "1", "10" }
2232 size_t nFieldCount = SAL_N_ELEMENTS(aFields);
2233 size_t nDataCount = SAL_N_ELEMENTS(aData);
2235 ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
2236 SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
2237 SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
2239 ScDPObject* pDPObj = createDPFromRange(
2240 m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, true);
2242 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2243 bool bSuccess = pDPs->InsertNewTable(pDPObj);
2244 CPPUNIT_ASSERT_MESSAGE("failed to insert a new datapilot object into document", bSuccess);
2245 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2246 pDPs->GetCount() == 1);
2247 pDPObj->SetName(pDPs->CreateNewName());
2249 ScRange aOutRange = refresh(pDPObj);
2251 // Expected output table content. 0 = empty cell
2252 const char* aOutputCheck[][2] = {
2253 { "Filter", 0 },
2254 { "Group2", "- all -" },
2255 { 0, 0 },
2256 { "Data", 0 },
2257 { "Sum - Val1", "8" },
2258 { "Sum - Val2", "80" }
2261 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (unfiltered)");
2262 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2265 AutoCalcSwitch aACSwitch(m_pDoc, true); // turn on auto calculation.
2267 ScAddress aFormulaAddr = aOutRange.aEnd;
2268 aFormulaAddr.IncRow(2);
2269 m_pDoc->SetString(aFormulaAddr.Col(), aFormulaAddr.Row(), aFormulaAddr.Tab(),
2270 rtl::OUString("=B6"));
2271 double fTest = m_pDoc->GetValue(aFormulaAddr);
2272 CPPUNIT_ASSERT_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", fTest == 80.0);
2274 // Set current page of 'Group2' to 'A'.
2275 pDPObj->BuildAllDimensionMembers();
2276 ScDPSaveData aSaveData(*pDPObj->GetSaveData());
2277 ScDPSaveDimension* pPageDim = aSaveData.GetDimensionByName(
2278 OUString("Group2"));
2279 CPPUNIT_ASSERT_MESSAGE("Dimension not found", pPageDim);
2280 OUString aPage("A");
2281 pPageDim->SetCurrentPage(&aPage);
2282 pDPObj->SetSaveData(aSaveData);
2283 aOutRange = refresh(pDPObj);
2285 // Expected output table content. 0 = empty cell
2286 const char* aOutputCheck[][2] = {
2287 { "Filter", 0 },
2288 { "Group2", "A" },
2289 { 0, 0 },
2290 { "Data", 0 },
2291 { "Sum - Val1", "4" },
2292 { "Sum - Val2", "40" }
2295 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by page)");
2296 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2299 fTest = m_pDoc->GetValue(aFormulaAddr);
2300 CPPUNIT_ASSERT_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", fTest == 40.0);
2302 // Set query filter.
2303 ScSheetSourceDesc aDesc(*pDPObj->GetSheetDesc());
2304 ScQueryParam aQueryParam(aDesc.GetQueryParam());
2305 CPPUNIT_ASSERT_MESSAGE("There should be at least one query entry.", aQueryParam.GetEntryCount() > 0);
2306 ScQueryEntry& rEntry = aQueryParam.GetEntry(0);
2307 rEntry.bDoQuery = true;
2308 rEntry.nField = 1; // Group1
2309 rEntry.GetQueryItem().mfVal = 1;
2310 aDesc.SetQueryParam(aQueryParam);
2311 pDPObj->SetSheetDesc(aDesc);
2312 aOutRange = refresh(pDPObj);
2314 // Expected output table content. 0 = empty cell
2315 const char* aOutputCheck[][2] = {
2316 { "Filter", 0 },
2317 { "Group2", "A" },
2318 { 0, 0 },
2319 { "Data", 0 },
2320 { "Sum - Val1", "2" },
2321 { "Sum - Val2", "20" }
2324 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by query)");
2325 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2328 fTest = m_pDoc->GetValue(aFormulaAddr);
2329 CPPUNIT_ASSERT_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", fTest == 20.0);
2331 // Set the current page of 'Group2' back to '- all -'. The query filter
2332 // should still be in effect.
2333 pPageDim->SetCurrentPage(NULL); // Remove the page.
2334 pDPObj->SetSaveData(aSaveData);
2335 aOutRange = refresh(pDPObj);
2337 // Expected output table content. 0 = empty cell
2338 const char* aOutputCheck[][2] = {
2339 { "Filter", 0 },
2340 { "Group2", "- all -" },
2341 { 0, 0 },
2342 { "Data", 0 },
2343 { "Sum - Val1", "4" },
2344 { "Sum - Val2", "40" }
2347 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by page)");
2348 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2352 pDPs->FreeTable(pDPObj);
2353 CPPUNIT_ASSERT_MESSAGE("There shouldn't be any data pilot table stored with the document.",
2354 pDPs->GetCount() == 0);
2356 m_pDoc->DeleteTab(1);
2357 m_pDoc->DeleteTab(0);
2360 void Test::testPivotTableNamedSource()
2362 m_pDoc->InsertTab(0, OUString("Data"));
2363 m_pDoc->InsertTab(1, OUString("Table"));
2365 // Dimension definition
2366 DPFieldDef aFields[] = {
2367 { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
2368 { "Group", sheet::DataPilotFieldOrientation_COLUMN, 0 },
2369 { "Score", sheet::DataPilotFieldOrientation_DATA, 0 }
2372 // Raw data
2373 const char* aData[][3] = {
2374 { "Andy", "A", "30" },
2375 { "Bruce", "A", "20" },
2376 { "Charlie", "B", "45" },
2377 { "David", "B", "12" },
2378 { "Edward", "C", "8" },
2379 { "Frank", "C", "15" },
2382 size_t nFieldCount = SAL_N_ELEMENTS(aFields);
2383 size_t nDataCount = SAL_N_ELEMENTS(aData);
2385 // Insert the raw data.
2386 ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
2387 rtl::OUString aRangeStr;
2388 aSrcRange.Format(aRangeStr, SCR_ABS_3D, m_pDoc);
2390 // Name this range.
2391 rtl::OUString aRangeName("MyData");
2392 ScRangeName* pNames = m_pDoc->GetRangeName();
2393 CPPUNIT_ASSERT_MESSAGE("Failed to get global range name container.", pNames);
2394 ScRangeData* pName = new ScRangeData(
2395 m_pDoc, aRangeName, aRangeStr);
2396 bool bSuccess = pNames->insert(pName);
2397 CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bSuccess);
2399 ScSheetSourceDesc aSheetDesc(m_pDoc);
2400 aSheetDesc.SetRangeName(aRangeName);
2401 ScDPObject* pDPObj = createDPFromSourceDesc(m_pDoc, aSheetDesc, aFields, nFieldCount, false);
2402 CPPUNIT_ASSERT_MESSAGE("Failed to create a new pivot table object.", pDPObj);
2404 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2405 bSuccess = pDPs->InsertNewTable(pDPObj);
2406 CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2407 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
2408 pDPs->GetCount() == 1);
2409 pDPObj->SetName(pDPs->CreateNewName());
2411 ScRange aOutRange = refresh(pDPObj);
2413 // Expected output table content. 0 = empty cell
2414 const char* aOutputCheck[][5] = {
2415 { "Sum - Score", "Group", 0, 0, 0 },
2416 { "Name", "A", "B", "C", "Total Result" },
2417 { "Andy", "30", 0, 0, "30" },
2418 { "Bruce", "20", 0, 0, "20" },
2419 { "Charlie", 0, "45", 0, "45" },
2420 { "David", 0, "12", 0, "12" },
2421 { "Edward", 0, 0, "8", "8" },
2422 { "Frank", 0, 0, "15", "15" },
2423 { "Total Result", "50", "57", "23", "130" }
2426 bSuccess = checkDPTableOutput<5>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2427 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2430 CPPUNIT_ASSERT_MESSAGE("There should be one named range data cache.",
2431 pDPs->GetNameCaches().size() == 1 && pDPs->GetSheetCaches().size() == 0);
2433 // Move the table with pivot table to the left of the source data sheet.
2434 m_pDoc->MoveTab(1, 0);
2435 rtl::OUString aTabName;
2436 m_pDoc->GetName(0, aTabName);
2437 CPPUNIT_ASSERT_MESSAGE( "Wrong sheet name.", aTabName == "Table" );
2438 CPPUNIT_ASSERT_MESSAGE("Pivot table output is on the wrong sheet!",
2439 pDPObj->GetOutRange().aStart.Tab() == 0);
2441 CPPUNIT_ASSERT_MESSAGE("Moving the pivot table to another sheet shouldn't have changed the cache state.",
2442 pDPs->GetNameCaches().size() == 1 && pDPs->GetSheetCaches().size() == 0);
2444 const ScSheetSourceDesc* pDesc = pDPObj->GetSheetDesc();
2445 CPPUNIT_ASSERT_MESSAGE("Sheet source description doesn't exist.", pDesc);
2446 CPPUNIT_ASSERT_MESSAGE("Named source range has been altered unexpectedly!",
2447 pDesc->GetRangeName().equals(aRangeName));
2449 CPPUNIT_ASSERT_MESSAGE("Cache should exist.", pDPs->GetNameCaches().hasCache(aRangeName));
2451 pDPs->FreeTable(pDPObj);
2452 CPPUNIT_ASSERT_MESSAGE("There should be no more tables.", pDPs->GetCount() == 0);
2453 CPPUNIT_ASSERT_MESSAGE("There shouldn't be any more cache stored.",
2454 pDPs->GetNameCaches().size() == 0);
2456 pNames->clear();
2457 m_pDoc->DeleteTab(1);
2458 m_pDoc->DeleteTab(0);
2461 void Test::testPivotTableCache()
2463 m_pDoc->InsertTab(0, OUString("Data"));
2465 // Raw data
2466 const char* aData[][3] = {
2467 { "F1", "F2", "F3" },
2468 { "Z", "A", "30" },
2469 { "R", "A", "20" },
2470 { "A", "B", "45" },
2471 { "F", "B", "12" },
2472 { "Y", "C", "8" },
2473 { "12", "C", "15" },
2476 ScAddress aPos(1,1,0);
2477 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2478 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2480 ScDPCache aCache(m_pDoc);
2481 aCache.InitFromDoc(m_pDoc, aDataRange);
2482 long nDimCount = aCache.GetColumnCount();
2483 CPPUNIT_ASSERT_MESSAGE("wrong dimension count.", nDimCount == 3);
2484 rtl::OUString aDimName = aCache.GetDimensionName(0);
2485 CPPUNIT_ASSERT_MESSAGE("wrong dimension name", aDimName.equalsAscii("F1"));
2486 aDimName = aCache.GetDimensionName(1);
2487 CPPUNIT_ASSERT_MESSAGE("wrong dimension name", aDimName.equalsAscii("F2"));
2488 aDimName = aCache.GetDimensionName(2);
2489 CPPUNIT_ASSERT_MESSAGE("wrong dimension name", aDimName.equalsAscii("F3"));
2491 // In each dimension, member ID values also represent their sort order (in
2492 // source dimensions only, not in group dimensions). Value items are
2493 // sorted before string ones. Also, no duplicate dimension members should
2494 // exist.
2496 // Dimension 0 - a mix of strings and values.
2497 long nMemCount = aCache.GetDimMemberCount(0);
2498 CPPUNIT_ASSERT_MESSAGE("wrong dimension member count", nMemCount == 6);
2499 const ScDPItemData* pItem = aCache.GetItemDataById(0, 0);
2500 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2501 pItem->GetType() == ScDPItemData::Value &&
2502 pItem->GetValue() == 12);
2503 pItem = aCache.GetItemDataById(0, 1);
2504 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2505 pItem->GetType() == ScDPItemData::String &&
2506 pItem->GetString().equalsAscii("A"));
2507 pItem = aCache.GetItemDataById(0, 2);
2508 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2509 pItem->GetType() == ScDPItemData::String &&
2510 pItem->GetString().equalsAscii("F"));
2511 pItem = aCache.GetItemDataById(0, 3);
2512 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2513 pItem->GetType() == ScDPItemData::String &&
2514 pItem->GetString().equalsAscii("R"));
2515 pItem = aCache.GetItemDataById(0, 4);
2516 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2517 pItem->GetType() == ScDPItemData::String &&
2518 pItem->GetString().equalsAscii("Y"));
2519 pItem = aCache.GetItemDataById(0, 5);
2520 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2521 pItem->GetType() == ScDPItemData::String &&
2522 pItem->GetString().equalsAscii("Z"));
2523 pItem = aCache.GetItemDataById(0, 6);
2524 CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
2526 // Dimension 1 - duplicate values in source.
2527 nMemCount = aCache.GetDimMemberCount(1);
2528 CPPUNIT_ASSERT_MESSAGE("wrong dimension member count", nMemCount == 3);
2529 pItem = aCache.GetItemDataById(1, 0);
2530 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2531 pItem->GetType() == ScDPItemData::String &&
2532 pItem->GetString().equalsAscii("A"));
2533 pItem = aCache.GetItemDataById(1, 1);
2534 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2535 pItem->GetType() == ScDPItemData::String &&
2536 pItem->GetString().equalsAscii("B"));
2537 pItem = aCache.GetItemDataById(1, 2);
2538 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2539 pItem->GetType() == ScDPItemData::String &&
2540 pItem->GetString().equalsAscii("C"));
2541 pItem = aCache.GetItemDataById(1, 3);
2542 CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
2544 // Dimension 2 - values only.
2545 nMemCount = aCache.GetDimMemberCount(2);
2546 CPPUNIT_ASSERT_MESSAGE("wrong dimension member count", nMemCount == 6);
2547 pItem = aCache.GetItemDataById(2, 0);
2548 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2549 pItem->GetType() == ScDPItemData::Value &&
2550 pItem->GetValue() == 8);
2551 pItem = aCache.GetItemDataById(2, 1);
2552 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2553 pItem->GetType() == ScDPItemData::Value &&
2554 pItem->GetValue() == 12);
2555 pItem = aCache.GetItemDataById(2, 2);
2556 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2557 pItem->GetType() == ScDPItemData::Value &&
2558 pItem->GetValue() == 15);
2559 pItem = aCache.GetItemDataById(2, 3);
2560 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2561 pItem->GetType() == ScDPItemData::Value &&
2562 pItem->GetValue() == 20);
2563 pItem = aCache.GetItemDataById(2, 4);
2564 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2565 pItem->GetType() == ScDPItemData::Value &&
2566 pItem->GetValue() == 30);
2567 pItem = aCache.GetItemDataById(2, 5);
2568 CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem &&
2569 pItem->GetType() == ScDPItemData::Value &&
2570 pItem->GetValue() == 45);
2571 pItem = aCache.GetItemDataById(2, 6);
2572 CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
2575 // Check the integrity of the source data.
2576 ScDPItemData aTest;
2577 long nDim;
2580 // Dimension 0: Z, R, A, F, Y, 12
2581 nDim = 0;
2582 const char* aChecks[] = { "Z", "R", "A", "F", "Y" };
2583 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2585 pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
2586 aTest.SetString(rtl::OUString::createFromAscii(aChecks[i]));
2587 CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2590 pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, 5, false));
2591 aTest.SetValue(12);
2592 CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2596 // Dimension 1: A, A, B, B, C, C
2597 nDim = 1;
2598 const char* aChecks[] = { "A", "A", "B", "B", "C", "C" };
2599 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2601 pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
2602 aTest.SetString(rtl::OUString::createFromAscii(aChecks[i]));
2603 CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2608 // Dimension 2: 30, 20, 45, 12, 8, 15
2609 nDim = 2;
2610 double aChecks[] = { 30, 20, 45, 12, 8, 15 };
2611 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2613 pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
2614 aTest.SetValue(aChecks[i]);
2615 CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem && *pItem == aTest);
2620 // Now, on to testing the filtered cache.
2623 // Non-filtered cache - everything should be visible.
2624 ScDPFilteredCache aFilteredCache(aCache);
2625 aFilteredCache.fillTable();
2627 sal_Int32 nRows = aFilteredCache.getRowSize();
2628 CPPUNIT_ASSERT_MESSAGE("Wrong dimension.", nRows == 6 && aFilteredCache.getColSize() == 3);
2630 for (sal_Int32 i = 0; i < nRows; ++i)
2632 if (!aFilteredCache.isRowActive(i))
2634 std::ostringstream os;
2635 os << "Row " << i << " should be visible but it isn't.";
2636 CPPUNIT_ASSERT_MESSAGE(os.str().c_str(), false);
2641 // TODO : Add test for filtered caches.
2643 m_pDoc->DeleteTab(0);
2646 void Test::testPivotTableDuplicateDataFields()
2648 m_pDoc->InsertTab(0, OUString("Data"));
2649 m_pDoc->InsertTab(1, OUString("Table"));
2651 // Raw data
2652 const char* aData[][2] = {
2653 { "Name", "Value" },
2654 { "A", "45" },
2655 { "A", "5" },
2656 { "A", "41" },
2657 { "A", "49" },
2658 { "A", "4" },
2659 { "B", "33" },
2660 { "B", "84" },
2661 { "B", "74" },
2662 { "B", "8" },
2663 { "B", "68" }
2666 // Dimension definition
2667 DPFieldDef aFields[] = {
2668 { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
2669 { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
2670 { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_COUNT }
2673 ScAddress aPos(2,2,0);
2674 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2675 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2677 ScDPObject* pDPObj = createDPFromRange(
2678 m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2680 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2681 bool bSuccess = pDPs->InsertNewTable(pDPObj);
2683 CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2684 CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2685 pDPs->GetCount(), static_cast<size_t>(1));
2686 pDPObj->SetName(pDPs->CreateNewName());
2688 ScRange aOutRange = refresh(pDPObj);
2690 // Expected output table content. 0 = empty cell
2691 const char* aOutputCheck[][3] = {
2692 { "Name", "Data", 0 },
2693 { "A", "Sum - Value", "144" },
2694 { 0, "Count - Value", "5" },
2695 { "B", "Sum - Value", "267" },
2696 { 0, "Count - Value", "5" },
2697 { "Total Sum - Value", 0, "411" },
2698 { "Total Count - Value", 0, "10" },
2701 bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2702 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2705 // Move the data layout dimension from row to column.
2706 ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2707 CPPUNIT_ASSERT_MESSAGE("No save data!?", pSaveData);
2708 ScDPSaveDimension* pDataLayout = pSaveData->GetDataLayoutDimension();
2709 CPPUNIT_ASSERT_MESSAGE("No data layout dimension.", pDataLayout);
2710 pDataLayout->SetOrientation(sheet::DataPilotFieldOrientation_COLUMN);
2711 pDPObj->SetSaveData(*pSaveData);
2713 // Refresh the table output.
2714 aOutRange = refresh(pDPObj);
2716 // Expected output table content. 0 = empty cell
2717 const char* aOutputCheck[][3] = {
2718 { 0, "Data", 0 },
2719 { "Name", "Sum - Value", "Count - Value" },
2720 { "A", "144", "5" },
2721 { "B", "267", "5" },
2722 { "Total Result", "411", "10" }
2725 bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2726 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2729 ScPivotParam aParam;
2730 pDPObj->FillLabelData(aParam);
2731 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be exactly 4 labels (2 original, 1 data layout, and 1 duplicate dimensions).",
2732 aParam.maLabelArray.size(), static_cast<size_t>(4));
2734 pDPs->FreeTable(pDPObj);
2735 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2736 CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2737 pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
2739 m_pDoc->DeleteTab(1);
2740 m_pDoc->DeleteTab(0);
2743 void Test::testPivotTableNormalGrouping()
2745 m_pDoc->InsertTab(0, OUString("Data"));
2746 m_pDoc->InsertTab(1, OUString("Table"));
2748 // Raw data
2749 const char* aData[][2] = {
2750 { "Name", "Value" },
2751 { "A", "1" },
2752 { "B", "2" },
2753 { "C", "3" },
2754 { "D", "4" },
2755 { "E", "5" },
2756 { "F", "6" },
2757 { "G", "7" }
2760 // Dimension definition
2761 DPFieldDef aFields[] = {
2762 { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
2763 { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
2766 ScAddress aPos(1,1,0);
2767 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2768 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2770 ScDPObject* pDPObj = createDPFromRange(
2771 m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2773 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2774 bool bSuccess = pDPs->InsertNewTable(pDPObj);
2776 CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2777 CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2778 pDPs->GetCount(), static_cast<size_t>(1));
2779 pDPObj->SetName(pDPs->CreateNewName());
2781 ScRange aOutRange = refresh(pDPObj);
2783 // Expected output table content. 0 = empty cell
2784 const char* aOutputCheck[][2] = {
2785 { "Name", 0 },
2786 { "A", "1" },
2787 { "B", "2" },
2788 { "C", "3" },
2789 { "D", "4" },
2790 { "E", "5" },
2791 { "F", "6" },
2792 { "G", "7" },
2793 { "Total Result", "28" }
2796 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Initial output without grouping");
2797 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2800 ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2801 CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
2802 ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
2803 CPPUNIT_ASSERT_MESSAGE("Failed to create dimension data.", pDimData);
2805 rtl::OUString aGroupPrefix("Group");
2806 rtl::OUString aBaseDimName("Name");
2807 rtl::OUString aGroupDimName =
2808 pDimData->CreateGroupDimName(aBaseDimName, *pDPObj, false, NULL);
2811 // Group A, B and C together.
2812 ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
2813 rtl::OUString aGroupName = aGroupDim.CreateGroupName(aGroupPrefix);
2814 CPPUNIT_ASSERT_MESSAGE("Unexpected group name", aGroupName.equalsAscii("Group1"));
2816 ScDPSaveGroupItem aGroup(aGroupName);
2817 aGroup.AddElement(rtl::OUString("A"));
2818 aGroup.AddElement(rtl::OUString("B"));
2819 aGroup.AddElement(rtl::OUString("C"));
2820 aGroupDim.AddGroupItem(aGroup);
2821 pDimData->AddGroupDimension(aGroupDim);
2823 ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
2824 pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
2825 pSaveData->SetPosition(pDim, 0); // Set it before the base dimension.
2828 pDPObj->SetSaveData(*pSaveData);
2829 aOutRange = refreshGroups(pDPs, pDPObj);
2831 // Expected output table content. 0 = empty cell
2832 const char* aOutputCheck[][3] = {
2833 { "Name2", "Name", 0 },
2834 { "D", "D", "4" },
2835 { "E", "E", "5" },
2836 { "F", "F", "6" },
2837 { "G", "G", "7" },
2838 { "Group1", "A", "1" },
2839 { 0, "B", "2" },
2840 { 0, "C", "3" },
2841 { "Total Result", 0, "28" }
2844 bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "A, B, C grouped by Group1.");
2845 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2848 pSaveData = pDPObj->GetSaveData();
2849 pDimData = pSaveData->GetDimensionData();
2852 // Group D, E, F together.
2853 ScDPSaveGroupDimension* pGroupDim = pDimData->GetGroupDimAccForBase(aBaseDimName);
2854 CPPUNIT_ASSERT_MESSAGE("There should be an existing group dimension.", pGroupDim);
2855 rtl::OUString aGroupName = pGroupDim->CreateGroupName(aGroupPrefix);
2856 CPPUNIT_ASSERT_MESSAGE("Unexpected group name", aGroupName.equalsAscii("Group2"));
2858 ScDPSaveGroupItem aGroup(aGroupName);
2859 aGroup.AddElement(rtl::OUString("D"));
2860 aGroup.AddElement(rtl::OUString("E"));
2861 aGroup.AddElement(rtl::OUString("F"));
2862 pGroupDim->AddGroupItem(aGroup);
2865 pDPObj->SetSaveData(*pSaveData);
2866 aOutRange = refreshGroups(pDPs, pDPObj);
2868 // Expected output table content. 0 = empty cell
2869 const char* aOutputCheck[][3] = {
2870 { "Name2", "Name", 0 },
2871 { "G", "G", "7" },
2872 { "Group1", "A", "1" },
2873 { 0, "B", "2" },
2874 { 0, "C", "3" },
2875 { "Group2", "D", "4" },
2876 { 0, "E", "5" },
2877 { 0, "F", "6" },
2878 { "Total Result", 0, "28" }
2881 bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "D, E, F grouped by Group2.");
2882 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2885 pDPs->FreeTable(pDPObj);
2886 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2887 CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2888 pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
2890 m_pDoc->DeleteTab(1);
2891 m_pDoc->DeleteTab(0);
2894 void Test::testPivotTableNumberGrouping()
2896 m_pDoc->InsertTab(0, OUString("Data"));
2897 m_pDoc->InsertTab(1, OUString("Table"));
2899 // Raw data
2900 const char* aData[][2] = {
2901 { "Order", "Score" },
2902 { "43", "171" },
2903 { "18", "20" },
2904 { "69", "159" },
2905 { "95", "19" },
2906 { "96", "163" },
2907 { "46", "70" },
2908 { "22", "36" },
2909 { "81", "49" },
2910 { "54", "61" },
2911 { "39", "62" },
2912 { "86", "17" },
2913 { "34", "0" },
2914 { "30", "25" },
2915 { "24", "103" },
2916 { "16", "59" },
2917 { "24", "119" },
2918 { "15", "86" },
2919 { "69", "170" }
2922 // Dimension definition
2923 DPFieldDef aFields[] = {
2924 { "Order", sheet::DataPilotFieldOrientation_ROW, 0 },
2925 { "Score", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
2928 ScAddress aPos(1,1,0);
2929 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
2930 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
2932 ScDPObject* pDPObj = createDPFromRange(
2933 m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2935 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2936 bool bSuccess = pDPs->InsertNewTable(pDPObj);
2938 CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
2939 CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2940 pDPs->GetCount(), static_cast<size_t>(1));
2941 pDPObj->SetName(pDPs->CreateNewName());
2943 ScDPSaveData* pSaveData = pDPObj->GetSaveData();
2944 CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
2945 ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
2946 CPPUNIT_ASSERT_MESSAGE("No dimension data !?", pDimData);
2949 ScDPNumGroupInfo aInfo;
2950 aInfo.mbEnable = true;
2951 aInfo.mbAutoStart = false;
2952 aInfo.mbAutoEnd = false;
2953 aInfo.mbDateValues = false;
2954 aInfo.mbIntegerOnly = true;
2955 aInfo.mfStart = 30;
2956 aInfo.mfEnd = 60;
2957 aInfo.mfStep = 10;
2958 ScDPSaveNumGroupDimension aGroup(rtl::OUString("Order"), aInfo);
2959 pDimData->AddNumGroupDimension(aGroup);
2962 pDPObj->SetSaveData(*pSaveData);
2963 ScRange aOutRange = refreshGroups(pDPs, pDPObj);
2965 // Expected output table content. 0 = empty cell
2966 const char* aOutputCheck[][2] = {
2967 { "Order", 0 },
2968 { "<30", "423" },
2969 { "30-39", "87" },
2970 { "40-49", "241" },
2971 { "50-60", "61" },
2972 { ">60", "577" },
2973 { "Total Result", "1389" }
2976 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Order grouped by numbers");
2977 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2980 pDPs->FreeTable(pDPObj);
2981 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
2982 CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2983 pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
2985 m_pDoc->DeleteTab(1);
2986 m_pDoc->DeleteTab(0);
2989 void Test::testPivotTableDateGrouping()
2991 m_pDoc->InsertTab(0, OUString("Data"));
2992 m_pDoc->InsertTab(1, OUString("Table"));
2994 // Raw data
2995 const char* aData[][2] = {
2996 { "Date", "Value" },
2997 { "2011-01-01", "1" },
2998 { "2011-03-02", "2" },
2999 { "2012-01-04", "3" },
3000 { "2012-02-23", "4" },
3001 { "2012-02-24", "5" },
3002 { "2012-03-15", "6" },
3003 { "2011-09-03", "7" },
3004 { "2012-12-25", "8" }
3007 // Dimension definition
3008 DPFieldDef aFields[] = {
3009 { "Date", sheet::DataPilotFieldOrientation_ROW, 0 },
3010 { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3013 ScAddress aPos(1,1,0);
3014 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
3015 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
3017 ScDPObject* pDPObj = createDPFromRange(
3018 m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3020 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3021 bool bSuccess = pDPs->InsertNewTable(pDPObj);
3023 CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3024 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3025 pDPs->GetCount() == 1);
3026 pDPObj->SetName(pDPs->CreateNewName());
3028 ScDPSaveData* pSaveData = pDPObj->GetSaveData();
3029 CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
3030 ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
3031 CPPUNIT_ASSERT_MESSAGE("No dimension data !?", pDimData);
3033 rtl::OUString aBaseDimName("Date");
3035 ScDPNumGroupInfo aInfo;
3036 aInfo.mbEnable = true;
3037 aInfo.mbAutoStart = true;
3038 aInfo.mbAutoEnd = true;
3040 // Turn the Date dimension into months. The first of the date
3041 // dimensions is always a number-group dimension which replaces the
3042 // original dimension.
3043 ScDPSaveNumGroupDimension aGroup(aBaseDimName, aInfo, sheet::DataPilotFieldGroupBy::MONTHS);
3044 pDimData->AddNumGroupDimension(aGroup);
3048 // Add quarter dimension. This will be an additional dimension.
3049 rtl::OUString aGroupDimName =
3050 pDimData->CreateDateGroupDimName(
3051 sheet::DataPilotFieldGroupBy::QUARTERS, *pDPObj, true, NULL);
3052 ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
3053 aGroupDim.SetDateInfo(aInfo, sheet::DataPilotFieldGroupBy::QUARTERS);
3054 pDimData->AddGroupDimension(aGroupDim);
3056 // Set orientation.
3057 ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
3058 pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
3059 pSaveData->SetPosition(pDim, 0); // set it to the left end.
3063 // Add year dimension. This is a new dimension also.
3064 rtl::OUString aGroupDimName =
3065 pDimData->CreateDateGroupDimName(
3066 sheet::DataPilotFieldGroupBy::YEARS, *pDPObj, true, NULL);
3067 ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
3068 aGroupDim.SetDateInfo(aInfo, sheet::DataPilotFieldGroupBy::YEARS);
3069 pDimData->AddGroupDimension(aGroupDim);
3071 // Set orientation.
3072 ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
3073 pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
3074 pSaveData->SetPosition(pDim, 0); // set it to the left end.
3077 pDPObj->SetSaveData(*pSaveData);
3078 ScRange aOutRange = refreshGroups(pDPs, pDPObj);
3080 // Expected output table content. 0 = empty cell
3081 const char* aOutputCheck[][4] = {
3082 { "Years", "Quarters", "Date", 0 },
3083 { "2011", "Q1", "Jan", "1" },
3084 { 0, 0, "Mar", "2" },
3085 { 0, "Q3", "Sep", "7" },
3086 { "2012", "Q1", "Jan", "3" },
3087 { 0, 0, "Feb", "9" },
3088 { 0, 0, "Mar", "6" },
3089 { 0, "Q4", "Dec", "8" },
3090 { "Total Result", 0, 0, "36" },
3093 bSuccess = checkDPTableOutput<4>(m_pDoc, aOutRange, aOutputCheck, "Years, quarters and months date groups.");
3094 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3098 // Let's hide year 2012.
3099 pSaveData = pDPObj->GetSaveData();
3100 ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(rtl::OUString("Years"));
3101 CPPUNIT_ASSERT_MESSAGE("Years dimension should exist.", pDim);
3102 ScDPSaveMember* pMem = pDim->GetMemberByName(rtl::OUString("2012"));
3103 CPPUNIT_ASSERT_MESSAGE("Member should exist.", pMem);
3104 pMem->SetIsVisible(false);
3106 pDPObj->SetSaveData(*pSaveData);
3107 pDPObj->ReloadGroupTableData();
3108 pDPObj->InvalidateData();
3110 aOutRange = refresh(pDPObj);
3112 // Expected output table content. 0 = empty cell
3113 const char* aOutputCheck[][4] = {
3114 { "Years", "Quarters", "Date", 0 },
3115 { "2011", "Q1", "Jan", "1" },
3116 { 0, 0, "Mar", "2" },
3117 { 0, "Q3", "Sep", "7" },
3118 { "Total Result", 0, 0, "10" },
3121 bSuccess = checkDPTableOutput<4>(m_pDoc, aOutRange, aOutputCheck, "Year 2012 data now hidden");
3122 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3125 // Remove all date grouping. The source dimension "Date" has two
3126 // external dimensions ("Years" and "Quarters") and one internal ("Date"
3127 // the same name but different hierarchy). Remove all of them.
3128 pSaveData = pDPObj->GetSaveData();
3129 pSaveData->RemoveAllGroupDimensions(aBaseDimName);
3130 pDPObj->SetSaveData(*pSaveData);
3131 pDPObj->ReloadGroupTableData();
3132 pDPObj->InvalidateData();
3134 aOutRange = refresh(pDPObj);
3136 // Expected output table content. 0 = empty cell
3137 const char* aOutputCheck[][2] = {
3138 { "Date", 0 },
3139 { "2011-01-01", "1" },
3140 { "2011-03-02", "2" },
3141 { "2011-09-03", "7" },
3142 { "2012-01-04", "3" },
3143 { "2012-02-23", "4" },
3144 { "2012-02-24", "5" },
3145 { "2012-03-15", "6" },
3146 { "2012-12-25", "8" },
3147 { "Total Result", "36" }
3150 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Remove all date grouping.");
3151 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3154 pDPs->FreeTable(pDPObj);
3155 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3156 CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3157 pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3159 m_pDoc->DeleteTab(1);
3160 m_pDoc->DeleteTab(0);
3163 void Test::testPivotTableEmptyRows()
3165 m_pDoc->InsertTab(0, OUString("Data"));
3166 m_pDoc->InsertTab(1, OUString("Table"));
3168 // Raw data
3169 const char* aData[][2] = {
3170 { "Name", "Value" },
3171 { "A", "1" },
3172 { "B", "2" },
3173 { "C", "3" },
3174 { "D", "4" },
3177 // Dimension definition
3178 DPFieldDef aFields[] = {
3179 { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3180 { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3183 ScAddress aPos(1,1,0);
3184 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
3185 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
3187 // Extend the range downward to include some trailing empty rows.
3188 aDataRange.aEnd.IncRow(2);
3190 ScDPObject* pDPObj = createDPFromRange(
3191 m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3193 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3194 bool bSuccess = pDPs->InsertNewTable(pDPObj);
3196 CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3197 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3198 pDPs->GetCount() == 1);
3199 pDPObj->SetName(pDPs->CreateNewName());
3201 ScRange aOutRange = refresh(pDPObj);
3204 // Expected output table content. 0 = empty cell
3205 const char* aOutputCheck[][2] = {
3206 { "Name", 0 },
3207 { "A", "1" },
3208 { "B", "2" },
3209 { "C", "3" },
3210 { "D", "4" },
3211 { "(empty)", 0 },
3212 { "Total Result", "10" },
3215 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Include empty rows");
3216 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3219 // This time, ignore empty rows.
3220 ScDPSaveData* pSaveData = pDPObj->GetSaveData();
3221 CPPUNIT_ASSERT_MESSAGE("Save data doesn't exist.", pSaveData);
3222 pSaveData->SetIgnoreEmptyRows(true);
3223 pDPObj->ClearTableData();
3224 aOutRange = refresh(pDPObj);
3227 // Expected output table content. 0 = empty cell
3228 const char* aOutputCheck[][2] = {
3229 { "Name", 0 },
3230 { "A", "1" },
3231 { "B", "2" },
3232 { "C", "3" },
3233 { "D", "4" },
3234 { "Total Result", "10" },
3237 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Ignore empty rows");
3238 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3241 // Modify the source to remove member 'A', then refresh the table.
3242 m_pDoc->SetString(1, 2, 0, "B");
3244 std::set<ScDPObject*> aRefs;
3245 sal_uLong nErr = pDPs->ReloadCache(pDPObj, aRefs);
3246 CPPUNIT_ASSERT_MESSAGE("Failed to reload cache.", !nErr);
3247 CPPUNIT_ASSERT_MESSAGE("There should only be one pivot table linked to this cache.",
3248 aRefs.size() == 1 && *aRefs.begin() == pDPObj);
3250 pDPObj->ClearTableData();
3251 aOutRange = refresh(pDPObj);
3254 // Expected output table content. 0 = empty cell
3255 const char* aOutputCheck[][2] = {
3256 { "Name", 0 },
3257 { "B", "3" },
3258 { "C", "3" },
3259 { "D", "4" },
3260 { "Total Result", "10" },
3263 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Ignore empty rows");
3264 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3267 pDPs->FreeTable(pDPObj);
3268 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3269 CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3270 pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3272 m_pDoc->DeleteTab(1);
3273 m_pDoc->DeleteTab(0);
3276 void Test::testPivotTableTextNumber()
3278 m_pDoc->InsertTab(0, OUString("Data"));
3279 m_pDoc->InsertTab(1, OUString("Table"));
3281 // Raw data
3282 const char* aData[][2] = {
3283 { "Name", "Value" },
3284 { "0001", "1" },
3285 { "0002", "2" },
3286 { "0003", "3" },
3287 { "0004", "4" },
3290 // Dimension definition
3291 DPFieldDef aFields[] = {
3292 { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3293 { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3296 // Insert raw data such that the first column values are entered as text.
3297 size_t nRowCount = SAL_N_ELEMENTS(aData);
3298 for (size_t nRow = 0; nRow < nRowCount; ++nRow)
3300 ScSetStringParam aParam;
3301 aParam.mbDetectNumberFormat = false;
3302 aParam.meSetTextNumFormat = ScSetStringParam::Always;
3303 m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aData[nRow][0]), &aParam);
3304 aParam.meSetTextNumFormat = ScSetStringParam::Never;
3305 m_pDoc->SetString(1, nRow, 0, OUString::createFromAscii(aData[nRow][1]), &aParam);
3307 if (nRow == 0)
3308 // Don't check the header row.
3309 continue;
3311 // Check the data rows.
3312 CPPUNIT_ASSERT_MESSAGE("This cell is supposed to be text.", m_pDoc->HasStringData(0, nRow, 0));
3313 CPPUNIT_ASSERT_MESSAGE("This cell is supposed to be numeric.", m_pDoc->HasValueData(1, nRow, 0));
3316 ScRange aDataRange(0, 0, 0, 1, 4, 0);
3318 ScDPObject* pDPObj = createDPFromRange(
3319 m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3321 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3322 bool bSuccess = pDPs->InsertNewTable(pDPObj);
3324 CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3325 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3326 pDPs->GetCount() == 1);
3327 pDPObj->SetName(pDPs->CreateNewName());
3329 ScRange aOutRange = refresh(pDPObj);
3332 // Expected output table content. 0 = empty cell
3333 const char* aOutputCheck[][2] = {
3334 { "Name", 0 },
3335 { "0001", "1" },
3336 { "0002", "2" },
3337 { "0003", "3" },
3338 { "0004", "4" },
3339 { "Total Result", "10" },
3342 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Text number field members");
3343 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3346 pDPs->FreeTable(pDPObj);
3347 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3348 CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3349 pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3351 m_pDoc->DeleteTab(1);
3352 m_pDoc->DeleteTab(0);
3355 void Test::testPivotTableCaseInsensitiveStrings()
3357 m_pDoc->InsertTab(0, OUString("Data"));
3358 m_pDoc->InsertTab(1, OUString("Table"));
3360 // Raw data
3361 const char* aData[][2] = {
3362 { "Name", "Value" },
3363 { "A", "1" },
3364 { "a", "2" },
3367 // Dimension definition
3368 DPFieldDef aFields[] = {
3369 { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3370 { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3373 ScAddress aPos(1,1,0);
3374 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
3375 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
3377 ScDPObject* pDPObj = createDPFromRange(
3378 m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3380 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3381 bool bSuccess = pDPs->InsertNewTable(pDPObj);
3383 CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3384 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3385 pDPs->GetCount() == 1);
3386 pDPObj->SetName(pDPs->CreateNewName());
3388 ScRange aOutRange = refresh(pDPObj);
3391 // Expected output table content. 0 = empty cell
3392 const char* aOutputCheck[][2] = {
3393 { "Name", 0 },
3394 { "A", "3" },
3395 { "Total Result", "3" },
3398 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Case insensitive strings");
3399 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3402 pDPs->FreeTable(pDPObj);
3403 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3404 CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3405 pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3407 m_pDoc->DeleteTab(1);
3408 m_pDoc->DeleteTab(0);
3411 void Test::testPivotTableNumStability()
3413 FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
3415 // Raw Data
3416 const char* aData[][4] = {
3417 { "Name", "Time Start", "Time End", "Total" },
3418 { "Sam", "07:48 AM", "09:00 AM", "=RC[-1]-RC[-2]" },
3419 { "Sam", "09:00 AM", "10:30 AM", "=RC[-1]-RC[-2]" },
3420 { "Sam", "10:30 AM", "12:30 PM", "=RC[-1]-RC[-2]" },
3421 { "Sam", "12:30 PM", "01:00 PM", "=RC[-1]-RC[-2]" },
3422 { "Sam", "01:00 PM", "01:30 PM", "=RC[-1]-RC[-2]" },
3423 { "Sam", "01:30 PM", "02:00 PM", "=RC[-1]-RC[-2]" },
3424 { "Sam", "02:00 PM", "07:15 PM", "=RC[-1]-RC[-2]" },
3425 { "Sam", "07:47 AM", "09:00 AM", "=RC[-1]-RC[-2]" },
3426 { "Sam", "09:00 AM", "10:00 AM", "=RC[-1]-RC[-2]" },
3427 { "Sam", "10:00 AM", "11:00 AM", "=RC[-1]-RC[-2]" },
3428 { "Sam", "11:00 AM", "11:30 AM", "=RC[-1]-RC[-2]" },
3429 { "Sam", "11:30 AM", "12:45 PM", "=RC[-1]-RC[-2]" },
3430 { "Sam", "12:45 PM", "01:15 PM", "=RC[-1]-RC[-2]" },
3431 { "Sam", "01:15 PM", "02:30 PM", "=RC[-1]-RC[-2]" },
3432 { "Sam", "02:30 PM", "02:45 PM", "=RC[-1]-RC[-2]" },
3433 { "Sam", "02:45 PM", "04:30 PM", "=RC[-1]-RC[-2]" },
3434 { "Sam", "04:30 PM", "06:00 PM", "=RC[-1]-RC[-2]" },
3435 { "Sam", "06:00 PM", "07:15 PM", "=RC[-1]-RC[-2]" },
3436 { "Mike", "06:15 AM", "08:30 AM", "=RC[-1]-RC[-2]" },
3437 { "Mike", "08:30 AM", "10:03 AM", "=RC[-1]-RC[-2]" },
3438 { "Mike", "10:03 AM", "12:00 PM", "=RC[-1]-RC[-2]" },
3439 { "Dennis", "11:00 AM", "01:00 PM", "=RC[-1]-RC[-2]" },
3440 { "Dennis", "01:00 PM", "02:00 PM", "=RC[-1]-RC[-2]" }
3443 // Dimension definition
3444 DPFieldDef aFields[] = {
3445 { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3446 { "Total", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3449 m_pDoc->InsertTab(0, OUString("Data"));
3450 m_pDoc->InsertTab(1, OUString("Table"));
3452 size_t nRowCount = SAL_N_ELEMENTS(aData);
3453 ScAddress aPos(1,1,0);
3454 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, nRowCount);
3456 // Insert formulas to manually calculate sums for each name.
3457 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])");
3458 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])");
3459 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])");
3461 m_pDoc->CalcAll();
3463 // Get correct sum values.
3464 double fDennisTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+1, aDataRange.aStart.Tab());
3465 double fMikeTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+2, aDataRange.aStart.Tab());
3466 double fSamTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+3, aDataRange.aStart.Tab());
3468 ScDPObject* pDPObj = createDPFromRange(
3469 m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3471 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3472 bool bSuccess = pDPs->InsertNewTable(pDPObj);
3474 CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3475 CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
3476 pDPs->GetCount(), static_cast<size_t>(1));
3477 pDPObj->SetName(pDPs->CreateNewName());
3479 ScRange aOutRange = refresh(pDPObj);
3481 // Manually check the total value for each name.
3483 // +--------------+----------------+
3484 // | Name | |
3485 // +--------------+----------------+
3486 // | Dennis | <Dennis total> |
3487 // +--------------+----------------+
3488 // | Mike | <Miks total> |
3489 // +--------------+----------------+
3490 // | Sam | <Sam total> |
3491 // +--------------+----------------+
3492 // | Total Result | ... |
3493 // +--------------+----------------+
3495 aPos = aOutRange.aStart;
3496 aPos.IncCol();
3497 aPos.IncRow();
3498 double fTest = m_pDoc->GetValue(aPos);
3499 CPPUNIT_ASSERT_MESSAGE("Incorrect value for Dennis.", rtl::math::approxEqual(fTest, fDennisTotal));
3500 aPos.IncRow();
3501 fTest = m_pDoc->GetValue(aPos);
3502 CPPUNIT_ASSERT_MESSAGE("Incorrect value for Mike.", rtl::math::approxEqual(fTest, fMikeTotal));
3503 aPos.IncRow();
3504 fTest = m_pDoc->GetValue(aPos);
3505 CPPUNIT_ASSERT_MESSAGE("Incorrect value for Sam.", rtl::math::approxEqual(fTest, fSamTotal));
3507 pDPs->FreeTable(pDPObj);
3508 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3509 CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3510 pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3512 m_pDoc->DeleteTab(1);
3513 m_pDoc->DeleteTab(0);
3516 void Test::testPivotTableFieldReference()
3518 m_pDoc->InsertTab(0, OUString("Data"));
3519 m_pDoc->InsertTab(1, OUString("Table"));
3521 // Raw data
3522 const char* aData[][2] = {
3523 { "Name", "Value" },
3524 { "A", "1" },
3525 { "B", "2" },
3526 { "C", "4" },
3527 { "D", "8" },
3530 // Dimension definition
3531 DPFieldDef aFields[] = {
3532 { "Name", sheet::DataPilotFieldOrientation_ROW, 0 },
3533 { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
3536 ScAddress aPos(1,1,0);
3537 ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
3538 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
3540 ScDPObject* pDPObj = createDPFromRange(
3541 m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
3543 ScDPCollection* pDPs = m_pDoc->GetDPCollection();
3544 bool bSuccess = pDPs->InsertNewTable(pDPObj);
3546 CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
3547 CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
3548 pDPs->GetCount() == 1);
3549 pDPObj->SetName(pDPs->CreateNewName());
3551 ScRange aOutRange = refresh(pDPObj);
3554 // Expected output table content. 0 = empty cell
3555 const char* aOutputCheck[][2] = {
3556 { "Name", 0 },
3557 { "A", "1" },
3558 { "B", "2" },
3559 { "C", "4" },
3560 { "D", "8" },
3561 { "Total Result", "15" },
3564 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Field reference (none)");
3565 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3568 ScDPSaveData aSaveData = *pDPObj->GetSaveData();
3569 sheet::DataPilotFieldReference aFieldRef;
3570 aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::ITEM_DIFFERENCE;
3571 aFieldRef.ReferenceField = "Name";
3572 aFieldRef.ReferenceItemType = sheet::DataPilotFieldReferenceItemType::NAMED;
3573 aFieldRef.ReferenceItemName = "A";
3574 ScDPSaveDimension* pDim = aSaveData.GetDimensionByName("Value");
3575 CPPUNIT_ASSERT_MESSAGE("Failed to retrieve dimension 'Value'.", pDim);
3576 pDim->SetReferenceValue(&aFieldRef);
3577 pDPObj->SetSaveData(aSaveData);
3579 aOutRange = refresh(pDPObj);
3581 // Expected output table content. 0 = empty cell
3582 const char* aOutputCheck[][2] = {
3583 { "Name", 0 },
3584 { "A", 0 },
3585 { "B", "1" },
3586 { "C", "3" },
3587 { "D", "7" },
3588 { "Total Result", 0 },
3591 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Field reference (difference from)");
3592 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3595 aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::ITEM_PERCENTAGE;
3596 pDim->SetReferenceValue(&aFieldRef);
3597 pDPObj->SetSaveData(aSaveData);
3599 aOutRange = refresh(pDPObj);
3601 // Expected output table content. 0 = empty cell
3602 const char* aOutputCheck[][2] = {
3603 { "Name", 0 },
3604 { "A", "100.00%" },
3605 { "B", "200.00%" },
3606 { "C", "400.00%" },
3607 { "D", "800.00%" },
3608 { "Total Result", 0 },
3611 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Field reference (% of)");
3612 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3615 aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::ITEM_PERCENTAGE_DIFFERENCE;
3616 pDim->SetReferenceValue(&aFieldRef);
3617 pDPObj->SetSaveData(aSaveData);
3619 aOutRange = refresh(pDPObj);
3621 // Expected output table content. 0 = empty cell
3622 const char* aOutputCheck[][2] = {
3623 { "Name", 0 },
3624 { "A", 0 },
3625 { "B", "100.00%" },
3626 { "C", "300.00%" },
3627 { "D", "700.00%" },
3628 { "Total Result", 0 },
3631 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Field reference (% difference from)");
3632 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3635 aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::RUNNING_TOTAL;
3636 pDim->SetReferenceValue(&aFieldRef);
3637 pDPObj->SetSaveData(aSaveData);
3639 aOutRange = refresh(pDPObj);
3641 // Expected output table content. 0 = empty cell
3642 const char* aOutputCheck[][2] = {
3643 { "Name", 0 },
3644 { "A", "1" },
3645 { "B", "3" },
3646 { "C", "7" },
3647 { "D", "15" },
3648 { "Total Result", 0 },
3651 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Field reference (Running total)");
3652 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3655 aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::COLUMN_PERCENTAGE;
3656 pDim->SetReferenceValue(&aFieldRef);
3657 pDPObj->SetSaveData(aSaveData);
3659 aOutRange = refresh(pDPObj);
3661 // Expected output table content. 0 = empty cell
3662 const char* aOutputCheck[][2] = {
3663 { "Name", 0 },
3664 { "A", "6.67%" },
3665 { "B", "13.33%" },
3666 { "C", "26.67%" },
3667 { "D", "53.33%" },
3668 { "Total Result", "100.00%" },
3671 bSuccess = checkDPTableOutput<2>(m_pDoc, aOutRange, aOutputCheck, "Field reference (% of column)");
3672 CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
3675 pDPs->FreeTable(pDPObj);
3676 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
3677 CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
3678 pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
3680 m_pDoc->DeleteTab(1);
3681 m_pDoc->DeleteTab(0);
3684 void Test::testSheetCopy()
3686 OUString aTabName("TestTab");
3687 m_pDoc->InsertTab(0, aTabName);
3688 CPPUNIT_ASSERT_MESSAGE("document should have one sheet to begin with.", m_pDoc->GetTableCount() == 1);
3689 SCROW nRow1, nRow2;
3690 bool bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3691 CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3693 // Copy and test the result.
3694 m_pDoc->CopyTab(0, 1);
3695 CPPUNIT_ASSERT_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount() == 2);
3696 bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3697 CPPUNIT_ASSERT_MESSAGE("copied sheet should also have all rows visible as the original.", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3698 m_pDoc->DeleteTab(1);
3700 m_pDoc->SetRowHidden(5, 10, 0, true);
3701 bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3702 CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3703 bHidden = m_pDoc->RowHidden(5, 0, &nRow1, &nRow2);
3704 CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3705 bHidden = m_pDoc->RowHidden(11, 0, &nRow1, &nRow2);
3706 CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3708 // Copy the sheet once again.
3709 m_pDoc->CopyTab(0, 1);
3710 CPPUNIT_ASSERT_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount() == 2);
3711 bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3712 CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3713 bHidden = m_pDoc->RowHidden(5, 1, &nRow1, &nRow2);
3714 CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3715 bHidden = m_pDoc->RowHidden(11, 1, &nRow1, &nRow2);
3716 CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3717 m_pDoc->DeleteTab(1);
3718 m_pDoc->DeleteTab(0);
3721 void Test::testSheetMove()
3723 OUString aTabName("TestTab1");
3724 m_pDoc->InsertTab(0, aTabName);
3725 CPPUNIT_ASSERT_EQUAL_MESSAGE("document should have one sheet to begin with.", m_pDoc->GetTableCount(), static_cast<SCTAB>(1));
3726 SCROW nRow1, nRow2;
3727 bool bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3728 CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3730 //test if inserting before another sheet works
3731 m_pDoc->InsertTab(0, OUString("TestTab2"));
3732 CPPUNIT_ASSERT_EQUAL_MESSAGE("document should have two sheets", m_pDoc->GetTableCount(), static_cast<SCTAB>(2));
3733 bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3734 CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3736 // Move and test the result.
3737 m_pDoc->MoveTab(0, 1);
3738 CPPUNIT_ASSERT_EQUAL_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount(), static_cast<SCTAB>(2));
3739 bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3740 CPPUNIT_ASSERT_MESSAGE("copied sheet should also have all rows visible as the original.", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
3741 rtl::OUString aName;
3742 m_pDoc->GetName(0, aName);
3743 CPPUNIT_ASSERT_MESSAGE( "sheets should have changed places", aName == "TestTab1" );
3745 m_pDoc->SetRowHidden(5, 10, 0, true);
3746 bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
3747 CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3748 bHidden = m_pDoc->RowHidden(5, 0, &nRow1, &nRow2);
3749 CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3750 bHidden = m_pDoc->RowHidden(11, 0, &nRow1, &nRow2);
3751 CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3753 // Move the sheet once again.
3754 m_pDoc->MoveTab(1, 0);
3755 CPPUNIT_ASSERT_EQUAL_MESSAGE("document now should have two sheets.", m_pDoc->GetTableCount(), static_cast<SCTAB>(2));
3756 bHidden = m_pDoc->RowHidden(0, 1, &nRow1, &nRow2);
3757 CPPUNIT_ASSERT_MESSAGE("rows 0 - 4 should be visible", !bHidden && nRow1 == 0 && nRow2 == 4);
3758 bHidden = m_pDoc->RowHidden(5, 1, &nRow1, &nRow2);
3759 CPPUNIT_ASSERT_MESSAGE("rows 5 - 10 should be hidden", bHidden && nRow1 == 5 && nRow2 == 10);
3760 bHidden = m_pDoc->RowHidden(11, 1, &nRow1, &nRow2);
3761 CPPUNIT_ASSERT_MESSAGE("rows 11 - maxrow should be visible", !bHidden && nRow1 == 11 && nRow2 == MAXROW);
3762 m_pDoc->GetName(0, aName);
3763 CPPUNIT_ASSERT_MESSAGE( "sheets should have changed places", aName == "TestTab2" );
3764 m_pDoc->DeleteTab(1);
3765 m_pDoc->DeleteTab(0);
3768 ScDocShell* findLoadedDocShellByName(const OUString& rName)
3770 TypeId aType(TYPE(ScDocShell));
3771 ScDocShell* pShell = static_cast<ScDocShell*>(SfxObjectShell::GetFirst(&aType, false));
3772 while (pShell)
3774 SfxMedium* pMedium = pShell->GetMedium();
3775 if (pMedium)
3777 OUString aName = pMedium->GetName();
3778 if (aName.equals(rName))
3779 return pShell;
3781 pShell = static_cast<ScDocShell*>(SfxObjectShell::GetNext(*pShell, &aType, false));
3783 return NULL;
3786 ScRange getCachedRange(const ScExternalRefCache::TableTypeRef& pCacheTab)
3788 ScRange aRange;
3790 vector<SCROW> aRows;
3791 pCacheTab->getAllRows(aRows);
3792 vector<SCROW>::const_iterator itrRow = aRows.begin(), itrRowEnd = aRows.end();
3793 bool bFirst = true;
3794 for (; itrRow != itrRowEnd; ++itrRow)
3796 SCROW nRow = *itrRow;
3797 vector<SCCOL> aCols;
3798 pCacheTab->getAllCols(nRow, aCols);
3799 vector<SCCOL>::const_iterator itrCol = aCols.begin(), itrColEnd = aCols.end();
3800 for (; itrCol != itrColEnd; ++itrCol)
3802 SCCOL nCol = *itrCol;
3803 if (bFirst)
3805 aRange.aStart = ScAddress(nCol, nRow, 0);
3806 aRange.aEnd = aRange.aStart;
3807 bFirst = false;
3809 else
3811 if (nCol < aRange.aStart.Col())
3812 aRange.aStart.SetCol(nCol);
3813 else if (aRange.aEnd.Col() < nCol)
3814 aRange.aEnd.SetCol(nCol);
3816 if (nRow < aRange.aStart.Row())
3817 aRange.aStart.SetRow(nRow);
3818 else if (aRange.aEnd.Row() < nRow)
3819 aRange.aEnd.SetRow(nRow);
3823 return aRange;
3826 void Test::testExternalRef()
3828 ScDocShellRef xExtDocSh = new ScDocShell;
3829 OUString aExtDocName("file:///extdata.fake");
3830 OUString aExtSh1Name("Data1");
3831 OUString aExtSh2Name("Data2");
3832 OUString aExtSh3Name("Data3");
3833 SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
3834 xExtDocSh->DoInitNew(pMed);
3835 CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
3836 findLoadedDocShellByName(aExtDocName) != NULL);
3838 // Populate the external source document.
3839 ScDocument* pExtDoc = xExtDocSh->GetDocument();
3840 pExtDoc->InsertTab(0, aExtSh1Name);
3841 pExtDoc->InsertTab(1, aExtSh2Name);
3842 pExtDoc->InsertTab(2, aExtSh3Name);
3844 OUString name("Name");
3845 OUString value("Value");
3846 OUString andy("Andy");
3847 OUString bruce("Bruce");
3848 OUString charlie("Charlie");
3849 OUString david("David");
3850 OUString edward("Edward");
3851 OUString frank("Frank");
3852 OUString george("George");
3853 OUString henry("Henry");
3855 // Sheet 1
3856 pExtDoc->SetString(0, 0, 0, name);
3857 pExtDoc->SetString(0, 1, 0, andy);
3858 pExtDoc->SetString(0, 2, 0, bruce);
3859 pExtDoc->SetString(0, 3, 0, charlie);
3860 pExtDoc->SetString(0, 4, 0, david);
3861 pExtDoc->SetString(1, 0, 0, value);
3862 double val = 10;
3863 pExtDoc->SetValue(1, 1, 0, val);
3864 val = 11;
3865 pExtDoc->SetValue(1, 2, 0, val);
3866 val = 12;
3867 pExtDoc->SetValue(1, 3, 0, val);
3868 val = 13;
3869 pExtDoc->SetValue(1, 4, 0, val);
3871 // Sheet 2 remains empty.
3873 // Sheet 3
3874 pExtDoc->SetString(0, 0, 2, name);
3875 pExtDoc->SetString(0, 1, 2, edward);
3876 pExtDoc->SetString(0, 2, 2, frank);
3877 pExtDoc->SetString(0, 3, 2, george);
3878 pExtDoc->SetString(0, 4, 2, henry);
3879 pExtDoc->SetString(1, 0, 2, value);
3880 val = 99;
3881 pExtDoc->SetValue(1, 1, 2, val);
3882 val = 98;
3883 pExtDoc->SetValue(1, 2, 2, val);
3884 val = 97;
3885 pExtDoc->SetValue(1, 3, 2, val);
3886 val = 96;
3887 pExtDoc->SetValue(1, 4, 2, val);
3889 // Test external refernces on the main document while the external
3890 // document is still in memory.
3891 OUString test;
3892 m_pDoc->InsertTab(0, OUString("Test Sheet"));
3893 m_pDoc->SetString(0, 0, 0, OUString("='file:///extdata.fake'#Data1.A1"));
3894 m_pDoc->GetString(0, 0, 0, test);
3895 CPPUNIT_ASSERT_MESSAGE("Value is different from the original", test.equals(name));
3897 // After the initial access to the external document, the external ref
3898 // manager should create sheet cache entries for *all* sheets from that
3899 // document. Note that the doc may have more than 3 sheets but ensure
3900 // that the first 3 are what we expect.
3901 ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
3902 sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
3903 vector<OUString> aTabNames;
3904 pRefMgr->getAllCachedTableNames(nFileId, aTabNames);
3905 CPPUNIT_ASSERT_MESSAGE("There should be at least 3 sheets.", aTabNames.size() >= 3);
3906 CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[0].equals(aExtSh1Name));
3907 CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[1].equals(aExtSh2Name));
3908 CPPUNIT_ASSERT_MESSAGE("Unexpected sheet name.", aTabNames[2].equals(aExtSh3Name));
3910 m_pDoc->SetString(1, 0, 0, OUString("='file:///extdata.fake'#Data1.B1"));
3911 m_pDoc->GetString(1, 0, 0, test);
3912 CPPUNIT_ASSERT_MESSAGE("Value is different from the original", test.equals(value));
3914 m_pDoc->SetString(0, 1, 0, OUString("='file:///extdata.fake'#Data1.A2"));
3915 m_pDoc->SetString(0, 2, 0, OUString("='file:///extdata.fake'#Data1.A3"));
3916 m_pDoc->SetString(0, 3, 0, OUString("='file:///extdata.fake'#Data1.A4"));
3917 m_pDoc->SetString(0, 4, 0, OUString("='file:///extdata.fake'#Data1.A5"));
3918 m_pDoc->SetString(0, 5, 0, OUString("='file:///extdata.fake'#Data1.A6"));
3921 // Referencing an empty cell should display '0'.
3922 const char* pChecks[] = { "Andy", "Bruce", "Charlie", "David", "0" };
3923 for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3925 m_pDoc->GetString(0, static_cast<SCROW>(i+1), 0, test);
3926 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3929 m_pDoc->SetString(1, 1, 0, OUString("='file:///extdata.fake'#Data1.B2"));
3930 m_pDoc->SetString(1, 2, 0, OUString("='file:///extdata.fake'#Data1.B3"));
3931 m_pDoc->SetString(1, 3, 0, OUString("='file:///extdata.fake'#Data1.B4"));
3932 m_pDoc->SetString(1, 4, 0, OUString("='file:///extdata.fake'#Data1.B5"));
3933 m_pDoc->SetString(1, 5, 0, OUString("='file:///extdata.fake'#Data1.B6"));
3935 double pChecks[] = { 10, 11, 12, 13, 0 };
3936 for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3938 m_pDoc->GetValue(1, static_cast<SCROW>(i+1), 0, val);
3939 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", val == pChecks[i]);
3943 m_pDoc->SetString(2, 0, 0, OUString("='file:///extdata.fake'#Data3.A1"));
3944 m_pDoc->SetString(2, 1, 0, OUString("='file:///extdata.fake'#Data3.A2"));
3945 m_pDoc->SetString(2, 2, 0, OUString("='file:///extdata.fake'#Data3.A3"));
3946 m_pDoc->SetString(2, 3, 0, OUString("='file:///extdata.fake'#Data3.A4"));
3948 const char* pChecks[] = { "Name", "Edward", "Frank", "George" };
3949 for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3951 m_pDoc->GetString(2, static_cast<SCROW>(i), 0, test);
3952 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3956 m_pDoc->SetString(3, 0, 0, OUString("='file:///extdata.fake'#Data3.B1"));
3957 m_pDoc->SetString(3, 1, 0, OUString("='file:///extdata.fake'#Data3.B2"));
3958 m_pDoc->SetString(3, 2, 0, OUString("='file:///extdata.fake'#Data3.B3"));
3959 m_pDoc->SetString(3, 3, 0, OUString("='file:///extdata.fake'#Data3.B4"));
3961 const char* pChecks[] = { "Value", "99", "98", "97" };
3962 for (size_t i = 0; i < SAL_N_ELEMENTS(pChecks); ++i)
3964 m_pDoc->GetString(3, static_cast<SCROW>(i), 0, test);
3965 CPPUNIT_ASSERT_MESSAGE("Unexpected cell value.", test.equalsAscii(pChecks[i]));
3969 // At this point, all accessed cell data from the external document should
3970 // have been cached.
3971 ScExternalRefCache::TableTypeRef pCacheTab = pRefMgr->getCacheTable(
3972 nFileId, aExtSh1Name, false);
3973 CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 1 should exist.", pCacheTab.get() != NULL);
3974 ScRange aCachedRange = getCachedRange(pCacheTab);
3975 CPPUNIT_ASSERT_MESSAGE("Unexpected cached data range.",
3976 aCachedRange.aStart.Col() == 0 && aCachedRange.aEnd.Col() == 1 &&
3977 aCachedRange.aStart.Row() == 0 && aCachedRange.aEnd.Row() == 4);
3979 // Sheet2 is not referenced at all; the cache table shouldn't even exist.
3980 pCacheTab = pRefMgr->getCacheTable(nFileId, aExtSh2Name, false);
3981 CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 2 should *not* exist.", pCacheTab.get() == NULL);
3983 // Sheet3's row 5 is not referenced; it should not be cached.
3984 pCacheTab = pRefMgr->getCacheTable(nFileId, aExtSh3Name, false);
3985 CPPUNIT_ASSERT_MESSAGE("Cache table for sheet 3 should exist.", pCacheTab.get() != NULL);
3986 aCachedRange = getCachedRange(pCacheTab);
3987 CPPUNIT_ASSERT_MESSAGE("Unexpected cached data range.",
3988 aCachedRange.aStart.Col() == 0 && aCachedRange.aEnd.Col() == 1 &&
3989 aCachedRange.aStart.Row() == 0 && aCachedRange.aEnd.Row() == 3);
3991 // Unload the external document shell.
3992 xExtDocSh->DoClose();
3993 CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
3994 findLoadedDocShellByName(aExtDocName) == NULL);
3996 m_pDoc->DeleteTab(0);
3999 void testExtRefFuncT(ScDocument* pDoc, ScDocument* pExtDoc)
4001 clearRange(pDoc, ScRange(0, 0, 0, 1, 9, 0));
4002 clearRange(pExtDoc, ScRange(0, 0, 0, 1, 9, 0));
4004 pExtDoc->SetString(0, 0, 0, rtl::OUString("'1.2"));
4005 pExtDoc->SetString(0, 1, 0, rtl::OUString("Foo"));
4006 pExtDoc->SetValue(0, 2, 0, 12.3);
4007 pDoc->SetString(0, 0, 0, rtl::OUString("=T('file:///extdata.fake'#Data.A1)"));
4008 pDoc->SetString(0, 1, 0, rtl::OUString("=T('file:///extdata.fake'#Data.A2)"));
4009 pDoc->SetString(0, 2, 0, rtl::OUString("=T('file:///extdata.fake'#Data.A3)"));
4010 pDoc->CalcAll();
4012 rtl::OUString aRes = pDoc->GetString(0, 0, 0);
4013 CPPUNIT_ASSERT_MESSAGE( "Unexpected result with T.", aRes == "1.2" );
4014 aRes = pDoc->GetString(0, 1, 0);
4015 CPPUNIT_ASSERT_MESSAGE( "Unexpected result with T.", aRes == "Foo" );
4016 aRes = pDoc->GetString(0, 2, 0);
4017 CPPUNIT_ASSERT_MESSAGE("Unexpected result with T.", aRes.isEmpty());
4020 void Test::testExternalRefFunctions()
4022 ScDocShellRef xExtDocSh = new ScDocShell;
4023 OUString aExtDocName("file:///extdata.fake");
4024 SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
4025 xExtDocSh->DoInitNew(pMed);
4026 CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
4027 findLoadedDocShellByName(aExtDocName) != NULL);
4029 ScExternalRefManager* pRefMgr = m_pDoc->GetExternalRefManager();
4030 CPPUNIT_ASSERT_MESSAGE("external reference manager doesn't exist.", pRefMgr);
4031 sal_uInt16 nFileId = pRefMgr->getExternalFileId(aExtDocName);
4032 const OUString* pFileName = pRefMgr->getExternalFileName(nFileId);
4033 CPPUNIT_ASSERT_MESSAGE("file name registration has somehow failed.",
4034 pFileName && pFileName->equals(aExtDocName));
4036 // Populate the external source document.
4037 ScDocument* pExtDoc = xExtDocSh->GetDocument();
4038 pExtDoc->InsertTab(0, rtl::OUString("Data"));
4039 double val = 1;
4040 pExtDoc->SetValue(0, 0, 0, val);
4041 // leave cell B1 empty.
4042 val = 2;
4043 pExtDoc->SetValue(0, 1, 0, val);
4044 pExtDoc->SetValue(1, 1, 0, val);
4045 val = 3;
4046 pExtDoc->SetValue(0, 2, 0, val);
4047 pExtDoc->SetValue(1, 2, 0, val);
4048 val = 4;
4049 pExtDoc->SetValue(0, 3, 0, val);
4050 pExtDoc->SetValue(1, 3, 0, val);
4052 m_pDoc->InsertTab(0, rtl::OUString("Test"));
4054 struct {
4055 const char* pFormula; double fResult;
4056 } aChecks[] = {
4057 { "=SUM('file:///extdata.fake'#Data.A1:A4)", 10 },
4058 { "=SUM('file:///extdata.fake'#Data.B1:B4)", 9 },
4059 { "=AVERAGE('file:///extdata.fake'#Data.A1:A4)", 2.5 },
4060 { "=AVERAGE('file:///extdata.fake'#Data.B1:B4)", 3 },
4061 { "=COUNT('file:///extdata.fake'#Data.A1:A4)", 4 },
4062 { "=COUNT('file:///extdata.fake'#Data.B1:B4)", 3 }
4065 for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
4067 m_pDoc->SetString(0, 0, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
4068 m_pDoc->CalcAll();
4069 m_pDoc->GetValue(0, 0, 0, val);
4070 CPPUNIT_ASSERT_MESSAGE("unexpected result involving external ranges.", val == aChecks[i].fResult);
4073 pRefMgr->clearCache(nFileId);
4074 testExtRefFuncT(m_pDoc, pExtDoc);
4076 // Unload the external document shell.
4077 xExtDocSh->DoClose();
4078 CPPUNIT_ASSERT_MESSAGE("external document instance should have been unloaded.",
4079 findLoadedDocShellByName(aExtDocName) == NULL);
4081 m_pDoc->DeleteTab(0);
4084 void Test::testDataArea()
4086 m_pDoc->InsertTab(0, OUString("Data"));
4088 // Totally empty sheet should be rightfully considered empty in all accounts.
4089 CPPUNIT_ASSERT_MESSAGE("Sheet is expected to be empty.", m_pDoc->IsPrintEmpty(0, 0, 0, 100, 100));
4090 CPPUNIT_ASSERT_MESSAGE("Sheet is expected to be empty.", m_pDoc->IsBlockEmpty(0, 0, 0, 100, 100));
4092 // Now, set borders in some cells....
4093 ::editeng::SvxBorderLine aLine(NULL, 50, table::BorderLineStyle::SOLID);
4094 SvxBoxItem aBorderItem(ATTR_BORDER);
4095 aBorderItem.SetLine(&aLine, BOX_LINE_LEFT);
4096 aBorderItem.SetLine(&aLine, BOX_LINE_RIGHT);
4097 for (SCROW i = 0; i < 100; ++i)
4098 // Set borders from row 1 to 100.
4099 m_pDoc->ApplyAttr(0, i, 0, aBorderItem);
4101 // Now the sheet is considered non-empty for printing purposes, but still
4102 // be empty in all the other cases.
4103 CPPUNIT_ASSERT_MESSAGE("Empty sheet with borders should be printable.",
4104 !m_pDoc->IsPrintEmpty(0, 0, 0, 100, 100));
4105 CPPUNIT_ASSERT_MESSAGE("But it should still be considered empty in all the other cases.",
4106 m_pDoc->IsBlockEmpty(0, 0, 0, 100, 100));
4108 // Adding a real cell content should turn the block non-empty.
4109 m_pDoc->SetString(0, 0, 0, OUString("Some text"));
4110 CPPUNIT_ASSERT_MESSAGE("Now the block should not be empty with a real cell content.",
4111 !m_pDoc->IsBlockEmpty(0, 0, 0, 100, 100));
4113 // TODO: Add more tests for normal data area calculation.
4115 m_pDoc->DeleteTab(0);
4118 void Test::testStreamValid()
4120 m_pDoc->InsertTab(0, OUString("Sheet1"));
4121 m_pDoc->InsertTab(1, OUString("Sheet2"));
4122 m_pDoc->InsertTab(2, OUString("Sheet3"));
4123 m_pDoc->InsertTab(3, OUString("Sheet4"));
4124 CPPUNIT_ASSERT_EQUAL_MESSAGE("We should have 4 sheet instances.", m_pDoc->GetTableCount(), static_cast<SCTAB>(4));
4126 OUString a1("A1");
4127 OUString a2("A2");
4128 OUString test;
4130 // Put values into Sheet1.
4131 m_pDoc->SetString(0, 0, 0, a1);
4132 m_pDoc->SetString(0, 1, 0, a2);
4133 m_pDoc->GetString(0, 0, 0, test);
4134 CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet1.A1", test.equals(a1));
4135 m_pDoc->GetString(0, 1, 0, test);
4136 CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet1.A2", test.equals(a2));
4138 // Put formulas into Sheet2 to Sheet4 to reference values from Sheet1.
4139 m_pDoc->SetString(0, 0, 1, OUString("=Sheet1.A1"));
4140 m_pDoc->SetString(0, 1, 1, OUString("=Sheet1.A2"));
4141 m_pDoc->SetString(0, 0, 2, OUString("=Sheet1.A1"));
4142 m_pDoc->SetString(0, 0, 3, OUString("=Sheet1.A2"));
4144 m_pDoc->GetString(0, 0, 1, test);
4145 CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet2.A1", test.equals(a1));
4146 m_pDoc->GetString(0, 1, 1, test);
4147 CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet2.A2", test.equals(a2));
4148 m_pDoc->GetString(0, 0, 2, test);
4149 CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet3.A1", test.equals(a1));
4150 m_pDoc->GetString(0, 0, 3, test);
4151 CPPUNIT_ASSERT_MESSAGE("Unexpected value in Sheet3.A1", test.equals(a2));
4153 // Set all sheet streams valid after all the initial cell values are in
4154 // place. In reality we need to have real XML streams stored in order to
4155 // claim they are valid, but we are just testing the flag values here.
4156 m_pDoc->SetStreamValid(0, true);
4157 m_pDoc->SetStreamValid(1, true);
4158 m_pDoc->SetStreamValid(2, true);
4159 m_pDoc->SetStreamValid(3, true);
4160 CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(0));
4161 CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(1));
4162 CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(2));
4163 CPPUNIT_ASSERT_MESSAGE("Stream is expected to be valid.", m_pDoc->IsStreamValid(3));
4165 // Now, insert a new row at row 2 position on Sheet1. This will move cell
4166 // A2 downward but cell A1 remains unmoved.
4167 m_pDoc->InsertRow(0, 0, MAXCOL, 0, 1, 2);
4168 m_pDoc->GetString(0, 0, 0, test);
4169 CPPUNIT_ASSERT_MESSAGE("Cell A1 should not have moved.", test.equals(a1));
4170 m_pDoc->GetString(0, 3, 0, test);
4171 CPPUNIT_ASSERT_MESSAGE("the old cell A2 should now be at A4.", test.equals(a2));
4172 const ScBaseCell* pCell = m_pDoc->GetCell(ScAddress(0, 1, 0));
4173 CPPUNIT_ASSERT_MESSAGE("Cell A2 should be empty.", pCell == NULL);
4174 pCell = m_pDoc->GetCell(ScAddress(0, 2, 0));
4175 CPPUNIT_ASSERT_MESSAGE("Cell A3 should be empty.", pCell == NULL);
4177 // After the move, Sheet1, Sheet2, and Sheet4 should have their stream
4178 // invalidated, whereas Sheet3's stream should still be valid.
4179 CPPUNIT_ASSERT_MESSAGE("Stream should have been invalidated.", !m_pDoc->IsStreamValid(0));
4180 CPPUNIT_ASSERT_MESSAGE("Stream should have been invalidated.", !m_pDoc->IsStreamValid(1));
4181 CPPUNIT_ASSERT_MESSAGE("Stream should have been invalidated.", !m_pDoc->IsStreamValid(3));
4182 CPPUNIT_ASSERT_MESSAGE("Stream should still be valid.", m_pDoc->IsStreamValid(2));
4184 m_pDoc->DeleteTab(3);
4185 m_pDoc->DeleteTab(2);
4186 m_pDoc->DeleteTab(1);
4187 m_pDoc->DeleteTab(0);
4190 void Test::testFunctionLists()
4192 const char* aDataBase[] = {
4193 "DAVERAGE",
4194 "DCOUNT",
4195 "DCOUNTA",
4196 "DGET",
4197 "DMAX",
4198 "DMIN",
4199 "DPRODUCT",
4200 "DSTDEV",
4201 "DSTDEVP",
4202 "DSUM",
4203 "DVAR",
4204 "DVARP",
4208 const char* aDateTime[] = {
4209 "DATE",
4210 "DATEDIF",
4211 "DATEVALUE",
4212 "DAY",
4213 "DAYS",
4214 "DAYS360",
4215 "EASTERSUNDAY",
4216 "HOUR",
4217 "MINUTE",
4218 "MONTH",
4219 "NOW",
4220 "SECOND",
4221 "TIME",
4222 "TIMEVALUE",
4223 "TODAY",
4224 "WEEKDAY",
4225 "WEEKNUM",
4226 "YEAR",
4230 const char* aFinancial[] = {
4231 "CUMIPMT",
4232 "CUMPRINC",
4233 "DB",
4234 "DDB",
4235 "DURATION",
4236 "EFFECTIVE",
4237 "FV",
4238 "IPMT",
4239 "IRR",
4240 "ISPMT",
4241 "MIRR",
4242 "NOMINAL",
4243 "NPER",
4244 "NPV",
4245 "PMT",
4246 "PPMT",
4247 "PV",
4248 "RATE",
4249 "RRI",
4250 "SLN",
4251 "SYD",
4252 "VDB",
4256 const char* aInformation[] = {
4257 "CELL",
4258 "CURRENT",
4259 "FORMULA",
4260 "INFO",
4261 "ISBLANK",
4262 "ISERR",
4263 "ISERROR",
4264 "ISFORMULA",
4265 "ISLOGICAL",
4266 "ISNA",
4267 "ISNONTEXT",
4268 "ISNUMBER",
4269 "ISREF",
4270 "ISTEXT",
4271 "N",
4272 "NA",
4273 "TYPE",
4277 const char* aLogical[] = {
4278 "AND",
4279 "FALSE",
4280 "IF",
4281 "IFERROR",
4282 "IFNA",
4283 "NOT",
4284 "OR",
4285 "TRUE",
4286 "XOR",
4290 const char* aMathematical[] = {
4291 "ABS",
4292 "ACOS",
4293 "ACOSH",
4294 "ACOT",
4295 "ACOTH",
4296 "ASIN",
4297 "ASINH",
4298 "ATAN",
4299 "ATAN2",
4300 "ATANH",
4301 "AVERAGEIF",
4302 "AVERAGEIFS",
4303 "BITAND",
4304 "BITLSHIFT",
4305 "BITOR",
4306 "BITRSHIFT",
4307 "BITXOR",
4308 "CEILING",
4309 "COMBIN",
4310 "COMBINA",
4311 "CONVERT",
4312 "COS",
4313 "COSH",
4314 "COT",
4315 "COTH",
4316 "COUNTBLANK",
4317 "COUNTIF",
4318 "COUNTIFS",
4319 "CSC",
4320 "CSCH",
4321 "DEGREES",
4322 "EUROCONVERT",
4323 "EVEN",
4324 "EXP",
4325 "FACT",
4326 "FLOOR",
4327 "GCD",
4328 "INT",
4329 "ISEVEN",
4330 "ISODD",
4331 "LCM",
4332 "LN",
4333 "LOG",
4334 "LOG10",
4335 "MOD",
4336 "ODD",
4337 "PI",
4338 "POWER",
4339 "PRODUCT",
4340 "RADIANS",
4341 "RAND",
4342 "ROUND",
4343 "ROUNDDOWN",
4344 "ROUNDUP",
4345 "SEC",
4346 "SECH",
4347 "SIGN",
4348 "SIN",
4349 "SINH",
4350 "SQRT",
4351 "SUBTOTAL",
4352 "SUM",
4353 "SUMIF",
4354 "SUMIFS",
4355 "SUMSQ",
4356 "TAN",
4357 "TANH",
4358 "TRUNC",
4362 const char* aArray[] = {
4363 "FREQUENCY",
4364 "GROWTH",
4365 "LINEST",
4366 "LOGEST",
4367 "MDETERM",
4368 "MINVERSE",
4369 "MMULT",
4370 "MUNIT",
4371 "SUMPRODUCT",
4372 "SUMX2MY2",
4373 "SUMX2PY2",
4374 "SUMXMY2",
4375 "TRANSPOSE",
4376 "TREND",
4380 const char* aStatistical[] = {
4381 "AVEDEV",
4382 "AVERAGE",
4383 "AVERAGEA",
4384 "B",
4385 "BETADIST",
4386 "BETAINV",
4387 "BINOMDIST",
4388 "CHIDIST",
4389 "CHIINV",
4390 "CHISQDIST",
4391 "CHISQINV",
4392 "CHITEST",
4393 "CONFIDENCE",
4394 "CORREL",
4395 "COUNT",
4396 "COUNTA",
4397 "COVAR",
4398 "CRITBINOM",
4399 "DEVSQ",
4400 "EXPONDIST",
4401 "FDIST",
4402 "FINV",
4403 "FISHER",
4404 "FISHERINV",
4405 "FORECAST",
4406 "FTEST",
4407 "GAMMA",
4408 "GAMMADIST",
4409 "GAMMAINV",
4410 "GAMMALN",
4411 "GAUSS",
4412 "GEOMEAN",
4413 "HARMEAN",
4414 "HYPGEOMDIST",
4415 "INTERCEPT",
4416 "KURT",
4417 "LARGE",
4418 "LOGINV",
4419 "LOGNORMDIST",
4420 "MAX",
4421 "MAXA",
4422 "MEDIAN",
4423 "MIN",
4424 "MINA",
4425 "MODE",
4426 "NEGBINOMDIST",
4427 "NORMDIST",
4428 "NORMINV",
4429 "NORMSDIST",
4430 "NORMSINV",
4431 "PEARSON",
4432 "PERCENTILE",
4433 "PERCENTRANK",
4434 "PERMUT",
4435 "PERMUTATIONA",
4436 "PHI",
4437 "POISSON",
4438 "PROB",
4439 "QUARTILE",
4440 "RANK",
4441 "RSQ",
4442 "SKEW",
4443 "SLOPE",
4444 "SMALL",
4445 "STANDARDIZE",
4446 "STDEV",
4447 "STDEVA",
4448 "STDEVP",
4449 "STDEVPA",
4450 "STEYX",
4451 "TDIST",
4452 "TINV",
4453 "TRIMMEAN",
4454 "TTEST",
4455 "VAR",
4456 "VARA",
4457 "VARP",
4458 "VARPA",
4459 "WEIBULL",
4460 "ZTEST",
4464 const char* aSpreadsheet[] = {
4465 "ADDRESS",
4466 "AREAS",
4467 "CHOOSE",
4468 "COLUMN",
4469 "COLUMNS",
4470 "DDE",
4471 "ERRORTYPE",
4472 "GETPIVOTDATA",
4473 "HLOOKUP",
4474 "HYPERLINK",
4475 "INDEX",
4476 "INDIRECT",
4477 "LOOKUP",
4478 "MATCH",
4479 "OFFSET",
4480 "ROW",
4481 "ROWS",
4482 "SHEET",
4483 "SHEETS",
4484 "STYLE",
4485 "VLOOKUP",
4489 const char* aText[] = {
4490 "ARABIC",
4491 "ASC",
4492 "BAHTTEXT",
4493 "BASE",
4494 "CHAR",
4495 "CLEAN",
4496 "CODE",
4497 "CONCATENATE",
4498 "DECIMAL",
4499 "DOLLAR",
4500 "EXACT",
4501 "FIND",
4502 "FIXED",
4503 "JIS",
4504 "LEFT",
4505 "LEN",
4506 "LOWER",
4507 "MID",
4508 "PROPER",
4509 "REPLACE",
4510 "REPT",
4511 "RIGHT",
4512 "ROMAN",
4513 "SEARCH",
4514 "SUBSTITUTE",
4515 "T",
4516 "TEXT",
4517 "TRIM",
4518 "UNICHAR",
4519 "UNICODE",
4520 "UPPER",
4521 "VALUE",
4525 struct {
4526 const char* Category; const char** Functions;
4527 } aTests[] = {
4528 { "Database", aDataBase },
4529 { "Date&Time", aDateTime },
4530 { "Financial", aFinancial },
4531 { "Information", aInformation },
4532 { "Logical", aLogical },
4533 { "Mathematical", aMathematical },
4534 { "Array", aArray },
4535 { "Statistical", aStatistical },
4536 { "Spreadsheet", aSpreadsheet },
4537 { "Text", aText },
4538 { "Add-in", 0 },
4539 { 0, 0 }
4542 ScFunctionMgr* pFuncMgr = ScGlobal::GetStarCalcFunctionMgr();
4543 sal_uInt32 n = pFuncMgr->getCount();
4544 for (sal_uInt32 i = 0; i < n; ++i)
4546 const formula::IFunctionCategory* pCat = pFuncMgr->getCategory(i);
4547 CPPUNIT_ASSERT_MESSAGE("Unexpected category name", pCat->getName().equalsAscii(aTests[i].Category));
4548 sal_uInt32 nFuncCount = pCat->getCount();
4549 for (sal_uInt32 j = 0; j < nFuncCount; ++j)
4551 const formula::IFunctionDescription* pFunc = pCat->getFunction(j);
4552 CPPUNIT_ASSERT_MESSAGE("Unexpected function name", pFunc->getFunctionName().equalsAscii(aTests[i].Functions[j]));
4557 void Test::testGraphicsInGroup()
4559 OUString aTabName("TestTab");
4560 m_pDoc->InsertTab(0, aTabName);
4561 CPPUNIT_ASSERT_MESSAGE("document should have one sheet to begin with.", m_pDoc->GetTableCount() == 1);
4562 SCROW nRow1, nRow2;
4563 bool bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4564 CPPUNIT_ASSERT_MESSAGE("new sheet should have all rows visible", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
4566 m_pDoc->InitDrawLayer();
4567 ScDrawLayer *pDrawLayer = m_pDoc->GetDrawLayer();
4568 CPPUNIT_ASSERT_MESSAGE("must have a draw layer", pDrawLayer != NULL);
4569 SdrPage* pPage = pDrawLayer->GetPage(0);
4570 CPPUNIT_ASSERT_MESSAGE("must have a draw page", pPage != NULL);
4573 //Add a square
4574 Rectangle aOrigRect(2,2,100,100);
4575 SdrRectObj *pObj = new SdrRectObj(aOrigRect);
4576 pPage->InsertObject(pObj);
4577 const Rectangle &rNewRect = pObj->GetLogicRect();
4578 CPPUNIT_ASSERT_MESSAGE("must have equal position and size", aOrigRect == rNewRect);
4580 ScDrawLayer::SetPageAnchored(*pObj);
4582 //Use a range of rows guaranteed to include all of the square
4583 m_pDoc->ShowRows(0, 100, 0, false);
4584 m_pDoc->SetDrawPageSize(0);
4585 CPPUNIT_ASSERT_MESSAGE("Should not change when page anchored", aOrigRect == rNewRect);
4586 m_pDoc->ShowRows(0, 100, 0, true);
4587 m_pDoc->SetDrawPageSize(0);
4588 CPPUNIT_ASSERT_MESSAGE("Should not change when page anchored", aOrigRect == rNewRect);
4590 ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4591 CPPUNIT_ASSERT_MESSAGE("That shouldn't change size or positioning", aOrigRect == rNewRect);
4593 m_pDoc->ShowRows(0, 100, 0, false);
4594 m_pDoc->SetDrawPageSize(0);
4595 CPPUNIT_ASSERT_MESSAGE("Left and Right should be unchanged",
4596 aOrigRect.Left() == rNewRect.Left() && aOrigRect.Right() == rNewRect.Right());
4597 CPPUNIT_ASSERT_MESSAGE("Height should be minimum allowed height",
4598 (rNewRect.Bottom() - rNewRect.Top()) <= 1);
4599 m_pDoc->ShowRows(0, 100, 0, true);
4600 m_pDoc->SetDrawPageSize(0);
4601 CPPUNIT_ASSERT_MESSAGE("Should not change when page anchored", aOrigRect == rNewRect);
4605 // Add a circle.
4606 Rectangle aOrigRect = Rectangle(10,10,210,210); // 200 x 200
4607 SdrCircObj* pObj = new SdrCircObj(OBJ_CIRC, aOrigRect);
4608 pPage->InsertObject(pObj);
4609 const Rectangle& rNewRect = pObj->GetLogicRect();
4610 CPPUNIT_ASSERT_MESSAGE("Position and size of the circle shouldn't change when inserted into the page.",
4611 aOrigRect == rNewRect);
4613 ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4614 CPPUNIT_ASSERT_MESSAGE("Size changed when cell anchored. Not good.",
4615 aOrigRect == rNewRect);
4617 // Insert 2 rows at the top. This should push the circle object down.
4618 m_pDoc->InsertRow(0, 0, MAXCOL, 0, 0, 2);
4619 m_pDoc->SetDrawPageSize(0);
4621 // Make sure the size of the circle is still identical.
4622 CPPUNIT_ASSERT_MESSAGE("Size of the circle has changed, but shouldn't!",
4623 aOrigRect.GetSize() == rNewRect.GetSize());
4625 // Delete 2 rows at the top. This should bring the circle object to its original position.
4626 m_pDoc->DeleteRow(0, 0, MAXCOL, 0, 0, 2);
4627 m_pDoc->SetDrawPageSize(0);
4628 CPPUNIT_ASSERT_MESSAGE("Failed to move back to its original position.", aOrigRect == rNewRect);
4632 // Add a line.
4633 basegfx::B2DPolygon aTempPoly;
4634 Point aStartPos(10,300), aEndPos(110,200); // bottom-left to top-right.
4635 Rectangle aOrigRect(10,200,110,300); // 100 x 100
4636 aTempPoly.append(basegfx::B2DPoint(aStartPos.X(), aStartPos.Y()));
4637 aTempPoly.append(basegfx::B2DPoint(aEndPos.X(), aEndPos.Y()));
4638 SdrPathObj* pObj = new SdrPathObj(OBJ_LINE, basegfx::B2DPolyPolygon(aTempPoly));
4639 pObj->NbcSetLogicRect(aOrigRect);
4640 pPage->InsertObject(pObj);
4641 const Rectangle& rNewRect = pObj->GetLogicRect();
4642 CPPUNIT_ASSERT_MESSAGE("Size differ.", aOrigRect == rNewRect);
4644 ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4645 CPPUNIT_ASSERT_MESSAGE("Size changed when cell-anchored. Not good.",
4646 aOrigRect == rNewRect);
4648 // Insert 2 rows at the top and delete them immediately.
4649 m_pDoc->InsertRow(0, 0, MAXCOL, 0, 0, 2);
4650 m_pDoc->DeleteRow(0, 0, MAXCOL, 0, 0, 2);
4651 m_pDoc->SetDrawPageSize(0);
4652 CPPUNIT_ASSERT_MESSAGE("Size of a line object changed after row insertion and removal.",
4653 aOrigRect == rNewRect);
4655 sal_Int32 n = pObj->GetPointCount();
4656 CPPUNIT_ASSERT_MESSAGE("There should be exactly 2 points in a line object.", n == 2);
4657 CPPUNIT_ASSERT_MESSAGE("Line shape has changed.",
4658 aStartPos == pObj->GetPoint(0) && aEndPos == pObj->GetPoint(1));
4661 m_pDoc->DeleteTab(0);
4664 void Test::testGraphicsOnSheetMove()
4666 m_pDoc->InsertTab(0, rtl::OUString("Tab1"));
4667 m_pDoc->InsertTab(1, rtl::OUString("Tab2"));
4668 CPPUNIT_ASSERT_MESSAGE("There should be only 2 sheets to begin with", m_pDoc->GetTableCount() == 2);
4670 m_pDoc->InitDrawLayer();
4671 ScDrawLayer* pDrawLayer = m_pDoc->GetDrawLayer();
4672 CPPUNIT_ASSERT_MESSAGE("No drawing layer.", pDrawLayer);
4673 SdrPage* pPage = pDrawLayer->GetPage(0);
4674 CPPUNIT_ASSERT_MESSAGE("No page instance for the 1st sheet.", pPage);
4676 // Insert an object.
4677 Rectangle aObjRect(2,2,100,100);
4678 SdrObject* pObj = new SdrRectObj(aObjRect);
4679 pPage->InsertObject(pObj);
4680 ScDrawLayer::SetCellAnchoredFromPosition(*pObj, *m_pDoc, 0);
4682 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be one object on the 1st sheet.", pPage->GetObjCount(), static_cast<sal_uIntPtr>(1));
4684 const ScDrawObjData* pData = ScDrawLayer::GetObjData(pObj);
4685 CPPUNIT_ASSERT_MESSAGE("Object meta-data doesn't exist.", pData);
4686 CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 0 && pData->maEnd.Tab() == 0);
4688 pPage = pDrawLayer->GetPage(1);
4689 CPPUNIT_ASSERT_MESSAGE("No page instance for the 2nd sheet.", pPage);
4690 CPPUNIT_ASSERT_EQUAL_MESSAGE("2nd sheet shouldn't have any object.", pPage->GetObjCount(), static_cast<sal_uIntPtr>(0));
4692 // Insert a new sheet at left-end, and make sure the object has moved to
4693 // the 2nd page.
4694 m_pDoc->InsertTab(0, rtl::OUString("NewTab"));
4695 CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be 3 sheets.", m_pDoc->GetTableCount(), static_cast<SCTAB>(3));
4696 pPage = pDrawLayer->GetPage(0);
4697 CPPUNIT_ASSERT_MESSAGE("1st sheet should have no object.", pPage && pPage->GetObjCount() == 0);
4698 pPage = pDrawLayer->GetPage(1);
4699 CPPUNIT_ASSERT_MESSAGE("2nd sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4700 pPage = pDrawLayer->GetPage(2);
4701 CPPUNIT_ASSERT_MESSAGE("3rd sheet should have no object.", pPage && pPage->GetObjCount() == 0);
4703 CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 1 && pData->maEnd.Tab() == 1);
4705 // Now, delete the sheet that just got inserted. The object should be back
4706 // on the 1st sheet.
4707 m_pDoc->DeleteTab(0);
4708 pPage = pDrawLayer->GetPage(0);
4709 CPPUNIT_ASSERT_MESSAGE("1st sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4710 CPPUNIT_ASSERT_MESSAGE("Size and position of the object shouldn't change.",
4711 pObj->GetLogicRect() == aObjRect);
4713 CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 0 && pData->maEnd.Tab() == 0);
4715 // Move the 1st sheet to the last position.
4716 m_pDoc->MoveTab(0, 1);
4717 pPage = pDrawLayer->GetPage(0);
4718 CPPUNIT_ASSERT_MESSAGE("1st sheet should have no object.", pPage && pPage->GetObjCount() == 0);
4719 pPage = pDrawLayer->GetPage(1);
4720 CPPUNIT_ASSERT_MESSAGE("2nd sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4721 CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 1 && pData->maEnd.Tab() == 1);
4723 // Copy the 2nd sheet, which has one drawing object to the last position.
4724 m_pDoc->CopyTab(1, 2);
4725 pPage = pDrawLayer->GetPage(2);
4726 CPPUNIT_ASSERT_MESSAGE("Copied sheet should have one object.", pPage && pPage->GetObjCount() == 1);
4727 pObj = pPage->GetObj(0);
4728 CPPUNIT_ASSERT_MESSAGE("Failed to get drawing object.", pObj);
4729 pData = ScDrawLayer::GetObjData(pObj);
4730 CPPUNIT_ASSERT_MESSAGE("Failed to get drawing object meta-data.", pData);
4731 CPPUNIT_ASSERT_MESSAGE("Wrong sheet ID in cell anchor data!", pData->maStart.Tab() == 2 && pData->maEnd.Tab() == 2);
4733 m_pDoc->DeleteTab(2);
4734 m_pDoc->DeleteTab(1);
4735 m_pDoc->DeleteTab(0);
4738 void Test::testPostIts()
4740 rtl::OUString aHello("Hello world");
4741 rtl::OUString aJimBob("Jim Bob");
4742 rtl::OUString aTabName("PostIts");
4743 rtl::OUString aTabName2("Table2");
4744 m_pDoc->InsertTab(0, aTabName);
4746 ScAddress rAddr(2, 2, 0); // cell C3
4747 ScPostIt *pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
4748 pNote->SetText(rAddr, aHello);
4749 pNote->SetAuthor(aJimBob);
4751 ScPostIt *pGetNote = m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr);
4752 CPPUNIT_ASSERT_MESSAGE("note should be itself", pGetNote == pNote );
4754 // Insert one row at row 1.
4755 bool bInsertRow = m_pDoc->InsertRow(0, 0, MAXCOL, 0, 1, 1);
4756 CPPUNIT_ASSERT_MESSAGE("failed to insert row", bInsertRow );
4758 CPPUNIT_ASSERT_MESSAGE("note hasn't moved", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == NULL);
4759 rAddr.IncRow(); // cell C4
4760 CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4762 // Insert column at column A.
4763 bool bInsertCol = m_pDoc->InsertCol(0, 0, MAXROW, 0, 1, 1);
4764 CPPUNIT_ASSERT_MESSAGE("failed to insert column", bInsertCol );
4766 CPPUNIT_ASSERT_MESSAGE("note hasn't moved", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == NULL);
4767 rAddr.IncCol(); // cell D4
4768 CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4770 // Insert a new sheet to shift the current sheet to the right.
4771 m_pDoc->InsertTab(0, aTabName2);
4772 CPPUNIT_ASSERT_MESSAGE("note hasn't moved", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == NULL);
4773 rAddr.IncTab(); // Move to the next sheet.
4774 CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4776 m_pDoc->DeleteTab(0);
4777 rAddr.IncTab(-1);
4778 CPPUNIT_ASSERT_MESSAGE("note not there", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4780 // Insert cell at C4. This should NOT shift the note position.
4781 bInsertRow = m_pDoc->InsertRow(2, 0, 2, 0, 3, 1);
4782 CPPUNIT_ASSERT_MESSAGE("Failed to insert cell at C4.", bInsertRow);
4783 CPPUNIT_ASSERT_MESSAGE("Note shouldn't have moved but it has.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4785 // Delete cell at C4. Again, this should NOT shift the note position.
4786 m_pDoc->DeleteRow(2, 0, 2, 0, 3, 1);
4787 CPPUNIT_ASSERT_MESSAGE("Note shouldn't have moved but it has.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4789 // Now, with the note at D4, delete cell D3. This should shift the note one cell up.
4790 m_pDoc->DeleteRow(3, 0, 3, 0, 2, 1);
4791 rAddr.IncRow(-1); // cell D3
4792 CPPUNIT_ASSERT_MESSAGE("Note at D4 should have shifted up to D3.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4794 // Delete column C. This should shift the note one cell left.
4795 m_pDoc->DeleteCol(0, 0, MAXROW, 0, 2, 1);
4796 rAddr.IncCol(-1); // cell C3
4797 CPPUNIT_ASSERT_MESSAGE("Note at D3 should have shifted left to C3.", m_pDoc->GetNotes(rAddr.Tab())->findByAddress(rAddr) == pNote);
4799 m_pDoc->DeleteTab(0);
4802 void Test::testToggleRefFlag()
4804 // In this test, there is no need to insert formula string into a cell in
4805 // the document, as ScRefFinder does not depend on the content of the
4806 // document except for the sheet names.
4808 OUString aTabName("Test");
4809 m_pDoc->InsertTab(0, aTabName);
4812 // Calc A1: basic 2D reference
4814 OUString aFormula("=B100");
4815 ScAddress aPos(1, 5, 0);
4816 ScRefFinder aFinder(aFormula, aPos, m_pDoc, formula::FormulaGrammar::CONV_OOO);
4818 // Original
4819 CPPUNIT_ASSERT_MESSAGE("Does not equal the original text.", aFormula.equals(aFinder.GetText()));
4821 // column relative / row relative -> column absolute / row absolute
4822 aFinder.ToggleRel(0, aFormula.getLength());
4823 aFormula = aFinder.GetText();
4824 CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=$B$100" );
4826 // column absolute / row absolute -> column relative / row absolute
4827 aFinder.ToggleRel(0, aFormula.getLength());
4828 aFormula = aFinder.GetText();
4829 CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=B$100" );
4831 // column relative / row absolute -> column absolute / row relative
4832 aFinder.ToggleRel(0, aFormula.getLength());
4833 aFormula = aFinder.GetText();
4834 CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=$B100" );
4836 // column absolute / row relative -> column relative / row relative
4837 aFinder.ToggleRel(0, aFormula.getLength());
4838 aFormula = aFinder.GetText();
4839 CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=B100" );
4843 // Excel R1C1: basic 2D reference
4845 OUString aFormula("=R2C1");
4846 ScAddress aPos(3, 5, 0);
4847 ScRefFinder aFinder(aFormula, aPos, m_pDoc, formula::FormulaGrammar::CONV_XL_R1C1);
4849 // Original
4850 CPPUNIT_ASSERT_MESSAGE("Does not equal the original text.", aFormula.equals(aFinder.GetText()));
4852 // column absolute / row absolute -> column relative / row absolute
4853 aFinder.ToggleRel(0, aFormula.getLength());
4854 aFormula = aFinder.GetText();
4855 CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R2C[-3]" );
4857 // column relative / row absolute - > column absolute / row relative
4858 aFinder.ToggleRel(0, aFormula.getLength());
4859 aFormula = aFinder.GetText();
4860 CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R[-4]C1" );
4862 // column absolute / row relative -> column relative / row relative
4863 aFinder.ToggleRel(0, aFormula.getLength());
4864 aFormula = aFinder.GetText();
4865 CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R[-4]C[-3]" );
4867 // column relative / row relative -> column absolute / row absolute
4868 aFinder.ToggleRel(0, aFormula.getLength());
4869 aFormula = aFinder.GetText();
4870 CPPUNIT_ASSERT_MESSAGE( "Wrong conversion.", aFormula == "=R2C1" );
4873 // TODO: Add more test cases esp. for 3D references, Excel A1 syntax, and
4874 // partial selection within formula string.
4876 m_pDoc->DeleteTab(0);
4879 void Test::testAutofilter()
4881 OUString aTabName("Test");
4882 OUString aDBName("NONAME");
4884 m_pDoc->InsertTab( 0, aTabName );
4886 // cell contents (0 = empty cell)
4887 const char* aData[][3] = {
4888 { "C1", "C2", "C3" },
4889 { "0", "1", "A" },
4890 { "1", "2", 0 },
4891 { "1", "2", "B" },
4892 { "0", "2", "B" }
4895 SCCOL nCols = SAL_N_ELEMENTS(aData[0]);
4896 SCROW nRows = SAL_N_ELEMENTS(aData);
4898 // Populate cells.
4899 for (SCROW i = 0; i < nRows; ++i)
4900 for (SCCOL j = 0; j < nCols; ++j)
4901 if (aData[i][j])
4902 m_pDoc->SetString(j, i, 0, rtl::OUString::createFromAscii(aData[i][j]));
4904 ScDBData* pDBData = new ScDBData(aDBName, 0, 0, 0, nCols-1, nRows-1);
4905 m_pDoc->SetAnonymousDBData(0,pDBData);
4907 pDBData->SetAutoFilter(true);
4908 ScRange aRange;
4909 pDBData->GetArea(aRange);
4910 m_pDoc->ApplyFlagsTab( aRange.aStart.Col(), aRange.aStart.Row(),
4911 aRange.aEnd.Col(), aRange.aStart.Row(),
4912 aRange.aStart.Tab(), SC_MF_AUTO);
4914 //create the query param
4915 ScQueryParam aParam;
4916 pDBData->GetQueryParam(aParam);
4917 ScQueryEntry& rEntry = aParam.GetEntry(0);
4918 rEntry.bDoQuery = true;
4919 rEntry.nField = 0;
4920 rEntry.eOp = SC_EQUAL;
4921 rEntry.GetQueryItem().mfVal = 0;
4922 // add queryParam to database range.
4923 pDBData->SetQueryParam(aParam);
4925 // perform the query.
4926 m_pDoc->Query(0, aParam, true);
4928 //control output
4929 SCROW nRow1, nRow2;
4930 bool bHidden = m_pDoc->RowHidden(2, 0, &nRow1, &nRow2);
4931 CPPUNIT_ASSERT_MESSAGE("rows 2 & 3 should be hidden", bHidden && nRow1 == 2 && nRow2 == 3);
4933 // Remove filtering.
4934 rEntry.Clear();
4935 m_pDoc->Query(0, aParam, true);
4936 bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4937 CPPUNIT_ASSERT_MESSAGE("All rows should be shown.", !bHidden && nRow1 == 0 && nRow2 == MAXROW);
4939 // Filter for non-empty cells by column C.
4940 rEntry.bDoQuery = true;
4941 rEntry.nField = 2;
4942 rEntry.SetQueryByNonEmpty();
4943 m_pDoc->Query(0, aParam, true);
4945 // only row 3 should be hidden. The rest should be visible.
4946 bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4947 CPPUNIT_ASSERT_MESSAGE("rows 1 & 2 should be visible.", !bHidden && nRow1 == 0 && nRow2 == 1);
4948 bHidden = m_pDoc->RowHidden(2, 0, &nRow1, &nRow2);
4949 CPPUNIT_ASSERT_MESSAGE("row 3 should be hidden.", bHidden && nRow1 == 2 && nRow2 == 2);
4950 bHidden = m_pDoc->RowHidden(3, 0, &nRow1, &nRow2);
4951 CPPUNIT_ASSERT_MESSAGE("row 4 and down should be visible.", !bHidden && nRow1 == 3 && nRow2 == MAXROW);
4953 // Now, filter for empty cells by column C.
4954 rEntry.SetQueryByEmpty();
4955 m_pDoc->Query(0, aParam, true);
4957 // Now, only row 1 and 3, and 6 and down should be visible.
4958 bHidden = m_pDoc->RowHidden(0, 0, &nRow1, &nRow2);
4959 CPPUNIT_ASSERT_MESSAGE("row 1 should be visible.", !bHidden && nRow1 == 0 && nRow2 == 0);
4960 bHidden = m_pDoc->RowHidden(1, 0, &nRow1, &nRow2);
4961 CPPUNIT_ASSERT_MESSAGE("row 2 should be hidden.", bHidden && nRow1 == 1 && nRow2 == 1);
4962 bHidden = m_pDoc->RowHidden(2, 0, &nRow1, &nRow2);
4963 CPPUNIT_ASSERT_MESSAGE("row 3 should be visible.", !bHidden && nRow1 == 2 && nRow2 == 2);
4964 bHidden = m_pDoc->RowHidden(3, 0, &nRow1, &nRow2);
4965 CPPUNIT_ASSERT_MESSAGE("rows 4 & 5 should be hidden.", bHidden && nRow1 == 3 && nRow2 == 4);
4966 bHidden = m_pDoc->RowHidden(5, 0, &nRow1, &nRow2);
4967 CPPUNIT_ASSERT_MESSAGE("rows 6 and down should be all visible.", !bHidden && nRow1 == 5 && nRow2 == MAXROW);
4969 m_pDoc->DeleteTab(0);
4972 void Test::testCopyPaste()
4974 m_pDoc->InsertTab(0, OUString("Sheet1"));
4975 m_pDoc->InsertTab(1, OUString("Sheet2"));
4976 //test copy&paste + ScUndoPaste
4977 //copy local and global range names in formulas
4978 //string cells and value cells
4979 m_pDoc->SetValue(0, 0, 0, 1);
4980 m_pDoc->SetValue(3, 0, 0, 0);
4981 m_pDoc->SetValue(3, 1, 0, 1);
4982 m_pDoc->SetValue(3, 2, 0, 2);
4983 m_pDoc->SetValue(3, 3, 0, 3);
4984 m_pDoc->SetString(2, 0, 0, OUString("test"));
4985 ScAddress aAdr (0, 0, 0);
4987 //create some range names, local and global
4988 ScRangeData* pLocal1 = new ScRangeData(m_pDoc, rtl::OUString("local1"), aAdr);
4989 ScRangeData* pLocal2 = new ScRangeData(m_pDoc, OUString("local2"), aAdr);
4990 ScRangeData* pGlobal = new ScRangeData(m_pDoc, OUString("global"), aAdr);
4991 ScRangeName* pGlobalRangeName = new ScRangeName();
4992 pGlobalRangeName->insert(pGlobal);
4993 ScRangeName* pLocalRangeName1 = new ScRangeName();
4994 pLocalRangeName1->insert(pLocal1);
4995 pLocalRangeName1->insert(pLocal2);
4996 m_pDoc->SetRangeName(pGlobalRangeName);
4997 m_pDoc->SetRangeName(0, pLocalRangeName1);
4999 //add formula
5000 rtl::OUString aFormulaString("=local1+global+SUM($C$1:$D$4)");
5001 m_pDoc->SetString(1, 0, 0, aFormulaString);
5003 double aValue = 0;
5004 m_pDoc->GetValue(1, 0, 0, aValue);
5005 std::cout << "Value: " << aValue << std::endl;
5006 ASSERT_DOUBLES_EQUAL_MESSAGE("formula should return 8", aValue, 8);
5008 //copy Sheet1.A1:C1 to Sheet2.A2:C2
5009 ScRange aRange(0,0,0,2,0,0);
5010 ScClipParam aClipParam(aRange, false);
5011 ScMarkData aMark;
5012 aMark.SetMarkArea(aRange);
5013 ScDocument* pClipDoc = new ScDocument(SCDOCMODE_CLIP);
5014 m_pDoc->CopyToClip(aClipParam, pClipDoc, &aMark);
5016 sal_uInt16 nFlags = IDF_ALL;
5017 aRange = ScRange(0,1,1,2,1,1);//target: Sheet2.A2:C2
5018 ScDocument* pUndoDoc = new ScDocument(SCDOCMODE_UNDO);
5019 pUndoDoc->InitUndo(m_pDoc, 1, 1, true, true);
5020 ScMarkData aMarkData2;
5021 aMarkData2.SetMarkArea(aRange);
5022 ScRefUndoData* pRefUndoData= new ScRefUndoData(m_pDoc);
5023 SfxUndoAction* pUndo = new ScUndoPaste(
5024 &m_xDocShRef, ScRange(0, 1, 1, 2, 1, 1), aMarkData2, pUndoDoc, NULL, IDF_ALL, pRefUndoData, false);
5025 m_pDoc->CopyFromClip(aRange, aMarkData2, nFlags, NULL, pClipDoc);
5027 //check values after copying
5028 rtl::OUString aString;
5029 m_pDoc->GetValue(1,1,1, aValue);
5030 m_pDoc->GetFormula(1,1,1, aString);
5031 ASSERT_DOUBLES_EQUAL_MESSAGE("copied formula should return 2", aValue, 2);
5032 CPPUNIT_ASSERT_MESSAGE("formula string was not copied correctly", aString == aFormulaString);
5033 m_pDoc->GetValue(0,1,1, aValue);
5034 CPPUNIT_ASSERT_MESSAGE("copied value should be 1", aValue == 1);
5036 //chack local range name after copying
5037 pLocal1 = m_pDoc->GetRangeName(1)->findByUpperName(OUString("LOCAL1"));
5038 CPPUNIT_ASSERT_MESSAGE("local range name 1 should be copied", pLocal1);
5039 ScRange aRangeLocal1;
5040 pLocal1->IsValidReference(aRangeLocal1);
5041 CPPUNIT_ASSERT_MESSAGE("local range 1 should still point to Sheet1.A1",aRangeLocal1 == ScRange(0,0,0,0,0,0));
5042 pLocal2 = m_pDoc->GetRangeName(1)->findByUpperName(OUString("LOCAL2"));
5043 CPPUNIT_ASSERT_MESSAGE("local2 should not be copied", pLocal2 == NULL);
5046 //check undo and redo
5047 pUndo->Undo();
5048 m_pDoc->GetValue(1,1,1, aValue);
5049 ASSERT_DOUBLES_EQUAL_MESSAGE("after undo formula should return nothing", aValue, 0);
5050 m_pDoc->GetString(2,1,1, aString);
5051 CPPUNIT_ASSERT_MESSAGE("after undo string should be removed", aString.equalsAsciiL(RTL_CONSTASCII_STRINGPARAM("")));
5053 pUndo->Redo();
5054 m_pDoc->GetValue(1,1,1, aValue);
5055 ASSERT_DOUBLES_EQUAL_MESSAGE("formula should return 2 after redo", aValue, 2);
5056 m_pDoc->GetString(2,1,1, aString);
5057 CPPUNIT_ASSERT_MESSAGE("Cell Sheet2.C2 should contain: test", aString.equalsAsciiL(RTL_CONSTASCII_STRINGPARAM("test")));
5058 m_pDoc->GetFormula(1,1,1, aString);
5059 CPPUNIT_ASSERT_MESSAGE("Formula should be correct again", aString == aFormulaString);
5061 //clear all variables
5062 delete pClipDoc;
5063 delete pUndoDoc;
5064 m_pDoc->DeleteTab(1);
5065 m_pDoc->DeleteTab(0);
5068 void Test::testMergedCells()
5070 //test merge and unmerge
5071 //TODO: an undo/redo test for this would be a good idea
5072 m_pDoc->InsertTab(0, rtl::OUString("Sheet1"));
5073 m_pDoc->DoMerge(0, 1, 1, 3, 3, false);
5074 SCCOL nEndCol = 1;
5075 SCROW nEndRow = 1;
5076 m_pDoc->ExtendMerge( 1, 1, nEndCol, nEndRow, 0, false);
5077 CPPUNIT_ASSERT_MESSAGE("did not merge cells", nEndCol == 3 && nEndRow == 3);
5078 ScRange aRange(0,2,0,MAXCOL,2,0);
5079 ScMarkData aMark;
5080 aMark.SetMarkArea(aRange);
5081 m_pDoc->SetInTest();
5082 m_xDocShRef->GetDocFunc().InsertCells(aRange, &aMark, INS_INSROWS, true, true);
5083 m_pDoc->ExtendMerge(1, 1, nEndCol, nEndRow, 0, false);
5084 cout << nEndRow << nEndCol;
5085 CPPUNIT_ASSERT_MESSAGE("did not increase merge area", nEndCol == 3 && nEndRow == 4);
5086 m_pDoc->DeleteTab(0);
5090 void Test::testRenameTable()
5092 //test set rename table
5093 //TODO: set name1 and name2 and do an undo to check if name 1 is set now
5094 //TODO: also check if new name for table is same as another table
5096 m_pDoc->InsertTab(0, "Sheet1");
5097 m_pDoc->InsertTab(1, "Sheet2");
5099 //test case 1 , rename table2 to sheet 1, it should return error
5100 rtl::OUString nameToSet = "Sheet1";
5101 ScDocFunc& rDocFunc = m_xDocShRef->GetDocFunc();
5102 CPPUNIT_ASSERT_MESSAGE("name same as another table is being set", !rDocFunc.RenameTable(1,nameToSet,false,true) );
5104 //test case 2 , simple rename to check name
5105 nameToSet = "test1";
5106 m_xDocShRef->GetDocFunc().RenameTable(0,nameToSet,false,true);
5107 rtl::OUString nameJustSet;
5108 m_pDoc->GetName(0,nameJustSet);
5109 CPPUNIT_ASSERT_MESSAGE("table not renamed", nameToSet != nameJustSet);
5111 //test case 3 , rename again
5112 rtl::OUString anOldName;
5113 m_pDoc->GetName(0,anOldName);
5115 nameToSet = "test2";
5116 rDocFunc.RenameTable(0,nameToSet,false,true);
5117 m_pDoc->GetName(0,nameJustSet);
5118 CPPUNIT_ASSERT_MESSAGE("table not renamed", nameToSet != nameJustSet);
5120 //test case 4 , check if undo works
5121 SfxUndoAction* pUndo = new ScUndoRenameTab(m_xDocShRef,0,anOldName,nameToSet);
5122 pUndo->Undo();
5123 m_pDoc->GetName(0,nameJustSet);
5124 CPPUNIT_ASSERT_MESSAGE("the correct name is not set after undo", nameJustSet == anOldName);
5126 pUndo->Redo();
5127 m_pDoc->GetName(0,nameJustSet);
5128 CPPUNIT_ASSERT_MESSAGE("the correct color is not set after redo", nameJustSet == nameToSet);
5130 m_pDoc->DeleteTab(0);
5131 m_pDoc->DeleteTab(1);
5136 void Test::testSetBackgroundColor()
5138 //test set background color
5139 //TODO: set color1 and set color2 and do an undo to check if color1 is set now.
5141 m_pDoc->InsertTab(0, rtl::OUString("Sheet1"));
5142 Color aColor;
5144 //test yellow
5145 aColor=Color(COL_YELLOW);
5146 m_xDocShRef->GetDocFunc().SetTabBgColor(0,aColor,false, true);
5147 CPPUNIT_ASSERT_MESSAGE("the correct color is not set", m_pDoc->GetTabBgColor(0)!= aColor);
5150 Color aOldTabBgColor=m_pDoc->GetTabBgColor(0);
5151 aColor.SetColor(COL_BLUE);//set BLUE
5152 m_xDocShRef->GetDocFunc().SetTabBgColor(0,aColor,false, true);
5153 CPPUNIT_ASSERT_MESSAGE("the correct color is not set the second time", m_pDoc->GetTabBgColor(0)!= aColor);
5155 //now check for undo
5156 SfxUndoAction* pUndo = new ScUndoTabColor(m_xDocShRef,0, aOldTabBgColor, aColor);
5157 pUndo->Undo();
5158 CPPUNIT_ASSERT_MESSAGE("the correct color is not set after undo", m_pDoc->GetTabBgColor(0)== aOldTabBgColor);
5159 pUndo->Redo();
5160 CPPUNIT_ASSERT_MESSAGE("the correct color is not set after undo", m_pDoc->GetTabBgColor(0)== aColor);
5161 m_pDoc->DeleteTab(0);
5166 void Test::testUpdateReference()
5168 //test that formulas are correctly updated during sheet delete
5169 //TODO: add tests for relative references, updating of named ranges, ...
5170 rtl::OUString aSheet1("Sheet1");
5171 rtl::OUString aSheet2("Sheet2");
5172 rtl::OUString aSheet3("Sheet3");
5173 rtl::OUString aSheet4("Sheet4");
5174 m_pDoc->InsertTab(0, aSheet1);
5175 m_pDoc->InsertTab(1, aSheet2);
5176 m_pDoc->InsertTab(2, aSheet3);
5177 m_pDoc->InsertTab(3, aSheet4);
5179 m_pDoc->SetValue(0,0,2, 1);
5180 m_pDoc->SetValue(1,0,2, 2);
5181 m_pDoc->SetValue(1,1,3, 4);
5182 m_pDoc->SetString(2,0,2, rtl::OUString("=A1+B1"));
5183 m_pDoc->SetString(2,1,2, rtl::OUString("=Sheet4.B2+A1"));
5185 double aValue;
5186 m_pDoc->GetValue(2,0,2, aValue);
5187 ASSERT_DOUBLES_EQUAL_MESSAGE("formula does not return correct result", aValue, 3);
5188 m_pDoc->GetValue(2,1,2, aValue);
5189 ASSERT_DOUBLES_EQUAL_MESSAGE("formula does not return correct result", aValue, 5);
5191 //test deleting both sheets: one is not directly before the sheet, the other one is
5192 m_pDoc->DeleteTab(0);
5193 m_pDoc->GetValue(2,0,1, aValue);
5194 ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting first sheet formula does not return correct result", aValue, 3);
5195 m_pDoc->GetValue(2,1,1, aValue);
5196 ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting first sheet formula does not return correct result", aValue, 5);
5198 m_pDoc->DeleteTab(0);
5199 m_pDoc->GetValue(2,0,0, aValue);
5200 ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting second sheet formula does not return correct result", aValue, 3);
5201 m_pDoc->GetValue(2,1,0, aValue);
5202 ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting second sheet formula does not return correct result", aValue, 5);
5204 //test adding two sheets
5205 m_pDoc->InsertTab(0, aSheet2);
5206 m_pDoc->GetValue(2,0,1, aValue);
5207 ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting first sheet formula does not return correct result", aValue, 3);
5208 m_pDoc->GetValue(2,1,1, aValue);
5209 ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting first sheet formula does not return correct result", aValue, 5);
5211 m_pDoc->InsertTab(0, aSheet1);
5212 m_pDoc->GetValue(2,0,2, aValue);
5213 ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting second sheet formula does not return correct result", aValue, 3);
5214 m_pDoc->GetValue(2,1,2, aValue);
5215 ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting second sheet formula does not return correct result", aValue, 5);
5217 //test new DeleteTabs/InsertTabs methods
5218 m_pDoc->DeleteTabs(0, 2);
5219 m_pDoc->GetValue(2, 0, 0, aValue);
5220 ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting sheets formula does not return correct result", aValue, 3);
5221 m_pDoc->GetValue(2, 1, 0, aValue);
5222 ASSERT_DOUBLES_EQUAL_MESSAGE("after deleting sheets formula does not return correct result", aValue, 5);
5224 std::vector<rtl::OUString> aSheets;
5225 aSheets.push_back(aSheet1);
5226 aSheets.push_back(aSheet2);
5227 m_pDoc->InsertTabs(0, aSheets, false, true);
5228 m_pDoc->GetValue(2, 0, 2, aValue);
5229 rtl::OUString aFormula;
5230 m_pDoc->GetFormula(2,0,2, aFormula);
5231 std::cout << "formel: " << rtl::OUStringToOString(aFormula, RTL_TEXTENCODING_UTF8).getStr() << std::endl;
5232 std::cout << std::endl << aValue << std::endl;
5233 ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting sheets formula does not return correct result", aValue, 3);
5234 m_pDoc->GetValue(2, 1, 2, aValue);
5235 ASSERT_DOUBLES_EQUAL_MESSAGE("after inserting sheets formula does not return correct result", aValue, 5);
5237 m_pDoc->DeleteTab(3);
5238 m_pDoc->DeleteTab(2);
5239 m_pDoc->DeleteTab(1);
5240 m_pDoc->DeleteTab(0);
5243 namespace {
5245 bool hasRange(const std::vector<ScTokenRef>& rRefTokens, const ScRange& rRange)
5247 std::vector<ScTokenRef>::const_iterator it = rRefTokens.begin(), itEnd = rRefTokens.end();
5248 for (; it != itEnd; ++it)
5250 const ScTokenRef& p = *it;
5251 if (!ScRefTokenHelper::isRef(p) || ScRefTokenHelper::isExternalRef(p))
5252 continue;
5254 switch (p->GetType())
5256 case formula::svSingleRef:
5258 ScSingleRefData aData = p->GetSingleRef();
5259 if (rRange.aStart != rRange.aEnd)
5260 break;
5262 ScAddress aThis(aData.nCol, aData.nRow, aData.nTab);
5263 if (aThis == rRange.aStart)
5264 return true;
5266 break;
5267 case formula::svDoubleRef:
5269 ScComplexRefData aData = p->GetDoubleRef();
5270 ScRange aThis(aData.Ref1.nCol, aData.Ref1.nRow, aData.Ref1.nTab, aData.Ref2.nCol, aData.Ref2.nRow, aData.Ref2.nTab);
5271 if (aThis == rRange)
5272 return true;
5274 break;
5275 default:
5279 return false;
5284 void Test::testJumpToPrecedentsDependents()
5286 // Precedent is another cell that the cell references, while dependent is
5287 // another cell that references it.
5288 m_pDoc->InsertTab(0, rtl::OUString("Test"));
5290 m_pDoc->SetString(2, 0, 0, rtl::OUString("=A1+A2+B3")); // C1
5291 m_pDoc->SetString(2, 1, 0, rtl::OUString("=A1")); // C2
5292 m_pDoc->CalcAll();
5294 std::vector<ScTokenRef> aRefTokens;
5295 ScDocFunc& rDocFunc = m_xDocShRef->GetDocFunc();
5298 // C1's precedent should be A1:A2,B3.
5299 ScRangeList aRange(ScRange(2, 0, 0));
5300 rDocFunc.DetectiveCollectAllPreds(aRange, aRefTokens);
5301 CPPUNIT_ASSERT_MESSAGE("A1:A2 should be a precedent of C1.",
5302 hasRange(aRefTokens, ScRange(0, 0, 0, 0, 1, 0)));
5303 CPPUNIT_ASSERT_MESSAGE("B3 should be a precedent of C1.",
5304 hasRange(aRefTokens, ScRange(1, 2, 0)));
5308 // C2's precedent should be A1 only.
5309 ScRangeList aRange(ScRange(2, 1, 0));
5310 rDocFunc.DetectiveCollectAllPreds(aRange, aRefTokens);
5311 CPPUNIT_ASSERT_EQUAL_MESSAGE("there should only be one reference token.",
5312 aRefTokens.size(), static_cast<size_t>(1));
5313 CPPUNIT_ASSERT_MESSAGE("A1 should be a precedent of C1.",
5314 hasRange(aRefTokens, ScRange(0, 0, 0)));
5318 // A1's dependent should be C1:C2.
5319 ScRangeList aRange(ScRange(0, 0, 0));
5320 rDocFunc.DetectiveCollectAllSuccs(aRange, aRefTokens);
5321 CPPUNIT_ASSERT_MESSAGE("C1:C2 should be the only dependent of A1.",
5322 aRefTokens.size() == 1 && hasRange(aRefTokens, ScRange(2, 0, 0, 2, 1, 0)));
5325 m_pDoc->DeleteTab(0);
5328 void Test::testAutoFill()
5330 m_pDoc->InsertTab(0, "test");
5332 m_pDoc->SetValue(0,0,0,1);
5334 ScMarkData aMarkData;
5335 aMarkData.SelectTable(0, true);
5337 m_pDoc->Fill( 0, 0, 0, 0, NULL, aMarkData, 5);
5338 for (SCROW i = 0; i< 6; ++i)
5339 ASSERT_DOUBLES_EQUAL(static_cast<double>(i+1.0), m_pDoc->GetValue(0, i, 0));
5341 // check that hidden rows are not affected by autofill
5342 // set values for hidden rows
5343 m_pDoc->SetValue(0,1,0,10);
5344 m_pDoc->SetValue(0,2,0,10);
5346 m_pDoc->SetRowHidden(1, 2, 0, true);
5347 m_pDoc->Fill( 0, 0, 0, 0, NULL, aMarkData, 8);
5349 ASSERT_DOUBLES_EQUAL(10.0, m_pDoc->GetValue(0,1,0));
5350 ASSERT_DOUBLES_EQUAL(10.0, m_pDoc->GetValue(0,2,0));
5351 for (SCROW i = 3; i< 8; ++i)
5352 ASSERT_DOUBLES_EQUAL(static_cast<double>(i-1.0), m_pDoc->GetValue(0, i, 0));
5354 m_pDoc->Fill( 0, 0, 0, 8, NULL, aMarkData, 5, FILL_TO_RIGHT );
5355 for (SCCOL i = 0; i < 5; ++i)
5357 for(SCROW j = 0; j < 8; ++j)
5359 if (j > 2)
5361 ASSERT_DOUBLES_EQUAL(static_cast<double>(j-1+i), m_pDoc->GetValue(i, j, 0));
5363 else if (j == 0)
5365 ASSERT_DOUBLES_EQUAL(static_cast<double>(i+1), m_pDoc->GetValue(i, 0, 0));
5367 else if (j == 1 || j== 2)
5369 if(i == 0)
5370 ASSERT_DOUBLES_EQUAL(10.0, m_pDoc->GetValue(0,j,0));
5371 else
5372 ASSERT_DOUBLES_EQUAL(0.0, m_pDoc->GetValue(i,j,0));
5377 // test auto fill user data lists
5378 m_pDoc->SetString( 0, 100, 0, "January" );
5379 m_pDoc->Fill( 0, 100, 0, 100, NULL, aMarkData, 2, FILL_TO_BOTTOM, FILL_AUTO );
5380 rtl::OUString aTestValue = m_pDoc->GetString( 0, 101, 0 );
5381 CPPUNIT_ASSERT_EQUAL( aTestValue, rtl::OUString("February") );
5382 aTestValue = m_pDoc->GetString( 0, 102, 0 );
5383 CPPUNIT_ASSERT_EQUAL( aTestValue, rtl::OUString("March") );
5385 // test that two same user data list entries will not result in incremental fill
5386 m_pDoc->SetString( 0, 101, 0, "January" );
5387 m_pDoc->Fill( 0, 100, 0, 101, NULL, aMarkData, 2, FILL_TO_BOTTOM, FILL_AUTO );
5388 for ( SCROW i = 102; i <= 103; ++i )
5390 aTestValue = m_pDoc->GetString( 0, i, 0 );
5391 CPPUNIT_ASSERT_EQUAL( aTestValue, rtl::OUString("January") );
5393 m_pDoc->DeleteTab(0);
5396 void Test::testCopyPasteFormulas()
5398 m_pDoc->InsertTab(0, "Sheet1");
5399 m_pDoc->InsertTab(1, "Sheet2");
5401 m_pDoc->SetString(0,0,0, "=COLUMN($A$1)");
5402 m_pDoc->SetString(0,1,0, "=$A$1+B2" );
5403 m_pDoc->SetString(0,2,0, "=$Sheet2.A1");
5404 m_pDoc->SetString(0,3,0, "=$Sheet2.$A$1");
5405 m_pDoc->SetString(0,4,0, "=$Sheet2.A$1");
5407 // to prevent ScEditableTester in ScDocFunc::MoveBlock
5408 m_pDoc->SetInTest();
5409 ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(0,0,0), 1.0);
5410 ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(0,1,0), 1.0);
5411 ScDocFunc& rDocFunc = m_xDocShRef->GetDocFunc();
5412 bool bMoveDone = rDocFunc.MoveBlock(ScRange(0,0,0,0,4,0), ScAddress( 10, 10, 0), false, false, false, true);
5414 // check that moving was succesful, mainly for editable tester
5415 CPPUNIT_ASSERT(bMoveDone);
5416 ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(10,10,0), 1.0);
5417 ASSERT_DOUBLES_EQUAL(m_pDoc->GetValue(10,11,0), 1.0);
5418 rtl::OUString aFormula;
5419 m_pDoc->GetFormula(10,10,0, aFormula);
5420 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=COLUMN($A$1)"));
5421 m_pDoc->GetFormula(10,11,0, aFormula);
5422 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$A$1+L12"));
5423 m_pDoc->GetFormula(10,12,0, aFormula);
5424 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$Sheet2.K11"));
5425 m_pDoc->GetFormula(10,13,0, aFormula);
5426 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$Sheet2.$A$1"));
5427 m_pDoc->GetFormula(10,14,0, aFormula);
5428 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$Sheet2.K$1"));
5431 void Test::testCopyPasteFormulasExternalDoc()
5433 rtl::OUString aDocName("file:///source.fake");
5434 SfxMedium* pMedium = new SfxMedium(aDocName, STREAM_STD_READWRITE);
5435 m_xDocShRef->DoInitNew(pMedium);
5436 m_pDoc = m_xDocShRef->GetDocument();
5438 ScDocShellRef xExtDocSh = new ScDocShell;
5439 OUString aExtDocName("file:///extdata.fake");
5440 OUString aExtSh1Name("ExtSheet1");
5441 OUString aExtSh2Name("ExtSheet2");
5442 SfxMedium* pMed = new SfxMedium(aExtDocName, STREAM_STD_READWRITE);
5443 xExtDocSh->DoInitNew(pMed);
5444 CPPUNIT_ASSERT_MESSAGE("external document instance not loaded.",
5445 findLoadedDocShellByName(aExtDocName) != NULL);
5447 ScDocument* pExtDoc = xExtDocSh->GetDocument();
5448 pExtDoc->InsertTab(0, aExtSh1Name);
5449 pExtDoc->InsertTab(1, aExtSh2Name);
5451 m_pDoc->InsertTab(0, "Sheet1");
5452 m_pDoc->InsertTab(1, "Sheet2");
5454 m_pDoc->SetString(0,0,0, "=COLUMN($A$1)");
5455 m_pDoc->SetString(0,1,0, "=$A$1+B2" );
5456 m_pDoc->SetString(0,2,0, "=$Sheet2.A1");
5457 m_pDoc->SetString(0,3,0, "=$Sheet2.$A$1");
5458 m_pDoc->SetString(0,4,0, "=$Sheet2.A$1");
5459 m_pDoc->SetString(0,5,0, "=$Sheet1.$A$1");
5461 ScRange aRange(0,0,0,0,5,0);
5462 ScClipParam aClipParam(aRange, false);
5463 ScMarkData aMark;
5464 aMark.SetMarkArea(aRange);
5465 ScDocument* pClipDoc = new ScDocument(SCDOCMODE_CLIP);
5466 m_pDoc->CopyToClip(aClipParam, pClipDoc, &aMark);
5468 sal_uInt16 nFlags = IDF_ALL;
5469 aRange = ScRange(1,1,1,1,6,1);
5470 ScMarkData aMarkData2;
5471 aMarkData2.SetMarkArea(aRange);
5472 pExtDoc->CopyFromClip(aRange, aMarkData2, nFlags, NULL, pClipDoc);
5474 rtl::OUString aFormula;
5475 pExtDoc->GetFormula(1,1,1, aFormula);
5476 //adjust absolute refs pointing to the copy area
5477 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=COLUMN($B$2)"));
5478 pExtDoc->GetFormula(1,2,1, aFormula);
5479 //adjust absolute refs and keep relative refs
5480 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$B$2+C3"));
5481 pExtDoc->GetFormula(1,3,1, aFormula);
5482 // make absolute sheet refs external refs
5483 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("='file:///source.fake'#$Sheet2.B2"));
5484 pExtDoc->GetFormula(1,4,1, aFormula);
5485 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("='file:///source.fake'#$Sheet2.$A$1"));
5486 pExtDoc->GetFormula(1,5,1, aFormula);
5487 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("='file:///source.fake'#$Sheet2.B$1"));
5488 pExtDoc->GetFormula(1,6,1, aFormula);
5489 CPPUNIT_ASSERT_EQUAL(aFormula, rtl::OUString("=$ExtSheet2.$B$2"));
5492 void Test::testFindAreaPosRowDown()
5494 const char* aData[][2] = {
5495 { "", "1" },
5496 { "1", "" },
5497 { "1", "1" },
5498 { "", "1" },
5499 { "1", "1" },
5500 { "1", "" },
5501 { "1", "1" }, };
5503 ScDocument* pDoc = m_xDocShRef->GetDocument();
5504 rtl::OUString aTabName1("test1");
5505 pDoc->InsertTab(0, aTabName1);
5506 clearRange( pDoc, ScRange(0, 0, 0, 1, SAL_N_ELEMENTS(aData), 0));
5507 ScAddress aPos(0,0,0);
5508 ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
5509 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
5511 pDoc->SetRowHidden(4,4,0,true);
5512 bool bHidden = pDoc->RowHidden(4,0);
5513 CPPUNIT_ASSERT(bHidden);
5515 SCCOL nCol = 0;
5516 SCROW nRow = 0;
5517 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5519 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), nRow);
5520 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5522 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5524 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), nRow);
5525 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5527 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5529 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(5), nRow);
5530 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5532 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5534 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), nRow);
5535 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5537 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5539 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(MAXROW), nRow);
5540 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), nCol);
5542 nCol = 1;
5543 nRow = 2;
5545 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5547 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), nRow);
5548 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCol);
5550 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_DOWN);
5552 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), nRow);
5553 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCol);
5555 pDoc->DeleteTab(0);
5558 void Test::testFindAreaPosColRight()
5560 const char* aData[][7] = {
5561 { "", "1", "1", "", "1", "1", "1" },
5562 { "", "", "1", "1", "1", "", "1" }, };
5564 ScDocument* pDoc = m_xDocShRef->GetDocument();
5565 rtl::OUString aTabName1("test1");
5566 pDoc->InsertTab(0, aTabName1);
5567 clearRange( pDoc, ScRange(0, 0, 0, 7, SAL_N_ELEMENTS(aData), 0));
5568 ScAddress aPos(0,0,0);
5569 ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
5570 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
5572 pDoc->SetColHidden(4,4,0,true);
5573 bool bHidden = pDoc->ColHidden(4,0);
5574 CPPUNIT_ASSERT(bHidden);
5576 SCCOL nCol = 0;
5577 SCROW nRow = 0;
5578 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5580 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5581 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), nCol);
5583 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5585 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5586 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(2), nCol);
5588 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5590 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5591 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(5), nCol);
5593 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5595 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5596 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(6), nCol);
5598 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5600 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), nRow);
5601 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(MAXCOL), nCol);
5603 nCol = 2;
5604 nRow = 1;
5606 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5608 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), nRow);
5609 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(3), nCol);
5611 pDoc->FindAreaPos(nCol, nRow, 0, SC_MOVE_RIGHT);
5613 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), nRow);
5614 CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(6), nCol);
5616 pDoc->DeleteTab(0);
5619 // regression test fo fdo#53814, sorting doens't work as expected
5620 // if cells in the sort are referenced by formulas
5621 void Test::testSortWithFormulaRefs()
5623 ScDocument* pDoc = m_xDocShRef->GetDocument();
5624 rtl::OUString aTabName1("List1");
5625 rtl::OUString aTabName2("List2");
5626 pDoc->InsertTab(0, aTabName1);
5627 pDoc->InsertTab(1, aTabName2);
5629 const char* aFormulaData[6] = {
5630 "=IF($List1.A2<>\"\",$List1.A2,\"\")",
5631 "=IF($List1.A3<>\"\",$List1.A3,\"\")",
5632 "=IF($List1.A4<>\"\",$List1.A4,\"\")",
5633 "=IF($List1.A5<>\"\",$List1.A5,\"\")",
5634 "=IF($List1.A6<>\"\",$List1.A6,\"\")",
5635 "=IF($List1.A7<>\"\",$List1.A7,\"\")",
5638 const char* aTextData[4] = {
5639 "bob",
5640 "tim",
5641 "brian",
5642 "larry",
5645 const char* aResults[ 6 ] = {
5646 "bob",
5647 "brian",
5648 "larry",
5649 "tim",
5653 // insert data to sort
5654 SCROW nStart = 1, nEnd = 4;
5655 for ( SCROW i = nStart; i <= nEnd; ++i )
5656 pDoc->SetString( 0, i, 0, rtl::OUString::createFromAscii(aTextData[i-1]) );
5657 // insert forumulas
5658 nStart = 0;
5659 nEnd = SAL_N_ELEMENTS(aFormulaData);
5660 for ( SCROW i = nStart; i < nEnd; ++i )
5661 pDoc->SetString( 0, i, 1, rtl::OUString::createFromAscii(aFormulaData[i]) );
5663 ScSortParam aSortData;
5664 aSortData.nCol1 = 0;
5665 aSortData.nCol2 = 0;
5666 aSortData.nRow1 = 1;
5667 aSortData.nRow2 = 7;
5668 aSortData.maKeyState[0].bDoSort = true;
5669 aSortData.maKeyState[0].nField = 0;
5671 pDoc->Sort(0, aSortData, false, NULL);
5673 nEnd = SAL_N_ELEMENTS( aResults );
5674 for ( SCROW i = nStart; i < nEnd; ++i )
5676 rtl::OUString sResult;
5677 pDoc->GetString( 0, i + 1, 0, sResult );
5678 CPPUNIT_ASSERT_EQUAL( rtl::OUString::createFromAscii( aResults[ i ] ), sResult );
5680 pDoc->DeleteTab(0);
5681 pDoc->DeleteTab(1);
5684 void Test::testSort()
5686 ScDocument* pDoc = m_xDocShRef->GetDocument();
5687 rtl::OUString aTabName1("test1");
5688 pDoc->InsertTab(0, aTabName1);
5690 const char* aData[][2] = {
5691 { "2", "4" },
5692 { "4", "1" },
5693 { "1", "2" }
5696 clearRange( pDoc, ScRange(0, 0, 0, 1, SAL_N_ELEMENTS(aData), 0));
5697 ScAddress aPos(0,0,0);
5698 ScRange aDataRange = insertRangeData( pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
5699 CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
5701 rtl::OUString aHello("Hello");
5702 rtl::OUString aJimBob("Jim Bob");
5703 ScAddress rAddr(1, 1, 0);
5704 ScPostIt* pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
5705 pNote->SetText(rAddr, aHello);
5706 pNote->SetAuthor(aJimBob);
5708 ScSortParam aSortData;
5709 aSortData.nCol1 = 1;
5710 aSortData.nCol2 = 1;
5711 aSortData.nRow1 = 0;
5712 aSortData.nRow2 = 2;
5713 aSortData.maKeyState[0].bDoSort = true;
5714 aSortData.maKeyState[0].nField = 1;
5716 pDoc->Sort(0, aSortData, false, NULL);
5717 double nVal = pDoc->GetValue(1,0,0);
5718 ASSERT_DOUBLES_EQUAL(nVal, 1.0);
5720 // check that note is also moved
5721 pNote = m_pDoc->GetNotes(0)->findByAddress( 1, 0 );
5722 CPPUNIT_ASSERT(pNote);
5724 pDoc->DeleteTab(0);
5727 void Test::testShiftCells()
5729 m_pDoc->InsertTab(0, "foo");
5731 OUString aTestVal("Some Text");
5733 // Text into cell E5.
5734 m_pDoc->SetString(4, 3, 0, aTestVal);
5736 // Insert cell at D5. This should shift the string cell to right.
5737 m_pDoc->InsertCol(3, 0, 3, 0, 3, 1);
5738 OUString aStr = m_pDoc->GetString(5, 3, 0);
5739 CPPUNIT_ASSERT_MESSAGE("We should have a string cell here.", aStr == aTestVal);
5740 CPPUNIT_ASSERT_MESSAGE("D5 is supposed to be blank.", m_pDoc->IsBlockEmpty(0, 3, 4, 3, 4));
5742 // Delete cell D5, to shift the text cell back into D5.
5743 m_pDoc->DeleteCol(3, 0, 3, 0, 3, 1);
5744 aStr = m_pDoc->GetString(4, 3, 0);
5745 CPPUNIT_ASSERT_MESSAGE("We should have a string cell here.", aStr == aTestVal);
5746 CPPUNIT_ASSERT_MESSAGE("E5 is supposed to be blank.", m_pDoc->IsBlockEmpty(0, 4, 4, 4, 4));
5748 m_pDoc->DeleteTab(0);
5751 void Test::testDeleteRow()
5753 ScDocument* pDoc = m_xDocShRef->GetDocument();
5754 rtl::OUString aSheet1("Sheet1");
5755 pDoc->InsertTab(0, aSheet1);
5757 rtl::OUString aHello("Hello");
5758 rtl::OUString aJimBob("Jim Bob");
5759 ScAddress rAddr(1, 1, 0);
5760 ScPostIt* pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
5761 pNote->SetText(rAddr, aHello);
5762 pNote->SetAuthor(aJimBob);
5764 pDoc->DeleteRow(0, 0, MAXCOL, 0, 1, 1);
5766 CPPUNIT_ASSERT(m_pDoc->GetNotes(0)->empty());
5767 pDoc->DeleteTab(0);
5770 void Test::testDeleteCol()
5772 ScDocument* pDoc = m_xDocShRef->GetDocument();
5773 rtl::OUString aSheet1("Sheet1");
5774 pDoc->InsertTab(0, aSheet1);
5776 rtl::OUString aHello("Hello");
5777 rtl::OUString aJimBob("Jim Bob");
5778 ScAddress rAddr(1, 1, 0);
5779 ScPostIt* pNote = m_pDoc->GetNotes(rAddr.Tab())->GetOrCreateNote(rAddr);
5780 pNote->SetText(rAddr, aHello);
5781 pNote->SetAuthor(aJimBob);
5783 pDoc->DeleteCol(0, 0, MAXROW, 0, 1, 1);
5785 CPPUNIT_ASSERT(m_pDoc->GetNotes(0)->empty());
5786 pDoc->DeleteTab(0);
5789 CPPUNIT_TEST_SUITE_REGISTRATION(Test);
5793 CPPUNIT_PLUGIN_IMPLEMENT();
5795 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */