Login

Excel Spreadsheet Export

Author:
MasonM
Posted:
July 24, 2008
Language:
Python
Version:
.96
Score:
5 (after 5 ratings)

This is an example of a view that returns an Excel spreadsheet; the last ~7 lines are the relevant ones. It relies on Excel's ability to automatically import HTML (see http://support.microsoft.com/kb/165499 for more info). The spreadsheet.html template is just has one big <table> tag with all the data as table cells (no <html> or other surrounding tags are necessary).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
def generate_spreadsheet(request):
    """
    Generates an Excel spreadsheet for review by a staff member.
    """
    election = Election.objects.latest()

    ballots = election.ballots.all()
    ballots = SortedDict([(b, b.candidates.all()) for b in ballots])
    # Flatten candidate list after converting QuerySets into lists
    candidates = sum(map(list, ballots.values()), [])
    votes = [(v, v.get_points_for_candidates(candidates))
             for v in election.votes.all()]
    response = render_to_response("spreadsheet.html", {
        'ballots': ballots.items(),
        'votes': votes,
    })
    filename = "election%s.xls" % (election.year_num)
    response['Content-Disposition'] = 'attachment; filename='+filename
    response['Content-Type'] = 'application/vnd.ms-excel; charset=utf-8'

    return response

More like this

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

Comments

youell (on July 25, 2008):

I like it. I've used this same approach in ASP for many years. Do you know if OpenOffice can do the same trick as Excel?

#

MasonM (on July 26, 2008):

Do you know if OpenOffice can do the same trick as Excel?

Yep. I just tried it in OpenOffice 2.4 and it worked fine. It took a pretty long time to import on my system, but that's probably because the election.xls spreadsheet that the above view returns is huge (>200KB).

#

phxx (on August 12, 2008):

Sorry, but i don't get where the excel file is generated. If I'm right it just attaches a file to the html response?

#

MasonM (on August 13, 2008):
Sorry, but i don't get where the excel file is generated. If I'm right it just attaches a file to the html response?

There's no such thing as an "HTML response" in Django; you must mean "HTTP response". The HTTP response this view generates contains an HTML file marked as an attachment with a MIME type of "vnd.ms-excel". Excel recognizes HTML and auto-imports it, as I said in the description.

#

phxx (on August 13, 2008):

ok, I thought it's just a reponse with html content but a different mimetype. And as you explained: It's just that. But that your spreadsheet app imports html is cool =)

Thanks for making things clear.

#

Please login first before commenting.