本文主要描述,springboot-mybatis框架下上传excel,并将之导入mysql数据库的过程,如果用户id已存在,则进行更新修改数据库中该项信息,由于用到的是前后端分离技术,这里记录的主要是后端java部分,通过与前端接口进行对接实现功能,使用layui等前端框架与之对接,也可以自己写前端代码,本文以controller开始,从导入过程开始讲述,其中包括字典表的转换
1.在pom.xml文件中导入注解,主要利用poi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
<dependency> <groupid>org.apache.poi</groupid> <artifactid>poi-ooxml</artifactid> <version> 3.9 </version> </dependency> <dependency> <groupid>commons-fileupload</groupid> <artifactid>commons-fileupload</artifactid> <version> 1.3 . 1 </version> </dependency> <dependency> <groupid>commons-io</groupid> <artifactid>commons-io</artifactid> <version> 2.4 </version> </dependency> |
2.controller接口
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
@postmapping ( "/save" ) public string adduser( @requestparam ( "file" ) multipartfile file) { string filename = file.getoriginalfilename(); try { return sysservice.batchimport(filename, file); } catch (myexception e) { e.printstacktrace(); return e.getmessage(); } catch (exception e){ e.printstacktrace(); return "文件异常,导入失败" ; } } |
3.服务层接口
1
|
boolean import (string filename, multipartfile file) throws exception; |
4.业务层实现类
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
|
@transactional (readonly = false ,rollbackfor = exception. class ) @override public boolean import (string filename, multipartfile file) throws exception { map<string, integer> departmentmap = finddepartment(); map<string, integer> rolemap = findrole(); boolean notnull = false ; list<user> userlist = new arraylist<user>(); if (!filename.matches( "^.+\\.(?i)(xls)$" ) && !filename.matches( "^.+\\.(?i)(xlsx)$" )) { throw new myexception( "上传文件格式不正确" ); } boolean isexcel2003 = true ; if (filename.matches( "^.+\\.(?i)(xlsx)$" )) { isexcel2003 = false ; } inputstream is = file.getinputstream(); workbook wb = null ; if (isexcel2003) { wb = new hssfworkbook(is); } else { wb = new xssfworkbook(is); } sheet sheet = wb.getsheetat( 0 ); if (sheet!= null ){ notnull = true ; } user user; for ( int r = 1 ; r <= sheet.getlastrownum(); r++) { row row = sheet.getrow(r); if (row == null ){ continue ; } user = new user(); if ( row.getcell( 0 ).getcelltype() != 1 ){ throw new myexception( "导入失败(第" +(r+ 1 )+ "行,id单元格格式请设为文本格式)" ); } string id = row.getcell( 0 ).getstringcellvalue(); if (id== null || id.isempty()){ throw new myexception( "导入失败(第" +(r+ 1 )+ "行,id未填写)" ); } string name = row.getcell( 1 ).getstringcellvalue(); if (name== null || name.isempty()){ throw new myexception( "导入失败(第" +(r+ 1 )+ "行,姓名未填写)" ); } string department = row.getcell( 2 ).getstringcellvalue(); if (departmentmap.get(department)== null ){ throw new myexception( "导入失败(第" +(r+ 1 )+ "行,不存在此单位或单位未填写)" ); } string role = row.getcell( 3 ).getstringcellvalue(); if (rolemap.get(role)== null ){ throw new myexception( "导入失败(第" +(r+ 1 )+ "行,不存在此角色或角色未填写)" ); } date date; if (row.getcell( 4 ).getcelltype() != 0 ){ throw new myexception( "导入失败(第" +(r+ 1 )+ "行,入职日期格式不正确或未填写)" ); } else { date = row.getcell( 4 ).getdatecellvalue(); } user.setid(id); user.setname(name); user.setdepartmentid(( int ) departmentmap.get(department)); user.setroleid(( int ) rolemap.get(role)); user.setdate(date); userlist.add(user); } for (user user : userlist) { string id = user.getid(); int cnt = usermapper.selectbyid(id); if (cnt == 0 ) { usermapper.adduser(user); } else { usermapper.updateuserbyid(user); } } return notnull; } |
总结
以上所述是小编给大家介绍的springboot上传excel并将表格数据导入或更新mysql数据库,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持!
原文链接:https://blog.csdn.net/xyy1028/article/details/79054749