In this article I will explain with an example, how to export DataTable to Excel file in Windows Forms (WinForms) Application 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 follows.
Export DataTable to Excel in Windows Forms
 
I have already inserted few records in the table.
Export DataTable to Excel in Windows Forms
 
Note: You can download the database table SQL by clicking the download link below.
            Download SQL file
 
 

Form Design

The Form consist of following controls:
Button – For exporting Excel file.
The Button has been assigned with a Click event handler.
Export DataTable to Excel in Windows Forms
 
 

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 file in C# and VB.Net

When the Export Button is clicked, first the connection is read from App.Config file.
Note: For more details on how to read connection string from App.Config, file please refer my article .Net 4.5: Read (Get) Connection String from App.Config file 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, an XLWorkbook 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.
Finally, using SaveAs method of XLWorkbook, which accepts the file name of Excel file as a parameter which saves the Excel to the specified location.
C#
private void OnExport(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);
 
                //Exporting to Excel.
                string folderPath = "E:\\Excel\\";
                if (!Directory.Exists(folderPath))
                {
                    Directory.CreateDirectory(folderPath);
                }
 
                using (XLWorkbook wb = new XLWorkbook())
                {
                    wb.Worksheets.Add(dt, "Customers");
                    wb.SaveAs(folderPath + "Customers.xlsx");
                }
            }
        }
    }
}
 
VB.Net
Private Sub OnExport(sender As Object, e As EventArgs) Handles btnExport.Click
    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)
 
                'Exporting to Excel.
                Dim folderPath As String = "E:\Excel\"
                If Not Directory.Exists(folderPath) Then
                    Directory.CreateDirectory(folderPath)
                End If
 
                Using wb As XLWorkbook = New XLWorkbook()
                    wb.Worksheets.Add(dt, "Customers")
                    wb.SaveAs(folderPath & "Customers.xlsx")
                End Using
            End Using
        End Using
    End Using
End Sub
 
 

Screenshots

Form

Export DataTable to Excel in Windows Forms
 

The Excel File

Export DataTable to Excel in Windows Forms
 
 

Downloads

Download Code