使用阿里EasyExcel实现导入导出

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。

在尽可能节约内存的情况下支持读写百M的Excel。

pom.xml中添加依赖

<!--阿里easyExcel工具包-->
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.1.6</version>
</dependency>

1.导出

@GetMapping("/export")
    @ApiOperation(value = "导出", notes = "导出")
    public void export(RetailTargetReqVo retailTargetReqVo, HttpServletResponse response) throws Exception {
        List<RetailTargetExcelVo> data = retailTargetService.getExcelExportData(retailTargetReqVo);
        RetailTargetExcelUtils.exportExcel(response,data);
    }

public static void exportExcel(HttpServletResponse response, List<RetailTargetExcelVo> data)throws Exception{
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(7, 0);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy1 = new OnceAbsoluteMergeStrategy(3, 3, 3, 6);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy2 = new OnceAbsoluteMergeStrategy(3, 3, 7, 10);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy3 = new OnceAbsoluteMergeStrategy(3, 3, 11, 14);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy4 = new OnceAbsoluteMergeStrategy(3, 3, 15, 18);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy5 = new OnceAbsoluteMergeStrategy(7, 7, 3, 6);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy6 = new OnceAbsoluteMergeStrategy(7, 7, 7, 10);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy7 = new OnceAbsoluteMergeStrategy(7, 7, 11, 14);
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy8 = new OnceAbsoluteMergeStrategy(7, 7, 15, 18);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("厂方批零售目标", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), RetailTargetExcelVo.class)
                .registerWriteHandler(loopMergeStrategy)
                .registerWriteHandler(onceAbsoluteMergeStrategy1)
                .registerWriteHandler(onceAbsoluteMergeStrategy2)
                .registerWriteHandler(onceAbsoluteMergeStrategy3)
                .registerWriteHandler(onceAbsoluteMergeStrategy4)
                .registerWriteHandler(onceAbsoluteMergeStrategy5)
                .registerWriteHandler(onceAbsoluteMergeStrategy6)
                .registerWriteHandler(onceAbsoluteMergeStrategy7)
                .registerWriteHandler(onceAbsoluteMergeStrategy8);
        for (int i = 2; i <= data.size()/7; i++) {
            excelWriterBuilder.registerWriteHandler(new OnceAbsoluteMergeStrategy(3+7*(i-1), 3+7*(i-1), 3, 6));
            excelWriterBuilder.registerWriteHandler(new OnceAbsoluteMergeStrategy(3+7*(i-1), 3+7*(i-1), 7, 10));
            excelWriterBuilder.registerWriteHandler(new OnceAbsoluteMergeStrategy(3+7*(i-1), 3+7*(i-1), 11, 14));
            excelWriterBuilder.registerWriteHandler(new OnceAbsoluteMergeStrategy(3+7*(i-1), 3+7*(i-1), 15, 18));

            excelWriterBuilder.registerWriteHandler(new OnceAbsoluteMergeStrategy(7+7*(i-1), 7+7*(i-1), 3, 6));
            excelWriterBuilder.registerWriteHandler(new OnceAbsoluteMergeStrategy(7+7*(i-1), 7+7*(i-1), 7, 10));
            excelWriterBuilder.registerWriteHandler(new OnceAbsoluteMergeStrategy(7+7*(i-1), 7+7*(i-1), 11, 14));
            excelWriterBuilder.registerWriteHandler(new OnceAbsoluteMergeStrategy(7+7*(i-1), 7+7*(i-1), 15, 18));
        }
        excelWriterBuilder.sheet("sheet1").doWrite(data);
    }

@Data
public class RetailTargetExcelVo {

    @ExcelProperty(value = "门店", index = 0)
    private String shopName;

    @ExcelProperty(value = "KPI", index = 1)
    private String kpi;

    @ExcelProperty(value = "合计", index = 2)
    private Integer total;

    @ExcelProperty(value = {"一季度", "小计"}, index = 3)
    private Integer subTotal1;
    @ExcelProperty(value = {"一季度", "1月"}, index = 4)
    private Integer january;
    @ExcelProperty(value = {"一季度", "2月"}, index = 5)
    private Integer february;
    @ExcelProperty(value = {"一季度", "3月"}, index = 6)
    private Integer march;

    @ExcelProperty(value = {"二季度", "小计"}, index = 7)
    private Integer subTotal2;
    @ExcelProperty(value = {"二季度", "4月"}, index = 8)
    private Integer april;
    @ExcelProperty(value = {"二季度", "5月"}, index = 9)
    private Integer may;
    @ExcelProperty(value = {"二季度", "6月"}, index = 10)
    private Integer june;

    @ExcelProperty(value = {"三季度", "小计"}, index = 11)
    private Integer subTotal3;
    @ExcelProperty(value = {"三季度", "7月"}, index = 12)
    private Integer july;
    @ExcelProperty(value = {"三季度", "8月"}, index = 13)
    private Integer august;
    @ExcelProperty(value = {"三季度", "9月"}, index = 14)
    private Integer september;

    @ExcelProperty(value = {"四季度", "小计"}, index = 15)
    private Integer subTotal4;
    @ExcelProperty(value = {"四季度", "10月"}, index = 16)
    private Integer october;
    @ExcelProperty(value = {"四季度", "11月"}, index = 17)
    private Integer november;
    @ExcelProperty(value = {"四季度", "12月"}, index = 18)
    private Integer december;


}

LoopMergeStrategy与OnceAbsoluteMergeStrategy是合并单元格的策略，如果只是简单的表格列表，则不需要。

2.导入

 @PostMapping("/import")
    @ApiOperation(value = "导入", notes = "导入")
    public void upload(MultipartFile file) throws Exception{
        EasyExcel.read(file.getInputStream(), RetailTargetTemplateExcelVo.class, new ImportDataListener(retailTargetService)).sheet().doRead();
    }

public class ImportDataListener extends AnalysisEventListener<RetailTargetTemplateExcelVo> {
    private static final Logger logger = LoggerFactory.getLogger(ImportDataListener.class);

    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;

    List<RetailTargetTemplateExcelVo> list = new ArrayList<>();

    private RetailTargetService retailTargetService;

    public ImportDataListener(RetailTargetService retailTargetService) {
        this.retailTargetService = retailTargetService;
    }

    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(RetailTargetTemplateExcelVo data, AnalysisContext analysisContext) {
        logger.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            retailTargetService.batchInsert(list);
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        retailTargetService.batchInsert(list);
        logger.info("所有数据解析完成！");
    }
}

 RetailTargetTemplateExcelVo为导入Excel中模板字段实体。