Django根据模型字段生成excel报表

Question

该应用程序基于django/angular构建。我想根据模型和用户选择的字段生成一个excel报表。你可以在下面找到搜索界面。我在django有4个模特。教练，球员，参与，外键参照俱乐部（一对多关系）。单个django模型将作为一个选择输入，而模型字段将作为一个选项

模型.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

搜索界面（选择下拉菜单）

^{pr2}$

用例

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

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

从select输入中选择的字段将被发送到django视图以进行查询和结果连接。 发送到django视图的数据如下所示

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

视图

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

这就是json响应的外观。JSON响应将转换为excel文件

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

}

帮助

如何根据所选模型字段获取俱乐部的所有记录以及与之相关的外键数据（教练、球员、参与度）？ 报告示例如上图所示。在

感谢任何帮助和反馈。在

Answer 1

试试这个代码

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 2

您尚未提供模型的代码。

通常，您可以使用.values()或QueryManager中非常有用的方法.values()或{}来获得特定字段的列表。您可以用__来引用值的关系，就像club__name。

我想你们出口的一行是指一个玩家。所以，你必须从玩家模型开始建立关系。

示例：

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

许多领域都比较困难。它们可能需要对QueryManager进行聚合或extra和select调用。

Answer 3

事实上，有一个实用程序允许您从Django querysets导出csv数据，包括从链接到查找字段的所有特性：django-queryset-csv。

使用示例：

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)

查看azavea blog以获取更多使用示例。我希望你能发现这个有用。