Spreadsheet reporting in Django

It’s often desired to quickly export data in a generic method that takes little coding. There are already some solutions for this. I didn’t like any however as they ignore  many to many fields. One could argue a more robust system might be needed when handling more complex reports with gui sql query builders and such. Screw that here’s a hack to get the job 95% done.

I want to check off fields in Django Admin’s model list display. Then make an action to make a spreadsheet report, not some crap csv.

Here is my hack function to take a model and query set and spit out an .xls file

def admin_export_xls(request, app, model, qs=None):
    """ Exports a model to xls.
    qs: optional queryset if only exporting some data"""
    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)
    # Lets get all fields names, even m2m
    for i, field in enumerate(mc._meta.get_all_field_names()):
        ws.write(0,i, field)
    if not qs:
        qs = mc.objects.all()

    for ri, row in enumerate(qs):
        for ci, f in enumerate(mc._meta.get_all_field_names()):
            try:
                # terrible way to detect m2m manager
                if unicode(getattr(row, f))[1:51] == 'django.db.models.fields.related.ManyRelatedManager':
                    # If it's a M2M relationship, serialize it and throw it in one cell.
                    value = ""
                    for item in getattr(row, f).all():
                        value += unicode(item) + ", "
                    value = value[:-2]
                    ws.write(ri+1, ci, value)
                else:
                    ws.write(ri+1, ci, unicode(getattr(row, f)))
            except:
                # happens when the m2m is has an appended _set. This is a hack that works sometimes, it sucks I know
                try:
                    f += "_set"
                    value = ""
                    for item in getattr(row, f).all():
                        value += unicode(item) + ", "
                    value = value[:-2]
                    ws.write(ri+1, ci, value)
                except:
                    ws.write(ri+1, ci, "")
    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

That will serialize manytomany fields to comma separated fields all in one cell. Next in you need an admin aciton.

def export_selected_objects(modeladmin, request, queryset):
    app = queryset[0]._meta.app_label
    model = queryset[0]._meta.module_name
    return admin_export_xls(request, app, model, queryset)
export_selected_objects.short_description = "Export selected items to XLS"

You can see I made mine global. It works on all models. For this to work well you need to make sure your unicode representations of your models are useful. The most common thing I run into are phone numbers. A person can have unlimited phone numbers. The end user will assume a report of people will include this. I make my number’s unicode something like “Cell: 555-555-5555”.

Of course this code isn’t perfect and there are many times a more robust solution will be needed. What if you want a report of companies with contacts at the company and phone numbers of each contact. At that point you need to generate a query that can get such data and that’s going to take some gross gui query builder program or custom reports by you the developer.

Django Hack: adding extra data to admin interface

A common need I have for Django’s admin interface is to show a little more data for convenience right on the edit page. For example showing a link to a foreign key’s edit page right there. The way I do this is by setting the help_text field in the render_change_form function. I create a new function in my admin class to override render_change_form

class whateverAdmin(admin.modelAdmin):
 def render_change_form(self, request, context, *args, **kwargs):
  context['adminform'].form.fields['someField'].help_text = "Go to edit page " + str(context['original'].anyFunction()) + " (will discard changes)"
  return super(whateverAdmin, self).render_change_form(request, context, args, kwargs)

the anyFunction() is just a function I made to display a URL in my model. Notice the allow_tags line to allow the function to return the html <a> tag

def anyFunction(self):
 try:
  field = self.someField
  urlRes = urlresolvers.reverse('admin:appName_someField_change', args=(field.id,))
  return '</a><a href="http://example.com' + urlRes + '">' + str(field) + '</a>'
 except:
 return ""
anyFunction.allow_tags = True

This link is then very convenient when using the admin interface just to look up information. The render_change_from function is also useful to editing about the admin page. I use it to modify queryset’s for foreign key data as well.