XLSX export: improve handling of checkbox (form controls)

This builds on top of commit fd238380ae7820f12ac1f7c52d0f7180a93f3ba3
(tdf#106181 XLSX export: output form controls, 2020-05-13) and adds the
missing VML version which seems to be mandated by Excel 2019.

It is not perfect (e.g. there is still an unwanted border around the
checkbox), but the checkbox has a correct position and its label is
readable, while it was just lost previously.

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

5 files changed, 131 insertions, 2 deletions

diff --git a/sc/qa/unit/data/xlsx/checkbox-form-control.xlsx b/sc/qa/unit/data/xlsx/checkbox-form-control.xlsx
new file mode 100644
index 000000000000..ad761a573aae
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/checkbox-form-control.xlsx
diff --git a/sc/qa/unit/subsequent_export-test2.cxx b/sc/qa/unit/subsequent_export-test2.cxx
index 1c088c813f21..eb6db21167d3 100644
--- a/sc/qa/unit/subsequent_export-test2.cxx
+++ b/sc/qa/unit/subsequent_export-test2.cxx
@@ -187,6 +187,7 @@ public:
     void testTdf139258_rotated_image();
     void testTdf126541_SheetVisibilityImportXlsx();
     void testTdf140431();
+    void testCheckboxFormControlXlsxExport();
 
     CPPUNIT_TEST_SUITE(ScExportTest2);
 
@@ -282,6 +283,7 @@ public:
     CPPUNIT_TEST(testTdf139258_rotated_image);
     CPPUNIT_TEST(testTdf126541_SheetVisibilityImportXlsx);
     CPPUNIT_TEST(testTdf140431);
+    CPPUNIT_TEST(testCheckboxFormControlXlsxExport);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -2301,6 +2303,24 @@ void ScExportTest2::testTdf140431()
     xDocSh->DoClose();
 }
 
