1. IronXL
2. IronXL Blog
3. Using IronXL
4. Excel to Datatable C#

Published May 24, 2023

How to Convert Excel to Datatable in C#

Excel is a widely used spreadsheet software that allows users to store, manipulate, and analyze data in a tabular format. However, when it comes to building applications that require the use of data stored in Excel files, it can be challenging to work with Excel file stream directly. This is where DataTable in C# comes in.

A DataTable is an in-memory representation of a table in a relational database, which allows efficient data manipulation and querying. By converting Excel object data into a DataTable, developers can leverage the full power of C# to process, analyze, export data and visualize the data. This Excel to DataTable conversion 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 worksheet. 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 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.

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.

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

4. Installing IronXL

The IronXL library can be downloaded and installed in different ways. We will only discuss two of these in this article.

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.

IronXL in search results:

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.

5. Convert Excel Data to DataTable Using IronXL

To export Excel data from Excel file stream to DataTable in C# can be easily implemented using IronXL, an Excel Library. In this section, we will discuss how you can convert Excel worksheet data to DataTable using IronXL.

First, we need an Excel file to convert its first worksheet data into DataTable. For this purpose we will use a sample Excel file.

Now lets look at our 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 above code, we are loading Excel file as workBook using WorkBook.Load method provided by IronXL and then saving its first worksheet data as workSheet. To convert this WorkSheet to DataTable we are using workSheet.ToDataTable(true) method that calls upon WorkSheet variable we previously created and exports data into DataTable and store it into new DataTable variable.

In the end we are printing DataTable data in console for Visualization.

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#

In this tutorial, we 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 link. If you need detailed tutorial on Excel C#