Published on

How to export large table in django-admin.

Overview

In my previous blog I've shared a really nice way to import large csv file in django-admin by customizing the default import action of django-import-export. This tutorial will also be on the similar lines and here we will try to export large table from django-admin. We are going to use the same tricks but with a little modification for this use case. Let's jump right into it. The code for this tutorial can be found in this github repo.

Introduction

The default export behaviour of django-import-export works really well but only for small table. What happens if we want to export 20k-50k rows, you will surely see a timeout error. So here I will present a solution to that by using AJAX calls to download large table in batches. I'm going to reuse the logic which I presented in last blog so please check it out also.

Create a template

Here I am skipping the project setup because it's really straightforward but still if you want to check it out then please take a look into my previous article.

Here are the models we will be working with.

models.py
from django.db import models

# Create your models here.
class Characters(models.Model):
    name = models.CharField(max_length=255,null=True,blank=True)
    height = models.FloatField(null=True, blank=True)
    mass = models.FloatField(null=True, blank=True)
    hair_color = models.CharField(max_length=25,null=True, blank=True)
    skin_color = models.CharField(max_length=25,null=True, blank=True)
    eye_color = models.CharField(max_length=25,null=True, blank=True)
    birth_year = models.CharField(max_length=25,null=True, blank=True)
    gender = models.CharField(max_length=25,null=True, blank=True)

    class Meta:
        verbose_name = "Characters"

    def __str__(self):
        return str(self.name)

In your admin you have to override the default export action from django-import-export.

admin.py
# your imports
from django.shortcuts import render
from starwars import models
from import_export.admin import ImportExportModelAdmin

@admin.register(models.Characters)
class CharactersAdmin(ImportExportModelAdmin,admin.ModelAdmin):
    list_display = ("name","birth_year","gender")

    # global variables to improve performance
    export_qs = None
    total_count = 0
    charactes = []

    def export_action(self,request):

        # define the queryset you want to export and get the count of rows
        self.total_count = models.Characters.objects.all().count()
        context = {"total_count":self.total_count,"form_title":"Export Characters to csv file",
                   "description":"",
                   "headers":["Name","Height","Mass","Birth Year","Gender"],
                   "endpoint":"/admin/starwars/characters/export/",
                   "fileName":"starwars_characters"}
        return render(
            request, "admin/export_data_to_csv.html", context
        )

Here we are telling the template that there are total_count(20k) number of rows, so that we can start the paginated calls.

Add the follwing template to your templates folder. You can find the complete code for the template here.

export_starwars_characters.html
<div>
  {% csrf_token %}
  <h1>{{form_title}}</h1>
  <h2>{{description}}</h2>
  <br />
  <br />
  <h1>Preview Export <span id="file_name"></span></h1>
  <div
    style="display: flex; justify-content: space-between; margin-bottom: 10px"
  >
    <span style="font-weight: bold; font-size: 18px"
      >Total entries: <span id="entries">{{total_count}}</span></span
    >
    <button class="downloadBtn" id="downloadReport" style="display: none">
      Download
    </button>
  </div>
  <table>
    <tr>
      {% for header in headers %}
      <th>{{header}}</th>
      {% endfor %}
    </tr>
  </table>
  <div id="spinner" class="loader" style="display: none"></div>
</div>
{{fileName|json_script:"fileName"}}
<br />
{{endpoint|json_script:"endpoint"}}

Add Jquery and AJAX

Now that we know we have the number of rows, we will start the pagination and call the AJAX requests in which we will send the offset and limit values. This way we can tell our admin to paginate the whole queryset accordigly.

Here I have presented the main AJAX call that happens in loop, you can find the complete logic in the github repo.

export_starwars_characters.html
// rest of the variables.
let endpoint = JSON.parse($('#endpoint').text())

function callExportRequest(pageObj) {
  let deferred = $.Deferred()
  let offset = pageObj.offset
  let limit = pageObj.limit

  $.ajax({
    type: 'POST',
    dataType: 'json',
    url: endpoint,
    data: {
      offset: JSON.stringify(offset),
      limit: JSON.stringify(limit),
      csrfmiddlewaretoken: $('input[name=csrfmiddlewaretoken]').val(),
      action: 'post',
    },
    beforeSend: () => {
      $('#spinner').show()
    },
    success: function (data) {
      // here we will update our DOM to show the rows from queryset

      data.results.forEach((obj)=>{
          let cols = "",
          Object.entries(obj).map(([key,val])=>{
              cols = cols + `<td>${val}</td>`;
          });
          $('table').append(`<tr class="child">${cols}</tr>`);
      });
      deferred.resolve(data)
    },
    complete: () => {
      $('#spinner').hide()
    },
  })
  return deferred.promise()
}

The above function is a simple ajax call that sends offset and limit varaibles to django view and in return gets the rows to be exported. To call this function the logic is really simple which is to make n pages of the total_count and then call the api sequentially.

Custom export logic

The only step left right now is to write our custom export logic which can easily be achieved by overriding the default export_action method. Let's see how to do it.

admin.py
# global variables to improve performance
export_qs = None
total_count = 0
characters = []

def export_action(self, request):
    if request.method == 'POST':
        offset = json.loads(request.POST.get('offset'))
        limit = json.loads(request.POST.get('limit'))
        self.characters = []
        if not self.export_qs:
            self.export_qs = models.Characters.objects.all().values_list("name", "height", "mass", "birth_year", "gender")

        for obj in self.export_qs[offset:limit]:
            self.characters.append({
                "name": obj[0],
                "height": obj[1],
                "mass": obj[2],
                "birth_year": obj[3],
                "gender": obj[4]
            })

        context = {
            "results": self.characters
        }
        return HttpResponse(json.dumps(context), content_type="application/json")

    # define the queryset you want to export and get the count of rows
    self.total_count = models.Characters.objects.all().count()
    context = {"total_count": self.total_count, "form_title": "Export Characters to csv file",
                "description": "",
                "headers": ["Name", "Height", "Mass", "Birth Year", "Gender"],
                "endpoint": "/admin/starwars/characters/export/",
                "fileName": "starwars_characters"}
    return render(
        request, "admin/export_starwars_characters.html", context
    )

As you can see it is fairly simple to do and we can resuse the same template in other models as well. An important point to notice here is that we are saving query calls to the database as we are caching the queryset in export_qs variable.

Conclusion

This tutorial demonstrates how you can write a custom template to export large number of rows from django-admin in a csv format (Of course it can be done in excel as well). Also the logic is totally reusable and implemented on other admin models/tables. I hope you learned something new in this tutorial and if so then please like and share it with your friends and colleagues. Thanks for reading!