前阵子接了个活儿几十个业务方发来的xlsx报表要按规则提取、汇总到一个文件里。这种“python3.8提取xlsx表格内容填入单个文件”的需求听起来就是把Excel当数据库捞一遍真上手时才发现坑不少。我用openpyxl写了个小脚本把目录下所有xlsx指定列抽出来、按金额过滤、最后合并成一张总表从手动复制半小时缩到十几秒。如果你也经常跟Excel报表打交道尤其是批量汇总、定时生成统计文件这类场景这篇文章应该能帮你省不少事。我会从需求拆解、库选型讲起把read_only、data_only、按表头定位这些细节说明白再给出一份可以直接改着用的完整脚本最后顺带整理几个我实测踩过的坑。1. 需求拆解与方案选型填进“单个文件”之前先把数据流看明白1.1 先别急着写代码确认源数据形态和目标文件长相“提取xlsx表格内容填入单个文件”这句话不同人说出来意思可能完全不一样。我把接过的需求大致归成几类你可以自己对号入座源数据形态目标文件形态常见处理方式多个xlsx每个结构相同单个汇总xlsx遍历文件按列提取追加写入单个xlsx里有多个sheet单个汇总sheet遍历sheet按需提取单个xlsx的单个sheet单个csv/txt读取后写文本文件用于导入数据库多个xlsx单个Word/报告模板提取关键字段后套模板通常配合docxtpl这篇文章重点讲最普遍的第一种多个结构相同的xlsx按条件提取若干列最终写入一个汇总xlsx。这个场景覆盖了报表合并、流水归集、数据清洗前的第一步也最容易暴露问题。我个人的习惯是动手写代码前先打开两个源文件人工看一眼。别小看这一步它解决的是“结构相同”到底有多同的问题——列名是“金额”还是“Amount”日期是字符串还是真正的日期格式表头在第几行这些问题光靠猜代码写到一半就会发现全是坑。另外目标文件的长相也要提前定。你是要保留所有原始列还是只留几列要不要加“来源文件”这一列需不需要按某个字段排序这些决定会直接影响脚本里REQUIRED_COLUMNS和写入顺序怎么定义。1.2 python3.8下读xlsx的库选型openpyxl、pandas、xlrd怎么选python3.8环境下处理xlsx主流的库有openpyxl、pandas、xlrd还有前端领域那个操作excel的js工具库xlsx(SheetJS)。很多人会在这些之间犹豫我用一张表把核心区别列出来库能否读xlsx能否写xlsx适用场景python3.8注意事项openpyxl可以可以常规读写、单元格样式、批量提取当前3.x版本均可装openpyxl3.0,3.2最稳妥pandas可以可以复杂聚合、数据清洗、统计分析推荐装2.0.xpandas2.0,2.1新版本可能要求更高pythonxlrd2.0后仅xls不可以读旧版xls文件遇到xls再考虑xlsx直接放弃它前端js库xlsx可以可以浏览器里解析、预览、上传跟python3.8后端联动太绕批处理场景不推荐我最终选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_onlyopenpyxl加载工作簿时有两个参数几乎每个写Excel处理脚本的人都会碰到但很多人搞混data_only和read_only。data_onlyTrue表示“只读数据不读公式”。如果一个单元格里写的是SUM(A1:A10)默认情况下openpyxl读取到的结果是那个公式字符串本身而不是计算后的数值。加了data_onlyTrue后读到的是Excel保存文件时缓存的计算结果。这里有个重要前提如果这个xlsx从来没有被Excel或WPS打开过、保存过那公式的结果压根没算过缓存里是空的data_onlyTrue读出来就是None。这个坑我后面专门讲遇到的人特别多。read_onlyTrue表示“流式只读模式”。它不会把整个工作簿一次性加载进内存而是按行从文件里读适合几百MB的大文件。代价是你不能修改这个工作簿不能访问某些高级API所以它只适合“提取数据”这种单向操作。我的经验是凡是“提取内容”类脚本两个参数都写上。data_onlyTrue避免读到公式字符串read_onlyTrue帮你在文件变大时不至于内存爆掉。from openpyxl import load_workbook # data_only拿到公式缓存结果read_only节省内存 wb load_workbook(销售数据.xlsx, data_onlyTrue, read_onlyTrue)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(row3, column2).value。适合你知道数据固定在第几行第几列的场景比如模板表、固定报表。缺点是硬编码源表稍微调一下格式脚本就废了。第二种是逐行遍历ws.iter_rows(min_row2, values_onlyTrue)。它会返回从第二行开始的所有行每行是一个元组值已经帮你从单元格里剥出来了。这是批量提取时最推荐的方式配合read_onlyTrue还能省内存。第三种是按表头名字动态找列号。这是我认为最重要的技巧。源表格式经常被调整今天“日期”在A列明天可能被挪到C列。与其写死列号不如先读一遍表头建立“表头名 - 列号”的映射再按列名取数据。def find_column_mapping(ws, header_row1): 读取表头行返回 {表头名: 列号} 映射 header_values next( ws.iter_rows(min_rowheader_row, max_rowheader_row, values_onlyTrue) ) return { str(cell.value).strip(): idx for idx, cell in enumerate(header_values, start1) 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(levellogging.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_row1): 读取表头行返回 {表头名: 列号} 的映射 header_values next( ws.iter_rows(min_rowheader_row, max_rowheader_row, values_onlyTrue) ) return { str(cell.value).strip(): idx for idx, cell in enumerate(header_values, start1) 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_row1): 读取单个xlsx返回提取到的行数据列表 rows [] wb load_workbook(filepath, data_onlyTrue, read_onlyTrue) 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_rowheader_row 1, values_onlyTrue): 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, defaultdata, helpxlsx文件所在目录) parser.add_argument(--output, defaultOUTPUT_FILE, help汇总输出文件路径) parser.add_argument(--min_amount, typefloat, defaultMIN_AMOUNT, help金额过滤阈值) parser.add_argument(--header_row, typeint, default1, 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_rowheader_row 1, values_onlyTrue)返回的是每行元组比ws.cell()逐个访问快很多。配合read_onlyTrue大文件也不会把内存吃满。数据行里如果整行都是空直接跳过避免把那些“看起来有样式但其实没内容”的行写进汇总表。最后是写入策略。目标工作簿用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汇总文件打开后大概是这样一张表日期产品金额2025-01-03 00:00:00无线鼠标1299.02025-01-05 00:00:00机械键盘899.02025-02-11 00:00:00显示器支架159.0注意“金额”列是浮点类型写进程保留两位日期保留datetime类型所以Excel里默认显示带时间。如果你不想看到00:00:00可以在写入前转成字符串或者给目标sheet这列设置数字格式。验证结果我的习惯是抽查随机挑两个源文件人工数一下符合条件的行数和日志里打印的数量对一下。数量一致基本就稳了。如果对不上优先怀疑过滤条件写错了比如金额比较时漏了float()转换导致字符串和数字比较。4. 常见问题与排查技巧实录4.1 读不到公式计算值data_onlyTrue返回None怎么办这是碰到最多的坑。场景通常是源表里有VLOOKUP(...)、SUM(...)这类公式你明明看到Excel里显示的是计算结果但openpyxl用data_onlyTrue读出来却是None。原因在于xlsx文件本身存储了两样东西公式文本和最近一次计算的结果缓存。openpyxl不是一个计算引擎它不负责重新计算公式只负责读文件。如果这个文件是机器生成的、或者从来没有被Excel/WPS打开保存过文件里可能只有公式没有结果缓存那data_onlyTrue也无能为力只能读到None。解决办法有这么几个方向用Excel或WPS打开一次源文件然后保存让软件重新计算公式并刷新缓存。对少量文件可以手动操作。如果源文件是程序自动生成的在生成侧想办法先算出结果再写入xlsx而不是只写公式。读取时做兜底如果读到None记录日志并跳过不要让它静默污染汇总结果。if value is None: logger.warning(第%s行金额单元格读到的值是None可能是公式无缓存, row_num) continue注意data_onlyTrue和read_onlyTrue是两个独立参数别以为设置了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_onlyTrue就是救命的。但read_onlyTrue有几个限制要提前知道不能修改工作簿只能读。部分API在read-only模式下不可用比如按行索引直接访问。ws.cell()在这模式下可能行为异常所以遍历数据尽量用iter_rows(values_onlyTrue)。实际使用中我通常这样组合小文件几MB以内就直接默认模式省事大文件一律read_onlyTrue并且只迭代需要的行区间不遍历整张表。# 大文件推荐只读模式 按行迭代 wb load_workbook(big.xlsx, data_onlyTrue, read_onlyTrue) ws wb.worksheets[0] for row in ws.iter_rows(min_row2, values_onlyTrue): # 每一行是一个tuple处理完就丢弃内存不会累积 pass wb.close()还有一个特别容易忽略的点iter_rows(values_onlyTrue)每行返回的元组长度取决于该行最大有内容的列数而不是固定等于表头列数。所以如果你靠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()专门处理这种情况。建议你也保留这步很多奇奇怪怪的匹配失败都是不可见字符惹的祸。症状可能原因排查方向读出来全是None公式无缓存或单元格是合并区域非左上角查公式缓存查合并单元格列匹配不上表头有多余空格、换行符用strip()清洗打印repr看原始字符数据顺序乱glob未排序sorted()包一层保存失败文件被Excel占用try/except捕获PermissionError5. 进阶玩法与个人经验让汇总文件一眼看懂5.1 用openpyxl给汇总表加进度条数据条条件格式需求做到后期经常会遇到“能不能让百分比那列显示进度条”这种要求。Excel里那个进度条效果本质不是单元格内容而是条件格式里的“数据条(Data Bar)”。用openpyxl完全可以写进xlsx而且不需要额外依赖。假设汇总表里有一列“完成率”数值范围0到100你想让它显示成可视化的进度条代码是这样的from openpyxl.formatting.rule import DataBarRule # 在“完成率”列上添加数据条条件格式 rule DataBarRule( start_typenum, start_value0, end_typenum, end_value100, color638EC6, showValueTrue, ) 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 21日志一定要留否则隔天发现数据不对连排查入口都没有。脚本里已经有logging输出重定向到文件以后出问题可以往回翻。跑通这个脚本之后我给自己定了两个习惯每个源文件先打印表头核对列名绝不凭印象写死列号汇总过程保留一份失败文件清单不静默跳过。这两个习惯救了我好几次尤其是源表格式突然被人改了一列的时候。如果你的需求不是汇总到xlsx而是灌进csv、txt甚至填到Word模板里核心思路也差不多先摸清源表结构再想清楚目标文件长什么样中间那层转换逻辑用openpyxl就能撑起来。这套流程跑顺之后日报、周报这类重复劳动基本就能脱手了。