使用XlsxWriter格式化Excel：区域设置未生效问题求助

问题背景 #

使用Python脚本处理CSV数据并导出Excel时，表头、前置零格式正常生效，但**VALOR（货币列）和VALIDADE_RMS、DT_ATUALIZACAO、PTU_LIMITE（日期列）**的格式设置后无效果，必须双击单元格回车才能显示正确格式。最初怀疑是虚拟机区域为美式英语导致，但实际核心问题是数据类型未正确转换。

问题根源 #

从CSV读取的日期和货币数据默认是字符串类型，而非Excel可识别的日期/数值类型。XlsxWriter的格式仅作用于对应数据类型的单元格，字符串即使设置了日期/货币格式也不会生效；手动双击时Excel会自动将字符串转为对应数据类型，格式才会正常显示。

解决方案 #

1. 转换日期列数据类型 #

读取CSV时直接解析日期列，或读取后将字符串转为datetime类型：

# 读取CSV时自动解析日期列（指定日期格式为DD/MM/YYYY）
csv_dataframe = pd.read_csv(
    report_csv_file,
    sep=',',
    encoding="ISO-8859-1",
    engine='python',
    index_col=None,
    names=['TIPO', 'CODIGO', 'PTU', 'DESCRICAO', 'FORNECEDOR', 'VALOR', 'COD_PRINCP_ATIVO', 'PRINCIPIO_ATIVO', 'ANVISA', 'VALIDADE_RMS', 'FABRICANTE', 'DT_ATUALIZACAO', 'PTU_LIMITE', 'COD_ESP', 'NOME_ESPEC', 'REFERENCIA', 'OBSERVACAO'],
    parse_dates=['VALIDADE_RMS', 'DT_ATUALIZACAO', 'PTU_LIMITE'],
    dayfirst=True
)

# 若读取时未解析，可后续批量转换
date_cols = ['VALIDADE_RMS', 'DT_ATUALIZACAO', 'PTU_LIMITE']
csv_dataframe[date_cols] = csv_dataframe[date_cols].apply(pd.to_datetime, dayfirst=True, errors='coerce')

2. 转换货币列数据类型 #

将VALOR列的字符串（可能包含R$或千分位分隔符）转为数值类型：

# 清理VALOR列的非数字字符，转为float类型
csv_dataframe['VALOR'] = csv_dataframe['VALOR'].replace(r'[R$\.]', '', regex=True)  # 移除R$和千分位点号
csv_dataframe['VALOR'] = csv_dataframe['VALOR'].replace(',', '.', regex=True)  # 替换逗号为小数分隔符
csv_dataframe['VALOR'] = pd.to_numeric(csv_dataframe['VALOR'], errors='coerce')

3. 优化XlsxWriter格式设置（可选） #

删除重复的表头写入逻辑，同时统一日期格式设置：

# 移除原代码中手动写入表头的循环（to_excel已通过header参数写入表头，重复写入会覆盖格式）
# for i, j in enumerate(list(csv_dataframe.columns)):
#     worksheet.write(0, i, j, header_format)

# 创建ExcelWriter时统一指定日期格式，增强格式优先级
writer = pd.ExcelWriter(template_excel_file, engine='xlsxwriter', date_format='DD/MM/YYYY', datetime_format='DD/MM/YYYY')

完整修正后代码 #

import os
import pandas as pd
import xlsxwriter

template_excel_file = r"C:\CriarTabelaOpme\Modelo Material Alto Custo - Intranet.xlsx"
depara_nome_espec_file = r"C:\CriarTabelaOpme\Especialidade_Dicionario.csv"
report_csv_file = r"C:\CriarTabelaOpme\ReportServiceIntranet.csv"

# 读取主CSV并解析日期列，转换货币列
csv_dataframe = pd.read_csv(
    report_csv_file,
    sep=',',
    encoding="ISO-8859-1",
    engine='python',
    index_col=None,
    names=['TIPO', 'CODIGO', 'PTU', 'DESCRICAO', 'FORNECEDOR', 'VALOR', 'COD_PRINCP_ATIVO', 'PRINCIPIO_ATIVO', 'ANVISA', 'VALIDADE_RMS', 'FABRICANTE', 'DT_ATUALIZACAO', 'PTU_LIMITE', 'COD_ESP', 'NOME_ESPEC', 'REFERENCIA', 'OBSERVACAO'],
    parse_dates=['VALIDADE_RMS', 'DT_ATUALIZACAO', 'PTU_LIMITE'],
    dayfirst=True
)

# 处理VALOR列：清理字符并转为数值
csv_dataframe['VALOR'] = csv_dataframe['VALOR'].replace(r'[R$\.]', '', regex=True)
csv_dataframe['VALOR'] = csv_dataframe['VALOR'].replace(',', '.', regex=True)
csv_dataframe['VALOR'] = pd.to_numeric(csv_dataframe['VALOR'], errors='coerce')

csv_dataframe.insert(16, "", "")

# 读取辅助CSV
csv_depara_espec = pd.read_csv(depara_nome_espec_file, sep=',', header=None, encoding="ISO-8859-1", engine='python')

# 调整列顺序
csv_dataframe = csv_dataframe.iloc[:, [0,1,2,3,4,5,6,7,8,9,10,11,12,13,16,14,15]]

# 填充NOME_ESPEC列
for row in range(len(csv_dataframe)):
    cod_esp_row = csv_dataframe.iloc[row, 13]
    csv_dataframe.iloc[row,14] = csv_depara_espec.iloc[cod_esp_row, 1]

# 创建ExcelWriter，指定日期格式
writer = pd.ExcelWriter(template_excel_file, engine='xlsxwriter', date_format='DD/MM/YYYY')

# 导出DataFrame到Excel
csv_dataframe.to_excel(
    writer,
    sheet_name='Material Alto Custo',
    index=False,
    header=['TIPO', 'CODIGO', 'PTU', 'DESCRICAO', 'FORNECEDOR', 'VALOR', 'COD_PRINCP_ATIVO', 'PRINCIPIO_ATIVO', 'ANVISA', 'VALIDADE_RMS', 'FABRICANTE', 'DT_ATUALIZACAO', 'PTU_LIMITE', 'COD_ESP', 'NOME_ESPEC', 'REFERENCIA', 'OBSERVACAO']
)

workbook = writer.book 
worksheet = writer.sheets['Material Alto Custo']

# 定义格式
header_format = workbook.add_format({'bold': True, 'font': 'Arial', 'size': 10})
font_and_size = workbook.add_format({'font': 'Arial', 'size': 10}) 
column_valor_format = workbook.add_format({'num_format': '[$R$-pt-BR] #.##0,00'}) 
column_date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'}) 
column_left_zeroes_format = workbook.add_format({'num_format': '00000000'})

# 应用格式
worksheet.set_row(0, None, header_format)
worksheet.set_column(0, csv_dataframe.shape[1]-1, 20.0, font_and_size)

worksheet.set_column(1, 1, 20.0, column_left_zeroes_format)
worksheet.set_column(2, 2, 20.0, column_left_zeroes_format)

worksheet.set_column(5, 5, 20.0, column_valor_format)

worksheet.set_column(9, 9, 20.0, column_date_format)
worksheet.set_column(11, 11, 20.0, column_date_format)
worksheet.set_column(12, 12, 20.0, column_date_format)

writer.close()

内容的提问来源于stack exchange，提问作者Leonardo Leipnitz