Streamlit: connection to a SharePoint folder — Part 3
Very recently, I had the opportunity to explore some features and capacities of Python, in particular with the library Streamlit for creating apps. Moreover, I also took a look at how to connect an app with a SharePoint folder and interact with its content.
This article is the second of a short series that I would like to share, where I will cover the following topics:
- Connect to a SharePoint folder and read its content (Part 1)
- Read and save a file from a SharePoint folder (Part 2)
- Upload and modify a file in a SharePoint folder (Part 3)
Streamlit: Brief introduction
Streamlit is an open-source Python library that can be used to develop and publish interactive apps for a very large number of use cases. It is very simple to use and, if you have a basic knowledge of Python, then you will find it a very useful tool. I used it for building from very simple apps (for some of my daily routines like saving useful links or organizing my calisthenics training) to more advanced ones, to practice and explore some of its capabilities.
One of those advanced apps was the implementation of a pipeline to clean and use an imported dataset with a Machine Learning model to make predictions in a guided process (this may be a future article).
This small intro is just to give a very broad idea of how to use this fantastic library. Now, let’s jump into the main topic of the article. Note: some topics, Python libraries, and Streamlit modules will be taken for granted and not discussed further (please refer to the official documentation for details).
Upload a new file to the folder
Starting from the previous script, we can create a new section in the app: in this section, we would like to:
- Upload a new file in the app
- Modify the uploaded file
- Upload the file to the SharePoint folder
First, below the if-statement of the previous script, we need to write a code to display the “file uploader” through which we can upload a file from our computer:
# Section: Upload a file
st.write("")
st.write("")
st.write("")
File_new = st.file_uploader("Upload an Excel file from your computer")
# Display the file only if it is uploaded into the app
if File_new :
File_new_df = pd.read_excel(File_new)
st.write("")
st.write("This is the file your uploaded:")
st.write(File_new_df)The first lines are useful to add vertical spaces between the previous section and this one. The Excel file we upload will be saved in the variable “File_new” which will be converted into a pandas dataframe. The if-statement is necessary to avoid displaying “None” if no file has been uploaded. The output will be something like this:
Manipulate the content of the file
Now we have a new file in our app. What we can do is define some filters to manipulate it and then display it again:
# Choose some filters
c1, c2 = st.columns(2)
with c1 :
Nation_filter = st.selectbox( 'Filter: Nation', File_new_df["Nation"], key = 1 )
with c2:
City_filter = st.selectbox( 'Filter: City', File_new_df["City"], key = 2 )
File_new_df_filtered = File_new_df[(File_new_df["Nation"] == Nation_filter) & (File_new_df["City"] == City_filter)]
st.write("This is the filtered table:")
st.write(File_new_df_filtered)Filters are displayed in two columns (defined with st.columns) and each chosen value is stored in a variable. Those choices will be used to filter the dataframe. The output will be something like this:
Of course, if you choose “Germany” and “Paris”, the table will be empty because the above filters are applied with an And statement.
Save the file in the folder
At this point, we have a filtered table that can be saved into our SharePoint folder. To do that, we have to use the object st.session_state[“ctx”] defined in the first article. The script will be very similar to the one used in the second article:
# Save data into a file when the button is clicked
if st.button("Save data in SharePoint folder") :
# Create "writer" object
output = BytesIO()
writer = pd.ExcelWriter(output, engine = 'xlsxwriter')
# Convert data into Excel and choose a sheet name
File_new_df_filtered.to_excel(writer,
index = False,
sheet_name = 'New filtered data')
writer.save()
output.seek(0)
workbook = output.read()
# Upload the file in folder, choosing a name
target_folder.upload_file("Filtered_data.xlsx", workbook).execute_query()
st.write("**New file uploaded successfully!**")Once the button is clicked, voilà: the file is now in our SharePoint folder!
Modify the app layout with a navigation menu and sections
At this point, we have a complete app for reading the content of a SharePoint folder, modifying one of its files, and uploading a new one. We can now make some changes to the layout. For example, we can put each section on a separate page and add a top menu to navigate them. After the authentication process, we can add the following script:
# Useful library
from streamlit_option_menu import option_menu
# Navigation menu
Pages = option_menu("", ['File list of your SharePoint folder',
'Save data',
'Upload a new file'],
default_index = 0,
orientation = "horizontal")This will create a horizontal navigation menu only if the authentication is successful. Now we have to distribute each section into its page. To do that, we need to add an if-elif statement before each section in this way:
if Pages == 'File list of your SharePoint folder' :
#
#
# Code here
#
#
elif Pages == 'Save data' :
#
#
# Code here
#
#
elif Pages == 'Upload a new file' :
#
#
# Code here
#
#Each statement means that if we are on a specific page (by clicking on the menu), only the code under that statement will be processed, showing the corresponding content. After the authentication, we will have something like this:
However, we have a problem: if we run the code with only this modification, once we change the menu page to “Save data”, we will encounter an error. This is because, in the previous section, we defined an element that we use in the “Save data” section. By adding an if statement, each time we change the page, that element will not be “remembered” by the app and therefore it does not know what to do. To solve this problem, we have to save that element in the app session_state, as we did before. All we have to do is to change this:
File_content = pd.read_excel(bytes_file_obj_reports) into this:
st.session_state["File_content"] = pd.read_excel(bytes_file_obj_reports) In the script, each time we want to use this element, we must refer to it by st.session_state[“File_content”]. If we had similar elements in the script, we should have applied the same procedure for each one of them.
The final script for the whole app is the following:
# Useful libraries
import io
import pandas as pd
import streamlit as st
from io import BytesIO
from streamlit_option_menu import option_menu
from office365.sharepoint.files.file import File
from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.authentication_context import AuthenticationContext
# Page setup
st.set_page_config(layout = 'wide')
# SharePoint and Folder urls
sharepoint_url = your_sharepoint_url
folder_in_sharepoint = your_sharepoint_folder_url
# Authentication section: e-mail and password as input
placeholder = st.empty()
with placeholder.container():
col1, col2, col3 = st.columns(3)
with col2:
st.markdown("## **SharePoint connection with Streamlit**")
st.markdown("--------------")
email_user = st.text_input("Your e-mail")
password_user = st.text_input("Your password", type="password")
Button = st.button("Connect")
if st.session_state.get('button') != True:
st.session_state['button'] = Button
# Authentication and connection to SharePoint
def authentication(email_user, password_user, sharepoint_url) :
auth = AuthenticationContext(sharepoint_url)
auth.acquire_token_for_user(email_user, password_user)
ctx = ClientContext(sharepoint_url, auth)
web = ctx.web
ctx.load(web)
ctx.execute_query()
return ctx
# Check if the button "Connect" has been clicked
if st.session_state['button'] :
try :
placeholder.empty()
# Authentication objects
if "ctx" not in st.session_state :
st.session_state["ctx"] = authentication(email_user,
password_user,
sharepoint_url)
st.write("Authentication: successfull!")
st.write("Connected to SharePoint: **{}**".format( st.session_state["ctx"].web.properties['Title']))
# Navigation menu
Pages = option_menu("", ['File list of your SharePoint folder', 'Save data', 'Upload a new file'], default_index = 0, orientation = "horizontal")
# Connection to the SharePoint folder
target_folder = st.session_state["ctx"].web.get_folder_by_server_relative_url(folder_in_sharepoint)
# Read and load items
items = target_folder.files
st.session_state["ctx"].load(items)
st.session_state["ctx"].execute_query()
# Save some information for each file using item.properties
names, last_mod, relative_url = [], [], []
for item in items:
names.append( item.properties["Name"] )
last_mod.append( item.properties["TimeLastModified"] )
relative_url.append( item.properties["ServerRelativeUrl"] )
# First page
if Pages == 'File list of your SharePoint folder' :
# Create and display the final dataframe
Index = ["File name", "Last modified", "Relative url"]
dataframe = pd.DataFrame([names, last_mod, relative_url], index = Index).T
st.write("")
st.write("")
st.write("These are the files in the folder:")
st.table(dataframe)
# Take the Relative URL of the file
path_to_the_file = dataframe[dataframe["File name"] == "Test_File.xlsx"]["Relative url"].iloc[0]
# Read the file content
response_reports = File.open_binary(st.session_state["ctx"], path_to_the_file)
bytes_file_obj_reports = io.BytesIO() # Save data to BytesIO stream
bytes_file_obj_reports.write(response_reports.content)
bytes_file_obj_reports.seek(0) # Set file object to start
st.session_state["File_content"] = pd.read_excel(bytes_file_obj_reports) # Save content in a pandas dataframe and in the session
# Display the dataframe
st.write("This is your file:")
st.table(st.session_state["File_content"])
# Second page
elif Pages == 'Save data' :
# Manipulated dataframe (example)
File_content_new = st.session_state["File_content"][(st.session_state["File_content"]["Field1"].isin(["A","B"])) & (st.session_state["File_content"]["Value"]>=5)]
# Diplay the data
st.write("This is your data:")
st.table(File_content_new)
# Save data into a file when the button is clicked
if st.button("Save new data") :
# Create "writer" object
output = BytesIO()
writer = pd.ExcelWriter(output, engine = 'xlsxwriter')
# Convert data into Excel and choose a sheet name
File_content_new.to_excel(writer,
index = False,
sheet_name = 'New data')
writer.save()
output.seek(0)
workbook = output.read()
# Upload the file in folder, choosing a name
target_folder.upload_file("File_content_new.xlsx", workbook).execute_query()
st.write("**File uploaded successfully!**")
# Third page
elif Pages == 'Upload a new file' :
# Section: Upload a file
st.write("")
st.write("")
st.write("")
File_new = st.file_uploader("Upload a file from your computer")
if File_new :
File_new_df = pd.read_excel(File_new)
st.write("")
st.write("This is the file your uploaded:")
st.write(File_new_df)
# Choose some filters
c1, c2 = st.columns(2)
with c1 :
Nation_filter = st.selectbox('Filter: Nation',
File_new_df["Nation"],
key = 1)
with c2:
City_filter = st.selectbox('Filter: City',
File_new_df["City"],
key = 2)
# Show filtered data
File_new_df_filtered = File_new_df[(File_new_df["Nation"] == Nation_filter) & (File_new_df["City"] == City_filter)]
st.write("This is the filtered table:")
st.write(File_new_df_filtered)
# Save data into a file when the button is clicked
if st.button("Save data in SharePoint folder") :
# Create "writer" object
output = BytesIO()
writer = pd.ExcelWriter(output, engine = 'xlsxwriter')
# Convert data into Excel and choose a sheet name
File_new_df_filtered.to_excel(writer,
index = False,
sheet_name = 'New filtered data')
writer.save()
output.seek(0)
workbook = output.read()
# Upload the file in folder, choosing a name
target_folder.upload_file("Filtered_data.xlsx", workbook).execute_query()
st.write("**New file uploaded successfully!**")
# Handle the error in the authentication section
except :
col1, col2, col3 = st.columns(3)
with col2:
st.write("**Authentication error: reload the page**")At this point, you can update the app you published previously with the new code lines. And that’s it! You have a very simple app that you can use to interact with a SharePoint folder as you like! For comments and suggestions, do not hesitate to contact me :D
