如何用Pandas配置多Sheet Excel的字体、数字及日期格式？

需将多个DataFrame写入Excel工作簿，目标效果：

• 所有工作表的默认字体为Century Gothic、字号9、字体颜色#2659D9
• 数字列设置为会计格式
• 日期列设置为yyyy-mm-dd格式
  现有代码未实现上述要求，原代码如下：

import pandas as pd
import numpy as np

df = pd.DataFrame({
        'JDate':["2022-01-31","2022-12-05","2023-11-10","2023-12-03","2024-01-16","2024-01-06","2011-01-04"],
        'Code':[None,'John Johnson',np.nan,'John Smith','Mary Williams','ted bundy','George Lucas'],
        'Unit Price':[np.nan,200,None,56,75,65,60],
        'Quantity':[1500, 140000, 1400000, 455, 648, 759,1000],
        'Amount':[100, 10000, 100000, 5, 48, 59,449],
        'Invoice':['soccer','basketball','baseball','football','baseball','ice hockey','football'],
        'energy':[100.,100,100,54,98,3,45],
        'Category':['alpha','bravo','kappa','alpha','bravo','bravo','kappa']
})

df["JDate"] = pd.to_datetime(df["JDate"])
df["JYearMonth"] =  df['JDate'].dt.to_period('M')

index_to_use = ['Category','Code','Invoice','Unit Price']
values_to_use = ['Amount']
columns_to_use = ['JYearMonth']

df2 = df.pivot_table(index=index_to_use,
                            values=values_to_use,
                            columns=columns_to_use)

df3 = df2.xs('alpha',level='Category')
df3 = df3.reset_index() 

writer= pd.ExcelWriter(
        "t2test13.xlsx",
        engine='xlsxwriter'
    )

df.to_excel(writer,sheet_name="t2",index=True)
df2.to_excel(writer,sheet_name="t2test",index=True)
df3.to_excel(writer,sheet_name="t2filter",index=True)

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

fmt_header = workbook.add_format({
    'bold':True,
    'text_wrap':True,
    'valign':'top',
    'fg_color': '#5DADE2',
    'font_color':'#2659D9',
    'border':1
})

writer.close()

通过xlsxwriter的格式控制功能，结合对工作表的遍历处理，可实现所有需求：

核心步骤 #

1. 设置全局默认格式：为工作簿所有单元格指定默认字体样式，覆盖Excel默认设置
2. 创建专用格式：分别定义日期格式、会计格式，保留原表头格式
3. 遍历处理每个工作表：
   • 为表头行应用预定义的表头格式
   • 根据DataFrame的数据类型，匹配对应列并应用日期/会计格式
   • 自动调整列宽，优化显示效果

完整可执行代码 #

import pandas as pd
import numpy as np

# 构造示例数据
df = pd.DataFrame({
        'JDate':["2022-01-31","2022-12-05","2023-11-10","2023-12-03","2024-01-16","2024-01-06","2011-01-04"],
        'Code':[None,'John Johnson',np.nan,'John Smith','Mary Williams','ted bundy','George Lucas'],
        'Unit Price':[np.nan,200,None,56,75,65,60],
        'Quantity':[1500, 140000, 1400000, 455, 648, 759,1000],
        'Amount':[100, 10000, 100000, 5, 48, 59,449],
        'Invoice':['soccer','basketball','baseball','football','baseball','ice hockey','football'],
        'energy':[100.,100,100,54,98,3,45],
        'Category':['alpha','bravo','kappa','alpha','bravo','bravo','kappa']
})

df["JDate"] = pd.to_datetime(df["JDate"])
df["JYearMonth"] = df['JDate'].dt.to_period('M')

# 生成透视表和筛选后的DataFrame
index_to_use = ['Category','Code','Invoice','Unit Price']
values_to_use = ['Amount']
columns_to_use = ['JYearMonth']

df2 = df.pivot_table(index=index_to_use, values=values_to_use, columns=columns_to_use)
df3 = df2.xs('alpha', level='Category').reset_index()

# 初始化ExcelWriter
writer = pd.ExcelWriter("t2test13.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name="t2", index=True)
df2.to_excel(writer, sheet_name="t2test", index=True)
df3.to_excel(writer, sheet_name="t2filter", index=True)

workbook = writer.book

# 设置全局默认格式：Century Gothic，字号9，字体颜色#2659D9
default_format = workbook.add_format({
    'font_name': 'Century Gothic',
    'font_size': 9,
    'font_color': '#2659D9'
})
workbook.set_default_format(default_format)

# 创建专用格式
fmt_header = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#5DADE2',
    'font_color': '#2659D9',
    'font_name': 'Century Gothic',
    'font_size': 9,
    'border': 1
})
fmt_date = workbook.add_format({
    'num_format': 'yyyy-mm-dd',
    'font_name': 'Century Gothic',
    'font_size': 9,
    'font_color': '#2659D9'
})
fmt_accounting = workbook.add_format({
    'num_format': '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)',
    'font_name': 'Century Gothic',
    'font_size': 9,
    'font_color': '#2659D9'
})

# 遍历所有工作表进行格式设置
for sheet_name in writer.sheets:
    worksheet = writer.sheets[sheet_name]
    df_current = None
    
    # 获取当前工作表对应的DataFrame
    if sheet_name == "t2":
        df_current = df
    elif sheet_name == "t2test":
        df_current = df2
    elif sheet_name == "t2filter":
        df_current = df3
    
    if df_current is not None:
        # 处理表头：适配普通列和多层索引列
        header_values = []
        if isinstance(df_current.columns, pd.MultiIndex):
            # 透视表的多层表头，取顶层和底层组合
            for col in df_current.columns:
                header_values.append(f"{col[0]}-{col[1]}")
        else:
            header_values = df_current.columns.tolist()
        # 加上索引列名（如果有）
        if df_current.index.name is not None:
            header_values.insert(0, df_current.index.name)
        
        # 应用表头格式
        for col_num, value in enumerate(header_values):
            worksheet.write(0, col_num, value, fmt_header)
        
        # 应用数据列格式
        start_col = 1 if df_current.index.name is not None else 0
        for col_num, col_name in enumerate(df_current.columns, start=start_col):
            # 处理日期列
            if pd.api.types.is_datetime64_any_dtype(df_current[col_name]):
                worksheet.set_column(col_num, col_num, 12, fmt_date)
            # 处理数字列
            elif pd.api.types.is_numeric_dtype(df_current[col_name]):
                worksheet.set_column(col_num, col_num, 15, fmt_accounting)
            else:
                # 文本列调整宽度
                worksheet.set_column(col_num, col_num, 20)
        
        # 处理索引列格式
        if df_current.index.name is not None:
            if pd.api.types.is_datetime64_any_dtype(df_current.index):
                worksheet.set_column(0, 0, 12, fmt_date)
            elif pd.api.types.is_numeric_dtype(df_current.index):
                worksheet.set_column(0, 0, 15, fmt_accounting)
            else:
                worksheet.set_column(0, 0, 20)

# 保存并关闭writer
writer.close()

关键说明 #

• 全局默认格式：通过workbook.set_default_format()确保所有单元格（包括未单独设置的文本列）都使用指定字体
• 格式继承：专用格式（日期、会计、表头）都显式指定了字体样式，避免被Excel默认样式覆盖
• 透视表适配：针对多层列索引的透视表，表头处理逻辑做了适配，确保格式正确应用

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