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.

10 thoughts on “Django admin: Export ForeignKey subfields to XLS”

  1. Hi, can you confirm the license under which this piece of code is released? I assume without any explicit declaration this piece is either BSD or creative commons but it would help if you could confirm that.

    Like

    1. Assume GPLv3 unless stated otherwise. I suppose I should put a little notice of that up. Most of the code I write about is from here

      Like

  2. I would suggest that you release these snippets under a more permissive license as well? Otherwise, people would be hard pressed to attribute you for your work (internally/externally) if they are unable to release their code under the GPLv3? They may still end up using the idea or a variant of it since they were probably planning to implement something similar anyways?

    With proper attribution (which will typically happen only with a more permissive license), you will at least get attention towards the main project of which this is a part.

    Just my two cents, Creative Commons and/or the new BSD/MIT are quite often used for snippets of code.

    Like

    1. Thanks for the suggestion. I have no problem with you using the code in any way you want. I’ll post a side bar item about this. Consider everything original here BSD except things taken from other sites. If I reference a Django snippet or other blog you should additionally check there too. Since I only post parts of SWORD (GPL) that I wrote myself I believe it’s fine to have them dual licensed.

      Like

  3. Hi, I’m trying your applications following your simple instructions, but I had some problems (using Django 1.3 + grappelli):
    1. I created an admin/change_list.html from your snippet but I got a TemplateNotFoundError: grappelli/templates/admin/change_list.html; so, I copied all grappeli template into my change_list and put your code into.
    2. Then, static/js/jquery.tools.min.js wan’t found; so, I downloaded it;
    3. When I select the ‘export action’, a javascript error occurs: $ is not defined;

    maybe i’m missing something…

    Like

  4. Ok, done all.
    Now the issue is that when I choose ‘Export to XLS’, I see the overlay opening for a moment, then the page completely changes and goes to where I choose columns.
    I checked the code: it seems like after sending data via Ajax, the page doesn’t wait for its return and submits the form as well…

    Like

  5. Just a quick question here,
    Can you add values that are computed as properties?
    That would REALLY be nice!
    Thanks for your work
    Greetings

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s