Python处理Excel：openpyxl实战5个场景，从此告别手动复制粘贴
在医院干了20年数据，我见过太多同事每天重复做同一件事：打开Excel，筛选，复制，粘贴到另一个表，保存。一上午就这么没了。
这些活儿其实几行Python就能搞定。今天把我实际在用的5个场景写出来，代码都是能直接跑的。
环境准备
pip install openpyxl
openpyxl 只处理 .xlsx 格式。如果你手里是老式的 .xls，先另存为 .xlsx，或者用 xlrd 读。
场景一：批量读取多个Excel，合并成一张表
最常见的需求：各科室每月报上来一个Excel，要把它们合并成总表。
import openpyxl
import os
from pathlib import Path
def merge_excels(folder, output_file):
"""把文件夹下所有xlsx合并成一个总表"""
wb_out = openpyxl.Workbook()
ws_out = wb_out.active
ws_out.title = "汇总"
header_written = False
total_rows = 0
for file in Path(folder).glob("*.xlsx"):
# 跳过输出文件本身
if file.name == os.path.basename(output_file):
continue
wb = openpyxl.load_workbook(file, read_only=True, data_only=True)
ws = wb.active
for i, row in enumerate(ws.iter_rows(values_only=True)):
# 第一个文件的第一行当表头
if not header_written:
ws_out.append(list(row))
header_written = True
continue
# 后续文件跳过表头
if i == 0:
continue
# 跳过空行
if any(cell is not None for cell in row):
ws_out.append(list(row))
total_rows += 1
wb.close()
print(f"已处理: {file.name}")
wb_out.save(output_file)
print(f"合并完成，共 {total_rows} 行 → {output_file}")
# 用法
merge_excels(r"D:\科室报表\2026年10月", r"D:\科室报表\汇总.xlsx")
关键点：
read_only=True只读模式，大文件快很多，内存占用也小data_only=True这个很重要——公式单元格会返回计算后的值，而不是公式本身。不加的话你读到的全是=SUM(A1:A10)这种字符串values_only=True直接返回值的元组，不用再.value
场景二：按条件拆分Excel（一个表拆成多个）
反过来也常用：一张全院明细，按科室拆成N个文件发给各科室。
import openpyxl
from collections import defaultdict
def split_by_column(src_file, col_index, output_dir):
"""按某一列的值拆分成多个Excel"""
wb = openpyxl.load_workbook(src_file, data_only=True)
ws = wb.active
# 先按分组收集数据
groups = defaultdict(list)
header = None
for i, row in enumerate(ws.iter_rows(values_only=True)):
if i == 0:
header = list(row)
continue
if row[col_index] is None:
continue
groups[str(row[col_index])].append(list(row))
# 每组写一个文件
os.makedirs(output_dir, exist_ok=True)
for key, rows in groups.items():
wb_new = openpyxl.Workbook()
ws_new = wb_new.active
ws_new.append(header)
for r in rows:
ws_new.append(r)
# 文件名里的非法字符要清掉
safe_key = "".join(c for c in key if c not in r'\/:*?"<>|')
out = os.path.join(output_dir, f"{safe_key}.xlsx")
wb_new.save(out)
print(f" {safe_key}: {len(rows)} 行")
print(f"拆分完成，共 {len(groups)} 个文件")
# 按第2列（索引1）拆分，也就是"科室"列
split_by_column(r"D:\全院明细.xlsx", 1, r"D:\按科室拆分")
这里我踩过坑：科室名称里带 / 或 * 会直接让保存失败，所以文件名必须过滤非法字符。
场景三：批量修改单元格 —— 医保数据清洗实战
这个是我真实在用的场景。医保报销明细里，金额列经常混进各种脏数据：有的带 ¥ 符号，有的是文本格式，有的带千分位逗号。
import openpyxl
import re
def clean_amount_column(src_file, col_letter, sheet_name=None):
"""把金额列洗成纯数字"""
wb = openpyxl.load_workbook(src_file)
ws = wb[sheet_name] if sheet_name else wb.active
fixed = 0
for row in range(2, ws.max_row + 1): # 从第2行开始，跳过表头
cell = ws[f"{col_letter}{row}"]
if cell.value is None:
continue
# 已经是数字就跳过
if isinstance(cell.value, (int, float)):
continue
raw = str(cell.value).strip()
# 去掉 ¥ 、千分位逗号、空格、全角字符
cleaned = raw.replace("¥", "").replace("￥", "")
cleaned = cleaned.replace(",", "").replace("，", "")
cleaned = cleaned.replace("元", "").strip()
# 提取数字部分（支持小数）
m = re.search(r'-?\d+\.?\d*', cleaned)
if m:
cell.value = float(m.group())
cell.number_format = '0.00' # 显示为两位小数
fixed += 1
else:
print(f" 第{row}行无法识别: {raw!r}")
cell.value = None
wb.save(src_file)
print(f"清洗完成，修正 {fixed} 个单元格")
clean_amount_column(r"D:\医保明细.xlsx", "F")
number_format 这一句别漏，不然金额显示成 1234.5 而不是 1234.50，财务看表会不舒服。
场景四：给Excel加样式 —— 让报表能直接交上去
程序处理完的表，交上去之前得能看。加个表头底色、冻结首行、自动列宽，观感立刻不一样。
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
def beautify_sheet(ws, header_row=1):
"""给工作表加通用样式"""
# 表头样式：蓝底白字加粗
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
header_font = Font(name="微软雅黑", size=11, bold=True, color="FFFFFF")
thin = Side(style="thin", color="BFBFBF")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
for cell in ws[header_row]:
cell.fill = header_fill
cell.font = header_font
cell.alignment = Alignment(horizontal="center", vertical="center")
cell.border = border
# 冻结表头行，滚动时表头不动
ws.freeze_panes = f"A{header_row + 1}"
# 自动列宽：按每列最长内容算
for col in ws.columns:
max_len = 0
col_letter = get_column_letter(col[0].column)
for cell in col:
if cell.value is not None:
# 中文按2个字符宽度算
text = str(cell.value)
length = sum(2 if ord(c) > 127 else 1 for c in text)
max_len = max(max_len, length)
ws.column_dimensions[col_letter].width = min(max_len + 2, 50)
# 开启自动筛选
ws.auto_filter.ref = ws.dimensions
# 用法
wb = openpyxl.load_workbook(r"D:\汇总.xlsx")
beautify_sheet(wb.active)
wb.save(r"D:\汇总_美化版.xlsx")
自动列宽那里我特意做了中文判断——中文字符在Excel里占两个英文字符的宽度，不这么算的话列宽不是太窄就是太宽。
场景五：生成图表
最后一公里。数据汇总完了，给领导看的得有图。
from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference
def add_chart(src_file, sheet_name="汇总"):
"""给数据加个柱状图"""
wb = openpyxl.load_workbook(src_file)
ws = wb[sheet_name]
# 假设：A列是科室名，B列是金额，数据从第2行到第20行
chart = BarChart()
chart.type = "col"
chart.title = "各科室费用对比"
chart.y_axis.title = "金额（元）"
chart.x_axis.title = "科室"
chart.height = 10
chart.width = 20
# 数据区域：B1是表头，B2:B20是数据
data = Reference(ws, min_col=2, min_row=1, max_row=20)
# 分类标签：A2:A20
cats = Reference(ws, min_col=1, min_row=2, max_row=20)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
# 放到 E2 位置
ws.add_chart(chart, "E2")
wb.save(src_file)
print("图表已生成")
add_chart(r"D:\汇总_美化版.xlsx")
Reference 那个 min_row=1 + titles_from_data=True 的组合是让他自动拿第一行当系列名称，别手写成 min_row=2 再自己补名字。
完整示例：一个脚本搞定月度报表
把上面几个串起来，这是我每个月跑一次的脚本：
import openpyxl
from pathlib import Path
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference
def monthly_report(folder, output):
# 1. 合并
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "月度汇总"
header = None
for f in Path(folder).glob("*.xlsx"):
src = openpyxl.load_workbook(f, read_only=True, data_only=True)
for i, row in enumerate(src.active.iter_rows(values_only=True)):
if header is None:
header = list(row)
ws.append(header)
continue
if i == 0:
continue
if any(c is not None for c in row):
ws.append(list(row))
src.close()
# 2. 样式
fill = PatternFill("solid", start_color="4472C4")
for cell in ws[1]:
cell.fill = fill
cell.font = Font(bold=True, color="FFFFFF")
cell.alignment = Alignment(horizontal="center")
ws.freeze_panes = "A2"
# 3. 图表
chart = BarChart()
chart.title = "月度数据分布"
chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=ws.max_row),
titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=ws.max_row))
ws.add_chart(chart, f"A{ws.max_row + 3}")
wb.save(output)
print(f"报表已生成: {output}")
monthly_report(r"D:\科室报表\10月", r"D:\科室报表\10月汇总.xlsx")
几个容易踩的坑
1. 忘记 data_only=True
读到的全是公式字符串。反过来，如果你用 data_only=True 打开一个从没被Excel打开过的文件，公式单元格会返回 None——因为缓存值还没生成。这时候要么先用Excel打开一次再保存，要么接受读不到值。
2. 修改后另存，别覆盖原文件
调试阶段务必存成新文件名。我有一次直接覆盖，源数据被改坏了，只能让科室重发。
3. 大文件用 read_only 模式
几万行的表，普通模式打开要好几秒还很吃内存。只读模式下基本秒开。
4. 日期读出来是数字
Excel的日期本质是数字。想正常显示，要么读的时候判断单元格的 number_format 是不是日期格式，要么直接用：
from datetime import datetime, timedelta
def excel_serial_to_date(serial):
"""Excel序列号转日期"""
if not isinstance(serial, (int, float)):
return serial
return datetime(1899, 12, 30) + timedelta(days=serial)
# 用法
print(excel_serial_to_date(45000)) # 2023-03-15
那个 1899-12-30 不是笔误，Excel有个著名的1900年闰年bug，基准日得这么算才对得上。
写在最后
这5个场景覆盖了日常90%的Excel处理需求。我的建议是别想着一口气写个万能工具，先把最烦的那个重复操作自动化掉，用起来之后自然会想优化第二步。
代码都在这了，复制过去改改路径就能跑。跑不通的地方欢迎来问我。
作者：雲澤 ｜ 医院IT运维，20年医院数据经验
关注我，每周分享Python实战技巧和服务器运维经验。
浙公网安备 33010602011771号