sc/CppunitTest_sc_macros_test.mk | 1 sc/qa/extras/testdocuments/MultiDocumentCopyPaste.xlsm |binary sc/qa/extras/testdocuments/SimpleCopyPaste.xlsm |binary sc/qa/extras/vba-macro-test.cxx | 148 +++++++++++++++++ sc/source/ui/vba/excelvbahelper.cxx | 25 +- sc/source/ui/vba/vbarange.cxx | 7 sc/source/ui/vba/vbaworkbooks.cxx | 11 - 7 files changed, 176 insertions(+), 16 deletions(-)
New commits: commit 54cda94175f372695c55576817e3ae14655598ea Author: Tomaž Vajngerl <[email protected]> AuthorDate: Tue Jan 18 15:34:49 2022 +0900 Commit: Xisco Fauli <[email protected]> CommitDate: Mon Jan 24 12:28:17 2022 +0100 sc: fix VBA Copy-Paste using same and separate document This fixes 2 issues with VBA copy-paste: - VBA command Range(..).Copy issue where the range wasn't selected when copying and the copied cells were from the previous selection. The Copy command now does the same selection as the Cut command. - VBA PasteSpecial issue where the wrong view was used to get the clip document. - VBA Workbooks.Add issue where the new workbook wasn't activated after it was created, which causes an issue when running tests, but not when running in LO application. The Add command does now the same as the Workbooks.Open command. All the issues are supported by new test cases. Change-Id: I36ec45c01f18f7f76e4f95a25a28402a6ee0e2e2 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/128720 Tested-by: Jenkins Reviewed-by: Tomaž Vajngerl <[email protected]> (cherry picked from commit 0c6ee963e1f089cb73e0c68a28af29d0f8d9e0df) Reviewed-on: https://gerrit.libreoffice.org/c/core/+/128678 Reviewed-by: Xisco Fauli <[email protected]> diff --git a/sc/CppunitTest_sc_macros_test.mk b/sc/CppunitTest_sc_macros_test.mk index b29c3fe03b54..37c498e81ec0 100644 --- a/sc/CppunitTest_sc_macros_test.mk +++ b/sc/CppunitTest_sc_macros_test.mk @@ -19,6 +19,7 @@ $(eval $(call gb_CppunitTest_use_common_precompiled_header,sc_macros_test)) $(eval $(call gb_CppunitTest_add_exception_objects,sc_macros_test, \ sc/qa/extras/macros-test \ + sc/qa/extras/vba-macro-test \ )) $(eval $(call gb_CppunitTest_use_libraries,sc_macros_test, \ diff --git a/sc/qa/extras/testdocuments/MultiDocumentCopyPaste.xlsm b/sc/qa/extras/testdocuments/MultiDocumentCopyPaste.xlsm new file mode 100644 index 000000000000..4f24cf4e663f Binary files /dev/null and b/sc/qa/extras/testdocuments/MultiDocumentCopyPaste.xlsm differ diff --git a/sc/qa/extras/testdocuments/SimpleCopyPaste.xlsm b/sc/qa/extras/testdocuments/SimpleCopyPaste.xlsm new file mode 100644 index 000000000000..6c71c75b345e Binary files /dev/null and b/sc/qa/extras/testdocuments/SimpleCopyPaste.xlsm differ diff --git a/sc/qa/extras/vba-macro-test.cxx b/sc/qa/extras/vba-macro-test.cxx new file mode 100644 index 000000000000..36aa84badd2d --- /dev/null +++ b/sc/qa/extras/vba-macro-test.cxx @@ -0,0 +1,148 @@ +/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ +/* + * This file is part of the LibreOffice project. + * + * This Source Code Form is subject to the terms of the Mozilla Public + * License, v. 2.0. If a copy of the MPL was not distributed with this + * file, You can obtain one at http://mozilla.org/MPL/2.0/. + */ + +#include <sal/config.h> +#include <test/unoapi_test.hxx> +#include <osl/file.hxx> +#include <sal/log.hxx> +#include <vcl/svapp.hxx> + +#include <docsh.hxx> +#include <document.hxx> +#include <attrib.hxx> +#include <scitems.hxx> + +#include <com/sun/star/sheet/XSpreadsheet.hpp> + +using namespace css; + +class VBAMacroTest : public UnoApiTest +{ +public: + uno::Reference<lang::XComponent> mxComponent; + + VBAMacroTest() + : UnoApiTest("/sc/qa/extras/testdocuments") + { + } + + virtual void tearDown() override + { + if (mxComponent.is()) + { + mxComponent->dispose(); + mxComponent.set(nullptr); + } + + test::BootstrapFixture::tearDown(); + } + + void testSimpleCopyAndPaste(); + void testMultiDocumentCopyAndPaste(); + + CPPUNIT_TEST_SUITE(VBAMacroTest); + CPPUNIT_TEST(testSimpleCopyAndPaste); + CPPUNIT_TEST(testMultiDocumentCopyAndPaste); + CPPUNIT_TEST_SUITE_END(); +}; + +void VBAMacroTest::testSimpleCopyAndPaste() +{ + // Copy-paste values in the same sheet + + // Range(Cells(4, 3), Cells(6, 3)).Copy + // Cells(4, 2).Activate + // ActiveCell.PasteSpecial xlValues + + OUString aFileName; + createFileURL(u"SimpleCopyPaste.xlsm", aFileName); + mxComponent = loadFromDesktop(aFileName, "com.sun.star.sheet.SpreadsheetDocument"); + + uno::Any aRet; + uno::Sequence<sal_Int16> aOutParamIndex; + uno::Sequence<uno::Any> aOutParam; + uno::Sequence<uno::Any> aParams; + + SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent); + + CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell); + ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell); + ScDocument& rDoc = pDocSh->GetDocument(); + + // Check state + CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(ScAddress(2, 3, 0))); + CPPUNIT_ASSERT_EQUAL(20.0, rDoc.GetValue(ScAddress(2, 4, 0))); + CPPUNIT_ASSERT_EQUAL(30.0, rDoc.GetValue(ScAddress(2, 5, 0))); + + CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 3, 0))); + CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 4, 0))); + CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 5, 0))); + + SfxObjectShell::CallXScript( + mxComponent, "vnd.sun.Star.script:VBAProject.Module1.test?language=Basic&location=document", + aParams, aRet, aOutParamIndex, aOutParam); + + // Copy from C4-C6 + CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(ScAddress(2, 3, 0))); + CPPUNIT_ASSERT_EQUAL(20.0, rDoc.GetValue(ScAddress(2, 4, 0))); + CPPUNIT_ASSERT_EQUAL(30.0, rDoc.GetValue(ScAddress(2, 5, 0))); + + // Paste to B4-B6 + CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(ScAddress(1, 3, 0))); + CPPUNIT_ASSERT_EQUAL(20.0, rDoc.GetValue(ScAddress(1, 4, 0))); + CPPUNIT_ASSERT_EQUAL(30.0, rDoc.GetValue(ScAddress(1, 5, 0))); +} + +void VBAMacroTest::testMultiDocumentCopyAndPaste() +{ + // Creates a new workbook (document) and copy-pastes values + // between the documents. + + // Set CurrentWB = ActiveWorkbook + // Workbooks.Add + // Set NewWB = ActiveWorkbook + // Cells(3, 2).Value = 200 + // Cells(4, 2).Value = 100 + // Range(Cells(3, 2), Cells(4, 2)).Copy + // CurrentWB.Activate + // Cells(2, 2).Activate + // ActiveCell.PasteSpecial xlValues + // ... + + OUString aFileName; + createFileURL(u"MultiDocumentCopyPaste.xlsm", aFileName); + mxComponent = loadFromDesktop(aFileName, "com.sun.star.sheet.SpreadsheetDocument"); + + uno::Any aRet; + uno::Sequence<sal_Int16> aOutParamIndex; + uno::Sequence<uno::Any> aOutParam; + uno::Sequence<uno::Any> aParams; + + SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent); + + CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell); + ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell); + ScDocument& rDoc = pDocSh->GetDocument(); + + CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 1, 0))); + CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 2, 0))); + CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 3, 0))); + + SfxObjectShell::CallXScript( + mxComponent, "vnd.sun.Star.script:VBAProject.Module1.test?language=Basic&location=document", + aParams, aRet, aOutParamIndex, aOutParam); + + CPPUNIT_ASSERT_EQUAL(200.0, rDoc.GetValue(ScAddress(1, 1, 0))); + CPPUNIT_ASSERT_EQUAL(100.0, rDoc.GetValue(ScAddress(1, 2, 0))); + CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 3, 0))); +} + +CPPUNIT_TEST_SUITE_REGISTRATION(VBAMacroTest); + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/vba/excelvbahelper.cxx b/sc/source/ui/vba/excelvbahelper.cxx index d337a0f6836e..37d3a5fcf1de 100644 --- a/sc/source/ui/vba/excelvbahelper.cxx +++ b/sc/source/ui/vba/excelvbahelper.cxx @@ -210,25 +210,28 @@ void implnPasteSpecial( const uno::Reference< frame::XModel>& xModel, InsertDele { PasteCellsWarningReseter resetWarningBox; - ScTabViewShell* pTabViewShell = getBestViewShell( xModel ); - ScDocShell* pDocShell = getDocShell( xModel ); - if ( !(pTabViewShell && pDocShell) ) + ScTabViewShell* pTabViewShell = getBestViewShell(xModel); + if (!pTabViewShell) + return; + + ScDocShell* pDocShell = getDocShell(xModel); + if (!pDocShell) return; ScViewData& rView = pTabViewShell->GetViewData(); vcl::Window* pWin = rView.GetActiveWin(); - if (pWin) + if (!pWin) + return; + + const ScTransferObj* pOwnClip = ScTransferObj::GetOwnClipboard(ScTabViewShell::GetClipData(pWin)); + if (pOwnClip) { - const ScTransferObj* pOwnClip = ScTransferObj::GetOwnClipboard(pDocShell->GetClipData()); - ScDocument* pDoc = nullptr; - if ( pOwnClip ) - pDoc = pOwnClip->GetDocument(); - pTabViewShell->PasteFromClip( nFlags, pDoc, + pTabViewShell->PasteFromClip(nFlags, pOwnClip->GetDocument(), nFunction, bSkipEmpty, bTranspose, false, - INS_NONE, InsertDeleteFlags::NONE, true ); + INS_NONE, InsertDeleteFlags::NONE, true); + pTabViewShell->CellContentChanged(); } - } ScDocShell* diff --git a/sc/source/ui/vba/vbarange.cxx b/sc/source/ui/vba/vbarange.cxx index 1cb307133cf7..75ae115ce9d4 100644 --- a/sc/source/ui/vba/vbarange.cxx +++ b/sc/source/ui/vba/vbarange.cxx @@ -2534,7 +2534,9 @@ ScVbaRange::Copy(const ::uno::Any& Destination) } else { - excel::implnCopy( getUnoModel() ); + uno::Reference<frame::XModel> xModel = getModelFromRange(mxRange); + Select(); + excel::implnCopy(getUnoModel()); } } @@ -2892,7 +2894,8 @@ ScVbaRange::PasteSpecial( const uno::Any& Paste, const uno::Any& Operation, cons InsertDeleteFlags nFlags = getPasteFlags(nPaste); ScPasteFunc nFormulaBits = getPasteFormulaBits(nOperation); - excel::implnPasteSpecial(pShell->GetModel(), nFlags,nFormulaBits,bSkipBlanks,bTranspose); + + excel::implnPasteSpecial(xModel, nFlags, nFormulaBits, bSkipBlanks, bTranspose); } uno::Reference< excel::XRange > diff --git a/sc/source/ui/vba/vbaworkbooks.cxx b/sc/source/ui/vba/vbaworkbooks.cxx index bd52292cfa5a..e3b608b33942 100644 --- a/sc/source/ui/vba/vbaworkbooks.cxx +++ b/sc/source/ui/vba/vbaworkbooks.cxx @@ -144,9 +144,14 @@ ScVbaWorkbooks::Add( const uno::Any& Template ) // need to set up the document modules ( and vba mode ) here excel::setUpDocumentModules( xSpreadDoc ); - if( xSpreadDoc.is() ) - return getWorkbook( mxContext, xSpreadDoc, mxParent ); - return uno::Any(); + if (!xSpreadDoc.is()) + return uno::Any(); + + uno::Any aRet = getWorkbook( mxContext, xSpreadDoc, mxParent ); + uno::Reference< excel::XWorkbook > xWBook( aRet, uno::UNO_QUERY ); + if (xWBook.is()) + xWBook->Activate(); + return aRet; } void SAL_CALL
