如何用Python将多DataFrame导出为工作簿并上传至SharePoint

前置依赖安装 #

先安装所需的Python库：

pip install office365-rest-python-client pandas openpyxl

步骤1：从SharePoint读取文件到DataFrame #

以下示例展示如何从SharePoint文档库读取Excel文件并解析为DataFrame：

from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.user_credential import UserCredential
import pandas as pd
from io import BytesIO

# SharePoint配置信息
site_url = "https://你的租户域名.sharepoint.com/sites/你的站点名"
username = "你的账号@租户域名.com"
password = "你的密码"
doc_library_name = "文档库名称"  # 比如默认的"Documents"
file_path_in_sp = "文件在文档库中的相对路径"  # 例如"Data/Headcount_Raw.xlsx"

# 初始化SharePoint上下文
ctx = ClientContext(site_url).with_credentials(UserCredential(username, password))

# 下载文件到内存
file = ctx.web.get_file_by_server_relative_url(f"/sites/你的站点名/{doc_library_name}/{file_path_in_sp}")
file_content = file.read().execute_query()

# 解析为DataFrame
headcount_raw = pd.read_excel(BytesIO(file_content), sheet_name="Sheet1")
# 重复上述逻辑读取另外两个文件
# YTD_Mobility = pd.read_excel(...)
# Future_Mobility = pd.read_excel(...)

步骤2：生成多Sheet Excel并上传到SharePoint #

将多个DataFrame写入同一Excel工作簿，再上传到SharePoint指定文件夹：

from io import BytesIO
import openpyxl

# 在内存中创建Excel文件
output = BytesIO()
with pd.ExcelWriter(output, engine='openpyxl') as writer:
    headcount_raw.to_excel(writer, sheet_name='Headcount_Raw', index=False)
    YTD_Mobility.to_excel(writer, sheet_name='YTD_Mobility', index=False)
    Future_Mobility.to_excel(writer, sheet_name='Future_Mobility', index=False)

# 重置文件指针到开头
output.seek(0)

# 配置SharePoint目标位置
target_folder_url = f"/sites/你的站点名/{doc_library_name}/目标文件夹路径"
target_file_name = "Combined_Mobility_Report.xlsx"

# 上传文件到SharePoint
target_folder = ctx.web.get_folder_by_server_relative_url(target_folder_url)
target_folder.upload_file(target_file_name, output.getvalue()).execute_query()

print("文件已成功保存到SharePoint！")

关键注意事项 #

• 若账号开启MFA（多因素认证），用户名密码方式无法生效，需改用应用权限认证：

from office365.runtime.auth.client_credential import ClientCredential

client_id = "你的应用客户端ID"
client_secret = "你的应用客户端密钥"
ctx = ClientContext(site_url).with_credentials(ClientCredential(client_id, client_secret))

• 确保操作账号拥有对应文档库的读取+写入权限
• 文档库和文件的相对路径可通过SharePoint页面的「复制链接」功能获取准确片段

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