Handsontable 的数据保存(增删改查+导出excel)
项目用到handsontable 插件
根据官网 API写的handsontable初始化,
数据展示,
ajax请求,
参数封装,
Controller参数接受
全局容器
1 var AllData = {}; 2 var updatelist = []; 3 var delidslist =[]; 4 var insertlist=[];
handsontable 的初始化
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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]&¶ms[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 }
删除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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
对应的pojo类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
参数封装pojo类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
Controller接收
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }