Read Xlsx Stored On Sharepoint Location With Openpyxl In Python?

quick one. I have XLSX file located on sharepoint drive and cannot open it using openpyxl in python, it works well if it is stored on my local drive. I tried this. from openpyxl

Solution 1:

Instead of trying to load directly from a web-address, try using urllib.

importurllibfile="https://content.potatocompany.com/workspaces/PotatoTeam/Shared Documents/XYZ errors/XYZ Errors_Confirm.xlsx"
urllib.urlretrieve(file,"test.xlsx")

From further research, urllib is apparently eschewed by requests.Try this:

import requests
from requests.auth import HTTPBasicAuth
file = "https://content.potatocompany.com/workspaces/PotatoTeam/Shared Documents/XYZ errors/XYZ Errors_Confirm.xlsx"

username = 'myUsername'
password = 'myPassword'

resp=requests.get(file, auth=HTTPBasicAuth(username, password))
output = open('test.xlsx', 'wb')
output.write(resp.content)
output.close()

To get requests installed:

pip install requests

Solution 2:

You probably first need to download it first rather than opening it directly. The following approach should work:

import urllib2
from openpyxl import load_workbook
importStringIO

data = urllib2.urlopen("https://content.potatocompany.com/workspaces/PotatoTeam/Shared Documents/XYZ errors/XYZ Errors_Confirm.xlsx")
xlsx = data.read()
wb = load_workbook(StringIO.StringIO(xlsx))

Python's StringIO could be used to make the downloaded data appear as a file interface.

Solution 3:

If the SP is internal, it might work by removing "https:" in the name you put in the load_workbook().

from openpyxl importload_workbookfile='//content.potatocompany.com/workspaces/PotatoTeam/Shared Documents/XYZ errors/XYZ Errors_Confirm.xlsx'
wb = load_workbook(file)

without authentication if your account at work is directly connected to SP. Otherwise, at my work, we use NTML authentification, that you can do by using HttpNtlmAuth from the library request_ntml.

Let me know if it works or if you are still interested by this issue, I could give you example with request_ntml