如何在C#中导入Excel文件

查克尼特·宾

2020年七月19日

更新 2024年十月20日

Translated

View the article in English

作为开发者，我们经常需要从Excel文件导入数据，并使用它来满足我们的应用程序和数据管理需求。不需要编写多行代码，IronXL 为我们提供了一种简单的方法，可以直接将我们需要的数据导入到 C# 项目中，然后以编程方式进行操作。

Import Excel Data C#

Import Data with the IronXL Library
Import Excel data in C#
Import data of specific cell range
Import Excel data with aggregate functions SUM, AVG, MIN, MAX, and more

How to Import Excel Files in C#

Download and install the C# library to import Excel files
Prepare the Excel files to be imported
Use the Load method to import spreadsheet
Edit the loaded Excel file using intuitive APIs
Export the edited Excel file in various types

Step 1

1. 使用 IronXL 库导入数据

使用本教程中将要使用的IronXL Excel库提供的功能导入数据。该软件可免费用于开发。

安装到您的通过 DLL 下载 C# 项目或导航使用 NuGet 软件包.

Install-Package IronXL.Excel

How to Tutorial

2. 访问项目工作表

为了我们今天的项目需求，我们将使用在第一步中安装的IronXL软件，将Excel数据导入到我们的C#应用程序中。

对于第二步，我们将通过使用 WorkBook.Load 在我们的CSharp项目中加载我们的Excel工作簿。()IronXL 的功能。我们在这个函数中将Excel工作簿的路径作为字符串参数传递，如下所示：

//load Excel file
WorkBook wb = WorkBook.Load("Path");

//load Excel file
WorkBook wb = WorkBook.Load("Path");

'load Excel file
Dim wb As WorkBook = WorkBook.Load("Path")

$vbLabelText $csharpLabel

指定路径的Excel文件将被加载到wb中。

接下来，我们需要访问Excel文件中的一个特定工作表，其数据将被导入到项目中。为此，我们可以使用GetWorkSheet()IronXL 的功能。我们将在此函数中传递工作簿中要导入哪个工作表的工作表名称字符串参数。

//specify sheet name of Excel WorkBook
WorkSheet ws = wb.GetWorkSheet("SheetName");

//specify sheet name of Excel WorkBook
WorkSheet ws = wb.GetWorkSheet("SheetName");

'specify sheet name of Excel WorkBook
Dim ws As WorkSheet = wb.GetWorkSheet("SheetName")

$vbLabelText $csharpLabel

WorkSheet将以ws形式导入，而wb是我们在上面的代码示例中定义的WorkBook。

还有以下其他方法可以将Excel工作表导入到项目中。

/**
Import WorkSheet 
anchor-access-worksheet-for-project
**/
//by sheet indexing
WorkBook.WorkSheets [SheetIndex];
//get default  WorkSheet
WorkBook.DefaultWorkSheet;
//get first WorkSheet
WorkBook.WorkSheets.First();
//for the first or default sheet:
WorkBook.WorkSheets.FirstOrDefault();

/**
Import WorkSheet 
anchor-access-worksheet-for-project
**/
//by sheet indexing
WorkBook.WorkSheets [SheetIndex];
//get default  WorkSheet
WorkBook.DefaultWorkSheet;
//get first WorkSheet
WorkBook.WorkSheets.First();
//for the first or default sheet:
WorkBook.WorkSheets.FirstOrDefault();

'''
'''Import WorkSheet 
'''anchor-access-worksheet-for-project
'''*
'by sheet indexing
WorkBook.WorkSheets (SheetIndex)
'get default  WorkSheet
WorkBook.DefaultWorkSheet
'get first WorkSheet
WorkBook.WorkSheets.First()
'for the first or default sheet:
WorkBook.WorkSheets.FirstOrDefault()

$vbLabelText $csharpLabel

现在，我们可以轻松地从指定的Excel文件中导入任何类型的数据。让我们看看在我们的项目中导入Excel文件数据时可以使用的所有可能的方面。

