XlsxWriter表头及日期类型列格式设置失败问题

问题描述 #

将DataFrame导出为格式化Excel表格时遇到两个异常：

• J、L、M三列（对应DataFrame索引9、11、12的日期类型列）无完整边框，但同是通过XlsxWriter设置格式的B、C、F列显示正常；
• 出现额外的R列被错误格式化。

原代码如下：

# Importar bibliotecas
import os
from typing import Self
import pandas as pd
import pandas.io.formats.excel
import pandas.io.excel
import numpy as np
import time
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_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'])
csv_dataframe.insert(16, "", "")

csv_dataframe["VALOR"] = csv_dataframe["VALOR"].apply(lambda x: x.replace(",", "")).astype('float')

csv_dataframe["VALIDADE_RMS"] = pd.to_datetime(csv_dataframe["VALIDADE_RMS"])
csv_dataframe["DT_ATUALIZACAO"] = pd.to_datetime(csv_dataframe["DT_ATUALIZACAO"])
csv_dataframe["PTU_LIMITE"] = pd.to_datetime(csv_dataframe["PTU_LIMITE"])

#print(csv_dataframe.dtypes)

csv_depara_espec = pd.read_csv(depara_nome_espec_file, sep = ',', header = None, encoding = "ISO-8859-1", engine = 'python')
#print(csv_depara_espec)

csv_dataframe = csv_dataframe.iloc[:, [0,1,2,3,4,5,6,7,8,9,10,11,12,13,16,14,15]]
#print(csv_dataframe)

dict = {'TIPO' : 'TIPO', 'CODIGO' : 'CODIGO', 'PTU': 'PTU', 
'DESCRICAO' : 'DESCRICAO', 'FORNECEDOR' : 'FORNECEDOR', 'VALOR' : 'VALOR', 'COD_PRINCP_ATIVO' : 'COD_PRINCP_ATIVO',
 'PRINCIPIO_ATIVO' : 'PRINCIPIO_ATIVO', 'ANVISA' : 'ANVISA', 'VALIDADE_RMS' : 'VALIDADE_RMS', 'FABRICANTE' : 'FABRICANTE',
  'DT_ATUALIZACAO' : 'DT_ATUALIZACAO', 'PTU_LIMITE' : 'PTU_LIMITE', 'COD_ESP' : 'COD_ESP', '' : 'NOME_ESPEC', 
  'NOME_ESPEC' : 'REFERENCIA', 'REFERENCIA' : 'OBSERVACAO'}
csv_dataframe.rename(columns = dict, inplace = True)


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]

pandas.io.formats.excel.header_style = None

writer = pd.ExcelWriter(template_excel_file, engine = 'xlsxwriter', date_format = 'dd/mm/yyyy', datetime_format = 'dd/mm/yyyy')

excel_dataframe = 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'])

(max_row, max_col) = csv_dataframe.shape

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

header_format = workbook.add_format({'bold' : True, 
'font' : 'Arial', 
'size' : 10,
'border' : 1})

font_size_and_border = workbook.add_format({'font' : 'Arial', 'size' : 10, 'border' : 1})
column_valor_format_and_border = workbook.add_format({'num_format': '[$R$-pt-BR] #,##0.00','font' : 'Arial', 'size' : 10,  'border' : 1})
column_date_format_and_border = workbook.add_format({'num_format' : 'dd/mm/yyyy','font' : 'Arial', 'size' : 10,  'border' : 1})
column_left_zeroes_format_and_border = workbook.add_format({'num_format' : '00000000','font' : 'Arial', 'size' : 10,  'border' : 1})

worksheet.set_row(0, None, header_format)

worksheet.set_column(0,max_col, 20.0, font_size_and_border)

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

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

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

worksheet.set_row(0, None, header_format)

writer.close()

解决方案 #

问题根源在于两处代码逻辑错误，修改后即可解决：

1. 列范围设置错误导致多余列格式化 #

