如何使用 IronXL 在 C# 中添加命名范围

Updated: 2026年6月4日

命名区域是指由唯一名称标识的指定单元格区域。你可以给一个区域指定一个名称，而不是通过单元格地址（例如 A1:B10）来引用它，这样在公式和函数中更容易引用和理解它。例如，如果您为一个范围命名为"SalesData"，可以在公式中引用它为SUM(SalesData)而不是直接指定单元格范围。

在使用 C# 中的 Excel 公式时，命名范围尤其有用，因为它们可以使您的代码更具可读性和可维护性。当结合IronXL强大的范围选择能力使用时，命名范围成为高效电子表格管理的必备工具。

快速入门：使用IronXL在一行中添加命名范围

using IronXL，只需调用一个方法即可定义一个命名范围。选择您的范围并应用SaveAsNamedRange方法。

1Install IronXL with NuGet Package Manager
PM > Install-Package IronXL.Excel
```
Install-Package IronXL.Excel
```

2复制并运行这段代码。

new IronXL.WorkBook()
    .DefaultWorkSheet["A1:B2"].SaveAsNamedRange("MyRange", true);

new IronXL.WorkBook()
    .DefaultWorkSheet["A1:B2"].SaveAsNamedRange("MyRange", true);

3部署到您的生产环境中进行测试

通过免费试用立即在您的项目中开始使用IronXL

最小工作流程（5 个步骤）

下载 C# 库以添加命名范围
选择目标区域workSheet["A1:A5"]
使用AddNamedRange方法添加命名范围
以多种方式检索命名范围
使用RemoveNamedRange方法可以轻松删除命名范围。

如何在工作表中添加命名范围？

IronXL.Excel 要添加命名范围，使用AddNamedRange方法，通过传递命名范围的名称和范围对象。此方法是IronXL.Excel全面工作表管理功能的一部分。

using IronXL;

WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Select range
var selectedRange = workSheet["A1:A5"];

// Add named range
workSheet.AddNamedRange("range1", selectedRange);

workBook.SaveAs("addNamedRange.xlsx");

using IronXL;

WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Select range
var selectedRange = workSheet["A1:A5"];

// Add named range
workSheet.AddNamedRange("range1", selectedRange);

workBook.SaveAs("addNamedRange.xlsx");

Imports IronXL

Private workBook As WorkBook = WorkBook.Create()
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Select range
Private selectedRange = workSheet("A1:A5")

' Add named range
workSheet.AddNamedRange("range1", selectedRange)

workBook.SaveAs("addNamedRange.xlsx")

Imports IronXL

Private workBook As WorkBook = WorkBook.Create()
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Select range
Private selectedRange = workSheet("A1:A5")

' Add named range
workSheet.AddNamedRange("range1", selectedRange)

workBook.SaveAs("addNamedRange.xlsx")

在创建命名范围时，必须遵循 Excel 的命名约定：

名称必须以字母或下划线开头
名称不能包含空格（使用下划线代替）
名称不能与单元格引用（如 "A1 "或 "R1C1"）冲突
名称不区分大小写，但保持大小写一致可提高可读性

Excel 名称框显示选中并高亮显示单元格 A1:A5 的 "range1"，红色箭头指向网格

还可以为非连续选区或整行/列创建命名范围。有关更复杂的范围操作，请查看合并多个 Excel 范围指南。

如何从我的工作簿中检索已命名的范围？

什么方法可以一次性获取所有已命名的范围？

GetNamedRanges方法将工作表中的所有命名范围作为字符串列表返回。当您需要审核或记录工作簿中的所有命名范围时，这一点尤其有用，类似于加载现有电子表格以分析其结构。

using IronXL;

WorkBook workBook = WorkBook.Load("addNamedRange.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Get all named range
var namedRangeList = workSheet.GetNamedRanges();

using IronXL;

WorkBook workBook = WorkBook.Load("addNamedRange.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Get all named range
var namedRangeList = workSheet.GetNamedRanges();

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("addNamedRange.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Get all named range
Private namedRangeList = workSheet.GetNamedRanges()

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("addNamedRange.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Get all named range
Private namedRangeList = workSheet.GetNamedRanges()

如何通过名称查找特定的命名范围？

使用Sheet1!$A$1:$A$5。然后可以使用地址公式来引用命名范围，或者选择与命名范围对应的范围。选择区域时，请注意工作表名称。

using IronXL;

WorkBook workBook = WorkBook.Load("addNamedRange.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Get named range address
string namedRangeAddress = workSheet.FindNamedRange("range1");

// Select range
var range = workSheet[$"{namedRangeAddress}"];

using IronXL;

WorkBook workBook = WorkBook.Load("addNamedRange.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Get named range address
string namedRangeAddress = workSheet.FindNamedRange("range1");

// Select range
var range = workSheet[$"{namedRangeAddress}"];

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("addNamedRange.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Get named range address
Private namedRangeAddress As String = workSheet.FindNamedRange("range1")

' Select range
Private range = workSheet($"{namedRangeAddress}")

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("addNamedRange.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Get named range address
Private namedRangeAddress As String = workSheet.FindNamedRange("range1")

' Select range
Private range = workSheet($"{namedRangeAddress}")

在多个工作表中使用命名范围

命名范围可以是工作簿级别，也可以是工作表级别。工作簿级别的名称可以从任何工作表中访问，而工作表级别的名称只能在其特定的工作表中访问。在管理 Excel 文件中的多个工作表时，这种区分非常重要。

// Example: Accessing named ranges from different worksheets
WorkBook workBook = WorkBook.Load("multisheet.xlsx");
WorkSheet sheet1 = workBook.GetWorkSheet("Sheet1");
WorkSheet sheet2 = workBook.GetWorkSheet("Sheet2");

// Both can access a workbook-level named range
var range1 = sheet1["GlobalData"];
var range2 = sheet2["GlobalData"];  // Same named range

// Example: Accessing named ranges from different worksheets
WorkBook workBook = WorkBook.Load("multisheet.xlsx");
WorkSheet sheet1 = workBook.GetWorkSheet("Sheet1");
WorkSheet sheet2 = workBook.GetWorkSheet("Sheet2");

// Both can access a workbook-level named range
var range1 = sheet1["GlobalData"];
var range2 = sheet2["GlobalData"];  // Same named range

Imports System

' Example: Accessing named ranges from different worksheets
Dim workBook As WorkBook = WorkBook.Load("multisheet.xlsx")
Dim sheet1 As WorkSheet = workBook.GetWorkSheet("Sheet1")
Dim sheet2 As WorkSheet = workBook.GetWorkSheet("Sheet2")

' Both can access a workbook-level named range
Dim range1 = sheet1("GlobalData")
Dim range2 = sheet2("GlobalData")  ' Same named range

Imports System

' Example: Accessing named ranges from different worksheets
Dim workBook As WorkBook = WorkBook.Load("multisheet.xlsx")
Dim sheet1 As WorkSheet = workBook.GetWorkSheet("Sheet1")
Dim sheet2 As WorkSheet = workBook.GetWorkSheet("Sheet2")

' Both can access a workbook-level named range
Dim range1 = sheet1("GlobalData")
Dim range2 = sheet2("GlobalData")  ' Same named range

如何从工作表中删除命名范围？

要移除命名范围，请使用RemoveNamedRange方法，通过传递命名范围的名称作为文本。这对于维护整洁、有序的工作簿以及在更新电子表格结构时防止命名冲突至关重要。

using IronXL;

WorkBook workBook = WorkBook.Load("addNamedRange.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Remove named range
workSheet.RemoveNamedRange("range1");

using IronXL;

WorkBook workBook = WorkBook.Load("addNamedRange.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Remove named range
workSheet.RemoveNamedRange("range1");

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("addNamedRange.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Remove named range
workSheet.RemoveNamedRange("range1")

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("addNamedRange.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Remove named range
workSheet.RemoveNamedRange("range1")

高级命名范围场景

使用公式命名范围

当与 Excel 公式一起使用时，命名范围才能真正发挥作用。它们使公式更易读、更易维护。有关全面的公式管理，请参阅C#中的Excel公式指南。

// Create named ranges for formula use
WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Create sample data
workSheet["A1:A5"].Value = new int[] { 10, 20, 30, 40, 50 };
workSheet["B1:B5"].Value = new int[] { 5, 10, 15, 20, 25 };

// Add named ranges
workSheet.AddNamedRange("FirstColumn", workSheet["A1:A5"]);
workSheet.AddNamedRange("SecondColumn", workSheet["B1:B5"]);

// Use named ranges in formulas
workSheet["D1"].Formula = "=SUM(FirstColumn)";
workSheet["D2"].Formula = "=AVERAGE(SecondColumn)";
workSheet["D3"].Formula = "=SUM(FirstColumn) + SUM(SecondColumn)";

// Evaluate formulas
workBook.EvaluateAll();

// Create named ranges for formula use
WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Create sample data
workSheet["A1:A5"].Value = new int[] { 10, 20, 30, 40, 50 };
workSheet["B1:B5"].Value = new int[] { 5, 10, 15, 20, 25 };

// Add named ranges
workSheet.AddNamedRange("FirstColumn", workSheet["A1:A5"]);
workSheet.AddNamedRange("SecondColumn", workSheet["B1:B5"]);

// Use named ranges in formulas
workSheet["D1"].Formula = "=SUM(FirstColumn)";
workSheet["D2"].Formula = "=AVERAGE(SecondColumn)";
workSheet["D3"].Formula = "=SUM(FirstColumn) + SUM(SecondColumn)";

// Evaluate formulas
workBook.EvaluateAll();

动态命名范围

IronXL.Excel 虽然 IronXL.Excel 并不直接支持 Excel 的动态命名范围（使用 OFFSET 或 INDEX 函数），但您可以根据数据变化以编程方式更新命名范围：

// Update named range based on data size
WorkBook workBook = WorkBook.Load("dynamicData.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Find last row with data
int lastRow = 1;
while (!workSheet[$"A{lastRow}"].IsEmpty)
{
    lastRow++;
}
lastRow--; // Adjust to actual last row

// Remove old range and add new one
workSheet.RemoveNamedRange("DataRange");
workSheet.AddNamedRange("DataRange", workSheet[$"A1:A{lastRow}"]);

// Update named range based on data size
WorkBook workBook = WorkBook.Load("dynamicData.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Find last row with data
int lastRow = 1;
while (!workSheet[$"A{lastRow}"].IsEmpty)
{
    lastRow++;
}
lastRow--; // Adjust to actual last row

// Remove old range and add new one
workSheet.RemoveNamedRange("DataRange");
workSheet.AddNamedRange("DataRange", workSheet[$"A1:A{lastRow}"]);

Imports IronXL

' Update named range based on data size
Dim workBook As WorkBook = WorkBook.Load("dynamicData.xlsx")
Dim workSheet As WorkSheet = workBook.DefaultWorkSheet

' Find last row with data
Dim lastRow As Integer = 1
While Not workSheet($"A{lastRow}").IsEmpty
    lastRow += 1
End While
lastRow -= 1 ' Adjust to actual last row

' Remove old range and add new one
workSheet.RemoveNamedRange("DataRange")
workSheet.AddNamedRange("DataRange", workSheet($"A1:A{lastRow}"))

Imports IronXL

' Update named range based on data size
Dim workBook As WorkBook = WorkBook.Load("dynamicData.xlsx")
Dim workSheet As WorkSheet = workBook.DefaultWorkSheet

' Find last row with data
Dim lastRow As Integer = 1
While Not workSheet($"A{lastRow}").IsEmpty
    lastRow += 1
End While
lastRow -= 1 ' Adjust to actual last row

' Remove old range and add new one
workSheet.RemoveNamedRange("DataRange")
workSheet.AddNamedRange("DataRange", workSheet($"A1:A{lastRow}"))

用于数据验证的命名范围

命名范围是创建数据验证列表和约束的绝佳工具。当与Excel 的数据验证功能相结合时，它们提供了一种确保数据完整性的强大方法：

// Create a named range for validation list
workSheet["F1:F5"].Value = new string[] { "Option1", "Option2", "Option3", "Option4", "Option5" };
workSheet.AddNamedRange("ValidationList", workSheet["F1:F5"]);

// Apply to data validation (conceptual example)
// The actual validation would reference "ValidationList"

// Create a named range for validation list
workSheet["F1:F5"].Value = new string[] { "Option1", "Option2", "Option3", "Option4", "Option5" };
workSheet.AddNamedRange("ValidationList", workSheet["F1:F5"]);

// Apply to data validation (conceptual example)
// The actual validation would reference "ValidationList"

' Create a named range for validation list
workSheet("F1:F5").Value = New String() {"Option1", "Option2", "Option3", "Option4", "Option5"}
workSheet.AddNamedRange("ValidationList", workSheet("F1:F5"))

' Apply to data validation (conceptual example)
' The actual validation would reference "ValidationList"

' Create a named range for validation list
workSheet("F1:F5").Value = New String() {"Option1", "Option2", "Option3", "Option4", "Option5"}
workSheet.AddNamedRange("ValidationList", workSheet("F1:F5"))

' Apply to data validation (conceptual example)
' The actual validation would reference "ValidationList"

最佳实践和性能提示

命名约定：使用描述性的、一致的名称以指示数据的用途（例如，Sales_Q1_2024而不是"Data1"） 2.范围管理：有意识地确定工作簿与工作表的范围，以避免冲突 3.文档：维护命名范围及其用途的列表，尤其是在复杂的工作簿中 4.性能：命名范围对性能的影响最小，但要避免创建数千个微小的命名范围 5.更新：当数据结构发生变化时，更新或删除相应的命名范围以保持准确性

有关更多高级 Excel 操作和性能优化，请浏览完整的 IronXL 文档，或查看特定功能，如对 Excel 范围进行排序或处理 Excel 表格。

常见问题解答

如何使用 C# 在 Excel 中创建命名范围？

要在 Excel 中使用 C# 和 IronXL 创建命名范围，请使用工作表索引器选择目标范围（例如，workSheet["A1:B2"]），然后调用 SaveAsNamedRange 方法，并输入所需的名称。您也可以使用 AddNamedRange 方法，将名称作为文本和范围对象传入。

Excel 命名范围的命名规则是什么？

using IronXL 创建命名范围时，请遵循这些 Excel 命名约定：名称必须以字母或下划线开头，不能包含空格（使用下划线代替），不能与单元格引用（如 "A1"）冲突，并且不区分大小写。创建命名范围时，IronXL 会自动执行这些规则。

能否以编程方式检索 Excel 工作簿中的所有命名范围？

是的，IronXL 提供了 GetNamedRanges 方法，该方法以字符串列表的形式返回工作表中的所有命名范围。该功能在审核或记录已加载 IronXL 的现有工作簿中的命名范围时特别有用。

命名范围如何改进我的 C# Excel 公式？

在使用 IronXL.Excel 时，命名范围使 Excel 公式更具可读性和可维护性。您可以在公式中使用 "SalesData "这样的描述性名称，而不是使用 "A1:B10 "这样的单元格引用，从而使您的 C# 代码更加清晰易懂。

是否可以为非连续单元格创建命名范围？

是的，IronXL 支持为非连续选区和整行或整列创建命名范围。该库的综合工作表管理功能允许您定义复杂的命名范围，而不是简单的矩形选择。

在一行代码中添加命名范围的最快方法是什么？

using IronXL，您可以在一行中创建一个命名范围，方法是：new IronXL.WorkBook().DefaultWorkSheet["A1:B2"].SaveAsNamedRange("MyRange", true)。这演示了 IronXL.Excel 简化的 Excel 操作 API。

如何删除 Excel 工作表中的命名范围？

IronXL 提供了 RemoveNamedRange 方法，可轻松删除工作表中的命名范围。这是该库全命名范围管理功能的一部分，可让您以编程方式添加、检索和删除命名范围。

Can dynamic named ranges be created with IronXL?

While IronXL doesn't support Excel's dynamic named ranges directly, you can update named ranges programmatically based on data changes to achieve similar functionality.

How are named ranges used for data validation?

Named ranges are perfect for data validation lists and constraints. When combined with Excel's data validation features, they ensure accurate and consistent data entry across your spreadsheet.

What are some best practices for using named ranges?

For effective use of named ranges, adhere to naming conventions, manage scope thoughtfully, document their purposes, and ensure updates reflect structural changes to maintain workbook accuracy.