In this article I will explain with an example, how to export GridView data to Excel file in ASP.Net using C# and VB.Net.
This article will illustrate how to export all Pages of GridView with Paging Enabled to Excel file along with formatting i.e. Styles and Colors in ASP.Net.
When the Export Button is clicked, the GridView will be rendered as an HTML string which will be later written to Response Stream and ultimately downloaded as Excel file in ASP.Net.
 
 

Database

Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Download and install Northwind Database
 
 

HTML Markup

The HTML Markup consists of:
GridView - For displaying data.

Columns

There are three BoundField columns for displaying the fields.
Button – For exporting the GridView to Excel file.
The Button has been assigned with a OnClick event handler.
<asp:GridView ID="gvCustomers" HeaderStyle-BackColor="#3AC0F2"
    RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000"
    runat="server" AutoGenerateColumns="false" AllowPaging="true" OnPageIndexChanging="OnPageIndexChanging">
    <Columns>
        <asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width="150px" />
        <asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="100px" />
        <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100px" />
    </Columns>
</asp:GridView>
<br />
<asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportToExcel" />
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using System.Configuration;
 
VB.Net
Imports System.IO
Imports System.Data
Imports System.Drawing
Imports System.Data.SqlClient
 
 

Populating the GridView control

Inside the Page_Load event handler, the BindGrid method is called.
Inside the BindGrid method, first the connection string is read from the Web.Config file.
Note: For more details on how to read connection string from Web.Config file, please refer my article Read or Write Connection Strings in Web.Config file using ASP.Net using C# and VB.Net.
 
Then, the records are fetched from the Customers Table of SQL Server database using SqlDataAdapter and copied to DataTable object using Fill method.
Finally, the DataTable is assigned to the DataSource property of GridView and the GridView is populated.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.BindGrid();
    }
}
 
private void BindGrid()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT ContactName, City, Country FROM Customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                }
            }
        }
    }
}
 
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Me.BindGrid()
    End If
End Sub
 
Private Sub BindGrid()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("SELECT ContactName, City, Country FROM Customers")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    sda.Fill(dt)
                    gvCustomers.DataSource = dt
                    gvCustomers.DataBind()
                End Using
            End Using
        End Using
    End Using
End Sub
 
 

Implement Paging in GridView

The OnPageIndexChanging event handles the Pagination in the GridView.
Inside the OnPageIndexChanging event handler, the GridView’s PageIndex property is updated and the GridView is again populated from Database by making call to the BindGrid method.
C#
protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvCustomers.PageIndex = e.NewPageIndex;
    this.BindGrid();
}
 
VB.Net
Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
    gvCustomers.PageIndex = e.NewPageIndex
    Me.BindGrid()
End Sub
 
 

Exporting GridView with Paging Enabled to PDF

When the Export Button is clicked, first the Content Type is set in the Response Stream in order to specify the Browser that the downloading file is an Excel file.
Then Paging is disabled for the GridView by setting the AllowPaging property to False and the GridView is again populated with records from the Database.
Finally, FOR Each loop is executed over the GridView rows and the style and formatting are applied to the Row and Alternating Row of the GridView control.
Note: The colors are applied to individual cell of each Row and not to the whole Row as if this is not done then the color will spread on all cells of the Excel sheet.
 
CSS class named textmode is applied to each cell of the GridView. This class makes sure that all the contents are rendered in Text format (mso number format).
Note: The mso number format style prevents large numbers from getting converted to exponential values.
 
C#
protected void ExportToExcel(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition","attachment; filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        using (HtmlTextWriter hw = new HtmlTextWriter(sw))
        {
            //To Export all pages
            gvCustomers.AllowPaging = false;
            this.BindGrid();
 
            gvCustomers.HeaderRow.BackColor = Color.White;
            foreach (TableCell cell in gvCustomers.HeaderRow.Cells)
            {
                cell.BackColor = gvCustomers.HeaderStyle.BackColor;
            }
            foreach (GridViewRow row in gvCustomers.Rows)
            {
                row.BackColor = Color.White;
                foreach (TableCell cell in row.Cells)
                {
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = gvCustomers.AlternatingRowStyle.BackColor;
                    }
                    else
                    {
                        cell.BackColor = gvCustomers.RowStyle.BackColor;
                    }
                    cell.CssClass = "textmode";
                }
            }
 
            gvCustomers.RenderControl(hw);
 
            //style to format numbers to string
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }
    }
}
public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}
 
VB.Net
Protected Sub ExportToExcel(sender As Object, e As EventArgs)
    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment; filename=GridViewExport.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    Using sw As New StringWriter()
        Using hw As New HtmlTextWriter(sw)
 
            'To Export all pages
            gvCustomers.AllowPaging = False
            Me.BindGrid()
 
            gvCustomers.HeaderRow.BackColor = Color.White
            For Each cell As TableCell In gvCustomers.HeaderRow.Cells
                cell.BackColor = gvCustomers.HeaderStyle.BackColor
            Next
            For Each row As GridViewRow In gvCustomers.Rows
                row.BackColor = Color.White
                For Each cell As TableCell In row.Cells
                    If row.RowIndex Mod 2 = 0 Then
                        cell.BackColor = gvCustomers.AlternatingRowStyle.BackColor
                    Else
                        cell.BackColor = gvCustomers.RowStyle.BackColor
                    End If
                    cell.CssClass = "textmode"
                Next
            Next
 
            gvCustomers.RenderControl(hw)
            'style to format numbers to string
            Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"
            Response.Write(style)
            Response.Output.Write(sw.ToString())
            Response.Flush()
            Response.[End]()
        End Using
    End Using
End Sub
 
Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    ' Verifies that the control is rendered
End Sub
 
 

Screenshot

The GridView with formatting

Export GridView ( all pages ) with Paging enabled ( AllowPaging ) to Excel in ASP.Net
 

The Exported Excel file

Export GridView ( all pages ) with Paging enabled ( AllowPaging ) to Excel in ASP.Net
 
 

Demo

View Demo
 
 

Downloads

Download Code