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

utkarsh2k2

我已经用这个挠头了太久了。在这里-

在对用户模型类进行一些更改之后，我尝试导出用户模型类（作为excel表），然后将其作为HttpResponse对象返回以供下载。这是我的查看代码：

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对象另存为http响应，以在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()

即。我尝试创建的下拉列表未反映在我下载的students.xls文件中。

在同一篇文章XlsxWriter对象另存为http响应以在Django中创建下载的第二个答案中，作者给出了一个示例，该示例创建一个新工作簿并使用BytesIO完成更改后将其下载。我想知道是否可以将这种方法与使用BytesIO的现有工作簿一起使用？

我也尝试过使用OpenPyXl库执行此任务，因为事实证明这也没有运气。我在这里找到了使用OpenPyXl的方法的帖子，在django中将openpyxl工作簿对象作为HttpResponse返回。可能吗？，我发现的一件有趣的事是

from openpyxl.writer.excel import save_virtual_workbook

我想知道是否有类似save_virtual_workbookxlsxwriter库的内容，因此我可以将工作簿保存在响应中，例如

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

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

有点

使用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)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。