In this tutorial, we will learn how to Export Data from the Database into Excel using Django. In this Django application, I used the pandas library.
Let’s start
Install pandas library
First, create a Django project, then create models
Django Export Data From Database into Excel
models.py
from django.db import models
from Django import forms
class tbl_Employee(models.Model):
# Id = models.IntegerField()
Empcode = models.CharField(max_length=10, default='')
firstName = models.CharField(max_length=150,null=True)
middleName = models.CharField(max_length=100,null=True)
lastName = models.CharField(max_length=100,null=True)
email = models.CharField(max_length=30,null=True)
phoneNo = models.CharField(max_length=12, default='',null=True)
address = models.CharField(max_length=500, default='',null=True)
exprience = models.CharField(max_length=50, default='',null=True)
DOB = models.DateField(null=True, blank=True)
gender = models.CharField(max_length=10, default='',null=True)
qualification = models.CharField(max_length=50,default='',null=True)
def __str__(self):
return self.firstName
objects = models.Manager()
let's migrate model
python manage.py makemigrations
python manage.py migrate
forms.py
from django import forms
from crispy_forms.helper import FormHelper
from crispy_forms.layout import Layout, Div, Submit, Row, Column, Field
from .models import tbl_Employee
class EmployeeRegistration(forms.ModelForm):
class Meta:
model = tbl_Employee
fields =[ 'Empcode','firstName','middleName','lastName','email','phoneNo' ,'address','exprience',
'DOB','gender','qualification'
]
Then let’s write a code in views.py to create a function to Export data from the database into Excel.
Views.py
from .models import tbl_Employee
import datetime as dt
import pandas as pd
import os
from django.conf import settings
from django.core.files.storage import FileSystemStorage
import csv
def export_users_csv(request):
if request.method == 'POST':
response = HttpResponse(content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename="EmployeeData.csv"'
writer = csv.writer(response)
writer.writerow(['Employee Detail'])
writer.writerow(['Employee Code','Employee Name','Relation Name','Last Name','gender','DOB','e-mail','Contact No' ,'Address' ,'exprience','Qualification'])
users = tbl_Employee.objects.all().values_list('Empcode','firstName' , 'middleName' , 'lastName','gender','DOB','email','phoneNo' ,'address','exprience','qualification')
for user in users:
writer.writerow(user)
return response
return render(request, 'exportexcel.html')
then add the path to the urls file
urls.py
from django.urls import path
from . import views
urlpatterns = [
path("",views.base,name="base"),
path("user_login/",views.user_login,name="user_login"),
path('export_users_csv/', views.export_users_csv,name="export_users_csv"),
]
Create a template folder in the root directory or app directory and a create html file named exportexcel.html
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
{% block content %}
<div class="shadow-lg continer">
<form method="post" enctype="multipart/form-data">
{% csrf_token %}
<div class="row">
<div class="col-md-6 col-xs-12">
<div class="x_panel">
<div class="x_title">
<h2>Data Export</h2>
<div class="clearfix"></div>
</div>
<div class="x_content">
<div class="row">
<div class="col-md-8 col-sm-12 col-xs-12 form-group">
<label class="control-label col-md-3 col-sm-3 col-xs-6" for="name">Company<span class="required">*</span>
</label>
</div>
</div>
<button type="submit" class="btn btn-success" >Export</button>
</div>
</div>
</div>
</div>
</form>
</div>
{% endblock %}
Then execute command
Python manage.py runserver