企业级分页(包含自动导出EXCEL)

本人写的这个分页是建立在公司的框架上,公司框架对分页的数据封装成了PageResult

package com.infoservice.po3.bean;

import java.util.List;

public class PageResult<T>
  implements DataBean
{
  private int curPage = 1;

  private int pageSize = 20;

  private int totalPages = 0;

  private int totalRecords = 0;

  private List<T> records = null;

  public int getCurPage()
  {
    return this.curPage;
  }

  public void setCurPage(int curPage)
  {
    this.curPage = curPage;
  }

  public int getPageSize()
  {
    return this.pageSize;
  }

  public void setPageSize(int pageSize)
  {
    this.pageSize = pageSize;
  }

  public int getTotalPages()
  {
    return this.totalPages;
  }

  public void setTotalPages(int totalPages)
  {
    this.totalPages = totalPages;
  }

  public int getTotalRecords()
  {
    return this.totalRecords;
  }

  public void setTotalRecords(int totalRecords)
  {
    this.totalRecords = totalRecords;
  }

  public List<T> getRecords()
  {
    return this.records;
  }

  public void setRecords(List<T> records)
  {
    this.records = records;
  }

  public String toString() {
    StringBuilder sbd = new StringBuilder();
    sbd.append("PageResult@");
    sbd.append(hashCode());
    sbd.append("[PSize=" + this.pageSize + "; ");
    sbd.append("TPSize=" + this.totalPages + "; ");
    sbd.append("TRSize=" + this.totalRecords + "; ");
    sbd.append("CPage=" + this.curPage + "; ");
    if (this.records.size() > 0) {
      sbd.append("Class=" + this.records.get(0).getClass());
    }
    sbd.append("]");
    return sbd.toString();
  }
}

查询出分页数据以后转化成json格式的数据,通过自己定义的分页脚本处理结果 : lkl_pageSplitPlugin.js

 /**
 * 分页组件
 * author:Liaokailin
 * email:284888279@qq.com
 */

