使用多线程时xlwings无法正确更新全局变量的问题排查

我通过多线程创建xlwings工作簿后，打印全局变量时触发COM错误，代码如下：

import xlwings as xw
import threading
workbook = ""

def function1():
    t1 = threading.Thread(target=function2)
    t1.start()
    t1.join()

def function2():
    global workbook
    workbook = xw.Book()

function1()
print(workbook)

运行后报错：

Traceback (most recent call last):
  File "c:\Users\rakesh.ramanjulu\Documents\Python Projects\Vendor ScoreCard Accuracy Final\test.py", line 51, in <module>
    print (workbook)
  File "C:\Users\rakesh.ramanjulu\AppData\Local\Programs\Python\Python312\Lib\site-packages\xlwings\main.py", line 1288, in __repr__
    return "<Book [{0}]>".format(self.name)
                                 ^^^^^^^^^
  File "C:\Users\rakesh.ramanjulu\AppData\Local\Programs\Python\Python312\Lib\site-packages\xlwings\main.py", line 1105, in name
    return self.impl.name
           ^^^^^^^^^^^^^^
  File "C:\Users\rakesh.ramanjulu\AppData\Local\Programs\Python\Python312\Lib\site-packages\xlwings\_xlwindows.py", line 837, in name
    return self.xl.Name
           ^^^^^^^^^^^^
  File "C:\Users\rakesh.ramanjulu\AppData\Local\Programs\Python\Python312\Lib\site-packages\xlwings\_xlwindows.py", line 199, in __getattr__
    v = getattr(self._inner, item)
        ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rakesh.ramanjulu\AppData\Local\Programs\Python\Python312\Lib\site-packages\xlwings\_win32patch.py", line 55, in __getattr__
    return getattr(d, attr)
           ^^^^^^^^^^^^^^^^
  File "C:\Users\rakesh.ramanjulu\AppData\Local\Programs\Python\Python312\Lib\site-packages\win32com\client\__init__.py", line 585, in __getattr__
    return self._ApplyTypes_(*args)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rakesh.ramanjulu\AppData\Local\Programs\Python\Python312\Lib\site-packages\win32com\client\__init__.py", line 574, in _ApplyTypes_
    self._oleobj_.InvokeTypes(dispid, 0, wFlags, retType, argTypes, *args),
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pywintypes.com_error: (-2147220995, 'Object is not connected to server', None, None)

之前用多线程更新全局变量从未出现问题，不清楚该如何解决这个报错。

原因 #

这不是全局变量的问题，核心在于Windows下xlwings依赖的Excel COM对象不支持跨线程访问。子线程创建工作簿对象后，线程结束时其关联的COM上下文会被销毁，导致主线程访问该对象时，它已经与Excel服务器断开连接，从而触发"Object is not connected to server"错误。

解决方案 #

方案1：将Excel操作移至主线程 #

如果业务逻辑允许，直接在主线程中创建和访问Excel对象：

import xlwings as xw
import threading
workbook = ""

def function1():
    global workbook
    workbook = xw.Book()

function1()
print(workbook)

方案2：在子线程内完成所有Excel操作 #

若必须使用多线程，所有涉及该工作簿对象的操作都要放在创建它的子线程中，避免跨线程传递对象：

import xlwings as xw
import threading

def function2():
    workbook = xw.Book()
    # 在此完成所有对workbook的读写、打印等操作
    print(workbook)

def function1():
    t1 = threading.Thread(target=function2)
    t1.start()
    t1.join()

function1()

方案3：显式管理COM上下文（进阶） #

通过pythoncom模块在子线程内初始化和释放COM上下文，同时显式控制Excel App的生命周期，可一定程度避免上下文提前销毁，但仍需注意不要跨线程访问对象：

import xlwings as xw
import threading
import pythoncom

def function2():
    # 初始化子线程的COM上下文
    pythoncom.CoInitialize()
    xl_app = xw.App(visible=True)
    workbook = xl_app.Book()
    # 在此完成所有Excel操作
    print(workbook)
    # 释放COM上下文
    pythoncom.CoUninitialize()

def function1():
    t1 = threading.Thread(target=function2)
    t1.start()
    t1.join()

function1()

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