[python]使用 Python 从我的组织 sharepoint 读取/下载一个 excel 文件

· 收录于 2023-12-06 19:56:09 · source URL

问题详情

我正在尝试从我组织的 sharepoint 中的共享文件夹中读取/下载 excel 文件。目前,我有一个 Azure AD 应用程序,我正在尝试使用它来验证访问。我正在使用在此处找到的代码来测试连接,但仍然收到以下错误消息:

None
{'token_type': 'Bearer', 'expires_in': 3599, 'ext_expires_in': 3599, 'access_token': 'retrieved_token'}
Read file failed. Status code: 400

我的应用具有以下访问权限: 应用

我还要求办公室管理员通过以下链接批准权限:https://login.microsoftonline.com/{tenant_ID_HERE}/adminconsent?client_id={CLIENT ID HERE},我在另一篇帮助 OP 的帖子中看到了这一点,他已经批准了。

这是我的代码:

import requests
import msal
import io
import pandas as pd

# Azure App Parameters
client_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
client_secret = 'mysecrethere'
authority = 'https://login.microsoftonline.com/tenant_id'

# SharePoint Parameters
sharepoint_site_url = 'https://myorganization.sharepoint.com'
excel_file_path = '/teams/group/Shared Documents/folder1/folder2/folder3/myexcelfile.xlsx'

# Create a ConfidentialClientApplication instance for credentials
app = msal.ConfidentialClientApplication(client_id, authority=authority, client_credential=client_secret)

# Acquire an access token using client credentials
result = app.acquire_token_silent(scopes=['https://graph.microsoft.com/.default'], account=None)
print(result)
if not result:
    # If the token is not found in the cache, acquire a new one
    result = app.acquire_token_for_client(scopes=['https://graph.microsoft.com/.default'])

# Print the result to inspect its content
print(result)

if 'access_token' in result:
    # Successful
    access_token = result['access_token']
    
    # Call Microsoft Graph to read the Excel file
    graph_url = f'https://graph.microsoft.com/v1.0/sites/https://myorganization.sharepoint.com:/r/teams/group/Shared Documents/folder1/folder2/folder3/myexcelfile.xlsx:/content'
    headers = {'Authorization': f'Bearer {access_token}'}
    response = requests.get(graph_url, headers=headers)

    if response.status_code == 200:
        # Successful
        excel_data = io.BytesIO(response.content)
        df = pd.read_excel(excel_data)
        print(df.head())
    else:
        print(f"Read file failed. Status code: {response.status_code}")
else:
    print("Failed to get access token.")

最佳回答

我在 SharePoint 网站中上传了一个文件,如下所示:

在此处输入图片描述

错误读取文件失败。状态代码:400,如果传递无效的图形 URL/查询来执行操作,通常会出现。

URL 必须是 https://graph.microsoft.com/v1.0/sites/SiteID/lists/listID/items/ItemID/driveItem/content

获取列表 ID:

在此处输入图像描述

列出项目并记下 ID:

https://graph.microsoft.com/v1.0/sites/SiteID/lists/ListID/items

在此处输入图像描述

通过传递正确的 Microsoft Graph 查询来修改代码,如下所示:

import requests
import msal
import io
import pandas as pd

# Azure App Parameters
client_id = 'a6ade814-fdba-4432-b71a-5af5ea***'
client_secret = '1di8Q~2Mltdd1l8****"'
authority = 'https://login.microsoftonline.com/TenantId'

# SharePoint Parameters
sharepoint_site_url = 'https://****.sharepoint.com'
#excel_file_path = 'https://****.sharepoint.com/sites/testruk/folder1/Book.xlsx'
# Create a ConfidentialClientApplication instance for credentials
app = msal.ConfidentialClientApplication(client_id, authority=authority, client_credential=client_secret)

# Acquire an access token using client credentials
result = app.acquire_token_silent(scopes=['https://graph.microsoft.com/.default'], account=None)
print(result)
if not result:
    # If the token is not found in the cache, acquire a new one
    result = app.acquire_token_for_client(scopes=['https://graph.microsoft.com/.default'])

# Print the result to inspect its content
print(result)

if 'access_token' in result:
    # Successful
    access_token = result['access_token']
    
    # Call Microsoft Graph to read the Excel file
    graph_url = f'https://graph.microsoft.com/v1.0/sites/siteid/lists/listid/items/10/driveItem/content'
    headers = {'Authorization': f'Bearer {access_token}'}
    response = requests.get(graph_url, headers=headers)

    if response.status_code == 200:
        # Successful
        excel_data = io.BytesIO(response.content)
        df = pd.read_excel(excel_data)
        print(df.head())
    else:
        print(f"Read file failed. Status code: {response.status_code}")
else:
    print("Failed to get access token.")

在此处输入图像描述

您将获得一个 @microsoft.graph.downloadUrl,当您单击该 URL 时,文件将被下载。

请确保向管理员授予对 Sites.Read.All Microsoft Graph API 权限的同意:

在此处输入图像描述

参考:

postman - 使用 Microsoft Graph API 下载 Sharepoint 文件不显示内容属性 - Stack Overflow 作者:Vadim Gremyachev