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

---

一、写在前面

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

准备: mave导入：easyexcel:3.0.5

二、使用步骤

定义导出的数据实体

@ExcelIgnoreUnannotatedpublic 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) {// 获取OutputStreamBiFunction<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();}