test/source/sheet/xspreadsheets2.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 <test/sheet/xspreadsheets2.hxx>
  11
  12 #include <com/sun/star/beans/XPropertySet.hpp>
  13 #include <com/sun/star/sheet/XSpreadsheetDocument.hpp>
  14 #include <com/sun/star/sheet/XSpreadsheet.hpp>
  15 #include <com/sun/star/sheet/XSpreadsheets2.hpp>
  16 #include <com/sun/star/sheet/XNamedRanges.hpp>
  17 #include <com/sun/star/sheet/XNamedRange.hpp>
  18 #include <com/sun/star/table/XCell.hpp>
  19 #include <com/sun/star/text/XTextRange.hpp>
  20 #include <com/sun/star/container/XIndexAccess.hpp>
  21
  22 #include <com/sun/star/style/XStyleFamiliesSupplier.hpp>
  23 #include <com/sun/star/container/XNameContainer.hpp>
  24 #include <com/sun/star/table/CellVertJustify.hpp>
  25 #include <com/sun/star/util/XCloseable.hpp>
  26
  27 #include <rtl/ustring.hxx>
  28 #include <cppunit/TestAssert.h>
  29
  30 using namespace css;
  31 using namespace css::uno;
  32
  33 namespace apitest {
  34
  35 constexpr OUStringLiteral gaSrcSheetName(u"SheetToCopy");
  36 constexpr OUStringLiteral gaSrcFileName(u"rangenamessrc.ods");
  37 constexpr OUStringLiteral gaDestFileBase(u"ScNamedRangeObj.ods");
  38
  39 static sal_Int32 nInsertedSheets(0);
  40
  41
  42 XSpreadsheets2::XSpreadsheets2()
  43 {
  44 }
  45
  46 XSpreadsheets2::~XSpreadsheets2()
  47 {
  48     if (xDestDoc.is())
  49     {
  50         uno::Reference<util::XCloseable> xCloseable(xDestDoc, UNO_QUERY_THROW);
  51         xCloseable->close(true);
  52     }
  53 }
  54
  55 void XSpreadsheets2::testImportedSheetNameAndIndex()
  56 {
  57 /**
  58     Verify that the imported sheet has the correct name and is placed at the right requested index
  59 */
  60
  61     importSheetToCopy();
  62
  63     uno::Reference< container::XNameAccess > xDestSheetNameAccess(xDestDoc->getSheets(), UNO_QUERY_THROW);
  64     CPPUNIT_ASSERT_MESSAGE("Wrong sheet name", xDestSheetNameAccess->hasByName(gaSrcSheetName));
  65
  66 }
  67
  68 void XSpreadsheets2::testImportString()
  69 {
  70 /**
  71     tests the cell A1 containing a string correctly imported
  72 */
  73     importSheetToCopy();
  74
  75     uno::Reference< table::XCell > xSrcCell = xSrcSheet->getCellByPosition(0,0);
  76     uno::Reference< text::XTextRange > xSrcTextRange(xSrcCell, UNO_QUERY_THROW);
  77     OUString aSrcString = xSrcTextRange->getString();
  78
  79     uno::Reference< table::XCell > xDestCell = xDestSheet->getCellByPosition(0,0);
  80     uno::Reference< text::XTextRange > xDestTextRange(xDestCell, UNO_QUERY_THROW);
  81     OUString aDestString = xDestTextRange->getString();
  82
  83     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong string imported", aSrcString, aDestString);
  84 }
  85
  86 void XSpreadsheets2::testImportValue()
  87 {
  88 /**
  89     tests the cell B1 containing a value correctly imported
  90 */
  91     importSheetToCopy();
  92
  93     uno::Reference< table::XCell > xSrcCell = xSrcSheet->getCellByPosition(1,0);
  94     sal_Int32 aSrcValue = xSrcCell->getValue();
  95
  96     uno::Reference< table::XCell > xDestCell = xDestSheet->getCellByPosition(1,0);
  97     sal_Int32 aDestValue = xDestCell->getValue();
  98
  99     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong value imported", aSrcValue, aDestValue);
 100 }
 101
 102 void XSpreadsheets2::testImportFormulaBasicMath()
 103 {
 104 /**
 105     tests the cell C1 containing an arithmetic formula correctly imported
 106 */
 107     importSheetToCopy();
 108
 109     uno::Reference< table::XCell > xSrcCell = xSrcSheet->getCellByPosition(2,0);
 110     OUString aSrcFormula = xSrcCell->getFormula();
 111
 112     uno::Reference< table::XCell > xDestCell = xDestSheet->getCellByPosition(2,0);
 113     OUString aDestFormula = xDestCell->getFormula();
 114
 115     // potential problem later: formulas might be adjusted
 116     // add some tests that the formulas are correctly adjusted
 117     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula imported", aSrcFormula, aDestFormula);
 118 }
 119
 120 void XSpreadsheets2::testImportFormulaWithNamedRange()
 121 {
 122 /**
 123     tests the cell D1 containing a formula that uses a NamedRange expression
 124 */
 125     importSheetToCopy();
 126
 127     uno::Reference< table::XCell > xSrcCell = xSrcSheet->getCellByPosition(3,0);
 128     OUString aSrcFormula = xSrcCell->getFormula();
 129
 130     uno::Reference< table::XCell > xDestCell = xDestSheet->getCellByPosition(3,0);
 131     OUString aDestFormula = xDestCell->getFormula();
 132
 133     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong Namedrange formula imported", aSrcFormula, aDestFormula);
 134 }
 135
 136 void XSpreadsheets2::testImportOverExistingNamedRange()
 137 {
 138 /**
 139     Both Source and Target file define the named range initial1
 140     in Source, initial1 is defined outside the copied sheet
 141     In Target, after import sheet, initial1 should point on its initial definition $Sheet1.$B$1
 142
 143     NEED MORE WORK
 144 */
 145     importSheetToCopy();
 146
 147     uno::Reference< container::XNameAccess > xDestNamedRangesNameAccess(getNamedRanges(xDestDoc), UNO_QUERY_THROW);
 148     uno::Any aNr = xDestNamedRangesNameAccess->getByName("initial1");
 149     uno::Reference< sheet::XNamedRange > xDestNamedRange(aNr, UNO_QUERY_THROW);
 150     OUString aNrDestContent = xDestNamedRange->getContent();
 151
 152     std::cout << "testImportSheet : initial1 aNrDestContent " << aNrDestContent << std::endl;
 153     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong address for initial1", OUString("$Sheet1.$B$1"), aNrDestContent);
 154
 155 }
 156
 157 void XSpreadsheets2::testImportNamedRangeDefinedInSource()
 158 {
 159 /**
 160     in Source file, InSheetRangeName named range is defined in the copied sheet
 161     it does not exists in target file
 162     test that the range named is created in target and that it points in the target copied sheet
 163 */
 164     importSheetToCopy();
 165
 166     // New range name defined in imported sheet $SheetToCopy.$A$7
 167     OUString aNewInSheetNamedRangeString("InSheetRangeName");
 168     uno::Reference< container::XNameAccess > xDestNamedRangesNameAccess(getNamedRanges(xDestDoc), UNO_QUERY_THROW);
 169     CPPUNIT_ASSERT_MESSAGE("InSheetRangeName", xDestNamedRangesNameAccess->hasByName(aNewInSheetNamedRangeString));
 170
 171     uno::Any aNewInSheetNr = xDestNamedRangesNameAccess->getByName(aNewInSheetNamedRangeString);
 172     uno::Reference< sheet::XNamedRange > xDestNewInSheetNamedRange(aNewInSheetNr, UNO_QUERY_THROW);
 173     OUString aNewInSheetNrDestContent = xDestNewInSheetNamedRange->getContent();
 174     OUString aNewInSheetExpectedContent("$SheetToCopy.$A$7");
 175
 176     std::cout << "testImportSheet : InSheetRangeName content " << aNewInSheetNrDestContent << std::endl;
 177     std::cout << "testImportSheet : InSheetRangeName expected " << aNewInSheetExpectedContent << std::endl;
 178     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong address for InSheetRangeName", aNewInSheetExpectedContent, aNewInSheetNrDestContent);
 179 }
 180
 181 void XSpreadsheets2::testImportNamedRangeRedefinedInSource()
 182 {
 183 /**
 184     in Source file, initial2 named range is defined in the copied sheet
 185     it is defined in another sheet of target file
 186     test that the range named points in the target copied sheet
 187 */
 188     importSheetToCopy();
 189
 190     // the source file redefines an existing named range in the imported sheet --> the target should not be changed
 191     OUString aRedefinedInSheetNamedRangeString("initial2");
 192     uno::Reference< container::XNameAccess > xDestNamedRangesNameAccess(getNamedRanges(xDestDoc), UNO_QUERY_THROW);
 193     CPPUNIT_ASSERT_MESSAGE("aRedefinedInSheetNamedRangeString", xDestNamedRangesNameAccess->hasByName(aRedefinedInSheetNamedRangeString));
 194
 195     uno::Any aRedefinedInSheetNr = xDestNamedRangesNameAccess->getByName(aRedefinedInSheetNamedRangeString);
 196     uno::Reference< sheet::XNamedRange > xDestRedefinedInSheetNamedRange(aRedefinedInSheetNr, UNO_QUERY_THROW);
 197     OUString aRedefinedInSheetNrDestContent = xDestRedefinedInSheetNamedRange->getContent();
 198     std::cout << "testImportSheet : initial2 content " << aRedefinedInSheetNrDestContent << std::endl;
 199     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong address for Redefined InSheet named range", OUString("$Sheet1.$B$2"), aRedefinedInSheetNrDestContent);
 200 }
 201
 202 void XSpreadsheets2::testImportNewNamedRange()
 203 {
 204 /**
 205     in Source file, new_rangename range named is defined outside the copied sheet
 206     it does not exists in target file test that new_rangename is created and its
 207     content points to source file as an external reference
 208 */
 209     importSheetToCopy();
 210
 211     //formula with a non-existent named range in dest - new_rangename
 212     OUString aNewNamedRangeString("new_rangename");
 213     uno::Reference< container::XNameAccess > xDestNamedRangesNameAccess(getNamedRanges(xDestDoc), UNO_QUERY_THROW);
 214     CPPUNIT_ASSERT_MESSAGE("New NamedRange not created", xDestNamedRangesNameAccess->hasByName(aNewNamedRangeString));
 215
 216     // verify the content of this new namedrange, pointing on $Sheet1.$B$1 in source. This address is already defined in target as NR content
 217
 218     uno::Any aNewNr = xDestNamedRangesNameAccess->getByName(aNewNamedRangeString);
 219     uno::Reference< sheet::XNamedRange > xDestNewNamedRange(aNewNr, UNO_QUERY_THROW);
 220     OUString aNewNrDestContent = xDestNewNamedRange->getContent();
 221
 222     OUString aNewExpectedContent("$Sheet1.$B$1");
 223
 224     std::cout << "testImportSheet : new_rangename aNewExpectedContent " << aNewExpectedContent << std::endl;
 225     std::cout << "testImportSheet : new_rangename aNewNrDestContent " << aNewNrDestContent << std::endl;
 226     CPPUNIT_ASSERT_MESSAGE("Wrong New NamedRange formula string value", isExternalReference(aNewNrDestContent, aNewExpectedContent));
 227 }
 228
 229 void XSpreadsheets2::testImportCellStyle()
 230 {
 231 /**
 232     in source file, imported sheet uses a cellstyle that does not exists in target
 233     test that
 234         - an imported cell D1 uses the right cellStyle
 235         - the cellStyle is created in CellStyles family
 236         - a property of the cellStyle (VertJustify) is correctly set
 237 */
 238     importSheetToCopy();
 239
 240     uno::Reference< table::XCell > xSrcCell = xSrcSheet->getCellByPosition(3,0);
 241     xDestSheet->getCellByPosition(3,0);
 242
 243     //new style created in dest
 244     uno::Reference< beans::XPropertySet > xSrcCellPropSet (xSrcCell, UNO_QUERY_THROW);
 245     const OUString aCellProperty("CellStyle");
 246     OUString aSrcStyleName;
 247     CPPUNIT_ASSERT(xSrcCellPropSet->getPropertyValue(aCellProperty) >>= aSrcStyleName);
 248
 249     uno::Reference< beans::XPropertySet > xDestCellPropSet (xSrcCell, UNO_QUERY_THROW);
 250     OUString aDestStyleName;
 251     CPPUNIT_ASSERT(xDestCellPropSet->getPropertyValue(aCellProperty) >>= aDestStyleName);
 252
 253     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong imported Cell Style", aSrcStyleName, aDestStyleName);
 254
 255     uno::Reference< style::XStyleFamiliesSupplier > xFamiliesSupplier (xDestDoc, UNO_QUERY_THROW);
 256     uno::Reference< container::XNameAccess > xFamiliesNameAccess (xFamiliesSupplier->getStyleFamilies(), UNO_SET_THROW);
 257     uno::Any aCellStylesFamily = xFamiliesNameAccess->getByName("CellStyles");
 258     uno::Reference< container::XNameContainer > xCellStylesFamilyNameAccess (aCellStylesFamily, UNO_QUERY_THROW);
 259
 260     CPPUNIT_ASSERT_MESSAGE("New cell style not present", xCellStylesFamilyNameAccess->hasByName(aDestStyleName));
 261
 262     uno::Any aCellStyle = xCellStylesFamilyNameAccess->getByName(aDestStyleName);
 263     uno::Reference< beans::XPropertySet > xCellStyleProp (aCellStyle, UNO_QUERY_THROW);
 264     sal_Int32 aVertJustify = 0;
 265     CPPUNIT_ASSERT(xCellStyleProp->getPropertyValue("VertJustify") >>= aVertJustify);
 266
 267     CPPUNIT_ASSERT_EQUAL_MESSAGE("New style: VertJustify not set", table::CellVertJustify_CENTER, static_cast<table::CellVertJustify>(aVertJustify));
 268 }
 269
 270 void XSpreadsheets2::testLastAfterInsertCopy()
 271 {
 272     /** Test that results in row 1 of all inserted sheets are equal to the
 273         source sheet. The loaded destination document is kept open so several
 274         sheets are imported.
 275     */
 276
 277     CPPUNIT_ASSERT(nInsertedSheets > 0);
 278     constexpr sal_Int32 nCols = 7;
 279
 280     uno::Reference< container::XNameAccess> xSrcNameAccess(init(),UNO_QUERY_THROW);
 281     xSrcSheet.set( xSrcNameAccess->getByName(gaSrcSheetName), UNO_QUERY_THROW);
 282
 283     OUString aSrcString[nCols];
 284     for (sal_Int32 nCol=0; nCol < nCols; ++nCol)
 285     {
 286         uno::Reference< table::XCell > xSrcCell = xSrcSheet->getCellByPosition(nCol, 0);
 287         uno::Reference< text::XTextRange > xSrcTextRange(xSrcCell, UNO_QUERY_THROW);
 288         aSrcString[nCol] = xSrcTextRange->getString();
 289     }
 290     // The named range 'initial2' is already present in the destination
 291     // document defined to $Sheet1.$B$2 and thus is not copied, pointing to
 292     // "content2" instead.
 293     aSrcString[6] = "content2";
 294
 295     xDestDoc = getDoc(gaDestFileBase);
 296     CPPUNIT_ASSERT(xDestDoc.is());
 297     uno::Reference< container::XIndexAccess > xDestSheetIndexAccess (xDestDoc->getSheets(), UNO_QUERY_THROW);
 298     CPPUNIT_ASSERT( nInsertedSheets < xDestSheetIndexAccess->getCount());
 299     for (sal_Int32 nSheet=0; nSheet < nInsertedSheets; ++nSheet)
 300     {
 301         xDestSheet.set( xDestSheetIndexAccess->getByIndex(nSheet), UNO_QUERY_THROW);
 302         for (sal_Int32 nCol=0; nCol < nCols; ++nCol)
 303         {
 304             uno::Reference< table::XCell > xDestCell = xDestSheet->getCellByPosition(nCol, 0);
 305             uno::Reference< text::XTextRange > xDestTextRange(xDestCell, UNO_QUERY_THROW);
 306             OUString aDestString = xDestTextRange->getString();
 307
 308             if (nCol == 4 && aDestString == "Err:540")
 309                 // The created external reference to the source document not
 310                 // allowed may result in Err:540
 311                 continue;
 312
 313             OString aMessage("Imported result does not match, sheet " + OString::number(nSheet)
 314                     + " column " + OString::number(nCol));
 315             CPPUNIT_ASSERT_EQUAL_MESSAGE( aMessage.getStr(), aSrcString[nCol], aDestString);
 316         }
 317     }
 318 }
 319
 320 uno::Reference< sheet::XSpreadsheetDocument> XSpreadsheets2::getDoc(const OUString& aFileBase)
 321 {
 322     OUString aFileURL;
 323     createFileURL(aFileBase, aFileURL);
 324
 325     uno::Reference< lang::XComponent > xComp = loadFromDesktop(aFileURL);
 326
 327     CPPUNIT_ASSERT(xComp.is());
 328
 329     uno::Reference< sheet::XSpreadsheetDocument > xDoc(xComp, UNO_QUERY_THROW);
 330     return xDoc;
 331 }
 332
 333 uno::Reference< sheet::XNamedRanges> XSpreadsheets2::getNamedRanges(uno::Reference< sheet::XSpreadsheetDocument> const & xDoc)
 334 {
 335     uno::Reference< beans::XPropertySet > xPropSet (xDoc, UNO_QUERY_THROW);
 336     uno::Reference< sheet::XNamedRanges > xNamedRanges(xPropSet->getPropertyValue("NamedRanges"), UNO_QUERY_THROW);
 337     return xNamedRanges;
 338 }
 339
 340 void XSpreadsheets2::importSheetToCopy()
 341 {
 342     uno::Reference< container::XNameAccess> xSrcNameAccess(init(),UNO_QUERY_THROW);
 343     xSrcSheet.set( xSrcNameAccess->getByName(gaSrcSheetName), UNO_QUERY_THROW);
 344
 345     xDestDoc = getDoc(gaDestFileBase);
 346     CPPUNIT_ASSERT(xDestDoc.is());
 347
 348     // import sheet
 349     uno::Reference< sheet::XSpreadsheets2 > xDestSheets (xDestDoc->getSheets(), UNO_QUERY_THROW);
 350     sal_Int32 nDestPos = 0;
 351     sal_Int32 nDestPosEffective = xDestSheets->importSheet(xDocument, gaSrcSheetName, nDestPos);
 352     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong sheet index", nDestPos, nDestPosEffective);
 353     ++nInsertedSheets;
 354
 355     uno::Reference< container::XIndexAccess > xDestSheetIndexAccess (xDestDoc->getSheets(), UNO_QUERY_THROW);
 356     xDestSheet.set( xDestSheetIndexAccess->getByIndex(nDestPosEffective), UNO_QUERY_THROW);
 357 }
 358
 359 bool XSpreadsheets2::isExternalReference(const OUString& aDestContent, std::u16string_view aSrcContent )
 360 {
 361     CPPUNIT_ASSERT(aDestContent.startsWith("'file://"));
 362
 363     return  (aDestContent.endsWithIgnoreAsciiCase(aSrcContent) // same cell address
 364             && aDestContent.indexOf(gaSrcFileName)>0); // contains source file name
 365 }
 366
 367 }
 368
 369 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */