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.
