如何读取 C# 中的 Excel 文件(开发者教程)

查克尼特·宾
查克尼特·宾
2019年五月26日
更新 2024年十二月10日
分享:
This article was translated from English: Does it need improvement?
Translated
View the article in English

本教程解释了如何在C#中读取Excel文件,以及如何执行日常任务,如数据验证、数据库转换、Web API集成和公式修改。 本文引用了使用IronXL .NET Excel库的代码示例。


概述

IronXL 支持使用 C# 读取和编辑 Microsoft Excel 文档。 IronXL既不需要Microsoft Excel也不需要Interop。 事实上,IronXL 提供比 Microsoft.Office.Interop.Excel 更快速且更直观的 API

IronXL 包括:

  • 我们的 .NET 工程师提供专门的产品支持。
  • 通过 Microsoft Visual Studio 轻松安装
  • 开发免费试用测试。 许可证从$749。

    使用 IronXL 软件库在 C# 和 VB.NET 中读取和创建 Excel 文件很容易。

使用IronXL读取.XLS和.XLSX Excel文件

以下是使用IronXL读取Excel文件的整体工作流程的总结:

  1. 安装 IronXL Excel 库。 我们可以通过使用我们的NuGet 包或下载.Net Excel DLL来实现这一点。

  2. 使用WorkBook.Load方法读取任何XLS、XLSX或CSV文档。

  3. 使用直观语法获取单元格值:sheet ["A11"].DecimalValue
:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-1.cs
using IronXL;
using System;
using System.Linq;

// Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
WorkBook workBook = WorkBook.Load("test.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();

// Select cells easily in Excel notation and return the calculated value
int cellValue = workSheet["A2"].IntValue;

// Read from Ranges of cells elegantly.
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Advanced Operations
// Calculate aggregate values such as Min, Max and Sum
decimal sum = workSheet["A2:A10"].Sum();

// Linq compatible
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);
Imports IronXL
Imports System
Imports System.Linq

' Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
Private workBook As WorkBook = WorkBook.Load("test.xlsx")
Private workSheet As WorkSheet = workBook.WorkSheets.First()

' Select cells easily in Excel notation and return the calculated value
Private cellValue As Integer = workSheet("A2").IntValue

' Read from Ranges of cells elegantly.
For Each cell In workSheet("A2:A10")
	Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell

' Advanced Operations
' Calculate aggregate values such as Min, Max and Sum
Dim sum As Decimal = workSheet("A2:A10").Sum()

' Linq compatible
Dim max As Decimal = workSheet("A2:A10").Max(Function(c) c.DecimalValue)
$vbLabelText   $csharpLabel

本教程接下来部分中使用的代码示例(以及示例项目代码)将适用于三个示例 Excel 电子表格(请参见下方的视觉效果): Vs Spreadsheets related to 使用IronXL读取.XLS和.XLSX Excel文件


教程

1. 免费下载 IronXL C# 库

立即在您的项目中开始使用IronXL,并享受免费试用。

第一步:
green arrow pointer


我们需要做的第一件事是安装IronXL.Excel库,为 .NET 框架添加 Excel 功能。

安装IronXL.Excel,最简单的方法是使用我们的NuGet包,不过您也可以选择手动将DLL安装到您的项目或全局程序集缓存中。

安装 IronXL NuGet 软件包

  1. 在 Visual Studio 中,右键单击项目选择“管理 NuGet 包...”。

  2. 搜索IronXL.Excel包并点击安装按钮以将其添加到项目中

    Ef Nuget related to 安装 IronXL NuGet 软件包

    另一种安装 IronXL 库的方法是使用 NuGet 包管理器控制台:

  3. 输入包管理器控制台

  4. 输入> Install-Package IronXL.Excel
  PM > Install-Package IronXL.Excel

此外,您可以在 NuGet 网站上查看该包

手动安装

或者,我们可以通过下载IronXL .NET Excel DLL 并手动安装到Visual Studio中来开始。

2. 加载一个Excel工作簿

WorkBook 类表示一个 Excel 表格。 要使用C#打开Excel文件,我们使用WorkBook.Load方法,指定Excel文件的路径。

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-2.cs
WorkBook workBook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");
Dim workBook As WorkBook = WorkBook.Load("Spreadsheets\\GDP.xlsx")
$vbLabelText   $csharpLabel

示例:ExcelToDBProcessor

每个WorkBook可以有多个WorkSheet对象。 每一个代表Excel文档中的一个单独的Excel工作表。 使用 WorkBook.GetWorkSheet 方法检索特定 Excel 工作表的引用。


:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-3.cs

示例:ExcelToDB

创建新的Excel文档

要创建一个新的 Excel 文档,构造一个新的WorkBook对象,并使用有效的文件类型。

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-4.cs
WorkBook workBook = new WorkBook(ExcelFileFormat.XLSX);
Dim workBook As New WorkBook(ExcelFileFormat.XLSX)
$vbLabelText   $csharpLabel

示例:ApiToExcelProcessor

注意:使用ExcelFileFormat.XLS来支持旧版本的Microsoft Excel(95及更早版本)。

向Excel文档添加工作表

如前所述,IronXL WorkBook 包含一个或多个 WorkSheet 的集合。

这是一份包含两个工作表的工作簿在Excel中的样子。

这就是 Excel 中包含两个工作表的工作簿的样子。

要创建新的WorkSheet,请使用工作表的名称调用WorkBook.CreateWorkSheet

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-5.cs
WorkSheet workSheet = workBook.GetWorkSheet("GDPByCountry");
Dim workSheet As WorkSheet = workBook.GetWorkSheet("GDPByCountry")
$vbLabelText   $csharpLabel

3.访问单元格值

读取和编辑单个单元格

访问单个电子表格单元格的值是通过从其WorkSheet中检索所需的单元格来实现的。 如下所示:

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-16.cs
WorkBook workBook = WorkBook.Load("test.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
IronXL.Cell cell = workSheet["B1"].First();
Dim workBook As WorkBook = WorkBook.Load("test.xlsx")
Dim workSheet As WorkSheet = workBook.DefaultWorkSheet
Dim cell As IronXL.Cell = workSheet("B1").First()
$vbLabelText   $csharpLabel

IronXL 的Cell类表示 Excel 电子表格中的单个单元格。 它包含属性和方法,使用户能够直接访问和修改单元格的值。

每个WorkSheet对象管理一个Cell对象索引,对应于 Excel 工作表中的每个单元格值。 在上述源代码中,我们使用标准数组索引语法通过其行和列索引(在本例中为单元格 B1)引用所需的单元格。

通过引用单元格对象,我们可以读取和写入电子表格单元格中的数据:

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-17.cs
IronXL.Cell cell = workSheet["B1"].First();
string value = cell.StringValue;   // Read the value of the cell as a string
Console.WriteLine(value);

cell.Value = "10.3289";           // Write a new value to the cell
Console.WriteLine(cell.StringValue);
Dim cell As IronXL.Cell = workSheet("B1").First()
Dim value As String = cell.StringValue ' Read the value of the cell as a string
Console.WriteLine(value)

cell.Value = "10.3289" ' Write a new value to the cell
Console.WriteLine(cell.StringValue)
$vbLabelText   $csharpLabel

读取和写入一系列单元格的值

Range 类表示一个二维的 Cell 对象集合。 此集合指的是Excel单元格的字面范围。 通过在WorkSheet对象上使用字符串索引器来获取范围。

参数文本可以是单个单元格的坐标(例如,之前所示的“A1”)或是从左到右从上到下的单元格范围(例如,“B2:E5”)。 也可以在WorkSheet上调用GetRange

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-6.cs
Range range = workSheet["D2:D101"];
Dim range As Range = workSheet("D2:D101")
$vbLabelText   $csharpLabel

示例:DataValidation

有几种方法可以读取或编辑范围内单元格的值。 如果已知计数,请使用 For 循环。

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-7.cs
// Iterate through the rows
for (var y = 2; y <= 101; y++)
{
    var result = new PersonValidationResult { Row = y };
    results.Add(result);

    // Get all cells for the person
    var cells = workSheet[$"A{y}:E{y}"].ToList();

    // Validate the phone number (1 = B)
    var phoneNumber = cells[1].Value;
    result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);

    // Validate the email address (3 = D)
    result.EmailErrorMessage = ValidateEmailAddress((string)cells[3].Value);

    // Get the raw date in the format of Month Day[suffix], Year (4 = E)
    var rawDate = (string)cells[4].Value;
    result.DateErrorMessage = ValidateDate(rawDate);
}
' Iterate through the rows
For y = 2 To 101
	Dim result = New PersonValidationResult With {.Row = y}
	results.Add(result)

	' Get all cells for the person
	Dim cells = workSheet($"A{y}:E{y}").ToList()

	' Validate the phone number (1 = B)
	Dim phoneNumber = cells(1).Value
	result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, CStr(phoneNumber))

	' Validate the email address (3 = D)
	result.EmailErrorMessage = ValidateEmailAddress(CStr(cells(3).Value))

	' Get the raw date in the format of Month Day[suffix], Year (4 = E)
	Dim rawDate = CStr(cells(4).Value)
	result.DateErrorMessage = ValidateDate(rawDate)
