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>info@wurth.pt</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.
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.