How to Export data from DataTable to Excel in C#

By FoxLearn 7/16/2024 9:30:12 AM   15.28K
You can export data from a DataTable to an Excel file in a C# Windows Forms application using libraries like EPPlus or Microsoft Office Interop.

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.

c# export data from datatable to excel

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.

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

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.