In this article I will explain with an example, how to export GridView data to multiple Sheets (Worksheets) of same Excel file in ASP.Net using C# and VB.Net.
Records of each page of GridView with paging enabled will be exported to a different Excel sheets (worksheets) using ClosedXML and OpenXml Libraries.
Download DocumentFormat.OpenXml and ClosedXML Libraries
You can download the libraries using the following download locations.
Note: You will need to install the OpenXml SDK 2.0 in your Windows Operating System.
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. The GridView has been specified with the AllowPaging property and the OnPageIndexChanging event in order to enable paging.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
OnPageIndexChanging="OnPageIndexChanging" PageSize = "10">
<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="ExportExcel" />
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Imports System.Data.SqlClient
Imports System.Configuration
Binding the GridView
Inside the Page Load event of the page the GridView is populated with records from the Customers Table of the Northwind database.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlCommand cmd = new SqlCommand("SELECT TOP 30 * 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 Me.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 TOP 30 * 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
Implement Paging in GridView
Inside the OnPageIndexChanging event of the GridView, the new PageIndex is set and the GridView is again populated with records.
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
Exporting GridView data to multiple Excel Sheets (Worksheets)
When the Export button is clicked, the following event handler is executed. First a loop is executed over the GridView pages and inside the loop the GridView is populated with records for each page.
Then a new DataTable is created with some name.
Note: The name of the DataTable is important as with the same name the Excel Sheet will be created.
Then columns are added to the DataTable by fetching the names of each column text in the GridView Header Row. Once the columns are added then a loop is executed over the GridView rows and the contents of each GridView Row are added to the DataTable Row.
Once the DataTable is populated, it is added to the ClosedXML WorkBook object as Worksheet.
Finally the WorkBook object is saved to the MemoryStream and the MemoryStream is written to the Response OutputStream which finally sends the Excel File for download.
C#
protected void ExportExcel(object sender, EventArgs e)
{
using (XLWorkbook wb = new XLWorkbook())
{
//Loop through the GridView pages.
for (int i = 0; i < GridView1.PageCount; i++)
{
//Set the Current Page.
GridView1.PageIndex = i;
this.BindGrid();
//Create a DataTable with schema same as GridView columns.
DataTable dt = new DataTable("Page_" + (i + 1));
foreach (TableCell cell in GridView1.HeaderRow.Cells)
{
dt.Columns.Add(cell.Text);
}
//Loop and add rows from GridView to DataTable.
foreach (GridViewRow row in GridView1.Rows)
{
dt.Rows.Add();
for (int j = 0; j < row.Cells.Count; j++)
{
dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
}
}
//Add DataTable as Worksheet.
wb.Worksheets.Add(dt);
}
//Export the Excel file.
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();
}
}
}
VB.Net
Protected Sub ExportExcel(sender As Object, e As EventArgs)
Using wb As New XLWorkbook()
'Loop through the GridView pages.
For i As Integer = 0 To GridView1.PageCount - 1
'Set the Current Page.
GridView1.PageIndex = i
Me.BindGrid()
'Create a DataTable with schema same as GridView columns.
Dim dt As New DataTable("Page_" & (i + 1))
For Each cell As TableCell In GridView1.HeaderRow.Cells
dt.Columns.Add(cell.Text)
Next
'Loop and add rows from GridView to DataTable.
For Each row As GridViewRow In GridView1.Rows
dt.Rows.Add()
For j As Integer = 0 To row.Cells.Count - 1
dt.Rows(dt.Rows.Count - 1)(j) = row.Cells(j).Text
Next
Next
'Add DataTable as Worksheet.
wb.Worksheets.Add(dt)
Next
'Export the Excel file.
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 New MemoryStream()
wb.SaveAs(MyMemoryStream)
MyMemoryStream.WriteTo(Response.OutputStream)
Response.Flush()
Response.[End]()
End Using
End Using
End Sub
Screenshots
GridView with Paging enabled
![Export GridView data to multiple Sheets (Worksheets) of Excel file in ASP.Net using C# and VB.Net]()
GridView pages exported to different sheets
![Export GridView data to multiple Sheets (Worksheets) of Excel file in ASP.Net using C# and VB.Net]()
Demo
Downloads
