Python 自动化之 Excel 高级处理——数据透视表、条件格式、图表
上一篇讲了 Excel 的基础操作，这篇讲进阶功能——数据透视表、条件格式、图表、批量样式。这些在出报表的时候非常实用，用 Python 生成一份带图表的 Excel 报告，比手动做快 100 倍。
一、安装
pipinstallopenpyxl# Excel 高级功能（样式、图表、格式）二、数据透视表
1. 用 pandas 生成透视表
importpandasaspd# 原始数据df=pd.read_excel("销售数据.xlsx")print(df.head())# 输出：# 日期 城市 销售员 品类 销售额# 0 2026-01 北京 张三 手机 12000# 1 2026-01 上海 李四 手机 15000# 2 2026-01 北京 王五 电脑 18000# 数据透视表：行=城市，列=品类，值=销售额总和pivot=pd.pivot_table(df,values="销售额",index="城市",columns="品类",aggfunc="sum",fill_value=0,margins=True,# 显示合计margins_name="合计"# 合计列名)print(pivot)# 品类 电脑 手机 合计# 城市# 北京 45000 32000 77000# 上海 38000 41000 79000# 合计 83000 73000 1560002. 多级透视表
pivot=pd.pivot_table(df,values="销售额",index=["城市","销售员"],columns="品类",aggfunc="sum",fill_value=0,)# 行索引有两级：城市 → 销售员3. 导出到 Excel
withpd.ExcelWriter("销售报表.xlsx",engine="openpyxl")aswriter:# 原始数据df.to_excel(writer,sheet_name="原始数据",index=False)# 透视表pivot.to_excel(writer,sheet_name="品类分析")# 多级透视表pivot_multi=pd.pivot_table(df,values="销售额",index=["城市","销售员"],aggfunc="sum")pivot_multi.to_excel(writer,sheet_name="员工业绩")三、样式美化
1. 设置单元格样式
fromopenpyxlimportload_workbookfromopenpyxl.stylesimportFont,PatternFill,Alignment,Border,Side wb=load_workbook("销售报表.xlsx")forsheet_nameinwb.sheetnames:ws=wb[sheet_name]# 表头样式：加粗、居中、蓝底白字header_font=Font(bold=True,color="FFFFFF",size=11)header_fill=PatternFill(start_color="4472C4",end_color="4472C4",fill_type="solid")header_align=Alignment(horizontal="center",vertical="center")forcellinws[1]:# 第一行是表头cell.font=header_font cell.fill=header_fill cell.alignment=header_align# 数据行：居中，自动列宽forrowinws.iter_rows(min_row=2,max_col=ws.max_column):forcellinrow:cell.alignment=Alignment(horizontal="center",vertical="center")# 自动列宽forcolinws.columns:max_length=0col_letter=col[0].column_letterforcellincol:ifcell.value:max_length=max(max_length,len(str(cell.value)))ws.column_dimensions[col_letter].width=max_length+4wb.save("销售报表_美化.xlsx")print("样式美化完成")2. 条件格式
fromopenpyxl.formatting.ruleimportCellIsRule,DataBarRule,ColorScaleRulefromopenpyxl.stylesimportPatternFill wb=load_workbook("销售报表.xlsx")ws=wb.active# 方法1：销售额 > 15000 标红red_fill=PatternFill(start_color="FFC7CE",end_color="FFC7CE",fill_type="solid")ws.conditional_formatting.add("D2:D100",CellIsRule(operator="greaterThan",formula=["15000"],fill=red_fill))# 方法2：数据条（直观显示大小）ws.conditional_formatting.add("D2:D100",DataBarRule(start_type="min",end_type="max",color="5B9BD5",showValue=True))# 方法3：色阶（绿→黄→红）ws.conditional_formatting.add("D2:D100",ColorScaleRule(start_type="min",start_color="63BE7B",mid_type="percentile",mid_value=50,mid_color="FFEB84",end_type="max",end_color="F8696B"))wb.save("销售报表_条件格式.xlsx")四、图表
1. 柱状图
fromopenpyxl.chartimportBarChart,Reference wb=load_workbook("销售报表.xlsx")ws=wb.active# 创建柱状图chart=BarChart()chart.type="col"# 柱状图chart.title="各城市销售额"chart.y_axis.title="销售额"chart.x_axis.title="城市"chart.style=10# 数据范围data=Reference(ws,min_col=4,min_row=1,max_col=4,max_row=ws.max_row)cats=Reference(ws,min_col=2,min_row=2,max_row=ws.max_row)# 城市列chart.add_data(data,titles_from_data=True)chart.set_categories(cats)chart.shape=4ws.add_chart(chart,"F2")wb.save("销售报表_图表.xlsx")2. 折线图（趋势）
fromopenpyxl.chartimportLineChart line_chart=LineChart()line_chart.title="月度销售趋势"line_chart.style=10line_chart.y_axis.title="销售额"line_chart.x_axis.title="月份"data=Reference(ws,min_col=4,min_row=1,max_row=ws.max_row)cats=Reference(ws,min_col=1,min_row=2,max_row=ws.max_row)line_chart.add_data(data,titles_from_data=True)line_chart.set_categories(cats)ws.add_chart(line_chart,"F20")3. 饼图（占比）
fromopenpyxl.chartimportPieChart pie_chart=PieChart()pie_chart.title="品类占比"pie_chart.style=10data=Reference(ws,min_col=4,min_row=1,max_row=ws.max_row)cats=Reference(ws,min_col=3,min_row=2,max_row=ws.max_row)pie_chart.add_data(data,titles_from_data=True)pie_chart.set_categories(cats)ws.add_chart(pie_chart,"F38")五、批量处理多个 Excel
importosimportpandasaspddefmerge_excel_files(input_dir,output_file):"""合并整个文件夹的 Excel 文件"""all_data=[]forfinos.listdir(input_dir):iff.endswith((".xlsx",".xls")):df=pd.read_excel(os.path.join(input_dir,f))df["来源文件"]=f# 标记来源all_data.append(df)print(f"已读取:{f}({len(df)}行)")result=pd.concat(all_data,ignore_index=True)withpd.ExcelWriter(output_file,engine="openpyxl")aswriter:result.to_excel(writer,sheet_name="汇总数据",index=False)# 生成报表pivot=pd.pivot_table(result,values="销售额",index="城市",aggfunc=["sum","mean","count"])pivot.to_excel(writer,sheet_name="统计报表")print(f"合并完成！共{len(result)}行 →{output_file}")# 使用merge_excel_files("月度报表","年度汇总.xlsx")六、完整案例：自动生成月报
importpandasaspdfromopenpyxlimportload_workbookfromopenpyxl.chartimportBarChart,Referencefromopenpyxl.stylesimportFont,PatternFill,Alignmentfromdatetimeimportdatetimedefgenerate_monthly_report(data_file,output_file):"""自动生成月度销售报表"""# 1. 读取数据df=pd.read_excel(data_file)month=datetime.now().strftime("%Y年%m月")# 2. 生成透视表withpd.ExcelWriter(output_file,engine="openpyxl")aswriter:# 原始数据df.to_excel(writer,sheet_name="数据源",index=False)# 按城市汇总city_pivot=pd.pivot_table(df,values="销售额",index="城市",aggfunc=["sum","count"])city_pivot.to_excel(writer,sheet_name="城市分析")# 按品类汇总cat_pivot=pd.pivot_table(df,values="销售额",index="品类",aggfunc="sum")cat_pivot.to_excel(writer,sheet_name="品类分析")# 3. 美化 + 图表wb=load_workbook(output_file)ws=wb["城市分析"]# 表头样式header_font=Font(bold=True,color="FFFFFF",size=11)header_fill=PatternFill("solid",fgColor="4472C4")forcellinws[1]:cell.font=header_font cell.fill=header_fill cell.alignment=Alignment(horizontal="center")# 柱状图chart=BarChart()chart.title=f"{month}各城市销售额"data=Reference(ws,min_col=2,min_row=1,max_col=2,max_row=ws.max_row)cats=Reference(ws,min_col=1,min_row=2,max_row=ws.max_row)chart.add_data(data,titles_from_data=True)chart.set_categories(cats)chart.width=18;chart.height=12ws.add_chart(chart,"D2")wb.save(output_file)print(f"月报已生成:{output_file}")# 使用generate_monthly_report("6月销售数据.xlsx","6月销售报表.xlsx")七、常用样式速查
# 字体Font(name="微软雅黑",size=11,bold=True,italic=False,color="FF0000")# 填充PatternFill(start_color="FFC000",end_color="FFC000",fill_type="solid")GradientFill(stop=("FFFFFF","4472C4"))# 对齐Alignment(horizontal="center",vertical="center",wrap_text=True)# 边框thin_border=Border(left=Side(style="thin"),right=Side(style="thin"),top=Side(style="thin"),bottom=Side(style="thin"),)# 行高列宽ws.row_dimensions[1].height=30ws.column_dimensions["A"].width=15💡 觉得有用的话，点赞 + 关注【张老师技术栈】吧！每周更新 Java/Python/爬虫 实战干货，不让你白来。