sc/qa/unit/subsequent_export_test2.cxx

   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 "helper/qahelper.hxx"
  11
  12 #include <docsh.hxx>
  13 #include <global.hxx>
  14 #include <dpcache.hxx>
  15 #include <dpobject.hxx>
  16 #include <clipparam.hxx>
  17
  18 #include <editeng/editobj.hxx>
  19 #include <editeng/flditem.hxx>
  20 #include <comphelper/scopeguard.hxx>
  21 #include <comphelper/propertyvalue.hxx>
  22 #include <unotools/syslocaleoptions.hxx>
  23 #include <formula/grammar.hxx>
  24 #include <svl/numformat.hxx>
  25 #include <svl/zformat.hxx>
  26
  27 #include <com/sun/star/sheet/XHeaderFooterContent.hpp>
  28
  29 using namespace ::com::sun::star;
  30 using namespace ::com::sun::star::uno;
  31
  32 class ScExportTest2 : public ScModelTestBase
  33 {
  34 public:
  35     ScExportTest2();
  36 };
  37
  38 ScExportTest2::ScExportTest2()
  39     : ScModelTestBase("sc/qa/unit/data")
  40 {
  41 }
  42
  43 CPPUNIT_TEST_FIXTURE(ScExportTest2, testGroupShape)
  44 {
  45     createScDoc("xlsx/groupShape.xlsx");
  46     save("Calc Office Open XML");
  47
  48     xmlDocUniquePtr pDoc = parseExport("xl/drawings/drawing1.xml");
  49     CPPUNIT_ASSERT(pDoc);
  50     assertXPath(pDoc, "/xdr:wsDr/xdr:twoCellAnchor/xdr:grpSp/xdr:grpSpPr"_ostr);
  51 }
  52
  53 CPPUNIT_TEST_FIXTURE(ScExportTest2, testMatrixMultiplicationXLSX)
  54 {
  55     createScDoc("xlsx/matrix-multiplication.xlsx");
  56
  57     save("Calc Office Open XML");
  58
  59     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
  60     CPPUNIT_ASSERT(pDoc);
  61
  62     OUString CellFormulaRange
  63         = getXPath(pDoc, "/x:worksheet/x:sheetData/x:row[4]/x:c/x:f"_ostr, "ref"_ostr);
  64
  65     // make sure that the CellFormulaRange is G5:G6.
  66     CPPUNIT_ASSERT_EQUAL(OUString("G5:G6"), CellFormulaRange);
  67
  68     OUString CellFormulaType
  69         = getXPath(pDoc, "/x:worksheet/x:sheetData/x:row[4]/x:c/x:f"_ostr, "t"_ostr);
  70
  71     // make sure that the CellFormulaType is array.
  72     CPPUNIT_ASSERT_EQUAL(OUString("array"), CellFormulaType);
  73 }
  74
  75 CPPUNIT_TEST_FIXTURE(ScExportTest2, testRefStringXLSX)
  76 {
  77     createScDoc("xlsx/ref_string.xlsx");
  78
  79     //make sure ref syntax gets saved for MSO-produced docs
  80     saveAndReload("Calc Office Open XML");
  81
  82     ScDocument* pDoc = getScDoc();
  83     ScCalcConfig aCalcConfig = pDoc->GetCalcConfig();
  84     CPPUNIT_ASSERT_EQUAL(formula::FormulaGrammar::CONV_XL_A1, aCalcConfig.meStringRefAddressSyntax);
  85 }
  86
  87 CPPUNIT_TEST_FIXTURE(ScExportTest2, testRefStringConfigXLSX)
  88 {
  89     // this doc is configured with CalcA1 ref syntax
  90     createScDoc("xlsx/empty.xlsx");
  91
  92     saveAndReload("Calc Office Open XML");
  93
  94     ScDocument* pDoc = getScDoc();
  95     ScCalcConfig aConfig = pDoc->GetCalcConfig();
  96     CPPUNIT_ASSERT_EQUAL_MESSAGE("String ref syntax doesn't match",
  97                                  formula::FormulaGrammar::CONV_OOO,
  98                                  aConfig.meStringRefAddressSyntax);
  99
 100     // this doc has no entry for ref syntax
 101     createScDoc("xlsx/empty-noconf.xlsx");
 102
 103     pDoc = getScDoc();
 104     aConfig = pDoc->GetCalcConfig();
 105     // therefore after import, ref syntax should be set to CalcA1 | ExcelA1
 106     CPPUNIT_ASSERT_EQUAL_MESSAGE("String ref syntax doesn't match",
 107                                  formula::FormulaGrammar::CONV_A1_XL_A1,
 108                                  aConfig.meStringRefAddressSyntax);
 109
 110     //set ref syntax to something else than ExcelA1 (native to xlsx format) ...
 111     aConfig.meStringRefAddressSyntax = formula::FormulaGrammar::CONV_XL_R1C1;
 112     pDoc->SetCalcConfig(aConfig);
 113
 114     saveAndReload("Calc Office Open XML");
 115
 116     // ... and make sure it got saved
 117     pDoc = getScDoc();
 118     aConfig = pDoc->GetCalcConfig();
 119     CPPUNIT_ASSERT_EQUAL_MESSAGE("String ref syntax doesn't match",
 120                                  formula::FormulaGrammar::CONV_XL_R1C1,
 121                                  aConfig.meStringRefAddressSyntax);
 122 }
 123
 124 CPPUNIT_TEST_FIXTURE(ScExportTest2, testRefStringUnspecified)
 125 {
 126     createScDoc();
 127
 128     ScDocument* pDoc = getScDoc();
 129     ScCalcConfig aConfig = pDoc->GetCalcConfig();
 130     CPPUNIT_ASSERT_EQUAL_MESSAGE("Default string ref syntax value doesn't match",
 131                                  formula::FormulaGrammar::CONV_UNSPECIFIED,
 132                                  aConfig.meStringRefAddressSyntax);
 133
 134     // change formula syntax (i.e. not string ref syntax) to ExcelA1
 135     pDoc->SetGrammar(formula::FormulaGrammar::GRAM_NATIVE_XL_A1);
 136
 137     saveAndReload("calc8");
 138
 139     // with string ref syntax at its default value, we should've saved ExcelA1
 140     pDoc = getScDoc();
 141     aConfig = pDoc->GetCalcConfig();
 142     CPPUNIT_ASSERT_EQUAL_MESSAGE("String ref syntax doesn't match",
 143                                  formula::FormulaGrammar::CONV_XL_A1,
 144                                  aConfig.meStringRefAddressSyntax);
 145 }
 146
 147 CPPUNIT_TEST_FIXTURE(ScExportTest2, testHeaderImageODS)
 148 {
 149     // Graphic as header background was lost on export.
 150     createScDoc("ods/header-image.ods");
 151     saveAndReload("calc8");
 152     uno::Reference<style::XStyleFamiliesSupplier> xStyleFamiliesSupplier(mxComponent,
 153                                                                          uno::UNO_QUERY);
 154     uno::Reference<container::XNameAccess> xStyleFamilies
 155         = xStyleFamiliesSupplier->getStyleFamilies();
 156     uno::Reference<container::XNameAccess> xPageStyles(xStyleFamilies->getByName("PageStyles"),
 157                                                        uno::UNO_QUERY);
 158     uno::Reference<beans::XPropertySet> xStyle(xPageStyles->getByName("Default"), uno::UNO_QUERY);
 159
 160     uno::Reference<graphic::XGraphic> xGraphic;
 161     xStyle->getPropertyValue("HeaderBackGraphic") >>= xGraphic;
 162     CPPUNIT_ASSERT(xGraphic.is());
 163 }
 164
 165 CPPUNIT_TEST_FIXTURE(ScExportTest2, testHeaderFooterContentODS)
 166 {
 167     createScDoc("ods/header-footer-content.ods");
 168     saveAndReload("calc8");
 169     uno::Reference<style::XStyleFamiliesSupplier> xStyleFamiliesSupplier(mxComponent,
 170                                                                          uno::UNO_QUERY);
 171     uno::Reference<container::XNameAccess> xStyleFamilies
 172         = xStyleFamiliesSupplier->getStyleFamilies();
 173     uno::Reference<container::XNameAccess> xPageStyles(xStyleFamilies->getByName("PageStyles"),
 174                                                        uno::UNO_QUERY);
 175     uno::Reference<beans::XPropertySet> xStyle(xPageStyles->getByName("Default"), uno::UNO_QUERY);
 176
 177     uno::Reference<css::sheet::XHeaderFooterContent> xContent;
 178     xStyle->getPropertyValue("RightPageHeaderContent") >>= xContent;
 179     CPPUNIT_ASSERT(xContent.is());
 180     CPPUNIT_ASSERT_EQUAL(OUString("header"), xContent->getCenterText()->getString());
 181
 182     xStyle->getPropertyValue("FirstPageHeaderContent") >>= xContent;
 183     CPPUNIT_ASSERT(xContent.is());
 184     CPPUNIT_ASSERT_EQUAL(OUString("first page header"), xContent->getCenterText()->getString());
 185
 186     xStyle->getPropertyValue("RightPageFooterContent") >>= xContent;
 187     CPPUNIT_ASSERT(xContent.is());
 188     CPPUNIT_ASSERT_EQUAL(OUString("footer"), xContent->getCenterText()->getString());
 189
 190     xStyle->getPropertyValue("FirstPageFooterContent") >>= xContent;
 191     // First page footer content used to be lost upon export.
 192     CPPUNIT_ASSERT(xContent.is());
 193     CPPUNIT_ASSERT_EQUAL(OUString("first page footer"), xContent->getCenterText()->getString());
 194 }
 195
 196 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTextDirectionXLSX)
 197 {
 198     createScDoc("xlsx/writingMode.xlsx");
 199
 200     save("Calc Office Open XML");
 201     xmlDocUniquePtr pDoc = parseExport("xl/styles.xml");
 202     CPPUNIT_ASSERT(pDoc);
 203
 204     assertXPath(pDoc, "/x:styleSheet/x:cellXfs/x:xf[2]/x:alignment"_ostr, "readingOrder"_ostr,
 205                 "1"); //LTR
 206     assertXPath(pDoc, "/x:styleSheet/x:cellXfs/x:xf[3]/x:alignment"_ostr, "readingOrder"_ostr,
 207                 "2"); //RTL
 208 }
 209
 210 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf121260)
 211 {
 212     createScDoc("ods/tdf121260.ods");
 213
 214     ScDocument* pDoc = getScDoc();
 215     // change formula syntax (i.e. not string ref syntax) to ExcelA1
 216     FormulaGrammarSwitch aFGSwitch(pDoc, formula::FormulaGrammar::GRAM_NATIVE_XL_A1);
 217
 218     save("Calc Office Open XML");
 219     xmlDocUniquePtr pChart1 = parseExport("xl/charts/chart1.xml");
 220     CPPUNIT_ASSERT(pChart1);
 221
 222     // Without the fix in place, this test would have failed with
 223     // - Expected: Sheet1!$A$1:$A$2
 224     // - Actual  : sheet1 $A$1:$A$2
 225     assertXPathContent(
 226         pChart1, "/c:chartSpace/c:chart/c:plotArea/c:barChart/c:ser[1]/c:val/c:numRef/c:f"_ostr,
 227         "Sheet1!$A$1:$A$2");
 228     assertXPathContent(
 229         pChart1, "/c:chartSpace/c:chart/c:plotArea/c:barChart/c:ser[2]/c:val/c:numRef/c:f"_ostr,
 230         "Sheet1!$B$1:$B$2");
 231 }
 232
 233 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf120168)
 234 {
 235     createScDoc("xlsx/tdf120168.xlsx");
 236
 237     save("Calc Office Open XML");
 238     xmlDocUniquePtr pDoc = parseExport("xl/styles.xml");
 239     CPPUNIT_ASSERT(pDoc);
 240
 241     // Without the fix in place, this test would have failed with
 242     // - Expected: left
 243     // - Actual  : general
 244     assertXPath(pDoc, "/x:styleSheet/x:cellXfs/x:xf[2]/x:alignment"_ostr, "horizontal"_ostr,
 245                 "left");
 246     assertXPath(pDoc, "/x:styleSheet/x:cellXfs/x:xf[3]/x:alignment"_ostr, "horizontal"_ostr,
 247                 "right");
 248 }
 249
 250 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf117266)
 251 {
 252     createScDoc("xlsm/tdf117266_macroButton.xlsm");
 253
 254     save("Calc MS Excel 2007 VBA XML");
 255     xmlDocUniquePtr pVmlDrawing = parseExport("xl/drawings/vmlDrawing1.vml");
 256
 257     OUString sName = getXPath(pVmlDrawing, "/xml/v:shape"_ostr, "id"_ostr);
 258     CPPUNIT_ASSERT_EQUAL(OUString("Button 1001"), sName);
 259
 260     OUString sSpid = getXPath(pVmlDrawing, "/xml/v:shape"_ostr, "spid"_ostr);
 261     CPPUNIT_ASSERT(sSpid.startsWith("_x0000_s"));
 262
 263     assertXPathContent(pVmlDrawing, "/xml/v:shape/v:textbox/div/font"_ostr, "Button 1 \"y\" z");
 264     // Why the xx:, I have no idea..., but it certainly doesn't work with just x:.
 265     assertXPathContent(pVmlDrawing, "/xml/v:shape//xx:FmlaMacro"_ostr, "Module1.Button1_Click");
 266 }
 267
 268 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf66668)
 269 {
 270     // Would hang on exporting without the fix in place
 271     createScDoc("xlsx/tdf66668.xlsx");
 272
 273     save("Calc Office Open XML");
 274     xmlDocUniquePtr pDoc = parseExport("xl/styles.xml");
 275     CPPUNIT_ASSERT(pDoc);
 276 }
 277
 278 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf130108)
 279 {
 280     createScDoc("ods/tdf130108.ods");
 281
 282     save("Calc Office Open XML");
 283     xmlDocUniquePtr pDoc = parseExport("xl/styles.xml");
 284     CPPUNIT_ASSERT(pDoc);
 285
 286     assertXPath(pDoc, "/x:styleSheet/x:dxfs/x:dxf/x:font/x:b"_ostr, "val"_ostr, "1");
 287     assertXPath(pDoc, "/x:styleSheet/x:dxfs/x:dxf/x:font/x:i"_ostr, "val"_ostr, "0");
 288     assertXPath(pDoc, "/x:styleSheet/x:dxfs/x:dxf/x:font/x:color"_ostr, "rgb"_ostr, "FFFFFFFF");
 289     assertXPath(pDoc, "/x:styleSheet/x:dxfs/x:dxf/x:font/x:sz"_ostr, "val"_ostr, "10");
 290     assertXPath(pDoc, "/x:styleSheet/x:dxfs/x:dxf/x:fill/x:patternFill/x:bgColor"_ostr, "rgb"_ostr,
 291                 "FFCC0000");
 292 }
 293
 294 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf76949)
 295 {
 296     createScDoc("ods/tdf76949.ods");
 297
 298     save("Calc Office Open XML");
 299     xmlDocUniquePtr pSheet = parseExport("xl/worksheets/sheet1.xml");
 300     CPPUNIT_ASSERT(pSheet);
 301
 302     assertXPathContent(pSheet, "/x:worksheet/x:sheetData/x:row/x:c/x:f"_ostr,
 303                        "_xlfn.CHISQ.DIST(1,1,1)");
 304 }
 305
 306 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf107586)
 307 {
 308     createScDoc("xlsx/tdf107586.xlsx");
 309
 310     save("Calc Office Open XML");
 311     xmlDocUniquePtr pSheet = parseExport("xl/worksheets/sheet1.xml");
 312     CPPUNIT_ASSERT(pSheet);
 313
 314     // Without the fix in place, this test would have failed with
 315     // XPath '/x:worksheet/x:sheetPr/x:tabColor' number of nodes is incorrect
 316     assertXPath(pSheet, "/x:worksheet/x:sheetPr/x:tabColor"_ostr, "rgb"_ostr, "FF9BBB59");
 317 }
 318
 319 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf55417)
 320 {
 321     createScDoc("xlsx/tdf55417.xlsx");
 322
 323     save("Calc Office Open XML");
 324     xmlDocUniquePtr pDoc = parseExport("xl/styles.xml");
 325     CPPUNIT_ASSERT(pDoc);
 326     assertXPath(pDoc, "/x:styleSheet/x:cellXfs/x:xf[1]/x:alignment"_ostr, 1);
 327     assertXPath(pDoc, "/x:styleSheet/x:cellXfs/x:xf[2]/x:alignment"_ostr, 1);
 328 }
 329
 330 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf129985)
 331 {
 332     createScDoc("xlsx/tdf129985.xlsx");
 333
 334     save("Calc Office Open XML");
 335     xmlDocUniquePtr pDoc = parseExport("xl/styles.xml");
 336     CPPUNIT_ASSERT(pDoc);
 337
 338     assertXPath(pDoc, "/x:styleSheet/x:numFmts/x:numFmt[2]"_ostr, "formatCode"_ostr, "m/d/yyyy");
 339 }
 340
 341 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf73063)
 342 {
 343     createScDoc("xlsx/tdf73063.xlsx");
 344
 345     save("Calc Office Open XML");
 346     xmlDocUniquePtr pDoc = parseExport("xl/styles.xml");
 347     CPPUNIT_ASSERT(pDoc);
 348
 349     assertXPath(pDoc, "/x:styleSheet/x:numFmts/x:numFmt[2]"_ostr, "formatCode"_ostr,
 350                 "[$-1C1A]dddd\", \"d\". \"mmmm\\ yyyy;@");
 351 }
 352
 353 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf95640_ods_to_xlsx)
 354 {
 355     // Roundtripping sort options with user defined list to XLSX
 356     createScDoc("ods/tdf95640.ods");
 357
 358     save("Calc Office Open XML");
 359     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
 360
 361     assertXPath(pDoc, "//x:worksheet/x:autoFilter"_ostr, "ref"_ostr, "A1:B4");
 362
 363     assertXPath(pDoc, "//x:worksheet/x:autoFilter/x:sortState/x:sortCondition"_ostr, "ref"_ostr,
 364                 "A2:A4");
 365
 366     assertXPath(pDoc, "//x:worksheet/x:autoFilter/x:sortState/x:sortCondition"_ostr,
 367                 "customList"_ostr, "Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec");
 368 }
 369
 370 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf95640_ods_to_xlsx_with_standard_list)
 371 {
 372     // Roundtripping sort options with user defined list to XLSX
 373     createScDoc("ods/tdf95640_standard_list.ods");
 374     save("Calc Office Open XML");
 375     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
 376
 377     assertXPath(pDoc, "//x:worksheet/x:autoFilter"_ostr, "ref"_ostr, "A1:B4");
 378
 379     assertXPath(pDoc, "//x:worksheet/x:autoFilter/x:sortState/x:sortCondition"_ostr, "ref"_ostr,
 380                 "A2:A4");
 381
 382     assertXPath(pDoc, "//x:worksheet/x:autoFilter/x:sortState/x:sortCondition"_ostr,
 383                 "customList"_ostr, "Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday");
 384 }
 385
 386 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf95640_xlsx_to_xlsx)
 387 {
 388     // XLSX Roundtripping sort options with custom sort list - note
 389     // that compared to ODS source documents above, here we _actually_
 390     // can use custom lists (beyond the global user defines), like
 391     // low, medium, high
 392     createScDoc("xlsx/tdf95640.xlsx");
 393
 394     save("Calc Office Open XML");
 395     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
 396
 397     assertXPath(pDoc, "//x:worksheet/x:autoFilter"_ostr, "ref"_ostr, "A1:B4");
 398
 399     assertXPath(pDoc, "//x:worksheet/x:autoFilter/x:sortState/x:sortCondition"_ostr, "ref"_ostr,
 400                 "A2:A4");
 401
 402     assertXPath(pDoc, "//x:worksheet/x:autoFilter/x:sortState/x:sortCondition"_ostr,
 403                 "customList"_ostr, "Low,Medium,High");
 404 }
 405
 406 CPPUNIT_TEST_FIXTURE(ScExportTest2, testDateAutofilterXLSX)
 407 {
 408     // XLSX Roundtripping autofilter with date list
 409     createScDoc("xlsx/dateAutofilter.xlsx");
 410
 411     save("Calc Office Open XML");
 412     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
 413     CPPUNIT_ASSERT(pDoc);
 414
 415     assertXPath(pDoc, "//x:autoFilter"_ostr, "ref"_ostr, "A1:B4");
 416     assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]"_ostr, "day"_ostr,
 417                 "02");
 418     assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]"_ostr,
 419                 "month"_ostr, "03");
 420     assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]"_ostr,
 421                 "year"_ostr, "2017");
 422     assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]"_ostr,
 423                 "dateTimeGrouping"_ostr, "day");
 424
 425     assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[2]"_ostr, "day"_ostr,
 426                 "01");
 427     assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[2]"_ostr,
 428                 "month"_ostr, "10");
 429     assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[2]"_ostr,
 430                 "year"_ostr, "2014");
 431     assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[2]"_ostr,
 432                 "dateTimeGrouping"_ostr, "day");
 433 }
 434
 435 CPPUNIT_TEST_FIXTURE(ScExportTest2, testDateAutofilterODS)
 436 {
 437     createScDoc("ods/tdf142231.ods");
 438
 439     save("calc8");
 440     xmlDocUniquePtr pDoc = parseExport("content.xml");
 441     CPPUNIT_ASSERT(pDoc);
 442
 443     assertXPath(pDoc, "//table:filter/table:filter-and/table:filter-condition[1]"_ostr,
 444                 "value"_ostr, "Calc");
 445     assertXPath(pDoc, "//table:filter/table:filter-and/table:filter-condition[2]"_ostr,
 446                 "value"_ostr, "2021-05-04");
 447 }
 448
 449 CPPUNIT_TEST_FIXTURE(ScExportTest2, testAutofilterColorsODF)
 450 {
 451     createScDoc("ods/autofilter-colors.ods");
 452
 453     save("calc8");
 454     xmlDocUniquePtr pDoc = parseExport("content.xml");
 455     CPPUNIT_ASSERT(pDoc);
 456
 457     assertXPath(pDoc,
 458                 "//table:database-ranges/table:database-range[1]/table:filter/table:filter-and/"
 459                 "table:filter-condition[1]"_ostr,
 460                 "value"_ostr, "#e8f2a1");
 461     assertXPath(pDoc,
 462                 "//table:database-ranges/table:database-range[1]/table:filter/table:filter-and/"
 463                 "table:filter-condition[1][@loext:data-type='background-color']"_ostr);
 464     assertXPath(pDoc,
 465                 "//table:database-ranges/table:database-range[1]/table:filter/table:filter-and/"
 466                 "table:filter-condition[2]"_ostr,
 467                 "value"_ostr, "#3465a4");
 468     assertXPath(pDoc,
 469                 "//table:database-ranges/table:database-range[1]/table:filter/"
 470                 "table:filter-and/table:filter-condition[2][@loext:data-type='text-color']"_ostr);
 471
 472     // tdf#142965 Check "none" value when automatic text color / no fill was selected
 473     assertXPath(pDoc, "//table:database-ranges/table:database-range[2]/table:filter/"
 474                       "table:filter-and/"
 475                       "table:filter-condition[1][@loext:data-type='background-color']"_ostr);
 476     assertXPath(pDoc,
 477                 "//table:database-ranges/table:database-range[2]/table:filter/table:filter-and/"
 478                 "table:filter-condition[1]"_ostr,
 479                 "value"_ostr, "transparent");
 480     assertXPath(pDoc,
 481                 "//table:database-ranges/table:database-range[3]/table:filter/"
 482                 "table:filter-and/table:filter-condition[1][@loext:data-type='text-color']"_ostr);
 483     assertXPath(pDoc,
 484                 "//table:database-ranges/table:database-range[3]/table:filter/table:filter-and/"
 485                 "table:filter-condition[1]"_ostr,
 486                 "value"_ostr, "window-font-color");
 487 }
 488
 489 CPPUNIT_TEST_FIXTURE(ScExportTest2, testAutofilterColorsOOXML)
 490 {
 491     {
 492         createScDoc("xlsx/autofilter-colors.xlsx");
 493         save("Calc Office Open XML");
 494         xmlDocUniquePtr pTable1 = parseExport("xl/tables/table1.xml");
 495         CPPUNIT_ASSERT(pTable1);
 496         sal_Int32 nDxfId
 497             = getXPath(pTable1, "/x:table/x:autoFilter/x:filterColumn/x:colorFilter"_ostr,
 498                        "dxfId"_ostr)
 499                   .toInt32()
 500               + 1;
 501
 502         xmlDocUniquePtr pStyles = parseExport("xl/styles.xml");
 503         CPPUNIT_ASSERT(pStyles);
 504         OString sDxfXPath("/x:styleSheet/x:dxfs/x:dxf[" + OString::number(nDxfId)
 505                           + "]/x:fill/x:patternFill/x:fgColor");
 506         assertXPath(pStyles, sDxfXPath, "rgb"_ostr, "FFFFD7D7");
 507     }
 508
 509     {
 510         createScDoc("xlsx/autofilter-colors-fg.xlsx");
 511         save("Calc Office Open XML");
 512         xmlDocUniquePtr pTable1 = parseExport("xl/tables/table1.xml");
 513         CPPUNIT_ASSERT(pTable1);
 514         sal_Int32 nDxfId
 515             = getXPath(pTable1, "/x:table/x:autoFilter/x:filterColumn/x:colorFilter"_ostr,
 516                        "dxfId"_ostr)
 517                   .toInt32()
 518               + 1;
 519
 520         xmlDocUniquePtr pStyles = parseExport("xl/styles.xml");
 521         CPPUNIT_ASSERT(pStyles);
 522         OString sDxfXPath("/x:styleSheet/x:dxfs/x:dxf[" + OString::number(nDxfId)
 523                           + "]/x:fill/x:patternFill/x:fgColor");
 524         assertXPath(pStyles, sDxfXPath, "rgb"_ostr, "FF3465A4");
 525     }
 526 }
 527
 528 CPPUNIT_TEST_FIXTURE(ScExportTest2, testAutofilterTop10XLSX)
 529 {
 530     createScDoc("xlsx/tdf143068_top10filter.xlsx");
 531
 532     save("Calc Office Open XML");
 533     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
 534     CPPUNIT_ASSERT(pDoc);
 535     assertXPath(pDoc, "//x:autoFilter/x:filterColumn"_ostr, "colId"_ostr, "0");
 536     assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:top10"_ostr, "val"_ostr, "4");
 537 }
 538
 539 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf88657ODS)
 540 {
 541     createScDoc("ods/tdf88657.ods");
 542
 543     save("calc8");
 544     xmlDocUniquePtr pDoc = parseExport("styles.xml");
 545     CPPUNIT_ASSERT(pDoc);
 546
 547     assertXPath(pDoc, "//number:fraction"_ostr, "min-denominator-digits"_ostr, "3");
 548 }
 549
 550 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf41722)
 551 {
 552     createScDoc("xlsx/tdf41722.xlsx");
 553
 554     save("Calc Office Open XML");
 555     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
 556     CPPUNIT_ASSERT(pDoc);
 557
 558     assertXPath(pDoc, "//x:conditionalFormatting/x:cfRule[1]"_ostr, "operator"_ostr,
 559                 "containsText");
 560     assertXPath(pDoc, "//x:conditionalFormatting/x:cfRule[2]"_ostr, "operator"_ostr,
 561                 "containsText");
 562     assertXPath(pDoc, "//x:conditionalFormatting/x:cfRule[3]"_ostr, "operator"_ostr,
 563                 "containsText");
 564 }
 565
 566 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf113621)
 567 {
 568     createScDoc("xlsx/tdf113621.xlsx");
 569
 570     save("Calc Office Open XML");
 571     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
 572     CPPUNIT_ASSERT(pDoc);
 573
 574     assertXPath(pDoc, "//x:conditionalFormatting"_ostr, "sqref"_ostr, "A1:A1048576");
 575 }
 576
 577 CPPUNIT_TEST_FIXTURE(ScExportTest2, testEscapeCharInNumberFormatXLSX)
 578 {
 579     createScDoc("xlsx/tdf81939.xlsx");
 580
 581     save("Calc Office Open XML");
 582     xmlDocUniquePtr pDoc = parseExport("xl/styles.xml");
 583     CPPUNIT_ASSERT(pDoc);
 584
 585     assertXPath(pDoc, "/x:styleSheet/x:numFmts/x:numFmt[2]"_ostr, "formatCode"_ostr,
 586                 "00\\ 00\\ 00\\ 00\\ 00");
 587     assertXPath(pDoc, "/x:styleSheet/x:numFmts/x:numFmt[3]"_ostr, "formatCode"_ostr,
 588                 "00\\.00\\.00\\.000\\.0"); // tdf#81939
 589     // "_-* #,##0\ _€_-;\-* #,##0\ _€_-;_-* "- "_€_-;_-@_-" // tdf#81222
 590     OUString rFormatStrExpected(u"_-* #,##0\\ _€_-;\\-* #,##0\\ _€_-;_-* \"- \"_€_-;_-@_-"_ustr);
 591     assertXPath(pDoc, "/x:styleSheet/x:numFmts/x:numFmt[4]"_ostr, "formatCode"_ostr,
 592                 rFormatStrExpected);
 593     // "_-* #,##0" €"_-;\-* #,##0" €"_-;_-* "- €"_-;_-@_-");
 594     rFormatStrExpected = u"_-* #,##0\" €\"_-;\\-* #,##0\" €\"_-;_-* \"- €\"_-;_-@_-"_ustr;
 595     assertXPath(pDoc, "/x:styleSheet/x:numFmts/x:numFmt[5]"_ostr, "formatCode"_ostr,
 596                 rFormatStrExpected);
 597     // remove escape char in fraction
 598     assertXPath(pDoc, "/x:styleSheet/x:numFmts/x:numFmt[6]"_ostr, "formatCode"_ostr,
 599                 "# ?/?;[RED]\\-# #/#####");
 600 }
 601
 602 CPPUNIT_TEST_FIXTURE(ScExportTest2, testNatNumInNumberFormatXLSX)
 603 {
 604     createScDoc("ods/tdf79398_NatNum5.ods");
 605
 606     save("Calc Office Open XML");
 607     xmlDocUniquePtr pDoc = parseExport("xl/styles.xml");
 608     CPPUNIT_ASSERT(pDoc);
 609
 610     assertXPath(pDoc, "/x:styleSheet/x:numFmts/x:numFmt[3]"_ostr, "formatCode"_ostr,
 611                 "[DBNum2][$-804]General;[RED][DBNum2][$-804]General");
 612 }
 613
 614 CPPUNIT_TEST_FIXTURE(ScExportTest2, testExponentWithoutSignFormatXLSX)
 615 {
 616     createScDoc("ods/tdf102370_ExponentWithoutSign.ods");
 617     saveAndReload("Calc Office Open XML");
 618
 619     saveAndReload("calc8");
 620
 621     ScDocument* pDoc = getScDoc();
 622     sal_uInt32 nNumberFormat = pDoc->GetNumberFormat(0, 0, 0);
 623     const SvNumberformat* pNumberFormat = pDoc->GetFormatTable()->GetEntry(nNumberFormat);
 624     const OUString& rFormatStr = pNumberFormat->GetFormatstring();
 625
 626     CPPUNIT_ASSERT_EQUAL_MESSAGE("Number format lost exponent without sign during Excel export",
 627                                  OUString("0.00E0"), rFormatStr);
 628 }
 629
 630 CPPUNIT_TEST_FIXTURE(ScExportTest2, testExtendedLCIDXLSX)
 631 {
 632     createScDoc("ods/tdf36038_ExtendedLCID.ods");
 633
 634     saveAndReload("Calc Office Open XML");
 635     xmlDocUniquePtr pDocXml = parseExport("xl/styles.xml");
 636     CPPUNIT_ASSERT(pDocXml);
 637     // Check export
 638     assertXPath(pDocXml, "/x:styleSheet/x:numFmts/x:numFmt[2]"_ostr, "formatCode"_ostr,
 639                 "[$-107041E]dd\\-mm\\-yyyy");
 640     assertXPath(pDocXml, "/x:styleSheet/x:numFmts/x:numFmt[3]"_ostr, "formatCode"_ostr,
 641                 "[$-D07041E]dd\\-mm\\-yyyy");
 642     assertXPath(pDocXml, "/x:styleSheet/x:numFmts/x:numFmt[4]"_ostr, "formatCode"_ostr,
 643                 "[$-1030411]dd\\-mm\\-ee");
 644     assertXPath(pDocXml, "/x:styleSheet/x:numFmts/x:numFmt[5]"_ostr, "formatCode"_ostr,
 645                 "[$-1B030411]dd\\-mm\\-ee");
 646     assertXPath(pDocXml, "/x:styleSheet/x:numFmts/x:numFmt[6]"_ostr, "formatCode"_ostr,
 647                 "[$-108040D]dd\\-mm\\-yyyy");
 648     assertXPath(pDocXml, "/x:styleSheet/x:numFmts/x:numFmt[7]"_ostr, "formatCode"_ostr,
 649                 "[$-108040D]dd\\-mm\\-yyyy");
 650     assertXPath(pDocXml, "/x:styleSheet/x:numFmts/x:numFmt[8]"_ostr, "formatCode"_ostr,
 651                 "[$-1060401]dd\\-mm\\-yyyy");
 652
 653     // Check import
 654     ScDocument* pDoc = getScDoc();
 655     SvNumberFormatter* pNumFormatter = pDoc->GetFormatTable();
 656     const OUString aLang[5] = { "[$-41E]", "[$-411]", "[$-40D]", "[$-401]", "[$-500]" };
 657     const OUString aCalendar[5] = { "[~buddhist]DD-MM-YYYY", "DD-MM-EE", "[~jewish]DD-MM-YYYY",
 658                                     "[~hijri]DD-MM-YYYY", "[~dangi]YYYY/MM/DD" };
 659     // Note: ja-JP Gengou calendar is an implicit secondary (non-gregorian)
 660     // calendar, the explicit [~gengou] calendar modifier does not need to be
 661     // present, the E and EE keywords are used instead of YY and YYYY.
 662     for (sal_Int16 nCol = 1; nCol <= 2; nCol++)
 663     {
 664         for (sal_Int16 nRow = 1; nRow <= 4; nRow++)
 665         {
 666             sal_uInt32 nNumberFormat = pDoc->GetNumberFormat(nCol, nRow, 0);
 667             const SvNumberformat* pNumberFormat = pNumFormatter->GetEntry(nNumberFormat);
 668             const OUString& rFormatStr = pNumberFormat->GetFormatstring();
 669             const OUString aExpectedFormatStr
 670                 = aLang[nRow - 1] + ((nCol == 2 && nRow != 3) ? OUString("[NatNum1]") : OUString())
 671                   + aCalendar[nRow - 1];
 672
 673             CPPUNIT_ASSERT_EQUAL_MESSAGE("Number format lost extended LCID during Excel export",
 674                                          aExpectedFormatStr, rFormatStr);
 675         }
 676     }
 677 }
 678
 679 CPPUNIT_TEST_FIXTURE(ScExportTest2, testHiddenRepeatedRowsODS)
 680 {
 681     createScDoc();
 682
 683     {
 684         ScDocument* pDoc = getScDoc();
 685         pDoc->SetRowHidden(0, 20, 0, true);
 686     }
 687
 688     saveAndReload("calc8");
 689     ScDocument* pDoc = getScDoc();
 690     SCROW nFirstRow = 0;
 691     SCROW nLastRow = 0;
 692     bool bHidden = pDoc->RowHidden(0, 0, &nFirstRow, &nLastRow);
 693     CPPUNIT_ASSERT(bHidden);
 694     CPPUNIT_ASSERT_EQUAL(SCROW(0), nFirstRow);
 695     CPPUNIT_ASSERT_EQUAL(SCROW(20), nLastRow);
 696 }
 697
 698 CPPUNIT_TEST_FIXTURE(ScExportTest2, testHyperlinkTargetFrameODS)
 699 {
 700     createScDoc("ods/hyperlink_frame.ods");
 701
 702     ScDocument* pDoc = getScDoc();
 703     const EditTextObject* pEditText = pDoc->GetEditText(ScAddress(2, 5, 0));
 704     CPPUNIT_ASSERT(pEditText);
 705
 706     const SvxFieldData* pData = pEditText->GetFieldData(0, 0, text::textfield::Type::URL);
 707     CPPUNIT_ASSERT_MESSAGE("Failed to get the URL data.", pData);
 708     CPPUNIT_ASSERT_EQUAL_MESSAGE("Failed to get the URL data.", text::textfield::Type::URL,
 709                                  pData->GetClassId());
 710
 711     const SvxURLField* pURLData = static_cast<const SvxURLField*>(pData);
 712     OUString aTargetFrame = pURLData->GetTargetFrame();
 713     CPPUNIT_ASSERT_EQUAL(OUString("_blank"), aTargetFrame);
 714
 715     save("calc8");
 716     xmlDocUniquePtr pDocXml = parseExport("content.xml");
 717     CPPUNIT_ASSERT(pDocXml);
 718     OUString aTargetFrameExport
 719         = getXPath(pDocXml,
 720                    "/office:document-content/office:body/office:spreadsheet/table:table/"
 721                    "table:table-row[2]/table:table-cell[2]/text:p/text:a"_ostr,
 722                    "target-frame-name"_ostr);
 723     CPPUNIT_ASSERT_EQUAL(OUString("_blank"), aTargetFrameExport);
 724 }
 725
 726 CPPUNIT_TEST_FIXTURE(ScExportTest2, testOpenDocumentAsReadOnly)
 727 {
 728     uno::Sequence<beans::PropertyValue> aParams = { comphelper::makePropertyValue("Silent", true) };
 729
 730     loadWithParams(createFileURL(u"xlsx/open-as-read-only.xlsx"), aParams);
 731     ScDocShell* pDocSh = getScDocShell();
 732     CPPUNIT_ASSERT(pDocSh->IsSecurityOptOpenReadOnly());
 733
 734     saveWithParams(uno::Sequence<beans::PropertyValue>());
 735     loadWithParams(maTempFile.GetURL(), aParams);
 736
 737     pDocSh = getScDocShell();
 738     CPPUNIT_ASSERT(pDocSh->IsSecurityOptOpenReadOnly());
 739 }
 740
 741 CPPUNIT_TEST_FIXTURE(ScExportTest2, testKeepSettingsOfBlankRows)
 742 {
 743     createScDoc("xlsx/tdf41425.xlsx");
 744
 745     save("Calc Office Open XML");
 746     xmlDocUniquePtr pSheet = parseExport("xl/worksheets/sheet1.xml");
 747     CPPUNIT_ASSERT(pSheet);
 748
 749     // saved blank row with not default setting in A2
 750     assertXPath(pSheet, "/x:worksheet/x:sheetData/x:row"_ostr, 2);
 751 }
 752
 753 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf133595)
 754 {
 755     createScDoc("xlsx/tdf133595.xlsx");
 756
 757     save("Calc Office Open XML");
 758     xmlDocUniquePtr pSheet = parseExport("xl/worksheets/sheet1.xml");
 759     CPPUNIT_ASSERT(pSheet);
 760
 761     // without the fix in place, mc:AlternateContent would have been added to sheet1
 762     assertXPath(pSheet, "/x:worksheet/mc:AlternateContent"_ostr, 0);
 763 }
 764
 765 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf134769)
 766 {
 767     createScDoc("xlsx/tdf134769.xlsx");
 768
 769     save("Calc Office Open XML");
 770     xmlDocUniquePtr pSheet = parseExport("xl/worksheets/sheet1.xml");
 771     CPPUNIT_ASSERT(pSheet);
 772
 773     // without the fix in place, the legacyDrawing would have been exported after the checkbox
 774     // and Excel would have claimed the document is corrupted
 775     // Use their ids to check the order
 776     assertXPath(pSheet, "/x:worksheet/x:drawing"_ostr, "id"_ostr, "rId2");
 777     assertXPath(pSheet, "/x:worksheet/x:legacyDrawing"_ostr, "id"_ostr, "rId3");
 778     assertXPath(pSheet,
 779                 "/x:worksheet/mc:AlternateContent/mc:Choice/x:controls/mc:AlternateContent/"
 780                 "mc:Choice/x:control"_ostr,
 781                 "id"_ostr, "rId4");
 782 }
 783
 784 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf106181)
 785 {
 786     createScDoc("ods/tdf106181.ods");
 787
 788     save("Calc Office Open XML");
 789     xmlDocUniquePtr pSheet = parseExport("xl/worksheets/sheet1.xml");
 790     CPPUNIT_ASSERT(pSheet);
 791
 792     assertXPath(pSheet,
 793                 "/x:worksheet/mc:AlternateContent/mc:Choice/x:controls/mc:AlternateContent/"
 794                 "mc:Choice/x:control"_ostr,
 795                 "name"_ostr, "Check Box");
 796     assertXPath(pSheet,
 797                 "/x:worksheet/mc:AlternateContent/mc:Choice/x:controls/mc:AlternateContent/"
 798                 "mc:Choice/x:control/x:controlPr"_ostr,
 799                 "altText"_ostr, "Check Box 1");
 800
 801     xmlDocUniquePtr pDrawing = parseExport("xl/drawings/drawing1.xml");
 802     CPPUNIT_ASSERT(pDrawing);
 803
 804     assertXPath(
 805         pDrawing,
 806         "/xdr:wsDr/mc:AlternateContent/mc:Choice/xdr:twoCellAnchor/xdr:sp/xdr:nvSpPr/xdr:cNvPr"_ostr,
 807         "name"_ostr, "Check Box 1");
 808     assertXPath(
 809         pDrawing,
 810         "/xdr:wsDr/mc:AlternateContent/mc:Choice/xdr:twoCellAnchor/xdr:sp/xdr:nvSpPr/xdr:cNvPr"_ostr,
 811         "descr"_ostr, "Check Box");
 812     assertXPath(
 813         pDrawing,
 814         "/xdr:wsDr/mc:AlternateContent/mc:Choice/xdr:twoCellAnchor/xdr:sp/xdr:nvSpPr/xdr:cNvPr"_ostr,
 815         "hidden"_ostr, "0");
 816
 817     xmlDocUniquePtr pVmlDrawing = parseExport("xl/drawings/vmlDrawing1.vml");
 818     CPPUNIT_ASSERT(pVmlDrawing);
 819     assertXPathContent(pVmlDrawing, "//xx:ClientData/xx:FmlaLink"_ostr, "$D$9");
 820 }
 821
 822 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf145057)
 823 {
 824     createScDoc("xlsx/tdf145057.xlsx");
 825
 826     save("Calc Office Open XML");
 827     xmlDocUniquePtr pDoc = parseExport("xl/tables/table1.xml");
 828     CPPUNIT_ASSERT(pDoc);
 829
 830     assertXPath(pDoc, "//x:colorFilter"_ostr, "dxfId"_ostr, "1");
 831 }
 832
 833 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf105272)
 834 {
 835     createScDoc("xlsx/tdf105272.xlsx");
 836     saveAndReload("Calc Office Open XML");
 837     ScDocument* pDoc = getScDoc();
 838     //without the fix in place,it would fail
 839     //Expected: Table1[[#This Row],[Total]]/Table1[[#This Row],['# Athletes]]
 840     //Actual  : table1[[#this row],[total]]/table1[[#this row],['# athletes]]
 841
 842     CPPUNIT_ASSERT_EQUAL_MESSAGE(
 843         "Wrong formula", OUString("=Table1[[#This Row],[Total]]/Table1[[#This Row],['# Athletes]]"),
 844         pDoc->GetFormula(7, 3, 0));
 845 }
 846
 847 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf118990)
 848 {
 849     createScDoc("xlsx/tdf118990.xlsx");
 850     saveAndReload("Calc Office Open XML");
 851     ScDocument* pDoc = getScDoc();
 852
 853     // TODO: also test A1, which contains a UNC reference to \\localhost\share\lookupsource.xlsx,
 854     // but currently looses "localhost" part when normalized in INetURLObject, becoming
 855     // file:///share/lookupsource.xlsx - which is incorrect, since it points to local filesystem
 856     // and not to Windows network share.
 857
 858     CPPUNIT_ASSERT_EQUAL_MESSAGE(
 859         "Wrong Windows share (using host IP) URL in A2",
 860         OUString("=VLOOKUP(B1,'file://192.168.1.1/share/lookupsource.xlsx'#$Sheet1.A1:B5,2)"),
 861         pDoc->GetFormula(0, 1, 0));
 862
 863     CPPUNIT_ASSERT_EQUAL_MESSAGE(
 864         "Wrong Windows share (using hostname) URL in A3",
 865         OUString("=VLOOKUP(B1,'file://NETWORKHOST/share/lookupsource.xlsx'#$Sheet1.A1:B5,2)"),
 866         pDoc->GetFormula(0, 2, 0));
 867 }
 868
 869 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf121612)
 870 {
 871     createScDoc("ods/tdf121612.ods");
 872     saveAndReload("Calc Office Open XML");
 873
 874     ScDocument* pDoc = getScDoc();
 875
 876     // There should be a pivot table
 877     CPPUNIT_ASSERT(pDoc->HasPivotTable());
 878
 879     // DP collection is not lost after export and has one entry
 880     ScDPCollection* pDPColl = pDoc->GetDPCollection();
 881     CPPUNIT_ASSERT(pDPColl);
 882     CPPUNIT_ASSERT_EQUAL(size_t(1), pDPColl->GetCount());
 883 }
 884
 885 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf112936)
 886 {
 887     createScDoc("xlsx/tdf112936.xlsx");
 888
 889     save("Calc Office Open XML");
 890     xmlDocUniquePtr pDoc = parseExport("xl/pivotCache/pivotCacheDefinition1.xml");
 891     CPPUNIT_ASSERT(pDoc);
 892
 893     assertXPath(pDoc, "//x:pivotCacheDefinition"_ostr, "recordCount"_ostr, "4");
 894     assertXPath(pDoc, "//x:pivotCacheDefinition"_ostr, "createdVersion"_ostr, "3");
 895 }
 896
 897 CPPUNIT_TEST_FIXTURE(ScExportTest2, testXltxExport)
 898 {
 899     // Create new document
 900     createScDoc();
 901
 902     // Export as template and check content type
 903     save("Calc MS Excel 2007 XML Template");
 904     xmlDocUniquePtr pDoc = parseExport("[Content_Types].xml");
 905     CPPUNIT_ASSERT(pDoc);
 906     assertXPath(pDoc, "/ContentType:Types/ContentType:Override[@PartName='/xl/workbook.xml']"_ostr,
 907                 "ContentType"_ostr,
 908                 "application/vnd.openxmlformats-officedocument.spreadsheetml.template.main+xml");
 909 }
 910
 911 CPPUNIT_TEST_FIXTURE(ScExportTest2, testPivotCacheAfterExportXLSX)
 912 {
 913     createScDoc("ods/numgroup_example.ods");
 914
 915     // export only
 916     save("Calc Office Open XML");
 917
 918     ScDocument* pDoc = getScDoc();
 919     CPPUNIT_ASSERT(pDoc->HasPivotTable());
 920
 921     // Two pivot tables
 922     ScDPCollection* pDPColl = pDoc->GetDPCollection();
 923     CPPUNIT_ASSERT(pDPColl);
 924     CPPUNIT_ASSERT_EQUAL(size_t(2), pDPColl->GetCount());
 925
 926     // One cache
 927     ScDPCollection::SheetCaches& rSheetCaches = pDPColl->GetSheetCaches();
 928     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), rSheetCaches.size());
 929     const ScDPCache* pCache = rSheetCaches.getExistingCache(ScRange(0, 0, 0, 3, 30, 0));
 930     CPPUNIT_ASSERT_MESSAGE("Pivot cache is expected for A1:D31 on the first sheet.", pCache);
 931
 932     // See if XLSX export didn't damage group info of the 1st pivot table
 933     const ScDPNumGroupInfo* pInfo = pCache->GetNumGroupInfo(1);
 934     CPPUNIT_ASSERT_MESSAGE("No number group info :(", pInfo);
 935 }
 936
 937 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf114969XLSX)
 938 {
 939     createScDoc("ods/sheet_name_with_dots.ods");
 940
 941     save("Calc Office Open XML");
 942     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
 943     CPPUNIT_ASSERT(pDoc);
 944     assertXPath(pDoc, "/x:worksheet/x:hyperlinks/x:hyperlink[1]"_ostr, "location"_ostr,
 945                 "'1.1.1.1'!C1");
 946     assertXPath(pDoc, "/x:worksheet/x:hyperlinks/x:hyperlink[2]"_ostr, "location"_ostr,
 947                 "'1.1.1.1'!C2");
 948 }
 949
 950 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf115192XLSX)
 951 {
 952     createScDoc("xlsx/test_115192.xlsx");
 953
 954     save("Calc Office Open XML");
 955     xmlDocUniquePtr pDoc = parseExport("xl/drawings/_rels/drawing1.xml.rels");
 956     CPPUNIT_ASSERT(pDoc);
 957     assertXPath(pDoc, "/rels:Relationships/rels:Relationship[@Id='rId1']"_ostr, "TargetMode"_ostr,
 958                 "External");
 959     assertXPathNoAttribute(pDoc, "/rels:Relationships/rels:Relationship[@Id='rId2']"_ostr,
 960                            "TargetMode"_ostr);
 961     assertXPath(pDoc, "/rels:Relationships/rels:Relationship[@Id='rId3']"_ostr, "TargetMode"_ostr,
 962                 "External");
 963 }
 964
 965 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf142764)
 966 {
 967     createScDoc("ods/tdf142764.ods");
 968
 969     save("Calc Office Open XML");
 970     xmlDocUniquePtr pSheet = parseExport("xl/worksheets/sheet1.xml");
 971     CPPUNIT_ASSERT(pSheet);
 972
 973     assertXPath(pSheet, "/x:worksheet/x:headerFooter"_ostr, "differentOddEven"_ostr, "true");
 974     assertXPath(pSheet, "/x:worksheet/x:headerFooter"_ostr, "differentFirst"_ostr, "true");
 975 }
 976
 977 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf91634XLSX)
 978 {
 979     createScDoc("xlsx/image_hyperlink.xlsx");
 980     save("Calc Office Open XML");
 981
 982     xmlDocUniquePtr pDoc = parseExport("xl/drawings/drawing1.xml");
 983     CPPUNIT_ASSERT(pDoc);
 984     assertXPath(pDoc, "/xdr:wsDr/xdr:twoCellAnchor/xdr:pic/xdr:nvPicPr/xdr:cNvPr/a:hlinkClick"_ostr,
 985                 1);
 986
 987     xmlDocUniquePtr pXmlRels = parseExport("xl/drawings/_rels/drawing1.xml.rels");
 988     CPPUNIT_ASSERT(pXmlRels);
 989     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship[@Id='rId1']"_ostr, "Target"_ostr,
 990                 "https://www.google.com/");
 991     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship[@Id='rId1']"_ostr,
 992                 "TargetMode"_ostr, "External");
 993 }
 994
 995 CPPUNIT_TEST_FIXTURE(ScExportTest2, testValidationCopyPaste)
 996 {
 997     createScDoc("ods/validation-copypaste.ods");
 998     ScDocument* pDoc = getScDoc();
 999
1000     // Copy B1 from src doc to clip
1001     ScDocument aClipDoc(SCDOCMODE_CLIP);
1002     ScRange aSrcRange(1, 0, 1);
1003     ScClipParam aClipParam(aSrcRange, false);
1004     ScMarkData aMark(pDoc->GetSheetLimits());
1005     aMark.SetMarkArea(aSrcRange);
1006     pDoc->CopyToClip(aClipParam, &aClipDoc, &aMark, false, false);
1007
1008     // Create second document, paste B1 from clip
1009     createScDoc();
1010     pDoc = getScDoc();
1011     ScRange aDstRange(1, 0, 0);
1012     ScMarkData aMark2(pDoc->GetSheetLimits());
1013     aMark2.SetMarkArea(aDstRange);
1014     pDoc->CopyFromClip(aDstRange, aMark2, InsertDeleteFlags::ALL, nullptr, &aClipDoc);
1015
1016     // save as XLSX
1017     save("Calc Office Open XML");
1018
1019     // check validation
1020     xmlDocUniquePtr pDocXml = parseExport("xl/worksheets/sheet1.xml");
1021     CPPUNIT_ASSERT(pDocXml);
1022     assertXPathContent(pDocXml, "/x:worksheet/x:dataValidations/x:dataValidation/x:formula1"_ostr,
1023                        "#REF!");
1024 }
1025
1026 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf115159)
1027 {
1028     createScDoc("xlsx/tdf115159.xlsx");
1029
1030     save("Calc Office Open XML");
1031     xmlDocUniquePtr pDoc = parseExport("xl/workbook.xml");
1032     CPPUNIT_ASSERT(pDoc);
1033
1034     //assert the existing OOXML built-in name is not duplicated
1035     assertXPath(pDoc, "/x:workbook/x:definedNames/x:definedName"_ostr, 1);
1036 }
1037
1038 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf112567)
1039 {
1040     // Set the system locale to Hungarian (a language with different range separator)
1041     SvtSysLocaleOptions aOptions;
1042     OUString sLocaleConfigString = aOptions.GetLanguageTag().getBcp47();
1043     aOptions.SetLocaleConfigString("hu-HU");
1044     aOptions.Commit();
1045     comphelper::ScopeGuard g([&aOptions, &sLocaleConfigString] {
1046         aOptions.SetLocaleConfigString(sLocaleConfigString);
1047         aOptions.Commit();
1048     });
1049
1050     createScDoc("xlsx/tdf112567.xlsx");
1051
1052     save("Calc Office Open XML");
1053     xmlDocUniquePtr pDoc = parseExport("xl/workbook.xml");
1054     CPPUNIT_ASSERT(pDoc);
1055
1056     //assert the existing OOXML built-in name is not duplicated
1057     assertXPath(pDoc, "/x:workbook/x:definedNames/x:definedName"_ostr, 1);
1058 }
1059
1060 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf75702)
1061 {
1062     // The problem was that line breaks were not imported.
1063     constexpr OUString sA1(u"line1\nline2"_ustr);
1064
1065     createScDoc("ods/tdf75702_textLineBreak.ods");
1066     ScDocument* pDoc = getScDoc();
1067     CPPUNIT_ASSERT_EQUAL_MESSAGE("load a1", sA1, pDoc->GetString(0, 0, 0));
1068
1069     saveAndReload("calc8");
1070     pDoc = getScDoc();
1071     CPPUNIT_ASSERT_EQUAL_MESSAGE("reload a1", sA1, pDoc->GetString(0, 0, 0));
1072
1073     save("calc8");
1074     xmlDocUniquePtr pContent = parseExport("content.xml");
1075     assertXPath(pContent, "//table:table-row[1]/table:table-cell/text:p"_ostr, 2);
1076 }
1077
1078 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf103829)
1079 {
1080     // The problem was that tabspaces were not imported or exported at all.
1081     // These strings match the current implementations of CELLTYPE_EDIT and CELLTYPE_STRING.
1082     constexpr OUString sA1(u"\x001Leading tab\nTHREE tabs inside: [\x001\x001\x001]"_ustr);
1083     constexpr OUString sA2(u"\tLeading tab. THREE tabs inside: [\t\t\t]"_ustr);
1084
1085     createScDoc("ods/tdf103829_textTab.ods");
1086     ScDocument* pDoc = getScDoc();
1087     CPPUNIT_ASSERT_EQUAL_MESSAGE("load a1", sA1, pDoc->GetString(0, 0, 0));
1088     CPPUNIT_ASSERT_EQUAL_MESSAGE("load a2", sA2, pDoc->GetString(0, 1, 0));
1089
1090     saveAndReload("calc8");
1091     pDoc = getScDoc();
1092     CPPUNIT_ASSERT_EQUAL_MESSAGE("reload a1", sA1, pDoc->GetString(0, 0, 0));
1093     CPPUNIT_ASSERT_EQUAL_MESSAGE("reload a2", sA2, pDoc->GetString(0, 1, 0));
1094 }
1095
1096 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf122191)
1097 {
1098     // Set the system locale to Hungarian
1099     SvtSysLocaleOptions aOptions;
1100     OUString sLocaleConfigString = aOptions.GetLanguageTag().getBcp47();
1101     aOptions.SetLocaleConfigString("hu-HU");
1102     aOptions.Commit();
1103     comphelper::ScopeGuard g([&aOptions, &sLocaleConfigString] {
1104         aOptions.SetLocaleConfigString(sLocaleConfigString);
1105         aOptions.Commit();
1106     });
1107
1108     createScDoc("xlsx/tdf122191.xlsx");
1109
1110     ScDocument* pDoc = getScDoc();
1111     CPPUNIT_ASSERT_EQUAL(OUString("IGAZ"), pDoc->GetString(0, 0, 0));
1112
1113     saveAndReload("Calc Office Open XML");
1114
1115     pDoc = getScDoc();
1116     // Without the fix in place, this test would have failed with
1117     // - Expected: IGAZ
1118     // - Actual  : BOOL00AN
1119     CPPUNIT_ASSERT_EQUAL(OUString("IGAZ"), pDoc->GetString(0, 0, 0));
1120 }
1121
1122 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf142881)
1123 {
1124     createScDoc("xlsx/tdf142881.xlsx");
1125
1126     save("Calc Office Open XML");
1127     xmlDocUniquePtr pDrawing1 = parseExport("xl/drawings/drawing1.xml");
1128     CPPUNIT_ASSERT(pDrawing1);
1129
1130     // Verify that the shapes are rotated and positioned in the expected way
1131     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[1]/xdr:from/xdr:col"_ostr, "11");
1132     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[1]/xdr:from/xdr:row"_ostr, "0");
1133     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[1]/xdr:to/xdr:col"_ostr, "12");
1134     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[1]/xdr:to/xdr:row"_ostr, "19");
1135
1136     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[2]/xdr:from/xdr:col"_ostr, "2");
1137     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[2]/xdr:from/xdr:row"_ostr, "8");
1138     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[2]/xdr:to/xdr:col"_ostr, "7");
1139     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[2]/xdr:to/xdr:row"_ostr, "10");
1140
1141     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[3]/xdr:from/xdr:col"_ostr, "10");
1142     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[3]/xdr:from/xdr:row"_ostr, "9");
1143     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[3]/xdr:to/xdr:col"_ostr, "11");
1144     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[3]/xdr:to/xdr:row"_ostr, "26");
1145
1146     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[4]/xdr:from/xdr:col"_ostr, "2");
1147     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[4]/xdr:from/xdr:row"_ostr, "17");
1148     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[4]/xdr:to/xdr:col"_ostr, "8");
1149     assertXPathContent(pDrawing1, "/xdr:wsDr/xdr:twoCellAnchor[4]/xdr:to/xdr:row"_ostr, "19");
1150 }
1151
1152 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf112567b)
1153 {
1154     // Set the system locale to Hungarian (a language with different range separator)
1155     SvtSysLocaleOptions aOptions;
1156     OUString sLocaleConfigString = aOptions.GetLanguageTag().getBcp47();
1157     aOptions.SetLocaleConfigString("hu-HU");
1158     aOptions.Commit();
1159     comphelper::ScopeGuard g([&aOptions, &sLocaleConfigString] {
1160         aOptions.SetLocaleConfigString(sLocaleConfigString);
1161         aOptions.Commit();
1162     });
1163
1164     createScDoc("ods/tdf112567.ods");
1165
1166     save("Calc Office Open XML");
1167     xmlDocUniquePtr pDoc = parseExport("xl/workbook.xml");
1168     CPPUNIT_ASSERT(pDoc);
1169
1170     //assert the existing OOXML built-in name is not duplicated
1171     assertXPath(pDoc, "/x:workbook/x:definedNames/x:definedName"_ostr, 1);
1172
1173     //and it contains "," instead of ";"
1174     assertXPathContent(pDoc, "/x:workbook/x:definedNames/x:definedName[1]"_ostr,
1175                        "Sheet1!$A:$A,Sheet1!$1:$1");
1176 }
1177
1178 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf123645XLSX)
1179 {
1180     createScDoc("xlsx/chart_hyperlink.xlsx");
1181     save("Calc Office Open XML");
1182
1183     xmlDocUniquePtr pDoc = parseExport("xl/drawings/drawing1.xml");
1184     CPPUNIT_ASSERT(pDoc);
1185     assertXPath(pDoc,
1186                 "/xdr:wsDr/xdr:twoCellAnchor[1]/xdr:graphicFrame/xdr:nvGraphicFramePr/xdr:cNvPr/"
1187                 "a:hlinkClick"_ostr,
1188                 1);
1189     assertXPath(pDoc,
1190                 "/xdr:wsDr/xdr:twoCellAnchor[2]/xdr:graphicFrame/xdr:nvGraphicFramePr/xdr:cNvPr/"
1191                 "a:hlinkClick"_ostr,
1192                 1);
1193     assertXPath(pDoc,
1194                 "/xdr:wsDr/xdr:twoCellAnchor[3]/xdr:graphicFrame/xdr:nvGraphicFramePr/xdr:cNvPr/"
1195                 "a:hlinkClick"_ostr,
1196                 1);
1197
1198     xmlDocUniquePtr pXmlRels = parseExport("xl/drawings/_rels/drawing1.xml.rels");
1199     CPPUNIT_ASSERT(pXmlRels);
1200     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship[@Id='rId1']"_ostr,
1201                 "TargetMode"_ostr, "External");
1202     assertXPathNoAttribute(pXmlRels, "/rels:Relationships/rels:Relationship[@Id='rId3']"_ostr,
1203                            "TargetMode"_ostr);
1204     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship[@Id='rId5']"_ostr,
1205                 "TargetMode"_ostr, "External");
1206     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship[@Id='rId1']"_ostr, "Target"_ostr,
1207                 "file:///C:/TEMP/test.xlsx");
1208     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship[@Id='rId3']"_ostr, "Target"_ostr,
1209                 "#Sheet2!A1");
1210     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship[@Id='rId5']"_ostr, "Target"_ostr,
1211                 "https://bugs.documentfoundation.org/show_bug.cgi?id=123645");
1212 }
1213
1214 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf125173XLSX)
1215 {
1216     createScDoc("ods/text_box_hyperlink.ods");
1217     save("Calc Office Open XML");
1218
1219     xmlDocUniquePtr pDoc = parseExport("xl/drawings/drawing1.xml");
1220     CPPUNIT_ASSERT(pDoc);
1221     assertXPath(pDoc, "/xdr:wsDr/xdr:twoCellAnchor/xdr:sp/xdr:nvSpPr/xdr:cNvPr/a:hlinkClick"_ostr,
1222                 1);
1223
1224     xmlDocUniquePtr pXmlRels = parseExport("xl/drawings/_rels/drawing1.xml.rels");
1225     CPPUNIT_ASSERT(pXmlRels);
1226     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship[@Id='rId1']"_ostr, "Target"_ostr,
1227                 "http://www.google.com/");
1228     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship[@Id='rId1']"_ostr,
1229                 "TargetMode"_ostr, "External");
1230 }
1231
1232 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf79972XLSX)
1233 {
1234     createScDoc("xlsx/tdf79972.xlsx");
1235     save("Calc Office Open XML");
1236
1237     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
1238     CPPUNIT_ASSERT(pDoc);
1239     assertXPath(pDoc, "/x:worksheet/x:hyperlinks/x:hyperlink"_ostr, "ref"_ostr, "A1");
1240
1241     xmlDocUniquePtr pXmlRels = parseExport("xl/worksheets/_rels/sheet1.xml.rels");
1242     CPPUNIT_ASSERT(pXmlRels);
1243     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship"_ostr, "Target"_ostr,
1244                 "https://bugs.documentfoundation.org/show_bug.cgi?id=79972");
1245     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship"_ostr, "TargetMode"_ostr,
1246                 "External");
1247 }
1248
1249 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf126024XLSX)
1250 {
1251     createScDoc("xlsx/hyperlink_formula.xlsx");
1252     save("Calc Office Open XML");
1253
1254     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
1255     CPPUNIT_ASSERT(pDoc);
1256     assertXPath(pDoc, "/x:worksheet/x:hyperlinks/x:hyperlink"_ostr, "ref"_ostr, "A2");
1257
1258     xmlDocUniquePtr pXmlRels = parseExport("xl/worksheets/_rels/sheet1.xml.rels");
1259     CPPUNIT_ASSERT(pXmlRels);
1260     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship"_ostr, "Target"_ostr,
1261                 "https://bugs.documentfoundation.org/");
1262     assertXPath(pXmlRels, "/rels:Relationships/rels:Relationship"_ostr, "TargetMode"_ostr,
1263                 "External");
1264 }
1265
1266 CPPUNIT_TEST_FIXTURE(ScExportTest2, testProtectedRange)
1267 {
1268     createScDoc("xlsx/protectedRange.xlsx");
1269     save("Calc Office Open XML");
1270
1271     xmlDocUniquePtr pDoc = parseExport("xl/worksheets/sheet1.xml");
1272     CPPUNIT_ASSERT(pDoc);
1273     // entire row was being exported as shorthand 'B:B' which LO couldn't read back
1274     assertXPath(pDoc, "//x:protectedRanges/x:protectedRange"_ostr, "sqref"_ostr, "B1:B1048576");
1275 }
1276
1277 CPPUNIT_TEST_FIXTURE(ScExportTest2, testTdf91332)
1278 {
1279     createScDoc("xlsx/tdf91332.xlsx");
1280     saveAndReload("Calc Office Open XML");
1281
1282     uno::Reference<drawing::XDrawPagesSupplier> xDoc(mxComponent, uno::UNO_QUERY_THROW);
1283     uno::Reference<drawing::XDrawPage> xPage(xDoc->getDrawPages()->getByIndex(0),
1284                                              uno::UNO_QUERY_THROW);
1285     uno::Reference<beans::XPropertySet> xShapeProps(xPage->getByIndex(0), uno::UNO_QUERY_THROW);
1286
1287     // Without the accompanying fix in place, this test would have failed with:
1288     // - Expected: rgba[90cf47ff]
1289     // - Actual  : rgba[ffffffff]
1290     // i.e. fill color inherited from theme lost after export.
1291     Color nColor;
1292     xShapeProps->getPropertyValue("FillColor") >>= nColor;
1293     CPPUNIT_ASSERT_EQUAL(Color(0x90cf47), nColor);
1294 }
1295
1296 CPPUNIT_PLUGIN_IMPLEMENT();
1297
1298 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */