Rendering xlsx files in Django Rest Framework

Renderers in Django Rest Framework add versatility to an API. They are magical! APIs generally provide serialized data as JSON.

When there is a use case to save an API's response as an excel file, drf-renderer-xlsx comes in handy. Let's dive in.

Consider a simple API view:

from django.contrib.auth.models import User
from rest_framework import permissions
from .serializers import UserSerializer
from rest_framework.views import APIView
from rest_framework.response import Response
from rest_framework import status


class UserView(APIView):
    """
    API endpoint that allows users to be viewed.
    """
    permission_classes = [permissions.IsAuthenticated]

    @staticmethod
    def get(request):
        queryset = User.objects.all()
        serializer = UserSerializer(queryset, many=True)

        return Response(data=serializer.data, status=status.HTTP_200_OK)

Consider the UserSerializer defined thus:

from django.contrib.auth.models import User
from rest_framework import serializers


class UserSerializer(serializers.ModelSerializer):
    class Meta:
        model = User
        fields = ['username', 'email']

This view simply returns username and email address of all users in our app setup. If we were to test run this API:

(drf_xlsx_renderer_tutorial) $ curl -H 'Accept: application/json; indent=4' -u admin  http://127.0.0.1:8000/users/
Enter host password for user 'admin':
[
    {
        "username": "admin",
        "email": "admin@example.com"
    },
    {
        "username": "Rama",
        "email": "rama@example.com"
    },
    {
        "username": "Bheema",
        "email": "bheema@example.com"
    },
    {
        "username": "Soma",
        "email": "soma@example.com"
    }
]
(drf_xlsx_renderer_tutorial) $

To have this data written to an xlsx file, all we need to do is:

  1. Install drf-renderer-xlsx module.
  2. Import the renderers required.
  3. Specify the renderer classes

So our API view becomes:

from django.contrib.auth.models import User
from rest_framework import permissions
from .serializers import UserSerializer
from rest_framework.views import APIView
from rest_framework.response import Response
from rest_framework import status
from rest_framework.renderers import JSONRenderer
from drf_renderer_xlsx.renderers import XLSXRenderer
from drf_renderer_xlsx.mixins import XLSXFileMixin

class UserView(APIView):
    """
    API endpoint that allows users to be viewed.
    """
    permission_classes = [permissions.IsAuthenticated]
    renderer_classes = [JSONRenderer, XLSXRenderer, XLSXFileMixin]

    @staticmethod
    def get(request):
        queryset = User.objects.all()
        serializer = UserSerializer(queryset, many=True)

        return Response(data=serializer.data, status=status.HTTP_200_OK)

And the magic is set!

Now, lets test it:

(drf_xlsx_renderer_tutorial) $ curl -H 'Accept: application/xlsx' -u admin  http://127.0.0.1:8000/users/ --output users.xlsx
Enter host password for user 'admin':
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  4786  100  4786    0     0  35451      0 --:--:-- --:--:-- --:--:-- 35451
(drf_xlsx_renderer_tutorial) $ ls -ltr *.xlsx
-rw-rw-r-- 1 hnhegde hnhegde   4786 May 21 14:19 users.xlsx
(drf_xlsx_renderer_tutorial) $

That's it! Excel file is ready! That's the magic of renderers!

Note that, if renderer_classes isn't specified, then JSON is the default rendering format. However, once renderer_classes are specified, JSONRenderer must also be listed in the array.

Full source of this illustration is available at: drf_xlsx_renderer_tutorial

More information at: