如何使用 C# 將 Excel 檔案匯入 DataTable？

您可以使用 IronXL 輕鬆地將 Excel 檔案匯入 C# 中的 DataTable。IronXL 提供簡單的 API 去載入 Excel 檔案並將其轉換為 DataTable 以供進一步操作。

using IronXL 操作 Excel 檔案在 C# 中有哪些好處？

IronXL 在 C# 中提供了一個強大的解決方案，用於操作 Excel 檔案，包括讀取、寫入和將 Excel 資料轉換為不同格式，如 DataTables。它支援所有主要的 Excel 檔案型別，提供卓越的性能和易於使用的特點。

IronXL 是否與 .NET Core 相容？

是的，IronXL 完全相容於 .NET Core，能夠支援需要 Excel 檔案操作的應用程式進行跨平台開發與部署。

我可以使用 IronXL 讀取不同格式的 Excel 檔案嗎？

IronXL 支持讀取多種格式的 Excel 檔案，包括 XLSX、XLS、CSV 和 TSV，提供了靈活性以處理不同型別的 Excel 資料。

IronXL 是否支持 Excel 檔案的安全功能？

是的，IronXL 支持帶有密碼保護的 Excel 檔案，允許您在 C# 應用程式中載入和操作安全的 Excel 文件。

我如何使用 IronXL 將 Excel 檔案轉換為 DataTable？

using IronXL，您可以透過載入工作簿、選擇工作表，然後使用內建的方法導出工作表資料到 DataTable 來轉換 Excel 檔案。

哪些型別的應用可以從使用 IronXL 中受益？

任何需要 Excel 資料操作的應用程式都適合使用 IronXL，比如資料分析工具、報告系統或任何需要匯入、匯出或修改 Excel 資料的軟體。

IronXL 可以用來在將 Excel 檔案匯入 DataTable 後進行編輯嗎？

可以，IronXL 允許您甚至在將 Excel 檔案匯入 DataTable 後進行編輯與更新，提供修改和保存變更的無縫體驗。

using IronXL 需要什麼樣的編碼經驗水平？

IronXL 專為易於使用而設計，擁有友好的 API，可以供各個經驗水平的開發者使用，使其對初學者和高級使用者均具可用性。

Where can you access more examples and documentation for using IronXL?

You can access more examples and detailed documentation on using IronXL by visiting the Iron Software website and viewing their resources and product documentation.

C# Excel 程式庫

Explore IronXL C# .NET 的 Excel 試算表程式庫開始免費試用

無需 Interop 即可讀取 Excel 檔案

查看所有程式碼範例

using IronXL;
using System;
using System.Linq;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select a cell and return the converted 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);
}

// 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);

using IronXL;
using System;
using System.Linq;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select a cell and return the converted 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);
}

// 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);

PM >Install-Package IronXL.Excel

Install-Package IronXL.Excel

USING IRONXL

using IronXL 將 Excel 檔案匯入 DataTable

Curtis Chau

Updated: 2026年5月8日

C# 將 Excel 文件匯入至 DataTable 使用 IronXL

開發者的一天中，手動解析 Excel 文件很少是讓人開心的事。我們都曾在這種情況下，試圖處理舊版 OLEDB 連接字串或試圖將無結構的試算表資料映射為您的 .NET 應用程式實際理解的內容。

C# 中的 DataTable 是一個強大的工具。它充當完美的橋樑，將那些行和列轉化為熟悉的結構，隨時準備進行 LINQ 查詢、DataGrid 綁定或快速導入至 SQL 資料庫。

IronXL 減輕了此過程的負擔。它使用一個簡單、直觀的 ToDataTable 方法來處理轉換，而不是複雜的配置。無論您是在構建報告工具還是資料遷移腳本，這是您輕鬆匯入 Excel 資料的方法。

想要跟著做嗎？使用 IronXL 免費試用版親自嘗試這個強大的程式庫。

How Can You Import an Excel File into a DataTable in C#?

要在 C# 中將 Excel 文件匯入 DataTable，請使用 WorkBook.Load 載入工作簿，存取目標工作表，然後調用 ToDataTable 方法。此方法適用於 XLSX 文件、XLS、XLSM 和 CSV 格式。請確保參考以下命名空間以存取所需的類。

通過 Visual Studio 中的 NuGet 套件管理器輸入以下命令來安裝 IronXL：

PM > Install-Package IronXL.Excel

Install-Package IronXL.Excel

然後，使用以下簡潔的程式碼片段來移動您的資料：

using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        // Load the Excel file from the specified path
        string filePath = "sales-data.xlsx";
        WorkBook workbook = WorkBook.Load(filePath);
        // Access the first worksheet in the workbook
        WorkSheet worksheet = workbook.DefaultWorkSheet;
        // Convert worksheet to DataTable with first row as column headers
        DataTable dt = worksheet.ToDataTable(true);
        // Display the imported data
        foreach (DataRow row in dt.Rows)
        {
            Console.WriteLine(string.Join(" | ", row.ItemArray));
        }
    }
}

using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        // Load the Excel file from the specified path
        string filePath = "sales-data.xlsx";
        WorkBook workbook = WorkBook.Load(filePath);
        // Access the first worksheet in the workbook
        WorkSheet worksheet = workbook.DefaultWorkSheet;
        // Convert worksheet to DataTable with first row as column headers
        DataTable dt = worksheet.ToDataTable(true);
        // Display the imported data
        foreach (DataRow row in dt.Rows)
        {
            Console.WriteLine(string.Join(" | ", row.ItemArray));
        }
    }
}

Imports IronXL
Imports System
Imports System.Data

Module Program
    Sub Main(args As String())
        ' Load the Excel file from the specified path
        Dim filePath As String = "sales-data.xlsx"
        Dim workbook As WorkBook = WorkBook.Load(filePath)
        ' Access the first worksheet in the workbook
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
        ' Convert worksheet to DataTable with first row as column headers
        Dim dt As DataTable = worksheet.ToDataTable(True)
        ' Display the imported data
        For Each row As DataRow In dt.Rows
            Console.WriteLine(String.Join(" | ", row.ItemArray))
        Next
    End Sub
End Module

Imports IronXL
Imports System
Imports System.Data

Module Program
    Sub Main(args As String())
        ' Load the Excel file from the specified path
        Dim filePath As String = "sales-data.xlsx"
        Dim workbook As WorkBook = WorkBook.Load(filePath)
        ' Access the first worksheet in the workbook
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
        ' Convert worksheet to DataTable with first row as column headers
        Dim dt As DataTable = worksheet.ToDataTable(True)
        ' Display the imported data
        For Each row As DataRow In dt.Rows
            Console.WriteLine(String.Join(" | ", row.ItemArray))
        Next
    End Sub
End Module

輸出

將 Excel 文件匯入 DataTable 使用 IronXL：圖 1 - Excel 文件匯入到 DataTable

WorkBook.Load 方法從字串 filePath 位置讀取 Excel 文件，並建立一個包含所有 Excel 表格資料的工作簿物件。 ToDataTable(true) 參數表示第一行包含列標題，這些標題會自動用作 DataTable 列名稱。

讀取 Excel 文件資料到 DataTable 的最佳方法是什麼？

有效讀取 Excel 文件內容需要處理標題行和原始資料情況。 IronXL 的 ToDataTable 方法接受一個布林參數，該參數確定第一行是作為列名稱還是作為資料處理。請注意，處理擴展屬性或轉換複雜的單元格型別可能需要額外的配置，但預設方法能夠健壯地處理大多數情況。

using IronXL;
using System.Data;
// Load workbook from file path
string filePath = @"C:\Data\inventory.xlsx";
WorkBook workbook = WorkBook.Load(filePath);
WorkSheet worksheet = workbook.WorkSheets[0];
// First row as headers (true) or as data (false)
DataTable dataTableWithHeaders = worksheet.ToDataTable(true);
DataTable dataTableWithoutHeaders = worksheet.ToDataTable(false);
// Process each row in the DataTable
foreach (DataRow row in dataTableWithHeaders.Rows)
{
    for (int i = 0; i < dataTableWithHeaders.Columns.Count; i++)
    {
        object value = row[i];
        Console.Write($"{value}\t");
    }
    Console.WriteLine();
}

