EasyPoi的使用

第一步：导入依赖

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

第二步：映射实体类

//引用普通字段 
@ApiModelProperty(value = "联系地址")
    @Excel(name = "联系地址",width = 35)
    private String address;

    @ApiModelProperty(value = "所属部门")
    @TableField("departmentId")
    private Integer departmentid;

    @ApiModelProperty(value = "职称ID")
    @TableField("jobLevelId")
    private Integer joblevelid;

    @ApiModelProperty(value = "职位ID")
    @TableField("posId")
    private Integer posid;

    @ApiModelProperty(value = "聘用形式")
    @Excel(name = "聘用形式",width = 35)
    @TableField("engageForm")
    private String engageform;

//引用对象
 @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;

//对象的实体类
@Data
@RequiredArgsConstructor  //有参数构造
@NoArgsConstructor   //无参数构造
@EqualsAndHashCode(callSuper = false,of = "name")
@TableName("t_position")
@ApiModel(value="Position对象", description="")
public class Position implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "id")
    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty(value = "职位")
    @Excel(name = "职位")
    @NonNull
    private String name;

    @ApiModelProperty(value = "创建时间")
    @TableField("createDate")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")
    private LocalDateTime createdate;

    @ApiModelProperty(value = "是否启用")
    private Boolean enabled;

第三步：控制层

@ApiOperation(value = "导入用户的数据信息")
	@PostMapping("/import")
	public RespBean importEmp(MultipartFile file){
		ImportParams params=new ImportParams();
		//删除标题行
		params.setTitleRows(1);
		//查询数据库
		List<Nation> nations = this.nationService.list();
		List<PoliticsStatus> politicsStatuses = this.politicsStatusService.list();
		List<Department> departments = this.departmentService.list();
		List<Joblevel> joblevels = this.joblevelService.list();
		List<Position> positions = this.positionService.list();

		try {
			List<Employee> list = ExcelImportUtil.importExcel(file.getInputStream(), Employee.class, params);
			list.forEach(employee -> {
				//根据hashcode获取对象里面的ID
				employee.setNationid(nations.get(nations.indexOf(new Nation(employee.getNation().getName()))).getId());
				employee.setPoliticid(politicsStatuses.get(politicsStatuses.indexOf(new PoliticsStatus(employee.getPoliticsStatus().getName()))).getId());
				employee.setDepartmentid(departments.get(departments.indexOf(new Department(employee.getDepartment().getName()))).getId());
				employee.setJoblevelid(joblevels.get(joblevels.indexOf(new Joblevel(employee.getJoblevel().getName()))).getId());
				employee.setPoliticid(positions.get(positions.indexOf(new Position(employee.getPosition().getName()))).getId());
			});
			//批量插入
			if(this.employeeService.saveBatch(list)){
				return RespBean.success("导入成功！");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return RespBean.error("导入失败！");
	}

	@ApiOperation(value = "导出员工的数据",produces = "application/octet-stream")
	@GetMapping("/export")
	public void exportEmp (HttpServletResponse response){
		//获取所有的员工数据
		List<Employee> employee = this.employeeService.getEmployee(null);
		//导出文件的参数
		ExportParams exportParams=new ExportParams("员工表","员工表", ExcelType.HSSF);
		//引用工具类
		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Employee.class, employee);
		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 (IOException e) {
			e.printStackTrace();
		}finally {
			if(null!=outputStream){
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

 效果展示