tdf#158857: [Power Query] export connections.xml

- import&export connections stream
- insert xl/connections.xml reference to [Content_Types].xml
- add Relationship::CONNECTIONS
- add support for xr16 namespace
- add ../customXml/item1.xml relationship to xl/_rels/workbook.xml.rels
- add import&export support for following xml elements in xl/connections.xml:

<connections>
<connection>

<dbPr />

<olapPr />

<webPr> (Web Query Properties)
<tables>
<m />
<s />
<x />
</tables>
</webPr>

<textPr>
<textFields>
<textField />
</textFields>
</textPr>

<parameters>
<parameter />
</parameters>

<extLst>
<ext>
(Any element in any namespace) <- TODO/LATER
</ext>
</extLst>

</connection>
</connections>

Change-Id: I1d1c10675b3fe5ffd6a35f1b91d4b83401be2cae
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/164290
Tested-by: Caolán McNamara <caolan.mcnamara@collabora.com>
Tested-by: Jenkins
Reviewed-by: Caolán McNamara <caolan.mcnamara@collabora.com>

7 files changed, 756 insertions, 14 deletions

diff --git a/sc/inc/document.hxx b/sc/inc/document.hxx
index 15f1720142fb..44e5bb0b4a62 100644
--- a/sc/inc/document.hxx
+++ b/sc/inc/document.hxx
@@ -58,7 +58,19 @@
 #include "markdata.hxx"
 #include "drwlayer.hxx"
 
+#include <oox/helper/refvector.hxx>
+
 namespace com::sun::star::chart2 { class XChartDocument; }
+namespace oox
+{
+    namespace xls
+    {
+        class Connection;
+        struct ConnectionModel;
+    }
+}
+
+typedef oox::RefVector<oox::xls::Connection> ConnectionVector;
 
 class Timer;
 
@@ -373,6 +385,16 @@ private:
 public:
     SC_DLLPUBLIC CellAttributeHelper& getCellAttributeHelper() const;
 
+    void setConnectionVector(const ConnectionVector& rIn)
+    {
+        maConnectionVector = rIn;
+    }
+
+    const ConnectionVector& getConnectionVector() const
+    {
+        return maConnectionVector;
+    }
+
 private:
     rtl::Reference<ScPoolHelper> mxPoolHelper;
 
@@ -465,6 +487,8 @@ private:
 
     // Stores Goal Seek settings
     ScGoalSeekSettings maGoalSeekSettings;
+
+    ConnectionVector maConnectionVector;
 public:
     /// list of ScInterpreterTableOpParams currently in use
     std::vector<ScInterpreterTableOpParams*> m_TableOpList;
@@ -599,10 +623,25 @@ private:
 
     size_t              mnMutationGuardFlags;
 
+    bool mbConnectionXml = false;
+    bool mbCustomXml = false;
+    OUString aCustomXmlFragmentPath;
+
 public:
     bool                     IsCellInChangeTrack(const ScAddress &cell,Color *pColCellBorder);
     void                     GetCellChangeTrackNote(const ScAddress &cell, OUString &strTrackText, bool &pbLeftEdge);
 
+    void setHasConnectionXml(bool bUse) { mbConnectionXml = bUse; }
+    bool hasConnectionXml() { return mbConnectionXml; }
+
+    void setHasCustomXml(bool bUse, OUString& sCustomXmlPath)
+    {
+        mbCustomXml = bUse;
+        aCustomXmlFragmentPath = sCustomXmlPath;
+    }
+    OUString getCustomXmlItems() { return aCustomXmlFragmentPath; }
+    bool hasCustomXml() { return mbCustomXml; }
+
     bool IsEmbedFonts() const { return mbEmbedFonts; }
     bool IsEmbedUsedFontsOnly() const { return mbEmbedUsedFontsOnly; }
     bool IsEmbedFontScriptLatin() const { return mbEmbedFontScriptLatin; }
diff --git a/sc/source/filter/excel/excdoc.cxx b/sc/source/filter/excel/excdoc.cxx
index 9b2a29ff62ca..847e6d457cbf 100644
--- a/sc/source/filter/excel/excdoc.cxx
+++ b/sc/source/filter/excel/excdoc.cxx
@@ -27,6 +27,8 @@
 #include <tabprotection.hxx>
 #include <postit.hxx>
 #include <root.hxx>
+#include <connectionsbuffer.hxx>
+#include <connectionsfragment.hxx>
 
 #include <excdoc.hxx>
 #include <xeextlst.hxx>
@@ -878,6 +880,8 @@ void ExcDocument::WriteXml( XclExpXmlStream& rStrm )
                 oox::ThemeExport aThemeExport(&rStrm, oox::drawingml::DOCUMENT_XLSX);
                 aThemeExport.write(sThemeDocumentPath, *pTheme);
 
+                // xl/_rels/workbook.xml.rels
+                // TODO: check if Theme import & export work correctly
                 rStrm.addRelation(rStrm.GetCurrentStream()->getOutputStream(),
                                   oox::getRelationship(Relationship::THEME),
                                   sThemeRelationshipPath);
@@ -910,6 +914,357 @@ void ExcDocument::WriteXml( XclExpXmlStream& rStrm )
         eConv = GetDoc().GetAddressConvention();
     }
 
