Как сделать функцию загрузки excel в python

1. The code below is a function to download a table as an Excel file. Table is being merged using DataTable jquery. The problem with the code below is that only two rows are compared and merged. I want to merge the whole data. Please help.

2. The reason that 'register_date_str' is printed twice in values_list is because in the case of values_list, student[0] cannot be used twice. In this case, the value is not output when downloading Excel. What's the best way to use Excel without printing the same value to valuse_list twice?

3. worksheet.write(row, col, str(student[2]) + '-' + str(student[3])) -> Only the 'name' value is printed, not combining two values ​​in one cell. Why?

    def download_excel:
        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet()
   
        d_format = '%Y-%m-%d'
        border_center = workbook.add_format({'align': 'center', 'border': 1, 'bold': 1})
        bold = workbook.add_format({'bold': True, 'border': 1, 'align': 'center', 'valign': 'vcenter', 'text_wrap': True})
   
   
        student_header = [["weekday name", 5],
                          ["register_date", 10],
                          ["name - sex", 30],
                          ["phone_number", 15],
                          ["class_name", 15],
                          ["teacher", 15]]
   
        row = 0
        col = 0
        for a_header, width in student_header:
            worksheet.write(row, col, a_header, border_center)
            if width is not None:
                worksheet.set_column(col, col, width)
            col += 1 
   
        students = Student.objects.filter(is_deleted=False).order_by('register_date') \
            .annotate(register_date_str=Cast('register_date', CharField())) \
            .values_list('register_date_str', 'register_date_str', 'name', 'sex', 'phone_number', 'class_name', 'teacher')
   
        row += 1
        col = 0
        for student in students:
            worksheet.write(row, col, student[0])
            col += 1
            worksheet.write(row, col, student[1])
            col += 1
            worksheet.write(row, col, str(student[2]) + '-' + str(student[3]))
            col += 1
            worksheet.write(row, col, student[4])
            col += 1
            worksheet.write(row, col, student[5])
            col += 1
            worksheet.write(row, col, student[6])
   
            row += 1
            col = 0
   
            abc_dict = {0: 'A', 1: 'B', 2: 'C', 3: 'D', 4: 'E', 5: 'F'}
            for i in range(len(students)):
                if i == 0:
                    worksheet.write_row('A2', students[0], bold)
                else:
                    for j in range(len(students[0]) - 2):
                        if students[i][j] == students[i - 1][j]:
                            worksheet.merge_range(abc_dict[j] + str(i + 1) + ':' + abc_dict[j] + str(i + 2),
                                          students[i - 1][j], bold)
                        else:
                            worksheet.write_row('A' + str(i + 2), students[i], bold)
   
        workbook.close()
   
        output.seek(0)
   
        filename = 'student_list.xlsx'
        try:
            filename.encode('ascii')
            file_expr = 'filename="{}"'.format(filename)
        except UnicodeEncodeError:
            file_expr = "filename*=utf-8''{}".format(quote(filename))
   
        response = HttpResponse(output,
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = 'attachment; ' + file_expr
   
        return response
   

Вернуться на верх