Django How to Export Data to XLS File
install the xlwt module
pip install xlwt
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | #views.pyfrom django.shortcuts import render from django.http import HttpResponse from django.contrib.auth.models import Userimport xlwtfrom myapp.models import StudentForm #models.pydef 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 responsedef 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 |
1 2 3 4 5 6 7 8 9 10 11 | #urls.pyfrom 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), ] |
1 2 3 4 5 6 7 8 9 10 11 12 13 | #models.pyfrom 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" |
1 2 3 4 5 6 7 8 9 10 11 12 13 | #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> |
