test/source/sheet/xspreadsheets2.cxx

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