Top Value Coloring

Step1 提取并合并多个 Sheet 中的关键维度数据，进行数据清洗、类型转换及 Top-N 筛选。

# 示例：合并两个 Sheet 的数据
# 读取 Sheet1 并清洗
df1 = pd.read_excel(file_path, sheet_name='Sheet1', header=None)
# 假设 group_col 在第0列，value_col 在第2列
data1 = df1.iloc[20:, [0, 2]].copy()
data1.columns = ['group_col', 'value_col_1']
data1['value_col_1'] = pd.to_numeric(data1['value_col_1'], errors='coerce')
data1['group_col'] = data1['group_col'].ffill() # 处理合并单元格产生的缺失

# 读取 Sheet2 并清洗
df2 = pd.read_excel(file_path, sheet_name='Sheet2', header=None)
data2 = df2.iloc[5:, [0, 1]].copy()
data2.columns = ['value_col_2', 'value_col_3']

# 合并数据
merged_df = pd.concat([data1.reset_index(drop=True), data2.reset_index(drop=True)], axis=1)
merged_df = merged_df.dropna(subset=['value_col_1'])

# 筛选关键指标前五的数据
top_results = merged_df.nlargest(5, 'value_col_1').copy()

# 占位示例：修正特定缺失值
# top_results.loc[top_results['group_col'].isna(), 'group_col'] = 'Default_Value'

Step2 使用 openpyxl 创建格式化表格，应用条件样式（如特定列标红、最大值高亮）并设置边框与对齐方式。

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

output_path = 'analysis_report.xlsx'

# 创建工作簿
wb = Workbook()
ws = wb.active
ws.title = 'Analysis_Results'

# 定义样式
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
header_font = Font(bold=True, color='FFFFFF', size=12)
red_font = Font(color='FF0000', bold=True) # 用于高亮异常或关键值
green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid') # 用于高亮最大值
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), 
                     top=Side(style='thin'), bottom=Side(style='thin'))
center_align = Alignment(horizontal='center', vertical='center')

# 写入表头
headers = ['Rank'] + list(top_results.columns)
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center_align
    cell.border = thin_border