3. 在C#中导入Excel数据

这是在我们的项目中导入Excel文件数据的基本方面。

为此，我们可以使用单元格寻址系统来指定我们需要导入哪些单元格数据。它将返回Excel文件中特定单元格地址的值。

WorkSheet ["Cell Address"];

WorkSheet ["Cell Address"];

WorkSheet ("Cell Address")

$vbLabelText $csharpLabel

我们也可以通过使用行和列索引从Excel文件导入单元格数据。此行代码将返回指定行和列索引的值。

WorkSheet.Rows [RowIndex].Columns [ColumnIndex]

WorkSheet.Rows [RowIndex].Columns [ColumnIndex]

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'WorkSheet.Rows [RowIndex].Columns [ColumnIndex]

$vbLabelText $csharpLabel

如果要将导入的单元格值赋值到变量中，可以使用以下代码。

/**
Import Data by Cell Address
anchor-import-excel-data-in-c-num
**/
//by cell addressing
string val = WorkSheet ["Cell Address"].ToString();
//by row and column indexing
string val = WorkSheet.Rows [RowIndex].Columns [ColumnIndex].Value.ToString();

/**
Import Data by Cell Address
anchor-import-excel-data-in-c-num
**/
//by cell addressing
string val = WorkSheet ["Cell Address"].ToString();
//by row and column indexing
string val = WorkSheet.Rows [RowIndex].Columns [ColumnIndex].Value.ToString();

'''
'''Import Data by Cell Address
'''anchor-import-excel-data-in-c-num
'''*
'by cell addressing
Dim val As String = WorkSheet ("Cell Address").ToString()
'by row and column indexing
Dim val As String = WorkSheet.Rows (RowIndex).Columns (ColumnIndex).Value.ToString()

$vbLabelText $csharpLabel

在上述示例中，行和列索引从 0 开始。

4. 导入特定范围的Excel数据

如果我们想从Excel工作簿中导入特定范围的数据，可以通过使用range函数轻松完成。要定义范围，我们需要描述起始单元格和结束单元格地址。这样，它将返回位于指定范围内的所有单元格值。

WorkSheet ["starting Cell Address : Ending Cell Address"];

WorkSheet ["starting Cell Address : Ending Cell Address"];

WorkSheet ("starting Cell Address : Ending Cell Address")

$vbLabelText $csharpLabel

有关与Excel 文件中的范围查看所提供的代码示例。

/**
Import Data by Range
anchor-import-excel-data-of-specific-range
**/
using IronXL;
static void Main(string [] args)
{
    //import Excel WorkBook
    WorkBook wb = WorkBook.Load("sample.xlsx");
    //specify WorkSheet
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    //import data of specific cell
    string val = ws ["A4"].Value.ToString();
    Console.WriteLine("Import Value of A4 Cell address: {0}",val);
    Console.WriteLine("import Values in Range From B3 To B9 :\n");
    //import data in specific range
    foreach (var item in ws ["B3:B9"])
    {
        Console.WriteLine(item.Value.ToString());
    }

    Console.ReadKey();
}

/**
Import Data by Range
anchor-import-excel-data-of-specific-range
**/
using IronXL;
static void Main(string [] args)
{
    //import Excel WorkBook
    WorkBook wb = WorkBook.Load("sample.xlsx");
    //specify WorkSheet
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    //import data of specific cell
    string val = ws ["A4"].Value.ToString();
    Console.WriteLine("Import Value of A4 Cell address: {0}",val);
    Console.WriteLine("import Values in Range From B3 To B9 :\n");
    //import data in specific range
    foreach (var item in ws ["B3:B9"])
    {
        Console.WriteLine(item.Value.ToString());
    }

    Console.ReadKey();
}

