Motivation

As business requirements evolve, certain features are expected to evolve as well. That was the case for me a few days ago when I had to implement a feature where Django model's data are converted to .xlsx excel format and sent, as an 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 files 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 the 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(if you consider using the paid services of coingecko, use can use my referral CGSIRNEIJ) to periodically fetch cryptocurrency 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.

Assumptions

It is assumed that readers are pretty familiar with Django and JavaScript as well as the typed extension of Python using mypy, the 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:

Sirneij / django_excel

Exporting Django model data as excel file (.xlsx) using openpyxl library and Google Spreadsheet API

django_excel

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

NOTE: If you use Coingecko's API, when you use my code, `CGSIRNEIJ`, I get some commissions. That can be a good way to help me.

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

Then, migrate the database:

(env) sirneij@pop-os ~/D/P/T/django_excel (main)> python manage.py migrate

Thereafter, run the project:

(env) sirneij@pop-os ~/D/P/T/django_excel (main)> python manage.py run

Run tests locally

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

(env)

…

View on GitHub

Aside from this, the application is live and can be accessed via https://django-excel-export.herokuapp.com/.

Implementation

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 -> settings.py
...
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] = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    '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': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]
...

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']
CELERY_TASK_SERIALIZER: str = 'json'
CELERY_RESULT_SERIALIZER: str = 'json'


# From celery 5.0, this is how to schedule tasks. Though we have not created them yet 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.
CELERY_ALWAYS_EAGER = True
CELERY_EAGER_PROPAGATES = True
BROKER_BACKEND = 'memory'

# Email configuration
ADMINS = (('Admin', config('EMAIL_HOST_USER', default='no-reply@django_excel.herokuapp.com')),)

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

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

    # ==============================================================================
    # SECURITY SETTINGS
    # ==============================================================================

    CSRF_COOKIE_SECURE = True
    CSRF_COOKIE_HTTPONLY = True

    SECURE_HSTS_SECONDS = 60 * 60 * 24 * 7 * 52  # one year
    SECURE_HSTS_INCLUDE_SUBDOMAINS = True
    SECURE_SSL_REDIRECT = True
    SECURE_BROWSER_XSS_FILTER = True
    SECURE_CONTENT_TYPE_NOSNIFF = True
    SECURE_PROXY_SSL_HEADER = ('HTTP_X_FORWARDED_PROTO', 'https')

    SESSION_COOKIE_SECURE = True

    db_from_env = dj_database_url.config(conn_max_age=500)
    DATABASES['default'].update(db_from_env)

Though the snippets above tend to be a 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='127.0.0.1,localhost', cast=Csv())

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 settings.py file, create a file named celery.py and make it look like:

# django_excel -> celery.py
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.
app.autodiscover_tasks()

if settings.DEBUG:

    @app.task(bind=True)
    def debug_task(self):
        """Debug method."""
        print(f'Request: {self.request!r}')

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

# django_excel -> __init__.py

# 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',)

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

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

...
# django_excel -> urls.py

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

urlpatterns = [
    path('admin/', admin.site.urls),
    path('', include('core.urls', namespace='core')), # include our app's urls.py file
]

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

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

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

# core -> models.py

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.name} - {self.symbol}'

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(referral code CGSIRNEIJ) 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 tasks.py file in your app's directory and make it look like:

# core -> tasks.py

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


@shared_task
def get_coins_data_from_coingecko_and_store() -> None:
    """Fetch data from coingecko API (referral code `CGSIRNEIJ`) and store."""
    base_url = 'https://api.coingecko.com/api/v3/coins/'
    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']
        coin.save()


@shared_task
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()
    workbook.remove(workbook.active)
    worksheet = workbook.create_sheet(title='Latest Cryptocurrency Coins Data', index=1)
    workbook.security.workbookPassword = config('PASSWORD', default='12345data')
    workbook.security.lockStructure = config('PROTECT', default=True, cast=bool)
    workbook.security.revisionsPassword = config('PASSWORD', default='12345data')
    worksheet.protection.sheet = config('PROTECT', default=True, cast=bool)
    worksheet.protection.formatCells = 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 = [
            coin.name,
            f'{coin.symbol}'.upper(),
            coin.rank,
            currency(coin.current_price),
            currency(coin.price_change_within_24_hours),
            currency(coin.market_cap),
            coin.total_supply,
        ]

        # 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
            cell.protection = Protection(locked=True)
    workbook.save(excelfile)
    now = timezone.now()
    message = EmailMessage(
        f'Coin data as of {now.date().isoformat()}',
        f'Generated at: {now.isoformat()}',
        settings.DEFAULT_FROM_EMAIL,
        [user_email],
    )
    message.attach('latest-coin-list.xlsx', excelfile.getvalue(), 'application/vnd.ms-excel')
    message.send()

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 -> settings.py
...
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'),
    },
}
...

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 worksheet.protection.sheet is set to True, your Microsoft excel toolbar will be grayed out. To prevent this, we set worksheet.protection.formatCells 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/vnd.ms-excel. 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 views.py file.

