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 https://github.com/narenaryan/Django-xdump
Objective
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
# In the project root
$ mkdir templates
# Add base to installed apps
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'base'
]
# Add templates in DIRS list
TEMPLATES = [
{
'BACKEND': 'django.template.backends.django.DjangoTemplates',
'DIRS': ["templates"],
'APP_DIRS': True,
'OPTIONS': {
'context_processors': [
'django.template.context_processors.debug',
'django.template.context_processors.request',
'django.contrib.auth.context_processors.auth',
'django.contrib.messages.context_processors.messages',
],
},
},
]
# From project root $ python manage.py migrate $ python manage.py createsuperuser
{% load staticfiles %}
<!DOCTYPE html>
<html>
<head>
<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="https://code.jquery.com/jquery-2.2.4.min.js"></script>
</head>
<body>
<div class="navbar navbar-default navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<a href="../" class="navbar-brand">Xdump</a>
</div>
</div>
</div>
{% block content %}
{% endblock %}
{% block javascript %}
{% endblock %}
</body>
</html>
{% extends 'base.html' %}
{% load staticfiles %}
{% block content %}
<div class="container">
<div class="row">
<div class="jumbotron">
<div class="row">
<center>
<h1>Hello</h1>
<p>Dump your data here</p>
<a href="javascript:void(0)" class="btn btn-primary btn-lg dump">Download</a>
</center>
</div>
</div>
</div>
</div>
{% endblock %}
{% block javascript %}
<script src="{% static 'js/home.js' %}"></script>
{% endblock %}
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, user.id)
worksheet.write(row, 1, user.username)
worksheet.write(row, 2, user.email)
worksheet.write(row, 3, user.date_joined.ctime())
row += 1
# Close workbook for building file
workbook.close()
output.seek(0)
response = HttpResponse(output.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
return response
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
# base/urls.py from django.conf.urls import url from base.views import Home, Userdump urlpatterns = [ url(r'^$', Home.as_view()), url(r'^dump/', Userdump.as_view()), ]
from django.conf.urls import include, url
from django.contrib import admin
urlpatterns = [
url(r'^admin/', admin.site.urls),
url(r'', include('base.urls')),
]
Now the toughest part comes
- Make a XHR request (not Ajax) to the “/dump” URL (see urls.py) 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();
a.download = "users_" + today.toDateString().split(" ").join("_") + ".xls";
a.style.display = 'none';
document.body.appendChild(a);
return a.click();
}
};
xhttp.open("GET", "/dump", true);
xhttp.setRequestHeader("Content-Type", "application/json");
xhttp.responseType = 'blob';
xhttp.send();
});
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);
document.body.appendChild(a);
// Using this we can set name for downloadable file
a.download = "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.
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.
Thanks for reading. 🙂
Good one Naren
Thanks Prashanth 🙂