使用OfficeOpenXml(.NET/C#)生成Excel数据验证规则遇故障求助

问题根源 #

手动确认数据验证规则后生效，说明公式逻辑本身正确，但EPPlus逐行生成数据验证时，可能存在公式引用格式不规范、Excel未自动初始化验证规则的问题。以下是针对性修复方案：

修复方案 #

1. 改用整列批量添加数据验证（推荐） #

逐行添加容易导致Excel解析异常，直接给目标列的连续区域设置验证，Excel会自动处理相对引用，避免逐行生成的格式问题：

columnRule = "Vendor";
indexColumnRule = columnsIndex.Where(x => x.Key.Equals(columnRule, StringComparison.OrdinalIgnoreCase)).FirstOrDefault().Value;
letterColumnRule = ExcelCellAddress.GetColumnLetter(indexColumnRule);

// 批量设置边框
var targetRange = ws.Cells[$"{letterColumnRule}2:{letterColumnRule}1000"];
var border = targetRange.Style.Border;
border.Top.Style = ExcelBorderStyle.Thin;
border.Bottom.Style = ExcelBorderStyle.Thin;
border.Left.Style = ExcelBorderStyle.Thin;
border.Right.Style = ExcelBorderStyle.Thin;

// 批量添加数据验证
var validationList = ws.DataValidations.AddListValidation(targetRange.Address);
// 使用相对引用，Excel会自动适配每行的Vendor type单元格
validationList.Formula.ExcelFormula = $"=INDIRECT({letterColumnRuleVendorType}2)";
// 若Vendor type列是Vendor列的前一列，也可使用R1C1相对引用更可靠：
// validationList.Formula.R1C1Formula = "=INDIRECT(RC[-1])";
validationList.ShowErrorMessage = true;
validationList.ErrorTitle = "Errore";
validationList.Error = "Select a valid Vendor.";
validationList.AllowBlank = true;

2. 调整公式赋值方式 #

部分EPPlus版本中Formula.ExcelFormula的解析存在兼容问题，改用Formula1直接赋值：

// 替换原代码中的公式行
validationList.Formula1 = $"INDIRECT({letterColumnRuleVendorType}{row})";

3. 确保命名范围与Vendor type值匹配 #

• 确认Vendor type列的选项文本与对应命名范围的名称完全一致（Excel的INDIRECT对命名范围大小写敏感）
• 若命名范围是工作表级，需在公式中添加工作表前缀：

  validationList.Formula.ExcelFormula = $"=INDIRECT(\"Vendors!\" & {letterColumnRuleVendorType}{row})";
  

4. 升级EPPlus版本 #

旧版EPPlus（如4.x）存在数据验证的兼容性bug，升级到最新稳定版（5.x/6.x）可解决多数格式解析问题。

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