使用XlsxWriter设置num_format为0!.0,后Excel显示为0!!.0的问题

使用XlsxWriter设置数字格式为0!.0,时，用Office Excel打开生成的文件，格式会变为0!!.0，逗号消失且多出一个感叹号。

问题原因 #

XlsxWriter在处理包含!和逗号的格式字符串时，会错误地将逗号转义为!，导致格式变形。这里的逗号是Excel格式中用于将数字除以1000的缩放标记，需要正确传递给Excel才能生效。

解决方案 #

在格式字符串中对逗号进行转义，使用\前缀，将格式改为0!.0\,，让XlsxWriter正确识别并传递该格式给Excel。

修改后的代码 #

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
ws.set_column('B:B', 30)

num=1234.52

num_formats = (
   '0.00',
   '#,##0.00',
   '0.00E+00',
   '##0.0E+0',
   '₹#,##0.00',
    '0!.0\\,'  # 对逗号进行转义
)
ws.write('A1', 'Formatted Number')
ws.write('B1', 'Format')

row = 1
for fmt in num_formats:
   format = wb.add_format({'num_format': fmt})
   ws.write_number(row, 0, num, format)
   # 还原显示原始格式字符串
   display_fmt = fmt.replace('\\,', ',')
   ws.write_string(row, 1, display_fmt)
   row += 1
wb.close()

说明 #

修改后的格式0!.0\,会被XlsxWriter正确传递给Excel，打开文件后格式将显示为0!.0,，数字会按预期显示为1.2（1234.52除以1000后保留一位小数）。代码中对B列显示的格式字符串做了还原处理，确保展示的是原始格式而非转义后的字符串。

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