tdf#149786 sc: add VBA function: ExportAsFixedFormat

Add ExportAsFixedFormat VBA function for calc.

Works fine with Workbook/Worksheet/Range objects

Optional parameters:
- Type: works but only with xlTypePDF. (xlTypeXPS not supperted by LO)
- FileName: works but not clear the xlQualityStandard or xlQualityMinimum real value,
so just used the lossless export in case of xlQualityStandard and 70% JPEG compression quality
for xlQualityMinimum.
- IncludeDocProperties: works
- IgnorePrintAreas: TODO
- From: works
- To: works
- OpenAfterPublish: works
- FixedFormatExtClassPtr: TODO?

Change-Id: I128fd880a82a5dd315897496c6f21bb9a7c2270b
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/146512
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>

10 files changed, 292 insertions, 0 deletions

diff --git a/sc/qa/extras/testdocuments/ExportAsPDF.xlsm b/sc/qa/extras/testdocuments/ExportAsPDF.xlsm
new file mode 100644
index 000000000000..6cb410beec21
--- /dev/null
+++ b/sc/qa/extras/testdocuments/ExportAsPDF.xlsm
diff --git a/sc/qa/extras/vba-macro-test.cxx b/sc/qa/extras/vba-macro-test.cxx
index 5c4bb5b88cc4..268b35ee33fe 100644
--- a/sc/qa/extras/vba-macro-test.cxx
+++ b/sc/qa/extras/vba-macro-test.cxx
@@ -65,6 +65,7 @@ public:
     void testTdf149531();
     void testTdf118247();
     void testTdf126457();
+    void testVbaPDFExport();
 
     CPPUNIT_TEST_SUITE(VBAMacroTest);
     CPPUNIT_TEST(testSimpleCopyAndPaste);
@@ -88,6 +89,7 @@ public:
     CPPUNIT_TEST(testTdf149531);
     CPPUNIT_TEST(testTdf118247);
     CPPUNIT_TEST(testTdf126457);
+    CPPUNIT_TEST(testVbaPDFExport);
     CPPUNIT_TEST_SUITE_END();
 };
 
@@ -829,6 +831,14 @@ void VBAMacroTest::testTdf126457()
 
     pDocSh->DoClose();
 }
+
+void VBAMacroTest::testVbaPDFExport()
+{
+    loadFromURL(u"ExportAsPDF.xlsm");
+
+    executeMacro("vnd.sun.Star.script:VBAProject.Module1.ExportAsPDF?"
+                 "language=Basic&location=document");
+}
 CPPUNIT_TEST_SUITE_REGISTRATION(VBAMacroTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/ui/vba/excelvbahelper.cxx b/sc/source/ui/vba/excelvbahelper.cxx
index 06661e45ca74..47621647b096 100644
--- a/sc/source/ui/vba/excelvbahelper.cxx
+++ b/sc/source/ui/vba/excelvbahelper.cxx
@@ -20,15 +20,23 @@
 #include "excelvbahelper.hxx"
 
 #include <basic/basmgr.hxx>
+#include <comphelper/propertyvalue.hxx>
 #include <comphelper/processfactory.hxx>
 #include <comphelper/servicehelper.hxx>
+#include <osl/file.hxx>
+#include <tools/urlobj.hxx>
 #include <vbahelper/vbahelper.hxx>
+#include <com/sun/star/beans/PropertyAttribute.hpp>
+#include <com/sun/star/beans/PropertyExistException.hpp>
 #include <com/sun/star/frame/XModel.hpp>
 #include <com/sun/star/sheet/XSheetCellRange.hpp>
 #include <com/sun/star/sheet/GlobalSheetSettings.hpp>
 #include <com/sun/star/sheet/XUnnamedDatabaseRanges.hpp>
 #include <com/sun/star/sheet/XSpreadsheet.hpp>
 #include <com/sun/star/sheet/XDatabaseRange.hpp>
+#include <com/sun/star/system/SystemShellExecute.hpp>
+#include <com/sun/star/system/SystemShellExecuteFlags.hpp>
+#include <com/sun/star/util/XCloneable.hpp>
 
 #include <document.hxx>
 #include <docuno.hxx>
@@ -388,6 +396,216 @@ void setUpDocumentModules( const uno::Reference< sheet::XSpreadsheetDocument >&
     }
 }
 
