datatable自定义搜索和导出按钮并解决在后端分页无法导出全部数据的问题

妖狐艹你老母 2022-12-26 14:29 249阅读 0赞

为了方便使用，即使是不懂datatable的人也能上手使用，所以还是自定义的好，按钮和搜索直接和往常一样根据html中定义

*  按钮：分了两种，有一个特殊的类型colvis和普通的按钮
    
     *  colvis  ( " . c o l v i s " ) . h t m l ( (".colvis").html( (".colvis").html(("\#example\_wrapper .buttons-colvis")); 直接写入dom元素
     *  普通导出按钮；
    
    1.  在datatable写出按钮并在页面上显示
    2.  自定义按钮，属性名data-type的值就是按钮的extend值
    3.  点击自定义按钮，触发名字相同的按钮的点击事件
 *  搜索
    
    1.  定义一个搜素的表单，定义一个点击事件（不是提交）
    2.  点击后刷新表单
    3.  在datatable提交数据时，将搜索表单一起传递
 *  导出数据
    
    1.  后端分页导出数据的时候只能导出当前页,所以定义一个参数提交
    2.  点击导出按钮后，重新提交数据，根据定义的参数，然后在导出

后台返回格式

$data = [
      "draw" => $_GET['draw'],
      "recordsTotal" => 570, //所有数据
      "recordsFiltered" => 60,// 过滤后的数据
      "data" => [["first_name" => "Airi", "last_name" => "Satou", "position" => "Accountant", "office" => "Tokyo", "start_date" => "28th Nov 08", "salary" => "$162,700",], ["first_name" => "Angelica", "last_name" => "Ramos", "position" => "Chief Executive Officer (CEO)", "office" => "London", "start_date" => "9th Oct 09", "salary" => "$1,200,000"], ["first_name" => "Ashton", "last_name" => "Cox", "position" => "Junior Technical Author", "office" => "San Francisco", "start_date" => "12th Jan 09", "salary" => "$86,000"],]
    ];

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc5NzE4Mg_size_16_color_FFFFFF_t_70]

<!DOCTYPE html>
    <html lang="en">
    
    <head>
      <meta charset="utf-8">
      <meta name="viewport" content="width=device-width, initial-scale=1">
      <title>AdminLTE 3 | DataTables</title>
    
      
      <link rel="stylesheet"
        href="https://fonts.googleapis.com/css?family=Source+Sans+Pro:300,400,400i,700&display=fallback">
      
      <link rel="stylesheet" href="../../plugins/fontawesome-free/css/all.min.css">
      
      <link rel="stylesheet" href="../../plugins/datatables-bs4/css/dataTables.bootstrap4.min.css">
      <link rel="stylesheet" href="../../plugins/datatables-responsive/css/responsive.bootstrap4.min.css">
      <link rel="stylesheet" href="../../plugins/datatables-buttons/css/buttons.bootstrap4.min.css">
      
      <link rel="stylesheet" href="../../dist/css/adminlte.min.css">
    
      <link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/css/bootstrap.min.css"> 
      <link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
    
      <style>
        body,
        html { 
          padding: 30px;
        }
    
        .bottom>div { 
          display: inline-block;
        }
        .custom-select-sm{ 
          padding-bottom: -3px;
        }
     
        #example_paginate { 
          float: right;
        }
    
        #example_length { 
          margin-left: 10px;
        }
    
        .customer .dropdown-toggle::after { 
          /* 去除下拉列表小三角 */
          display: none
        }
    
        .customer .btn-secondary { 
    
          background-color: white;
    
        }
    
        .style2>div { 
          float: right;
          padding-left: 5px;
        }
      </style>
    </head>
    
    <body>
      <div class="customer">
        
        搜索样式
        <form id="search">
          用户名称：<input type="text" class="input-text" style="width:250px" placeholder="输入用户名称" id="name" name="name">
          用户手机：<input type="text" class="input-text" style="width:250px" placeholder="输入用户电话" id="phone" name="phone">
          <button type="button" class="btn btn-success" onclick="search()"> 搜用户</button>
        </form>
        
        <br> 按钮样式1
        <div class="style1">
          <div class="btns">
            <button type="button" class="btn btn-success" data-type="copy"> 复制本页数据</button>
            <button type="button" class="btn btn-primary" data-type="pdf"> 导出为pdf</button>
            <button type="button" class="btn btn-info" data-type="csv"> 导出为csv</button>
            <button type="button" class="btn btn-warning" data-type="print"> 导出为print</button>
            <button type="button" class="btn btn-danger" data-type="excel"> 导出为excel</button>
            <button type="button" class="btn btn-success" data-type="colvis"> 导出为colvis</button>
            <button type="button" class="btn btn-link"> 添加</button>
    
          </div>
        </div>
        <br> 按钮样式2
        <div class="style2">
          <div class="dropdown">
            <button type="button" class="btn btn-outline-secondary dropdown-toggle" data-toggle="dropdown">
              <span class="glyphicon glyphicon-download-alt"></span>
            </button>
            <div class="dropdown-menu">
              <a class="dropdown-item" data-type="copy"> 复制本页数据</a>
              <a class="dropdown-item" data-type="pdf"> 导出为pdf</a>
              <a class="dropdown-item" data-type="csv"> 导出为csv</a>
              <a class="dropdown-item" data-type="print"> 导出为print</a>
              <a class="dropdown-item" data-type="excel"> 导出为excel</a>
              <a class="dropdown-item" data-type="colvis"> 导出为colvis</a>
            </div>
          </div>
          <div class="colvis">
            
          </div>
        </div>
        <br>按钮样式3
        <div class="style3">
          <div class="btn-group">
            <button type="button" class="btn btn-success" data-type="copy"> 复制本页数据</button>
            <button type="button" class="btn btn-primary" data-type="pdf"> 导出为pdf</button>
            <button type="button" class="btn btn-info" data-type="csv"> 导出为csv</button>
            <button type="button" class="btn btn-warning" data-type="print"> 导出为print</button>
            <button type="button" class="btn btn-danger" data-type="excel"> 导出为excel</button>
            
            <div class="colvis">
              
            </div>
          </div>
          <button type="button" class="btn btn-danger"> 添加</button>
    
        </div>
      </div>
    
      
      <table id="example" class="table table-bordered table-hover table-sm">
        <thead class="thead-light"> </thead>
      </table>
    
      
      <script src="../../plugins/jquery/jquery.min.js"></script>
      
      <script src="../../plugins/bootstrap/js/bootstrap.bundle.min.js"></script>
      
      <script src="../../plugins/datatables/jquery.dataTables.min.js"></script>
      <script src="../../plugins/datatables-bs4/js/dataTables.bootstrap4.min.js"></script>
      <script src="../../plugins/datatables-responsive/js/dataTables.responsive.min.js"></script>
      <script src="../../plugins/datatables-responsive/js/responsive.bootstrap4.min.js"></script>
      <script src="../../plugins/datatables-buttons/js/dataTables.buttons.min.js"></script>
      <script src="../../plugins/datatables-buttons/js/buttons.bootstrap4.min.js"></script>
      <script src="../../plugins/jszip/jszip.min.js"></script>
      <script src="../../plugins/pdfmake/pdfmake.min.js"></script>
      <script src="../../plugins/pdfmake/vfs_fonts.js"></script>
      <script src="../../plugins/datatables-buttons/js/buttons.html5.min.js"></script>
      <script src="../../plugins/datatables-buttons/js/buttons.print.min.js"></script>
      <script src="../../plugins/datatables-buttons/js/buttons.colVis.min.js"></script>
      
      <script src="../../dist/js/adminlte.min.js"></script>
      
      <script src="../../dist/js/demo.js"></script>
      
      <script>
        var url = "./demo.php" //搜索请求地址
        var delurl = "./demo.php" //删除请求地址
        var table = null; //表格
        var exportBtn= ''; //导出按钮，在datatable定义的按钮
      
        var request = (url, params, method = "GET") => {  //ajax封装
          return new Promise((resolve, reject) => { 
            $.ajax({ 
              type: method,
              url: url,
              cache: false, //禁用缓存
              data: params, //传入组装的参数
              dataType: "json",
              success: function (result) { 
                resolve(result);
              },
              error: function () { 
                reject('出错')
              }
            });
          })
        }
        // 修改
        function update(me) { 
          var row = table.rows($(me).parents('tr')).data()[0]; // 选中行数的数据
          alert('修改：' + JSON.stringify(row))
        }
        // 删除
        async function del(me) { 
          var row = table.rows($(me).parents('tr')).data()[0]; // 选中行数的数据
          var {  first_name } = row; //这里一般是主键，但是没有传过来的id值，这里就用name替代了
          var param = {  first_name, method: 'del' }; //传递的参数，也可以在添加一些判断条件
          var res = await request(delurl, param);
          if (res && res.code == 0) {  //判断返回的数据
            $(me).parents('tr').remove();
            alert('删除成功')
          }
        }
        // 搜索
        function search() { 
          table.draw();
        }
        // 导出-自定义按钮功能实现，只和data-type属性有关
        $("[data-type]").on('click', function () { 
          var name = $(this).attr('data-type');
          exportBtn = name;
          table.draw();
          exportBtn = '';
          $('.buttons-' + name).click();
        })
      
        $(document).ready(function () { 
          table = $("#example").DataTable({ 
            "processing": true,
            "serverSide": true,
            ajax: function (data, callback, settings) { 
              // 自定义搜索
              var formSearch = $("#search").serializeArray();
              data.search = formSearch;
              // 由于后端分页，导出数据就不是所有数据而是当前页，所以需要修改
              data.exportBtn = exportBtn
              console.log('提交的数据：', data);
              request(url, data).then(result => { 
                // console.log('返回的数据：', result);
                callback(result);
              })
            },
            "columns": [
              { 
                'sTitle': '第一个名字', //在thead中显示，有过原来有thead则替换
                "data": "first_name",
                "width": '20%',
                'render': function (data, type, row) { 
                  return "<span style='color:red'>" + data + '</span>';
                },
                'class': 'text-center', //添加一个css
              },
              { 
                'sTitle': '最后一个名字', //在thead中显示，有过原来有thead则替换
                "data": "last_name", "orderable": false
              },
              { 
                "sTitle": '操作',
                "orderable": false,
                'render': function (data, type, row) { 
                  return ` <button type="button" class="btn btn-sm btn-info" οnclick="update(this)">修改</> <button type="button" class="btn btn-sm btn-danger " οnclick="del(this)">删除</button> `;
                },
              }
      
            ],
            "language": //把文字变为中文
            { 
              "sProcessing": "加载中...",
              "sLengthMenu": "每页显示 _MENU_ 条记录",
              "sZeroRecords": "没有匹配结果",
              "sInfo": "第 _START_ - _END_ 条,共计 _TOTAL_ 条",
              "sInfoFiltered": "",
              "sInfoPostFix": "",
              "sSearch": "搜索:",
              "sUrl": "",
              "sEmptyTable": "表中数据为空",
              "sLoadingRecords": "载入中...",
              "sInfoThousands": ",",
              "oPaginate": { 
                "sPrevious": "<", //上一页
                "sNext": ">", //下一页
              },
            },
            'aLengthMenu': [3, 10, 20, 30], //设置每页显示记录的下拉菜单
            dom: 'Bt<"bottom"ilp><"clear">',
            "buttons": [
              {  extend: 'copy', text: '复制本页数据', },
              {  extend: 'pdf', text: '导出为pdf', filename: '数据表', title: '标题', },
              {  extend: 'csv', text: '导出为csv', filename: '数据表', title: '标题', },
              {  extend: "print", text: '导出为print', filename: '数据表', title: '标题', },
              {  extend: 'excel', text: '导出为excel', filename: '数据表', title: '标题', },
              {  extend: "colvis", text: ' <span class="glyphicon glyphicon-th-list"></span>', className: "btn btn-outline-secondary " },  //决定怎么显示
            ],
            "autoWidth": false,
            searching: false,
          })
          // 按钮设置，但是有个特殊的就是colvis
          $(".colvis").html($("#example_wrapper .buttons-colvis"));
          $("#example_wrapper .dt-buttons").hide(); //隐藏按钮，使用自定义按钮
      	  $("[name='example_length']").removeClass()
        });
      </script>
    </body>
    
    </html>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc5NzE4Mg_size_16_color_FFFFFF_t_70]: /images/20221120/b9d0b874d0534229808a07f019f13199.png