使用EasyExcel分页导出Excel

有时候我们需要导出数据库中所有的数据，通常我们使用的方法是将数据库的数据全部查询出来，然后写到Excel中导出。但是当数据量比较大的时候，将几万条数据全部放入内存的话，可能会导致OOM，所以采取分页的方式一页一页的写入Excel文件中导出，能有效避免OOM问题

一、环境准备

数据库实体EmployeeDO.java

@TableName("employee")
public class EmployeeDO implements Serializable {
    @TableId("employee_id")
    private Long employeeId;
    @TableField(value = "employee_name")
    private String employeeName;
    private Integer age;
    private String gender;
    @TableField(value = "create_time")
    private LocalDateTime createTime;
}

导出的对象EmployeeExportDTO.java

public class EmployeeExportDTO implements Serializable {

    @ExcelProperty(index = 0, value = "序号")
    private String index;
    @ExcelProperty(index = 1, value = "员工ID")
    @JsonFormat(shape = JsonFormat.Shape.STRING)
    private String employeeId;
    @ExcelProperty(index = 2, value = "员工姓名")
    private String employeeName;
    @ExcelProperty(index = 3, value = "年龄")
    private String age;
    @ExcelProperty(index = 4, value = "性别")
    private String gender;
    @ExcelProperty(index = 5, value = "创建时间", converter = LocalDateTimeConverter.class)
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd")
    private String createTime;
}

二、封装EasyExcel工具类

@Slf4j
public class EasyExcelUtil {

    public static void pageExport(File file, Class clazz, Page page, Supplier<Page> supplier, Function function) {
        ExcelWriter excelWriter = EasyExcel.write(file).head(clazz).build();
        export(page, supplier, function, excelWriter);
    }

    public static void pageExport(OutputStream outputStream, Class clazz, Page page, Supplier<Page> supplier, Function function) {
        ExcelWriter excelWriter = EasyExcel.write(outputStream).head(clazz).build();
        export(page, supplier, function, excelWriter);
    }

    private static void export(Page page, Supplier<Page> supplier, Function function, ExcelWriter excelWriter) {
        WriteSheet sheet1 = EasyExcel.writerSheet("test").build();
        long currentPage = 1;
        long totalPage = 0;
        page.setCurrent(currentPage);
        do {
            Page res = supplier.get();
            List collect = (List) res.getRecords().stream().map(function::apply).collect(Collectors.toList());
            excelWriter.write(collect, sheet1);
            totalPage = res.getPages();
            currentPage++;
            page.setCurrent(currentPage);
        } while (currentPage <= totalPage);
        excelWriter.finish();
    }

}

三、测试

@Test
public void testExportByPage() {
    File file = new File("D:\\doc\\a.xlsx");
    Page<EmployeeDO> page = new Page<>();
    LambdaQueryWrapper<EmployeeDO> queryWrapper = Wrappers.lambdaQuery(EmployeeDO.class);
    page.setSize(20000);
    EasyExcelUtil.pageExport(file, EmployeeExportDTO.class, page, () -> employeeMapper.selectPage(page, queryWrapper), (res) -> {
        EmployeeExportDTO employeeExportDTO = new EmployeeExportDTO();
        BeanUtils.copyProperties(res, employeeExportDTO);
        return employeeExportDTO;
    });
}

四、导出结果