Handsontable 的数据保存(增删改查+导出excel)

项目用到handsontable 插件 
根据官网 API写的handsontable初始化, 
数据展示, 
ajax请求, 
参数封装, 
Controller参数接受

全局容器

1     var AllData = {};
2     var updatelist = [];
3     var delidslist =[];
4     var insertlist=[];

handsontable 的初始化

  1 function onIniHandsonTable(data) {
  2         $('#hot').empty();
  3         var hotElement = document.querySelector('#hot');
  4         var hotSettings =  {
  5                 data :data,
  6                 hiddenColumns: {
  7                       columns: [0],
  8                       indicators: true
  9                     },
 10                 columns : [
 11                     {
 12                         data:'id',
 13                         readOnly: true
 14                     },{
 15                         data:'ersystem',
 16                         type:'text'
 17                     },{
 18                         data:'concursystem',
 19                         type:'text'
 20                     },{
 21                         data:'apisystem',
 22                         type:'text'
 23                     },{
 24                         data:'myeven',
 25                         type:'text'
 26                     },{
 27                         data:'expresssum',
 28                         type:'text'
 29                     },{
 30                         data: 'todaydate',
 31                         type: 'date',
 32                         dateFormat: 'YYYY-MM-DD'
 33                     },{
 34                         data:'enddate',
 35                         type: 'date',
 36                         dateFormat: 'YYYY-MM-DD'
 37                     },{
 38                         data:'delayday',
 39                         type: 'text',
 40                         validator:/^[0-9]*$/
 41                     }
 42                 ],
 43                  stretchH: 'all',
 44                  autoWrapRow: true,
 45                  rowHeaders: true,
 46                  colHeaders : [ 'ID', ' ERSystem ', ' ConcurSystem ','APISystem','MyEven','ExpressSum','TodayDate','EndDate','DelayDay' ],
 47                  columnSorting: true,
 48                  contextMenu:true,
 49                  sortIndicator: true,
 50                  dropdownMenu:  ['filter_by_condition', 'filter_by_value','filter_action_bar'],
 51                  //dropdownMenu:true,
 52                  filters: true,
 53                  renderAllRows: true,
 54                  search: true,
 55 
 56                  afterDestroy (){
 57                     // 移除事件
 58                      Handsontable.Dom.removeEvent(save, 'click', saveData);
 59                      loadDataTable();
 60                  },
 61                  beforeRemoveRow:function(index,amount){
 62                      var ids = [];
 63                     //封装id成array传入后台
 64                      if(amount!=0){
 65                          for(var i = index;i<amount+index;i++){
 66                          var rowdata = hot.getDataAtRow(i);
 67                           ids.push(rowdata[0]);
 68                      }
 69                          delExpressCount(ids);
 70                           screening();
 71                      }
 72                  },
 73 
 74                  afterChange:function(changes, source){
 75                         //params 参数 1.column num , 2,id, 3,oldvalue , 4.newvalue
 76                         var params =[];
 77                         if(changes!=null){  
 78                         var index = changes[0][0];//行号码
 79                         var rowdata = hot.getDataAtRow(index);
 80                         params.push(rowdata[0]);
 81                         params.push(changes[0][1]);
 82                         params.push(changes[0][2]);
 83                         params.push(changes[0][3]);
 84 
 85                         //仅当单元格发生改变的时候,id!=null,说明是更新
 86                         if(params[2]!=params[3]&&params[0]!=null){
 87                             var data={
 88                                     id:rowdata[0],
 89                                     ersystem:rowdata[1],
 90                                     concursystem:rowdata[2],
 91                                     apisystem:rowdata[3],
 92                                     myeven:rowdata[4],
 93                                     expresssum:rowdata[5],
 94                                     todaydate:rowdata[6],
 95                                     enddate:rowdata[7],
 96                                     delayday:rowdata[8]
 97                             }
 98                             updateExpressCount(data);
 99                         }
100                     }
101                  }
102             }
103 
104             hot = new Handsontable(hotElement,hotSettings);
105              //数据导入
106              var button = {excel: document.getElementById('excelexport')};
107              var exportPlugin = hot.getPlugin('exportFile'); 
108              var rows = hot.countRows();
109              var cols = hot.countCols();
110              button.excel.addEventListener('click', function() {
111                     exportPlugin.downloadFile('csv', {
112                         filename: 'Expresscount'+'-'+getNowFormatDate(),
113                         rowHeaders:false,
114                         columnHeaders:true,
115                     });
116                   });
117              //数据导入
118             //插入的数据的获取
119             function insertExpressCount(){
120                 var idsdata = hot.getDataAtCol(0);//所有的id
121                 for(var i=0;i<idsdata.length;i++){
122                     //id=null时,是插入数据,此时的i正好是行号
123                     if(idsdata[i]==null){
124                         //获得id=null时的所有数据封装进data
125                         var rowdata = hot.getDataAtRow(i);
126                         //var collength = hot.countCols();
127                         if(rowdata!=null){
128                             var data={
129                                     ersystem:rowdata[1],
130                                     concursystem:rowdata[2],
131                                     apisystem:rowdata[3],
132                                     myeven:rowdata[4],
133                                     expresssum:rowdata[5],
134                                     todaydate:rowdata[6],
135                                     enddate:rowdata[7],
136                                     delayday:rowdata[8]
137                                 }
138                             insertlist.push(data);
139                         }
140                     }
141                 }
142                 if(insertlist.length!=0){
143                       AllData.insertlist = insertlist;
144                 }
145 
146             }
147 
148             saveData =function (){
149                 //插入的数据的获取
150                 insertExpressCount();
151                 if(JSON.stringify(AllData) != "{}"&&validresult){
152                     $.ajax({
153                         url:'globalprocess', 
154                         type:'post',
155                         dataType:'json',
156                         contentType:'application/json',
157                         data:JSON.stringify(AllData),
158                         success:function(rdata){
159                             if(rdata.success){
160                                 $.alert({
161                                     title: '消息提示',
162                                     type: 'blue',
163                                     content: '保存成功.',
164                                 });
165                                 //保存以后重置全局容器
166                                 clearContainer();
167                                 //销毁
168                                 hot.destroy();
169                             }
170                             else {
171                                 $.alert({
172                                     title: '错误提示',
173                                     type: 'red',
174                                     content: '保存数据失败.',
175                                 });
176 
177                             }
178                         },
179                         error:function () {
180                             $.alert({
181                                     title: '错误提示',
182                                     type: 'red',
183                                     content: '请求失败.',
184                                 });
185                             clearContainer();
186                         }
187                     })
188                 }else{
189                     if(!validresult){
190                          $.alert({
191                                 title: '错误提示',
192                                 type: 'red',
193                                 content: '数据类型错误.',
194                             });
195                     }else{
196                       $.alert({
197                             title: '错误提示',
198                             type: 'red',
199                             content: '没有添加或修改数据.',
200                         });
201                     }
202                 }
203             }
204           //绑定事件保存数据
205           Handsontable.Dom.addEvent(save, 'click', saveData);
206     }

