DEV Community

Cover image for Django and Openpyxl: Extracting and Sending Django model data as excel file (.xlsx)
John Idogun
John Idogun

Posted on • Updated on

Django and Openpyxl: Extracting and Sending Django model data as excel file (.xlsx)


As business requirements evolve, certain features are expected to evolve as well. That was the case for me few days ago when I had to implement a feature where Django model's data are converted to .xlsx excel format and sent, as attachment, to a provided email address literally. It was a big problem for me to figure out how to do this with that file extension as most available solutions were using the old .xls excel file extension and none really talked about sending excel file as attachments of an email sent via Django. To solve this problem, I looked at a similar solution by Vitor Freitas who used xlwt to export Django model's data to .xls excel file format and served it as response data to the user for download. Though a great companion, I needed a different file extension and was solving a slightly different problem. From further research, I came across Working with Excel Files in Python, from Working with Spreadsheets using Python, which recommended openpyxl for "reading and writing Excel 2010 files (ie: .xlsx)" so I decided to try the package out. This experience birthed this article where I utilized coingecko's api to periodically fetch cryptocurrency's coin's data, store the data in a database, and then display as well as allow users to extract the data to excel files sent to any email address provided. It's my deepest hope that you will find it helpful.


  • It is assummed that readers are pretty familiar with Django and JavaScript as well as the typed extension of Python using mypy, typing built-in module, and the rules by PEP8.

  • You should also be familiar with writing tests for Django models, methods, views, celery tasks, and functions. You don't have to be militant at that though.

  • Knowledge of HTML, and CSS (and its frameworks — Bootstrap for this project) is also assumed.

Source code

The entire source code for this article can be accessed via:

GitHub logo Sirneij / django_excel

Exporting Django model data as excel file (.xlsx) using openpyxl library


main Issues Forks Stars License

This repository accompanies this tutorial on It has been deployed to heroku and can be accessed live via this link.

Run locally

It can be run locally by creating a virtual environment using any of venv, poetry, virtualenv, and pipenv. I used virtualenv while developing the app. Having created the virtual environment, activate it and install the project's dependencies by issuing the following command in your terminal:

(env) sirneij@pop-os ~/D/P/T/django_excel (main)> pip install -r requirements.txt
Enter fullscreen mode Exit fullscreen mode

Then, migrate the database:

(env) sirneij@pop-os ~/D/P/T/django_excel (main)> python migrate
Enter fullscreen mode Exit fullscreen mode

Thereafter, run the project:

(env) sirneij@pop-os ~/D/P/T/django_excel (main)> python run
Enter fullscreen mode Exit fullscreen mode

Run tests locally

To run the tests, run the following in your terminal:

(env) sirneij@pop-os ~/D/P/T/django_excel (main)> py.test --nomigrations --reuse-db -W error::RuntimeWarning --cov=core --cov-report=html tests/
Enter fullscreen mode Exit fullscreen mode

Aside this, the application is live and can be accessed via


Step 1: Start a project, an application, and configure settings

To incept building out the project, create a directory for it — mine was django_excel — start a virtual environment, start a django project — called mine django_excel as well — and create a django application — my app was named core. Then proceed to opening your project's settings in your preferred IDE/text editor. Add the following configurations:

# django_excel ->
from pathlib import Path
from typing import Any

# add these lines for python-decouple and celery.
from celery.schedules import crontab
from decouple import Csv, config

