pandas设置Excel单元格格式

:需要安装xlsxwriteropenpyxl三方库

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()

效果如下:

posted @   cnblogs用户  阅读(3881)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
点击右上角即可分享
微信分享提示