View Code

删除list封装([ id1,id2,…])

 1 //删除的优先级最高
 2     function  delExpressCount (ids){
 3         //传入的ids.length不可能为0
 4             $.each(ids,function(index,id){
 5                 if(id!=null){
 6                     delidslist.push(id);
 7                 }
 8             });
 9             AllData.delidslist=delidslist;
10     }

删除的优先级别大于更新,如果一条数据被更新了又被删除了,事实上不需要执行更新操作的

 1 //updatelist数据更新
 2     function screening(){
 3         if(updatelist.length!=0&&delidslist.lentgh!=0){
 4             for(var i=0;i<delidslist.length;i++){
 5                 for(var j=0;j<updatelist.length;j++){
 6                     if(updatelist[j].id == delidslist[i]){
 7                     //更新updatelist
 8                     updatelist.splice(j,1);
 9                     }
10                 }
11             }
12             //把updatelist封装进AllData
13             AllData.updatelist=updatelist;
14         }
15     }

更新list封装([{expresscount1},…])

 1 //更新数据
 2     function updateExpressCount(data){
 3         if(JSON.stringify(data) != "{}"){
 4             var flag = true;
 5             //判断记录是否存在,更新数据     
 6             $.each(updatelist,function(index,node){
 7                     if(node.id==data.id){
 8                         //此记录已经有了
 9                         flag = false;
10                         //用新得到的记录替换原来的,不用新增
11                         updatelist[index] = data;
12                     }
13                 });
14             flag&&updatelist.push(data);
15             //封装
16             AllData.updatelist=updatelist;
17         }
18     }

