Using XSLT to transform XML into Excel File

Question

Before anything i'm kind of new to XSLT , i've been asked to transform a XML file into an Excel file.

After of couple hours playing around with xslt i've came up with this.

This is a sample of my XML i've blured some information with *** since it's important stuff!

<RELATORIO>
  <LANG>PT</LANG>
  <MODULO>
    <NAME>ObtemIdentificacao_P</NAME>
    <VALUES>
      <ROW>
        <Cae1>46740</Cae1>
        <Cae2 />
        <Cae3 />
        <CodCP>***</CodCP>
        <Concelho>SINTRA</Concelho>
        <DataInvestigacao>20160418</DataInvestigacao>
        <DescricaoCP>SINTRA</DescricaoCP>
        <Distrito>LISBOA</Distrito>
        <Email>[email protected]</Email>
        <Fax1>*****</Fax1>
        <Fax2>****</Fax2>
        <Fax3 />
        <Internet>www.wurth.pt</Internet>
        <Localidade />
        <Moeda>EURO</Moeda>
        <Morada>Estrada Nacional - 249-4 - Abrunheira</Morada>
        <Nome>WURTH (PORTUGAL)-TECNICA DE MONTAGEM LDA</Nome>
        <NumeroContribuinte>****</NumeroContribuinte>
        <NumeroSine>38358</NumeroSine>
        <pais>174</pais>
        <Telefone1>***</Telefone1>
      </ROW>
    </VALUES>
  </MODULO>
</RELATORIO>

Here's the XSLT i came up with

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output omit-xml-declaration="no"/>
  <xsl:template match="/">
    <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
      <Styles>
        <Style ss:ID="header" ss:Name="Normal">
          <Font ss:FontName="Verdana" ss:Bold="1" />
        </Style>
      </Styles>
      <Worksheet ss:Name="Identificacao">
        <Table>
          <Row ss:Index="1">
            <Cell ss:Index="1" ss:StyleID="header">
              <Data ss:Type="String">Nome</Data>
            </Cell>
            <Cell ss:Index="2" ss:StyleID="header">
              <Data ss:Type="String">Nº Sine</Data>
            </Cell>
            <Cell ss:Index="3" ss:StyleID="header">
              <Data ss:Type="String">Nº Contribuinte</Data>
            </Cell>
            <Cell ss:Index="4" ss:StyleID="header">
              <Data ss:Type="String">Distrito</Data>
            </Cell>
            <Cell ss:Index="5" ss:StyleID="header">
              <Data ss:Type="String">Concelho</Data>
            </Cell>
            <Cell ss:Index="6" ss:StyleID="header">
              <Data ss:Type="String">Código Postal</Data>
            </Cell>
            <Cell ss:Index="7" ss:StyleID="header">
              <Data ss:Type="String">Morada</Data>
            </Cell>
            <Cell ss:Index="8" ss:StyleID="header">
              <Data ss:Type="String">Telefone</Data>
            </Cell>
            <Cell ss:Index="9" ss:StyleID="header">
              <Data ss:Type="String">Email</Data>
            </Cell>
            <Cell ss:Index="10" ss:StyleID="header">
              <Data ss:Type="String">Fax</Data>
            </Cell>
            <Cell ss:Index="11" ss:StyleID="header">
              <Data ss:Type="String">Pais</Data>
            </Cell>
          </Row>
          <Row ss:Index="{position()}">
            <Cell ss:Index="1">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/Nome"/>
              </Data>
            </Cell>
            <Cell ss:Index="2">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/NumeroSine"/>
              </Data>
            </Cell>
            <Cell ss:Index="3">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/NumeroContribuinte"/>
              </Data>
            </Cell>
            <Cell ss:Index="4">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/Distrito"/>
              </Data>
            </Cell>
            <Cell ss:Index="5">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/Concelho"/>
              </Data>
            </Cell>
            <Cell ss:Index="6">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/CodCP"/>
              </Data>
            </Cell>
            <Cell ss:Index="7">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/Morada"/>
              </Data>
            </Cell>
            <Cell ss:Index="8">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/Telefone1"/>
              </Data>
            </Cell>
            <Cell ss:Index="9">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/Email"/>
              </Data>
            </Cell>
            <Cell ss:Index="10">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/Fax1"/>
              </Data>
            </Cell>
            <Cell ss:Index="11">
              <Data ss:Type="String">
                <xsl:value-of select="VALUES/ROW/pais"/>
              </Data>
            </Cell>
          </Row>
        </Table>
      </Worksheet>
    </Workbook>
  </xsl:template>
