EPPlus Read Excel to Datasheet C# Alternative Iron XL (2023)

1. Hierro XL
2. Iron XL Blog
3. Compare with other components
4. EPPlus Leer Excel Data Sheet C#

Posted on May 17, 2023

Looking for an excel library to read excel data intodata sheetCª#?

Read Excel filesdata sheetin C# has some practical applications in various industries and fields, such as data analysis and reporting, data import into database, 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 the ability to read Excel data and Excel files intodata sheet.The library is

1. EPPlus
2. Hierro XL

1. EPPlus library

EPPlusis a powerful open source library for creating and manipulating Excel files in C#. It provides a simple and intuitive API that allows developers to programmatically generate, read and modify Excel spreadsheets without requiring Microsoft Office or Excel to be installed on the server or client computers. 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 provides a complete set of features and functionality to simplify working with Excel files in C# applications.

2. Iron XL

Hierro XLis a powerful and versatile library that enables developers to easily read, write and manipulate Excel files in their .NET applications. With its intuitive and comprehensive API, IronXL simplifies the complex process of working with spreadsheets, allowing developers to easily and seamlessly extract data, perform calculations, create graphs and generate reports. Whether automating data import/export tasks, performing data analysis, or creating dynamic Excel templates, IronXL provides a powerful solution that saves developers valuable time and effort while ensuring the accuracy and reliability of processed data . With its seamless integration, extensive documentation and wide range of features, IronXL is the first choice for developers looking for a reliable and efficient tool to overcome the challenges associated with manipulating Excel files in the .NET Framework.

3. Installation of EPPlus library

To install the EPPlus library in your C# project, you must first create a new console-based project in Visual Studio. Afterwards, you can easily install it using the NuGet package manager.

After creating a new project, go to Tools and hover over 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 need to select the latest stable version. Then click the "Install" button on the right to install the EPPlus library.

Therefore, EPPlus will be added to your project.

4. IronXL installation

There are many ways to install IronXL, but in this section we will only discuss installing IronXL using the NuGet package manager.

Similar to Part 3, create a new project, go to Tools and open the NuGet Package Manager for your solution.

Enter the keyword "IronXL" in the search bar of the new window. A list will appear allowing you to select the IronXL package to install. Then click the Install button to install IronXL in your project.

IronXL is now ready to use.

5. Use the EPPlus library to read excel files and data into DataTable

In this section we examine the use of Excel asdata sheetUse the Excel library in the C# EPPlus package.

We need a sample excel file to readdata sheet. For this, we will generate a sample Excel file.

Below is the code to read the excel filedata sheet.

Use OfficeOpenXml; use system; use system data; use System.IO; class procedure { static void Main(string[] args) { var path = @"sample.xlsx"; var data = ExcelDataToDataTable(path, "table"); foreach (row DataRow 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 a new exception("File" + filepath + " Does not exist"); 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.Row.Row, worksheet.Dimension.Final.Column].ToDataTable(c => { c.FirstRowIsColumnNames = true; }); return data; } }

Use OfficeOpenXml; use system; use system data; use System.IO; class procedure { static void Main(string[] args) { var path = @"sample.xlsx"; var data = ExcelDataToDataTable(path, "table"); foreach (row DataRow 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 a new exception("File" + filepath + " Does not exist"); 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.Row.Row, worksheet.Dimension.Final.Column].ToDataTable(c => { c.FirstRowIsColumnNames = true; }); return data; } }

Importaciones OfficeOpenXmlImports SystemImports System.DataImports System.IOFriend 类 ProgramShared Sub Main(ByVal args() As String)Dim path = "sample.xlsx"Dim data = ExcelDataToDataTable(path, "Table")Para cada fila Como DataRow In data.RowsFor Each wsrow In row.ItemArrayConsole.Write(wsrow)Next wsrowConsole.WriteLine()Next rowEnd SubPublic Shared Function ExcelDataToDataTable(ByVal filePath As String, ByVal sheetName As String, Optional ByVal hasHeader As Boolean = True) As DataTableDim dt 作为新数据表()Dim fi = New FileInfo(filePath)' Comprobar si el archivo existeSi no es fi.Existe EntoncesLanzar una nueva excepción("Archivo" & filePath & "No existe")Finalizar IfExcelPackage.LicenseContext = LicenseContext.NonCommercialDim xlPackage = New ExcelPackage(fi)'获取 workbookDim worksheet = xlPackage.Workbook.Worksheets(sheetName)dt = worksheet.Cells(1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column).ToDataTable(Sub(c )c .FirstRowIsColumnNames = TrueEnd Sub)Return dtEnd FunctionEnd Class

The preceding code calls a method that takes input parameters, such as the file path and worksheet name, and returns adata sheetas output.

5.1. Production

6. Using IronXL to read Excel files as data tables

Using IronXL to convert an Excel worksheet and read it as a DataTable is as easy as a few lines of code. Also, we will use the above excel file as input.

The following code example performs the same function as the previous code, but uses IronXL.

use IronXL; use system; use system data; WorkBook workBook = WorkBook.Load("sample.xlsx"); worksheet worksheet = workbook.DefaultWorkSheet; table DataTable = worksheet.ToDataTable(true); foreach(rows in Table.Rows Data row) { for (int row = 0; row < datatable.Columns.Count; row ++) { Console.Write(row[row] + " "); } Console.WriteLine(); }

use IronXL; use system; use system data; WorkBook workBook = WorkBook.Load("sample.xlsx"); worksheet worksheet = workbook.DefaultWorkSheet; table DataTable = worksheet.ToDataTable(true); foreach(rows in Table.Rows Data row) { for (int row = 0; row < datatable.Columns.Count; row ++) { Console.Write(row[row] + " "); } Console.WriteLine(); }

Importa IronXLImports SystemImports System.DataPrivate workBook As WorkBook = WorkBook.Load("sample.xlsx")Private workSheet As WorkSheet = workBook.DefaultWorkSheetPrivate Table As DataTable = workSheet.ToDataTable(True)For Each row As DataRow In Table.RowsFor row 作为整数= 0 Para dataTable.Columns.Count - 1Console.Write(fila(fila) & " ")Siguiente filaConsole.WriteLine()Siguiente fila

In the code sample above we simply uploaded the excel file and converted it todata sheetuseworksheet.ToDataTable(true)method.

6.1 Salida

7. Conclusion

In summary, when it comes to reading Excel files and converting them to DataTables in C#, both EPPlus and IronXL are excellent libraries that provide great functionality and simplify the process.

EPPlusis an open source library that provides a simple API to programmatically generate, read and modify Excel worksheets. It supports .xls and .xlsx formats and provides efficient performance and memory management.

on the other hand,Hierro XLis a versatile library that allows developers to effortlessly work with Excel files in .NET applications. It provides an intuitive API and comprehensive functionality for extracting data, performing calculations, creating graphs and generating reports. IronXL simplifies complex Excel file manipulation tasks such as data import/export, data analysis and dynamic template creation.

Comparing the IronXL and EPPlus code samples, we found the EPPlus code to be quite long, complex and difficult to read. IronXL's code, on the other hand, is very simple and easy to read. IronXL uses the default worksheet, but in EPPlus you must give the name of the worksheet; otherwise you will get an error.

All in all I would recommend IronXL over EPPlus for manipulating excel files and reading excel filesdata sheetSecond. Also, IronXL offers more functionality than EPPlus for handling Excel files with simple code. For more IronXL tutorials, visit the following URLsassociate.