本文主要描述，springboot-mybatis框架下上傳excel，并將之導(dǎo)入mysql數(shù)據(jù)庫(kù)的過程，如果用戶id已存在，則進(jìn)行更新修改數(shù)據(jù)庫(kù)中該項(xiàng)信息，由于用到的是前后端分離技術(shù)，這里記錄的主要是后端java部分，通過與前端接口進(jìn)行對(duì)接實(shí)現(xiàn)功能，使用layui等前端框架與之對(duì)接，也可以自己寫前端代碼，本文以controller開始，從導(dǎo)入過程開始講述，其中包括字典表的轉(zhuǎn)換
1.在pom.xml文件中導(dǎo)入注解，主要利用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 "文件異常,導(dǎo)入失敗"; } } |
3.服務(wù)層接口
|
1
|
boolean import(string filename, multipartfile file) throws exception; |
4.業(yè)務(wù)層實(shí)現(xiàn)類
|
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) @overridepublic 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("導(dǎo)入失敗(第"+(r+1)+"行,id單元格格式請(qǐng)?jiān)O(shè)為文本格式)"); } string id = row.getcell(0).getstringcellvalue(); if(id==null || id.isempty()){ throw new myexception("導(dǎo)入失敗(第"+(r+1)+"行,id未填寫)"); } string name = row.getcell(1).getstringcellvalue(); if(name==null || name.isempty()){ throw new myexception("導(dǎo)入失敗(第"+(r+1)+"行,姓名未填寫)"); } string department = row.getcell(2).getstringcellvalue(); if(departmentmap.get(department)==null){ throw new myexception("導(dǎo)入失敗(第"+(r+1)+"行,不存在此單位或單位未填寫)"); } string role = row.getcell(3).getstringcellvalue(); if(rolemap.get(role)==null){ throw new myexception("導(dǎo)入失敗(第"+(r+1)+"行,不存在此角色或角色未填寫)"); } date date; if(row.getcell(4).getcelltype() !=0){ throw new myexception("導(dǎo)入失敗(第"+(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; } |
總結(jié)
以上所述是小編給大家介紹的springboot上傳excel并將表格數(shù)據(jù)導(dǎo)入或更新mysql數(shù)據(jù)庫(kù)，希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)服務(wù)器之家網(wǎng)站的支持！
原文鏈接：https://blog.csdn.net/xyy1028/article/details/79054749
