Django - Generate excel reports based on model fields
<p>The application is built on <strong>django/angular</strong>. 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. <strong>Coach</strong>, <strong>Player</strong>,  <strong>Participation</strong> which have foreign key reference to <strong>Club</strong> (One-To-Many relationship). The individual django model will act as a select input and model fields as an options</p>

<p><strong>models.py</strong></p>

<pre><code>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
</code></pre>

<p><strong>Search UI (Select dropdown)</strong></p>

<pre><code>#####       ######      #######     #############
Club        Coach       Player      Participation
#####       ######      #######     #############
name        fname       fname       league
estd        lname       lname       startdate
address     age         country     
</code></pre>

<p><strong>Use case</strong></p>

<pre><code>- 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.
</code></pre>

<p><strong>HTML</strong>
    </p>

<pre><code>&lt;select class="form-control" data-ng-model="selected" data-ng-options="item.tablefield for item in coach" ng-click="addField()"&gt;&lt;/select&gt;

&lt;select class="form-control" data-ng-model="selected" data-ng-options="item.tablefield for item in player" ng-click="addField()"&gt;&lt;/select&gt;

&lt;select class="form-control" data-ng-model="selected" data-ng-options="item.tablefield for item in participation" ng-click="addField()"&gt;&lt;/select&gt;


&lt;button type="button" class="btn btn-default" ng-click="report()"&gt;Generate report&lt;/button&gt;
</code></pre>

<p><strong>Angular JS</strong></p>

<pre><code>    $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 &gt; 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');
            });
        }
    };
</code></pre>

<p>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</p>

<pre><code>[{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'}]
</code></pre>

<p><strong>Views</strong></p>

<pre><code>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)))
</code></pre>

<p>This is how json response must look like. The JSON response will be converted to excel file</p>

<pre><code>{  
    "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"
                        ]
                    }
                ]
            }
        ]
    }, 

}
</code></pre>

<p><strong>Help</strong> </p>

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

<p>Any help and feedback's are appreciated.</p>