简介这份面向 Python 数据处理学习者的压缩包围绕 Excel 文件的读取与写入展开适用于数据分析、数据清洗、自动化报表等场景既可作为入门练习也便于有基础的开发者查阅对照。包内共 4 个文件全部为 Python 脚本压缩包约 6KB内容覆盖 pandas 读取表格、按编号读取、读写结合以及 openpyxl 操作工作簿等思路能直观呈现不同库处理表格数据的代码结构。已有 704 人学习说明其在同类入门资料中具有一定参考价值。读者可获得可直接运行的示例脚本用来理解 DataFrame 筛选、结果导出、单元格遍历与工作簿保存等关键环节并据此扩展到合并单元格、格式设置、日期处理等实际需求。整体体量轻巧脚本主题明确适合快速浏览后按需取用作为日常 Excel 自动化任务的代码起点与复用小样。1. 一个只做两件事的压缩包python 读 Excel 再写回去翻车点全在“写回去”拿到这个叫 python读取Excel并写入.zip 的资源多数人第一反应是——读表写表不就是两行 pandas我一开始也这么想直到同一份脚本连喂三张真实业务表一张表头在第 4 行一张单元格合并了三层一张订单号 18 位。脚本一行没报错数据全错而且错得非常安静安静到下游对账时才发现。这类工具真正解决的从来不是“能不能读”而是“读出来的东西能不能直接用”。它把读取、清洗、写入、回读校验串成一条能复现的链路适合每天要合并几十张报表的运营开发、做数据交付的分析岗以及被“帮我把这些表汇总一下”反复打断的后端。下面按拆包选型、读、写、排错、验收的顺序走一遍每一步都给出能直接抄的代码和参数。2. 拆包与选型三个引擎各管一段选错一条后面全是补丁动手前先把一件容易被跳过的事说清楚python 处理 Excel 从来不是一个库的事而是三个库分工。分不清这个分工后面所有问题都会退化成“玄学”——明明代码一样换个文件就报错。2.1 谁读数据、谁保格式、谁兜老文件常见的组织方式是pandas 负责数据层openpyxl 负责文件层。pandas 的read_excel和to_excel快在数据搬进搬出但它对样式、公式、合并单元格基本是“视而不见”——读进来是值写回去是白板。openpyxl 反过来它能精确到某个单元格的字体、边框、数字格式、冻结窗格但逐格操作慢几十万行就会让你等到怀疑人生。至于 xlrd它的定位在老.xls。新版 xlrd 已经把 xlsx 支持砍掉了很多人第一次踩坑就是拿它去读 xlsx报一句“Excel xlsx file; not supported”然后开始怀疑环境装错了。判断方式很简单看后缀。.xls走老路或者先转格式.xlsx/.xlsm走 openpyxl只取数据就用 pandas 包一层。使用场景推荐组合理由别这么干只取数据做分析、汇总pandas openpyxl 引擎一行读出 DataFrame省掉手写循环用 openpyxl 逐格读几十万行要保留原表样式、公式、合并openpyxl只改指定单元格其余原样不动pandas 读进来再to_excel样式全丢老.xls文件xlrd 只读或先转成 xlsx新引擎不认这个格式报错了才回头找原因几十万行导出openpyxl 的write_only模式流式落盘内存平稳DataFrame.to_excel一次性生成需要跑宏、调本机办公软件桌面自动化类方案目前只有它能碰宏放到没有桌面环境的服务器上跑这张表建议存下来。我见过的绝大多数“python 读写 Excel 出问题”本质都是拿数据层的工具去干文件层的活或者反过来。2.2 环境准备与最小可跑验证不要直接装在系统 python 里。Excel 相关库的依赖链不算短装冲突了排查成本极高。# 独立虚拟环境别污染系统解释器 python -m venv .venv source .venv/bin/activate # Windows 用 .venv\Scripts\activate # 三个包各司其职pandas 管数据openpyxl 管 xlsxxlrd 兜老 xls pip install pandas openpyxl xlrd # 锁死版本换机器复现全靠这个文件 pip freeze requirements.txt装完别急着上真实数据先跑一个最小闭环。这一步的价值在于把“环境问题”和“数据问题”隔离开。如果最小脚本都过不了后面所有报错都不用看。from openpyxl import Workbook, load_workbook wb Workbook() ws wb.active ws.title 验证 ws.append([订单号, 金额, 日期]) # 18 位单号必须写成字符串写成数字会变成浮点末位直接归零 ws.append([202401150000000001, 19.9, 2024-01-15]) wb.save(smoke.xlsx) # 回读一遍确认落盘的内容和你写进去的一致 wb2 load_workbook(smoke.xlsx, data_onlyFalse) for row in wb2[验证].iter_rows(values_onlyTrue): print(row)这里有两个参数值得单独记一下。data_onlyFalse表示读公式本身形如SUM(A1:A9)data_onlyTrue表示读公式的缓存计算结果。后者是很多坑的源头——如果这个文件从来没被办公软件打开并保存过缓存值是空的你读出来就是None不是 0也不是报错。另一个是iter_rows(values_onlyTrue)它直接吐元组而不是 Cell 对象速度快不少日常遍历优先用它。2.3 把工具切成四段出问题才知道往哪看单文件脚本写到两三百行还能忍写到八百行就是黑匣子报错在第一百行根因在第六百行。我一般会把这类工具切成四个函数边界每个边界只干一件事。# 1) read_source(path) - 只负责把文件变成 list[list] 或 DataFrame不做任何清洗 # 2) normalize(rows) - 表头对齐、合并单元格铺平、类型转换纯函数可单测 # 3) write_target(df, tpl) - 只负责写格式尽量交给模板不在代码里硬编码样式 # 4) verify(path, df) - 回读比对行数、列名、关键列空值率这么切的好处很实际读取出错就盯第一段数据不对就盯第二段文件打不开就盯第三段。中间那段是纯函数输入输出都是内存对象写几个断言就能覆盖掉大部分回归。真实项目里救命的往往不是多聪明的代码而是这种“出了问题能三分钟定位”的结构。3. 读的那一层表头、合并单元格与长数字三处必须手动接管读取是整个链路的入口也是最容易被“自动推断”坑到的地方。pandas 的默认行为是尽量聪明但业务表从来不长成它期待的样子。3.1 表头不在第一行header、skiprows、usecols 的配合业务报表的第一行经常是标题、第二行是导出时间、第三行是空行、第四行才是真表头。默认header0会把标题当列名后面全是错位。import pandas as pd df pd.read_excel( report.xlsx, sheet_name明细, header3, # 0 基索引第 4 行是表头 usecolsB:H, # 只取 B 到 H 列跳过左侧的说明列 dtypestr, # 全部按字符串进来类型后面自己转 engineopenpyxl, )header3是零基的第 4 行写 3这里错一位整张表就废了。usecols支持B:H这种区间写法也支持列名列表前者更适合格式固定的模板表。真正关键的是dtypestr让 pandas 别猜类型。它一猜001变成118 位单号变成2.02401e17日期变成Timestamp混着字符串。先全字符串进来后面按业务语义astype或pd.to_numeric(errorscoerce)错在哪一行你能查得出来让 pandas 猜错了你连原始值都看不到。如果表头行里还夹着空单元格pandas 会生成Unnamed: 3这种列名。判断方法很直接df.columns.str.startswith(Unnamed).any()。有就说明表头没对齐别急着往下跑。3.2 合并单元格与多级表头先铺平再拼列名openpyxl 读合并区域时只有左上角那个格有值其余全是None。这是规范行为不是 bug但对下游来说是灾难——你按列取值取到一堆None。from openpyxl import load_workbook wb load_workbook(multi_header.xlsx, data_onlyTrue) ws wb.active # 顺序很重要先取值再拆合并最后回填反了值就丢了 for rng in list(ws.merged_cells.ranges): top_left ws.cell(rng.min_row, rng.min_col).value ws.unmerge_cells(str(rng)) for r in range(rng.min_row, rng.max_row 1): for c in range(rng.min_col, rng.max_col 1): ws.cell(r, c).value top_leftrng.min_row / max_row / min_col / max_col是合并区域的边界unmerge_cells要传字符串形式的区域地址。这里必须list()包一层再遍历因为你在循环体里改了merged_cells.ranges直接迭代会抛“集合已改变”。铺平之后处理两级表头思路是把上下两行拼成一个列名rows list(ws.iter_rows(values_onlyTrue)) top, sub rows[0], rows[1] columns [ f{a}_{b} if b and str(b) not in str(a) else str(a) for a, b in zip(top, sub) ] data rows[2:]那个if判断是为了处理“上层有值下层为空”的情况——纵向合并的大类标题下层本来就是None直接拼会得到销售_None。要数值更规范可以先把None换掉再统一拼。数据部分的rows[2:]起始位置也要跟着表头层数走两级表头就是[2:]三级就是[3:]。3.3 类型陷阱长数字、日期序列号、公式缓存值这三样是读取阶段的高频翻车点而且都有个共同特征不报错。长数字的根子是浮点精度。双精度能精确表示的整数上限是 15 位左右18 位订单号、身份证号一进数值通道末几位必然归零。dtypestr能救的只有“源文件里本来就是文本”的情况如果源文件里已经存成了数字那这枚后悔药已经吃完了只能在导出方改格式重录或者从数据库重新拉一遍。我现在的习惯是拿到任何一张新表先df.dtypes看一眼哪列是float64但业务上应该是编号立刻停下来确认。日期在 xlsx 内部是序列号从某个基准日算起的天数openpyxl 一般会帮你转成datetime但如果单元格格式被设成“常规”读出来就是一个整数比如45306。混合类型列更麻烦整列会退化成object里面一半是datetime一半是int。稳妥做法是读完先做一次类型体检# 日期列体检混着 int 和 datetime 说明格式不统一 col df[日期] kinds col.map(lambda v: type(v).__name__).value_counts() print(kinds)公式的问题在 2.2 已经提过这里补一个判断手法用data_onlyTrue读出来整列None而data_onlyFalse读出来是...开头那就实锤了——文件是程序生成的缓存值从来没算过。这种情况别指望 openpyxl 帮你算它没有计算引擎。3.4 多 sheet 与大文件read_only 是省内存不是万能一次拿全部 sheet 的写法很短返回值是字典键是 sheet 名sheets pd.read_excel(book.xlsx, sheet_nameNone, dtypestr) for name, df in sheets.items(): print(name, df.shape)但它在内存里同时装下所有表表一多就顶不住。大文件走另一条路from openpyxl import load_workbook wb load_workbook(big.xlsx, read_onlyTrue, data_onlyTrue) ws wb[明细] for i, row in enumerate(ws.iter_rows(values_onlyTrue)): if i 0: continue # 跳过表头 # 这里做流式处理不要往 list 里塞 process(row) wb.close() # read_only 模式必须显式关闭否则文件句柄不释放read_onlyTrue的代价要说清楚只能顺序往前读不能随机访问某个单元格而且为了省内存它不解析合并单元格信息ws.merged_cells.ranges会是空的。所以“既要合并信息又要大文件”这个组合实际得走两遍——先普通模式读一遍表头结构再只读模式跑数据。最后那句wb.close()别省Windows 上文件句柄不释放后续写同名文件会直接报权限错误。4. 写的那一层从 DataFrame 到能直接发出去的表写比读更容易出“看起来没问题”的事故因为读错了你当场能看到写错了要等下游打开文件才发现。4.1 DataFrame 落地index、起始行、列宽与冻结最朴素的df.to_excel(out.xlsx)有三个默认行为值得改带上行号索引、从 A1 开始写、列宽全是默认值。from openpyxl.utils import get_column_letter with pd.ExcelWriter(out.xlsx, engineopenpyxl) as writer: # indexFalse 去掉行号startrow1 空出第一行放标题 df.to_excel(writer, sheet_name结果, indexFalse, startrow1) ws writer.sheets[结果] ws[A1] 数据导出自动生成 ws.freeze_panes A3 # 冻结前两行滚动时表头不跑 # 列宽按字符数估中文按两个字符宽算上限 40 防止某列撑爆 for idx, col in enumerate(df.columns, start1): width max(len(str(col)) * 2, 12) ws.column_dimensions[get_column_letter(idx)].width min(width, 40)indexFalse几乎永远是你要的除非索引本身是业务主键。startrow1配合ws[A1]是给表格加一行说明的常见做法注意加了之后freeze_panes要相应下移。列宽那段是个经验公式len()算的是字符数一个中文字实际占两格宽所以乘 2再卡一个 40 的上限否则某列里出现一长串备注整张表就没法看了。with语句在这里不是装饰ExcelWriter必须在退出时关闭才会真正落盘。忘了写with又不手动save()你会得到一个 0 字节的文件然后对着它发呆。4.2 往模板里追加load_workbook不要重建要往一张有表头样式、有下拉、有合并单元格的模板里填数据只有一条路——打开它追加另存。任何“读出来再to_excel”的做法都会把模板格式清零。from openpyxl import load_workbook wb load_workbook(template.xlsx) ws wb[明细] start ws.max_row 1 for row in df.itertuples(indexFalse): ws.append(list(row)) # 模板只给前几行刷了格式追加段要补上 for r in range(start, ws.max_row 1): ws.cell(r, 2).number_format 0.00 # 金额列两位小数 wb.save(template_filled.xlsx)df.itertuples(indexFalse)比iterrows()快而且不会把行转成 Series 导致类型漂移——iterrows遇到混合类型列会把整行升格成object数字变字符串是常事。ws.append()按顺序往最后一行后面接但它判断“最后一行”用的就是max_row这就埋下了 5.1 里要讲的那个坑。补格式那段不能省模板里预刷的边框和数字格式只覆盖到设计时的行数追加出去的部分是裸的。4.3 大文件写入write_only 与生成器几十万行用普通模式写内存会一路涨到几个 G。write_onlyTrue是流式写边生成边落盘。from openpyxl import Workbook wb Workbook(write_onlyTrue) ws wb.create_sheet(明细) ws.append([订单号, 金额]) def gen_rows(n): for i in range(n): yield [fNO{i:012d}, round(i * 0.01, 2)] for r in gen_rows(200000): ws.append(r) wb.save(big_out.xlsx)这个模式限制很硬只能append不能回头修改已写的单元格不能用ws.cell(r, c)随机访问ws.max_row也不可靠。所以它适合“数据已经算完、只需要落盘”的导出场景不适合需要反复调整格式的报表。另外Workbook(write_onlyTrue)建出来没有默认 sheet得自己create_sheet。4.4 原子替换别让下游读到半个文件写到一半进程被杀、磁盘满、或者下游定时任务正好在这个时间点来读都会拿到一个损坏的文件。稳妥做法是先写临时文件成功了再替换。import os, tempfile tmp_fd, tmp_path tempfile.mkstemp(suffix.xlsx, dir.) os.close(tmp_fd) # mkstemp 返回的 fd 要关掉不然 Windows 上占用 try: wb.save(tmp_path) os.replace(tmp_path, out.xlsx) # 同分区内是原子操作 finally: if os.path.exists(tmp_path): os.remove(tmp_path)os.replace在同一个分区内是原子的跨分区就会退化成“复制 删除”失去意义。所以dir.要跟目标文件同目录别图省事指到系统临时目录去。这个习惯看起来多写了五行但在有定时任务的环境里能挡掉一整类“文件损坏”的报障。5. 避坑与排查五条血泪记录每条都能让你少加一次班下面这些不是我编的案例是这类工具在真实环境里最常复现的五种故障。每条按现象、原因、解决写照着对号入座就行。5.1 读的时候翻的三次车现象一读出来一堆Unnamed: 3这样的列名。原因是表头行里存在空单元格或者表头本身是合并单元格pandas 拿不到名字就自动编号。解决方式是headerNone先把原始行读出来自己定位表头行再重命名不要试图用rename一个个改列一多必漏。raw pd.read_excel(x.xlsx, headerNone, dtypestr) header_row 3 # 第 4 行是表头 df raw.iloc[header_row 1:].copy() df.columns raw.iloc[header_row].tolist()现象二订单号写进文件后变成科学计数法末几位全是 0。原因是源文件里这一列本来就存成了数值超过 15 位精度已经丢了dtypestr只是把丢完的结果转成字符串。这种数据救不回来只能回源头重导。判断方法用 openpyxl 读原始单元格看cell.data_type是n就说明存的是数字。往后做这类列一律在导出环节强制文本格式。现象三data_onlyTrue读公式列整列None。原因是文件由程序生成缓存值从未被计算过openpyxl 又没有计算引擎。解决方式是让生成方直接写计算结果或者让上游提供值而不是公式。指望“用代码触发重算”基本走不通需要重算的场景只能交给能执行公式的桌面端方案。现象四read_onlyTrue打开后合并单元格信息是空的。原因是只读模式为省内存跳过了合并区域解析。要用合并信息就别开read_only或者先用普通模式把表头结构读一遍再换只读模式跑数据。5.2 写的时候翻的两次车现象五追加数据后新内容跑到几千行之后中间大片空白。原因是模板底部刷过边框或填充色ws.max_row返回的是“有过格式的最大行”不是“有数据的最大行”。这是最典型的格式污染。解决办法是自己从下往上找第一行有值的行def last_data_row(ws, key_col1): 从下往上扫关键列避开模板里只刷了格式的空行 for r in range(ws.max_row, 0, -1): if ws.cell(r, key_col).value not in (None, ): return r return 0key_col要选业务上一定不为空的列比如订单号或序号列。用 A 列不一定稳有些模板 A 列是空着的辅助列。现象六写入以开头的字符串打开文件变成#NAME?。原因是 openpyxl 见到开头就按公式处理。像某些编码、表达式片段业务上就是纯文本结果被当成了公式。cell ws.cell(rowr, columnc) cell.value text if isinstance(text, str) and text.startswith(): cell.data_type s # 强制按字符串存别让它走公式通道这个改法在不同版本上行为略有差异所以写完必须回读一次确认别只看代码觉得对就交付。我的习惯是只要这一列可能出现、、-开头的文本写入后立刻用load_workbook打开抽查前几行两秒钟的事。还有一个跨格式的问题如果输出的是 csv 而不是 xlsx中文乱码就别去调 Excel 设置了是编码的事to_csv(..., encodingutf-8-sig)带上 BOM 就能被常见表格软件正确识别。6. 交付前的回读校验与批量跑目录的两个习惯工具写完不算完真正的分水岭在“交付前有没有验”。我现在的规矩是只要这份文件要发给别人就必须过一遍回读校验不管多小的表。6.1 一个函数挡住大部分事故校验不需要复杂盯三件事就够行数对不对、表头对不对、关键列空值率有没有异常。from openpyxl import load_workbook def verify(path, expect_rows, key_index): 回读校验行数、表头、关键列空值率三项对不上就别交付 wb load_workbook(path, read_onlyTrue, data_onlyTrue) ws wb[wb.sheetnames[0]] it ws.iter_rows(values_onlyTrue) header next(it) # 第一行当表头 n, blank 0, 0 for row in it: n 1 if row[key_index] in (None, ): blank 1 wb.close() return { rows: n, expect: expect_rows, blank_rate: round(blank / max(n, 1), 4), header: header, } print(verify(out.xlsx, expect_rowslen(df), key_index0))expect_rows传源数据的行数key_index传关键列的零基下标。返回值里blank_rate是个很灵敏的指标正常业务表通常低于 1%一旦跳到 10% 以上基本可以断定写入时列错位了或者合并单元格没铺平。这比肉眼看前几行靠谱得多因为错位往往从中间某一行才开始。顺带一个经验值rows比expect多出几百上千行八成是 5.2 里那个max_row被格式污染的问题少了几百行八成是读取时skiprows或空行处理把数据吃掉了。看着这两个数字对不上定位方向立刻就清楚了。6.2 批量跑目录先排掉锁文件再谈并发批量处理一个目录的表格最容易踩的不是逻辑是文件筛选。from pathlib import Path src Path(./inbox) # 办公软件打开文件时会生成 ~$ 开头的临时锁文件读它必报错 files sorted(p for p in src.glob(*.xls*) if not p.name.startswith(~$)) print(len(files), [p.name for p in files[:3]])*.xls*能同时匹配.xls和.xlsx但也会把~$xxx.xlsx这种锁文件捞进来。这类文件是二进制残留load_workbook打开会报BadZipFile或者直接卡住而且它只在文件被打开时存在本地测不出来、线上一跑就炸。输出命名上我一般带上源文件 stem 加批次时间戳别用固定名字覆盖f{p.stem}_{batch}.xlsx。这样重跑不会互相踩出问题也能顺着文件名回溯到源。并发这块说个边界openpyxl 的 Workbook 对象不是线程安全的多线程共享同一个 wb 去 append写出来的文件一定是乱的。真要提速用进程池每个进程独立处理一个文件进程数开到 CPU 核数的一半左右就够——这类任务的瓶颈在磁盘 IO 和解析开满了反而互相拖。批量跑的时候我固定会在循环里加一层 try/except 把异常文件记进一个失败清单跑完统一看而不是让一个坏文件把整批中断掉。最后说个我自己的习惯。早年做报表交付我图快写完文件扫一眼前两行就发出去结果有一次列错位从第 300 行开始下游拿去做汇总硬是把一份百万级的账算歪了回头对了两天才定位到。从那以后我每次保存完 xlsx都强制走一遍回读校验 抽查中间几行哪怕这张表只有二十行。多花的这三十秒换来的是不用在半夜爬起来查账。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/30 7:18:31 · 阅读 1.2k Python读写Excel避坑指南：表头、合并单元格与回读校验
简介这份面向 Python 数据处理学习者的压缩包围绕 Excel 文件的读取与写入展开适用于数据分析、数据清洗、自动化报表等场景既可作为入门练习也便于有基础的开发者查阅对照。包内共 4 个文件全部为 Python 脚本压缩包约 6KB内容覆盖 pandas 读取表格、按编号读取、读写结合以及 openpyxl 操作工作簿等思路能直观呈现不同库处理表格数据的代码结构。已有 704 人学习说明其在同类入门资料中具有一定参考价值。读者可获得可直接运行的示例脚本用来理解 DataFrame 筛选、结果导出、单元格遍历与工作簿保存等关键环节并据此扩展到合并单元格、格式设置、日期处理等实际需求。整体体量轻巧脚本主题明确适合快速浏览后按需取用作为日常 Excel 自动化任务的代码起点与复用小样。1. 一个只做两件事的压缩包python 读 Excel 再写回去翻车点全在“写回去”拿到这个叫 python读取Excel并写入.zip 的资源多数人第一反应是——读表写表不就是两行 pandas我一开始也这么想直到同一份脚本连喂三张真实业务表一张表头在第 4 行一张单元格合并了三层一张订单号 18 位。脚本一行没报错数据全错而且错得非常安静安静到下游对账时才发现。这类工具真正解决的从来不是“能不能读”而是“读出来的东西能不能直接用”。它把读取、清洗、写入、回读校验串成一条能复现的链路适合每天要合并几十张报表的运营开发、做数据交付的分析岗以及被“帮我把这些表汇总一下”反复打断的后端。下面按拆包选型、读、写、排错、验收的顺序走一遍每一步都给出能直接抄的代码和参数。2. 拆包与选型三个引擎各管一段选错一条后面全是补丁动手前先把一件容易被跳过的事说清楚python 处理 Excel 从来不是一个库的事而是三个库分工。分不清这个分工后面所有问题都会退化成“玄学”——明明代码一样换个文件就报错。2.1 谁读数据、谁保格式、谁兜老文件常见的组织方式是pandas 负责数据层openpyxl 负责文件层。pandas 的read_excel和to_excel快在数据搬进搬出但它对样式、公式、合并单元格基本是“视而不见”——读进来是值写回去是白板。openpyxl 反过来它能精确到某个单元格的字体、边框、数字格式、冻结窗格但逐格操作慢几十万行就会让你等到怀疑人生。至于 xlrd它的定位在老.xls。新版 xlrd 已经把 xlsx 支持砍掉了很多人第一次踩坑就是拿它去读 xlsx报一句“Excel xlsx file; not supported”然后开始怀疑环境装错了。判断方式很简单看后缀。.xls走老路或者先转格式.xlsx/.xlsm走 openpyxl只取数据就用 pandas 包一层。使用场景推荐组合理由别这么干只取数据做分析、汇总pandas openpyxl 引擎一行读出 DataFrame省掉手写循环用 openpyxl 逐格读几十万行要保留原表样式、公式、合并openpyxl只改指定单元格其余原样不动pandas 读进来再to_excel样式全丢老.xls文件xlrd 只读或先转成 xlsx新引擎不认这个格式报错了才回头找原因几十万行导出openpyxl 的write_only模式流式落盘内存平稳DataFrame.to_excel一次性生成需要跑宏、调本机办公软件桌面自动化类方案目前只有它能碰宏放到没有桌面环境的服务器上跑这张表建议存下来。我见过的绝大多数“python 读写 Excel 出问题”本质都是拿数据层的工具去干文件层的活或者反过来。2.2 环境准备与最小可跑验证不要直接装在系统 python 里。Excel 相关库的依赖链不算短装冲突了排查成本极高。# 独立虚拟环境别污染系统解释器 python -m venv .venv source .venv/bin/activate # Windows 用 .venv\Scripts\activate # 三个包各司其职pandas 管数据openpyxl 管 xlsxxlrd 兜老 xls pip install pandas openpyxl xlrd # 锁死版本换机器复现全靠这个文件 pip freeze requirements.txt装完别急着上真实数据先跑一个最小闭环。这一步的价值在于把“环境问题”和“数据问题”隔离开。如果最小脚本都过不了后面所有报错都不用看。from openpyxl import Workbook, load_workbook wb Workbook() ws wb.active ws.title 验证 ws.append([订单号, 金额, 日期]) # 18 位单号必须写成字符串写成数字会变成浮点末位直接归零 ws.append([202401150000000001, 19.9, 2024-01-15]) wb.save(smoke.xlsx) # 回读一遍确认落盘的内容和你写进去的一致 wb2 load_workbook(smoke.xlsx, data_onlyFalse) for row in wb2[验证].iter_rows(values_onlyTrue): print(row)这里有两个参数值得单独记一下。data_onlyFalse表示读公式本身形如SUM(A1:A9)data_onlyTrue表示读公式的缓存计算结果。后者是很多坑的源头——如果这个文件从来没被办公软件打开并保存过缓存值是空的你读出来就是None不是 0也不是报错。另一个是iter_rows(values_onlyTrue)它直接吐元组而不是 Cell 对象速度快不少日常遍历优先用它。2.3 把工具切成四段出问题才知道往哪看单文件脚本写到两三百行还能忍写到八百行就是黑匣子报错在第一百行根因在第六百行。我一般会把这类工具切成四个函数边界每个边界只干一件事。# 1) read_source(path) - 只负责把文件变成 list[list] 或 DataFrame不做任何清洗 # 2) normalize(rows) - 表头对齐、合并单元格铺平、类型转换纯函数可单测 # 3) write_target(df, tpl) - 只负责写格式尽量交给模板不在代码里硬编码样式 # 4) verify(path, df) - 回读比对行数、列名、关键列空值率这么切的好处很实际读取出错就盯第一段数据不对就盯第二段文件打不开就盯第三段。中间那段是纯函数输入输出都是内存对象写几个断言就能覆盖掉大部分回归。真实项目里救命的往往不是多聪明的代码而是这种“出了问题能三分钟定位”的结构。3. 读的那一层表头、合并单元格与长数字三处必须手动接管读取是整个链路的入口也是最容易被“自动推断”坑到的地方。pandas 的默认行为是尽量聪明但业务表从来不长成它期待的样子。3.1 表头不在第一行header、skiprows、usecols 的配合业务报表的第一行经常是标题、第二行是导出时间、第三行是空行、第四行才是真表头。默认header0会把标题当列名后面全是错位。import pandas as pd df pd.read_excel( report.xlsx, sheet_name明细, header3, # 0 基索引第 4 行是表头 usecolsB:H, # 只取 B 到 H 列跳过左侧的说明列 dtypestr, # 全部按字符串进来类型后面自己转 engineopenpyxl, )header3是零基的第 4 行写 3这里错一位整张表就废了。usecols支持B:H这种区间写法也支持列名列表前者更适合格式固定的模板表。真正关键的是dtypestr让 pandas 别猜类型。它一猜001变成118 位单号变成2.02401e17日期变成Timestamp混着字符串。先全字符串进来后面按业务语义astype或pd.to_numeric(errorscoerce)错在哪一行你能查得出来让 pandas 猜错了你连原始值都看不到。如果表头行里还夹着空单元格pandas 会生成Unnamed: 3这种列名。判断方法很直接df.columns.str.startswith(Unnamed).any()。有就说明表头没对齐别急着往下跑。3.2 合并单元格与多级表头先铺平再拼列名openpyxl 读合并区域时只有左上角那个格有值其余全是None。这是规范行为不是 bug但对下游来说是灾难——你按列取值取到一堆None。from openpyxl import load_workbook wb load_workbook(multi_header.xlsx, data_onlyTrue) ws wb.active # 顺序很重要先取值再拆合并最后回填反了值就丢了 for rng in list(ws.merged_cells.ranges): top_left ws.cell(rng.min_row, rng.min_col).value ws.unmerge_cells(str(rng)) for r in range(rng.min_row, rng.max_row 1): for c in range(rng.min_col, rng.max_col 1): ws.cell(r, c).value top_leftrng.min_row / max_row / min_col / max_col是合并区域的边界unmerge_cells要传字符串形式的区域地址。这里必须list()包一层再遍历因为你在循环体里改了merged_cells.ranges直接迭代会抛“集合已改变”。铺平之后处理两级表头思路是把上下两行拼成一个列名rows list(ws.iter_rows(values_onlyTrue)) top, sub rows[0], rows[1] columns [ f{a}_{b} if b and str(b) not in str(a) else str(a) for a, b in zip(top, sub) ] data rows[2:]那个if判断是为了处理“上层有值下层为空”的情况——纵向合并的大类标题下层本来就是None直接拼会得到销售_None。要数值更规范可以先把None换掉再统一拼。数据部分的rows[2:]起始位置也要跟着表头层数走两级表头就是[2:]三级就是[3:]。3.3 类型陷阱长数字、日期序列号、公式缓存值这三样是读取阶段的高频翻车点而且都有个共同特征不报错。长数字的根子是浮点精度。双精度能精确表示的整数上限是 15 位左右18 位订单号、身份证号一进数值通道末几位必然归零。dtypestr能救的只有“源文件里本来就是文本”的情况如果源文件里已经存成了数字那这枚后悔药已经吃完了只能在导出方改格式重录或者从数据库重新拉一遍。我现在的习惯是拿到任何一张新表先df.dtypes看一眼哪列是float64但业务上应该是编号立刻停下来确认。日期在 xlsx 内部是序列号从某个基准日算起的天数openpyxl 一般会帮你转成datetime但如果单元格格式被设成“常规”读出来就是一个整数比如45306。混合类型列更麻烦整列会退化成object里面一半是datetime一半是int。稳妥做法是读完先做一次类型体检# 日期列体检混着 int 和 datetime 说明格式不统一 col df[日期] kinds col.map(lambda v: type(v).__name__).value_counts() print(kinds)公式的问题在 2.2 已经提过这里补一个判断手法用data_onlyTrue读出来整列None而data_onlyFalse读出来是...开头那就实锤了——文件是程序生成的缓存值从来没算过。这种情况别指望 openpyxl 帮你算它没有计算引擎。3.4 多 sheet 与大文件read_only 是省内存不是万能一次拿全部 sheet 的写法很短返回值是字典键是 sheet 名sheets pd.read_excel(book.xlsx, sheet_nameNone, dtypestr) for name, df in sheets.items(): print(name, df.shape)但它在内存里同时装下所有表表一多就顶不住。大文件走另一条路from openpyxl import load_workbook wb load_workbook(big.xlsx, read_onlyTrue, data_onlyTrue) ws wb[明细] for i, row in enumerate(ws.iter_rows(values_onlyTrue)): if i 0: continue # 跳过表头 # 这里做流式处理不要往 list 里塞 process(row) wb.close() # read_only 模式必须显式关闭否则文件句柄不释放read_onlyTrue的代价要说清楚只能顺序往前读不能随机访问某个单元格而且为了省内存它不解析合并单元格信息ws.merged_cells.ranges会是空的。所以“既要合并信息又要大文件”这个组合实际得走两遍——先普通模式读一遍表头结构再只读模式跑数据。最后那句wb.close()别省Windows 上文件句柄不释放后续写同名文件会直接报权限错误。4. 写的那一层从 DataFrame 到能直接发出去的表写比读更容易出“看起来没问题”的事故因为读错了你当场能看到写错了要等下游打开文件才发现。4.1 DataFrame 落地index、起始行、列宽与冻结最朴素的df.to_excel(out.xlsx)有三个默认行为值得改带上行号索引、从 A1 开始写、列宽全是默认值。from openpyxl.utils import get_column_letter with pd.ExcelWriter(out.xlsx, engineopenpyxl) as writer: # indexFalse 去掉行号startrow1 空出第一行放标题 df.to_excel(writer, sheet_name结果, indexFalse, startrow1) ws writer.sheets[结果] ws[A1] 数据导出自动生成 ws.freeze_panes A3 # 冻结前两行滚动时表头不跑 # 列宽按字符数估中文按两个字符宽算上限 40 防止某列撑爆 for idx, col in enumerate(df.columns, start1): width max(len(str(col)) * 2, 12) ws.column_dimensions[get_column_letter(idx)].width min(width, 40)indexFalse几乎永远是你要的除非索引本身是业务主键。startrow1配合ws[A1]是给表格加一行说明的常见做法注意加了之后freeze_panes要相应下移。列宽那段是个经验公式len()算的是字符数一个中文字实际占两格宽所以乘 2再卡一个 40 的上限否则某列里出现一长串备注整张表就没法看了。with语句在这里不是装饰ExcelWriter必须在退出时关闭才会真正落盘。忘了写with又不手动save()你会得到一个 0 字节的文件然后对着它发呆。4.2 往模板里追加load_workbook不要重建要往一张有表头样式、有下拉、有合并单元格的模板里填数据只有一条路——打开它追加另存。任何“读出来再to_excel”的做法都会把模板格式清零。from openpyxl import load_workbook wb load_workbook(template.xlsx) ws wb[明细] start ws.max_row 1 for row in df.itertuples(indexFalse): ws.append(list(row)) # 模板只给前几行刷了格式追加段要补上 for r in range(start, ws.max_row 1): ws.cell(r, 2).number_format 0.00 # 金额列两位小数 wb.save(template_filled.xlsx)df.itertuples(indexFalse)比iterrows()快而且不会把行转成 Series 导致类型漂移——iterrows遇到混合类型列会把整行升格成object数字变字符串是常事。ws.append()按顺序往最后一行后面接但它判断“最后一行”用的就是max_row这就埋下了 5.1 里要讲的那个坑。补格式那段不能省模板里预刷的边框和数字格式只覆盖到设计时的行数追加出去的部分是裸的。4.3 大文件写入write_only 与生成器几十万行用普通模式写内存会一路涨到几个 G。write_onlyTrue是流式写边生成边落盘。from openpyxl import Workbook wb Workbook(write_onlyTrue) ws wb.create_sheet(明细) ws.append([订单号, 金额]) def gen_rows(n): for i in range(n): yield [fNO{i:012d}, round(i * 0.01, 2)] for r in gen_rows(200000): ws.append(r) wb.save(big_out.xlsx)这个模式限制很硬只能append不能回头修改已写的单元格不能用ws.cell(r, c)随机访问ws.max_row也不可靠。所以它适合“数据已经算完、只需要落盘”的导出场景不适合需要反复调整格式的报表。另外Workbook(write_onlyTrue)建出来没有默认 sheet得自己create_sheet。4.4 原子替换别让下游读到半个文件写到一半进程被杀、磁盘满、或者下游定时任务正好在这个时间点来读都会拿到一个损坏的文件。稳妥做法是先写临时文件成功了再替换。import os, tempfile tmp_fd, tmp_path tempfile.mkstemp(suffix.xlsx, dir.) os.close(tmp_fd) # mkstemp 返回的 fd 要关掉不然 Windows 上占用 try: wb.save(tmp_path) os.replace(tmp_path, out.xlsx) # 同分区内是原子操作 finally: if os.path.exists(tmp_path): os.remove(tmp_path)os.replace在同一个分区内是原子的跨分区就会退化成“复制 删除”失去意义。所以dir.要跟目标文件同目录别图省事指到系统临时目录去。这个习惯看起来多写了五行但在有定时任务的环境里能挡掉一整类“文件损坏”的报障。5. 避坑与排查五条血泪记录每条都能让你少加一次班下面这些不是我编的案例是这类工具在真实环境里最常复现的五种故障。每条按现象、原因、解决写照着对号入座就行。5.1 读的时候翻的三次车现象一读出来一堆Unnamed: 3这样的列名。原因是表头行里存在空单元格或者表头本身是合并单元格pandas 拿不到名字就自动编号。解决方式是headerNone先把原始行读出来自己定位表头行再重命名不要试图用rename一个个改列一多必漏。raw pd.read_excel(x.xlsx, headerNone, dtypestr) header_row 3 # 第 4 行是表头 df raw.iloc[header_row 1:].copy() df.columns raw.iloc[header_row].tolist()现象二订单号写进文件后变成科学计数法末几位全是 0。原因是源文件里这一列本来就存成了数值超过 15 位精度已经丢了dtypestr只是把丢完的结果转成字符串。这种数据救不回来只能回源头重导。判断方法用 openpyxl 读原始单元格看cell.data_type是n就说明存的是数字。往后做这类列一律在导出环节强制文本格式。现象三data_onlyTrue读公式列整列None。原因是文件由程序生成缓存值从未被计算过openpyxl 又没有计算引擎。解决方式是让生成方直接写计算结果或者让上游提供值而不是公式。指望“用代码触发重算”基本走不通需要重算的场景只能交给能执行公式的桌面端方案。现象四read_onlyTrue打开后合并单元格信息是空的。原因是只读模式为省内存跳过了合并区域解析。要用合并信息就别开read_only或者先用普通模式把表头结构读一遍再换只读模式跑数据。5.2 写的时候翻的两次车现象五追加数据后新内容跑到几千行之后中间大片空白。原因是模板底部刷过边框或填充色ws.max_row返回的是“有过格式的最大行”不是“有数据的最大行”。这是最典型的格式污染。解决办法是自己从下往上找第一行有值的行def last_data_row(ws, key_col1): 从下往上扫关键列避开模板里只刷了格式的空行 for r in range(ws.max_row, 0, -1): if ws.cell(r, key_col).value not in (None, ): return r return 0key_col要选业务上一定不为空的列比如订单号或序号列。用 A 列不一定稳有些模板 A 列是空着的辅助列。现象六写入以开头的字符串打开文件变成#NAME?。原因是 openpyxl 见到开头就按公式处理。像某些编码、表达式片段业务上就是纯文本结果被当成了公式。cell ws.cell(rowr, columnc) cell.value text if isinstance(text, str) and text.startswith(): cell.data_type s # 强制按字符串存别让它走公式通道这个改法在不同版本上行为略有差异所以写完必须回读一次确认别只看代码觉得对就交付。我的习惯是只要这一列可能出现、、-开头的文本写入后立刻用load_workbook打开抽查前几行两秒钟的事。还有一个跨格式的问题如果输出的是 csv 而不是 xlsx中文乱码就别去调 Excel 设置了是编码的事to_csv(..., encodingutf-8-sig)带上 BOM 就能被常见表格软件正确识别。6. 交付前的回读校验与批量跑目录的两个习惯工具写完不算完真正的分水岭在“交付前有没有验”。我现在的规矩是只要这份文件要发给别人就必须过一遍回读校验不管多小的表。6.1 一个函数挡住大部分事故校验不需要复杂盯三件事就够行数对不对、表头对不对、关键列空值率有没有异常。from openpyxl import load_workbook def verify(path, expect_rows, key_index): 回读校验行数、表头、关键列空值率三项对不上就别交付 wb load_workbook(path, read_onlyTrue, data_onlyTrue) ws wb[wb.sheetnames[0]] it ws.iter_rows(values_onlyTrue) header next(it) # 第一行当表头 n, blank 0, 0 for row in it: n 1 if row[key_index] in (None, ): blank 1 wb.close() return { rows: n, expect: expect_rows, blank_rate: round(blank / max(n, 1), 4), header: header, } print(verify(out.xlsx, expect_rowslen(df), key_index0))expect_rows传源数据的行数key_index传关键列的零基下标。返回值里blank_rate是个很灵敏的指标正常业务表通常低于 1%一旦跳到 10% 以上基本可以断定写入时列错位了或者合并单元格没铺平。这比肉眼看前几行靠谱得多因为错位往往从中间某一行才开始。顺带一个经验值rows比expect多出几百上千行八成是 5.2 里那个max_row被格式污染的问题少了几百行八成是读取时skiprows或空行处理把数据吃掉了。看着这两个数字对不上定位方向立刻就清楚了。6.2 批量跑目录先排掉锁文件再谈并发批量处理一个目录的表格最容易踩的不是逻辑是文件筛选。from pathlib import Path src Path(./inbox) # 办公软件打开文件时会生成 ~$ 开头的临时锁文件读它必报错 files sorted(p for p in src.glob(*.xls*) if not p.name.startswith(~$)) print(len(files), [p.name for p in files[:3]])*.xls*能同时匹配.xls和.xlsx但也会把~$xxx.xlsx这种锁文件捞进来。这类文件是二进制残留load_workbook打开会报BadZipFile或者直接卡住而且它只在文件被打开时存在本地测不出来、线上一跑就炸。输出命名上我一般带上源文件 stem 加批次时间戳别用固定名字覆盖f{p.stem}_{batch}.xlsx。这样重跑不会互相踩出问题也能顺着文件名回溯到源。并发这块说个边界openpyxl 的 Workbook 对象不是线程安全的多线程共享同一个 wb 去 append写出来的文件一定是乱的。真要提速用进程池每个进程独立处理一个文件进程数开到 CPU 核数的一半左右就够——这类任务的瓶颈在磁盘 IO 和解析开满了反而互相拖。批量跑的时候我固定会在循环里加一层 try/except 把异常文件记进一个失败清单跑完统一看而不是让一个坏文件把整批中断掉。最后说个我自己的习惯。早年做报表交付我图快写完文件扫一眼前两行就发出去结果有一次列错位从第 300 行开始下游拿去做汇总硬是把一份百万级的账算歪了回头对了两天才定位到。从那以后我每次保存完 xlsx都强制走一遍回读校验 抽查中间几行哪怕这张表只有二十行。多花的这三十秒换来的是不用在半夜爬起来查账。希望帮到你。本文还有配套的精品资源点击获取