using IronXL;
using System.Data;
// Load workbook from file path
string filePath = @"C:\Data\inventory.xlsx";
WorkBook workbook = WorkBook.Load(filePath);
WorkSheet worksheet = workbook.WorkSheets[0];
// First row as headers (true) or as data (false)
DataTable dataTableWithHeaders = worksheet.ToDataTable(true);
DataTable dataTableWithoutHeaders = worksheet.ToDataTable(false);
// Process each row in the DataTable
foreach (DataRow row in dataTableWithHeaders.Rows)
{
    for (int i = 0; i < dataTableWithHeaders.Columns.Count; i++)
    {
        object value = row[i];
        Console.Write($"{value}\t");
    }
    Console.WriteLine();
}

Imports IronXL
Imports System.Data

' Load workbook from file path
Dim filePath As String = "C:\Data\inventory.xlsx"
Dim workbook As WorkBook = WorkBook.Load(filePath)
Dim worksheet As WorkSheet = workbook.WorkSheets(0)

' First row as headers (true) or as data (false)
Dim dataTableWithHeaders As DataTable = worksheet.ToDataTable(True)
Dim dataTableWithoutHeaders As DataTable = worksheet.ToDataTable(False)

' Process each row in the DataTable
For Each row As DataRow In dataTableWithHeaders.Rows
    For i As Integer = 0 To dataTableWithHeaders.Columns.Count - 1
        Dim value As Object = row(i)
        Console.Write($"{value}" & vbTab)
    Next
    Console.WriteLine()
Next

Imports IronXL
Imports System.Data

' Load workbook from file path
Dim filePath As String = "C:\Data\inventory.xlsx"
Dim workbook As WorkBook = WorkBook.Load(filePath)
Dim worksheet As WorkSheet = workbook.WorkSheets(0)

' First row as headers (true) or as data (false)
Dim dataTableWithHeaders As DataTable = worksheet.ToDataTable(True)
Dim dataTableWithoutHeaders As DataTable = worksheet.ToDataTable(False)

' Process each row in the DataTable
For Each row As DataRow In dataTableWithHeaders.Rows
    For i As Integer = 0 To dataTableWithHeaders.Columns.Count - 1
        Dim value As Object = row(i)
        Console.Write($"{value}" & vbTab)
    Next
    Console.WriteLine()
Next

輸出

將 Excel 文件匯入 DataTable 使用 IronXL：圖 2 - 匯入和讀取 Excel 文件到 DataTable 的輸出

這種靈活性使得 IronXL 非常適合處理無論其結構如何的 Excel 試算表文件。與 EPPlus 套件或傳統方法需要新的 OleDbDataAdapter 實例不同，IronXL 在一個方法調用中處理轉換。該程式庫支持 .NET Core、.NET Framework 和跨平台部署，而不需要安裝 Microsoft Excel。

如何將多個 Excel 工作表轉換為 DataSet？

當您的 Excel 工作簿包含多個工作表時，轉換為 DataSet 可保留每個 Excel 工作表作為集合中的單獨 DataTable。這種方法對於報告應用程式和多工作表資料處理至關重要。

using IronXL;
using System;
using System.Data;
// Load multi-sheet workbook
WorkBook workbook = WorkBook.Load("quarterly-reports.xlsx");
// Convert entire workbook to DataSet (each sheet becomes a DataTable)
DataSet dataset = workbook.ToDataSet();
// Iterate through all tables in the DataSet
foreach (DataTable table in dataset.Tables)
{
    Console.WriteLine($"Sheet: {table.TableName}");
    Console.WriteLine($"Rows: {table.Rows.Count}, Columns: {table.Columns.Count}");
    foreach (DataRow row in table.Rows)
    {
        Console.WriteLine(string.Join(", ", row.ItemArray));
    }
}

using IronXL;
using System;
using System.Data;
// Load multi-sheet workbook
WorkBook workbook = WorkBook.Load("quarterly-reports.xlsx");
// Convert entire workbook to DataSet (each sheet becomes a DataTable)
DataSet dataset = workbook.ToDataSet();
// Iterate through all tables in the DataSet
foreach (DataTable table in dataset.Tables)
{
    Console.WriteLine($"Sheet: {table.TableName}");
    Console.WriteLine($"Rows: {table.Rows.Count}, Columns: {table.Columns.Count}");
    foreach (DataRow row in table.Rows)
    {
        Console.WriteLine(string.Join(", ", row.ItemArray));
    }
}

