Django HttpResponse Excel

Question

I am trying to generate an excel file on Django site so I searched for it and look at this example. I write simply a function that writes what I need into an Excel file;

def create_excel(personal_information):

    output = StringIO.StringIO()
    book = xlsxwriter.Workbook(output)

    sheet = book.add_worksheet()

    if personal_information['name']:
        sheet.write(1, 1,  personal_information['name'], text_format)

    book.close() 
    output.seek(0)

    return output

In my view.py;

def export(request):
    personal_information = json.loads(request.POST.get('personal_data'))

    output = create_excel(personal_information)
    response = HttpResponse(output.read(), content_type="application/ms-excel")
    response['Content-Disposition'] = 'attachment; filename=Excel.xls'

    return response

However, that gave "None". Do you have any idea to solve my problem?

Thank you.

What happens if you `print output` before rendering the response? Is the output being formed properly? Also, is your `return response` statement indented properly? It doesn't appear to be in the question... — rnevius, Mar 14 '16 at 15:13
@rnevius Yes I cannot copy and paste the code properly but in my original code, there is no indentation problem. When I print the output before return it, it gives "". And when I debug it, I can see the content of response that has some unknown figures. — Mehmet Kagan Kayaalp, Mar 14 '16 at 15:23
Also I need to say that I am trying to get the excel file from a button in an pop-up window. I mean maybe I need to handle it ? — Mehmet Kagan Kayaalp, Mar 14 '16 at 15:33
@waterkinq are you sure the `return response` is properly indented? What response do you see returned in the browser? — alecxe, Mar 14 '16 at 15:36
:) Here it is the return response when I look at from Google Chrome Network, I see such a response; "PKÖnHoÇT°[mxl/worksheets/sheet1.xmlQo0Çß÷),¿/p$@ªVU´=L¶v{vÀTÀÈvîÛÏ8 ....." — Mehmet Kagan Kayaalp, Mar 14 '16 at 15:39

Gilles Criton · Answer 1 · 2018-02-07T09:14:18.893

Try this: in your function create_excel:

output      = io.BytesIO()
workbook    = xlsxwriter.Workbook(output)
      .... your code .....
           at the end of your function
 # close workbook
workbook.close()
xlsx_data = output.getvalue()
return xlsx_data

in your view:

if request.method == 'POST':
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; 
filename=your_template_name.xlsx'
    xlsx_data = create_excel()
    response.write(xlsx_data)

score 2 · Answer 2 · answered May 20 '20 at 15:43

I've a few solutions to this. I have a demo project where I do this under the views.py file. I use openpyxl but you can also use other libraries like xlwings if you need to keep macros (.xlsm).

Here is my code snippet:

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

    # create workbook
    wb = Workbook()
    sheet = wb.active

    # stylize header row
    # 'id','title', 'quantity','pub_date'

    c1 = sheet.cell(row = 1, column = 1) 
    c1.value = "id"
    c1.font = Font(bold=True)

    c2 = sheet.cell(row= 1 , column = 2) 
    c2.value = "title"
    c2.font = Font(bold=True)

    c3 = sheet.cell(row= 1 , column = 3) 
    c3.value = "quantity"
    c3.font = Font(bold=True)

    c4 = sheet.cell(row= 1 , column = 4) 
    c4.value = "pub_date"
    c4.font = Font(bold=True)

    # export data to Excel
    rows = models.Data.objects.all().values_list('id','category', 'quantity','pub_date',)
    for row_num, row in enumerate(rows, 1):
        # row is just a tuple
        for col_num, value in enumerate(row):
            c5 = sheet.cell(row=row_num+1, column=col_num+1) 
            c5.value = value

    wb.save(response)

    return response

Django HttpResponse Excel

2 Answers2