Microsoft Excel 365

Examples

Update rows

excel = pq.connect("Microsoft Excel 365 Writeback")

data = [
  ["abc", "def"],
  [123, 456]
]

result = excel.update("rows", 
	item_id = "2FB756A6F54BC7FB!741",  # id of the Excel file on Drive
	worksheet_name = "Sheet1",
	start_cell = "A1",
	end_cell = "B2", 
	values = data)

st.json(result)

Read the data of a sheet

Example to get data from sheets of the file that was entered when adding the connection under “My Connections”:

excel_api = pq.connect('Microsoft Excel 365') # Connection is linked to one Excel file

# Get full sheet
result = excel_api.get('worksheet_values', sheet_name = "Sheet1")
st.json(result)

# Get range on sheet
result = excel_api.get('worksheet_range_values', range = "A1:C4", sheet_name = "Sheet2")
st.json(result)

Read the data of any Excel file into a dataframe

import pandas as pd
import base64

# How to find the drive_id:
# See first part of the file_id (before "!") or copy "cid" from the OneDrive URL in your browser
drive_id = "2fe749a6f54bc7fb"

# How to find the item_id:
# See "id" or "resid" in the URL of the Excel file in your browser
item_id = "2fe749a6f54bc7fb!745"

excel_api = pq.connect('Microsoft Excel 365')

file_content_base64 = excel_api.get('worksheet_content', 
		worksheet_drive_id = drive_id,
    worksheet_drive_item_id =item_id)

file_content = base64.b64decode(file_content_base64['base64'])
df = pd.read_excel(file_content)
st.dataframe(df)

Tips & Trick

Convert a column number into the Excel column name (e.g. column 27 is AA):

def int_to_col_name(n):
    col_str = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        col_str = chr(65 + remainder) + col_str
    return col_str