12

I'm making a small website using Flask, with a SQLite database. One of the things I want to do is take some data (from the database) and export it as an Excel file. I want to offer an option of downloading that Excel file. One option to do this is to use Pandas to write to an Excel file which would be stored on the web server, and to use Flask's send_file to offer the download option.

However, is it possible to provide a downloadable Excel file without storing the file "locally" on the server? For example on AWS S3. I want to have predictable storage size on the web server. (And just see if it's possible, in any case.)

One option might be to write to a file "locally", then send it to AWS, then delete it from the server. Ideally I'd rather capture the file stream directly and then send that to S3, but I don't think that's possible, since to_excel only takes a file path (or an ExcelWriter object, but that takes a file path).

Vegard Stikbakke
  • 749
  • 1
  • 7
  • 21

3 Answers3

28

To add to balderman's answer, the complete code for getting it to S3 would be

import io
import pandas as pd
import boto3

# ...

# make data frame 'df'

with io.BytesIO() as output:
  with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
    df.to_excel(writer)
  data = output.getvalue()

s3 = boto3.resource('s3')
s3.Bucket('my-bucket').put_object(Key='data.xlsx', Body=data)

See also the XlsxWriter documentation.

Vegard Stikbakke
  • 749
  • 1
  • 7
  • 21
2

to anyone who might want to try and write multiple sheets to a single excel workbook using boto3 in 2023, I got a working result by expanding upon @Vegard's answer:

import pandas as pd
import io
import boto3

s3 = boto3.client("s3")
bucket = name_of_bucket
key = path_to_save_directory_from_bucket
file = name_of_save_file


output = io.BytesIO()
writer = pd.ExcelWriter(output, engine=your_engine)

for df in list_of_dfs:
    # do things ...
    df.to_excel(writer,
                sheet_name=name_your_sheet)

writer.save()
output.seek(0)
s3.put_object(Bucket=bucket, Key=key + file, Body=output.getvalue())
writer.close()
eMeS
  • 21
  • 3
1

Taken from here: Write to StringIO object using Pandas Excelwriter?

You can dump the 'output' to S3

# Note, Python 2 example. For Python 3 use: output = io.BytesIO().
output = StringIO.StringIO()

# Use the StringIO object as the filehandle.
writer = pd.ExcelWriter(output, engine='xlsxwriter')
balderman
  • 22,927
  • 7
  • 34
  • 52