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