Genarate an Excel Sheet from a XML file
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.
