How to Convert Excel to Datatable in C#

This blog demonstrates how to implement an Excel to DataTable conversion. This task can be done easily using IronXL, which provides a simple and efficient way to read and write Excel files in C#

1. IronXL

IronXL is a powerful and user-friendly C# library designed to simplify the reading, writing, exporting data and manipulation of Excel worksheets. It provides developers with a seamless way to work with Excel spreadsheets and automate tasks within their C#

With IronXL, you can easily import, export, and modify data in Excel workbooks, worksheets, and cells. It supports both the older XLS format and the newer XLSX format, making it compatible with a wide range of Excel versions.

The library offers a straightforward API that enables developers to perform various operations on Excel files, such as creating new workbooks, reading and writing data to cells, applying formatting, handling formulas, and even extracting charts and images. IronXL also supports working with complex data structures like tables, ranges, and named ranges.

2. Prerequisites

To work with Excel files and convert them to DataTable using IronXL in C#, there are a few prerequisites that need to be in place:

1. Development Environment: Ensure that you have a compatible development environment set up on your machine, such as Visual Studio or any other C# IDE.
2. IronXL Library: Download and install the IronXL library. You can obtain the library from the official IronXL website or via NuGet Package Manager in Visual Studio. Include the IronXL namespace in your C# project to access its classes and methods.
3. .NET Framework or .NET Core: IronXL supports both the .NET Framework and .NET Core. Ensure that you have the appropriate version installed on your machine, depending on your project requirements.

3. Creating a New Project in C#

To use the IronXL library for Excel-related tasks, the initial step is to create a .NET project in Visual Studio. While any version of Visual Studio can be used, it's recommended to use the latest one.

This tutorial will use the Console Application project template to showcase how to work with IronXL.

New Project

Once you have selected the project type, proceed to specify a name and location for the project. Furthermore, you have the option to choose the preferred framework for the project, such as .NET Core 6.

Project Configuration

After the solution is generated, you can access the Program.cs file, where you can input code and create/execute the application.

Program.cs

4. Installing 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 NuGet Packages

To install the IronXL library with NuGet Package Manager in Visual Studio, 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 use the IronXL library in your project.

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

NuGet Package Manager

IronXL in search results:

IronXL

4.2 Using the Visual Studio Command Line

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

• Go to Tools > NuGet Package Manager > Package Manager Console in Visual Studio.
• 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 ready for use.

Install the IronXL package

5. Convert Excel Data to DataTable Using IronXL

Exporting Excel data from an Excel file stream to DataTable in C# can be easily implemented using IronXL, an Excel Library. This section will discuss how you can convert Excel worksheet data to DataTable using IronXL.

First, an Excel file is required to convert its first worksheet data into DataTable. For this purpose, a sample Excel file should be created.

Sample Excel

Now let's look at the code samples.

using IronXL;
using System;
using System.Data;
//Workbook object
WorkBook workBook = WorkBook.Load("sample.xlsx");

WorkSheet workSheet = workBook.DefaultWorkSheet;
//var dt
DataTable dataTable = workSheet.ToDataTable(true);

foreach (DataRow row in dataTable.Rows)
{
    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        Console.Write(row [i]);
    }
    Console.WriteLine();
}

using IronXL;
using System;
using System.Data;
//Workbook object
WorkBook workBook = WorkBook.Load("sample.xlsx");

WorkSheet workSheet = workBook.DefaultWorkSheet;
//var dt
DataTable dataTable = workSheet.ToDataTable(true);

foreach (DataRow row in dataTable.Rows)
{
    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        Console.Write(row [i]);
    }
    Console.WriteLine();
}

Imports IronXL
Imports System
Imports System.Data
'Workbook object
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")

Private workSheet As WorkSheet = workBook.DefaultWorkSheet
'var dt
Private dataTable As DataTable = workSheet.ToDataTable(True)

For Each row As DataRow In dataTable.Rows
	For i As Integer = 0 To dataTable.Columns.Count - 1
		Console.Write(row (i))
	Next i
	Console.WriteLine()
Next row

In the above code, an Excel file is loaded as a WorkBook object using the WorkBook.Load method provided by IronXL and then saving its first worksheet data as WorkSheet. To convert this WorkSheet to DataTable, the workSheet.ToDataTable method is used to call upon the WorkSheet variable, then export data into DataTable and store it into a new DataTable variable.

In the end, print DataTable data in the console for Visualization.

Export Data to DataTable Output

6. Conclusion

The use of DataTable in C# provides a powerful and efficient solution for working with Excel data within applications. By leveraging libraries like IronXL, developers can easily convert Excel data into DataTable and unlock the full potential of C#

This tutorial explored the capabilities of IronXL and its seamless integration with C#. We discussed the prerequisites for working with Excel files, such as having a compatible development environment, installing the IronXL library, and ensuring the appropriate .NET Framework or .NET Core version.

We also walked through the process of creating a new C# project in Visual Studio and installing IronXL either through the NuGet Package Manager or the Visual Studio Command Line. With IronXL successfully integrated into the project, we demonstrated exporting data to a DataTable using the library's easy-to-use API.

By loading an Excel file, accessing the desired worksheet, and utilizing the ToDataTable method provided by IronXL, we were able to extract the data and store it in a DataTable. Finally, we showcased how to visualize the DataTable by printing its contents in the console.

Using IronXL and DataTable in C#, developers can build robust applications that seamlessly interact with Excel data, enabling efficient data processing, analysis, and visualization. With the knowledge gained from this tutorial, you can now confidently incorporate Excel worksheets into your C# projects and take advantage of the extensive functionality provided by DataTable. For more tutorials on IronXL, please visit the tutorial pages. If you need a detailed tutorial on Excel C#.