如果你也被一个 80 多 MB 的 Excel 折磨过应该能体会我上周的感受。客户甩来一张 87 万行的销售流水底表说“帮忙跑个汇总”。我用 pandas 直接 read_excel 一把梭结果内存从 4G 一路飙到 22G跑了整整十几分钟还差点把我笔记本干死机。后来我把处理流程拆成三个方案挨个试才找到了一条稳妥的路。今天想把这三种处理 Excel 大数据文件的方式完整记录下来——pandas 精细化读取、openpyxl 的只读/只写模式、以及先转 CSV 或 SQLite 再分块处理。它们不是替代关系而是适配不同量级和场景的组合拳。不管你是做数据清洗、财务对账还是写自动化报表脚本这篇文章里应该能找到适合你的那一种也省得你再去踩我踩过的内存坑。1. Excel 大文件的内存黑洞先搞懂文件格式再谈优化1.1 Excel 文件是一颗“压缩糖衣炮弹”很多人以为 Excel 是个数据库其实它只是个被压缩包装的 XML 包。你在 Windows 里把.xlsx后缀改成.zip解压开会看到一堆sheet1.xml、styles.xml、sharedStrings.xml之类的文件。表格里每一个单元格的值、样式、公式都变成了一段一段的 XML 文本。这个设计本来是为了让文件在磁盘上更小但对 Python 的处理库来说就是噩梦。读取一个 80MB 的 xlsx实际解压后内存里要展开的 XML 内容可能是 500MB 甚至 1GB。pandas 底层调用 openpyxl 时默认会把整个 sheet 的内容都解析成 Python 对象再拼成一个 1GB 的列表最后再转成 DataFrame。这个中间过程的内存峰值就远远超过了文件本身的大小。明白了这个底层的搬运逻辑你才能理解后面为什么有些方法快、有些方法慢。所有优化方案的核心只有一句话尽可能少地在内存里同时保存数据要么控制读取量要么用迭代器逐行处理要么干脆换个更轻的格式。1.2 到底多大的 Excel 才需要“特殊处理”我经常收到类似“多少行算大数据”的提问。这个问题没有绝对答案主要看你的电脑内存和处理需求。以我自己为例文件量级行数直接 read_excel 的感受5MB 以内1~5 万行秒开随便处理20MB ~ 50MB10~30 万行内存开始紧张能跑但卡80MB ~ 200MB50~200 万行有 MemoryError 风险必须优化200MB 以上超 200 万行建议直接转 CSV / 数据库Excel 单 sheet 本身有 104 万行的硬上限所以真正意义上的“无限大数据”根本不该用 Excel 存。但实际生活和工作中几十万行、七八十个列的大宽表已经很常见这种情况下用 pandas 默认参数一次性读进来内存很容易失控。2. 方案一pandas 精细化读取把内存需求砍到三分之一2.1 engine 选型与基础读取pandas 读取 Excel 有几种底层引擎最常见的是 openpyxl。它的优点是你熟悉、文档多、社区支持好。如果你电脑上装了calamine注意新版 pandas 里也能装可以试试 speed 更快的引擎但为了可复现性我下面都以 openpyxl 为例。基础版代码大家都写过一眼就会import pandas as pd df pd.read_excel(sales_2024.xlsx, engineopenpyxl) print(df.head())这行代码对 10 万行以下的表没问题对大宽表就是灾难。因为你不加任何参数pandas 会做三件事把所有单元格读进来、自动推断每一列的数据类型、保存所有原始文本。这三件事随便哪一件都吃内存。2.2 usecols dtype少读比快读更重要优化内存的第一原则是“能不读的数据就不读”。Excel 表动不动一百多列但很多列对当前任务毫无用处。用usecols只挑需要的列内存消耗能直接降一半以上。import pandas as pd df pd.read_excel( sales_2024.xlsx, engineopenpyxl, sheet_name流水, usecols[日期, 区域, 客户编号, 金额], dtype{ 客户编号: string, 金额: float32, 区域: category, }, ) print(df.info())这里有几个细节值得展开讲一讲usecols可以传列名列表也可以传 Excel 列号范围比如usecolsA:D。但我强烈推荐用列名因为可读性强而且表结构调整后不容易出错。dtype参数是关键。默认情况下pandas 会用object类型去存储文本列这种类型的内存开销极大。如果客户编号这类列实际上不需要参与计算把它指定为string内存会小很多。金额列如果不需要超高精度float64换成float32内存直接少一半。对于千万级的加总float32的精度误差通常可以忽略但如果你要做精确财务核算还是要保留float64这是个小取舍。区域列如果只有几个枚举值转成category类型是内存利器。比如一百万个“华东/华北/华南”字符串如果用category底层只存三个分类编号内存能省几十倍。我记得有一次跑一张 30 万行的订单表就用了这三板斧DataFrame 的 memory usage 从 800MB 掉到 260MB 左右效果立竿见影。2.3 nrows/skiprows 分段读取的适用边界你可能会问那 pandas 像read_csv一样支不支持chunksize很遗憾pd.read_excel没有原生chunksize参数。网上有文章误导别人说可以用nrowsskiprows组合做分块读取我直接告诉你结论能用但性能极差。原因是 openpyxl 引擎每次都会从文件开头解析到你指定的skiprows位置这意味着你分 10 块读就要完整解析文件 10 次。对 50 万行的文件来说这种写法比一次读入还慢内存也并不会好到哪去。# 不推荐。这个写法对读取大 Excel 没有真正的性能优势 start 0 batch_size 50000 while True: df_batch pd.read_excel( sales_2024.xlsx, engineopenpyxl, usecols[金额], skiprowsstart 1, nrowsbatch_size, headerNone, ) if df_batch.empty: break # 处理数据 start batch_size如果非要用 pandas 做分段我建议先转 CSV再用pd.read_csv(chunksize...)。这个思路我会在第四章展开讲。2.4 to_excel 写入大 DataFrame 时要注意什么写入场景同样容易踩坑。最典型的问题是你想把一个很大的 DataFrame 直接to_excel结果写出来的文件又慢又大还不能中途断点续传。df.to_excel(output.xlsx, indexFalse, engineopenpyxl)这行的默认行为是pandas 会先构建一个完整的内存对象再写盘。如果你对 50 万行 × 30 列的 DataFrame 执行这行内存又会飙起来。优化手段是减少列、降低精度或者干脆用下面说的 openpyxlwrite_only模式逐行追加。另外indexFalse一定要加除非你真的需要保留行号否则那个默认索引列会莫名其妙让文件变大。3. 方案二openpyxl 只读/只写模式把 Excel 当数据库游标用3.1 read_onlyTrue 的原理不加载工作簿而逐行解析openpyxl 是 pandas 底层的 Excel 引擎它自己其实有一对“隐藏技能”——read_only和write_only模式。这两个模式才是处理超大 Excel 的正道。正常模式下load_workbook会把整个工作簿的工作表数据全部加载到内存。但如果你传一个read_onlyTrue它就变成了一个流式解析器每次只从 XML 里解出一行数据处理完就丢掉内存占用几乎和文件行数无关。from openpyxl import load_workbook wb load_workbook(sales_2024.xlsx, read_onlyTrue, data_onlyTrue) ws wb[流水] for i, row in enumerate(ws.iter_rows(values_onlyTrue)): if i 0: print(表头:, row) continue # 这里逐行处理 print(row[3]) wb.close()注意参数data_onlyTrue很关键。Excel 单元格里如果存的是公式正常读取拿到的是公式字符串而不是计算结果。data_onlyTrue会尝试拿 Excel 缓存的计算结果。但如果这个文件是程序生成的、从没被 Excel 打开过缓存值可能为空这也是个隐蔽的坑。read_only模式下ws.iter_rows()返回的是一个生成器你可以放心遍历百万行也不会撑爆内存。这本质上就是我开头说的“数据库游标”思路一行一行取用多少取多少。3.2 用 iter_rows 做一个分批读取生成器实际项目中我通常不会在 read_only 模式下直接for裸处理而是把它封装成一个可复用的“分批读取生成器”每一批返回一个 DataFrame。这样既享受了 openpyxl 的低内存读取又能用 pandas 做批量计算。import pandas as pd from openpyxl import load_workbook def read_excel_in_chunks(path, sheet_name, chunk_size50000): wb load_workbook(path, read_onlyTrue, data_onlyTrue) ws wb[sheet_name] header None rows [] for row in ws.iter_rows(values_onlyTrue): if header is None: header row continue rows.append(row) if len(rows) chunk_size: yield pd.DataFrame(rows, columnsheader) rows [] if rows: yield pd.DataFrame(rows, columnsheader) wb.close() # 用法示例 for chunk in read_excel_in_chunks(sales_2024.xlsx, 流水, chunk_size100000): total chunk[金额].sum() print(f本批金额: {total})这个生成器的好处很明显处理 100 万行时任意时刻内存里只有 10 万行数据。而且它把“读 Excel”和“算业务”解耦了后面的代码只需要专注处理 DataFrame 就行。我实际用这个函数处理过 140 万行的进销存底表循环跑了十几个批次内存始终稳定在 2G 以内。这个方案是处理“Excel 单表还没超过 104 万行上限”时的最优解之一。3.3 write_onlyTrue百万行数据的低内存写入读的方向解决了写的方向也有对应方案。Workbook()默认是全功能模式追加很多行时会积累大量样式对象。要批量写超大 Excel应该用write_onlyTrue创建轻量工作簿。from openpyxl import Workbook wb Workbook(write_onlyTrue) ws wb.create_sheet(结果) ws.append([区域, 金额合计]) # 假设这里有一批批很大的结果数据 for region, total in generated_data: ws.append([region, total]) wb.save(result.xlsx)write_only模式下ws.append()会把数据直接序列化到文件不会在内存里积累所有行。它的限制也别忘了不支持读取单元格、不支持合并单元格、不支持图表和图片适合纯数据导出的场景。如果既要写大数据又需要一些基础样式比如表头加粗、冻结窗格你可以在append时按行处理或者等写完后再用正常的 openpyxl 打开文件做一次轻量样式修饰。不过对小文件来说无所谓大文件二次打开也是一笔时间开销建议一次写到位。4. 方案三先转 CSV 或 SQLite 再分块处理绕开 Excel 引擎的终极思路4.1 为什么 CSV 更适合大数据处理Excel 的 XML 结构太重了处理大数据时最彻底的思路是绕开它。CSV 就是纯文本每一行就是一条记录没有样式、没有公式、没有共享字符串表。pandas 读 CSV 时走的是 C 解析器速度比读 Excel 快很多而且还原生支持chunksize分块这才是“流式处理”的正确打开方式。肯定有人会问CSV 不就是把 Excel 表换了个马甲吗不完全是。CSV 没有列宽、没有格式、没有多 sheet但它对齐了“表格数据”最核心的价值——行和列。对于自动化数据处理来说丢掉那些花哨格式反而是优势。4.2 用 openpyxl 写个轻量脚本批量转 CSV既然 pandas 读不了大 Excel那我们就先用流式方式把 Excel 转成 CSV再用 pandas 的read_csv(chunksize...)分块处理。转换这一步用 openpyxl 的read_only就够了。import csv from openpyxl import load_workbook wb load_workbook(sales_2024.xlsx, read_onlyTrue, data_onlyTrue) for sheet_name in wb.sheetnames: ws wb[sheet_name] csv_file f{sheet_name}.csv with open(csv_file, w, newline, encodingutf-8-sig) as f: writer csv.writer(f) for row in ws.iter_rows(values_onlyTrue): writer.writerow(row) wb.close()这里有两个细节我特意想说一下encodingutf-8-sig加 BOM 头这样用 Excel 打开 CSV 时中文不会乱码。如果只是给 pandas 读用utf-8就够了加 BOM 反而会让第一列列名多个看不见的字符。转出来的 CSV 会丢失多 sheet 结构和单元格格式但它非常干净。如果你还需要保留多个 sheet 之间的关系建议直接走 SQLite。4.3 SQLite 中转让“几十万行的 Excel”变成可查询的数据库如果同一个 Excel 文件你要反复统计、多次切片每次都重新解析一遍 Excel 太浪费了。更聪明的做法是先把 Excel 内容灌进 SQLite后面全用 SQL 查询。import sqlite3 import pandas as pd from openpyxl import load_workbook conn sqlite3.connect(warehouse.db) wb load_workbook(sales_2024.xlsx, read_onlyTrue, data_onlyTrue) ws wb[流水] header None rows [] CHUNK 50000 for row in ws.iter_rows(values_onlyTrue): if header is None: header row continue rows.append(row) if len(rows) CHUNK: df_chunk pd.DataFrame(rows, columnsheader) df_chunk.to_sql(sales, conn, if_existsappend, indexFalse) rows [] if rows: df_chunk pd.DataFrame(rows, columnsheader) df_chunk.to_sql(sales, conn, if_existsappend, indexFalse) wb.close() # 之后就能用 SQL 做各种聚合 df_result pd.read_sql( SELECT 区域, SUM(金额) AS total FROM sales GROUP BY 区域, conn, ) print(df_result) conn.close()这套做法的核心价值是“一次导入多次查询”。而且 SQLite 单文件就可以跨机器复制不需要安装独立的数据库服务不管是个人电脑还是服务器都很方便。我自己做月度账单汇总时就喜欢先用这个流程把几十张 Excel 底表灌进库之后想怎么切维度就怎么切。4.4 多进程并行把多核 CPU 用起来Excel 文件如果是多个 sheet或者你已经把它们拆分成了多个 CSV就可以用 Python 的多进程并行处理。注意不是多线程因为 Python 的 GIL 对 CPU 密集型任务限制很大线程池在计算密集场景下几乎加速不了。ProcessPoolExecutor每个子进程有独立内存和独立的 Python 解释器才能真吃满多核。from concurrent.futures import ProcessPoolExecutor import pandas as pd def process_csv(path): total 0.0 for chunk in pd.read_csv(path, chunksize50000): total chunk[金额].sum() return total if __name__ __main__: csv_files [1月.csv, 2月.csv, 3月.csv, 4月.csv] with ProcessPoolExecutor(max_workers4) as pool: results list(pool.map(process_csv, csv_files)) print(各月金额合计:, results) print(总计:, sum(results))有一点必须提醒很多新手在 Windows 上用ProcessPoolExecutor时会发现程序报错或没有反应原因通常是没有写if __name__ __main__:保护。多进程在 Windows 下会重新导入主模块不加保护就会递归启动子进程轻则警告重则崩溃。这是用 Python 做并行处理时最常踩的坑之一。5. 三种方式怎么选对比表格和真实踩坑记录5.1 适用场景对比我决定整理一张表把三种方式的指标罗列清楚方便你直接对照选择。方案典型场景内存占用处理速度格式/样式保留上手难度推荐指数pandas 精细化读取20~50 万行、几十张报表中中差读入后样式丢失低日常首选openpyxl 读写模式50~100 万行、需要流式读取/写入低中中可保留单元格样式中大表优先转 CSV/SQLite 再分块多文件批处理、反复查询、超 104 万行极低高差丢失样式中高终极方案我自己的选择逻辑很简单10 万行以下pandas 一把梭50 万行左右openpyxl 流式处理超过 100 万行、或者要跨多个文件做统计分析直接转 CSV/SQLite。很多项目里我其实会同时混用比如先用 openpyxl 把大表转成 CSV再用 pandas chunksize 分块聚合最后用 openpyxlwrite_only把结果导成给客户的 Excel 报表。5.2 我踩过的坑科学计数法、日期乱码、内存不释放、编码问题读 Excel 大文件时有四个坑我几乎每次都会遇到这里集中记录下来。第一个坑是长数字变科学计数法。Excel 里的长数字列比如订单号、身份证号如果单元格格式是“常规”pandas 读出来可能变成1.23457E17。解决方案是在read_excel时给这一列指定dtypestr或者用 converters 转字符串。如果你已经在用read_csv读 CSV 时同样要指定dtype{订单号: str}否则纯数字的订单号也可能被读成 float。第二个坑是日期串格式混乱。同一张表里“2024-01-01”“20240101”“45000 (Excel 序列号)”三种形态并存的情况非常常见。读进来后建议统一用pd.to_datetime()转格式但要注意指定正确的 format否则 pandas 会猜错。示例df[日期] pd.to_datetime(df[日期], format%Y-%m-%d, errorscoerce)如果这一列里混入了 Excel 序列号pd.to_datetime会直接解析失败返回 NaT。可以先用一个函数把序列号转成日期。Excel 序列号 45000 对应的是 2023-03-22 左右公式是1899-12-30 timedelta(daysserial)。第三个坑是内存不释放。在 Jupyter 里反复read_excel大文件会发现内存越用越多即使del df了也没用。原因是 openpyxl 底层会缓存一些对象而且 pandas 的内存池不会立刻把空闲内存还给操作系统。最有效的办法是把处理逻辑封装成脚本直接跑完退出或强制gc.collect()。如果还是不行考虑换子进程处理数据。第四个坑是编码问题。在把 Excel 转 CSV 时默认的utf-8编码在 Excel 打开会乱码所以我习惯用utf-8-sig。但如果 CSV 只给自己写脚本用保持utf-8就好不然 pandas 读入时表头的第一列会带着一个\ufeff字符还得额外处理。5.3 我的选择建议最后给一个更实际的建议不要追求“用一种方法解决所有问题”。我在实际项目中一般这样分工一次性的快速分析用 pandas usecolsdtype优化够快够省事。需要保留 Excel 格式的自动化报表openpyxlread_only读数据、write_only写结果。多文件、复杂查询、重复统计先把 Excel 转 SQLite后面全部用 SQL。数据量巨大或需要做模型训练直接导出 CSV用pandas.read_csv(chunksize...)做成数据管道。这几种方式配合使用基本能覆盖日常遇到的所有“Excel 大数据文件”读写需求。如果你手头正好有一张怎么读都会卡死的表建议先按顺序试试这三个方案大概率能找到那个让电脑“松了一口气”的解。