最近帮同事处理了一个 2GB 的订单 CSV他原本想把文件直接拖进 Excel结果 Excel 直接卡死风扇转了十分钟都没缓过来。最后的需求落到了这个标题上把大型 CSV 文件按指定行数拆分到 Excel 的不同工作表中。我花了半小时写了段脚本把 200 多万行数据按每表 30 万行拆成了 7 个工作表同事拿走后不仅不再卡顿还能按分区分发给不同业务小组。这种需求在数据处理岗位实在太常见了接到一个超大 CSV要么是 Excel 打不开要么是打开后操作严重卡顿要么是希望把数据分块后分给不同的人。今天就把我从需求拆解到完整实现的整个思路写清楚包含可直接复制的 Python 代码、参数设计的理由以及我在实际过程中踩过的坑。1. 需求拆解与方案选型CSV 拆分到 Excel 到底在拆什么1.1 三个必须做拆分的现实理由先把问题看清。很多人以为“拆分 CSV 到 Excel”只是一个简单的格式转换实际上背后藏着三个完全不同的痛点。第一个痛点是 Excel 行数上限。Excel 2007 及之后的版本单个工作表最多能装 1048576 行数据注意这是含表头的总数实际可用数据行是 1048575 行。我曾经接过一个数据量超过 150 万行的 CSV直接粘贴到 Excel 时会发现后半段数据莫名其妙地消失这就是行数上限在作怪。所以只要 CSV 超过 100 万行拆分就不是优化选项而是前置条件。第二个痛点是文件打开和操作性能。即使行数没超过 1048576一个 50 万行、20 列的文件放进单个工作表Excel 打开时内存占用就非常夸张滚动、筛选、排序动不动就“未响应”。把数据按合理规模拆到多个工作表中每个表控制在 20-30 万行Excel 的运行才会回到可接受的范围。考虑到国内普遍使用的办公电脑配置参差不齐这个阈值我一般还会再保守一些。第三个痛点是业务分发需求。我遇到最多的实际场景是数据文件需要提供给多个团队每个团队只关心其中一段行数范围的数据。与其把整个大文件发出去让各团队自己筛不如直接按指定行数拆成多个工作表每个表保存一部分数据各取所需。这种方式对齐的是业务的使用方式单纯从技术角度很容易忽略。1.2 方案选型为什么我选了 pandas openpyxl针对这个需求市面上大致有三类方案Power Query 手工操作、VBA 宏处理、Python 脚本。我最终选定了 Python 里的 pandas 配合 openpyxl 引擎写入 Excel。Power Query 是 Excel 自带的数据处理工具它可以读取 CSV 并按条件拆分但问题在于它需要手工配置查询步骤对超大数据量时刷新极其缓慢而且遇到 100 万行以上数据时加载策略不好控制。如果是一次性任务Power Query 勉强能用如果这种需求每周都会出现每次都重新配置一遍查询效率太低了。VBA 宏也是很多人想到的方案但 VBA 写起来繁琐处理大文件时 UI 线程卡死错误排查也不直观。更麻烦的是Excel 的宏安全性设置、文件格式要求需要 xlsm 后缀就把一部分人挡在外面了在企业环境里还经常会被安全策略禁用。最后剩 Python。选 pandas 的核心原因有两个一是它读取 CSV 时可以分块迭代控制内存峰值后面会专门展开二是 pandas 的 to_excel 配合 ExcelWriter 可以轻松创建多工作表 Excel 文件代码量比原生方案小很多。写入引擎我用 openpyxl兼容性最好不依赖额外环境。1.3 环境准备与版本说明动手前先把环境准备好。你需要安装 pandas 和 openpyxl 两个库直接 pip 安装即可pip install pandas openpyxl注意版本兼容问题。我在实际操作中用 pandas 2.0 以上版本时pd.ExcelWriter默认引擎会根据扩展名自动选择但如果你同时装有 xlsxwriterpandas 的默认行为可能会有细微差异。稳妥的做法是在代码里显式指定engineopenpyxl避免不同机器上行为不一致。安装完成后可以用下面这段代码验证环境是否正常import pandas as pd print(pd.__version__) import openpyxl print(openpyxl.__version__)只要版本能正常打印出来环境就准备好了。如果你的环境是 Python 2 或者 pandas 0.x 老版本建议直接升级后面提到的很多 API 语法在老版本里完全不同。2. 核心拆分逻辑按指定行数分片先搞懂这几件事2.1 行数怎么算数据行、表头行和 Excel 行数上限“按指定行数拆分”这句话听起来简单但实际操作中第一个要确认的问题是这个行数到底包含不包含表头比如你指定每表 10 万行那么每个工作表应该是“1 行表头 10 万行数据”还是“表头 99999 行数据”我从业务沟通中总结的经验是绝大多数人说的行数指的是数据行数表头要单独保留。所以最终每个 sheet 的实际 Excel 行数 指定行数 1表头行这个细节一定要跟需求方确认清楚。另一个必须做的事是设置安全边界。我之前收到过这样的参数用户指定每表 200 万行。这个数量已经超出了 Excel 单表的物理上限如果不在代码里拦截写入后 Excel 打开时会报“文件格式或文件扩展名无效”或者直接读不出数据。所以代码里必须加一道限制rows_per_sheet min(rows_per_sheet, 1048575)把用户传入的行数强制限制在 Excel 容量范围内。这是一个防呆设计实际写代码时不要省略。2.2 分块读取原理为什么不能一把梭 read_csv很多人第一次写这个需求时会直接写pd.read_csv(file)把整个 CSV 读进内存然后按行数切片再写到 Excel。这种写法在文件小于 500MB 时可能没问题但一旦文件达到 GB 级别一次read_csv就会把内存直接打满严重时电脑卡死甚至进程被系统直接杀掉。正确的思路是分块迭代读取。pandas 的read_csv支持chunksize参数指定后返回的不是一个完整 DataFrame而是一个 TextFileReader 迭代器。每次迭代只从磁盘读取指定行数的数据块处理完后这一块就可以被释放掉内存峰值被控制在很低的水平。这里面的原理值得多说一句。read_csv在读取时会把整个文件加载到内存中再解析成 DataFrame文件越大耗时和内存开销就越不可控。而分块读取本质上是一个生产-消费模型Reader 对象维护着文件指针每次迭代只读取一小段处理完丢弃内存里永远只有当前正在处理的数据块。等于把一个大任务切成了多个小任务逐段执行这是处理大型 CSV 文件的标准姿势。正因为这个原因我要求代码里必须使用分块读取而不是一次性加载。即使 CSV 文件只有 200MB也建议保持分块的习惯因为你无法保证下次拿到的是一个 4.7GB 的文件。2.3 Sheet 命名规则与表头保留策略拆分生成的多个工作表需要有一套清晰的命名规则不然文件发给别人后对方看到的是一堆 Sheet1、Sheet2根本不知道对应哪段数据。最简单的方案是允许用户自定义前缀用“前缀 序号”的方式。比如指定前缀为“订单数据”生成的工作表依次是“订单数据_1”“订单数据_2”。这个命名规则在 Excel 中不会重名也保留了原始语义。要注意的是 Excel 对工作表名称有两个硬性限制名称长度不能超过 31 个字符名称中不能包含\ / ? * [ ] :这几个字符。如果你的 CSV 文件名本身带有这些特殊字符直接作为 sheet 名会报错需要在代码里做字符过滤。表头保留策略也很关键。每个独立的数据块都需要表头否则只看某个 sheet 时根本不知道每一列是什么含义。分块读取时第一个块的第一行会被 pandas 自动解析成列名从第二块开始就不存在表头了但这些列名会一直被保留在 DataFrame 的 columns 属性中。用to_excel写入时会自动带上表头所以只要第一块的表头解析正确后续每一块写入到 Excel 时都会自动带上列名不需要额外处理。3. 完整代码实现与逐段解析3.1 核心函数按指定行数拆分 CSV 到 Excel 多工作表先给出一版可以直接用的核心实现。这个函数是经过实际项目打磨的版本覆盖了参数校验、表头保留、sheet 命名和进度输出。import os import time import pandas as pd def split_csv_to_excel(csv_file, rows_per_sheet, excel_fileNone, sheet_prefixsheet, encodingutf-8): 将大型 CSV 文件按指定行数拆分到 Excel 的不同工作表。 参数 csv_file: 输入的 CSV 文件路径 rows_per_sheet: 每个工作表的数据行数不含表头 excel_file: 输出的 xlsx 文件路径默认与 csv 同目录 sheet_prefix: 工作表名前缀 encoding: CSV 文件编码默认 utf-8 返回 生成的工作表个数 if rows_per_sheet 0: raise ValueError(每表行数必须是正整数) # 强制限制在 Excel 单表最大行数范围内 rows_per_sheet min(rows_per_sheet, 1048575) if excel_file is None: base os.path.splitext(csv_file)[0] excel_file f{base}_拆分结果.xlsx total_rows 0 sheet_count 0 start_time time.time() with pd.ExcelWriter(excel_file, engineopenpyxl) as writer: for chunk in pd.read_csv(csv_file, chunksizerows_per_sheet, encodingencoding): sheet_count 1 sheet_name f{sheet_prefix}_{sheet_count} chunk.to_excel(writer, sheet_namesheet_name, indexFalse) total_rows len(chunk) elapsed time.time() - start_time print(f[{elapsed:.1f}s] 已写入 {sheet_name}, f当前表行数 {len(chunk)}, 累计行数 {total_rows}) print(f拆分完成: 共 {sheet_count} 个工作表, f总行数 {total_rows}, 用时 {time.time() - start_time:.1f}s) return sheet_count这个函数的关键点在于with pd.ExcelWriter(...) as writer。ExcelWriter 是一个资源对象写入多个工作表后会占用文件句柄如果不用with或者不在结束时显式调用close()生成的 Excel 文件很可能会损坏表现为文件打不开或者内容丢失。用上下文管理器保证资源释放这是我在实际中踩过一次坑后才养成的习惯。还有一点要解释chunk.to_excel(writer, sheet_namesheet_name, indexFalse)这个调用第二个参数传入的是 writer 而不是文件路径这代表的是“把当前 chunk 追加为一个新的工作表写入到已经打开的 Excel 文件中”。如果这里写成chunk.to_excel(output.xlsx, sheet_namexxx)每次都会被当作一个新文件写入最终只会剩下最后一个工作表。这个 API 的使用方式很容易被搞混。3.2 应对无表头 CSV 的变体上面的函数默认 CSV 的第一行是表头这是最常见的情况。但实际工作中我遇到过很多导出的原始 CSV 完全不包含表头第一行就是数据。这时如果还用上面的写法pandas 会自动把第一行数据当成列名结果就是丢了一行真实数据且列名变成了第一行的内容完全错误。应对无表头的情况需要增加一个参数来控制def split_csv_to_excel_v2(csv_file, rows_per_sheet, excel_fileNone, sheet_prefixsheet, encodingutf-8, has_headerTrue): if rows_per_sheet 0: raise ValueError(每表行数必须是正整数) rows_per_sheet min(rows_per_sheet, 1048575) if excel_file is None: base os.path.splitext(csv_file)[0] excel_file f{base}_拆分结果.xlsx # 设置 pandas 读取时的表头策略 header 0 if has_header else None # 写入 Excel 时是否保留表头行 write_header has_header total_rows 0 sheet_count 0 with pd.ExcelWriter(excel_file, engineopenpyxl) as writer: for chunk in pd.read_csv(csv_file, chunksizerows_per_sheet, encodingencoding, headerheader): sheet_count 1 sheet_name f{sheet_prefix}_{sheet_count} chunk.to_excel(writer, sheet_namesheet_name, indexFalse, headerwrite_header) total_rows len(chunk) return sheet_countheaderNone时 pandas 会用 0、1、2 这样的数字作为临时列名所以写 Excel 时必须配合headerFalse否则 Excel 里会多出一行无意义的数字表头。这个细节我在最初写的时候漏掉过生成的文件里每个工作表都多了一行“0、1、2、3”业务同事看到后完全不知道是什么。3.3 带进度反馈的调用示例大型文件拆分时间长如果脚本什么都不输出运行者会陷入“是不是卡死了”的焦虑。所以我在循环里加了进度打印每写完一个 sheet 就输出当前耗时的累计信息。实际调用方式和运行输出效果如下# 调用方式 sheets split_csv_to_excel( csv_fileD:/data/orders_2024.csv, rows_per_sheet300000, sheet_prefix订单, encodingutf-8 ) # 运行效果示例输出 # [3.2s] 已写入 订单_1, 当前表行数 300000, 累计行数 300000 # [10.8s] 已写入 订单_2, 当前表行数 300000, 累计行数 600000 # ... # 拆分完成: 共 7 个工作表, 总行数 2100000, 用时 189.5s如果你是在命令行里跑这段脚本可以简化为只输出百分比进度但作为一版通用工具保留每个 sheet 的关键信息已经足够。进度信息对于后面排查问题也有帮助可以清晰地看到每批耗时是否均匀。4. 实操演示与常用变体扩展4.1 一次真实的拆分过程我用一个 130 万行的销售明细 CSV 来演示真实效果。文件大约 700MB列字段包括订单编号、客户名称、商品编码、销售数量、销售金额等 12 列。拆分的需求是按每表 25 万行来拆最终会生成 6 个工作表。在脚本启动前我先做了一个检查确认 CSV 文件的编码。这个文件是从数据库直接导出的我用 Notepad 打开看了一下确认是 UTF-8 编码且第一行是表头。如果打开看到中文乱码大概率是 GBK 编码这时要把参数改成encodinggbk否则后面写入 Excel 的内容会全部乱码。脚本运行过程中我观察到前三个工作表的写入速度比较平稳每个大约耗时 8-10 秒但到第四个时耗时增加到 15 秒左右。原因是随着 Excel 文件越来越大openpyxl 写入时需要维护的数据结构也越来越多写入性能会自然下降。如果你发现文件巨大导致耗时不可接受可以考虑改用 xlsxwriter 引擎后面我会单独讲。拆分完成后我做了三步验证。第一步打开生成的 Excel 文件确认工作表数量是 6 个名称依次是 销售_1 到 销售_6。第二步逐个查看工作表底部行数确认每个 sheet 的数据行数都是“250000”加表头是 250001。第三步抽查最后一个 sheet 的最后几行确认数据与原始 CSV 的最后几行完全一致。这三步都通过才算真正交付。4.2 变体一把文件均分成 N 份而不是指定行数有些需求方不说“每表多少行”而是说“把这个分成 5 份”。这种情况下需要先把总行数统计出来再用总行数除以分数得到每份行数。关键问题是CSV 文件在读取前无法知道总行数需要先扫一遍文件统计行数。这里要区分两种统计方式。如果 CSV 是标准格式每条记录只有一行中间不会因为字段里包含换行符而跨行那么可以快速按行数统计速度非常快def count_lines_fast(csv_path): 快速统计行数适用于字段内不含换行符的 CSV with open(csv_path, rb) as f: return sum(1 for _ in f)但如果 CSV 的某个字段内部可能包含换行符这是合法且常见的按行统计就会偏大。此时更安全的方式是用 pandas 分块读取并累加每个块的长度但需要事先知道确切的行数才能计算均分值这变成了一个“鸡生蛋”的问题。我的实用解法是先用快速统计法拿到一个略偏大的行数以此计算每份行数然后在真正拆分时对最后一个 sheet 做裁剪丢弃超出预期范围的数据。说实话实际工作中我会先跟需求方确认 CSV 字段里有没有换行符绝大多数导出的业务数据都不会有快速统计的方式里几乎没有误判风险。4.3 变体二不装 pandas 的轻量方案如果你的环境限制非常大不允许安装 pandas 或者安装失败还有一个只依赖标准库 csv 模块和 openpyxl 的替代方案。这个方案不需要 pandas代码也完全可控但性能上比 pandas 略慢一点。import csv from openpyxl import Workbook def split_csv_to_excel_light(csv_file, rows_per_sheet, excel_fileNone): if excel_file is None: excel_file csv_file.replace(.csv, _拆分结果.xlsx) wb Workbook() wb.remove(wb.active) # 删除默认创建的空白工作表 with open(csv_file, r, encodingutf-8, newline) as f: reader csv.reader(f) header next(reader) # 读取表头 ws None current_count 0 sheet_idx 1 for row in reader: if ws is None or current_count rows_per_sheet: ws wb.create_sheet(fsheet_{sheet_idx}) ws.append(header) sheet_idx 1 current_count 0 ws.append(row) current_count 1 wb.save(excel_file)这个方案的核心思想是用csv.reader逐行读取同时用 openpyxl 的 Workbook 对象动态创建工作表。当当前工作表的行数达到阈值时创建下一个工作表并把表头写进去。优点是只依赖两个库python 环境自带 csv 模块openpyxl 可以通过 pip 安装缺点是逐行追加写入在数据量非常大时会比较慢实测 100 万行数据大约需要 2 分钟左右而 pandas 版本只需要 1 分钟左右。如果你的文件超过 500 万行我建议还是优先用 pandas。4.4 变体三拆分时按某列值分类到不同工作表最后分享一个“按行数拆分”的延伸需求。有时候需求方说“我想按城市拆每个城市一个工作表但每个城市的数据可能超过 30 万行超了的再按 30 万行继续拆”。这个需求本质上是“两层拆分”先按业务维度分组再按行数阈值二次拆分。实现思路是在分块读取的基础上对每个 chunk 使用groupby处理再把每个组追加到对应名称的工作表中。如果同组数据行数仍然超大需要维护一个“当前组当前写到第几个工作表”的计数器实际操作会复杂不少。普通的做法是读一块处理一块但要注意一个块内可能出现同一个城市的行数超过剩余容量需要做循环切分。这块代码稍微复杂如果你的需求确实涉及这种组合场景可以基于前面的函数进一步改造。我在这里只提醒一点当分组维度存在严重数据倾斜时个别组数据量极大单纯依赖 Excel 的行数上限仍然会造成写入失败所以二次拆分的阈值通常建议设置得保守一些。5. 常见问题排查与技术细节5.1 生成的文件打不开格式或扩展名无效这个是最常见的报错现象是脚本正常跑完输出文件也存在但双击打开时 Excel 提示“文件格式或文件扩展名无效”。我排查下来主要原因是三类。第一类是 ExcelWriter 没有正确关闭导致文件写入不完整。解决办法是使用with上下文管理或者结束时手动调用writer.close()。第二类是扩展名与实际格式不匹配比如 engine 用了 openpyxl 但文件后缀写成了 .xls这样也会打不开。第三类是杀毒软件或者云盘同步软件在文件写入过程中锁定了文件导致写入中断。给一个快速排查思路先检查脚本是否用with包住了 ExcelWriter如果没有把它改掉重新运行一次。如果仍然打不开可以把输出后缀改成.xlsx并确认 ExcelWriter 的引擎是 openpyxl。如果还不行试着把输出路径换到一个没有被杀毒软件监控的临时目录。5.2 内存还是爆了怎么办三个优化方向如果你按我上面的方式分块读取仍然发现内存不够用问题大概率集中在三个方向。第一个方向是chunksize设置得太大。比如你指定每表 80 万行那么 pandas 每次读取就会一次性加载 80 万行到内存这个数量在列多的情况下占用几个 GB 是完全可能的。我的经验是单块数据控制在 20-30 万行内存占用一般在 1-2GB 范围内Openpyxl 写入不会太慢。第二个方向是列类型推断带来的内存放大。pandas 在读取 CSV 时会自动推断每一列的数据类型字符串列会被单独存储为对象类型非常占用内存。如果你只关心数据拆分而不做计算可以在read_csv时指定dtypestr强制把所有列当作字符串读取这样能大幅降低内存开销。代价是数值列丢失了数据类型信息但拆分场景下完全无所谓。第三个方向是无关列太多。一个 100 列的 CSV业务只用到其中 10 列那在读取时直接传usecols[...]只读需要的列即可。这个优化对于超大文件效果非常显著我曾经把一个 5GB 的 200 列文件缩减到只需 15 列后再拆分耗时从半小时缩减到 4 分钟。5.3 写入速度慢要不要换引擎前面提到 openpyxl 写入速度会随文件增大而下降如果拆分后的 Excel 总量特别大可以考虑使用 xlsxwriter 引擎。xlsxwriter是为高性能写入设计的库底层直接生成 Excel 的 XML 结构不经过内存对象模型写入速度通常比 openpyxl 快 50% 到一倍左右。使用方式很简单把engineopenpyxl换成enginexlsxwriter前提是安装pip install xlsxwriter。但注意两个区别xlsxwriter 不支持在已有 Excel 文件上追加写入只能从零创建同时它对 Excel 部分复杂格式的支持没有 openpyxl 全面。纯拆分数据根本不需要任何格式所以这个场景下我推荐用 xlsxwriter。这里给出一个引擎选择的判断标准如果拆分后的 Excel 文件总大小超过 200MB或者工作表总数超过 20 个优先换 xlsxwriter如果只是常规的 100 万行数据拆成几个 sheetopenpyxl 完全够用不用折腾。5.4 其他问题速查表现象可能原因解决办法拆分结果中文乱码CSV 编码与 encoding 参数不匹配用记事本或工具确认编码改成gbk或utf-8-sig每个 sheet 多了一行数字 0/1/2无表头 CSV 未设置headerFalse检查has_header参数是否正确传递工作表名称带特殊字符报错sheet 名包含\/?*[]:等非法字符在生成 sheet_name 时过滤和替换非法字符拆分后的文件打开很慢单个 sheet 行数过多调低rows_per_sheet建议不超过 30 万最后一个 sheet 行数很少文件总行数无法整除每表行数属正常现象业务接收方一般接受最后一张表偏小每次运行结果不稳定内存占用波动导致系统卡顿加dtypestr降低chunksize这个表格里的问题都是我实际遇到的每一项都对应过一次真实的排查经历。尤其是“无表头 CSV 多了数字行”这个问题看起来是小问题但生成的文件交付出去对方会直接质疑数据完整性影响信任度。建议你做拆分任务时一定先把原始文件的前几行结构搞清楚再动手。最后聊聊我的经验拆分 CSV 到 Excel 这个需求技术难度不高但真正决定工具好不好用的往往是那些容易被忽略的细节表头重不保留、sheet 怎么命名、文件编码是什么、最后一张表行数偏少要不要特殊说明。我在实际交付时还有一个习惯就是每次拆分完都顺手生成一个“说明 sheet”放在 Excel 的第一个位置写清楚每个工作表的来源 CSV、行数范围、生成时间。别嫌啰嗦对方收到文件后几乎不需要再跑来问我“这个表是哪段数据”沟通成本节省得非常明显。另外提一句如果你工作中经常要处理这种“把大体量数据整理成方便他人使用的形式”的需求值得把这类脚本沉淀成一个小工具库。我今天分享的这几个函数已经不只是解决 CS 到 Excel 拆分往前面加一步网络下载、往后面加一步邮件发送就能变成一个完整的数据分发流程。工具不怕小能稳定解决一类重复问题就是最大的价值。