1. IronXL
2. IronXL Blog
3. Using IronXL
4. C# Fastest way to Export DataTable to Excel

Published March 31, 2023

How to Export DataTable to Excel in the Fastest Way

Microsoft Excel application is a spreadsheet for storing large data sets in the form of table. It provides various features on the data like calculations, data representation in graphs and charts, pivot tables, and a macro Visual Basic programming language. It is a leading software in industry due to its powerful data analysis and data visualization tools. With MS Excel, you can create, edit, view, and share your Excel file quickly and easily.

In C#, DataTable is the object in ADO.NET library. The DataTable class is actually a database table representation of tabular data in C# program. It provides a collection of rows and columns to store data table in grid form. Datatables are highly used in C# to represent Excel range in a formatted manner. Just like Excel worksheet, datatables in C# also provide filters and sorting methods to manipulate and export table data. It is created on runtime and when the application object is terminated, the data is lost. To save this data to a permanent source, we have to export data with various data types to either Excel file or CSV file.

Here in this article, I will show you how to create datatable in C# and then export data to Excel document using the IronXL .NET Excel library.

IronXL .NET Excel Library

IronXL is a C# .NET library, which helps creating Excel file with ease. Multiple sheets can be added to the newly created Excel workbook. It provides almost all MS Excel features to work with Excel files in C#. It helps developers open CSV files along with Excel files and modify existing data in Excel worksheet.

Most importantly, IronXL works without Microsoft Office Interop. This means no need to install Microsoft Office or any other special dependencies. IronXL works on .NET Core and .NET Framework 5, 6 and 7.

With IronXL, you can save or export data in different formats like XLS and XLSX, CSV data and TSV, JSON, XML and HTML, Binary and Byte Array. It helps edit metadata, workbook permissions and passwords, edit formulas, edit layout, work with cell range and cell styles and many more excel features.

Steps to Export Data from Datatable to Excel File

Prerequisites

To use IronXL in exporting data from datatable to Excel file in C#, we need the following components to be installed on local computer. Let's have a look at them one by one.

1. Visual Studio - Visual Studio is the IDE for C# programming and must be installed. You can download and install latest version from Visual Studio website.

   • Once the IDE is setup, a Console application/Windows forms needs to be created which will help to export datatables to Excel. The following screenshots show how to create a project.

     Create a Project

     Console App

     Name project

     .NET Framework

     After clicking Create in last screenshot, the project with name "DemoApp" is created.

2. IronXL library - IronXL library must be downloaded and installed in Visual Studio project. There are multiple ways to do it.

   • Using Visual Studio - It provides NuGet Package Manager to install IronXL. You can access it via Tools menu or Solution Explorer. The following screenshots help to install IronXL.

     Solution Explorer

     Tools - NuGet Package Manager

     Browse IronXL

   • Developer Command Prompt - Open Developer Command Prompt either from Visual Studio Tools menu or from Visual Studio folder. Type the following command to download and install IronXL in your project:

     PM > Install-Package IronXL.Excel

   • Directly from NuGet Package Manager - Navigate to this URL https://www.nuget.org/packages/ironxl.excel/ to download IronXL.

3. Add Necessary Namespaces - In order to create a DataTable and use IronXL, both should be referenced on top of the Program.cs file.

   using IronXL; //add reference...
   using System.Data;

   using IronXL; //add reference...
   using System.Data;

   Imports IronXL 'add reference...
   Imports System.Data

   VB   C#

Once all the prerequisites are fulfilled, it's time to export data from DataTable to Excel sheet.

Create a DataTable in C#

The following code helps to create a data table with two column headers and multiple rows:

//new datatable dt...
DataTable dt = new DataTable();
//add column names...
dt.Columns.Add("Animal");
dt.Columns.Add("Sound");
dt.Rows.Add("Lion", "Roars"); // first row...
dt.Rows.Add("Dog", "Barks");
dt.Rows.Add("Cat", "Meows");
dt.Rows.Add("Goat", "Bleats");
dt.Rows.Add("Wolf", "Howls");
dt.Rows.Add("Cheetah", "Purrs");

//new datatable dt...
DataTable dt = new DataTable();
//add column names...
dt.Columns.Add("Animal");
dt.Columns.Add("Sound");
dt.Rows.Add("Lion", "Roars"); // first row...
dt.Rows.Add("Dog", "Barks");
dt.Rows.Add("Cat", "Meows");
dt.Rows.Add("Goat", "Bleats");
dt.Rows.Add("Wolf", "Howls");
dt.Rows.Add("Cheetah", "Purrs");