Next y
$vbLabelText   $csharpLabel

示例:DataValidation

向电子表格添加公式

通过Formula属性设置Cell的公式。

以下代码遍历每个状态,并在C列中放置百分比总计。

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-13.cs
// Iterate through all rows with a value
for (var y = 2 ; y < i ; y++)
{
    // Get the C cell
    Cell cell = workSheet[$"C{y}"].First();

    // Set the formula for the Percentage of Total column
    cell.Formula = $"=B{y}/B{i}";
}
' Iterate through all rows with a value
Dim y = 2
Do While y < i
	' Get the C cell
	Dim cell As Cell = workSheet($"C{y}").First()

	' Set the formula for the Percentage of Total column
	cell.Formula = $"=B{y}/B{i}"
	y += 1
Loop
$vbLabelText   $csharpLabel

示例:AddFormulaeProcessor

验证电子表格数据

使用IronXL验证数据表。 DataValidation示例使用libphonenumber-csharp来验证电话号码,并使用标准的C# API来验证电子邮件地址和日期。

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-8.cs
// Iterate through the rows
for (var i = 2; i <= 101; i++)
{
    var result = new PersonValidationResult { Row = i };
    results.Add(result);

    // Get all cells for the person
    var cells = worksheet[$"A{i}:E{i}"].ToList();

    // Validate the phone number (1 = B)
    var phoneNumber = cells[1].Value;
    result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);

    // Validate the email address (3 = D)
    result.EmailErrorMessage = ValidateEmailAddress((string)cells[3].Value);

    // Get the raw date in the format of Month Day[suffix], Year (4 = E)
    var rawDate = (string)cells[4].Value;
    result.DateErrorMessage = ValidateDate(rawDate);
}
' Iterate through the rows
For i = 2 To 101
	Dim result = New PersonValidationResult With {.Row = i}
	results.Add(result)

	' Get all cells for the person
	Dim cells = worksheet($"A{i}:E{i}").ToList()

	' Validate the phone number (1 = B)
	Dim phoneNumber = cells(1).Value
	result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, CStr(phoneNumber))

	' Validate the email address (3 = D)
	result.EmailErrorMessage = ValidateEmailAddress(CStr(cells(3).Value))

	' Get the raw date in the format of Month Day[suffix], Year (4 = E)
	Dim rawDate = CStr(cells(4).Value)
	result.DateErrorMessage = ValidateDate(rawDate)
Next i
$vbLabelText   $csharpLabel

上面的代码遍历电子表格中的每一行,并将单元格作为列表获取。每个验证方法检查单元格的值,如果值无效,则返回错误消息。

此代码创建一个新工作表,指定表头,并输出错误信息结果,以便记录无效数据。

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-9.cs
var resultsSheet = workBook.CreateWorkSheet("Results");
resultsSheet["A1"].Value = "Row";
resultsSheet["B1"].Value = "Valid";
resultsSheet["C1"].Value = "Phone Error";
resultsSheet["D1"].Value = "Email Error";
resultsSheet["E1"].Value = "Date Error";
for (var i = 0; i < results.Count; i++)
{
    var result = results[i];
    resultsSheet[$"A{i + 2}"].Value = result.Row;
    resultsSheet[$"B{i + 2}"].Value = result.IsValid ? "Yes" : "No";
    resultsSheet[$"C{i + 2}"].Value = result.PhoneNumberErrorMessage;
    resultsSheet[$"D{i + 2}"].Value = result.EmailErrorMessage;
    resultsSheet[$"E{i + 2}"].Value = result.DateErrorMessage;
}
workBook.SaveAs(@"Spreadsheets\\PeopleValidated.xlsx");
Dim resultsSheet = workBook.CreateWorkSheet("Results")
resultsSheet("A1").Value = "Row"
resultsSheet("B1").Value = "Valid"
resultsSheet("C1").Value = "Phone Error"
resultsSheet("D1").Value = "Email Error"
resultsSheet("E1").Value = "Date Error"
For i = 0 To results.Count - 1
	Dim result = results(i)
	resultsSheet($"A{i + 2}").Value = result.Row
	resultsSheet($"B{i + 2}").Value = If(result.IsValid, "Yes", "No")
	resultsSheet($"C{i + 2}").Value = result.PhoneNumberErrorMessage
	resultsSheet($"D{i + 2}").Value = result.EmailErrorMessage
	resultsSheet($"E{i + 2}").Value = result.DateErrorMessage
