Following is the complete code of .ashx handler to create datatable and export datatable to excel file:
<%@ WebHandler Language="C#" Class="Export" %>
using System;
using System.Web;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
public class Export : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.AddHeader("content-disposition", "attachment;
filename=FileName.xls");
context.Response.ContentType = "application/ms-excel";
HttpRequest request = context.Request;
HttpResponse
response = context.Response;
string exportContent = CreateExcel();
response.Write(exportContent);
}
private string CreateExcel()
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
//ds = GetDatasetFromDatabase();//implement this function
to get data from database
//dt =
ds.Tables[0];//get datatable from dataset
dt = GetTable();//dummy table filled with dummy data
using (StringWriter sb = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sb))
{
Table table = new Table();// create a table to hold all
the data which we need to export
TableHeaderRow thr = new TableHeaderRow();
TableRow row = new TableRow();
TableHeaderCell thc;//create 1st row as header
row
foreach (DataColumn dc in dt.Columns)//loop
to create header columns
{
thc = new TableHeaderCell();
thc.Text = dc.ColumnName;
row.Cells.Add(thc);
}
table.Rows.Add(row);//add header row to table
TableCell tc;
foreach (DataRow dr in dt.Rows)//loop to crate all
rows of excel
{
row = new TableRow();//create new row of table
foreach (DataColumn dc in dt.Columns)//loop to create columns of individual row
{
tc = new TableCell();
tc.Text =
dr[dc].ToString();
row.Cells.Add(tc);
}
table.Rows.Add(row);//add row to table
}
table.RenderControl(htw);//render table to html text writer
return sb.ToString();//return
table as string
}
}
}
DataTable GetTable()
{
// Here
we create a DataTable with four columns.
DataTable table = new DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
// Here
we add five DataRows.
table.Rows.Add(25, "Indocin", "David", DateTime.Now);
table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
return table;
}
public bool IsReusable
{
get
{
return false;
}
}
}
Use open url of handler in browser to download excel file. Following is the code to create link to download the excel:
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/Export.ashx">Export to Excel</asp:HyperLink>
<a href="Export.ashx">Export to Excel </a>
