Django - Generate excel reports based on model fields

Question

The application is built on django/angular. I want to generate a excel report based on model and it's fields selected by users. You can find search UI below. I have 4 models in django. Coach, Player, Participation which have foreign key reference to Club (One-To-Many relationship). The individual django model will act as a select input and model fields as an options

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 UI (Select dropdown)

#####       ######      #######     #############
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');
            });
        }
    };

The Selected fields from select inputs are sent to django views for query and result concatenation. The data send to django views looks like this

[{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 how json response must look like. The 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"
                        ]
                    }
                ]
            }
        ]
    }, 

}

Help

How i can get all records of Clubs and foreign key data (Coach, Player, Participation) related it based on selected model fields? Example of report is shown above.

Any help and feedback's are appreciated.

Answer 1

In fact there is a utility that allow you to export csv data from Django querysets with all its features from chaining to lookups fields: django-queryset-csv.

usage 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 azavea blog for more usage samples. I hope you could find this helpful.

Answer 2

You have not provided the code of your models.

In general, you can get a list of certain fields using the very helpful methods .values() or .values_list() of the QueryManager. You can reference relation of values with __ like club__name.

I suppose that one line in your export refers to one player. So, you would have to have the relation established starting at the player model.

Example:

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

ManyToMany fields are more difficult. They might require aggregation or extra and select calls on the QueryManager.

Answer 3

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))