Python - how to read Sharepoint excel sheet specific worksheet

Question

In Python I am utilizing Office 365 REST Python Client library to access and read an excel workbook that contains many sheets.

While the authentication is successful, I am unable to append the right path of sheet name to the file name in order to access the 1st or 2nd worksheet by its name, which is why the output from the sheet is not JSON, rather IO Bytes which my code is not able to process.

My end goal is to simply access the specific work sheet by its name 'employee_list' and transform it into JSON or Pandas Data frame for further usage.

Code snippet below -

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/sites/SAMPLE/_layouts/15/Doc.aspx?OR=teams&action=edit&sourcedoc={739271873}'      
# HOW TO ACCESS WORKSHEET BY ITS NAME IN ABOVE LINE

ctx = 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

Answer 1

You can access it by sheet index, check the following code....

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))

Answer 2

The update I'm using (Office365-REST-Python-Client==2.3.11) allows simpler access to an Excel file in the SharePoint repository.

# 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

We now have a binary copy of the Excel file as BytesIO named excel_file. Progressing, reading it as pd.DataFrame is straight-forward like usual Excel file stored in local drive. Eg.:

pd.read_excel(excel_file) # -> pd.DataFrame

Hence, if you are interested in a specific sheet like 'employee_list', you may preferably read it as

employee_list = pd.read_excel(excel_file,
                              sheet_name='employee_list')
    # -> pd.DataFrame

or

data = pd.read_excel(excel_file,
                     sheet_name=None) # -> dict
employee_list = data.get('employee_list') 
    # -> [pd.DataFrame, None]