How to export orders in Saleor.io to XLSX file

Bravelab
Bravelab Developers

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 Saleor.io team is working on adding the ability to export various elements in CSV, we can build our own XLS exporter.

The Saleor.io 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)

    workbook.close()

    file.seek(0)

    filename = 'orders.xlsx'

    response = HttpResponse(
        file,
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    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 settings.py. 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

JWT_AUTH_HEADER = 'HTTP_AUTHORIZATION'
JWT_AUTH_HEADER_PREFIX = 'JWT'


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

    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.

Bravelab
Bravelab Developer who is detail-oriented, enthusiastic about data analysis and highly focused on implementing modern solutions to help out business to grow.

Read time: 9 min

Aug 04, 2020

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

developmentclutchdevelopment
  • development
  • clutch
Item 1 of 3

0 Professionals

0 Finished project

Kudos.

  • “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 9

Contact.

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

This website stores cookies on your computer. These cookies are used to collect information about how you interact with our website and allow us to remember you. We use this information in order to improve and customize your browsing experience & for analytics & metrics about our visitors both on this website & other media.
To find out more about the cookies we use, see our Privacy Policy.