Django - generate excel reports based on model fields

Question

The application is built on django / angular . I want to create an excel report based on a model and its fields selected by users. Below you can find the search user interface. I have 4 models in django. Coach , Player , Participation that has a link to a foreign key for the Club (one-to-many relationship). The individual django model will act as input fields and models as parameters

models.py

from datetime import datetime from django.db import models class Club(models.Model): name = models.CharField(max_length=200) estd = models.IntegerField() address = models.CharField(max_length=200) def __unicode__(self): return "%s" % self.name class Coach(models.Model): fname = models.CharField(max_length=80) lname = models.CharField(max_length=80) age = models.IntegerField() fk = models.ForeignKey(Club, related_name='coaches') def __unicode__(self): return "%s" % self.fname class Player(models.Model): fname = models.CharField(max_length=80) lname = models.CharField(max_length=80) country = models.CharField(max_length=42) fk = models.ForeignKey(Club, related_name='players') def __unicode__(self): return "%s" % self.fname class Participation(models.Model): league = models.CharField(max_length=80) startdate = models.DateTimeField() fk = models.ForeignKey(Club, related_name='participations') def __unicode__(self): return "%s" % self.league 

Search in the user interface (select the dropdown menu)

 ##### ###### ####### ############# Club Coach Player Participation ##### ###### ####### ############# name fname fname league estd lname lname startdate address age country 

Use case

 - User have to select at least one field from the Club dropdown. - User can select one or more fields from Coach, Player and Participation dropdown. 

HTML

 <select class="form-control" data-ng-model="selected" data-ng-options="item.tablefield for item in coach" ng-click="addField()"></select> <select class="form-control" data-ng-model="selected" data-ng-options="item.tablefield for item in player" ng-click="addField()"></select> <select class="form-control" data-ng-model="selected" data-ng-options="item.tablefield for item in participation" ng-click="addField()"></select> <button type="button" class="btn btn-default" ng-click="report()">Generate report</button> 

Angular js

  $scope.club = [{ 'tablename': 'Club', 'tablefield': 'name' }, { 'tablename': 'Coach', 'tablefield': 'estd' }, { 'tablename': 'Coach', 'tablefield': 'address' } ]; $scope.coach = [{ 'tablename': 'Coach', 'tablefield': 'fname' }, { 'tablename': 'Coach', 'tablefield': 'lname' }, { 'tablename': 'Coach', 'tablefield': 'age' } ]; $scope.player = [{ 'tablename': 'Player', 'tablefield': 'fname' }, { 'tablename': 'Player', 'tablefield': 'lname' }, { 'tablename': 'Player', 'tablefield': 'country' } ]; And Similar for participation $scope.queryfields = []; // add fields $scope.addField = function(){ var found = $scope.queryfields.some(function (el) { return el.value === $scope.selected.tablefield; }); if (!found) { var searchkey = $scope.selected.tablename, searchvalue = $scope.selected.tablefield; $scope.queryfields.push({ key: searchkey, value: searchvalue }) } else{ console.log('field already exist'); } }; // SEARCH $scope.report = function() { if($scope.queryfields.length > 1){ // post search fields data $http.post('/api/gamify/advancesearch/', $scope.queryfields) .success(function (response) { $scope.queryset = response; }) .error(function (data, status, headers, config) { console.log('error'); }); } }; 

Selected fields from the selected inputs are sent to django views to query and concatenate the result. The data sent to django views is as follows:

 [{u'value': u'name', u'key': u'Club'}, {u'value': u'fname', u'key': u'Coach'}, {u'value': u'lname', u'key': u'Coach'}, {u'value': u'fname', u'key': u'Player'}, {u'value': u'league', u'key': u'Participation'}] 

Views

 def report(request): qfields = json.loads(request.body) print query """ [{u'value': u'name', u'key': u'Club'}, {u'value': u'fname', u'key': u'Coach'}, {u'value': u'lname', u'key': u'Coach'}, {u'value': u'fname', u'key': u'Player'}, {u'value': u'league', u'key': u'Participation'}]""" # TO-DO # Get all records of Club (field: name) # Get all records of Coach (fields: fname, lname) which is reference of Club. # Get all records of Player (field: fname) which is reference of Club. # Get all records of Participation (field: league) which is reference of club. # Export to excel # Response json object records = Player.objects.order_by('id').values('id', *qfields) return HttpResponse(json.dumps(list(records))) 

This is what the json answer should look like. JSON response will be converted to excel file

 { "datarow1":{ "Club":[ { "club.name":"FC Bar" }, { "coach":{ "coach.fname":[ "Hari", "Shyam", "Parbe" ] } }, { "player":[ { "player.fname":[ "King", "Leo", "Singh" ] }, { "player.lname":[ "Dev", "Fin" ] } ] }, { "participation":[ { "participation.league":[ "la liga", "UEFA" ] } ] } ] }, "datarow2":{ "Club":[ { "club.name":"FC TU" }, { "coach":{ "coach.fname":[ "Xavi", "Hola", "Them" ] } }, { "player":[ { "player.fname":[ "Sab", "Hi", "Suz" ] }, { "player.lname":[ "Messi", "Aura" ] } ] }, { "participation":[ { "participation.league":[ "Italian", "Premier" ] } ] } ] }, } 

reference

How can I get all the club records and foreign key data (Coach, Player, Participation) associated with it based on the selected model fields ? An example report is shown above.

Any help and feedback is appreciated.

Answer 1

You have not provided a code for your models.

In general, you can get a list of specific fields using the very useful .values() or .values_list() methods for QueryManager . You can refer to the relation of values ​​with __ as club__name .

I believe that one line in your export refers to one player. Therefore, you must establish a relationship starting with the player model.

Example:

 Player.objects.order_by('lname').values('lname', 'coach__fname', 'coach__lname', 'club__name', 'club__league') 

ManyToMany fields are more complex. They may require aggregation or extra and select calls in the QueryManager.

Answer 2

Try this code

 def report(request): query = json.loads(request.body) print query """ [{u'value': u'name', u'key': u'Club'}, {u'value': u'fname', u'key': u'Coach'}, {u'value': u'lname', u'key': u'Coach'}, {u'value': u'fname', u'key': u'Player'}, {u'value': u'league', u'key': u'Participation'}]""" clubs = Club.objects.all() result = {} for index, club in enumerate(clubs): coach_fname = club.coach_set.all().values_list('fname', flat=True) player_fname = club.player_set.all().values_list('fname', flat=True) player_lname = club.player_set.all().values_list('lname', flat=True) participation_leage = club.participation_set.all().values_list('league') out_put = [] club_details = {"club.name": club.name } coach_details = {"coach":{"coach.fname": list(coach_fname) }} player_details = { "player":[ { "player.fname": list(player_fname)},{ "player.lname": list(player_lname)}]} participation_details = { "participation":[ { "participation.league": list(participation_leage)}]} out_put.append(club_details) out_put.append(coach_details) out_put.append(player_details) out_put.append(participation_details) result.update({ ['datarow{}'.format(index)]['Club']: out_put}) return HttpResponse(json.dumps(result)) 

Answer 3

In fact, there is a utility that allows you to export csv data from Django request sets with all its functions from the chain to the search fields: django-queryset-csv .

using exapmle:

 from djqscsv import render_to_csv_response def csv_view(request): qs = Player.objects.order_by('lname').values('lname', 'coach__fname', 'coach__lname', 'club__name', 'club__league') return render_to_csv_response(qs) 

Check out the azavea blog for more usage patterns. Hope you find this helpful.