Imports IronXL
Imports System
Imports System.Data

' Load multi-sheet workbook
Dim workbook As WorkBook = WorkBook.Load("quarterly-reports.xlsx")
' Convert entire workbook to DataSet (each sheet becomes a DataTable)
Dim dataset As DataSet = workbook.ToDataSet()
' Iterate through all tables in the DataSet
For Each table As DataTable In dataset.Tables
    Console.WriteLine($"Sheet: {table.TableName}")
    Console.WriteLine($"Rows: {table.Rows.Count}, Columns: {table.Columns.Count}")
    For Each row As DataRow In table.Rows
        Console.WriteLine(String.Join(", ", row.ItemArray))
    Next
Next

Imports IronXL
Imports System
Imports System.Data

' Load multi-sheet workbook
Dim workbook As WorkBook = WorkBook.Load("quarterly-reports.xlsx")
' Convert entire workbook to DataSet (each sheet becomes a DataTable)
Dim dataset As DataSet = workbook.ToDataSet()
' Iterate through all tables in the DataSet
For Each table As DataTable In dataset.Tables
    Console.WriteLine($"Sheet: {table.TableName}")
    Console.WriteLine($"Rows: {table.Rows.Count}, Columns: {table.Columns.Count}")
    For Each row As DataRow In table.Rows
        Console.WriteLine(String.Join(", ", row.ItemArray))
    Next
Next

輸出

將 Excel 文件匯入 DataTable 使用 IronXL：圖 3 - 匯入多工作表 Excel 文件到 DataSet 的輸出

ToDataSet 方法建立一個包含每個工作表的 DataTable 物件的新 DataSet。每個表的 TableName 屬性反映了原始 Excel 表名，使得在處理期間能夠輕鬆引用特定資料。

如何將 Excel 中的資料匯入資料庫？

一個常見的工作流程涉及讀取 Excel 文件資料並將其插入到資料庫系統中。 DataTable 結構與 ADO.NET 的資料庫操作無縫整合。

using IronXL;
using System.Data;
using System.Data.SqlClient;
string filePath = @"C:\imports\customer-data.xlsx";
WorkBook workbook = WorkBook.Load(filePath);
WorkSheet worksheet = workbook.DefaultWorkSheet;
// Convert Excel data to DataTable
DataTable dt = worksheet.ToDataTable(true);
// Validate data before database insert
if (dt == null || dt.Rows.Count == 0)
{
    Console.WriteLine("No data to import");
    return;
}
// Insert into database using SqlBulkCopy
string connectionString = "Server=.;Database=Sales;Trusted_Connection=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "Customers";
        bulkCopy.WriteToServer(dt);
    }
}
Console.WriteLine($"Imported {dt.Rows.Count} records successfully");

using IronXL;
using System.Data;
using System.Data.SqlClient;
string filePath = @"C:\imports\customer-data.xlsx";
WorkBook workbook = WorkBook.Load(filePath);
WorkSheet worksheet = workbook.DefaultWorkSheet;
// Convert Excel data to DataTable
DataTable dt = worksheet.ToDataTable(true);
// Validate data before database insert
if (dt == null || dt.Rows.Count == 0)
{
    Console.WriteLine("No data to import");
    return;
}
// Insert into database using SqlBulkCopy
string connectionString = "Server=.;Database=Sales;Trusted_Connection=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "Customers";
        bulkCopy.WriteToServer(dt);
    }
}
Console.WriteLine($"Imported {dt.Rows.Count} records successfully");

