tdf#106525: Implement an XSLT import filter for ADO rowset XML

There are online services and management systems (like SharePoint)
that allow to export datasets in ADO rowset XML format ([MS-PRSTFR],
https://msdn.microsoft.com/en-us/library/cc313112). Usually they are
intended to be open with MS Excel as a spreadsheet (with autofilter).

This allows to open this data in Calc.

Change-Id: I495cd790138bdd6bd24630c0f422a0c8b4e3d0fb
Reviewed-on: https://gerrit.libreoffice.org/35159
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Mike Kaganski <mike.kaganski@collabora.com>
(cherry picked from commit d6323a2180cf51f9bd6a62d50503c2e0ef0964f1)
Reviewed-on: https://gerrit.libreoffice.org/36131
Tested-by: Mike Kaganski <mike.kaganski@collabora.com>

6 files changed, 278 insertions, 0 deletions

diff --git a/filter/Configuration_filter.mk b/filter/Configuration_filter.mk
index 2d36991785a6..49e09aeb9c3b 100644
--- a/filter/Configuration_filter.mk
+++ b/filter/Configuration_filter.mk
@@ -871,6 +871,7 @@ $(eval $(call filter_Configuration_add_internal_filters,fcfg_langpack,fcfg_inter
 
 # fcfg_xslt
 $(eval $(call filter_Configuration_add_types,fcfg_langpack,fcfg_xslt_types.xcu,filter/source/config/fragments/types,\
+	calc_ADO_rowset_XML \
 	calc_MS_Excel_2003_XML \
 	writer_DocBook_File \
 	writer_MS_Word_2003_XML \
@@ -881,6 +882,7 @@ $(eval $(call filter_Configuration_add_types,fcfg_langpack,fcfg_xslt_types.xcu,f
 ))
 
 $(eval $(call filter_Configuration_add_filters,fcfg_langpack,fcfg_xslt_filters.xcu,filter/source/config/fragments/filters,\
+	ADO_rowset_XML \
 	DocBook_File \
 	MS_Excel_2003_XML \
 	MS_Word_2003_XML \
diff --git a/filter/Package_xslt.mk b/filter/Package_xslt.mk
index 314ff7abb0a9..3fe8ff92fdbf 100644
--- a/filter/Package_xslt.mk
+++ b/filter/Package_xslt.mk
@@ -43,6 +43,7 @@ $(eval $(call gb_Package_add_file,filter_xslt,$(LIBO_SHARE_FOLDER)/xslt/export/w
 $(eval $(call gb_Package_add_file,filter_xslt,$(LIBO_SHARE_FOLDER)/xslt/export/wordml/ooo2wordml_text.xsl,export/wordml/ooo2wordml_text.xsl))
 $(eval $(call gb_Package_add_file,filter_xslt,$(LIBO_SHARE_FOLDER)/xslt/import/common/ms2ooo_docpr.xsl,import/common/ms2ooo_docpr.xsl))
 $(eval $(call gb_Package_add_file,filter_xslt,$(LIBO_SHARE_FOLDER)/xslt/import/spreadsheetml/spreadsheetml2ooo.xsl,import/spreadsheetml/spreadsheetml2ooo.xsl))
+$(eval $(call gb_Package_add_file,filter_xslt,$(LIBO_SHARE_FOLDER)/xslt/import/spreadsheetml/adorowset2ods.xsl,import/spreadsheetml/adorowset2ods.xsl))
 $(eval $(call gb_Package_add_file,filter_xslt,$(LIBO_SHARE_FOLDER)/xslt/import/uof/uof2odf_presentation.xsl,import/uof/uof2odf_presentation.xsl))
 $(eval $(call gb_Package_add_file,filter_xslt,$(LIBO_SHARE_FOLDER)/xslt/import/uof/uof2odf_spreadsheet.xsl,import/uof/uof2odf_spreadsheet.xsl))
 $(eval $(call gb_Package_add_file,filter_xslt,$(LIBO_SHARE_FOLDER)/xslt/import/uof/uof2odf_text.xsl,import/uof/uof2odf_text.xsl))
diff --git a/filter/source/config/cache/typedetection.cxx b/filter/source/config/cache/typedetection.cxx
index 92c10074d454..3559e9b65276 100644
--- a/filter/source/config/cache/typedetection.cxx
+++ b/filter/source/config/cache/typedetection.cxx
@@ -243,6 +243,7 @@ int getFlatTypeRank(const OUString& rType)
         "calc_ODS_FlatXML",
         "impress_ODP_FlatXML",
         "draw_ODG_FlatXML",
+        "calc_ADO_rowset_XML",
         "calc_MS_Excel_2003_XML",
         "writer_MS_Word_2003_XML",
         "writer_DocBook_File",
diff --git a/filter/source/config/fragments/filters/ADO_rowset_XML.xcu b/filter/source/config/fragments/filters/ADO_rowset_XML.xcu
new file mode 100644
index 000000000000..c31848ef82a0
--- /dev/null
+++ b/filter/source/config/fragments/filters/ADO_rowset_XML.xcu
@@ -0,0 +1,30 @@
+<!--
+ * 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/.
+ *
+ * This file incorporates work covered by the following license notice:
+ *
+ *   Licensed to the Apache Software Foundation (ASF) under one or more
+ *   contributor license agreements. See the NOTICE file distributed
+ *   with this work for additional information regarding copyright
+ *   ownership. The ASF licenses this file to you under the Apache
+ *   License, Version 2.0 (the "License"); you may not use this file
+ *   except in compliance with the License. You may obtain a copy of
+ *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
+-->
+    <node oor:name="ADO Rowset XML" oor:op="replace">
+        <prop oor:name="Flags"><value>IMPORT ALIEN 3RDPARTYFILTER</value></prop>
+        <prop oor:name="UIComponent"/>
+        <prop oor:name="FilterService"><value>com.sun.star.comp.Writer.XmlFilterAdaptor</value></prop>
+        <prop oor:name="UserData"><value oor:separator=",">com.sun.star.documentconversion.XSLTFilter,,com.sun.star.comp.Calc.XMLOasisImporter,com.sun.star.comp.Calc.XMLOasisExporter,../$(share_subdir_name)/xslt/import/spreadsheetml/adorowset2ods.xsl,</value></prop>
+        <prop oor:name="FileFormatVersion"><value>0</value></prop>
+        <prop oor:name="Type"><value>calc_ADO_rowset_XML</value></prop>
+        <prop oor:name="TemplateName"/>
+        <prop oor:name="DocumentService"><value>com.sun.star.sheet.SpreadsheetDocument</value></prop>
+        <prop oor:name="UIName">
+            <value xml:lang="en-US">ADO Rowset XML</value>
+        </prop>
+    </node>
diff --git a/filter/source/config/fragments/types/calc_ADO_rowset_XML.xcu b/filter/source/config/fragments/types/calc_ADO_rowset_XML.xcu
new file mode 100644
index 000000000000..2f02f568ed15
--- /dev/null
+++ b/filter/source/config/fragments/types/calc_ADO_rowset_XML.xcu
@@ -0,0 +1,29 @@
+<!--
+ * 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/.
+ *
+ * This file incorporates work covered by the following license notice:
+ *
+ *   Licensed to the Apache Software Foundation (ASF) under one or more
+ *   contributor license agreements. See the NOTICE file distributed
+ *   with this work for additional information regarding copyright
+ *   ownership. The ASF licenses this file to you under the Apache
+ *   License, Version 2.0 (the "License"); you may not use this file
+ *   except in compliance with the License. You may obtain a copy of
+ *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
+-->
+    <node oor:name="calc_ADO_rowset_XML" oor:op="replace">
+        <prop oor:name="DetectService"><value>com.sun.star.comp.filters.XMLFilterDetect</value></prop>
+        <prop oor:name="URLPattern"/>
+        <prop oor:name="Extensions"><value>xml</value></prop>
+        <prop oor:name="MediaType"/>
+        <prop oor:name="Preferred"><value>false</value></prop>
+        <prop oor:name="PreferredFilter"><value>ADO Rowset XML</value></prop>
+        <prop oor:name="UIName">
+            <value xml:lang="en-US">ADO Rowset XML</value>
+        </prop>
+        <prop oor:name="ClipboardFormat"><value>doctype:urn:schemas-microsoft-com:rowset</value></prop>
+    </node>
diff --git a/filter/source/xslt/import/spreadsheetml/adorowset2ods.xsl b/filter/source/xslt/import/spreadsheetml/adorowset2ods.xsl
new file mode 100644
index 000000000000..d8e77f39a047
--- /dev/null
+++ b/filter/source/xslt/import/spreadsheetml/adorowset2ods.xsl
@@ -0,0 +1,215 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
+                              xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
+                              xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
+                              xmlns:rs="urn:schemas-microsoft-com:rowset"
+                              xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
+                              xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"
+                              xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
+                              xmlns:calcext="urn:org:documentfoundation:names:experimental:calc:xmlns:calcext:1.0"
+                              xmlns:z="#RowsetSchema">
+  <xsl:output indent="no" version="1.0" encoding="UTF-8" method="xml"/>
+  <xsl:template match="/">
+    <office:document office:mimetype="application/vnd.oasis.opendocument.spreadsheet" office:version="1.0">
+      <xsl:element name="office:body">
+        <xsl:element name="office:spreadsheet">
+          <!-- Just a single table (sheet) with default name -->
+          <xsl:element name="table:table">
+            <!-- declare columns -->
+            <xsl:for-each select="./xml/s:Schema/s:ElementType[@name='row']/s:AttributeType">
+              <xsl:element name="table:table-column"/>
+            </xsl:for-each>
+            <!-- header row from Schema -->
+            <xsl:element name="table:table-row">
+              <xsl:for-each select="./xml/s:Schema/s:ElementType[@name='row']/s:AttributeType">
+                <xsl:element name="table:table-cell">
+                  <xsl:attribute name="office:value-type">string</xsl:attribute>
+                  <xsl:attribute name="calcext:value-type">string</xsl:attribute>
+                  <xsl:element name="text:p">
+                    <!-- User-readable field name may be defined in optional @rs:name -->
+                    <xsl:choose>
+                      <xsl:when test="./@rs:name">
+                        <xsl:value-of select="./@rs:name"/>
+                      </xsl:when>
+                      <xsl:otherwise>
+                        <xsl:value-of select="./@name"/>
+                      </xsl:otherwise>
+                    </xsl:choose>
+                  </xsl:element>
+                </xsl:element>
+              </xsl:for-each>
+            </xsl:element>
+            <!-- Now add data itself -->
+            <xsl:apply-templates select="./xml/rs:data"/>
+          </xsl:element>
+          <!-- Add autofilter to the whole range -->
+          <xsl:element name="table:database-ranges">
+            <xsl:element name="table:database-range">
+              <xsl:attribute name="table:target-range-address">
+                <xsl:call-template name="RangeName">
+                  <xsl:with-param name="rowStartNum" select="1"/>
+                  <xsl:with-param name="colStartNum" select="1"/>
+                  <xsl:with-param name="rowEndNum" select="count(/xml/rs:data/row)+1"/>
+                  <xsl:with-param name="colEndNum" select="count(/xml/s:Schema/s:ElementType[@name='row']/s:AttributeType)"/>
+                </xsl:call-template>
+              </xsl:attribute>
+              <xsl:attribute name="table:display-filter-buttons">true</xsl:attribute>
+            </xsl:element>
+          </xsl:element>
+        </xsl:element>
+      </xsl:element>
+    </office:document>
+  </xsl:template>
+  <xsl:template match="rs:data">
+    <xsl:apply-templates select="./row"/>
+    <xsl:apply-templates select="./z:row"/>
+  </xsl:template>
+  <xsl:template match="row|z:row">
+    <xsl:element name="table:table-row">
+      <!-- Store current row in a variable -->
+      <xsl:variable name="thisRow" select="."/>
+      <!-- Get column order from Schema -->
+      <xsl:for-each select="/xml/s:Schema/s:ElementType[@name='row']/s:AttributeType">
+        <xsl:element name="table:table-cell">
+          <xsl:variable name="thisColName" select="./@name"/>
+          <xsl:variable name="thisCellValue">
+            <xsl:value-of select="$thisRow/@*[local-name()=$thisColName]"/>
+          </xsl:variable>
+          <xsl:if test="string-length($thisCellValue) &gt; 0">
+            <xsl:variable name="thisColType">
+              <xsl:call-template name="ValTypeFromAttributeType">
+                <xsl:with-param name="AttributeTypeNode" select="."/>
+              </xsl:call-template>
+            </xsl:variable>
+            <xsl:attribute name="office:value-type"><xsl:value-of select="$thisColType"/></xsl:attribute>
+            <xsl:attribute name="calcext:value-type"><xsl:value-of select="$thisColType"/></xsl:attribute>
+            <xsl:choose>
+              <xsl:when test="$thisColType='float'">
+                <xsl:attribute name="office:value"><xsl:value-of select="$thisCellValue"/></xsl:attribute>
+              </xsl:when>
+              <xsl:when test="$thisColType='date'">
+                <xsl:attribute name="office:date-value"><xsl:value-of select="$thisCellValue"/></xsl:attribute>
+              </xsl:when>
+              <xsl:when test="$thisColType='time'">
+                <xsl:attribute name="office:time-value"><xsl:value-of select="$thisCellValue"/></xsl:attribute>
+              </xsl:when>
+              <xsl:when test="$thisColType='boolean'">
+                <xsl:attribute name="office:boolean-value">
+                  <xsl:choose>
+                    <xsl:when test="$thisCellValue=0">false</xsl:when>
+                    <xsl:otherwise>true</xsl:otherwise>
+                  </xsl:choose>
+                </xsl:attribute>
+              </xsl:when>
+            </xsl:choose>
+            <xsl:element name="text:p">
+              <xsl:value-of select="$thisCellValue"/>
+            </xsl:element>
+          </xsl:if>
+        </xsl:element>
+      </xsl:for-each>
+    </xsl:element>
+  </xsl:template>
+  <!-- https://msdn.microsoft.com/en-us/library/ms675943 -->
+  <xsl:template name="ValTypeFromAttributeType">
+    <xsl:param name="AttributeTypeNode"/>
+    <xsl:variable name="thisDataType">
+      <xsl:choose>
+        <xsl:when test="$AttributeTypeNode/@dt:type"><xsl:value-of select="$AttributeTypeNode/@dt:type"/></xsl:when>
+        <xsl:when test="$AttributeTypeNode/s:datatype"><xsl:value-of select="$AttributeTypeNode/s:datatype/@dt:type"/></xsl:when>
+        <xsl:otherwise>string</xsl:otherwise>
+      </xsl:choose>
+    </xsl:variable>
+    <xsl:call-template name="XMLDataType2ValType">
+      <xsl:with-param name="XMLDataType" select="$thisDataType"/>
+    </xsl:call-template>
+  </xsl:template>
+  <!-- https://www.w3.org/TR/1998/NOTE-XML-data-0105/#API -->
+  <xsl:template name="XMLDataType2ValType">
+    <xsl:param name="XMLDataType"/>
+    <xsl:choose>
+      <xsl:when test="$XMLDataType='number'">float</xsl:when>
+      <xsl:when test="$XMLDataType='int'">float</xsl:when>
+      <xsl:when test="starts-with($XMLDataType, 'float')">float</xsl:when>
+      <xsl:when test="starts-with($XMLDataType, 'fixed')">float</xsl:when>
+      <xsl:when test="$XMLDataType='i1'">float</xsl:when>
+      <xsl:when test="$XMLDataType='i2'">float</xsl:when>
+      <xsl:when test="$XMLDataType='i4'">float</xsl:when>
+      <xsl:when test="$XMLDataType='i8'">float</xsl:when>
+      <xsl:when test="$XMLDataType='ui1'">float</xsl:when>
+      <xsl:when test="$XMLDataType='ui2'">float</xsl:when>
+      <xsl:when test="$XMLDataType='ui4'">float</xsl:when>
+      <xsl:when test="$XMLDataType='ui8'">float</xsl:when>
+      <xsl:when test="$XMLDataType='r4'">float</xsl:when>
+      <xsl:when test="$XMLDataType='r8'">float</xsl:when>
+      <xsl:when test="$XMLDataType='datetime'">date</xsl:when>
+      <xsl:when test="starts-with($XMLDataType, 'dateTime')">date</xsl:when>
+      <xsl:when test="starts-with($XMLDataType, 'date')">date</xsl:when>
+      <xsl:when test="starts-with($XMLDataType, 'time')">time</xsl:when>
+      <xsl:when test="$XMLDataType='boolean'">boolean</xsl:when>
+      <xsl:otherwise>string</xsl:otherwise>
+    </xsl:choose>
+  </xsl:template>
+  <!-- An utility to convert a column number (e.g. 27; 1-based) to column name (like AA) -->
+  <xsl:template name="ColNum2Name">
+    <xsl:param name="num"/>
+    <xsl:if test="$num > 0">
+      <xsl:call-template name="ColNum2Name">
+        <xsl:with-param name="num" select="floor($num div 26)"/>
+      </xsl:call-template>
+      <xsl:choose>
+        <xsl:when test="$num mod 26 = 1">A</xsl:when>
+        <xsl:when test="$num mod 26 = 2">B</xsl:when>
+        <xsl:when test="$num mod 26 = 3">C</xsl:when>
+        <xsl:when test="$num mod 26 = 4">D</xsl:when>
+        <xsl:when test="$num mod 26 = 5">E</xsl:when>
+        <xsl:when test="$num mod 26 = 6">F</xsl:when>
+        <xsl:when test="$num mod 26 = 7">G</xsl:when>
+        <xsl:when test="$num mod 26 = 8">H</xsl:when>
+        <xsl:when test="$num mod 26 = 9">I</xsl:when>
+        <xsl:when test="$num mod 26 = 10">J</xsl:when>
+        <xsl:when test="$num mod 26 = 11">K</xsl:when>
+        <xsl:when test="$num mod 26 = 12">L</xsl:when>
+        <xsl:when test="$num mod 26 = 13">M</xsl:when>
+        <xsl:when test="$num mod 26 = 14">N</xsl:when>
+        <xsl:when test="$num mod 26 = 15">O</xsl:when>
+        <xsl:when test="$num mod 26 = 16">P</xsl:when>
+        <xsl:when test="$num mod 26 = 17">Q</xsl:when>
+        <xsl:when test="$num mod 26 = 18">R</xsl:when>
+        <xsl:when test="$num mod 26 = 19">S</xsl:when>
+        <xsl:when test="$num mod 26 = 20">T</xsl:when>
+        <xsl:when test="$num mod 26 = 21">U</xsl:when>
+        <xsl:when test="$num mod 26 = 22">V</xsl:when>
+        <xsl:when test="$num mod 26 = 23">W</xsl:when>
+        <xsl:when test="$num mod 26 = 24">X</xsl:when>
+        <xsl:when test="$num mod 26 = 25">Y</xsl:when>
+        <xsl:otherwise>Z</xsl:otherwise><!-- 0 -->
+      </xsl:choose>
+    </xsl:if>
+  </xsl:template>
+  <!-- An utility to convert a cell address (e.g. row 2, column 27) to cell name (like AA2) -->
+  <xsl:template name="CellName">
+    <xsl:param name="rowNum"/>
+    <xsl:param name="colNum"/>
+    <xsl:call-template name="ColNum2Name">
+      <xsl:with-param name="num" select="$colNum"/>
+    </xsl:call-template>
+    <xsl:value-of select="$rowNum"/>
+  </xsl:template>
+  <!-- An utility to convert a range given in terms of numbers (e.g. row 1, column 1 to row 2, column 27) to range name (like A1:AA2) -->
+  <xsl:template name="RangeName">
+    <xsl:param name="rowStartNum"/>
+    <xsl:param name="colStartNum"/>
+    <xsl:param name="rowEndNum"/>
+    <xsl:param name="colEndNum"/>
+    <xsl:call-template name="CellName">
+      <xsl:with-param name="rowNum" select="$rowStartNum"/>
+      <xsl:with-param name="colNum" select="$colStartNum"/>
+    </xsl:call-template>
+    <xsl:text>:</xsl:text>
+    <xsl:call-template name="CellName">
+      <xsl:with-param name="rowNum" select="$rowEndNum"/>
+      <xsl:with-param name="colNum" select="$colEndNum"/>
+    </xsl:call-template>
+  </xsl:template>
+</xsl:stylesheet>
\ No newline at end of file