首次在公众号发布 datatables及在django中的使用

Datatables

先介绍一个网站https://adminlte.io/,一个很不错的后台管理的前端界面开源网站,基本功能是免费的。它提供了很多常用的前端模板。

本文跟它没有直接的关系,但是它在datatables章节https://adminlte.io/themes/AdminLTE/pages/tables/data.html 用的就是datatables,本文会着重介绍这个库的用法。

官方网站 https://datatables.net/

安装

需安装对应的Javascript和CSS, 可以在bootCDN上找到,下面列的是官方CDN供参考。

Javascript
<script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
CSS
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css"/>

JQuery必须已安装,下面版本是当前项目使用的版本 - https://code.jquery.com/jquery-3.3.1.js

在AdminLTE依赖库里面也自带boostrap文件,但是实际使用中会跟基本bootstrap<!-- Bootstrap 3.3.7 -->冲突,所以最终不使用。

<link rel="stylesheet" href="{% static 'adminlte/bower_components/datatables.net-bs/css/dataTables.bootstrap.min.css' %}">
<script src="{% static 'adminlte/bower_components/datatables.net-bs/js/dataTables.bootstrap.min.js' %}"></script>

配置

i18n

有两种方法可以加载国际化选项,通过ajax请求或者language属性。https://datatables.net/plug-ins/i18n/#How-to-use

在i18n手册中https://datatables.net/manual/i18n,我们可以看到一些常用的显示字符串,下面是法语的翻译,可以根据需求改成自己的语言。

$('#example').DataTable( {
    language: {
        processing: "Traitement en cours...",
        search: "Rechercher :",
        lengthMenu: "Afficher _MENU_ éléments",
        info: "Affichage de l'élement _START_ à _END_ sur _TOTAL_ éléments",
        infoEmpty: "Affichage de l'élement 0 à 0 sur 0 éléments",
        infoFiltered: "(filtré de _MAX_ éléments au total)",
        infoPostFix: "",
        loadingRecords: "Chargement en cours...",
        zeroRecords: "Aucun élément à afficher",
        emptyTable: "Aucune donnée disponible dans le tableau",
        paginate: {
            first: "Premier",
            previous: "Précédent",
            next: "Suivant",
            last: "Dernier"
        },
        aria: {
            sortAscending: ": activer pour trier la colonne par ordre croissant",
            sortDescending: ": activer pour trier la colonne par ordre décroissant"
        }
    }
} );
基本设置

参考网页 https://datatables.net/examples/basic_init/language

下面例子中,对一些常用的字符串进行了翻译。

对于选择框和按钮的翻译,我一起写在下面了,后面有专门章节介绍。

它支持trans templatetag,具体的参数可参考文档。

$(function () { 
    $('#example').DataTable( { 
        "language": {
            "lengthMenu": "每页显示 _MENU_ 个记录",
            "zeroRecords": "无记录",
            "info": '{% trans "Showing _START_ to _END_ of _TOTAL_ entries" %}', //"显示页 _PAGE_/_PAGES_",
            "infoEmpty": "没有找到记录",
            "paginate": {
                "first": "首页",
                "last": "尾页",
                "next": "下一页",
                "previous": "前一页", 
            },
            "infoFiltered": "(从_MAX_条记录中过滤)",
            "search": "{% trans 'Search' %}",
            select: {
                rows: {
                    _: '{% trans "You have selected %d rows" %}',
                    0: '{% trans "Click a row to select it" %}',
                    1: '{% trans "Only 1 row selected" %}'
                }
            },
            buttons: {
                selectAll: '{% trans "Select all items" %}',
                selectNone: '{% trans "Select none" %}',
                copy: '{% trans "copy" %}',
                print: '{% trans "print" %}',
                selectAll: '{% trans "Select all items" %}',
                selectNone: '{% trans "Select none" %}', 
            } 
        },
    } );
})
高级设置

参考文档 https://datatables.net/examples/advanced_init/language_file

也可以通过ajax方式从文件中读入i18n,本项目没有采用这种方式,所以没贴出对应代码。

$(function () { 
    $('#example').DataTable( { 
        "language": {
            "url":"{% static 'langs.json' %}", 
        },
    } );
})

选择框 select-checkbox

参考文档 https://datatables.net/extensions/select/examples/

静态文件如下

<script type="text/javascript" src="https://cdn.datatables.net/select/1.2.5/js/dataTables.select.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/1.2.5/css/select.dataTables.min.css"/>

