Python从SharePoint Online拉取Excel行报错求助

我是Python新手，尝试从SharePoint Online中的Excel文件拉取一行数据，先后遇到两个错误：

第一个错误：TypeError #

最初代码执行时出现以下错误：

line 25, in <module>
excel_file = io.BytesIO(file.download().content)
TypeError: File.download() missing 1 required positional argument: 'file_object'

我的初始代码：

import io
import openpyxl
import docx
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext

# SharePoint Online authentication information
site_url = "https://yourtenant.sharepoint.com/sites/your-site"
username = "your-username@yourtenant.onmicrosoft.com"
password = "your-password"

# Prompt user for row number to export
row_number = int(input("Enter row number to export: "))

# SharePoint Online file information
file_url = "https://yourtenant.sharepoint.com/sites/your-site/Shared%20Documents/Workbook.xlsx"

# Connect to SharePoint Online and get Excel file
ctx_auth = AuthenticationContext(site_url)
ctx_auth.acquire_token_for_user(username, password)
ctx = ClientContext(site_url, ctx_auth)
file = ctx.web.get_file_by_server_relative_url(file_url)

# Load Excel file and get row values
excel_file = io.BytesIO(file.download().content)
wb = openpyxl.load_workbook(excel_file)
ws = wb['Sheet1']
row_values_1 = [cell.value for cell in ws[1]]
row_values_2 = [cell.value for cell in ws[row_number]]

# Load Word document and create a new table with the row values
doc = docx.Document('Document.docx')
table = doc.add_table(rows=2, cols=len(row_values_1), style='Table Grid')
hdr_cells = table.rows[0].cells
for i, val in enumerate(row_values_1):
    hdr_cells[i].text = str(val)

for i, val in enumerate(row_values_2):
    row_cells = table.rows[1].cells
    row_cells[i].text = str(val)

# Save the modified Word document
doc.save('Document.docx')

修改后遇到的第二个错误：BadZipFile #

调整下载代码后，出现新错误：

Traceback (most recent call last):
File "C:/Users/Documents/ExtoWordtablespv2.py", line 28, in 
wb = openpyxl.load_workbook(excel_file)
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\reader\excel.py", line 344, in load_workbook
reader = ExcelReader(filename, read_only, keep_vba,
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\reader\excel.py", line 123, in __init__
self.archive = _validate_archive(fn)
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\reader\excel.py", line 95, in _validate_archive
archive = ZipFile(filename, 'r')
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\zipfile.py", line 1267, in __init__
self._RealGetContents()
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\zipfile.py", line 1334, in _RealGetContents
raise BadZipFile("File is not a zip file")
zipfile.BadZipFile: File is not a zip file

修改后的下载部分代码：

# Load Excel file and get row values
excel_file = io.BytesIO()
file.download(excel_file)
excel_file.seek(0)
wb = openpyxl.load_workbook(excel_file)
ws = wb['Sheet1']
row_values_1 = [cell.value for cell in ws[1]]
row_values_2 = [cell.value for cell in ws[row_number]]

解决方案 #

出现BadZipFile的核心原因有两个：

• 未执行查询获取文件对象：通过get_file_by_server_relative_url获取file对象后，必须调用ctx.execute_query()来完成SharePoint请求，否则file对象没有实际的文件数据。
• 文件路径错误：get_file_by_server_relative_url需要的是服务器相对路径，不是完整的URL。完整URL中site_url之后的部分才是相对路径。

修正后的完整代码 #

import io
import openpyxl
import docx
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext

# SharePoint配置信息
site_url = "https://yourtenant.sharepoint.com/sites/your-site"
username = "your-username@yourtenant.onmicrosoft.com"
password = "your-password"
# 服务器相对路径：从site_url之后的部分开始
server_relative_file_url = "/sites/your-site/Shared%20Documents/Workbook.xlsx"

# 获取用户输入的行号
row_number = int(input("Enter row number to export: "))

# 认证并连接SharePoint
ctx_auth = AuthenticationContext(site_url)
ctx_auth.acquire_token_for_user(username, password)
ctx = ClientContext(site_url, ctx_auth)

# 获取文件对象并执行查询
file = ctx.web.get_file_by_server_relative_url(server_relative_file_url)
ctx.load(file)
ctx.execute_query()

# 下载并加载Excel文件
excel_file = io.BytesIO()
file.download(excel_file)
excel_file.seek(0)
wb = openpyxl.load_workbook(excel_file)
ws = wb['Sheet1']
row_values_1 = [cell.value for cell in ws[1]]
row_values_2 = [cell.value for cell in ws[row_number]]

# 写入Word文档
doc = docx.Document('Document.docx')
table = doc.add_table(rows=2, cols=len(row_values_1), style='Table Grid')
# 写入表头
hdr_cells = table.rows[0].cells
for i, val in enumerate(row_values_1):
    hdr_cells[i].text = str(val) if val is not None else ""
# 写入目标行数据
row_cells = table.rows[1].cells
for i, val in enumerate(row_values_2):
    row_cells[i].text = str(val) if val is not None else ""

# 保存Word文档
doc.save('Document.docx')

额外注意事项 #

• 确保账号有该SharePoint文件的访问权限，避免权限不足导致下载的内容是权限错误页面（同样会触发BadZipFile）。
• 如果使用的是MFA账号，不能直接用用户名密码认证，需要改用其他认证方式（比如应用权限证书）。

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