从SharePoint上的Excel文件中提取数据到一个DataFrame中

后端开发 2026-07-08

我想把存放在SharePoint的、结构完全相同的Excel文件中的数据提取出来，汇总到一个DataFrame中。

目前，下面的做法在常量填好后似乎可行，但速度相当慢。现在大约有60个文件，所有文件合计大约5,000条记录，运行大约需要5 分钟。不过，每增加一个新文件到源文件夹，耗时会明显增加。

我并不指望有大量更多的文件或数据，但如果有更简单高效地完成下面这件事的方法，我宁愿采用。

有没有办法改进下面的做法？

我还需要在每个Excel文件的前3-4行跳过，但具体怎么做还不太清楚。

import requests
import pandas as pd

HEADERS = ""
SITE_ID = ""
DRIVE_ID = ""
SHAREPOINT_PATH = ""
RANGE_TO_READ = ""

def extract_from_sharepoint_():

    folder_items_url = f"https://graph.microsoft.com/v1.0/sites/{SITE_ID}/drives/{DRIVE_ID}/root:/{SHAREPOINT_PATH}:/children"

    folder_items = requests.get(folder_items_url, headers=HEADERS)
    folder_items = folder_items.json().get('value', [])

    dataframes = []
    for x in folder_items:

        file_id = x['id']
        file_name = x['name']

        if not file_name.endswith('.xlsx'):
            continue

        worksheet_url = f'https://graph.microsoft.com/v1.0/drives/{DRIVE_ID}/items/{file_id}/workbook/worksheets'
        response = requests.get(worksheet_url, headers=HEADERS)
        worksheets_name = response.json()['value'][0]['name']

        download_url = f"https://graph.microsoft.com/v1.0/drives/{DRIVE_ID}/items/{file_id}/workbook/worksheets('{worksheets_name}')/range(address='{RANGE_TO_READ}')"

        file_response = requests.get(download_url, headers=HEADERS)
        file_response = file_response.json()['values']
        df = pd.DataFrame(file_response[1:], columns=file_response[0])
        df.columns = df.columns.map(lambda x: x.lower().strip())

        dataframes.append(df)

    combined_df = pd.concat(dataframes, ignore_index=True)

解决方案

60个文件（只有大约5,000行数据）用5 分钟太慢。

我建议：绕过Graph工作簿API。改为使用 /content 端点将原始二进制文件直接下载到内存中，并让 pandas 在本地解析。这样也把对API的请求次数降到每个文件1 次（目前每个文件3 次API请求）。

用 openpyxl 搭配 pip3 install openpyxl 安装。openpyxl 能够有效解析数据，速度能降到秒级。使用 skiprows 参数跳过前几行

import io
import requests
import pandas as pd

HEADERS = {"Authorization": "Bearer YOUR_TOKEN"} # Ensure correct auth formatting
SITE_ID = "YOUR_SITE_ID"
DRIVE_ID = "YOUR_DRIVE_ID"
SHAREPOINT_PATH = "YOUR_FOLDER_PATH"

def extract_from_sharepoint_():
    # Get the list of items in the folder
    folder_items_url = f"https://graph.microsoft.com/v1.0/sites/{SITE_ID}/drives/{DRIVE_ID}/root:/{SHAREPOINT_PATH}:/children"

    # Use a session for connection pooling (faster subsequent requests)
    session = requests.Session()
    session.headers.update(HEADERS)

    response = session.get(folder_items_url)
    response.raise_for_status() # Catch HTTP errors early
    folder_items = response.json().get('value', [])

    dataframes = []

    for item in folder_items:
        file_name = item['name']
        file_id = item['id']

        # Only process Excel files
        if not file_name.endswith('.xlsx'):
            continue

        # Get the direct binary download endpoint for the file
        download_url = f"https://graph.microsoft.com/v1.0/drives/{DRIVE_ID}/items/{file_id}/content"

        file_response = session.get(download_url)
        if file_response.status_code != 200:
            print(f"Failed to download {file_name}")
            continue

        # Read the binary data straight into Pandas without saving to disk
        # 'skiprows=4' skips the first 4 rows. Row 5 becomes your header.
        try:
            df = pd.read_excel(
                io.BytesIO(file_response.content), 
                sheet_name=0,  # Reads the first sheet dynamically
                skiprows=4,
                engine='openpyxl' # Use openpyxl
            )

            # Clean columns
            df.columns = df.columns.map(lambda x: str(x).lower().strip())
            dataframes.append(df)

        except Exception as e:
            print(f"Error parsing {file_name}: {e}")

    # Combine all DataFrames 
    if dataframes:
        combined_df = pd.concat(dataframes, ignore_index=True)
        return combined_df
    else:
        print("No data found.")
        return pd.DataFrame()

在上面的代码中，requests.Session() 重用同一个连接池（目前对于每一个HTTP请求都会新建一次握手连接）

io.BytesIO 会让文件始终驻留在RAM中。因此，你可以省去将临时文件写入硬盘的时间。

Excel