Skip to footer content
USING IRONXL

C# Fastest way to Export DataTable to Excel

When working with spreadsheets, the Microsoft Excel application is a popular spreadsheet tool designed for managing large data sets in a tabular format. It offers powerful features such as complex calculations, data visualization through charts and graphs, pivot tables, and support for automation via Visual Basic for Applications (VBA). Its robust data analysis and visualization tools have made Excel a leading choice across various industries. With Excel, you can easily create, edit, view, and share files, streamlining your data management tasks.

In C#, the DataTable object in the ADO.NET library represents tabular data within a program, much like an Excel worksheet. It organizes data into rows and columns, allowing for easy manipulation and export. Just like Excel, DataTable supports filtering, sorting, and formatting options, making it a go-to tool for managing data ranges in C#. However, DataTable is created at runtime, and its data is lost when the application is closed, unless exported to a more permanent format, such as an Excel file or CSV file.

Today, we will be exploring how to create a DataTable in C# and export its data to an Excel document using IronXL, a powerful .NET Excel library.

IronXL: A .NET Excel Library

IronXL is a C# .NET library, which simplifies the process of creating Excel files. With IronXL, you can create new spreadsheets, edit existing ones, work with Excel formulas, style your spreadsheet's cells, and more. Its rich range of features makes working with Excel files programmatically a breeze, and, most importantly, IronXL works without Microsoft Office Interop. This means no need to install Microsoft Office or any other special dependencies.

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 also boasts strong workbook security features such as adding permissions & passwords and allows you to edit the workbook metadata.

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 a 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 the latest version from Visual Studio website.

    • Once the IDE is set up, a Console application/Windows form needs to be created which will help to export DataTable to Excel. Following screenshots show how to create a project.

      Create a project

      Now, choose your project type. In our example, we will be creating a Console App.

      Console App

      Name your project and choose the location it will be saved to.

      Name project

      Finally, choose your .NET Framework, and click "Create".

      Dot NET Framework

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

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

    • Using Visual Studio - It provides the NuGet Package Manager to install IronXL. You can access it via the Tools menu or Solution Explorer. The following screenshots help to install IronXL. First, navigate to "Tools" in the top bar, or right-click within your solution explorer.

      Solution Explorer

      Tools - NuGet Package Manager

      Go to Manage NuGet Packages for Solution, and search for IronXL. Then, you just have to press "Install" and the IronXL library will be added to your project.

      Browse IronXL

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

      PM > Install-Package IronXL.Excel
      PM > Install-Package IronXL.Excel
      SHELL
    • Directly from NuGet Package Manager - Navigate to this URL https://www.nuget.org/packages/ironxl.excel/ to download IronXL.
  3. Add Necessary Namespaces - To create DataTable and use IronXL, both should be referenced on top of the Program.cs file.

    using IronXL; // Add reference to the IronXL library
    using System.Data; // Add reference to System.Data for DataTable
    using IronXL; // Add reference to the IronXL library
    using System.Data; // Add reference to System.Data for DataTable
    Imports IronXL ' Add reference to the IronXL library
    Imports System.Data ' Add reference to System.Data for DataTable
    $vbLabelText   $csharpLabel

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 creates a new data table with two column headers and multiple rows:

// Create a new DataTable object
DataTable dt = new DataTable();
// Add column names to the DataTable
dt.Columns.Add("Animal");
dt.Columns.Add("Sound");
// Add rows representing different animals and their sounds
dt.Rows.Add("Lion", "Roars");
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");
// Create a new DataTable object
DataTable dt = new DataTable();
// Add column names to the DataTable
dt.Columns.Add("Animal");
dt.Columns.Add("Sound");
// Add rows representing different animals and their sounds
dt.Rows.Add("Lion", "Roars");
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");
' Create a new DataTable object
Dim dt As New DataTable()
' Add column names to the DataTable
dt.Columns.Add("Animal")
dt.Columns.Add("Sound")
' Add rows representing different animals and their sounds
dt.Rows.Add("Lion", "Roars")
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")
$vbLabelText   $csharpLabel

First, we create a new DataTable called "dt". Then, using Columns.Add, we can add a specified number of columns to the data table by name; in our example, we have two columns called "Animal" and "Sound". We then use Rows.Add to add new rows, placing the content for each row within the brackets. The content is separated by commas, separating each string by column.

Create Excel File using IronXL in C#

When creating Excel file types from scratch using IronXL, it is a two-step process and very easy to implement in C#. IronXL first creates an Excel workbook and then helps add a worksheet to it. The following sample code demonstrates how to create a workbook along with the worksheet:

// Create a new workbook in XLSX format
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
// Reference to the default worksheet in the workbook
WorkSheet ws = wb.DefaultWorkSheet;
// Create a new workbook in XLSX format
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
// Reference to the default worksheet in the workbook
WorkSheet ws = wb.DefaultWorkSheet;
' Create a new workbook in XLSX format
Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
' Reference to the default worksheet in the workbook
Dim ws As WorkSheet = wb.DefaultWorkSheet
$vbLabelText   $csharpLabel

Exporting Data from DataTable to Excel Worksheet using IronXL

Utilizing IronXL to add values to a worksheet streamlines the entire process, with it being able to carry out this task with minimal code. Here we will learn to export data from the DataTable created in the previous section to the newly created Excel worksheet. Let's have a look at the code step by step.

Add column header to the Excel Worksheet

