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._meta.app_label model = queryset._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.