Python读取SharePoint长路径Excel文件失败求助

问题场景 #

公司调整SharePoint文件夹结构后，原有Python读取Excel的脚本失效：

• 旧路径（可正常运行）：

FILE_PATH = "https://company.sharepoint.com/sites/Folder1/Shared%20Documents/Project%20Files/0.%20Databases%202022/Previous%20DB%20versions%20(Not-used)/Some_Excel_File.xlsx"

• 新路径（执行报错）：

FILE_PATH = "https://company.sharepoint.com/sites/MainMission/Shared%20Documents/5.%20Programme%20(projects-Files-TU)%20Department/DLLY052%20-%20EX%20Projects%20II/9.%20042%20Operational%20Documents/0.%20Databases%202022/Previous%20DB%20versions%20(Not-used)/Some_Excel_File.xlsx"

• 报错信息：

Error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Open method of Workbooks class failed', 'xlmain11.chm', 0, -2146827284), None)

• 已尝试无效的路径处理：

FILE_PATH = r'{0}'.format(FILE_PATH)
FILE_PATH = os.path.expandvars(FILE_PATH)
FILE_PATH = FILE_PATH.replace("%", "%%")

将文件复制到短路径可正常读取，说明问题源于长路径或路径解析异常。

问题原因 #

该错误由Excel COM组件的局限性导致：

1. Excel COM对过长的HTTPS格式SharePoint URL支持有限，深层嵌套的路径会触发解析失败；
2. URL中的编码字符（如%20）或特殊符号（如括号）可能被COM组件错误解析，无法定位文件。

解决方案 #

方案1：映射SharePoint库为本地网络驱动器 #

通过将SharePoint文档库映射为网络驱动器，用短路径访问文件：

1. 打开Windows「此电脑」，点击「映射网络驱动器」；
2. 输入SharePoint文档库的UNC路径，格式为\\company.sharepoint.com@SSL\DavWWWRoot\sites\MainMission\Shared%20Documents；
3. 映射完成后，脚本中使用本地驱动器路径，同时解码URL编码字符：

import urllib.parse

# 替换Z:为你映射的驱动器号
file_path = "Z:/5.%20Programme%20(projects-Files-TU)%20Department/DLLY052%20-%20EX%20Projects%20II/9.%20042%20Operational%20Documents/0.%20Databases%202022/Previous%20DB%20versions%20(Not-used)/Some_Excel_File.xlsx"
decoded_path = urllib.parse.unquote(file_path)

# 后续用decoded_path调用Excel COM或其他库读取

方案2：改用不依赖Excel COM的库 #

使用pandas+openpyxl直接读取，绕开Excel COM的限制：

import pandas as pd
import urllib.parse

# 解码URL中的编码字符
decoded_url = urllib.parse.unquote("https://company.sharepoint.com/sites/MainMission/Shared%20Documents/5.%20Programme%20(projects-Files-TU)%20Department/DLLY052%20-%20EX%20Projects%20II/9.%20042%20Operational%20Documents/0.%20Databases%202022/Previous%20DB%20versions%20(Not-used)/Some_Excel_File.xlsx")

# 读取Excel文件，openpyxl支持xlsx格式
df = pd.read_excel(decoded_url, engine='openpyxl')

注意：若SharePoint需要身份验证，需先获取访问令牌，或用requests下载文件到临时目录后再读取。

方案3：修复路径编码后调用Excel COM #

先解码URL中的编码字符，再尝试用COM组件打开：

import win32com.client
import urllib.parse

decoded_path = urllib.parse.unquote("https://company.sharepoint.com/sites/MainMission/Shared%20Documents/5.%20Programme%20(projects-Files-TU)%20Department/DLLY052%20-%20EX%20Projects%20II/9.%20042%20Operational%20Documents/0.%20Databases%202022/Previous%20DB%20versions%20(Not-used)/Some_Excel_File.xlsx")

excel = win32com.client.Dispatch("Excel.Application")
# 可选：设置为后台运行，不显示Excel窗口
excel.Visible = False
workbook = excel.Workbooks.Open(decoded_path)

# 执行你的读取逻辑
# ...

# 操作完成后关闭资源
workbook.Close(SaveChanges=False)
excel.Quit()

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