Windows Forms: How to Export data from DataTable to Excel in C#

This post shows you How to Export data from DataTable to Excel in C# Windows Forms Application.

Dragging a DataGridView, Button from the Visual Studio toolbox into your form designer.

Next, 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.

Export datatable to excel c# windows application

c# export data from datatable to excel

Right-clicking on your project, then select the Manage Nuget Packages from your Visual Studio.

EPPlus nuget

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

c# epplus

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;
}

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#.

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");
                worksheet.Cells.LoadFromDataTable(this.appData.Customers.CopyToDataTable(), true);
                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 epplus c# example, i hope so you can find the best way to export data from datatable to excel file in c# windows forms application.

Related Posts