Upload an excel to a datatable media object column

Anvil Q&A
#1

Im trying pull down a template (.xlsx) stored in media object column in a datatable then write a dataframe to that template then save it back into the datatable as an .xlsx and ultimately download the file on the client side. Below is what I have so far. The first part of the code seems to work, but dont know how to save the edited file and add it back to the datatable. Appreciate the help.
What I’ve tried and what’s not working:

Code Sample:

# this is a formatted code snippet.
# paste your code between ``` 
```     for row in app_tables.templates.search():
      content = row['Templates']

      file = content.get_bytes()
      bytestream = io.BytesIO(file)
      bytestream.seek(0)
      book = load_workbook(bytestream)
      print(book)
      # print(workbook.sheetnames)
 
      writer = pd.ExcelWriter(bytestream, engine='openpyxl')
      writer.book = book
      writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
      
      report_data.to_excel(writer, 'Adult_Protocol_Data')
      # report_filename = Client + "." + Year + "." + Quarter + ".xlsx"
      writer.save()
      writer.close()
      app_tables.templates.add_row(Templates=?)


**Clone link:**
*share a copy of your app*
#2

It looks like you have the contents of the file stream in bytes already so you are pretty close.

You need to construct a new media object, setting the filename, the MIME type (to excel) and the contents to the results of reading the bytestream object.

be sure to do this again first:

Then the contents of the media object should be the result of bytestream.read()

The media object can then be put into the media column of the data table, where you have Templates=?

Computer Science ProTip: Most things that deal with reading and writing or I/O are abstractions from the original way computers stored and processed information. Either by decks of punched cards, or ribbons, or eventually magnetic tape. This means its just a stream of information with something that signifies the beginning, the end, and a “reader” that traverses between them (or jumps/jogs around a.k.a. ‘seeking’).

1 Like
#3

Hi Ian,

Thank you so much for the response. Do you think you could provide me a sample code that will do what I need. I am a beginner and am not sure where to go after writing to the sheet.

#4

Something like: (I did not test this, also you have to fix your formatting)

import anvil.media

for row in app_tables.templates.search():
    content = row["Templates"]

    file = content.get_bytes()
    bytestream = io.BytesIO(file)
    bytestream.seek(0)
    book = load_workbook(bytestream)
    print(book)
    # print(workbook.sheetnames)

    writer = pd.ExcelWriter(bytestream, engine="openpyxl")
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

    report_data.to_excel(writer, "Adult_Protocol_Data")
    # report_filename = Client + "." + Year + "." + Quarter + ".xlsx"
    writer.save()
    writer.close()

    bytestream.seek(0)
    media_object = anvil.BlobMedia(
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        bytestream.read(),
        name="excel_name_here.xlsx"
    )

    app_tables.templates.add_row(Templates=media_object)

maybe if what you want is replacing the template in that same row use:

row['Templates'] = media_object

#5

Wow Ian your a life saver. Thank you so much. Unfortunately the file gets corrupted when I try to open it. Any other ideas?

#6

I am unsure how pandas uses file handlers to create excel sheets, you might have to google that part.

I have used openpyxl directly, but not through pandas.

#7

Yea I dont need to use openpyxl if you know of an alternative to accomplish what I am trying to accomplish. Im just pulling a excel file stored in a DataTable then adding a Dataframe to the excel then reuploading it. Do you have a suggestion on how to do this another way?