5

views.py

def export_to_excel(request):

    lists = MyModel.objects.all()

    # your excel html format
    template_name = "sample_excel_format.html"

    response = render_to_response(template_name, {'lists': lists})

    # this is the output file
    filename = "model.csv"

    response['Content-Disposition'] = 'attachment; filename='+filename
    response['Content-Type'] = 'application/vnd.ms-excel; charset=utf-16'
    return response

urls.py

from django.conf.urls.defaults import *

urlpatterns = patterns('app_name.views',
   url(r'^export/$', 'export_to_excel', name='export_to_excel'),  
)
  1. Last, in your page create a button or link that will point in exporting.

page.html

<a href="{% url app_name:export_to_excel %}">Export</a>

Nothing getting file option for download and not giving any error but i can see all result in log its working fine.

Nik
  • 420
  • 4
  • 16
Ashish Kumar Saxena
  • 4,400
  • 8
  • 27
  • 48
  • Can you provied info on what tutorial/guide are you following, and what is rendered in html/what you expect. – alko Nov 18 '13 at 05:55
  • This seems to be based on the nasty trick of feeding excel with an HTML table. – Paulo Scardine Nov 18 '13 at 05:58
  • in html render like that ... {% for report in lists %} {{ report }} {% endfor %} {% for res in lists_datas %} {% for ren in lists_data %} {{ ren }} {{ res.ren }} {% endfor %} {% endfor %} simply return data from query object – Ashish Kumar Saxena Nov 18 '13 at 06:02
  • 1
    http://catherinetenajeros.blogspot.com/2013/06/export-data-in-excel-format.html – Ashish Kumar Saxena Nov 18 '13 at 06:03
  • Althrough a little abandoned, this package can do the job https://github.com/cuker/django-reportengine – alko Nov 18 '13 at 06:11

5 Answers5

7

I think that the solution to export excel files is :

   if 'excel' in request.POST:
        response = HttpResponse(content_type='application/vnd.ms-excel')
        response['Content-Disposition'] = 'attachment; filename=Report.xlsx'
        xlsx_data = WriteToExcel(weather_period, town)
        response.write(xlsx_data)
        return response

In this example the library used for exporting is xlsxWriter. Here is a very complete and practical solution for this, and many others: http://assist-software.net/blog/how-export-excel-files-python-django-application .

Alexandra
  • 121
  • 2
  • 1
4

This seems to be based on the practice of tricking excel into opening an HTML table by changing the file name and MIME type. In order to make this work, the HTML file has to assemble an HTML table, and this is likely to trigger a warning that the real content of the file is different from the declared content.

IMHO it is a crude hack and should be avoided. Instead you can create a real excel file using the xlwt module, or you can create a real CSV file using the csv module.

[update]

After looking the blog post you refered, I see it is recommending another bad practice: using anything but the csv module to produce CSV files is dangerous because if the data contains the delimiter character, quotes or line breaks, you may end up with a bad CSV. The csv module will take care of all corner cases and produce a proper formatted output.

I've seen people use a Django template naming the file "something.xls" and using HTML tables instead of the CSV format, but this has some corner cases as well.

Paulo Scardine
  • 73,447
  • 11
  • 124
  • 153
4

It seems that you are trying to generate an excel workbook with HTML content. I don't know if Excel (or LibreOffice) is able to open such file but I think it is not the right approach.

You should fist generate a excel file : you can use csv, xlwt for xls and openpyxl for xlsx

The content of the file can be passed to the HttpResponse

for example, if you work with xlwt:

import xlwt
wb = xlwt.Workbook()

#use xlwt to fill the workbook
#
#ws = wb.add_sheet("sheet")
#ws.write(0, 0, "something")

response = HttpResponse(mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=the-file.xls'
wb.save(response)
return response

You can also look at

I hope it helps

luc
  • 41,928
  • 25
  • 127
  • 172
4

In addition to the options shown in the other answers you can also use XlsxWriter to create Excel files.

See this example.

ZhangChn
  • 3,154
  • 21
  • 41
jmcnamara
  • 38,196
  • 6
  • 90
  • 108
1

Export Data to XLS File

Use it 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.

First of all, install the xlwt module. The easiest way is to use pip.

pip install xlwt

views.py

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

urls.py

import views

urlpatterns = [
    ...
    url(r'^export/xls/$', views.export_users_xls, name='export_users_xls'),
]

template.html

<a href="{% url 'export_users_xls' %}">Export all users</a>

Learn more about the xlwt module reading its official documentation. https://simpleisbetterthancomplex.com/tutorial/2016/07/29/how-to-export-to-excel.html

TheLastCodeBender
  • 538
  • 5
  • 10