C# 讀取 XLSX 檔案
當處理各種 Excel 格式時,通常需要讀取數據並以程式方式進行操作。在以下教程中,我們將學習如何使用方便的工具 IronXL 在 C# 中從 Excel 試算表讀取數據。
讀取 .XLSX 檔案 C#
第一步
1. 為您的專案獲取 IronXL
在您的專案中使用 IronXL,是在 C# 中處理 Excel 文件格式的簡單方法。您可以選擇 通過直接下載安裝 IronXL 或者,您可以使用 Visual Studio 的 NuGet 安裝該軟體的開發是免費的。
Install-Package IronXL.Excel
如何操作教程
2. 加載工作簿
WorkBook
是 IronXL 的一個類,該類的對象提供對 Excel 文件及其全部功能的完全訪問。例如,如果我們想訪問 Excel 文件,我們將使用以下代碼:
/**
Load Workbook
anchor-load-workbook
**/
WorkBook wb = WorkBook.Load("sample.xlsx");//Excel file path
/**
Load Workbook
anchor-load-workbook
**/
WorkBook wb = WorkBook.Load("sample.xlsx");//Excel file path
'''
'''Load Workbook
'''anchor-load-workbook
'''*
Dim wb As WorkBook = WorkBook.Load("sample.xlsx") 'Excel file path
在上面的代碼中,WorkBook.Load()
function加載
sample.xlsx到
wb。 透過訪問 Excel 文件的特定工作表,可以對
wb` 執行任何類型的函式。
3. 存取特定工作表
要存取 Excel 文件中的特定工作表,IronXL 提供了 WorkSheet
類別。它可以有幾種不同的使用方式:
/**
Access Sheet by Name
anchor-access-specific-worksheet
**/
WorkSheet ws = wb.GetWorkSheet("Sheet1"); //by sheet name
/**
Access Sheet by Name
anchor-access-specific-worksheet
**/
WorkSheet ws = wb.GetWorkSheet("Sheet1"); //by sheet name
'''
'''Access Sheet by Name
'''anchor-access-specific-worksheet
'''*
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1") 'by sheet name
wb
是在上述部分中宣告的工作簿。
/**
Access Sheet by Index
anchor-access-specific-worksheet
**/
WorkSheet ws = wb.WorkSheets [0]; //by sheet index
/**
Access Sheet by Index
anchor-access-specific-worksheet
**/
WorkSheet ws = wb.WorkSheets [0]; //by sheet index
'''
'''Access Sheet by Index
'''anchor-access-specific-worksheet
'''*
Dim ws As WorkSheet = wb.WorkSheets (0) 'by sheet index
或
WorkSheet ws = wb.DefaultWorkSheet; //for the default sheet:
WorkSheet ws = wb.DefaultWorkSheet; //for the default sheet:
Dim ws As WorkSheet = wb.DefaultWorkSheet 'for the default sheet:
或
WorkSheet ws = wb.WorkSheets.First();//for the first sheet:
WorkSheet ws = wb.WorkSheets.First();//for the first sheet:
Dim ws As WorkSheet = wb.WorkSheets.First() 'for the first sheet:
或
WorkSheet ws = wb.WorkSheets.FirstOrDefault();//for the first or default sheet:
WorkSheet ws = wb.WorkSheets.FirstOrDefault();//for the first or default sheet:
Dim ws As WorkSheet = wb.WorkSheets.FirstOrDefault() 'for the first or default sheet:
在獲取 ExcelSheet ws
後,您可以從中獲取任何類型的數據並執行所有 Excel 功能。
4. 從工作表中訪問數據
數據可以通過 Excel 工作表 ws
訪問:
string c = ws ["cell address"].ToString(); //for string
Int32 val = ws ["cell address"].Int32Value; //for integer
string c = ws ["cell address"].ToString(); //for string
Int32 val = ws ["cell address"].Int32Value; //for integer
Dim c As String = ws ("cell address").ToString() 'for string
Dim val As Int32 = ws ("cell address").Int32Value 'for integer
也可以從特定列的多個單元格中獲取數據。
foreach (var cell in ws ["A2:A10"])
{
Console.WriteLine("value is: {0}", cell.Text);
}
foreach (var cell in ws ["A2:A10"])
{
Console.WriteLine("value is: {0}", cell.Text);
}
For Each cell In ws ("A2:A10")
Console.WriteLine("value is: {0}", cell.Text)
Next cell
它將顯示從單元格 A2
到 A10
的值。
上述具體內容的完整代碼示例提供如下。
/**
Access WorkSheet Data
anchor-access-data-from-worksheet
**/
using IronXL;
static void Main(string [] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
foreach (var cell in ws ["A2:A10"])
{
Console.WriteLine("value is: {0}", cell.Text);
}
Console.ReadKey();
}
/**
Access WorkSheet Data
anchor-access-data-from-worksheet
**/
using IronXL;
static void Main(string [] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
foreach (var cell in ws ["A2:A10"])
{
Console.WriteLine("value is: {0}", cell.Text);
}
Console.ReadKey();
}
'''
'''Access WorkSheet Data
'''anchor-access-data-from-worksheet
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
For Each cell In ws ("A2:A10")
Console.WriteLine("value is: {0}", cell.Text)
Next cell
Console.ReadKey()
End Sub
它將顯示以下結果:
使用 Excel 文件 Sample.xlsx
:
我們可以看到,使用這些方法在您的專案中使用 Excel 文件數據是多麼輕而易舉。
5. 對資料執行函數
只需要使用以下代碼,就能夠非常簡單地通過應用和計等聚合函數來存取 Excel 工作表中的過濾數據:
decimal sum = ws ["From:To"].Sum();
decimal min = ws ["From:To"].Min();
decimal max = ws ["From:To"].Max();
decimal sum = ws ["From:To"].Sum();
decimal min = ws ["From:To"].Min();
decimal max = ws ["From:To"].Max();
Dim sum As Decimal = ws ("From:To").Sum()
Dim min As Decimal = ws ("From:To").Min()
Dim max As Decimal = ws ("From:To").Max()
如果您想了解更多詳情,請查看我們的詳細教程《How to》。 撰寫 C# Excel 檔案 包含聚合函數的具體資訊。
/**
Sum Min Max Functions
anchor-perform-functions-on-data
**/
using IronXL;
static void Main(string [] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
decimal sum = ws ["G2:G10"].Sum();
decimal min = ws ["G2:G10"].Min();
decimal max = ws ["G2:G10"].Max();
Console.WriteLine("Sum is: {0}", sum);
Console.WriteLine("Min is: {0}", min);
Console.WriteLine("Max is: {0}", max);
Console.ReadKey();
}
/**
Sum Min Max Functions
anchor-perform-functions-on-data
**/
using IronXL;
static void Main(string [] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
decimal sum = ws ["G2:G10"].Sum();
decimal min = ws ["G2:G10"].Min();
decimal max = ws ["G2:G10"].Max();
Console.WriteLine("Sum is: {0}", sum);
Console.WriteLine("Min is: {0}", min);
Console.WriteLine("Max is: {0}", max);
Console.ReadKey();
}
'''
'''Sum Min Max Functions
'''anchor-perform-functions-on-data
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
Dim sum As Decimal = ws ("G2:G10").Sum()
Dim min As Decimal = ws ("G2:G10").Min()
Dim max As Decimal = ws ("G2:G10").Max()
Console.WriteLine("Sum is: {0}", sum)
Console.WriteLine("Min is: {0}", min)
Console.WriteLine("Max is: {0}", max)
Console.ReadKey()
End Sub
此代碼將顯示以下輸出:
這就是 Excel 檔案 Sample.xlsx
的樣子:
6. 將 Excel 工作表讀取為 DataTable
使用 IronXL,操作 Excel 工作表如同使用 DataTable 一樣非常簡單。
DataTable dt=WorkSheet.ToDataTable();
DataTable dt=WorkSheet.ToDataTable();
Dim dt As DataTable=WorkSheet.ToDataTable()
如果我們想將 Excel 表的第一行用作 DataTable 的列名稱,那麼:
DataTable dt=WorkSheet.ToDataTable(True);
DataTable dt=WorkSheet.ToDataTable(True);
Dim dt As DataTable=WorkSheet.ToDataTable([True])
因此,ToDataTable
的布爾參數()將第一行設為資料表的欄位名稱。默認值為 False
。
/**
WorkSheet as DataTable
anchor-read-excel-worksheet-as-datatable
**/
using IronXL;
using System.Data;
static void Main(string [] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
DataTable dt = ws.ToDataTable(true);//parse sheet1 of sample.xlsx file into datatable
foreach (DataRow row in dt.Rows) //access rows
{
for (int i = 0; i < dt.Columns.Count; i++) //access columns of corresponding row
{
Console.Write(row [i]);
}
}
}
/**
WorkSheet as DataTable
anchor-read-excel-worksheet-as-datatable
**/
using IronXL;
using System.Data;
static void Main(string [] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
DataTable dt = ws.ToDataTable(true);//parse sheet1 of sample.xlsx file into datatable
foreach (DataRow row in dt.Rows) //access rows
{
for (int i = 0; i < dt.Columns.Count; i++) //access columns of corresponding row
{
Console.Write(row [i]);
}
}
}
'''
'''WorkSheet as DataTable
'''anchor-read-excel-worksheet-as-datatable
'''*
Imports IronXL
Imports System.Data
Shared Sub Main(ByVal args() As String)
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
Dim dt As DataTable = ws.ToDataTable(True) 'parse sheet1 of sample.xlsx file into datatable
For Each row As DataRow In dt.Rows 'access rows
For i As Integer = 0 To dt.Columns.Count - 1 'access columns of corresponding row
Console.Write(row (i))
Next i
Next row
End Sub
使用上述程式碼,可以根據需要訪問和使用工作表的每個單元格值。
7. 讀取 Excel 檔案作為 DataSet
IronXL 提供了一個非常簡單的功能,用以如何使用完整的 Excel 檔案。 (Workbook
) 作為 DataSet。
DataSet ds = WorkBook.ToDataSet();
DataSet ds = WorkBook.ToDataSet();
Dim ds As DataSet = WorkBook.ToDataSet()
WorkBook 是您的 Excel 文件,如下面的例子所示:
在本例中,我們將看到如何將 Excel 文件用作 DataSet。
/**
Excel File as DataSet
anchor-read-excel-file-as-dataset
**/
using IronXL;
using System.Data;
static void Main(string [] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
DataSet ds = wb.ToDataSet(); //Parse WorkBook wb into DataSet
foreach (DataTable dt in ds.Tables)
{
Console.WriteLine(dt.TableName);
}
}
/**
Excel File as DataSet
anchor-read-excel-file-as-dataset
**/
using IronXL;
using System.Data;
static void Main(string [] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
DataSet ds = wb.ToDataSet(); //Parse WorkBook wb into DataSet
foreach (DataTable dt in ds.Tables)
{
Console.WriteLine(dt.TableName);
}
}
'''
'''Excel File as DataSet
'''anchor-read-excel-file-as-dataset
'''*
Imports IronXL
Imports System.Data
Shared Sub Main(ByVal args() As String)
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ds As DataSet = wb.ToDataSet() 'Parse WorkBook wb into DataSet
For Each dt As DataTable In ds.Tables
Console.WriteLine(dt.TableName)
Next dt
End Sub
上述程式碼的輸出將如下所示:
Excel 檔案 Sample.xlsx
看起來會像這樣:
在上面的例子中,我們可以看到,我們可以輕鬆地將 Excel 檔案解析為 DataSet,並將每個 Excel 檔案的 WorkSheet 作為 DataTable 處理。深入了解如何 作為 DataSet 解析 Excel 在此展示代碼範例。
讓我們再看一個如何訪問所有 Excel 工作表的每個單元格值的例子。在這裡,我們可以訪問 Excel 文件每個工作表的每個單元格值。
/**
WorkSheet Cell Values
anchor-read-excel-file-as-dataset
**/
using IronXL;
using System.Data;
static void Main(string [] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
DataSet ds = wb.ToDataSet();//behave complete Excel file as DataSet
foreach (DataTable dt in ds.Tables)//behave Excel WorkSheet as DataTable.
{
foreach (DataRow row in dt.Rows)//corresponding Sheet's Rows
{
for (int i = 0; i < dt.Columns.Count; i++)//Sheet columns of corresponding row
{
Console.Write(row [i]);
}
}
}
}
/**
WorkSheet Cell Values
anchor-read-excel-file-as-dataset
**/
using IronXL;
using System.Data;
static void Main(string [] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
DataSet ds = wb.ToDataSet();//behave complete Excel file as DataSet
foreach (DataTable dt in ds.Tables)//behave Excel WorkSheet as DataTable.
{
foreach (DataRow row in dt.Rows)//corresponding Sheet's Rows
{
for (int i = 0; i < dt.Columns.Count; i++)//Sheet columns of corresponding row
{
Console.Write(row [i]);
}
}
}
}
'''
'''WorkSheet Cell Values
'''anchor-read-excel-file-as-dataset
'''*
Imports IronXL
Imports System.Data
Shared Sub Main(ByVal args() As String)
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ds As DataSet = wb.ToDataSet() 'behave complete Excel file as DataSet
For Each dt As DataTable In ds.Tables 'behave Excel WorkSheet as DataTable.
For Each row As DataRow In dt.Rows 'corresponding Sheet's Rows
For i As Integer = 0 To dt.Columns.Count - 1 'Sheet columns of corresponding row
Console.Write(row (i))
Next i
Next row
Next dt
End Sub
使用上述範例,可以非常方便地訪問每個 Excel 檔案的所有工作表中的每個儲存格值。
如需更多相關資訊,請參閱 無需使用互操作讀取Excel文件 在此查看程式碼。