清空全局容器

clearContainer =function (){
        AllData = {};
        updatelist = [];
        delidslist =[];
        insertlist=[];
    }

 1 //获得当前时间
 2     function getNowFormatDate() {
 3         var date = new Date();
 4         var seperator1 = "-";
 5         var seperator2 = ":";
 6         var month = date.getMonth() + 1;
 7         var strDate = date.getDate();
 8         if (month >= 1 && month <= 9) {
 9             month = "0" + month;
10         }
11         if (strDate >= 0 && strDate <= 9) {
12             strDate = "0" + strDate;
13         }
14         var currentdate = date.getFullYear() + seperator1 + month + seperator1 + strDate
15                 + " " + date.getHours() + seperator2 + date.getMinutes()
16                 + seperator2 + date.getSeconds();
17         return currentdate;
18     }

加载用户数据，并绑定到handsontable

 1 function loadDataTable(){
 2         showWait(); 
 3         $.ajax({
 4             url:'fillList',
 5             type:'post',
 6             dataType:'json',
 7             success:function(rdata){
 8                 closeWait();
 9                 var convertData = [];
10                 if (rdata && typeof rdata === "object") {
11                     //重新封装数据,因为时间类型不符合要求
12                     $.each(rdata,function(index,node){
13                         convertData.push({
14                             id:node.id,
15                             myeven:node.myeven,
16                             expresssum:node.expresssum,
17                             ersystem:node.ersystem,
18                             concursystem:node.concursystem,
19                             apisystem :node.apisystem,
20                             todaydate :common.timestampToDate(node.todaydate),
21                             enddate:common.timestampToDate(node.enddate),
22                             delayday:node.delayday
23                             });
24                     });
25                     onIniHandsonTable(convertData);
26                 }
27                 else{
28                     $.alert({
29                         title: '消息提示',
30                         type: 'red',
31                         content: '加载数据失败.',
32                     });
33                 }   
34             },
35             error:function(e,j,t){
36                 closeWait();
37                 $.alert({
38                     title: '错误提示',
39                     type: 'red',
40                     content: '加载数据错误.',
41                 });
42                 console.log('express count/fill error:'+j+','+t);
43             }
44         });
45     }

View Code

