How to Export Gridview data in excel format in c# asp.net with example

Hi everyone in this article i am going to show you how to Export gridview  data into excel.

first thing is that you should have  Ms offices installed i don't have installed but i will show you in browser.


lets get the code which will fill the grid .i am using visual studio 2010 and database is Sql server 2008.


private void DiplayAllREcord()
    {
       
        SqlDataAdapter sda = new SqlDataAdapter("Sp_InsertUpadetData 'SELECT','','','',''", sqcon);
        DataTable DtGetAllEmoRecord = new DataTable();
        sda.Fill(DtGetAllEmoRecord);
        grddata.DataSource = DtGetAllEmoRecord;
        grddata.DataBind();
       
       }


this is my code to fill grid view before post back .
now my button to export code behind code

protected void btnExport_Click(Object sender, EventArgs e)
{
    ExportExcelusingGridview(grddata, "Report For User" + DateTime.Now.ToShortDateString() + ".xls");
}

now i have given code to export gridview data to excel  as i dont have  MS - office installed so i m going to show in browser.

public void ExportExcelusingGridview(GridView grd, string fileName)
    {
        StringBuilder sbDocBody = new StringBuilder(); ;
        try
        {


            if (grd.Rows.Count > 0)
            {


                sbDocBody.Append("<table width='100%' border='1' " + "cellSpacing='0' cellPadding='0' " +

"style='font-size:11.0pt;'> <tr style='font-size:12pt;'>");
                // Add Data Rows
                for (int j = 0; j < grd.HeaderRow.Cells.Count; j++)
                    sbDocBody.Append("<td style='background-color:#ECE9D8;color:#000000;' width=\"120\"><B>" +

grd.HeaderRow.Cells[j].Text + "</B></td>");
                sbDocBody.Append("</tr>");
                for (int i = 0; i < grd.Rows.Count; i++)
                {
                    sbDocBody.Append("<tr>");
                    for (int j = 0; j < grd.Rows[i].Cells.Count; j++)
                    {
                        sbDocBody.Append("<td style='background-color:#FFFFE8;color:#333333;'>" + grd.Rows[i].Cells[j].Text +

"</td>");
                    }
                    sbDocBody.Append("</tr>");
                }
                sbDocBody.Append("<tr>");
                for (int j = 0; j < grd.FooterRow.Cells.Count; j++)
                {
                    sbDocBody.Append("<td style='background-color:#ECE9D8;color:#fff;' width=\"120\"

style='text-align:left'>" + grd.FooterRow.Cells[j].Text + "</td>");
                }
                sbDocBody.Append("</tr>");
                sbDocBody.Append("</table>");

            }
            else
                sbDocBody.Append("No records to export !!");

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.AppendHeader("Content-Type", "application/ms-excel");
            HttpContext.Current.Response.AppendHeader("Content-disposition", "attachment; filename=" + fileName);
            HttpContext.Current.Response.Write(sbDocBody.ToString());
            HttpContext.Current.Response.End();
        }
        catch (Exception ex)
        {
            //
        }
    }



so after click on excel it will appear like this..in browser but u can save it and if you need open with excel also.just copy code  ans paste it will work fine .just remember two things to include.
using System.Drawing;
using System.Text; //for string builder
using System.Web; // for httpcontext

Share This