1
\$\begingroup\$

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 ?

\$\endgroup\$
2
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ First, thank you very much with your answer ! Now I have an issue: 'Competence' object is not subscriptable on this line: worksheet.write(row, i, attr_getter(item)) \$\endgroup\$ – Essex Jun 12 at 15:10
  • \$\begingroup\$ @Essex Please see the updated code. itemgetter -> attrgetter. \$\endgroup\$ – Peilonrayz Jun 12 at 15:22
  • \$\begingroup\$ I updated the code : 'Competence' object has no attribute 'item' in worksheet.write(row, i, attr_getter(item)). I have to see what doesn't work. \$\endgroup\$ – Essex Jun 13 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.