Python处理Excel:openpyxl实战5个场景,从此告别手动复制粘贴
- 2026-11-09 00:00:02
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实战技巧和服务器运维经验。
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
