How to Export DataTable to Excel without Interop in C#

How to Export DataTable to Excel without Interop in C#

Creating a simple windows forms application. Next, drag a DataGridView, button from the Visual Studio toolbox into your form designer, then modify your form as shown below.

Adding a bindingsource to the DataGridView control. We will use the Northwind database to play demo. When clicking the Export button, it allows you to export DataGridView to Excel without using Interop in c#.

You need to install the ClosedXML package via NuGet Package Manager:

Install-Package ClosedXML

You can also right-click on your project, then select Manage Nuget Packages.

Next, Search 'ClosedXml', then download and install it.

It's an open source library helps you read, manipulate and write Excel 2007+ (*.xlsx, *.xlsm) files. It's intended to provide an intuitive and user-friendly interface for basic OpenXML API handling.

After adding bindsource to datagridview, you should see Form_Load event handler automatically added to your code behind as shown below.

private void frmExportDataTableToExcel_Load(object sender, EventArgs e)
{
    // TODO: This line of code loads data into the 'appData.Customers' table. You can move, or remove it, as needed.
    this.customersTableAdapter.Fill(this.appData.Customers);
}

Export DataTable to Excel C# without loop

Adding a click event handler to the Export button allows you to export datatable to an excel in c#.

//Export DataTable to Excel xlsx in C#
private void btnExportData_Click(object sender, EventArgs e)
{
    using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel Workbook|*.xlsx" })
    {
        if (sfd.ShowDialog() == DialogResult.OK)
        {
            try
            {
                // Create a new Excel workbook
                using (XLWorkbook workbook = new XLWorkbook())
                {
                    // Add a worksheet
                    workbook.Worksheets.Add(this.appData.Customers.CopyToDataTable(), "Customers");
                    // Save the workbook
                    workbook.SaveAs(sfd.FileName);
                }
                MessageBox.Show("You have successfully exported the file.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

We will use the ClosedXml library to export datatable to excel without interop c# windows forms application. This is the fastest way to export DataTable to Excel in C# without loop and interop.