Springboot上传excel并将表格数据导入或更新mySql数据库（转）

转自https://blog.csdn.net/xyy1028/article/details/79054749 亲测可用

本文主要描述，Springboot-mybatis框架下上传excel，并将之导入mysql数据库的过程，如果用户id已存在，则进行更新修改数据库中该项信息，由于用到的是前后端分离技术，这里记录的主要是后端java部分，通过与前端接口进行对接实现功能

1.在pom.xml文件中导入注解，主要利用POI

1. <dependency>
2. <groupId>org.apache.poi </groupId>
3. <artifactId>poi-ooxml </artifactId>
4. <version>3.9 </version>
5. </dependency>
6. <dependency>
7. <groupId>commons-fileupload </groupId>
8. <artifactId>commons-fileupload </artifactId>
9. <version>1.3.1 </version>
10. </dependency>
11. <dependency>
12. <groupId>commons-io </groupId>
13. <artifactId>commons-io </artifactId>
14. <version>2.4 </version>
15. </dependency>

2.entity实体类

1. public class User implements Serializable {
2. private Integer id;
4. private String name;
6. private String phone;
8. private String address;
10. private Date enrolDate;
12. private String des;
14. private static final long serialVersionUID = 1L;
16. public User(Integer id, String name, String phone, String address, Date enrolDate, String des) {
17. this.id = id;
18. this.name = name;
19. this.phone = phone;
20. this.address = address;
21. this.enrolDate = enrolDate;
22. this.des = des;
23. }
25. public User() {
26. super();
27. }
29. public Integer getId() {
30. return id;
31. }
33. public void setId(Integer id) {
34. this.id = id;
35. }
37. public String getName() {
38. return name;
39. }
41. public void setName(String name) {
42. this.name = name == null ? null : name.trim();
43. }
45. public String getPhone() {
46. return phone;
47. }
49. public void setPhone(String phone) {
50. this.phone = phone == null ? null : phone.trim();
51. }
53. public String getAddress() {
54. return address;
55. }
57. public void setAddress(String address) {
58. this.address = address == null ? null : address.trim();
59. }
61. public Date getEnrolDate() {
62. return enrolDate;
63. }
65. public void setEnrolDate(Date enrolDate) {
66. this.enrolDate = enrolDate;
67. }
69. public String getDes() {
70. return des;
71. }
73. public void setDes(String des) {
74. this.des = des == null ? null : des.trim();
75. }
77. @Override
78. public boolean equals(Object that) {
79. if ( this == that) {
80. return true;
81. }
82. if (that == null) {
83. return false;
84. }
85. if (getClass() != that.getClass()) {
86. return false;
87. }
88. User other = (User) that;
89. return ( this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))
90. && ( this.getName() == null ? other.getName() == null : this.getName().equals(other.getName()))
91. && ( this.getPhone() == null ? other.getPhone() == null : this.getPhone().equals(other.getPhone()))
92. && ( this.getAddress() == null ? other.getAddress() == null : this.getAddress().equals(other.getAddress()))
93. && ( this.getEnrolDate() == null ? other.getEnrolDate() == null : this.getEnrolDate().equals(other.getEnrolDate()))
94. && ( this.getDes() == null ? other.getDes() == null : this.getDes().equals(other.getDes()));
95. }
97. @Override
98. public int hashCode() {
99. final int prime = 31;
100. int result = 1;
101. result = prime * result + ((getId() == null) ? 0 : getId().hashCode());
102. result = prime * result + ((getName() == null) ? 0 : getName().hashCode());
103. result = prime * result + ((getPhone() == null) ? 0 : getPhone().hashCode());
104. result = prime * result + ((getAddress() == null) ? 0 : getAddress().hashCode());
105. result = prime * result + ((getEnrolDate() == null) ? 0 : getEnrolDate().hashCode());
106. result = prime * result + ((getDes() == null) ? 0 : getDes().hashCode());
107. return result;
108. }
109. }

3.Controller接口

1. @RestController
2. @RequestMapping( "/test/")
3. public class TestController {
5. @Autowired
6. private ITestService testService;
8. @PostMapping( "/import")
9. public boolean addUser(@RequestParam("file") MultipartFile file) {
10. boolean a = false;
11. String fileName = file.getOriginalFilename();
12. try {
13. a = testService.batchImport(fileName, file);
14. } catch (Exception e) {
15. e.printStackTrace();
16. }
17. return a;
18. }
20. }

4.服务层接口

1. public interface ITestService {
3. boolean batchImport(String fileName, MultipartFile file) throws Exception;
5. }

5.业务层实现类

