Reading Excel using Openpyxl from Sharepoint

Question

I have excel saved in sharepoint and I am trying to read it with openpyxl. I have URL path to excel which is below and I have different options I see on internet and none of them worked.

How can I read excel in below path using openpyxl?

https://company.sharepoint.com/:x:/r/sites/global/tasks/ADT/_layouts/15/Doc.aspx?sourcedoc=%7BBFD411FA-303E-4950-961C-6702D81E112B%7D&file=SEPT%20ALL%20ROB.xlsx&action=default&mobileredirect=true

Below is the code I have and I getting HTTP forbidden error

from openpyxl import load_workbook
from io import BytesIO
import urllib


URL = https://company.sharepoint.com/:x:/r/sites/global/tasks/ADT/_layouts/15/Doc.aspx? 
sourcedoc=%7BBFD411FA-303E-4950-961C- 
6702D81E112B%7D&file=SEPT%20ALL%20ROB.xlsx&action=default&mobileredirect=true

def load_workbook_from_url(URL):
  file = urllib.request.urlopen(URL).read()
  return load_workbook(filename=BytesIO(file))


wb = load_workbook_from_url(URL)

sheetlist = wb.get_sheet_names()
print(sheetlist)

Error I get is : raise HTTPError(req.full_url, code, msg, hdrs, fp) urllib.error.HTTPError: HTTP Error 403: Forbidden

Try defining the URL as a raw string with URL = r"https://path/to/your/file.xlsx". Other than that, i do not know if you can access files on a sharepoint publicly like that, i would need an example, accessible file to investigate further. — mnikley
– mnikley, Commented Dec 6, 2021 at 17:18
I tried with raw string and I did not work. If I copy and paste above URL, it will open excel in browser. It is internal website not external. — Bobby
– Bobby, Commented Dec 6, 2021 at 17:33

walther leonardo · Accepted Answer · 2022-07-21 17:27:16Z

This worked for me: import shareplum and define the file to be downloaded

from shareplum import Site
from shareplum import Office365
from shareplum.site import Version

def loadShareFile(filename):
    authcookie = Office365('https://{your sharepoint site}.sharepoint.com', username='{usermail}@mail.com', password='{your password}').GetCookies()
    site = Site('https://{your sharepoint site}.sharepoint.com/sites/{your path}', version=Version.v365, authcookie=authcookie)
    
    folder = site.Folder('Documentos Compartidos/{folder}')  #or Shared Documents
    return folder.get_file(filename)  # this return bytes

then get the data with openpyxl:

from openpyxl import load_workbook
from io import BytesIO
import loadShareFile  # import the above method 

wb = load_workbook(BytesIO(loadShareFile(filename='{my file name}.xlsx')))

that's it! now you have you wb object to work with. If someboy nows how to do the other way arround will help me, I'm struggling with that part.

Collectives™ on Stack Overflow

Reading Excel using Openpyxl from Sharepoint

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related