在 Python 中，我利用Office 365 REST Python 客戶端庫來訪問和讀取包含許多工作表的 Excel 工作簿。雖然身份驗證成功，但我無法將工作表名稱的正確路徑附加到文件名，以便通過其名稱訪問第一個或第二個工作表，這就是為什么工作表的輸出不是 JSON，而是 IO Bytes 的原因我的代碼無法處理。我的最終目標是簡單地通過名稱“employee_list”訪問特定工作表，并將其轉換為 JSON 或 Pandas 數據框架以供進一步使用。下面的代碼片段 -import ioimport jsonimport pandas as pdfrom office365.runtime.auth.authentication_context import AuthenticationContextfrom office365.runtime.auth.user_credential import UserCredentialfrom office365.runtime.http.request_options import RequestOptionsfrom office365.sharepoint.client_context import ClientContextfrom office365.sharepoint.files.file import Filefrom io import BytesIOusername = '[email protected]'password = 'abcd'site_url = 'https://sample.sharepoint.com/sites/SAMPLE/_layouts/15/Doc.aspx?OR=teams&action=edit&sourcedoc={739271873}' # HOW TO ACCESS WORKSHEET BY ITS NAME IN ABOVE LINEctx = ClientContext(site_url).with_credentials(UserCredential(username, password))request = RequestOptions("{0}/_api/web/".format(site_url))response = ctx.execute_request_direct(request)json_data = json.loads(response.content) # ERROR ENCOUNTERED JSON DECODE ERROR SINCE DATA IS IN BYTES
5 回答
手掌心
TA貢獻1942條經驗 獲得超3個贊
您可以通過工作表索引訪問它，檢查以下代碼......
import xlrd
loc = ("File location")
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
# For row 0 and column 0
print(sheet.cell_value(1, 0))
尚方寶劍之說
TA貢獻1788條經驗 獲得超4個贊
我正在使用的更新 ( Office365-REST-Python-Client==2.3.11) 允許更輕松地訪問 SharePoint 存儲庫中的 Excel 文件。
# from original_question import pd,\
# username,\
# password,\
# UserCredential,\
# File,\
# BytesIO
user_credentials = UserCredential(user_name=username,
password=password)
file_url = ('https://sample.sharepoint.com'
'/sites/SAMPLE/{*recursive_folders}'
'/sample_worksheet.xlsx')
## absolute path of excel file on SharePoint
excel_file = BytesIO()
## initiating binary object
excel_file_online = File.from_url(abs_url=file_url)
## requesting file from SharePoint
excel_file_online = excel_file_online.with_credentials(
credentials=user_credentials)
## validating file with accessible credentials
excel_file_online.download(file_object=excel_file).execute_query()
## writing binary response of the
## file request into bytes object
BytesIO現在我們有了一個名為 的Excel 文件的二進制副本excel_file。繼續閱讀它，pd.DataFrame就像存儲在本地驅動器中的普通 Excel 文件一樣直接。例如。：
pd.read_excel(excel_file) # -> pd.DataFrame
因此，如果您對特定的工作表（例如 ）感興趣'employee_list'，您最好將其閱讀為
employee_list = pd.read_excel(excel_file,
sheet_name='employee_list')
# -> pd.DataFrame
或者
data = pd.read_excel(excel_file,
sheet_name=None) # -> dict
employee_list = data.get('employee_list')
# -> [pd.DataFrame, None]
子衿沉夜
TA貢獻1828條經驗 獲得超3個贊
我知道您說過您不能使用 BytesIO 對象，但是對于那些像我一樣以 BytesIO 對象形式讀取文件的人來說，您可以使用 arg sheet_namein pd.read_excel：
url = "https://sharepoint.site.com/sites/MySite/MySheet.xlsx"
sheet_name = 'Sheet X'
response = File.open_binary(ctx, relative_url)
bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0)
df = pd.read_excel(bytes_file_obj, sheet_name = sheet_name) //call sheet name
呼喚遠方
TA貢獻1856條經驗 獲得超11個贊
看來構建的訪問數據的 URL 不正確。您應該在瀏覽器中測試完整的 URL 是否正常工作，然后修改代碼即可開始。您可以嘗試進行一些更改，我已經驗證使用此邏輯形成的 URL 將返回 JSON 數據。
import io
import json
import pandas as pd
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.runtime.auth.user_credential import UserCredential
from office365.runtime.http.request_options import RequestOptions
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File
from io import BytesIO
username = '[email protected]'
password = 'abcd'
site_url = 'https://sample.sharepoint.com/_vti_bin/ExcelRest.aspx/RootFolder/ExcelFileName.xlsx/Model/Ranges('employee_list!A1%7CA10')?$format=json'? ? ??
# Replace RootFolder/ExcelFileName.xlsx with actual path of excel file from the root.
# Replace A1 and A10 with actual start and end of cell range.
ctx = ClientContext(site_url).with_credentials(UserCredential(username, password))
request = RequestOptions(site_url)
response = ctx.execute_request_direct(request)
json_data = json.loads(response.content)?
添加回答
舉報
0/150
提交
取消