Python openpyxl操作Excel完整指南：10个高频场景附代码

Python openpyxl操作Excel完整指南10个高频场景附代码数据分析工作中Excel处理是绕不开的环节。手动操作费时费力用Python自动化才是正道。本文用10个实战场景带你掌握openpyxl的核心用法。一、环境准备openpyxl是Python操作xlsx文件最成熟的库之一。安装只需一行pip install openpyxl支持Python 3.6无需额外依赖。处理百万级数据建议用pandas日常几千到几十万行的Excel用openpyxl足够。二、10个高频实战场景场景1读取Excel数据from openpyxl import load_workbook # 加载已有文件 wb load_workbook(data/sales_2026.xlsx) ws wb.active # 获取活动工作表 # 按行读取数据 for row in ws.iter_rows(min_row2, values_onlyTrue): date, product, amount row[0], row[1], row[2] print(f{date} | {product} | {amount}) # 按列读取 for col in ws.iter_cols(min_col1, max_col3, values_onlyTrue): print(col)iter_rows是核心方法values_onlyTrue直接返回单元格值不含Cell对象效率更高。min_row2跳过表头。场景2创建新Excel并写入数据from openpyxl import Workbook wb Workbook() ws wb.active ws.title 销售数据 # 写入表头 headers [日期, 产品, 销售额, 利润率] ws.append(headers) # 写入数据行 data [ [2026-01-15, 产品A, 15200, 0.23], [2026-01-16, 产品B, 28400, 0.31], [2026-01-17, 产品A, 19600, 0.27], ] for row in data: ws.append(row) wb.save(output/sales_report.xlsx)场景3批量合并多个Excelimport os from openpyxl import load_workbook, Workbook # 找到目标目录下所有xlsx文件 files [f for f in os.listdir(data/) if f.endswith(.xlsx)] wb_out Workbook() ws_out wb_out.active ws_out.title 合并数据 # 写入表头从第一个文件读取 wb_first load_workbook(fdata/{files[0]}) ws_first wb_first.active ws_out.append([cell.value for cell in ws_first[1]]) # 合并所有文件的数据行跳过表头 for fname in files: wb load_workbook(fdata/{fname}) ws wb.active for row in ws.iter_rows(min_row2, values_onlyTrue): ws_out.append(row) wb_out.save(output/merged_all.xlsx) print(f合并完成共{len(files)}个文件)这是数据分析工作中最高频的需求之一。每月/每周各分公司提交报表合并分析是家常便饭。场景4设置单元格格式数字/百分比/日期from openpyxl.styles import numbers, Font, Alignment, PatternFill # 数字格式千分位分隔 ws[C2].number_format #,##0 # 百分比格式 ws[D2].number_format 0.0% # 日期格式 ws[A2].number_format YYYY-MM-DD # 字体设置 ws[A1].font Font(boldTrue, size14, colorFFFFFF) # 背景色 ws[A1].fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) # 居中对齐 ws[A1].alignment Alignment(horizontalcenter, verticalcenter)场景5条件格式自动高亮异常数据from openpyxl.formatting.rule import CellIsRule, FormulaRule from openpyxl.styles import PatternFill # 销售额低于10000的标红 red_fill PatternFill(start_colorFFC7CE, end_colorFFC7CE, fill_typesolid) ws.conditional_formatting.add( C2:C100, CellIsRule(operatorlessThan, formula[10000], fillred_fill) ) # 利润率低于20%的标黄 yellow_fill PatternFill(start_colorFFEB9C, end_colorFFEB9C, fill_typesolid) ws.conditional_formatting.add( D2:D100, CellIsRule(operatorlessThan, formula[0.2], fillyellow_fill) )条件格式让数据异常一目了然。领导看报表时不需要你解释颜色已经说明了问题。场景6按条件筛选数据并导出新表from openpyxl import Workbook, load_workbook wb load_workbook(data/sales_2026.xlsx) ws wb.active wb_out Workbook() ws_out wb_out.active ws_out.title 筛选产品A # 写表头 ws_out.append([cell.value for cell in ws[1]]) # 筛选产品A的数据 count 0 for row in ws.iter_rows(min_row2, values_onlyTrue): if row[1] 产品A: # 第2列是产品名 ws_out.append(row) count 1 wb_out.save(output/filtered_product_a.xlsx) print(f筛选完成共{count}条记录)场景7添加公式SUM/AVERAGE/VLOOKUP# 求和 ws[C101] fSUM(C2:C100) # 平均值 ws[C102] fAVERAGE(C2:C100) # 计数 ws[C103] fCOUNTA(C2:C100) # VLOOKUP跨表匹配 ws[E2] VLOOKUP(B2,产品表!A:B,2,FALSE) # 注意openpyxl写公式时用Excel标准语法 # 打开文件后Excel会自动计算不需要openpyxl执行场景8冻结窗格 自动筛选# 冻结首行滚动时表头不动 ws.freeze_panes A2 # 冻结前两行和第一列 # ws.freeze_panes B3 # 添加自动筛选表头下拉箭头 ws.auto_filter.ref A1:D100 # 设置列宽 ws.column_dimensions[A].width 15 ws.column_dimensions[B].width 20 ws.column_dimensions[C].width 15 ws.column_dimensions[D].width 12场景9图表生成柱状图折线图from openpyxl.chart import BarChart, LineChart, Reference # 柱状图各产品销售额 chart BarChart() chart.title 各产品销售额对比 chart.x_axis.title 产品 chart.y_axis.title 销售额元 data Reference(ws, min_col3, min_row1, max_row10) categories Reference(ws, min_col2, min_row2, max_row10) chart.add_data(data, titles_from_dataTrue) chart.set_categories(categories) ws.add_chart(chart, F2) # 折线图销售额趋势 line_chart LineChart() line_chart.title 月度销售趋势 line_chart.y_axis.title 销售额 data2 Reference(ws, min_col5, min_row1, max_row13) cats2 Reference(ws, min_col1, min_row2, max_row13) line_chart.add_data(data2, titles_from_dataTrue) line_chart.set_categories(cats2) ws.add_chart(line_chart, F18)场景10数据透视表效果分组汇总from collections import defaultdict # 按产品分组汇总 summary defaultdict(lambda: {sales: 0, count: 0}) for row in ws.iter_rows(min_row2, values_onlyTrue): product, amount row[1], row[2] if product and amount: summary[product][sales] amount summary[product][count] 1 # 写入汇总表 ws_out wb.create_sheet(汇总) ws_out.append([产品, 总销售额, 订单数, 平均客单价]) for product, stats in summary.items(): avg stats[sales] / stats[count] if stats[count] else 0 ws_out.append([product, stats[sales], stats[count], round(avg, 2)]) wb.save(output/sales_with_summary.xlsx)三、性能优化技巧当数据量超过1万行时openpyxl可能会变慢。几个优化技巧1. 只读模式加载大文件时用load_workbook(file.xlsx, read_onlyTrue)内存占用降低90%以上。2. 只写模式创建大文件时用Workbook(write_onlyTrue)逐行写入不缓存。3. 避免频繁访问Cell对象用iter_rows(values_onlyTrue)比逐个访问ws.cell()快5-10倍。4. 大数据量建议用pandas超过10万行直接pd.read_excel()处理写回用df.to_excel()。四、总结openpyxl是Python操作Excel的瑞士军刀。日常工作中90%的Excel处理需求用这10个场景就能覆盖读取数据 → 写入数据 → 合并文件 → 格式设置 → 条件格式 → 筛选导出 → 公式计算 → 冻结筛选 → 图表生成 → 分组汇总学会这些你的Excel处理效率至少提升10倍。省下来的时间去学点更有价值的东西。我是船长一个在数据分析领域干了近10年的实战派。关注我用最接地气的方式学数据分析。