</xsl:stylesheet>

This XSLT does produce an Excel file but the only thing shown in the Excel are the Names of the Fields, like

Nome | Nº Sine | Nº Contribuinte | Etc..

But no values are shown below the Fields.

How do i get the values of the text inside the nodes to be shown in the Excel file?

And if possible i wanted each field to have it's own row instead of being a Column.

The <xsl:value-of select="VALUES/ROW/Nome"/> is selecting nothing. Try <xsl:value-of select="//VALUES/ROW/Nome"/>. The same for each <xsl:value-of select="//VALUES/ROW/..."/>. — Axel Richter
– Axel Richter, Commented Feb 22, 2018 at 10:45
Oh, and position() starts with 1. But you have row 1 already as the heading row. So <Row ss:Index="{position()+1}"> — Axel Richter
– Axel Richter, Commented Feb 22, 2018 at 11:03

Tim C · Accepted Answer · 2018-02-22 11:18:06Z

3

Do you want one row per MODULO? If so, you should select these elements with xsl:for-each, or xsl:apply-templates

Try this XSLT (which I have shortened for ease of reading)

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output omit-xml-declaration="no" indent="yes"/>

  <xsl:template match="/">
    <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
      <Styles>
        <Style ss:ID="header" ss:Name="Normal">
          <Font ss:FontName="Verdana" ss:Bold="1" />
        </Style>
      </Styles>
      <Worksheet ss:Name="Identificacao">
        <Table>
          <Row ss:Index="1">
            <Cell ss:Index="1" ss:StyleID="header">
              <Data ss:Type="String">Nome</Data>
            </Cell>
            <Cell ss:Index="2" ss:StyleID="header">
              <Data ss:Type="String">Nº Sine</Data>
            </Cell>
          </Row>
          <xsl:for-each select="RELATORIO/MODULO">
            <Row ss:Index="{position() + 1}">
              <Cell ss:Index="1">
                <Data ss:Type="String">
                  <xsl:value-of select="VALUES/ROW/Nome"/>
                </Data>
              </Cell>
              <Cell ss:Index="2">
                <Data ss:Type="String">
                  <xsl:value-of select="VALUES/ROW/NumeroSine"/>
                </Data>
              </Cell>
            </Row>   
          </xsl:for-each>
        </Table>
      </Worksheet>
    </Workbook>
  </xsl:template>
</xsl:stylesheet>

Notice how it selects RELATORIO/MODULO and not MODULO. This is because the code is in a template that matches / which is the document node, and RELATORIO is a child of the document node.

answered Feb 22, 2018 at 11:18

Tim C

70.7k14 gold badges77 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gustavo Jose Over a year ago

Thank you ! The Excel file was not opening and was giving a weird error but it's working now! Troughtout the whole XML the Rows and Columns will differ from "MODULO" to "MODULO" some i want columns and some i want rows , but i think i can work that out myself from the code you gave me! Thank you again :)

Gustavo Jose Over a year ago

Hello again! I have a different question about the same thing, The code you gave me was perfect! The only problem now is that i have like 20 more "MODULO" to convert to Excel ( It's a big Report) and each "MODULO" is unique , with different names likie <NAME>ObtemIdentificacao_P<NAME> How do i identify each MODULO by its name ?

Gustavo Jose Over a year ago

So i can make multiple Worksheets, one for each MODULO

Tim C Over a year ago

It would be better to ask a whole new question here on SO, rather than try and answer this in comments. Thanks!

Collectives™ on Stack Overflow

Using XSLT to transform XML into Excel File

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related