Django and Openpyxl: Extracting and Sending Django model data as excel file (.xlsx)
Tags: javascript, webdev, python, django
Published: 2022-05-18 12:51:14

## 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][1] who used [xlwt][2] 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][4], from [Working with Spreadsheets using Python][3], which recommended [`openpyxl`][5] 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][9](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][6], the `typing` built-in module, and the rules by [PEP8][7].

- 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 &mdash; Bootstrap for this project) is also assumed.

## Source code

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

{% github Sirneij/django_excel %}

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 &mdash; mine was `django_excel` &mdash; start a virtual environment, start a django project &mdash; called mine `django_excel` as well &mdash; and create a django application &mdash; my app was named `core`. Then proceed to opening your project's settings in your preferred IDE/text editor. Add the following configurations:

```python
# 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:

```python
# 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:

```python
# 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][8] with few modifications to suit our project. Then proceed to the `__init__.py` file in that same directory and turn it into:

```python
# 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:

```python
...
# 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:

```python
# 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:

```python
# 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 &mdash; 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][10] 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 &mdash; we want it to run automatically at a specified time &mdash; it was appended to our `CELERY_BEAT_SCHEDULE` earlier:

```python
# 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 &mdash; `export_data_to_excel` &mdash; 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.

```python
# 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][11] to send post requests and data was passed via `body` option of the Request object as shown in the template:

```html
<!--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:

```python
# 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:

```bash
(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:

```bash
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:

```python
# 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:

```python
# 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`:

```python
# 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:

```python
# 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](https://www.linkedin.com/in/idogun-john-nelson/) and Twitter: [Twitter](https://twitter.com/Sirneij).

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

[1]: https://simpleisbetterthancomplex.com/tutorial/2016/07/29/how-to-export-to-excel.html 'How to Export to Excel'
[2]: https://pypi.org/project/xlwt/ 'xlwt'
[3]: https://medium.com/swlh/working-with-spreadsheets-using-python-903202509407#:~:text=The%20package%20xlwt%20supports%20the,xlsx%20extensions%20of%20Excel.&text=After%20the%20installation%20is%20complete,it%20the%20name%20excelScript.py%20. 'Working with Spreadsheets using Python'
[4]: https://www.python-excel.org/ 'Working with Excel Files in Python'
[5]: https://openpyxl.readthedocs.io/en/stable/ 'openpyxl'
[6]: http://mypy-lang.org/ 'mypy'
[7]: https://peps.python.org/pep-0008/ 'PEP 8 – Style Guide for Python Code'
[8]: https://docs.celeryq.dev/en/stable/django/first-steps-with-django.html 'First steps with Django'
[9]: https://api.coingecko.com/api/v3/coins/ 'Coingecko api'
[10]: https://docs.djangoproject.com/en/4.0/ref/models/querysets/#get-or-create 'get-or-create'
[11]: https://developer.mozilla.org/en-US/docs/Web/API/fetch 'fetch API'