导入依赖
<!--easy poi依赖-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.3</version>
</dependency>
导出controller层
@ApiOperation(value = "导出员工数据")
@GetMapping(value = "/export",produces = "application/octet-stream")
public void exportListEmp(HttpServletResponse response){
//查出员工数据
List<Employee> employees = employeeService.exportListEmp(null);
//获取export数据
ExportParams params = new ExportParams("员工表","员工表", ExcelType.HSSF);
Workbook workbook = ExcelExportUtil.exportExcel(params, Employee.class, employees);
ServletOutputStream out = null;
try {
//流形式
response.setHeader("content-type","application/octet-stream");
//防止中文乱码
response.setHeader("content-disposition","attachment;filename="+ URLEncoder.encode("员工表.xls","UTF-8"));
out = response.getOutputStream();
workbook.write(out);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
if (out!=null){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
实体类
@ApiModelProperty(value = "工号")
@Excel(name = "工号")
@TableField("work_id")
private String workId;
@ApiModelProperty(value = "合同期限")
@Excel(name = "合同期限",suffix = "年")
@TableField("contract_term")
private Double contractTerm;
@ApiModelProperty(value = "转正日期")
@Excel(name = "转正日期",width = 15,format = "yyyy-MM-dd")
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/shanghai")
@TableField("conversion_time")
private LocalDate conversionTime;
@ApiModelProperty(value = "民族")
@TableField(exist = false)
@ExcelEntity(name = "民族")
private Nation nation;
@ExcelEntity()关联到其他实体类
导入controller层
@ApiOperation(value = "员工带入")
@PostMapping("/import")
public RespBean importExportEmp(MultipartFile file){
ImportParams params = new ImportParams();
//去掉第一行(标题行)
params.setTitleRows(1);
List<Nation> nations = nationService.list();
List<PoliticsStatus> politicsStatusList = politicsStatusService.list();
List<Position> positionList = positionService.list();
List<Joblevel> joblevelList = joblevelService.list();
List<Department> departmentList = departmentService.list();
try {
List<Employee> excel = ExcelImportUtil.importExcel(file.getInputStream(), Employee.class, params);
excel.forEach(employee ->{
//民族id
employee.setNationId(nations.get(nations.indexOf(new Nation(employee.getNation().getName()))).getId());
//部门id
employee.setDepartmentId(departmentList.get(departmentList.indexOf(new Department(employee.getDepartment().getName()))).getId());
//政治面貌id
employee.setPoliticId(positionList.get(positionList.indexOf(new Position(employee.getPosition().getName()))).getId());
//职位id
employee.setPosId(politicsStatusList.get(politicsStatusList.indexOf(new PoliticsStatus(employee.getPoliticsStatus().getName()))).getId());
//职称id
employee.setJobLevelId(joblevelList.get(joblevelList.indexOf(new Joblevel(employee.getJoblevel().getName()))).getId());
});
if (employeeService.saveBatch(excel)){
return RespBean.success("导入成功");
}
} catch (Exception e) {
e.printStackTrace();
}
return RespBean.error("导入失败");
}