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_row=2, values_only=True): date, product, amount = row[0], row[1], row[2] print(f"{date} | {product} | {amount}")

按列读取

for col in ws.iter_cols(min_col=1, max_col=3, values_only=True): print(col) `

iter_rows是核心方法，values_only=True直接返回单元格值（不含Cell对象），效率更高。min_row=2跳过表头。

场景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：批量合并多个Excel

`import 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(f'data/{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(f'data/{fname}') ws = wb.active for row in ws.iter_rows(min_row=2, values_only=True): 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(bold=True, size=14, color='FFFFFF')

背景色

ws['A1'].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')

居中对齐

ws['A1'].alignment = Alignment(horizontal='center', vertical='center') `

场景5：条件格式（自动高亮异常数据）

`from openpyxl.formatting.rule import CellIsRule, FormulaRule from openpyxl.styles import PatternFill

销售额低于10000的标红

red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')

ws.conditional_formatting.add( 'C2:C100', CellIsRule(operator='lessThan', formula=['10000'], fill=red_fill) )

利润率低于20%的标黄

yellow_fill = PatternFill(start_color='FFEB9C', end_color='FFEB9C', fill_type='solid')

ws.conditional_formatting.add( 'D2:D100', CellIsRule(operator='lessThan', formula=['0.2'], fill=yellow_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_row=2, values_only=True): 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'] = f'=SUM(C2:C100)'

平均值

ws['C102'] = f'=AVERAGE(C2:C100)'

计数

ws['C103'] = f'=COUNTA(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_col=3, min_row=1, max_row=10) categories = Reference(ws, min_col=2, min_row=2, max_row=10) chart.add_data(data, titles_from_data=True) chart.set_categories(categories)

ws.add_chart(chart, "F2")

折线图：销售额趋势

line_chart = LineChart() line_chart.title = "月度销售趋势" line_chart.y_axis.title = "销售额" data2 = Reference(ws, min_col=5, min_row=1, max_row=13) cats2 = Reference(ws, min_col=1, min_row=2, max_row=13) line_chart.add_data(data2, titles_from_data=True) 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_row=2, values_only=True): 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_only=True)，内存占用降低90%以上。

**2. 只写模式：**创建大文件时用Workbook(write_only=True)，逐行写入不缓存。

**3. 避免频繁访问Cell对象：**用iter_rows(values_only=True)比逐个访问ws.cell()快5-10倍。

**4. 大数据量建议用pandas：**超过10万行，直接pd.read_excel()处理，写回用df.to_excel()。

四、总结

openpyxl是Python操作Excel的瑞士军刀。日常工作中，90%的Excel处理需求用这10个场景就能覆盖：

读取数据 → 写入数据 → 合并文件 → 格式设置 → 条件格式 → 筛选导出 → 公式计算 → 冻结筛选 → 图表生成 → 分组汇总

学会这些，你的Excel处理效率至少提升10倍。省下来的时间，去学点更有价值的东西。

我是船长，一个在数据分析领域干了近10年的实战派。关注我，用最接地气的方式学数据分析。