在 Python 中，我利用Office 365 REST Python 客戶端庫(kù)來(lái)訪問(wèn)和讀取包含許多工作表的 Excel 工作簿。雖然身份驗(yàn)證成功，但我無(wú)法將工作表名稱的正確路徑附加到文件名，以便通過(guò)其名稱訪問(wèn)第一個(gè)或第二個(gè)工作表，這就是為什么工作表的輸出不是 JSON，而是 IO Bytes 的原因我的代碼無(wú)法處理。我的最終目標(biāo)是簡(jiǎn)單地通過(guò)名稱“employee_list”訪問(wèn)特定工作表，并將其轉(zhuǎn)換為 JSON 或 Pandas 數(shù)據(jù)框架以供進(jìn)一步使用。下面的代碼片段 -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 = 'abc@a.com'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貢獻(xiàn)1942條經(jīng)驗(yàn) 獲得超3個(gè)贊
您可以通過(guò)工作表索引訪問(wèn)它，檢查以下代碼......
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貢獻(xiàn)1155條經(jīng)驗(yàn) 獲得超0個(gè)贊
您可以嘗試將組件“sheetname”添加到網(wǎng)址中，如下所示。
https://site/lib/workbook.xlsx#'Sheet1'!A1
尚方寶劍之說(shuō)
TA貢獻(xiàn)1788條經(jīng)驗(yàn) 獲得超4個(gè)贊
我正在使用的更新 ( Office365-REST-Python-Client==2.3.11) 允許更輕松地訪問(wèn) SharePoint 存儲(chǔ)庫(kù)中的 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現(xiàn)在我們有了一個(gè)名為 的Excel 文件的二進(jìn)制副本excel_file。繼續(xù)閱讀它，pd.DataFrame就像存儲(chǔ)在本地驅(qū)動(dòng)器中的普通 Excel 文件一樣直接。例如。：
pd.read_excel(excel_file) # -> pd.DataFrame
因此，如果您對(duì)特定的工作表（例如 ）感興趣'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貢獻(xiàn)1828條經(jīng)驗(yàn) 獲得超3個(gè)贊
我知道您說(shuō)過(guò)您不能使用 BytesIO 對(duì)象，但是對(duì)于那些像我一樣以 BytesIO 對(duì)象形式讀取文件的人來(lái)說(shuō)，您可以使用 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
呼喚遠(yuǎn)方
TA貢獻(xiàn)1856條經(jīng)驗(yàn) 獲得超11個(gè)贊
看來(lái)構(gòu)建的訪問(wèn)數(shù)據(jù)的 URL 不正確。您應(yīng)該在瀏覽器中測(cè)試完整的 URL 是否正常工作，然后修改代碼即可開(kāi)始。您可以嘗試進(jìn)行一些更改，我已經(jīng)驗(yàn)證使用此邏輯形成的 URL 將返回 JSON 數(shù)據(jù)。
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 = 'abc@a.com'
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)?
添加回答
舉報(bào)
0/150
提交
取消