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()):
                # 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)
                    ws.write(ri+1, ci, unicode(getattr(row, f)))
                # happens when the m2m is has an appended _set. This is a hack that works sometimes, it sucks I know
                    f += "_set"
                    value = ""
                    for item in getattr(row, f).all():
                        value += unicode(item) + ", "
                    value = value[:-2]
                    ws.write(ri+1, ci, value)
                    ws.write(ri+1, ci, "")
    fd, fn = tempfile.mkstemp()
    fh = open(fn, 'rb')
    resp = fh.read()
    response = HttpResponse(resp, mimetype='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename=%s.xls' % 
    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.

More uno reports

I’ve been playing around more with openoffice.org’s uno api for making reports. Since I’ll be making more updates I’ll just post a link to the Google Code site


My latest improvement is supporting tables. It’s probably best to just show what it does.

Now a user could just download the report, change fonts, layout, etc, and reupload it. It does have it’s limitations and is a work in progress but it’s already pretty cool. As a developer I can just define what variables can be used and let someone else make the report (and change it around later). To use it you just have to make the proper data structures, so really this could be ported to any data driven application, not just Django.

Django and OpenOffice.org uno reports

Openoffice.org’s python uno library is great for report writing in a web application. I wanted to create a report template system where users can create templates with a word processor of their choice. I created a simple template model which consists of a CharField and FileField. Users can upload templates here in .doc, .odt, or any format OpenOffice.org supports.

Next I created some generic template report functions.

from django.http import HttpResponse
from django.core.servers.basehttp import FileWrapper

import uno
import os
import string
import tempfile

def findandreplace(document, search, find, replace):
"""This function searches and replaces. Create search, call function findFirst, and finally replace what we found."""
#What to search for
search.SearchString = unicode(find)
#search.SearchCaseSensitive = True
#search.SearchWords = True
found = document.findFirst( search )
if found:
print 'Found %s' % find
while found:
found.String = string.replace( found.String, unicode(find),unicode(replace))
found = document.findNext( found.End, search)

def replace_report(infile, outfile, data):
"""Replace words in a file use like this
data['$TEST']='worked yay'
returns a django HttpResponse of the file"""
# Boring uno stuff
local = uno.getComponentContext()
resolver = local.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local)
context = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
desktop = context.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", context)

# open document
document = desktop.loadComponentFromURL("file://" + str(infile) ,"_blank", 0, ())
cursor = document.Text.createTextCursor()

search = document.createSearchDescriptor()
#Do a loop of the data and replace the content.
for find,replace in data.items():
print find,replace

# create temporariy file to store document in
tmp = tempfile.NamedTemporaryFile()
document.storeToURL("file://" + str(tmp.name), ())

# create http response out of temporariy file.
wrapper = FileWrapper(file(tmp.name))
response = HttpResponse(wrapper, content_type='application/odt')
response['Content-Length'] = os.path.getsize(tmp.name)
response['Content-Disposition'] = 'attachment; filename=' + outfile

return response

In this example I’m replacing text in a file. The result is returned as a http response. I can use it like this

template = Template.objects.get_or_create(name="My Selected Template")[0]
data['$date'] = str(date.today())
return replace_report(template.file.path, "file.odt", data)

In this case I am taking out my template and replacing all instances of the word “$date” with the current date. I choose to use $ to mark it as a variable but it could really be anything. Now when a user wants to change a font they can do it themselves.

Django models from non Django applications

I have an existing LAMP application that I want to write some quick code for. I want to easily manipulate the database without taking the time to understand how the application works fully and mess around with PHP. The solution, create a Django model to act as a wrapper for the sections of the database I need.

First I created some very simple views in MySQL to my new Django created database. I could have just put both programs in the same database but this should make updates easier. Remember in MySQL views are updatable only when the view references one table. All my views look like
create otherdb.my_view as select * from newdb.some_table;
With this method I can easily drop and replace the other database when I update it.

