How to Export Datatable to Excel in ASP.NET Core

This article will explore the different ways to export DataTable to an Excel file and the benefits of doing so.

1. IronXL

IronXL is a powerful .NET library that provides developers with the ability to read, write, and manipulate Excel files using ASP.NET. It offers a range of features that simplify the process of working with Excel files, such as data extraction, formatting, and validation.

IronXL can handle both XLS and XLSX file formats, making it a versatile tool for working with Excel files. It also supports a range of other file formats, such as CSV and TSV, allowing users to work with different data sources. This article will explore the different features of IronXL and how it can help developers streamline the process of working with Excel files.

2. Prerequisites

Prerequisites for exporting data from DataTable to Excel using the IronXL library:

• To create an ASP.NET project, Visual Studio needs to be installed on your system. If you don't already have Visual Studio, you can download it from the Microsoft website.
• Before creating an ASP.NET project, you need to ensure that ASP.NET is installed on your system. You can verify this by going to the Control Panel and checking for the "Turn Windows features on or off" option. Make sure that the "ASP.NET" option is enabled.
• To be able to export data from DataTable to an Excel file using the IronXL library, you must have it installed. You can install it by downloading the IronXL NuGet package from the NuGet Package Manager within Visual Studio.

3. Creating New ASP.NET Core Project

To use the IronXL library with Excel, you need to create a .NET project in Visual Studio. You can use any version of Visual Studio, but it's recommended to use the latest version. Depending on your requirements, you can choose from different project templates, such as Windows Forms. To make things easier, the Console Application will be used for this tutorial.

Create a new project in Visual Studio

After selecting the Project type, provide a name for the project and designate its location. Choose the desired Framework, such as .NET Core 6, for the project.

Configure the new project

Once the solution is created, the Program.cs file will be opened, enabling you to enter code and construct/run the application.

.NET Framework selection

Finally, you can incorporate the library to test the code.

4. Install IronXL

The IronXL Library can be downloaded and installed in different ways.

These are:

• Using Visual Studio NuGet packages
• Using the Visual Studio Command-Line.

4.1 Using Visual Studio

To install the IronXL library, the simplest approach is to use the NuGet Package Manager in Visual Studio. Simply open the NuGet Package Manager and search for IronXL in the Browse tab. Once you have located IronXL in the search results, select it and proceed with the installation. Once the installation is complete, you can start using the IronXL library in your project.

The below screenshot shows how to open the NuGet Package Manager in Visual Studio.

Navigate to NuGet Package Manager

IronXL in search results:

Search and install the IronXL package in NuGet Package Manager UI

4.2 Using the Visual Studio Command-Line

Many people prefer to install packages using the command line interface. To install IronXL using the command line, follow these steps:

• In Visual Studio, go to Tools > NuGet Package Manager > Package Manager Console.
• Enter the following line in the Package Manager Console tab:

Install-Package IronXL.Excel

Now the package will download/install to the current project and be made ready to use.

Installation progress in Package Manager Console

5. Creating Excel file using DataTable DT Using IronXL

Exporting data from data tables to Excel file format is quite popular. Using IronXL, you can easily export DataTable data to an Excel document. The below-attached sample code example will convert a DataTable to an Excel worksheet saved in the XLSX file format.

5.1. DataTable to Excel File

First, you need to include the namespace of IronXL and other dependencies. Then you can start writing the following code as shown below.

using IronXL;
using System.Data;