+void ExportAsFixedFormatHelper(
+    const uno::Reference< frame::XModel >& xModel, const css::uno::Reference< XApplication >& xApplication,
+    const css::uno::Any& Type, const css::uno::Any& FileName, const css::uno::Any& Quality,
+    const css::uno::Any& IncludeDocProperties, const css::uno::Any& From,
+    const css::uno::Any& To, const css::uno::Any& OpenAfterPublish)
+{
+    OUString sType;
+    if ((Type >>= sType) && (sType.equalsIgnoreAsciiCase(u"xlTypeXPS") || sType == "1"))
+    {
+        /* xlTypePDF    0   "PDF" - Portable Document Format file(.pdf)
+           xlTypeXPS    1   "XPS" - XPS Document(.xps) --> not supported in LibreOffice */
+        return;
+    }
+
+    OUString sFileName;
+    FileName >>= sFileName;
+    OUString sRelURL;;
+    osl::FileBase::getFileURLFromSystemPath(sFileName, sRelURL);
+    // detect if there is no path then we need
+    // to use the current folder
+    INetURLObject aURL(sRelURL);
+    OUString sURL;
+    sURL = aURL.GetMainURL(INetURLObject::DecodeMechanism::ToIUri);
+    if (sURL.isEmpty())
+    {
+        // need to add cur dir ( of this workbook ) or else the 'Work' dir
+        sURL = xModel->getURL();
+
+        if (sURL.isEmpty())
+        {
+            // not path available from 'this' document
+            // need to add the 'document'/work directory then
+            OUString sWorkPath = xApplication->getDefaultFilePath();
+            OUString sWorkURL;
+            osl::FileBase::getFileURLFromSystemPath(sWorkPath, sWorkURL);
+            aURL.SetURL(sWorkURL);
+        }
+        else
+        {
+            if (!sFileName.isEmpty())
+            {
+                aURL.SetURL(INetURLObject::GetAbsURL(sURL, sRelURL));
+            }
+            else
+            {
+                aURL.SetURL(sURL);
+                if (aURL.removeExtension())
+                    aURL.setExtension(u"pdf");
+            }
+        }
+        sURL = aURL.GetMainURL(INetURLObject::DecodeMechanism::ToIUri);
+
+    }
+
+    sal_Int32 nTo = 0;
+    sal_Int32 nFrom = 0;
+    From >>= nFrom;
+    To >>= nTo;
+
+    OUString sRange("-");
+
+    css::uno::Sequence<css::beans::PropertyValue> aFilterData;
+    if (nFrom || nTo)
+    {
+        if (nFrom)
+            sRange = OUString::number(nFrom) + sRange;
+        if (nTo)
+            sRange += OUString::number(nTo);
+
+        aFilterData.realloc(aFilterData.getLength() + 1);
+        aFilterData.getArray()[aFilterData.getLength() - 1] = comphelper::makePropertyValue("PageRange", sRange);
+    }
+
+    OUString sQuality;
+    if (Quality >>= sQuality)
+    {
+        if (sQuality.equalsIgnoreAsciiCase(u"xlQualityMinimum") || sQuality == "1")
+        {
+            aFilterData.realloc(aFilterData.getLength() + 1);
+            aFilterData.getArray()[aFilterData.getLength() - 1] = comphelper::makePropertyValue("Quality", sal_Int32(70));
+        }
+        else if (sQuality.equalsIgnoreAsciiCase(u"xlQualityStandard") || sQuality == "0")
+        {
+            aFilterData.realloc(aFilterData.getLength() + 1);
+            aFilterData.getArray()[aFilterData.getLength() - 1] = comphelper::makePropertyValue("UseLosslessCompression", true);
+        }
+        else
+        {
+            /* Name               Value Description
+               xlQualityMinimum   1     Minimum quality
+               xlQualityStandard  0     Standard quality */
+        }
+    }
+
+    // init set of params for storeToURL() call
+    css::uno::Sequence<css::beans::PropertyValue> storeProps{
+        comphelper::makePropertyValue("FilterData", aFilterData),
+        comphelper::makePropertyValue("FilterName", OUString("calc_pdf_Export")),
+        comphelper::makePropertyValue("URL", sURL)
+    };
+
+    bool bIncludeDocProperties = true;
+    if ((IncludeDocProperties >>= bIncludeDocProperties) && !bIncludeDocProperties)
+    {
+        uno::Reference<document::XDocumentPropertiesSupplier> xDPS(xModel, uno::UNO_QUERY);
+        if (xDPS.is())
+        {
+            uno::Reference<document::XDocumentProperties> xDocProps = xDPS->getDocumentProperties();
+            uno::Reference<util::XCloneable> xCloneable(xDocProps, uno::UNO_QUERY_THROW);
+            uno::Reference<document::XDocumentProperties> xOldDocProps(xCloneable->createClone(), uno::UNO_QUERY_THROW);
+
+            // reset doc properties to default temporary
+            xDocProps->resetUserData(OUString());
+
+            uno::Reference< frame::XStorable > xStor(xModel, uno::UNO_QUERY_THROW);
+            try {
+                xStor->storeToURL(sURL, storeProps);
+            }
+            catch (const uno::Exception&)
+            {
+                SetDocInfoState(xModel, xOldDocProps);
+                throw;
+            }
+
+            SetDocInfoState(xModel, xOldDocProps);
+        }
+    }
+    else
+    {
+        uno::Reference< frame::XStorable > xStor(xModel, uno::UNO_QUERY_THROW);
+        xStor->storeToURL(sURL, storeProps);
+    }
+
+    bool bOpenAfterPublish = false;
+    if ((OpenAfterPublish >>= bOpenAfterPublish) && bOpenAfterPublish)
+    {
+        uno::Reference<css::system::XSystemShellExecute> xSystemShellExecute(css::system::SystemShellExecute::create(::comphelper::getProcessComponentContext()));
+        xSystemShellExecute->execute(aURL.GetMainURL(INetURLObject::DecodeMechanism::NONE), "", css::system::SystemShellExecuteFlags::URIS_ONLY);
+    }
+}
+
+void SetDocInfoState(
+    const uno::Reference< frame::XModel >& xModel,
+    const uno::Reference< css::document::XDocumentProperties>& i_xOldDocProps)
+{
+    uno::Reference<document::XDocumentPropertiesSupplier> const
+        xModelDocPropsSupplier(xModel, uno::UNO_QUERY_THROW);
+    uno::Reference<document::XDocumentProperties> const xDocPropsToFill =
+        xModelDocPropsSupplier->getDocumentProperties();
+    uno::Reference< beans::XPropertySet > const xPropSet(
+        i_xOldDocProps->getUserDefinedProperties(), uno::UNO_QUERY_THROW);
+
+    uno::Reference< util::XModifiable > xModifiable(xModel, uno::UNO_QUERY);
+    if (!xModifiable.is())
+        throw uno::RuntimeException();
+
+    bool bIsModified = xModifiable->isModified();
+
+    try
+    {
+        uno::Reference< beans::XPropertySet > const xSet(
+            xDocPropsToFill->getUserDefinedProperties(), uno::UNO_QUERY);
+        uno::Reference< beans::XPropertyContainer > xContainer(xSet, uno::UNO_QUERY);
+        uno::Reference< beans::XPropertySetInfo > xSetInfo = xSet->getPropertySetInfo();
+        const uno::Sequence< beans::Property > lProps = xSetInfo->getProperties();
+        for (const beans::Property& rProp : lProps)
+        {
+            uno::Any aValue = xPropSet->getPropertyValue(rProp.Name);
+            if (rProp.Attributes & css::beans::PropertyAttribute::REMOVABLE)
+            {
+                try
+                {
+                    // QUESTION: DefaultValue?!
+                    xContainer->addProperty(rProp.Name, rProp.Attributes, aValue);
+                }
+                catch (beans::PropertyExistException const&) {}
+                try
+                {
+                    // it is possible that the propertysets from XML and binary files differ; we shouldn't break then
+                    xSet->setPropertyValue(rProp.Name, aValue);
+                }
+                catch (const uno::Exception&) {}
+            }
+        }
+
+        // sigh... have to set these manually I'm afraid...
+        xDocPropsToFill->setAuthor(i_xOldDocProps->getAuthor());
+        xDocPropsToFill->setGenerator(i_xOldDocProps->getGenerator());
+        xDocPropsToFill->setCreationDate(i_xOldDocProps->getCreationDate());
+        xDocPropsToFill->setTitle(i_xOldDocProps->getTitle());
+        xDocPropsToFill->setSubject(i_xOldDocProps->getSubject());
+        xDocPropsToFill->setDescription(i_xOldDocProps->getDescription());
+        xDocPropsToFill->setKeywords(i_xOldDocProps->getKeywords());
+        xDocPropsToFill->setModifiedBy(i_xOldDocProps->getModifiedBy());
+        xDocPropsToFill->setModificationDate(i_xOldDocProps->getModificationDate());
+        xDocPropsToFill->setPrintedBy(i_xOldDocProps->getPrintedBy());
+        xDocPropsToFill->setPrintDate(i_xOldDocProps->getPrintDate());
+        xDocPropsToFill->setAutoloadURL(i_xOldDocProps->getAutoloadURL());
+        xDocPropsToFill->setAutoloadSecs(i_xOldDocProps->getAutoloadSecs());
+        xDocPropsToFill->setDefaultTarget(i_xOldDocProps->getDefaultTarget());
+        xDocPropsToFill->setEditingCycles(i_xOldDocProps->getEditingCycles());
+        xDocPropsToFill->setEditingDuration(i_xOldDocProps->getEditingDuration());
+    }
+    catch (const uno::Exception&) {}
+
+    // set the modified flag back if required
+    if (bIsModified != bool(xModifiable->isModified()))
+        xModifiable->setModified(bIsModified);
+}
+
 SfxItemSet*
 ScVbaCellRangeAccess::GetDataSet( ScCellRangesBase* pRangeObj )
 {
diff --git a/sc/source/ui/vba/excelvbahelper.hxx b/sc/source/ui/vba/excelvbahelper.hxx
index 0b72481e31ed..542991aca0ec 100644
--- a/sc/source/ui/vba/excelvbahelper.hxx
+++ b/sc/source/ui/vba/excelvbahelper.hxx
@@ -21,6 +21,8 @@
 #include <sal/config.h>
 
 #include <comphelper/servicehelper.hxx>
+#include <com/sun/star/document/XDocumentProperties.hpp>
+#include <ooo/vba/excel/XApplication.hpp>
 
 #include <vector>
 #include <global.hxx>
@@ -74,6 +76,16 @@ css::uno::Reference< ooo::vba::XHelperInterface > getUnoSheetModuleObj( const cs
 ScDocShell* GetDocShellFromRange( const css::uno::Reference< css::uno::XInterface >& xRange );
 void setUpDocumentModules( const css::uno::Reference< css::sheet::XSpreadsheetDocument >& xDoc );
 
+void ExportAsFixedFormatHelper(
+    const css::uno::Reference< css::frame::XModel >& xModel, const css::uno::Reference< ooo::vba::excel::XApplication >& xApplication,
+    const css::uno::Any& Type, const css::uno::Any& FileName, const css::uno::Any& Quality,
+    const css::uno::Any& IncludeDocProperties, const css::uno::Any& From,
+    const css::uno::Any& To, const css::uno::Any& OpenAfterPublish);
+
+void SetDocInfoState(
+    const css::uno::Reference< css::frame::XModel >& xModel,
+    const css::uno::Reference< css::document::XDocumentProperties>& i_xOldDocInfo);
+
 class ScVbaCellRangeAccess
 {
 public:
diff --git a/sc/source/ui/vba/vbarange.cxx b/sc/source/ui/vba/vbarange.cxx
index 16d6d1456b3d..089e92f8c948 100644
--- a/sc/source/ui/vba/vbarange.cxx
+++ b/sc/source/ui/vba/vbarange.cxx
@@ -5721,6 +5721,25 @@ ScVbaRange::Subtotal( ::sal_Int32 _nGroupBy, ::sal_Int32 _nFunction, const uno::
     }
 }
 
+void SAL_CALL
+ScVbaRange::ExportAsFixedFormat(const css::uno::Any& Type, const css::uno::Any& FileName, const css::uno::Any& Quality,
+    const css::uno::Any& IncludeDocProperties, const css::uno::Any& /*IgnorePrintAreas*/, const css::uno::Any& From,
+    const css::uno::Any& To, const css::uno::Any& OpenAfterPublish, const css::uno::Any& /*FixedFormatExtClassPtr*/)
+{
+    ScCellRangesBase* pUnoRangesBase = getCellRangesBase();
+    if (!pUnoRangesBase)
+        throw uno::RuntimeException("Failed to access underlying uno range object");
+    ScDocShell* pShell = pUnoRangesBase->GetDocShell();
+    if (!pShell)
+        return;
+
+    uno::Reference< frame::XModel > xModel(pShell->GetModel(), uno::UNO_SET_THROW);
+    uno::Reference< excel::XApplication > xApplication(Application(), uno::UNO_QUERY_THROW);
+
+    excel::ExportAsFixedFormatHelper(xModel, xApplication, Type, FileName, Quality,
+        IncludeDocProperties, From, To, OpenAfterPublish);
+}
+
 OUString
 ScVbaRange::getServiceImplName()
 {
diff --git a/sc/source/ui/vba/vbarange.hxx b/sc/source/ui/vba/vbarange.hxx
index 7a2d186f5edb..a9d82201a685 100644
--- a/sc/source/ui/vba/vbarange.hxx
+++ b/sc/source/ui/vba/vbarange.hxx
@@ -290,6 +290,9 @@ public:
     virtual void SAL_CALL RemoveSubtotal(  ) override;
     virtual css::uno::Reference< ov::excel::XRange > SAL_CALL MergeArea() override;
     virtual void SAL_CALL Subtotal( ::sal_Int32 GroupBy, ::sal_Int32 Function, const css::uno::Sequence< ::sal_Int32 >& TotalList, const css::uno::Any& Replace, const css::uno::Any& PageBreaks, const css::uno::Any& SummaryBelowData ) override;
+    virtual void SAL_CALL ExportAsFixedFormat(const css::uno::Any& Type, const css::uno::Any& FileName, const css::uno::Any& Quality,
+        const css::uno::Any& IncludeDocProperties, const css::uno::Any& IgnorePrintAreas, const css::uno::Any& From,
+        const css::uno::Any& To, const css::uno::Any& OpenAfterPublish, const css::uno::Any& FixedFormatExtClassPtr) override;
 
     // XEnumerationAccess
     virtual css::uno::Reference< css::container::XEnumeration > SAL_CALL createEnumeration() override;
diff --git a/sc/source/ui/vba/vbaworkbook.cxx b/sc/source/ui/vba/vbaworkbook.cxx
index 23cc523a1aa8..fad0ef2d1e20 100644
--- a/sc/source/ui/vba/vbaworkbook.cxx
+++ b/sc/source/ui/vba/vbaworkbook.cxx
@@ -352,6 +352,18 @@ ScVbaWorkbook::SaveAs( const uno::Any& FileName, const uno::Any& FileFormat, con
     xStor->storeAsURL( sURL, storeProps );
 }
 
+void SAL_CALL
+ScVbaWorkbook::ExportAsFixedFormat(const css::uno::Any& Type, const css::uno::Any& FileName, const css::uno::Any& Quality,
+    const css::uno::Any& IncludeDocProperties, const css::uno::Any& /*IgnorePrintAreas*/, const css::uno::Any& From,
+    const css::uno::Any& To, const css::uno::Any& OpenAfterPublish, const css::uno::Any& /*FixedFormatExtClassPtr*/)
+{
+    uno::Reference< frame::XModel > xModel(getModel(), uno::UNO_SET_THROW);
+    uno::Reference< excel::XApplication > xApplication(Application(), uno::UNO_QUERY_THROW);
+
+    excel::ExportAsFixedFormatHelper(xModel, xApplication, Type, FileName, Quality,
+        IncludeDocProperties, From, To, OpenAfterPublish);
+}
+
 css::uno::Any SAL_CALL
 ScVbaWorkbook::Styles( const uno::Any& Item )
 {
diff --git a/sc/source/ui/vba/vbaworkbook.hxx b/sc/source/ui/vba/vbaworkbook.hxx
index 886f771bf5e7..e6a838b1ab1e 100644
--- a/sc/source/ui/vba/vbaworkbook.hxx
+++ b/sc/source/ui/vba/vbaworkbook.hxx
@@ -58,6 +58,9 @@ public:
     virtual css::uno::Any SAL_CALL Colors( const css::uno::Any& Index ) override;
     virtual ::sal_Int32 SAL_CALL getFileFormat(  ) override;
     virtual void SAL_CALL SaveCopyAs( const OUString& Filename ) override;
+    virtual void SAL_CALL ExportAsFixedFormat( const css::uno::Any& Type, const css::uno::Any& FileName, const css::uno::Any& Quality,
+        const css::uno::Any& IncludeDocProperties, const css::uno::Any& IgnorePrintAreas, const css::uno::Any& From,
+        const css::uno::Any& To, const css::uno::Any& OpenAfterPublish, const css::uno::Any& FixedFormatExtClassPtr) override;
 
     // code name
     virtual OUString SAL_CALL getCodeName() override;
diff --git a/sc/source/ui/vba/vbaworksheet.cxx b/sc/source/ui/vba/vbaworksheet.cxx
index cfc84537da62..1160cd275032 100644
--- a/sc/source/ui/vba/vbaworksheet.cxx
+++ b/sc/source/ui/vba/vbaworksheet.cxx
@@ -1037,6 +1037,18 @@ ScVbaWorksheet::PrintOut( const uno::Any& From, const uno::Any& To, const uno::A
     PrintOutHelper( excel::getBestViewShell( xModel ), From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, bSelection );
 }
 
+void SAL_CALL
+ScVbaWorksheet::ExportAsFixedFormat(const css::uno::Any& Type, const css::uno::Any& FileName, const css::uno::Any& Quality,
+    const css::uno::Any& IncludeDocProperties, const css::uno::Any& /*IgnorePrintAreas*/, const css::uno::Any& From,
+    const css::uno::Any& To, const css::uno::Any& OpenAfterPublish, const css::uno::Any& /*FixedFormatExtClassPtr*/)
+{
+    uno::Reference< frame::XModel > xModel(getModel(), uno::UNO_SET_THROW);
+    uno::Reference< excel::XApplication > xApplication(Application(), uno::UNO_QUERY_THROW);
+
+    excel::ExportAsFixedFormatHelper(xModel, xApplication, Type, FileName, Quality,
+        IncludeDocProperties, From, To, OpenAfterPublish);
+}
+
 sal_Int64 SAL_CALL
 ScVbaWorksheet::getSomething(const uno::Sequence<sal_Int8 > & rId)
 {
diff --git a/sc/source/ui/vba/vbaworksheet.hxx b/sc/source/ui/vba/vbaworksheet.hxx
index 439e5a3139bd..7ac05220a2f4 100644
--- a/sc/source/ui/vba/vbaworksheet.hxx
+++ b/sc/source/ui/vba/vbaworksheet.hxx
@@ -157,6 +157,9 @@ public:
     sal_Int16 getSheetID() const;
 
     virtual void SAL_CALL PrintOut( const css::uno::Any& From, const css::uno::Any& To, const css::uno::Any& Copies, const css::uno::Any& Preview, const css::uno::Any& ActivePrinter, const css::uno::Any& PrintToFile, const css::uno::Any& Collate, const css::uno::Any& PrToFileName, const css::uno::Any& IgnorePrintAreas ) override;
+    virtual void SAL_CALL ExportAsFixedFormat(const css::uno::Any& Type, const css::uno::Any& FileName, const css::uno::Any& Quality,
+        const css::uno::Any& IncludeDocProperties, const css::uno::Any& IgnorePrintAreas, const css::uno::Any& From,
+        const css::uno::Any& To, const css::uno::Any& OpenAfterPublish, const css::uno::Any& FixedFormatExtClassPtr) override;
     // XHelperInterface
     virtual OUString getServiceImplName() override;
     virtual css::uno::Sequence<OUString> getServiceNames() override;