How to Export Data, Queryset to Excel Sheet in Django

In the world of web development, Django has proven to be a powerful and versatile framework for creating robust and feature-rich applications. One common task developers often face is exporting data from a Django application to Excel files. Whether you need to provide users with a downloadable spreadsheet or generate reports for analysis, the process can be straightforward with the right approach. In this blog post, we’ll walk you through the steps of exporting data to Excel files using Django.

Step 1: Set Up Your Django Project

Assuming you have a Django project up and running, make sure you have a model that represents the data you want to export. If you don’t have one, create it using Django’s Object-Relational Mapping (ORM). In this example, let’s say you have a model named Product with fields like name, price, and quantity.

Step 2: Install Required Packages

Before you proceed, you’ll need to install the openpyxl library, which is a powerful package for working with Excel files in Python. You can install it using pip:

pip install openpyxl

In this project, we will be using openpyxl library as xlwt no more maintained.

Advantages of openpyxl:

Supports Excel files in the .xlsx format (Excel 2007 and later).
Provides better compatibility with newer Excel features like charts, tables, and advanced formatting.
Can read and modify existing Excel files.
Supports cell styles, formulas, and more advanced features.
Actively maintained and updated, with a strong community.

Step 3: Create the Export View

Next, you’ll need to create a view that generates and serves the Excel file. Open your views.py file and import the necessary modules:

from django.http import HttpResponse
from openpyxl import Workbook
from .models import Product

Now, create a view function that retrieves the data from your database model and creates an Excel table workbook:

def export_to_excel(request):
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename="products.xlsx"'

    wb = Workbook()
    ws = wb.active
    ws.title = "Products"

    # Add headers
    headers = ["Name", "Price", "Quantity"]
    ws.append(headers)

    # Add data from the model
    products = Product.objects.all()
    for product in products:
        ws.append([product.name, product.price, product.quantity])

    # Save the workbook to the HttpResponse
    wb.save(response)
    return response

Step 4: Create a URL Route

Now that you’ve defined the view, you need to create a URL route that maps to it. Open your urls.py file and add the following code:

from django.urls import path
from .views import export_to_excel

urlpatterns = [
    # Other URL patterns
    path('export/', export_to_excel, name='export_to_excel'),
]

Step 5: Create a Link or Button to Download Spreadsheet File

In your template file, you can create a link or button that users can click to trigger the export process:

<a href="{% url 'export_to_excel' %}">Export to Excel</a>

Step 6: Run the Server and Test

With everything set up, run your Django development server and navigate to the URL you defined for the export view. Click the “Export to Excel” link or button, and your browser will prompt you to download the generated Excel file.

Congratulations! You’ve successfully implemented a feature to exxport data from your Django application to Excel files. This can be a valuable tool for users who need to analyze and manipulate data offline or share it with others.

Find this tutorial on Github.