class Program
{
    static void Main()
    {
        // Create a new DataTable.
        DataTable dt = new DataTable();
        dt.Columns.Add("DataSet", typeof(string)); // Add a column to the DataTable.

        // Add rows to the DataTable.
        dt.Rows.Add("Pen");
        dt.Rows.Add("Paper");
        dt.Rows.Add("Book");
        dt.Rows.Add("Cat");
        dt.Rows.Add("Panther");
        dt.Rows.Add("Cell");
        dt.Rows.Add("Bag");
        dt.Rows.Add("Bed");
        dt.Rows.Add("Fan");
        dt.Rows.Add("Smoke");

        // Create a new workbook.
        WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
        // Get the default worksheet.
        WorkSheet ws = wb.DefaultWorkSheet;
        // Start adding data from row 1.
        int rowCount = 1;

        // Populate the worksheet with data from the DataTable.
        foreach (DataRow row in dt.Rows)
        {
            ws["A" + (rowCount)].Value = row[0].ToString();
            rowCount++;
        }

        // Save the workbook as an XLSX file.
        wb.SaveAs("datatable.xlsx");
    }
}

using IronXL;
using System.Data;

class Program
{
    static void Main()
    {
        // Create a new DataTable.
        DataTable dt = new DataTable();
        dt.Columns.Add("DataSet", typeof(string)); // Add a column to the DataTable.

        // Add rows to the DataTable.
        dt.Rows.Add("Pen");
        dt.Rows.Add("Paper");
        dt.Rows.Add("Book");
        dt.Rows.Add("Cat");
        dt.Rows.Add("Panther");
        dt.Rows.Add("Cell");
        dt.Rows.Add("Bag");
        dt.Rows.Add("Bed");
        dt.Rows.Add("Fan");
        dt.Rows.Add("Smoke");

        // Create a new workbook.
        WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
        // Get the default worksheet.
        WorkSheet ws = wb.DefaultWorkSheet;
        // Start adding data from row 1.
        int rowCount = 1;

        // Populate the worksheet with data from the DataTable.
        foreach (DataRow row in dt.Rows)
        {
            ws["A" + (rowCount)].Value = row[0].ToString();
            rowCount++;
        }

        // Save the workbook as an XLSX file.
        wb.SaveAs("datatable.xlsx");
    }
}

Imports IronXL
Imports System.Data

Friend Class Program
	Shared Sub Main()
		' Create a new DataTable.
		Dim dt As New DataTable()
		dt.Columns.Add("DataSet", GetType(String)) ' Add a column to the DataTable.

		' Add rows to the DataTable.
		dt.Rows.Add("Pen")
		dt.Rows.Add("Paper")
		dt.Rows.Add("Book")
		dt.Rows.Add("Cat")
		dt.Rows.Add("Panther")
		dt.Rows.Add("Cell")
		dt.Rows.Add("Bag")
		dt.Rows.Add("Bed")
		dt.Rows.Add("Fan")
		dt.Rows.Add("Smoke")

		' Create a new workbook.
		Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
		' Get the default worksheet.
		Dim ws As WorkSheet = wb.DefaultWorkSheet
		' Start adding data from row 1.
		Dim rowCount As Integer = 1

		' Populate the worksheet with data from the DataTable.
		For Each row As DataRow In dt.Rows
			ws("A" & (rowCount)).Value = row(0).ToString()
			rowCount += 1
		Next row

		' Save the workbook as an XLSX file.
		wb.SaveAs("datatable.xlsx")
	End Sub
End Class

Output

Below is the output of the above source code showing how the data looks in the Excel file.

The output Excel file

5.2. DataTable to Supported Excel file formats

Using IronXL you can also convert DataTable to different Excel-supported file formats like CSV files. We will use the below example, but this time save it as a CSV file. This is all you need to do to save the workbook in a different file format.

using IronXL;
using System.Data;

class Program
{
    static void Main()
    {
        // Create a new DataTable.
        DataTable dt = new DataTable();
        dt.Columns.Add("DataSet", typeof(string)); // Add a column to the DataTable.

        // Add rows to the DataTable.
        dt.Rows.Add("Pen");
        dt.Rows.Add("Paper");
        dt.Rows.Add("Book");
        dt.Rows.Add("Cat");
        dt.Rows.Add("Panther");
        dt.Rows.Add("Cell");
        dt.Rows.Add("Bag");
        dt.Rows.Add("Bed");
        dt.Rows.Add("Fan");
        dt.Rows.Add("Smoke");

        // Create a new workbook.
        WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
        // Get the default worksheet.
        WorkSheet ws = wb.DefaultWorkSheet;
        // Start adding data from row 1.
        int rowCount = 1;

        // Populate the worksheet with data from the DataTable.
        foreach (DataRow row in dt.Rows)
        {
            ws["A" + (rowCount)].Value = row[0].ToString();
            rowCount++;
        }

        // Save the workbook as a CSV file.
        wb.SaveAsCsv("datatable.csv");
    }
}

using IronXL;
using System.Data;

class Program
{
    static void Main()
    {
        // Create a new DataTable.
        DataTable dt = new DataTable();
        dt.Columns.Add("DataSet", typeof(string)); // Add a column to the DataTable.

        // Add rows to the DataTable.
        dt.Rows.Add("Pen");
        dt.Rows.Add("Paper");
        dt.Rows.Add("Book");
        dt.Rows.Add("Cat");
        dt.Rows.Add("Panther");
        dt.Rows.Add("Cell");
        dt.Rows.Add("Bag");
        dt.Rows.Add("Bed");
        dt.Rows.Add("Fan");
        dt.Rows.Add("Smoke");

        // Create a new workbook.
        WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
        // Get the default worksheet.
        WorkSheet ws = wb.DefaultWorkSheet;
        // Start adding data from row 1.
        int rowCount = 1;

        // Populate the worksheet with data from the DataTable.
        foreach (DataRow row in dt.Rows)
        {
            ws["A" + (rowCount)].Value = row[0].ToString();
            rowCount++;
        }

        // Save the workbook as a CSV file.
        wb.SaveAsCsv("datatable.csv");
    }
}

Imports IronXL
Imports System.Data

Friend Class Program
	Shared Sub Main()
		' Create a new DataTable.
		Dim dt As New DataTable()
		dt.Columns.Add("DataSet", GetType(String)) ' Add a column to the DataTable.

		' Add rows to the DataTable.
		dt.Rows.Add("Pen")
		dt.Rows.Add("Paper")
		dt.Rows.Add("Book")
		dt.Rows.Add("Cat")
		dt.Rows.Add("Panther")
		dt.Rows.Add("Cell")
		dt.Rows.Add("Bag")
		dt.Rows.Add("Bed")
		dt.Rows.Add("Fan")
		dt.Rows.Add("Smoke")

		' Create a new workbook.
		Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
		' Get the default worksheet.
		Dim ws As WorkSheet = wb.DefaultWorkSheet
		' Start adding data from row 1.
		Dim rowCount As Integer = 1

		' Populate the worksheet with data from the DataTable.
		For Each row As DataRow In dt.Rows
			ws("A" & (rowCount)).Value = row(0).ToString()
			rowCount += 1
		Next row

		' Save the workbook as a CSV file.
		wb.SaveAsCsv("datatable.csv")
	End Sub
End Class

The output CSV file

6. Conclusion

Exporting a DataTable to Excel is a crucial step in data analysis and management. Excel spreadsheets are widely used for data visualization and presentation, and the IronXL library offers a range of features to simplify the process of working with Excel files in ASP.NET. This article covered the steps to create a new ASP.NET project, install IronXL, and use it to export data from a DataTable to an Excel file. Additionally, it demonstrated how to save the workbook in different file formats, such as CSV. By utilizing IronXL, developers can streamline the process of working with Excel files and create visually appealing presentations of their data.

To learn more about IronXL and exporting DataTable to different Excel formats, visit the following tutorial.

IronXL is free to install and use for non-commercial development purposes. A free trial is available for testing in production. Look at the pricing plans for more details about prices and licensing.

Users can also benefit from the Iron Suite, a collection of five professional libraries including IronXL, IronPDF, and more.