pandas设置Excel单元格格式

注：需要安装xlsxwriter或openpyxl三方库

import pandas as pd


res_dic = {
    '编号': [],
    '代码': [],
    '原金额': [],
    '名称': [],
    '余额': [],
    '差异': []
}

diff_df = pd.DataFrame(res_dic)

# 获取原币金额小于0的数据，并拿到行索引
less_than_zero = diff_df[diff_df['原金额'] < 0.0]
idx = less_than_zero.index.values

# 数据写入Excel
writer = pd.ExcelWriter(res_file)
diff_df.to_excel(writer, 'sheet1', index=False)

# 表格格式设置
workbook = writer.book
worksheet = writer.sheets['sheet1']
# 将原金额小于0的行设为底红色
bg_color_format = workbook.add_format({'bg_color': 'red'})
for i in idx:
    worksheet.conditional_format(
        i + 1, 
        0, 
        i + 1, 
        5, 
        {'type': 'text', 'criteria': 'containing', 'value': '', 'format': bg_color_format}
    )
# 将金额相关列格式设置为 会计专用
balance_format = workbook.add_format({'num_format': '_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_ '})
# xlsxwriter方法
worksheet.set_column('C:C', width=25, cell_format=balance_format)
worksheet.set_column('E:E', width=25, cell_format=balance_format)
worksheet.set_column('F:F', width=25, cell_format=balance_format)
"""
# openpyxl方法
from openpyxl.styles import PatternFill

worksheet.column_dimensions('A').width = 25
for col in range(1, 7):  # 设置A1-F1背景色
    cell = worksheet.cell(row=1, column=col)
    cell.fill = PatternFill(start_color='0000FF', end_color='0000FF', fill_type='solid')
# workbook.save()
"""
writer.save()

效果如下：