如何在 C# 中讀取 Excel 文件(開發者教程)
本教程解釋了如何在 C# 中讀取 Excel 文件,以及執行每日任務,如數據驗證、數據庫轉換、Web API 整合和公式修改。 本文提到了使用 IronXL .NET Excel 函式庫的程式碼範例。
概述
How to Read Excel File in C#
- 下載可讀取 Excel 文件的 C# 函式庫
- 載入並讀取 Excel 文件 (工作簿)
- 建立 CSV 或 XLSX 格式的 Excel 活頁簿
- 编辑單元格範圍內的單元格值
- 驗證試算表數據
- 使用 Entity Framework 匯出數據
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
庫,以將 Excel 功能添加到 .NET framework。
安裝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. 使用 Entity Framework 匯出資料
使用 IronXL 將數據導出到數據庫或將 Excel 電子表格轉換為數據庫。 ExcelToDB
範例讀取包含各國 GDP 的試算表,然後將該數據匯出到 SQLite。
它使用EntityFramework
來構建資料庫,然後逐行匯出資料。
添加 SQLite Entity Framework 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
的 "List"。 然後可以輕鬆地遍歷每個國家,並將 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 數據的樣子。
以下程式碼遍歷國家,並在試算表中設定名稱、人口數、地區、數字代碼以及前三名語言。
: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 Client Library for .NET 感興趣,這可以讓您修改 Google 試算表。
快速指南
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