如何用Pandas为Excel不同工作表的指定列设置不同背景色？

我写了一段Python脚本，能读取多个Excel文件并合并到一个最终Excel的不同工作表里。现在已经实现给所有工作表的列名设置蓝色背景，但想给特定工作表的部分列设绿色背景，其余列保持蓝色，请问能实现吗？

以下是我的脚本：

def gitanalysis():
    dest = createdir()
    dfGitUsers = pd.read_excel(os.path.join(dest, "GitUsers.xlsx"))
    dfGitUsers.fillna("N/A", inplace=True)
    dfGitGroupMembership = pd.read_excel(os.path.join(dest, "GitGroupMembership.xlsx"))
    dfGitRepoGroupAccess= pd.read_excel(os.path.join(dest,"GitRepoGroupAccess.xlsx"))
    dfGitReposSize=pd.read_excel(os.path.join(dest,"GitReposSize.xlsx"))
    dfGitRepoLastChangeDate=pd.read_excel(os.path.join(dest,"GitRepoLastChangeDate.xlsx"))
    pathdest = path_dir()

    # 读取CM_UserDetails.xlsx并加入到结果表中
    dfUserDetails = pd.read_excel(rf"{pathdest}\CM_UsersDetails.xlsx")
    dfUserDetails.fillna("N/A", inplace=True)

    timestr = time.strftime("%Y-%m-%d-")
    xlwriter = pd.ExcelWriter(os.path.join(dest, f'{timestr}Usage-GitAnalysis.xlsx'))
    dfUserDetails.to_excel(xlwriter, sheet_name='UserDetails', index=False)
    dfGitUsers.to_excel(xlwriter, sheet_name='GitUsers', index=False)
    dfGitGroupMembership.to_excel(xlwriter, sheet_name='GitGroupMembership', index=False)
    dfGitRepoGroupAccess.to_excel(xlwriter,sheet_name='GitRepoGroupAccess',index=False)
    dfGitReposSize.to_excel(xlwriter,sheet_name='GitReposSize',index=False)
    dfGitRepoLastChangeDate.to_excel(xlwriter,sheet_name='GitRepoLastChangeDate',index=False)
    for column in dfGitUsers:
        column_width = max(dfGitUsers[column].astype(str).map(len).max(), len(column))
        col_idx = dfGitUsers.columns.get_loc(column)
        xlwriter.sheets['GitUsers'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['UserDetails'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['GitGroupMembership'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['GitRepoGroupAccess'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['GitReposSize'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['GitRepoLastChangeDate'].set_column(col_idx,col_idx,column_width)

# 这里是设置蓝色背景的代码
    workbook = xlwriter.book
    cell_format = workbook.add_format({'bg_color': 'blue'})
    cell_format.set_bold()
    cell_format.set_font_color('black')
    cell_format.set_border(1)

    for sheet_name in xlwriter.sheets:
        ws = xlwriter.sheets[sheet_name]
        ws.freeze_panes(1, 0)
        ws.conditional_format('A1:{}1'.format(chr(65 + ws.dim_colmax)), {'type': 'no_blanks', 'format': cell_format})

    xlwriter.close()
    print("GitSvnAnalysis.xlsx was exported with succes!")

当前效果：所有工作表的表头列名均为蓝色背景。
预期效果：以GitUsers工作表为例，其中部分列的表头为绿色背景，其余列表头保持蓝色。

完全可以实现，核心思路是针对特定工作表单独处理表头格式，其他工作表保持原有蓝色格式即可。具体修改后的完整脚本如下：

def gitanalysis():
    dest = createdir()
    dfGitUsers = pd.read_excel(os.path.join(dest, "GitUsers.xlsx"))
    dfGitUsers.fillna("N/A", inplace=True)
    dfGitGroupMembership = pd.read_excel(os.path.join(dest, "GitGroupMembership.xlsx"))
    dfGitRepoGroupAccess= pd.read_excel(os.path.join(dest,"GitRepoGroupAccess.xlsx"))
    dfGitReposSize=pd.read_excel(os.path.join(dest,"GitReposSize.xlsx"))
    dfGitRepoLastChangeDate=pd.read_excel(os.path.join(dest,"GitRepoLastChangeDate.xlsx"))
    pathdest = path_dir()

    # 读取CM_UserDetails.xlsx并加入到结果表中
    dfUserDetails = pd.read_excel(rf"{pathdest}\CM_UsersDetails.xlsx")
    dfUserDetails.fillna("N/A", inplace=True)

    timestr = time.strftime("%Y-%m-%d-")
    xlwriter = pd.ExcelWriter(os.path.join(dest, f'{timestr}Usage-GitAnalysis.xlsx'))
    dfUserDetails.to_excel(xlwriter, sheet_name='UserDetails', index=False)
    dfGitUsers.to_excel(xlwriter, sheet_name='GitUsers', index=False)
    dfGitGroupMembership.to_excel(xlwriter, sheet_name='GitGroupMembership', index=False)
    dfGitRepoGroupAccess.to_excel(xlwriter,sheet_name='GitRepoGroupAccess',index=False)
    dfGitReposSize.to_excel(xlwriter,sheet_name='GitReposSize',index=False)
    dfGitRepoLastChangeDate.to_excel(xlwriter,sheet_name='GitRepoLastChangeDate',index=False)
    
    # 设置列宽（原逻辑保留）
    for column in dfGitUsers:
        column_width = max(dfGitUsers[column].astype(str).map(len).max(), len(column))
        col_idx = dfGitUsers.columns.get_loc(column)
        xlwriter.sheets['GitUsers'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['UserDetails'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['GitGroupMembership'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['GitRepoGroupAccess'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['GitReposSize'].set_column(col_idx, col_idx, column_width)
        xlwriter.sheets['GitRepoLastChangeDate'].set_column(col_idx,col_idx,column_width)

    # 创建两种格式：蓝色（默认）和绿色（特定列）
    workbook = xlwriter.book
    blue_format = workbook.add_format({'bg_color': 'blue'})
    blue_format.set_bold()
    blue_format.set_font_color('black')
    blue_format.set_border(1)

    green_format = workbook.add_format({'bg_color': 'green'})
    green_format.set_bold()
    green_format.set_font_color('black')
    green_format.set_border(1)

    # 定义目标工作表和需要设为绿色的列名（根据需求修改）
    target_sheet = 'GitUsers'
    green_columns = ['需要设绿的列名1', '需要设绿的列名2']  # 替换为实际列名

    for sheet_name in xlwriter.sheets:
        ws = xlwriter.sheets[sheet_name]
        ws.freeze_panes(1, 0)
        
        if sheet_name == target_sheet:
            # 先给所有表头设置蓝色背景
            ws.conditional_format('A1:{}1'.format(chr(65 + ws.dim_colmax)), {'type': 'no_blanks', 'format': blue_format})
            # 再给指定列覆盖设置绿色背景
            for col_name in green_columns:
                if col_name in dfGitUsers.columns:
                    col_idx = dfGitUsers.columns.get_loc(col_name)
                    col_letter = chr(65 + col_idx)
                    ws.conditional_format(f'{col_letter}1', {'type': 'no_blanks', 'format': green_format})
        else:
            # 非目标工作表直接用蓝色格式
            ws.conditional_format('A1:{}1'.format(chr(65 + ws.dim_colmax)), {'type': 'no_blanks', 'format': blue_format})

    xlwriter.close()
    print("GitSvnAnalysis.xlsx 导出成功！")

关键修改说明： #

• 新增green_format格式，配置与蓝色格式一致，仅背景色改为绿色
• 定义target_sheet（目标工作表）和green_columns（需要设绿色的列名列表），可根据实际需求修改
• 遍历工作表时，对目标工作表先设置全表蓝色表头，再对指定列覆盖设置绿色表头；非目标工作表保持原逻辑

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