Microsoft Excel 365 - Getting started in Peliqan

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

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

result = excel_writeback_sharepoint_api.update("rows",
	worksheet_name = "Sheet1",
	start_cell = "A1",
	end_cell = "B2", 
	values = data)

st.json(result)

result_get_drive_ids = excel_writeback_sharepoint_api.get('drive_id')
drive_id = result_get_drive_ids["value"][0]["id"]

file = {
    'drive_id': drive_id,
    'relative_file_path': "Excel_test_file.xlsx",
}
result = excel_writeback_sharepoint_api.get('drive_item_id', file)
drive_item_id = result["id"]

rows = {
    'drive_id': drive_id,
    'drive_item_id': drive_item_id,
    'sheet_name': "Sheet1",
    'start_cell': "A1",
    'end_cell': "B2",
    'values': [['abc', 'def'], [123, 456]],
}

# Make sure to use "" instead of blank/no values 
result = excel_writeback_sharepoint_api.update('rows', rows) 
st.json(result)

import numpy as np

excel_writeback_sharepoint_api = pq.connect('Sharepoint Excel 365 Writeback')

def int_to_excel_colindex(i):
  s = 1
  l = ''
  while i > 25 + s:   
    l += chr(65 + int((i-s)/26) - 1)
    i = i - (int((i-s)/26))*26
  l += chr(65 - s + (int(i)))
  return l

def cast_df(df):
  df = df.fillna(np.nan).replace([np.nan], [None])
  for c in df.columns.tolist():
    col_type = str(df.dtypes[c]).lower()
    if col_type[:8]=='datetime':
      df[c] = df[c].dt.strftime('%Y-%m-%d')
    elif col_type[:6]=="object":
      df[c] = df[c].astype(str)
  return df

dbconn = pq.dbconnect(pq.DW_NAME)
df = dbconn.fetch(pq.DW_NAME, 'chargebee', 'customers', df=True)

df = cast_df(df)

rows = df.values.tolist()
column_names = df.columns.tolist()
rows_with_header = [column_names] + rows

end_col = int_to_excel_colindex(len(column_names))
end_row = len(rows) + 1
end_cell = end_col + str(end_row)

result = excel_writeback_sharepoint_api.update("rows", 
  sheet_name = "Sheet1", 
  start_cell = "A1",
  end_cell = end_cell, 
  values = rows_with_header)

st.write(result)

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)

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