使用xlwings访问SharePoint文件时触发UnboundLocalError

问题场景 #

此前6个月一直正常运行的xlwings代码，尝试访问SharePoint存储的runs_excel.xlsm文件时突然报错，操作前已通过任务管理器关闭所有Excel实例，报错信息为：UnboundLocalError: cannot access local variable 'root' where it is not associated with a value

相关代码 #

import xlwings as xw
wb = xw.Book('runs_excel.xlsm')
sht = wb.sheets['macro']
wb.sheets['macro'].select()

完整报错栈 #

UnboundLocalError                         Traceback (most recent call last) Cell In[5], line 2
      1 #os.chdir(filepath_main)
----> 2 wb = xw.Book('ecp_runs_excel_api.xlsm')
      3 sht = wb.sheets['macro']
      4 wb.sheets['macro'].select()

File C:\Program Files (x86)\temp\python\env_local\Lib\site-packages\xlwings\main.py:924, in Book.__init__(self, fullname, update_links, read_only, format, password, write_res_password, ignore_read_only_recommended, origin, delimiter, editable, notify, converter, add_to_mru, local, corrupt_load, impl, json, mode, engine, **kwargs)
    919                 candidates.append((app, wb))
    920         # Comparing by name first saves us from having to compare the
    921         # fullname for non-candidates, which can get around issues in
    922         # case the fullname is a problematic URL (GH 1946)
    923         elif wb.name.lower() == os.path.split(fullname)[1].lower() and (
---> 924             wb.fullname.lower() == fullname.lower()
    925             or wb.name.lower() == fullname.lower()
    926         ):
    927             candidates.append((app, wb))
    929 app = apps.active

File C:\Program Files (x86)\temp\python\env_local\Lib\site-packages\xlwings\main.py:1178, in Book.fullname(self)    1171 @property    1172 def fullname(self):   1173     """
    1174     Returns the name of the object, including its path on disk, as a string.
    1175     Read-only String.
    1176 
    1177     """
-> 1178     return self.impl.fullname

File C:\Program Files (x86)\temp\python\env_local\Lib\site-packages\xlwings\_xlwindows.py:903, in Book.fullname(self)
    901 if "://" in self.xl.FullName:
    902     config = read_config_sheet(xlwings.Book(impl=self))
---> 903     return fullname_url_to_local_path(
    904         url=self.xl.FullName,
    905         sheet_onedrive_consumer_config=config.get("ONEDRIVE_CONSUMER_WIN"),
    906         sheet_onedrive_commercial_config=config.get("ONEDRIVE_COMMERCIAL_WIN"),
    907         sheet_sharepoint_config=config.get("SHAREPOINT_WIN"),
    908     )
    909 else:
    910     return self.xl.FullName

File C:\Program Files (x86)\temp\python\env_local\Lib\site-packages\xlwings\utils.py:626, in fullname_url_to_local_path(url, sheet_onedrive_consumer_config, sheet_onedrive_commercial_config, sheet_sharepoint_config)
    622                 return str(local_path)
    623     # Horrible fallback
    624     return search_local_sharepoint_path(
    625         url,
---> 626         root if not mount_point else mount_point,
    627         sharepoint_config,
    628         sharepoint_config_name,
    629     )
    630 raise xlwings.XlwingsError(
    631     f"URL {url} not recognized as valid OneDrive/SharePoint link."
    632 )

UnboundLocalError: cannot access local variable 'root' where it is not associated with a value

问题原因 #

该错误属于xlwings内部逻辑bug：程序在解析SharePoint文件的URL并转换为本地路径时，变量root未被正确初始化就被调用，导致局部变量未绑定值的异常。

解决方法 #

1. 升级xlwings到最新版本 #

官方已在后续版本修复该bug，执行以下命令升级：

pip install --upgrade xlwings

2. 使用本地同步完整路径打开文件 #

如果无法升级，找到SharePoint文件在本地的同步路径（如OneDrive同步后的本地文件夹），用完整路径替代文件名打开：

import xlwings as xw
# 替换为你的本地同步完整路径
wb = xw.Book(r"C:\Users\你的用户名\OneDrive - 公司名称\目标文件夹\runs_excel.xlsm")
sht = wb.sheets['macro']
wb.sheets['macro'].select()

3. 显式创建Excel应用实例 #

手动创建新的Excel应用实例，避免残留实例干扰：

import xlwings as xw
# visible=True表示显示Excel窗口，可根据需求设置为False
app = xw.App(visible=True, add_book=False)
wb = app.books.open('runs_excel.xlsm')
sht = wb.sheets['macro']
wb.sheets['macro'].select()

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