Datatables 使用总结

行列转换

数据格式为

 var resData=res.data,data=[{type:'预约总量'},{type:'预约量'},{type:'爽约量'},{type:'受理量'},{type:'预约剩余量'}],r,s1=0,s2=0,s3=0,s4=0,s5=0;
              //行列转换
              for(var i=0;i<resData.length;i++){
                  r=resData[i];
                  if($('#pp1').text()=='全部业务'){
                      data[0][r.deptName]=r.countDept;
                      data[4][r.deptName]=r.countDept-r.countReverse;
                  }else{
                      data[0][r.deptName]=r.countClass;
                      data[4][r.deptName]=r.countClass-r.countReverse;
                  }
                  data[1][r.deptName]=r.countReverse;
                  data[2][r.deptName]=r.countFailure;
                  data[3][r.deptName]=r.countAccept;
                  s1+=r.countDept;
                  s2+=r.countReverse;
                  s3+=r.countFailure;
                  s4+=r.countAccept;
                  s5+=r.countDept-r.countReverse;
              }
              data[0].sum=s1;
              data[1].sum=s2;
              data[2].sum=s3;
              data[3].sum=s4;
              data[4].sum=s5;
              res.data=data;
              callback(res);
              $table1.DataTable().draw();
                $(window).resize();

计算Datatables的高度

$(window).resize(function() {
                var height=$('#main').height()-$('#search').height()-5,$dtable=$('#example_wrapper'), h;
                $('#tableArea').height(height);
                h= height-$dtable.children('.dt-buttons').outerHeight()-
                $('#example_info').outerHeight()-
                $dtable.find('.dataTables_scrollHead').outerHeight()-
                $dtable.find('.dataTables_scrollFoot').outerHeight()-20;
                $table.DataTable().settings()[0].oScroll.sY=h;
                $('.dataTables_scrollBody').css('height', h);
                $table.DataTable().draw();
              }).resize();

未进行后台分页添加序号

$table.DataTable().on('order.dt search.dt', function () {
              $table.DataTable().column(0, { search: 'applied', order: 'applied' }).nodes().each(function (cell, i) {
                  cell.innerHTML = i + 1;
              });
          }).draw();

未进行后台分页写法：
columns.push({data:'shortName'});
  columns.push({data:'cAccept'});
  columns.push({data:'tAccept'});
  for ( var i = 0,l=classLength; i < l; i++) {
    columns.push({data:'cAccept'+i});
    columns.push({data:'tAccept'+i});
  }
  columns.push({data:'cAcceptOther'});
  columns.push({data:'tAcceptOther'});
  columns.push({data:'cRatify'});
  columns.push({data:'tRatify'});
  for ( var i = 0,l=classLength; i < l; i++) {
    columns.push({data:'cRatify'+i});
    columns.push({data:'tRatify'+i});
  }
  columns.push({data:'cRatifyOther'});
  columns.push({data:'tRatifyOther'});
  columns.push({data:'cCharge'});
  columns.push({data:'tCharge'});
  columns.push({data:'cChargeR'});
  columns.push({data:'tChargeR'});
  columns.push({data:'cChargeN'});
  columns.push({data:'tChargeN'});
  columns.push({data:'cChargeP'});
  columns.push({data:'tChargeP'});
  $table.dataTable({
    scrollY: 200,
    scrollX: true,// 水平滚动
    //bProcessing : true,//显示加载中
    paging: false,// 禁止分页
    dom: 'Bfrtip',
    buttons: [{
      text:'导出excel',
      extend:'excel',
      footer:true,
      customizeData: function(data){
        var header=data.header,body=data.body,hs=[[],[],[]],l=header.length,$ts=$($table.DataTable().table().header()).children(),ts,th,r,c,v,f;
        
        for (var i=0; i<3; i++) {
          ts=$ts.eq(i).children();
          for(var j=0; j<ts.length; j++){
            th=ts.eq(j);
            r=Number(th.attr('rowspan')||1);
            c=Number(th.attr('colspan')||1);
            v=th.children('div').length?th.children().text():th.text();
            
            if (r == 1 && c == 1){
              hs[i].push(v);
              continue;
            }
            
            f=true;
            for (var ri=i,rl=i+r; ri<rl; ri++) {
              for(var ci=0; ci<c; ci++){
                if(f){
                  hs[ri].push(v);
                  f=false;
                } else {
                  hs[ri].push('');
                }
              }
            }
          }
        }
        for ( var i = 0; i < l; i++) {
          header[i]='';
        }
        data.body.unshift(hs[0],hs[1],hs[2]);
        exportData=data.body;
      },
      customize: function(xlsx){
        var sheet = xlsx.xl.worksheets['sheet1.xml'],mcs=$('mergeCells',sheet),col = $('col', sheet),
          count=4,l1=classLength,l2=columns.length-1,p=createCellPos(l2),data=exportData,res,ls,lsu,c,b;
        mcs.append('<mergeCell ref="A2:'+p+'2" />');
        mcs.append('<mergeCell ref="A3:A5" />');
        mcs.append('<mergeCell ref="B3:'+createCellPos(4+2*l1)+'3" />');
        mcs.append('<mergeCell ref="'+createCellPos(5+2*l1)+'3:'+createCellPos(8+4*l1)+'3" />');
        mcs.append('<mergeCell ref="'+createCellPos(9+4*l1)+'3:'+p+'3" />');
        for(var i=1;i<l2;){
          mcs.append('<mergeCell ref="'+createCellPos(i++)+'4:'+createCellPos(i++)+'4" />');
          count++;
        }
        mcs.attr( 'count', Number(mcs.attr( 'count' ))+count );
        //调整导出的Excel列宽
        for (var colnum = 0; colnum < 29; colnum++) {
            ls=0;
            for ( var i = 2; i < data.length; i++) {
                c=data[i][colnum];
                b=strWidth(c)>ls;
                if(b){
                  res=c;
                  
                  ls=strWidth(c);
                }
            }
            lsu=res?res.length:0;
            width = Math.round(((ls * 7 + 5) / 7 * 256) / 256);
            if(ls>lsu || lsu>50){
              $(col[colnum]).attr('width', width);
            }
        }
        
      }
    }/*,{
      text:'打印',
      extend:'print',
      footer:true,
      customize: function(win){
        var body= $(win.document.body);
        body.find('table').css('font-size', '11px').children('thead')
          .html($($table.DataTable().table().header()).html())
          .find('th').each(function(){
          var th=$(this);
          if(th.children('div').length){
            th.html(th.children().text());
          }
        });
        body.find('h1').css('text-align','center');
        body.find('tfoot th').addClass('ui-state-default');
      }
    }*/],
    fixedColumns: {
      leftColumns: 1
    },
    language:{
      info:'共_TOTAL_条数据',
      search : "查找:",
      zeroRecords : "暂无数据",
      //processing : '',
      loadingRecords : "请稍等",
      infoFiltered:   "(从 _MAX_ 条数据中过滤)",
      infoEmpty: "共0条数据"
    },
    ajax: function(data, callback, settings){
      var api=this.api(), numVal=function(v){
        return v ? typeof v == 'string' ? v * 1 : typeof v == 'number' ? v : 0 : 0;
      };
      
      $("#mask").css("height",$(document).height());     
      $("#mask").css("width",$(document).width());     
      $("#mask").show(); 
      
      $.post(basePath+'/stats/integrated-data',{
        sDate:$('#sDateVal').val(),
        eDate: $('#eDateVal').val(),
        dept:dept
      },function(res){
        $("#mask").hide();
        callback(res);
        
        for ( var i= 1,l=api.columns()[0].length; i < l; i++) {
          $(api.column(i).footer()).html(
            api.column(i).data().reduce(function(a,b) {
              return numVal(a) + numVal(b);
            })
          );
        }
        $(window).resize();
      },'json');
    },
    columnDefs:[ {
      targets: 0,
      type:'string',
      className:'tac',
      orderable:false,
      render: function(data,type,row){
        var id=row.orderId;
        id=id>9?''+id:'0'+id;
        return type=='sort'?id:data;
      }
    },{
      targets: '_all',
      width: '60px',
      type:'num',
      className:'tar',
      render: function(data){
        return data || 0;
      }
    }],
    columns:columns
  });