# core -> views.py

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']
        export_data_to_excel.delay(email)
        return JsonResponse({'message': 'Coins data successfully extracted 💃!'}, status=200)

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

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">
  <head>
    <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>
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
      integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3"
      crossorigin="anonymous"
    />
    <style>
      .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%;
        }
      }
    </style>
  </head>
  <body>
    <div class="container">
      <form class="row mt-3 mb-2 form">
        {% csrf_token %}
        <div class="input-group">
          <input
            class="form-control"
            type="email"
            name="email"
            id="email"
            placeholder="Email address, e.g. django_excel@johnspeaks.com"
            required
          />
          <div class="input-group-append">
            <button
              title="Export data and send to my email address."
              class="btn btn-success"
              type="submit"
            >
              Export data
            </button>
          </div>
        </div>
      </form>
      {% 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">
            <tr>
              <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>
            </tr>
          </thead>
          <tbody>
            {% for coin in coin_data %}
            <tr>
              <td><img src="{{coin.image_url}}" height="50" /></td>
              <td class="align-middle">{{coin.name}}</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>
              <td class="align-middle">{{coin.market_cap | currency}}</td>
              <td class="align-middle">{{coin.total_supply}}</td>
            </tr>
            {% endfor %}
          </tbody>
        </table>
      </div>
      {% else %}
      <h3 class="text-center justify-content-center">
        No coin data currently...
      </h3>
      {% endif %}

      <footer class="py-1 my-2 d-flex justify-content-center">
        <p class="text-center text-muted">
          &copy; <span id="year"></span>
          <a
            href="http://github.com/sirneij"
            target="_blank"
            rel="noopener noreferrer"
          >
            Idogun, John owolabi
          </a>
        </p>
        .
      </footer>
    </div>
    <script
      src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"
      integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p"
      crossorigin="anonymous"
    ></script>
    <script>
      document.getElementById('year').innerHTML = new Date().getFullYear();

      document
        .querySelector('.form')
        .addEventListener('submit', async (event) => {
          await event.preventDefault();
          if (
            !window.confirm(
              'Are you sure you want to extract this data and send to the email provided?'
            )
          ) {
            return;
          }
          const csrftoken = await document.querySelector(
            '[name=csrfmiddlewaretoken]'
          ).value;
          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) {
            alert(responseData.message);
            window.location = location.href;
          } else {
            alert(responseData.message);
          }
        });
    </script>
  </body>
</html>

Edit your app's urls.py to reflect these changes:

# core -> urls.py

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'),
]

Run your server now and navigate to 127.0.0.1:8000 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

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:

tests/
├── core
│   ├── __init__.py
│   ├── test_models.py
│   ├── test_tasks.py
│   └── test_views.py
└── __init__.py

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

# tests -> core -> test_models.py
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.name} - {self.coin.symbol}')

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/test_tasks.py with:

# tests -> core -> test_tasks.py
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': 'https://assets.coingecko.com/coins/images/1/large/bitcoin.png?1547033579',
                    'current_price': 12644080,
                    'market_cap': 240714282203755,
                    'market_cap_rank': 1,
                    'price_change_24h': 197155,
                    'total_supply': 21000000.0,
                }
            ]

            get_coins_data_from_coingecko_and_store()

        mock_get.assert_called_once()

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

        export_data_to_excel('admin@django_excel.com')

        self.assertEqual(len(mail.outbox), 1)
        self.assertEqual(mail.outbox[0].to, ['admin@django_excel.com'])

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/test_views.py:

# tests -> core -> test_views.py

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()
        self.data = {'userEmail': 'django_excel@django.com'}

    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 = self.client.post(reverse('core:extract_data'), self.data, content_type='application/json')

        self.assertEqual(response.status_code, 200)
        mock_export_data_to_excel.assert_called_once()

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

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 -> settings.py
...
CELERY_ALWAYS_EAGER = True
CELERY_EAGER_PROPAGATES = True
BROKER_BACKEND = 'memory'
...

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.

Outro

Enjoyed this article? I'm a Software Engineer and Technical Writer actively seeking new opportunities, particularly in areas related to web security, finance, health care, and education. If you think my expertise aligns with your team's needs, let's chat! You can find me on LinkedIn: LinkedIn and Twitter: Twitter.

If you found this article valuable, consider sharing it with your network to help spread the knowledge!

Blog

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

John Owolabi Idogun

Motivation

Assumptions

Source code

Sirneij / django_excel

Exporting Django model data as excel file (.xlsx) using openpyxl library and Google Spreadsheet API

django_excel

NOTE: If you use Coingecko's API, when you use my code, `CGSIRNEIJ`, I get some commissions. That can be a good way to help me.

Run locally

Run tests locally

Implementation

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

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

Step 3: Testing, testing, and testing.

Bonus point

Outro

Join Our Newsletter. No Spam, Only the good stuff.

Related

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

John Owolabi Idogun

Motivation

Assumptions

Source code

Sirneij / django_excel

Exporting Django model data as excel file (.xlsx) using openpyxl library and Google Spreadsheet API

django_excel

NOTE: If you use Coingecko's API, when you use my code, CGSIRNEIJ, I get some commissions. That can be a good way to help me.

Run locally

Run tests locally

Implementation

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

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

Step 3: Testing, testing, and testing.

Bonus point

Outro

Join Our Newsletter. No Spam, Only the good stuff.

Related

NOTE: If you use Coingecko's API, when you use my code, `CGSIRNEIJ`, I get some commissions. That can be a good way to help me.