In this tutorial, you will learn how to Export Excel Spreadsheet with Django. You will learn how to do read-write operations on excel sheets. Styling Excel spreadsheet with XLWT Library. And writing to Existing Excel Workbook using XLWT.
From this tutorial, you will learn how to – export to excel, excel export, django download excel file, django-excel tutorial, export data to excel,create excel file, generate excel file. From this tutorial you will learn how to –
Exporting Data to Excel is a very common task for many web applications. We can export the data from the database to an Excel Spreadsheet. Python uses the in-built CSV Library, which makes exporting CSV files very easy. But for export Excel Sheet, we will be using the XLWT library. It is the library to generate spreadsheet files compatible with Microsoft Excel versions 95 to 2019.
Following tutorial you will learn:
- Simple Excel Write Operation
- Styling your Excel Sheet with XLWT
- Writing to Existing Workbook using XLWT, XLRD, XLUTILS
Installing Required Libraries for Django Excel Export:
pip install xlwt pip install xlutils # Required when reading excel file pip install xlrd # Required when reading excel file
Quick Django Application Setup for Django Export to Excel
mkdir django_excel cd django_excel virtualenv env . env/bin/activate pip install django django-admin startproject django_excel . django-admin startapp excel_app
Open your settings.py file and add app name, which we have created with the name excel_app
INSTALLED_APPS = [
...,
'excel_app', # <- this
]
Create a View to display all html code
from django.views.generic.base import TemplateView
class CSVPageView(TemplateView):
template_name = "excel_home.html"
excel_app/templates/excel_home.html
<!DOCTYPE html>
<html>
<head><title>Excel Examples</title></head>
<body>
<h3>Excel Example - Read Write Examples</h3>
<ul>
<li>Write Operation
<ul>
<li>
<a href="{% url 'export_excel' %}">Simple Excel Write Operation</a>
</li>
<li>
<a href="{% url 'export_styling_excel' %}">Styling your Excel data with xlwt</a>
</li>
<li>
<a href="{% url 'export_write_xls' %}">writing to existing workbook using xlwt </a>
</li>
</ul>
</li>
</ul>
</body>
</html>
Note: if you run the above file it will give an error because we have not created URLs. We are going to create those URLs below in Read/Write operation code.
Create a file named urls.py in your excel_app folder and the code. Note the URLs of these apps can be created here.
# django_excel/urls.py
from django.contrib import admin
from django.urls import path
from excel_app import views
urlpatterns = [
path('admin/', admin.site.urls),
path('', views.ExcelPageView.as_view(), name='home'),
]
Before getting started, learn some code explanation of XLWT Library.
Python XLWT Library Code Explanation
HttpResponse(content_type='application/ms-excel')– This tells browsers that the document is an MS-EXCEL file, instead of an HTML file.response['Content-Disposition'] = 'attachment; filename="users.xls"'– This contains CSV filename and downloads files with that name.wb = xlwt.Workbook(encoding='utf-8')– Creating a Workbook of encoding utf-8ws = wb.add_sheet('Users Data')– Creating a Sheet named “Users Data” and all the data will be written inside this sheet.wb.save(response)– Saving the workbook and Excel file will automatically get downloaded.
Export Django QuerySet to Excel Spreadsheet
The code will export User Model Data like username, first last name, email to Excel Sheet.
# excel_app/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 Data') # this will make a sheet named Users Data
# 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) # at 0 row 0 column
# 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
# django_excel/urls.py
...
urlpatterns = [
...,
path('export/excel', views.export_users_xls, name='export_excel'),
]
Export XLS File using XLWT Library With Styling
The code will explain how to Style your Excel File. The bellow code will explain Wrap text in the cell, background color, border, and text color.
# excel_app/views.py
import xlwt
from django.http import HttpResponse
def export_styling_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('Styling Data') # this will make a sheet named Users Data - First Sheet
styles = dict(
bold = 'font: bold 1',
italic = 'font: italic 1',
# Wrap text in the cell
wrap_bold = 'font: bold 1; align: wrap 1;',
# White text on a blue background
reversed = 'pattern: pattern solid, fore_color blue; font: color white;',
# Light orange checkered background
light_orange_bg = 'pattern: pattern fine_dots, fore_color white, back_color orange;',
# Heavy borders
bordered = 'border: top thick, right thick, bottom thick, left thick;',
# 16 pt red text
big_red = 'font: height 320, color red;',
)
for idx, k in enumerate(sorted(styles)):
style = xlwt.easyxf(styles[k])
ws.write(idx, 0, k)
ws.write(idx, 1, styles[k], style)
wb.save(response)
return response
# django_excel/urls.py
...
urlpatterns = [
...,
path('export/excel-styling', views.export_styling_xls, name='export_styling_excel'),
]
Writing to existing Excel workbook using XLWT
The bellow code will explain how to write data in Exisiting excel file and the content inside it.
# excel_app/views.py
from xlutils.copy import copy # http://pypi.python.org/pypi/xlutils
from xlrd import open_workbook # http://pypi.python.org/pypi/xlrd
import xlwt
from django.http import HttpResponse
import os
def export_write_xls(request):
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename="users.xls"'
# EG: path = excel_app/sample.xls
path = os.path.dirname(__file__)
file = os.path.join(path, 'sample.xls')
rb = open_workbook(file, formatting_info=True)
r_sheet = rb.sheet_by_index(0)
wb = copy(rb)
ws = wb.get_sheet(0)
row_num = 2 # index start from 0
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])
# wb.save(file) # will replace original file
# wb.save(file + '.out' + os.path.splitext(file)[-1]) # will save file where the excel file is
wb.save(response)
return response
# django_excel/urls.py
...
urlpatterns = [
...,
path('export/export-write-xls', views.export_write_xls, name='export_write_xls'),
]
GitHub Code Django Export to Excel – Run Example Locally
It is also available on GitHub – https://github.com/studygyaan/How-to-Export-Excel-File-With-Django
Clone the Repository –
git clone https://github.com/studygyaan/How-to-Export-Excel-File-With-Django.git
Change Directory
cd How-to-Export-Excel-File-With-Django
Create Virtual Environment – VirtualENV
virtualenv env
Activate Virtual Environment
source env/bin/activate
Run requirement.txt file to install libraries using Pip3
pip3 install -r requirements.txt
Run the server
python3 manage.py runserver
And open http://localhost:8000/ in your browser.
