XLSX export: handle macros on button form controls

This builds on top of commit 1e3263a677b61c718d0fd1be15c066b933f7de18
(XLSX export: handle button form controls, 2021-07-01).

The binary XLS export already had code to turn Calc macro names into
Excel ones, reuse that for XLSX purposes.

Also fix the unwanted named range on export,
oox::xls::FormulaParser::importMacroName() mentions how XLSX doesn't
have matching named ranges for vba macros (while XLS has), mirror this
on the export side as well.

Change-Id: I877b6ba2c2e834a2327482da5cadcddf1b4672bb
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/118485
Reviewed-by: Miklos Vajna <vmiklos@collabora.com>
Tested-by: Jenkins

5 files changed, 72 insertions, 11 deletions

diff --git a/sc/CppunitTest_sc_macros_test.mk b/sc/CppunitTest_sc_macros_test.mk
index e0852b653598..b4f62624e693 100644
--- a/sc/CppunitTest_sc_macros_test.mk
+++ b/sc/CppunitTest_sc_macros_test.mk
@@ -12,6 +12,7 @@ $(eval $(call gb_CppunitTest_CppunitTest,sc_macros_test))
 $(eval $(call gb_CppunitTest_use_externals,sc_macros_test, \
     boost_headers \
     mdds_headers \
+    libxml2 \
 ))
 
 $(eval $(call gb_CppunitTest_use_common_precompiled_header,sc_macros_test))
