简介这是一份围绕 Python 操作 Excel 的代码示例包面向需要做数据读取、清洗、报表输出或自动化办公的初、中级开发者。压缩包内含 4 个 .py 文件整体约 6KB均为可直接参考的脚本示例分别对应基础读取、数值内容处理、按条件读取以及读写结合等练习场景。已有 704 人学习说明其作为入门到实操的参考材料具有一定实用性。内容重点落在 pandas 与 openpyxl 两个常用库既能了解 read_excel 读取表格、条件筛选行数据、to_excel 导出结果也能通过 load_workbook 遍历单元格、用 Workbook 新建工作簿并写入保存并延伸到公式、样式等更复杂表格的处理思路。对于想快速对照代码理解 Excel 读写流程、积累脚本模板的读者这份小而集中的示例包便于按文件逐个运行和修改适合作为日常数据处理任务的起点。1. Python 读取 Excel 并写入一个被低估的日常脏活财务同事甩过来一个 30MB 的 xlsx七个 sheet 里塞着三个月订单要求按区域汇总之后按模板导出一份带格式的日报。你解压python读取Excel并写入.zip里面没有花哨的框架就是一堆围绕 openpyxl 和 pandas 组织的 py 文件。这类资源的定位一点都不高级把单元格里的数据搬进内存洗干净再搬回去顺便让导出的表能直接给人看。但真要做扎实并不容易合并单元格、日期变成五位数序列号、写十万行时内存直接冲顶全是常见翻车现场。这份代码包适合两类人刚接手手工表格活、想找个能直接改的底子的开发以及想看看别人怎么把读和写拆成两条独立链路、再组装成流水线的熟手。下面按目录结构、读取链路、写入链路、排错、二次开发五个层次逐层拆。2. 项目结构读写两条链路为什么要物理隔离很多人写 Excel 脚本的习惯是pd.read_excel()之后接一串to_excel()十几行跑通交付时才发现需求变成了「表头要合并、日期要格式化、超过十万行不能卡死」。这时候再去改那十几行基本等于重写。这个包的第一条设计原则是把读写做成两个方向相反、互不知情的模块中间靠一层纯数据结构衔接后面加需求才不用动地基。2.1 目录骨架与各文件职责解压后的目录大致是下面这个形状reader.py和writer.py是两个端点中间夹着一个 schema 层负责「列叫什么、什么类型、叫什么别名」excel_io/ ├── excel_io/ │ ├── __init__.py │ ├── reader.py # 读取链路Workbook - 行数据 │ ├── writer.py # 写入链路行数据 - Workbook │ ├── schema.py # 列定义标准名 / 别名 / 类型 / 清洗函数 │ ├── transform.py # 聚合、去重、派生列纯函数不碰文件 │ ├── styles.py # 表头样式、列宽、冻结窗格、数字格式 │ └── cli.py # 命令行入口串起上面四块 ├── examples/ │ ├── raw/ # 待处理的原始表 │ └── out/ # 导出的结果表 ├── tests/ │ └── test_roundtrip.py ├── requirements.txt └── README.mdtransform.py单独存在的意义要强调一下。它只接受list[dict]返回list[dict]不接收路径、不接触 Workbook 对象所以能在测试里直接喂假数据一个 pytest 用例就能覆盖几十种边界。反过来如果清洗逻辑和load_workbook写在一个函数里想验证「金额字段带千分位逗号」这一条就得先造一个 xlsx 文件测试成本高到没人愿意写。cli.py是薄薄一层胶水做三件事解析参数、按配置调 reader、把结果交给 writer。它不承载任何业务规则这样后面换成 Web 接口或者定时任务可以直接绕过 CLI 调包内的函数。2.2 依赖清单与版本钉死的理由requirements.txt里真正必要的依赖不多但有一个必须锁版本openpyxl3.1 pandas2.0 xlrd1.2.0openpyxl 负责 xlsx 的读写、样式、合并单元格、write_only流式写这是主力。pandas 不是必须的但它在这个项目里承担两件事一是读取侧用read_excel快速拿到 DataFrame 做探查二是 transform 层做 groupby 聚合比手写字典循环省事。xlrd 锁在 1.2.0 是血泪经验——2.0 之后这个库主动砍掉了 xlsx 支持只保留老式 .xls。哪天遇到一个 2003 格式的历史文件没这个钉版本就会直接报Excel xlsx file; not supported。如果你确定手上全是 xlsx这行可以直接删掉少一个依赖少一份麻烦。还有一类可选依赖经常被忽略python-calamine。它是 Rust 写的读取引擎pandas 通过enginecalamine就能用在几十万行的读场景里速度比 openpyxl 快一个量级。我的建议是先不引入等你确实被读取耗时卡住了再加别一开始就把依赖树搞复杂。2.3 数据契约schema 层长什么样schema.py是整个项目的隐形骨架。原始表最恶心的地方在于表头不统一——同一列在 A 部门的表里叫「客户名称」在 B 部门的表里叫「客户名」还有叫「客户 名称」中间带空格的。把别名映射收敛到一个地方后面加新表只改数据不改代码from dataclasses import dataclass, field from typing import Callable, Optional dataclass class Column: name: str # 内部标准列名后续代码只认这个 aliases: list[str] field(default_factorylist) # 原始表里可能出现的写法 dtype: str str # str / int / float / date required: bool True # 缺失时是报错还是跳过 cleaner: Optional[Callable[[object], object]] None # 单值清洗钩子aliases匹配时会先做归一化去空格、全角转半角、去括号里的备注这样「金额元」和「金额」能落到同一列。cleaner是个单参数函数比如把「1,234.50」这种带千分位的字符串转成 float或者把「是/否」映射成布尔值。把清洗挂到列定义上而不是写进主流程好处是加字段时只加一行声明主流程一动不动。3. 读取链路拆解从磁盘字节到内存行数据读取这半边要处理的是「别人给的表格永远不合规范」这个前提。整个链路分四步打开工作簿、定位表头、逐行抽取并清洗、按 sheet 聚合返回。每一步都有对应的坑拆开看会清楚很多。3.1 打开工作簿三个参数决定后面所有行为不要在每个函数里裸调load_workbook统一收口到一个入口函数参数语义写死在 docstring 里from pathlib import Path from openpyxl import load_workbook from openpyxl.workbook.workbook import Workbook def open_workbook( path: str | Path, data_only: bool True, read_only: bool False, ) - Workbook: 统一的工作簿打开入口。 data_onlyTrue读到公式的缓存计算结果而不是 SUM(A1:A9) 字符串。 注意如果文件是脚本生成的、从未在 Excel 里保存过 缓存值为空此时读出来是 None。 read_onlyTrue流式解析内存占用极低但拿不到单元格样式 不能随机跳转且用完必须 wb.close()。 return load_workbook( filenamePath(path), data_onlydata_only, read_onlyread_only, keep_linksFalse, # 断开外部引用防止打开时去追一个不存在的网络路径 )data_only这个参数是最容易翻车的。同一个文件用 False 打开拿到的是公式文本用 True 打开拿到的是上次保存时 Excel 缓存的计算值。如果你的上游是用脚本生成的 xlsx比如另一个 openpyxl 脚本刚save()完那缓存值是空的data_onlyTrue会读出一片 None看起来像数据丢了。判断方法很简单用 Excel 手动打开一次再保存问题消失那就说明是缓存问题。read_onlyTrue是内存救命开关但它有两个限制要知道第一读到的单元格没有样式信息cell.font之类全是 None第二只能顺序遍历ws[A1]这种随机访问会退化成遍历整表。所以它适合「读大表取值」不适合「读大表判断格式」。3.2 表头定位不要假设第一行就是列名真实的业务表格第一行往往是大标题跨列合并第二行是制表日期第三行才是列名。硬编码header0迟早出问题写一个表头探测器更稳妥def locate_header(ws, max_scan: int 10) - int: 在前 max_scan 行里找最像表头的一行返回 1-based 行号。 判定规则非空单元格最多且文本型占比最高。 合并单元格在非左上角的格子里读出来是 None所以先做一次填充。 best_row, best_score 1, -1 for r in range(1, min(ws.max_row, max_scan) 1): cells [c.value for c in ws[r]] filled [v for v in cells if v is not None and str(v).strip() ! ] if len(filled) 2: # 只有一格内容的行基本是标题 continue text_ratio sum(isinstance(v, str) for v in filled) / len(filled) score len(filled) * text_ratio if score best_score: best_row, best_score r, score return best_row这里同步暴露一个坑合并单元格只有左上角那个格子有值其余是MergedCell对象.value为 None。如果表头是「客户信息」横跨三列后面两列读出来就是 None导致列名缺失。稳妥做法是在定位表头之前先读ws.merged_cells.ranges把合并区域的值铺满或者干脆在 schema 层用位置兜底。3.3 逐行抽取把清洗钩子挂上去拿到表头之后逐行构造字典同时用 schema 做别名归一和类型转换def iter_rows(ws, header_row: int, columns: list[Column]) - list[dict]: 按 schema 抽取数据行返回 dict 列表。 表头 - 标准列名的映射只算一次避免每行都重新匹配。 raw_headers [str(c.value).strip() if c.value is not None else for c in ws[header_row]] name_map: dict[int, Column] {} for idx, h in enumerate(raw_headers): norm _normalize(h) # 去空格、全角转半角 for col in columns: if norm _normalize(col.name) or norm in map(_normalize, col.aliases): name_map[idx] col break rows: list[dict] [] for cells in ws.iter_rows(min_rowheader_row 1, values_onlyTrue): if cells is None or all(v is None or str(v).strip() for v in cells): continue # 跳过尾部空行Excel 的 max_row 常偏大 record {} for idx, col in name_map.items(): value cells[idx] if idx len(cells) else None if col.cleaner: value col.cleaner(value) record[col.name] cast_value(value, col.dtype) missing [c.name for c in columns if c.required and record.get(c.name) is None] if missing: raise ValueError(f第 {ws.max_row} 行缺少必填列: {missing}) rows.append(record) return rowsiter_rows(values_onlyTrue)比逐格访问快很多原因是它跳过了 Cell 对象包装。min_rowheader_row 1直接跳过表头区省掉一次判断。必填列校验放在行级而不是表级报错信息里带上行号排查时不用大海捞针。cast_value里的日期处理要单独提一句。openpyxl 读出来的日期有时是datetime有时是数字单元格没设日期格式数字是按 1899-12-30 起算的序列号。稳妥写法是判断类型datetime直接用float/int且落在合理区间就用datetime(1899, 12, 30) timedelta(daysv)换算字符串则尝试几种常见格式解析。这个换算基准是 Excel 的历史遗留1900 年被当成闰年所以基准挪到了 12 月 30 日。3.4 多 sheet 调度与返回结构一个工作簿里可能有「汇总」「明细」「说明」多个 sheet其中说明页通常是纯文本不适合按 schema 抽。调度层用白名单 规则过滤def read_workbook(path, columns, sheet_filterNone): 返回 {sheet_name: [row_dict, ...]}。 sheet_filter 可以是 None全部、列表白名单 或一个 callable(ws) - bool用于按行数、表头内容过滤。 wb open_workbook(path, data_onlyTrue, read_onlyTrue) try: result {} for ws in wb.worksheets: if sheet_filter is not None and not _match(ws, sheet_filter): continue hr locate_header(ws) result[ws.title] iter_rows(ws, hr, columns) return result finally: wb.close() # read_only 模式必须关否则文件句柄不释放finally里的close()不是可选项。read_only 模式下 openpyxl 会保持文件句柄打开Windows 上会直接导致文件被占用、删不掉、改不了名是那种「明明代码没错但就是报 PermissionError」的玄学问题。另外注意ws.max_row在大文件里会偏大因为删过行但未重置所以空行跳过逻辑不能省。4. 写入链路让导出的表能直接交到人手上读进来洗完写出去这一步决定交付质量。很多人以为写 Excel 就是to_excel()结果同事打开一看列宽全挤在一起、日期显示成 2024-01-01 00:00:00、表头没有底色、数字变成科学计数法。写入模块要解决的问题一半是数据一半是观感。4.1 新建与追加两种模式不能混着写「新建」和「追加」是两个完全不同的场景混在一起写会互相拖累from openpyxl import Workbook, load_workbook def write_new(rows: list[dict], headers: list[str], out_path: str, sheet_name结果): 从零生成一个工作簿。表头顺序由 headers 显式给定 不从 rows[0].keys() 推断——字典顺序在某些构造方式下不可靠。 wb Workbook() ws wb.active ws.title sheet_name ws.append(headers) for r in rows: ws.append([r.get(h) for h in headers]) wb.save(out_path) def append_to_existing(rows: list[dict], headers: list[str], out_path: str): 向已有文件的末尾追加数据保留原有格式。 注意 load_workbook 默认会重写整个文件文件很大时这一步很慢。 wb load_workbook(out_path) ws wb.worksheets[0] for r in rows: ws.append([r.get(h) for h in headers]) wb.save(out_path)追加模式有个隐藏成本openpyxl 保存时会重写整个文件一个 50MB 的表追加 100 行耗时可能十几秒。如果追加频率高正确的做法不是反复打开保存而是把增量写成一个新的 sheet 或者 CSV 落地定期合并。这一点在开发阶段没感觉上了定时任务才会暴露。4.2 样式控制列宽、冻结、数字格式样式代码写起来琐碎但收益直接。下面这三个函数基本覆盖了日常需求from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter def style_header(ws, row: int 1): fill PatternFill(solid, fgColorDDEBF7) for cell in ws[row]: cell.font Font(boldTrue, size11) cell.fill fill cell.alignment Alignment(horizontalcenter, verticalcenter, wrap_textTrue) ws.freeze_panes ws.cell(rowrow 1, column1) # 冻结表头滚动时列名不跑 def autofit_columns(ws, min_w: int 8, max_w: int 40): 按内容估算列宽。中文按 2 个字符宽度计 直接 len() 会让所有中文列被压扁成一个字宽。 widths: dict[str, int] {} for row in ws.iter_rows(): for cell in row: if cell.value is None: continue w sum(2 if ord(ch) 127 else 1 for ch in str(cell.value)) widths[cell.column_letter] max(widths.get(cell.column_letter, 0), w) for letter, w in widths.items(): ws.column_dimensions[letter].width min(max(w 2, min_w), max_w) def set_number_format(ws, col_letter: str, fmt: str #,##0.00): 给整列设置数字格式fmt 用 Excel 的原生格式串 比如金额 #,##0.00、日期 yyyy-mm-dd、百分比 0.0%。 for cell in ws[col_letter]: cell.number_format fmtautofit_columns里那段按 ord 判断宽度的写法是个小技巧。按字符数算宽度的方案在纯英文表里没问题一旦有中文实际渲染宽度差不多是英文的两倍min(max(w2))的收口又避免了某个超长备注列把整表撑到屏幕外。freeze_panes的赋值用ws.cell(rowrow1, column1)而不是字符串A2好处是表头行号变化时不用改代码。这个参数的含义是「冻结此单元格上方和左侧的所有行列」所以传 A2 就是冻结第一行。4.3 大文件写入write_only 模式与内存曲线普通模式下每个 Cell 都是一个 Python 对象十万行乘二十列就是两百万个对象内存轻松上到 1GB 以上。超过五万行就该切到流式模式from openpyxl import Workbook def stream_write(rows, headers, out_path: str): 流式写内存占用与行数几乎无关。 代价不能读回单元格、不能用 ws.cell()、不能随机定位、样式只能在 append 前设。 wb Workbook(write_onlyTrue) ws wb.create_sheet(结果) ws.append(headers) for r in rows: ws.append([r.get(h) for h in headers]) wb.save(out_path)流式模式的核心限制是「只进不退」往ws里 append 的数据会立刻刷到临时文件你没法再回头改某一格。所以列宽这类设置只能在 append 表头之前用ws.column_dimensions设好而单元格样式要在 append 的时候传WriteOnlyCell对象进去。这是写入侧最容易翻车的地方——代码跑了没报错但打开文件发现样式一个都没生效。判断该用哪种模式我的经验阈值是行数 × 列数小于 50 万用普通模式大于就上 write_only如果还需要复杂样式和公式那宁可先写数据再开一个普通模式补样式分两步走。两害相权分两步虽然慢一点但代码逻辑清楚得多。5. 避坑与排查那些让脚本跑一半就崩的细节读写 Excel 的坑有个共同特点报错信息极其模糊TypeError: MergedCell object attribute value is read-only这种还算友好的更多时候是数据静默错了你导出的日报里金额全是零但程序一路绿灯。下面按读取侧、写入侧、排查手法三块记录几条最高频的。5.1 读取侧的四个高频翻车点现象日期列读出来是 45292 这样的整数。原因是单元格本身没有设日期格式openpyxl 只能按存储值返回。解决方法是判断出数字型且列语义是日期时按datetime(1899, 12, 30) timedelta(daysv)手动换算同时在 schema 里把这一列dtype标成date让转换只在必要的地方发生。现象ValueError: Cannot convert 0.0 to Excel或者整列数据凭空少了一半。原因通常是表头行定位错了探测器把一行合并标题当成了表头导致后续所有列的索引全部偏移。解决方法是把locate_header的返回行号打日志人工核对一次如果表结构稳定干脆在配置里写死header_row别让程序猜。现象读出来的数字精度掉了1234567.89变成1234567.8899999。这是浮点存储的固有问题Excel 内部也是双精度。如果业务涉及金额正确做法是在 schema 里把该列当成Decimal处理或者读出后立刻round(v, 2)并全程用整数分存储。数量级越大越要注意别等到对账差一分钱才发现。现象openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support the old .xls file format。拿到的是老格式文件。解决方法是引入xlrd1.2.0单独走一条分支读取后转成同样的行字典结构让下游代码无感知。别试图用 openpyxl 硬扛它从设计上就不支持。5.2 写入侧的三类静默错误现象导出的文件用 Excel 打开提示「发现不可读取的内容」点修复后格式全丢。原因多半是往合并单元格的非左上角格子写值或者写入的字符串以开头被当成公式解析。前者需要写之前检查ws.merged_cells后者要在写入时给这类字符串加个前导单引号或强制cell.data_type s。现象write_only模式下样式全丢列宽设置不生效。原因前面提过流式模式只进不退。解决办法是把列宽设置放在ws.append(headers)之前单元格样式用WriteOnlyCell包装后再 append。这个错误的迷惑性在于程序不报任何异常必须打开文件肉眼验证。现象程序结束但目标文件是 0 字节或者打开报损坏。常见于异常路径没走到wb.save()或者两个进程同时写同一个文件。解决方法是把 save 放在try/finally外层的正常路径上同时写入时先写.tmp临时文件成功后os.replace()原子替换避免下游读到半个文件。5.3 排查手法最小复现与中间态落盘遇到「数据不对但程序不报错」的情况我一般按这个顺序排查。第一步是把出问题的那一行单独抠出来存成一个只有两三行的小 xlsx用它跑读取逻辑排除大文件干扰。第二步是在iter_rows返回之后、transform之前把结果json.dump到磁盘看一眼中间态很多问题到这一步就现形了——比如某一列全是 None一看就知道是别名没匹配上。第三步是核对表头把raw_headers和name_map一起打出来确认 schema 里声明的列真的被识别到了。还有一个工具性的习惯写完文件之后不要只信wb.save()没报错用load_workbook(out, read_onlyTrue)重新打开读一遍行数和前几行做一次回环校验。这一步能拦住绝大部分「保存成功但内容错位」的问题成本只有几百毫秒。6. 二次开发把它改造成一条可配置的报表流水线这份代码包跑通示例之后最有价值的改造方向不是加功能而是把它从「一个脚本」变成「一条能被别人调用的流水线」。我在几个项目里反复验证过改造分三步走性价比最高。6.1 先抽接口再谈扩展把 reader 和 writer 收敛成两个协议后面换实现换成 CSV、换成数据库、换成在线表格 API时上层代码不用动from typing import Protocol, Iterable class TableReader(Protocol): def read(self, source: str) - dict[str, list[dict]]: ... class TableWriter(Protocol): def write(self, rows: list[dict], target: str) - None: ...有了这层协议cli.py里就只依赖接口具体是 openpyxl 实现还是 pandas 实现由配置决定。这一步的收益在半年后才体现某天上游要求支持 CSV 输入你只需要新增一个CsvReader主流程一行不改。6.2 用配置驱动字段映射别写死在代码里表头别名、必填校验、清洗规则这些内容会随业务方变化而频繁调整把它们从 Python 挪到 YAML改一次字段就不用发版sheet: 明细 header_row: 3 columns: - name: 客户名称 aliases: [客户名, 客户 名称, 客户] dtype: str required: true - name: 订单金额 aliases: [金额, 金额元] dtype: decimal required: true cleaner: strip_thousand_sep # 指向 excel_io.cleaners 里的函数名加载配置时用importlib按名字找 cleaner找不到就报错退出别静默降级成不处理——清洗函数没生效比程序崩溃更难查。6.3 验证方法拿一份黄金文件做回归改造过程中最怕的是「改完之后结果看起来差不多但某个字段错了」。我的做法是准备一份人工核对过的黄金文件输入 期望输出写一个对比测试读入原始表、跑完整流水线、把输出读回来和黄金输出逐行逐列比对差异超过阈值就失败。对比维度用什么比失败时的信号行数len(actual) len(expected)过滤条件改了但没同步列集合比对set(keys)schema 漏配或别名失效数值列Decimal精确比较精度丢失或单位换算错日期列统一转 ISO 字符串比较时区或序列号基准错文本列去首尾空格后比较清洗钩子没挂上表格里的日期列那一条要单独说直接比datetime对象容易因为时区或微秒差异误报统一转成YYYY-MM-DD再比误报率立刻降下来。6.4 一个具体技巧给写入加幂等键如果这条流水线要定时跑重复执行时必须保证输出不重复累积。最省事的做法不是每次清空重写而是在写入前按业务主键去重def upsert(rows, existing, key订单号): 以 key 为准做覆盖合并顺序保持 existing 在前、新增在后。 index {r[key]: i for i, r in enumerate(existing)} result list(existing) for r in rows: if r[key] in index: result[index[r[key]]] r else: index[r[key]] len(result) result.append(r) return result这个函数看着简单但它把「每次跑都追加」变成了「每次跑都对齐」定时任务重跑一次不会让报表翻倍。我真吃过这个亏——一个每天早上八点跑的脚本某天因为上游延迟手动补跑了一次当天报表金额直接翻倍财务拿着表来问的时候还挺尴尬。从那以后我每次做定时导出都强制走一遍幂等校验哪怕是看起来绝不会重复的单次任务。希望这些拆解能帮到你先把示例跑通再按自己的表结构改 schema剩下的就是填业务规则了。本文还有配套的精品资源点击获取 
长河君 · 2026/10/30 3:23:16 · 阅读 1.2k Python读写Excel实战：openpyxl/pandas双链路与避坑
简介这是一份围绕 Python 操作 Excel 的代码示例包面向需要做数据读取、清洗、报表输出或自动化办公的初、中级开发者。压缩包内含 4 个 .py 文件整体约 6KB均为可直接参考的脚本示例分别对应基础读取、数值内容处理、按条件读取以及读写结合等练习场景。已有 704 人学习说明其作为入门到实操的参考材料具有一定实用性。内容重点落在 pandas 与 openpyxl 两个常用库既能了解 read_excel 读取表格、条件筛选行数据、to_excel 导出结果也能通过 load_workbook 遍历单元格、用 Workbook 新建工作簿并写入保存并延伸到公式、样式等更复杂表格的处理思路。对于想快速对照代码理解 Excel 读写流程、积累脚本模板的读者这份小而集中的示例包便于按文件逐个运行和修改适合作为日常数据处理任务的起点。1. Python 读取 Excel 并写入一个被低估的日常脏活财务同事甩过来一个 30MB 的 xlsx七个 sheet 里塞着三个月订单要求按区域汇总之后按模板导出一份带格式的日报。你解压python读取Excel并写入.zip里面没有花哨的框架就是一堆围绕 openpyxl 和 pandas 组织的 py 文件。这类资源的定位一点都不高级把单元格里的数据搬进内存洗干净再搬回去顺便让导出的表能直接给人看。但真要做扎实并不容易合并单元格、日期变成五位数序列号、写十万行时内存直接冲顶全是常见翻车现场。这份代码包适合两类人刚接手手工表格活、想找个能直接改的底子的开发以及想看看别人怎么把读和写拆成两条独立链路、再组装成流水线的熟手。下面按目录结构、读取链路、写入链路、排错、二次开发五个层次逐层拆。2. 项目结构读写两条链路为什么要物理隔离很多人写 Excel 脚本的习惯是pd.read_excel()之后接一串to_excel()十几行跑通交付时才发现需求变成了「表头要合并、日期要格式化、超过十万行不能卡死」。这时候再去改那十几行基本等于重写。这个包的第一条设计原则是把读写做成两个方向相反、互不知情的模块中间靠一层纯数据结构衔接后面加需求才不用动地基。2.1 目录骨架与各文件职责解压后的目录大致是下面这个形状reader.py和writer.py是两个端点中间夹着一个 schema 层负责「列叫什么、什么类型、叫什么别名」excel_io/ ├── excel_io/ │ ├── __init__.py │ ├── reader.py # 读取链路Workbook - 行数据 │ ├── writer.py # 写入链路行数据 - Workbook │ ├── schema.py # 列定义标准名 / 别名 / 类型 / 清洗函数 │ ├── transform.py # 聚合、去重、派生列纯函数不碰文件 │ ├── styles.py # 表头样式、列宽、冻结窗格、数字格式 │ └── cli.py # 命令行入口串起上面四块 ├── examples/ │ ├── raw/ # 待处理的原始表 │ └── out/ # 导出的结果表 ├── tests/ │ └── test_roundtrip.py ├── requirements.txt └── README.mdtransform.py单独存在的意义要强调一下。它只接受list[dict]返回list[dict]不接收路径、不接触 Workbook 对象所以能在测试里直接喂假数据一个 pytest 用例就能覆盖几十种边界。反过来如果清洗逻辑和load_workbook写在一个函数里想验证「金额字段带千分位逗号」这一条就得先造一个 xlsx 文件测试成本高到没人愿意写。cli.py是薄薄一层胶水做三件事解析参数、按配置调 reader、把结果交给 writer。它不承载任何业务规则这样后面换成 Web 接口或者定时任务可以直接绕过 CLI 调包内的函数。2.2 依赖清单与版本钉死的理由requirements.txt里真正必要的依赖不多但有一个必须锁版本openpyxl3.1 pandas2.0 xlrd1.2.0openpyxl 负责 xlsx 的读写、样式、合并单元格、write_only流式写这是主力。pandas 不是必须的但它在这个项目里承担两件事一是读取侧用read_excel快速拿到 DataFrame 做探查二是 transform 层做 groupby 聚合比手写字典循环省事。xlrd 锁在 1.2.0 是血泪经验——2.0 之后这个库主动砍掉了 xlsx 支持只保留老式 .xls。哪天遇到一个 2003 格式的历史文件没这个钉版本就会直接报Excel xlsx file; not supported。如果你确定手上全是 xlsx这行可以直接删掉少一个依赖少一份麻烦。还有一类可选依赖经常被忽略python-calamine。它是 Rust 写的读取引擎pandas 通过enginecalamine就能用在几十万行的读场景里速度比 openpyxl 快一个量级。我的建议是先不引入等你确实被读取耗时卡住了再加别一开始就把依赖树搞复杂。2.3 数据契约schema 层长什么样schema.py是整个项目的隐形骨架。原始表最恶心的地方在于表头不统一——同一列在 A 部门的表里叫「客户名称」在 B 部门的表里叫「客户名」还有叫「客户 名称」中间带空格的。把别名映射收敛到一个地方后面加新表只改数据不改代码from dataclasses import dataclass, field from typing import Callable, Optional dataclass class Column: name: str # 内部标准列名后续代码只认这个 aliases: list[str] field(default_factorylist) # 原始表里可能出现的写法 dtype: str str # str / int / float / date required: bool True # 缺失时是报错还是跳过 cleaner: Optional[Callable[[object], object]] None # 单值清洗钩子aliases匹配时会先做归一化去空格、全角转半角、去括号里的备注这样「金额元」和「金额」能落到同一列。cleaner是个单参数函数比如把「1,234.50」这种带千分位的字符串转成 float或者把「是/否」映射成布尔值。把清洗挂到列定义上而不是写进主流程好处是加字段时只加一行声明主流程一动不动。3. 读取链路拆解从磁盘字节到内存行数据读取这半边要处理的是「别人给的表格永远不合规范」这个前提。整个链路分四步打开工作簿、定位表头、逐行抽取并清洗、按 sheet 聚合返回。每一步都有对应的坑拆开看会清楚很多。3.1 打开工作簿三个参数决定后面所有行为不要在每个函数里裸调load_workbook统一收口到一个入口函数参数语义写死在 docstring 里from pathlib import Path from openpyxl import load_workbook from openpyxl.workbook.workbook import Workbook def open_workbook( path: str | Path, data_only: bool True, read_only: bool False, ) - Workbook: 统一的工作簿打开入口。 data_onlyTrue读到公式的缓存计算结果而不是 SUM(A1:A9) 字符串。 注意如果文件是脚本生成的、从未在 Excel 里保存过 缓存值为空此时读出来是 None。 read_onlyTrue流式解析内存占用极低但拿不到单元格样式 不能随机跳转且用完必须 wb.close()。 return load_workbook( filenamePath(path), data_onlydata_only, read_onlyread_only, keep_linksFalse, # 断开外部引用防止打开时去追一个不存在的网络路径 )data_only这个参数是最容易翻车的。同一个文件用 False 打开拿到的是公式文本用 True 打开拿到的是上次保存时 Excel 缓存的计算值。如果你的上游是用脚本生成的 xlsx比如另一个 openpyxl 脚本刚save()完那缓存值是空的data_onlyTrue会读出一片 None看起来像数据丢了。判断方法很简单用 Excel 手动打开一次再保存问题消失那就说明是缓存问题。read_onlyTrue是内存救命开关但它有两个限制要知道第一读到的单元格没有样式信息cell.font之类全是 None第二只能顺序遍历ws[A1]这种随机访问会退化成遍历整表。所以它适合「读大表取值」不适合「读大表判断格式」。3.2 表头定位不要假设第一行就是列名真实的业务表格第一行往往是大标题跨列合并第二行是制表日期第三行才是列名。硬编码header0迟早出问题写一个表头探测器更稳妥def locate_header(ws, max_scan: int 10) - int: 在前 max_scan 行里找最像表头的一行返回 1-based 行号。 判定规则非空单元格最多且文本型占比最高。 合并单元格在非左上角的格子里读出来是 None所以先做一次填充。 best_row, best_score 1, -1 for r in range(1, min(ws.max_row, max_scan) 1): cells [c.value for c in ws[r]] filled [v for v in cells if v is not None and str(v).strip() ! ] if len(filled) 2: # 只有一格内容的行基本是标题 continue text_ratio sum(isinstance(v, str) for v in filled) / len(filled) score len(filled) * text_ratio if score best_score: best_row, best_score r, score return best_row这里同步暴露一个坑合并单元格只有左上角那个格子有值其余是MergedCell对象.value为 None。如果表头是「客户信息」横跨三列后面两列读出来就是 None导致列名缺失。稳妥做法是在定位表头之前先读ws.merged_cells.ranges把合并区域的值铺满或者干脆在 schema 层用位置兜底。3.3 逐行抽取把清洗钩子挂上去拿到表头之后逐行构造字典同时用 schema 做别名归一和类型转换def iter_rows(ws, header_row: int, columns: list[Column]) - list[dict]: 按 schema 抽取数据行返回 dict 列表。 表头 - 标准列名的映射只算一次避免每行都重新匹配。 raw_headers [str(c.value).strip() if c.value is not None else for c in ws[header_row]] name_map: dict[int, Column] {} for idx, h in enumerate(raw_headers): norm _normalize(h) # 去空格、全角转半角 for col in columns: if norm _normalize(col.name) or norm in map(_normalize, col.aliases): name_map[idx] col break rows: list[dict] [] for cells in ws.iter_rows(min_rowheader_row 1, values_onlyTrue): if cells is None or all(v is None or str(v).strip() for v in cells): continue # 跳过尾部空行Excel 的 max_row 常偏大 record {} for idx, col in name_map.items(): value cells[idx] if idx len(cells) else None if col.cleaner: value col.cleaner(value) record[col.name] cast_value(value, col.dtype) missing [c.name for c in columns if c.required and record.get(c.name) is None] if missing: raise ValueError(f第 {ws.max_row} 行缺少必填列: {missing}) rows.append(record) return rowsiter_rows(values_onlyTrue)比逐格访问快很多原因是它跳过了 Cell 对象包装。min_rowheader_row 1直接跳过表头区省掉一次判断。必填列校验放在行级而不是表级报错信息里带上行号排查时不用大海捞针。cast_value里的日期处理要单独提一句。openpyxl 读出来的日期有时是datetime有时是数字单元格没设日期格式数字是按 1899-12-30 起算的序列号。稳妥写法是判断类型datetime直接用float/int且落在合理区间就用datetime(1899, 12, 30) timedelta(daysv)换算字符串则尝试几种常见格式解析。这个换算基准是 Excel 的历史遗留1900 年被当成闰年所以基准挪到了 12 月 30 日。3.4 多 sheet 调度与返回结构一个工作簿里可能有「汇总」「明细」「说明」多个 sheet其中说明页通常是纯文本不适合按 schema 抽。调度层用白名单 规则过滤def read_workbook(path, columns, sheet_filterNone): 返回 {sheet_name: [row_dict, ...]}。 sheet_filter 可以是 None全部、列表白名单 或一个 callable(ws) - bool用于按行数、表头内容过滤。 wb open_workbook(path, data_onlyTrue, read_onlyTrue) try: result {} for ws in wb.worksheets: if sheet_filter is not None and not _match(ws, sheet_filter): continue hr locate_header(ws) result[ws.title] iter_rows(ws, hr, columns) return result finally: wb.close() # read_only 模式必须关否则文件句柄不释放finally里的close()不是可选项。read_only 模式下 openpyxl 会保持文件句柄打开Windows 上会直接导致文件被占用、删不掉、改不了名是那种「明明代码没错但就是报 PermissionError」的玄学问题。另外注意ws.max_row在大文件里会偏大因为删过行但未重置所以空行跳过逻辑不能省。4. 写入链路让导出的表能直接交到人手上读进来洗完写出去这一步决定交付质量。很多人以为写 Excel 就是to_excel()结果同事打开一看列宽全挤在一起、日期显示成 2024-01-01 00:00:00、表头没有底色、数字变成科学计数法。写入模块要解决的问题一半是数据一半是观感。4.1 新建与追加两种模式不能混着写「新建」和「追加」是两个完全不同的场景混在一起写会互相拖累from openpyxl import Workbook, load_workbook def write_new(rows: list[dict], headers: list[str], out_path: str, sheet_name结果): 从零生成一个工作簿。表头顺序由 headers 显式给定 不从 rows[0].keys() 推断——字典顺序在某些构造方式下不可靠。 wb Workbook() ws wb.active ws.title sheet_name ws.append(headers) for r in rows: ws.append([r.get(h) for h in headers]) wb.save(out_path) def append_to_existing(rows: list[dict], headers: list[str], out_path: str): 向已有文件的末尾追加数据保留原有格式。 注意 load_workbook 默认会重写整个文件文件很大时这一步很慢。 wb load_workbook(out_path) ws wb.worksheets[0] for r in rows: ws.append([r.get(h) for h in headers]) wb.save(out_path)追加模式有个隐藏成本openpyxl 保存时会重写整个文件一个 50MB 的表追加 100 行耗时可能十几秒。如果追加频率高正确的做法不是反复打开保存而是把增量写成一个新的 sheet 或者 CSV 落地定期合并。这一点在开发阶段没感觉上了定时任务才会暴露。4.2 样式控制列宽、冻结、数字格式样式代码写起来琐碎但收益直接。下面这三个函数基本覆盖了日常需求from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter def style_header(ws, row: int 1): fill PatternFill(solid, fgColorDDEBF7) for cell in ws[row]: cell.font Font(boldTrue, size11) cell.fill fill cell.alignment Alignment(horizontalcenter, verticalcenter, wrap_textTrue) ws.freeze_panes ws.cell(rowrow 1, column1) # 冻结表头滚动时列名不跑 def autofit_columns(ws, min_w: int 8, max_w: int 40): 按内容估算列宽。中文按 2 个字符宽度计 直接 len() 会让所有中文列被压扁成一个字宽。 widths: dict[str, int] {} for row in ws.iter_rows(): for cell in row: if cell.value is None: continue w sum(2 if ord(ch) 127 else 1 for ch in str(cell.value)) widths[cell.column_letter] max(widths.get(cell.column_letter, 0), w) for letter, w in widths.items(): ws.column_dimensions[letter].width min(max(w 2, min_w), max_w) def set_number_format(ws, col_letter: str, fmt: str #,##0.00): 给整列设置数字格式fmt 用 Excel 的原生格式串 比如金额 #,##0.00、日期 yyyy-mm-dd、百分比 0.0%。 for cell in ws[col_letter]: cell.number_format fmtautofit_columns里那段按 ord 判断宽度的写法是个小技巧。按字符数算宽度的方案在纯英文表里没问题一旦有中文实际渲染宽度差不多是英文的两倍min(max(w2))的收口又避免了某个超长备注列把整表撑到屏幕外。freeze_panes的赋值用ws.cell(rowrow1, column1)而不是字符串A2好处是表头行号变化时不用改代码。这个参数的含义是「冻结此单元格上方和左侧的所有行列」所以传 A2 就是冻结第一行。4.3 大文件写入write_only 模式与内存曲线普通模式下每个 Cell 都是一个 Python 对象十万行乘二十列就是两百万个对象内存轻松上到 1GB 以上。超过五万行就该切到流式模式from openpyxl import Workbook def stream_write(rows, headers, out_path: str): 流式写内存占用与行数几乎无关。 代价不能读回单元格、不能用 ws.cell()、不能随机定位、样式只能在 append 前设。 wb Workbook(write_onlyTrue) ws wb.create_sheet(结果) ws.append(headers) for r in rows: ws.append([r.get(h) for h in headers]) wb.save(out_path)流式模式的核心限制是「只进不退」往ws里 append 的数据会立刻刷到临时文件你没法再回头改某一格。所以列宽这类设置只能在 append 表头之前用ws.column_dimensions设好而单元格样式要在 append 的时候传WriteOnlyCell对象进去。这是写入侧最容易翻车的地方——代码跑了没报错但打开文件发现样式一个都没生效。判断该用哪种模式我的经验阈值是行数 × 列数小于 50 万用普通模式大于就上 write_only如果还需要复杂样式和公式那宁可先写数据再开一个普通模式补样式分两步走。两害相权分两步虽然慢一点但代码逻辑清楚得多。5. 避坑与排查那些让脚本跑一半就崩的细节读写 Excel 的坑有个共同特点报错信息极其模糊TypeError: MergedCell object attribute value is read-only这种还算友好的更多时候是数据静默错了你导出的日报里金额全是零但程序一路绿灯。下面按读取侧、写入侧、排查手法三块记录几条最高频的。5.1 读取侧的四个高频翻车点现象日期列读出来是 45292 这样的整数。原因是单元格本身没有设日期格式openpyxl 只能按存储值返回。解决方法是判断出数字型且列语义是日期时按datetime(1899, 12, 30) timedelta(daysv)手动换算同时在 schema 里把这一列dtype标成date让转换只在必要的地方发生。现象ValueError: Cannot convert 0.0 to Excel或者整列数据凭空少了一半。原因通常是表头行定位错了探测器把一行合并标题当成了表头导致后续所有列的索引全部偏移。解决方法是把locate_header的返回行号打日志人工核对一次如果表结构稳定干脆在配置里写死header_row别让程序猜。现象读出来的数字精度掉了1234567.89变成1234567.8899999。这是浮点存储的固有问题Excel 内部也是双精度。如果业务涉及金额正确做法是在 schema 里把该列当成Decimal处理或者读出后立刻round(v, 2)并全程用整数分存储。数量级越大越要注意别等到对账差一分钱才发现。现象openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support the old .xls file format。拿到的是老格式文件。解决方法是引入xlrd1.2.0单独走一条分支读取后转成同样的行字典结构让下游代码无感知。别试图用 openpyxl 硬扛它从设计上就不支持。5.2 写入侧的三类静默错误现象导出的文件用 Excel 打开提示「发现不可读取的内容」点修复后格式全丢。原因多半是往合并单元格的非左上角格子写值或者写入的字符串以开头被当成公式解析。前者需要写之前检查ws.merged_cells后者要在写入时给这类字符串加个前导单引号或强制cell.data_type s。现象write_only模式下样式全丢列宽设置不生效。原因前面提过流式模式只进不退。解决办法是把列宽设置放在ws.append(headers)之前单元格样式用WriteOnlyCell包装后再 append。这个错误的迷惑性在于程序不报任何异常必须打开文件肉眼验证。现象程序结束但目标文件是 0 字节或者打开报损坏。常见于异常路径没走到wb.save()或者两个进程同时写同一个文件。解决方法是把 save 放在try/finally外层的正常路径上同时写入时先写.tmp临时文件成功后os.replace()原子替换避免下游读到半个文件。5.3 排查手法最小复现与中间态落盘遇到「数据不对但程序不报错」的情况我一般按这个顺序排查。第一步是把出问题的那一行单独抠出来存成一个只有两三行的小 xlsx用它跑读取逻辑排除大文件干扰。第二步是在iter_rows返回之后、transform之前把结果json.dump到磁盘看一眼中间态很多问题到这一步就现形了——比如某一列全是 None一看就知道是别名没匹配上。第三步是核对表头把raw_headers和name_map一起打出来确认 schema 里声明的列真的被识别到了。还有一个工具性的习惯写完文件之后不要只信wb.save()没报错用load_workbook(out, read_onlyTrue)重新打开读一遍行数和前几行做一次回环校验。这一步能拦住绝大部分「保存成功但内容错位」的问题成本只有几百毫秒。6. 二次开发把它改造成一条可配置的报表流水线这份代码包跑通示例之后最有价值的改造方向不是加功能而是把它从「一个脚本」变成「一条能被别人调用的流水线」。我在几个项目里反复验证过改造分三步走性价比最高。6.1 先抽接口再谈扩展把 reader 和 writer 收敛成两个协议后面换实现换成 CSV、换成数据库、换成在线表格 API时上层代码不用动from typing import Protocol, Iterable class TableReader(Protocol): def read(self, source: str) - dict[str, list[dict]]: ... class TableWriter(Protocol): def write(self, rows: list[dict], target: str) - None: ...有了这层协议cli.py里就只依赖接口具体是 openpyxl 实现还是 pandas 实现由配置决定。这一步的收益在半年后才体现某天上游要求支持 CSV 输入你只需要新增一个CsvReader主流程一行不改。6.2 用配置驱动字段映射别写死在代码里表头别名、必填校验、清洗规则这些内容会随业务方变化而频繁调整把它们从 Python 挪到 YAML改一次字段就不用发版sheet: 明细 header_row: 3 columns: - name: 客户名称 aliases: [客户名, 客户 名称, 客户] dtype: str required: true - name: 订单金额 aliases: [金额, 金额元] dtype: decimal required: true cleaner: strip_thousand_sep # 指向 excel_io.cleaners 里的函数名加载配置时用importlib按名字找 cleaner找不到就报错退出别静默降级成不处理——清洗函数没生效比程序崩溃更难查。6.3 验证方法拿一份黄金文件做回归改造过程中最怕的是「改完之后结果看起来差不多但某个字段错了」。我的做法是准备一份人工核对过的黄金文件输入 期望输出写一个对比测试读入原始表、跑完整流水线、把输出读回来和黄金输出逐行逐列比对差异超过阈值就失败。对比维度用什么比失败时的信号行数len(actual) len(expected)过滤条件改了但没同步列集合比对set(keys)schema 漏配或别名失效数值列Decimal精确比较精度丢失或单位换算错日期列统一转 ISO 字符串比较时区或序列号基准错文本列去首尾空格后比较清洗钩子没挂上表格里的日期列那一条要单独说直接比datetime对象容易因为时区或微秒差异误报统一转成YYYY-MM-DD再比误报率立刻降下来。6.4 一个具体技巧给写入加幂等键如果这条流水线要定时跑重复执行时必须保证输出不重复累积。最省事的做法不是每次清空重写而是在写入前按业务主键去重def upsert(rows, existing, key订单号): 以 key 为准做覆盖合并顺序保持 existing 在前、新增在后。 index {r[key]: i for i, r in enumerate(existing)} result list(existing) for r in rows: if r[key] in index: result[index[r[key]]] r else: index[r[key]] len(result) result.append(r) return result这个函数看着简单但它把「每次跑都追加」变成了「每次跑都对齐」定时任务重跑一次不会让报表翻倍。我真吃过这个亏——一个每天早上八点跑的脚本某天因为上游延迟手动补跑了一次当天报表金额直接翻倍财务拿着表来问的时候还挺尴尬。从那以后我每次做定时导出都强制走一遍幂等校验哪怕是看起来绝不会重复的单次任务。希望这些拆解能帮到你先把示例跑通再按自己的表结构改 schema剩下的就是填业务规则了。本文还有配套的精品资源点击获取