easyExcel 导出海量数据

一、官网介绍

二、excel海量数据导出

2.1 引入依赖

 		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>

2.2 主要代码

2.2.1 controller：

public void importData(@Param("startTime") Date startTime, @Param("endTime") Date endTime,
                           HttpServletResponse response) throws Exception {
        log.info("**: startTime-{},endTime-{}", startTime, endTime);

        OutputStream outputStream = response.getOutputStream();
        //添加响应头信息
        response.setHeader("Content-disposition",
                "attachment; filename=" + "userAchive-" + startTime + "-" + endTime + ".xlsx");
        response.setHeader("Pragma", "No-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");

        demoService.exportHandle(startTime, endTime, outputStream);
        response.getOutputStream().close();
    }

2.2.2 service:

public void exportHandle(Date startTime, Date endTime, OutputStream outputStream) {
        //获取总数据量
        Integer count = userSocreMapper.getCount(startTime, endTime);
        log.info("** count: " + count);
        if (count > 1000000) log.info("数量过多，无法导出!!!");
        if (count > 10000) {
            int max = 10000;
            int countTotal = count / max + 1;
            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            for (int i = 0; i < countTotal; i++) {
                List<UserSocreExcel> exportList = userSocreMapper.exportPoints(i * max, max, startTime, endTime);
                WriteSheet writeSheet = EasyExcel.writerSheet(0, fileName).head(UserSocreExcel.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                excelWriter.write(exportList, writeSheet);
                log.info("** exportList: NO:{} size:{}", i, exportList.size());
            }
            //刷新流
            excelWriter.finish();
        } else {
            List<UserSocreExcel> exportList = userSocreMapper.exportPoints(null, null, startTime, endTime);
            log.info("** exportListSize:{}", exportList.size());
            EasyExcel.write(outputStream, UserSocreExcel.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet(fileName).doWrite(exportList);
        }
        try {
            outputStream.flush();
        } catch (IOException e) {
            log.error("** outputStream.flush(): " + e.getMessage());
        }
    }

注意：service中的count判断以及max值的设置，均可根据实际需求进行更改。

2.2.3 mapper：此处省略

2.2.4 实体类：

@Data
public class UserSocreExcel {
    @ExcelIgnore
    private Long id;
    @ExcelProperty(value = "工号",index = 0)
    private String empCode;
    @ExcelProperty(value = "姓名",index = 1)
    private String empName;
    @ExcelProperty(value = "分数",index = 2)
    private Long score;
    @ExcelProperty(value = "创建时间",index = 3)
    private String createTime;
    @ExcelProperty(value = "更新时间",index = 4)
    private String updateTime;
}

注意注解中保证index的顺序

三、总结

至此，即可实现excel海量数据批量导出。总体思路为分批次进行刷入，此处为每次刷1万数据，也可单次刷入10万数据，可视情况而定。

以上，仅供参考。