'new datatable dt...
Dim dt As New DataTable()
'add column names...
dt.Columns.Add("Animal")
dt.Columns.Add("Sound")
dt.Rows.Add("Lion", "Roars") ' first row...
dt.Rows.Add("Dog", "Barks")
dt.Rows.Add("Cat", "Meows")
dt.Rows.Add("Goat", "Bleats")
dt.Rows.Add("Wolf", "Howls")
dt.Rows.Add("Cheetah", "Purrs")

First, I created a DataTable object and then added columns with column name "Animal" and "Sound". After that, using table object, multiple rows are added. In similar manner, multiple column headers can be added along with multiple rows.

Create Excel File using IronXL in C#

Creating an Excel file using IronXL is a two step process and very easy to implement in C#. As we know, Excel file is a combination of workbook having multiple worksheets, IronXL first creates an Excel workbook and then helps add worksheet to it. The following sample code will help create a workbook along with the worksheet:

//create new workbook...
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet ws = wb.DefaultWorkSheet;

//create new workbook...
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet ws = wb.DefaultWorkSheet;

'create new workbook...
Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim ws As WorkSheet = wb.DefaultWorkSheet

Exporting Data from DataTable to Excel Worksheet using IronXL

IronXL facilitates to add values to worksheet in very easy syntax. Here we will learn to export data from datatable created in previous section to the newly created Excel worksheet. Let's have a look at the code step by step.

Add column header to Excel worksheet

ws["A1"].Value = table.Columns[0].ToString();
ws["B1"].Value = table.Columns[1].ToString();
int rowCount = 2;

ws["A1"].Value = table.Columns[0].ToString();
ws["B1"].Value = table.Columns[1].ToString();
int rowCount = 2;

ws("A1").Value = table.Columns(0).ToString()
ws("B1").Value = table.Columns(1).ToString()
Dim rowCount As Integer = 2

In above code, Excel sheet column "A1" is assigned the value from the datatable column 1 at index 0 and next Excel column "B1" value is assigned from column 2 at index 1 in the datatable. The rowCount variable is set to a value 2 for reading rows from datatable.

Add Rows to Excel Worksheet

The following code will read each row from the datatable and assign it to new row in Excel file:

foreach (DataRow row in table.Rows)
{
ws["A" + (rowCount)].Value = row[0].ToString();
        ws["B" + (rowCount)].Value = row[1].ToString();
        rowCount++;
}

foreach (DataRow row in table.Rows)
{
ws["A" + (rowCount)].Value = row[0].ToString();
        ws["B" + (rowCount)].Value = row[1].ToString();
        rowCount++;
}

For Each row As DataRow In table.Rows
ws("A" & (rowCount)).Value = row(0).ToString()
		ws("B" & (rowCount)).Value = row(1).ToString()
		rowCount += 1
Next row

The rowCount variable is incremented every time so that new row is read from the datatable to Excel worksheet cells in the above code.

Save Excel File

Finally, save the Excel file using SaveAs method.

wb.SaveAs("DataTable_to_Excel_IronXL.xlsx");

wb.SaveAs("DataTable_to_Excel_IronXL.xlsx");

wb.SaveAs("DataTable_to_Excel_IronXL.xlsx")

The file can be saved in other formats as well e.g CSV (Comma Separated Values), JSON, XML.

wb.SaveAsCsv("DataTable_to_Excel_IronXL.csv");
wb.SaveAsJson("DataTable_to_Excel_IronXL.json");
wb.SaveAsXml("DataTable_to_Excel_IronXL.xml");

wb.SaveAsCsv("DataTable_to_Excel_IronXL.csv");
wb.SaveAsJson("DataTable_to_Excel_IronXL.json");
wb.SaveAsXml("DataTable_to_Excel_IronXL.xml");

wb.SaveAsCsv("DataTable_to_Excel_IronXL.csv")
wb.SaveAsJson("DataTable_to_Excel_IronXL.json")
wb.SaveAsXml("DataTable_to_Excel_IronXL.xml")

You can also save it with custom delimiter.

The final output of the file looks like this:

DataTable to Excel Output

Summary

In this article, we looked at how to create a datatable with columns and rows in C# and then created an Excel workbook with a default worksheet using IronXL. Finally, we exported tabular data from datatable to Excel and saved in xlsx file format.

IronXL is an intuitive C# Excel library which helps developers to work with Excel files even without Excel application. It allows developers to export from different formats like CSV data format to Excel for data manipulation and calculation. You can explore more powerful features of IronXL from the code examples page.

IronXL is free for individual development purposes and can be licensed for commercial use. It also provides all features in a 30-day free trial to ensure compatibility with one's needs. You can get more information on IronXL licenses from this link.