C#操作Excel：实现下拉选项对应ID写入指定列

实现逻辑 #

1. 借助VeryHidden工作表（命名为Types）存储两组数据：A列存MarketID，B列存对应下拉显示的Market名称
2. 在数据工作表的指定列（如B列）设置下拉验证，数据源指向Types的B列
3. 在关联ID列（如A列）配置公式，通过下拉选中的文本反向匹配对应的ID并自动填充

C#代码实现 #

1. 写入VeryHidden工作表数据 #

先把数据库返回的ID和选项数据写入Types工作表，并设置为VeryHidden状态：

// 获取或创建Types工作表
var typesSheet = workbook.Worksheets.FirstOrDefault(w => w.Name == "Types") ?? workbook.Worksheets.Add("Types");
int rowIndex = 1;
foreach (var market in markets)
{
    // A列存储MarketID
    typesSheet.Cells[rowIndex, 1].Value = market.Id;
    // B列存储下拉显示的Market名称
    typesSheet.Cells[rowIndex, 2].Value = market.Name;
    rowIndex++;
}
// 将工作表设为VeryHidden
typesSheet.Visibility = WorksheetVisibility.VeryHidden;

2. 配置下拉验证与ID列公式 #

在数据工作表中设置下拉列表，并给ID列添加匹配公式：

var dataSheet = workbook.Worksheets["数据工作表"]; // 替换为你的数据工作表名称
int dataRowCount = markets.Count > 0 ? markets.Count : 1;

// 给B3:B60003区域设置下拉数据验证
using (var dropdownRange = dataSheet.Cells["B3:B60003"])
{
    var listValidation = dropdownRange.DataValidation.AddListDataValidation();
    listValidation.ShowInputMessage = true;
    listValidation.InputTitle = "选择市场";
    listValidation.InputMessage = "请从下拉列表中选择";
    listValidation.ShowErrorMessage = true;
    listValidation.ErrorStyle = DataValidation.ErrorStyle.Stop;
    listValidation.ErrorTitle = "无效选择";
    listValidation.ErrorMessage = "仅允许选择列表中的选项";
    // 下拉数据源指向Types工作表的B列
    listValidation.Formula.ExcelFormula = $"Types!$B$1:$B${dataRowCount}";
}

// 给A3:A60003区域设置公式，自动匹配对应MarketID
using (var idRange = dataSheet.Cells["A3:A60003"])
{
    // 使用INDEX+MATCH组合公式，比VLOOKUP更灵活，避免反向查找问题
    idRange.Formula = $"=INDEX(Types!$A$1:$A${dataRowCount},MATCH(B3,Types!$B$1:$B${dataRowCount},0))";
}

注意事项 #

• 确保Types工作表中B列的下拉选项无重复值，否则MATCH会返回第一个匹配项的ID
• 若需防止Types工作表被误修改，可添加工作表保护
• 公式中的数据范围需与实际写入的行数一致，避免出现#N/A错误

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