Creating Excel using Django-REST-Pandas

Question

Background: I am using Django REST Framework (DRF) with Django REST Pandas (DRP) as the requirement is to have export to excel feature in the application.

Question: How to generate the excel workbook being downloaded by adding

1. Data with customized headers
2. Adding chart in the workbook
3. Adding worksheets.
4. Specifying name of file being downloaded

I am looking for small example (tutorial) links illustrating hooks in DRP to do the aforementioned tasks.

Why have I put this open question here? Asking here as Google searches haven't given any worthy results so far. You can also recommend any better module than DRP which will be elegant to achieve the goals.

Answer 1

You can do this with Django REST Pandas by extending PandasExcelRenderer and overriding the get_pandas_kwargs() function. This function can return any of the arguments for DataFrame.to_excel()

class CustomExcelRenderer(PandasExcelRenderer):
    def get_pandas_kwargs(self, data, renderer_context=None):
        return {
            'sheet_name': 'Custom Name',
        }

Edit: the one special case is the first to_excel() argument (excel_writer), which is handled separately in get_pandas_args():

class CustomExcelRenderer(PandasExcelRenderer):
    def get_pandas_args(self, data):
        writer = ExcelWriter(self.filename)  # Initialize with temp file
        # (Custom chart commands...)
        return [writer]

For changing the output filename you'd need to set a header in the view, probably by overriding the list() method:

class ExcelExportViewSet(PandasViewSet):
    renderer_classes = [CustomExcelRenderer]

    def list(self, request, *args, **kwargs):
        response = super().list(request, *args, **kwargs)
        # (compute filename)
        response['Content-Disposition'] = 'attachment;filename=' + filename

Answer 2

Try to use openpyxl for generating workbooks. Here is example code for Django to download file:

from openpyxl.writer.excel import save_virtual_workbook
from openpyxl import Workbook


def download_excel(request):
    if request.method == "GET":
        filename = "XLS import.xlsx"
        wb = Workbook()
        response = HttpResponse(save_virtual_workbook(wb), content_type="application/ms-excel")
        response["Content-Disposition"] = 'attachment; filename="' + filename + '"'
        return response
    else:
        return HttpResponse("Error")