如何用Python的XlsxWriter为多工作表PNL列设置条件格式

要实现给每个工作表中两个DataFrame的PNL列设置条件格式（值>0显示绿色，<0显示红色），可以利用xlsxwriter的条件格式功能完成。下面是修改后的完整代码及关键说明：

修改后的代码 #

import os
import pandas as pd

# 请确保pnl、calc函数及gp、quar变量已提前定义
# etfc = [r'D:\Development\Output',]
# gp = ... 
# quar = ...

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

for et in etfc:
    for entry in os.scandir(et):
        if entry.is_file() and entry.name.endswith('.csv'):
            df = pd.read_csv(entry.path, index_col=False,)
            
            start_row = 0
            start_col = 0
            
            # 生成第一个DataFrame并写入Excel
            qw = []
            for i in gp:
                sd = df[(df[i] == True)]
                if sd.shape[0] > 0:
                    qw.append({'INDX':i,'PNL': pnl(sd) ,'WR': calc(sd) ,})
            qw_df = pd.DataFrame(qw)
            sheet_name = f'{index}_{j}'  # 确保index和j变量已定义
            qw_df.to_excel(writer, startrow=start_row, startcol=start_col, index=False, sheet_name=sheet_name)

            # 获取当前工作表对象
            worksheet = writer.sheets[sheet_name]
            # 创建条件格式样式
            green_format = writer.book.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'})
            red_format = writer.book.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
            
            # 处理第一个PNL列（qw_df的PNL）
            qw_pnl_col = start_col + 1  # PNL是qw_df的第二列
            qw_data_start_row = start_row + 1  # 表头在start_row，数据从下一行开始
            qw_data_end_row = start_row + len(qw_df)  # 数据最后一行
            
            # 添加大于0的条件格式
            worksheet.conditional_format(qw_data_start_row, qw_pnl_col, qw_data_end_row, qw_pnl_col,
                                        {'type': 'cell',
                                        'criteria': '>',
                                        'value': 0,
                                        'format': green_format})
            # 添加小于0的条件格式
            worksheet.conditional_format(qw_data_start_row, qw_pnl_col, qw_data_end_row, qw_pnl_col,
                                        {'type': 'cell',
                                        'criteria': '<',
                                        'value': 0,
                                        'format': red_format})

            start_col += 5

            # 生成第二个DataFrame并写入Excel
            qua = []
            for qu in quar:
                sd = df[(df[qu] == True)]
                if sd.shape[0] > 0:
                    qua.append({'DP':qu,'PNL': pnl(sd) ,'WR': calc(sd) })
            qua_df = pd.DataFrame(qua)
            qua_df.to_excel(writer, startrow=start_row, startcol=start_col, index=False, sheet_name=sheet_name)

            # 处理第二个PNL列（qua_df的PNL）
            qua_pnl_col = start_col + 1  # PNL是qua_df的第二列
            qua_data_start_row = start_row + 1
            qua_data_end_row = start_row + len(qua_df)
            
            # 添加条件格式规则
            worksheet.conditional_format(qua_data_start_row, qua_pnl_col, qua_data_end_row, qua_pnl_col,
                                        {'type': 'cell',
                                        'criteria': '>',
                                        'value': 0,
                                        'format': green_format})
            worksheet.conditional_format(qua_data_start_row, qua_pnl_col, qua_data_end_row, qua_pnl_col,
                                        {'type': 'cell',
                                        'criteria': '<',
                                        'value': 0,
                                        'format': red_format})

writer.save()

关键步骤说明 #

1. 保留DataFrame引用：将生成的qw和qua转换为DataFrame变量（qw_df、qua_df），方便获取数据行数和后续操作。
2. 获取工作表对象：通过writer.sheets[sheet_name]拿到对应工作表的操作句柄，这是xlsxwriter设置格式的核心入口。
3. 创建格式样式：用writer.book.add_format()定义绿色（正数值）和红色（负数值）的填充及字体颜色样式。
4. 定位PNL列范围：
   • 列位置：每个DataFrame的PNL是第二列，所以对应起始列+1。
   • 行范围：表头在start_row，数据从start_row+1开始，到start_row + DataFrame行数结束，确保覆盖所有数据行。
5. 添加条件格式：调用conditional_format()分别设置大于0和小于0的格式规则，指定应用的单元格范围和对应样式。

注意事项 #

• 确保index和j变量在循环内有正确定义（原代码中用作工作表名称，需保证作用域内有效）。
• 如果PNL列存在非数值数据，可添加qw_df['PNL'] = pd.to_numeric(qw_df['PNL'], errors='coerce')转换为数值型，避免条件格式失效。

内容的提问来源于stack exchange，提问作者Divyansh Kumar Singh