Published May 17, 2023
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
- EPPlus
- 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";
var data = ExcelDataToDataTable(path, "Table");
foreach (DataRow row in data.Rows)
{
foreach (var wsrow in row.ItemArray)
{
Console.Write(wsrow);
}
Console.WriteLine();
}
}
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 Exists");
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var xlPackage = new ExcelPackage(fi);
// get the first worksheet in the workbook
var worksheet = xlPackage.Workbook.Worksheets[sheetName];
dt = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column].ToDataTable(c =>
{
c.FirstRowIsColumnNames = true;
});
return dt;
}
}
using OfficeOpenXml;
using System;
using System.Data;
using System.IO;
class Program
{
static void Main(string[] args)
{
var path = @"sample.xlsx";
var data = ExcelDataToDataTable(path, "Table");
foreach (DataRow row in data.Rows)
{
foreach (var wsrow in row.ItemArray)
{
Console.Write(wsrow);
}
Console.WriteLine();
}
}
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 Exists");
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var xlPackage = new ExcelPackage(fi);
// get the first worksheet in the workbook
var worksheet = xlPackage.Workbook.Worksheets[sheetName];
dt = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column].ToDataTable(c =>
{
c.FirstRowIsColumnNames = true;
});
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"
Dim data = ExcelDataToDataTable(path, "Table")
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
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 Exists")
End If
ExcelPackage.LicenseContext = LicenseContext.NonCommercial
Dim xlPackage = New ExcelPackage(fi)
' get the first worksheet in the workbook
Dim worksheet = xlPackage.Workbook.Worksheets(sheetName)
dt = worksheet.Cells(1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column).ToDataTable(Sub(c)
c.FirstRowIsColumnNames = True
End Sub)
Return dt
End Function
End ClassThe above code calls a method that takes input parameters such as the file path and sheet name, and returns a DataTable as output.
5.1. Output
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;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
DataTable Table = workSheet.ToDataTable(true);
foreach (DataRow row in Table.Rows)
{
for (int row = 0; row < dataTable.Columns.Count; row ++)
{
Console.Write(row[row ] + " ");
}
Console.WriteLine();
}
using IronXL;
using System;
using System.Data;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
DataTable Table = workSheet.ToDataTable(true);
foreach (DataRow row in Table.Rows)
{
for (int row = 0; row < dataTable.Columns.Count; row ++)
{
Console.Write(row[row ] + " ");
}
Console.WriteLine();
}
Imports IronXL
Imports System
Imports System.Data
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
Private Table As DataTable = workSheet.ToDataTable(True)
For Each row As DataRow In Table.Rows
For row As Integer = 0 To dataTable.Columns.Count - 1
Console.Write(row(row) & " ")
Next row
Console.WriteLine()
Next rowIn 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
7. Conclusion
In conclusion, when it comes to reading Excel files and converting them to DataTable 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.
