如何读取 C# 中的 Excel 文件(开发者教程)
本教程解释了如何在C#中读取Excel文件,以及如何执行日常任务,如数据验证、数据库转换、Web API集成和公式修改。 本文引用了使用IronXL .NET Excel库的代码示例。
概述
How to Read Excel File in C#
- 下载用于读取Excel文件的C#库
- 加载并读取 Excel 文件(工作簿)
- 以 CSV 或 XLSX 格式创建 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文件的整体工作流程的总结:
-
安装 IronXL Excel 库。 我们可以通过使用我们的NuGet 包或下载.Net Excel DLL来实现这一点。
-
使用
WorkBook.Load
方法读取任何XLS、XLSX或CSV文档。 - 使用直观语法获取单元格值:
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)
本教程接下来部分中使用的代码示例(以及示例项目代码)将适用于三个示例 Excel 电子表格(请参见下方的视觉效果):
教程
1. 免费下载 IronXL C# 库
立即在您的项目中开始使用IronXL,并享受免费试用。
我们需要做的第一件事是安装IronXL.Excel
库,为 .NET 框架添加 Excel 功能。
安装IronXL.Excel
,最简单的方法是使用我们的NuGet包,不过您也可以选择手动将DLL安装到您的项目或全局程序集缓存中。
安装 IronXL NuGet 软件包
-
在 Visual Studio 中,右键单击项目选择“管理 NuGet 包...”。
-
搜索IronXL.Excel包并点击安装按钮以将其添加到项目中
另一种安装 IronXL 库的方法是使用 NuGet 包管理器控制台:
-
输入包管理器控制台
- 输入
> 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")
示例: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)
示例:ApiToExcelProcessor
注意:使用ExcelFileFormat.XLS
来支持旧版本的Microsoft Excel(95及更早版本)。
向Excel文档添加工作表
如前所述,IronXL WorkBook
包含一个或多个 WorkSheet
的集合。
要创建新的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")
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()
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)
读取和写入一系列单元格的值
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")
示例: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
示例: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
示例: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
上面的代码遍历电子表格中的每一行,并将单元格作为列表获取。每个验证方法检查单元格的值,如果值无效,则返回错误消息。
此代码创建一个新工作表,指定表头,并输出错误信息结果,以便记录无效数据。
: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")
4. 使用实体框架导出数据
使用IronXL将数据导出到数据库或将Excel电子表格转换为数据库。 ExcelToDB
示例读取包含各国GDP的电子表格,然后将该数据导出到SQLite。
它使用EntityFramework
来构建数据库,然后逐行导出数据。
添加 SQLite 实体框架 NuGet 包。
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
要使用不同的数据库,请安装相应的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
创建一个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
示例: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))()
示例:ApiToExcel
这是 API JSON 数据的样子。
以下代码会遍历所有国家,并在电子表格中设置名称、人口、地区、数字代码和前 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
对象引用和资源
您还可以在对象参考中找到非常有价值的[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 感兴趣。
教程快速访问
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.
DownloadExplore 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