+void ScExportTest2::testCheckboxFormControlXlsxExport()
+{
+    // Given a document that has a checkbox form control:
+    ScDocShellRef xShell = loadDoc(u"checkbox-form-control.", FORMAT_XLSX);
+    CPPUNIT_ASSERT(xShell.is());
+
+    // When exporting to XLSX:
+    std::shared_ptr<utl::TempFile> pXPathFile
+        = ScBootstrapFixture::exportTo(&(*xShell), FORMAT_XLSX);
+
+    // Then make sure its VML markup is written and it has a correct position + size:
+    xmlDocUniquePtr pDoc
+        = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/drawings/vmlDrawing1.vml");
+    // Without the fix in place, this test would have failed as there was no such stream.
+    CPPUNIT_ASSERT(pDoc);
+    assertXPathContent(pDoc, "/xml/v:shape/xx:ClientData/xx:Anchor", "1, 22, 3, 3, 3, 30, 6, 1");
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(ScExportTest2);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/filter/excel/xeescher.cxx b/sc/source/filter/excel/xeescher.cxx
index 1bc1a753acad..2f1253c0a16e 100644
--- a/sc/source/filter/excel/xeescher.cxx
+++ b/sc/source/filter/excel/xeescher.cxx
@@ -70,6 +70,7 @@
 #include <oox/export/chartexport.hxx>
 #include <oox/export/utils.hxx>
 #include <oox/token/namespaces.hxx>
+#include <oox/export/vmlexport.hxx>
 #include <memory>
 
 using namespace com::sun::star;
@@ -651,7 +652,8 @@ XclExpTbxControlObj::XclExpTbxControlObj( XclExpObjectManager& rRoot, Reference<
     mbScrollHor( false ),
     mbPrint( false ),
     mbVisible( false ),
-    mnShapeId( 0 )
+    mnShapeId( 0 ),
+    mrRoot(rRoot)
 {
     namespace FormCompType = css::form::FormComponentType;
     namespace AwtVisualEffect = css::awt::VisualEffect;
@@ -1084,6 +1086,100 @@ void XclExpTbxControlObj::setShapeId(sal_Int32 aShapeId)
     mnShapeId = aShapeId;
 }
 
+namespace
+{
+/// Handles the VML export of form controls (e.g. checkboxes).
+class VmlFormControlExporter : public oox::vml::VMLExport
+{
+    sal_uInt16 m_nObjType;
+    tools::Rectangle m_aAreaFrom;
+    tools::Rectangle m_aAreaTo;
+    OUString m_aLabel;
+
+public:
+    VmlFormControlExporter(const sax_fastparser::FSHelperPtr& p, sal_uInt16 nObjType,
+                           const tools::Rectangle& rAreaFrom, const tools::Rectangle& rAreaTo,
+                           const OUString& rLabel);
+
+protected:
+    using VMLExport::StartShape;
+    sal_Int32 StartShape() override;
+    using VMLExport::EndShape;
+    void EndShape(sal_Int32 nShapeElement) override;
+};
+
+VmlFormControlExporter::VmlFormControlExporter(const sax_fastparser::FSHelperPtr& p,
+                                               sal_uInt16 nObjType,
+                                               const tools::Rectangle& rAreaFrom,
+                                               const tools::Rectangle& rAreaTo,
+                                               const OUString& rLabel)
+    : VMLExport(p)
+    , m_nObjType(nObjType)
+    , m_aAreaFrom(rAreaFrom)
+    , m_aAreaTo(rAreaTo)
+    , m_aLabel(rLabel)
+{
+}
+
+sal_Int32 VmlFormControlExporter::StartShape()
+{
+    // Host control.
+    AddShapeAttribute(XML_type, "#_x0000_t201");
+    return VMLExport::StartShape();
+}
+
+void VmlFormControlExporter::EndShape(sal_Int32 nShapeElement)
+{
+    sax_fastparser::FSHelperPtr pVmlDrawing = GetFS();
+
+    pVmlDrawing->startElement(FSNS(XML_v, XML_textbox));
+    pVmlDrawing->startElement(XML_div);
+    pVmlDrawing->write(m_aLabel);
+    pVmlDrawing->endElement(XML_div);
+    pVmlDrawing->endElement(FSNS(XML_v, XML_textbox));
+
+    OString aObjectType;
+    switch (m_nObjType)
+    {
+        case EXC_OBJTYPE_CHECKBOX:
+            aObjectType = "Checkbox";
+            break;
+    }
+    pVmlDrawing->startElement(FSNS(XML_x, XML_ClientData), XML_ObjectType, aObjectType);
+    OString aAnchor = OString::number(m_aAreaFrom.Left());
+    aAnchor += ", " + OString::number(m_aAreaFrom.Top());
+    aAnchor += ", " + OString::number(m_aAreaFrom.Right());
+    aAnchor += ", " + OString::number(m_aAreaFrom.Bottom());
+    aAnchor += ", " + OString::number(m_aAreaTo.Left());
+    aAnchor += ", " + OString::number(m_aAreaTo.Top());
+    aAnchor += ", " + OString::number(m_aAreaTo.Right());
+    aAnchor += ", " + OString::number(m_aAreaTo.Bottom());
+    XclXmlUtils::WriteElement(pVmlDrawing, FSNS(XML_x, XML_Anchor), aAnchor);
+
+    // XclExpOcxControlObj::WriteSubRecs() has the same fixed value.
+    XclXmlUtils::WriteElement(pVmlDrawing, FSNS(XML_x, XML_TextVAlign), "Center");
+
+    pVmlDrawing->endElement(FSNS(XML_x, XML_ClientData));
+    VMLExport::EndShape(nShapeElement);
+}
+
+}
+
+/// Save into xl/drawings/vmlDrawing1.vml.
+void XclExpTbxControlObj::SaveVml(XclExpXmlStream& rStrm)
+{
+    SdrObject* pObj = SdrObject::getSdrObjectFromXShape(mxShape);
+    tools::Rectangle aAreaFrom;
+    tools::Rectangle aAreaTo;
+    // 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);
+    aFormControlExporter.AddSdrObject(*pObj, /*bIsFollowingTextFlow=*/false, /*eHOri=*/-1,
+                                      /*eVOri=*/-1, /*eHRel=*/-1, /*eVRel=*/-1,
+                                      /*pWrapAttrList=*/nullptr, /*bOOxmlExport=*/true);
+}
+
 // save into xl\drawings\drawing1.xml
 void XclExpTbxControlObj::SaveXml( XclExpXmlStream& rStrm )
 {
diff --git a/sc/source/filter/inc/xeescher.hxx b/sc/source/filter/inc/xeescher.hxx
index b6ff9e562f15..f775571cac53 100644
--- a/sc/source/filter/inc/xeescher.hxx
+++ b/sc/source/filter/inc/xeescher.hxx
@@ -258,6 +258,8 @@ public:
 
     virtual void        SaveXml( XclExpXmlStream& rStrm ) override;
 
+    void SaveVml(XclExpXmlStream& rStrm);
+
     OUString SaveControlPropertiesXml(XclExpXmlStream& rStrm) const;
     void SaveSheetXml(XclExpXmlStream& rStrm, const OUString& aIdFormControlPr) const;
 
@@ -295,6 +297,7 @@ private:
     sal_Int32           mnShapeId;
     tools::Rectangle    maAreaFrom;
     tools::Rectangle    maAreaTo;
+    XclExpObjectManager& mrRoot;
 };
 
 //#endif
diff --git a/sc/source/filter/xcl97/xcl97rec.cxx b/sc/source/filter/xcl97/xcl97rec.cxx
index 4cde52e362f1..56a4dd956f0e 100644
--- a/sc/source/filter/xcl97/xcl97rec.cxx
+++ b/sc/source/filter/xcl97/xcl97rec.cxx
@@ -178,7 +178,7 @@ bool IsVmlObject( const XclObj *rObj )
 sal_Int32 GetVmlObjectCount( XclExpObjList& rList )
 {
     return static_cast<sal_Int32>(std::count_if(rList.begin(), rList.end(),
-        [](const std::unique_ptr<XclObj>& rxObj) { return IsVmlObject( rxObj.get() ); }));
+        [](const std::unique_ptr<XclObj>& rxObj) { return IsVmlObject( rxObj.get() ) || IsFormControlObject( rxObj.get() ); }));
 }
 
 bool IsValidObject( const XclObj& rObj )
