Export Gridview Data into Excel in ASP.Net C#

ASP.Net page

<asp:GridView ID="gridexport" runat="server" AllowPaging="True"  visible="true"
          AllowSorting="True" AutoGenerateColumns="False" EmptyDataText="No Records" 
                                                      OnPageIndexChanging="view_PageIndexChanging" >
                <Columns>
                    <asp:TemplateField HeaderText="S.No.">
                        <ItemTemplate>
     <%# Displaysnos(Convert.ToInt32(DataBinder.GetPropertyValue(Container,"RowIndex"))+1) %>
                        </ItemTemplate>
                    </asp:TemplateField>
                  
                     <asp:TemplateField HeaderText="Date">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem, "attdate", "{0:dd-MM-yyyy}")%>
                        </ItemTemplate>
                    </asp:TemplateField>
                    
                    <asp:TemplateField HeaderText="Subject Name">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem, "subname")%>
                        </ItemTemplate>
                    </asp:TemplateField>
 
                    <asp:TemplateField HeaderText="Year">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem, "year")%>
                        </ItemTemplate>
                    </asp:TemplateField>
 
                      <asp:TemplateField HeaderText="Dayorder">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem, "Dayorder")%>
                        </ItemTemplate>
                    </asp:TemplateField>
 
                    <asp:TemplateField HeaderText="Hour">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem, "subhour")%>
                        </ItemTemplate>
                    </asp:TemplateField>
          
                   <asp:TemplateField HeaderText="Lesson Unit">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem, "LessonUnit")%>
                        </ItemTemplate>
                    </asp:TemplateField>
       
                    <asp:TemplateField HeaderText="Lesson content">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem, "LessonContent")%>
                        </ItemTemplate>
                    </asp:TemplateField>
 
                   
                </Columns>
            </asp:GridView>

<asp:ImageButton ID="btnexport" runat="server" ImageUrl="~/Images/btnexport.jpg"  
Text="Export" onclick="btnexport_Click"  />



Define Connection

OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=|DataDirectory|internaldata.mdb");



Binding the GridView


    protected void Page_Load(object sender, EventArgs e)
    {
        lessonGridexport();
    }


    public void lessonGridexport()
    {
 
        dt = con.GetDT("select distinct slno,attdate,subname,year,Dayorder,Subhour,LessonUnit
,LessonContent from tbl_attendance where staffname='" + Session["staffname"] + "'
and staffcode=(select staffcode from staffmaster where staffname='" + Session["staffname"] + "')");    

        if (dt.Rows.Count > 0)
        {
            gridexport.DataSource = dt;
            gridexport.DataBind();
        }
    }

Verify Rendering Events:



public override void VerifyRenderingInServerForm(Control control)
    {
        /* Verifies that the control is rendered */
    }


Export Button Events:

protected void btnexport_Click(object sender, ImageClickEventArgs e)
    {
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", Session["staffname"] + "lessonplan.xls"));
        Response.ContentType = "application/ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        gridexport.AllowPaging = false;
        gridexport.DataBind();
 
        for (int i = 0; i < gridexport.HeaderRow.Cells.Count; i++)
        {
            gridexport.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
        }
        int j = 1;
 
        foreach (GridViewRow gvrow in gridexport.Rows)
        {
 
            if (j <= gridexport.Rows.Count)
            {
                if (j % 2 != 0)
                {
                    for (int k = 0; k < gvrow.Cells.Count; k++)
                    {
                        gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
                    }
                }
            }
            j++;
        }
        gridexport.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }

Note:

use below code to excel file name created dynamically by using specified fields.

 Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", 
Session["staffname"] + "lessonplan.xls"));

Output: