Hi akhter,

In Excel 2003 sheet has a limitation where a sheet can stored maximum of 65536 records in one sheet.

In Excel 2010 sheet has a limitation where a sheet can stored maximum of 1048576 records in one sheet.

If client has Office 2010 installed or later version one single sheet can contain 1048576 records.

So the solution would be better to breakdown your records into multiple sets and bind each set of records with each sheet.

For this refer below article.

Export GridView data to multiple Sheets (Worksheets) of Excel file in ASP.Net using C# and VB.Net

Hi akhter,

I have checked your code it is working fine.

Check below sample.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" PageSize="10"
    AllowPaging="true" OnPageIndexChanging="gvCustomers_PageIndexChanging">
    <Columns>
        <asp:TemplateField HeaderText="Customer ID">
            <ItemTemplate>
                <asp:Label ID="lblCustomerID" Text='<%# Eval("CustomerID") %>' runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Name">
            <ItemTemplate>
                <asp:Label ID="lblContactName" Text='<%# Eval("ContactName") %>' runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="City">
            <ItemTemplate>
                <asp:Label ID="lblCity" Text='<%# Eval("City") %>' runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country">
            <ItemTemplate>
                <asp:Label ID="lblCountry" Text='<%# Eval("Country") %>' runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<asp:Button ID="btnExport" Text="Export to Excel" runat="server" OnClick="OnExportExcel" />

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using ClosedXML.Excel;

 VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports ClosedXML.Excel

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindCustomers();
    }
}

protected void OnExportExcel(object sender, EventArgs e)
{
    using (XLWorkbook wb = new XLWorkbook())
    {
        for (int i = 0; i < gvCustomers.PageCount; i++)
        {
            gvCustomers.PageIndex = i;
            this.BindCustomers();
            DataTable dt = new DataTable("Page_" + (i + 1));
            foreach (TableCell cell in gvCustomers.HeaderRow.Cells)
            {
                dt.Columns.Add(cell.Text);
            }
            foreach (GridViewRow row in gvCustomers.Rows)
            {
                dt.Rows.Add();
                for (int j = 0; j < row.Cells.Count - 1; j++)
                {
                    if (row.Cells[j].Controls.Count > 0)
                    {
                        dt.Rows[dt.Rows.Count - 1][j] = (row.Cells[j].Controls[1] as Label).Text;
                    }
                    else
                    {
                        dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
                    }
                }
            }
            wb.Worksheets.Add(dt);
        }
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

protected void gvCustomers_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    this.gvCustomers.PageIndex = e.NewPageIndex;
    this.BindCustomers();
}

private void BindCustomers()
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerID,ContactName,City,Country FROM Customers", con))
        {
            cmd.CommandType = CommandType.Text;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = cmd;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                this.gvCustomers.DataSource = dt;
                this.gvCustomers.DataBind();
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindCustomers()
    End If
End Sub

Protected Sub OnExportExcel(ByVal sender As Object, ByVal e As EventArgs)
    Using wb As XLWorkbook = New XLWorkbook()
        For i As Integer = 0 To gvCustomers.PageCount - 1
            gvCustomers.PageIndex = i
            Me.BindCustomers()
            Dim dt As DataTable = New DataTable("Page_" & (i + 1))
            For Each cell As TableCell In gvCustomers.HeaderRow.Cells
                dt.Columns.Add(cell.Text)
            Next
            For Each row As GridViewRow In gvCustomers.Rows
                dt.Rows.Add()
                For j As Integer = 0 To row.Cells.Count - 1 - 1
                    If row.Cells(j).Controls.Count > 0 Then
                        dt.Rows(dt.Rows.Count - 1)(j) = (TryCast(row.Cells(j).Controls(1), Label)).Text
                    Else
                        dt.Rows(dt.Rows.Count - 1)(j) = row.Cells(j).Text
                    End If
                Next
            Next
            wb.Worksheets.Add(dt)
        Next
        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx")
        Using MyMemoryStream As MemoryStream = New MemoryStream()
            wb.SaveAs(MyMemoryStream)
            MyMemoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Using
End Sub

Protected Sub gvCustomers_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    Me.gvCustomers.PageIndex = e.NewPageIndex
    Me.BindCustomers()
End Sub

Private Sub BindCustomers()
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("SELECT CustomerID,ContactName,City,Country FROM Customers", con)
            cmd.CommandType = CommandType.Text
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                sda.SelectCommand = cmd
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                Me.gvCustomers.DataSource = dt
                Me.gvCustomers.DataBind()
            End Using
        End Using
    End Using
End Sub

Screenshot