如何用C#的EPPlus在Excel中创建联动下拉列表？

实现联动下拉的核心思路是利用Excel的名称管理器+INDIRECT函数，结合EPPlus的数据验证功能来实现。以下是完整实现步骤和代码：

步骤说明 #

1. 创建数据存储工作表：专门用来存放国家和对应的地区数据，建议隐藏该工作表避免误操作。
2. 定义名称范围：为每个国家对应的地区列表创建一个名称，名称与国家名称一致，方便后续引用。
3. 主表添加国家下拉：在指定单元格添加国家选项的下拉列表。
4. 主表添加地区联动下拉：通过INDIRECT函数引用对应国家的名称范围，实现选项随国家选择自动过滤。

完整代码示例 #

using OfficeOpenXml;
using OfficeOpenXml.DataValidation;

// 初始化Excel包
using var package = new ExcelPackage();

// 1. 创建数据存储工作表（存放国家-地区数据）
var dataWs = package.Workbook.Worksheets.Add("CountryRegionData");
// 写入国家和地区数据（可根据实际需求扩展）
dataWs.Cells["A1"].Value = "US";
dataWs.Cells["A2"].Value = "CA";
dataWs.Cells["B1"].Value = "California";
dataWs.Cells["B2"].Value = "New York";
dataWs.Cells["C1"].Value = "Ontario";
dataWs.Cells["C2"].Value = "Quebec";

// 隐藏数据工作表
dataWs.Hidden = eWorkSheetHidden.Hidden;

// 2. 为每个国家的地区列表定义名称
// US对应的地区在B1:B2
var usRegionRange = dataWs.Cells["B1:B2"];
package.Workbook.Names.Add("US", usRegionRange);
// CA对应的地区在C1:C2
var caRegionRange = dataWs.Cells["C1:C2"];
package.Workbook.Names.Add("CA", caRegionRange);

// 3. 创建主工作表并添加国家下拉列表
var mainWs = package.Workbook.Worksheets.Add("Main");
mainWs.Cells["A1"].Value = "国家";
mainWs.Cells["B1"].Value = "地区";

// 国家下拉的范围：A2到A列所有行
var countryRange = ExcelRange.GetAddress(2, 1, ExcelPackage.MaxRows, 1);
var countryValidation = mainWs.DataValidations.AddListValidation(countryRange);
countryValidation.ShowErrorMessage = true;
countryValidation.Formula.Values.Add("US");
countryValidation.Formula.Values.Add("CA");

// 4. 添加地区联动下拉列表
// 地区下拉的范围：B2到B列所有行
var regionRange = ExcelRange.GetAddress(2, 2, ExcelPackage.MaxRows, 2);
var regionValidation = mainWs.DataValidations.AddListValidation(regionRange);
regionValidation.ShowErrorMessage = true;
// 使用INDIRECT函数引用对应国家的名称范围，$A2对应当前行的国家单元格
regionValidation.Formula.ExcelFormula = "INDIRECT($A2)";

// 保存Excel文件
package.SaveAs(new FileInfo(@"C:\Temp\联动下拉示例.xlsx"));

关键说明 #

• 名称管理器的作用：通过将每个国家的地区列表定义为名称，让Excel可以通过国家名称直接定位到对应的地区范围。
• INDIRECT函数的用法：INDIRECT($A2)会读取当前行A列的国家值，自动匹配对应的名称范围，从而实现下拉选项的动态过滤。
• 数据工作表隐藏：避免用户误修改联动的数据源，保证下拉列表的准确性。

内容的提问来源于stack exchange，提问作者user1447679