Skip to footer content
COMPARE TO OTHER COMPONENTS

EPPlus Read Excel to Datatable C# (IronXL Tutorial)

Looking for an Excel library to read Excel data into a DataTable in C#?

Reading Excel files into a DataTable in C# has several practical applications across various industries and domains, such as data analysis and reporting, importing data into databases, data migration, data validation and cleaning, integration with other systems, automation, and batch processing.

In this article, we will discuss and compare two different Excel libraries for .NET Core in C# that provide this feature to read Excel data and Excel files into a DataTable. The libraries are

  1. EPPlus
  2. IronXL

1. EPPlus library

EPPlus is a powerful open-source library for creating and manipulating Excel files in C#. It provides a simple and intuitive API that allows developers to generate, read, and modify Excel spreadsheets programmatically, without requiring Microsoft Office or Excel installation on the server or client machines. With EPPlus, you can easily create worksheets, add data, apply formatting, create charts, and perform other operations on Excel files. It supports both the older .xls format and the newer .xlsx format and provides efficient performance and memory management. Whether you need to generate dynamic reports, import/export data, or automate Excel-related tasks, EPPlus offers a comprehensive set of features and capabilities to simplify your Excel file handling in C# applications.

2. IronXL

IronXL is a powerful and versatile library that empowers developers with the ability to effortlessly read, write, and manipulate Excel files within their .NET applications. With its intuitive and comprehensive API, IronXL simplifies the otherwise complex process of working with spreadsheets, enabling developers to seamlessly extract data, perform calculations, create charts, and generate reports with ease. Whether it's automating data import/export tasks, performing data analysis, or creating dynamic Excel templates, IronXL provides a robust solution that saves developers valuable time and effort, while ensuring accuracy and reliability in handling Excel data. With its seamless integration, extensive documentation, and wide range of features, IronXL emerges as the go-to choice for developers seeking a dependable and efficient tool to conquer the challenges associated with Excel file manipulation in the .NET framework.

3. Installing EPPlus library

To install the EPPlus library in your C# project, first, you need to create a new console-based project in Visual Studio. After that, you can easily install it using the NuGet Package Manager.

Once the new project is created, go to Tools and hover over the NuGet Package Manager, then select "Manage NuGet Packages for Solution".

A new window will appear. In this new window, go to the "Browse" option and search for "EPPlus". A list of packages will appear, and you should select the latest stable version. Then, click on the "Install" button on the right side to install the EPPlus library.

Just like that, EPPlus will be added to your project.

4. Installing IronXL

There are many methods to install IronXL, but in this section, we will only discuss installing IronXL using the NuGet Package Manager.

Just like in section 3, create a new project and go to "Tools" and open the NuGet Package Manager for solutions.

In the new window, enter the keyword "IronXL" in the search bar. A list will appear, and you can select the IronXL package you want to install. Then, click on the "Install" button to install IronXL in your project.

Now IronXL is ready for use.

5. Reading Excel Files and Data to DataTable Using EPPlus Library

In this section, we will examine the code to read Excel as a DataTable using the C# EPPlus package Excel library.

We need a sample Excel file to read as a DataTable. For that purpose, we will generate a sample Excel file.

Below is the code for reading the Excel file as a DataTable.