+    ScDocument& rDoc = GetDoc();
+    bool connectionXml = rDoc.hasConnectionXml();
+
+    if (connectionXml)
+    {
+        // save xl/connections.xml reference into [Content_Types].xml
+        sax_fastparser::FSHelperPtr aConnectionsXml = rStrm.CreateOutputStream(
+            "xl/connections.xml", u"connections.xml", rStrm.GetCurrentStream()->getOutputStream(),
+            "application/vnd.openxmlformats-officedocument.spreadsheetml.connections+xml",
+            oox::getRelationship(Relationship::CONNECTIONS));
+        rStrm.PushStream(aConnectionsXml);
+
+        /*
+            <connections
+            xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
+            xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
+            xmlns:xr16="http://schemas.microsoft.com/office/spreadsheetml/2017/revision16"
+            mc:Ignorable="xr16">
+        */
+
+        // write into xl/connections.xml
+        // export <connections>
+        aConnectionsXml->startElement(
+            XML_connections, XML_xmlns, rStrm.getNamespaceURL(OOX_NS(xls)), FSNS(XML_xmlns, XML_mc),
+            rStrm.getNamespaceURL(OOX_NS(mce)), FSNS(XML_xmlns, XML_xr16),
+            rStrm.getNamespaceURL(OOX_NS(xr16)), FSNS(XML_mc, XML_Ignorable), "xr16");
+
+        // get a list of <connection> element and export it with its child elements
+        ConnectionVector vConnVector = rDoc.getConnectionVector();
+        for (const auto& rConnection : vConnVector)
+        {
+            const oox::xls::ConnectionModel& rModel = rConnection->getModel();
+
+            if (rModel.mnId == -1 || rModel.mnRefreshedVersion == -1)
+                continue; // incorrect values, skip
+
+            rtl::Reference<sax_fastparser::FastAttributeList> pAttrList
+                = sax_fastparser::FastSerializerHelper::createAttrList();
+
+            pAttrList->add(XML_id, OUString::number(rModel.mnId));
+
+            if (!rModel.maXr16Uid.isEmpty())
+                pAttrList->add(FSNS(XML_xr16, XML_uid), rModel.maXr16Uid);
+            if (!rModel.maSourceFile.isEmpty())
+                pAttrList->add(XML_sourceFile, rModel.maSourceFile);
+            if (!rModel.maSourceConnFile.isEmpty())
+                pAttrList->add(XML_odcFile, rModel.maSourceConnFile);
+
+            pAttrList->add(XML_keepAlive, ToPsz10(rModel.mbKeepAlive));
+            pAttrList->add(XML_interval, OUString::number(rModel.mnInterval));
+
+            if (!rModel.maName.isEmpty())
+                pAttrList->add(XML_name, rModel.maName);
+            if (!rModel.maDescription.isEmpty())
+                pAttrList->add(XML_description, rModel.maDescription);
+            if (OUString::number(rModel.mnType).length > 0)
+                pAttrList->add(XML_type, OUString::number(rModel.mnType));
+
+            pAttrList->add(XML_reconnectionMethod, OUString::number(rModel.mnReconnectMethod));
+            pAttrList->add(XML_refreshedVersion, OUString::number(rModel.mnRefreshedVersion));
+            pAttrList->add(XML_minRefreshableVersion,
+                           OUString::number(rModel.mnMinRefreshableVersion));
+            pAttrList->add(XML_savePassword, ToPsz10(rModel.mbSavePassword));
+            pAttrList->add(XML_new, ToPsz10(rModel.mbNew));
+            pAttrList->add(XML_deleted, ToPsz10(rModel.mbDeleted));
+            pAttrList->add(XML_onlyUseConnectionFile, ToPsz10(rModel.mbOnlyUseConnFile));
+            pAttrList->add(XML_background, ToPsz10(rModel.mbBackground));
+            pAttrList->add(XML_refreshOnLoad, ToPsz10(rModel.mbRefreshOnLoad));
+            pAttrList->add(XML_saveData, ToPsz10(rModel.mbSaveData));
+
+            // import credentials="" attribute of <connection> element
+            if (rModel.mnCredentials != -1)
+            {
+                sal_Int32 nToken = rModel.mnCredentials;
+                OUString nValue;
+
+                switch (nToken)
+                {
+                    case XML_none:
+                        nValue = "none";
+                        break;
+                    case XML_stored:
+                        nValue = "stored";
+                        break;
+                    case XML_prompt:
+                        nValue = "prompt";
+                        break;
+                    default:
+                        nValue = "integrated";
+                        break;
+                }
+
+                pAttrList->add(XML_credentials, nValue);
+            }
+
+            if (!rModel.maSsoId.isEmpty())
+                pAttrList->add(XML_singleSignOnId, rModel.maSsoId);
+
+            // export <connection> with attributes
+            rStrm.GetCurrentStream()->startElement(XML_connection, pAttrList);
+
+            /*
+                start export child elements of <connection>
+
+                <xsd:sequence>
+                    <xsd:element name="dbPr" minOccurs="0" maxOccurs="1" type="CT_DbPr"/>
+                    <xsd:element name="olapPr" minOccurs="0" maxOccurs="1" type="CT_OlapPr"/>
+                    <xsd:element name="webPr" minOccurs="0" maxOccurs="1" type="CT_WebPr"/>
+                    <xsd:element name="textPr" minOccurs="0" maxOccurs="1" type="CT_TextPr"/>
+                    <xsd:element name="parameters" minOccurs="0" maxOccurs="1" type="CT_Parameters"/>
+                    <xsd:element name="extLst" minOccurs="0" maxOccurs="1" type="CT_ExtensionList"/>
+                </xsd:sequence>
+            */
+
+            { // export <dbPr>
+
+                rtl::Reference<sax_fastparser::FastAttributeList> pAttrListDbPr
+                    = sax_fastparser::FastSerializerHelper::createAttrList();
+
+                css::uno::Sequence<css::uno::Any> aSeqs = rConnection->getDbPrSequenceAny();
+                addElemensToAttrList(pAttrListDbPr, aSeqs);
+
+                rStrm.GetCurrentStream()->singleElement(XML_dbPr, pAttrListDbPr);
+            }
+
+            { // export <olapPr>
+
+                rtl::Reference<sax_fastparser::FastAttributeList> pAttrListOlapPr
+                    = sax_fastparser::FastSerializerHelper::createAttrList();
+
+                css::uno::Sequence<css::uno::Any> aSeqs = rConnection->getOlapPrSequenceAny();
+                addElemensToAttrList(pAttrListOlapPr, aSeqs);
+
+                // this prints empty <olapPr/> even if aSeqs is empty, TODO: check if aSeqs is empty
+                rStrm.GetCurrentStream()->singleElement(XML_olapPr, pAttrListOlapPr);
+            }
+
+            { // export <webPr> and its child elements
+                rtl::Reference<sax_fastparser::FastAttributeList> pAttrListWebPr
+                    = sax_fastparser::FastSerializerHelper::createAttrList();
+
+                if (rModel.mxWebPr)
+                {
+                    pAttrListWebPr->add(XML_xml, ToPsz10(rModel.mxWebPr->mbXml));
+                    pAttrListWebPr->add(XML_sourceData, ToPsz10(rModel.mxWebPr->mbSourceData));
+                    pAttrListWebPr->add(XML_parsePre, ToPsz10(rModel.mxWebPr->mbParsePre));
+                    pAttrListWebPr->add(XML_consecutive, ToPsz10(rModel.mxWebPr->mbConsecutive));
+                    pAttrListWebPr->add(XML_firstRow, ToPsz10(rModel.mxWebPr->mbFirstRow));
+                    pAttrListWebPr->add(XML_xl97, ToPsz10(rModel.mxWebPr->mbXl97Created));
+                    pAttrListWebPr->add(XML_textDates, ToPsz10(rModel.mxWebPr->mbTextDates));
+                    pAttrListWebPr->add(XML_xl2000, ToPsz10(rModel.mxWebPr->mbXl2000Refreshed));
+                    pAttrListWebPr->add(XML_htmlTables, ToPsz10(rModel.mxWebPr->mbHtmlTables));
+
+                    if (!rModel.mxWebPr->maUrl.isEmpty())
+                        pAttrListWebPr->add(XML_url, rModel.mxWebPr->maUrl);
+                    if (!rModel.mxWebPr->maPostMethod.isEmpty())
+                        pAttrListWebPr->add(XML_post, rModel.mxWebPr->maPostMethod);
+                    if (!rModel.mxWebPr->maEditPage.isEmpty())
+                        pAttrListWebPr->add(XML_editPage, rModel.mxWebPr->maEditPage);
+
+                    // import htmlFormat="" attribute of <webPr> element
+                    if (rModel.mxWebPr->mnHtmlFormat != -1)
+                    {
+                        sal_Int32 nToken = rModel.mxWebPr->mnHtmlFormat;
+                        OUString nValue;
+
+                        switch (nToken)
+                        {
+                            case XML_all:
+                                nValue = "all";
+                                break;
+                            case XML_rtf:
+                                nValue = "rtf";
+                                break;
+                            default:
+                                nValue = "none";
+                                break;
+                        }
+
+                        pAttrListWebPr->add(XML_htmlFormat, nValue);
+                    }
+
+                    // export <webPr> with attributes
+                    rStrm.GetCurrentStream()->startElement(XML_webPr, pAttrListWebPr);
+
+                    { // export <tables> and its child elements
+
+                        rtl::Reference<sax_fastparser::FastAttributeList> pAttrListTables
+                            = sax_fastparser::FastSerializerHelper::createAttrList();
+
+                        // <tables count="<xsd:unsignedInt>">
+                        if (rModel.mxWebPr->mnCount >= 0)
+                        {
+                            pAttrListTables->add(XML_count,
+                                                 OUString::number(rModel.mxWebPr->mnCount));
+
+                            // export <tables> with attributes
+                            rStrm.GetCurrentStream()->startElement(XML_tables, pAttrListTables);
+
+                            { // export <m>, <s> and <x> elements
+                                for (auto& tableElement : rModel.mxWebPr->maTables)
+                                {
+                                    OUString sElement;
+                                    tableElement >>= sElement;
+                                    OUString token = sElement.getToken(0, ',');
+                                    OUString attributeValue = sElement.getToken(1, ',');
+
+                                    if (token == "s")
+                                        rStrm.GetCurrentStream()->singleElement(XML_s, XML_v,
+                                                                                attributeValue);
+                                    else if (token == "x")
+                                        rStrm.GetCurrentStream()->singleElement(XML_x, XML_v,
+                                                                                attributeValue);
+                                    else
+                                        rStrm.GetCurrentStream()->singleElement(XML_m);
+                                }
+                            }
+
+                            // put </tables>
+                            rStrm.GetCurrentStream()->endElement(XML_tables);
+                        }
+                    }
+
+                    // put </webPr>
+                    rStrm.GetCurrentStream()->endElement(XML_webPr);
+                }
+            }
+
+            { // export <textPr>
+
+                rtl::Reference<sax_fastparser::FastAttributeList> pAttrListTextPr
+                    = sax_fastparser::FastSerializerHelper::createAttrList();
+
+                if (rModel.mxTextPr)
+                {
+                    addElemensToAttrList(pAttrListTextPr, rModel.mxTextPr->maTextPrSequenceAny);
+
+                    rStrm.GetCurrentStream()->startElement(XML_textPr, pAttrListTextPr);
+
+                    { // export <textFields>
+                        rtl::Reference<sax_fastparser::FastAttributeList> pAttrListTextFields
+                            = sax_fastparser::FastSerializerHelper::createAttrList();
+
+                        addElemensToAttrList(pAttrListTextFields,
+                                             rModel.mxTextPr->maTextFieldsSequenceAny);
+
+                        rStrm.GetCurrentStream()->startElement(XML_textFields, pAttrListTextFields);
+
+                        { // export <textField>
+
+                            for (auto& textFieldAttr : rModel.mxTextPr->vTextField)
+                            {
+                                rtl::Reference<sax_fastparser::FastAttributeList> pAttrListTextField
+                                    = sax_fastparser::FastSerializerHelper::createAttrList();
+
+                                addElemensToAttrList(pAttrListTextField, textFieldAttr);
+
+                                rStrm.GetCurrentStream()->singleElement(XML_textField,
+                                                                        pAttrListTextField);
+                            }
+                        }
+
+                        // put </textFields>
+                        rStrm.GetCurrentStream()->endElement(XML_textFields);
+                    }
+
+                    // put </textPr>
+                    rStrm.GetCurrentStream()->endElement(XML_textPr);
+                }
+            }
+
+            { // export <parameters>
+
+                rtl::Reference<sax_fastparser::FastAttributeList> pAttrListParameters
+                    = sax_fastparser::FastSerializerHelper::createAttrList();
+
+                if (rModel.mxParameters && rModel.mxParameters->mnCount > -1)
+                {
+                    pAttrListParameters->add(XML_count,
+                                             OUString::number(rModel.mxParameters->mnCount));
+
+                    rStrm.GetCurrentStream()->startElement(XML_parameters, pAttrListParameters);
+
+                    // export <parameter>
+                    for (auto& parameterAttr : rModel.mxParameters->vParameter)
+                    {
+                        rtl::Reference<sax_fastparser::FastAttributeList> pAttrListParameter
+                            = sax_fastparser::FastSerializerHelper::createAttrList();
+
+                        addElemensToAttrList(pAttrListParameter, parameterAttr);
+
+                        rStrm.GetCurrentStream()->singleElement(XML_parameter, pAttrListParameter);
+                    }
+
+                    // put </parameters>
+                    rStrm.GetCurrentStream()->endElement(XML_parameters);
+                }
+            }
+
+            { // export <extLst>
+                if (rModel.mxExtensionList)
+                {
+                    // put <extLst>, it has no attributes
+                    rStrm.GetCurrentStream()->startElement(XML_extLst);
+
+                    // export uri attribute of <ext> element
+                    for (auto& uriValue : rModel.mxExtensionList->vExtension)
+                    {
+                        // export <ext> with uri attribute.
+                        rStrm.GetCurrentStream()->startElement(XML_ext, XML_uri, uriValue);
+
+                    /*
+                        TODO: export child elements of <ext>. We should export "any element in any namespace", which seems challenging.
+
+                        <extLst>
+                            <ext>
+                                (Any element in any namespace)
+                            </ext>
+                        </extLst>
+                    */
+
+                        // put </ext>
+                        rStrm.GetCurrentStream()->endElement(XML_ext);
+                    }
+
+                    // put </extLst>
+                    rStrm.GetCurrentStream()->endElement(XML_extLst);
+                }
+            }
+
+            // put </connection>
+            rStrm.GetCurrentStream()->endElement(XML_connection);
+        }
+
+        // put </connections>
+        aConnectionsXml->endElement(XML_connections);
+        rStrm.PopStream();
+    }
+
+    if (rDoc.hasCustomXml())
+    {
+        // export customXml/item1.xml into xl/_rels/workbook.xml.rels
+        OUString sCustomXmlPath = OUString::Concat("../") + rDoc.getCustomXmlItems();
+
+        // FIXME: what if there are customXml/item2.xml, customXml/item3.xml etc?
+        // we need to save all customXml/item*.xml paths into ScDocument from XmlFilterBase::importCustomFragments
+        // then we should get them with rDoc here.
+        rStrm.addRelation(rStrm.GetCurrentStream()->getOutputStream(),
+                          oox::getRelationship(Relationship::CUSTOMXML), sCustomXmlPath);
+    }
+
     // write if it has been read|imported or explicitly changed
     // or if ref syntax isn't what would be native for our file format
     // i.e. ExcelA1 in this case
