# 使用pandas和XlsxWriter创建Excel文件、向单个文件中写入不同的工作表、设置样式

import pandas as pd
from datetime import datetime

a = [['月份', '购买用户数', '7D', '15D', '30D', '60D', '>60D'], ['202011', 1876, 18, 13, 36, 53, 21], ['', '', '0.96%', '0.69%', '1.92%', '2.83%', '1.12%'], ['202012', 1872, 12, 9, 20, 24, 0], ['', '', '0.64%', '0.48%', '1.07%', '1.28%', 0], ['202101', 1532, 14, 7, 11, 0, 0], ['', '', '0.91%', '0.46%', '0.72%', 0, 0], ['202102', 183, 2, 0, 0, 0, 0], ['', '', '1.09%', 0, 0, 0, 0]]
b = [['月份', '购买用户数', '7D', '15D', '30D', '60D', '>60D'], ['202011', 552, 13, 11, 20, 21, 6], ['', '', '2.36%', '1.99%', '3.62%', '3.8%', '1.09%'], ['202012', 764, 21, 5, 34, 26, 0], ['', '', '2.75%', '0.65%', '4.45%', '3.4%', 0], ['202101', 1047, 23, 13, 19, 4, 0], ['', '', '2.2%', '1.24%', '1.81%', '0.38%', 0], ['202102', 132, 1, 0, 0, 0, 0], ['', '', '0.76%', 0, 0, 0, 0]]

# 插入一行，表头占位,用于后面的单元格合并
a.insert(0, [])
b.insert(0, [])

df_a = pd.DataFrame(data=a)
df_b = pd.DataFrame(data=b)

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df_a.to_excel(writer, sheet_name='Sheet1', index=False, header=False)
df_b.to_excel(writer, sheet_name='Sheet2', index=False, header=False)

# 样式
for sheet in ['Sheet1', 'Sheet2']:
    workbook = writer.book
    worksheet = writer.sheets[sheet]

    format1 = workbook.add_format({
        'valign': 'vcenter',   # 垂直对齐
        'align': 'center',    # 水平对齐
    })

    # 列
    worksheet.set_column('A:G', width=10, cell_format=format1)
    # 行
    for i in range(len(a)):
        worksheet.set_row(i, height=20)

    # 表头
    format_title = workbook.add_format({
        'bold': 1,
        'valign': 'vcenter',
        'align': 'center',
    })
    # 合并单元格
    worksheet.merge_range('A1:G1', f"统计时间：{datetime.now().strftime('%Y-%m-%d')}", cell_format=format_title)

writer.save()

参考链接：https://xlsxwriter.readthedocs.io/