Importing from Excel

[troberts]

I need to import from an excel spreadsheet. Can you advise?

[daviesian]

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!

[meredydd]

If you want more sample code, here’s an example I posted the other week, which uses the Uplink to import an Excel sheet into a data table:

Importing existing data into Data Table

Hi Chris, Anvil can return a lot from a server function, but custom classes like Pandas data frame rows will fox it. If you want to return data like this, I’d suggest turning it into a dict (Pandas’s to_dict() method will help you here). In fact, if you’re using the uplink, you don’t even need to return this data from a server function - you can add it to the data table directly! You can call add_row() on a data table from Uplink code, same as server code. Something like this, perhaps: impor…

[ts_coder]

Could you point me in the direction of where I could more information on downloading the excel file create by Pandas? I am importing an excel file into pandas, cleaning the data, writing back to excel, and then want to return that excel file to the user as a download. I’ve been able to get everything to write properly to the excel file properly, but have hit a dead end when it comes to returning the temporary excel file to the user or uploading it to a datatable to be returned later.

Thanks for any help you can provide.