如何在Python中保留原Excel格式并保存带条件着色的表格？

加载带有单元格格式（边框、合并单元格、字体等）的Excel数据，通过Python条件语句为满足特定条件的单元格着色，保存表格时希望保留原Excel格式，但现有代码无法实现预期效果。

用户尝试的代码：

import pandas as pd
import numpy as np
from openpyxl import load_workbook

workbook = openpyxl.load_workbook('test.xlsx')
worksheet = workbook['Tables']


# Load the DataFrame from the Excel file
df = pd.read_excel('test.xlsx', sheet_name='Tables')

# Define the column list
column_list = df.columns.to_list()[3:]

# Define the color function
def color(df, subset=column_list, up=5, low=-5):
    tmp = df[column_list].sub(pd.to_numeric(df['Unnamed: 2'], errors='coerce'), axis=0)
    return pd.DataFrame(np.select([tmp.gt(up), tmp.lt(low)],
                                  ['background-color: #e6ffe6;',
                                   'background-color: #ffe6e6;'],
                                  None), columns=subset, index=df.index
                       ).reindex_like(df)

df.style.apply(color, axis=None).to_excel('result.xlsx', engine='openpyxl', index=False)

原代码的核心问题是：pandas.Styler.to_excel会生成全新的Excel工作簿，完全不继承原文件的格式（边框、合并单元格、字体等）。你虽然加载了openpyxl的工作簿，但后续保存流程并未使用它，等于白加载。

直接用openpyxl操作原工作簿，在保留原有格式的基础上，对符合条件的单元格设置填充色。具体步骤：

1. 加载原工作簿时保留所有格式
2. 用DataFrame计算单元格差值，确定着色规则
3. 遍历目标单元格，按条件设置填充色
4. 保存修改后的原工作簿

修改后的代码：

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

# 加载原工作簿：data_only=False保留公式和格式，read_only=False允许修改
wb = load_workbook('test.xlsx', data_only=False, read_only=False)
ws = wb['Tables']

# 读取数据用于计算差值
df = pd.read_excel('test.xlsx', sheet_name='Tables')
column_list = df.columns.to_list()[3:]
reference_col = pd.to_numeric(df['Unnamed: 2'], errors='coerce')
diff_df = df[column_list].sub(reference_col, axis=0)

# 定义填充样式
green_fill = PatternFill(start_color='e6ffe6', end_color='e6ffe6', fill_type='solid')
red_fill = PatternFill(start_color='ffe6e6', end_color='ffe6e6', fill_type='solid')

# 遍历单元格设置颜色：Excel行号从1开始，表头占第1行，所以数据行从第2行开始
for row_idx in range(len(df)):
    excel_row = row_idx + 2
    for col_name in column_list:
        # 转换DataFrame列索引为Excel列号
        col_idx = df.columns.get_loc(col_name) + 1
        cell = ws.cell(row=excel_row, column=col_idx)
        diff_val = diff_df.loc[row_idx, col_name]
        if pd.notna(diff_val):
            if diff_val > 5:
                cell.fill = green_fill
            elif diff_val < -5:
                cell.fill = red_fill

# 保存结果
wb.save('result.xlsx')

• load_workbook的参数设置是核心，确保原格式不丢失
• 直接操作原工作簿的单元格，所有原有格式（边框、合并单元格、字体等）都会被保留
• 行号转换要注意Excel和DataFrame的索引差异，避免错位
• 原表格的合并单元格结构无需额外处理，openpyxl会自动保留

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