@@ -37,6 +38,7 @@ $(eval $(call gb_CppunitTest_use_libraries,sc_macros_test, \
     sax \
     sb \
     sc \
+    scqahelper \
     sfx \
     sot \
     subsequenttest \
@@ -58,6 +60,7 @@ $(eval $(call gb_CppunitTest_use_libraries,sc_macros_test, \
 $(eval $(call gb_CppunitTest_set_include,sc_macros_test,\
     -I$(SRCDIR)/sc/source/ui/inc \
     -I$(SRCDIR)/sc/inc \
+    -I$(SRCDIR)/sc/qa/unit \
     $$(INCLUDE) \
 ))
 
diff --git a/sc/qa/extras/macros-test.cxx b/sc/qa/extras/macros-test.cxx
index fae1e4f71027..88336ef230ee 100644
--- a/sc/qa/extras/macros-test.cxx
+++ b/sc/qa/extras/macros-test.cxx
@@ -14,6 +14,7 @@
 #include <unotools/tempfile.hxx>
 #include <vcl/svapp.hxx>
 #include <editeng/borderline.hxx>
+#include <unotools/mediadescriptor.hxx>
 
 #include <docsh.hxx>
 #include <document.hxx>
@@ -25,13 +26,17 @@
 #include <com/sun/star/script/XLibraryContainerPassword.hpp>
 #include <com/sun/star/drawing/XDrawPageSupplier.hpp>
 
+#include <helper/xpath.hxx>
+
 using namespace ::com::sun::star;
 using namespace ::com::sun::star::uno;
 
 /* Implementation of Macros test */
 
-class ScMacrosTest : public UnoApiTest
+class ScMacrosTest : public UnoApiTest, public XmlTestTools
 {
+protected:
+    void registerNamespaces(xmlXPathContextPtr& pXmlXPathCtx) override;
 public:
     ScMacrosTest();
     void saveAndReload(css::uno::Reference<css::lang::XComponent>& xComponent,
@@ -59,6 +64,7 @@ public:
     void testTdf138646();
     void testTdf105558();
     void testTdf90278();
+    void testMacroButtonFormControlXlsxExport();
 
     CPPUNIT_TEST_SUITE(ScMacrosTest);
     CPPUNIT_TEST(testStarBasic);
@@ -84,10 +90,17 @@ public:
     CPPUNIT_TEST(testTdf138646);
     CPPUNIT_TEST(testTdf105558);
     CPPUNIT_TEST(testTdf90278);
+    CPPUNIT_TEST(testMacroButtonFormControlXlsxExport);
 
     CPPUNIT_TEST_SUITE_END();
 };
 
+void ScMacrosTest::registerNamespaces(xmlXPathContextPtr& pXmlXPathCtx)
+{
+    XmlTestTools::registerOOXMLNamespaces(pXmlXPathCtx);
+    XmlTestTools::registerODFNamespaces(pXmlXPathCtx);
+}
+
 void ScMacrosTest::saveAndReload(css::uno::Reference<css::lang::XComponent>& xComponent,
                                  const OUString& rFilter)
 {
@@ -471,6 +484,37 @@ void ScMacrosTest::testRowColumn()
     pDocSh->DoClose();
 }
 
+void ScMacrosTest::testMacroButtonFormControlXlsxExport()
+{
+    // Given a button form control with an associated macro:
+    OUString aFileName;
+    createFileURL(u"macro-button-form-control.xlsm", aFileName);
+    uno::Reference<lang::XComponent> xComponent = loadFromDesktop(aFileName, "com.sun.star.sheet.SpreadsheetDocument");
+
+    // When exporting to XLSM:
+    uno::Reference<frame::XStorable> xStorable(xComponent, uno::UNO_QUERY);
+    utl::MediaDescriptor aMediaDescriptor;
+    aMediaDescriptor["FilterName"] <<= OUString("Calc MS Excel 2007 VBA XML");
+    auto pTempFile = std::make_shared<utl::TempFile>();
+    pTempFile->EnableKillingFile();
+    xStorable->storeToURL(pTempFile->GetURL(), aMediaDescriptor.getAsConstPropertyValueList());
+    xComponent->dispose();
+
+    // Then make sure that the macro is associated with the control:
+    xmlDocUniquePtr pSheetDoc = XPathHelper::parseExport(pTempFile, m_xSFactory, "xl/worksheets/sheet1.xml");
+    CPPUNIT_ASSERT(pSheetDoc);
+    // Without the fix in place, this test would have failed with:
+    // - XPath '//x:controlPr' no attribute 'macro' exist
+    // i.e. the macro was lost on export.
+    assertXPath(pSheetDoc, "//x:controlPr", "macro", "Module1.Button1_Click");
+
+    // Then also make sure that there is no defined name for the macro, which is only needed for
+    // XLS:
+    xmlDocUniquePtr pWorkbookDoc = XPathHelper::parseExport(pTempFile, m_xSFactory, "xl/workbook.xml");
+    CPPUNIT_ASSERT(pWorkbookDoc);
+    assertXPath(pWorkbookDoc, "//x:workbook/definedNames", 0);
+}
+
 void ScMacrosTest::testTdf131562()
 {
     OUString aFileName;
diff --git a/sc/qa/extras/testdocuments/macro-button-form-control.xlsm b/sc/qa/extras/testdocuments/macro-button-form-control.xlsm
new file mode 100644
index 000000000000..e4e76b13ff5a
--- /dev/null
+++ b/sc/qa/extras/testdocuments/macro-button-form-control.xlsm
diff --git a/sc/source/filter/excel/xeescher.cxx b/sc/source/filter/excel/xeescher.cxx
index fcffbd7534b2..eafc30de8b0a 100644
--- a/sc/source/filter/excel/xeescher.cxx
+++ b/sc/source/filter/excel/xeescher.cxx
@@ -1095,11 +1095,12 @@ class VmlFormControlExporter : public oox::vml::VMLExport
     tools::Rectangle m_aAreaFrom;
     tools::Rectangle m_aAreaTo;
     OUString m_aLabel;
+    OUString m_aMacroName;
 
 public:
     VmlFormControlExporter(const sax_fastparser::FSHelperPtr& p, sal_uInt16 nObjType,
                            const tools::Rectangle& rAreaFrom, const tools::Rectangle& rAreaTo,
-                           const OUString& rLabel);
+                           const OUString& rLabel, const OUString& rMacroName);
 
 protected:
     using VMLExport::StartShape;
@@ -1112,12 +1113,13 @@ VmlFormControlExporter::VmlFormControlExporter(const sax_fastparser::FSHelperPtr
                                                sal_uInt16 nObjType,
                                                const tools::Rectangle& rAreaFrom,
                                                const tools::Rectangle& rAreaTo,
-                                               const OUString& rLabel)
+                                               const OUString& rLabel, const OUString& rMacroName)
     : VMLExport(p)
     , m_nObjType(nObjType)
     , m_aAreaFrom(rAreaFrom)
     , m_aAreaTo(rAreaTo)
     , m_aLabel(rLabel)
+    , m_aMacroName(rMacroName)
 {
 }
 
@@ -1159,6 +1161,11 @@ void VmlFormControlExporter::EndShape(sal_Int32 nShapeElement)
     aAnchor += ", " + OString::number(m_aAreaTo.Bottom());
     XclXmlUtils::WriteElement(pVmlDrawing, FSNS(XML_x, XML_Anchor), aAnchor);
 
+    if (!m_aMacroName.isEmpty())
+    {
+        XclXmlUtils::WriteElement(pVmlDrawing, FSNS(XML_x, XML_FmlaMacro), m_aMacroName);
+    }
+
     // XclExpOcxControlObj::WriteSubRecs() has the same fixed values.
     if (m_nObjType == EXC_OBJTYPE_BUTTON)
     {
@@ -1181,7 +1188,7 @@ void XclExpTbxControlObj::SaveVml(XclExpXmlStream& rStrm)
     // Unlike XclExpTbxControlObj::SaveXml(), this is not calculated in EMUs.
     lcl_GetFromTo(mrRoot, pObj->GetLogicRect(), GetTab(), aAreaFrom, aAreaTo);
     VmlFormControlExporter aFormControlExporter(rStrm.GetCurrentStream(), GetObjType(), aAreaFrom,
-                                                aAreaTo, msLabel);
+                                                aAreaTo, msLabel, GetMacroName());
     aFormControlExporter.AddSdrObject(*pObj, /*bIsFollowingTextFlow=*/false, /*eHOri=*/-1,
                                       /*eVOri=*/-1, /*eHRel=*/-1, /*eVRel=*/-1,
                                       /*pWrapAttrList=*/nullptr, /*bOOxmlExport=*/true);
@@ -1472,11 +1479,11 @@ void XclExpTbxControlObj::SaveSheetXml(XclExpXmlStream& rStrm, const OUString& a
             rWorksheet->startElement(FSNS(XML_mc, XML_Choice), XML_Requires, "x14");
 
             rWorksheet->startElement(XML_control, XML_shapeId, OString::number(mnShapeId).getStr(),
-                                     FSNS(XML_r, XML_id), aIdFormControlPr, XML_name, msLabel);
+                                     FSNS(XML_r, XML_id), aIdFormControlPr, XML_name, msCtrlName);
 
             rWorksheet->startElement(XML_controlPr, XML_defaultSize, "0", XML_print,
                                      mbPrint ? "true" : "false", XML_autoFill, "0", XML_autoPict,
-                                     "0");
+                                     "0", XML_macro, GetMacroName());
 
             rWorksheet->startElement(XML_anchor, XML_moveWithCells, "true", XML_sizeWithCells,
                                      "false");
@@ -1800,13 +1807,15 @@ void XclMacroHelper::WriteMacroSubRec( XclExpStream& rStrm )
         WriteFormulaSubRec( rStrm, EXC_ID_OBJMACRO, *mxMacroLink );
 }
 
+OUString XclMacroHelper::GetMacroName() const { return maMacroName; }
+
 bool
 XclMacroHelper::SetMacroLink( const ScriptEventDescriptor& rEvent, const XclTbxEventType& nEventType )
 {
-    OUString aMacroName = XclControlHelper::ExtractFromMacroDescriptor( rEvent, nEventType );
-    if( !aMacroName.isEmpty() )
+    maMacroName = XclControlHelper::ExtractFromMacroDescriptor(rEvent, nEventType);
+    if (!maMacroName.isEmpty())
     {
-        return SetMacroLink( aMacroName );
+        return SetMacroLink(maMacroName);
     }
     return false;
 }
@@ -1814,10 +1823,13 @@ XclMacroHelper::SetMacroLink( const ScriptEventDescriptor& rEvent, const XclTbxE
 bool
 XclMacroHelper::SetMacroLink( const OUString& rMacroName )
 {
-    if( !rMacroName.isEmpty() )
+    // OOXML documents do not store any defined name for VBA macros (while BIFF documents do).
+    bool bOOXML = GetOutput() == EXC_OUTPUT_XML_2007;
+    if (!rMacroName.isEmpty() && !bOOXML)
     {
         sal_uInt16 nExtSheet = GetLocalLinkManager().FindExtSheet( EXC_EXTSH_OWNDOC );
-        sal_uInt16 nNameIdx = GetNameManager().InsertMacroCall( rMacroName, true, false );
+        sal_uInt16 nNameIdx
+            = GetNameManager().InsertMacroCall(rMacroName, /*bVBasic=*/true, /*bFunc=*/false);
         mxMacroLink = GetFormulaCompiler().CreateNameXFormula( nExtSheet, nNameIdx );
         return true;
     }
diff --git a/sc/source/filter/inc/xeescher.hxx b/sc/source/filter/inc/xeescher.hxx
index f775571cac53..6ffc5d3bf1ab 100644
--- a/sc/source/filter/inc/xeescher.hxx
+++ b/sc/source/filter/inc/xeescher.hxx
@@ -194,6 +194,7 @@ protected:
 class XclMacroHelper : public XclExpControlHelper
 {
     XclTokenArrayRef    mxMacroLink;    /// Token array containing a link to an attached macro.
+    OUString maMacroName;
 
 public:
     explicit            XclMacroHelper( const XclExpRoot& rRoot );
@@ -207,6 +208,7 @@ public:
     /** Sets the name of a macro
         @return  true = The passed macro name has been found. */
     bool                SetMacroLink( const OUString& rMacro );
+    OUString GetMacroName() const;
 };
 
 class XclExpShapeObj : public XclObjAny, public XclMacroHelper