上一篇讲了 Excel 的基础操作这篇讲进阶功能——数据透视表、条件格式、图表、批量样式。这些在出报表的时候非常实用用 Python 生成一份带图表的 Excel 报告比手动做快 100 倍。一、安装pipinstallopenpyxl# Excel 高级功能样式、图表、格式二、数据透视表1. 用 pandas 生成透视表importpandasaspd# 原始数据dfpd.read_excel(销售数据.xlsx)print(df.head())# 输出# 日期 城市 销售员 品类 销售额# 0 2026-01 北京 张三 手机 12000# 1 2026-01 上海 李四 手机 15000# 2 2026-01 北京 王五 电脑 18000# 数据透视表行城市列品类值销售额总和pivotpd.pivot_table(df,values销售额,index城市,columns品类,aggfuncsum,fill_value0,marginsTrue,# 显示合计margins_name合计# 合计列名)print(pivot)# 品类 电脑 手机 合计# 城市# 北京 45000 32000 77000# 上海 38000 41000 79000# 合计 83000 73000 1560002. 多级透视表pivotpd.pivot_table(df,values销售额,index[城市,销售员],columns品类,aggfuncsum,fill_value0,)# 行索引有两级城市 → 销售员3. 导出到 Excelwithpd.ExcelWriter(销售报表.xlsx,engineopenpyxl)aswriter:# 原始数据df.to_excel(writer,sheet_name原始数据,indexFalse)# 透视表pivot.to_excel(writer,sheet_name品类分析)# 多级透视表pivot_multipd.pivot_table(df,values销售额,index[城市,销售员],aggfuncsum)pivot_multi.to_excel(writer,sheet_name员工业绩)三、样式美化1. 设置单元格样式fromopenpyxlimportload_workbookfromopenpyxl.stylesimportFont,PatternFill,Alignment,Border,Side wbload_workbook(销售报表.xlsx)forsheet_nameinwb.sheetnames:wswb[sheet_name]# 表头样式加粗、居中、蓝底白字header_fontFont(boldTrue,colorFFFFFF,size11)header_fillPatternFill(start_color4472C4,end_color4472C4,fill_typesolid)header_alignAlignment(horizontalcenter,verticalcenter)forcellinws[1]:# 第一行是表头cell.fontheader_font cell.fillheader_fill cell.alignmentheader_align# 数据行居中自动列宽forrowinws.iter_rows(min_row2,max_colws.max_column):forcellinrow:cell.alignmentAlignment(horizontalcenter,verticalcenter)# 自动列宽forcolinws.columns:max_length0col_lettercol[0].column_letterforcellincol:ifcell.value:max_lengthmax(max_length,len(str(cell.value)))ws.column_dimensions[col_letter].widthmax_length4wb.save(销售报表_美化.xlsx)print(样式美化完成)2. 条件格式fromopenpyxl.formatting.ruleimportCellIsRule,DataBarRule,ColorScaleRulefromopenpyxl.stylesimportPatternFill wbload_workbook(销售报表.xlsx)wswb.active# 方法1销售额 15000 标红red_fillPatternFill(start_colorFFC7CE,end_colorFFC7CE,fill_typesolid)ws.conditional_formatting.add(D2:D100,CellIsRule(operatorgreaterThan,formula[15000],fillred_fill))# 方法2数据条直观显示大小ws.conditional_formatting.add(D2:D100,DataBarRule(start_typemin,end_typemax,color5B9BD5,showValueTrue))# 方法3色阶绿→黄→红ws.conditional_formatting.add(D2:D100,ColorScaleRule(start_typemin,start_color63BE7B,mid_typepercentile,mid_value50,mid_colorFFEB84,end_typemax,end_colorF8696B))wb.save(销售报表_条件格式.xlsx)四、图表1. 柱状图fromopenpyxl.chartimportBarChart,Reference wbload_workbook(销售报表.xlsx)wswb.active# 创建柱状图chartBarChart()chart.typecol# 柱状图chart.title各城市销售额chart.y_axis.title销售额chart.x_axis.title城市chart.style10# 数据范围dataReference(ws,min_col4,min_row1,max_col4,max_rowws.max_row)catsReference(ws,min_col2,min_row2,max_rowws.max_row)# 城市列chart.add_data(data,titles_from_dataTrue)chart.set_categories(cats)chart.shape4ws.add_chart(chart,F2)wb.save(销售报表_图表.xlsx)2. 折线图趋势fromopenpyxl.chartimportLineChart line_chartLineChart()line_chart.title月度销售趋势line_chart.style10line_chart.y_axis.title销售额line_chart.x_axis.title月份dataReference(ws,min_col4,min_row1,max_rowws.max_row)catsReference(ws,min_col1,min_row2,max_rowws.max_row)line_chart.add_data(data,titles_from_dataTrue)line_chart.set_categories(cats)ws.add_chart(line_chart,F20)3. 饼图占比fromopenpyxl.chartimportPieChart pie_chartPieChart()pie_chart.title品类占比pie_chart.style10dataReference(ws,min_col4,min_row1,max_rowws.max_row)catsReference(ws,min_col3,min_row2,max_rowws.max_row)pie_chart.add_data(data,titles_from_dataTrue)pie_chart.set_categories(cats)ws.add_chart(pie_chart,F38)五、批量处理多个 Excelimportosimportpandasaspddefmerge_excel_files(input_dir,output_file):合并整个文件夹的 Excel 文件all_data[]forfinos.listdir(input_dir):iff.endswith((.xlsx,.xls)):dfpd.read_excel(os.path.join(input_dir,f))df[来源文件]f# 标记来源all_data.append(df)print(f已读取:{f}({len(df)}行))resultpd.concat(all_data,ignore_indexTrue)withpd.ExcelWriter(output_file,engineopenpyxl)aswriter:result.to_excel(writer,sheet_name汇总数据,indexFalse)# 生成报表pivotpd.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. 读取数据dfpd.read_excel(data_file)monthdatetime.now().strftime(%Y年%m月)# 2. 生成透视表withpd.ExcelWriter(output_file,engineopenpyxl)aswriter:# 原始数据df.to_excel(writer,sheet_name数据源,indexFalse)# 按城市汇总city_pivotpd.pivot_table(df,values销售额,index城市,aggfunc[sum,count])city_pivot.to_excel(writer,sheet_name城市分析)# 按品类汇总cat_pivotpd.pivot_table(df,values销售额,index品类,aggfuncsum)cat_pivot.to_excel(writer,sheet_name品类分析)# 3. 美化 图表wbload_workbook(output_file)wswb[城市分析]# 表头样式header_fontFont(boldTrue,colorFFFFFF,size11)header_fillPatternFill(solid,fgColor4472C4)forcellinws[1]:cell.fontheader_font cell.fillheader_fill cell.alignmentAlignment(horizontalcenter)# 柱状图chartBarChart()chart.titlef{month}各城市销售额dataReference(ws,min_col2,min_row1,max_col2,max_rowws.max_row)catsReference(ws,min_col1,min_row2,max_rowws.max_row)chart.add_data(data,titles_from_dataTrue)chart.set_categories(cats)chart.width18;chart.height12ws.add_chart(chart,D2)wb.save(output_file)print(f月报已生成:{output_file})# 使用generate_monthly_report(6月销售数据.xlsx,6月销售报表.xlsx)七、常用样式速查# 字体Font(name微软雅黑,size11,boldTrue,italicFalse,colorFF0000)# 填充PatternFill(start_colorFFC000,end_colorFFC000,fill_typesolid)GradientFill(stop(FFFFFF,4472C4))# 对齐Alignment(horizontalcenter,verticalcenter,wrap_textTrue)# 边框thin_borderBorder(leftSide(stylethin),rightSide(stylethin),topSide(stylethin),bottomSide(stylethin),)# 行高列宽ws.row_dimensions[1].height30ws.column_dimensions[A].width15 觉得有用的话点赞 关注【张老师技术栈】吧每周更新 Java/Python/爬虫 实战干货不让你白来。