How to export orders in to XLSX file

Michał Dziadowicz
Michał Dziadowicz Python Developer

Read time: 9 min

Aug 04, 2020

Running a successful e-commerce site requires constantly monitoring sales performance. The ability to export data for analyzing and visualization in popular formats that can be shared is crucial. The simplest way is to export data in CSV format that can be read by plethora of tools. However if there is a need to control how data is presented, using format that embeds formating alongside is better. Exporting data in Excel XLSX format allows changing font family, font colors, columns size, compute values using formulas and so on. While the team is working on adding the ability to export various elements in CSV, we can build our own XLS exporter.

The dashboard uses GraphQL, which means you cannot return data in another format than JSON. Instead of returning the XLS file, we can save the file on disk and return the link for download. Another way is to skip the GraphQL part and add a new view that returns the generated file.

There two main packages to choose from: openpyxl and xlsxwriter (and xlwt if there is a need for an older binary format xls). Each package has its pros and cons but general usage is the same. Create a workbook, add a worksheet to it and populate worksheet with data. Then save the file on disk or return it in HTTP response.

First, install a package that adds XLSX support to python.

pip install XlsxWriter

Then create a view that prepares data and writes it to the XLS file

import io

import xlsxwriter

from django.http import HttpResponse
from saleor.order.models import Order

def export_orders(request):
    file = io.BytesIO()

    # automatically remove timezone 
    workbook = xlsxwriter.Workbook(file, {'remove_timezone': True})
    worksheet = workbook.add_worksheet('Orders')

    headers = ['Email', 'Quantity', 'Order status']

    # add default formatting for dates
    date_format = workbook.add_format({'num_format': 'mm/dd/yyyy', 'align': 'left'})

    # set custom width for columns
    worksheet.set_column(0, 0, 30)
    worksheet.set_column(1, 1, 20)
    worksheet.set_column(2, 2, 20)

    for col, header in enumerate(headers):
        worksheet.write(0, col, header)

    for row, order in enumerate(Order.objects.all()):
        row += 1

        worksheet.write(row, 0, order.user_email)
        worksheet.write(row, 1, order.get_total_quantity())
        worksheet.write(row, 2, order.status)


    filename = 'orders.xlsx'

    response = HttpResponse(
    response['Content-Disposition'] = 'attachment; filename="{}"'.format(filename)

    return response

After that, create a route for this view.

from django.urls import path

from . import view

urlpatterns = [
    path(“export/orders/” views.export_orders, name=”export-orders”)

and register the newly created app in Visiting /export/orders/ url should respond with xlsx file. However anyone that knows this url can download a file. The next step is to add permissions checking. Start by checking if request contains proper JWT token, if so decode it and compare user with theirs permissions.

from graphql_jwt.utils import jwt_decode

from saleor.account.models import User
from saleor.core.permissions import OrderPermissions


def get_token_from_request(request):
    auth = request.META.get(JWT_AUTH_HEADER, "").split(maxsplit=1)

    if len(auth) != 2 or auth[0].upper() != prefix:
        return None
    return auth[1]

def has_user_permission(request, permission):
    token = get_token_from_request(request)
    payload = jwt_decode(token)
    user = User.objects.filter(email=payload["email"], is_active=True).first()
    return user.has_perm(permission)

Validate user before generating the file:

def export_orders(request):
    if not has_user_permission(request, OrderPermissions.MANAGE_ORDERS):
            return HttpResponse('Unauthorized', status=401)

Exporting data in excel format is a straightforward task. All libraries for manipulating xlsx files have similar conversions, and APIs for creating workbooks and familiarity with one makes switching to another easy. The presented method can be used to export all sorts of data from the saleor database.

Michał Dziadowicz
Michał Dziadowicz The high-skilled software engineer who is constantly developing his skills and competency within the challenging projects.

Read time: 9 min

Aug 04, 2020

Schedule /a meeting/

with our specialist Michał Maj

Michał Maj
Michał Maj Business Development Manager

Read more from our brave's writers

Our mission is to be /trusted partner
to our clients/
in the field of webplatform
development & staff augmentation

Check our rank

  • development
  • clutch
Item 1 of 3

0 Professionals

0 Finished project


  • “Not afraid of doing even the most difficult tasks and proposing innovative solutions. They don’t avoid challenges, rely on new technologies, and - most importantly - always carry the projects through from the beginning to the end.”

    Adam Bogdan
    Adam Bogdan Python Developer
Item 1 of 8

/keep in touch/

We're happy to help! Leave your contact data, and we will get in touch with you within one business day.

Tell us about your needs, ask about our experience, portfolio, and even partnership programs.

Contact us

Thinking of

We are on constant lookout for new talents to join our Brave team! Don’t wait and check our latest Job openings!