Categories python

python怎样用多进程用xlwings创建很多个excel文件

Leave a comment by

问题描述

如下是一段代码，想要创建多个空的excel文件，想要用多线程池创建

import xlwings as xw
from concurrent.futures import ThreadPoolExecutor, as_completed
import time

def add(date):
    app = xw.App(visible=True, add_book=False)
    t = app.books.add()
    t.save(f"./{date}的表格.xlsx")
    t.close()
    app.quit()

start = time.time()
with ThreadPoolExecutor() as pool:
    results = [pool.submit(add, s) for s in range(51, 101)]
    for result in results:
        result.result()
end = time.time()
print(end - start)

报错信息如下：

pywintypes.com_error: (-2147221008, '尚未调用 CoInitialize。', None, None)

这个错误，是xlwings自身的报错；

考虑到xlwings是和excel软件进程交互，应该是不允许单个进程中进行多个excel进程的交互。
因此改成多进程池

从多线程池改成多进程池

代码如下，改成了 ProcessPoolExecutor

import xlwings as xw
from concurrent.futures import ProcessPoolExecutor, as_completed
import time

def add(date):
    app = xw.App(visible=True, add_book=False)
    t = app.books.add()
    t.save(f"./{date}的表格.xlsx")
    t.close()
    app.quit()

start = time.time()
with ProcessPoolExecutor() as pool:
    results = [pool.submit(add, s) for s in range(51, 101)]
    for result in results:
        result.result()
end = time.time()
print(end - start)

但是这个时候，报错信息如下：

concurrent.futures.process.BrokenProcessPool:
A process in the process pool was terminated abruptly
while the future was running or pending.

这个错误，是主进程和子进程的交互导致的。
对于多进程池，需要把代码放到if name == "main":中进行启动，才不会有这个错误

将多进程的代码放到main入口中

改进的代码：

import xlwings as xw
from concurrent.futures import ProcessPoolExecutor, as_completed
import time

def add(date):
    app = xw.App(visible=True, add_book=False)
    t = app.books.add()
    t.save(f"./{date}的表格.xlsx")
    t.close()
    app.quit()

if __name__ == "__main__":
    start = time.time()
    pool = ProcessPoolExecutor()
    results = [pool.submit(add, s) for s in range(51, 101)]
    for result in results:
        result.result()
    end = time.time()
    print(end - start)
    pool.shutdown()

运行成功
注意：这个代码会生成好多个excel文件，自己别玩坏了！

Leave a Comment