Outputting and downloading an excel with multiple sheets using pandas

Anvil Q&A
1

Hey everyone!

I have code that reads in an excel file and processes it using Pandas. I want my app to output a new excel file containing two sheets with one sheet referencing the other. This excel format comes from higher up than me so it can’t be changed.

My code for reading in an excel file and processing it works in my app, the only part I’m having trouble with is getting my app to download the excel file.

I’ve seen this post which uses a BytesIO object as the writer with pandas.to_excel, my only problem is that I have 2 DataFrames that are to be written to two different sheets in the same excel workbook. From my understanding in order to write to multiple sheets within the same workbook using pandas you should utilize an ExcelWriter object to enable sheet specification and because of this I don’t see a way to use a BytesIO object as the writer for a multi-sheet write.

I’ve also found this post outlining the same solution.

Here’s my attempt to use the BytesIO solution in my server module:

def output(dict1, summary, date):
 
    file_name = r"file name -{}.xlsx".format(date)
  
    dict1_sheet = pd.DataFrame(data=dict1)
    content_1 = io.BytesIO()
    dict1_sheet.to_excel(content_1, sheet_name="dict1", engine='xlsxwriter', index=False)
    content_1.seek(0, 0)
    dict1_excel = anvil.BlobMedia(content=content_1.read(), content_type="application/vnd.ms-excel")

    dict1_summary = pd.DataFrame(data=summary)
    content_2 = io.BytesIO()
    dict1_summary.to_excel(content_2, sheet_name="dict1 summary", engine='xlsxwriter', index=False, startrow=4)
    content_2.seek(0, 0)
    summary_excel = anvil.BlobMedia(content=content_2.read(), content_type="application/vnd.ms-excel")
    
    return dict1_excel, summary_excel

This allows me to download two seperate excel files in my client as follows:

result = anvil.server.call('main', self.file, self.date) 
      anvil.media.download(result[0])
      anvil.media.download(result[1])

The problem with this is summary_excel references dict1_excel and thus is useless output in this way.

I tried is combining the bytes objects so that I can download them both to the same Blobmedia object but I had no success with this.

I also tried using the same ByteIO object for both to_excel calls:

    a. changing the .seek whence=SEEK_CUR (corrupted output all to one sheet) 
    b. changing whence=SEEK_END (empty one sheet workbook)
    c. I didn't touch the offset as I didn't think it was needed.

If anyone could provide me any guidance at all I would greatly appreciate it.
Thank you!

2

Create an ExcelWriter instance with a BytesIO instance as its first argument.

3 Likes
3

To those interested,

I figured I would post a minimal example of the code that worked for me for this problem.

Following @owen.campbell answer by using a BytesIO instance as the path parameter (first argument) when creating an ExcelWriter object, the code is as follows:

import pandas as pd
import io

# IN SERVER
def output(data1, data2):
  
    sheet1 = pd.DataFrame(data=data1)
    sheet2 = pd.DataFrame(data=data2)
  
    content = io.BytesIO()
  
    # NOTE: I have not tried this solution with the append mode yet so not sure if it will cause problems. 
    writer = pd.ExcelWriter(content, engine='xlsxwriter', mode='w')
   
    sheet1.to_excel(writer, sheet_name="Sheet 1")
    sheet2.to_excel(writer, sheet_name="Sheet 2")
  
    writer.close()
    content.seek(0, 0)
    
    excel_file = anvil.BlobMedia(content=content.read(), content_type="application/vnd.ms-excel")
    
    return excel_file

For the instantiation of the ExcelWriter object and when performing DataFrame.to_excel() feel free to add/change the parameters as needed.

Hope you find this useful.
Best!

2 Likes