function genePageSplitDate(){
            // 初始化数据
            var curPage = ps.curPage==null||ps.curPage=='undefined'||ps.curPage==0?1:ps.curPage ;// 当前页数 
            var pageSize = ps.pageSize==null||ps.pageSize=='undefined'?15:ps.pageSize ;//每页显示记录数
            var pageCount = ps.totalPages==null||ps.totalPages=='undefined'?0:ps.totalPages ;// 总页数
            var totalRecords = ps.totalRecords==null||ps.totalRecords=='undefined'?0:ps.totalRecords ; // 总记录数
            var jsonData = ps.records ;  //总记录
            $("#pageSplit").text("") ;
            //title提示
               var table = $("<table border='1' width='99%' id='pageSplitTitle'  class='table_list' style='border-bottom:1px solid #DAE0EE' />");
            var tr = $("<tr style='background-color:#DAE0EE' id='showTitle'  />");
            var td = $("<td nowrap='nowrap' align='left' />");
            var hiddenBut = title==null?"":title ;
            //var hiddenBut = title==null?"":title+"<img src='images/uup.png' id='hiddenBut' style='z-index: 5px'>&nbsp;&nbsp;";
            td.html(hiddenBut) ;
            td.appendTo(tr);
            tr.appendTo(table);        
            table.appendTo($("#pageSplit"));
            if (columns != 'undefined' && columns && columns.length > 0) {
                var table = $("<table border='1' width='99%' id='pageSplitData' class='table_list_01' style='border-bottom:1px solid #DAE0EE' />");
                var tr = $("<tr style='background-color:#DAE0EE'  />");
                //处理表头
                for (var i = 0; i < columns.length; i++) {
                    
                    if(columns[i]["sortable"]!=null&&columns[i]["sortable"]!="undefined"&&columns[i]["sortable"]==true){
                      var td = $("<td   id='sortable_"+columns[i]["dataIndex"]+"' onclick='sortAbleColumns(\""+columns[i]["dataIndex"]+"\")'/>");
                    }else{
                      var td = $("<td id='header'/>");
                    }
                    td.text(columns[i]["header"]==null?"":columns[i]["header"]);
                //    var div = $("<span id='seleteItem' style='display:none' onmouseover='showImg(this)' onmouseout='hiddenImg(this)' />") ;
                //    div.html("&nbsp;&nbsp;&nbsp;<img src='images/dn.png' />") ;
                //    div.appendTo(td) ;
                    td.appendTo(tr);
                }
                tr.appendTo(table);
                //处理数据
            if(jsonData&&jsonData.length>0){
                for (var i = 0 ;i<jsonData.length ;i++){
                     var tr = $("<tr />");
                        for (var j = 0; j < columns.length; j++) {
                            var td = $("<td />");
                            if(columns[j]["header"]=="序号"){
                                var index  ;
                                if(curPage==1){
                                   index = i+1 ;
                                }else{
                                   index = parseInt(curPage - 1) * pageSize + i+1;
                                }
                                td.text(index) ;
                            }else{
                                var cellData ;
                                if(jsonData[i][columns[j]["dataIndex"]]==null||jsonData[i][columns[j]["dataIndex"]]=='undefined'){
                                   cellData = "" ;
                                }else{
                                   cellData =jsonData[i][columns[j]["dataIndex"]] ;
                                }
                                if(columns[j]["renderer"]!='undefined'){
                                    if(typeof(columns[j]["renderer"])=='function'){
                                         var dataArray = {};
                                        dataArray.data = jsonData[i];
                                        cellData = columns[j].renderer(cellData, {}, dataArray); 
                                    }
                                   td.html(cellData);
                                }else{
                                  td.text(cellData);
                                }
                            }
                            if(columns[j]["align"]!='undefined'){
                              td.attr("align",columns[j]["align"]) ;
                            }else{
                                td.attr("align","center") ;
                            }
                            td.appendTo(tr);
                        }
                      tr.appendTo(table);
                // alert(jsonData[i]["loc"]) ;
                }
            }else{
              var tr = $("<tr/>"); 
              var td = $("<td colspan="+columns.length+" align='center'/>"); 
               td.text("没有满足条件的数据!!!") ;
               td.appendTo(tr);
               tr.appendTo(table);
            }
                
                table.appendTo($("#pageSplit"));
                
                
                //生成分页条码
                var table = $("<table border='1' width='99%'  class='table_query'  id='pageSplitNavi'/>"); 
                var tr = $("<tr/>");
                var td = $("<td/>");
                var navi="第<font color='red'>"+curPage+"</font>页/共"+pageCount+"页&nbsp;" ;
                var pageSizeArray = [15,20,50,100,200,500] ;
                navi=navi+"每页显示<select name='pageSize' id='pageSizeSelect' onchange='__extQuery__(this.value)' >" ;
                for(var i = 0 ;i<pageSizeArray.length ;i++){
                  if(pageSize==pageSizeArray[i]){
                  navi = navi + "<option value="+curPage+"_"+pageSizeArray[i]+" selected='selected'>"+pageSizeArray[i]+"</option>" ;
                  }else{
                  navi = navi + "<option value="+curPage+"_"+pageSizeArray[i]+">"+pageSizeArray[i]+"</option>" ;
                  }
                }
                navi=navi+"</select>条&nbsp;" ;
                navi = navi+"共"+totalRecords+"条记录" ;
                //navi=navi+"每页显示<select name='pageSize'><option value=15>15</option><option value=30>30</option><option value=50>50</option><option value=100>100</option><option value=200>200</option><option value=500>500</option></select>"
                if(curPage<=1){
                   navi = navi+geneButton("首页",null)+geneButton("上一页",null) ;
                }else{
                   navi = navi+geneButton("首页",1)+geneButton("上一页",curPage-1) ;
                }
                
                var startIndex; 
                var endIndex ;
                if(pageCount<=10){
                   startIndex = 1 ;
                   endIndex = pageCount ;
                }else{//总页数大于10的时候显示当前页附近的10个页码，这里设置为左边4右边5
                  startIndex = curPage-4 ;
                  endIndex = curPage+5 ;
                  //前面不足4则显示前10页
                  if(startIndex<1){
                      startIndex = 1 ;
                      endIndex = 10 ;
                  }
                  //后面不足5则显示后10页
                  else if(endIndex>pageCount){
                    endIndex = pageCount ;
                    startIndex = endIndex-10 + 1 ;
                  } 
                }
                
                for (var i=startIndex ; i<=endIndex ;i++){
                    navi = navi+geneButton(i,i) ;
                }
                if(curPage>=pageCount){
                   navi = navi+geneButton("下一页",null)+geneButton("尾页",null) ;
                }else{
                    navi = navi+geneButton("下一页",curPage+1)+geneButton("尾页",pageCount) ;
                }
                
                navi=navi+"&nbsp;<input type='text' id='newPage'  value="+curPage+" name='newPage' class='mini_txt' />&nbsp;<input type='button' value='GO'  id='goButton'   class='normal_btn'/>"
                td.html(navi) ;
                td.appendTo(tr);
                tr.appendTo(table);
                table.appendTo($("#pageSplit"));
                
                //控制颜色  
                $("table tr:nth-child(2n+3)").css("background-color", "#f3f3f3");
                //鼠标事件
                $("table tr:nth-child(2n)").mousemove(function(){
                   $(this).css("background-color", "#DAE0EE") ;
                }).mouseout(function(){
                   $(this).css("background-color", "#ffffff") ;
                });
                //绑定按钮单击事件
//                $("#hiddenBut").bind('click', function() {
//                    if (this.src.indexOf("uup.png") == -1) {
//                        $("#pageSplitData,#pageSplitNavi").slideToggle("slow");
//                        this.src = "images/uup.png";
//                    } else {
//                        $("#pageSplitData,#pageSplitNavi").slideToggle("slow");
//                        this.src = "images/ddn.png";
//                    }
//                });
                
                  $("#showTitle").bind('click', function() {
                        $("#pageSplitData,#pageSplitNavi").slideToggle("fast");
                });
                
                $("#goButton").bind('click', function() {
                    if(pageCount==0){
                       $(this).attr("disabled",true) ;
                       return ;
                     }
                     var pageIndex = $("input[name='newPage']").val() ;
                     if(!isNaN(pageIndex)&&parseInt(pageIndex)>=1&&parseInt(pageIndex)<=pageCount){
                          __extQuery__(pageIndex) ;
                     }else{
                       alert("请输入正确的页码!!!") ;
                       $("input[name='newPage']").val(curPage) ;
                     }
                     
                });
                
             
        
            }
            
}

   function sortAbleColumns(data){
         if(sortable=='undefined'){
          alert("排序出错，联系管理员指定排序标识!!!") ;           
          return ;
         }
         
         if(sortable==null){
           sortable = "" ;
         }
           //var img = $("<img src='images/uup.png' style='z-index: 5px' />");
          //alert($("#sortable_"+data+"").html()) ;
          //$("#sortable_"+data+"").text($("#sortable_"+data+"").html()+"<img src='images/uup.png' style='z-index: 5px' />") ;
          //$("#sortable_"+data+"").html($("#sortable_"+data+"").html()+"hello") ;
         if(sortable.indexOf(data)!=-1){  //表示存在，降序
             var temp = data ;
           data = data + " desc " ; 
           
          
       //   this.text(this.text()+"hello") ;
        sortable = sortable.removeStr(temp) ; //替换json
         }else{ //不存在，升序
            sortable  = sortable + data;
            data = data + " asc " ;
     }
         //alert(sortable) ;
         var splitParam = ps.curPage+"_"+ps.pageSize ;
         
         __extQuery__(splitParam,data) ;
   }

   function geneButton(buttonValue,pageIndex){
      var but = "&nbsp;<input type='button' class='normal_btn' value="+buttonValue+" " ;
      if(pageIndex==null){
          but = but+" disabled='disabled' " ;
      } else{
           if(ps.curPage == pageIndex){
             but = but+"  onclick='__extQuery__("+pageIndex+") ;' style='border-color: red' " ;
          } else{
             but = but+" onclick='__extQuery__("+pageIndex+") ;' " ;
          } 
      }
      but = but +"/>" ;
      return but ;
   }
 
 /**
  * 查询
   */
