如何在Delphi中覆盖系统列表分隔符适配Excel公式Evaluate

问题背景 #

在Delphi中做Excel自动化开发时，调用Evaluate执行公式时，Excel始终采用系统区域设置的列表分隔符，无视代码中对FormatSettings.ListSeparator的修改。即便显式修改了Delphi的FormatSettings，Excel仍不受影响，导致用逗号(,)作为参数分隔符的公式，在系统分隔符为分号(;)时执行失败。

复现代码 #

procedure TForm6.Button2Click(Sender: TObject);
var
  lExcel: TExcel;
  lstrResult: String;
  lnResult: Integer;
begin
  ShowMessage(FormatSettings.ListSeparator);

  FormatSettings.ListSeparator := ',';
  FormatSettings.ThousandSeparator := ',';
  FormatSettings.DecimalSeparator := '.';

  ShowMessage(FormatSettings.ListSeparator);

  lExcel := TExcel.GetInstance;
  lExcel.Start;
  lExcel.OpenFile('C:\Testing\test.xls');

  lstrResult := lExcel.V.Evaluate('=if(len(N6)>0,5,6)');
  //lstrResult := lExcel.V.Evaluate('=if(len(N6)=0,N6,"ABC")');

  lExcel.Shutdown;
end;

说明：TExcel是自定义Excel自动化包装类，通过CreateOleObject('Excel.Application')创建COM实例，V属性直接指向Excel的COM对象，可调用Evaluate等原生方法。

失败场景 #

当系统区域设置的列表分隔符为分号(;)时，执行=IF(LEN(N6)>0,5,6)这类用逗号分隔参数的公式，Evaluate会报错，因为Excel此时期望用分号作为参数分隔符。

解决方案 #

方案1：直接修改Excel实例的区域设置 #

Excel的COM对象自带区域配置属性，可以直接修改当前Excel实例的列表分隔符，无需依赖Delphi的FormatSettings：

// 在lExcel.Start之后添加以下代码
lExcel.V.International[xlListSeparator] := ',';

注：xlListSeparator是Excel内置常量，对应数值为5，若无法直接引用常量，可直接传入5替代。

方案2：动态适配Excel的当前分隔符 #

先获取Excel实例正在使用的列表分隔符，再替换公式中的逗号为该分隔符，适配性更强：

var
  excelListSeparator: string;
begin
  // Excel初始化完成后执行
  excelListSeparator := lExcel.V.International[xlListSeparator];
  // 替换公式中的逗号为Excel当前使用的分隔符
  lstrResult := lExcel.V.Evaluate(StringReplace('=if(len(N6)>0,5,6)', ',', excelListSeparator, [rfReplaceAll]));
end;

方案3：使用宏表函数Evaluate（强制英文分隔符） #

如果上述方法无效，可通过创建临时命名公式的方式调用宏表函数EVALUATE，这种方式强制使用英文逗号作为参数分隔符：

var
  namedRange: Variant;
begin
  // 创建临时命名公式
  namedRange := lExcel.V.ActiveWorkbook.Names.Add(Name:='TempEval', RefersTo:='=EVALUATE("IF(LEN(N6)>0,5,6)")');
  // 获取计算结果
  lstrResult := lExcel.V.Range('TempEval').Value;
  // 清理临时命名
  namedRange.Delete;
end;

注：部分Excel版本可能需要调整宏安全设置以允许宏表函数执行。

内容的提问来源于stack exchange，提问作者I'mSRJ