使用Python下载SharePoint文件遇Excel格式无效错误

需求：使用Python从SharePoint下载文件、修改后重新上传，已排除权限问题（其他公共SharePoint链接测试代码正常）。

尝试1 #

代码：

from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File

url = 'Sharepoint Link'
username = 'username'
password = 'password'

ctx_auth = AuthenticationContext(url)
ctx_auth.acquire_token_for_user(username, password)  
ctx = ClientContext(url, ctx_auth)
response = File.open_binary(ctx, "/Shared Documents/Data.xlsx")
with open("./Data.xlsx", "wb") as local_file:
    local_file.write(response.content)

预期：

• 文件下载至本地磁盘
  结果：
• 文件已下载，但打开时提示错误："Excel file cannot be open because the file format or file extension is not valid."
  尝试的解决办法：
• 开放所有用户访问权限
• 关闭所有打开的Excel文件

尝试2 #

代码：

import requests
import pandas as pd
import io

url = 'Sharepoint Link'
username = 'username'
password = 'password'

response = requests.get(url, auth=(username, password))
bytes_file_obj = io.BytesIO(response.content)
df = pd.read_excel(bytes_file_obj, sheet_name=sheet_name, engine='openpyxl')
print("Excel file downloaded")

预期：

• 文件下载至本地磁盘
  结果：
• 文件已下载，但打开时提示错误："Excel file cannot be open because the file format or file extension is not valid."
  尝试的解决办法：
• 开放所有用户访问权限
• 关闭所有打开的Excel文件
• 指定解析引擎

尝试3 #

代码：

import pandas as pd
import xml.etree.ElementTree as ET
import openpyxl
import io
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File

url = 'Sharepoint Link'
username = 'username'
password = 'password'
sheet_name = 'table'

ctx_auth = AuthenticationContext(url)
if ctx_auth.acquire_token_for_user(username, password):
    ctx = ClientContext(url, ctx_auth)
    web = ctx.web
    ctx.load(web)
    ctx.execute_query()
    print("Authentication successful")
    response = File.open_binary(ctx, url)
    bytes_file_obj = io.BytesIO()
    bytes_file_obj.write(response.content)
    bytes_file_obj.seek(0)  
    df = pd.read_excel(bytes_file_obj, sheet_name=sheet_name)
    print("Excel file loaded successfully")

预期：

• 文件下载至本地磁盘
  结果：
• 文件已下载，但打开时提示错误："Excel file cannot be open because the file format or file extension is not valid."
  尝试的解决办法：
• 开放所有用户访问权限
• 关闭所有打开的Excel文件
• 指定解析引擎

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