SpringBoot EasyExcel整合

    com.alibaba
    easyexcel



    org.apache.xmlbeans
    xmlbeans

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 {

    
    private static final int BATCH_COUNT = 5;
    List list = new ArrayList();

    private DictMapper dictMapper;

	//传入mapper对象
    public ExcelDictDTOListener(DictMapper dictMapper) {
        this.dictMapper = dictMapper;
    }

    
    @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("存储数据库成功！");
    }
}

略过。List数组批量插入数据库。

@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 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 switchData() {
        List dictList = this.list();
        List list = new ArrayList<>();
        dictList.forEach(e -> {
            ExcelDictDto excelDictDto = new ExcelDictDto();
            BeanUtils.copyProperties(e, excelDictDto);
            list.add(excelDictDto);
        });
        return list;
    }