Exporting DataTable To Excel using ASP.NET

In continuation to my previous post which addressed Export to Excel issue in ASP.NET (click here), here is another post which is a solution for Exporting a DataTable to Excel using ASP.NET. Many times we come across scenario where we don't need to show the grid and just export data which is available in DataTable to Excel.

You can use following method by passing the DataTable as parameter, which in turn export the data to Excel.

I have implemented with AJAX also without any issue.
Note: While using AJAX on a button click, do not forget to Add a PostBackTrigger to your UpdatePanel that points at the button, otherwise it will give "Sys.WebForms.PageRequestManagerParserErrorException" Exception because of violation of using Response.Write bypasses the normal rendering of ASP.NET control.

private void ExportGridToExcel(DataTable dt)
{
        StringWriter strwriter = new StringWriter();
        HtmlTextWriter htmlwriter = new HtmlTextWriter(strwriter);
        BoundField BF_Column1 = new BoundField();
        BoundField BF_Column2 = new BoundField();
        BoundField BF_Column3 = new BoundField();


        BF_Column1 .HeaderText = "Column1";
        BF_Column2 .HeaderText = "Column2";
        BF_Column3 .HeaderText = "Column3";


        BF_Column1 .DataField = "Column1";
        BF_Column2 .DataField = "Column2";
        BF_Column3 .DataField = "Column3";


        GridView g = new GridView();
        g.AutoGenerateColumns = false;
        g.RowDataBound += new GridViewRowEventHandler(g_RowDataBound);


        g.Columns.Insert(0, BF_Column1 );
        g.Columns.Insert(1, BF_Column2 );
        g.Columns.Insert(2, BF_Column3 );
        g.DataSource = dtData;
        g.DataBind();
        g.RenderControl(htmlwriter);


        HtmlForm form = new HtmlForm();
        string attachment = "attachment; filename=SearchResult.xls";
        Page.Response.ClearContent();
        Page.Response.AddHeader("content-disposition", attachment);
        Page.Response.ContentType = "application/ms-excel";
        Response.Write(strwriter.ToString());
        Response.End();
    }

You need to add RowDataBound Event Handler, if you need some extra formatting to excel.

 protected void g_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[0].Attributes.Add("class", "dateformat");
            e.Row.Cells[1].Attributes.Add("class", "dateformat");
        }
    }