大數據導出是當我們的導出數量在幾萬,到上百萬的數據時,一次從數據庫查詢這么多數據加載到內存然后寫入會對我們的內存和CPU都產生壓力,這個時候需要我們像分頁一樣處理導出分段寫入Excel緩解Excel的壓力 EasyPoi提供的是兩個方法 ***強制使用 xssf版本的Excel*** ~~~ /** * @param entity * 表格標題屬性 * @param pojoClass * Excel對象Class * @param dataSet * Excel對象數據List */ public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) { ExcelBatchExportServer batachServer = ExcelBatchExportServer .getExcelBatchExportServer(entity, pojoClass); return batachServer.appendData(dataSet); } public static void closeExportBigExcel() { ExcelBatchExportServer batachServer = ExcelBatchExportServer.getExcelBatchExportServer(null, null); batachServer.closeExportBigExcel(); } ~~~ 添加數據和關閉服務,關閉服務不是必須的,可以調也可以不掉 我們只需要for循環寫入Excel就可以了 ~~~ @Test public void bigDataExport() throws Exception { List<MsgClient> list = new ArrayList<MsgClient>(); Workbook workbook = null; Date start = new Date(); ExportParams params = new ExportParams("大數據測試", "測試"); for (int i = 0; i < 1000000; i++) { //一百萬數據量 MsgClient client = new MsgClient(); client.setBirthday(new Date()); client.setClientName("小明" + i); client.setClientPhone("18797" + i); client.setCreateBy("JueYue"); client.setId("1" + i); client.setRemark("測試" + i); MsgClientGroup group = new MsgClientGroup(); group.setGroupName("測試" + i); client.setGroup(group); list.add(client); if(list.size() == 10000){ workbook = ExcelExportUtil.exportBigExcel(params, MsgClient.class, list); list.clear(); } } ExcelExportUtil.closeExportBigExcel(); System.out.println(new Date().getTime() - start.getTime()); File savefile = new File("D:/excel/"); if (!savefile.exists()) { savefile.mkdirs(); } FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportBigData.bigDataExport.xlsx"); workbook.write(fos); fos.close(); } ~~~ 生成的Excel數據  Cpu和內存  多次測試用時統計,速度還是可以接受的,^^ | 數據量 | 用時 | 文件大小 | 列數 | | --- | --- | --- | --- | | 100W| 16.4s| 24.3MB | 5| | 100W| 15.9s | 24.3MB| 5 | | 200W| 29.5s | 48.5MB | 5 | | 100W | 30.8s | 37.8MB | 10 | | 200W | 58.7s | 76.1MB | 10 |