对应的CDN

https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css
https://cdn.datatables.net/select/1.2.5/css/select.dataTables.min.css

https://code.jquery.com/jquery-3.3.1.js
https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js
https://cdn.datatables.net/select/1.2.5/js/dataTables.select.min.js

html文件里添加内容,注意在第一列添加空列用于选择框显示。

表格必须添加class display,否则css不会起作用。

<table id="example" class="display" style="width:100%">
    <thead>
        <tr>
            <th></th>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Salary</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td></td>
            <td>Tiger Nixon</td>
            <td>System Architect</td>
            <td>Edinburgh</td>
            <td>61</td>
            <td>$320,800</td>
        </tr>
        <tr>
            <td></td>
            <td>Garrett Winters</td>
            <td>Accountant</td>
            <td>Tokyo</td>
            <td>63</td>
            <td>$170,750</td>
        </tr>
    </tbody>
    <tfoot>
        <tr>
            <th></th>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Salary</th>
        </tr>
    </tfoot>
</table>

添加js代码设置相关属性,style: ”os”表示单选,如果多选,需要改成'multi'

$(document).ready(function() {
    $('#example').DataTable( {
        columnDefs: [ {
            orderable: false,
            className: 'select-checkbox',
            targets: 0
        } ],
        select: {
            style: 'os',
            selector: 'td:first-child'
        },
        order: [[ 1, 'asc' ]]
    } );
} );

详情参考 https://datatables.net/extensions/select/examples/initialisation/checkbox.html

i18n

参考文档 https://datatables.net/extensions/select/examples/initialisation/i18n.html,本项目设置如下

