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

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

全局容器

var AllData = {};
var updatelist = [];
var delidslist =[];
var insertlist=[];

handsontable 的初始化

function onIniHandsonTable(data) {
KaTeX parse error: Expected 'EOF', got '#' at position 3: ('#̲hot').empty(); …/
}
],
stretchH: ‘all’,
autoWrapRow: true,
rowHeaders: true,
colHeaders : [ ‘ID’, ’ ERSystem ', ’ ConcurSystem ',‘APISystem’,‘MyEven’,‘ExpressSum’,‘TodayDate’,‘EndDate’,‘DelayDay’ ],
columnSorting: true,
contextMenu:true,
sortIndicator: true,
dropdownMenu: [‘filter_by_condition’, ‘filter_by_value’,‘filter_action_bar’],
//dropdownMenu:true,
filters: true,
renderAllRows: true,
search: true,

             afterDestroy (){
                // 移除事件
                 Handsontable.Dom.removeEvent(save, 'click', saveData);
                 loadDataTable();
             },
             beforeRemoveRow:function(index,amount){
                 var ids = [];
                //封装id成array传入后台
                 if(amount!=0){
                     for(var i = index;i<amount+index;i++){
                     var rowdata = hot.getDataAtRow(i);
                      ids.push(rowdata[0]);
                 }
                     delExpressCount(ids);
                      screening();
                 }
             },

             afterChange:function(changes, source){
                    //params 参数 1.column num , 2,id, 3,oldvalue , 4.newvalue
                    var params =[];
                    if(changes!=null){  
                    var index = changes[0][0];//行号码
                    var rowdata = hot.getDataAtRow(index);
                    params.push(rowdata[0]);
                    params.push(changes[0][1]);
                    params.push(changes[0][2]);
                    params.push(changes[0][3]);

                    //仅当单元格发生改变的时候,id!=null,说明是更新
                    if(params[2]!=params[3]&&params[0]!=null){
                        var data={
                                id:rowdata[0],
                                ersystem:rowdata[1],
                                concursystem:rowdata[2],
                                apisystem:rowdata[3],
                                myeven:rowdata[4],
                                expresssum:rowdata[5],
                                todaydate:rowdata[6],
                                enddate:rowdata[7],
                                delayday:rowdata[8]
                        }
                        updateExpressCount(data);
                    }
                }
             }
        }

        hot = new Handsontable(hotElement,hotSettings);
         //数据导入
         var button = {excel: document.getElementById('excelexport')};
         var exportPlugin = hot.getPlugin('exportFile'); 
         var rows = hot.countRows();
         var cols = hot.countCols();
         button.excel.addEventListener('click', function() {
                exportPlugin.downloadFile('csv', {
                    filename: 'Expresscount'+'-'+getNowFormatDate(),
                    rowHeaders:false,
                    columnHeaders:true,
                });
              });
         //数据导入
        //插入的数据的获取
        function insertExpressCount(){
            var idsdata = hot.getDataAtCol(0);//所有的id
            for(var i=0;i<idsdata.length;i++){
                //id=null时,是插入数据,此时的i正好是行号
                if(idsdata[i]==null){
                    //获得id=null时的所有数据封装进data
                    var rowdata = hot.getDataAtRow(i);
                    //var collength = hot.countCols();
                    if(rowdata!=null){
                        var data={
                                ersystem:rowdata[1],
                                concursystem:rowdata[2],
                                apisystem:rowdata[3],
                                myeven:rowdata[4],
                                expresssum:rowdata[5],
                                todaydate:rowdata[6],
                                enddate:rowdata[7],
                                delayday:rowdata[8]
                            }
                        insertlist.push(data);
                    }
                }
            }
            if(insertlist.length!=0){
                  AllData.insertlist = insertlist;
            }

        }

        saveData =function (){
            //插入的数据的获取
            insertExpressCount();
            if(JSON.stringify(AllData) != "{}"&&validresult){
                $.ajax({
                    url:'globalprocess', 
                    type:'post',
                    dataType:'json',
                    contentType:'application/json',
                    data:JSON.stringify(AllData),
                    success:function(rdata){
                        if(rdata.success){
                            $.alert({
                                title: '消息提示',
                                type: 'blue',
                                content: '保存成功.',
                            });
                            //保存以后重置全局容器
                            clearContainer();
                            //销毁
                            hot.destroy();
                        }
                        else {
                            $.alert({
                                title: '错误提示',
                                type: 'red',
                                content: '保存数据失败.',
                            });

                        }
                    },
                    error:function () {
                        $.alert({
                                title: '错误提示',
                                type: 'red',
                                content: '请求失败.',
                            });
                        clearContainer();
                    }
                })
            }else{
                if(!validresult){
                     $.alert({
                            title: '错误提示',
                            type: 'red',
                            content: '数据类型错误.',
                        });
                }else{
                  $.alert({
                        title: '错误提示',
                        type: 'red',
                        content: '没有添加或修改数据.',
                    });
                }
            }
        }
      //绑定事件保存数据
      Handsontable.Dom.addEvent(save, 'click', saveData);
}

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

//删除的优先级最高
function delExpressCount (ids){
//传入的ids.length不可能为0
$.each(ids,function(index,id){
if(id!=null){
delidslist.push(id);
}
});
AllData.delidslist=delidslist;
}

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

//updatelist数据更新
function screening(){
    if(updatelist.length!=0&&delidslist.lentgh!=0){
        for(var i=0;i<delidslist.length;i++){
            for(var j=0;j<updatelist.length;j++){
                if(updatelist[j].id == delidslist[i]){
                //更新updatelist
                updatelist.splice(j,1);
                }
            }
        }
        //把updatelist封装进AllData
        AllData.updatelist=updatelist;
    }
}

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

