|
https://blog.csdn.net/qq_35206261/article/details/88579151 一. 簡(jiǎn)介 導(dǎo)出是后臺(tái)管理系統(tǒng)的常用功能，當(dāng)數(shù)據(jù)量特別大的時(shí)候會(huì)內(nèi)存溢出和卡頓頁面，曾經(jīng)自己封裝過一個(gè)導(dǎo)出，POI百萬級(jí)大數(shù)據(jù)量EXCEL導(dǎo)出 采用了分批查詢數(shù)據(jù)來避免內(nèi)存溢出和使用SXSSFWorkbook方式緩存數(shù)據(jù)到文件上以解決下載大文件EXCEL卡死頁面的問題。不過一是存在封裝不太友好使用不方便的問題，二是這些poi的操作方式仍然存在內(nèi)存占用過大的問題,三是存在空循環(huán)和整除的時(shí)候數(shù)據(jù)有缺陷的問題，以及存在內(nèi)存溢出的隱患。無意間查詢到阿里開源的EasyExcel框架，發(fā)現(xiàn)可以將解析的EXCEL的內(nèi)存占用控制在KB級(jí)別，并且絕對(duì)不會(huì)內(nèi)存溢出（內(nèi)部實(shí)現(xiàn)待研究）,還有就是速度極快， 大概100W條記錄，十幾個(gè)字段， 只需要70秒即可完成下載。遂拋棄自己封裝的，轉(zhuǎn)戰(zhàn)研究阿里開源的EasyExcel. 不過 說實(shí)話，當(dāng)時(shí)自己封裝的那個(gè)還是有些技術(shù)含量的，例如 外觀模式，模板方法模式，以及委托思想，組合思想，可以看看。 EasyExcel的github地址是：https://github.com/alibaba/easyexcel 二. 案例 2.1 POM依賴 <!-- 阿里開源EXCEL --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.1</version> </dependency> 2.2 POJO對(duì)象 package com.authorization.privilege.excel; import java.util.Date; /** * @author qjwyss * @date 2019/3/15 * @description */ public class User { private String uid; private String name; private Integer age; private Date birthday; public User() { } public User(String uid, String name, Integer age, Date birthday) { this.uid = uid; this.name = name; this.age = age; this.birthday = birthday; } public String getUid() { return uid; } public void setUid(String uid) { this.uid = uid; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } } 2.3 測(cè)試環(huán)境 2.3.1 數(shù)據(jù)量少的(20W以內(nèi)吧)：一個(gè)SHEET一次查詢導(dǎo)出 /** * 針對(duì)較少的記錄數(shù)(20W以內(nèi)大概)可以調(diào)用該方法一次性查出然后寫入到EXCEL的一個(gè)SHEET中 * 注意： 一次性查詢出來的記錄數(shù)量不宜過大，不會(huì)內(nèi)存溢出即可。 * * @throws IOException */ @Test public void writeExcelOneSheetOnceWrite() throws IOException { // 生成EXCEL并指定輸出路徑 OutputStream out = new FileOutputStream("E:\\temp\\withoutHead1.xlsx"); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); // 設(shè)置SHEET Sheet sheet = new Sheet(1, 0); sheet.setSheetName("sheet1"); // 設(shè)置標(biāo)題 Table table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); titles.add(Arrays.asList("用戶ID")); titles.add(Arrays.asList("名稱")); titles.add(Arrays.asList("年齡")); titles.add(Arrays.asList("生日")); table.setHead(titles); // 查詢數(shù)據(jù)導(dǎo)出即可 比如說一次性總共查詢出100條數(shù)據(jù) List<List<String>> userList = new ArrayList<>(); for (int i = 0; i < 100; i++) { userList.add(Arrays.asList("ID_" + i, "小明" + i, String.valueOf(i), new Date().toString())); } writer.write0(userList, sheet, table); writer.finish(); } 2.3.2 數(shù)據(jù)量適中（100W以內(nèi)）： 一個(gè)SHEET分批查詢導(dǎo)出 /** * 針對(duì)105W以內(nèi)的記錄數(shù)可以調(diào)用該方法分多批次查出然后寫入到EXCEL的一個(gè)SHEET中 * 注意： * 每次查詢出來的記錄數(shù)量不宜過大，根據(jù)內(nèi)存大小設(shè)置合理的每次查詢記錄數(shù)，不會(huì)內(nèi)存溢出即可。 * 數(shù)據(jù)量不能超過一個(gè)SHEET存儲(chǔ)的最大數(shù)據(jù)量105W * * @throws IOException */ @Test public void writeExcelOneSheetMoreWrite() throws IOException { // 生成EXCEL并指定輸出路徑 OutputStream out = new FileOutputStream("E:\\temp\\withoutHead2.xlsx"); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); // 設(shè)置SHEET Sheet sheet = new Sheet(1, 0); sheet.setSheetName("sheet1"); // 設(shè)置標(biāo)題 Table table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); titles.add(Arrays.asList("用戶ID")); titles.add(Arrays.asList("名稱")); titles.add(Arrays.asList("年齡")); titles.add(Arrays.asList("生日")); table.setHead(titles); // 模擬分批查詢：總記錄數(shù)50條，每次查詢20條， 分三次查詢 最后一次查詢記錄數(shù)是10 Integer totalRowCount = 50; Integer pageSize = 20; Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1); // 注： 此處僅僅為了模擬數(shù)據(jù)，實(shí)用環(huán)境不需要將最后一次分開，合成一個(gè)即可， 參數(shù)為： currentPage = i+1; pageSize = pageSize for (int i = 0; i < writeCount; i++) { // 前兩次查詢 每次查20條數(shù)據(jù) if (i < writeCount - 1) { List<List<String>> userList = new ArrayList<>(); for (int j = 0; j < pageSize; j++) { userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString())); } writer.write0(userList, sheet, table); } else if (i == writeCount - 1) { // 最后一次查詢 查多余的10條記錄 List<List<String>> userList = new ArrayList<>(); Integer lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize; for (int j = 0; j < lastWriteRowCount; j++) { userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString())); } writer.write0(userList, sheet, table); } } writer.finish(); } 2.3.3 數(shù)據(jù)量很大（幾百萬都行）： 多個(gè)SHEET分批查詢導(dǎo)出 /** * 針對(duì)幾百萬的記錄數(shù)可以調(diào)用該方法分多批次查出然后寫入到EXCEL的多個(gè)SHEET中 * 注意： * perSheetRowCount % pageSize要能整除 為了簡(jiǎn)潔，非整除這塊不做處理 * 每次查詢出來的記錄數(shù)量不宜過大，根據(jù)內(nèi)存大小設(shè)置合理的每次查詢記錄數(shù)，不會(huì)內(nèi)存溢出即可。 * * @throws IOException */ @Test public void writeExcelMoreSheetMoreWrite() throws IOException { // 生成EXCEL并指定輸出路徑 OutputStream out = new FileOutputStream("E:\\temp\\withoutHead3.xlsx"); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); // 設(shè)置SHEET名稱 String sheetName = "測(cè)試SHEET"; // 設(shè)置標(biāo)題 Table table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); titles.add(Arrays.asList("用戶ID")); titles.add(Arrays.asList("名稱")); titles.add(Arrays.asList("年齡")); titles.add(Arrays.asList("生日")); table.setHead(titles); // 模擬分批查詢：總記錄數(shù)250條，每個(gè)SHEET存100條，每次查詢20條 則生成3個(gè)SHEET，前倆個(gè)SHEET查詢次數(shù)為5， 最后一個(gè)SHEET查詢次數(shù)為3 最后一次寫的記錄數(shù)是10 // 注：該版本為了較少數(shù)據(jù)判斷的復(fù)雜度，暫時(shí)perSheetRowCount要能夠整除pageSize， 不去做過多處理 合理分配查詢數(shù)據(jù)量大小不會(huì)內(nèi)存溢出即可。 Integer totalRowCount = 250; Integer perSheetRowCount = 100; Integer pageSize = 20; Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1); Integer previousSheetWriteCount = perSheetRowCount / pageSize; Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ? previousSheetWriteCount : (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1)); for (int i = 0; i < sheetCount; i++) { // 創(chuàng)建SHEET Sheet sheet = new Sheet(i, 0); sheet.setSheetName(sheetName + i); if (i < sheetCount - 1) { // 前2個(gè)SHEET, 每個(gè)SHEET查5次 每次查20條 每個(gè)SHEET寫滿100行 2個(gè)SHEET合計(jì)200行 實(shí)用環(huán)境：參數(shù)： currentPage: j+1 + previousSheetWriteCount*i, pageSize: pageSize for (int j = 0; j < previousSheetWriteCount; j++) { List<List<String>> userList = new ArrayList<>(); for (int k = 0; k < 20; k++) { userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString())); } writer.write0(userList, sheet, table); } } else if (i == sheetCount - 1) { // 最后一個(gè)SHEET 實(shí)用環(huán)境不需要將最后一次分開，合成一個(gè)即可， 參數(shù)為： currentPage = i+1; pageSize = pageSize for (int j = 0; j < lastSheetWriteCount; j++) { // 前倆次查詢 每次查詢20條 if (j < lastSheetWriteCount - 1) { List<List<String>> userList = new ArrayList<>(); for (int k = 0; k < 20; k++) { userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString())); } writer.write0(userList, sheet, table); } else if (j == lastSheetWriteCount - 1) { // 最后一次查詢 將剩余的10條查詢出來 List<List<String>> userList = new ArrayList<>(); Integer lastWriteRowCount = totalRowCount - (sheetCount - 1) * perSheetRowCount - (lastSheetWriteCount - 1) * pageSize; for (int k = 0; k < lastWriteRowCount; k++) { userList.add(Arrays.asList("ID_" + Math.random(), "小明1", String.valueOf(Math.random()), new Date().toString())); } writer.write0(userList, sheet, table); } } } } writer.finish(); } 2.4 生產(chǎn)環(huán)境 2.4.0 Excel常量類 package com.authorization.privilege.constant; /** * @author qjwyss * @date 2019/3/18 * @description EXCEL常量類 */ public class ExcelConstant { /** * 每個(gè)sheet存儲(chǔ)的記錄數(shù) 100W */ public static final Integer PER_SHEET_ROW_COUNT = 1000000; /** * 每次向EXCEL寫入的記錄數(shù)(查詢每頁數(shù)據(jù)大小) 20W */ public static final Integer PER_WRITE_ROW_COUNT = 200000; } 注： 為了書寫方便，此處倆個(gè)必須要整除，可以省去很多不必要的判斷。 另外如果自己測(cè)試，可以改為100,20。 2.4.1 數(shù)據(jù)量少的(20W以內(nèi)吧)：一個(gè)SHEET一次查詢導(dǎo)出 @Override public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception { ServletOutputStream out = null; try { out = response.getOutputStream(); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); // 設(shè)置EXCEL名稱 String fileName = new String(("SystemExcel").getBytes(), "UTF-8"); // 設(shè)置SHEET名稱 Sheet sheet = new Sheet(1, 0); sheet.setSheetName("系統(tǒng)列表sheet1"); // 設(shè)置標(biāo)題 Table table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); titles.add(Arrays.asList("系統(tǒng)名稱")); titles.add(Arrays.asList("系統(tǒng)標(biāo)識(shí)")); titles.add(Arrays.asList("描述")); titles.add(Arrays.asList("狀態(tài)")); titles.add(Arrays.asList("創(chuàng)建人")); titles.add(Arrays.asList("創(chuàng)建時(shí)間")); table.setHead(titles); // 查數(shù)據(jù)寫EXCEL List<List<String>> dataList = new ArrayList<>(); List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO); if (!CollectionUtils.isEmpty(sysSystemVOList)) { sysSystemVOList.forEach(eachSysSystemVO -> { dataList.add(Arrays.asList( eachSysSystemVO.getSystemName(), eachSysSystemVO.getSystemKey(), eachSysSystemVO.getDescription(), eachSysSystemVO.getState().toString(), eachSysSystemVO.getCreateUid(), eachSysSystemVO.getCreateTime().toString() )); }); } writer.write0(dataList, sheet, table); // 下載EXCEL response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); writer.finish(); out.flush(); } finally { if (out != null) { try { out.close(); } catch (Exception e) { e.printStackTrace(); } } } return ResultVO.getSuccess("導(dǎo)出系統(tǒng)列表EXCEL成功"); } 2.4.2 數(shù)據(jù)量適中（100W以內(nèi)）： 一個(gè)SHEET分批查詢導(dǎo)出 @Override public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception { ServletOutputStream out = null; try { out = response.getOutputStream(); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); // 設(shè)置EXCEL名稱 String fileName = new String(("SystemExcel").getBytes(), "UTF-8"); // 設(shè)置SHEET名稱 Sheet sheet = new Sheet(1, 0); sheet.setSheetName("系統(tǒng)列表sheet1"); // 設(shè)置標(biāo)題 Table table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); titles.add(Arrays.asList("系統(tǒng)名稱")); titles.add(Arrays.asList("系統(tǒng)標(biāo)識(shí)")); titles.add(Arrays.asList("描述")); titles.add(Arrays.asList("狀態(tài)")); titles.add(Arrays.asList("創(chuàng)建人")); titles.add(Arrays.asList("創(chuàng)建時(shí)間")); table.setHead(titles); // 查詢總數(shù)并 【封裝相關(guān)變量 這塊直接拷貝就行 不要改動(dòng)】 Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO); Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT; Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1); // 寫數(shù)據(jù) 這個(gè)i的最大值直接拷貝就行了 不要改 for (int i = 0; i < writeCount; i++) { List<List<String>> dataList = new ArrayList<>(); // 此處查詢并封裝數(shù)據(jù)即可 currentPage, pageSize這個(gè)變量封裝好的 不要改動(dòng) PageHelper.startPage(i + 1, pageSize); List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO); if (!CollectionUtils.isEmpty(sysSystemVOList)) { sysSystemVOList.forEach(eachSysSystemVO -> { dataList.add(Arrays.asList( eachSysSystemVO.getSystemName(), eachSysSystemVO.getSystemKey(), eachSysSystemVO.getDescription(), eachSysSystemVO.getState().toString(), eachSysSystemVO.getCreateUid(), eachSysSystemVO.getCreateTime().toString() )); }); } writer.write0(dataList, sheet, table); } // 下載EXCEL response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); writer.finish(); out.flush(); } finally { if (out != null) { try { out.close(); } catch (Exception e) { e.printStackTrace(); } } } return ResultVO.getSuccess("導(dǎo)出系統(tǒng)列表EXCEL成功"); } 2.4.3 數(shù)據(jù)里很大（幾百萬都行）： 多個(gè)SHEET分批查詢導(dǎo)出 @Override public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception { ServletOutputStream out = null; try { out = response.getOutputStream(); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); // 設(shè)置EXCEL名稱 String fileName = new String(("SystemExcel").getBytes(), "UTF-8"); // 設(shè)置SHEET名稱 String sheetName = "系統(tǒng)列表sheet"; // 設(shè)置標(biāo)題 Table table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); titles.add(Arrays.asList("系統(tǒng)名稱")); titles.add(Arrays.asList("系統(tǒng)標(biāo)識(shí)")); titles.add(Arrays.asList("描述")); titles.add(Arrays.asList("狀態(tài)")); titles.add(Arrays.asList("創(chuàng)建人")); titles.add(Arrays.asList("創(chuàng)建時(shí)間")); table.setHead(titles); // 查詢總數(shù)并封裝相關(guān)變量(這塊直接拷貝就行了不要改) Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO); Integer perSheetRowCount = ExcelConstant.PER_SHEET_ROW_COUNT; Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT; Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1); Integer previousSheetWriteCount = perSheetRowCount / pageSize; Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ? previousSheetWriteCount : (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1)); for (int i = 0; i < sheetCount; i++) { // 創(chuàng)建SHEET Sheet sheet = new Sheet(i, 0); sheet.setSheetName(sheetName + i); // 寫數(shù)據(jù) 這個(gè)j的最大值判斷直接拷貝就行了，不要改動(dòng) for (int j = 0; j < (i != sheetCount - 1 ? previousSheetWriteCount : lastSheetWriteCount); j++) { List<List<String>> dataList = new ArrayList<>(); // 此處查詢并封裝數(shù)據(jù)即可 currentPage, pageSize這倆個(gè)變量封裝好的 不要改動(dòng) PageHelper.startPage(j + 1 + previousSheetWriteCount * i, pageSize); List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO); if (!CollectionUtils.isEmpty(sysSystemVOList)) { sysSystemVOList.forEach(eachSysSystemVO -> { dataList.add(Arrays.asList( eachSysSystemVO.getSystemName(), eachSysSystemVO.getSystemKey(), eachSysSystemVO.getDescription(), eachSysSystemVO.getState().toString(), eachSysSystemVO.getCreateUid(), eachSysSystemVO.getCreateTime().toString() )); }); } writer.write0(dataList, sheet, table); } } // 下載EXCEL response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); writer.finish(); out.flush(); } finally { if (out != null) { try { out.close(); } catch (Exception e) { e.printStackTrace(); } } } return ResultVO.getSuccess("導(dǎo)出系統(tǒng)列表EXCEL成功"); } 三、總結(jié) 造的假數(shù)據(jù)，100W條記錄，18個(gè)字段，測(cè)試導(dǎo)出是70s。 在實(shí)際上產(chǎn)環(huán)境使用的時(shí)候，具體的還是要看自己寫的sql的性能。 sql性能快的話，會(huì)很快。 有一點(diǎn)推薦一下： 在做分頁的時(shí)候使用單表查詢， 對(duì)于所需要處理的外鍵對(duì)應(yīng)的冗余字段，在外面一次性查出來放到map里面(推薦使用@MapKey注解)，然后遍歷list的時(shí)候根據(jù)外鍵從map中獲取對(duì)應(yīng)的名稱。一個(gè)宗旨：少發(fā)查詢sql, 才能更快的導(dǎo)出。 題外話： 如果數(shù)據(jù)量過大，在使用count(1)查詢總數(shù)的時(shí)候會(huì)很慢，可以通過調(diào)整mysql的緩沖池參數(shù)來加快查詢，請(qǐng)參見博主的另一篇博文MYSQL單表數(shù)據(jù)量過大查詢過慢配置優(yōu)化innodb_buffer_pool_size。 還有就是遇到了一個(gè)問題，使用pagehelper的時(shí)候，數(shù)據(jù)量大的時(shí)候，limit 0,20W; limit 20W,40W, limit 40W,60W, limit 60W,80W 查詢有的時(shí)候會(huì)很快，有的時(shí)候會(huì)很慢，待研究。 --------------------- 作者：請(qǐng)叫我猿叔叔 來源：CSDN 原文：https://blog.csdn.net/qq_35206261/article/details/88579151 版權(quán)聲明：本文為博主原創(chuàng)文章，轉(zhuǎn)載請(qǐng)附上博文鏈接！ |
|
|