1. @Service
2. @Transactional(readOnly = true)
3. public class TestServiceImpl implements ITestService {
5. @Autowired
6. private UserMapper userMapper;
9. @Transactional(readOnly = false,rollbackFor = Exception.class)
10. @Override
11. public boolean batchImport(String fileName, MultipartFile file) throws Exception {
13. boolean notNull = false;
14. List<User> userList = new ArrayList<User>();
15. if (!fileName.matches( "^.+\\.(?i)(xls)$") && !fileName.matches( "^.+\\.(?i)(xlsx)$")) {
16. throw new MyException( "上传文件格式不正确");
17. }
18. boolean isExcel2003 = true;
19. if (fileName.matches( "^.+\\.(?i)(xlsx)$")) {
20. isExcel2003 = false;
21. }
22. InputStream is = file.getInputStream();
23. Workbook wb = null;
24. if (isExcel2003) {
25. wb = new HSSFWorkbook(is);
26. } else {
27. wb = new XSSFWorkbook(is);
28. }
29. Sheet sheet = wb.getSheetAt( 0);
30. if(sheet!= null){
31. notNull = true;
32. }
33. User user;
34. for ( int r = 1; r <= sheet.getLastRowNum(); r++) {
35. Row row = sheet.getRow(r);
36. if (row == null){
37. continue;
38. }
40. user = new User();
42. if( row.getCell( 0).getCellType() != 1){
43. throw new MyException( "导入失败(第"+(r+ 1)+ "行,姓名请设为文本格式)");
44. }
45. String name = row.getCell( 0).getStringCellValue();
47. if(name == null || name.isEmpty()){
48. throw new MyException( "导入失败(第"+(r+ 1)+ "行,姓名未填写)");
49. }
51. row.getCell( 1).setCellType(Cell.CELL_TYPE_STRING);
52. String phone = row.getCell( 1).getStringCellValue();
53. if(phone== null || phone.isEmpty()){
54. throw new MyException( "导入失败(第"+(r+ 1)+ "行,电话未填写)");
55. }
56. String add = row.getCell( 2).getStringCellValue();
57. if(add== null){
58. throw new MyException( "导入失败(第"+(r+ 1)+ "行,不存在此单位或单位未填写)");
59. }
61. Date date;
62. if(row.getCell( 3).getCellType() != 0){
63. throw new MyException( "导入失败(第"+(r+ 1)+ "行,入职日期格式不正确或未填写)");
64. } else{
65. date = row.getCell( 3).getDateCellValue();
66. }
68. String des = row.getCell( 4).getStringCellValue();
70. user.setName(name);
71. user.setPhone(phone);
72. user.setAddress(add);
73. user.setEnrolDate(date);
74. user.setDes(des);
76. userList.add(user);
77. }
78. for (User userResord : userList) {
79. String name = userResord.getName();
80. int cnt = userMapper.selectByName(name);
81. if (cnt == 0) {
82. userMapper.addUser(userResord);
83. System.out.println( " 插入 "+userResord);
84. } else {
85. userMapper.updateUserByName(userResord);
86. System.out.println( " 更新 "+userResord);
87. }
88. }
89. return notNull;
90. }
91. }

6.mapper层

1. @Mapper
2. public interface UserMapper {
4. void addUser(User sysUser);
6. int updateUserByName(User sysUser);
8. int selectByName(String name);
9. }

7.mybatis

1. <?xml version= "1.0" encoding= "UTF-8"?>
2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3. <mapper namespace= "com.why.MyProject.mapper.UserMapper">
4. <resultMap id= "BaseResultMap" type= "com.why.MyProject.entity.User">
5. <constructor>
6. <idArg column= "t_id" javaType= "java.lang.Integer" jdbcType= "INTEGER" />
7. <arg column= "t_name" javaType= "java.lang.String" jdbcType= "VARCHAR" />
8. <arg column= "t_phone" javaType= "java.lang.String" jdbcType= "VARCHAR" />
9. <arg column= "t_address" javaType= "java.lang.String" jdbcType= "VARCHAR" />
10. <arg column= "t_enrol_date" javaType= "java.util.Date" jdbcType= "TIMESTAMP" />
11. <arg column= "t_des" javaType= "java.lang.String" jdbcType= "VARCHAR" />
12. </constructor>
13. </resultMap>
15. <insert id= "addUser" parameterType= "com.why.MyProject.entity.User">
16. insert into user
17. (name,phone,address,enrol_date,des)
18. values
19. (
20. #{name},
21. #{phone},
22. #{address},
23. #{enrolDate},
24. #{des}
25. )
26. </insert>
28. <update id= "updateUserByName" parameterType= "com.why.MyProject.entity.User">
29. update user
30. set
31. phone=#{phone},
32. address=#{address},
33. enrol_date=#{enrolDate},
34. des=#{des}
35. where name = #{name}
36. </update>
38. <select id= "selectByName" resultType= "java.lang.Integer">
39. SELECT
40. count(*)
41. FROM user
42. WHERE name=#{name}
43. </select>
44. </mapper>

8.数据库建表语句

1. CREATE TABLE `user` (
2. `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
3. `name` varchar(255) DEFAULT NULL,
4. `phone` varchar(255) DEFAULT NULL,
5. `address` varchar(255) DEFAULT NULL,
6. `enrol_date` datetime DEFAULT NULL,
7. `des` varchar(255) DEFAULT NULL,
8. PRIMARY KEY (`id`)
9. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

9.excel示例