How to Export Your Data as CSV, XLS, or XLSX

There are times, when you need to export the data from your database to different formats. For example, you want to create some diagrams in Office program for a presentation. In this post I will show you how to create admin actions which export selected items as files for a spreadsheet application (like MS Excel, OpenOffice Calc, LibreOffice Calc, Gnumeric, or Numbers). I will cover the mostly used formats: Comma Separated Values (CSV), Binary Excel (XLS), and Office Open XML (XLSX).
First of all, have a look at the model we will be dealing with. It's a simple model with title, description, and - of course - the id. 
# models.py
from django.db import models
class MyModel(models.Model):
title = models.CharField(max_length=100)
description = models.TextField(blank=True)
In the admininstration options, we'll define three admin actions: export_csv, export_xls, and export_xlsx.
# admin.py
from django.contrib import admin
from models import MyModel
# ... export functions will go here ...
class MyModelAdmin(admin.Admin):
actions = [export_csv, export_xls, export_xlsx]
admin.site.register(MyModel, MyModelAdmin)
Now let's create functions for each of those actions!
Comma-Separated Values Format
CSV is the most common import and export format for spreadsheets and databases. It's a textual format which one could easily create or parse himself, but there is also a python built-in library csv for handy data manipulation.
def export_csv(modeladmin, request, queryset):
import csv
from django.utils.encoding import smart_str
response = HttpResponse(mimetype='text/csv')
response['Content-Disposition'] = 'attachment; filename=mymodel.csv'
writer = csv.writer(response, csv.excel)
response.write(u'\ufeff'.encode('utf8')) # BOM (optional...Excel needs it to open UTF-8 file properly)
writer.writerow([
smart_str(u"ID"),
smart_str(u"Title"),
smart_str(u"Description"),
])
for obj in queryset:
writer.writerow([
smart_str(obj.pk),
smart_str(obj.title),
smart_str(obj.description),
])
return response
export_csv.short_description = u"Export CSV"
As you can see, HttpResponse is a file-like object and we used it to write data to.
Excel Binary File Format
XLS is the main spreadsheet format which holds data in worksheets, charts, and macros. We are going to use xlwt library to create a spreadsheet. There is analogous library xlrd to read XLS files. Note, that this format allows to have only 256 columns.
def export_xls(modeladmin, request, queryset):
import xlwt
response = HttpResponse(mimetype='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=mymodel.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet("MyModel")
row_num = 0
columns = [
(u"ID", 2000),
(u"Title", 6000),
(u"Description", 8000),
]
font_style = xlwt.XFStyle()
font_style.font.bold = True
for col_num in xrange(len(columns)):
ws.write(row_num, col_num, columns[col_num][0], font_style)
# set column width
ws.col(col_num).width = columns[col_num][1]
font_style = xlwt.XFStyle()
font_style.alignment.wrap = 1
for obj in queryset:
row_num += 1
row = [
obj.pk,
obj.title,
obj.description,
]
for col_num in xrange(len(row)):
ws.write(row_num, col_num, row[col_num], font_style)
wb.save(response)
return response
export_xls.short_description = u"Export XLS"
Here we created one worksheet, filled it with data, marked the first row in bold, and made the lines in the other cells wrapped. Also we set the width for each column. We'll do the same in the next format too.
Office Open XML Format
XLSX (a.k.a. OOXML or OpenXML) is a zipped, XML-based file format developed by Microsoft. It is fully supported by Microsoft Office 2007 and newer versions. OpenOffice 4.0, for example, can only read it. There is a python library openpyxl for reading and writing those files. This format is great when you need more than 256 columns and text formatting options.
def export_xlsx(modeladmin, request, queryset):
import openpyxl
from openpyxl.cell import get_column_letter
response = HttpResponse(mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=mymodel.xlsx'
wb = openpyxl.Workbook()
ws = wb.get_active_sheet()
ws.title = "MyModel"
row_num = 0
columns = [
(u"ID", 15),
(u"Title", 70),
(u"Description", 70),
]
for col_num in xrange(len(columns)):
c = ws.cell(row=row_num + 1, column=col_num + 1)
c.value = columns[col_num][0]
c.style.font.bold = True
# set column width
ws.column_dimensions[get_column_letter(col_num+1)].width = columns[col_num][1]
for obj in queryset:
row_num += 1
row = [
obj.pk,
obj.title,
obj.description,
]
for col_num in xrange(len(row)):
c = ws.cell(row=row_num + 1, column=col_num + 1)
c.value = row[col_num]
c.style.alignment.wrap_text = True
wb.save(response)
return response
export_xlsx.short_description = u"Export XLSX"

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)