Django- Create a downloadable excel file using pd.read_html & df.to_excel

Question

I currently have a python script that uses pd.read_html to pull data from a site. I then use df.to_excel which sets 'xlsxwriter' as the engine.

I am trying to find a way to incorporate this into a django webapp. However, I am lost as how to do this or even know if possible.

I've seen a few ways to create downloadable excel files in django but none that have pandas as the driving force of creating the data in the excel file. My python code for creating the excel file without django is somewhat long so not sure what to show. Below is part of my pandas code:

        xlWriter = pd.ExcelWriter(excel_sheet2, engine='xlsxwriter')
        workbook = xlWriter.book

        money_fmt = workbook.add_format({'num_format': 42, 'align': 'center', 'text_wrap': True})
        text_fmt = workbook.add_format({'bold': True, 'align': 'center', 'text_wrap': True})



        for i, df in enumerate(dfs):
            for col in df.columns[1:]:
                df.loc[df[col] == '-', col] = 0 
                df[col] = df[col].astype(float)

            df.to_excel(xlWriter, sheet_name='Sheet{}'.format(i))

Below is my templates.html code

{% block content %}
<form type="get" action="." style="margin: 0">
 <input id="search_box" type="text" name="search_box" placeholder="Enter URL..." >
 <button id="search_submit" type="submit" >Submit</button>
</form>
{% endblock %}

And this is the beginning of my views.py

def financials(request):
    return render(request, 'personal/financials.html')

    if request.method == 'GET':
        search_query = request.GET.get('search_box', None)
        url = search_query

        dfs = pd.read_html(url, flavor='html5lib')

zunayed ali · Accepted Answer · 2017-02-08 20:47:28Z

1

Why don't you just call your pandas functions within the Django view and save the file to /tmp. Once you have the file you can just send it and tell the browser to treat it as a file in your response.

You can then just return the file

from django.http import HttpResponse

def my_view(request):
    # your pandas code here to grab the data
    response = HttpResponse(my_data, content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename="foo.xls"'
    return response

https://docs.djangoproject.com/en/dev/ref/request-response/#telling-the-browser-to-treat-the-response-as-a-file-attachment

answered Feb 8, 2017 at 20:47

zunayed ali

464 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

gluc7 Over a year ago

I think I tried that and it didn't work. Maybe it was a mistake on my part. I will try the code again. Just to be clear though, the 'my_data' that your referring to would be the name of the excel file that I create when using pd.ExcelWriter()? I have edited my original question to include part of my pandas code with the ExcelWriter.

gluc7 Over a year ago

I just got a change to try it again and nothing happens. I get no errors either. It might be a mistake somewhere else in the code. The download is supposed to be triggered based upon a submit button my template.html. Perhaps I am not passing that correctly to views.py. I will add this to my question to as well.

zunayed ali Over a year ago

In you financials view you return immediately at the start of the function. You'll never actually run any of your pandas code.

gluc7 Over a year ago

I swapped the order and it still didn't work. It turns out there was a spelling error in the response line. The download works now. Unfortunately, it is not pulling my already formatted excel sheet though. Thanks for all your help!

gluc7 · Accepted Answer · 2017-02-14 02:28:43Z

0

I just wanted to add what I finally came up with that got everything working. Instead of including my data within the HttpResponse, I included the response within the wb.save() command. This got everything working correctly including my formatting of the spreadsheet prior to downloading.

wb = load_workbook(excel_sheet2)

response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename= "Data.xlsx"'

wb.save(response)

return response

answered Feb 14, 2017 at 2:28

gluc7

5851 gold badge7 silver badges20 bronze badges

Collectives™ on Stack Overflow

Django- Create a downloadable excel file using pd.read_html & df.to_excel

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related