Building an Excel file dump service in Django

Namaste Everyone. I recently wrote an excel dump service as part of my work and wants to share that experience with you. Initially, I irritated a lot because creating excel in Python is fairly easy but downloading it from JavaScript as an Ajax response is very painful. You should know how to set the right response headers, how to perform XHR request etc. I start with a sample project and explain the technique which I successfully used to solve the problem in a step by step way. Here we use a library called XLSX writer for building excel files.

Most of the times data export can be done through CSV, but if we need to dump data to an advanced data format, excel is the right one. We can add many worksheets to a workbook in Excel. Remember that CSV is plain text but a .XLSX(excel) file is binary one. We cannot create excel files from client-side since browser won’t let you mess up with the file system.

One approach is to create a physical excel file and store it in S3. Then we can pass the S3 file link back to UI and pass it as href to the button. But if we need to serve excel file without creating them on disk, then this technique can help you and you can proceed down.

At any point of time if you feel stuck, refer code from  here


Our main goal is to request an Excel file from Django server and download it through the browser.

  • We can either do a GET or POST request. POST request is used to supply filters to create the file on the server. Here for simplicity I just do a GET request to fetch a file
  • create a button on the UI and when a user clicks it, data should be exported to xlsx file and will download through the browser’s downloader.

Now I am creating a fresh project with the configuration of Python3, Django 1.9 & Sqlite3. Install Django and XlsxWriter using pip.

$ pip install django==1.9 xlsxwriter

Now let us create the project, app and modify necessary files.

$ django-admin startproject xdump
$ django-admin startapp base
For showing the minimal UI, I am creating a homepage with a button. Before that we need to include our app in settings, migrate. Create templates folder and add it in DIRS in settings.
# In the project root
$ mkdir templates
Now modify to add our app and templates folder configuration.
# Add base to installed apps