function __extQuery__(page,sort){
    //验证表单操作
         var urlValue ;
         if(isNaN(page)){   
             var pageArray = page.split("_") ;
             //处理修改pageSize以后计算得到总页数如果小于当前页，则将当前页置为该总页数
            if(parseInt((parseInt(ps.totalRecords+parseInt(pageArray[1]))-1)/pageArray[1])<pageArray[0]){  
               pageArray[0] = parseInt((parseInt(ps.totalRecords+parseInt(pageArray[1]))-1)/pageArray[1]) ;
            }// 
                if(sort!='undefined'&&sort!=null){
                urlValue = url+(url.lastIndexOf("?") == -1?"?":"&")+"curPage="+pageArray[0]+"&pageSize="+pageArray[1]+"&sortColumn="+sort+"&ts="+new Date().getTime()  ;                    
                }else{
                urlValue = url+(url.lastIndexOf("?") == -1?"?":"&")+"curPage="+pageArray[0]+"&pageSize="+pageArray[1]+"&ts="+new Date().getTime()  ;
                }
            }else{
                    var pageS  ;
                    if(ps==null||ps==""){
                        pageS = 15 ;
                    }else{
                       pageS = ps.pageSize ;
                    }
//                    urlValue = url+(url.lastIndexOf("?") == -1?"?":"&")+"curPage="+page+"&ts="+new Date().getTime() ;
                     urlValue = url+(url.lastIndexOf("?") == -1?"?":"&")+"curPage="+page+"&pageSize="+pageS+"&ts="+new Date().getTime()  ;
            }
    //获取表单参数
        var para = getFormPara($("form[name=fm]"));
        //alert(para["dname"]) ;
     $.ajax({
            url : urlValue ,
            data : para,
            async : false,
            dataType : "json",
            success : function(data) {
                //alert(data) ;
                ps = data.ps;
                genePageSplitDate() ;
                }
            });
}



