XlsxWriter/OpenPyXl object save as HttpResponse to create download

Question

I've been scratching my head far too long with this one. Here it goes -

I am trying to export a user model class(as excel sheet), after making some changes to it then returning it as HttpResponse object for download. Here is my view code:

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)

I followed the approach given in the third answer given in this post XlsxWriter object save as http response to create download in Django, but no luck. I don't get the data-validation changes made here -

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. the dropdown I am trying to create does not reflect in my downloaded students.xls file.

In the second answer of the same post XlsxWriter object save as http response to create download in Django , the author has given an example of creating a new workbook and downloading it with the changes done using BytesIO. I wonder can I use this approach with an existing workbook using BytesIO?

I have also tried using OpenPyXl library for this task, as it turns out no luck with that either. I found this post with an approach using OpenPyXl here Return openpyxl workbook object as HttpResponse in django. Is it possible? , One interesting thing i found was

from openpyxl.writer.excel import save_virtual_workbook

I was wondering is there a similar thing like save_virtual_workbook for xlsxwriter library so I can save my workbook in the response like

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

Can someone please help me out with this problem?

You don't write the workbook to the repsonse. – Charlie Clark Dec 15 '18 at 14:32 — Charlie Clark, Dec 15 '18 at 14:32
then how am i supposed to download it? – utkarsh2k2 Dec 15 '18 at 14:39 — utkarsh2k2, Dec 15 '18 at 14:39

score 3 · Accepted Answer · answered Dec 20 '18 at 08:54

Use BytesIO() and worksheet.writer to possible to instantiate existing query set and below code should work and give you dropdown to excel file

    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)

Ausaf · Answer 2 · 2018-12-15T19:37:42.707

1

I guess it should be response['Content-Type'] and not response['content_type']

edit:

Following is what works for me,

from xlwt import Workbook
def render_excel_report(request):
    if request.GET:
        input_form = ReportInputForm(request.GET)
        if input_form.is_valid():
            wb = Workbook()
            ...

            # Create a response object
            response = HttpResponse(mimetype='application/vnd.ms-excel')
            response['Content-Disposition'] = '''attachment; filename="download.xls"'''

            # Save the workbook to response object
            wb.save(response)

            # Return the response object
            return response

    else:
        ...

Note: I am using XLWT

edited Dec 15 '18 at 19:37

answered Dec 15 '18 at 18:53

Ausaf

197
9

thanks for the edited answer.. however i was wondering XLWT `workbook()` accepts the response like it does in xlsxwriter `workbook = xlsxwriter.Workbook(response, {'in_memory': True})` – utkarsh2k2 Dec 16 '18 at 14:30
also is it possible to add datavalidation type list using XLWT? if yes can u give me an example. I cannot find any proper documentation for XLWT – utkarsh2k2 Dec 17 '18 at 06:51

score 0 · Answer 3 · answered Sep 16 '20 at 13:43

The save_virtual_workbook() is deprecated.

You should use python's tempfile.NamedTempFile class with BytesIO.

from tempfile import NamedTemporaryFile

responseStream = None

with NamedTemporaryFile() as tmpFile:
    wb.save(tmpFile.name)
    tmpFile.seek(0)
    responseStream = BytesIO(tmpFile.read())

return flask.send_file(
    responseStream, as_attachment=True,
    attachment_filename = 'report.xlsx',
    mimetype = wb.mime_type
)

Python manages the automatic cleanup of NamedTemporaryFile files.

Read: OpenPyXl documentation

XlsxWriter/OpenPyXl object save as HttpResponse to create download

3 Answers3