@@ -925,4 +1280,28 @@ void ExcDocument::WriteXml( XclExpXmlStream& rStrm )
     rWorkbook.reset();
 }
 
+void ExcDocument::addElemensToAttrList(rtl::Reference<sax_fastparser::FastAttributeList>& pAttrList,
+                                       css::uno::Sequence<css::uno::Any>& aSeqs)
+{
+    css::uno::Sequence<css::xml::FastAttribute> aFastSeq;
+    css::uno::Sequence<css::xml::Attribute> aUnkSeq;
+
+    // TODO: check if aSeqs is empty or not
+    for (const auto& a : aSeqs)
+    {
+        if (a >>= aFastSeq)
+        {
+            for (const auto& rAttr : aFastSeq)
+                pAttrList->add(rAttr.Token, rAttr.Value);
+        }
+        else if (a >>= aUnkSeq)
+        {
+            for (const auto& rAttr : aUnkSeq)
+                pAttrList->addUnknown(rAttr.NamespaceURL,
+                                      OUStringToOString(rAttr.Name, RTL_TEXTENCODING_UTF8),
+                                      OUStringToOString(rAttr.Value, RTL_TEXTENCODING_UTF8));
+        }
+    }
+}
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/filter/inc/connectionsbuffer.hxx b/sc/source/filter/inc/connectionsbuffer.hxx
index 9308da5a36b5..6278b8b50044 100644
--- a/sc/source/filter/inc/connectionsbuffer.hxx
+++ b/sc/source/filter/inc/connectionsbuffer.hxx
@@ -22,6 +22,8 @@
 #include <memory>
 #include <oox/helper/refvector.hxx>
 #include "workbookhelper.hxx"
