How to Export Django Model Data to an Excel Sheet Using Pandas

Django, a high-level Python web framework, is widely used to build web applications quickly and with clean and pragmatic design. One of the common requirements in web applications is to export data from the database to an Excel file. This can be easily achieved using Django and Pandas, a powerful data manipulation library in Python.

This blog post will walk you through the steps to export Django model data to an Excel sheet using Django functional views.

Prerequisites

Before beginning, please confirm that you have installed the following items:

Python
Django
Pandas
Openpyxl (optional, but recommended for handling Excel files)

You can install these packages using pip:

pip install django pandas openpyxl

Step 1: Set Up Your Django Project & App

First, create a Django project and app if you haven’t already:

django-admin startproject myproject
cd myproject
django-admin startapp myapp

Include your app in the INSTALLED_APPS list within the settings.py file:

# myproject/settings.py

INSTALLED_APPS = [
    ...
    'myapp',
]

Step 2: Create a Django Model

Let’s create a simple model in models.py:

# myapp/models.py

from django.db import models

class Person(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)
    email = models.EmailField()

    def __str__(self):
        return f"{self.first_name} {self.last_name}"

These commands will apply the migrations to the database:

python manage.py makemigrations
python manage.py migrate

Step 3: Create a View to Export Data

Now, let’s create a view to export the data from the Person model to an Excel file using Pandas.

# myapp/views.py

import pandas as pd
from django.http import HttpResponse
from .models import Person

def export_persons_to_excel(request):
    # Query the Person model to get all records
    persons = Person.objects.all().values()
    
    # Convert the QuerySet to a DataFrame
    df = pd.DataFrame(list(persons))

    # Define the Excel file response
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=persons.xlsx'

    # Use Pandas to write the DataFrame to an Excel file
    df.to_excel(response, index=False, engine='openpyxl')

    return response

Step 4: Add a URL Pattern

Add a URL pattern to map to the view we just created:

urlpatterns = [
    path('export-persons/', export_persons_to_excel, name='export_persons'),
]

Include the app’s URLs in the project’s main URL configuration:

# myproject/urls.py

from django.contrib import admin
from django.urls import path, include

urlpatterns = [
    path('admin/', admin.site.urls),
    path('myapp/', include('myapp.urls')),
]

Step 5: Test the Export Functionality

To test the export functionality, ensure your development server is running:

python manage.py runserver

Visit http://127.0.0.1:8000/myapp/export-persons/ in your browser. If everything is set up correctly, your browser should prompt you to download an Excel file named persons.xlsx containing the data from the Person model.

Conclusion

In this blog post, we have demonstrated how to export data from a Django model to an Excel file using Pandas. This approach is not only efficient, but it also leverages the powerful data manipulation capabilities of Pandas. With this setup, you can easily extend the functionality to export more complex datasets or apply custom formatting to the Excel files.

Feel free to enhance this basic example by adding more features, such as filtering data before exporting, styling the Excel file, or even creating different export formats (CSV, JSON, etc.).

Additional Enhancements

Filtering Data Before Exporting

You can enhance the export functionality by allowing users to filter the data before exporting. For example, you might want to export only the data for people with a specific last name. Here’s how you can modify the view to include filtering:

# myapp/views.py

def export_persons_to_excel(request):
    last_name_filter = request.GET.get('last_name', None)
    if last_name_filter:
        persons = Person.objects.filter(last_name=last_name_filter).values()
    else:
        persons = Person.objects.all().values()
    
    df = pd.DataFrame(list(persons))
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=persons.xlsx'
    df.to_excel(response, index=False, engine='openpyxl')

    return response

Now, you can filter the data by appending a query parameter to the URL, such as http://127.0.0.1:8000/myapp/export-persons/?last_name=Smith.

Styling the Excel File

Using Pandas, you can also apply styling to your Excel files. For example, you can set the header background color or adjust the column widths. Here’s an example of how to implement basic styling:

# myapp/views.py

from pandas.io.formats.style import Styler

def export_persons_to_excel(request):
    persons = Person.objects.all().values()
    df = pd.DataFrame(list(persons))

    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=persons.xlsx'

    # Applying basic styling
    def style_specific_cell(x):
        color = 'background-color: yellow'
        return [color if x.name == 'first_name' else '' for _ in x]

    df.style.apply(style_specific_cell, axis=1).to_excel(response, index=False, engine='openpyxl')

    return response

With this setup, the cells in the first_name column will have a yellow background.

Exporting to Different Formats

Besides Excel, you might want to provide options for exporting data in different formats, such as CSV or JSON. You can extend the view to handle multiple formats:

# myapp/views.py

def export_data(request, file_format):
    persons = Person.objects.all().values()
    df = pd.DataFrame(list(persons))

    if file_format == 'excel':
        response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = 'attachment; filename=persons.xlsx'
        df.to_excel(response, index=False, engine='openpyxl')
    elif file_format == 'csv':
        response = HttpResponse(content_type='text/csv')
        response['Content-Disposition'] = 'attachment; filename=persons.csv'
        df.to_csv(response, index=False)
    elif file_format == 'json':
        response = HttpResponse(content_type='application/json')
        response['Content-Disposition'] = 'attachment; filename=persons.json'
        response.write(df.to_json(orient='records'))
    else:
        response = HttpResponse("Unsupported format", status=400)

    return response

Update the URL patterns to include the file format parameter:

# myapp/urls.py

from django.urls import path
from .views import export_data

urlpatterns = [
    path('export-persons//', export_data, name='export_data'),
]

Now, you can export data in different formats by specifying the desired format in the URL, such as http://127.0.0.1:8000/myapp/export-persons/excel/, http://127.0.0.1:8000/myapp/export-persons/csv/, or http://127.0.0.1:8000/myapp/export-persons/json/.

Implementing these enhancements can provide a more robust and flexible data export feature in your Django application.