Django export to xls format

Question

I'm working on a Django project and I'm wondering if there is any way to improve the process time to export my data to xls format.

Actually, it takes between 43 seconds and 49 seconds to create the file and download it. I would like to know if my code could be improved in order to save time.

This is my function:

def get(self, request):

    output = io.BytesIO()

    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('Competence List')
    row = 0
    worksheet.write(row, 0, 'ID')
    worksheet.set_column('A:B', 8)
    worksheet.write(row, 1, 'OMCL ID')
    worksheet.write(row, 2, 'OMCL Code')
    worksheet.set_column('C:C', 25)
    worksheet.write(row, 3, 'OMCL Name')
    worksheet.set_column('D:D', 80)
    worksheet.write(row, 4, 'Country Code')
    worksheet.set_column('E:E', 12)
    worksheet.write(row, 5, 'Last Name')
    worksheet.set_column('F:G', 15)
    worksheet.write(row, 6, 'First Name')
    worksheet.write(row, 7, 'Email')
    worksheet.set_column('H:H', 40)
    worksheet.write(row, 8, 'Title')
    worksheet.set_column('I:I', 8)
    worksheet.write(row, 9, 'Test Method ID')
    worksheet.set_column('J:J', 15)
    worksheet.write(row, 10, 'Name')
    worksheet.set_column('K:K', 65)
    worksheet.write(row, 11, 'Type')
    worksheet.set_column('L:L', 6)

    worksheet.write(row, 12, 'Sub Method ID')
    worksheet.set_column('M:M', 14)
    worksheet.write(row, 13, 'Group Name')
    worksheet.set_column('N:N', 20)
    worksheet.write(row, 14, 'Name')
    worksheet.set_column('O:O', 60)
    worksheet.write(row, 15, 'Ref Gen Chap Ph.Eur.')
    worksheet.set_column('P:T', 20)

    worksheet.write(row, 16, 'Competence Level')
    worksheet.write(row, 17, 'Competence Level')
    worksheet.write(row, 18, 'Frequency Level')
    worksheet.write(row, 19, 'Frequency Level')

    worksheet.set_column('U:Z', 15)
    worksheet.write(row, 20, 'Animals')
    worksheet.write(row, 21, 'Product Class')
    worksheet.write(row, 22, 'Manufacturer')
    worksheet.write(row, 23, 'BSP')
    worksheet.write(row, 24, 'CAP')
    worksheet.write(row, 25, 'MSS')
    worksheet.write(row, 26, 'QMS Covered')
    worksheet.write(row, 27, 'Last PTS Year')
    worksheet.write(row, 28, 'OCABR')
    worksheet.write(row, 29, 'Run Acc Ph.Eur.')
    worksheet.write(row, 30, 'Comment')
    worksheet.write(row, 31, 'Subcontracted')
    worksheet.set_column('AA:AA', 35)
    worksheet.write(row, 32, 'Counterfeit/Illegal Medicine Testing')
    worksheet.set_column('AB:AB', 15)
    worksheet.write(row, 33, '3R programme')

    worksheet.set_column('AC:AF', 20)
    worksheet.write(row, 34, 'Created')
    worksheet.write(row, 35, 'Created By')
    worksheet.write(row, 36, 'Modified')
    worksheet.write(row, 37, 'Modified By')

    objects = Competence.objects.filter(
        id__in=self.request.session['result_recordset']
        ).select_related(
        'omcl', 'method', 'sub_method', 'sub_method__sub_method', 'sub_method__group', 'created_by',
        'omcl__country'
        ).annotate(
            concat_animals=StringAgg('animals__name', ';', True),
            concat_products=StringAgg('product_classes__name', ';', True),
            concat_manufacturers=StringAgg('manufacturers__name', ';', True),
            concat_bsp=StringAgg('bsp_items__code', ';', True),
            concat_cap=StringAgg('cap_items__code', ';', True),
            concat_mss=StringAgg('mss_items__code', ';', True),
            concat_pts=StringAgg(Cast('pts_years__pts_year', CharField(max_length=4)), ';', True))

    contacts = Contact.objects.filter(type='OMCL')
    omcl_contacts = {x.omcl_id: x for x in contacts}

    row += 1
    for item in objects:
        omcl_contact = omcl_contacts.get(item.omcl.id, None)
        worksheet.write(row, 0, item.pk)
        worksheet.write(row, 1, item.omcl.id)
        worksheet.write(row, 2, item.omcl.code)
        worksheet.write(row, 3, item.omcl.name)
        worksheet.write(row, 4, item.omcl.country.code)
        if omcl_contact:
            last = omcl_contact.last_name if omcl_contact.last_name else ''
            first = omcl_contact.first_name if omcl_contact.first_name else ''
            worksheet.write(row, 5, last)
            worksheet.write(row, 6, first)
            worksheet.write(row, 7, omcl_contact.email)
            worksheet.write(row, 8, omcl_contact.title)

        worksheet.write(row, 9, item.method.pk)
        worksheet.write(row, 10, item.method.name)
        worksheet.write(row, 11, item.method.type)

        if item.sub_method:
            worksheet.write(row, 12, item.sub_method.id)
            worksheet.write(row, 13, item.sub_method.group.name)
            worksheet.write(row, 14, item.sub_method.sub_method.name)
            worksheet.write(row, 15, item.method.reference_pheur)

        worksheet.write(row, 16, item.level)
        worksheet.write(row, 17, item.get_level())
        worksheet.write(row, 18, item.frequency)
        worksheet.write(row, 19, item.get_frequency())

        worksheet.write(row, 20, item.concat_animals)
        worksheet.write(row, 21, item.concat_products)
        worksheet.write(row, 22, item.concat_manufacturers)
        worksheet.write(row, 23, item.concat_bsp)
        worksheet.write(row, 24, item.concat_cap)
        worksheet.write(row, 25, item.concat_mss)

        worksheet.write(row, 26, item.qms_covered)

        worksheet.write(row, 27, item.concat_pts)

        worksheet.write(row, 28, item.ocabr)
        worksheet.write(row, 29, item.run_acc_pheur)
        worksheet.write(row, 30, item.comment)
        worksheet.write(row, 31, item.is_subcontracted)
        worksheet.write(row, 32, item.is_illegal_med_test)
        worksheet.write(row, 33, item.is_three_r)
        locale = timezone.localtime(item.creation_date)
        worksheet.write(row, 34, date_format(locale, 'DATETIME_FORMAT'))
        worksheet.write(row, 35, six.u(str(item.created_by)))
        locale = timezone.localtime(item.modification_date)
        worksheet.write(row, 36, date_format(locale, 'DATETIME_FORMAT'))
        worksheet.write(row, 37, six.u(str(item.modified_by)))

        row += 1
    workbook.close()

    output.seek(0)
    response = HttpResponse(output.read(),
                            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename="competence_list.xlsx"'
    return response

Maybe by using xlwt library ?

Answer 1

You have a lot of lines like this:

worksheet.write(row, 0, item.pk)

This means you should use a for loop. Take:

for i, attr in enumerate(...):
    worksheet.write(row, i, item???attr)

But what should item???attr be? With operator.attrgetter it's pretty simple.

Take the following example:

>>> class Test:
        value = '{} world!'

>>> Test.value.format('Hello')
'Hello world!'
>>> operator.attrgetter('value.format')(Test)('Hello')
'Hello world!'

From this it's easy to see that the loop can be:

for i, attr in enumerate(...):
    worksheet.write(row, i, operator.attrgetter(attr)(item))

---

Assuming that worksheet.set_column can be called after all the worksheet.writes your code can be simplified.

TITLES = [
    'ID',
    'OMCL ID',
    ...
]

COLUMNS = [
    ('A:B', 8),
    ('C:C', 25),
    ...
]

ATTRS = [
    (1, 'pk'),
    (2, 'ocml.id'),
    ...
]

def get(self, request):

    output = io.BytesIO()

    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('Competence List')

    row = 0
    for i, name in enumerate(TITLES):
        worksheet.write(row, i, 'ID')

    for column, value in COLUMNS:
        worksheet.set_column(column, value)

    objects = Competence.objects.filter(
        id__in=self.request.session['result_recordset']
        ).select_related(
        'omcl', 'method', 'sub_method', 'sub_method__sub_method', 'sub_method__group', 'created_by',
        'omcl__country'
        ).annotate(
            concat_animals=StringAgg('animals__name', ';', True),
            concat_products=StringAgg('product_classes__name', ';', True),
            concat_manufacturers=StringAgg('manufacturers__name', ';', True),
            concat_bsp=StringAgg('bsp_items__code', ';', True),
            concat_cap=StringAgg('cap_items__code', ';', True),
            concat_mss=StringAgg('mss_items__code', ';', True),
            concat_pts=StringAgg(Cast('pts_years__pts_year', CharField(max_length=4)), ';', True))

    contacts = Contact.objects.filter(type='OMCL')
    omcl_contacts = {x.omcl_id: x for x in contacts}

    attrs = [(i, operator.attrgetter(a)) for i, a in ATTRS]

    for row, item in enumerate(objects, start=1):
        for i, attr_getter in attrs:
            worksheet.write(row, i, attr_getter(item))

        omcl_contact = omcl_contacts.get(item.omcl.id, None)
        if omcl_contact:
            worksheet.write(row, 5, omcl_contact.last_name or '')
            worksheet.write(row, 6, omcl_contact.first_name or '')
            worksheet.write(row, 7, omcl_contact.email)
            worksheet.write(row, 8, omcl_contact.title)

        worksheet.write(row, 17, item.get_level())
        worksheet.write(row, 19, item.get_frequency())

        locale = timezone.localtime(item.creation_date)
        worksheet.write(row, 34, date_format(locale, 'DATETIME_FORMAT'))
        worksheet.write(row, 35, six.u(str(item.created_by)))

        locale = timezone.localtime(item.modification_date)
        worksheet.write(row, 36, date_format(locale, 'DATETIME_FORMAT'))
        worksheet.write(row, 37, six.u(str(item.modified_by)))

    workbook.close()

    output.seek(0)
    response = HttpResponse(output.read(),
                            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename="competence_list.xlsx"'
    return response