The pandas package is great for this. You’ll probably want the user to upload their Excel file using a FileLoader component, then you should pass the file to a Server Module for processing with pandas.
In the Server Module, do something like:
# Import pandas
import pandas as pd
# Pass the selected file to this server function
@anvil.server.callable
def load_excel(file):
# Generate a temporary random local file name
tmp_name = "/tmp/%s" % "".join([random.choice("0123456789abcdef") for x in range(32)])
# Read the uploaded Excel file into the temporary local file
with open(tmp_name, 'wb') as f:
f.write(file.get_bytes())
# Load spreadsheet
xl = pd.ExcelFile(tmp_name)
# Print the sheet names
print(xl.sheet_names)
# Load a sheet into a DataFrame by name: df1
df1 = xl.parse('Sheet1')
# etc...
For what it’s worth, writing to Excel from a pandas DataFrame is also very easy:
# Specify a writer
writer = pd.ExcelWriter('/tmp/example.xlsx', engine='xlsxwriter')
# Write your DataFrame to the file
df1.to_excel(writer, 'Sheet1')
# Save the result
writer.save()
# Now return the file data, or send it somewhere, as you wish...
Note that in order to use the pandas library, you will need to choose “Full Python 2” or “Full Python 3” as the environment for your server modules. This requires being on one of our paid plans.
Hope that helps!