+#include <document.hxx>
+#include <com/sun/star/xml/sax/XFastAttributeList.hpp>
 
 namespace oox { class AttributeList; }
 namespace oox { class SequenceInputStream; }
@@ -38,12 +40,25 @@ const sal_Int32 BIFF12_CONNECTION_TEXT                  = 6;
 const sal_Int32 BIFF12_CONNECTION_ADO                   = 7;
 const sal_Int32 BIFF12_CONNECTION_DSP                   = 8;
 
+/*
+TODO: update import&export of Microsoft Excel Binary (XLSB) File Format (option in Excel 2007 and later)
+
+XLSB is not an international standard. It is a proprietary Microsoft format for spreadsheets
+that has been available as a full-fidelity alternative to the default XLSX format since Excel 2007.
+It is intended for users who need to load and save large data files as fast as possible.
+
+https://www.loc.gov/preservation/digital/formats/fdd/fdd000512.shtml
+*/
+
 /** Special properties for data connections representing web queries. */
 struct WebPrModel
 {
+    // <tables>
     typedef ::std::vector< css::uno::Any > TablesVector;
-
     TablesVector        maTables;           /// Names or indexes of the web query tables.
+    sal_Int32           mnCount;            /// Number of tables to pull data from when refreshing from a web query.
+
+    // <webPr>
     OUString     maUrl;              /// Source URL to refresh the data.
     OUString     maPostMethod;       /// POST method to query data.
     OUString     maEditPage;         /// Web page showing query data (for XML queries).
@@ -61,22 +76,58 @@ struct WebPrModel
     explicit            WebPrModel();
 };
 
