Login

ExcelResponse

Author:
Tarken
Posted:
October 23, 2008
Language:
Python
Version:
1.0
Score:
13 (after 13 ratings)

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

  1. Template tag - list punctuation for a list of items by shapiromatron 9 months ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 9 months, 1 week ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 4 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 4 months ago
  5. Help text hyperlinks by sa2812 1 year, 5 months ago

Comments

guettli (on October 30, 2008):

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.

#

eiriks (on November 8, 2010):

Sweet! This is beautiful! Thanks a bunch.

#

haptork (on March 16, 2012):

Nice Code. Thanks!

Used it and modified it a bit for custom column headers. If anybody wants that code then lemme know.

#

haptork (on March 21, 2012):

shows an IndexError at line#19 if the data is empty query set.

#

Wladislav (on March 16, 2017):

ValuesQuerySet is no longer supported and deprecated, will the snippet be rewritten for Django 1.10?

#

Please login first before commenting.