How to access an Excel file on Onedrive with Openpyxl

Question

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

Answer 1

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.

Answer 2

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}'")