xmlexcelxsltspreadsheetml

XSLT Transformation to Excel


I have to transform the result of a query (xml format) into an Excel file.

Example of the query-result :

<?xml version="1.0" encoding="UTF-8"?>
<dbqueries>
<dbquery id="main">
    <rows>
        <row user="Me" user-id="1"/>
        <row user="Myself" user-id="2"/>
        <row user="I" user-id="3"/>
    </rows>
</dbquery>
</dbqueries>

I use the following XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet 
version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:output method="xml" indent="yes" encoding="UTF-8" />   
<xsl:template match="/dbqueries">
    <xsl:processing-instruction name="mso-application">
        <xsl:text>progid="Excel.Sheet"</xsl:text>
    </xsl:processing-instruction>

    <Workbook>  
        <Styles>
            <Style ss:ID="Default" ss:Name="Normal">
                <Alignment ss:Vertical="Bottom" />
                <Borders />
                <Font />
                <Interior />
                <NumberFormat />
                <Protection />
            </Style>
            <Style ss:ID="s21">
                <Font ss:Size="22" ss:Bold="1" />
            </Style>
            <Style ss:ID="s22">
                <Font ss:Size="14" ss:Bold="1" />
            </Style>
            <Style ss:ID="s23">
                <Font ss:Size="12" ss:Bold="1" />
            </Style>
            <Style ss:ID="s24">
                <Font ss:Size="10" ss:Bold="1" />
            </Style>
        </Styles>
        <!-- Write each query -->
        <xsl:for-each select="dbquery">
            <Worksheet ss:Name="{@id}">
                <Table>
                    <xsl:apply-templates select="//rows" mode="rows" />
                </Table>
            </Worksheet>
        </xsl:for-each>
    </Workbook>
</xsl:template>
<xsl:template match="//rows/row" mode="rows">
    <xsl:variable name="x" select="position()" />
    <Row ss:Index="{$x}">
        <Cell ss:Index="1">
            <Data ss:Type="String">
                <xsl:value-of select="@id" />
            </Data>
        </Cell>
        <Cell ss:Index="2">
            <Data ss:Type="String">
                <xsl:value-of select="@user" />
            </Data>
        </Cell>
    </Row>
</xsl:template>

The conversion works except that the first character is lost during the conversion.

See below :

Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

What is possibly going wrong ?


Solution

  • The question may be closed. The solution to the problem was the fact that in the used framework I had to ask a type of result "application/vnd.ms-excel" and that I asked "text/xml" instead.