//更新数据
function updateExpressCount(data){
if(JSON.stringify(data) != “{}”){
var flag = true;
//判断记录是否存在,更新数据
$.each(updatelist,function(index,node){
if(node.id==data.id){
//此记录已经有了
flag = false;
//用新得到的记录替换原来的,不用新增
updatelist[index] = data;
}
});
flag&&updatelist.push(data);
//封装
AllData.updatelist=updatelist;
}
}

清空全局容器

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

//获得当前时间
function getNowFormatDate() {
var date = new Date();
var seperator1 = “-”;
var seperator2 = “:”;
var month = date.getMonth() + 1;
var strDate = date.getDate();
if (month >= 1 && month <= 9) {
month = “0” + month;
}
if (strDate >= 0 && strDate <= 9) {
strDate = “0” + strDate;
}
var currentdate = date.getFullYear() + seperator1 + month + seperator1 + strDate
+ " " + date.getHours() + seperator2 + date.getMinutes()
+ seperator2 + date.getSeconds();
return currentdate;
}

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

function loadDataTable(){
    showWait(); 
    $.ajax({
        url:'fillList',
        type:'post',
        dataType:'json',
        success:function(rdata){
            closeWait();
            var convertData = [];
            if (rdata && typeof rdata === "object") {
                //重新封装数据,因为时间类型不符合要求
                $.each(rdata,function(index,node){
                    convertData.push({
                        id:node.id,
                        myeven:node.myeven,
                        expresssum:node.expresssum,
                        ersystem:node.ersystem,
                        concursystem:node.concursystem,
                        apisystem :node.apisystem,
                        todaydate :common.timestampToDate(node.todaydate),
                        enddate:common.timestampToDate(node.enddate),
                        delayday:node.delayday
                        });
                });
                onIniHandsonTable(convertData);
            }
            else{
                $.alert({
                    title: '消息提示',
                    type: 'red',
                    content: '加载数据失败.',
                });
            }   
        },
        error:function(e,j,t){
            closeWait();
            $.alert({
                title: '错误提示',
                type: 'red',
                content: '加载数据错误.',
            });
            console.log('express count/fill error:'+j+','+t);
        }
    });
}

对应的pojo类

public class ExpressCount {
private Integer id;

private Integer ersystem;

private Integer concursystem;

private Integer apisystem;

private Integer myeven;

private Integer expresssum;

private Date todaydate;

private Date enddate;

private Integer delayday;

private Date createdate;

private Integer createuserid;

private String createusername;

public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public Integer getErsystem() {
    return ersystem;
}

public void setErsystem(Integer ersystem) {
    this.ersystem = ersystem;
}

public Integer getConcursystem() {
    return concursystem;
}

public void setConcursystem(Integer concursystem) {
    this.concursystem = concursystem;
}

public Integer getApisystem() {
    return apisystem;
}

public void setApisystem(Integer apisystem) {
    this.apisystem = apisystem;
}

public Integer getMyeven() {
    return myeven;
}

public void setMyeven(Integer myeven) {
    this.myeven = myeven;
}

public Integer getExpresssum() {
    return expresssum;
}

public void setExpresssum(Integer expresssum) {
    this.expresssum = expresssum;
}

public Date getTodaydate() {
    return todaydate;
}

public void setTodaydate(Date todaydate) {
    this.todaydate = todaydate;
}

public Date getEnddate() {
    return enddate;
}

public void setEnddate(Date enddate) {
    this.enddate = enddate;
}

public Integer getDelayday() {
    return delayday;
}

public void setDelayday(Integer delayday) {
    this.delayday = delayday;
}

public Date getCreatedate() {
    return createdate;
}

public void setCreatedate(Date createdate) {
    this.createdate = createdate;
}

public Integer getCreateuserid() {
    return createuserid;
}

public void setCreateuserid(Integer createuserid) {
    this.createuserid = createuserid;
}

public String getCreateusername() {
    return createusername;
}

public void setCreateusername(String createusername) {
    this.createusername = createusername == null ? null : createusername.trim();
}

}

参数封装pojo类

public class ExpressCountDataList {
List updatelist;
List insertlist;
List delidslist;

public List<ExpressCount> getUpdatelist() {
    return updatelist;
}
public void setUpdatelist(List<ExpressCount> updatelist) {
    this.updatelist = updatelist;
}
public List<ExpressCount> getInsertlist() {
    return insertlist;
}
public void setInsertlist(List<ExpressCount> insertlist) {
    this.insertlist = insertlist;
}
public List<Integer> getDelidslist() {
    return delidslist;
}
public void setDelidslist(List<Integer> delidslist) {
    this.delidslist = delidslist;
}

}

Controller接收

/**
* 保存编辑后table数据
* @param AllData(删除,更新,插入)
* @return
*/
@ResponseBody
@RequestMapping("/globalprocess")
public Page globalprocess(@RequestBody ExpressCountDataList AllData){
Page page = new Page();

    List<ExpressCount> updatelist =AllData.getUpdatelist();
    List<ExpressCount> insertlist =AllData.getInsertlist();
    List<Integer> delidslist=AllData.getDelidslist();
    //更新
    boolean result = expressCountService.globalprocess(updatelist,insertlist,delidslist);
    if(result == true){
        page.setErrorCode(0);
        page.setSuccess(true);
        page.setMessage("操作成功");
    }else{
        page.setErrorCode(0);
        page.setSuccess(true);
        page.setMessage("操作失败");
    }
    return page;
}