对应的pojo类

  1 public class ExpressCount {
  2     private Integer id;
  3 
  4     private Integer ersystem;
  5 
  6     private Integer concursystem;
  7 
  8     private Integer apisystem;
  9 
 10     private Integer myeven;
 11 
 12     private Integer expresssum;
 13 
 14     private Date todaydate;
 15 
 16     private Date enddate;
 17 
 18     private Integer delayday;
 19 
 20     private Date createdate;
 21 
 22     private Integer createuserid;
 23 
 24     private String createusername;
 25 
 26     public Integer getId() {
 27         return id;
 28     }
 29 
 30     public void setId(Integer id) {
 31         this.id = id;
 32     }
 33 
 34     public Integer getErsystem() {
 35         return ersystem;
 36     }
 37 
 38     public void setErsystem(Integer ersystem) {
 39         this.ersystem = ersystem;
 40     }
 41 
 42     public Integer getConcursystem() {
 43         return concursystem;
 44     }
 45 
 46     public void setConcursystem(Integer concursystem) {
 47         this.concursystem = concursystem;
 48     }
 49 
 50     public Integer getApisystem() {
 51         return apisystem;
 52     }
 53 
 54     public void setApisystem(Integer apisystem) {
 55         this.apisystem = apisystem;
 56     }
 57 
 58     public Integer getMyeven() {
 59         return myeven;
 60     }
 61 
 62     public void setMyeven(Integer myeven) {
 63         this.myeven = myeven;
 64     }
 65 
 66     public Integer getExpresssum() {
 67         return expresssum;
 68     }
 69 
 70     public void setExpresssum(Integer expresssum) {
 71         this.expresssum = expresssum;
 72     }
 73 
 74     public Date getTodaydate() {
 75         return todaydate;
 76     }
 77 
 78     public void setTodaydate(Date todaydate) {
 79         this.todaydate = todaydate;
 80     }
 81 
 82     public Date getEnddate() {
 83         return enddate;
 84     }
 85 
 86     public void setEnddate(Date enddate) {
 87         this.enddate = enddate;
 88     }
 89 
 90     public Integer getDelayday() {
 91         return delayday;
 92     }
 93 
 94     public void setDelayday(Integer delayday) {
 95         this.delayday = delayday;
 96     }
 97 
 98     public Date getCreatedate() {
 99         return createdate;
100     }
101 
102     public void setCreatedate(Date createdate) {
103         this.createdate = createdate;
104     }
105 
106     public Integer getCreateuserid() {
107         return createuserid;
108     }
109 
110     public void setCreateuserid(Integer createuserid) {
111         this.createuserid = createuserid;
112     }
113 
114     public String getCreateusername() {
115         return createusername;
116     }
117 
118     public void setCreateusername(String createusername) {
119         this.createusername = createusername == null ? null : createusername.trim();
120     }
121 }

View Code

参数封装pojo类

 1 public class ExpressCountDataList {
 2     List<ExpressCount> updatelist;
 3     List<ExpressCount> insertlist;
 4     List<Integer> delidslist;
 5 
 6     public List<ExpressCount> getUpdatelist() {
 7         return updatelist;
 8     }
 9     public void setUpdatelist(List<ExpressCount> updatelist) {
10         this.updatelist = updatelist;
11     }
12     public List<ExpressCount> getInsertlist() {
13         return insertlist;
14     }
15     public void setInsertlist(List<ExpressCount> insertlist) {
16         this.insertlist = insertlist;
17     }
18     public List<Integer> getDelidslist() {
19         return delidslist;
20     }
21     public void setDelidslist(List<Integer> delidslist) {
22         this.delidslist = delidslist;
23     }
24 
25 }

View Code

Controller接收

 1 /**
 2      * 保存编辑后table数据
 3      * @param AllData(删除,更新,插入)
 4      * @return
 5      */
 6     @ResponseBody
 7     @RequestMapping("/globalprocess")
 8     public Page globalprocess(@RequestBody ExpressCountDataList AllData){
 9         Page page = new Page();
10 
11         List<ExpressCount> updatelist =AllData.getUpdatelist();
12         List<ExpressCount> insertlist =AllData.getInsertlist();
13         List<Integer> delidslist=AllData.getDelidslist();
14         //更新
15         boolean result = expressCountService.globalprocess(updatelist,insertlist,delidslist);
16         if(result == true){
17             page.setErrorCode(0);
18             page.setSuccess(true);
19             page.setMessage("操作成功");
20         }else{
21             page.setErrorCode(0);
22             page.setSuccess(true);
23             page.setMessage("操作失败");
24         }
25         return page;
26     }

View Code

 

转载于:https://www.cnblogs.com/yuwenjing0727/p/8259722.html