@@ -333,6 +333,7 @@ void SaveVmlObjects( XclExpObjList& rList, XclExpXmlStream& rStrm )
     rStrm.GetCurrentStream()->singleElement(XML_legacyDrawing, FSNS(XML_r, XML_id), sId.toUtf8());
 
     rStrm.PushStream( pVmlDrawing );
+    pVmlDrawing->write("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n");
     pVmlDrawing->startElement( XML_xml,
             FSNS(XML_xmlns, XML_v),   rStrm.getNamespaceURL(OOX_NS(vml)).toUtf8(),
             FSNS(XML_xmlns, XML_o),   rStrm.getNamespaceURL(OOX_NS(vmlOffice)).toUtf8(),
@@ -341,6 +342,15 @@ void SaveVmlObjects( XclExpObjList& rList, XclExpXmlStream& rStrm )
 
     for ( const auto& rxObj : rList )
     {
+        if (IsFormControlObject(rxObj.get()))
+        {
+            auto pFormControlObject = dynamic_cast<XclExpTbxControlObj*>(rxObj.get());
+            if (pFormControlObject)
+            {
+                pFormControlObject->SaveVml(rStrm);
+            }
+        }
+
         if( !IsVmlObject( rxObj.get() ) )
             continue;
         rxObj->SaveXml( rStrm );