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 responseAfter 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.
