In this article I will explain how to create an Excel file using the data (records) from SQL Server Table and then download it in ASP.Net using C# and VB.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.
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" />
You will need to import the following namespaces.
using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Configuration;
using System.Data.SqlClient;
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.
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())
                    using (XLWorkbook wb = new XLWorkbook())
                        wb.Worksheets.Add(dt, "Customers");
                        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())
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()
                    Using wb As New XLWorkbook()
                        wb.Worksheets.Add(dt, "Customers")
                        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()
                        End Using
                    End Using
                End Using
            End Using
        End Using
    End Using
End Sub
The downloaded Excel file containing the SQL Server data is shown below.
Export data from SQL Server to Excel in ASP.Net using C# and VB.Net