AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=471&pId=-1
Convert XML To an Excel Spreadsheet Using XSL
page
by Andrew Mooney
Feedback
Average Rating: 
Views (Total / Last 10 Days): 174208/ 287

Introduction

[Download Code]
Use ASP.NET and a generic XSL file to convert any XML data into an Excel spreadsheet. This generic XSL can be used to present XML reports to users in Excel via their web browser. The XML data actually gets converted to an Excel XML spreadsheet, and for that reason Excel 2002/2003 is required on the client. Once the data has been converted it can be saved, on the client, as an Excel Spreadsheet or opened in the client's web browser.

 

The XML File

[Download Code]
This is an example of the XML file that is included in the download. The XML file is required to be in this format to work with the XSL.

<?xml version="1.0"?>
<NewDataSet>
  <authors>
    <au_id>172-32-1176</au_id>
    <au_lname>White</au_lname>
    <au_fname>Johnson</au_fname>
    <phone>408 496-7223</phone>
    <address>10932 Bigge Rd.</address>
    <city>Menlo Park</city>
    <state>CA</state>
    <zip>94025</zip>
    <contract>true</contract>
  </authors>
  <authors>
    <au_id>213-46-8915</au_id>
    <au_lname>Green</au_lname>
    <au_fname>Marjorie</au_fname>
    <phone>415 986-7020</phone>
    <address>309 63rd St. #411</address>
    <city>Oakland</city>
    <state>CA</state>
    <zip>94618</zip>
    <contract>true</contract>
  </authors>
  <authors>
    <au_id>238-95-7766</au_id>
    <au_lname>Carson</au_lname>
    <au_fname>Cheryl</au_fname>
    <phone>415 548-7723</phone>
    <address>589 Darwin Ln.</address>
    <city>Berkeley</city>
    <state>CA</state>
    <zip>94705</zip>
    <contract>true</contract>
  </authors>
</NewDataSet>

XSL Does All The Work

[Download Code]
The XSL file gets the element local names and uses them for the header row in the Excel spreadsheet. This way you don't have to have the column names in the XSL. And you can resuse the XSL with any XML file that is in the proper format. This generic XSL will work if you have one column or many columns. In addition, the XSL gets the second level element local name and uses it for the worksheet name.

<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="*[position() = 1]/*">
          <Cell><Data ss:Type="String">
          <xsl:value-of select="local-name()"/>
          </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:template>


</xsl:stylesheet>

Use ASP.NET To Transform The XML

[Download Code]
The first thing the ASP.NET page does is set the content type to MS Excel. It then opens the XML file as an XML data document, transforms it with XSL, and sends it to the client's browser.

<%@ Page Language="c#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Xml.Xsl" %>
<script language="C#" runat="server">
    public void Page_Load(Object sender, EventArgs E) {
        Response.ContentType = "application/vnd.ms-excel";
        Response.Charset = "";
        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("Authors.xml"));
        XmlDataDocument xdd = new XmlDataDocument(ds);
        XslTransform  xt = new XslTransform();
        xt.Load(Server.MapPath("Excel.xsl"));
        xt.Transform(xdd, null, Response.OutputStream);
        Response.End();              
      }
</script>

Conclusion and Sample

[Download Code]
Hopefully, I have demonstrated how easy it is to convert XML to an Excel spreadsheet using XSL. But, there is more you can do with this same XSL file. For example, you can convert data from various database types to Excel. Just use a one-table DataSet in place of the XML file. One thing's for sure--if your users want their reports in Excel, you're going to save a lot of time.

[Run Sample] Requires Excel 2002/2003 to run.



©Copyright 1998-2018 ASPAlliance.com  |  Page Processed at 2018-10-21 6:20:55 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search