如何用worksheet.add_format()格式化DataFrame时跳过表头行？

尝试将DataFrame的数据格式化为保留4位小数，但当前操作会同时格式化表头行。请问使用worksheet.add_format()时能否跳过第一行（表头）？

原始DataFrame #

DateReferencia  2023    2024    2025    2026    2027    2028
Date                        
02/01/2024  2.9233  1.5500  2.0 2.0 2.0 NaN
03/01/2024  2.9233  1.5500  2.0 2.0 2.0 NaN
04/01/2024  2.9313  1.5872  2.0 2.0 2.0 NaN
05/01/2024  2.9221  1.5872  2.0 2.0 2.0 NaN
08/01/2024  2.9208  1.5922  2.0 2.0 2.0 NaN

当前使用代码 #

writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine="xlsxwriter")
df.to_excel(writer, sheet_name='Sheet1')

workbook = writer.book
worksheet = writer.sheets["Sheet1"]

format1 = workbook.add_format({"num_format": "0.0000"})
worksheet.set_column(1,len(df), None, format1)
writer.close()

当前输出问题 #

表头的年份被错误格式化为带四位小数的数值（如2,023.0000），仅需格式化数据行：

DateReferencia 2,023.0000   2,024.0000  2,025.0000  2,026.0000  2,027.0000  2,028.0000
Date
02/01/2024  2.9233  1.5500  2.0000  2.0000  2.0000  
03/01/2024  2.9233  1.5500  2.0000  2.0000  2.0000  
04/01/2024  2.9313  1.5872  2.0000  2.0000  2.0000  
05/01/2024  2.9221  1.5872  2.0000  2.0000  2.0000  
08/01/2024  2.9208  1.5922  2.0000  2.0000  2.0000  

worksheet.set_column()是对整列应用格式，无法直接跳过表头行。要实现仅格式化数据行，可采用以下两种方法：

方法一：逐行设置数据行格式 #

遍历所有数据行，为每行指定列应用小数格式：

writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine="xlsxwriter")
df.to_excel(writer, sheet_name='Sheet1')

workbook = writer.book
worksheet = writer.sheets["Sheet1"]

format1 = workbook.add_format({"num_format": "0.0000"})

# 数据起始行：因DataFrame有多层表头，数据从第2行开始（Excel行号从0计数）
start_row = 2
# 遍历所有数据行
for row_num in range(start_row, start_row + len(df)):
    # 对第1列到最后一列应用格式
    worksheet.set_row(row_num, None, format1)

writer.close()

方法二：先整列设置格式，再覆盖表头格式 #

先给整列设置小数格式，再为表头行设置默认格式覆盖：

writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine="xlsxwriter")
df.to_excel(writer, sheet_name='Sheet1')

workbook = writer.book
worksheet = writer.sheets["Sheet1"]

format1 = workbook.add_format({"num_format": "0.0000"})
# 整列设置小数格式
worksheet.set_column(1, len(df), None, format1)

# 创建表头格式：使用默认格式避免被小数格式影响
header_format = workbook.add_format()
# 覆盖表头行（第0行和第1行）的格式
worksheet.set_row(0, None, header_format)
worksheet.set_row(1, None, header_format)

writer.close()

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