这是一个非常实用的 Excel 拆分需求：将一个大型 Excel 文件（可能含多列、多行，甚至多个 sheet）按**每个子文件最多 100,000 行**进行拆分，并为每个拆分后的文件生成独立的 `.xlsx` 文件（保留原始表头、格式兼容性、sheet 结构）。由于 `openpyxl` 不支持读取大数据（且不保留公式/样式时较重），而 `pandas` + `openpyxl`（写）或 `xlsxwriter`（写）是更高效、可控的组合；但注意：**`xlsxwriter` 不支持读取 Excel，仅写入；`openpyxl` 可读可写但内存占用高；`pandas` 读取灵活，适合结构化数据处理**。 ✅ **推荐方案**（兼顾正确性、可读性、性能与通用性）： - 使用 `pandas` 读取 Excel（支持 `.xls`, `.xlsx`, `.xlsm`，自动推断引擎；对大文件可用 `chunksize` 流式读取，但本例需整表拆分，故先读入内存 —— 若原始文件超 50 万行 × 100 列，建议改用 `dask` 或 `polars`，此处按常规场景处理）； - 对每个 sheet 分别拆分（保持多 sheet 支持）； - 每个输出文件：**一个 sheet（同名），首行为原始表头，后续每块 ≤100,000 行数据**； - 输出文件命名规则：`原文件名_页码.xlsx`（如 `data.xlsx` → `data_1.xlsx`, `data_2.xlsx`, ...）； - ✅ 自动处理空行、混合类型、日期、数字精度（`pandas` 默认保持）； - ❌ 不保留原始单元格样式/条件格式/图表/宏（Excel 拆分工具通常不需此功能；若强需，需 `openpyxl` 逐单元格操作，性能极差，不推荐）。 --- ### ✅ 完整可运行 Python 脚本（使用 `pandas` + `openpyxl` 写入，确保 `.xlsx` 兼容性） > ⚠️ 注意：需提前安装依赖 > ```bash > pip install pandas openpyxl > ``` ```python import os import pandas as pd from pathlib import Path def split_excel_by_rows(input_path: str, max_rows_per_file: int = 100000): """ 将 Excel 文件按行数拆分为多个 Excel 文件，每个文件每 sheet 最多 max_rows_per_file 行（含表头）。 Args: input_path (str): 输入 Excel 文件路径 max_rows_per_file (int): 每个输出文件中每个 sheet 的最大行数（含 header） """ input_path = Path(input_path) if not input_path.exists(): raise FileNotFoundError(f"输入文件不存在: {input_path}") # 读取所有 sheet（不假设 sheet 名，保留原始结构） excel_file = pd.ExcelFile(input_path) all_sheets = {} for sheet_name in excel_file.sheet_names: # 读取该 sheet：header=0 表示第一行为列名；dtype=object 避免类型强制转换（如 001→1） df = excel_file.parse(sheet_name, header=0, dtype=object) all_sheets[sheet_name] = df # 构建输出目录（同级目录下新建 'split_output'） output_dir = input_path.parent / "split_output" output_dir.mkdir(exist_ok=True) # 全局文件计数器（用于命名：xxx_1.xlsx, xxx_2.xlsx...） file_index = 1 # 遍历每个 sheet，按块拆分并写入新文件 while True: # 准备当前要写入的文件中的所有 sheet 数据（dict: sheet_name -> DataFrame） current_file_sheets = {} # 标记是否还有数据可写 has_data = False for sheet_name, df in all_sheets.items(): if df.empty: continue # 计算当前 sheet 剩余行数（不含 header） remaining_rows = len(df) if remaining_rows == 0: continue # 当前块：取 min(max_rows_per_file - 1, remaining_rows) 行数据（因为 header 占 1 行） # 即：实际数据行数 ≤ max_rows_per_file - 1，总行数 ≤ max_rows_per_file data_rows_to_take = min(max_rows_per_file - 1, remaining_rows) # 截取数据块（从开头取 data_rows_to_take 行） chunk_df = df.iloc[:data_rows_to_take].copy() # 更新原 df：移除已取走的行 all_sheets[sheet_name] = df.iloc[data_rows_to_take:].reset_index(drop=True) current_file_sheets[sheet_name] = chunk_df has_data = True # 如果没有任何数据可写，说明全部拆分完成 if not has_data: break # 生成输出文件路径 stem = input_path.stem output_file = output_dir / f"{stem}_{file_index}.xlsx" # 使用 openpyxl 引擎写入（支持 .xlsx，保留基础格式如数字/日期类型） with pd.ExcelWriter(output_file, engine='openpyxl') as writer: for sheet_name, chunk_df in current_file_sheets.items(): # 写入时 index=False，避免额外索引列；header=True（自带表头） chunk_df.to_excel(writer, sheet_name=sheet_name, index=False, header=True) print(f"✅ 已生成: {output_file} （共 {len(current_file_sheets)} 个 sheet）") file_index += 1 print(f"\n🎉 拆分完成！共生成 {file_index - 1} 个文件，保存于: {output_dir.resolve()}") # —————— 使用示例（取消注释并修改路径即可运行）—————— if __name__ == "__main__": # ✅ 替换为你自己的 Excel 路径 INPUT_FILE = "large_data.xlsx" # ← 修改此处！ try: split_excel_by_rows(INPUT_FILE, max_rows_per_file=100000) except Exception as e: print(f"❌ 执行出错: {e}") ``` --- ### 🔍 代码关键点解释： | 部分 | 说明 | |------|------| | `pd.ExcelFile(...)` | 安全打开 Excel，获取所有 sheet 名，不立即加载数据，节省内存。 | | `excel_file.parse(..., dtype=object)` | 强制所有列为 `object` 类型，防止 `pandas` 自动将 `"001"` 转为 `1`、将 `"2023-01-01"` 转为 `datetime64` 导致格式丢失（后续写入仍能保持原始字符串显示）。如需类型感知，可删掉 `dtype=object` 并配合 `converters` 参数。 | | `max_rows_per_file - 1` | 因为每个输出 sheet 的第 1 行是表头（`header=True`），所以最多再放 `max_rows_per_file - 1` 行数据。确保**每个文件每 sheet 总行数 ≤ 100,000**。 | | `df.iloc[:n]` & `df.iloc[n:]` | 高效切片，无拷贝开销（视图），适合大 DataFrame。 | | `pd.ExcelWriter(..., engine='openpyxl')` | `openpyxl` 是目前最稳定支持 `.xlsx` 写入的引擎，能正确处理日期、数字、布尔值等类型（比 `xlsxwriter` 更兼容读取端）。 | | 输出目录 `split_output/` | 自动创建，避免污染源目录；路径清晰可追溯。 | --- ### 🧪 测试建议： 1. 创建一个测试文件 `test.xlsx`，含 1 个 sheet，205000 行数据（含表头），列包括：`ID`（文本）、`Name`（字符串）、`Score`（数字）、`Date`（日期字符串如 `"2024-03-15"`）； 2. 运行脚本 → 应生成 `test_1.xlsx`（100000 行：1 行 header + 99999 data）、`test_2.xlsx`（100000 行）、`test_3.xlsx`（5001 行）； 3. 用 Excel 打开验证：行数、表头、数据完整性、日期/数字未被篡改。 --- ### ⚠️ 局限性与增强方向（进阶可选）： - **超大文件（>1GB）**：改用 `dask.dataframe.read_excel()` 或 `polars.read_excel()` + 分块写入； - **保留样式/合并单元格**：必须用 `openpyxl` 逐 cell 处理，性能下降 10x+，需权衡； - **多线程加速写入**：`openpyxl` 非线程安全，不可并行写同一文件，但可并行写不同文件（需重构为 `concurrent.futures`）； - **命令行接口**：加 `argparse`，支持 `python split.py -i data.xlsx -r 100000`； - **进度条**：用 `tqdm` 包装 `all_sheets.items()` 循环。 ---