c# XML to Excel

cited
from Internet

 

 

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.

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>

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>

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>

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.