How to generate Excel file from XML using XSLT

Hi Guys,

Today, I got a new task to generate customised MS Excel file using XSLT where the source data is available in XML file. I have an experience in creating the HTML and PDF files using XSLT. But never heard of creating the Excel. After googling, I found out that SpreadsheetML is a Markup Laungage used to write the XSLT transformation for creating the Excel files. You can use WordProcessingML to create Word document out of XML.

SpreadsheetML is defined by Microsoft people. You just need to mention in XSLT that you are using MS office spreadsheet functions by defining the different namespaces in the XSL declaration (in the first line).
Then define the Workbook properties and styles which can be used for the work sheets.

When you executed the XSLT, the output is generated. Save the output with the extension as .xls and open it in Excel. You could see the file.

Raw XML:

<companies name="My Companies">
    <company>
        <companyName>Cool Beans</companyName>
        <serviceID>1</serviceID>
        <startDate>01-01-2014 00:00:00</startDate>
        <endDate>01-02-2014 00:00:00</endDate>
        <hours>2</hours>
    </company>
    <company>
        <companyName>Hot Beans</companyName>
        <serviceID>2</serviceID>
        <startDate>01-01-2014 00:00:00</startDate>
        <endDate>01-02-2014 00:00:00</endDate>
        <hours>2</hours>
    </company>
    <company>
        <companyName>Evil Beans</companyName>
        <serviceID>3</serviceID>
        <startDate>01-03-2014 00:00:00</startDate>
        <endDate>01-04-2014 00:00:00</endDate>
        <hours>2</hours>
    </company>
</companies>

XSLT File:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 <xsl:output method="xml" indent="yes"/>
 <xsl:template match="/">
  <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
  <Workbook>
   <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>10005</WindowHeight>
    <WindowWidth>10005</WindowWidth>
    <WindowTopX>120</WindowTopX>
    <WindowTopY>135</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
   </ExcelWorkbook>
   <Styles>
    <Style ss:ID="head_cell_style">
     <Font ss:Bold="1" x:Family="Swiss"/>
     <Borders>
      <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
     </Borders>
     <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
     <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
    </Style>
    <Style ss:ID="solid_row_style">
     <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
     <Borders>
      <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
     </Borders>
    </Style>
    <Style ss:ID="solid_row_top_style">
     <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
     <Borders>
      <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
     </Borders>
    </Style>
    <Style ss:ID="value_row_style">
     <Borders>
      <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/>
     </Borders>
    </Style>
   </Styles>
   <xsl:apply-templates select="companies"/>
  </Workbook>
 </xsl:template>
 <xsl:template match="companies">
  <Worksheet ss:Name="{@name}">
   <Table ss:DefaultRowHeight="13.2" ss:ExpandedColumnCount="5" x:FullColumns="1" x:FullRows="1">
    <Column ss:Index="1" ss:Width="100"/>
    <Column ss:Index="2" ss:Width="100"/>
    <Column ss:Index="3" ss:Width="100"/>
    <Column ss:Index="4" ss:Width="100"/>
    <Column ss:Index="5" ss:Width="100"/>
    <Row>
     <!-- Header Row -->
     <xsl:apply-templates select="company[1]/*" mode="headers"/>
    </Row>
    <xsl:apply-templates select="company"/>
    <Row>
     <!-- Last Row -->
     <Cell ss:Index="4"  ss:StyleID="value_row_style">
      <Data ss:Type="String">Total:</Data>
     </Cell>
     <Cell ss:Formula="=SUM(R[-{count(company)}]C:R[-1]C)"  ss:StyleID="value_row_style">
      <Data ss:Type="Number"/>
     </Cell>
    </Row>
   </Table>
  </Worksheet>
 </xsl:template>
 <xsl:template match="company[1]/*" mode="headers">
  <Cell ss:StyleID="head_cell_style">
   <Data ss:Type="String">
    <xsl:value-of select="name()"/>
   </Data>
  </Cell>
 </xsl:template>
 <xsl:template match="company">
  <Row>
   <xsl:apply-templates select="*"/>
  </Row>
 </xsl:template>
 <xsl:template match="companyName|serviceID|startDate|endDate">
  <Cell  ss:StyleID="value_row_style">
   <Data ss:Type="String">
    <xsl:value-of select="."/>
   </Data>
  </Cell>
 </xsl:template>
 <xsl:template match="hours">
  <Cell>
   <Data ss:Type="Number">
    <xsl:value-of select="."/>
   </Data>
  </Cell>
 </xsl:template>
</xsl:stylesheet>

Generated Ouput.xls:

<?xml version="1.0" encoding="UTF-8"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>10005</WindowHeight>
  <WindowWidth>10005</WindowWidth>
  <WindowTopX>120</WindowTopX>
  <WindowTopY>135</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="head_cell_style">
   <Font ss:Bold="1" x:Family="Swiss"/>
   <Borders>
    <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
   </Borders>
   <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
  </Style>
  <Style ss:ID="solid_row_style">
   <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
   <Borders>
    <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
   </Borders>
  </Style>
  <Style ss:ID="solid_row_top_style">
   <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
   <Borders>
    <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
   </Borders>
  </Style>
  <Style ss:ID="value_row_style">
   <Borders>
    <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/>
   </Borders>
  </Style>
 </Styles>
 <Worksheet ss:Name="My Companies">
  <Table ss:DefaultRowHeight="13.2" ss:ExpandedColumnCount="5" x:FullColumns="1" x:FullRows="1">
   <Column ss:Index="1" ss:Width="100"/>
   <Column ss:Index="2" ss:Width="100"/>
   <Column ss:Index="3" ss:Width="100"/>
   <Column ss:Index="4" ss:Width="100"/>
   <Column ss:Index="5" ss:Width="100"/>
   <Row>
    <Cell ss:StyleID="head_cell_style">
     <Data ss:Type="String">companyName</Data>
    </Cell>
    <Cell ss:StyleID="head_cell_style">
     <Data ss:Type="String">serviceID</Data>
    </Cell>
    <Cell ss:StyleID="head_cell_style">
     <Data ss:Type="String">startDate</Data>
    </Cell>
    <Cell ss:StyleID="head_cell_style">
     <Data ss:Type="String">endDate</Data>
    </Cell>
    <Cell ss:StyleID="head_cell_style">
     <Data ss:Type="String">hours</Data>
    </Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">Cool Beans</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">1</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-01-2014 00:00:00</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-02-2014 00:00:00</Data>
    </Cell>
    <Cell>
     <Data ss:Type="Number">2</Data>
    </Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">Hot Beans</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">2</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-01-2014 00:00:00</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-02-2014 00:00:00</Data>
    </Cell>
    <Cell>
     <Data ss:Type="Number">2</Data>
    </Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">Evil Beans</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">3</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-03-2014 00:00:00</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-04-2014 00:00:00</Data>
    </Cell>
    <Cell>
     <Data ss:Type="Number">2</Data>
    </Cell>
   </Row>
   <Row>
    <Cell ss:Index="4" ss:StyleID="value_row_style">
     <Data ss:Type="String">Total:</Data>
    </Cell>
    <Cell ss:Formula="=SUM(R[-3]C:R[-1]C)" ss:StyleID="value_row_style">
     <Data ss:Type="Number"/>
    </Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

Output in Excel: