Python3.8+openpyxl实现多Excel表格批量提取汇总到单个文件

前阵子接了个活儿，几十个业务方发来的xlsx报表要按规则提取、汇总到一个文件里。这种“python3.8提取xlsx表格内容填入单个文件”的需求，听起来就是把Excel当数据库捞一遍，真上手时才发现坑不少。我用openpyxl写了个小脚本，把目录下所有xlsx指定列抽出来、按金额过滤、最后合并成一张总表，从手动复制半小时缩到十几秒。

如果你也经常跟Excel报表打交道，尤其是批量汇总、定时生成统计文件这类场景，这篇文章应该能帮你省不少事。我会从需求拆解、库选型讲起，把read_only、data_only、按表头定位这些细节说明白，再给出一份可以直接改着用的完整脚本，最后顺带整理几个我实测踩过的坑。

1. 需求拆解与方案选型：填进“单个文件”之前，先把数据流看明白

1.1 先别急着写代码：确认源数据形态和目标文件长相

“提取xlsx表格内容填入单个文件”这句话，不同人说出来意思可能完全不一样。我把接过的需求大致归成几类，你可以自己对号入座：

这篇文章重点讲最普遍的第一种：多个结构相同的xlsx，按条件提取若干列，最终写入一个汇总xlsx。这个场景覆盖了报表合并、流水归集、数据清洗前的第一步，也最容易暴露问题。

我个人的习惯是：动手写代码前，先打开两个源文件人工看一眼。别小看这一步，它解决的是“结构相同”到底有多同的问题——列名是“金额”还是“Amount”？日期是字符串还是真正的日期格式？表头在第几行？这些问题光靠猜，代码写到一半就会发现全是坑。

另外，目标文件的长相也要提前定。你是要保留所有原始列，还是只留几列？要不要加“来源文件”这一列？需不需要按某个字段排序？这些决定会直接影响脚本里REQUIRED_COLUMNS和写入顺序怎么定义。

1.2 python3.8下读xlsx的库选型：openpyxl、pandas、xlrd怎么选

python3.8环境下处理xlsx，主流的库有openpyxl、pandas、xlrd，还有前端领域那个操作excel的js工具库xlsx(SheetJS)。很多人会在这些之间犹豫，我用一张表把核心区别列出来：

我最终选openpyxl，原因是它的API足够底层，能清楚看到每个单元格的值类型，而且是纯python实现，pip install openpyxl就能用，不依赖pandas那一整套东西。你如果只是“提取内容填入单个文件”，openpyxl是工作量最小、心智负担最低的方案。

有人会在搜索引擎里看到那个操作excel的js工具库xlsx，想在python里用。我的看法是：除非你的场景是网页端让用户上传文件后在浏览器里预览，否则后端批处理老老实实用openpyxl。强行把前端解析、接口传输、后端落盘串起来，只是给自己多造了几个中间环节。

提示：如果源文件是老式.xls格式，建议先用WPS或Excel批量另存为.xlsx，或者用xlrd读出来再交给openpyxl写。混用格式会让代码复杂一倍，能避免尽量避免。

2. 核心细节解析与实操要点：读懂xlsx单元格的脾气

2.1 load_workbook的两个关键参数：data_only与read_only

openpyxl加载工作簿时有两个参数，几乎每个写Excel处理脚本的人都会碰到，但很多人搞混：data_only和read_only。

data_only=True表示“只读数据，不读公式”。如果一个单元格里写的是=SUM(A1:A10)，默认情况下openpyxl读取到的结果是那个公式字符串本身，而不是计算后的数值。加了data_only=True后，读到的是Excel保存文件时缓存的计算结果。这里有个重要前提：如果这个xlsx从来没有被Excel或WPS打开过、保存过，那公式的结果压根没算过，缓存里是空的，data_only=True读出来就是None。这个坑我后面专门讲，遇到的人特别多。

read_only=True表示“流式只读模式”。它不会把整个工作簿一次性加载进内存，而是按行从文件里读，适合几百MB的大文件。代价是你不能修改这个工作簿，不能访问某些高级API，所以它只适合“提取数据”这种单向操作。

我的经验是：凡是“提取内容”类脚本，两个参数都写上。data_only=True避免读到公式字符串，read_only=True帮你在文件变大时不至于内存爆掉。

from openpyxl import load_workbook # data_only拿到公式缓存结果，read_only节省内存 wb = load_workbook("销售数据.xlsx", data_only=True, read_only=True)

2.2 单元格值类型：日期、数字、文本、空值到底怎么处理

openpyxl读出来的单元格值，类型就那么几种：int、float、str、datetime、bool、None。看起来简单，实际处理时恰恰是这里最容易出错。

日期单元格读出来是datetime.datetime对象，不是字符串。如果你直接把它写进汇总表，没问题；如果你想拼成文本、做文件名、写进csv，就必须先格式化。我之前有一次偷懒，直接把datetime对象转成了字符串塞进csv，结果出来的是一串2024-01-15 00:00:00，跟源表展示的2024/1/15完全不一样，下游同事差点没认出来。

数字更要注意。excel里看起来是整数的单元格，openpyxl可能返回3.0而不是3，因为底层存储就是浮点。处理金额的时候，最好统一float(value)再按需round()，避免后面算合计时精度对不上。

空值的处理取决于业务语义。有些场景空白就代表“没有”，有些场景要区分“0”和“空”。我通常会把None转成空字符串""，这样写进汇总表后，Excel里不会出现“无”或“0”这类容易误导的内容。如果下游要导入数据库，空字符串和NULL又不一样，需要另说。

def normalize_cell(value): """统一单元格值：None转空串，浮点保留两位，其余原样返回""" if value is None: return "" if isinstance(value, float): return round(value, 2) return value

用这个函数包一层，后续做判断、写文件都能省很多事。

2.3 定位单元格和遍历行：按坐标、按行、按表头

读取xlsx时，定位数据有三种常见姿势，选对了效率差很多。

第一种是精确坐标访问：ws.cell(row=3, column=2).value。适合你知道数据固定在第几行第几列的场景，比如模板表、固定报表。缺点是硬编码，源表稍微调一下格式，脚本就废了。

第二种是逐行遍历：ws.iter_rows(min_row=2, values_only=True)。它会返回从第二行开始的所有行，每行是一个元组，值已经帮你从单元格里剥出来了。这是批量提取时最推荐的方式，配合read_only=True还能省内存。

第三种是按表头名字动态找列号。这是我认为最重要的技巧。源表格式经常被调整，今天“日期”在A列，明天可能被挪到C列。与其写死列号，不如先读一遍表头，建立“表头名 -> 列号”的映射，再按列名取数据。

def find_column_mapping(ws, header_row=1): """读取表头行，返回 {表头名: 列号} 映射""" header_values = next( ws.iter_rows(min_row=header_row, max_row=header_row, values_only=True) ) return { str(cell.value).strip(): idx for idx, cell in enumerate(header_values, start=1) if cell.value is not None }

有了这个映射，即使源表列顺序变了，只要列名没变，脚本就能继续跑。这是我在实际项目里被“表头顺序又变了”逼出来的必备技能。

3. 实操过程与核心环节实现：写一个可复用的xlsx汇总脚本

3.1 完整代码：目录下所有xlsx按条件提取写入单个文件

下面是我实际用过的脚本结构，功能是：遍历data目录下所有.xlsx，取第一个sheet，按表头名读取“日期”“产品”“金额”三列，过滤掉金额小于1000的行，最终把所有数据合并写入汇总结果.xlsx。代码设计成命令行可调，目录、输出路径、过滤阈值、表头行号都能传参。

import argparse import logging import sys from pathlib import Path from openpyxl import Workbook, load_workbook logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s") logger = logging.getLogger("xlsx-summary") REQUIRED_COLUMNS = ["日期", "产品", "金额"] MIN_AMOUNT = 1000.0 OUTPUT_FILE = "汇总结果.xlsx" def find_column_mapping(ws, header_row=1): """读取表头行，返回 {表头名: 列号} 的映射""" header_values = next( ws.iter_rows(min_row=header_row, max_row=header_row, values_only=True) ) return { str(cell.value).strip(): idx for idx, cell in enumerate(header_values, start=1) if cell.value is not None } def normalize_cell(value): """统一单元格值：None转空串，浮点保留两位，其余原样返回""" if value is None: return "" if isinstance(value, float): return round(value, 2) return value def extract_file(filepath, header_row=1): """读取单个xlsx，返回提取到的行数据列表""" rows = [] wb = load_workbook(filepath, data_only=True, read_only=True) try: ws = wb.worksheets[0] # 默认取第一个sheet col_map = find_column_mapping(ws, header_row) missing = [c for c in REQUIRED_COLUMNS if c not in col_map] if missing: logger.warning("%s 缺少列 %s，跳过该文件", filepath.name, missing) return rows date_col = col_map["日期"] product_col = col_map["产品"] amount_col = col_map["金额"] for row in ws.iter_rows(min_row=header_row + 1, values_only=True): if not any(row): continue amount_raw = row[amount_col - 1] try: amount = float(amount_raw) except (TypeError, ValueError): continue if amount < MIN_AMOUNT: continue rows.append([ normalize_cell(row[date_col - 1]), normalize_cell(row[product_col - 1]), amount, ]) finally: wb.close() return rows def main(): parser = argparse.ArgumentParser(description="把目录下所有xlsx的指定列按条件提取汇总到单个xlsx") parser.add_argument("--input_dir", default="data", help="xlsx文件所在目录") parser.add_argument("--output", default=OUTPUT_FILE, help="汇总输出文件路径") parser.add_argument("--min_amount", type=float, default=MIN_AMOUNT, help="金额过滤阈值") parser.add_argument("--header_row", type=int, default=1, help="表头所在行号") args = parser.parse_args() src_dir = Path(args.input_dir) files = sorted(src_dir.glob("*.xlsx")) if not files: logger.error("%s 下没有xlsx文件，请确认路径", src_dir) sys.exit(1) wb = Workbook() ws = wb.active ws.title = "汇总" ws.append(["日期", "产品", "金额"]) total = 0 for filepath in files: try: rows = extract_file(filepath, args.header_row) except Exception as exc: logger.exception("处理 %s 出错: %s", filepath.name, exc) continue logger.info("%s -> %d 行", filepath.name, len(rows)) for row in rows: ws.append(row) total += len(rows) ws.column_dimensions["A"].width = 22 ws.column_dimensions["B"].width = 30 ws.column_dimensions["C"].width = 12 wb.save(args.output) logger.info("全部完成，共提取 %d 行，保存到 %s", total, args.output) if __name__ == "__main__": main()

这个脚本我尽量保持功能完整但不过度设计。你在自己项目里可以直接改REQUIRED_COLUMNS、MIN_AMOUNT和extract_file里的过滤逻辑，然后跑python merge_xlsx.py --input_dir ./data --output ./结果.xlsx就行。

3.2 关键环节逐段拆解：路径遍历、按表头取列、写入追加

脚本里几个关键点，我单独拆开讲，因为每个都对应一类实际问题。

首先是目录遍历。我用的是Path.glob("*.xlsx")，然后用sorted()排序。这里有个细节：glob()返回的文件顺序不固定，同一个目录两次运行结果可能不同。汇总类脚本最好保持输出稳定，所以必须排序。如果源文件在子目录里，需要递归查找就用rglob("*.xlsx")，但要注意会不会误读临时文件和备份文件。

其次是按表头取列。find_column_mapping读的是表头行，返回一个列名到列号的字典。这种做法最大的好处是“列顺序随便动”，脚本不用改。比如源表把“产品”列从B列挪到D列，只要表头文字还是“产品”，映射就能找对。

然后是读取数据的性能设计。iter_rows(min_row=header_row + 1, values_only=True)返回的是每行元组，比ws.cell()逐个访问快很多。配合read_only=True，大文件也不会把内存吃满。数据行里如果整行都是空，直接跳过，避免把那些“看起来有样式但其实没内容”的行写进汇总表。

最后是写入策略。目标工作簿用Workbook()新建，先append表头，再循环里逐行append数据。append会自动接着已有数据的下一行写，不需要手动维护行号，省心。

3.3 实际运行效果与本地验证

在真实数据上跑一遍，输出大概长这样：

2025-01-05 09:00:00 - INFO - 一月销售数据.xlsx -> 128 行 2025-01-05 09:00:01 - INFO - 二月销售数据.xlsx -> 95 行 2025-01-05 09:00:02 - INFO - 三月销售数据.xlsx -> 143 行 2025-01-05 09:00:03 - INFO - 全部完成，共提取 366 行，保存到 汇总结果.xlsx

汇总文件打开后大概是这样一张表：

注意“金额”列是浮点类型，写进程保留两位；日期保留datetime类型，所以Excel里默认显示带时间。如果你不想看到00:00:00，可以在写入前转成字符串，或者给目标sheet这列设置数字格式。

验证结果我的习惯是抽查：随机挑两个源文件，人工数一下符合条件的行数，和日志里打印的数量对一下。数量一致基本就稳了。如果对不上，优先怀疑过滤条件写错了，比如金额比较时漏了float()转换，导致字符串和数字比较。

4. 常见问题与排查技巧实录

4.1 读不到公式计算值：data_only=True返回None怎么办

这是碰到最多的坑。场景通常是：源表里有=VLOOKUP(...)、=SUM(...)这类公式，你明明看到Excel里显示的是计算结果，但openpyxl用data_only=True读出来却是None。

原因在于xlsx文件本身存储了两样东西：公式文本和最近一次计算的结果缓存。openpyxl不是一个计算引擎，它不负责重新计算公式，只负责读文件。如果这个文件是机器生成的、或者从来没有被Excel/WPS打开保存过，文件里可能只有公式没有结果缓存，那data_only=True也无能为力，只能读到None。

解决办法有这么几个方向：

• 用Excel或WPS打开一次源文件，然后保存，让软件重新计算公式并刷新缓存。对少量文件可以手动操作。
• 如果源文件是程序自动生成的，在生成侧想办法先算出结果再写入xlsx，而不是只写公式。
• 读取时做兜底：如果读到None，记录日志并跳过，不要让它静默污染汇总结果。

if value is None: logger.warning("第%s行金额单元格读到的值是None，可能是公式无缓存", row_num) continue

注意：data_only=True和read_only=True是两个独立参数，别以为设置了data_only就自动省内存。两者各管各的。

4.2 中文文件名、路径和文件占用导致的麻烦

python3.8里处理中文文件名，用pathlib.Path基本没问题。但有几个细节容易绊倒人：

第一，glob出来的文件名排序如果直接用字符串排序，中文会按Unicode码点排，可能跟你大脑里的“拼音序”不一致。如果汇总结果需要按文件名顺序展示，建议在文件名里带上数字前缀，比如01_华东.xlsx、02_华南.xlsx，排序结果才符合直觉。

第二，输出文件保存时如果目标xlsx正被Excel打开，wb.save()会报PermissionError。这个错误处理很简单：保存前检查文件是否被占用，或者干脆在异常里提示“请先关闭Excel里的同名文件”。

try: wb.save(args.output) except PermissionError: logger.error("保存失败：%s 被其他程序占用，请先关闭", args.output) sys.exit(1)

第三，涉及到csv输出时，中文容易乱码。同样是写文件，csv默认编码得显式指定utf-8-sig，否则Excel打开csv时中文会变成乱码。xlsx内部是XML标准，用openpyxl写没有这个烦恼。

4.3 大文件性能差、内存占用高：read_only怎么用才对

文件大了以后，比如几十MB甚至几百MB的xlsx，直接load_workbook默认模式会把整个文件读进内存，轻则卡顿，重则OOM。这时候read_only=True就是救命的。

但read_only=True有几个限制要提前知道：

• 不能修改工作簿，只能读。
• 部分API在read-only模式下不可用，比如按行索引直接访问。
• ws.cell()在这模式下可能行为异常，所以遍历数据尽量用iter_rows(values_only=True)。

实际使用中，我通常这样组合：小文件（几MB以内）就直接默认模式，省事；大文件一律read_only=True，并且只迭代需要的行区间，不遍历整张表。

# 大文件推荐：只读模式 + 按行迭代 wb = load_workbook("big.xlsx", data_only=True, read_only=True) ws = wb.worksheets[0] for row in ws.iter_rows(min_row=2, values_only=True): # 每一行是一个tuple，处理完就丢弃，内存不会累积 pass wb.close()

还有一个特别容易忽略的点：iter_rows(values_only=True)每行返回的元组长度，取决于该行最大有内容的列数，而不是固定等于表头列数。所以如果你靠row[3]取第四列，碰到一行在第四列之后才有内容，前面的索引可能取错位置。稳妥做法是结合表头映射，用变量保存列号再取，而不是硬编码“第几个元素”。

4.4 多级表头、合并单元格、不规则表头怎么提取

现实里的源表很少是标准的一行表头。常见情况有三种：

第一种是多级表头。第一行是大类，第二行才是真正的字段名。这种情况直接把header_row参数从1改成2就行。但如果第一行还有合并单元格，iter_rows读出来第一行会有大量None，干扰判断。我的做法是先打印前几行，确认表头在哪一行，再传--header_row 2。

第二种是合并单元格。openpyxl读取合并单元格时，只有左上角的单元格有值，其他区域是None。比如A1:C1合并后写了“销售汇总”，B1和C1读出来都是None。处理函数里要过滤掉这些空值，否则表头映射会漏列。

第三种是表头里有隐藏字符，比如“金额 ”后面带了个空格。find_column_mapping里我已经做了str(cell.value).strip()，专门处理这种情况。建议你也保留这步，很多奇奇怪怪的匹配失败都是不可见字符惹的祸。

5. 进阶玩法与个人经验：让汇总文件一眼看懂

5.1 用openpyxl给汇总表加进度条（数据条条件格式）

需求做到后期，经常会遇到“能不能让百分比那列显示进度条”这种要求。Excel里那个进度条效果，本质不是单元格内容，而是条件格式里的“数据条(Data Bar)”。用openpyxl完全可以写进xlsx，而且不需要额外依赖。

假设汇总表里有一列“完成率”，数值范围0到100，你想让它显示成可视化的进度条，代码是这样的：

from openpyxl.formatting.rule import DataBarRule # 在“完成率”列上添加数据条条件格式 rule = DataBarRule( start_type="num", start_value=0, end_type="num", end_value=100, color="638EC6", showValue=True, ) ws.conditional_formatting.add("E2:E100", rule)

这里有几个点要说明一下。起点类型和终点类型我用的都是“数值型”num，如果你希望Excel自动按整列最大值缩放，可以把这两项改成min/max。color是数据条颜色，showValue控制单元格里是否同时显示数字。

需要注意的是，openpyxl只是把条件格式规则写进xlsx文件，最终那个彩色进度条是Excel打开文件时自己渲染出来的。你用文本编辑器打开xlsx看不到，用WPS或Excel打开就能看到效果。这个特性也不影响底层数据，你随时可以去掉格式规则。

5.2 把脚本变成能复用的小工具：命令行参数与定时执行

脚本写到第三版的时候，我建议你把路径、阈值这些全都参数化，而不是每次改代码。上面给的例子已经用argparse做好了：--input_dir指定源目录，--output指定输出文件，--min_amount控制过滤阈值。这样你可以放到计划任务里，每天自动跑。

Windows上的做法是“任务计划程序”，Linux/macOS上是cron。核心命令就是一条python调用，比如每天凌晨两点执行：

python merge_xlsx.py --input_dir D:/data/sales --output D:/data/汇总.xlsx --min_amount 500 >> D:/data/run.log 2>&1

日志一定要留，否则隔天发现数据不对，连排查入口都没有。脚本里已经有logging输出，重定向到文件以后，出问题可以往回翻。

跑通这个脚本之后，我给自己定了两个习惯：每个源文件先打印表头核对列名，绝不凭印象写死列号；汇总过程保留一份失败文件清单，不静默跳过。这两个习惯救了我好几次，尤其是源表格式突然被人改了一列的时候。如果你的需求不是汇总到xlsx，而是灌进csv、txt甚至填到Word模板里，核心思路也差不多：先摸清源表结构，再想清楚目标文件长什么样，中间那层转换逻辑用openpyxl就能撑起来。这套流程跑顺之后，日报、周报这类重复劳动基本就能脱手了。