Django admin: Export ForeignKey subfields to XLS

Last post I made an export to XLS tool for Django’s admin interface. A common request is to quickly export related field data as well. Today I’ll show you how to export foreign key fields, recursively. It doesn’t work for ManyToMany which are of course much more complicated since it can return more than one result! First I though it would be nice to have a view permission for my models. If I just want to export them, why would I need edit permission? This blog shows a quick way to get view permissions to all models. Note you could just use the edit permission instead if that works for you.

This time AJAX is a must. I’m using it to insert html where I want it to dynamically construct the export view. This has the advantage of not getting caught up in loops of references referencing other references…uck recursion. I’ve heavily modified my files from the last post. If you really want to understand how all this works, I suggest you start there. Note I still use the export_simple_selected_objects function from before, left untouched. Here are my new views:
[python]
def get_fields_for_model(request):
""" Get the related fields of a selected foreign key """
model_class = ContentType.objects.get(id=request.GET[‘ct’]).model_class()
queryset = model_class.objects.filter(pk__in=request.GET[‘ids’].split(‘,’))

rel_name = request.POST[‘rel_name’]
related = model_class
for item in rel_name.split(‘__’):
related = getattr(related, item).field.rel.to

model = related
model_fields = model._meta.fields
previous_fields = rel_name

for field in model_fields:
if hasattr(field, ‘related’):
if request.user.has_perm(field.rel.to._meta.app_label + ‘.view_’ + field.rel.to._meta.module_name):
field.perm = True

return render_to_response(‘sis/export_to_xls_related.html’, {
‘model_name’: model_class._meta.verbose_name,
‘model’: model._meta.app_label + ":" + model._meta.module_name,
‘fields’: model_fields,
‘previous_fields’: previous_fields,
}, RequestContext(request, {}),)

def admin_export_xls(request):
model_class = ContentType.objects.get(id=request.GET[‘ct’]).model_class()
queryset = model_class.objects.filter(pk__in=request.GET[‘ids’].split(‘,’))
get_variables = request.META[‘QUERY_STRING’]
model_fields = model_class._meta.fields

for field in model_fields:
if hasattr(field, ‘related’):
if request.user.has_perm(field.rel.to._meta.app_label + ‘.view_’ + field.rel.to._meta.module_name) or request.user.has_perm(field.rel.to._meta.app_label + ‘.change_’ + field.rel.to._meta.module_name):
field.perm = True

if ‘xls’ in request.POST:
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet(unicode(model_class._meta.verbose_name_plural))

# Get field names from POST data
fieldnames = []
# request.POST reorders the data 😦 There’s little reason to go through all
# the work of reordering it right again when raw data is ordered correctly.
for value in request.raw_post_data.split(‘&’):
if value[:7] == "field__" and value[-3:] == "=on":
fieldname = value[7:-3]
app = fieldname.split(‘__’)[0].split(‘%3A’)[0]
model = fieldname.split(‘__’)[0].split(‘%3A’)[1]
# Server side permission check, edit implies view.
if request.user.has_perm(app + ‘.view_’ + model) or request.user.has_perm(app + ‘.change_’ + model):
fieldnames.append(fieldname)

# Title
for i, field in enumerate(fieldnames):
#ex field ‘sis%3Astudent__fname’
field = field.split(‘__’)
model = get_model(field[0].split(‘%3A’)[0], field[0].split(‘%3A’)[1])
txt = ""
for sub_field in field[1:-1]:
txt += sub_field + " "
txt += model._meta.get_field_by_name(field[-1])[0].verbose_name
worksheet.write(0,i, txt)

# Data
for ri, row in enumerate(queryset): # For Row iterable, data row in the queryset
for ci, field in enumerate(fieldnames): # For Cell iterable, field, fields
try:
field = field.split(‘__’)
data = getattr(row, field[1])
for sub_field in field[2:]:
data = getattr(data, sub_field)
worksheet.write(ri+1, ci, unicode(data))
except: # In case there is a None for a referenced field
pass

# Boring file handeling crap
fd, fn = tempfile.mkstemp()
os.close(fd)
workbook.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(model_class._meta.verbose_name_plural),)
return response

return render_to_response(‘sis/export_to_xls.html’, {
‘model_name’: model_class._meta.verbose_name,
‘model’: model_class._meta.app_label + ":" + model_class._meta.module_name,
‘fields’: model_fields,
‘get_variables’: get_variables,
}, RequestContext(request, {}),)
[/python]

admin_export_xls does the bulk of the work and actually creates your xls reports. We use a similar syntax to django queries to find out exactly what data we are looking for. Something like “field__work_study:company__placement__company__id” where field just makes it a unique html name, followed by the app:model, then each object. So here we know we have a work_study.company model. We take our row in the queryset and say row.placement.company.id. Of course it uses a recursive getattr instead.

get_fields_for_model handles each ajax request for getting more fields. Now for the html I have two files
export_to_xls.html
[html]
<style type="text/css">
td, th {
padding: 2px 0px 3px 3px;
text-align: left;
}
</style>

$(document).ready(function()
{
$(“#check_all”).click(function()
{
var checked_status = this.checked;
$(“.check_field”).each(function()
{
this.checked = checked_status;
});
});
});

function get_related(name){
$.post(
“/sis/export_to_xls_related/?{{ get_variables }}”,
{get_related: “True”, rel_name: name},
function(data){
$(“#field_” + name).hide(‘fast’);
$(“#field_” + name).html(data);
$(“#field_” + name).show(‘slow’);
}
);
}

<h2> Export {{ model_name }} </h2>

<form method="post" action="/sis/export_to_xls/?{{ get_variables }}">
<table>
<tr>
<th>
<input type="checkbox" id="check_all" checked="checked" />
</th>
<th>
Field
</th>
</tr>

{% for field in fields %}
<tr>
<td>
<input type="checkbox" class="check_field" checked="checked" name="field__{{ model }}__{{ field.name }}"/>
</td>
<td>
{{ field.verbose_name }}
</td>
</tr>
{% if field.related %}{% if field.perm %}
<tr>
<td></td>
<td>

</td>
</tr>
{% endif %}{% endif %}
{% endfor %}
</table>
<input type="submit" name="xls" onclick=’$("#export_xls_form").overlay().close();’ value="Submit"/>
</form>
[/html]

export_to_xls_related.html
[html]
<table>
{% for field in fields %}
<tr>
<td>
<input type="checkbox" class="check_field" checked="checked" name="field__{{ model }}__{{ previous_fields }}__{{ field.name }}"/>
</td>
<td>
{{ field.verbose_name }}
</td>
</tr>
{% if field.related %}{% if field.perm %}
<tr>
<td></td>
<td>

</td>
</tr>
{% endif %}{% endif %}
{% endfor %}
</table>
[/html]

I also use the modified change_list.html from last post. export_to_xls.html gets inserted into export_to_xls_related.html a potentially infinite amount of times which is all displayed on an overlay in the edit list view. When you click Submit you get an XLS file with all checked off fields. Hurray!

TODO: Many to Many fields! This will be much harder. The only way to show them is either by repeating rows or smashing them all in one cell. This is the peril of trying to fit a potentially infinite dimensional object into a 2D spreadsheet.