依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
</dependency>
创建实体映射类
package com.atguigu.srb.core.pojo.dto;
@Data
public class ExcelDictDTO {
//该注解表名属性对应的excel的列名
@ExcelProperty("id")
private Long id;
@ExcelProperty("上级id")
private Long parentId;
@ExcelProperty("名称")
private String name;
@ExcelProperty("值")
private Integer value;
@ExcelProperty("编码")
private String dictCode;
}
创建监听器
package com.atguigu.srb.core.listener;
@Slf4j
//@AllArgsConstructor //全参
@NoArgsConstructor //无参
public class ExcelDictDTOListener extends AnalysisEventListener<ExcelDictDTO> {
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<ExcelDictDTO> list = new ArrayList();
private DictMapper dictMapper;
//传入mapper对象
public ExcelDictDTOListener(DictMapper dictMapper) {
this.dictMapper = dictMapper;
}
/**
*遍历每一行的记录
* @param data
* @param context
*/
@Override
public void invoke(ExcelDictDTO data, AnalysisContext context) {
log.info("解析到一条记录: {}", data);
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", list.size());
dictMapper.insertBatch(list); //批量插入
log.info("存储数据库成功!");
}
}
Mapper层批量插入
略过。List数组批量插入数据库。
service层导入和导出
导入
@Transactional(rollbackFor = {Exception.class})
@Override
public void importData(InputStream inputStream) {
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(inputStream, ExcelDictDTO.class, new ExcelDictDTOListener(baseMapper)).sheet().doRead();
log.info("importData finished");
}
导出
@GetMapping("/export")
@ApiOperation("导出数据字典")
public void export(HttpServletResponse response) {
List<ExcelDictDto> list = dictService.switchData();
String filename = "myDist.xlsx";
response.setHeader("Content-disposition", "attachment;filename=" + filename);
try {
EasyExcel.write(response.getOutputStream(), ExcelDictDto.class).sheet("数据字典").doWrite(list);
} catch (IOException e) {
throw new RuntimeException("导出失败!" + e.getMessage());
}
}
public List<ExcelDictDto> switchData() {
List<Dict> dictList = this.list();
List<ExcelDictDto> list = new ArrayList<>();
dictList.forEach(e -> {
ExcelDictDto excelDictDto = new ExcelDictDto();
BeanUtils.copyProperties(e, excelDictDto);
list.add(excelDictDto);
});
return list;
}