多线程PyQt5 Excel应用中com_error(-2147221008)问题的解决方法

错误原因 #

你在主线程初始化了Excel COM对象，但在工作线程中直接使用这个对象。COM模型要求每个使用COM的线程必须单独初始化COM环境，且Excel属于单线程公寓(STA)模型的COM对象，跨线程直接访问会触发CoInitialize has not been called错误。

修复步骤 #

1. 将Excel对象的创建移到工作线程内部：不在主线程提前初始化excel，而是在Worker的任务方法里创建，确保COM环境在当前线程初始化。
2. 设置工作线程为STA模式：QThread默认是MTA模式，需要显式设置为STA，适配Excel的COM线程模型。
3. 线程内完成Excel资源清理：所有Excel相关的关闭、退出操作都在Worker线程内完成，避免跨线程操作COM对象。

修改后的完整代码 #

import sys
import time

from PyQt5.uic import loadUi
from PyQt5 import QtWidgets
from PyQt5.QtWidgets import QDialog, QApplication, QMainWindow, QWidget, QProgressBar, QVBoxLayout
from PyQt5.QtCore import QThread, pyqtSignal, QObject, pyqtSlot
import win32com.client as win32


month = "April"    
destination = rf"C:\Users\RC\Desktop\Important Emails\WIP Special Workings\FY2024\{month}"


class Worker(QObject):
    finished = pyqtSignal()
    intReady = pyqtSignal(int)

    @pyqtSlot()
    def proc_counter(self):
        count = 0
        # 在工作线程内部创建Excel对象，自动初始化COM环境
        excel = win32.gencache.EnsureDispatch('Excel.Application')
        excel.Visible = True
        excel.DisplayAlerts = False

        data_file = rf"C:\Users\RC\Desktop\Important Emails\WIP Special Workings\FY2024\{month}\gl transaction download-cc1.xlsx"
        template_file = rf"C:\Users\RC\Desktop\Important Emails\WIP Special Workings\FY2024\{month}\GL Dump - {month}.xlsx"
        
        wb_template = excel.Workbooks.Open(template_file)
        wb_data = excel.Workbooks.Open(data_file)
        count += 20
        self.intReady.emit(count)
        
        wb_template.Sheets("GL Data Jan").Range("A2:W5000").Clear()
        wb_data.Sheets("Sheet1").Range("A2:W5000").Copy()
        wb_template.Sheets("GL Data Jan").Paste(wb_template.Sheets("GL Data Jan").Range('A2'))
        count += 20
        self.intReady.emit(count)
        
        wb_template.RefreshAll()
        time.sleep(3)
        count += 40
        self.intReady.emit(count)
        
        wb_template.Save()
        count += 19
        self.intReady.emit(count)
        
        wb_template.Close(True)
        wb_data.Close(True)
        excel.Application.Quit()
        # 释放COM对象引用，避免内存泄漏
        del wb_template, wb_data, excel
        count += 1
        self.intReady.emit(count)
        
        self.finished.emit()
        return None
            
            
class PopUpProgressB(QWidget):
    def __init__(self):
        super().__init__()
        self.pbar = QProgressBar(self)
        self.pbar.setGeometry(30, 40, 500, 75)
        self.layout = QVBoxLayout()
        self.layout.addWidget(self.pbar)
        self.setLayout(self.layout)
        self.setGeometry(300, 300, 550, 100)
        self.setWindowTitle('Progress Bar')

        self.obj = Worker()
        self.thread = QThread()
        # 设置线程为STA模式，适配Excel的COM模型
        self.thread.setProperty("ThreadType", "STA")

        self.obj.intReady.connect(self.on_count_changed)
        
        self.obj.moveToThread(self.thread)
     
        self.obj.finished.connect(self.thread.quit)
        self.obj.finished.connect(self.hide)
        self.thread.started.connect(self.obj.proc_counter)
        # 线程结束后销毁Worker对象，避免内存泄漏
        self.thread.finished.connect(self.obj.deleteLater)

    def start_progress(self):
        self.show()
        # QThread只能启动一次，若线程已结束则重新创建
        if self.thread.isFinished():
            self.thread = QThread()
            self.thread.setProperty("ThreadType", "STA")
            self.obj.moveToThread(self.thread)
            self.thread.started.connect(self.obj.proc_counter)
            self.obj.finished.connect(self.thread.quit)
            self.obj.finished.connect(self.hide)
            self.thread.finished.connect(self.obj.deleteLater)
        self.thread.start()

    def on_count_changed(self, value):
        self.pbar.setValue(value)

class Screen2(QDialog):
    def __init__(self):
        super(Screen2,self).__init__()
        loadUi("GL_Dump_Curr.ui",self)
        self.pushButton_2.clicked.connect(self.gotoScreen1)
        self.popup = PopUpProgressB()
        self.pushButton.clicked.connect(self.popup.start_progress)

    def gotoScreen1(self):
        # 补充窗口切换逻辑，示例为关闭当前窗口
        self.close()

if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = Screen2()
    window.show()
    sys.exit(app.exec_())

额外说明 #

• QThread只能启动一次，所以在start_progress里增加了线程状态判断，避免重复启动报错。
• 新增了COM对象的del操作，手动释放引用，降低内存泄漏风险。

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