EPPlus 读取 Excel 到 DataTable C#（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

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.

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

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

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.