diff options
Diffstat (limited to 'filter/source/xslt/import/spreadsheetml/adorowset2ods.xsl')
-rw-r--r-- | filter/source/xslt/import/spreadsheetml/adorowset2ods.xsl | 216 |
1 files changed, 216 insertions, 0 deletions
diff --git a/filter/source/xslt/import/spreadsheetml/adorowset2ods.xsl b/filter/source/xslt/import/spreadsheetml/adorowset2ods.xsl new file mode 100644 index 000000000..0d36494d2 --- /dev/null +++ b/filter/source/xslt/import/spreadsheetml/adorowset2ods.xsl @@ -0,0 +1,216 @@ +<?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) > 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'"> + <!-- We need to convert '2017-04-06 00:40:40' to '2017-04-06T00:40:40', so replace space with 'T' --> + <xsl:attribute name="office:date-value"><xsl:value-of select="translate($thisCellValue,' ','T')"/></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> + <!-- A 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> + <!-- A 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> + <!-- A 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> |