function createCellPos(n){
var ordA = 'A'.charCodeAt(0);
var ordZ = 'Z'.charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while(n>= 0) {
s = String.fromCharCode(n % len + ordA) + s;
n = Math.floor(n / len) - 1;
}
return s;
}

 

进行后台分页添加序号

//序号
    fnDrawCallback:function () {
        var api = this.api();
        var startIndex = api.context[0]._iDisplayStart;//获取本页开始的条数
        api.column(0).nodes().each(function(cell, i) {
            cell.innerHTML = startIndex + i + 1;
        });
    },

后台分页写法：
$table.dataTable({
    //scrollY: 200,
    scrollX: true,// 水平滚动
    deferRender: true,//当处理大数据量时，提高查询速度
    bFilter : false, //禁止搜索框
    ordering : false,//禁止排序
    pageLength : 10,
    serverSide : true, //开启服务器模式:启用服务器分页
    ajax : function(data, callback, settings) {
        var api = this.api();
        
        $("#mask").css("height", $(document).height());
        $("#mask").css("width", $(document).width());
        $("#mask").show();
        
        $.post(basePath + '/stats/bankAccept-getAccept',$.extend({}, data, {
          bankName : $bank.val(),
          registTypeDesc : $type.val(),
          sDate:$('#sDateVal').val(),
          eDate: $('#eDateVal').val(),
          state:$state.val()
        }),function(res){
          if (res.success) {
              callback(res.data);
          } else {
              alert('请求数据失败，请刷新页面');
          }
          $("#mask").hide();
          //重新计算
          $(window).resize();
        },'json');
    },
    //序号
    fnDrawCallback:function () {
        var api = this.api();
        var startIndex = api.context[0]._iDisplayStart;//获取本页开始的条数
        api.column(0).nodes().each(function(cell, i) {
            cell.innerHTML = startIndex + i + 1;
        });
    },
    columns:[
        {data:'bankName',width: '10px'},
        {data:'bankName'},
        {data:'registTypeDesc'},
        {data:'deveCaseno'},
        {data:'realestatecaseno'},
        {data:'userName'},
        {data:'gmtAcceptStart',width: '80px'},
        {data:'timeLimit',width: '50px'},
        {data:'state',width:'10px',render: function(data,type,row){
                var show='';
                if(data == '正常'){
                    show=' style=""';
                }
                if(data == '预警'){
                   show=' style="color:#FFC000"';
                }
                if(data == '超期'){
                    show=' style="color:#FF0000"';
                }
            return '<span'+show+'>'+data+'</span>';
          }
        }  
    ],
    columnDefs:[{
        targets: 1,
        className:'tal'
    },{
        targets: '_all',
        className:'tac'
    }],
    language:{
        processing : "处理中...",
      lengthMenu : "显示 _MENU_ 项结果",
      info : "显示第 _START_ 至 _END_ 项结果，共 _TOTAL_ 项",
      infoEmpty : "显示第 0 至 0 项结果，共 0 项",
      emptyTable : "暂无数据",
      infoFiltered:   "",
      infoThousands : ",",
      paginate : {
        first : "首页",
        previous : "上页",
        next : "下页",
        last : "末页"
      }    
    }
  });