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 组件时。
备注:内容仅供参考。