Next I run Django’s inspectdb command to get some generic Django model code. Edit this to one’s liking. For me that meant renaming fields, deleting the primary key (so Django automatically makes it. Only works if it is in fact a primary key auto incr integer). Turn foreign keys into foreign key fields. Make dates into DateFields.

Setup my Django admin interface and all the sudden I can use the spiffy Django admin interface on a completely different application. YAY!

However I ran into a small problem. I’ll post this separately for people who only care about Creating a custom Django DateTimeField

Introducing SWORD

Student WOrker Relational Database (SWORD) is a student worker database for use in schools with work study programs. It’s the project I’ve been working on for the past 6 months or so.


It’s a great example of leveraging Django to create a full website in very little time (Until recently it was just a side project I worked on occasionally) . I’m releasing it open source to help distribute it for use in other schools. Right now the installation process is a little ugly though. I’ll have to learn how to make Debian  and RPM packages sooner or later. Next steps will be to integrate it with some other programs, possibly Schooltool and  SugarCRM. It’s already working with CAS for Single Sign On. If you know any schools that might be able to use this please comment or email. I may be offering it as a hosted solution soon.

Django and Google Maps

Last post I wrote about extending the Django Admin interface by adding a little dynamic data to the help_text field. For more advanced things you need to customize the html widget itself.

One thing I want to do is have a Django model automatically get a Google Map of the address I have stored and save this file. For my purposes though I don’t want this to be automatic, I’d rather an admin user check the map first then decide to overwrite any previous map file. I’ll extend the a widget to get this effect.

First I need a preview box. I want to insert some extra html, I’m actually going to put this into a checkbox form, you will see why later. I need to override the render def on the widget to add some code.

class MapImageWidget(forms.CheckboxInput):
def render(self, name, value, attrs=None):
output = []
output.append(super(MapImageWidget, self).render(name, value, attrs))
output.append("If checked, the above map file will be overwritten with Google Maps.<table><tr><td><a href="javascript:get_map()">Preview Google Maps</a></td></tr><tr><td> </td></tr></table>")

return mark_safe(u''.join(output))

I’m using Google Maps static maps url to get my previews. I made a javascript function called get_map to look up the url based on the address field on my model. I want it to actually use the currently typed in address rather than some ajax solution which may get the currently saved address.

function get_map(){
var address = document.getElementById("id_address").value;
var city = document.getElementById("id_city").value;
var state = document.getElementById("id_state").value;
var zip = document.getElementById("id_zip").value;
parent.mapframe.location="http://maps.google.com/maps/api/staticmap?sensor=false&size=400x400&markers=size:mid|color:red|" + address + "," + city + "," + state + "," + zip;

I put the javascript into my change_form.html admin template. This function is only for previewing by the user and has nothing to do with actually saving my map image. For that I have a checkbox as in this screen shot

Maps preview in admin edit page

When the user checks off the box, Django will know to get that image and set it to the map ImageField. I do this by overriding the save function on my model.

def save(self, *args, **kwargs):
if (self.use_google_maps):
self.use_google_maps = False;
image = urllib.urlretrieve("http://maps.google.com/maps/api/staticmap? sensor=false&size=400x400&markers=size:mid|color:red|" +
self.address + "," + self.city + "," + self.state + "," + self.zip)
self.map.save(self.company_name + "_map.jpg", File(open(image[0])))
super(Company, self).save(*args, **kwargs)

I use urllib to “wget” the file. This process will replace the previous practice of manually looking up maps, saving them, and uploading them. Of course if I trusted Google maps (or my addresses to be correct) I could just have it dynamically get that image when needed instead of storing the image file.

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):
  field = self.someField
  urlRes = urlresolvers.reverse('admin:appName_someField_change', args=(field.id,))
  return '</a><a href="http://example.com' + urlRes + '">' + str(field) + '</a>'
 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.