企业官网建设流程全解析

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年的实战派。关注我，用最接地气的方式学数据分析。