By continuing to navigate on this website, you accept the use of cookies to serve you more relevant services & content.
For more information and to change the setting of cookies on your computer, please read our Cookie Policy.

Using Python xlwt generating CSV writer and Excel files

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 lets 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 to create 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 response object. Finally the response will be Excel file(books.xls) with all the records in the Book Model.

    Posted On
  • 06 July 2013
  • By
  • Micropyramid

Need any Help in your Project?Let's Talk

Latest Comments
Related Articles
QRCode generation in python

A Quick Response code(QRCode) is a two-dimensional pictographic code used for its fast readability and comparatively large storage capacity. The code consists of black modules ...

Continue Reading...
How to implement Case Insensitive CSV DictReader in python

In general use cases we upload the CSV files to the system to store huge amount of data by uploading single file. For example in ...

Continue Reading...
Debugging in Python

When something goes wrong with your code instead of using standard debugging techniques such as print statements use debugging tools. I found two great tools ...

Continue Reading...
open source packages

Subscribe To our news letter

Subscribe and Stay Updated about our Webinars, news and articles on Django, Python, Machine Learning, Amazon Web Services, DevOps, Salesforce, ReactJS, AngularJS, React Native.
* We don't provide your email contact details to any third parties