日常在做后台系统的时候会很频繁的遇到Excel导入导出的问题,下面就流程写出
1.依赖 alibaba的easyexcel
<groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId>
2.注解
3.导入 isCovered是是否覆盖的参数,不需要这个功能可以忽略
@PostMapping("/import-dept")
@ApiOperationSupport(order = 13)
@ApiOperation(value = "导入部门", notes = "传入excel")
public void importDept(@RequestParam(value = "file") MultipartFile file, @RequestParam(value = "isCovered") String isCovered) throws IOException {
deptService.importDept(file ,isCovered);
}
Service层
@Transactional(rollbackFor = Exception.class)
@Override
public void importDept(MultipartFile file, String isCovered) throws IOException {
//取流
InputStream inputStream = file.getInputStream();
//转为定义的excel实体集合-根据部门分组
List<DeptExcel> deptExcelList = EasyExcel.read(inputStream).head(DeptExcel.class)
// 设置sheet,默认读取第一个
.sheet()
// 设置标题所在行数
.headRowNumber(1)
.doReadSync();
List<Dept> deptList = new ArrayList<>();
//循环字段翻译,就忽略了
deptExcelList.forEach(deptExcel -> {
Dept dept1 = new Dept();
if (StringUtil.isNotBlank(deptExcel.getParentname())) {
if (Objects.nonNull(DeptCache.getDeptByName(deptExcel.getParentname()))) {
dept1.setParentId(DeptCache.getDeptByName(deptExcel.getParentname()).getId());
}
}
dept1.setVcode(deptExcel.getVcode());
dept1.setVname(deptExcel.getVname());
deptList.add(dept1);
});
if (isCovered.equals("0")) {
deptList.forEach(dept -> {
Long id = this.getOne(Wrappers.<Dept>query().lambda().eq(Dept::getVcode, dept.getVcode())).getId();
if (id != null) {
dept.setId(id);
this.saveOrUpdate(dept);
} else {
this.saveOrUpdate(dept);
}
});
} else {
deptList.forEach(this::saveOrUpdate);
}
}
4.导出
/**
* 导出部门
*/
@GetMapping("/export-dept")
@ApiOperationSupport(order = 14)
@ApiOperation(value = "导出部门", notes = "传入user")
public void exportDept(@ApiIgnore @RequestParam Map<String, Object> dept, HttpServletResponse response) {
QueryWrapper<Dept> queryWrapper = Condition.getQueryWrapper(dept, Dept.class);
List<DeptExcel> list = deptService.exportDept(queryWrapper);
ExcelUtil.export(response, "导出部门" + DateUtil.time(), "导出部门表", list, DeptExcel.class);
}
//写的工具类 就不全发出 自己拼进去
private static <T> void export(HttpServletResponse response, String fileName, String sheetName, List<T> dataList, Class<T> clazz) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(Charsets.UTF_8.name());
fileName = URLEncoder.encode(fileName, Charsets.UTF_8.name());
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(dataList);
} catch (Throwable var6) {
throw var6;
}
}
Service层
@Override
public List<DeptExcel> exportDept(QueryWrapper<Dept> queryWrapper) {
List<Dept> deptExcels = this.list(queryWrapper);
List<DeptExcel> deptExcelList = new ArrayList<>();
//翻译部分略过
deptExcels.forEach(dept1 -> {
DeptExcel deptExcel = new DeptExcel();
if (dept1.getParentId() != null) {
if (Objects.nonNull(DeptCache.getDept(dept1.getParentId()))) {
deptExcel.setParentname(DeptCache.getDept(dept1.getParentId()).getVname());
}
}
deptExcel.setVname(dept1.getVname());
deptExcel.setVcode(dept1.getVcode());
deptExcelList.add(deptExcel);
});
return deptExcelList;
}