Django How to Export Data to XLS File
install the xlwt module
pip install xlwt
#views.py
from django.shortcuts import render
from django.http import HttpResponse
from django.contrib.auth.models import User
import xlwt
from myapp.models import StudentForm #models.py
def index(request):
return render(request,"index.html")
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
def export_student_xls(request):
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename="student.xls"'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Student')
# Sheet header, first row
row_num = 0
font_style = xlwt.XFStyle()
font_style.font.bold = True
columns = ['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 = StudentForm.objects.all().values_list('firstname', 'lastname', '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
from django.contrib import admin
from django.urls import path
from myapp import views
urlpatterns = [
path('admin/', admin.site.urls),
path('index/', views.index),
path('export_users_xls/', views.export_users_xls),
path('export_student_xls/', views.export_student_xls),
]
#models.py
from django.db import models
# Create your models here.
class StudentForm(models.Model):
firstname = models.CharField("Enter first name", max_length=50)
lastname = models.CharField("Enter last name", max_length = 50)
email = models.EmailField("Enter Email")
bio = models.TextField(blank=True)
file = models.FileField() # for creating file input
class Meta:
db_table = "student"
#templates/index.html
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Django How to Export Data to XLS File</title>
</head>
<body>
<p><h2>Django How to Export Data to XLS File</h2></p>
<p><h4><a href="/export_users_xls">Export all users</a></h4></p>
<p><h4><a href="/export_student_xls">Export all Students</a></h4></p>
</body>
</html>
