In this article I will explain with an example, how to export SQL data to Excel file with Column Headers (Column Names) in C# and VB.Net.
The records from the SQL Server database Table are fetched into a DataTable and then the DataTable will be exported to Excel file using the ClosedXml library in 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.
Database
I have made use of the following table Customers with the schema as follows.
![Export SQL data to Excel with Column Headers (Column Names) in C# and VB.Net]()
I have already inserted few records in the table.
![Export SQL data to Excel with Column Headers (Column Names) in 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
Export SQL data to Excel with Column Headers (Column Names) in 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 SQL data to Excel with Column Headers (Column Names) in C# and VB.Net]()
Demo
Downloads