// Assign DataTable column names to the first row of the Excel worksheet
ws["A1"].Value = dt.Columns[0].ToString();
ws["B1"].Value = dt.Columns[1].ToString();
// Start adding data from the second row
int rowCount = 2;
// Assign DataTable column names to the first row of the Excel worksheet
ws["A1"].Value = dt.Columns[0].ToString();
ws["B1"].Value = dt.Columns[1].ToString();
// Start adding data from the second row
int rowCount = 2;
' Assign DataTable column names to the first row of the Excel worksheet
ws("A1").Value = dt.Columns(0).ToString()
ws("B1").Value = dt.Columns(1).ToString()
' Start adding data from the second row
Dim rowCount As Integer = 2
$vbLabelText   $csharpLabel

In the above code, the Excel sheet column "A1" is assigned the value from the DataTable column 1 at index 0 and the next Excel column "B1" value is assigned from column 2 at index 1 in the DataTable. The rowCount variable is set to a value of 2 for reading rows from the DataTable starting from row two; this ensures we don't count the header row.

Add rows to the Excel Worksheet

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

// Iterate through each row in the DataTable and add to Excel worksheet
foreach (DataRow row in dt.Rows)
{
    ws["A" + (rowCount)].Value = row[0].ToString();
    ws["B" + (rowCount)].Value = row[1].ToString();
    rowCount++;
}
// Iterate through each row in the DataTable and add to Excel worksheet
foreach (DataRow row in dt.Rows)
{
    ws["A" + (rowCount)].Value = row[0].ToString();
    ws["B" + (rowCount)].Value = row[1].ToString();
    rowCount++;
}
' Iterate through each row in the DataTable and add to Excel worksheet
For Each row As DataRow In dt.Rows
	ws("A" & (rowCount)).Value = row(0).ToString()
	ws("B" & (rowCount)).Value = row(1).ToString()
	rowCount += 1
Next row
$vbLabelText   $csharpLabel

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

Save Excel File

Finally, save the Excel file using the SaveAs() method.

// Save the workbook to a file
wb.SaveAs("DataTable_to_Excel_IronXL.xlsx");
// Save the workbook to a file
wb.SaveAs("DataTable_to_Excel_IronXL.xlsx");
' Save the workbook to a file
wb.SaveAs("DataTable_to_Excel_IronXL.xlsx")
$vbLabelText   $csharpLabel

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

// Save the workbook in different file formats
wb.SaveAsCsv("DataTable_to_Excel_IronXL.csv");
wb.SaveAsJson("DataTable_to_Excel_IronXL.json");
wb.SaveAsXml("DataTable_to_Excel_IronXL.xml");
// Save the workbook in different file formats
wb.SaveAsCsv("DataTable_to_Excel_IronXL.csv");
wb.SaveAsJson("DataTable_to_Excel_IronXL.json");
wb.SaveAsXml("DataTable_to_Excel_IronXL.xml");
' Save the workbook in different file formats
wb.SaveAsCsv("DataTable_to_Excel_IronXL.csv")
wb.SaveAsJson("DataTable_to_Excel_IronXL.json")
wb.SaveAsXml("DataTable_to_Excel_IronXL.xml")
$vbLabelText   $csharpLabel

You can also save it with a custom delimiter.

Output

The final output of the file looks like this:

DataTable to Excel Output

Summary

In this article, we demonstrated how to create a DataTable with columns and rows in C#, followed by generating an Excel workbook with a default worksheet using IronXL. We then successfully exported the tabular data from the DataTable into an Excel file and saved it in the .xlsx format.

IronXL is a user-friendly C# library that allows developers to work with Excel files seamlessly, even without having MS Excel installed. It supports exporting data from various formats, such as CSV files, for further manipulation and calculation.

To learn more about IronXL and its robust feature set, be sure to check out its extensive documentation. Want to try it out for yourself? IronXL also provides a free trial with full access to all features, so you can start exploring how this powerful library can improve your spreadsheet projects right away!

Frequently Asked Questions

How can I export a DataTable to Excel quickly using C#?

You can use the IronXL .NET Excel library to export a DataTable to Excel efficiently. This library allows you to create and manipulate Excel files without the need for Microsoft Office Interop.

What tools are necessary for exporting a DataTable to Excel in C#?

To export a DataTable to Excel using IronXL, you need to have Visual Studio installed and the IronXL library added to your project via NuGet Package Manager.

How do I create a DataTable in a C# application?

In C#, you create a DataTable by instantiating a new DataTable object. You can then add columns using Columns.Add and populate it with rows using Rows.Add.

What is the method to export a DataTable's data into an Excel file?

To export data from a DataTable to an Excel file, iterate through each DataTable row and assign the values to corresponding Excel cells using IronXL functionalities.

Can I save an Excel file in different formats using a .NET library?

Yes, with IronXL, you can save Excel files in various formats including XLSX, CSV, JSON, and XML, and even specify custom delimiters.

Do I need Microsoft Office to manage Excel files with a C# library?

No, you don't need Microsoft Office installed. IronXL enables you to manage Excel files programmatically without relying on Microsoft Office Interop.

What are the advantages of using a .NET Excel library?

IronXL offers numerous benefits such as creating and editing Excel files, applying formulas, styling cells, and exporting data in multiple formats. It also supports security features like password protection.

What steps are involved in saving an Excel workbook programmatically?

To save an Excel workbook using IronXL, use the SaveAs method, selecting the desired file format like XLSX, CSV, or JSON.

Is there a free trial available for a .NET Excel library?

Yes, IronXL offers a free trial that gives you full access to its features, allowing developers to test its capabilities before committing.

Where can I find detailed documentation for using a .NET Excel library?

Comprehensive documentation for IronXL, including guides and examples, can be found on their official website.

Regan Pun
Software Engineer
Regan graduated from the University of Reading, with a BA in Electronic Engineering. Before joining Iron Software, his previous job roles had him laser-focused on single tasks; and what he most enjoys at Iron Software is the spectrum of work he gets to undertake, whether it’s adding value to ...Read More