How to Export data from DataTable to Excel in C#

Here's a basic example using EPPlus, which is a popular library for creating Excel spreadsheets:

Open your Visual Studio, then create a new Windows Forms project.

Next, Drag and drop the DataGridView, Button from the Visual Studio toolbox into your form designer, then create a simple UI allows you to retrieve customer data from the Northwind database, then export data from datatable to excel file in c# as shown below.

How to export datatable to excel c#

First, make sure to install the EPPlus NuGet package in your project. You can right-click on your project, then select the Manage Nuget Packages from your Visual Studio.

EPPlus nuget

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

The EPPlus is an open source 3rd party helps you handle excel file in c#. In addition, EPPlus supports multiple worksheet properties such as cell range, cell styling, charts, pictures, shapes, comments, tables, protection, encryption, pivot tables, data validation, conditional formatting, formula calculation...etc.

The EPPlus is a .NET library that help you read and write Excel 2007/2010/2013 files in the Open Office Xml (xlsx) format. Using the EPPlus you can easily export large data from datatable to excel in c#.

After you add the bindingsource to the DataGridView control, you need to add the LicenseContext as shown below.

//epplus license
private void frmExportData_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);
    ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
}

How to export datatable to excel xlsx in c#

Adding a click event handler to the Export button allows you to export data from DataTable to excel file in c#.

// epplus c# example
private void btnExportData_Click(object sender, EventArgs e)
{
    using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel Workbook|*.xlsx" })
    {
        if (sfd.ShowDialog() == DialogResult.OK)
        {
            var fileInfo = new FileInfo(sfd.FileName);
            using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
            {
                ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Customers");
                // Load the data from the DataTable to the Excel worksheet
                worksheet.Cells.LoadFromDataTable(this.appData.Customers.CopyToDataTable(), true);
                // Save the Excel file
                excelPackage.Save();
            }
        }
    }
}

Using the SaveFileDialog class helps you open a save file dialog combination filter excel workbook allows you to enter a file name.

Using the ExcelPackage class helps you create an excel file, then use ExcelWorksheet class to create a worksheet.

Finally, Use the LoadFromDataTable method to fill data to excel sheet.

This is the fastest way to export datatable to excel in c#. If you want to export an excel file with password protect you can modify your code as shown below.

excelPackage.Save('foxlearn');//export excel file with password

Through this c# example, i hope so you can find the best way to export data from datatable to excel file in c# windows forms application.