Menu
On a django site, I want to generate an excel file based on some data in the database.
I'm thinking of using xlwt, but it only has a method to save the data to a file. How can get the file to the HttpResponse object? Or maybe do you know a better library?
I've also found this snippet but it doesn't do what I need. All I want is a way to get the stream from the xlwt object to the response object (without writing to a temporary file)
971
Using xlwt module, one can perform multiple operations on spreadsheet. For example, writing or modifying the data can be done in Python. Also, the user might have to go through various sheets and retrieve data based on some criteria or modify some rows and columns and do a lot of work.
2019 update: xlwt doesn't support xlsx Format. XlsxWriter is 100% compatible with xlsx, well-maintained and has a good reputation. For reading xlsx files, you can use xlrd .
There are two methods on the Worksheet class to do this, write_merge and merge . merge takes existing cells and merges them, while write_merge writes a label (just like write ) and then does the same stuff merge does. Both take the cells to merge as r1, r2, c1, c2 , and accept an optional style parameter.
Here we are using openpyxl module to read Excel file in Django. First get the excel file from FILES in request and then get the desired worksheet from the workbook. Now iterate over the rows in worksheet and for each row iterate over the cells and read the value in each cell.
neat package! i didn't know about this
According to the doc, the save(filename_or_stream) method takes either a filename to save on, or a file-like stream to write on.
And a Django response object happens to be a file-like stream! so just do xls.save(response). Look the Django docs about generating PDFs with ReportLab to see a similar situation.
edit: (adapted from ShawnMilo's comment):
def xls_to_response(xls, fname): response = HttpResponse(mimetype="application/ms-excel") response['Content-Disposition'] = 'attachment; filename=%s' % fname xls.save(response) return response then, from your view function, just create the xls object and finish with
return xls_to_response(xls,'foo.xls') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
