1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 /*
3 * This file is part of the LibreOffice project.
4 *
5 * This Source Code Form is subject to the terms of the Mozilla Public
6 * License, v. 2.0. If a copy of the MPL was not distributed with this
7 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8 */
9
10 #include <sal/config.h>
11 #include <unotest/filters-test.hxx>
12 #include <test/bootstrapfixture.hxx>
13
14 #include "helper/qahelper.hxx"
15
16 #include <docsh.hxx>
17 #include <inputopt.hxx>
18 #include <postit.hxx>
19 #include <document.hxx>
20 #include <drwlayer.hxx>
21 #include <userdat.hxx>
22 #include <formulacell.hxx>
23 #include <tabprotection.hxx>
24 #include <testlotus.hxx>
25 #include <dbdocfun.hxx>
26 #include <globalnames.hxx>
27 #include <dbdata.hxx>
28 #include <sortparam.hxx>
29 #include <scopetools.hxx>
30 #include <scmod.hxx>
31
32 #include <svx/svdpage.hxx>
33
34 using namespace ::com::sun::star;
35 using namespace ::com::sun::star::uno;
36
37 /* Implementation of Filters test */
38
39 class ScFiltersTest
40 : public test::FiltersTest
41 , public ScBootstrapFixture
42 {
43 public:
44 ScFiltersTest();
45
46 virtual void setUp() override;
47 virtual void tearDown() override;
48
49 virtual bool load( const OUString &rFilter, const OUString &rURL,
50 const OUString &rUserData, SfxFilterFlags nFilterFlags,
51 SotClipboardFormatId nClipboardID, unsigned int nFilterVersion) override;
52 /**
53 * Ensure CVEs remain unbroken
54 */
55 void testCVEs();
56
57 //ods, xls, xlsx filter tests
58 void testRangeNameODS(); // only test ods here, xls and xlsx in subsequent_filters-test
59 void testContentODS();
60 void testContentXLS();
61 void testContentXLSX();
62 void testContentXLSXStrict(); // strict OOXML
63 void testContentLotus123();
64 void testContentofz9704();
65 void testContentDIF();
66 void testContentXLSB();
67 void testContentXLS_XML();
68 void testContentGnumeric();
69 void testSharedFormulaXLS();
70 void testSharedFormulaXLSX();
71 void testSharedFormulaRefUpdateXLSX();
72 void testSheetNamesXLSX();
73 void testTdf79998();
74 void testLegacyCellAnchoredRotatedShape();
75 void testEnhancedProtectionXLS();
76 void testEnhancedProtectionXLSX();
77 void testSortWithSharedFormulasODS();
78 void testSortWithSheetExternalReferencesODS();
79 void testSortWithSheetExternalReferencesODS_Impl( ScDocShellRef const & xDocShRef, SCROW nRow1, SCROW nRow2,
80 bool bCheckRelativeInSheet );
81 void testSortWithFormattingXLS();
82
83 CPPUNIT_TEST_SUITE(ScFiltersTest);
84 CPPUNIT_TEST(testCVEs);
85 CPPUNIT_TEST(testRangeNameODS);
86 CPPUNIT_TEST(testContentODS);
87 CPPUNIT_TEST(testContentXLS);
88 CPPUNIT_TEST(testContentXLSX);
89 CPPUNIT_TEST(testContentXLSXStrict);
90 CPPUNIT_TEST(testContentLotus123);
91 CPPUNIT_TEST(testContentofz9704);
92 CPPUNIT_TEST(testContentDIF);
93 CPPUNIT_TEST(testContentXLSB);
94 CPPUNIT_TEST(testContentXLS_XML);
95 CPPUNIT_TEST(testContentGnumeric);
96 CPPUNIT_TEST(testSharedFormulaXLS);
97 CPPUNIT_TEST(testSharedFormulaXLSX);
98 CPPUNIT_TEST(testSharedFormulaRefUpdateXLSX);
99 CPPUNIT_TEST(testSheetNamesXLSX);
100 CPPUNIT_TEST(testTdf79998);
101 CPPUNIT_TEST(testLegacyCellAnchoredRotatedShape);
102 CPPUNIT_TEST(testEnhancedProtectionXLS);
103 CPPUNIT_TEST(testEnhancedProtectionXLSX);
104 CPPUNIT_TEST(testSortWithSharedFormulasODS);
105 CPPUNIT_TEST(testSortWithSheetExternalReferencesODS);
106 CPPUNIT_TEST(testSortWithFormattingXLS);
107
108 CPPUNIT_TEST_SUITE_END();
109
110 private:
111 uno::Reference<uno::XInterface> m_xCalcComponent;
112 bool mbUpdateReferenceOnSort; ///< Remember the configuration option so that we can set it back.
113 };
114
load(const OUString & rFilter,const OUString & rURL,const OUString & rUserData,SfxFilterFlags nFilterFlags,SotClipboardFormatId nClipboardID,unsigned int nFilterVersion)115 bool ScFiltersTest::load(const OUString &rFilter, const OUString &rURL,
116 const OUString &rUserData, SfxFilterFlags nFilterFlags,
117 SotClipboardFormatId nClipboardID, unsigned int nFilterVersion)
118 {
119 ScDocShellRef xDocShRef = ScBootstrapFixture::load(rURL, rFilter, rUserData,
120 OUString(), nFilterFlags, nClipboardID, nFilterVersion );
121 bool bLoaded = xDocShRef.is();
122 //reference counting of ScDocShellRef is very confused.
123 if (bLoaded)
124 xDocShRef->DoClose();
125 return bLoaded;
126 }
127
testCVEs()128 void ScFiltersTest::testCVEs()
129 {
130 #ifndef DISABLE_CVE_TESTS
131 testDir("Quattro Pro 6.0",
132 m_directories.getURLFromSrc("/sc/qa/unit/data/qpro/"));
133
134 //warning, the current "sylk filter" in sc (docsh.cxx) automatically
135 //chains on failure on trying as csv, rtf, etc. so "success" may
136 //not indicate that it imported as .slk.
137 testDir("SYLK",
138 m_directories.getURLFromSrc("/sc/qa/unit/data/slk/"));
139
140 testDir("MS Excel 97",
141 m_directories.getURLFromSrc("/sc/qa/unit/data/xls/"));
142
143 testDir("Calc Office Open XML",
144 m_directories.getURLFromSrc("/sc/qa/unit/data/xlsx/"), OUString(), XLSX_FORMAT_TYPE);
145
146 testDir("Calc Office Open XML",
147 m_directories.getURLFromSrc("/sc/qa/unit/data/xlsm/"), OUString(), XLSX_FORMAT_TYPE);
148
149 testDir("dBase",
150 m_directories.getURLFromSrc("/sc/qa/unit/data/dbf/"));
151
152 testDir("Lotus",
153 m_directories.getURLFromSrc("/sc/qa/unit/data/wks/"));
154
155 #endif
156 }
157
158 namespace {
159
testRangeNameImpl(const ScDocument & rDoc)160 void testRangeNameImpl(const ScDocument& rDoc)
161 {
162 //check one range data per sheet and one global more detailed
163 //add some more checks here
164 ScRangeData* pRangeData = rDoc.GetRangeName()->findByUpperName(OUString("GLOBAL1"));
165 CPPUNIT_ASSERT_MESSAGE("range name Global1 not found", pRangeData);
166 double aValue;
167 rDoc.GetValue(1,0,0,aValue);
168 ASSERT_DOUBLES_EQUAL_MESSAGE("range name Global1 should reference Sheet1.A1", 1.0, aValue);
169 pRangeData = rDoc.GetRangeName(0)->findByUpperName(OUString("LOCAL1"));
170 CPPUNIT_ASSERT_MESSAGE("range name Sheet1.Local1 not found", pRangeData);
171 rDoc.GetValue(1,2,0,aValue);
172 ASSERT_DOUBLES_EQUAL_MESSAGE("range name Sheet1.Local1 should reference Sheet1.A3", 3.0, aValue);
173 pRangeData = rDoc.GetRangeName(1)->findByUpperName(OUString("LOCAL2"));
174 CPPUNIT_ASSERT_MESSAGE("range name Sheet2.Local2 not found", pRangeData);
175 //check for correct results for the remaining formulas
176 rDoc.GetValue(1,1,0, aValue);
177 ASSERT_DOUBLES_EQUAL_MESSAGE("=global2 should be 2", 2.0, aValue);
178 rDoc.GetValue(1,3,0, aValue);
179 ASSERT_DOUBLES_EQUAL_MESSAGE("=local2 should be 4", 4.0, aValue);
180 rDoc.GetValue(2,0,0, aValue);
181 ASSERT_DOUBLES_EQUAL_MESSAGE("=SUM(global3) should be 10", 10.0, aValue);
182 }
183
184 }
185
testRangeNameODS()186 void ScFiltersTest::testRangeNameODS()
187 {
188 ScDocShellRef xDocSh = loadDoc("named-ranges-global.", FORMAT_ODS);
189
190 CPPUNIT_ASSERT_MESSAGE("Failed to load named-ranges-global.*", xDocSh.is());
191
192 xDocSh->DoHardRecalc();
193
194 ScDocument& rDoc = xDocSh->GetDocument();
195 testRangeNameImpl(rDoc);
196
197 OUString aCSVPath;
198 createCSVPath( "rangeExp_Sheet2.", aCSVPath );
199 testFile( aCSVPath, rDoc, 1);
200 xDocSh->DoClose();
201 }
202
203 namespace {
204
testContentImpl(ScDocument & rDoc,sal_Int32 nFormat)205 void testContentImpl(ScDocument& rDoc, sal_Int32 nFormat ) //same code for ods, xls, xlsx
206 {
207 double fValue;
208 //check value import
209 rDoc.GetValue(0,0,0,fValue);
210 ASSERT_DOUBLES_EQUAL_MESSAGE("value not imported correctly", 1.0, fValue);
211 rDoc.GetValue(0,1,0,fValue);
212 ASSERT_DOUBLES_EQUAL_MESSAGE("value not imported correctly", 2.0, fValue);
213 OUString aString = rDoc.GetString(1, 0, 0);
214
215 //check string import
216 CPPUNIT_ASSERT_EQUAL_MESSAGE("string imported not correctly", OUString("String1"), aString);
217 aString = rDoc.GetString(1, 1, 0);
218 CPPUNIT_ASSERT_EQUAL_MESSAGE("string not imported correctly", OUString("String2"), aString);
219
220 //check basic formula import
221 // in case of DIF it just contains values
222 rDoc.GetValue(2,0,0,fValue);
223 ASSERT_DOUBLES_EQUAL_MESSAGE("=2*3", 6.0, fValue);
224 rDoc.GetValue(2,1,0,fValue);
225 ASSERT_DOUBLES_EQUAL_MESSAGE("=2+3", 5.0, fValue);
226 rDoc.GetValue(2,2,0,fValue);
227 ASSERT_DOUBLES_EQUAL_MESSAGE("=2-3", -1.0, fValue);
228 rDoc.GetValue(2,3,0,fValue);
229 ASSERT_DOUBLES_EQUAL_MESSAGE("=C1+C2", 11.0, fValue);
230
231 //check merged cells import
232 if (nFormat != FORMAT_LOTUS123 && nFormat != FORMAT_DIF && nFormat != FORMAT_XLS_XML
233 && nFormat != FORMAT_GNUMERIC)
234 {
235 SCCOL nCol = 4;
236 SCROW nRow = 1;
237 rDoc.ExtendMerge(4, 1, nCol, nRow, 0);
238 CPPUNIT_ASSERT_MESSAGE("merged cells are not imported", nCol == 5 && nRow == 2);
239
240 //check notes import
241 ScAddress aAddress(7, 2, 0);
242 ScPostIt* pNote = rDoc.GetNote(aAddress);
243 CPPUNIT_ASSERT_MESSAGE("note not imported", pNote);
244 CPPUNIT_ASSERT_EQUAL_MESSAGE("note text not imported correctly", OUString("Test"), pNote->GetText() );
245 }
246
247 //add additional checks here
248 }
249
250 }
251
testContentODS()252 void ScFiltersTest::testContentODS()
253 {
254 ScDocShellRef xDocSh = loadDoc("universal-content.", FORMAT_ODS);
255 xDocSh->DoHardRecalc();
256
257 ScDocument& rDoc = xDocSh->GetDocument();
258 testContentImpl(rDoc, FORMAT_ODS);
259 xDocSh->DoClose();
260 }
261
testContentXLS()262 void ScFiltersTest::testContentXLS()
263 {
264 ScDocShellRef xDocSh = loadDoc("universal-content.", FORMAT_XLS);
265 xDocSh->DoHardRecalc();
266
267 ScDocument& rDoc = xDocSh->GetDocument();
268 testContentImpl(rDoc, FORMAT_XLS);
269 xDocSh->DoClose();
270 }
271
testContentXLSX()272 void ScFiltersTest::testContentXLSX()
273 {
274 ScDocShellRef xDocSh = loadDoc("universal-content.", FORMAT_XLSX);
275 xDocSh->DoHardRecalc();
276
277 ScDocument& rDoc = xDocSh->GetDocument();
278 testContentImpl(rDoc, FORMAT_XLSX);
279 xDocSh->DoClose();
280 }
281
testContentXLSXStrict()282 void ScFiltersTest::testContentXLSXStrict()
283 {
284 ScDocShellRef xDocSh = loadDoc("universal-content-strict.", FORMAT_XLSX);
285 xDocSh->DoHardRecalc();
286
287 ScDocument& rDoc = xDocSh->GetDocument();
288 testContentImpl(rDoc, FORMAT_XLSX);
289 xDocSh->DoClose();
290 }
291
testContentLotus123()292 void ScFiltersTest::testContentLotus123()
293 {
294 ScDocShellRef xDocSh = loadDoc("universal-content.", FORMAT_LOTUS123);
295 xDocSh->DoHardRecalc();
296
297 ScDocument& rDoc = xDocSh->GetDocument();
298 testContentImpl(rDoc, FORMAT_LOTUS123);
299 xDocSh->DoClose();
300 }
301
testContentofz9704()302 void ScFiltersTest::testContentofz9704()
303 {
304 OUString aFileName;
305 createFileURL("ofz9704.", "123", aFileName);
306 SvFileStream aFileStream(aFileName, StreamMode::READ);
307 TestImportWKS(aFileStream);
308 }
309
testContentDIF()310 void ScFiltersTest::testContentDIF()
311 {
312 ScDocShellRef xDocSh = loadDoc("universal-content.", FORMAT_DIF);
313
314 CPPUNIT_ASSERT_MESSAGE("Failed to load universal-content.dif", xDocSh.is());
315
316 xDocSh->DoClose();
317 }
318
testContentXLSB()319 void ScFiltersTest::testContentXLSB()
320 {
321 ScDocShellRef xDocSh = loadDoc("universal-content.", FORMAT_XLSB);
322 xDocSh->DoHardRecalc();
323
324 ScDocument& rDoc = xDocSh->GetDocument();
325 testContentImpl(rDoc, FORMAT_XLSB);
326 xDocSh->DoClose();
327 }
328
testContentXLS_XML()329 void ScFiltersTest::testContentXLS_XML()
330 {
331 ScDocShellRef xDocSh = loadDoc("universal-content.", FORMAT_XLS_XML);
332 CPPUNIT_ASSERT(xDocSh.is());
333
334 ScDocument& rDoc = xDocSh->GetDocument();
335 testContentImpl(rDoc, FORMAT_XLS_XML);
336 xDocSh->DoClose();
337 }
338
testContentGnumeric()339 void ScFiltersTest::testContentGnumeric()
340 {
341 ScDocShellRef xDocSh = loadDoc("universal-content.", FORMAT_GNUMERIC);
342 CPPUNIT_ASSERT(xDocSh.is());
343
344 ScDocument& rDoc = xDocSh->GetDocument();
345 testContentImpl(rDoc, FORMAT_GNUMERIC);
346 xDocSh->DoClose();
347 }
348
testSharedFormulaXLS()349 void ScFiltersTest::testSharedFormulaXLS()
350 {
351 ScDocShellRef xDocSh = loadDoc("shared-formula/basic.", FORMAT_XLS);
352 CPPUNIT_ASSERT(xDocSh.is());
353 ScDocument& rDoc = xDocSh->GetDocument();
354 xDocSh->DoHardRecalc();
355 // Check the results of formula cells in the shared formula range.
356 for (SCROW i = 1; i <= 18; ++i)
357 {
358 double fVal = rDoc.GetValue(ScAddress(1,i,0));
359 double fCheck = i*10.0;
360 CPPUNIT_ASSERT_EQUAL(fCheck, fVal);
361 }
362
363 ScFormulaCell* pCell = rDoc.GetFormulaCell(ScAddress(1,18,0));
364 CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pCell);
365 ScFormulaCellGroupRef xGroup = pCell->GetCellGroup();
366 CPPUNIT_ASSERT_MESSAGE("This cell should be a part of a cell group.", xGroup);
367 CPPUNIT_ASSERT_MESSAGE("Incorrect group geometry.", xGroup->mpTopCell->aPos.Row() == 1 && xGroup->mnLength == 18);
368
369 xDocSh->DoClose();
370
371 // The following file contains shared formula whose range is inaccurate.
372 // Excel can easily mess up shared formula ranges, so we need to be able
373 // to handle these wrong ranges that Excel stores.
374
375 xDocSh = loadDoc("shared-formula/gap.", FORMAT_XLS);
376 CPPUNIT_ASSERT(xDocSh.is());
377 ScDocument& rDoc2 = xDocSh->GetDocument();
378 rDoc2.CalcAll();
379
380 ASSERT_FORMULA_EQUAL(rDoc2, ScAddress(1,0,0), "A1*20", "Wrong formula.");
381 ASSERT_FORMULA_EQUAL(rDoc2, ScAddress(1,1,0), "A2*20", "Wrong formula.");
382 ASSERT_FORMULA_EQUAL(rDoc2, ScAddress(1,2,0), "A3*20", "Wrong formula.");
383
384 // There is an intentional gap at row 4.
385
386 ASSERT_FORMULA_EQUAL(rDoc2, ScAddress(1,4,0), "A5*20", "Wrong formula.");
387 ASSERT_FORMULA_EQUAL(rDoc2, ScAddress(1,5,0), "A6*20", "Wrong formula.");
388 ASSERT_FORMULA_EQUAL(rDoc2, ScAddress(1,6,0), "A7*20", "Wrong formula.");
389 ASSERT_FORMULA_EQUAL(rDoc2, ScAddress(1,7,0), "A8*20", "Wrong formula.");
390
391 // We re-group formula cells on load. Let's check that as well.
392
393 ScFormulaCell* pFC = rDoc2.GetFormulaCell(ScAddress(1,0,0));
394 CPPUNIT_ASSERT_MESSAGE("Failed to fetch formula cell.", pFC);
395 CPPUNIT_ASSERT_MESSAGE("This should be the top cell in formula group.", pFC->IsSharedTop());
396 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(3), pFC->GetSharedLength());
397
398 pFC = rDoc2.GetFormulaCell(ScAddress(1,4,0));
399 CPPUNIT_ASSERT_MESSAGE("Failed to fetch formula cell.", pFC);
400 CPPUNIT_ASSERT_MESSAGE("This should be the top cell in formula group.", pFC->IsSharedTop());
401 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(4), pFC->GetSharedLength());
402
403 xDocSh->DoClose();
404 }
405
testSharedFormulaXLSX()406 void ScFiltersTest::testSharedFormulaXLSX()
407 {
408 ScDocShellRef xDocSh = loadDoc("shared-formula/basic.", FORMAT_XLSX);
409 ScDocument& rDoc = xDocSh->GetDocument();
410 xDocSh->DoHardRecalc();
411 // Check the results of formula cells in the shared formula range.
412 for (SCROW i = 1; i <= 18; ++i)
413 {
414 double fVal = rDoc.GetValue(ScAddress(1,i,0));
415 double fCheck = i*10.0;
416 CPPUNIT_ASSERT_EQUAL(fCheck, fVal);
417 }
418
419 ScFormulaCell* pCell = rDoc.GetFormulaCell(ScAddress(1,18,0));
420 CPPUNIT_ASSERT_MESSAGE("This should be a formula cell.", pCell);
421 ScFormulaCellGroupRef xGroup = pCell->GetCellGroup();
422 CPPUNIT_ASSERT_MESSAGE("This cell should be a part of a cell group.", xGroup);
423 CPPUNIT_ASSERT_MESSAGE("Incorrect group geometry.", xGroup->mpTopCell->aPos.Row() == 1 && xGroup->mnLength == 18);
424
425 xDocSh->DoClose();
426 }
427
testSharedFormulaRefUpdateXLSX()428 void ScFiltersTest::testSharedFormulaRefUpdateXLSX()
429 {
430 ScDocShellRef xDocSh = loadDoc("shared-formula/refupdate.", FORMAT_XLSX);
431 ScDocument& rDoc = xDocSh->GetDocument();
432 sc::AutoCalcSwitch aACSwitch(rDoc, true); // turn auto calc on.
433 rDoc.DeleteRow(ScRange(0, 4, 0, rDoc.MaxCol(), 4, 0)); // delete row 5.
434
435 struct TestCase {
436 ScAddress aPos;
437 const char* pExpectedFormula;
438 const char* pErrorMsg;
439 };
440
441 TestCase aCases[4] = {
442 { ScAddress(1, 0, 0), "B29+1", "Wrong formula in B1" },
443 { ScAddress(2, 0, 0), "C29+1", "Wrong formula in C1" },
444 { ScAddress(3, 0, 0), "D29+1", "Wrong formula in D1" },
445 { ScAddress(4, 0, 0), "E29+1", "Wrong formula in E1" },
446 };
447
448 for (size_t nIdx = 0; nIdx < 4; ++nIdx)
449 {
450 TestCase& rCase = aCases[nIdx];
451 ASSERT_FORMULA_EQUAL(rDoc, rCase.aPos, rCase.pExpectedFormula, rCase.pErrorMsg);
452 }
453
454 xDocSh->DoClose();
455 }
456
testSheetNamesXLSX()457 void ScFiltersTest::testSheetNamesXLSX()
458 {
459 ScDocShellRef xDocSh = loadDoc("sheet-names.", FORMAT_XLSX);
460 ScDocument& rDoc = xDocSh->GetDocument();
461
462 std::vector<OUString> aTabNames = rDoc.GetAllTableNames();
463 CPPUNIT_ASSERT_EQUAL_MESSAGE("The document should have 5 sheets in total.", size_t(5), aTabNames.size());
464 CPPUNIT_ASSERT_EQUAL(OUString("S&P"), aTabNames[0]);
465 CPPUNIT_ASSERT_EQUAL(OUString("Sam's Club"), aTabNames[1]);
466 CPPUNIT_ASSERT_EQUAL(OUString("\"The Sheet\""), aTabNames[2]);
467 CPPUNIT_ASSERT_EQUAL(OUString("A<B"), aTabNames[3]);
468 CPPUNIT_ASSERT_EQUAL(OUString("C>D"), aTabNames[4]);
469
470 xDocSh->DoClose();
471 }
472
473 // FILESAVE: XLSX export with long sheet names (length > 31 characters)
testTdf79998()474 void ScFiltersTest::testTdf79998()
475 {
476 // check: original document has tab name > 31 characters
477 ScDocShellRef xDocSh = loadDoc("tdf79998.", FORMAT_ODS);
478 ScDocument& rDoc1 = xDocSh->GetDocument();
479 const std::vector<OUString> aTabNames1 = rDoc1.GetAllTableNames();
480 CPPUNIT_ASSERT_EQUAL(OUString("Utilities (FX Kurse, Kreditkarten etc)"), aTabNames1[1]);
481
482 // check: saved XLSX document has truncated tab name
483 xDocSh = saveAndReload( &(*xDocSh), FORMAT_XLSX);
484 ScDocument& rDoc2 = xDocSh->GetDocument();
485 const std::vector<OUString> aTabNames2 = rDoc2.GetAllTableNames();
486 CPPUNIT_ASSERT_EQUAL(OUString("Utilities (FX Kurse, Kreditkart"), aTabNames2[1]);
487
488 xDocSh->DoClose();
489 }
490
impl_testLegacyCellAnchoredRotatedShape(ScDocument & rDoc,const tools::Rectangle & aRect,const ScDrawObjData & aAnchor,long TOLERANCE=30)491 static void impl_testLegacyCellAnchoredRotatedShape( ScDocument& rDoc, const tools::Rectangle& aRect, const ScDrawObjData& aAnchor, long TOLERANCE = 30 /* 30 hmm */ )
492 {
493 ScDrawLayer* pDrawLayer = rDoc.GetDrawLayer();
494 CPPUNIT_ASSERT_MESSAGE("No drawing layer.", pDrawLayer);
495 SdrPage* pPage = pDrawLayer->GetPage(0);
496 CPPUNIT_ASSERT_MESSAGE("No page instance for the 1st sheet.", pPage);
497 CPPUNIT_ASSERT_EQUAL( static_cast<size_t>(1), pPage->GetObjCount() );
498
499 SdrObject* pObj = pPage->GetObj(0);
500 const tools::Rectangle& aSnap = pObj->GetSnapRect();
501 printf("expected height %ld actual %ld\n", aRect.GetHeight(), aSnap.GetHeight() );
502 CPPUNIT_ASSERT_EQUAL( true, testEqualsWithTolerance( aRect.GetHeight(), aSnap.GetHeight(), TOLERANCE ) );
503 printf("expected width %ld actual %ld\n", aRect.GetWidth(), aSnap.GetWidth() );
504 CPPUNIT_ASSERT_EQUAL( true, testEqualsWithTolerance( aRect.GetWidth(), aSnap.GetWidth(), TOLERANCE ) );
505 printf("expected left %ld actual %ld\n", aRect.Left(), aSnap.Left() );
506 CPPUNIT_ASSERT_EQUAL( true, testEqualsWithTolerance( aRect.Left(), aSnap.Left(), TOLERANCE ) );
507 printf("expected right %ld actual %ld\n", aRect.Top(), aSnap.Top() );
508 CPPUNIT_ASSERT_EQUAL( true, testEqualsWithTolerance( aRect.Top(), aSnap.Top(), TOLERANCE ) );
509
510 ScDrawObjData* pData = ScDrawLayer::GetObjData( pObj );
511 CPPUNIT_ASSERT_MESSAGE("expected object meta data", pData);
512 printf("expected startrow %" SAL_PRIdINT32 " actual %" SAL_PRIdINT32 "\n", aAnchor.maStart.Row(), pData->maStart.Row() );
513 CPPUNIT_ASSERT_EQUAL( aAnchor.maStart.Row(), pData->maStart.Row() );
514 printf("expected startcol %d actual %d\n", aAnchor.maStart.Col(), pData->maStart.Col() );
515 CPPUNIT_ASSERT_EQUAL( aAnchor.maStart.Col(), pData->maStart.Col() );
516 printf("expected endrow %" SAL_PRIdINT32 " actual %" SAL_PRIdINT32 "\n", aAnchor.maEnd.Row(), pData->maEnd.Row() );
517 CPPUNIT_ASSERT_EQUAL( aAnchor.maEnd.Row(), pData->maEnd.Row() );
518 printf("expected endcol %d actual %d\n", aAnchor.maEnd.Col(), pData->maEnd.Col() );
519 CPPUNIT_ASSERT_EQUAL( aAnchor.maEnd.Col(), pData->maEnd.Col() );
520 }
521
testLegacyCellAnchoredRotatedShape()522 void ScFiltersTest::testLegacyCellAnchoredRotatedShape()
523 {
524 {
525 // This example doc contains cell anchored shape that is rotated, the
526 // rotated shape is in fact clipped by the sheet boundaries (and thus
527 // is a good edge case test to see if we import it still correctly)
528 ScDocShellRef xDocSh = loadDoc("legacycellanchoredrotatedclippedshape.", FORMAT_ODS);
529
530 ScDocument& rDoc = xDocSh->GetDocument();
531 // ensure the imported legacy rotated shape is in the expected position
532 tools::Rectangle aRect( 6000, -2000, 8000, 4000 );
533 // ensure the imported ( and converted ) anchor ( note we internally now store the anchor in
534 // terms of the rotated shape ) is more or less contains the correct info
535 ScDrawObjData aAnchor;
536 aAnchor.maStart.SetRow( 0 );
537 aAnchor.maStart.SetCol( 5 );
538 aAnchor.maEnd.SetRow( 3 );
539 aAnchor.maEnd.SetCol( 7 );
540 impl_testLegacyCellAnchoredRotatedShape( rDoc, aRect, aAnchor );
541 // test save and reload
542 // for some reason having this test in subsequent_export-test.cxx causes
543 // a core dump in editeng ( so moved to here )
544 xDocSh = saveAndReload( &(*xDocSh), FORMAT_ODS);
545 ScDocument& rDoc2 = xDocSh->GetDocument();
546 impl_testLegacyCellAnchoredRotatedShape( rDoc2, aRect, aAnchor );
547
548 xDocSh->DoClose();
549 }
550 {
551 // This example doc contains cell anchored shape that is rotated, the
552 // rotated shape is in fact clipped by the sheet boundaries, additionally
553 // the shape is completely hidden because the rows the shape occupies
554 // are hidden
555 ScDocShellRef xDocSh = loadDoc("legacycellanchoredrotatedhiddenshape.", FORMAT_ODS, true);
556 ScDocument& rDoc = xDocSh->GetDocument();
557 // ensure the imported legacy rotated shape is in the expected position
558 // when a shape is fully hidden reloading seems to result is in some errors, usually
559 // ( same but different error happens pre-patch ) - we should do better here, I regard it
560 // as a pre-existing bug though (#FIXME)
561 //Rectangle aRect( 6000, -2000, 8000, 4000 ); // proper dimensions
562 tools::Rectangle aRect( 6000, -2000, 7430, 4000 );
563 // ensure the imported (and converted) anchor (note we internally now store the anchor in
564 // terms of the rotated shape) is more or less contains the correct info
565 ScDrawObjData aAnchor;
566 aAnchor.maStart.SetRow( 0 );
567 aAnchor.maStart.SetCol( 5 );
568 aAnchor.maEnd.SetRow( 3 );
569 aAnchor.maEnd.SetCol( 7 );
570 rDoc.ShowRows(0, 9, 0, true); // show relevant rows
571 rDoc.SetDrawPageSize(0); // trigger recalcpos
572
573 // apply hefty (1 mm) tolerance here, as some opensuse tinderbox
574 // failing
575 impl_testLegacyCellAnchoredRotatedShape( rDoc, aRect, aAnchor, 100 );
576
577 xDocSh->DoClose();
578 }
579 {
580 // This example doc contains cell anchored shape that is rotated
581 ScDocShellRef xDocSh = loadDoc("legacycellanchoredrotatedshape.", FORMAT_ODS);
582
583 ScDocument& rDoc = xDocSh->GetDocument();
584 // ensure the imported legacy rotated shape is in the expected position
585 tools::Rectangle aRect( 6000, 3000, 8000, 9000 );
586 // ensure the imported (and converted) anchor (note we internally now store the anchor in
587 // terms of the rotated shape) more or less contains the correct info
588
589 ScDrawObjData aAnchor;
590 aAnchor.maStart.SetRow( 3 );
591 aAnchor.maStart.SetCol( 6 );
592 aAnchor.maEnd.SetRow( 9 );
593 aAnchor.maEnd.SetCol( 7 );
594 // test import
595 impl_testLegacyCellAnchoredRotatedShape( rDoc, aRect, aAnchor );
596 // test save and reload
597 xDocSh = saveAndReload( &(*xDocSh), FORMAT_ODS);
598 ScDocument& rDoc2 = xDocSh->GetDocument();
599 impl_testLegacyCellAnchoredRotatedShape( rDoc2, aRect, aAnchor );
600
601 xDocSh->DoClose();
602 }
603 }
604
testEnhancedProtectionImpl(const ScDocument & rDoc)605 static void testEnhancedProtectionImpl( const ScDocument& rDoc )
606 {
607 const ScTableProtection* pProt = rDoc.GetTabProtection(0);
608
609 CPPUNIT_ASSERT( pProt);
610
611 CPPUNIT_ASSERT( !pProt->isBlockEditable( ScRange( 0, 0, 0, 0, 0, 0))); // locked
612 CPPUNIT_ASSERT( pProt->isBlockEditable( ScRange( 0, 1, 0, 0, 1, 0))); // editable without password
613 CPPUNIT_ASSERT( pProt->isBlockEditable( ScRange( 0, 2, 0, 0, 2, 0))); // editable without password
614 CPPUNIT_ASSERT( !pProt->isBlockEditable( ScRange( 0, 3, 0, 0, 3, 0))); // editable with password "foo"
615 CPPUNIT_ASSERT( !pProt->isBlockEditable( ScRange( 0, 4, 0, 0, 4, 0))); // editable with descriptor
616 CPPUNIT_ASSERT( !pProt->isBlockEditable( ScRange( 0, 5, 0, 0, 5, 0))); // editable with descriptor and password "foo"
617 CPPUNIT_ASSERT( pProt->isBlockEditable( ScRange( 0, 1, 0, 0, 2, 0))); // union of two different editables
618 CPPUNIT_ASSERT( !pProt->isBlockEditable( ScRange( 0, 0, 0, 0, 1, 0))); // union of locked and editable
619 CPPUNIT_ASSERT( !pProt->isBlockEditable( ScRange( 0, 2, 0, 0, 3, 0))); // union of editable and password editable
620 }
621
testEnhancedProtectionXLS()622 void ScFiltersTest::testEnhancedProtectionXLS()
623 {
624 ScDocShellRef xDocSh = loadDoc("enhanced-protection.", FORMAT_XLS);
625 CPPUNIT_ASSERT(xDocSh.is());
626 ScDocument& rDoc = xDocSh->GetDocument();
627
628 testEnhancedProtectionImpl( rDoc);
629
630 xDocSh->DoClose();
631 }
632
testEnhancedProtectionXLSX()633 void ScFiltersTest::testEnhancedProtectionXLSX()
634 {
635 ScDocShellRef xDocSh = loadDoc("enhanced-protection.", FORMAT_XLSX);
636 CPPUNIT_ASSERT(xDocSh.is());
637 ScDocument& rDoc = xDocSh->GetDocument();
638
639 testEnhancedProtectionImpl( rDoc);
640
641 xDocSh->DoClose();
642 }
643
testSortWithSharedFormulasODS()644 void ScFiltersTest::testSortWithSharedFormulasODS()
645 {
646 ScDocShellRef xDocSh = loadDoc("shared-formula/sort-crash.", FORMAT_ODS, true);
647 CPPUNIT_ASSERT(xDocSh.is());
648 ScDocument& rDoc = xDocSh->GetDocument();
649
650 // E2:E10 should be shared.
651 const ScFormulaCell* pFC = rDoc.GetFormulaCell(ScAddress(4,1,0));
652 CPPUNIT_ASSERT(pFC);
653 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
654 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), pFC->GetSharedLength());
655
656 // E12:E17 should be shared.
657 pFC = rDoc.GetFormulaCell(ScAddress(4,11,0));
658 CPPUNIT_ASSERT(pFC);
659 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(11), pFC->GetSharedTopRow());
660 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(6), pFC->GetSharedLength());
661
662 // Set A1:E17 as an anonymous database range to sheet, or else Calc would
663 // refuse to sort the range.
664 std::unique_ptr<ScDBData> pDBData(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 4, 16, true, true));
665 rDoc.SetAnonymousDBData(0, std::move(pDBData));
666
667 // Sort ascending by Column E.
668
669 ScSortParam aSortData;
670 aSortData.nCol1 = 0;
671 aSortData.nCol2 = 4;
672 aSortData.nRow1 = 0;
673 aSortData.nRow2 = 16;
674 aSortData.bHasHeader = true;
675 aSortData.maKeyState[0].bDoSort = true;
676 aSortData.maKeyState[0].nField = 4;
677 aSortData.maKeyState[0].bAscending = true;
678
679 // Do the sorting. This should not crash.
680 ScDBDocFunc aFunc(*xDocSh);
681 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
682 CPPUNIT_ASSERT(bSorted);
683
684 // After the sort, E2:E16 should be shared.
685 pFC = rDoc.GetFormulaCell(ScAddress(4,1,0));
686 CPPUNIT_ASSERT(pFC);
687 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), pFC->GetSharedTopRow());
688 CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(15), pFC->GetSharedLength());
689
690 xDocSh->DoClose();
691 }
692
693 // https://bugs.freedesktop.org/attachment.cgi?id=100089 from fdo#77018
694 // mentioned also in fdo#79441
695 // Document contains cached external references.
testSortWithSheetExternalReferencesODS()696 void ScFiltersTest::testSortWithSheetExternalReferencesODS()
697 {
698 ScDocShellRef xDocSh = loadDoc("sort-with-sheet-external-references.", FORMAT_ODS, true);
699 CPPUNIT_ASSERT(xDocSh.is());
700 ScDocument& rDoc = xDocSh->GetDocument();
701 sc::AutoCalcSwitch aACSwitch(rDoc, true); // turn auto calc on.
702 rDoc.CalcAll();
703
704 // We reset the SortRefUpdate value back to the original in tearDown().
705 ScInputOptions aInputOption = SC_MOD()->GetInputOptions();
706
707 // The complete relative test only works with UpdateReferenceOnSort==true,
708 // but the internal and external sheet references have to work in both
709 // modes.
710
711 aInputOption.SetSortRefUpdate(true);
712 SC_MOD()->SetInputOptions(aInputOption);
713
714 // Sort A15:D20 with relative row references. UpdateReferenceOnSort==true
715 // With in-sheet relative references.
716 testSortWithSheetExternalReferencesODS_Impl( xDocSh, 14, 19, true);
717
718 // Undo sort with relative references to perform same sort.
719 rDoc.GetUndoManager()->Undo();
720 rDoc.CalcAll();
721
722 aInputOption.SetSortRefUpdate(false);
723 SC_MOD()->SetInputOptions(aInputOption);
724
725 // Sort A15:D20 with relative row references. UpdateReferenceOnSort==false
726 // Without in-sheet relative references.
727 testSortWithSheetExternalReferencesODS_Impl( xDocSh, 14, 19, false);
728
729 // Undo sort with relative references to perform new sort.
730 rDoc.GetUndoManager()->Undo();
731 rDoc.CalcAll();
732
733 // Sort with absolute references has to work in both UpdateReferenceOnSort
734 // modes.
735
736 aInputOption.SetSortRefUpdate(true);
737 SC_MOD()->SetInputOptions(aInputOption);
738
739 // Sort A23:D28 with absolute row references. UpdateReferenceOnSort==true
740 // With in-sheet relative references.
741 testSortWithSheetExternalReferencesODS_Impl( xDocSh, 22, 27, true);
742
743 // Undo sort with absolute references to perform same sort.
744 rDoc.GetUndoManager()->Undo();
745 rDoc.CalcAll();
746
747 aInputOption.SetSortRefUpdate(false);
748 SC_MOD()->SetInputOptions(aInputOption);
749
750 // Sort A23:D28 with absolute row references. UpdateReferenceOnSort==false
751 // With in-sheet relative references.
752 testSortWithSheetExternalReferencesODS_Impl( xDocSh, 22, 27, true);
753
754 xDocSh->DoClose();
755 }
756
testSortWithSheetExternalReferencesODS_Impl(ScDocShellRef const & xDocSh,SCROW nRow1,SCROW nRow2,bool bCheckRelativeInSheet)757 void ScFiltersTest::testSortWithSheetExternalReferencesODS_Impl( ScDocShellRef const & xDocSh, SCROW nRow1, SCROW nRow2,
758 bool bCheckRelativeInSheet )
759 {
760 ScDocument& rDoc = xDocSh->GetDocument();
761
762 // Check the original data is there.
763 for (SCROW nRow=nRow1+1; nRow <= nRow2; ++nRow)
764 {
765 double const aCheck[] = { 1, 2, 3, 4, 5 };
766 CPPUNIT_ASSERT_EQUAL( aCheck[nRow-nRow1-1], rDoc.GetValue( ScAddress(0,nRow,0)));
767 }
768 for (SCROW nRow=nRow1+1; nRow <= nRow2; ++nRow)
769 {
770 for (SCCOL nCol=1; nCol <= 3; ++nCol)
771 {
772 double const aCheck[] = { 1, 12, 123, 1234, 12345 };
773 CPPUNIT_ASSERT_EQUAL( aCheck[nRow-nRow1-1], rDoc.GetValue( ScAddress(nCol,nRow,0)));
774 }
775 }
776
777 // Set as an anonymous database range to sort.
778 std::unique_ptr<ScDBData> pDBData(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, nRow1, 3, nRow2, true, true));
779 rDoc.SetAnonymousDBData(0, std::move(pDBData));
780
781 // Sort descending by Column A.
782 ScSortParam aSortData;
783 aSortData.nCol1 = 0;
784 aSortData.nCol2 = 3;
785 aSortData.nRow1 = nRow1;
786 aSortData.nRow2 = nRow2;
787 aSortData.bHasHeader = true;
788 aSortData.maKeyState[0].bDoSort = true;
789 aSortData.maKeyState[0].nField = 0;
790 aSortData.maKeyState[0].bAscending = false;
791
792 // Do the sorting.
793 ScDBDocFunc aFunc(*xDocSh);
794 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
795 CPPUNIT_ASSERT(bSorted);
796 rDoc.CalcAll();
797
798 // Check the sort and that all sheet references and external references are
799 // adjusted to point to the original location.
800 for (SCROW nRow=nRow1+1; nRow <= nRow2; ++nRow)
801 {
802 double const aCheck[] = { 5, 4, 3, 2, 1 };
803 CPPUNIT_ASSERT_EQUAL( aCheck[nRow-nRow1-1], rDoc.GetValue( ScAddress(0,nRow,0)));
804 }
805 // The last column (D) are in-sheet relative references.
806 SCCOL nEndCol = (bCheckRelativeInSheet ? 3 : 2);
807 for (SCROW nRow=nRow1+1; nRow <= nRow2; ++nRow)
808 {
809 for (SCCOL nCol=1; nCol <= nEndCol; ++nCol)
810 {
811 double const aCheck[] = { 12345, 1234, 123, 12, 1 };
812 CPPUNIT_ASSERT_EQUAL( aCheck[nRow-nRow1-1], rDoc.GetValue( ScAddress(nCol,nRow,0)));
813 }
814 }
815 }
816
testSortWithFormattingXLS()817 void ScFiltersTest::testSortWithFormattingXLS()
818 {
819 ScDocShellRef xDocSh = loadDoc("tdf129127.", FORMAT_XLS, true);
820 CPPUNIT_ASSERT(xDocSh.is());
821 ScDocument& rDoc = xDocSh->GetDocument();
822
823 // Set as an anonymous database range to sort.
824 std::unique_ptr<ScDBData> pDBData(
825 new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 4, 9, false, false));
826 rDoc.SetAnonymousDBData(0, std::move(pDBData));
827
828 // Sort ascending by Row 1
829 ScSortParam aSortData;
830 aSortData.nCol1 = 0;
831 aSortData.nCol2 = 4;
832 aSortData.nRow1 = 0;
833 aSortData.nRow2 = 9;
834 aSortData.bHasHeader = false;
835 aSortData.bByRow = false;
836 aSortData.maKeyState[0].bDoSort = true;
837 aSortData.maKeyState[0].nField = 0;
838 aSortData.maKeyState[0].bAscending = true;
839
840 // Do the sorting.
841 ScDBDocFunc aFunc(*xDocSh);
842 // Without the fix, sort would crash.
843 bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
844 CPPUNIT_ASSERT(bSorted);
845 xDocSh->DoClose();
846 }
847
ScFiltersTest()848 ScFiltersTest::ScFiltersTest()
849 : ScBootstrapFixture( "sc/qa/unit/data" )
850 , mbUpdateReferenceOnSort(false)
851 {
852 }
853
setUp()854 void ScFiltersTest::setUp()
855 {
856 test::BootstrapFixture::setUp();
857
858 // This is a bit of a fudge, we do this to ensure that ScGlobals::ensure,
859 // which is a private symbol to us, gets called
860 m_xCalcComponent =
861 getMultiServiceFactory()->createInstance("com.sun.star.comp.Calc.SpreadsheetDocument");
862 CPPUNIT_ASSERT_MESSAGE("no calc component!", m_xCalcComponent.is());
863
864 // one test sets this configuration option; make sure we remember the
865 // original value
866 ScInputOptions aInputOption = SC_MOD()->GetInputOptions();
867 mbUpdateReferenceOnSort = aInputOption.GetSortRefUpdate();
868 }
869
tearDown()870 void ScFiltersTest::tearDown()
871 {
872 uno::Reference< lang::XComponent >( m_xCalcComponent, UNO_QUERY_THROW )->dispose();
873 test::BootstrapFixture::tearDown();
874
875 // one test sets this configuration option; make sure we return it back
876 ScInputOptions aInputOption = SC_MOD()->GetInputOptions();
877 if (mbUpdateReferenceOnSort != aInputOption.GetSortRefUpdate())
878 {
879 aInputOption.SetSortRefUpdate(mbUpdateReferenceOnSort);
880 SC_MOD()->SetInputOptions(aInputOption);
881 }
882 }
883
884 CPPUNIT_TEST_SUITE_REGISTRATION(ScFiltersTest);
885
886 CPPUNIT_PLUGIN_IMPLEMENT();
887
888 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
889