+/** Special properties for <textPr> (Text Import Settings) and its child elements */
+struct TextPrModel
+{
+    css::uno::Sequence<css::uno::Any> maTextPrSequenceAny; // <textPr> attributes.
+    css::uno::Sequence<css::uno::Any> maTextFieldsSequenceAny; // <textFields> attributes.
+    std::vector<css::uno::Sequence<css::uno::Any>>
+        vTextField; // Field settings for text import, <textField>.
+};
+
+/** Special properties for <parameters> (Query Parameters) and its child element. */
+struct ParametersModel
+{
+    // <parameters> has only a single attribute.
+    sal_Int32                                       mnCount;    // The number of parameters used.
+    std::vector<css::uno::Sequence<css::uno::Any>>  vParameter; // holds <parameter> attributes.
+};
+
+/** Special properties for <extLst> (Future Feature Data Storage Area) and its child elements. */
+struct ExtensionListModel
+{
+    // <extLst> has no attribute.
+    // <ext> has only one attribute:
+    // - uri (A token to identify version and application information for the particular extension)
+    std::vector<OUString> vExtension; // holds uri (URI) attribute of <ext> (Extension) element.
+};
+
 /** Common properties of an external data connection. */
 struct ConnectionModel
 {
     typedef ::std::unique_ptr< WebPrModel > WebPrModelPtr;
-
     WebPrModelPtr       mxWebPr;            /// Special settings for web queries.
+
+    std::unique_ptr<TextPrModel> mxTextPr;  /// Text import settings.
+    std::unique_ptr<ParametersModel> mxParameters;  /// Query Parameters settings.
+    std::unique_ptr<ExtensionListModel> mxExtensionList;  /// Extension List settings.
+
+    css::uno::Sequence<css::uno::Any> maDbPrSequenceAny;
+    css::uno::Sequence<css::uno::Any> maOlapPrSequenceAny;
+
     OUString     maName;             /// Unique name of this connection.
     OUString     maDescription;      /// User description of this connection.
     OUString     maSourceFile;       /// URL of a source data file.
-    OUString     maSourceConnFile;   /// URL of a source connection file.
+    OUString     maSourceConnFile;   /// URL of a source connection file. Office Data Connection (ODC) file to share data between Excel users
     OUString     maSsoId;            /// Single sign-on identifier.
+    OUString     maXr16Uid;          /// Value of xr16:uid attribute.
     sal_Int32           mnId;               /// Unique connection identifier.
     sal_Int32           mnType;             /// Data source type.
     sal_Int32           mnReconnectMethod;  /// Reconnection method.
     sal_Int32           mnCredentials;      /// Credentials method.
     sal_Int32           mnInterval;         /// Refresh interval in minutes.
+    sal_Int16           mnRefreshedVersion; /// Refreshed version.
+    sal_Int16           mnMinRefreshableVersion; /// minRefreshableVersion (type="xsd:unsignedByte")
     bool                mbKeepAlive;        /// True = keep connection open after import.
     bool                mbNew;              /// True = new connection, never updated.
     bool                mbDeleted;          /// True = connection has been deleted.
@@ -89,6 +140,9 @@ struct ConnectionModel
     explicit            ConnectionModel();
 
     WebPrModel&         createWebPr();
+    TextPrModel&        createTextPr();
+    ParametersModel&    createParameters();
+    ExtensionListModel& createExtensionList();
 };
 
 /** An external data connection (database, web query, etc.). */
@@ -102,9 +156,27 @@ public:
     /** Imports web query settings from the webPr element. */
     void                importWebPr( const AttributeList& rAttribs );
     /** Imports web query table settings from the tables element. */
-    void                importTables();
+    void                importTables(const AttributeList& rAttribs);
     /** Imports a web query table identifier from the m, s, or x element. */
     void                importTable( const AttributeList& rAttribs, sal_Int32 nElement );
+    /** Imports database settings from the dbPr element. */
+    void                importDbPr(const AttributeList& rAttribs);
+    /** Imports OLAP settings from the olapPr element. */
+    void                importOlapPr(const AttributeList& rAttribs);
+    /** Imports text settings from the textPr element. */
+    void                importTextPr(const AttributeList& rAttribs);
+    /** Set of fields to retrieve from a text file - represents textFields element. */
+    void                importTextFields(const AttributeList& rAttribs);
+    /** Field settings for text import from the textField element. */
+    void                importTextField(const AttributeList& rAttribs);
+    /** Imports collection of parameters for an ODBC or web query from the parameters element. */
+    void                importParameters(const AttributeList& rAttribs);
+    /** Imports properties about any parameters used with external data connections from the parameter element. */
+    void                importParameter(const AttributeList& rAttribs);
+    /** This element provides a convention for extending spreadsheetML in predefined locations, from the extLst element. */
+    void                importExtensionList();
+    /** Imports extensions to the standard SpreadsheetML feature set, from the ext element. */
+    void                importExtension(const AttributeList& rAttribs);
 
     /** Imports connection settings from the CONNECTION record. */
     void                importConnection( SequenceInputStream& rStrm );
@@ -115,13 +187,31 @@ public:
     /** Imports a web query table identifier from the PCITEM_MISSING, PCITEM_STRING, or PCITEM_INDEX record. */
     void                importWebPrTable( SequenceInputStream& rStrm, sal_Int32 nRecId );
 
+    static css::uno::Sequence<css::uno::Any>
+    getSequenceOfAny(css::uno::Reference<css::xml::sax::XFastAttributeList>& xFastAttributeList);
     /** Returns the unique connection identifier. */
     sal_Int32    getConnectionId() const { return maModel.mnId; }
+    /** Returns the version of the application which last refreshed this connection. */
+    sal_Int16    getConnectionRefreshedVersion() const { return maModel.mnRefreshedVersion; }
+    OUString    getConnectionName() const { return maModel.maName; }
     /** Returns the source data type of the connection. */
     sal_Int32    getConnectionType() const { return maModel.mnType; }
     /** Returns read-only access to the connection model data. */
     const ConnectionModel& getModel() const { return maModel; }
 
