sc/qa/extras/macros-test.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 <sal/config.h>
  11 #include <test/unoapixml_test.hxx>
  12 #include <sal/log.hxx>
  13 #include <unotools/tempfile.hxx>
  14 #include <svx/svdpage.hxx>
  15 #include <unotools/mediadescriptor.hxx>
  16 #include <comphelper/processfactory.hxx>
  17 #include <comphelper/propertyvalue.hxx>
  18
  19 #include <conditio.hxx>
  20 #include <docsh.hxx>
  21 #include <document.hxx>
  22 #include <scitems.hxx>
  23
  24 #include <com/sun/star/sheet/XFunctionAccess.hpp>
  25 #include <com/sun/star/sheet/XSpreadsheet.hpp>
  26
  27 #include <com/sun/star/script/XLibraryContainerPassword.hpp>
  28 #include <com/sun/star/drawing/XDrawPageSupplier.hpp>
  29 #include <editeng/brushitem.hxx>
  30
  31 using namespace ::com::sun::star;
  32 using namespace ::com::sun::star::uno;
  33
  34 /* Implementation of Macros test */
  35
  36 class ScMacrosTest : public UnoApiXmlTest
  37 {
  38 protected:
  39     void registerNamespaces(xmlXPathContextPtr& pXmlXPathCtx) override;
  40 public:
  41     ScMacrosTest();
  42
  43     void testStarBasic();
  44     void testMSP();
  45     void testPasswordProtectedStarBasic();
  46     void testTdf114427();
  47     void testRowColumn();
  48     void testTdf104902();
  49     void testTdf64639();
  50     void testTdf142033();
  51     void testPasswordProtectedUnicodeString();
  52     void testPasswordProtectedArrayInUserType();
  53     void testTdf131296_legacy();
  54     void testTdf131296_new();
  55     void testTdf46119();
  56     void testTdf128218();
  57     void testTdf71271();
  58     void testTdf43003();
  59     void testTdf75263();
  60     void testTdf133887();
  61     void testTdf133889();
  62     void testTdf144970();
  63     void testTdf138646();
  64     void testTdf105558();
  65     void testTdf143582();
  66     void testTdf144085();
  67     void testTdf125800();
  68     void testTdf130307();
  69     void testTdf146742();
  70     void testMacroButtonFormControlXlsxExport();
  71     void testTdf107572();
  72     void testShapeLayerId();
  73     void testFunctionAccessIndirect();
  74     void testTdf147122();
  75
  76     CPPUNIT_TEST_SUITE(ScMacrosTest);
  77     CPPUNIT_TEST(testStarBasic);
  78     CPPUNIT_TEST(testMSP);
  79     CPPUNIT_TEST(testPasswordProtectedStarBasic);
  80     CPPUNIT_TEST(testTdf114427);
  81     CPPUNIT_TEST(testRowColumn);
  82     CPPUNIT_TEST(testTdf104902);
  83     CPPUNIT_TEST(testTdf64639);
  84     CPPUNIT_TEST(testTdf142033);
  85     CPPUNIT_TEST(testPasswordProtectedUnicodeString);
  86     CPPUNIT_TEST(testPasswordProtectedArrayInUserType);
  87     CPPUNIT_TEST(testTdf131296_legacy);
  88     CPPUNIT_TEST(testTdf131296_new);
  89     CPPUNIT_TEST(testTdf46119);
  90     CPPUNIT_TEST(testTdf128218);
  91     CPPUNIT_TEST(testTdf128218);
  92     CPPUNIT_TEST(testTdf71271);
  93     CPPUNIT_TEST(testTdf43003);
  94     CPPUNIT_TEST(testTdf75263);
  95     CPPUNIT_TEST(testTdf133887);
  96     CPPUNIT_TEST(testTdf133889);
  97     CPPUNIT_TEST(testTdf144970);
  98     CPPUNIT_TEST(testTdf138646);
  99     CPPUNIT_TEST(testTdf105558);
 100     CPPUNIT_TEST(testTdf143582);
 101     CPPUNIT_TEST(testTdf144085);
 102     CPPUNIT_TEST(testTdf125800);
 103     CPPUNIT_TEST(testTdf130307);
 104     CPPUNIT_TEST(testTdf146742);
 105     CPPUNIT_TEST(testMacroButtonFormControlXlsxExport);
 106     CPPUNIT_TEST(testTdf107572);
 107     CPPUNIT_TEST(testShapeLayerId);
 108     CPPUNIT_TEST(testFunctionAccessIndirect);
 109     CPPUNIT_TEST(testTdf147122);
 110
 111     CPPUNIT_TEST_SUITE_END();
 112 };
 113
 114 void ScMacrosTest::registerNamespaces(xmlXPathContextPtr& pXmlXPathCtx)
 115 {
 116     XmlTestTools::registerOOXMLNamespaces(pXmlXPathCtx);
 117     XmlTestTools::registerODFNamespaces(pXmlXPathCtx);
 118 }
 119
 120 // I suppose you could say this test doesn't really belong here, OTOH
 121 // we need a full document to run the test ( it related originally to an
 122 // imported Excel VBA macro ) It's convenient and fast to unit test
 123 // this the problem this way. Perhaps in the future there will be some sort
 124 // of slowcheck tests ( requiring a full document environment in the scripting
 125 // module, we could move the test there then ) - relates to fdo#67547
 126 void ScMacrosTest::testMSP()
 127 {
 128     loadFromURL(u"MasterScriptProviderProblem.ods");
 129
 130     Any aRet = executeMacro("vnd.sun.Star.script:Standard.Module1.TestMSP?language=Basic&location=document");
 131     OUString sResult;
 132     aRet >>= sResult;
 133
 134     SAL_INFO("sc.qa", "Result is " << sResult );
 135     CPPUNIT_ASSERT_EQUAL_MESSAGE("TestMSP ( for fdo#67547) failed", OUString("OK"), sResult);
 136 }
 137
 138 void ScMacrosTest::testPasswordProtectedStarBasic()
 139 {
 140     loadFromURL(u"testTypePassword.ods");
 141
 142     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 143
 144     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 145     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 146     ScDocument& rDoc = pDocSh->GetDocument();
 147
 148     // User defined types
 149
 150     executeMacro("vnd.sun.Star.script:Standard.Module1.LoadAndExecuteTest?language=Basic&location=document");
 151
 152     OUString aValue = rDoc.GetString(0,0,0);
 153     CPPUNIT_ASSERT_EQUAL_MESSAGE("User defined types script did not change the value of Sheet1.A1", OUString("success"), aValue);
 154
 155     // Big Module
 156
 157     executeMacro("vnd.sun.Star.script:MyLibrary.BigModule.bigMethod?language=Basic&location=document");
 158
 159     aValue = rDoc.GetString(1,0,0);
 160     CPPUNIT_ASSERT_EQUAL_MESSAGE("Big module script did not change the value of Sheet1.B1", OUString("success"), aValue);
 161
 162     // far big method tdf#94617
 163
 164     executeMacro("vnd.sun.Star.script:MyLibrary.BigModule.farBigMethod?language=Basic&location=document");
 165
 166     aValue = rDoc.GetString(2,0,0);
 167     CPPUNIT_ASSERT_EQUAL_MESSAGE("Far Method script did not change the value of Sheet1.C1", OUString("success"), aValue);
 168 }
 169
 170 void ScMacrosTest::testStarBasic()
 171 {
 172     loadFromURL(u"StarBasic.ods");
 173
 174     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 175
 176     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 177     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 178     ScDocument& rDoc = pDocSh->GetDocument();
 179
 180     executeMacro("vnd.sun.Star.script:Standard.Module1.Macro1?language=Basic&location=document");
 181     double aValue = rDoc.GetValue(0,0,0);
 182     CPPUNIT_ASSERT_DOUBLES_EQUAL_MESSAGE("script did not change the value of Sheet1.A1",2.0, aValue, 0.00001);
 183 }
 184
 185 void ScMacrosTest::testRowColumn()
 186 {
 187     loadFromURL(u"StarBasic.ods");
 188
 189     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 190
 191     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 192     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 193     ScDocument& rDoc = pDocSh->GetDocument();
 194
 195     executeMacro("vnd.sun.Star.script:Standard.Module1.Macro_RowHeight?language=Basic&location=document");
 196
 197     sal_uInt16 nHeight = o3tl::convert(rDoc.GetRowHeight(0, 0), o3tl::Length::twip, o3tl::Length::mm100);
 198     CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(2000), nHeight);
 199
 200     executeMacro("vnd.sun.Star.script:Standard.Module1.Macro_ColumnWidth?language=Basic&location=document");
 201     sal_uInt16 nWidth  = o3tl::convert(rDoc.GetColWidth(0, 0), o3tl::Length::twip, o3tl::Length::mm100);
 202     CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(4001), nWidth);
 203 }
 204
 205 void ScMacrosTest::testTdf146742()
 206 {
 207     loadFromURL(u"tdf146742.ods");
 208
 209     // Export to ODS and reload the file
 210     saveAndReload("calc8");
 211
 212     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 213     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 214     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 215     ScDocument& rDoc = pDocSh->GetDocument();
 216
 217     CPPUNIT_ASSERT_EQUAL(OUString("1"), rDoc.GetString(ScAddress(0,0,0)));
 218     CPPUNIT_ASSERT_EQUAL(OUString("2"), rDoc.GetString(ScAddress(0,1,0)));
 219
 220     CPPUNIT_ASSERT_EQUAL(OUString("TRUE"), rDoc.GetString(ScAddress(1,0,0)));
 221     // Without the fix in place, this test would have failed with
 222     // - Expected: FALSE
 223     // - Actual  : TRUE
 224     CPPUNIT_ASSERT_EQUAL(OUString("FALSE"), rDoc.GetString(ScAddress(1,1,0)));
 225 }
 226
 227 void ScMacrosTest::testMacroButtonFormControlXlsxExport()
 228 {
 229     // Given a button form control with an associated macro:
 230     loadFromURL(u"macro-button-form-control.xlsm");
 231
 232     // When exporting to XLSM:
 233     save("Calc MS Excel 2007 VBA XML");
 234
 235     // Then make sure that the macro is associated with the control:
 236     xmlDocUniquePtr pSheetDoc = parseExport("xl/worksheets/sheet1.xml");
 237     CPPUNIT_ASSERT(pSheetDoc);
 238     // Without the fix in place, this test would have failed with:
 239     // - XPath '//x:controlPr' no attribute 'macro' exist
 240     // i.e. the macro was lost on export.
 241     assertXPath(pSheetDoc, "//x:controlPr", "macro", "Module1.Button1_Click");
 242
 243     // Then also make sure that there is no defined name for the macro, which is only needed for
 244     // XLS:
 245     xmlDocUniquePtr pWorkbookDoc = parseExport("xl/workbook.xml");
 246     CPPUNIT_ASSERT(pWorkbookDoc);
 247     assertXPath(pWorkbookDoc, "//x:workbook/definedNames", 0);
 248 }
 249
 250 void ScMacrosTest::testTdf104902()
 251 {
 252     loadFromURL(u"tdf104902.ods");
 253
 254     executeMacro("vnd.sun.Star.script:Standard.Module1.display_bug?language=Basic&location=document");
 255
 256     // Export to ODS
 257     saveAndReload("calc8");
 258
 259     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 260
 261     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 262     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 263     ScDocument& rDoc = pDocSh->GetDocument();
 264
 265     CPPUNIT_ASSERT_EQUAL(OUString("string no newlines"), rDoc.GetString(ScAddress(0, 0, 0)));
 266
 267     // Without the fix in place, this test would have failed with
 268     // - Expected: string with
 269     // newlines
 270     // - Actual  : string withnewlines
 271     CPPUNIT_ASSERT_EQUAL(OUString(u"string with" + OUStringChar(u'\xA') + u"newlines"), rDoc.GetString(ScAddress(0, 1, 0)));
 272 }
 273
 274 void ScMacrosTest::testTdf64639()
 275 {
 276     loadFromURL(u"tdf64639.ods");
 277
 278     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 279
 280     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 281     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 282     ScDocument& rDoc = pDocSh->GetDocument();
 283
 284     ScDrawLayer* pDrawLayer = rDoc.GetDrawLayer();
 285     const SdrPage* pPage = pDrawLayer->GetPage(0);
 286
 287     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(0), pPage->GetObjCount());
 288
 289     // Add and delete the chart a few times
 290     // Without the fix in place, this test would have crashed here
 291     for (size_t i = 0; i < 5; ++i)
 292     {
 293         executeMacro("vnd.sun.Star.script:Standard.Module1.DrawGraph?language=Basic&location=document");
 294
 295         CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pPage->GetObjCount());
 296
 297         executeMacro("vnd.sun.Star.script:Standard.Module1.DeleteGraph?language=Basic&location=document");
 298
 299         CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(0), pPage->GetObjCount());
 300     }
 301 }
 302
 303 void ScMacrosTest::testTdf142033()
 304 {
 305     loadFromURL(u"tdf142033.ods");
 306
 307     executeMacro("vnd.sun.Star.script:Standard.Module1.display_bug?language=Basic&location=document");
 308
 309     // Export to ODS
 310     saveAndReload("calc8");
 311
 312     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 313
 314     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 315     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 316     ScDocument& rDoc = pDocSh->GetDocument();
 317
 318     CPPUNIT_ASSERT_EQUAL(OUString("string no newlines"), rDoc.GetString(ScAddress(0,0,0)));
 319     CPPUNIT_ASSERT_EQUAL(OUString("string no newlines"), rDoc.GetString(ScAddress(0,1,0)));
 320
 321     // Without the fix in place, this test would have failed with
 322     // - Expected: string with
 323     // newlines
 324     // - Actual  : string withnewlines
 325     CPPUNIT_ASSERT_EQUAL(OUString(u"string with" + OUStringChar(u'\xA') + u"newlines"), rDoc.GetString(ScAddress(1,0,0)));
 326     CPPUNIT_ASSERT_EQUAL(OUString(u"string with" + OUStringChar(u'\xA') + u"newlines"), rDoc.GetString(ScAddress(1,1,0)));
 327 }
 328
 329 void ScMacrosTest::testPasswordProtectedUnicodeString()
 330 {
 331     const OUString sCorrectString(u"English Русский 中文");
 332     static const OUStringLiteral sMacroURL(
 333         u"vnd.sun.Star.script:Protected.Module1.TestUnicodeString?language=Basic&location=document");
 334     static const OUStringLiteral sLibName(u"Protected");
 335
 336     loadFromURL(u"tdf57113.ods");
 337
 338     // Check that loading password-protected macro image correctly loads Unicode strings
 339     {
 340         Any aRet = executeMacro(sMacroURL);
 341
 342         OUString aReturnValue;
 343         aRet >>= aReturnValue;
 344         CPPUNIT_ASSERT_EQUAL(sCorrectString, aReturnValue);
 345     }
 346
 347     // Unlock and load the library, to regenerate the image on save
 348     css::uno::Reference<css::document::XEmbeddedScripts> xES(mxComponent, UNO_QUERY_THROW);
 349     css::uno::Reference<css::script::XLibraryContainer> xLC(xES->getBasicLibraries(),
 350                                                             UNO_QUERY_THROW);
 351     css::uno::Reference<css::script::XLibraryContainerPassword> xPasswd(xLC, UNO_QUERY_THROW);
 352     CPPUNIT_ASSERT(xPasswd->isLibraryPasswordProtected(sLibName));
 353     CPPUNIT_ASSERT(!xPasswd->isLibraryPasswordVerified(sLibName));
 354     CPPUNIT_ASSERT(xPasswd->verifyLibraryPassword(sLibName, "password"));
 355     xLC->loadLibrary(sLibName);
 356     CPPUNIT_ASSERT(xLC->isLibraryLoaded(sLibName));
 357
 358     // Now check that saving stores Unicode data correctly in image's string pool
 359     saveAndReload("calc8");
 360
 361     {
 362         Any aRet = executeMacro(sMacroURL);
 363
 364         OUString aReturnValue;
 365         aRet >>= aReturnValue;
 366         CPPUNIT_ASSERT_EQUAL(sCorrectString, aReturnValue);
 367     }
 368 }
 369
 370 void ScMacrosTest::testPasswordProtectedArrayInUserType()
 371 {
 372     static const OUStringLiteral sMacroURL(
 373         u"vnd.sun.Star.script:Protected.Module1.TestMyType?language=Basic&location=document");
 374     static const OUStringLiteral sLibName(u"Protected");
 375
 376     loadFromURL(u"ProtectedArrayInCustomType.ods");
 377
 378     // Check that loading password-protected macro image correctly loads array bounds
 379     {
 380         Any aRet = executeMacro(sMacroURL);
 381
 382         sal_Int16 nReturnValue;
 383         aRet >>= nReturnValue;
 384         CPPUNIT_ASSERT_EQUAL(sal_Int16(1), nReturnValue);
 385     }
 386
 387     // Unlock and load the library, to regenerate the image on save
 388     css::uno::Reference<css::document::XEmbeddedScripts> xES(mxComponent, UNO_QUERY_THROW);
 389     css::uno::Reference<css::script::XLibraryContainer> xLC(xES->getBasicLibraries(),
 390         UNO_QUERY_THROW);
 391     css::uno::Reference<css::script::XLibraryContainerPassword> xPasswd(xLC, UNO_QUERY_THROW);
 392     CPPUNIT_ASSERT(xPasswd->isLibraryPasswordProtected(sLibName));
 393     CPPUNIT_ASSERT(!xPasswd->isLibraryPasswordVerified(sLibName));
 394     CPPUNIT_ASSERT(xPasswd->verifyLibraryPassword(sLibName, "password"));
 395     xLC->loadLibrary(sLibName);
 396     CPPUNIT_ASSERT(xLC->isLibraryLoaded(sLibName));
 397
 398     // Now check that saving stores array bounds correctly
 399     saveAndReload("calc8");
 400
 401     {
 402         Any aRet = executeMacro(sMacroURL);
 403
 404         sal_Int16 nReturnValue;
 405         aRet >>= nReturnValue;
 406         CPPUNIT_ASSERT_EQUAL(sal_Int16(1), nReturnValue);
 407     }
 408 }
 409
 410 void ScMacrosTest::testTdf114427()
 411 {
 412     loadFromURL(u"tdf114427.ods");
 413
 414     uno::Reference< sheet::XSpreadsheetDocument > xDoc(mxComponent, UNO_QUERY_THROW);
 415     uno::Reference< container::XIndexAccess > xIA(xDoc->getSheets(), UNO_QUERY_THROW);
 416     uno::Reference< drawing::XDrawPageSupplier > xDrawPageSupplier( xIA->getByIndex(0), UNO_QUERY_THROW);
 417     uno::Reference< container::XIndexAccess > xDraws(xDrawPageSupplier->getDrawPage(), UNO_QUERY_THROW);
 418     CPPUNIT_ASSERT_EQUAL(static_cast<sal_Int32>(1), xDraws->getCount());
 419
 420     // Without the fix in place, it would have crashed here
 421     executeMacro("vnd.sun.Star.script:Standard.Module1.DeletingFrame?language=Basic&location=document");
 422
 423     CPPUNIT_ASSERT_EQUAL(static_cast<sal_Int32>(0), xDraws->getCount());
 424 }
 425
 426 void ScMacrosTest::testTdf131296_legacy()
 427 {
 428     // For legacy password-protected library images, we must correctly get the constants' values,
 429     // and also - for Integer - the type.
 430     const std::vector<std::pair<OUString, OUString>> aTests({
 431         { "TestIntConst", "Integer: 123" },
 432         { "TestLongConst", "Double: 123" },
 433         { "TestSingleConst", "Double: 123" },
 434         { "TestDoubleConst", "Double: 123" },
 435     });
 436
 437     loadFromURL(u"tdf131296_legacy.ods");
 438     {
 439         for (auto& [sTestName, sExpected] : aTests)
 440         {
 441             Any aRet = executeMacro("vnd.sun.Star.script:Protected.Module1." + sTestName
 442                                             + "?language=Basic&location=document");
 443
 444             OUString aReturnValue;
 445             aRet >>= aReturnValue;
 446             CPPUNIT_ASSERT_EQUAL_MESSAGE(sTestName.toUtf8().getStr(), sExpected, aReturnValue);
 447         }
 448     }
 449 }
 450
 451 void ScMacrosTest::testTdf131296_new()
 452 {
 453     // For new password-protected library images, we must correctly get both the constants' values
 454     // and their types.
 455     const std::vector<std::pair<OUString, OUString>> aTests({
 456         { "TestIntConst", "Integer: 123" },
 457         { "TestLongConst", "Long: 123" },
 458         { "TestSingleConst", "Single: 123" },
 459         { "TestDoubleConst", "Double: 123" },
 460         { "TestCurrencyConst", "Currency: 123.0000" },
 461     });
 462
 463     loadFromURL(u"tdf131296_new.ods");
 464     {
 465         for (auto& [sTestName, sExpected] : aTests)
 466         {
 467             Any aRet = executeMacro("vnd.sun.Star.script:Protected.Module1." + sTestName
 468                                             + "?language=Basic&location=document");
 469
 470             OUString aReturnValue;
 471             aRet >>= aReturnValue;
 472             CPPUNIT_ASSERT_EQUAL_MESSAGE(sTestName.toUtf8().getStr(), sExpected, aReturnValue);
 473         }
 474     }
 475 }
 476
 477 void ScMacrosTest::testTdf46119()
 478 {
 479     loadFromURL(u"tdf46119.ods");
 480
 481     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 482
 483     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 484     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 485     ScDocument& rDoc = pDocSh->GetDocument();
 486
 487     executeMacro("vnd.sun.Star.script:Standard.Module1.Main?language=Basic&location=document");
 488
 489     CPPUNIT_ASSERT_EQUAL(OUString("0.074"), rDoc.GetString(ScAddress(2, 24, 0)));
 490     CPPUNIT_ASSERT_EQUAL(OUString("0.067"), rDoc.GetString(ScAddress(2, 25, 0)));
 491     CPPUNIT_ASSERT_EQUAL(OUString("0.273"), rDoc.GetString(ScAddress(2, 26, 0)));
 492     CPPUNIT_ASSERT_EQUAL(OUString("0.259"), rDoc.GetString(ScAddress(2, 27, 0)));
 493
 494     CPPUNIT_ASSERT_EQUAL(OUString("0.097"), rDoc.GetString(ScAddress(3, 24, 0)));
 495     CPPUNIT_ASSERT_EQUAL(OUString("0.087"), rDoc.GetString(ScAddress(3, 25, 0)));
 496     CPPUNIT_ASSERT_EQUAL(OUString("0.311"), rDoc.GetString(ScAddress(3, 26, 0)));
 497     CPPUNIT_ASSERT_EQUAL(OUString("0.296"), rDoc.GetString(ScAddress(3, 27, 0)));
 498
 499     CPPUNIT_ASSERT_EQUAL(OUString("0.149"), rDoc.GetString(ScAddress(4, 24, 0)));
 500     CPPUNIT_ASSERT_EQUAL(OUString("0.134"), rDoc.GetString(ScAddress(4, 25, 0)));
 501     CPPUNIT_ASSERT_EQUAL(OUString("0.386"), rDoc.GetString(ScAddress(4, 26, 0)));
 502     CPPUNIT_ASSERT_EQUAL(OUString("0.366"), rDoc.GetString(ScAddress(4, 27, 0)));
 503 }
 504
 505 void ScMacrosTest::testTdf128218()
 506 {
 507     loadFromURL(u"tdf128218.ods");
 508
 509     Any aRet = executeMacro("vnd.sun.Star.script:Standard.Module1.TestRAND?language=Basic&location=document");
 510
 511     OUString aReturnValue;
 512     aRet >>= aReturnValue;
 513
 514     // Without the fix in place, this test would have failed with
 515     // - Expected: Double
 516     // - Actual  : Object()
 517
 518     CPPUNIT_ASSERT_EQUAL(OUString("Double"), aReturnValue);
 519 }
 520
 521 void ScMacrosTest::testTdf71271()
 522 {
 523     mxComponent = loadFromDesktop("private:factory/scalc");
 524
 525     {
 526         uno::Reference<sheet::XSpreadsheetDocument> xDoc(mxComponent, uno::UNO_QUERY_THROW);
 527         uno::Reference<container::XIndexAccess> xIndex(xDoc->getSheets(), uno::UNO_QUERY_THROW);
 528         uno::Reference<sheet::XSpreadsheet> xSheet(xIndex->getByIndex(0), uno::UNO_QUERY_THROW);
 529         uno::Reference<beans::XPropertySet> xProps(xSheet, uno::UNO_QUERY_THROW);
 530         xProps->setPropertyValue("CodeName", uno::Any(OUString("NewCodeName")));
 531     }
 532
 533     saveAndReload("");
 534
 535     {
 536         uno::Reference<sheet::XSpreadsheetDocument> xDoc(mxComponent, uno::UNO_QUERY_THROW);
 537         uno::Reference<container::XIndexAccess> xIndex(xDoc->getSheets(), uno::UNO_QUERY_THROW);
 538         uno::Reference<sheet::XSpreadsheet> xSheet(xIndex->getByIndex(0), uno::UNO_QUERY_THROW);
 539         OUString sCodeName;
 540         uno::Reference<beans::XPropertySet> xProps(xSheet, uno::UNO_QUERY_THROW);
 541         // Without the fix in place the codename would not have been saved
 542         xProps->getPropertyValue("CodeName") >>= sCodeName;
 543         CPPUNIT_ASSERT_EQUAL(OUString("NewCodeName"), sCodeName);
 544     }
 545 }
 546
 547 void ScMacrosTest::testTdf43003()
 548 {
 549     loadFromURL(u"tdf43003.ods");
 550
 551     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 552     CPPUNIT_ASSERT(pFoundShell);
 553
 554     ScDocShellRef xDocSh = dynamic_cast<ScDocShell*>(pFoundShell);
 555     CPPUNIT_ASSERT(xDocSh);
 556
 557     ScDocument& rDoc = xDocSh->GetDocument();
 558
 559     // Without the fix in place, the values of the specified cells won't be changed
 560     rDoc.SetValue(ScAddress(0, 0, 0), 2);
 561     CPPUNIT_ASSERT_EQUAL(3.0, rDoc.GetValue(ScAddress(1, 0, 0)));
 562     CPPUNIT_ASSERT_EQUAL(4.0, rDoc.GetValue(ScAddress(2, 0, 0)));
 563 }
 564
 565
 566 void ScMacrosTest::testTdf75263()
 567 {
 568     loadFromURL(u"tdf75263.xlsm");
 569
 570     {
 571         SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 572         CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 573         ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 574         ScDocument& rDoc = pDocSh->GetDocument();
 575         rDoc.CalcAll();
 576
 577         // A1 contains formula with user-defined function, and the function is defined in VBA.
 578         CPPUNIT_ASSERT_EQUAL(OUString(u"проба"), rDoc.GetString(ScAddress(0, 0, 0)));
 579     }
 580
 581     saveAndReload("Calc MS Excel 2007 VBA XML");
 582
 583     {
 584         SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 585         CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 586         ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 587         ScDocument& rDoc = pDocSh->GetDocument();
 588         rDoc.CalcAll();
 589
 590         // Without the accompanying fix in place, this test would have failed with:
 591         // - Expected: проба (sample)
 592         // - Actual  : ?????
 593         CPPUNIT_ASSERT_EQUAL(OUString(u"проба"), rDoc.GetString(ScAddress(0, 0, 0)));
 594     }
 595 }
 596
 597 void ScMacrosTest::testTdf133887()
 598 {
 599     loadFromURL(u"tdf133887.ods");
 600
 601     css::uno::Any aRet;
 602     css::uno::Sequence<sal_Int16> aOutParamIndex;
 603     css::uno::Sequence<css::uno::Any> aOutParam;
 604     css::uno::Sequence<css::uno::Any> aParams{ css::uno::Any(sal_Int16(0)) };
 605
 606     SfxObjectShell::CallXScript(
 607         mxComponent,
 608         "vnd.sun.Star.script:Standard.Module1.TestInvoke?language=Basic&location=document", aParams,
 609         aRet, aOutParamIndex, aOutParam);
 610
 611     double aReturnValue;
 612     aOutParam[0] >>= aReturnValue;
 613
 614     // Without the fix in place, this test would have failed with
 615     // - Expected: 6.75
 616     // - Actual  : 7
 617
 618     CPPUNIT_ASSERT_EQUAL(6.75, aReturnValue);
 619 }
 620
 621 void ScMacrosTest::testTdf133889()
 622 {
 623     loadFromURL(u"tdf133889.ods");
 624
 625     css::uno::Any aRet;
 626     css::uno::Sequence<sal_Int16> aOutParamIndex;
 627     css::uno::Sequence<css::uno::Any> aOutParam;
 628     css::uno::Sequence<css::uno::Any> aParams{ css::uno::Any(sal_Int32(0)) };
 629
 630     SfxObjectShell::CallXScript(
 631         mxComponent,
 632         "vnd.sun.Star.script:Standard.Module1.TestInvoke?language=Basic&location=document", aParams,
 633         aRet, aOutParamIndex, aOutParam);
 634
 635     sal_Int32 aReturnValue;
 636     aOutParam[0] >>= aReturnValue;
 637
 638     // Without the fix in place, this test would have failed with
 639     // - Expected: 100000
 640     // - Actual  : 0
 641
 642     CPPUNIT_ASSERT_EQUAL(sal_Int32(100000), aReturnValue);
 643 }
 644
 645 void ScMacrosTest::testTdf143582()
 646 {
 647     loadFromURL(u"tdf143582.ods");
 648
 649     Any aRet = executeMacro("vnd.sun.Star.script:Standard.Module1.TestScriptInvoke?language=Basic&location=document");
 650
 651     OUString aReturnValue;
 652     aRet >>= aReturnValue;
 653
 654     // Without the fix in place, this test would have failed with
 655     // - Expected: Test6
 656     // - Actual  : TeTest8
 657     CPPUNIT_ASSERT_EQUAL(OUString("Test6"), aReturnValue);
 658 }
 659
 660 void ScMacrosTest::testTdf144085()
 661 {
 662     loadFromURL(u"tdf144085.ods");
 663
 664     Any aRet = executeMacro("vnd.sun.Star.script:Standard.Module1.TestScriptInvoke?language=Basic&location=document");
 665
 666     OUString aReturnValue;
 667     aRet >>= aReturnValue;
 668
 669     // Without the fix in place, this test would have failed with
 670     // - Expected: $Sheet1.$B$5:$E$17
 671     // - Actual  : $Sheet1.$B$5:$C$10
 672     CPPUNIT_ASSERT_EQUAL(OUString("$Sheet1.$B$5:$E$17"), aReturnValue);
 673 }
 674
 675 void ScMacrosTest::testTdf125800()
 676 {
 677     loadFromURL(u"tdf125800.ods");
 678
 679     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 680
 681     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 682     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 683     ScDocument& rDoc = pDocSh->GetDocument();
 684
 685     ScConditionalFormat* pFormat = rDoc.GetCondFormat(1, 2, 0);
 686     CPPUNIT_ASSERT(!pFormat);
 687
 688     // Without the fix in place, this test would have failed with
 689     // - Expression: false
 690     // - Unexpected dialog:  Error: Inadmissible value or data type. Index out of defined range.
 691     Any aRet = executeMacro("vnd.sun.Star.script:Standard.cf.doItForThisSheetindexThisRange?language=Basic&location=document");
 692
 693     OUString aReturnValue;
 694     aRet >>= aReturnValue;
 695
 696     pFormat = rDoc.GetCondFormat(1, 2, 0);
 697     CPPUNIT_ASSERT(pFormat);
 698
 699     const ScFormatEntry* pEntry = pFormat->GetEntry(0);
 700     CPPUNIT_ASSERT(pEntry);
 701     CPPUNIT_ASSERT_EQUAL(ScFormatEntry::Type::Condition, pEntry->GetType());
 702
 703     const ScCondFormatEntry* pCondition = static_cast<const ScCondFormatEntry*>(pEntry);
 704     CPPUNIT_ASSERT_EQUAL(ScConditionMode::Direct, pCondition->GetOperation());
 705 }
 706
 707 void ScMacrosTest::testTdf130307()
 708 {
 709     loadFromURL(u"tdf130307.ods");
 710
 711     Any aRet = executeMacro("vnd.sun.Star.script:Standard.Module1.ForEachSheets?language=Basic&location=document");
 712
 713     OUString aReturnValue;
 714     aRet >>= aReturnValue;
 715
 716     // Without the fix in place, this test would have crashed here
 717     CPPUNIT_ASSERT_EQUAL(OUString("Sheet1Sheet2"), aReturnValue);
 718 }
 719
 720 void ScMacrosTest::testTdf144970()
 721 {
 722     loadFromURL(u"tdf144970.ods");
 723
 724     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 725
 726     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 727     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 728     ScDocument& rDoc = pDocSh->GetDocument();
 729
 730     formula::FormulaGrammar::Grammar eGram = formula::FormulaGrammar::GRAM_ENGLISH_XL_A1;
 731     rDoc.SetGrammar(eGram);
 732
 733     ScDrawLayer* pDrawLayer = rDoc.GetDrawLayer();
 734     const SdrPage* pPage = pDrawLayer->GetPage(0);
 735
 736     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(0), pPage->GetObjCount());
 737
 738     // Without the fix in place, this test would have failed with
 739     // - Expression: false
 740     // - Unexpected dialog:  Error: BASIC runtime error.
 741     // An exception occurred
 742     // Type: com.sun.star.lang.IllegalArgumentException
 743     executeMacro("vnd.sun.Star.script:Standard.Module1.Main?language=Basic&location=document");
 744
 745     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pPage->GetObjCount());
 746 }
 747
 748 void ScMacrosTest::testTdf138646()
 749 {
 750     loadFromURL(u"tdf138646.ods");
 751
 752     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 753     CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
 754
 755     ScDocShell* pDocSh = dynamic_cast<ScDocShell*>(pFoundShell);
 756     CPPUNIT_ASSERT(pDocSh);
 757
 758     // Without the fix in place, changing the grammar from GRAM_NATIVE to either GRAM_NATIVE_XL_A1
 759     // or GRAM_NATIVE_XL_R1C1 would cause a Basic exception/error in the following script.
 760     pDocSh->GetDocument().SetGrammar(formula::FormulaGrammar::Grammar::GRAM_NATIVE_XL_R1C1);
 761
 762     const std::vector<std::pair<OUString, OUString>> aTests({
 763         { "GlobalNamedCell", "GlobalNamedCell" },
 764         { "GlobalNamedCellSheet", "GlobalNamedCell" },
 765         { "LocalNamedCell", "LocalNamedCell" },
 766         { "LocalNamedCellAccessError", "Exception" }
 767     });
 768
 769     {
 770         for (auto& [sTestName, sExpected] : aTests)
 771         {
 772             Any aRet = executeMacro("vnd.sun.Star.script:Standard.Module1." + sTestName
 773                                             + "?language=Basic&location=document");
 774
 775             OUString aReturnValue;
 776             aRet >>= aReturnValue;
 777             CPPUNIT_ASSERT_EQUAL_MESSAGE(sTestName.toUtf8().getStr(), sExpected, aReturnValue);
 778         }
 779     }
 780 }
 781
 782 void ScMacrosTest::testTdf105558()
 783 {
 784     loadFromURL(u"tdf105558.ods");
 785
 786     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 787     CPPUNIT_ASSERT(pFoundShell);
 788
 789     ScDocShellRef xDocSh = dynamic_cast<ScDocShell*>(pFoundShell);
 790     CPPUNIT_ASSERT(xDocSh);
 791     ScDocument& rDoc = xDocSh->GetDocument();
 792
 793     // Without the fix in place, this test would have failed with
 794     // - Expected: 5.5
 795     // - Actual  : 0
 796     CPPUNIT_ASSERT_EQUAL(5.5, rDoc.GetValue(ScAddress(0, 0, 0)));
 797 }
 798
 799 void ScMacrosTest::testTdf107572()
 800 {
 801     mxComponent = loadFromDesktop("private:factory/scalc");
 802
 803     // insert initial library
 804     css::uno::Reference<css::document::XEmbeddedScripts> xDocScr(mxComponent, UNO_QUERY_THROW);
 805     auto xLibs = xDocScr->getBasicLibraries();
 806     auto xLibrary = xLibs->createLibrary("TestLibrary");
 807     xLibrary->insertByName(
 808         "TestModule",
 809         uno::Any(
 810             OUString("Function Main\n"
 811                      "  thisComponent.Sheets(0).getCellRangeByName(\"A1:F14\").autoformat(\"Default\")\n"
 812                      "End Function\n")));
 813
 814     SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
 815     ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
 816     CPPUNIT_ASSERT(pDocSh);
 817
 818     // Without the fix in place, this test would have crashed
 819     executeMacro("vnd.sun.Star.script:TestLibrary.TestModule.Main?language=Basic&location=document");
 820
 821     ScDocument& rDoc = pDocSh->GetDocument();
 822
 823     //Check the autoformat has been applied
 824     for (SCCOL i = 0; i < 5; ++i)
 825     {
 826         const ScPatternAttr* pAttr = rDoc.GetPattern(i, 0, 0);
 827         const SfxPoolItem& rItem = pAttr->GetItem(ATTR_BACKGROUND);
 828         const SvxBrushItem& rBackground = static_cast<const SvxBrushItem&>(rItem);
 829         const Color& rColor = rBackground.GetColor();
 830
 831         CPPUNIT_ASSERT_EQUAL(Color(0x0, 0x0, 0x80), rColor);
 832     }
 833
 834     for (SCROW i = 1; i < 13; ++i)
 835     {
 836         const ScPatternAttr* pAttr = rDoc.GetPattern(0, i, 0);
 837         const SfxPoolItem& rItem = pAttr->GetItem(ATTR_BACKGROUND);
 838         const SvxBrushItem& rBackground = static_cast<const SvxBrushItem&>(rItem);
 839         const Color& rColor = rBackground.GetColor();
 840
 841         CPPUNIT_ASSERT_EQUAL(Color(0x4d, 0x4d, 0x4d), rColor);
 842
 843         const ScPatternAttr* pAttr2 = rDoc.GetPattern(5, i, 0);
 844         const SfxPoolItem& rItem2 = pAttr2->GetItem(ATTR_BACKGROUND);
 845         const SvxBrushItem& rBackground2 = static_cast<const SvxBrushItem&>(rItem2);
 846         const Color& rColor2 = rBackground2.GetColor();
 847
 848         CPPUNIT_ASSERT_EQUAL(Color(0xcc, 0xcc, 0xcc), rColor2);
 849     }
 850 }
 851
 852 void ScMacrosTest::testShapeLayerId()
 853 {
 854     mxComponent = loadFromDesktop("private:factory/scalc");
 855
 856     // insert initial library
 857     css::uno::Reference<css::document::XEmbeddedScripts> xDocScr(mxComponent, UNO_QUERY_THROW);
 858     auto xLibs = xDocScr->getBasicLibraries();
 859     auto xLibrary = xLibs->createLibrary("TestLibrary");
 860     xLibrary->insertByName(
 861         "TestModule",
 862         uno::Any(
 863             OUString("Function TestLayerID\n"
 864                      "  xShape = thisComponent.createInstance(\"com.sun.star.drawing.TextShape\")\n"
 865                      "  thisComponent.DrawPages(0).Add(xShape)\n"
 866                      "  origID = xShape.LayerID\n"
 867                      "  On Error Goto handler\n"
 868                      "  xShape.LayerID = 257 ' 1 if wrongly converted to unsigned 8-bit type\n"
 869                      "  TestLayerID = origID & \" \" & xShape.LayerID ' Should not happen\n"
 870                      "  Exit Function\n"
 871                      "handler:\n"
 872                      "  ' This is expected to happen\n"
 873                      "  TestLayerID = origID & \" Expected runtime error happened\"\n"
 874                      "End Function\n")));
 875
 876     Any aRet = executeMacro("vnd.sun.Star.script:TestLibrary.TestModule.TestLayerID?language=Basic&location=document");
 877     // Without the fix in place, this test would have failed in non-debug builds with
 878     // - Expected : <Any: (string) 0 Expected runtime error happened>
 879     // - Actual   : <Any: (string) 0 1>
 880     // In debug builds, it would crash on assertion inside strong_int ctor.
 881     // The LayerID property of com.sun.star.drawing.Shape service has 'short' IDL type.
 882     // The expected run-time error is because there are only 5 layers there.
 883     CPPUNIT_ASSERT_EQUAL(Any(OUString("0 Expected runtime error happened")), aRet);
 884 }
 885
 886 void ScMacrosTest::testFunctionAccessIndirect()
 887 {
 888     OUString aFileName = loadFromURL(u"tdf120161.ods"); // just some document with known values in cells
 889
 890     const OUString aReference = "'" + aFileName + "'#$Sheet1.A1";
 891
 892     css::uno::Reference<css::sheet::XFunctionAccess> xFunc(
 893         comphelper::getProcessServiceFactory()->createInstance("com.sun.star.sheet.FunctionAccess"),
 894         UNO_QUERY_THROW);
 895
 896     // tdf#148040: without the fix in place, this would have failed with:
 897     //   An uncaught exception of type com.sun.star.lang.IllegalArgumentException
 898     // because of disallowed external link update (needed to obtain the cell value).
 899     css::uno::Any aResult = xFunc->callFunction("INDIRECT", {css::uno::Any(aReference)});
 900     CPPUNIT_ASSERT_EQUAL(css::uno::Any(OUString("a1")), aResult);
 901 }
 902
 903 void ScMacrosTest::testTdf147122()
 904 {
 905     mxComponent = loadFromDesktop("private:factory/scalc");
 906
 907     css::uno::Reference<css::document::XEmbeddedScripts> xDocScr(mxComponent, UNO_QUERY_THROW);
 908     auto xLibs = xDocScr->getBasicLibraries();
 909     auto xLibrary = xLibs->createLibrary("TestLibrary");
 910     xLibrary->insertByName(
 911         "TestModule",
 912         uno::Any(
 913             OUString("Function TestMergedSelection\n"
 914                      // Insert test string into cell A1
 915                      "  oActiveSheet = ThisComponent.CurrentController.ActiveSheet\n"
 916                      "  oActiveCell = oActiveSheet.getCellRangeByName(\"A1\")\n"
 917                      "  oActiveCell.setString(\"This is a test\")\n"
 918                      // Merge A1:B2 cell range and return the content of the merged range
 919                      "  oRange = oActiveSheet.getCellRangeByName(\"A1:B2\")\n"
 920                      "  ThisComponent.getCurrentController.Select(oRange)\n"
 921                      "  oActiveCell = ThisComponent.CurrentSelection\n"
 922                      "  oActiveCell.Merge(True)\n"
 923                      "  TestMergedSelection = ThisComponent.getCurrentSelection().getString()\n"
 924                      "End Function\n")));
 925
 926     Any aRet = executeMacro("vnd.sun.Star.script:TestLibrary.TestModule.TestMergedSelection?"
 927                             "language=Basic&location=document");
 928     // Without the fix in place, this test would have failed with
 929     // - Expression: false
 930     // - Unexpected dialog: Error: BASIC runtime error.
 931     // Property or method not found: getString.
 932     CPPUNIT_ASSERT_EQUAL(Any(OUString("This is a test")), aRet);
 933 }
 934
 935 ScMacrosTest::ScMacrosTest()
 936       : UnoApiXmlTest("/sc/qa/extras/testdocuments")
 937 {
 938 }
 939
 940 CPPUNIT_TEST_SUITE_REGISTRATION(ScMacrosTest);
 941
 942 CPPUNIT_PLUGIN_IMPLEMENT();
 943
 944 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */