如何通过C#的GemBox Spreadsheet获取Excel自定义验证结果？

我在Excel中给某单元格设置了自定义数据验证，用C#的GemBox Spreadsheet设置该单元格的值后，需要验证这个值是否符合自定义验证规则。目前已经实现了查找单元格关联DataValidation对象的方法，也试过用临时单元格计算验证公式的方案（可行），但想找更优的实现方式，或者更合理的临时单元格选取逻辑。

现有代码 #

查找单元格关联的DataValidation对象

private DataValidation FindDatataValidationForCell(ExcelCell requiredCell)
{
    foreach (DataValidation dv in requiredCell.Worksheet.DataValidations)
    {
        foreach (CellRange range in dv.CellRanges)
        {
            foreach (ExcelCell foundCell in range)
            {
                if (foundCell == requiredCell)
                    return dv;
            }
        }
    }

    return null;
}

临时单元格验证方案

public bool IsValid(ExcelCell cell)
{
    DataValidation dv = FindDatataValidationForCell(cell);
    if (dv != null)
    {
        if (dv.Type == DataValidationType.Custom)
        {
            string str = dv.Formula1 as string;
            if (str != null && str.StartsWith("="))
            {
                // dodgy: use a cell which is known to be unused somewhere on the worksheet.                        
                var dummyCell = cell.Worksheet.Cells[100, 0];

                dummyCell.Formula = str;
                dummyCell.Calculate();                        
                bool res = dummyCell.BoolValue;
                dummyCell.Formula = null;   // no longer required. Reset. 

                return res; 
            }
        }
    }
    return true;
}

优化方案 #

1. 提升DataValidation查找效率

原方法遍历验证范围内的每个单元格进行匹配，数据量大时效率低下。可以利用CellRange.Contains方法直接判断单元格位置是否在验证范围内，无需遍历每个单元格：

private DataValidation FindDatataValidationForCell(ExcelCell requiredCell)
{
    foreach (DataValidation dv in requiredCell.Worksheet.DataValidations)
    {
        foreach (CellRange range in dv.CellRanges)
        {
            if (range.Contains(requiredCell.Position))
                return dv;
        }
    }
    return null;
}

2. 更安全的临时单元格选取

原方案固定使用第100行第1列的单元格，存在被业务数据占用的风险。可以采用以下两种更可靠的方式：

• 方式一：使用工作表末尾的单元格
  选取工作表最后一行的第一个单元格，这类位置通常不会被常规业务逻辑使用：

  public bool IsValid(ExcelCell cell)
  {
      DataValidation dv = FindDatataValidationForCell(cell);
      if (dv != null && dv.Type == DataValidationType.Custom)
      {
          string formula = dv.Formula1 as string;
          if (!string.IsNullOrEmpty(formula) && formula.StartsWith("="))
          {
              // 选取工作表最后一行的第一个单元格
              var dummyCell = cell.Worksheet.Cells[cell.Worksheet.Rows.Count - 1, 0];
              
              dummyCell.Formula = formula;
              dummyCell.Calculate();
              bool isValid = dummyCell.BoolValue;
              
              // 清理临时单元格，恢复初始状态
              dummyCell.Formula = null;
              dummyCell.Value = null;
  
              return isValid;
          }
      }
      return true;
  }
  

• 方式二：使用预先定义的命名单元格
  在工作表中提前创建一个名为ValidationTemp的空单元格，专门用于验证计算，完全避免冲突：

  public bool IsValid(ExcelCell cell)
  {
      DataValidation dv = FindDatataValidationForCell(cell);
      if (dv != null && dv.Type == DataValidationType.Custom)
      {
          string formula = dv.Formula1 as string;
          if (!string.IsNullOrEmpty(formula) && formula.StartsWith("="))
          {
              // 使用预先定义的命名单元格
              var dummyCell = cell.Worksheet.Cells["ValidationTemp"];
              
              dummyCell.Formula = formula;
              dummyCell.Calculate();
              bool isValid = dummyCell.BoolValue;
              
              dummyCell.Formula = null;
              dummyCell.Value = null;
  
              return isValid;
          }
      }
      return true;
  }
  

3. 独立临时工作表方案（完全隔离原数据）

如果担心临时单元格影响原工作表数据，可以创建一个独立的内存临时工作表，所有验证计算都在这个表中进行，完全不干扰业务数据：

// 类级别缓存临时工作表，避免重复创建
private static ExcelWorksheet _validationTempSheet = new ExcelFile().AddWorksheet("ValidationTemp");

public bool IsValid(ExcelCell cell)
{
    DataValidation dv = FindDatataValidationForCell(cell);
    if (dv != null && dv.Type == DataValidationType.Custom)
    {
        string formula = dv.Formula1 as string;
        if (!string.IsNullOrEmpty(formula) && formula.StartsWith("="))
        {
            var dummyCell = _validationTempSheet.Cells[0, 0];
            // 替换公式中的工作表引用为临时表
            string tempFormula = formula.Replace($"{cell.Worksheet.Name}!", $"{_validationTempSheet.Name}!");
            
            // 复制公式中引用的原单元格值到临时表（适用于引用其他单元格的验证公式）
            foreach (var formulaRef in dummyCell.FormulaReferences)
            {
                var originalCell = cell.Worksheet.Cells[formulaRef.Position];
                _validationTempSheet.Cells[formulaRef.Position].Value = originalCell.Value;
            }
            
            dummyCell.Formula = tempFormula;
            dummyCell.Calculate();
            bool isValid = dummyCell.BoolValue;
            
            // 清理临时表内容
            dummyCell.Formula = null;
            dummyCell.Value = null;
            foreach (var formulaRef in dummyCell.FormulaReferences)
            {
                _validationTempSheet.Cells[formulaRef.Position].Value = null;
            }

            return isValid;
        }
    }
    return true;
}

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