Generic export to excel for any django model

Posted: April 09, 2009
Comments: 5
Tags: django excel

This post will show you how to extend the django admin to allow for any model to be exported to an excel spreadsheet. it uses the xlwt module to generate the spreadsheet.

views.py 

def admin_export_xls(request, app, model):
    mc = ContentType.objects.get(app_label=app, model=model).model_class()
    wb = xlwt.Workbook()
    ws = wb.add_sheet(unicode(mc._meta.verbose_name_plural))
    for i, f in enumerate(mc._meta.fields):
        ws.write(0,i, f.name)
    qs = mc.objects.all()
    for ri, row in enumerate(qs):
        for ci, f in enumerate(mc._meta.fields):
            ws.write(ri+1, ci, unicode(getattr(row, f.name)))
    fd, fn = tempfile.mkstemp()
    os.close(fd)
    wb.save(fn)
    fh = open(fn, 'rb')
    resp = fh.read()
    fh.close()
    response = HttpResponse(resp, mimetype='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename=%s.xls' % \
          (unicode(mc._meta.verbose_name_plural),)
    return response

because the xlwt module doesn't allow you to write to an open file handle, we have to first create a temporary file using tempfile.mkstemp() and subsequently close the file, then save spreadsheet with the returned filename. This might not be the most efficient way, but it works.

in the urls file we need to point to the new view, be sure to put this line before the regular admin include since it acts as a sort of catch-all. [Update: Wrapped the view in admin.site.admin_view decorator.] urls.py 

...

    (r'^admin/([^\/]+)/([^\/]+)/xls/$', admin_export_xls),
    (r'^admin/(.*)', admin.site.admin_view(admin.site.root)),
...

Now we need to add a button to the top of each change list template that we want to have this functionality.

change_list.html 

{% extends 'admin/change_list.html' %}{% load i18n %}
{% block object-tools %}
<ul class="object-tools">
  {% if has_add_permission %}
  <li>
<a href="add/{% if is_popup %}?_popup=1{% endif %}" class="addlink">
      {% blocktrans with cl.opts.verbose_name as name %}Add {{ name }}{% endblocktrans %}
    </a>
</li>
  {% endif %}
  <li>
<a href="xls/" class="viewsitelink">Export to .XLS</a>
</li>
</ul>
{% endblock %}

Comments/suggestions appreciated.

Comments

Will (link)
2009 April 10, 8:37 PM
This sounds like it would be nice for the new admin actions: http://docs.djangoproject.com/en/dev/ref/contrib/admin/actions/#ref-contrib-admin-actions You may like to accept a list of ids to allow the exported objects to be selected: if 'ids' in request.GET: qs = mc.objects.filter(pk__in=request.GET['ids'].split(",")) else: qs = mc.objects.all() And then add the following admin action, similar to the one in the documentation: from django.contrib import admin from django.contrib.contenttypes.models import ContentType from django.http import HttpResponseRedirect from django.core.urlresolvers import reverse def export_selected_objects(modeladmin, request, queryset): selected = request.POST.getlist(admin.ACTION_CHECKBOX_NAME) ct = ContentType.objects.get_for_model(queryset.model) return HttpResponseRedirect("%s?ids=%s" % (reverse(admin_export_xls', args=[ct.app, ct.model]), ",".join(selected)))
andre
2009 April 11, 8:52 AM
@will It would indeed make for a good admin action, that was my plan originally however in its current state, admin actions doesn't allow you to select all items if they span more than a single page. I opened a ticket requesting that the admin actions be allowed to accept an empty queryset. this way admin actions could decide to use ALL rows, or could even do something generic that doesn't apply to any specific rows. unfortunately the devs didn't think it was a good idea. ticket is here: http://code.djangoproject.com/ticket/10768
Mike
2009 April 14, 2:35 PM
I think it's kind of ridiculous that the dev's didn't like the idea, especially with the reasons that they gave. As it stands that drop down is border-line useless, or at the very least not as useful as it should be. Either way, nice workaround and look forward to using it on upcoming projects!
matt.geek.nz (link)
2009 April 30, 12:31 AM
Thanks for this, you saved me a couple of hours!
jävi (link)
2009 May 05, 6:19 PM
Hi Andre, I'm using your workaround and it works fine =) But... How do you check user auth? I mean, If somebody without auth calls admin/.../.../xls , that works?

Comments have been closed for this post.