In this article I will explain with an example, how to export multiple DataTables of a DataSet to multiple Excel Sheets (Worksheets) in ASP.Net using C# and VB.Net.
Records of each DataTable in the DataSet will be exported to a different Sheet (Worksheet) of an Excel file using ClosedXml library in ASP.Net.
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 Button which will be used to export the contents of DataSet to multiple Excel Sheets (Worksheets).
<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
Exporting DataTables of DataSet to multiple Excel Sheets (Worksheets)
When the Export button is clicked, the following event handler is executed. First the DataSet is populated from records of Customers and Employees tables of the Northwind Database.
Once the DataSet is populated with two DataTables, the name of
each DataTable is set.
Note: The name of the DataTable is important as with the same name the Excel Sheet will be created.
Then a loop is executed and each DataTable of the DataSet 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)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT TOP 10 ContactName, City, Country FROM Customers;";
query += "SELECT TOP 10 (FirstName + ' ' + LastName) EmployeeName, City, Country FROM Employees";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
//Set Name of DataTables.
ds.Tables[0].TableName = "Customers";
ds.Tables[1].TableName = "Employees";
using (XLWorkbook wb = new XLWorkbook())
{
foreach (DataTable dt in ds.Tables)
{
//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=DataSet.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)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "SELECT TOP 10 ContactName, City, Country FROM Customers;"
query &= "SELECT TOP 10 (FirstName + ' ' + LastName) EmployeeName, City, Country FROM Employees"
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand(query)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As New DataSet()
sda.Fill(ds)
'Set Name of DataTables.
ds.Tables(0).TableName = "Customers"
ds.Tables(1).TableName = "Employees"
Using wb As New XLWorkbook()
For Each dt As DataTable In ds.Tables
'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=DataSet.xlsx")
Using MyMemoryStream As New MemoryStream()
wb.SaveAs(MyMemoryStream)
MyMemoryStream.WriteTo(Response.OutputStream)
Response.Flush()
Response.End()
End Using
End Using
End Using
End Using
End Using
End Using
End Sub
Screenshots
DataTables in DataSet
![Export GridView data to multiple Sheets (Worksheets) of Excel file in ASP.Net using C# and VB.Net]()
DataTables of DataSet exported to different sheets
![Export GridView data to multiple Sheets (Worksheets) of Excel file in ASP.Net using C# and VB.Net]()
Demo
Downloads
