In this short article I will explain how to export GridView with paging enabled to Excel file in ASP.Net.
 
Database
For this article I am making use of the Microsoft’s Northwind Database. Download and install instructions are provided in the link below
 
HTML Markup
The HTML Markup consists of an ASP.Net GridView and a Button. For the GridView I have enabled paging using the AllowPaging property and I have also specified on the OnPageIndexChanging event.
<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
    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
Imports System.Configuration
 
 
Binding the GridView
Below is the code to bind the GridView with records from the Customers table of the Northwind database
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.BindGrid();
    }
}
 
private void BindGrid()
{
    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.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 strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(strConnString)
        Using cmd As New SqlCommand("SELECT * FROM Customers")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    sda.Fill(dt)
                    GridView1.DataSource = dt
                    GridView1.DataBind()
                End Using
            End Using
        End Using
    End Using
End Sub
 
 
Export GridView ( all pages ) with Paging enabled ( AllowPaging ) to Excel in ASP.Net
 
Implement Paging in GridView
The OnPageIndexChanging event handles the Pagination in the GridView
C#
protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}
 
VB.Net
Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
    GridView1.PageIndex = e.NewPageIndex
    Me.BindGrid()
End Sub
 
 
Export GridView with Paging Enabled to Excel
Below is the code to Export GridView to Excel file with Paging enabled. Firstly the GridView is again populated with data from database after setting AllowPaging to false.
Then a loop is executed on all rows of the GridView and the colors of the Row and the Alternating Row are applied to their individual cells. If this is not done then the color will spread on all cells of the Excel sheet for each row
Class textmode is applied to all cells so that they are rendered as text as per mso number format, doing this 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())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
 
        //To Export all pages
        GridView1.AllowPaging = false;
        this.BindGrid();
 
        GridView1.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in GridView1.HeaderRow.Cells)
        {
            cell.BackColor = GridView1.HeaderStyle.BackColor;
        }
        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);
           
        //style to format numbers to string
        string style = @"<style> .textmode { } </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()
        Dim hw As New HtmlTextWriter(sw)
 
        'To Export all pages
        GridView1.AllowPaging = False
        Me.BindGrid()
 
        GridView1.HeaderRow.BackColor = Color.White
        For Each cell As TableCell In GridView1.HeaderRow.Cells
            cell.BackColor = GridView1.HeaderStyle.BackColor
        Next
        For Each row As GridViewRow In GridView1.Rows
            row.BackColor = Color.White
            For Each cell As TableCell In row.Cells
                If row.RowIndex Mod 2 = 0 Then
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor
                Else
                    cell.BackColor = GridView1.RowStyle.BackColor
                End If
                cell.CssClass = "textmode"
            Next
        Next
 
        GridView1.RenderControl(hw)
        'style to format numbers to string
        Dim style As String = "<style> .textmode { } </style>"
        Response.Write(style)
        Response.Output.Write(sw.ToString())
        Response.Flush()
        Response.[End]()
    End Using
End Sub
 
Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    ' Verifies that the control is rendered
End Sub
 
Export GridView ( all pages ) with Paging enabled ( AllowPaging ) to Excel in ASP.Net
 
 
Demo
 
Downloads