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 ?