'''
'''Import Data by Range
'''anchor-import-excel-data-of-specific-range
'''*
Imports Microsoft.VisualBasic
Imports IronXL
Shared Sub Main(ByVal args() As String)
	'import Excel WorkBook
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	'specify WorkSheet
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	'import data of specific cell
	Dim val As String = ws ("A4").Value.ToString()
	Console.WriteLine("Import Value of A4 Cell address: {0}",val)
	Console.WriteLine("import Values in Range From B3 To B9 :" & vbLf)
	'import data in specific range
	For Each item In ws ("B3:B9")
		Console.WriteLine(item.Value.ToString())
	Next item

	Console.ReadKey()
End Sub

$vbLabelText $csharpLabel

上述代码会显示以下输出结果：

Excel 文件 sample.xlsx 的值为

5. 通过聚合函数导入Excel数据

我们还可以对Excel文件应用聚合函数，并导入这些聚合函数的结果数据。这里有一些不同功能的示例以及如何使用它们。

Sum()`

 //to find the sum of specific cell range 
WorkSheet ["Starting Cell Address : Ending Cell Address"].Sum();

 //to find the sum of specific cell range 
WorkSheet ["Starting Cell Address : Ending Cell Address"].Sum();

'to find the sum of specific cell range 
WorkSheet ("Starting Cell Address : Ending Cell Address").Sum()

$vbLabelText $csharpLabel

平均值()`

 //to find the average of specific cell range 
WorkSheet ["Starting Cell Address : Ending Cell Address"].Avg()

 //to find the average of specific cell range 
WorkSheet ["Starting Cell Address : Ending Cell Address"].Avg()

'to find the average of specific cell range 
'INSTANT VB TODO TASK: The following line uses invalid syntax:
'WorkSheet ["Starting Cell Address : Ending Cell Address"].Avg()

$vbLabelText $csharpLabel

最小()`

 //to find the Min In specific cell range 
WorkSheet ["Starting Cell Address : Ending Cell Address"].Min()

 //to find the Min In specific cell range 
WorkSheet ["Starting Cell Address : Ending Cell Address"].Min()

'to find the Min In specific cell range 
'INSTANT VB TODO TASK: The following line uses invalid syntax:
'WorkSheet ["Starting Cell Address : Ending Cell Address"].Min()

$vbLabelText $csharpLabel

最大()`

 //to find the Max in specific cell range 
WorkSheet ["Starting Cell Address : Ending Cell Address"].Max()

 //to find the Max in specific cell range 
WorkSheet ["Starting Cell Address : Ending Cell Address"].Max()

'to find the Max in specific cell range 
'INSTANT VB TODO TASK: The following line uses invalid syntax:
'WorkSheet ["Starting Cell Address : Ending Cell Address"].Max()

$vbLabelText $csharpLabel

您可以阅读更多关于与Excel 中的 C# 聚合函数了解更多关于以不同方法提取数据的信息。

让我们看一个例子，说明如何通过应用这些功能导入Excel文件数据。

/**
Import Data by Aggregate Function
anchor-import-excel-data-by-aggregate-functions
**/
using IronXL;
static void Main(string [] args)
{
    //Import Excel file
    WorkBook wb = WorkBook.Load("sample.xlsx");
    //Specify WorkSheet
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    //Import Excel file data by applying aggregate functions
    decimal Sum = ws ["D2:D9"].Sum();
    decimal Avg = ws ["D2:D9"].Avg();
    decimal Min = ws ["D2:D9"].Min();
    decimal Max = ws ["D2:D9"].Max();
    Console.WriteLine("Sum From D2 To D9: {0}", Sum);
    Console.WriteLine("Avg From D2 To D9: {0}", Avg);
    Console.WriteLine("Min From D2 To D9: {0}", Min);
    Console.WriteLine("Max From D2 To D9: {0}", Max);
    Console.ReadKey();
}

