通过REST API访问SharePoint上的Excel并同步数据至MS SQL

业务场景与方案背景 #

用户无法将全部所需数据录入ERP系统，通过维护Excel文件存储这类数据。为开展规划工作，需将该Excel数据与ERP数据库数据合并到新Excel文件，支持添加批注，并每日用最新ERP数据更新文件。

计划通过Python脚本实现：夜间采集Excel数据，加载至MS SQL完成数据转换，再将结果回传至SharePoint替换旧文件。当前核心难点是使用Office365 Python库访问SharePoint上的Excel文件。

实现方案 #

以下提供两种可行方案，满足读取Excel数据并转换为Pandas DataFrame的需求：

方案1：直接读取Excel指定范围数据 #

通过Office365库直接操作Excel对象，读取指定工作表和单元格范围：

首先安装依赖库：

pip install office365-rest-python-client pandas

实现代码：

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

# 配置参数
SHAREPOINT_USERNAME = "Username@company.com"
SHAREPOINT_PASSWORD = "Password"
SHAREPOINT_SITE_URL = "https://yourcompany.sharepoint.com/sites/YourTargetSite"
SHAREPOINT_DOC_LIBRARY = "Documents"  # 目标文档库名称
FILE_RELATIVE_PATH = "Path/To/NeededData.xls"  # 文件相对于文档库的路径
SHEET_NAME = "Sheet 1"
RANGE_ADDRESS = "A1:D20"

# 建立SharePoint连接
ctx = ClientContext(SHAREPOINT_SITE_URL).with_credentials(
    UserCredential(SHAREPOINT_USERNAME, SHAREPOINT_PASSWORD)
)

# 获取目标Excel文件对象
server_relative_url = f"/{SHAREPOINT_DOC_LIBRARY}/{FILE_RELATIVE_PATH}"
file = ctx.web.get_file_by_server_relative_url(server_relative_url)
ctx.load(file)
ctx.execute_query()

# 读取指定工作表的目标范围数据
workbook = file.open_excel()
worksheet = workbook.worksheet(SHEET_NAME)
range_data = worksheet.range(RANGE_ADDRESS).values
ctx.execute_query()

# 转换为Pandas DataFrame（假设第一行为表头）
df = pd.DataFrame(range_data[1:], columns=range_data[0])

# --- 后续写入MS SQL示例（需安装sqlalchemy和pyodbc）---
# from sqlalchemy import create_engine
# engine = create_engine(
#     "mssql+pyodbc://sql_username:sql_password@sql_server/db_name?driver=ODBC+Driver+17+for+SQL+Server"
# )
# df.to_sql("target_table_name", engine, if_exists="replace", index=False)

方案2：先下载Excel文件再读取 #

若直接操作Excel对象遇到权限或版本兼容问题，可先将文件下载至本地服务器，再用Pandas读取：

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

# 配置参数
SHAREPOINT_USERNAME = "Username@company.com"
SHAREPOINT_PASSWORD = "Password"
SHAREPOINT_SITE_URL = "https://yourcompany.sharepoint.com/sites/YourTargetSite"
SHAREPOINT_DOC_LIBRARY = "Documents"
FILE_RELATIVE_PATH = "Path/To/NeededData.xls"
LOCAL_TEMP_PATH = "./temp_NeededData.xls"  # 本地临时文件路径
SHEET_NAME = "Sheet 1"
RANGE = "A1:D20"

# 建立SharePoint连接
ctx = ClientContext(SHAREPOINT_SITE_URL).with_credentials(
    UserCredential(SHAREPOINT_USERNAME, SHAREPOINT_PASSWORD)
)

# 下载文件到本地
server_relative_url = f"/{SHAREPOINT_DOC_LIBRARY}/{FILE_RELATIVE_PATH}"
file = ctx.web.get_file_by_server_relative_url(server_relative_url)
with open(LOCAL_TEMP_PATH, "wb") as local_file:
    file.download(local_file).execute_query()

# 用Pandas读取指定范围数据
df = pd.read_excel(LOCAL_TEMP_PATH, sheet_name=SHEET_NAME, usecols=RANGE)

# 清理临时文件
os.remove(LOCAL_TEMP_PATH)

# --- 后续写入MS SQL代码同方案1 ---

注意事项 #

• 确保使用的Office365库为最新稳定版，避免兼容性问题
• 验证账号对目标SharePoint站点、文档库及文件拥有读写权限
• 若账号启用了多因素认证（MFA），需改用应用权限或证书认证方式，上述代码仅支持账号密码认证

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