How to Export Excel File With Django

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.

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.

Django Excel - How to Export Excel File With Django

Following tutorial you will learn:

  • Simple Excel Write Operation
  • Styling your Excel Sheet with XLWT
  • Writing to Existing Workbook using XLWT, XLRD, XLUTILS

Quick Django Application Setup

mkdir django_excel
cd django_excel
virtualenv env
pip3 install django
django-admin startproject django_excel .
django-admin startapp excel_app

Installing Required Libraries:

pip install xlwt
pip install xlutils    # Required when reading excel file
pip install xlrd       # Required when reading excel file

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.

from django.urls import path
from excel_app import views

urlpatterns = [
    path('', views.ExcelPageView.as_view(), name='home'),
]

The last thing, we need to import excel_app/urls.py in the main folder django_excel/urls.py. Edit django_excel/urls.py and your django_excel/urls.py should look like below:

from django.contrib import admin
from django.urls import path, include

from excel_app import urls as excel_app_urls

urlpatterns = [
    path('admin/', admin.site.urls),

    path('', include(excel_app_urls))
]

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-8
  • ws = 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
# excel_app/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
# excel_app/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
# excel_app/urls.py
...
urlpatterns = [
    ...,
    path('export/export-write-xls', views.export_write_xls, name='export_write_xls'),
]

GitHub – 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.

Share