How to Export DataTable to Excel without Interop in C#

ClosedXML is a powerful library that allows you to work with Excel files without having Excel installed on the machine.

How to Export DataSet to Excel 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);
            }
        }
    }
}

If you have many tables in a dataset, you can use a loop to iterate through all the tables and then export each one to Excel, with each table being a separate sheet.

// export dataset to excel in c# using ClosedXML
public void ExportDataSetToExcel(DataSet dataSet, string filePath)
{
    using (var workbook = new XLWorkbook())
    {
        // Loop through all tables in the dataset
        foreach (var table in dataSet.Tables)
        {
            // Create a worksheet for each table in the dataset
            var worksheet = workbook.AddWorksheet(table.TableName);

            // Load the DataTable into the worksheet, starting from cell A1
            worksheet.Cell(1, 1).InsertTable(table);
        }

        // Save the workbook to the specified file path
        workbook.SaveAs(filePath);
    }
}

For example:

ExportDataSetToExcel(this.appData, @"C:\path\to\your\file.xlsx");

To export a dataset to an Excel file in C#, you can use EPPlus, which allow you to work with Excel files without needing Microsoft Office installed.

EPPlus is a popular library for creating and reading Excel files (XLSX). First, you need to install the EPPlus NuGet package.

// export dataset to excel in c# using EPPlus
public void ExportDataSetToExcel(DataSet dataSet, string filePath)
{
    // Ensure EPPlus uses the non-commercial license
    ExcelPackage.LicenseContext = LicenseContext.Commercial;

    // Create a new Excel package (i.e., a workbook)
    using (var package = new ExcelPackage())
    {
        // Loop through all the tables in the DataSet
        foreach (var table in dataSet.Tables)
        {
            // Create a worksheet for each DataTable in the DataSet
            var worksheet = package.Workbook.Worksheets.Add(table.TableName);

            // Load the DataTable into the worksheet, starting from cell A1
            worksheet.Cells["A1"].LoadFromDataTable(table, PrintHeaders: true);
        }
        // Save the Excel file to the specified path
        FileInfo fi = new FileInfo(filePath);
        package.SaveAs(fi);
    }
}

EPPlus supports .xlsx file format, and it's free to use for non-commercial purposes. For commercial use, a license is required.

This is the quickest method to export a DataTable to Excel in C# without using loops or interop. Both libraries enable creating Excel files, writing data from a DataSet, and saving it to a specified file path.