$(function () { 
    $('#example').DataTable( { 
        "language": {
            select: {
                rows: {
                    _: '{% trans "You have selected %d rows" %}',
                    0: '{% trans "Click a row to select it" %}',
                    1: '{% trans "Only 1 row selected" %}'
                }
            }, 
        },
    } );

预定义按钮 buid-in button

参考文档 https://datatables.net/extensions/buttons/examples

静态文件如下

js

<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js"></script>

css

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css"/>

对应的CDN,包括CSS和JS

https://code.jquery.com/jquery-3.3.1.js
https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js
https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js
https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.js
https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js
https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/pdfmake.min.js
https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/vfs_fonts.js
https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js
https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js
https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css
https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css

下面是常用的buttons设置,包括拷贝,csv,excel,打印,全选,全不选功能。

$(document).ready(function() {
    $('#example').DataTable( {
        // buttons
        dom: 'Bfrtip',
        buttons: [
            'copy', 
            'csv', 
            'excel', 
            'pdf', 
            'print',
            'selectAll',
            'selectNone',
        ]
    } );
} );

具体可参考 https://datatables.net/extensions/buttons/examples/initialisation/simple.html

dom的详细内容可以在https://datatables.net/reference/option/dom查看。

Options
The built-in table control elements in DataTables are:
B - Buttons
l - length changing input control
f - filtering input
t - The table!
i - Table information summary
p - pagination control
r - processing display element

默认是ltrip,注意如果不加B,那么按钮不会显示。

预定义的按钮在https://datatables.net/reference/button/定义

每个按钮可以扩展其功能https://datatables.net/extensions/buttons/examples/initialisation/custom.html

例如,本文扩展了一个按钮,显示已选择行数,参考https://datatables.net/reference/button/selected

$(document).ready(function() {
    $('#example').DataTable( {
        buttons: [
            {
                extend: 'selected',
                    text: 'Count selected rows',
                    action: function ( e, dt, button, config ) {
                        alert( dt.rows( { selected: true } ).indexes().length +' row(s) selected' );
                    }
            },
        ]
    } );
} );

该Button ”select”本身提供了一个基本的框架,它会根据是否行被选择而动态的enable和disable,你可以通过action添加你针对各种状态需要执行的操作。

自定义按钮

除了预定义按钮及按钮功能扩展之外,我们还可以创建我们自定义的按钮

比如下面代码会创建一个按钮”My button”,当点击是会弹出对话框。

$(document).ready(function() {
    $('#example').DataTable( {
        // buttons
        dom: 'Bfrtip',
        buttons: [
            {
                text: 'My button',
                action: function ( e, dt, node, config ) {
                    alert( 'Button activated' );
                }
            }, 
        ]
    } );
} );

细节参考 https://datatables.net/extensions/buttons/examples/initialisation/custom.html

参考文档
分页设置

参考文档

参考文档里写到了下面这个用法,但是chrome会报错

$(document).ready(function() {
    $('#example').DataTable( {
        "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]]
    } );
} );

本例使用

$(document).ready(function() {
$('#example').DataTable( {
        'lengthChange': true,
        "lengthMenu": [10, 25, 50, -1]]
    } );
} );

CRDU

付费支持 https://editor.datatables.net/

参考文档

行内操作按钮

参考文档

后台加载

参考文档

在前端添加下面选项,细节参考https://datatables.net/examples/server_side/post.html

文档中用了post,但是我的例子中并没有用post,要添加csrf_token等功能,对演示来说,get足够了。

$(function () { 
    $('#example').DataTable( {  
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "{% url 'customerpredefined_api' %}",
            // "type": "POST",
            "data": function ( d ) {
                console.log(d)
            }
        },
        "columns": [
            { "data": "id" }, // check box
            { "data": "name" },
            { "data": "position" },
            { "data": "office" },
            { "data": "start_date" },
            { "data": "salary" }
        ], 
    } );

在HTML里仅保留thead和tfoot

<table id="example" class="display" style="width:100%">
    <thead>
        <tr>
            <th></th>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Salary</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th></th>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Salary</th>
        </tr>
    </tfoot>
</table>    

指定url

urlpatterns = [
    url(r'^customerpredefinedapi$', CustomerPredefinedView, name='customerpredefined_api'), 
]

在视图里模拟返回JSON数据

def CustomerPredefinedView(request):
    draw = request.GET.get("draw")
    start = request.GET.get("start")
    length = request.GET.get("length")
    order = request.GET.get("order")
    datas = [
        {
            "name": "Satou",
            "position": "Accountant",
            "office": "Tokyo",
            "start_date": "28th Nov 08",
            "salary": "$162,700"
        },
        ......
        {
            "name": "Kelly",
            "position": "Senior Javascript Developer",
            "office": "Edinburgh",
            "start_date": "29th Mar 12",
            "salary": "$433,060"
        }
        ]
    data = datas[int(start):int(start)+int(length)]
    for index,_ in enumerate(data):
        _.update({"id": ''})
    data_resonse = {
        "draw": draw,
        "recordsTotal": 20,
        "recordsFiltered": 20,
        "data": data
    }

    return HttpResponse(json.dumps(data_resonse), content_type="application/json")

因为要显示checkbox,所以要有额外一列,但是并不打算显示它,只是用它来在表格中站位。如果没有这个,那么name数据会显示到checkbox里。
尝试过在前端模板columns里留空,但是会报错。不确定是否有更好的办法。

前端显示时,总共二十条记录,每页10个,下面是页面1-2-1-2顺序访问时chrome console打印的发送到后端的请求。

展开查看其内容

返回值

返回值的定义参考文档https://datatables.net/manual/server-side#Returned-data

特别说明的是recordsTotalrecordsFiltered这两个参数的差别

recordsTotal:数据库记录总数
recordsFiltered:过滤后的数据总数,注意:不仅仅是当前页的条目个数

比如,我数据库有1000条记录,搜索结果有3000条,每页显示20条,那么返回的数据应该是下面样子

{
    "draw": 1,
    "recordsTotal": 10000,
    "recordsFiltered": 3000,
    "data": [
       // ... skipped 20 records ...
    ]
}

recordsTotal仅仅用于信息显示,比如显示3000条记录中的1-20条(总数10000)。如果这儿不显示总数的话,recordsTotal属性可以不需要返回。

recordsFiltered属性用于计算需要显示的页数。

详情查看:https://stackoverflow.com/questions/43161353/recordstotal-recordsfiltered-explanation-jquery-datatable

Serializer

对于后端处理的datatables,可以通过Serializer来提高效率。

定义返回Json数据函数

class JSONDTResponse(HttpResponse):
    def __init__(self, data, **kwargs):
        content = JSONRenderer().render(data)
        dt_content = "{\"data\":" + content + "}"
        kwargs['content_type'] = 'application/json'
        super(JSONDTResponse, self).__init__(dt_content,**kwargs)

视图处理函数

def CustomerPredefinedJSONView(request): 
    cp = CustomerPredefined.objects.all() 
    serializer = CustomerPredefinedSerializer(cp,many=True)

    return JSONDTResponse(serializer.data)

定义Serializer类

from rest_framework import serializers
from .models import CustomerPredefined

class CustomerPredefinedSerializer(serializers.ModelSerializer):
    class Meta:
        model = CustomerPredefined
        fields = (
            'shipping_address',
            'shipping_company',
            'contact',
            'phone',
            'special_request',
            )
分页功能

从ajax请求参数里面读取页面相关信息,并返回对应数据

def CustomerPredefinedJSONView(request):     
    draw = request.GET.get("draw")
    start = request.GET.get("start")
    length = request.GET.get("length")
    order = request.GET.get("order")
#
    cp = CustomerPredefined.objects.all() 
    recordsTotal = cp.count() 
    recordsFiltered = recordsTotal
#
    cp = cp[int(start):int(start)+int(length)]
#
    serializer = CustomerPredefinedSerializer(cp,many=True)
#
    return JSONDTResponse(serializer.data, draw=draw, recordsTotal=recordsTotal, recordsFiltered=recordsFiltered )
class JSONDTResponse(HttpResponse):
    def __init__(self, data, **kwargs):
        content = JSONRenderer().render(data)
#
        draw = kwargs.pop("draw", None)
        recordsTotal = kwargs.pop("recordsTotal", None)
        recordsFiltered = kwargs.pop("recordsFiltered", None)
        data_resonse = {
            "draw": draw,
            "recordsTotal": recordsTotal,
            "recordsFiltered": recordsFiltered,
            "data": data
        }
        dt_content = json.dumps(data_resonse)
        kwargs['content_type'] = 'application/json'
        super(JSONDTResponse, self).__init__(dt_content,**kwargs)
选择框功能

返回一个不使用的变量id(内容为空)来占据选择框对应列

def CustomerPredefinedJSONView(request): 
    draw = request.GET.get("draw")
    start = request.GET.get("start")
    length = request.GET.get("length")
    order = request.GET.get("order")
#
    cp = CustomerPredefined.objects.all() 
    recordsTotal = cp.count() 
    recordsFiltered = recordsTotal
#
    cp = cp[int(start):int(start)+int(length)]
    for _ in cp:
        _.id=None
    serializer = CustomerPredefinedSerializer(cp,many=True)
#
    return JSONDTResponse(serializer.data, draw=draw, recordsTotal=recordsTotal, recordsFiltered=recordsFiltered )

前台添加这列id

    var table = $('#distributionbillset').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "{% url 'customerpredefinedjson_api' %}",
            // "type": "POST",
            "data": function ( d ) {
                console.log(d)
            },
        },
        "columns": [
            { "data": "id" }, // check box
            { "data": "shipping_address" },
            { "data": "shipping_company" },
            { "data": "contact" },
            { "data": "phone" },
            { "data": "special_request" }
        ], 

Serializer里将它添加到field

class CustomerPredefinedSerializer(serializers.ModelSerializer):
    class Meta:
        model = CustomerPredefined
        fields = (
            'id',
            'shipping_address',
            'shipping_company',
            'contact',
            'phone',
            'special_request',
            )
搜索功能

读取相应的搜索请求内容,字段为search[value],并返回相应的数据

def CustomerPredefinedJSONView(request): 
    draw = request.GET.get("draw")
    start = request.GET.get("start")
    length = request.GET.get("length")
    order = request.GET.get("order")
    search = request.GET.get("search[value]")
#
    cp = None
    if not search:
        cp = CustomerPredefined.objects.all() 
    else:
        cp = CustomerPredefined.objects.filter(Q(shipping_address__icontains=search) | Q(shipping_company__icontains=search)).distinct()
#
    recordsTotal = cp.count() 
    recordsFiltered = recordsTotal
#
    cp = cp[int(start):int(start)+int(length)]
    for _ in cp:
        _.id=None
#
    serializer = CustomerPredefinedSerializer(cp,many=True)
#
    return JSONDTResponse(serializer.data, draw=draw, recordsTotal=recordsTotal, recordsFiltered=recordsFiltered )
参考文档

其他的一些常用库

jeasyui

jqGrid

http://www.guriddo.net/demo/bootstrap/

CDN

flexigrid

datapicker

http://my97.net/demo/index.htm

参考文档

Datatables JQuery

Button

CRDU

Ajax

checkbox

Jquery easyui

评论