Openpyxl并行处理问题：多子工作簿读写主工作簿数据丢失

问题原因 #

多进程（包括Ray）的内存空间相互隔离，每个子进程会复制主进程中的master_workbook和ws1对象作为独立副本。子进程对副本的修改不会同步回主进程的原始对象，最终主进程保存的仍是未修改的原始工作簿，导致累加数据丢失。

解决方案 #

核心思路是让子进程仅负责读取子工作簿的数据，返回需要累加的数值集合，由主进程统一完成累加和写入操作，避免多进程直接操作同一个工作簿对象。

1. Python内置multiprocessing实现 #

from openpyxl import load_workbook, Workbook
from openpyxl.styles import NamedStyle
import multiprocessing

def read_child_data(file_path):
    # 子进程仅读取数据，返回单元格与对应数值的字典
    child_wb = load_workbook(filename=file_path, data_only=True, read_only=True)
    sheet = child_wb["Sheet1"]
    data = {}
    for i in range(0, 10):
        target_row = 12
        target_col = 2 + i
        # 读取子工作簿数据，空值默认设为0
        val = sheet.cell(row=10, column=2+i).value or 0
        data[(target_row, target_col)] = val
    child_wb.close()
    return data

if __name__ == "__main__":
    # 初始化主工作簿与样式
    master_workbook = Workbook()
    ws1 = master_workbook.active
    ws1.title = "Sample"

    # 定义并添加样式（补充你原有样式的具体配置）
    number_default_style = NamedStyle(name="number_default_style")
    # number_default_style.font = Font(...)
    # number_default_style.alignment = Alignment(...)
    master_workbook.add_named_style(number_default_style)

    # 子工作簿路径列表
    child_files = [
        "C:\\Somesamplefile1.xlsx",
        "C:\\Somesamplefile2.xlsx",
        "C:\\Somesamplefile3.xlsx"
    ]

    # 多进程读取所有子工作簿数据
    with multiprocessing.Pool(processes=3) as pool:
        results = pool.map(read_child_data, child_files)

    # 主进程统一累加并写入数据
    for cell_data in results:
        for (row, col), val in cell_data.items():
            current_val = ws1.cell(row=row, column=col).value or 0
            ws1.cell(row=row, column=col).value = current_val + val
            ws1.cell(row=row, column=col).style = "number_default_style"

    # 后续格式处理与保存
    master_workbook.save(filename="C:\\Someoutputfile.xlsx")

2. Ray实现 #

from openpyxl import load_workbook, Workbook
from openpyxl.styles import NamedStyle
import ray

ray.init()

@ray.remote
def read_child_data_ray(file_path):
    # Ray远程函数仅读取数据，返回结构化结果
    child_wb = load_workbook(filename=file_path, data_only=True, read_only=True)
    sheet = child_wb["Sheet1"]
    data = {}
    for i in range(0, 10):
        target_row = 12
        target_col = 2 + i
        val = sheet.cell(row=10, column=2+i).value or 0
        data[(target_row, target_col)] = val
    child_wb.close()
    return data

if __name__ == "__main__":
    # 初始化主工作簿与样式
    master_workbook = Workbook()
    ws1 = master_workbook.active
    ws1.title = "Sample"

    number_default_style = NamedStyle(name="number_default_style")
    # 补充样式具体配置
    master_workbook.add_named_style(number_default_style)

    child_files = [
        "C:\\Somesamplefile1.xlsx",
        "C:\\Somesamplefile2.xlsx",
        "C:\\Somesamplefile3.xlsx"
    ]

    # 提交Ray任务并获取结果
    futures = [read_child_data_ray.remote(file) for file in child_files]
    results = ray.get(futures)

    # 主进程统一累加写入
    for cell_data in results:
        for (row, col), val in cell_data.items():
            current_val = ws1.cell(row=row, column=col).value or 0
            ws1.cell(row=row, column=col).value = current_val + val
            ws1.cell(row=row, column=col).style = "number_default_style"

    master_workbook.save(filename="C:\\Someoutputfile.xlsx")
    ray.shutdown()

关键注意事项 #

• 子进程/远程函数仅执行读操作，返回字典、列表等可序列化数据，避免传递openpyxl的工作簿/工作表对象。
• 所有对主工作簿的写操作均在主进程中完成，确保操作的是同一个工作簿实例。
• 若子工作簿数据量极大，可分块读取返回，避免内存占用过高。

内容的提问来源于stack exchange，提问作者sword134