1. 先别急着写代码先把“提取”和“填入”这两件事拆清楚如果你看到“python3.8 提取xlsx表格内容填入单个文件”这个需求第一反应多半是“用 Python 读 Excel然后写出去”。这个方向没错但如果直接上手写循环大概率会写出一堆跑完也不知道对不对的脚本尤其是数据量大、文件多的时候。我处理这种需求的经验是先花十分钟把“提取”和“填入”两个动作分别定义清楚。提取到底是从一个工作簿里的多个工作表取内容还是从几十个 xlsx 文件里取同一位置的单元格填入是生成一个新的 xlsx还是输出成一个 CSV 或者纯文本清单同一个标题下这两种场景的代码结构完全不同。以标题里的“表格内容”四个字来说实际中通常指三种形态整张工作表的所有数据行比如多个分公司的报表数据合并成一份总表指定列的特定记录比如只提取每个文件里的“项目名称”和“负责人”某个固定位置的单元格信息比如标题、日期、总金额这类散落数据需要拉出来组成一张索引表。另外还要确认输出文件的“单个文件”是什么格式。如果下游是要人工查看、二次编辑生成 xlsx 最稳妥如果是要进数据库或者做数据分析CSV 反而更好。映射到 Python 3.8 环境里openpyxl 和 pandas 读写方式差异很大选错了后面的代码就会绕路。把需求拆到这个颗粒度之后再从技术层面看这件事就清晰了一个 xlsx 文件本质是一个 zip 压缩包里面装着 XML 格式的表格数据。Python 的第三方库负责解开这层壳把 XML 转成我们能直接操作的单元格对象。所以所有库的本质工作都是“解析 XML 维持数据格式”理解了这一点后面的踩坑点就都能找到根因了。2. 环境准备给 Python 3.8 装上趁手的 Excel 工具2.1 为什么选 openpyxl 而不是其他库Python 3.8 环境下处理 xlsx绕不开两个主流库openpyxl 和 pandas。如果你的场景是“读取表格中若干单元格然后填到另一个文件”不需要做复杂统计那 openpyxl 就是最轻的选择——它只负责读写不引入 DataFrame 那套抽象。安装方式很简单pip install openpyxl如果你的机器上有多个 Python 版本不要直接pip install先确认当前用的确实是 3.8python --version实际工作中很多人在这里踩了坑系统默认的python命令指向的是 Python 2.7 或者 3.10装库的时候装到了另一个版本底下导致后面import openpyxl报错。稳妥的做法是用虚拟环境python3.8 -m venv excel_venv source excel_venv/bin/activate # Windows 下是 excel_venv\Scripts\activate pip install openpyxl之前我帮同事排查过一个问题脚本在他的电脑上报ModuleNotFoundError: No module named openpyxl但pip show openpyxl明明显示已安装。最后发现是因为他用shutil复制了一台机器上的 Python 目录过来包环境和解释器路径错位。虚拟环境一开这个问题彻底消失。2.2 xlsx 的三个层级工作簿、工作表、单元格openpyxl 的 API 设计完全对应 Excel 的层级结构搞清楚这三个对象之间的关系读代码和写代码都会顺畅很多工作簿Workbook一个 xlsx 文件就是一个工作簿。用load_workbook()打开后通过wb.sheetnames可以拿到里面所有工作表的名称列表。工作表Worksheet工作簿里的每一页。通过wb[工作表名]或者wb.active获取当前页。单元格Cell最小的数据单元通过sheet[A1]或sheet.cell(row, column)访问。日常操作里的“遍历一整张表”通常用的是sheet.iter_rows()它会按行吐出单元格元组。还有一个特别有用的属性是sheet.values它返回的是每一行的值生成器注意它不包含单元格格式信息但用来提取数据恰恰最合适后面会演示。2.3 验证环境的经典最小用例装完库之后先别急着写完整脚本跑一个五行的最小用例确认环境没问题import openpyxl # 创建一个简单的测试工作簿 wb openpyxl.Workbook() ws wb.active ws[A1] 姓名 ws[B1] 得分 ws.append([张三, 88]) ws.append([李四, 95]) wb.save(demo.xlsx) # 读取测试工作簿 wb2 openpyxl.load_workbook(demo.xlsx) ws2 wb2[Sheet] for row in ws2.values: print(row)执行后如果看到(姓名, 得分)、(张三, 88)、(李四, 95)三行输出那环境就完全没问题了。注意我用了ws2.values而不是ws2.iter_rows()两者的区别在数据量大时非常明显iter_rows()会构造一个Cell对象数组你需要再取.valuevalues直接生成 Python 原生类型内存和时间都省。3. 单文件提取把一个 xlsx 里多个工作表的内容汇总到单文件先做一个最常见的场景一个 xlsx 文件里有多个格式相同的工作表比如 1 月到 12 月的数据页想把它们纵向拼接成一份汇总表写到一个全新的工作簿里。有人可能会问这直接在 Excel 里复制粘贴不就行了当然行但如果每个月表都有两三百行且以后每个月都要做一次脚本的价值就出来了。3.1 读取所有工作表并统一字段假设每个月的工作表结构如下A 列是日期B 列是产品C 列是销售额每张表的第一行都是表头。我们要做的就是把所有表从第二行开始的数据读出来合并到一张新表里。import openpyxl src_path 2024_sales.xlsx wb openpyxl.load_workbook(src_path, data_onlyTrue) new_wb openpyxl.Workbook() new_ws new_wb.active new_ws.append([日期, 产品, 销售额]) # 新表表头 for sheet_name in wb.sheetnames: ws wb[sheet_name] for row in ws.iter_rows(min_row2, values_onlyTrue): # 用一个简单判断跳过空行 if row[0] is None: continue new_ws.append(row) new_wb.save(merged_output.xlsx)这里两个细节值得展开data_onlyTrue表示读取单元格的缓存值而不是公式。如果你的原表里“销售额”这一列是C2*1.1这类公式不加这个参数读到的是公式字符串加上之后读的是 Excel 计算并缓存的结果。这个参数在“提取内容”场景里基本是必需品。values_onlyTrue等同于前面提到的values属性但iter_rows里可以传min_row控制起始行。为什么要min_row2因为第一行是表头直接用ws.values会把每张表的表头也合并进来最后生成的文件里会出现 12 次表头后续做透视表时还得手动过滤。3.2 提取特定单元格散落信息怎么拉出来不是所有提取都是整表抓取。实际需求里相当常见的是每个工作表某个固定位置有一个关键信息比如“本月目标”在 F2、“完成率”在 F3需要把所有工作表里的这些散落值提取出来形成一张汇总清单。这种场景用 openpyxl 简直是量身定做import openpyxl wb openpyxl.load_workbook(monthly_reports.xlsx, data_onlyTrue) new_wb openpyxl.Workbook() new_ws new_wb.active new_ws.append([工作表名, 本月目标, 完成率]) for sheet_name in wb.sheetnames: ws wb[sheet_name] target ws[F2].value rate ws[F3].value new_ws.append([sheet_name, target, rate]) new_wb.save(summary.xlsx)这里有一处容易出错ws[F2]返回的是一个Cell对象要拿数据必须取.value。手一抖直接 append 这个 Cell 对象也能生效openpyxl 会把它当成值写进去但如果你后续要拿这个值做计算就会得到一堆 Cell 对象报错排查起来很头疼。所以我现在的习惯是凡是取单元格内容一律在下一行先val cell.value再使用这个变量。3.3 写出汇总文件CSV 与 xlsx 两种输出对比上面两个例子写出的都是新的 xlsx 文件。但很多场景下下游系统不需要 Excel 格式反而要 CSV。CSV 的好处是编码明确、文件小、通用性强但坏处是它不保存格式日期变成纯文本、数字前导零消失、公式也不存在。如果决定输出 CSV上面的代码最后一段改成import csv with open(summary.csv, w, newline, encodingutf-8-sig) as f: writer csv.writer(f) writer.writerow([工作表名, 本月目标, 完成率]) for sheet_name in wb.sheetnames: ws wb[sheet_name] writer.writerow([sheet_name, ws[F2].value, ws[F3].value])这里必须注意encodingutf-8-sig这个参数。UTF-8 编码的文件如果用utf-8Windows 上的 Excel 打开会乱码utf-8-sig会在文件开头加一个 BOM 标记Excel 就能正确识别是 UTF-8。这个坑我踩过不止一次最开始写出的 CSV 在记事本里正常Excel 里全是“锟斤拷”老板差点以为数据被加密了。3.4 实测一个三工作表文件的提取过程我自己做了一个测试文件三个工作表的名字分别是华东、华南、华北每个表的数据都从第二行开始结构一致。运行上面的合并脚本后输出的merged_output.xlsx里数据行数等于三张表有效行数之和表头只出现一次列顺序也和原表一致。整个读取 8000 行数据的过程耗时不到 0.2 秒说明几百 KB 级别的 xlsx 文件openpyxl 处理起来毫无压力。如果想验证结果没有漏行有一个土办法在 Excel 里手动数一下原表每个工作表底部状态栏的“计数”数字再和脚本输出文件的总行数对比。4. 多文件批量提取几十个 xlsx 汇总到一个文件单文件提取只是热身。真正让我觉得“这项技能值钱”的场景是拿十几个甚至上百个 xlsx 文件每个文件里的工作表格式还不完全一样要把指定列的内容汇到一个总表里。4.1 文件遍历与路径处理批量处理的第一步是找到所有要处理的目标文件。用递归遍历最稳妥因为文件可能散落在多层子目录中glob.glob配**可以实现同样的效果但在目录结构特别深的场景下os.walk可读性更好。import os file_list [] for root, dirs, files in os.walk(reports): for f in files: if f.endswith(.xlsx) and not f.startswith(~$): file_list.append(os.path.join(root, f))这里过滤~$前缀是关键。当有人打开 Excel 文件时系统会在同目录生成一个临时副本文件名称通常是~$日报.xlsx它并不是合法的工作簿文件。不排除它load_workbook会直接报InvalidFileException而且这个问题只在 Windows 上出现排查起来特容易懵。4.2 合并规则同构表与异构表批量合并前必须先判断所有文件的表结构是不是一致的。同构表可以直接纵向拼接数据列数天然对齐。异构表就要设计字段映射规则比如 A 文件里“项目名称”在第 2 列B 文件里在第 3 列这时候不能用row[1]和row[2]硬写要改成按表头来定位列。按表头定位的写法非常推荐因为哪怕列顺序变了只要表头名字不变代码还是一样的def find_col_by_header(ws, header_name): header_row next(ws.iter_rows(max_row1, values_onlyTrue)) for idx, val in enumerate(header_row): if val header_name: return idx 1 # openpyxl 的列号从 1 开始 return None这里注意一个容易忽略的细节enumerate(header_row)从 0 开始计数而 openpyxl 的列索引从 1 开始所以必须1。这个 off-by-one 错误在数据处理里太常见了尤其是同一段代码里混用cells和values的时候。4.3 批量汇总完整脚本下面是一个相对完整的脚本遍历reports目录下所有的 xlsx从每张表的 A 列和 B 列提取“项目名称”和“负责人”汇总到all_projects.xlsx。import os import openpyxl result_wb openpyxl.Workbook() result_ws result_wb.active result_ws.append([项目名称, 负责人]) def find_col_by_header(ws, header_name): header_row next(ws.iter_rows(max_row1, values_onlyTrue)) for idx, val in enumerate(header_row): if val header_name: return idx 1 return None for root, dirs, files in os.walk(reports): for f in files: if not f.endswith(.xlsx) or f.startswith(~$): continue file_path os.path.join(root, f) try: wb openpyxl.load_workbook(file_path, data_onlyTrue) except Exception as e: print(f跳过 {file_path}: {e}) continue for sheet_name in wb.sheetnames: ws wb[sheet_name] name_col find_col_by_header(ws, 项目名称) owner_col find_col_by_header(ws, 负责人) if name_col is None or owner_col is None: continue for row in ws.iter_rows(min_row2, values_onlyTrue): if row[name_col - 1] is None: continue result_ws.append([row[name_col - 1], row[owner_col - 1]]) result_wb.save(all_projects.xlsx)写这个脚本时做了一件刻意的事把find_col_by_header独立成一个函数而不是写得很直白。如果在实际项目里碰到建议把这个字段映射的配置改成config.ini或者 JSON不同文件的表头映射关系列成一张表以后调整字段时不用改代码。4.4 验证与核对如何判断没漏没重合并完之后最怕的是看起来文件挺完整实际上某几个文件因为表头名字敲错了一个字被静默跳过了。所以批量脚本里建议加一个统计日志记录每个文件的成功/失败状态和提取行数。我习惯把日志写到一个 CSV 里每次跑完先看这个文件import csv log_rows [] # 在循环中记录 log_rows.append([file_path, sheet_name, extracted_rows]) # 结束后写文件 with open(merge_log.csv, w, newline, encodingutf-8-sig) as f: writer csv.writer(f) writer.writerow([文件路径, 工作表, 提取行数]) writer.writerows(log_rows)有了这个日志“有没有漏文件”一目了然。特别是当文件数量超过 100 个时人眼不可能逐个核对输出结果日志里的行数汇总对照原文件数量就是最可靠的验收依据。5. 踩坑记录提取 xlsx 时的四个高频问题做 Excel 数据提取这件事功能跑通只是第一步真正让人头大的是各种数据格式的“暗坑”。这里把我踩过的、帮别人排查过的高频问题集中列出来每一个都有明确的解决办法。5.1 日期时间变成了数字串原表里明明是2024-01-15用 openpyxl 读出来却是一个datetime.datetime对象打印出来是2024-01-15 00:00:00。如果直接把这个对象写进 CSV导出后就是2024-01-15 00:00:00而不是 Excel 里显示的那种精简格式如果原表日期是手输文本格式读出来的反而就是字符串。同一种格式在不同单元格里读出来类型不同这其实是 Excel 本身不强制校验日期的结果。处理方式很简单在写入之前统一格式化。from datetime import datetime def normalize_date(val): if isinstance(val, datetime): return val.strftime(%Y-%m-%d) return val如果你希望保留时间部分就strftime(%Y-%m-%d %H:%M:%S)。强烈建议所有日期字段都过一遍这个函数避免后续做数据校验时发现类型不一致。5.2 长数字显示成科学计数法最常见的例子是身份证号或者订单号。Excel 单元格里显示的是6.85123e17openpyxl 读出来是一个 float拿到之后第一反应是“数据丢了”。实际上数字精度还在但没有一个整数类型的变量能精确表示 18 位的身份证号因为 Python 的 int 可以无限大但 Excel 的单元格数值存储默认是双精度浮点数超过 15 位有效数字就被四舍五入了。这种情况最好的办法是从源头控制如果原表还没生成建议把这类字段所在的列设置成文本格式再导数据。如果表格已经存在读取时通过参数read_onlyTrue配合data_onlyTrue也救不回丢失的精度。只能妥协接受 float 的近似值或者在 Excel 端先把文本格式的单元格内容拷出来。写代码时有个小技巧可以避免部分麻烦openpyxl 读到的浮点数如果是类似6851234567890123456.0的.0 结尾可以转成 int 再变成字符串num ws[D2].value if isinstance(num, float) and num.is_integer(): text str(int(num))但要注意这个方法只在数字没超过 Excel 存储精度时有效真正的身份证号还是会在源头丢精度。5.3 公式单元格读不到计算值前面提到过data_onlyTrue的作用这里再展开说一下它为什么会产生“读不到值”的错觉。Excel 的文件结构里公式单元格同时保存公式字符串和上一次计算结果的缓存值。openpyxl 在data_onlyFalse默认时读公式在data_onlyTrue时读缓存值。如果你用代码生成的新工作簿还没有被 Excel 打开过那公式单元格大概率没有缓存值data_onlyTrue读出来就是None。遇到None先别怀疑代码写错先用 Excel 打开文件保存一次再运行脚本如果这时能读出数值就说明问题出在缓存。真正的解决方案是场外确认数据源是否有 Excel 自动重算宏或 Python 侧的公式引擎这些超出了 openpyxl 的能力范围不要指望一个库同时做渲染引擎和解析器。5.4 超大文件内存占用爆炸用只读模式一个几十 MB 的 xlsx用默认的load_workbook方式加载内存轻松突破 1GB。原因很简单openpyxl 默认是完整载入模式把 XML 全部解析成对象并常驻内存。解决方案是用read_onlyTrue模式wb openpyxl.load_workbook(big_file.xlsx, read_onlyTrue, data_onlyTrue)只读模式下工作簿不再持有所有对象的引用iter_rows迭代时按需读取内存占用能压到原来的百分之几。需要配合的处理是只读模式下工作簿会占用文件句柄用完必须wb.close()否则在 Windows 上文件会被锁定后续再读取或删除会报权限错误。我用一个 200MB 左右的 xlsx 做过实测默认模式加载耗时约 40 秒内存峰值 1.6GB切换为只读模式后加载时间和内存分别降到了 6 秒和 180MB效果非常显著。6. 按需优化pandas 方案什么时候更合适6.1 pandas 一行代码读 Excel如果前面这些脚本已经能解决日常 90% 的需求那为什么还要提 pandas因为确实有场景让 openpyxl 写法变得很啰嗦比如你要按列筛选、“销售额大于 10000”的行再做分组汇总openpyxl 手写循环 50 行pandas 几行就完事。import pandas as pd df pd.read_excel(2024_sales.xlsx, sheet_nameNone, header0) # df 是一个字典键是工作表名值是 DataFrame all_data pd.concat(df.values(), ignore_indexTrue) result all_data[all_data[销售额] 10000].groupby(产品)[销售额].sum() result.to_excel(sales_summary.xlsx)这里sheet_nameNone会把所有工作表读成一个有序字典一个参数解决了“遍历所有表”的问题确实方便。但 pandas 读取的底层引擎是 openpyxl所以前面提到的缓存值问题依然存在。6.2 两种方案的对比对比维度openpyxlpandas读单元格级数据灵活可操作任意单元格必须按行列统一结构数据处理能力需要手写逻辑自带筛选、分组、聚合内存占用默认高只读模式低会比 openpyxl 更高写回 xlsx支持全部格式控制to_excel也有但格式控制弱学习成本低模型和 Excel 对齐需理解 DataFrame如果你的需求仅仅是把 A 表的某些单元格抄到 B 表pandas 反而绕路如果是读表后做统计pandas 是更合适的选择。6.3 混合使用的实战思路一个常见且比较合理的组合是用 pandas 读取和清洗数据因为处理逻辑简单最后用 openpyxl 写回带格式的 Excel 文件因为需要设定列宽、字号、填充色等。pandas 的to_excel底层也是 openpyxl但控制力有限。我自己在做一个周报自动生成脚本时就是先pd.read_excel汇总再openpyxl.load_workbook打开一个配置好的模板文件把计算结果填到指定单元格最后保留模板的样式。这种做法的巧妙之处在于模板文件里已经预设好页眉、边框和公式openpyxl 只负责填数不管排版。既享受了 pandas 的灵活性又保住了 Excel 的观感。如果你以后遇到“提取 xlsx 表格内容填入单个文件”但还想保持格式的需求这个思路可以直接搬过去用。最后分享一个私人经验不管脚本多简单最后一定要留一个备份源文件的机制。我曾经在一个批量汇总脚本里忘了加只读处理点击运行后输出结果直接覆盖了源文件最后表和汇总表结构不一致返工了大半天。从那以后我的脚本一律要求源目录和输出目录分离输出路径单独定义成变量绝不就地覆盖。这一条比任何技巧都重要。