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

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

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

org.apache.poipoi-ooxml3.9

commons-fileuploadcommons-fileupload1.3.1

commons-iocommons-io2.4

2.entity实体类

public class User implements Serializable {private Integer id;private String name;private String phone;private String address;private Date enrolDate;private String des;private static final long serialVersionUID = 1L;public User(Integer id, String name, String phone, String address, Date enrolDate, String des) {this.id = id;this.name = name;this.phone = phone;this.address = address;this.enrolDate = enrolDate;this.des = des;}public User() {super();}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name == null ? null : name.trim();}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone == null ? null : phone.trim();}public String getAddress() {return address;}public void setAddress(String address) {this.address = address == null ? null : address.trim();}public Date getEnrolDate() {return enrolDate;}public void setEnrolDate(Date enrolDate) {this.enrolDate = enrolDate;}public String getDes() {return des;}public void setDes(String des) {this.des = des == null ? null : des.trim();}@Overridepublic boolean equals(Object that) {if (this == that) {return true;}if (that == null) {return false;}if (getClass() != that.getClass()) {return false;}User other = (User) that;return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))&& (this.getName() == null ? other.getName() == null : this.getName().equals(other.getName()))&& (this.getPhone() == null ? other.getPhone() == null : this.getPhone().equals(other.getPhone()))&& (this.getAddress() == null ? other.getAddress() == null : this.getAddress().equals(other.getAddress()))&& (this.getEnrolDate() == null ? other.getEnrolDate() == null : this.getEnrolDate().equals(other.getEnrolDate()))&& (this.getDes() == null ? other.getDes() == null : this.getDes().equals(other.getDes()));}@Overridepublic int hashCode() {final int prime = 31;int result = 1;result = prime * result + ((getId() == null) ? 0 : getId().hashCode());result = prime * result + ((getName() == null) ? 0 : getName().hashCode());result = prime * result + ((getPhone() == null) ? 0 : getPhone().hashCode());result = prime * result + ((getAddress() == null) ? 0 : getAddress().hashCode());result = prime * result + ((getEnrolDate() == null) ? 0 : getEnrolDate().hashCode());result = prime * result + ((getDes() == null) ? 0 : getDes().hashCode());return result;}
}

3.Controller接口

@RestController
@RequestMapping("/test/")
public class TestController {@Autowiredprivate ITestService testService;@PostMapping("/import")public boolean addUser(@RequestParam("file") MultipartFile file) {boolean a = false;String fileName = file.getOriginalFilename();try {a = testService.batchImport(fileName, file);} catch (Exception e) {e.printStackTrace();}return  a;}}

4.服务层接口

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

5.业务层实现类

@Service
@Transactional(readOnly = true)
public class TestServiceImpl implements ITestService {@Autowiredprivate UserMapper userMapper;@Transactional(readOnly = false,rollbackFor = Exception.class)@Overridepublic boolean batchImport(String fileName, MultipartFile file) throws Exception {boolean notNull = false;List userList = new ArrayList();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)+"行,姓名请设为文本格式)");}String name = row.getCell(0).getStringCellValue();if(name == null || name.isEmpty()){throw new MyException("导入失败(第"+(r+1)+"行,姓名未填写)");}row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);String phone = row.getCell(1).getStringCellValue();if(phone==null || phone.isEmpty()){throw new MyException("导入失败(第"+(r+1)+"行,电话未填写)");}String add = row.getCell(2).getStringCellValue();if(add==null){throw new MyException("导入失败(第"+(r+1)+"行,不存在此单位或单位未填写)");}Date date;if(row.getCell(3).getCellType() !=0){throw new MyException("导入失败(第"+(r+1)+"行,入职日期格式不正确或未填写)");}else{date = row.getCell(3).getDateCellValue();}String des = row.getCell(4).getStringCellValue();user.setName(name);user.setPhone(phone);user.setAddress(add);user.setEnrolDate(date);user.setDes(des);userList.add(user);}for (User userResord : userList) {String name = userResord.getName();int cnt = userMapper.selectByName(name);if (cnt == 0) {userMapper.addUser(userResord);System.out.println(" 插入 "+userResord);} else {userMapper.updateUserByName(userResord);System.out.println(" 更新 "+userResord);}}return notNull;}
}

6.mapper层

@Mapper
public interface UserMapper {void addUser(User sysUser);int updateUserByName(User sysUser);int selectByName(String name);
}

7.mybatis

insert into user(name,phone,address,enrol_date,des)values(#{name},#{phone},#{address},#{enrolDate},#{des})update usersetphone=#{phone},address=#{address},enrol_date=#{enrolDate},des=#{des}where name = #{name}SELECTcount(*)FROM userWHERE name=#{name}

8.数据库建表语句

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`name` varchar(255) DEFAULT NULL,`phone` varchar(255) DEFAULT NULL,`address` varchar(255) DEFAULT NULL,`enrol_date` datetime DEFAULT NULL,`des` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

9.excel示例

demo地址:springboot上传excel导入到数据库完整demo(后端代码)_springboot导入excel到数据库,springboot上传excel存到数据库-Java文档类资源-CSDN下载

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！