Export data to excel is a common requirement on many web applications. Python makes everything easier. But, nevertheless, it is the kind of task I need to look for references whenever I have to implement.
I will give you two options in this tutorial: (1) export data to a .csv file using Python’s csv module; (2) export data to a .xls file using a third-party module named xlwt.
For both cases, consider we are exporting django.contrib.auth.models.User data.
Export Data to CSV File
Easiest way. No need to install dependencies, which is a great thing. Use it if you do not need any fancy formating.
views.py
import csv
from django.http import HttpResponse
from django.contrib.auth.models import User
def export_users_csv(request):
response = HttpResponse(content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename="users.csv"'
writer = csv.writer(response)
writer.writerow(['Username', 'First name', 'Last name', 'Email address'])
users = User.objects.all().values_list('username', 'first_name', 'last_name', 'email')
for user in users:
writer.writerow(user)
return responseurls.py
import views
urlpatterns = [
...
url(r'^export/csv/$', views.export_users_csv, name='export_users_csv'),
]template.html
<a href="{% url 'export_users_csv' %}">Export all users</a>In the example above I used the values_list in the QuerySet for two reasons: First to query only the fields I needed
to export, and second because it will return the data in a tuple instead of model instances. The writerow method
expects a list/tuple.
Another way to do it would be:
writer.writerow([user.username, user.first_name, user.last_name, user.email, ])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 xlwtviews.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 responseurls.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.
Django Tips #22 Designing Better Models
A Complete Beginner's Guide to Django - Part 2
Django Tips #17 Using QuerySet Latest & Earliest Methods
How to Extend Django User Model
How to Setup a SSL Certificate on Nginx for a Django Application
How to Deploy a Django Application to Digital Ocean