Download excel file

What I’m trying to do:
I’m trying to download data in a excel file. Currently i have data in datatable and also i get data from an api. I’m mergin the data and creating a dictionary. The only way i could download the data is in a .csv format (which is very ugly).

What I’ve tried and what’s not working:
I tried to download the csv file as a excel file but i broke the entire file.

Is there any way to handle excel files in the same way as csv files?
Thank you all

hi @javier and welcome to the forum,

If you’re trying to do complex data table manipulation in python then it might be a good time to look at pandas.

(Available on the server in full python3 mode - or via uplink)

It might be just what you need since it can read/write excel and csv formats.

If you want to output an excel file you may also want to explore XlsxWriter

4 Likes

Thanks for your answer @stucork
I’m doing my data manipulation with pandas. But the problem is that i need to download that information in a excel file, in the client side. Currently i can download a .csv file as a BlobMedia file. First a get all the data, then transform to a .csv file and then BlobMedia file. Because i can’t use a excel file as a BlobMedia object, i can’t download it.
Code example:

In server code

@anvil.server.callable
def exportToExcel(data, columns):
df1 = pd.DataFrame(data, columns=columns)
csv_media = anvil.BlobMedia(‘text/plain’, df_as_csv.encode(), name=f’Report {today}.csv’)

Then i return that csv_media to the client code and download it using:
csv_media_file= anvil.server.call(‘exportToExcel’) # exportToExcel is my server code function
anvil.media.download(csv_media_file)

I want to do the same BUT with a excel format. Where each value has it’s own column and not all values are in the same column

How about something like this:

import io
import pandas as pd


@anvil.server.callable
def export_to_excel(data, columns):
    df = pd.DataFrame(data, columns=columns)
    content = io.BytesIO()
    df.to_excel(content, index=False)
    content.seek(0, 0)
    return BlobMedia(content=content.read(), content_type="application/vnd.ms-excel")

and on the client side, you might have this in a click hander for a download button:

def download_button_click(self, **event_args):
    result = anvil.server.call("export_to_excel", <pass your data and columns here>)
    anvil.media.download(result)
9 Likes

Thank you for your answer @owen.campbell !!
That code work!
Thank you again.

1 Like

Hey Owen,

I stumbled upon this answer when I was search for pretty much the same question. Is content = io.BytesIO() supposed to include the df dataframe created on the line before?

I pretty much have the same scenario outlined in the OP, where I’m building an excel file on the server end to return to the user. I’m able to create the dataframe, I just can’t seem to get the file written to excel and passed back to the front-end, so this seems like it’s very relevant to that without having to create a new thread.

Thanks in advance!

No, the code is correct. That line instantiates a BytesIO object and the following line writes the data into it.

2 Likes