10

I have a Django app that will be placed in a Docker container.

The app prepares data in Dataframe format. I would like to allow the user to download the data to his/her local drive as excel file.

I have used df.to_excel in the past, but this won't work in this case.

Please advise best way to do this.

jscriptor
  • 775
  • 1
  • 11
  • 26

2 Answers2

19

As of , you can let Django write to a BytesIO directly, like:

from django.http import HttpResponse
from io import BytesIO

def some_view(request):
    with BytesIO() as b:
        # Use the StringIO object as the filehandle.
        writer = pd.ExcelWriter(b, engine='xlsxwriter')
        df.to_excel(writer, sheet_name='Sheet1')
        writer.save()
        # Set up the Http response.
        filename = 'django_simple.xlsx'
        response = HttpResponse(
            b.getvalue(),
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response['Content-Disposition'] = 'attachment; filename=%s' % filename
        return response

You might need to install an Excel writer module (like xlsxwriter, or openpyxl).

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • Hi. I am using your function as is, except for passing in df. I also added a a line : ldm_df.to_excel("D:\\Users\\myfolder\\test.xlsx") right before your return to validate I can write excel. I am currently running on local host. I was expecting return HttpResponse to shown download to temp. But nothing is being downloaded. Please advise what I am missing. Thank you for your help. – jscriptor Jun 01 '19 at 15:57
  • @jscriptor: you should *not* write it to a file. The `writer` here is an object in memory, likely the view just crashes on that line. – Willem Van Onsem Jun 01 '19 at 15:59
  • 1
    Hi. I am using ajax to make the call to my version of some_view. --- $.ajax({ headers: {"X-CSRFToken": csrftoken}, url: 'carmaapp/export_data', data: { 'user_selections': adict, }, type: "POST", success: function (data) { alert(data) }, error: function (errMsg) { alert(errMsg); } }); ---- how do I push the data to excel file in user home folder? – jscriptor Jun 01 '19 at 19:10
  • @jscriptor: you can not "push" a file to a directory, since HTML/JavaScript have no access to the user's disk(s), or not in general, there are a very limited number of options the browser monitors. You can however let the user confirm saving it, see here: https://stackoverflow.com/a/9970672/67579 – Willem Van Onsem Jun 01 '19 at 19:16
  • Thank you. I got the download working but it seems to work for small size download only .. I get [Chrome download(1) Failed - Network Error] message. It seems I can't download > 2MB. tested 1.187 KB and it worked). I added DATA(FILE)_UPLOAD_MAX_MEMORY_SIZE = 5242880 to settings.py, but this did not help. I am running on Chrome local host 8000. Any idea how to increase download size? Thank You – jscriptor Jun 02 '19 at 22:31
  • @jscriptor: what if you use a `StreamingHttpResponse` instead? – Willem Van Onsem Jun 03 '19 at 09:31
  • Corrupted file downloaded ----- with BytesIO() as b: writer = pd.ExcelWriter(b, engine='xlsxwriter') my_df.to_excel(writer, sheet_name='Sheet1') writer.save() writer.close() content_type='application/vnd.ms-excel') response = StreamingHttpResponse(b.getvalue(), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=%s' % 'Somefilename.xlsx' return response – jscriptor Jun 03 '19 at 17:03
  • The error I get when I try to open the file is: Excel cannot open the file 'somefile.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file – jscriptor Jun 03 '19 at 17:06
  • No resolved yet .... I only get corrupted file when I try to open the file – jscriptor Jun 03 '19 at 17:07
  • @jscriptor: what if you `print(len(b.getvalue()))` before returning the response, what size do you get? – Willem Van Onsem Jun 03 '19 at 17:25
  • it prints zero (0). I am surprised because the df has records ... can see head and tail – jscriptor Jun 03 '19 at 17:34
  • @jscriptor: but this really does not seem to make much sense, since at least some data (35KB) is transferred, we expect at least that it shows *some* data. You have printed it *after* you `writer.save()`d it? – Willem Van Onsem Jun 03 '19 at 17:36
  • @jscriptor: what if you `b.flush()` immediately *after* the `writer.save()`? – Willem Van Onsem Jun 03 '19 at 17:39
  • len printed is 29351 .... file downloaded is 74K per file explorer. When I try to open, I get the message. – jscriptor Jun 03 '19 at 17:50
  • with BytesIO() as b: writer = pd.ExcelWriter(b, engine='xlsxwriter') my_df.to_excel(writer, sheet_name='Sheet1') writer.save() b.flush() content_type='application/vnd.ms-excel') print (len(b.getvalue())) response = StreamingHttpResponse(b.getvalue(), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=%s' % 'my.xlsx' return response – jscriptor Jun 03 '19 at 17:52
  • what is `content_type='application/vnd.ms-excel')` doing here? Especially the `)` is "strange". – Willem Van Onsem Jun 03 '19 at 17:53
  • Sorry ... I am using the code you pasted in first response .... should I have taken this out? – jscriptor Jun 03 '19 at 17:55
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194391/discussion-between-willem-van-onsem-and-jscriptor). – Willem Van Onsem Jun 03 '19 at 17:56
  • firewall will block me – jscriptor Jun 03 '19 at 17:59
  • @jscriptor: well the closed bracket is rather odd, since that would mean a syntax error. – Willem Van Onsem Jun 03 '19 at 18:02
  • Not able to use, but got me thinking. – jscriptor Jun 03 '19 at 18:23
2

I think it can be even simpler and more concise these days. You can just pass the http response directly to the Excel writer. The following works for me:

from django.http import HttpResponse
import pandas as pd

# df = CREATE YOUR OWN DATAFRAME

response = HttpResponse(content_type='application/xlsx')
response['Content-Disposition'] = f'attachment; filename="FILENAME.xlsx"'
with pd.ExcelWriter(response) as writer:
    df.to_excel(writer, sheet_name='SHEET NAME')

return response
Dre
  • 1,985
  • 16
  • 13