LibreOffice Module test (master)  1
xspreadsheets2.cxx
Go to the documentation of this file.
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 
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 {
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 {
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 {
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 {
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 {
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 {
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 {
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 {
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 {
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 {
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 {
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: */
constexpr OUStringLiteral gaSrcFileName(u"rangenamessrc.ods")
constexpr OUStringLiteral gaSrcSheetName(u"SheetToCopy")
static sal_Int32 nInsertedSheets(0)
constexpr OUStringLiteral gaDestFileBase(u"ScNamedRangeObj.ods")
FILE * init(int, char **)