Next i
workBook.SaveAs("Spreadsheets\\PeopleValidated.xlsx")
$vbLabelText   $csharpLabel

4. 使用实体框架导出数据

使用IronXL将数据导出到数据库或将Excel电子表格转换为数据库。 ExcelToDB示例读取包含各国GDP的电子表格,然后将该数据导出到SQLite。

它使用EntityFramework来构建数据库,然后逐行导出数据。

添加 SQLite 实体框架 NuGet 包。 Ironxl Nuget related to 4. 使用实体框架导出数据 EntityFramework允许您创建可以将数据导出到数据库的模型对象。

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-10.cs
public class Country
    {
        [Key]
        public Guid Key { get; set; }
        public string Name { get; set; }
        public decimal GDP { get; set; }
    }
Public Class Country
		<Key>
		Public Property Key() As Guid
		Public Property Name() As String
		Public Property GDP() As Decimal
End Class
$vbLabelText   $csharpLabel

要使用不同的数据库,请安装相应的NuGet包,并找到UseSqLite()的等效项。

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-11.cs
public class CountryContext : DbContext
    {
        public DbSet<Country> Countries { get; set; }
        public CountryContext()
        {
            //TODO: Make async
            Database.EnsureCreated();
        }
        /// <summary>
        /// Configure context to use Sqlite
        /// </summary>
        /// <param name="optionsBuilder"></param>
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connection = new SqliteConnection($"Data Source=Country.db");
            connection.Open();
            var command = connection.CreateCommand();
            //Create the database if it doesn't already exist
            command.CommandText = $"PRAGMA foreign_keys = ON;";
            command.ExecuteNonQuery();
            optionsBuilder.UseSqlite(connection);
            base.OnConfiguring(optionsBuilder);
        }
    }
Public Class CountryContext
	Inherits DbContext

		Public Property Countries() As DbSet(Of Country)
		Public Sub New()
			'TODO: Make async
			Database.EnsureCreated()
		End Sub
		''' <summary>
		''' Configure context to use Sqlite
		''' </summary>
		''' <param name="optionsBuilder"></param>
		Protected Overrides Sub OnConfiguring(ByVal optionsBuilder As DbContextOptionsBuilder)
			Dim connection = New SqliteConnection($"Data Source=Country.db")
			connection.Open()
			Dim command = connection.CreateCommand()
			'Create the database if it doesn't already exist
			command.CommandText = $"PRAGMA foreign_keys = ON;"
			command.ExecuteNonQuery()
			optionsBuilder.UseSqlite(connection)
			MyBase.OnConfiguring(optionsBuilder)
		End Sub
End Class
$vbLabelText   $csharpLabel

创建一个CountryContext,遍历范围以创建每个记录,然后使用SaveAsync将数据提交到数据库

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-12.cs
public async Task ProcessAsync()
{
    //Get the first worksheet
    var workbook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");
    var worksheet = workbook.GetWorkSheet("GDPByCountry");
    //Create the database connection
    using (var countryContext = new CountryContext())
    {
        //Iterate through all the cells
        for (var i = 2; i <= 213; i++)
        {
            //Get the range from A-B
            var range = worksheet[$"A{i}:B{i}"].ToList();
            //Create a Country entity to be saved to the database
            var country = new Country
            {
                Name = (string)range[0].Value,
                GDP = (decimal)(double)range[1].Value
            };
            //Add the entity
            await countryContext.Countries.AddAsync(country);
        }
        //Commit changes to the database
        await countryContext.SaveChangesAsync();
    }
}
Public Async Function ProcessAsync() As Task
	'Get the first worksheet
	Dim workbook = WorkBook.Load("Spreadsheets\\GDP.xlsx")
	Dim worksheet = workbook.GetWorkSheet("GDPByCountry")
	'Create the database connection
	Using countryContext As New CountryContext()
		'Iterate through all the cells
		For i = 2 To 213
			'Get the range from A-B
			Dim range = worksheet($"A{i}:B{i}").ToList()
			'Create a Country entity to be saved to the database
			Dim country As New Country With {
				.Name = CStr(range(0).Value),
				.GDP = CDec(CDbl(range(1).Value))
			}
			'Add the entity
			Await countryContext.Countries.AddAsync(country)
		Next i
		'Commit changes to the database
		Await countryContext.SaveChangesAsync()
	End Using
