How to Export DataTable to Excel without Interop in C#
By FoxLearn 12/13/2024 1:47:48 AM 14.69K
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.
- How To Open and Read an Excel file into a ListView in C#
- How to Open and Read Excel Files in VB.NET
- How to Open and Read Excel Files in C#
- How to create excel file in c# using dataset
- How to get excel sheet in C#
- How to read an excel (.xls/.xlsx) file in C#
- How to Read Excel file in C# using OleDb
- How to Read Excel file in C#