csv_dataframe.shape返回的max_col是列的数量，而XlsxWriter的列索引从0开始，因此最后一列的索引应为max_col - 1。原代码中worksheet.set_column(0,max_col, ...)会将超出DataFrame范围的列（对应Excel的R列）也应用格式，修正后即可避免多余列被格式化。

2. 日期格式被Pandas默认设置覆盖 #

在pd.ExcelWriter中设置date_format和datetime_format后，Pandas会为日期类型单元格自动应用无边框的默认格式，覆盖自定义的带边框日期格式。移除这两个参数，完全使用自定义格式即可恢复边框显示。

修改后的完整代码 #

# Importar bibliotecas
import os
from typing import Self
import pandas as pd
import pandas.io.formats.excel
import pandas.io.excel
import numpy as np
import time
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_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'])
csv_dataframe.insert(16, "", "")

csv_dataframe["VALOR"] = csv_dataframe["VALOR"].apply(lambda x: x.replace(",", "")).astype('float')

csv_dataframe["VALIDADE_RMS"] = pd.to_datetime(csv_dataframe["VALIDADE_RMS"])
csv_dataframe["DT_ATUALIZACAO"] = pd.to_datetime(csv_dataframe["DT_ATUALIZACAO"])
csv_dataframe["PTU_LIMITE"] = pd.to_datetime(csv_dataframe["PTU_LIMITE"])

#print(csv_dataframe.dtypes)

csv_depara_espec = pd.read_csv(depara_nome_espec_file, sep = ',', header = None, encoding = "ISO-8859-1", engine = 'python')
#print(csv_depara_espec)

csv_dataframe = csv_dataframe.iloc[:, [0,1,2,3,4,5,6,7,8,9,10,11,12,13,16,14,15]]
#print(csv_dataframe)

dict = {'TIPO' : 'TIPO', 'CODIGO' : 'CODIGO', 'PTU': 'PTU', 
'DESCRICAO' : 'DESCRICAO', 'FORNECEDOR' : 'FORNECEDOR', 'VALOR' : 'VALOR', 'COD_PRINCP_ATIVO' : 'COD_PRINCP_ATIVO',
 'PRINCIPIO_ATIVO' : 'PRINCIPIO_ATIVO', 'ANVISA' : 'ANVISA', 'VALIDADE_RMS' : 'VALIDADE_RMS', 'FABRICANTE' : 'FABRICANTE',
  'DT_ATUALIZACAO' : 'DT_ATUALIZACAO', 'PTU_LIMITE' : 'PTU_LIMITE', 'COD_ESP' : 'COD_ESP', '' : 'NOME_ESPEC', 
  'NOME_ESPEC' : 'REFERENCIA', 'REFERENCIA' : 'OBSERVACAO'}
csv_dataframe.rename(columns = dict, inplace = True)


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]

pandas.io.formats.excel.header_style = None

# 移除Writer层面的日期格式参数，避免覆盖自定义格式
writer = pd.ExcelWriter(template_excel_file, engine = 'xlsxwriter')

excel_dataframe = 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'])

(max_row, max_col) = csv_dataframe.shape

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

header_format = workbook.add_format({'bold' : True, 
'font' : 'Arial', 
'size' : 10,
'border' : 1})

font_size_and_border = workbook.add_format({'font' : 'Arial', 'size' : 10, 'border' : 1})
column_valor_format_and_border = workbook.add_format({'num_format': '[$R$-pt-BR] #,##0.00','font' : 'Arial', 'size' : 10,  'border' : 1})
column_date_format_and_border = workbook.add_format({'num_format' : 'dd/mm/yyyy','font' : 'Arial', 'size' : 10,  'border' : 1})
column_left_zeroes_format_and_border = workbook.add_format({'num_format' : '00000000','font' : 'Arial', 'size' : 10,  'border' : 1})

worksheet.set_row(0, None, header_format)

# 修正列范围，使用max_col -1作为结束索引
worksheet.set_column(0, max_col - 1, 20.0, font_size_and_border)

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

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

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

worksheet.set_row(0, None, header_format)

writer.close()

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