End Function
$vbLabelText   $csharpLabel

示例:ExcelToDB

5. 从API下载数据到电子表格

以下调用通过RestClient.Net进行REST调用。 它下载 JSON 并将其转换为类型为RestCountry的“列表”。 然后可以轻松地遍历每个国家,并将REST API的数据保存到Excel电子表格中。

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-14.cs
var client = new Client(new Uri("https://restcountries.eu/rest/v2/"));
List<RestCountry> countries = await client.GetAsync<List<RestCountry>>();
Dim client As New Client(New Uri("https://restcountries.eu/rest/v2/"))
Dim countries As List(Of RestCountry) = Await client.GetAsync(Of List(Of RestCountry))()
$vbLabelText   $csharpLabel

示例:ApiToExcel

这是 API JSON 数据的样子。 Country Data related to 5. 从API下载数据到电子表格 以下代码会遍历所有国家,并在电子表格中设置名称、人口、地区、数字代码和前 3 种语言。

:path=/static-assets/excel/content-code-examples/tutorials/how-to-read-excel-file-csharp-15.cs
for (var i = 2; i < countries.Count; i++)
{
    var country = countries[i];
    //Set the basic values
    workSheet[$"A{i}"].Value = country.name;
    workSheet[$"B{i}"].Value = country.population;
    workSheet[$"G{i}"].Value = country.region;
    workSheet[$"H{i}"].Value = country.numericCode;
    //Iterate through languages
    for (var x = 0; x < 3; x++)
    {
        if (x > (country.languages.Count - 1)) break;
        var language = country.languages[x];
        //Get the letter for the column
        var columnLetter = GetColumnLetter(4 + x);
        //Set the language name
        workSheet[$"{columnLetter}{i}"].Value = language.name;
    }
}
For i = 2 To countries.Count - 1
	Dim country = countries(i)
	'Set the basic values
	workSheet($"A{i}").Value = country.name
	workSheet($"B{i}").Value = country.population
	workSheet($"G{i}").Value = country.region
	workSheet($"H{i}").Value = country.numericCode
	'Iterate through languages
	For x = 0 To 2
		If x > (country.languages.Count - 1) Then
			Exit For
		End If
		Dim language = country.languages(x)
		'Get the letter for the column
		Dim columnLetter = GetColumnLetter(4 + x)
		'Set the language name
		workSheet($"{columnLetter}{i}").Value = language.name
	Next x
Next i
$vbLabelText   $csharpLabel

对象引用和资源

您还可以在对象参考中找到非常有价值的[IronXL 类文档](/csharp/excel/object-reference/api/" target="_blank)。

此外,还有其他教程可能会阐明IronXL.Excel的其他方面,包括创建打开、写入、编辑、保存和导出 XLS、XLSX 和 CSV 文件,而无需使用Excel Interop

摘要

IronXL.Excel 是唯一的 .NET 软件库,用于读取各种各样的电子表格格式。 它不需要安装Microsoft Excel,并且不依赖于Interop。

如果您发现 .NET 库对于修改 Excel 文件很有用,您可能也会对使用 Google Sheets API 客户端库 来修改 Google Sheets 感兴趣。


教程快速访问

Brand Visual Studio related to 教程快速访问

Download this Tutorial as C# Source Code

The full free C# for Excel Source Code for this tutorial is available to download as a zipped Visual Studio 2017 project file.

Download

Explore this Tutorial on GitHub

The source code for this project is available in C# and VB.NET on GitHub.

Use this code as an easy way to get up and running in just a few minutes. The project is saved as a Microsoft Visual Studio 2017 project, but is compatible with any .NET IDE.

How to Read Excel File in C# on GitHub
Github Icon related to 教程快速访问
Documentation related to 教程快速访问

查看应用程序接口参考

探索 IronXL 的 API 参考,其中概述了 IronXL 的所有功能、命名空间、类、方法字段和枚举的详细信息。

查看应用程序接口参考
查克尼特·宾
软件工程师
Chaknith 负责 IronXL 和 IronBarcode 的工作。他在 C# 和 .NET 方面拥有深厚的专业知识,帮助改进软件并支持客户。他从用户互动中获得的洞察力,有助于提升产品、文档和整体体验。