Imports IronXL
Imports System.Data
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        Dim filePath As String = "C:\imports\customer-data.xlsx"
        Dim workbook As WorkBook = WorkBook.Load(filePath)
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
        ' Convert Excel data to DataTable
        Dim dt As DataTable = worksheet.ToDataTable(True)
        ' Validate data before database insert
        If dt Is Nothing OrElse dt.Rows.Count = 0 Then
            Console.WriteLine("No data to import")
            Return
        End If
        ' Insert into database using SqlBulkCopy
        Dim connectionString As String = "Server=.;Database=Sales;Trusted_Connection=True;"
        Using connection As New SqlConnection(connectionString)
            connection.Open()
            Using bulkCopy As New SqlBulkCopy(connection)
                bulkCopy.DestinationTableName = "Customers"
                bulkCopy.WriteToServer(dt)
            End Using
        End Using
        Console.WriteLine($"Imported {dt.Rows.Count} records successfully")
    End Sub
End Module

Imports IronXL
Imports System.Data
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        Dim filePath As String = "C:\imports\customer-data.xlsx"
        Dim workbook As WorkBook = WorkBook.Load(filePath)
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
        ' Convert Excel data to DataTable
        Dim dt As DataTable = worksheet.ToDataTable(True)
        ' Validate data before database insert
        If dt Is Nothing OrElse dt.Rows.Count = 0 Then
            Console.WriteLine("No data to import")
            Return
        End If
        ' Insert into database using SqlBulkCopy
        Dim connectionString As String = "Server=.;Database=Sales;Trusted_Connection=True;"
        Using connection As New SqlConnection(connectionString)
            connection.Open()
            Using bulkCopy As New SqlBulkCopy(connection)
                bulkCopy.DestinationTableName = "Customers"
                bulkCopy.WriteToServer(dt)
            End Using
        End Using
        Console.WriteLine($"Imported {dt.Rows.Count} records successfully")
    End Sub
End Module

此程式碼示範了如何載入 Excel 文件，將其轉換為 DataTable，並執行批量的資料庫插入。空值檢查確保在嘗試匯入操作之前的資料完整性。

如何將 DataTable 匯出回 Excel？

IronXL 支援雙向資料流，允許您從 DataTable 物件建立 Excel 文件，適用於報告和資料匯出場景。

using IronXL;
using System.Data;
// Create sample DataTable
DataTable dt = new DataTable("Products");
dt.Columns.Add("ProductID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Price", typeof(decimal));
dt.Rows.Add(1, "Widget", 29.99m);
dt.Rows.Add(2, "Gadget", 49.99m);
// Create new workbook and load DataTable
WorkBook workbook = WorkBook.Create();
workbook.LoadWorkSheetsFromDataSet(new DataSet { Tables = { dt } });
// Save as Excel file
workbook.SaveAs("exported-products.xlsx");

using IronXL;
using System.Data;
// Create sample DataTable
DataTable dt = new DataTable("Products");
dt.Columns.Add("ProductID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Price", typeof(decimal));
dt.Rows.Add(1, "Widget", 29.99m);
dt.Rows.Add(2, "Gadget", 49.99m);
// Create new workbook and load DataTable
WorkBook workbook = WorkBook.Create();
workbook.LoadWorkSheetsFromDataSet(new DataSet { Tables = { dt } });
// Save as Excel file
workbook.SaveAs("exported-products.xlsx");

Imports IronXL
Imports System.Data

' Create sample DataTable
Dim dt As New DataTable("Products")
dt.Columns.Add("ProductID", GetType(Integer))
dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("Price", GetType(Decimal))
dt.Rows.Add(1, "Widget", 29.99D)
dt.Rows.Add(2, "Gadget", 49.99D)

' Create new workbook and load DataTable
Dim workbook As WorkBook = WorkBook.Create()
workbook.LoadWorkSheetsFromDataSet(New DataSet With {.Tables = {dt}})

' Save as Excel file
workbook.SaveAs("exported-products.xlsx")

Imports IronXL
Imports System.Data

' Create sample DataTable
Dim dt As New DataTable("Products")
dt.Columns.Add("ProductID", GetType(Integer))
dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("Price", GetType(Decimal))
dt.Rows.Add(1, "Widget", 29.99D)
dt.Rows.Add(2, "Gadget", 49.99D)

' Create new workbook and load DataTable
Dim workbook As WorkBook = WorkBook.Create()
workbook.LoadWorkSheetsFromDataSet(New DataSet With {.Tables = {dt}})

' Save as Excel file
workbook.SaveAs("exported-products.xlsx")