asp.net-Export Datatable to Excel using handler(.ashx)

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>