Generate multiple Excel file from ASP.Net GridView using While Loop in C# and VB.Net

Hey seriverma,

Please refer below sample.

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<asp:Button Text="Export" runat="server" OnClick="Export" />

Namespaces

C#

using System.IO;
using System.Data;

VB.Net

Imports System.IO
Imports System.Data

Code

C#

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

private DataTable BindGrid()
{
    DataTable dt1 = new DataTable();
    dt1.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                        new DataColumn("Name", typeof(string)),
                        new DataColumn("Country",typeof(string)) });
    dt1.Rows.Add(1, "John Hammond", "United States");
    dt1.Rows.Add(2, "Mudassar Khan", "India");
    dt1.Rows.Add(3, "Suzanne Mathews", "France");
    dt1.Rows.Add(4, "Robert Schidner", "Russia");
    GridView1.DataSource = dt1;
    GridView1.DataBind();
    ViewState["dt1"] = dt1;
    return dt1;
}

protected void Export(object sender, EventArgs e)
{
    DataTable dt1 = ViewState["dt1"] as DataTable;
    int j = 0;
    while (j < dt1.Rows.Count)
    {
        ExportToExcel();
        j = j + 1;
    }
}

int count = 0;
public void ExportToExcel()
{
    string a = "Book1";
    string path = Server.MapPath("~/File");
    if (!Directory.Exists(path))
    {
        Directory.CreateDirectory(path);
    }
    if (File.Exists(path + "/File" + a + "_" + Convert.ToString(count) + ".xls"))
    {
        File.Delete(path + "/File" + a + "_" + Convert.ToString(count) + ".xls");
    }
    StreamWriter writer = File.CreateText(path + "/File" + a + "_" + Convert.ToString(count) + ".xls");
    using (StringWriter sw = new StringWriter())
    {
        using (HtmlTextWriter hw = new HtmlTextWriter(sw))
        {
            this.BindGrid();
            GridView1.RenderControl(hw);
            Response.Output.Write(sw.ToString());
            writer.Write(sw.ToString());
            writer.Flush();
            writer.Close();
            writer.Dispose();
            Response.Flush();
        }
    }
    count++;
}
public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

VB.Net

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

Private Function BindGrid() As DataTable
    Dim dt1 As DataTable = New DataTable()
    dt1.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
    dt1.Rows.Add(1, "John Hammond", "United States")
    dt1.Rows.Add(2, "Mudassar Khan", "India")
    dt1.Rows.Add(3, "Suzanne Mathews", "France")
    dt1.Rows.Add(4, "Robert Schidner", "Russia")
    GridView1.DataSource = dt1
    GridView1.DataBind()
    ViewState("dt1") = dt1
    Return dt1
End Function

Protected Sub Export(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt1 As DataTable = TryCast(ViewState("dt1"), DataTable)
    Dim j As Integer = 0

    While j < dt1.Rows.Count
        ExportToExcel()
        j = j + 1
    End While
End Sub

Private count As Integer = 0
Public Sub ExportToExcel()
    Dim a As String = "Book1"
    Dim path As String = Server.MapPath("~/File")
    If Not Directory.Exists(path) Then
        Directory.CreateDirectory(path)
    End If

    If File.Exists(path & "/File" & a & "_" & Convert.ToString(count) & ".xls") Then
        File.Delete(path & "/File" & a & "_" & Convert.ToString(count) & ".xls")
    End If

    Dim writer As StreamWriter = File.CreateText(path & "/File" & a & "_" & Convert.ToString(count) & ".xls")
    Using sw As StringWriter = New StringWriter()
        Using hw As HtmlTextWriter = New HtmlTextWriter(sw)
            Me.BindGrid()
            GridView1.RenderControl(hw)
            Response.Output.Write(sw.ToString())
            writer.Write(sw.ToString())
            writer.Flush()
            writer.Close()
            writer.Dispose()
            Response.Flush()
        End Using
    End Using

    count += 1
End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
End Sub