Java利用EasyPoi实现excel文件导入和导出

1.导入依赖

<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.1.3</version></dependency>

2.去需要导出的实体类中注解

package com.study.server.pojo;import cn.afterturn.easypoi.excel.annotation.Excel;import cn.afterturn.easypoi.excel.annotation.ExcelEntity;import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableName;import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableId;import java.io.Serializable;import java.time.LocalDateTime;import java.util.Date;import com.fasterxml.jackson.annotation.JsonFormat;import io.swagger.annotations.ApiModel;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.EqualsAndHashCode;/*** <p>* * </p>** @author mq* @since -03-24*/@Data@EqualsAndHashCode(callSuper = false)@TableName("t_employee")@ApiModel(value="Employee对象", description="")public class Employee implements Serializable {private static final long serialVersionUID = 1L;@ApiModelProperty(value = "员工编号")@TableId(value = "id", type = IdType.AUTO)private Integer id;@ApiModelProperty(value = "员工姓名")@Excel(name="员工姓名")private String name;@ApiModelProperty(value = "性别")@Excel(name="性别")private String gender;@ApiModelProperty(value = "出生日期")@JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")@Excel(name = "出生日期",format = "yyyy-MM-dd",width = 20)private Date birthday;@ApiModelProperty(value = "身份证号")@Excel(name = "身份证号",width = 30)private String idCard;@ApiModelProperty(value = "婚姻状况")@Excel(name = "婚姻状况")private String wedlock;@ApiModelProperty(value = "民族")private Integer nationId;@ApiModelProperty(value = "籍贯")@Excel(name = "籍贯")private String nativePlace;@ApiModelProperty(value = "政治面貌")private Integer politicId;@ApiModelProperty(value = "邮箱")@Excel(name = "邮箱",width = 30)private String email;@ApiModelProperty(value = "电话号码")@Excel(name = "电话号码",width = 15)private String phone;@ApiModelProperty(value = "联系地址")@Excel(name = "联系地址",width = 40)private String address;@ApiModelProperty(value = "所属部门")private Integer departmentId;@ApiModelProperty(value = "职称ID")private Integer jobLevelId;@ApiModelProperty(value = "职位ID")private Integer posId;@ApiModelProperty(value = "聘用形式")@Excel(name = "聘用形式")private String engageForm;@ApiModelProperty(value = "最高学历")@Excel(name = "最高学历")private String tiptopDegree;@ApiModelProperty(value = "所属专业")@Excel(name = "所属专业",width = 20)private String specialty;@ApiModelProperty(value = "毕业院校")@Excel(name = "毕业院校",width = 20)private String school;@ApiModelProperty(value = "入职日期")@JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")@Excel(name = "入职日期",format = "yyyy-MM-dd",width = 20)private Date beginDate;@ApiModelProperty(value = "在职状态")@Excel(name = "在职状态")private String workState;@ApiModelProperty(value = "工号")@Excel(name = "工号")private String workID;@ApiModelProperty(value = "合同期限")@Excel(name = "合同期限",suffix = "年")private Double contractTerm;@ApiModelProperty(value = "转正日期")@JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")@Excel(name = "转正日期",format = "yyyy-MM-dd",width = 20)private Date conversionTime;@ApiModelProperty(value = "离职日期")@JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")@Excel(name = "离职日期",format = "yyyy-MM-dd",width = 20)private Date notWorkDate;@ApiModelProperty(value = "合同起始日期")@JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")@Excel(name = "合同起始日期",format = "yyyy-MM-dd",width = 20)private Date beginContract;@ApiModelProperty(value = "合同终止日期")@JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")@Excel(name = "合同终止日期",format = "yyyy-MM-dd",width = 20)private Date endContract;@ApiModelProperty(value = "工龄")private Integer workAge;@ApiModelProperty(value = "工资账套ID")private Integer salaryId;@ApiModelProperty(value = "民族")@TableField(exist = false)@ExcelEntity(name = "民族")private Nation nation;@ApiModelProperty(value = "政治面貌")@TableField(exist = false)@ExcelEntity(name = "政治面貌")private PoliticsStatus politicsStatus;@ApiModelProperty(value = "部门")@TableField(exist = false)@ExcelEntity(name = "部门")private Department department;@ApiModelProperty(value = "职称")@TableField(exist = false)@ExcelEntity(name = "职称")private Joblevel joblevel;@ApiModelProperty(value = "职位")@TableField(exist = false)@ExcelEntity(name = "职位")private Position position;@ApiModelProperty(value = "工资套账")@TableField(exist = false)private Salary salary;public static long getSerialVersionUID() {return serialVersionUID;}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;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getIdCard() {return idCard;}public void setIdCard(String idCard) {this.idCard = idCard;}public String getWedlock() {return wedlock;}public void setWedlock(String wedlock) {this.wedlock = wedlock;}public Integer getNationId() {return nationId;}public void setNationId(Integer nationId) {this.nationId = nationId;}public String getNativePlace() {return nativePlace;}public void setNativePlace(String nativePlace) {this.nativePlace = nativePlace;}public Integer getPoliticId() {return politicId;}public void setPoliticId(Integer politicId) {this.politicId = politicId;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public Integer getDepartmentId() {return departmentId;}public void setDepartmentId(Integer departmentId) {this.departmentId = departmentId;}public Integer getJobLevelId() {return jobLevelId;}public void setJobLevelId(Integer jobLevelId) {this.jobLevelId = jobLevelId;}public Integer getPosId() {return posId;}public void setPosId(Integer posId) {this.posId = posId;}public String getEngageForm() {return engageForm;}public void setEngageForm(String engageForm) {this.engageForm = engageForm;}public String getTiptopDegree() {return tiptopDegree;}public void setTiptopDegree(String tiptopDegree) {this.tiptopDegree = tiptopDegree;}public String getSpecialty() {return specialty;}public void setSpecialty(String specialty) {this.specialty = specialty;}public String getSchool() {return school;}public void setSchool(String school) {this.school = school;}public Date getBeginDate() {return beginDate;}public void setBeginDate(Date beginDate) {this.beginDate = beginDate;}public String getWorkState() {return workState;}public void setWorkState(String workState) {this.workState = workState;}public String getWorkID() {return workID;}public void setWorkID(String workID) {this.workID = workID;}public Double getContractTerm() {return contractTerm;}public void setContractTerm(Double contractTerm) {this.contractTerm = contractTerm;}public Date getConversionTime() {return conversionTime;}public void setConversionTime(Date conversionTime) {this.conversionTime = conversionTime;}public Date getNotWorkDate() {return notWorkDate;}public void setNotWorkDate(Date notWorkDate) {this.notWorkDate = notWorkDate;}public Date getBeginContract() {return beginContract;}public void setBeginContract(Date beginContract) {this.beginContract = beginContract;}public Date getEndContract() {return endContract;}public void setEndContract(Date endContract) {this.endContract = endContract;}public Integer getWorkAge() {return workAge;}public void setWorkAge(Integer workAge) {this.workAge = workAge;}public Integer getSalaryId() {return salaryId;}public void setSalaryId(Integer salaryId) {this.salaryId = salaryId;}public Nation getNation() {return nation;}public void setNation(Nation nation) {this.nation = nation;}public PoliticsStatus getPoliticsStatus() {return politicsStatus;}public void setPoliticsStatus(PoliticsStatus politicsStatus) {this.politicsStatus = politicsStatus;}public Department getDepartment() {return department;}public void setDepartment(Department department) {this.department = department;}public Joblevel getJoblevel() {return joblevel;}public void setJoblevel(Joblevel joblevel) {this.joblevel = joblevel;}public Position getPosition() {return position;}public void setPosition(Position position) {this.position = position;}}