/**
 * 导出excel
 */
function _exportDataToExcel_(flag){
   $(this).attr("disabled",true) ;
   var urlValue ; 
   var pageS  ;
   if (ps == null || ps == "") {
        pageS = 15;
    } else {
        pageS = ps.pageSize;
    }
    if (flag == 1) { // 导出当前页数据
//        var reg = /json/g ;
//        url = url.replace(reg,"do") ; //替换json
      urlValue = url+(url.lastIndexOf("?") == -1?"?":"&")+"curPage="+ps.curPage+"&pageSize="+pageS+"&exportFlag=exportFlag&ts="+new Date().getTime()  ;
   }else if(flag==2){//导出所有数据  excle2003最大容量65536
         urlValue = url+(url.lastIndexOf("?") == -1?"?":"&")+"curPage="+1+"&pageSize="+65536+"&exportFlag=exportFlag&ts="+new Date().getTime()  ;
   }
     //处理columns数据
   var columnsValue  ;
       for (var j = 0; j < columns.length; j++) {
           if(columns[j]["isExport"]=="yes"){
             columnsValue = columnsValue+columns[j]["header"]+","+columns[j]["dataIndex"]+";" ;               
           }
       }
   //    alert(columnsValue) ;
   //获取表单参数
    var para = getFormPara($("form[name=fm]"));
    var fm = $("form[name=fm]") ;
    //var hid = $("<input/>") ;
    var hid = $("<input type='hidden' name='columns' value='"+columnsValue+"' />") ;
    //hid.attr("type","hidden").attr("value",columnsValue).attr("name","columns") ;
    hid.appendTo(fm)  ;
    //var hidden = $("input type='hidden' value='"+columnsValue+"' id='columns'") ;
    //value= urlValue+"&columns="+columnsValue+""  ;
    fm.attr("action",urlValue) ;
    //fm.attr("mothod","post") ;
    fm.submit() ;
    //fm.action = urlValue;
    //alert(urlValue) ;
    //fm.method="post" ;
//     $.ajax({
//            url : urlValue ,
//            data : para,
//            async : false,
//            dataType : "json",
//            success : function(data) {
//            if("error"==data.result){
//               alert("数据导出失败，请与管理员联系") ;
//            }else{
//               alert("数据导出成功!位于:"+data.result+"") ;
//            }
//            
//             }
//        });
//   
}


   
   
