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