C＃讀取XLSX檔案

Translated

View the article in English

在處理各種 Excel 格式時，通常需要讀取數據並以程式方式操作它。在下面的教程中，我們將學習如何使用一個方便的工具IronXL，在C#中讀取Excel試算表中的數據。

讀取 .XLSX 檔案 C#

為您的項目獲取IronXL
載入工作簿
從工作表存取數據
應用功能如總和、最小值和最大值
將工作表讀取為數據表、數據集等

第一步

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

VB C#

在上面的代碼中，WorkBook.Load()function在wb中載入sample.xlsx。可以通過訪問 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

VB C#

wb 是在上面部分中宣告的 WorkBook。

或

/**
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

VB C#

或

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:

VB C#

或

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:

VB C#

或

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:

VB C#

在獲取 ExcelSheet ws 後，您可以從中獲取任何類型的數據並執行所有 Excel 功能。

4. 從工作表存取資料

在此過程中，可以從ExcelSheet 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

VB C#

也可以從特定列的多個單元格中獲取數據。

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

VB C#

它將顯示從單元格 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

VB C#

它將顯示以下結果：

使用 Excel 文件 Sample.xlsx :

我們可以看到，使用這些方法在您的專案中使用 Excel 文件數據是多麼輕而易舉。

5. 對數據執行功能

通过以下代码应用聚合函数（如 Sum、Min 或 Max）来访问 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()

VB C#

如果您想了解更多詳情，請查看我們的詳細教程《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

VB C#

此代碼將顯示以下輸出：

這就是 Excel 檔案 Sample.xlsx 的樣子：

6. 將 Excel 工作表讀取為 DataTable

使用 IronXL，將 Excel 工作表作為 DataTable 操作非常容易。

DataTable dt=WorkSheet.ToDataTable();

DataTable dt=WorkSheet.ToDataTable();

Dim dt As DataTable=WorkSheet.ToDataTable()

VB C#

如果我們想將 Excel 表的第一行用作 DataTable 的列名稱，那麼：

DataTable dt=WorkSheet.ToDataTable(True);

DataTable dt=WorkSheet.ToDataTable(True);

Dim dt As DataTable=WorkSheet.ToDataTable([True])

VB C#

因此，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

VB C#

使用上述程式碼，可以根據需要訪問和使用工作表的每個單元格值。

7. 將 Excel 檔案讀取為 DataSet

IronXL 提供了一個非常簡單的功能，說明如何使用完整的 Excel 文件。(Workbook)作為 DataSet。

DataSet ds = WorkBook.ToDataSet();

DataSet ds = WorkBook.ToDataSet();

Dim ds As DataSet = WorkBook.ToDataSet()

VB C#

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

VB C#

上述程式碼的輸出將如下所示：

Excel 檔案 Sample.xlsx 看起來會像這樣：

在上述範例中，我們可以看到，將Excel文件解析成DataSet並將每個Excel文件的工作表作為一個DataTable來處理是非常容易的。深入了解如何作為 DataSet 解析 Excel這裡展示了代碼示例。

讓我們再看一個例子，說明如何訪問所有ExcelSheets的每個單元格的值。在這裡，我們可以訪問 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

VB C#

使用上述示例，可以非常方便地訪問Excel文件每個工作表的每個單元格的值。

如需了解更多如何無需使用互操作讀取Excel文件在此查看程式碼。