In few previous article, we have explained how to export data to Excel in ASP.NET MVC and exporting datatable to excel using C# now in this article, I have explained how you can export to excel GridView in C# ASP.NET Web-Forms without using any external library and with formatting.

Step 1: Create new project in Visual Studio, navigate to File->New -> Project -> Select "Web" from left-pane and Select "ASP.NET Web-Application" from "right-pane", Name you project ("ExportGridViewToExcel") and Click "Ok".

In the Next Pop-Up, Select "Empty" and check "Web-Forms" to create Web-Forms Empty template, as shown below

export-gridview-data-to-excel-asp-net-min.png

Step 2: Connect your GridView to database to show data, before we connect GridView to database, I would like to mention we will be using AdventureWorks2012 database, if you don't have it, you can download and install AdventureWorks2012 in your SQL Server.

So, create a new "Default.aspx" Web-Form in your Solution and Add GridView

 <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
           HeaderStyle-BackColor="Black" HeaderStyle-ForeColor="White"  OnPageIndexChanging="OnPageIndexChanging" PageSize="10">
            <Columns>
                <asp:BoundField  DataField="PersonType" HeaderText="Person Type" />
                <asp:BoundField  DataField="FirstName" HeaderText="First Name" />
                <asp:BoundField  DataField="LastName" HeaderText="Last Name" />
               
            </Columns>
        </asp:GridView>
    </form>

In the above code, we have enabled paging using "AllowPaging= True" and calling method "OnPageIndexChanging="OnPageIndexChanging"" , each page will show 10 rows so "PageSize =10"

In the Default.aspx.cs code, use the below code to load data in GridView with paging

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.BindGrid();
            }
        }
        private void BindGrid()
        {
            string constr = @"Data Source=VKS-PC-NEW\SQLEXPRESS;Initial Catalog='AdventureWorks2012';Integrated Security=True";
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT PersonType, FirstName, LastName FROM Person.Person"))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            GridView1.DataSource = dt;
                            GridView1.DataBind();
                        }
                    }
                }
            }
        }

        protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            this.BindGrid();
        }

Once you are done, build and run your project in browser, you should see output as below

export-gridview-to-excel-c-sharp-min.png

Step 3: Add New to button and call C# code in Codebehind to export GrdiView data into Excel, so complete form code after adding new button to export data to excel:

<form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
           HeaderStyle-BackColor="Black" HeaderStyle-ForeColor="White"  OnPageIndexChanging="OnPageIndexChanging" PageSize="10">
            <Columns>
                <asp:BoundField  DataField="PersonType" HeaderText="Person Type" />
                <asp:BoundField  DataField="FirstName" HeaderText="First Name" />
                <asp:BoundField  DataField="LastName" HeaderText="Last Name" />
               
            </Columns>
        </asp:GridView>

        <div>
    <input type="button" id="ExportToExcelBtn" value="Export GridView To Excel" 
        onserverclick="ExportToExcelClick" runat="server" />
</div>
    </form>

Now, we need to add new C# code in CodeBehind (Default.aspx.cs), which will be called on button click

   protected void ExportToExcelClick(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            //set file name and add headers
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
            Response.Charset = "";
            //set content type of file
            Response.ContentType = "application/vnd.ms-excel";
            using (StringWriter sw = new StringWriter())
            {
                HtmlTextWriter hw = new HtmlTextWriter(sw);

                //set AllowPaging to false to export all pages at once.
                GridView1.AllowPaging = false;
                this.BindGrid();

                GridView1.HeaderRow.BackColor = Color.White;
                //header style
                foreach (TableCell cell in GridView1.HeaderRow.Cells)
                {
                    cell.BackColor = GridView1.HeaderStyle.BackColor;
                }

               // apply colors to rows if there is any in GridView rows
                foreach (GridViewRow row in GridView1.Rows)
                {
                    row.BackColor = Color.White;
                    foreach (TableCell cell in row.Cells)
                    {
                        if (row.RowIndex % 2 == 0)
                        {
                            cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                        }
                        else
                        {
                            cell.BackColor = GridView1.RowStyle.BackColor;
                        }
                        cell.CssClass = "textmode";
                    }
                }

                GridView1.RenderControl(hw);

               
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }

        }

        // need to Override metho to avoid this error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
               server control at run time. */

        }

As mentioned in the comments, we have added below code

        // need to Override metho to avoid this error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
               server control at run time. */

        }

To avoid error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."

So complete Codebehind Code will be as below

using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ExportGridViewToExcel
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.BindGrid();
            }
        }
        private void BindGrid()
        {
            string constr = @"Data Source=VKS-PC-NEW\SQLEXPRESS;Initial Catalog='AdventureWorks2012';Integrated Security=True";
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT PersonType, FirstName, LastName FROM Person.Person"))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            GridView1.DataSource = dt;
                            GridView1.DataBind();
                        }
                    }
                }
            }
        }

        protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            this.BindGrid();
        }

        protected void ExportToExcelClick(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            //set file name and add headers
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
            Response.Charset = "";
            //set content type of file
            Response.ContentType = "application/vnd.ms-excel";
            using (StringWriter sw = new StringWriter())
            {
                HtmlTextWriter hw = new HtmlTextWriter(sw);

                //set AllowPaging to false to export all pages at once.
                GridView1.AllowPaging = false;
                this.BindGrid();

                GridView1.HeaderRow.BackColor = Color.White;
                //header style
                foreach (TableCell cell in GridView1.HeaderRow.Cells)
                {
                    cell.BackColor = GridView1.HeaderStyle.BackColor;
                }

               // apply colors to rows if there is any in GridView rows
                foreach (GridViewRow row in GridView1.Rows)
                {
                    row.BackColor = Color.White;
                    foreach (TableCell cell in row.Cells)
                    {
                        if (row.RowIndex % 2 == 0)
                        {
                            cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                        }
                        else
                        {
                            cell.BackColor = GridView1.RowStyle.BackColor;
                        }
                        cell.CssClass = "textmode";
                    }
                }

                GridView1.RenderControl(hw);

               
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }

        }

        // need to Override metho to avoid this error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
               server control at run time. */

        }


    }
}

Once you are done, you can build solution and run this in your browser, you will see output as below.

export-to-excel-gridview-asp-net-csharp-min.gif

Warning which you saw in the above Gif, once the file is opened in MS Excel, is because this excel sheet is created using Custom C# code, if you want to avoid this error, I will insist you to use EPPlus Or Microsoft.Office.Interop to Export data to excel.

You may also like to read:

Read Excel and Import data into GridView using ASP.NET