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.
Then create a view that prepares data and writes it to the XLS file
After that, create a route for this view.
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.
Validate user before generating the file:
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.