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# projects.

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 the 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 and install to the current project and will 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. This section will discuss how you can convert Excel worksheet data to DataTable using IronXL.

First, a sample Excel file is required with the data you want to convert into a DataTable. For demonstration, a simple Excel file should be created.

Sample Excel

Now let's look at the code samples:

using IronXL;
using System;
using System.Data;

class Program
{
    static void Main()
    {
        // Load the Excel workbook from a file
        WorkBook workBook = WorkBook.Load("sample.xlsx");

        // Access the default worksheet in the workbook
        WorkSheet workSheet = workBook.DefaultWorkSheet;

        // Convert the worksheet data to a DataTable
        // Set the parameter to true to consider the first row as column headers
        DataTable dataTable = workSheet.ToDataTable(true);

        // Iterate over each row in the DataTable
        foreach (DataRow row in dataTable.Rows)
        {
            // Print each column in the row
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                Console.Write($"{row[i]} \t");
            }
            Console.WriteLine(); // Move to the next line after printing each row
        }
    }
}

using IronXL;
using System;
using System.Data;

class Program
{
    static void Main()
    {
        // Load the Excel workbook from a file
        WorkBook workBook = WorkBook.Load("sample.xlsx");

        // Access the default worksheet in the workbook
        WorkSheet workSheet = workBook.DefaultWorkSheet;

        // Convert the worksheet data to a DataTable
        // Set the parameter to true to consider the first row as column headers
        DataTable dataTable = workSheet.ToDataTable(true);

        // Iterate over each row in the DataTable
        foreach (DataRow row in dataTable.Rows)
        {
            // Print each column in the row
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                Console.Write($"{row[i]} \t");
            }
            Console.WriteLine(); // Move to the next line after printing each row
        }
    }
}

Imports Microsoft.VisualBasic
Imports IronXL
Imports System
Imports System.Data

Friend Class Program
	Shared Sub Main()
		' Load the Excel workbook from a file
		Dim workBook As WorkBook = WorkBook.Load("sample.xlsx")

		' Access the default worksheet in the workbook
		Dim workSheet As WorkSheet = workBook.DefaultWorkSheet

		' Convert the worksheet data to a DataTable
		' Set the parameter to true to consider the first row as column headers
		Dim dataTable As DataTable = workSheet.ToDataTable(True)

		' Iterate over each row in the DataTable
		For Each row As DataRow In dataTable.Rows
			' Print each column in the row
			For i As Integer = 0 To dataTable.Columns.Count - 1
				Console.Write($"{row(i)} " & vbTab)
			Next i
			Console.WriteLine() ' Move to the next line after printing each row
		Next row
	End Sub
End Class

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

Finally, the DataTable data is printed to 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.