+    css::uno::Sequence<css::uno::Any> getDbPrSequenceAny() const
+    {
+        return maModel.maDbPrSequenceAny;
+    }
+    css::uno::Sequence<css::uno::Any> getOlapPrSequenceAny() const
+    {
+        return maModel.maOlapPrSequenceAny;
+    }
+    css::uno::Sequence<css::uno::Any> getTextPrSequenceAny() const
+    {
+        return maModel.mxTextPr->maTextPrSequenceAny;
+    }
+
 private:
     ConnectionModel     maModel;
 };
diff --git a/sc/source/filter/inc/excdoc.hxx b/sc/source/filter/inc/excdoc.hxx
index 40748b38f63e..af0ffade53e5 100644
--- a/sc/source/filter/inc/excdoc.hxx
+++ b/sc/source/filter/inc/excdoc.hxx
@@ -94,6 +94,10 @@ public:
     void                ReadDoc();
     void                Write( SvStream& rSvStrm );
     void                WriteXml( XclExpXmlStream& );
+
+    // add an element attributes and values to FastAttributeList
+    static void addElemensToAttrList(rtl::Reference<sax_fastparser::FastAttributeList>& pAttrList,
+                                     css::uno::Sequence<css::uno::Any>& aSeqs);
 };
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/filter/oox/connectionsbuffer.cxx b/sc/source/filter/oox/connectionsbuffer.cxx
index c8a2b700a863..7bd57e256dec 100644
--- a/sc/source/filter/oox/connectionsbuffer.cxx
+++ b/sc/source/filter/oox/connectionsbuffer.cxx
@@ -25,6 +25,10 @@
 #include <oox/token/namespaces.hxx>
 #include <oox/token/tokens.hxx>
 #include <oox/helper/binaryinputstream.hxx>
+#include <sax/fastattribs.hxx>
+#include <documentimport.hxx>
+#include <document.hxx>
+#include <sax/fshelper.hxx>
 
 namespace oox::xls {
 
@@ -67,7 +71,8 @@ const sal_uInt8 BIFF12_WEBPR_HAS_URL                    = 0x04;
 } // namespace
 
 WebPrModel::WebPrModel() :
-    mnHtmlFormat( XML_none ),
+    mnCount( 0 ),
+    mnHtmlFormat( -1 ),
     mbXml( false ),
     mbSourceData( false ),
     mbParsePre( false ),
@@ -84,8 +89,10 @@ ConnectionModel::ConnectionModel() :
     mnId( -1 ),
     mnType( BIFF12_CONNECTION_UNKNOWN ),
     mnReconnectMethod( BIFF12_RECONNECT_AS_REQUIRED ),
-    mnCredentials( XML_integrated ),
+    mnCredentials( -1 ),
     mnInterval( 0 ),
+    mnRefreshedVersion(-1),
+    mnMinRefreshableVersion(0),
     mbKeepAlive( false ),
     mbNew( false ),
     mbDeleted( false ),
@@ -104,10 +111,32 @@ WebPrModel& ConnectionModel::createWebPr()
     return *mxWebPr;
 }
 
+TextPrModel& ConnectionModel::createTextPr()
+{
+    OSL_ENSURE(!mxTextPr, "ConnectionModel::createTextPr - multiple call");
+    mxTextPr.reset(new TextPrModel);
+    return *mxTextPr;
+}
+
+ParametersModel& ConnectionModel::createParameters()
+{
+    OSL_ENSURE(!mxParameters, "ConnectionModel::createParameters - multiple call");
+    mxParameters.reset(new ParametersModel);
+    return *mxParameters;
+}
+
+ExtensionListModel& ConnectionModel::createExtensionList()
+{
+    OSL_ENSURE(!mxExtensionList, "ConnectionModel::createExtensionList - multiple call");
+    mxExtensionList.reset(new ExtensionListModel);
+    return *mxExtensionList;
+}
+
 Connection::Connection( const WorkbookHelper& rHelper ) :
     WorkbookHelper( rHelper )
 {
-    maModel.mnId = -1;
+    maModel.mnId = -1; // use="required"
+    maModel.mnRefreshedVersion = -1; // use="required"
 }
 
 void Connection::importConnection( const AttributeList& rAttribs )
@@ -118,10 +147,12 @@ void Connection::importConnection( const AttributeList& rAttribs )
     maModel.maSourceConnFile  = rAttribs.getXString( XML_odcFile, OUString() );
     maModel.maSsoId           = rAttribs.getXString( XML_singleSignOnId, OUString() );
     maModel.mnId              = rAttribs.getInteger( XML_id, -1 );
+    maModel.mnRefreshedVersion = rAttribs.getInteger(XML_refreshedVersion, -1);
+    maModel.mnMinRefreshableVersion = rAttribs.getInteger(XML_minRefreshableVersion, 0);
     // type and reconnectionMethod are using the BIFF12 constants instead of XML tokens
     maModel.mnType            = rAttribs.getInteger( XML_type, BIFF12_CONNECTION_UNKNOWN );
     maModel.mnReconnectMethod = rAttribs.getInteger( XML_reconnectionMethod, BIFF12_RECONNECT_AS_REQUIRED );
-    maModel.mnCredentials     = rAttribs.getToken( XML_credentials, XML_integrated );
+    maModel.mnCredentials     = rAttribs.getToken( XML_credentials, -1 );
     maModel.mnInterval        = rAttribs.getInteger( XML_interval, 0 );
     maModel.mbKeepAlive       = rAttribs.getBool( XML_keepAlive, false );
     maModel.mbNew             = rAttribs.getBool( XML_new, false );
@@ -131,6 +162,24 @@ void Connection::importConnection( const AttributeList& rAttribs )
     maModel.mbRefreshOnLoad   = rAttribs.getBool( XML_refreshOnLoad, false );
     maModel.mbSaveData        = rAttribs.getBool( XML_saveData, false );
     maModel.mbSavePassword    = rAttribs.getBool( XML_savePassword, false );