INSTALLED_APPS: list[str] = [
    'core.apps.CoreConfig', # add your app

TEMPLATES: list[dict[str, Any]] = [
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [BASE_DIR / 'templates'], # configure your templates
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [

STATIC_ROOT: str = BASE_DIR / 'staticfiles' # add static_root for deployment purposes


# Configure celery broker_url and other configurations
CELERY_BROKER_URL: str = config('REDIS_URL', default='amqp://localhost')
CELERY_RESULT_BACKEND: str = config('REDIS_URL', default='')
CELERY_ACCEPT_CONTENT: list[str] = ['application/json']

# From celery 5.0, this is how to schedule tasks. Though we have not created them yet but it will come in handy
CELERY_BEAT_SCHEDULE: dict[str, dict[str, Any]] = {
    'get_coins_data_from_coingecko_and_store': {
        'task': 'core.tasks.get_coins_data_from_coingecko_and_store',
        'schedule': crontab(minute='*/1'),

# For testing purposes, we need to set these.

# Email configuration
ADMINS = (('Admin', config('EMAIL_HOST_USER', default='')),)

# We use the console to send emails during development and real email in production
    EMAIL_BACKEND = 'django.core.mail.backends.console.EmailBackend'
    EMAIL_BACKEND = 'django.core.mail.backends.smtp.EmailBackend'
    EMAIL_HOST_USER = config('EMAIL_HOST_USER', default='')
    EMAIL_HOST = ''
    EMAIL_PORT = 587
    EMAIL_USE_TLS = True

# Basic security and database settings for production ready applications
if not DEBUG:
    import dj_database_url

    # ==============================================================================
    # ==============================================================================


    SECURE_HSTS_SECONDS = 60 * 60 * 24 * 7 * 52  # one year


    db_from_env = dj_database_url.config(conn_max_age=500)
Enter fullscreen mode Exit fullscreen mode

Though the snippets above tend to be handful, they are just some basic configurations to set up our system for development and production environments. Comments are appended to shed light on what they do. If you ain't familiar with typed python, no worries. We specify the type of a variable in python using the following pattern:

# variable_name: variable_type = default_value, for example

ALLOWED_HOSTS: list[str] = config('ALLOWED_HOSTS', default=',localhost', cast=Csv())
Enter fullscreen mode Exit fullscreen mode

Since our ALLOWED_HOSTS variable is a list of strings, we specified this by using list[str]. One of the generic types in python.

Moving forward, let's bring in celery. In the same directory as your file, create a file named and make it look like:

# django_excel ->
import os

from celery import Celery
from django.conf import settings

# Set the default Django settings module for the 'celery' program.
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'django_excel.settings')

app = Celery('django_excel')
app.config_from_object('django.conf:settings', namespace='CELERY')

# Load task modules from all registered Django apps.

if settings.DEBUG:

    def debug_task(self):
        """Debug method."""
        print(f'Request: {self.request!r}')

Enter fullscreen mode Exit fullscreen mode

That was lifted from django celery documentation with few modifications to suit our project. Then proceed to the file in that same directory and turn it into:

# django_excel ->

# This will make sure the app is always imported when
# Django starts so that shared_task will use this app.
from .celery import app as celery_app

__all__ = ('celery_app',)

Enter fullscreen mode Exit fullscreen mode

Also lifted from the same source. The comments on top say it all.

Before taking a break from configurations, let's visit our root file:

# django_excel ->

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

urlpatterns = [
    path('', include('core.urls', namespace='core')), # include our app's file
Enter fullscreen mode Exit fullscreen mode

Ensure you create a file in your core app. That's it with configurations. Now to the next step.

Step 2: Define your model, views and write your tasks.

Let's go on to create our model. Open your app's file in your editor and fill it with:

# core ->

from django.db import models

class Coins(models.Model):
    name = models.CharField(max_length=200, null=True)
    symbol = models.CharField(max_length=200, null=True)
    image_url = models.URLField(null=True)
    current_price = models.DecimalField(decimal_places=2, max_digits=50, null=True)
    price_change_within_24_hours = models.DecimalField(decimal_places=2, max_digits=50, null=True)
    rank = models.IntegerField(null=True)
    market_cap = models.DecimalField(decimal_places=2, max_digits=50, null=True)
    total_supply = models.DecimalField(decimal_places=2, max_digits=50, null=True)

    def __str__(self) -> str:
        """Return model string representation."""
        return f'{} - {self.symbol}'

Enter fullscreen mode Exit fullscreen mode

We are interested in the coins's name, symbol, image, current_price, price_change_within_24_hours, rank, market_cap, and total_supply. Coingecko's api provides more fields but those are our current interests. You can now makemigrations and migrate but ensure you install all the dependencies we have introduced. This project's repository has a requirements.txt file with all of them.

Now, create a file in your app's directory and make it look like:

# core ->

from io import BytesIO

import requests
from celery import shared_task
from decouple import config
from django.conf import settings
from django.core.mail import EmailMessage
from django.utils import timezone
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, Protection

from core.models import Coins
from core.templatetags.custom_tags import currency

def get_coins_data_from_coingecko_and_store() -> None:
    """Fetch data from coingecko api and store."""
    base_url = ''
    market_currency_order = 'markets?vs_currency=ngn&order=market_cap_desc&'
    per_page = 'per_page=250&page=1&sparkline=false'
    final_url = f'{base_url}{market_currency_order}{per_page}'

    coin_data = requests.get(final_url).json()

    for data in coin_data:
        coin, _ = Coins.objects.get_or_create(name=data['name'], symbol=data['symbol'])
        coin.image_url = data['image']
        coin.current_price = data['current_price']
        coin.price_change_within_24_hours = data['price_change_24h']
        coin.rank = data['market_cap_rank']
        coin.market_cap = data['market_cap']
        coin.total_supply = data['total_supply']

def export_data_to_excel(user_email: str) -> None:
    """Send extracted model data and save in excel and send to email."""
    excelfile = BytesIO()
    workbook = Workbook()
    worksheet = workbook.create_sheet(title='Latest Cryptocurrency Coins Data', index=1) = config('PASSWORD', default='12345data') = config('PROTECT', default=True, cast=bool) = config('PASSWORD', default='12345data') = config('PROTECT', default=True, cast=bool) = config('PROTECT', default=False, cast=bool)

    worksheet.sheet_properties.tabColor = '1072BA'
    worksheet.freeze_panes = 'I2'

    coin_queryset = Coins.objects.all().order_by('rank')
    columns = ['Name', 'Symbol', 'Rank', 'Current price', 'Price change', 'Market cap', 'Total supply']
    row_num = 1

    # Assign the titles for each cell of the header
    for col_num, column_title in enumerate(columns, 1):
        cell = worksheet.cell(row=row_num, column=col_num)
        cell.value = column_title
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        cell.font = Font(bold=True)
    # Iterate through all coins
    for _, coin in enumerate(coin_queryset, 1):
        row_num += 1

        # Define the data for each cell in the row
        row = [

        # Assign the data for each cell of the row
        for col_num, cell_value in enumerate(row, 1):
            cell = worksheet.cell(row=row_num, column=col_num)
            cell.value = cell_value
   = Protection(locked=True)
    now =
    message = EmailMessage(
        f'Coin data as of {}',
        f'Generated at: {now.isoformat()}',
    message.attach('latest-coin-list.xlsx', excelfile.getvalue(), 'application/')

Enter fullscreen mode Exit fullscreen mode

The first task, get_coins_data_from_coingecko_and_store, does exactly what its name implies — get coin's data from coingecko api and store them in our database. It uses the requests library to achieve this. After getting the data, it loops through them and uses Django's get_or_create QuerySet api to get a coin data from the database using it's name and symbol. If such data doesn't exist, it creates it and update the created intance with other data from the api. Since this task is periodic — we want it to run automatically at a specified time — it was appended to our CELERY_BEAT_SCHEDULE earlier:

# django_excel ->
CELERY_BEAT_SCHEDULE: dict[str, dict[str, Any]] = {
    'get_coins_data_from_coingecko_and_store': {
        'task': 'core.tasks.get_coins_data_from_coingecko_and_store',
        'schedule': crontab(minute='*/1'),
Enter fullscreen mode Exit fullscreen mode

We gave it a name, which is the normal name we defined it as, and gave the thorough path where it can be found 'core.tasks.get_coins_data_from_coingecko_and_store'. Then it was scheduled to run every 1 minute.

Pretty simple huh?!

The next and major task — export_data_to_excel — does the extraction of data to excel format. First, it instantiated a temporary memory to hold the excel file before sending using BytesIO. You could use StringIO as well but I favour the former. Then, an openpyxl Workbook was created. By default, after this initialization, a woorksheet instance active is created but since I just didn't want to work with that, I removed it and created mine using workbook.create_sheet(title='Latest Cryptocurrency Coins Data', index=1). create_sheet takes a compulsory argument title but I also included the index I want the wooksheet to occupy. Since I am security conscious and don't want anyone to fiddle with my excel data anyhow without my permission, I decided to lock the workbook and provided password incase some changes are inevitable. From experience, immediately is set to True, your Microsoft excel toolbar will be grayed out. To prevent this, we set to True. Also, since we will be working with hundreds of coins and scrolling downwards might make us lose focus on the columns being investigated, I decided to make the colum headers fixed so that even when scrolling, you still see them. To achieve this, you go one cell more than the total header colum's row and column. Take for instance, if your headers have four colums, ['A', 'B', 'C', 'D'], to fix them, I will stop at the fifth column, 'E', and second row, '2'. So that my worksheet.freeze_panes will be set to E2. Got it? Cool 😎! Next, we defined our queryset and the colums we are interested in. Then, we start from row number one since openpyxl starts its row number from 1 unlike xlwt. We then iterate through these colums and populate the worksheet cell at each combination of row and column (row 1 and dynamic columns in this case since our row remains at 1). We also align the texts and bold them as well. You can do so much than those. Thanks to openpyxl 👏! After that, we iterated through the entire QuerySet previously defined. In each iteration, we first increment the row number so that previous rows won't be overriden and then populate each cell (a combination of row and colum) with their respective value. Double for loop 😨. There is a more nifty way to do this but I settled for this nonetheless. After populating the cells, I saved the workbook in the artificial memory created earlier and then sent the mail with the excel file as attachment. To get the excel file from the temporary memory, we used the .getvalue() method shipped with BytesIO. It's also available for StringIO. We also gave the file a name, latest-coin-list.xlsx and a type application/ As far as I know, they are important for easy encoding and decoding by your e-mail service provider. That was mouthful 🤤!

Next is our file.

# core ->

import json

from django.http import HttpRequest, HttpResponse, JsonResponse
from django.shortcuts import render

from core.models import Coins
from core.tasks import export_data_to_excel

def index(request: HttpRequest) -> HttpResponse:
    """Index view."""
    coins = Coins.objects.all().order_by('rank')
    context: dict[str, str] = {
        'coin_data': coins,
    return render(request, 'coin_data.html', context)

def extract_and_send_coin_data_via_email(request: HttpRequest) -> JsonResponse:
    """Handle the post requests for sending emails."""
    if request.method == 'POST':
        request_data = json.loads(request.body)
        email = request_data['userEmail']
        return JsonResponse({'message': 'Coins data successfully extracted 💃!'}, status=200)

    return JsonResponse({'message': 'Coins data failed to be extracted 😔!'}, status=500)

Enter fullscreen mode Exit fullscreen mode

Nothing much, just the normal function-based views. The first serves our templates and the other handles any export requests. It's basically an API endpoint that utilizes the export_data_to_excel task we discussed above. Notice that I didn't get my request data from request.POST.get(). This is because I used pure asynchronous fetch API to send post requests and data was passed via body option of the Request object as shown in the template:

<!--teamplates -> coin_data.html-->

{% load custom_tags %}

<!DOCTYPE html>
<html lang="en">
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Cryptocurrency Coins Data</title>
      .header {
        position: sticky;
        top: 0;
      .table-wrapper {
        width: 100%;
        height: 80vh;
        overflow: auto;
      .form {
        margin: auto;
        width: 50%;
      @media (max-width: 990px) {
        .form {
          width: 100%;
    <div class="container">
      <form class="row mt-3 mb-2 form">
        {% csrf_token %}
        <div class="input-group">
            placeholder="Email address, e.g."
          <div class="input-group-append">
              title="Export data and send to my email address."
              class="btn btn-success"
              Export data
      {% if coin_data %}
      <div class="table-wrapper table-responsive">
        <table class="table table-striped table-hover">
          <thead style="position: sticky; top: 0" class="table-dark">
              <th class="header" scope="col">Logo</th>
              <th class="header" scope="col">Name</th>
              <th class="header" scope="col">Symbol</th>
              <th class="header" scope="col">Rank</th>
              <th class="header" scope="col">Current price</th>
              <th class="header" scope="col">Price change</th>
              <th class="header" scope="col">Market cap</th>
              <th class="header" scope="col">Total supply</th>
            {% for coin in coin_data %}
              <td><img src="{{coin.image_url}}" height="50" /></td>
              <td class="align-middle">{{}}</td>
              <td class="align-middle">{{coin.symbol | upper}}</td>
              <td class="align-middle">{{coin.rank}}</td>
              <td class="align-middle">{{coin.current_price | currency}}</td>
              <td class="align-middle">
                {{coin.price_change_within_24_hours | currency}}
              <td class="align-middle">{{coin.market_cap | currency}}</td>
              <td class="align-middle">{{coin.total_supply}}</td>
            {% endfor %}
      {% else %}
      <h3 class="text-center justify-content-center">
        No coin data currently...
      {% endif %}

      <footer class="py-1 my-2 d-flex justify-content-center">
        <p class="text-center text-muted">
          &copy; <span id="year"></span>
            rel="noopener noreferrer"
            Idogun, John owolabi
      document.getElementById('year').innerHTML = new Date().getFullYear();

        .addEventListener('submit', async (event) => {
          await event.preventDefault();
          if (
              'Are you sure you want to extract this data and send to the email provided?'
          ) {
          const csrftoken = await document.querySelector(
          const userEmail = await document.querySelector('[name=email]').value;
          const data = await JSON.stringify({
            userEmail: userEmail,

          const response = await fetch("{% url 'core:extract_data' %}", {
            method: 'POST',
            headers: {
              'X-CSRFToken': csrftoken,
              'Content-Type': 'application/json',
              charset: 'utf-8',
            mode: 'same-origin',
            body: data,
          const responseData = await response.json();
          if (response.ok) {
            window.location = location.href;
          } else {
Enter fullscreen mode Exit fullscreen mode

Edit your app's to reflect these changes:

# core ->

from django.urls import path

from core import views

app_name: str = 'core'

urlpatterns = [
    path('', views.index, name='index'),
    path('extract-data-to-excel/', views.extract_and_send_coin_data_via_email, name='extract_data'),
Enter fullscreen mode Exit fullscreen mode

Run your server now and navigate to in your browser. Did you see something? Yeah, you must see No coin data currently... That's because you don't have any data currently in your database. To have data, you need to run our celery task which is responsible for fetching data from coingecko API. To run it, open another terminal which has your virtual environment activated and issue this command:

(virtualenv) sirneij@pop-os ~/D/P/T/django_excel (main)> celery -A django_excel worker -l info -B
Enter fullscreen mode Exit fullscreen mode

If your project name isn't django_excel, please modify that command appropriately. Now, you should see some streams of request and response 😊. If you now visit your browser after 1 minute, you should see something 💃!!!

Step 3: Testing, testing, and testing.

For almost a year now, I have been a strong proponent of TDD. That's because I struggled to adjust to that style initially but it's proved kinda awesome now. Try it out, it's worth the stress sometimes! From the foregoing, let's write some tests 😂! In your root directory, create a new folder named tests and make the folder look like:

├── core
│   ├──
│   ├──
│   ├──
│   └──
Enter fullscreen mode Exit fullscreen mode

We will be using pytest and it's django package to run our tests. You can delete core/ In tests/core/, let have something like:

# tests -> core ->
from django.test import TestCase

from core.models import Coins

class CoinsModelTests(TestCase):
    def setUp(self) -> None:
        """Create the setup of the test."""
        self.coin = Coins.objects.create(name='bitcoin', symbol='btc')

    def test_unicode(self) -> None:
        """Test the model's __str__ method"""
        self.assertEqual(str(self.coin), f'{} - {self.coin.symbol}')
Enter fullscreen mode Exit fullscreen mode

We are just checking if our unicode or string representation is correctly formatted. If we'd had bigger application structure, factory would have been great for this.

Also, populate tests/core/ with:

# tests -> core ->
from unittest.mock import patch

from django.core import mail
from django.test import TestCase

from core.models import Coins
from core.tasks import export_data_to_excel, get_coins_data_from_coingecko_and_store

class CoinTasksTests(TestCase):
    def test_get_coins_data_from_coingecko_and_store(self):
        '''Test get_coins_data_from_coingecko_and_store.'''

        with patch('core.tasks.requests.get') as mock_get:
            mock_get.return_value.coin_data = [
                    'symbol': 'btc',
                    'name': 'Bitcoin',
                    'image': '',
                    'current_price': 12644080,
                    'market_cap': 240714282203755,
                    'market_cap_rank': 1,
                    'price_change_24h': 197155,
                    'total_supply': 21000000.0,



    def test_export_data_to_excel(self):
        '''Test export_data_to_excel task.'''
            name='bitcoin', symbol='btc', current_price=12000000, price_change_within_24_hours=500, market_cap=210000000
            name='etherum', symbol='eth', current_price=12000000, price_change_within_24_hours=500, market_cap=210000000
            name='xrp', symbol='xrp', current_price=12000000, price_change_within_24_hours=500, market_cap=210000000


        self.assertEqual(len(mail.outbox), 1)
        self.assertEqual(mail.outbox[0].to, [''])
Enter fullscreen mode Exit fullscreen mode

In these tests, we introduced the concept of mocking. It's used to 'mock' some behaviour that might otherwise be dependent on network calls or availability of the external API being called. Though this test doesn't guarantee 100% test coverage as I would have wanted but it does well above 80%. And finally, tests/core/

# tests -> core ->

from unittest.mock import patch

from django.test import Client, TestCase
from django.urls import reverse

class IndexViewTests(TestCase):
    def setUp(self) -> None:
        """Create the setup of the test."""
        self.client = Client()

    def test_context(self) -> None:
        """Test index context and template."""
        response = self.client.get(reverse('core:index'))
        self.assertEqual(response.templates[0].name, 'coin_data.html')

class ExtractAndSendCoinDataViaEmailTests(TestCase):
    def setUp(self) -> None:
        """Create the setup of the test."""
        self.client = Client() = {'userEmail': ''}

    def test_extract_and_send_coin_data_via_email_success(self):
        """Test extract and send extracted data."""

        with patch('core.views.export_data_to_excel.delay') as mock_export_data_to_excel:
            response ='core:extract_data'),, content_type='application/json')

        self.assertEqual(response.status_code, 200)

    def test_extract_and_send_coin_data_via_email_failure(self):
        response = self.client.get(reverse('core:extract_data'),, content_type='application/json')
        self.assertEqual(response.status_code, 500)
Enter fullscreen mode Exit fullscreen mode

We also used mocking to mimick our export_data_to_excel task. It should be noted that setting the following is important to really test celery tasks:

# django_excel ->
Enter fullscreen mode Exit fullscreen mode

That brings us to the end of this surjourn 😎! Thanks for sticking around.

Bonus point

This article's repository has some other files to provide a more complete project. There are codes for currency custom filter, pytest configurations, some CI/CD using GitHub actions, code inspection to ensure all best practices are taken into consideration. You can go through them when you are opportune to.


Enjoyed this article? Consider contacting me for a job, something worthwhile or buying a coffee ☕. You can also connect with/follow me on LinkedIn. Also, it isn't bad if you help share it for wider coverage. I will appreciate...

Discussion (3)

mkalioby profile image
Mohamed M El-Kalioby

Did you check django-tables2 with its export feature?

sirneij profile image
John Idogun Author

I haven't really. Maybe I will look into trying it out.

mkalioby profile image
Mohamed M El-Kalioby

It saves alot regarding putting data in tables and export it.