Export DataTable data to Excel in ASP.NET
We can export datatable data to Excel in Asp.Net easily by using StringWriter and HtmlTextWriter classes.
We have to use the HttpResponse class to export data to excel and mention content type of HttpResponse class to the application/vnd.ms-excel.
In this example we export the XML file data to the Excel file by using data set , gridview as shown below.
using System;
using System.Data;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace DownloadToExcelAspNet
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("Employees.xml"));
gv1.DataSource = ds.Tables[0];
gv1.DataBind();
}
}
protected void btnToExcel_Click(object sender, EventArgs e)
{
try
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("Employees.xml"));
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.Charset = "";
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment;filename=Employees.xlsx;");
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
using (sw)
{
using (hw)
{
GridView gv2 = new GridView();
gv2.DataSource = ds.Tables[0];
gv2.DataBind();
gv2.RenderControl(hw);
response.Write(sw.ToString());
response.End();
}
}
hw.Dispose();
sw.Dispose();
hw = null;
sw = null;
}
catch (Exception ex)
{
//Response.Write(ex.Message);
}
finally
{
}
}
}
}
As shown above we can export or download data table data to the Excel file in Asp.Net.
