In this article I will explain with an example, how to export DataTable to Excel file in ASP.Net using C# and VB.Net.
 
 
 

Install ClosedXml package

In order to install ClosedXml library using Nuget, please refer my article Install ClosedXml Nuget Package.
 
 

Database

I have made use of the following table Customers with the schema as follow.
Export DataTable to Excel in ASP.Net using C# and VB.Net
 
I have already inserted few records in the table.
Export DataTable to Excel in ASP.Net using C# and VB.Net
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 

HTML Markup

The HTML Markup consists of following control:
Button – For exporting Excel file.
<asp:Button ID="btnExport" runat="server" Text="Export" OnClick="ExportExcel" /> 
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using ClosedXML.Excel;
 
VB.Net
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports ClosedXML.Excel
 
 

Creating and Downloading Excel using C# and VB.Net

When the Export Button is clicked, first the connection is read from Web.Config file.
Note: For more details on how to read connection string from Web.Config file, please refer my article Read or Write Connection Strings in Web.Config file using ASP.Net using C# and VB.Net.
 
Then, the SqlDataAdapter object is initialized with the SqlCommand and using the Fill function, the DataTable is populated with the records from database.
After that, a Workbook class 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, the 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;
    string sql = "SELECT CustomerId, Name, Country FROM Customers";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlDataAdapter sda = =new SqlDataAdapter(sql, con))
        {
            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=Customers.xlsx");
                    using (MemoryStream ms = new MemoryStream())
                    {
                        wb.SaveAs(ms);
                        ms.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 sql As String = "SELECT CustomerId, Name, Country FROM Customers"
    Using con As SqlConnection = New SqlConnection(constr)
        Using sda As SqlDataAdapter = New SqlDataAdapter(sql, con)
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)
                Using wb As XLWorkbook = 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=Customers.xlsx")
                    Using ms As MemoryStream = New MemoryStream()
                        wb.SaveAs(ms)
                        ms.WriteTo(Response.OutputStream)
                        Response.Flush()
                        Response.End()
                    End Using
                End Using
            End Using
        End Using
    End Using
End Sub
 
 

Screenshot

Form

Export DataTable to Excel in ASP.Net using C# and VB.Net
 

Excel file

Export DataTable to Excel in ASP.Net using C# and VB.Net
 
 

Demo

View Demo
 
 

Downloads

SQLServer_Export_Excel_ASP.Net.zip