3.去controller中写对应的方法

/**8* 导出员工数据excel* @param response*/@GetMapping("/export")public void exportEmployee(HttpServletResponse response) throws IOException {List<Employee> list=employeeService.getEmployee(null);ExportParams params=new ExportParams("员工表","员工表", ExcelType.HSSF);Workbook workbook=ExcelExportUtil.exportExcel(params,Employee.class,list);ServletOutputStream outputStream = null;try {//流形式response.setHeader("content-type","application/octet-stream");//中文乱码response.setHeader("content-disposition","attachment;filename="+ URLEncoder.encode("员工表.xls","UTF-8"));outputStream = response.getOutputStream();workbook.write(outputStream);} catch (Exception e) {e.printStackTrace();}finally {if (null != outputStream){try {outputStream.flush();outputStream.close();} catch (IOException e) {e.printStackTrace();}}}}

/**** 导入员工数据* @param file* @return*/@PostMapping("/import")public RespBean importEmployee(MultipartFile file){ImportParams params = new ImportParams();//去掉标题行params.setTitleRows(1);List<Nation> nations = nationService.list();List<PoliticsStatus> politicsStatuses = politicsStatusService.list();List<Position> positions = positionService.list();List<Department> departments = departmentService.list();List<Joblevel> joblevels = joblevelService.list();try {List<Employee> list = ExcelImportUtil.importExcel(file.getInputStream(), Employee.class, params);list.forEach(employee ->{//民族idemployee.setNationId(nations.get(nations.indexOf(new Nation(employee.getNation().getName()))).getId());//政治面貌idemployee.setPoliticId(politicsStatuses.get(politicsStatuses.indexOf(new PoliticsStatus(employee.getPoliticsStatus().getName()))).getId());//部门idemployee.setDepartmentId(departments.get(departments.indexOf(new Department(employee.getDepartment().getName()))).getId());//职称idemployee.setJobLevelId(joblevels.get(joblevels.indexOf(new Joblevel(employee.getJoblevel().getName()))).getId());//职位idemployee.setPosId(positions.get(positions.indexOf(new Position(employee.getPosition().getName()))).getId());});if (employeeService.saveBatch(list)){return RespBean.success("导入成功");}return RespBean.error("导入失败");} catch (Exception e) {e.printStackTrace();}return RespBean.error("导入失败");}

4.Postman测试

测试导出：

测试导入：