Generating CSV, Excel files Using Python

In most of the cases, you need to export the data from your database to different formats. In this post, I will show you how to create functions in your Django applications which export data as files. Here I will be covering the mostly used formats: CSV and Excel.

First of all, look at the model.

from django.db import models
class Book(models.Model):
title = models.CharField(max_length=100)
description = models.TextField(blank=True)

It’s a simple model with title and description. Now let’s create the functions.

CSV Format:

CSV is the most common import and export format for databases.It’s a textual format. Python comes with a built-in CSV library called CSV.

import csv
from django.http import HttpResponse
from app_name.models import Book
    def generate_book_csv(request):
books = Book.objects.all()
        # Create the HttpResponse object with CSV header.This tells browsers that 
# the document is a CSV file.
response = HttpResponse(content_type='text/csv')
        # The response also has additional Content-Disposition header, which contains 
# the name of the CSV file.
response['Content-Disposition'] = 'attachment; filename=books.csv'
        # The csv.writer function takes file-like object as argument i.e.,HttpResponse object
writer = csv.writer(response)
        # For each row in your CSV file, call writer.writerow function by passing 
# a list or tuple to it.
writer.writerow(['ID', 'Title', 'Description'])
        for book in books:
writer.writerow([book.id, book.title, book.description])
return response

Finally, return the response object.

Excel Format:

Excel is the main spreadsheet format which holds data in worksheets, charts etc. We are going to use xlwt library for creating a spreadsheet.

First, you need to install xlwt library using the command — pip install xlwt

import xlwt
from django.http import HttpResponse
from app_name.models import Book
    def generate_book_excel(request):
books = Book.objects.all()
        # Create the HttpResponse object with Excel header.This tells browsers that 
# the document is a Excel file.
response = HttpResponse(content_type='application/ms-excel')
        # The response also has additional Content-Disposition header, which contains 
# the name of the Excel file.
response['Content-Disposition'] = 'attachment; filename=books.xls'
        # Create object for the Workbook which is under xlwt library.
workbook = xlwt.Workbook()
        # By using Workbook object, add the sheet with the name of your choice.
worksheet = workbook.add_sheet("Books")

row_num = 0
columns = ['ID', 'Title', 'Description']
for col_num in range(len(columns)):
# For each cell in your Excel Sheet, call write function by passing row number,
# column number and cell data.
worksheet.write(row_num, col_num, columns[col_num])

for book in books:
row_num += 1
row = [book.id,book.title,book.description]
for col_num in range(len(row)):
worksheet.write(row_num, col_num, row[col_num])

workbook.save(response)
return response

After filling up the sheet with the data, save the workbook by passing response object as an argument and return a response object. Finally, the response will be Excel file(books.xls) with all the records in the Book Model.

Show your support

Clapping shows how much you appreciated MicroPyramid’s story.