User Id :    Password :      New Member   Forgot Password
 
Genarate an Excel Sheet from a XML file
Description In this article I am going to show that how we can show the data of a XML file in an Excel sheet. For doing this we have to use a XSLTFile.   No. of Views     1752
  Rating     5
Author Rahul Saxena   Posted On     31 Jul 2011
Tags ASP.NET,XML,Xslt,.NET Frameworks    

Sample Code   Download Code

 

In this article I am going to show that how we can show the data of a XML file in an Excel sheet. For doing this we have to use a XSLTFile. Let see this with an example.

XMLFile.xml

 

<?xml version="1.0"?>
<EmployeeRecord>
  <Employee>
    <Emp_ID>1003</Emp_ID>
    <Emp_Name>Rahul</Emp_Name>
    <Emp_Fname>V.K Saxena</Emp_Fname>
    <Phone>0120-4444</Phone>
    <Mobile>1111</Mobile>
    <City>Noida</City>
    <State>UP</State>     
  </Employee>
  <Employee>
    <Emp_ID>1145</Emp_ID>
    <Emp_Name>Mohit</Emp_Name>
    <Emp_Fname>D.K Saxena</Emp_Fname>
    <Phone>011-12345</Phone>
    <Mobile>12345</Mobile>
    <City>Banglore</City>
    <State>Karnatka</State>
  </Employee>
  <Employee>
    <Emp_ID>56452</Emp_ID>
    <Emp_Name>aaryan</Emp_Name>
    <Emp_Fname>V.K Saxena</Emp_Fname>
    <Phone>12345</Phone>
    <Mobile>9999</Mobile>
    <City>Mumbai</City>
    <State>Mumbai</State>
  </Employee>
  <Employee>
    <Emp_ID>98999</Emp_ID>
    <Emp_Name>Reyan</Emp_Name>
    <Emp_Fname>R.K Saxena</Emp_Fname>
    <Phone>6876786</Phone>
    <Mobile>6786</Mobile>
    <City>Newyark</City>
    <State>Newyark</State>
  </Employee>
</EmployeeRecord>

 

This XML file data will show in Excel sheet.

XSLTFile.xsl

 


<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 .cs File coding is:
 

 


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Xml.Xsl;
 
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Response.ContentType = "application/vnd.ms-excel";
        Response.Charset = "";
        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("XMLFile.xml"));
        XmlDataDocument xdd = new XmlDataDocument(ds);
        XslTransform xt = new XslTransform();
        xt.Load(Server.MapPath("XSLTFile.xsl"));
        xt.Transform(xdd, null, Response.OutputStream);
        Response.End();
    }
}

 

When user run the application then the window will look like this:

 

 

Figure 1: From here user can open and save the file.

If user click on open.

 

 

 

About Author

About Author Rahul shows great interests in working with Microsoft technologies. He specializes in the implementation of DataBase & Graphics. His area of expertise includes: C#, ASP.NET,ADO.NET,Windows Forms & Web Services. He hails from background , Master's in Computer Application. With programming he loves photography, traveling and reading books. Rahul Saxena
No Photo
 
Country India
Company N/A
Home Page N/A

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

 
 
Posted By Gaurav on 03 Aug 2011 at 04:14 AM
 
Hi Rahul... This article is very helpful.. Thanks ..Gaurav
 
 
     
Write your comment here.  
Comment
Verification Code   
  
    
 
Section sponsored by