PAYATHON 2026

Italian locale 导致 Excel Formulas 失效,如何解决?

排障赵师傅

结论

通过 Ruby 的 WIN32OLE 调用 Excel COM 时,先将 OLE 调用使用的 LCID 固定为 en-US,再通过 Range.Formula 写入使用英文函数名和固定格式的公式:

WIN32OLE.locale = 0x0409 # en-US

设置后,Excel 会按照英文公式语法解析 SUM、ISOWEEKNUM 等函数,不会受到 Italian locale 下函数名、参数分隔符和小数分隔符差异的影响。

FormulaLocal 是正式 API,但调用方需要生成与当前 Excel 界面语言相匹配的公式。如果程序需要支持多种 locale,固定 LCID 并使用 Formula 通常更便于维护。

为什么会出现 #NOME? 和 #NAME?

Excel 公式常用的接口有两种:

  • Formula 使用 Excel 的英文公式语法。
  • FormulaLocal 使用当前 Excel 用户界面的本地化语法。

在 Italian locale 下,函数名可能会被本地化,参数分隔符和小数分隔符也可能不同。例如,英文公式通常写成:

=SUM(A1,B1)
=1.2/3.0

本地语法则可能使用其他函数名,以分号分隔参数,并用逗号表示小数点。

出现错误的原因是 COM 调用携带了 locale 信息。当 WIN32OLE 使用 Italian LCID,而代码传入英文公式时,Excel 可能按本地语法解析公式字符串。这样一来,SUM、ISOWEEKNUM 等内容会被视为无法识别的名称,最终返回 #NOME?。

同一个文件在英文 Excel 中重新打开后,错误会显示为 #NAME?,变化的只是错误信息的语言。公式在保存时已经被错误解析,重新打开文件不会自动修复。

推荐修改方式

创建 Excel.Application 前,将 WIN32OLE.locale 设置为 0x0409,即 en-US:

# frozen_string_literal: false

require 'win32ole'

EN_US_LCID = 0x0409
previous_locale = WIN32OLE.locale

application = nil
workbook = nil
worksheet = nil

begin
  # Make Excel COM parse Formula strings using English formula syntax.
  WIN32OLE.locale = EN_US_LCID

  application = WIN32OLE.new('Excel.Application')
  application.Visible = true

  workbook = application.Workbooks.Add
  worksheet = workbook.Worksheets(1)

  worksheet.Range('B2').Formula = '=ISOWEEKNUM(B1)'
  worksheet.Range('D2').Formula = '=SUM($D$1:$E$1)'
  worksheet.Range('D3').Formula = '=(SUM($D$1:$E$1) * 1.2)/3.0'

  worksheet.Range('D2:D3').NumberFormat = '0.00'

  output_path = File.join(
    File.dirname(File.expand_path(__FILE__)),
    'output.xlsx'
  ).tr('/', '\\')

  previous_alerts = application.DisplayAlerts

  begin
    application.DisplayAlerts = false
    workbook.SaveAs(output_path, 51) # xlOpenXMLWorkbook
  ensure
    application.DisplayAlerts = previous_alerts
  end

  puts "Saved Excel file to: #{output_path}"
ensure
  begin
    workbook.Close(0) if workbook
  rescue WIN32OLE::RuntimeError => e
    warn "Error closing workbook: #{e.message}"
  end

  begin
    application.Quit if application
  rescue WIN32OLE::RuntimeError => e
    warn "Error quitting Excel: #{e.message}"
  end

  worksheet = nil
  workbook = nil
  application = nil

  WIN32OLE.locale = previous_locale
  GC.start
end

设置 LCID 后,传给 Formula 的内容要统一使用英文公式语法:

worksheet.Range('A1').Formula = '=SUM(B1,C1)'
worksheet.Range('A2').Formula = '=IF(B2>0,1.5,0.5)'

具体要求如下:

  • 函数名使用英文,如 SUM、IF、ISOWEEKNUM。
  • 函数参数用逗号分隔。
  • 数字使用 . 作为小数点。
  • 单元格地址采用 Formula 要求的 A1 引用格式。

如果代码更适合生成 R1C1 引用,可以改用 FormulaR1C1,但仍需先固定 WIN32OLE.locale。

日期也应避免使用本地化字符串

示例中将日期作为字符串写入:

worksheet.Cells(1, index + 2).Value = '2024-09-16'

Excel 能否将其识别为日期,可能受区域设置影响。即使公式名称已经可以正确解析,B1 如果仍是文本,ISOWEEKNUM(B1) 也可能返回其他错误。

更可靠的方式是传入日期值,并单独设置显示格式:

dates = [
  Time.local(2024, 9, 16),
  Time.local(2024, 9, 22)
]

dates.each_with_index do |date, index|
  worksheet.Cells(1, index + 2).Value = date
end

worksheet.Range('B1:C1').NumberFormat = 'yyyy-mm-dd'
worksheet.Range('B2').Formula = '=ISOWEEKNUM(B1)'

数值也应以 Ruby 数值的形式传给 COM,不要预先转换为 "1.35" 这样的字符串:

worksheet.Range('D1').Value = 1.35
worksheet.Range('E1').Value = 2.65

是否应该使用 FormulaLocal

FormulaLocal 可以用于生产环境,但调用方需要处理当前 Excel 语言带来的各种差异,包括:

  • 本地化函数名;
  • 参数列表分隔符;
  • 小数分隔符;
  • 语言包和 Excel 版本可能存在的差异。

如果直接写死一套意大利语公式,同一个程序在英文 Excel 上仍会失效。只有在程序面向单一语言环境,或已经具备完整公式本地化层的情况下,使用 FormulaLocal 才更合适。

对于跨 locale 的 COM 自动化,可以将 WIN32OLE.locale 设为 0x0409,通过 Formula 统一写入英文公式。完成操作后还要恢复原来的 LCID,尤其是在同一个 Ruby 进程还会调用其他依赖 locale 的 COM 组件时。

备注:内容仅供参考。