How to Export Excel data to XML and XML data to Excel through .NET?
In this article I am going to explain about in simple way export Excel data to XML and also explained about how to export XML data to excel with help of dataset. This concept may be used for you in some times.
1. Export Excel data to XML:
First I have to create excel with some sample data like this
Write like below code to export excel data into xml
//Export Excel data into XML
protected void btnExportXML_Click(object sender, EventArgs e)
{
try
{
string Exfilepath = Server.MapPath("sample.xls");
System.Data.OleDb.OleDbConnection MyConnection = null;
System.Data.DataSet DtSet = null;
System.Data.OleDb.OleDbDataAdapter MyCommand = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + Exfilepath + "';Extended Properties=Excel 8.0;");
//If you MS Excel 2007 then use below lin instead of above line
//MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Exfilepath & "';Extended Properties=Excel 12.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet, "[Sheet1$]");
MyConnection.Close();
//Write to XML data using below line
DtSet.WriteXml(Server.MapPath("Xmlfilename_" + DateTime.Now.ToString("ddMMyyyy_hhMMss") + ".xml"));
}
catch (Exception ex)
{
Response.Write("Error: not able to export excel data to xml");
}
}2. Export XML data to EXCEL:
First create one sample xml and its data like below
<?xml version="1.0" encoding="utf-8" ?>
<Employee>
<e1>
<eno>101</eno>
<empname>Ravindran</empname>
<sal>45000</sal>
</e1>
<e1>
<eno>102</eno>
<empname>James</empname>
<sal>45000</sal>
</e1>
<e1>
<eno>103</eno>
<empname>Mike</empname>
<sal>45000</sal>
</e1>
</Employee>
Code behind
Write code like below to export xml data to excel
//Export XML data into Excel
protected void btnExportExcel_Click(object sender, EventArgs e)
{
try
{
//get data from xml to dataset
ds.ReadXml(Server.MapPath("XMLFile.xml"));
DataTable dt = ds.Tables[0];
GridView1.DataSource = dt;
GridView1.DataBind();
string filename = DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".xls";
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + filename);
Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
catch (Exception ex)
{
}
}
Complete source :
using System.Data;
using System.Web.UI.HtmlControls;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
}
//Export Excel data into XML
protected void btnExportXML_Click(object sender, EventArgs e)
{
try
{
string Exfilepath = Server.MapPath("sample.xls");
System.Data.OleDb.OleDbConnection MyConnection = null;
System.Data.DataSet DtSet = null;
System.Data.OleDb.OleDbDataAdapter MyCommand = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + Exfilepath + "';Extended Properties=Excel 8.0;");
//If you MS Excel 2007 then use below lin instead of above line
//MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Exfilepath & "';Extended Properties=Excel 12.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet, "[Sheet1$]");
MyConnection.Close();
//Write to XML data using below line
DtSet.WriteXml(Server.MapPath("Xmlfilename_" + DateTime.Now.ToString("ddMMyyyy_hhMMss") + ".xml"));
}
catch (Exception ex)
{
Response.Write("Error: not able to export excel data to xml");
}
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
try
{
//get data from xml to dataset
ds.ReadXml(Server.MapPath("XMLFile.xml"));
DataTable dt = ds.Tables[0];
GridView1.DataSource = dt;
GridView1.DataBind();
string filename = DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".xls";
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + filename);
Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
catch (Exception ex)
{
}
}
public override void VerifyRenderingInServerForm(Control control)
{
}
}Source code:
Client Side: ASP.NET
Code Behind: C#Conclusion
I hope this code snippet is help you to know about export data to xml and excel in simple way.