+    // FIXME: FSNS(XML_xr16, XML_uid) gets wrong(?) id of xr16:uid
+    // maModel.maXr16Uid = rAttribs.getXString( FSNS(XML_xr16, XML_uid), OUString() );
+
+    // workaround for finding correct XML id of xr16:uid
+    if (auto xFastAttributeList = rAttribs.getFastAttributeList())
+    {
+        css::uno::Sequence<css::xml::FastAttribute> aFast = xFastAttributeList->getFastAttributes();
+
+        for (auto& attr : aFast)
+        {
+            // xr16:uid="{...}" // tokenId = 3347856
+            if (attr.Value.startsWith("{"))
+            {
+                maModel.maXr16Uid = attr.Value;
+                break;
+            }
+        }
+    }
 }
 
 void Connection::importWebPr( const AttributeList& rAttribs )
@@ -140,7 +189,7 @@ void Connection::importWebPr( const AttributeList& rAttribs )
     rWebPr.maUrl             = rAttribs.getXString( XML_url, OUString() );
     rWebPr.maPostMethod      = rAttribs.getXString( XML_post, OUString() );
     rWebPr.maEditPage        = rAttribs.getXString( XML_editPage, OUString() );
-    rWebPr.mnHtmlFormat      = rAttribs.getToken( XML_htmlFormat, XML_none );
+    rWebPr.mnHtmlFormat      = rAttribs.getToken( XML_htmlFormat, -1 );
     rWebPr.mbXml             = rAttribs.getBool( XML_xml, false );
     rWebPr.mbSourceData      = rAttribs.getBool( XML_sourceData, false );
     rWebPr.mbParsePre        = rAttribs.getBool( XML_parsePre, false );
@@ -152,12 +201,32 @@ void Connection::importWebPr( const AttributeList& rAttribs )
     rWebPr.mbHtmlTables      = rAttribs.getBool( XML_htmlTables, false );
 }
 
-void Connection::importTables()
+void Connection::importDbPr(const AttributeList& rAttribs)
+{
+    if (auto xFastAttributeList = rAttribs.getFastAttributeList())
+    {
+        css::uno::Sequence<css::uno::Any> aDbPrAny = getSequenceOfAny(xFastAttributeList);
+        maModel.maDbPrSequenceAny = aDbPrAny;
+    }
+}
+
+void Connection::importOlapPr(const AttributeList& rAttribs)
+{
+    if (auto xFastAttributeList = rAttribs.getFastAttributeList())
+    {
+        css::uno::Sequence<css::uno::Any> aOlapPrAny = getSequenceOfAny(xFastAttributeList);
+        maModel.maOlapPrSequenceAny = aOlapPrAny;
+    }
+}
+
+void Connection::importTables(const AttributeList& rAttribs)
 {
     if( maModel.mxWebPr )
     {
         OSL_ENSURE( maModel.mxWebPr->maTables.empty(), "Connection::importTables - multiple calls" );
         maModel.mxWebPr->maTables.clear();
+
+        maModel.mxWebPr->mnCount = rAttribs.getInteger(XML_count, 0);
     }
 }
 
