Test in production without watermarks.
Works wherever you need it to.
Get 30 days of fully functional product.
Have it up and running in minutes.
Full access to our support engineering team during your product trial
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
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.
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.
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.
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.
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
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.
The output will be the contents of the Excel file printed to the console.
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
In the above code example, we are simply loading the Excel file and converting it to a DataTable
using the workSheet.ToDataTable(true)
method.
The output will be the contents of the Excel file printed to the console.
In conclusion, when it comes to reading Excel files and converting them to DataTable
s 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 DataTable
s. 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.
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.
IronXL is a versatile library that empowers developers to effortlessly read, write, and manipulate Excel files within their .NET applications. It simplifies the process of working with spreadsheets, enabling data extraction, calculations, chart creation, and report generation.
To install EPPlus, create a new console project in Visual Studio, go to the NuGet Package Manager, search for 'EPPlus', and install the latest stable version.
To install IronXL, create a new project, open the NuGet Package Manager, search for 'IronXL', and install the desired package.
Using EPPlus, you can read an Excel file as a DataTable by loading the Excel file into an ExcelPackage, selecting the desired worksheet, and converting it to a DataTable.
Using IronXL, load the Excel file into a WorkBook, get the default worksheet, and convert it to a DataTable using the ToDataTable method.
IronXL offers a simpler and more intuitive API than EPPlus, making it easier to read and write Excel files. It also provides additional features for data analysis, report generation, and template creation.
Yes, EPPlus can be used to manipulate Excel files without requiring Microsoft Office or Excel installation on the server or client machines.
Yes, IronXL supports both .xls and .xlsx formats, allowing developers to work with different Excel file types.
Reading Excel data into a DataTable in C# is useful for data analysis and reporting, importing data into databases, data migration, data validation and cleaning, integration with other systems, automation, and batch processing.