Cómo exportar DataTable a Excel en ASP.NET Core

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.

Install-Package IronXL.Excel

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.

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

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

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.