In this article I will explain how to export data (records) from SQL Server Table to Excel file in ASP.Net using C# and VB.Net. This article will work for all SQL Server versions i.e. 2005, 2008, 2008R2, 2012, etc.
The data will be first fetched into a DataTable and then the DataTable will be exported into an Excel Sheet using OpenXml and ClosedXml Excel 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
I have made use of the following table Customers with the schema as follows.
![Export data from SQL Server to Excel in ASP.Net using C# and VB.Net]()
I have already inserted few records in the table.
![Export data from SQL Server to Excel in ASP.Net using C# and VB.Net]()
Note: The SQL for creating the database is provided in the attached sample code.
HTML Markup
The HTML markup consists of a Button which when clicked will trigger the process of exporting the data from SQL Server table to Excel file.
<asp:Button Text="Export" OnClick="ExportExcel" runat="server" />
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Imports System.Configuration
Imports System.Data.SqlClient
Exporting data from SQL Server to Excel in ASP.Net using C# and VB.Net
When the Export button is clicked, the following event handler is
executed. A DataTable is populated with records from the Customers table.
Then a Workbook object is created to which the DataTable is added as Worksheet using the Add method which accepts DataTable and the name of the Sheet as parameters.
Once the DataTable is added as a Worksheet to the Workbook, the WorkBook is saved to a MemoryStream.
Finally MemoryStream is written to the Response which initiates the File download.
C#
protected void ExportExcel(object sender, EventArgs e)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
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);
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Customers");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=SqlExport.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
Using con As New SqlConnection(constr)
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)
Using wb As New XLWorkbook()
wb.Worksheets.Add(dt, "Customers")
Response.Clear()
Response.Buffer = True
Response.Charset = ""
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment;filename=SqlExport.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
Screenshot
![Export data from SQL Server to Excel in ASP.Net using C# and VB.Net]()
Demo
Downloads