/**
Import Data by Aggregate Function
anchor-import-excel-data-by-aggregate-functions
**/
using IronXL;
static void Main(string [] args)
{
    //Import Excel file
    WorkBook wb = WorkBook.Load("sample.xlsx");
    //Specify WorkSheet
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    //Import Excel file data by applying aggregate functions
    decimal Sum = ws ["D2:D9"].Sum();
    decimal Avg = ws ["D2:D9"].Avg();
    decimal Min = ws ["D2:D9"].Min();
    decimal Max = ws ["D2:D9"].Max();
    Console.WriteLine("Sum From D2 To D9: {0}", Sum);
    Console.WriteLine("Avg From D2 To D9: {0}", Avg);
    Console.WriteLine("Min From D2 To D9: {0}", Min);
    Console.WriteLine("Max From D2 To D9: {0}", Max);
    Console.ReadKey();
}

'''
'''Import Data by Aggregate Function
'''anchor-import-excel-data-by-aggregate-functions
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	'Import Excel file
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	'Specify WorkSheet
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	'Import Excel file data by applying aggregate functions
	Dim Sum As Decimal = ws ("D2:D9").Sum()
	Dim Avg As Decimal = ws ("D2:D9").Avg()
	Dim Min As Decimal = ws ("D2:D9").Min()
	Dim Max As Decimal = ws ("D2:D9").Max()
	Console.WriteLine("Sum From D2 To D9: {0}", Sum)
	Console.WriteLine("Avg From D2 To D9: {0}", Avg)
	Console.WriteLine("Min From D2 To D9: {0}", Min)
	Console.WriteLine("Max From D2 To D9: {0}", Max)
	Console.ReadKey()
End Sub

$vbLabelText $csharpLabel

上述代码的输出结果如下

我们的文件 sample.xlsx 将包含这些值：

6. 导入完整的 Excel 文件数据

如果我们想在我们的CSharp项目中导入完整的Excel文件数据，那么我们可以首先将加载的工作簿解析到一个数据集中。通过这种方式，完整的Excel数据将被导入到DataSet中，Excel文件上的WorkSheets将变成该DataSet的DataTables。以下是实际操作：

//import WorkBook into dataset
DataSet ds = WorkBook.ToDataSet();

//import WorkBook into dataset
DataSet ds = WorkBook.ToDataSet();

'import WorkBook into dataset
Dim ds As DataSet = WorkBook.ToDataSet()

$vbLabelText $csharpLabel

通过这种方式，我们指定的工作表将被导入到一个数据集中，我们可以根据我们的需求使用这个数据集。

通常，Excel文件的第一列用作列名。在这种情况下，我们需要将第一列设置为DataTable的ColumnName。为此，我们设置了 ToDataSet 的布尔参数。()IronXL 的功能如下：

ToDataSet(true);

ToDataSet(true);

ToDataSet(True)

$vbLabelText $csharpLabel

这将使 Excel 文件的第一列成为 DataTable 的列名。

让我们看一个完整的例子，如何将Excel数据导入到数据集中，并使用Excel工作表的第一列作为DataTable的列名：

/**
Import to Dataset
anchor-import-complete-excel-file-data
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    DataSet ds = new DataSet();
    ds = wb.ToDataSet(true);
    Console.WriteLine("Excel file data imported to dataset successfully.");
    Console.ReadKey();
}

/**
Import to Dataset
anchor-import-complete-excel-file-data
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    DataSet ds = new DataSet();
    ds = wb.ToDataSet(true);
    Console.WriteLine("Excel file data imported to dataset successfully.");
    Console.ReadKey();
}

'''
'''Import to Dataset
'''anchor-import-complete-excel-file-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 ds As New DataSet()
	ds = wb.ToDataSet(True)
	Console.WriteLine("Excel file data imported to dataset successfully.")
	Console.ReadKey()
End Sub

$vbLabelText $csharpLabel

与Excel 数据集和数据表函数可能比较复杂，但我们有更多的示例可用于将文件数据整合到您的 C# 项目中。