/**
 * 将表单中各域的值自动封装成参数对象
 */
function getFormPara(oFrm, obj){
     
    oFrm = $(oFrm)[0];
    var len = oFrm.elements.length;
    var ret;
    if (obj && obj instanceof Object) {
        ret = obj;
    }
    else {
        ret = {};
    }
    for (var i = 0; i < len; i++) {
        var oEle = oFrm.elements[i];
        // alert(oEle.type) ;
        if (oEle.type === "radio") {
            if (oEle.checked) {
                ret[oEle.name] = oEle.value.trim();
            }
        }
        else if (oEle.type === "checkbox") {
            var curVal = ret[oEle.name];
            if (curVal === undefined) {
                ret[oEle.name] = [];
            }
            if (oEle.checked) {
                ret[oEle.name].push(oEle.value.trim());
            }
        }
         //遇到要操作的type可以自己手动添加 ,不要直接使用else
         else if(oEle.type === "text"||oEle.type ==="select-one" ||oEle.type ==="select-multiple" ){ 
        // else if(oEle.type === "button"){
             ret[oEle.name] = oEle.value;
        }
    }
//    if(columns!=null){
//       ret["columns"] = columns ;
//    }
//    
    return ret;
}

 
String.prototype.trim = function(){
    return this.replace(/(^\s*)|(\s*$)/g, "");
};
/**
 * 删除字符串操作 
 * author : Liaokailin
 * @param {} str
 */
String.prototype.removeStr = function(str){
    if(str!=null&&str!=""){
        var index = this.indexOf(str) ;
        if(index!=-1){
            if(index==0){
              return  this.substring(str.length,this.length) ;
            }else if(index==this.length){
                return this.substring(0,index) ;
            }
            else{
              return this.substr(0,index)+this.substr(index+str.length,this.length) ;
            }
        }else{
          alert("没有指定的子串") ;
          return this ;
        }
    }else{
      alert("不能删除空串!") ;
      return this ;
    }
};

 
 

jsp页面处理起来就简单多了:不管是查询什么字段只要给定一个columns :例如:

  <div id="pageSplit" style="z-index: 10px" ></div>
  </center>
  <script type="text/javascript">
    var title ="廖凯林分页数据测试【样式待修改】" ;
    var ps ;  
    var sortable ; //排序标识
    var url = "<%=request.getContextPath() %>/jquery/JqueryPractice7Action/queryUserOrder.json?query=1"  ;
    var columns = [
                {header: "序号",align:'center',isExport:"no"},  //获取序号不再需要renderer:getIndex ，只需指定header: "序号" ;
                {header: "USER_ID", dataIndex:'USER_ID', align:'center',renderer:mySelect,isExport:"yes",sortable:true},
                {header: "NAME",dataIndex: 'USER_NAME', align:'center',isExport:"yes",sortable:true},                
                {header: "RNAME",dataIndex: 'REAL_NAME', align:'center',isExport:"yes",sortable:true},                
                {header: "操作", dataIndex: 'VIN',renderer:operateLink}    
              ];
              
   function mySelect(value,metaDate,record){
       return value ;
    }
    
    function operateLink(value,meta,record){
       return "<a href='#' onclick='alert(\"修改\")' >修改</a>&nbsp;<a href='#' onclick='alert(\"删除\")' >删除</a>" ;
     } 
     __extQuery__(1) ;
  </script>

最后就是导出excel了,在页面添加两个按钮:

  <td style="background-image: "><input type="button"  value="查询" class="normal_btn" onclick="__extQuery__(1);"/>
          <input type="button" id="exportCurrPageData"   value="导出当前页数据" class="normal_btn"  onclick="_exportDataToExcel_(1)"/>
          <input type="button" id="exprotAllData"   value="导出所有数据" class="normal_btn"  onclick="_exportDataToExcel_(2)"/>
         </td>