A subclass of HttpResponse
which will transform a QuerySet
, or sequence of sequences, into either an Excel spreadsheet or CSV file formatted for Excel, depending on the amount of data. All of this is done in-memory and on-the-fly, with no disk writes, thanks to the StringIO library.
Requires: xlwt
Example:
def excelview(request):
objs = SomeModel.objects.all()
return ExcelResponse(objs)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | import datetime
from django.db.models.query import QuerySet, ValuesQuerySet
from django.http import HttpResponse
class ExcelResponse(HttpResponse):
def __init__(self, data, output_name='excel_data', headers=None,
force_csv=False, encoding='utf8'):
# Make sure we've got the right type of data to work with
valid_data = False
if isinstance(data, ValuesQuerySet):
data = list(data)
elif isinstance(data, QuerySet):
data = list(data.values())
if hasattr(data, '__getitem__'):
if isinstance(data[0], dict):
if headers is None:
headers = data[0].keys()
data = [[row[col] for col in headers] for row in data]
data.insert(0, headers)
if hasattr(data[0], '__getitem__'):
valid_data = True
assert valid_data is True, "ExcelResponse requires a sequence of sequences"
import StringIO
output = StringIO.StringIO()
# Excel has a limit on number of rows; if we have more than that, make a csv
use_xls = False
if len(data) <= 65536 and force_csv is not True:
try:
import xlwt
except ImportError:
# xlwt doesn't exist; fall back to csv
pass
else:
use_xls = True
if use_xls:
book = xlwt.Workbook(encoding=encoding)
sheet = book.add_sheet('Sheet 1')
styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
'default': xlwt.Style.default_style}
for rowx, row in enumerate(data):
for colx, value in enumerate(row):
if isinstance(value, datetime.datetime):
cell_style = styles['datetime']
elif isinstance(value, datetime.date):
cell_style = styles['date']
elif isinstance(value, datetime.time):
cell_style = styles['time']
else:
cell_style = styles['default']
sheet.write(rowx, colx, value, style=cell_style)
book.save(output)
mimetype = 'application/vnd.ms-excel'
file_ext = 'xls'
else:
for row in data:
out_row = []
for value in row:
if not isinstance(value, basestring):
value = unicode(value)
value = value.encode(encoding)
out_row.append(value.replace('"', '""'))
output.write('"%s"\n' %
'","'.join(out_row))
mimetype = 'text/csv'
file_ext = 'csv'
output.seek(0)
super(ExcelResponse, self).__init__(content=output.getvalue(),
mimetype=mimetype)
self['Content-Disposition'] = 'attachment;filename="%s.%s"' % \
(output_name.replace('"', '\"'), file_ext)
|
More like this
- Template tag - list punctuation for a list of items by shapiromatron 9 months ago
- JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 9 months, 1 week ago
- Serializer factory with Django Rest Framework by julio 1 year, 4 months ago
- Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 4 months ago
- Help text hyperlinks by sa2812 1 year, 5 months ago
Comments
Hi,
nice recipe.
BTW: you can send excel a HTML table, too. If the extension (IE ignores mime-types) is .xls, the HTML will get imported by excel automatically.
This makes it possible to store hyper links (href="..") inside cells.
Be sure that the url ends with .xls, otherwise IE might not open excel.
#
Sweet! This is beautiful! Thanks a bunch.
#
Nice Code. Thanks!
Used it and modified it a bit for custom column headers. If anybody wants that code then lemme know.
#
shows an IndexError at line#19 if the data is empty query set.
#
ValuesQuerySet is no longer supported and deprecated, will the snippet be rewritten for Django 1.10?
#
Please login first before commenting.