Handsontable 的数据保存(增删改查+导出excel)_handsontable csv导出样式-CSDN博客

来源：http://blog.csdn.net/sunscheung/article/details/74011249

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

全局容器

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

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);
    }
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206

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

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

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


    //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;
        }
    }

     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

更新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;
        }
    }
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

清空全局容器

clearContainer =function (){
        AllData = {};
        updatelist = [];
        delidslist =[];
        insertlist=[];
    }
     
     1
2
3
4
5
6
     
     1
2
3
4
5
6
     
     1
2
3
4
5
6

//获得当前时间
    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;
    }
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

加载用户数据，并绑定到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);
            }
        });
    }
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

对应的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();
    }
}
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121

参数封装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;
    }

}
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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;
    }
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
     
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

这里写图片描述