1.首先，在pom.xml中引入依赖，该案例主要使用poi.
<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.写user实体类：
public class User{ private Integer uid; @NotBlank(message = "账号不能为空") @Length(min=6, max=12, message="账号长度为6到12位") private String account; @NotBlank(message = "密码不能为空") private String password; private String name; @Pattern(regexp = "^[1-9]\\d{5}(18|19|20)\\d{2}((0[1-9])|(1[0-2]))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$",message = "身份证格式不正确") private String cardId; @Pattern(regexp = "0?(13|14|15|17|18|19)[0-9]{9}", message = "手机号格式不正确") private String telephone; private Integer sex; @Past(message = "必须是过去的日期") private Date birthday; private Date created; private Date updated; private Integer state; private Integer isdelete; public Integer getUid() { return uid; } public void setUid(Integer uid) { this.uid = uid; } public String getAccount() { return account; } public void setAccount(String account) { this.account = account; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getCardId() { return cardId; } public void setCardId(String cardId) { this.cardId = cardId; } public String getArea() { return area; } public void setArea(String area) { this.area = area; } public Integer getSex() { return sex; } public void setSex(Integer sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public Date getCreated() { return created; } public void setCreated(Date created) { this.created = created; } public Date getUpdated() { return updated; } public void setUpdated(Date updated) { this.updated = updated; } public Integer getState() { return state; } public void setState(Integer state) { this.state = state; } public Integer getIsdelete() { return isdelete; } public void setIsdelete(Integer isdelete) { this.isdelete = isdelete; } public String getTelephone() { return telephone; } public void setTelephone(String telephone) { this.telephone = telephone; } public User() { super(); } public User(@NotBlank(message = "账号不能为空") @Length(min = 6, max = 12, message = "账号长度为6到12位") String account, @NotBlank(message = "密码不能为空") String password) { this.account = account; this.password = password; } public User(Integer uid, @NotBlank(message = "账号不能为空") @Length(min = 6, max = 12, message = "账号长度为6到12位") String account, @NotBlank(message = "密码不能为空") String password, String name, @Pattern(regexp = "^[1-9]\\d{5}(18|19|20)\\d{2}((0[1-9])|(1[0-2]))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$", message = "身份证格式不正确") String cardId, String area, @Pattern(regexp = "0?(13|14|15|17|18|19)[0-9]{9}", message = "手机号格式不正确") String telephone, Integer sex, @Past(message = "必须是过去的日期") Date birthday, Date created, Date updated, Integer state, Integer isdelete) { this.uid = uid; this.account = account; this.password = password; this.name = name; this.cardId = cardId; this.telephone = telephone; this.sex = sex; this.birthday = birthday; this.created = created; this.updated = updated; this.state = state; this.isdelete = isdelete; } }3.mapper.xml代码：
<insert id="insert" parameterType="com.springboot.model.User"> insert into user (uid, account, password, name, card_id, telephone, sex, birthday, created, updated, state, isDelete) values (#{uid,jdbcType=INTEGER}, #{account,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{cardId,jdbcType=VARCHAR}, ##{telephone,jdbcType=VARCHAR}, #{sex,jdbcType=INTEGER}, #{birthday,jdbcType=DATE}, #{created,jdbcType=TIMESTAMP}, #{updated,jdbcType=TIMESTAMP}, #{state,jdbcType=INTEGER}, #{isdelete,jdbcType=INTEGER}) </insert> <update id="updateByPrimaryKey" parameterType="com.springboot.model.User"> update user set account = #{account,jdbcType=VARCHAR}, password = #{password,jdbcType=VARCHAR}, name = #{name,jdbcType=VARCHAR}, card_id = #{cardId,jdbcType=VARCHAR}, telephone = #{telephone,jdbcType=VARCHAR}, sex = #{sex,jdbcType=INTEGER}, birthday = #{birthday,jdbcType=DATE}, created = #{created,jdbcType=TIMESTAMP}, updated = #{updated,jdbcType=TIMESTAMP}, state = #{state,jdbcType=INTEGER}, isDelete = #{isdelete,jdbcType=INTEGER} where uid = #{uid,jdbcType=INTEGER} </update>4.dao层代码：
@Mapper public interface UserMapper { void insert(User user); int updateByPrimaryKey(User user); }5.业务实现层代码：
@Transactional(readOnly = false,rollbackFor = Exception.class) @Override public boolean addUserByExcel(String fileName, MultipartFile file) throws Exception { 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++) { String errorString = "(第("+(r+1)+")行导入失败,"; Row row = sheet.getRow(r); if (row == null){ continue; } user = new User(); Cell cell0=row.getCell(0); cell0.setCellType(Cell.CELL_TYPE_STRING); String account=row.getCell(1).getStringCellValue(); row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); String name=row.getCell(3).getStringCellValue(); String cardId=row.getCell(4).getStringCellValue(); String telephone=row.getCell(5).getStringCellValue(); Cell cell6=row.getCell(6); cell6.setCellType(Cell.CELL_TYPE_STRING); Date birthday=row.getCell(7).getDateCellValue(); Date created=row.getCell(8).getDateCellValue(); Date updated=row.getCell(9).getDateCellValue(); Cell cell10=row.getCell(10); cell10.setCellType(Cell.CELL_TYPE_STRING); Cell cell11=row.getCell(11); cell11.setCellType(Cell.CELL_TYPE_STRING); user.setUid(Integer.parseInt(cell0.getStringCellValue())); user.setAccount(account); user.setPassword(row.getCell(2).getStringCellValue()); user.setName(name); user.setCardId(cardId); user.setTelephone(telephone); user.setSex(Integer.parseInt(cell6.getStringCellValue())); user.setBirthday(birthday); user.setCreated(created); user.setUpdated(updated); user.setState(Integer.parseInt(cell10.getStringCellValue())); user.setIsdelete(Integer.parseInt(cell11.getStringCellValue())); userList.add(user); } for (User userResord : userList) { int uid = userResord.getUid(); int cnt = userMapper.selectByPrimaryKey(uid); if (cnt == 0) { userMapper.insert(userResord); System.out.println(" 插入 "+userResord); } else { userMapper.updateByPrimaryKey(userResord); System.out.println(" 更新 "+userResord); } } return notNull; }6.Controller层代码：
@PostMapping("/add/excel") public Boolean addUserByExcel(@RequestParam("file")MultipartFile file) { boolean a = false; String fileName = file.getOriginalFilename(); try { a = userService.addUserByExcel(fileName, file); } catch (Exception e) { e.printStackTrace(); } return a; }7.mysql数据库建表代码:
create table user( uid int(11) auto_increment not null, account varchar (32) , password varchar (32) , name varchar(32) , card_id varchar (32), telephone varchar (16), sex int(2) , birthday date, created datetime, updated datetime, state int(2), isDelete int(2) , primary key (uid) )ENGINE=InnoDB DEFAULT CHARSET=utf8;8.下面是我的excel模板。 至此，该部分功能实现完成。
