Create Excel (SpeadsheetML) output with XSLT

Question

I have this XML file and I want to create an XSL file to convert it to Excel. Each row should represent a logo. The columns will be the key attributes like color, id, description plus any other key for other logos.

<Top>
  <logo>
    <field key="id">172-32-1176</field>
    <field key="color">Blue</field>
    <field key="description"><p>Short Description</p></field>
    <field key="startdate">408 496-7223</field>
  </logo>
  <logo>
    <field key="id">111-111-111</field>
    <field key="color">Red</field>
  </logo>
  <!-- ... -->    
</Top>

The XSL file is something like this:

<xsl:stylesheet 
  version="1.0"
  xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  xmlns:user="urn:my-scripts"
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns:x="urn:schemas-microsoft-com:office:excel"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
> 

  <xsl:template match="/">
    <Workbook 
      xmlns="urn:schemas-microsoft-com:office:spreadsheet"
      xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns:x="urn:schemas-microsoft-com:office:excel"
      xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
      xmlns:html="http://www.w3.org/TR/REC-html40"
    >
      <xsl:apply-templates/>
    </Workbook>
  </xsl:template>

  <xsl:template match="/*">  
    <Worksheet>
      <xsl:attribute name="ss:Name">
        <xsl:value-of> select="local-name(/*)"/>
      </xsl:attribute>
      <Table x:FullColumns="1" x:FullRows="1">
        <Row>
          <xsl:for-each select="*/*">
            <Cell>
              <Data ss:Type="String">
                <xsl:value-of select="@key"/>
              </Data>
            </Cell>
          </xsl:for-each>
        </Row>
        <xsl:apply-templates/>
      </Table>
    </Worksheet>
  </xsl:template>

  <xsl:template match="/*/*">
    <Row>
      <xsl:apply-templates/>
    </Row>
  </xsl:template>

  <xsl:template match="/*/*/*">
    <Cell>
      <Data ss:Type="String">
        <xsl:value-of select="."/>
      </Data>
    </Cell>
    <!-- <xsl:apply-templates/> -->
  </xsl:template>
</xsl:stylesheet>

But data are not correctly placed under the columns and column names are repeating. How can this be done? The columns could be in any order and also column stardate should be empty for second row in excel. Similarly for more .

Answer 1

You were very close. Try to be more specific when it comes to template matching - don't say template match"/*/*/*" when you can say template match="field".

Other than that, this is your approach, only slightly modified:

<xsl:stylesheet 
  version="1.0"
  xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  xmlns:user="urn:my-scripts"
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns:x="urn:schemas-microsoft-com:office:excel"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
> 
  <xsl:output method="xml" encoding="utf-8" indent="yes" />

  <xsl:template match="/">
    <Workbook 
      xmlns="urn:schemas-microsoft-com:office:spreadsheet"
      xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns:x="urn:schemas-microsoft-com:office:excel"
      xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
      xmlns:html="http://www.w3.org/TR/REC-html40"
    >
      <xsl:apply-templates select="Top" />
    </Workbook>
  </xsl:template>

  <xsl:template match="Top">  
    <Worksheet ss:Name="{local-name()}">
      <Table x:FullColumns="1" x:FullRows="1">
        <Row>
          <!-- header row, made from the first logo -->
          <xsl:apply-templates select="logo[1]/field/@key" />
        </Row>
        <xsl:apply-templates select="logo" />
      </Table>
    </Worksheet>
  </xsl:template>

  <!-- a <logo> will turn into a <Row> -->
  <xsl:template match="logo">
    <Row>
      <xsl:apply-templates select="field" />
    </Row>
  </xsl:template>

  <!-- convenience: <field> and @key both turn into a <Cell> -->
  <xsl:template match="field | field/@key">
    <Cell>
      <Data ss:Type="String">
        <xsl:value-of select="."/>
      </Data>
    </Cell>
  </xsl:template>

</xsl:stylesheet>

Your "repeating column names" problem roots in this expression:

<xsl:for-each select="*/*">

In your context, this selects any third level element in the document (literally all <field> nodes in all <logo>s), and makes a header row out of them. I replaced it with

<xsl:apply-templates select="logo[1]/field/@key" />

which makes a header row out of the first <logo> only.

If a certain column order is required (other than document order) or not all <field> nodes are in the same order for all <logo>s, things get more complex. Tell me if you need that.