# Add templates in DIRS list
    'BACKEND': 'django.template.backends.django.DjangoTemplates',
    'DIRS': ["templates"],
    'APP_DIRS': True,
    'OPTIONS': {
       'context_processors': [
Let us do DB migration with this command. It is to create user tables in Django. After migration create super user.
# From project root
$ python migrate
$ python createsuperuser
By this, we are able to start our adventure. We can create a service to dump all Django auth user details. Instead of user model you can dump data from any other models as per your requirement.


Now run Django server and add few users from admin panel. I added four users Manoj, Nikhil, Ram and Sai Kiran.


My UI template is so simple. I will just create a view to render the template. Template will have a button. If we click the button, all users details will be prepared and downloaded as a xlsx file, fairly simple. Isn’t it?


For any Django project, there will be a base HTML file that acts as a parent for extending by other templates. Here I define one such template called templates/base.html.
{% load staticfiles %}
<!DOCTYPE html>

    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Auth Demo</title>
    <!-- Core CSS - Include with every page -->
    <link href="{% static 'css/bootstrap.min.css' %}" rel="stylesheet">
    <script src=""></script>

    <div class="navbar navbar-default navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <a href="../" class="navbar-brand">Xdump</a>
    {% block content %} 
    {% endblock %} 
    {% block javascript %} 
    {% endblock %}


Now I create one more template for holding home view and it extends base template. File is templates/home.html

 {% extends 'base.html' %} 
 {% load staticfiles %} 
 {% block content %}
<div class="container">
    <div class="row">
        <div class="jumbotron">
            <div class="row">
                    <p>Dump your data here</p>
                    <a href="javascript:void(0)" class="btn btn-primary btn-lg dump">Download</a>
{% endblock %} 
{% block javascript %}
<script src="{% static 'js/home.js' %}"></script>
{% endblock %}

home.js is the file where we are going to write logic for requesting the excel file and downloading it in browser. For now create a file in /static/js/home.js. static folder should be in the root directory. I added the templates but need Django views to power them. So I write views here. /base/
from django.contrib.auth import get_user_model
from django.http import JsonResponse, HttpResponse
from django.views.generic import View
from django.shortcuts import render
from io import BytesIO
import xlsxwriter
# Create your views here.

class Home(View):
    template_name = "home.html"
    def get(self, request):
        return render(request,self.template_name,{})

class Userdump(View):
    def get(self, request):
        output = BytesIO()
        # Feed a buffer to workbook
        workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet("users")
        users = get_user_model().objects.all()
        bold = workbook.add_format({'bold': True})
        columns = ["user", "name", "email", "date_joined"]
        # Fill first row with columns
        row = 0
        for i,elem in enumerate(columns):
            worksheet.write(row, i, elem, bold)
        row += 1
        # Now fill other rows with columns
        for user in users:
            worksheet.write(row, 0,
            worksheet.write(row, 1, user.username)
            worksheet.write(row, 2,
            worksheet.write(row, 3, user.date_joined.ctime())
            row += 1
        # Close workbook for building file
        response = HttpResponse(, content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        return response
If you observe in the views, I just created a temporary buffer using BytesIO and worked on that using XLSXwriter. You can find documentation for usage of XLSXWriter here. . Above I created a workbook, added a worksheet and then wrote data according to the guidelines of library and then returned binary data back as a response. content_type is very important here.
Now this view is able to serve the excel file as response. I quickly configure the necessary urls with views.
# base/
from django.conf.urls import url
from base.views import Home, Userdump

urlpatterns = [
 url(r'^$', Home.as_view()),
 url(r'^dump/', Userdump.as_view()),
And include these urls in main of root project.
from django.conf.urls import include, url
from django.contrib import admin

urlpatterns = [
 url(r'', include('base.urls')),
I started my Django server. It looks like this, if we visit the homepage.
Now our target is to download excel file containing user details when we click on that Download button. In the home.js I will add the logic to download file after browser recieve the response.

Now the toughest part comes

Our Django view is rendering the excel response. But we need to make a request from UI and then recieve it. So I will add an on click handler to the button with class dump in the UI and it does following:

  • Make a XHR request (not Ajax) to the “/dump” URL (see and recieve response
  • Recieve response as a blob and download the file with custom name and date

The reason why I am using raw XHRRequest instead of Ajax is Ajax won’t allow us to process on the binary response. Now I will show how code for home.js will look like

$(".dump").on("click", function() {
    xhttp = new XMLHttpRequest();
    xhttp.onreadystatechange = function() {
        var a, today;
        if (xhttp.readyState === 4 && xhttp.status === 200) {
            a = document.createElement('a');
            a.href = window.URL.createObjectURL(xhttp.response);
            today = new Date();
   = "users_" + today.toDateString().split(" ").join("_") + ".xls";
   = 'none';
    };"GET", "/dump", true);
    xhttp.setRequestHeader("Content-Type", "application/json");
    xhttp.responseType = 'blob';

The technique here I used is not the new one, but a clever idea of attaching an invisible anchor tag and adding my dump URL to it. So whenever button in the UI is clicked a XMLHttpRequest is prepared and it’s responseType is set as blob(which means binary data). Next, we attached download attribute to the anchor tag.

// These lines creates a hidden anchor tag on HTML document's body and sets it's href with download link
a = document.createElement('a');
a.href = window.URL.createObjectURL(xhttp.response);

// Using this we can set name for downloadable file = "users_" + today.toDateString().split(" ").join("_") + ".xls";

So now if we refresh the site and click on the button, we can download a real excel file which looks like this.

If I open the file in the libre office, I can see the data in the exact format I wished to.

Data is just as we prepared on server side
As I already mentioned in the beginning, it is straight forward to create an excel file response, but harder if you don’t know how to recieve and make it download from JavaScript. Tricky part is we cannot use Jquery Ajax to download file, instead we used a plain XMLHttpRequest. This file downloading technique can be applied to any other file types with setting correct response header content_type on the server.

If you have any doubts in code, the working project code is here
Hope this experience can help those who are facing the same trouble of writing a file dump service.
Thanks for reading.🙂

2 thoughts on “Building an Excel file dump service in Django

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s