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:

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, {}),)

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

<style type="text/css">
  td, th {
    padding: 2px 0px 3px 3px;
    text-align: left;
  }
</style>

<script type="text/javascript"> 
  $(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');
      }  
    );  
  }
</script>   

<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>
            <div id="field_{{ field.name }}">
              <a href="javascript:void(0)" onclick="get_related('{{ field.name }}')"> >> Expand {{ field.verbose_name }}</a>
            </div>
          </td>
        </tr>
      {% endif %}{% endif %}
    {% endfor %}
  </table>
  <input type="submit" name="xls" onclick='$("#export_xls_form").overlay().close();' value="Submit"/>
</form>

export_to_xls_related.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>
          <div id="field_{{ previous_fields }}__{{ field.name }}">
            <a href="javascript:void(0)" onclick="get_related('{{ previous_fields }}__{{ field.name }}')"> >> Expand {{ field.verbose_name }}</a>
          </div>
        </td>
      </tr>
    {% endif %}{% endif %}
  {% endfor %}
</table>

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.

  2. 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

  3. 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.

  4. 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.

  5. 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…

  6. 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…

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

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA Image

*