django - XlsxWriter/OpenPyXl 对象另存为 HttpResponse 以创建下载

Question

我已经为这个挠头太久了。来了——

我正在尝试导出用户模型类(作为 excel 表)，对其进行一些更改然后将其作为 HttpResponse 对象返回以供下载。这是我的 View 代码:

if request.method == 'POST':
    form = ExportStudentscsv(request.POST)

    if form.is_valid():
        data = form.cleaned_data
        #get course from dropdown value
        course = data.get('course')
        # find course id based on course title
        courseid = Course.objects.get(title=course)
        #find groups using course id
        groups = Groups.objects.filter(course=courseid)

        desiredintake = data.get('desiredintake')
        intakeyear = data.get('intakeyear')

        user_resource = UserResource()
        queryset = User.objects.filter(desiredintake=desiredintake, intakeyear=intakeyear, role=4)
        if not queryset:
            return page_not_found(request, "Bad Request")

        dataset = user_resource.export(queryset)
        dataset.xls
        response = HttpResponse(dataset.xls, content_type='application/vnd.ms-excel')
        response['Content-Disposition'] = 'attachment; filename="students.xls"'

        workbook = xlsxwriter.Workbook(response, {'in_memory': True})
        worksheet = workbook.add_worksheet('Groups')
        worksheet.data_validation('B11', {'validate': 'list',
                                          'source': ['open', 'high', 'close']})
        workbook.close()

        response['content_type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        response['Content-Disposition'] = 'attachment; filename=students.xls'

        return response

    else:
        args = {'form': form}
        return render(request, 'epitaadmin/export_studentscsv.html', args)

我遵循这篇文章中给出的第三个答案中给出的方法 XlsxWriter object save as http response to create download in Django ，但没有运气。我没有得到这里所做的数据验证更改 -

workbook = xlsxwriter.Workbook(response, {'in_memory': True})
worksheet = workbook.add_worksheet('Groups')
worksheet.data_validation('B11', {'validate': 'list',
                                 'source': ['open', 'high', 'close']})
workbook.close()

IE。我试图创建的下拉列表没有反射(reflect)在我下载的students.xls 文件中。

在同一篇文章的第二个答案 XlsxWriter object save as http response to create download in Django ，作者给出了一个创建新工作簿并下载使用 BytesIO 完成的更改的示例。我想知道我可以将这种方法与使用 BytesIO 的现有工作簿一起使用吗？

我也尝试过使用 OpenPyXl 库来完成这项任务，因为它也没有运气。我在这里找到了这篇文章使用 OpenPyXl 的方法 Return openpyxl workbook object as HttpResponse in django. Is it possible? , 我发现一件有趣的事是

from openpyxl.writer.excel import save_virtual_workbook

我想知道有没有类似的东西 save_virtual_workbook对于 xlsxwriter 库，因此我可以将我的工作簿保存在响应中，例如

response = HttpResponse(content=save_virtual_workbook(workbook), mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

有人可以帮我解决这个问题吗？

Answer 1

使用 BytesIO() 和 worksheet.writer 可以实例化现有的查询集，下面的代码应该可以工作，并为您提供 excel 文件的下拉列表

    if request.method == 'POST':
    form = ExportStudentscsv(request.POST)

    if form.is_valid():
        data = form.cleaned_data
        #get course from dropdown value
        course = data.get('course')
        # find course id based on course title
        courseid = Course.objects.get(title=course)
        #find groups using course id
        groups = Groups.objects.filter(course=courseid)
        groupnames = []
        for group in groups:
            groupnames.append(group.name)

        desiredintake = data.get('desiredintake')
        intakeyear = data.get('intakeyear')

        user_resource = UserResource()
        queryset = User.objects.filter(desiredintake=desiredintake, intakeyear=intakeyear, role=4)
        if not queryset:
            return page_not_found(request, "Bad Request")

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        worksheet = workbook.add_worksheet()
        worksheet.write('A1', 'Username')
        worksheet.write('B1', 'Firstname')
        worksheet.write('C1', 'Lastname')
        worksheet.write('D1', 'Desiredintake')
        worksheet.write('E1', 'Intakeyear')
        worksheet.write('F1', 'StudentId')
        worksheet.write('G1', 'Course')
        worksheet.write('H1', 'CourseId')
        worksheet.write('I1', 'Groups')
        i = 2

        for obj in queryset:
            worksheet.write('A'+str(i), obj.username)
            worksheet.write('B'+str(i), obj.first_name)
            worksheet.write('C'+str(i), obj.last_name)
            worksheet.write('D'+str(i), str(obj.desiredintake))
            worksheet.write('E'+str(i), obj.intakeyear)
            worksheet.write('F'+str(i), obj.id)
            worksheet.write('G'+str(i), str(course))
            worksheet.write('H'+str(i), str(courseid.id))
            txt = 'Select a value from a drop down list'
            worksheet.write('I'+str(i), txt)
            worksheet.data_validation('I'+str(i), {'validate': 'list',
                                          'source': groupnames})

            i += 1
        # Close the workbook before sending the data.
        workbook.close()
        # Rewind the buffer.
        output.seek(0)
        # Set up the Http response.
        # filename = 'students.xlsx'
        response = HttpResponse(
            output,
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        # response['Content-Disposition'] = 'attachment; filename=%s' % filename
        response['Content-Disposition'] = 'attachment; filename="students.xlsx"'

        return response
    else:
        args = {'form': form}
        return render(request, 'epitaadmin/export_studentscsv.html', args)