如何高效实现按供应商拆分Excel数据并批量写入独立文件-Python教程-PHP中文网

如何高效实现按供应商拆分Excel数据并批量写入独立文件

本文详解如何优化python中基于“vendor name”列拆分excel数据并写入多个文件的性能瓶颈，重点解决嵌套循环与高频文件i/o导致的效率低下问题，并提供可直接运行的高效替代方案。

本文详解如何优化python中基于“vendor name”列拆分excel数据并写入多个文件的性能瓶颈，重点解决嵌套循环与高频文件i/o导致的效率低下问题，并提供可直接运行的高效替代方案。

在实际业务场景中（如采购订单分发、供应商对账等），常需将一份汇总Excel（File2）按“Vendor Name”字段拆分为多个子集，并将每个子集完整写入一个已存在的模板工作簿（File1，即 blank_order_book_path）的指定工作表（如 "Data Dump"）中，最终保存为独立的 .xlsx 文件。然而，原始代码存在严重性能缺陷：它在最内层循环中反复调用 ws.cell(...) 赋值 + 每次都执行 wb.save()，导致时间复杂度爆炸式增长——对含 N 行、M 列的数据，仅写入操作就达 O(N×M) 次单元格访问，且每次 save() 都触发完整文件序列化，I/O 开销极高。

✅ 核心优化原则

避免逐单元格写入 → 改用 openpyxl 的 ws.append() 批量追加行（底层更高效）；
杜绝循环内保存 → 所有数据写入完成后，仅执行一次 save()；
减少工作簿重复加载 → 每个供应商只加载 1 次模板，而非在循环内反复 load_workbook()；
预处理供应商名称 → 提前过滤非字符串值，避免运行时类型检查开销。

✅ 优化后完整代码（专业、健壮、可直接运行）

import os
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import pandas as pd

def split_and_save_by_vendor(
    df: pd.DataFrame,
    blank_order_book_path: str,
    output_folder: str = "Downloads/VendorSplittingApp",
    vendor_col: str = "Vendor Name",
    target_sheet: str = "Data Dump"
):
    """
    高效拆分DataFrame并写入多个Excel文件（基于模板）

    Args:
        df: 待拆分的源DataFrame
        blank_order_book_path: 模板Excel文件路径（含"Data Dump"工作表）
        output_folder: 输出目录（自动创建）
        vendor_col: 用于分组的列名
        target_sheet: 模板中目标工作表名
    """
    # 确保输出目录存在
    os.makedirs(output_folder, exist_ok=True)

    # 过滤并获取有效供应商名（排除NaN、None、非字符串）
    valid_suppliers = df[vendor_col].dropna().astype(str).unique()

    for supplier in valid_suppliers:
        # 安全命名：移除非法字符（Windows文件系统限制）
        safe_name = "".join(c for c in supplier if c.isalnum() or c in " _-").strip()
        if not safe_name:
            continue
        output_path = os.path.join(output_folder, f"{safe_name}.xlsx")

        # ✅ 关键优化1：每个供应商只加载1次模板
        wb = load_workbook(blank_order_book_path)
        ws = wb[target_sheet]

        # ✅ 关键优化2：清空原"Data Dump"内容（保留表头结构？按需调整）
        # 若需保留表头，注释掉下一行；若需完全清空（含表头），保留
        ws.delete_rows(1, ws.max_row)  # 彻底清空

        # 构建待写入数据：[表头] + [数据行]
        supplier_df = df[df[vendor_col] == supplier].copy()
        data_to_write = [supplier_df.columns.tolist()] + supplier_df.values.tolist()

        # ✅ 关键优化3：使用append()批量写入（比cell()快5–10倍）
        for row in data_to_write:
            ws.append([str(v) if pd.isna(v) else v for v in row])

        # ✅ 关键优化4：所有写入完成后，仅保存1次
        wb.save(output_path)
        print(f"✓ Saved: {output_path}")

# 使用示例
# split_and_save_by_vendor(
#     df=df,  # 来自File2的DataFrame
#     blank_order_book_path="path/to/blank_order_book.xlsx",
#     output_folder="Downloads/VendorSplittingApp"
# )

⚠️ 重要注意事项

内存安全：该方案仍需将每个 supplier_df 加载进内存。若单个供应商数据超千万行，建议改用 pandas.read_csv + 分块处理（.chunksize），但需配合 xlsxwriter 流式写入（openpyxl 不支持流式）；
表头一致性：确保 df 列顺序与模板 "Data Dump" 表头严格一致，否则 append() 可能错位。可在写入前校验：assert list(supplier_df.columns) == list(ws.iter_rows(min_row=1, max_row=1, values_only=True))[0]；
日期/数字格式丢失：openpyxl.append() 会丢失原始格式（如日期显示为数值）。如需保留格式，请改用 ws._cells[(row, col)] = value + 手动设置 number_format，但会牺牲部分性能；
并发安全：当前为单线程。若需加速，可用 concurrent.futures.ProcessPoolExecutor 并行处理不同供应商（注意：openpyxl 工作簿对象不可跨进程传递，必须在子进程中重新加载）。