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.

Teahouse

Add the code of your models. Otherwise it is not possible to provide you with a concrete answer. – Casserole

models code has been added! – Teahouse

In your example output, players of the same club can have different coachers but this is not reflected in your models. You would have to add a ForeignKey to Coacher in your Player model for that. – Casserole

@Casserole Players and coaches are not related in this case! I just want to get all records of Club and related models (Coach, Player, Participation) based on their fields. Is it clear enough? – Teahouse

Then fix your example. Your example result cannot be achieved with your models. You have to understand yourself what your data relations are and what you want to output. – Casserole

I have updated output of the queryset in JSON format. – Teahouse

Answer 1

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.

Casserole

Export to excel line you mean? – Teahouse

I am getting duplicate data using select related approach! Is there any fix? – Teahouse

distinct() on the queryset might help. You should understand why you are getting duplicates - that way you can decide whether distinct is indeed the right approach or whether the query is wrong. – Casserole

Answer 2

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.

Gamete

Thanks for mentioning django-queryset-csv. – Teahouse

I am getting duplicate data using select related approach! Is there any fix? – Teahouse

Can you share what did you try and more details about the issue? – Gamete

records = Player.objects.order_by('id').values('id', *qfields). When i make query like this then i see some duplication in data. – Teahouse

the duplication is in records or in the csv data ? – Gamete

Duplication in both! – Teahouse

If you have duplicated records you have to check your database data, because the queryset will just returns you what exists in your database. If you think of making result distinct relatively to a specific fields check distinct doc – Gamete

Duplication is not in database level rather in the queryset! – Teahouse

How is it possible? The queryset could not duplicate result, it just returns data matching the query. – Gamete

Well i am wondering the same! I see duplication only when values fields are from more then 2 models. From the distinct doc - Similarly, if you use a values() query to restrict the columns selected, the columns used in any order_by() (or default model ordering) will still be involved and may affect uniqueness of the results. – Teahouse

Ah okay in this case, it may be possible. So try just to remove duplicates before rendering the response, a possible way is to use set to remove duplicates in a list: list(set(records)) – Gamete

records is a Queryset! TypeError: unhashable type: 'dict' – Teahouse

Because you need to serialize it before applying set. It a whole new question. Let us continue this discussion in chat. – Gamete

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

Oruntha

Thanks @Anoop! AttributeError: club object has no attribute coach_set.all(). NOTE: The model fields provided in the example is just one use case. Model fields varies according to what users have selected in the front-end. Looks like we're pretty close! – Teahouse

I fixed AttributeError: club object has no attribute coach_set.all() error! – Teahouse

Instead of club.coach_set.all() i used select related key. So club.coaches.all(). – Teahouse