1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
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
15 * The Initial Developer of the Original Code is
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>
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"
51 #include "reftokenhelper.hxx"
52 #include "userdat.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"
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>
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
;
109 class Test
: public test::BootstrapFixture
{
113 virtual void setUp();
114 virtual void tearDown();
117 void testRangeList();
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();
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
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
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
205 void testPivotTableFieldReference();
207 void testSheetCopy();
208 void testSheetMove();
209 void testExternalRef();
210 void testExternalRefFunctions();
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
226 void testFunctionLists();
228 void testGraphicsInGroup();
229 void testGraphicsOnSheetMove();
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
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();
250 void testCopyPasteFormulas();
251 void testCopyPasteFormulasExternalDoc();
253 void testFindAreaPosRowDown();
254 void testFindAreaPosColRight();
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();
323 ScDocShellRef m_xDocShRef
;
326 void clearRange(ScDocument
* pDoc
, const ScRange
& rRange
)
328 ScMarkData aMarkData
;
329 aMarkData
.SetMarkArea(rRange
);
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
)
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");
373 * Temporarily switch on/off auto calculation mode.
380 AutoCalcSwitch(ScDocument
* pDoc
, bool bAutoCalc
) : mpDoc(pDoc
), mbOldValue(pDoc
->GetAutoCalc())
382 mpDoc
->SetAutoCalc(bAutoCalc
);
387 mpDoc
->SetAutoCalc(mbOldValue
);
392 * Temporarily set formula grammar.
394 class FormulaGrammarSwitch
397 formula::FormulaGrammar::Grammar meOldGrammar
;
399 FormulaGrammarSwitch(ScDocument
* pDoc
, formula::FormulaGrammar::Grammar eGrammar
) :
400 mpDoc(pDoc
), meOldGrammar(pDoc
->GetGrammar())
402 mpDoc
->SetGrammar(eGrammar
);
405 ~FormulaGrammarSwitch()
407 mpDoc
->SetGrammar(meOldGrammar
);
418 BootstrapFixture::setUp();
421 m_xDocShRef
= new ScDocShell(
423 SFXMODEL_DISABLE_EMBEDDED_SCRIPTS
|
424 SFXMODEL_DISABLE_DOCUMENT_RECOVERY
);
426 m_pDoc
= m_xDocShRef
->GetDocument();
429 void Test::tearDown()
432 BootstrapFixture::tearDown();
435 void Test::testCollator()
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");
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'");
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
)
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)"));
499 pDoc
->GetValue (0, 2, 0, result
);
500 CPPUNIT_ASSERT_MESSAGE ("calculation failed", result
== 2.0);
503 void testFuncPRODUCT(ScDocument
* pDoc
)
507 pDoc
->SetValue(0, 0, 0, val
);
509 pDoc
->SetValue(0, 1, 0, val
);
511 pDoc
->SetValue(0, 2, 0, val
);
512 pDoc
->SetString(0, 3, 0, OUString("=PRODUCT(A1:A3)"));
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})"));
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
)
527 // Clear the area first.
528 clearRange(pDoc
, ScRange(0, 0, 0, 1, 20, 0));
530 // Put values to reference.
532 pDoc
->SetValue(0, 0, 0, val
);
533 pDoc
->SetString(0, 2, 0, OUString("Text"));
535 pDoc
->SetValue(0, 3, 0, val
);
537 pDoc
->SetValue(0, 4, 0, val
);
539 pDoc
->SetValue(0, 5, 0, val
);
540 pDoc
->SetString(0, 6, 0, OUString("'12.3"));
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)"));
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\")"));
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.
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
];
578 cerr
<< "row " << (i
+1) << ": expected=" << checks1
[i
] << " actual=" << result
<< endl
;
579 CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
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
];
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
[] = {
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
625 const char* pFormula
; double fResult
;
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
)
644 pDoc
->SetString(0, nRow
, 0, rtl::OUString::createFromAscii(aChecks
[i
].pFormula
));
648 for (SCROW i
= 0; i
< nRows
; ++i
)
652 pDoc
->GetValue(0, nRow
, 0, result
);
653 bool bGood
= result
== aChecks
[i
].fResult
;
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.
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)"));
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
[] = {
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
706 const char* pFormula
; const char* pResult
;
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
)
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.
733 aMark
.SelectOneTable(0);
734 pDoc
->InsertMatrixFormula(0, 20 + nRows
-2, 0, 20 + nRows
-1, aMark
, "=IFERROR(3*A11:A12;1998)", NULL
);
738 for (SCROW i
= 0; i
< nRows
; ++i
)
740 rtl::OUString aResult
;
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
)
753 clearRange(pDoc
, ScRange(0, 0, 0, 5, 39, 0));
756 const char* aData
[][2] = {
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");
785 const char* pLookup
; const char* pFormula
; const char* pRes
;
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
));
814 printRange(pDoc
, ScRange(3, 0, 0, 4, 17, 0), "formula data for VLOOKUP");
817 for (size_t i
= 0; i
< SAL_N_ELEMENTS(aChecks
); ++i
)
820 // Skip the header row.
824 pDoc
->GetString(4, i
, 0, aRes
);
825 bool bGood
= aRes
.equalsAscii(aChecks
[i
].pRes
);
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);
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
);
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
)
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
[] = {
908 StrStrCheck aChecks
[] = {
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
[] = {
949 StrStrCheck aChecks
[] = {
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.
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
));
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
);
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
);
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
);
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
);
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
);
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
);
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");
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);
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
));
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
));
1218 m_pDoc
->SetValue(0, 0, 0, val
);
1219 m_pDoc
->SetString(0, 1, 0, OUString("=IF(A1>0;NOW();0"));
1221 m_pDoc
->GetValue(0, 1, 0, now1
);
1222 CPPUNIT_ASSERT_MESSAGE("Value of NOW() should be positive.", now1
> 0.0);
1225 m_pDoc
->SetValue(0, 0, 0, val
);
1226 m_pDoc
->CalcFormulaTree(false, false);
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);
1232 m_pDoc
->SetValue(0, 0, 0, val
);
1233 m_pDoc
->CalcFormulaTree(false, false);
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);
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
);
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.
1332 for (SCROW nRow
= 1; nRow
<= 9; ++nRow
)
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.
1356 m_pDoc
->SetValue(0, 0, 0, val
);
1358 m_pDoc
->SetValue(1, 0, 0, val
);
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);
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
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
1400 ScCalcConfig aConfig
;
1402 // With "Empty string as zero" option.
1403 aConfig
.mbEmptyStringAsZero
= true;
1404 ScInterpreter::SetGlobalConfig(aConfig
);
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
);
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()
1434 const char* pName
; const char* pExpr
; sal_uInt16 nIndex
;
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(
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"));
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;
1500 const char *pStr
; int eSep
; bool bResult
; double nValue
;
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
? ',' : '.',
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
)
1533 rMat
.GetDimensions(nC
, nR
);
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);
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);
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()
1614 // First, test the zero matrix type.
1615 pMat
= new ScMatrix(0, 0, 0.0);
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
);
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");
1684 m_pDoc
->SetValue(0, 1, 0, val
);
1686 m_pDoc
->SetValue(1, 1, 0, val
);
1688 // Create a matrix range in A4:B5 referencing A1:B2.
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);
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.
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
);
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");
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
)
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
];
1765 OUString aCheckVal
= OUString::createFromAscii(p
);
1766 bool bEqual
= aCheckVal
.equals(aVal
);
1769 cout
<< "Expected: " << aCheckVal
<< " Actual: " << aVal
<< endl
;
1773 else if (!aVal
.isEmpty())
1775 cout
<< "Empty cell expected" << endl
;
1780 printer
.print(pCaption
);
1784 ScDPObject
* createDPFromSourceDesc(
1785 ScDocument
* pDoc
, const ScSheetSourceDesc
& rDesc
, DPFieldDef aFields
[], size_t nFieldCount
,
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
);
1826 sheet::DataPilotFieldSortInfo aSortInfo
;
1827 aSortInfo
.IsAscending
= true;
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();
1855 ScDPObject
* createDPFromRange(
1856 ScDocument
* pDoc
, const ScRange
& rRange
, DPFieldDef aFields
[], size_t nFieldCount
,
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();
1875 ScRange
refreshGroups(ScDPCollection
* pDPs
, ScDPObject
* pDPObj
)
1877 // We need to first create group data in the cache, then the group data in
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 }
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
)
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
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.
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 }
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 }
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;
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 }
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] = {
2254 { "Group2", "- all -" },
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] = {
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] = {
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] = {
2340 { "Group2", "- all -" },
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 }
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
);
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);
2457 m_pDoc
->DeleteTab(1);
2458 m_pDoc
->DeleteTab(0);
2461 void Test::testPivotTableCache()
2463 m_pDoc
->InsertTab(0, OUString("Data"));
2466 const char* aData
[][3] = {
2467 { "F1", "F2", "F3" },
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
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.
2580 // Dimension 0: Z, R, A, F, Y, 12
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));
2592 CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem
&& *pItem
== aTest
);
2596 // Dimension 1: A, A, B, B, C, C
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
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"));
2652 const char* aData
[][2] = {
2653 { "Name", "Value" },
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] = {
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"));
2749 const char* aData
[][2] = {
2750 { "Name", "Value" },
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] = {
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 },
2838 { "Group1", "A", "1" },
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 },
2872 { "Group1", "A", "1" },
2875 { "Group2", "D", "4" },
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"));
2900 const char* aData
[][2] = {
2901 { "Order", "Score" },
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;
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] = {
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"));
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
);
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
);
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] = {
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"));
3169 const char* aData
[][2] = {
3170 { "Name", "Value" },
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] = {
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] = {
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] = {
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"));
3282 const char* aData
[][2] = {
3283 { "Name", "Value" },
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
);
3308 // Don't check the header row.
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] = {
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"));
3361 const char* aData
[][2] = {
3362 { "Name", "Value" },
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] = {
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
);
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])");
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 // +--------------+----------------+
3485 // +--------------+----------------+
3486 // | Dennis | <Dennis total> |
3487 // +--------------+----------------+
3488 // | Mike | <Miks total> |
3489 // +--------------+----------------+
3490 // | Sam | <Sam total> |
3491 // +--------------+----------------+
3492 // | Total Result | ... |
3493 // +--------------+----------------+
3495 aPos
= aOutRange
.aStart
;
3498 double fTest
= m_pDoc
->GetValue(aPos
);
3499 CPPUNIT_ASSERT_MESSAGE("Incorrect value for Dennis.", rtl::math::approxEqual(fTest
, fDennisTotal
));
3501 fTest
= m_pDoc
->GetValue(aPos
);
3502 CPPUNIT_ASSERT_MESSAGE("Incorrect value for Mike.", rtl::math::approxEqual(fTest
, fMikeTotal
));
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"));
3522 const char* aData
[][2] = {
3523 { "Name", "Value" },
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] = {
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] = {
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] = {
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] = {
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] = {
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] = {
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);
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));
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));
3774 SfxMedium
* pMedium
= pShell
->GetMedium();
3777 OUString aName
= pMedium
->GetName();
3778 if (aName
.equals(rName
))
3781 pShell
= static_cast<ScDocShell
*>(SfxObjectShell::GetNext(*pShell
, &aType
, false));
3786 ScRange
getCachedRange(const ScExternalRefCache::TableTypeRef
& pCacheTab
)
3790 vector
<SCROW
> aRows
;
3791 pCacheTab
->getAllRows(aRows
);
3792 vector
<SCROW
>::const_iterator itrRow
= aRows
.begin(), itrRowEnd
= aRows
.end();
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
;
3805 aRange
.aStart
= ScAddress(nCol
, nRow
, 0);
3806 aRange
.aEnd
= aRange
.aStart
;
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
);
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");
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
);
3863 pExtDoc
->SetValue(1, 1, 0, val
);
3865 pExtDoc
->SetValue(1, 2, 0, val
);
3867 pExtDoc
->SetValue(1, 3, 0, val
);
3869 pExtDoc
->SetValue(1, 4, 0, val
);
3871 // Sheet 2 remains empty.
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
);
3881 pExtDoc
->SetValue(1, 1, 2, val
);
3883 pExtDoc
->SetValue(1, 2, 2, val
);
3885 pExtDoc
->SetValue(1, 3, 2, val
);
3887 pExtDoc
->SetValue(1, 4, 2, val
);
3889 // Test external refernces on the main document while the external
3890 // document is still in memory.
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)"));
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"));
4040 pExtDoc
->SetValue(0, 0, 0, val
);
4041 // leave cell B1 empty.
4043 pExtDoc
->SetValue(0, 1, 0, val
);
4044 pExtDoc
->SetValue(1, 1, 0, val
);
4046 pExtDoc
->SetValue(0, 2, 0, val
);
4047 pExtDoc
->SetValue(1, 2, 0, val
);
4049 pExtDoc
->SetValue(0, 3, 0, val
);
4050 pExtDoc
->SetValue(1, 3, 0, val
);
4052 m_pDoc
->InsertTab(0, rtl::OUString("Test"));
4055 const char* pFormula
; double fResult
;
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
));
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));
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
[] = {
4208 const char* aDateTime
[] = {
4230 const char* aFinancial
[] = {
4256 const char* aInformation
[] = {
4277 const char* aLogical
[] = {
4290 const char* aMathematical
[] = {
4362 const char* aArray
[] = {
4380 const char* aStatistical
[] = {
4464 const char* aSpreadsheet
[] = {
4489 const char* aText
[] = {
4526 const char* Category
; const char** Functions
;
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
},
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);
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
);
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
);
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
);
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
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);
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
);
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
);
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" },
4895 SCCOL nCols
= SAL_N_ELEMENTS(aData
[0]);
4896 SCROW nRows
= SAL_N_ELEMENTS(aData
);
4899 for (SCROW i
= 0; i
< nRows
; ++i
)
4900 for (SCCOL j
= 0; j
< nCols
; ++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);
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;
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);
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.
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;
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
);
5000 rtl::OUString
aFormulaString("=local1+global+SUM($C$1:$D$4)");
5001 m_pDoc
->SetString(1, 0, 0, aFormulaString
);
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);
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
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("")));
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
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);
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);
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
);
5123 m_pDoc
->GetName(0,nameJustSet
);
5124 CPPUNIT_ASSERT_MESSAGE("the correct name is not set after undo", nameJustSet
== anOldName
);
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"));
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
);
5158 CPPUNIT_ASSERT_MESSAGE("the correct color is not set after undo", m_pDoc
->GetTabBgColor(0)== aOldTabBgColor
);
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"));
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);
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
))
5254 switch (p
->GetType())
5256 case formula::svSingleRef
:
5258 ScSingleRefData aData
= p
->GetSingleRef();
5259 if (rRange
.aStart
!= rRange
.aEnd
)
5262 ScAddress
aThis(aData
.nCol
, aData
.nRow
, aData
.nTab
);
5263 if (aThis
== rRange
.aStart
)
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
)
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
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
)
5361 ASSERT_DOUBLES_EQUAL(static_cast<double>(j
-1+i
), m_pDoc
->GetValue(i
, j
, 0));
5365 ASSERT_DOUBLES_EQUAL(static_cast<double>(i
+1), m_pDoc
->GetValue(i
, 0, 0));
5367 else if (j
== 1 || j
== 2)
5370 ASSERT_DOUBLES_EQUAL(10.0, m_pDoc
->GetValue(0,j
,0));
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);
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] = {
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
);
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
);
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
);
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
);
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
);
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
);
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] = {
5645 const char* aResults
[ 6 ] = {
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]) );
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
);
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] = {
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
);
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());
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());
5789 CPPUNIT_TEST_SUITE_REGISTRATION(Test
);
5793 CPPUNIT_PLUGIN_IMPLEMENT();
5795 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */