How to export excel file in django

Question

I need help with exporting data using a template. I installed django-import-export and added it to admin panel, now I can only export data from the admin panel. I want to know how can i export excel file using template.

Answer 1

This should get you started:

import StringIO
import xlsxwriter
from django.http import HttpResponse

def export_page(request):
    # create our spreadsheet.  I will create it in memory with a StringIO
    output = StringIO.StringIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet()
    worksheet.write('A1', 'Some Data')
    workbook.close()

    # create a response
    response = HttpResponse(content_type='application/vnd.ms-excel')

    # tell the browser what the file is named
    response['Content-Disposition'] = 'attachment;filename="some_file_name.xlsx"'

    # put the spreadsheet data into the response
    response.write(output.getvalue())

    # return the response
    return response

Answer 2

I tried the same with newer version of Django and after trial and error found this worked.

import io
import xlsxwriter

def excelreport(request):

  buffer = io.BytesIO()
  workbook = xlsxwriter.Workbook(buffer)
  worksheet = workbook.add_worksheet()
  worksheet.write('A1', 'Some Data')
  workbook.close()
  buffer.seek(0)

  return FileResponse(buffer, as_attachment=True, filename='report.xlsx')

Answer 3

You can alos use xlwt if you really need to export to a .xls file. You will be able to add formating as bold font, font size, define column size, etc.

$ pip install xlwt

import xlwt

from django.http import HttpResponse
from django.contrib.auth.models import User

def export_users_xls(request):
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename="users.xls"'

    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('Users')

    # Sheet header, first row
    row_num = 0

    font_style = xlwt.XFStyle()
    font_style.font.bold = True

    columns = ['Username', 'First name', 'Last name', 'Email address', ]

    for col_num in range(len(columns)):
        ws.write(row_num, col_num, columns[col_num], font_style)

    # Sheet body, remaining rows
    font_style = xlwt.XFStyle()

    rows = User.objects.all().values_list('username', 'first_name', 'last_name', 'email')
    for row in rows:
        row_num += 1
        for col_num in range(len(row)):
            ws.write(row_num, col_num, row[col_num], font_style)

    wb.save(response)
    return response