java-easyexcel导出excel-50行代码搞定大量数据导出

---

一、写在前面

场景： 当数据量导出过大时如果一次从数据库取出所有数据会导致内存飙升导致系统奔溃，所以我们采取循环读取和循环写入。

准备: mave导入：easyexcel:3.0.5

二、使用步骤

定义导出的数据实体

@ExcelIgnoreUnannotated

public class OrderExportVO {
	// value是excel表头 index是excel中的列
    @ExcelProperty(value = "订单号",index = 0)
    private String orderSn;

    @ExcelProperty(value = "订单ERP客户ID",index = 1)
    private String memberErpCode;
	
		// 省略get set方法
}

导出

  	@PostMapping("/export")
    public void orderExport(HttpServletResponse httpServletResponse) {
   		// 获取OutputStream
        BiFunction<HttpServletResponse, String, OutputStream> biFunction = (response, fileName) -> {
            String SYS_TEM_DIR = System.getProperty("java.io.tmpdir") + File.separator;
            String filePath = SYS_TEM_DIR + fileName + ".xlsx";
            File file = new File(filePath);
            try {
                if (!file.exists() || file.isDirectory()) {
                    file.createNewFile();
                }
                fileName = new String(filePath.getBytes(), "ISO-8859-1");
                response.addHeader("Content-Disposition", "filename=" + fileName);
                return response.getOutputStream();
            } catch (IOException e) {
                throw  new RuntimeException(e.getMessage());
            }
        };

		// 创建表格
        OutputStream outputStream = biFunction.apply(httpServletResponse, "订单表格");
        ExcelWriter excelWriter = EasyExcel.write(outputStream, OrderExportVO.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").registerWriteHandler(EasyExcelUtil.getStyleStrategy()).build();

        // 每次循环导出的数量
        int pageSize = 5000;

        // 分页查询方法这替换成你自己的分页查询方法
        IPage<OrderExportVO> page = getPage(1, pageSize);
        // 总行数
        int total = (int) page.getTotal();
        // 总页数
        int totalPage = (total % pageSize) > 0 ? (total / pageSize) + 1 : (total / pageSize);

        /**
         * totalPage=0导出空文件
         * totalPage=1直接导出查询结果
         * totalPage>1循环查询写入并导出
         */
        if (totalPage == 0) {
        } else if (totalPage == 1) {
            excelWriter.write(page.getRecords(), writeSheet);
        } else {
            Stream.iterate(1, i -> i + 1).limit(totalPage).forEach(pageIndex -> {
                List<OrderExportVO> list = getPage(pageIndex, pageSize).getRecords();
                excelWriter.write(list, writeSheet);
            });
        }
         excelWriter.finish();
    }