using OfficeOpenXml;
using System;
using System.Data;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        var path = @"sample.xlsx"; // Specify the path to your Excel file
        var data = ExcelDataToDataTable(path, "Table");

        // Iterate through each row in the DataTable and print its contents
        foreach (DataRow row in data.Rows)
        {
            foreach (var wsrow in row.ItemArray)
            {
                Console.Write(wsrow + " ");
            }
            Console.WriteLine();
        }
    }

    /// <summary>
    /// Converts Excel sheet data to a DataTable.
    /// </summary>
    /// <param name="filePath">The path to the Excel file.</param>
    /// <param name="sheetName">The name of the worksheet to read from.</param>
    /// <param name="hasHeader">Indicates whether the Excel sheet has a header row.</param>
    /// <returns>DataTable containing Excel data.</returns>
    public static DataTable ExcelDataToDataTable(string filePath, string sheetName, bool hasHeader = true)
    {
        DataTable dt = new DataTable();
        var fi = new FileInfo(filePath);

        // Check if the file exists
        if (!fi.Exists)
            throw new Exception("File " + filePath + " does not exist.");

        // Set the license context for EPPlus
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

        // Load the Excel file into an EPPlus ExcelPackage
        using (var xlPackage = new ExcelPackage(fi))
        {
            // Get the specified worksheet from the workbook
            var worksheet = xlPackage.Workbook.Worksheets[sheetName];

            // Convert the worksheet to a DataTable, optionally using the first row as column names
            dt = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column].ToDataTable(c =>
            {
                c.FirstRowIsColumnNames = hasHeader;
            });
        }

        return dt;
    }
}
using OfficeOpenXml;
using System;
using System.Data;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        var path = @"sample.xlsx"; // Specify the path to your Excel file
        var data = ExcelDataToDataTable(path, "Table");

        // Iterate through each row in the DataTable and print its contents
        foreach (DataRow row in data.Rows)
        {
            foreach (var wsrow in row.ItemArray)
            {
                Console.Write(wsrow + " ");
            }
            Console.WriteLine();
        }
    }

    /// <summary>
    /// Converts Excel sheet data to a DataTable.
    /// </summary>
    /// <param name="filePath">The path to the Excel file.</param>
    /// <param name="sheetName">The name of the worksheet to read from.</param>
    /// <param name="hasHeader">Indicates whether the Excel sheet has a header row.</param>
    /// <returns>DataTable containing Excel data.</returns>
    public static DataTable ExcelDataToDataTable(string filePath, string sheetName, bool hasHeader = true)
    {
        DataTable dt = new DataTable();
        var fi = new FileInfo(filePath);

        // Check if the file exists
        if (!fi.Exists)
            throw new Exception("File " + filePath + " does not exist.");

        // Set the license context for EPPlus
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

        // Load the Excel file into an EPPlus ExcelPackage
        using (var xlPackage = new ExcelPackage(fi))
        {
            // Get the specified worksheet from the workbook
            var worksheet = xlPackage.Workbook.Worksheets[sheetName];

            // Convert the worksheet to a DataTable, optionally using the first row as column names
            dt = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column].ToDataTable(c =>
            {
                c.FirstRowIsColumnNames = hasHeader;
            });
        }

        return dt;
    }
}
Imports OfficeOpenXml
Imports System
Imports System.Data
Imports System.IO

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		Dim path = "sample.xlsx" ' Specify the path to your Excel file
		Dim data = ExcelDataToDataTable(path, "Table")

		' Iterate through each row in the DataTable and print its contents
		For Each row As DataRow In data.Rows
			For Each wsrow In row.ItemArray
				Console.Write(wsrow & " ")
			Next wsrow
			Console.WriteLine()
		Next row
	End Sub

	''' <summary>
	''' Converts Excel sheet data to a DataTable.
	''' </summary>
	''' <param name="filePath">The path to the Excel file.</param>
	''' <param name="sheetName">The name of the worksheet to read from.</param>
	''' <param name="hasHeader">Indicates whether the Excel sheet has a header row.</param>
	''' <returns>DataTable containing Excel data.</returns>
	Public Shared Function ExcelDataToDataTable(ByVal filePath As String, ByVal sheetName As String, Optional ByVal hasHeader As Boolean = True) As DataTable
		Dim dt As New DataTable()
		Dim fi = New FileInfo(filePath)

		' Check if the file exists
		If Not fi.Exists Then
			Throw New Exception("File " & filePath & " does not exist.")
		End If

		' Set the license context for EPPlus
		ExcelPackage.LicenseContext = LicenseContext.NonCommercial

		' Load the Excel file into an EPPlus ExcelPackage
		Using xlPackage = New ExcelPackage(fi)
			' Get the specified worksheet from the workbook
			Dim worksheet = xlPackage.Workbook.Worksheets(sheetName)

			' Convert the worksheet to a DataTable, optionally using the first row as column names
			dt = worksheet.Cells(1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column).ToDataTable(Sub(c)
				c.FirstRowIsColumnNames = hasHeader
			End Sub)
		End Using

		Return dt
	End Function
End Class
$vbLabelText   $csharpLabel

The above code defines a method that takes input parameters such as the file path and sheet name and returns a DataTable as output. It also iterates through each row of the DataTable, printing the data.

5.1. Output

The output will be the contents of the Excel file printed to the console.

6. Reading Excel Files as a DataTable using IronXL

Converting an Excel sheet and reading it as a DataTable is quite easy using IronXL, with just a few lines of code. Additionally, we will use the previous Excel file as input.

The following code example performs the same functionality as the above code, but using IronXL.

using IronXL;
using System;
using System.Data;

class Program
{
    static void Main(string[] args)
    {
        // Load the Excel file into an IronXL WorkBook
        WorkBook workBook = WorkBook.Load("sample.xlsx");

        // Get the default worksheet from the workbook
        WorkSheet workSheet = workBook.DefaultWorkSheet;

        // Convert the worksheet to a DataTable, specifying that the first row contains column names
        DataTable table = workSheet.ToDataTable(true);

        // Iterate through each row in the DataTable and print its contents
        foreach (DataRow row in table.Rows)
        {
            foreach (var cell in row.ItemArray)
            {
                Console.Write(cell + " ");
            }
            Console.WriteLine();
        }
    }
}
using IronXL;
using System;
using System.Data;

class Program
{
    static void Main(string[] args)
    {
        // Load the Excel file into an IronXL WorkBook
        WorkBook workBook = WorkBook.Load("sample.xlsx");

        // Get the default worksheet from the workbook
        WorkSheet workSheet = workBook.DefaultWorkSheet;

        // Convert the worksheet to a DataTable, specifying that the first row contains column names
        DataTable table = workSheet.ToDataTable(true);

        // Iterate through each row in the DataTable and print its contents
        foreach (DataRow row in table.Rows)
        {
            foreach (var cell in row.ItemArray)
            {
                Console.Write(cell + " ");
            }
            Console.WriteLine();
        }
    }
}
Imports IronXL
Imports System
Imports System.Data

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		' Load the Excel file into an IronXL WorkBook
		Dim workBook As WorkBook = WorkBook.Load("sample.xlsx")

		' Get the default worksheet from the workbook
		Dim workSheet As WorkSheet = workBook.DefaultWorkSheet

		' Convert the worksheet to a DataTable, specifying that the first row contains column names
		Dim table As DataTable = workSheet.ToDataTable(True)

		' Iterate through each row in the DataTable and print its contents
		For Each row As DataRow In table.Rows
			For Each cell In row.ItemArray
				Console.Write(cell & " ")
			Next cell
			Console.WriteLine()
		Next row
	End Sub
End Class
$vbLabelText   $csharpLabel

In the above code example, we are simply loading the Excel file and converting it to a DataTable using the workSheet.ToDataTable(true) method.

6.1 Output

The output will be the contents of the Excel file printed to the console.

7. Conclusion

In conclusion, when it comes to reading Excel files and converting them to DataTables in C#, both EPPlus and IronXL are excellent libraries that offer powerful features and simplify the process.

EPPlus is an open-source library that provides a straightforward API for generating, reading, and modifying Excel spreadsheets programmatically. It supports both .xls and .xlsx formats and offers efficient performance and memory management.

On the other hand, IronXL is a versatile library that enables developers to effortlessly work with Excel files in .NET applications. It offers an intuitive API and comprehensive features for extracting data, performing calculations, creating charts, and generating reports. IronXL simplifies complex Excel file manipulation tasks, such as data import/export, data analysis, and dynamic template creation.

On comparing the code examples of both IronXL and EPPlus, we find that the EPPlus code is quite lengthy, complex, and difficult to read. On the other hand, the IronXL code is quite simple and easy to read. IronXL uses the default worksheet, but in EPPlus, you need to give the name of the worksheet; otherwise, you will get an error.

In summary, I would recommend IronXL over EPPlus for manipulating Excel files and reading Excel files as DataTables. Also, IronXL offers a lot more features than EPPlus in handling Excel files with simple code. For more tutorials on IronXL, please visit the following link.

Please noteEPPlus is a registered trademark of its respective owner. This site is not affiliated with, endorsed by, or sponsored by EPPlus. All product names, logos, and brands are property of their respective owners. Comparisons are for informational purposes only and reflect publicly available information at the time of writing.

Frequently Asked Questions

How can I read Excel data into a DataTable in C#?

You can use IronXL to read Excel data into a DataTable by loading the Excel workbook with WorkBook.Load(), accessing the worksheet, and using ToDataTable() to convert the data.

What are the advantages of using IronXL for Excel manipulation?

IronXL offers a simple and intuitive API that simplifies Excel file manipulation. It includes features like data extraction, calculations, chart creation, and report generation, making it a comprehensive solution for developers.

Does IronXL support .xls and .xlsx file formats?

Yes, IronXL supports both .xls and .xlsx file formats, allowing for flexibility in working with different types of Excel files.

Can I use IronXL without Microsoft Office installed?

Yes, IronXL can be used to manipulate Excel files without requiring Microsoft Office or Excel to be installed on your machine.

How do I install IronXL in a .NET project?

To install IronXL, open the NuGet Package Manager in your .NET project, search for 'IronXL', and install the package. This will add IronXL to your project and allow you to start using its features.

What are some common issues when reading Excel files into a DataTable and how to troubleshoot them?

Common issues include incorrect file paths, unsupported formats, or improperly formatted data. Ensure the file path is correct, the format is supported, and the data is clean. IronXL provides clear error messages to help troubleshoot these issues.

How does IronXL compare to EPPlus for reading Excel files into a DataTable?

IronXL is noted for its ease of use and comprehensive features, whereas EPPlus is also effective but may be more complex to implement. IronXL provides a more straightforward API for developers.

Is IronXL suitable for large Excel files?

Yes, IronXL is designed to handle large Excel files efficiently, offering features that optimize performance and memory usage during file manipulation.

Can IronXL be used for data analysis and reporting?

Absolutely, IronXL is well-suited for data analysis and reporting, offering robust features for extracting and manipulating data, creating charts, and generating reports.

What are the key features of IronXL that benefit developers?

Key features of IronXL include seamless data extraction, powerful calculation capabilities, easy chart creation, efficient report generation, and broad support for Excel file formats.

Regan Pun
Software Engineer
Regan graduated from the University of Reading, with a BA in Electronic Engineering. Before joining Iron Software, his previous job roles had him laser-focused on single tasks; and what he most enjoys at Iron Software is the spectrum of work he gets to undertake, whether it’s adding value to ...Read More