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

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

全局容器

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

handsontable 的初始化

function onIniHandsonTable(data) {
        $('#hot').empty();
        var hotElement = document.querySelector('#hot');
        var hotSettings =  {
                data :data,
                hiddenColumns: {
                      columns: [0],
                      indicators: true
                    },
                columns : [
                    {
                        data:'id',
                        readOnly: true
                    },{
                        data:'ersystem',
                        type:'text'
                    },{
                        data:'concursystem',
                        type:'text'
                    },{
                        data:'apisystem',
                        type:'text'
                    },{
                        data:'myeven',
                        type:'text'
                    },{
                        data:'expresssum',
                        type:'text'
                    },{
                        data: 'todaydate',
                        type: 'date',
                        dateFormat: 'YYYY-MM-DD'
                    },{
                        data:'enddate',
                        type: 'date',
                        dateFormat: 'YYYY-MM-DD'
                    },{
                        data:'delayday',
                        type: 'text',
                        validator:/^[0-9]*$/
                    }
                ],
                 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<ExpressCount> updatelist;
    List<ExpressCount> insertlist;
    List<Integer> 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;
    }