Export data from SQL Server to Excel in ASP.Net using C# and VB.Net

Here Asp.Net Satyanarayana has explained 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.

 

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.

 

 

You can download the libraries using the following download locations.

Download OpenXml SDK 2.0

Download ClosedXml Library

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