I want to open an Excel file (on Onedrive) with Openpyxl (Python). I received error trying this:
from openpyxl import load_workbook
file = r"https://d.docs.live.net/dd10xxxxxxxxxx"
wb = load_workbook(filename = file)
self.fp = io.open(file, filemode)
OSError: [Errno 22] Invalid argument: 'https://d.docs.live.net/dd10...
OpenPyXL cannot read/write files over http. It expects a file on a traditional filesystem, whether it's local, on a network share, etc.
If you're using OneDrive For Business you could try mapping it to a drive letter, or investigate the use of Google Sheets and the gspread library instead.
If no authentication to be concerned about, copy the shared URL from Onedrive and convert to a direct download URL then request direct url as a bytes file which Openpyxl can upload.
The example onedrive url is from my local drive just for clarity.
Note the conversion function is copied from OneDrive as Data Storage for Python Project by Joe T. Santhanavanich
import base64
import io
import urllib.request
from openpyxl import load_workbook
def create_onedrive_directdownload(onedrive_link):
data_bytes64 = base64.b64encode(bytes(onedrive_link, 'utf-8'))
data_bytes64_String = data_bytes64.decode('utf-8').replace('/', '_').replace('+', '-').rstrip("=")
resultUrl = f"https://api.onedrive.com/v1.0/shares/u!{data_bytes64_String}/root/content"
return resultUrl
### Original link copied from the file in onedrive
onedrive_link = "https://1drv.ms/x/s!AoNMV-zn1OSxhANyuaBK4RQiKmDb?e=tZ2mrv"
### Converted Onedrive link
onedrive_direct_link = create_onedrive_directdownload(onedrive_link)
### Retrieve url as bytes file
file = urllib.request.urlopen(onedrive_direct_link).read()
### Load file into Openpyxl
wb = load_workbook(filename=io.BytesIO(file))
ws = wb['Sheet1']
print(f"Value in Cell A1: '{ws['A1'].value}'")
One alternative way is to use google drive rather than one drive then open google colab, mount the google drive and open the file from google drive.
from google.colab import drive
drive.mount('/content/gdrive/')