@@ -169,9 +238,14 @@ void Connection::importTable( const AttributeList& rAttribs, sal_Int32 nElement
     Any aTableAny;
     switch( nElement )
     {
-        case XLS_TOKEN( m ):                                                            break;
-        case XLS_TOKEN( s ):    aTableAny <<= rAttribs.getXString( XML_v, OUString() ); break;
-        case XLS_TOKEN( x ):    aTableAny <<= rAttribs.getInteger( XML_v, -1 );         break;
+        case XLS_TOKEN(m): // no value
+            break;
+        case XLS_TOKEN(s): // character value
+            aTableAny <<= "s," + rAttribs.getXString(XML_v, OUString());
+            break;
+        case XLS_TOKEN(x): // shared items index
+            aTableAny <<= "x," + OUString::number(rAttribs.getInteger(XML_v, -1));
+            break;
         default:
             OSL_ENSURE( false, "Connection::importTable - unexpected element" );
             return;
@@ -179,8 +253,91 @@ void Connection::importTable( const AttributeList& rAttribs, sal_Int32 nElement
     maModel.mxWebPr->maTables.push_back( aTableAny );
 }
 
+void Connection::importTextPr(const AttributeList& rAttribs)
+{
+    TextPrModel& rTextPr = maModel.createTextPr();
+
+    if (auto xFastAttributeList = rAttribs.getFastAttributeList())
+    {
+        css::uno::Sequence<css::uno::Any> aTextPrAny = getSequenceOfAny(xFastAttributeList);
+        rTextPr.maTextPrSequenceAny = aTextPrAny;
+    }
+}
+
+void Connection::importTextFields(const AttributeList& rAttribs)
+{
+    if (maModel.mxTextPr)
+    {
+        OSL_ENSURE(maModel.mxTextPr->vTextField.empty(),
+                   "Connection::importTextFields - multiple calls");
+        maModel.mxTextPr->vTextField.clear();
+
+        if (auto xFastAttributeList = rAttribs.getFastAttributeList())
+        {
+            css::uno::Sequence<css::uno::Any> aTextFieldsAny = getSequenceOfAny(xFastAttributeList);
+            maModel.mxTextPr->maTextFieldsSequenceAny = aTextFieldsAny;
+        }
+    }
+}
+
+void Connection::importTextField(const AttributeList& rAttribs)
+{
+    if (!maModel.mxTextPr)
+        return;
+
+    if (auto xFastAttributeList = rAttribs.getFastAttributeList())
+    {
+        css::uno::Sequence<css::uno::Any> aTextFieldAny = getSequenceOfAny(xFastAttributeList);
+        maModel.mxTextPr->vTextField.push_back(aTextFieldAny);
+    }
+}
+
+void Connection::importParameters(const AttributeList& rAttribs)
+{
+    ParametersModel& rParameters = maModel.createParameters();
+    maModel.mxParameters->vParameter.clear();
+    rParameters.mnCount = rAttribs.getInteger(XML_count, -1);
+}
+
+void Connection::importParameter(const AttributeList& rAttribs)
+{
+    if (!maModel.mxParameters)
+        return;
+
+    if (auto xFastAttributeList = rAttribs.getFastAttributeList())
+    {
+        css::uno::Sequence<css::uno::Any> aParameterAny = getSequenceOfAny(xFastAttributeList);
+        maModel.mxParameters->vParameter.push_back(aParameterAny);
+    }
+}
+
+void Connection::importExtensionList()
+{
+    maModel.createExtensionList();
+    maModel.mxExtensionList->vExtension.clear();
+}
+
+void Connection::importExtension(const AttributeList& rAttribs)
+{
+    if (!maModel.mxExtensionList)
+        return;
+
+    // store uri attributes of <ext> element
+    OUString sUri = rAttribs.getXString(XML_uri, OUString());
+    maModel.mxExtensionList->vExtension.push_back(sUri);
+}
+
+css::uno::Sequence<css::uno::Any> Connection::getSequenceOfAny(
+    css::uno::Reference<css::xml::sax::XFastAttributeList>& xFastAttributeList)
+{
+    css::uno::Sequence<css::xml::FastAttribute> aFast = xFastAttributeList->getFastAttributes();
+    css::uno::Sequence<css::xml::Attribute> aUnk = xFastAttributeList->getUnknownAttributes();
+    return { css::uno::Any(aFast), css::uno::Any(aUnk) };
+}
+
 void Connection::importConnection( SequenceInputStream& rStrm )
 {
+    // TODO: update import&export of Microsoft Excel Binary (XLSB) File Format
     sal_uInt16 nFlags, nStrFlags;
     sal_uInt8 nSavePassword, nCredentials;
     rStrm.skip( 2 );
@@ -292,6 +449,9 @@ void ConnectionsBuffer::finalizeImport()
 {
     for( const auto& rxConnection : maConnections )
         insertConnectionToMap( rxConnection );
+
+    ScDocument& rDoc = getDocImport().getDoc();
+    rDoc.setConnectionVector(maConnections);
 }
 
 ConnectionRef ConnectionsBuffer::getConnection( sal_Int32 nConnId ) const
diff --git a/sc/source/filter/oox/connectionsfragment.cxx b/sc/source/filter/oox/connectionsfragment.cxx
index 3fafefd1dd3b..68b30587c547 100644
--- a/sc/source/filter/oox/connectionsfragment.cxx
+++ b/sc/source/filter/oox/connectionsfragment.cxx
@@ -18,10 +18,12 @@
  */
 
 #include <connectionsfragment.hxx>
+#include <documentimport.hxx>
 
 #include <oox/token/namespaces.hxx>
 #include <biffhelper.hxx>
 #include <connectionsbuffer.hxx>
+#include <sal/log.hxx>
 
 namespace oox::xls {
 
@@ -38,17 +40,42 @@ ContextHandlerRef ConnectionContext::onCreateContext( sal_Int32 nElement, const
     switch( getCurrentElement() )
     {
         case XLS_TOKEN( connection ):
+            if (nElement == XLS_TOKEN(dbPr))
+            {
+                mrConnection.importDbPr(rAttribs);
+                return this;
+            }
+            if (nElement == XLS_TOKEN(olapPr))
+            {
+                mrConnection.importOlapPr(rAttribs);
+                return this;
+            }
             if( nElement == XLS_TOKEN( webPr ) )
             {
                 mrConnection.importWebPr( rAttribs );
                 return this;
             }
+            if (nElement == XLS_TOKEN(textPr))
+            {
+                mrConnection.importTextPr(rAttribs);
+                return this;
+            }
+            if (nElement == XLS_TOKEN(parameters))
+            {
+                mrConnection.importParameters(rAttribs);
+                return this;
+            }
+            if (nElement == XLS_TOKEN(extLst))
+            {
+                mrConnection.importExtensionList();
+                return this;
+            }
         break;
 
         case XLS_TOKEN( webPr ):
             if( nElement == XLS_TOKEN( tables ) )
             {
-                mrConnection.importTables();
+                mrConnection.importTables(rAttribs);
                 return this;
             }
         break;
@@ -56,6 +83,38 @@ ContextHandlerRef ConnectionContext::onCreateContext( sal_Int32 nElement, const
         case XLS_TOKEN( tables ):
             mrConnection.importTable( rAttribs, nElement );
         break;
+
+        case XLS_TOKEN(textPr):
+            if (nElement == XLS_TOKEN(textFields))
+            {
+                mrConnection.importTextFields(rAttribs);
+                return this;
+            }
+            break;
+
+        case XLS_TOKEN(textFields):
+            if (nElement == XLS_TOKEN(textField))
+            {
+                mrConnection.importTextField(rAttribs);
+                return this;
+            }
+            break;
+
+        case XLS_TOKEN(parameters):
+            if (nElement == XLS_TOKEN(parameter))
+            {
+                mrConnection.importParameter(rAttribs);
+                return this;
+            }
+            break;
+
+        case XLS_TOKEN(extLst):
+            if (nElement == XLS_TOKEN(ext))
+            {
+                mrConnection.importExtension(rAttribs);
+                return this;
+            }
+            break;
     }
     return nullptr;
 }
diff --git a/sc/source/filter/oox/workbookfragment.cxx b/sc/source/filter/oox/workbookfragment.cxx
index ea3b0aca4ac1..14584891a21d 100644
--- a/sc/source/filter/oox/workbookfragment.cxx
+++ b/sc/source/filter/oox/workbookfragment.cxx
@@ -76,6 +76,8 @@
 #include <comphelper/processfactory.hxx>
 #include <officecfg/Office/Calc.hxx>
 
+#include <xestream.hxx>
+
 namespace oox::xls {
 
 using namespace ::com::sun::star::io;
@@ -379,7 +381,16 @@ void WorkbookFragment::finalizeImport()
     // read the connections substream
     OUString aConnFragmentPath = getFragmentPathFromFirstTypeFromOfficeDoc( u"connections" );
     if( !aConnFragmentPath.isEmpty() )
+    {
         importOoxFragment( new ConnectionsFragment( *this, aConnFragmentPath ) );
+        getScDocument().setHasConnectionXml(true);
+    }
+
+    // read the customXml substream
+    OUString aCustomXmlFragmentPath = getFragmentPathFromFirstTypeFromOfficeDoc( u"customXml" );
+    if (!aCustomXmlFragmentPath.isEmpty())
+        getScDocument().setHasCustomXml(true, aCustomXmlFragmentPath);
+
     xGlobalSegment->setPosition( 1.0 );
 
     /*  Create fragments for all sheets, before importing them. Needed to do