如何用Python将条形图嵌入含数据透视表的Excel对应位置

生成数据透视表的现有代码 #

import pandas as pd

df_pivot_comp = pd.pivot_table(pd.read_excel(excel_file, sheet_name=sheet),
                               values=['COMPILER WARNING'], columns=None, index=['GROUP','DOMAIN'],
                               aggfunc='count', margins=True, margins_name='Total', fill_value=0)
df_pivot_comp["Total"] = df_pivot_comp
group_count = df_pivot_comp.reset_index().groupby(["GROUP"]).agg({"COMPILER WARNING": sum})
for idx in df_pivot_comp.index.get_level_values(0).unique():
    df_pivot_comp.loc[(idx,), "Total"] = group_count.loc[idx].values[0]
for idx_0 in df_pivot_comp.index.get_level_values(0).unique():  # bar, foo, total
    if idx_0 == "Total":
        continue
for idx_1 in df_pivot_comp.loc[(idx_0,), :].index[1:]:
    df_pivot_comp.loc[(idx_0, idx_1), "Total"] = "" 

现有图表代码（仅临时显示，无法嵌入Excel） #

import matplotlib.pyplot as plt

df_pivot_comp.groupby('GROUP')['Total'].value_counts().unstack(level=1).plot.bar(stacked=True)
plt.legend(loc='upper left', prop={'size':7}, bbox_to_anchor=(1,1))
plt.tight_layout(pad=3)

问题 #

上述matplotlib代码生成的图表仅在弹窗临时显示，关闭后即消失，无法嵌入到Excel文件中与数据透视表关联保存。

解决方案 #

使用xlsxwriter作为Pandas的Excel写入引擎，直接在Excel中创建并嵌入条形图，步骤如下：

1. 安装依赖 #

确保已安装xlsxwriter：

pip install xlsxwriter

2. 完整实现代码 #

import pandas as pd

# 读取数据并生成数据透视表
df = pd.read_excel(excel_file, sheet_name=sheet)
df_pivot_comp = pd.pivot_table(df,
                               values=['COMPILER WARNING'], columns=None, index=['GROUP','DOMAIN'],
                               aggfunc='count', margins=True, margins_name='Total', fill_value=0)
df_pivot_comp["Total"] = df_pivot_comp
group_count = df_pivot_comp.reset_index().groupby(["GROUP"]).agg({"COMPILER WARNING": sum})
for idx in df_pivot_comp.index.get_level_values(0).unique():
    df_pivot_comp.loc[(idx,), "Total"] = group_count.loc[idx].values[0]
for idx_0 in df_pivot_comp.index.get_level_values(0).unique():
    if idx_0 == "Total":
        continue
for idx_1 in df_pivot_comp.loc[(idx_0,), :].index[1:]:
    df_pivot_comp.loc[(idx_0, idx_1), "Total"] = "" 

# 使用xlsxwriter写入Excel并嵌入图表
output_file = "pivot_with_charts.xlsx"
with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
    # 将数据透视表写入工作表
    df_pivot_comp.to_excel(writer, sheet_name='PivotTable')
    workbook = writer.book
    worksheet = writer.sheets['PivotTable']
    
    # 获取每个GROUP的行范围（Excel行号从1开始，表头在第1行）
    group_rows = {}
    current_group = None
    start_row = 1  # 数据起始行
    for row_idx, (group, domain) in enumerate(df_pivot_comp.index, start=2):
        if group != current_group:
            if current_group is not None:
                group_rows[current_group] = (start_row, row_idx - 1)
            current_group = group
            start_row = row_idx
    # 处理最后一个GROUP和Total行
    group_rows[current_group] = (start_row, row_idx)
    
    # 为每个GROUP创建条形图
    chart_row = df_pivot_comp.shape[0] + 3  # 图表起始行，在数据透视表下方留空行
    for group_name, (start_row, end_row) in group_rows.items():
        if group_name == 'Total':
            continue
        
        # 创建堆叠条形图对象
        chart = workbook.add_chart({'type': 'bar', 'subtype': 'stacked'})
        
        # 添加数据系列：关联COMPILER WARNING列数据
        chart.add_series({
            'name':       ['PivotTable', 0, 1],  # 列名
            'categories': ['PivotTable', start_row, 1, end_row, 1],  # DOMAIN标签
            'values':     ['PivotTable', start_row, 2, end_row, 2],  # 对应数值
        })
        
        # 设置图表样式
        chart.set_title({'name': f'Compiler Warnings by Domain - {group_name}'})
        chart.set_legend({'position': 'right', 'font': {'size': 7}})
        chart.set_y_axis({'name': 'Count'})
        
        # 将图表插入工作表指定位置
        worksheet.insert_chart(f'A{chart_row}', chart, {'x_offset': 25, 'y_offset': 10})
        
        # 更新下一个图表的起始行，避免重叠
        chart_row += 15

print(f"文件 {output_file} 已生成，包含数据透视表和对应条形图")

代码说明 #

• 用xlsxwriter引擎直接操作Excel工作簿，确保图表永久嵌入文件
• 遍历数据透视表行，定位每个GROUP对应的数据源范围
• 为每个GROUP创建堆叠条形图，关联对应数据列
• 控制图表位置，确保每个图表显示在对应GROUP数据的下方

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