0

I am attempting to open, read and use a macro and then re save an Excel file on Sharepoint using Python. Using the Office 365 REST-Python Client I can open and read but struggling to see how to do the rest.

Would appreciate any help, thanks!

`ctx_auth = AuthenticationContext(url)
if ctx_auth.acquire_token_for_user(username, password):
    ctx = ClientContext(url, ctx_auth)
    web = ctx.web
    ctx.load(web)
    ctx.execute_query()
    response = File.open_binary(ctx, relative_url)
    #save data to BytesIO stream
    bio = io.BytesIO()
    bio.write(response.content)
    bio.seek(0) #set file object to start

    #read file into pandas dataframe
    df = pd.read_excel(bio, sheet_name="Overview")
    print(df)
    df.at[0,"Unnamed: 1"] = "description"

    bio2 = io.BytesIO()

    #pip install xlsxwriter
    writer = pd.ExcelWriter(bio2)
    df.to_excel(writer, sheet_name="Overview")
    writer.save()
    bio2.seek(0)
    df = pd.read_excel(bio2, sheet_name="Overview")
    workbook = bio2.read()
    response2 = File.save_binary(ctx, relative_url, workbook)
    print(response2)`
Adil Anees
  • 1

1 Answers1

0

You can refer to the following Python script to save and read Excel files on Sharepoint.

#import all the libraries
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File 
import io
import pandas as pd

#target url taken from sharepoint and credentials
url = 'https://company.sharepoint.com/user/folder'
path = '/user/folder/Documents/Target_Excel_File_v4.xlsx'
username = 'Dumby_account@company.com'
password = 'Password!'

ctx_auth = AuthenticationContext(url)
if ctx_auth.acquire_token_for_user(username, password):
  ctx = ClientContext(url, ctx_auth)
  web = ctx.web
  ctx.load(web)
  ctx.execute_query()
  print("Authentication successful")

response = File.open_binary(ctx, path)

#save data to BytesIO stream
bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0) #set file object to start

#read excel file and each sheet into pandas dataframe 
df = pd.read_excel(bytes_file_obj, sheet_name = None)
print(df)

There is a similar SO threading problem here.

Carl Zhao
  • 8,543
  • 2
  • 11
  • 19
  • This didn't really help, i am able to open read the file easily. I followed the vid https://www.youtube.com/watch?v=w0pBFo9zpiU&t=176s but when executing the uploadfile(file_name, content).execute_query() function i get an error office365.runtime.client_requestexception.ClientRequestException: ('-1, Microsoft.Sharepoint.Client.ClientServiceException', "The HTTP method 'GET' cannot be used to access the resource ;Add." – Adil Anees Feb 01 '23 at 18:17