如何优雅地实现 Excel 文件导出功能?(阿里出品的 EasyExcel,安利一波)
EasyExcel
前言
導出是后臺管理系統的常用功能,當數據量特別大的時候會內存溢出和卡頓頁面,曾經自己封裝過一個導出,POI百萬級大數據量EXCEL導出 采用了分批查詢數據來避免內存溢出和使用SXSSFWorkbook方式緩存數據到文件上以解決下載大文件EXCEL卡死頁面的問題。不過一是存在封裝不太友好使用不方便的問題,二是這些poi的操作方式仍然存在內存占用過大的問題,三是存在空循環和整除的時候數據有缺陷的問題,以及存在內存溢出的隱患。阿里開源的EasyExcel框架,可以將解析的EXCEL的內存占用控制在KB級別,并且絕對不會內存溢出(內部實現待研究),還有就是速度極快, 大概100W條記錄,十幾個字段, 只需要70秒即可完成下載。遂拋棄自己封裝的,轉戰研究阿里開源的EasyExcel
EasyExcel的github地址是:https://github.com/alibaba/easyexcel
今天博主將為大家分享7 行代碼優雅地實現 Excel 文件導出功能?(阿里出品的 EasyExcel,安利一波),不喜勿噴,如有異議歡迎討論!
入題
關于導出 Excel 文件,可以說是大多數服務中都需要集成的功能。那么,要如何去實現這個功能呢?
你可能第一想法是:這還不簡單?用 Apache 開源框架 poi, 或者 jxl 都可以實現啊。面向百度編程,把代碼模板 copy 下來,根據自己的業務再改改,能有多難?
嗯… 的確不難,但是你的代碼可能是下面這個熊樣子的:
上面這段代碼看上去是不是又臭又長呢?今天,教您如何使用 7 行代碼搞定 Excel 文件生成功能!
Apache poi、jxl 的缺陷
在說如何實現之前,我們先來討論一下傳統 Excel 框架的不足!除了上面說的,Apache poi、jxl 都存在生成 excel 文件不夠簡單優雅快速外,它們都還存在一個嚴重的問題,那就是非常耗內存,嚴重時會導致內存溢出。
POI 雖然目前來說,是 excel 解析框架中被使用最廣泛的,但這個框架并不完美。
為什么這么說呢?
開發者們大部分使用 POI,都是使用其 userModel 模式。而 userModel 的好處是上手容易使用簡單,隨便拷貝個代碼跑一下,剩下就是寫業務轉換了,雖然轉換也要寫上百行代碼,但是還是可控的。
然而 userModel 模式最大的問題是在于,對內存消耗非常大,一個幾兆的文件解析甚至要用掉上百兆的內存。現實情況是,很多應用現在都在采用這種模式,之所以還正常在跑是因為并發不大,并發上來后,一定會OOM或者頻繁的 full gc。
阿里出品的 EasyExcel,安利一波
Java解析、生成Excel比較有名的框架有Apache poi、jxl。但他們都存在一個嚴重的問題就是非常的耗內存,poi有一套SAX模式的API可以一定程度的解決一些內存溢出的問題,但POI還是有一些缺陷,比如07版Excel解壓縮以及解壓后存儲都是在內存中完成的,內存消耗依然很大。easyexcel重寫了poi對07版Excel的解析,能夠原本一個3M的excel用POI sax依然需要100M左右內存降低到KB級別,并且再大的excel不會出現內存溢出,03版依賴POI的sax模式。在上層做了模型轉換的封裝,讓使用者更加簡單方便
POM依賴
<!-- 阿里開源EXCEL --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>1.1.2-beta5</version></dependency>七行代碼搞定 Excel 生成
***上面這段示例代碼中,有兩個點很重要,已經重點標注標:***
-
①:WriteModel 這個對象就是要寫入 Excel 的數據模型對象,等等,你這好像不行吧?表頭 head,以及每個單元格內的數據順序都沒指定,能達到想要的效果么?別急,后面會討論這塊!
-
②:創建需要寫入的數據集,當然了,正常業務中,這塊都是從數據庫中查詢出來的。
如果說寫入的數據量很大,需要做分片查詢再寫入的處理,否則可能會 OOM(Out of Memory).
回過頭來,我們來看看 WriteModel 這個對象內部到底有什么幺蛾子!
ExayExcel 提供注解的方式, 來方便的定義 Excel 需要的數據模型:
-
①:首先,定義的寫入模型必須要繼承自 BaseRowModel.java;
-
②:通過 @ExcelProperty 注解來指定每個字段的列名稱,以及下標位置;
同時,上面定義的 createModelList() 方法也很簡單,通過循環,創建一個寫入模型的 List 集合:
特殊場景支持
動態生成 Excel 內容
上面的例子是基于注解的,也就是說表頭 head, 以及內容都是寫死的,換句話說,我定義好了一個數據模型,那么,生成的 Excel 文件也就是只能遵循這種模型來了,但是,實際業務中可能會存在動態變化的需求,要怎么做呢?
- ①:無注解模式,動態添加表頭,也可自由組合復雜表頭,代碼如下:
- ②:創建動態數據,注意這里的數據類型是 Object:
自定義表頭以及內容樣式
我們復用了上面的示例代碼,并額外添加了設置自定義表格樣式的代碼, createTableStytle()具體內容如下:
我們可以通過 TableStyle 這個類來設置表頭、表格主題的樣式。
合并單元格
我們可以通過 merge() 方法來合并單元格:
自定義處理
對于更復雜的處理,EasyExcel 預留了 WriterHandler 接口來,允許你自定義處理代碼:
接口中定義了三個方法:
- sheet(): 在創建每個 sheet 后自定義業務邏輯處理;
- row(): 在創建每個 row 后自定義業務邏輯處理;
- cell(): 在創建每個 cell 后自定義業務邏輯處理;
我們實現了該接口后,編寫自定義邏輯處理代碼,然后調用 getWriterWithTempAndHandler()靜態方法獲取 ExcelWriter 對象時,傳入 WriterHandler 的實現類即可。
ExcelWriter writer = EasyExcelFactory.getWriterWithTempAndHandler(null, out, ExcelTypeEnum.XLSX, true, new MyWriterHandler());Web 下載示例代碼
public class Down {@GetMapping("/a.htm")public void cooperation(HttpServletRequest request, HttpServletResponse response) {ServletOutputStream out = response.getOutputStream();ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);String fileName = new String(("UserInfo " + new SimpleDateFormat("yyyy-MM-dd").format(new Date())).getBytes(), "UTF-8");Sheet sheet1 = new Sheet(1, 0);sheet1.setSheetName("第一個sheet");writer.write0(getListString(), sheet1);writer.finish();response.setContentType("multipart/form-data");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");out.flush();}}}POJO對象(來一波栗子)
@Data public class User {private String uid;private String name;private Integer age;private Date birthday;}生產環境
Excel常量類
package com.authorization.privilege.constant;/*** @author ChenYongJia* @date 2019/5/15* @description EXCEL常量類*/ public class ExcelConstant {/*** 每個sheet存儲的記錄數 100W*/public static final Integer PER_SHEET_ROW_COUNT = 1000000;/*** 每次向EXCEL寫入的記錄數(查詢每頁數據大小) 20W*/public static final Integer PER_WRITE_ROW_COUNT = 200000;}注: 為了書寫方便,此處倆個必須要整除,可以省去很多不必要的判斷。 另外如果自己測試,可以改為100,20。
數據量少的(20W以內吧):一個SHEET一次查詢導出
@Overridepublic ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {ServletOutputStream out = null;try {out = response.getOutputStream();ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);// 設置EXCEL名稱String fileName = new String(("SystemExcel").getBytes(), "UTF-8");// 設置SHEET名稱Sheet sheet = new Sheet(1, 0);sheet.setSheetName("系統列表sheet1");// 設置標題Table table = new Table(1);List<List<String>> titles = new ArrayList<List<String>>();titles.add(Arrays.asList("系統名稱"));titles.add(Arrays.asList("系統標識"));titles.add(Arrays.asList("描述"));titles.add(Arrays.asList("狀態"));titles.add(Arrays.asList("創建人"));titles.add(Arrays.asList("創建時間"));table.setHead(titles);// 查數據寫EXCELList<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);// 下載EXCELresponse.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("導出系統列表EXCEL成功");}數據量適中(100W以內): 一個SHEET分批查詢導出
@Overridepublic ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {ServletOutputStream out = null;try {out = response.getOutputStream();ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);// 設置EXCEL名稱String fileName = new String(("SystemExcel").getBytes(), "UTF-8");// 設置SHEET名稱Sheet sheet = new Sheet(1, 0);sheet.setSheetName("系統列表sheet1");// 設置標題Table table = new Table(1);List<List<String>> titles = new ArrayList<List<String>>();titles.add(Arrays.asList("系統名稱"));titles.add(Arrays.asList("系統標識"));titles.add(Arrays.asList("描述"));titles.add(Arrays.asList("狀態"));titles.add(Arrays.asList("創建人"));titles.add(Arrays.asList("創建時間"));table.setHead(titles);// 查詢總數并 【封裝相關變量 這塊直接拷貝就行 不要改動】Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);// 寫數據 這個i的最大值直接拷貝就行了 不要改for (int i = 0; i < writeCount; i++) {List<List<String>> dataList = new ArrayList<>();// 此處查詢并封裝數據即可 currentPage, pageSize這個變量封裝好的 不要改動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);}// 下載EXCELresponse.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("導出系統列表EXCEL成功");}數據里很大(幾百萬都行): 多個SHEET分批查詢導出
@Overridepublic ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {ServletOutputStream out = null;try {out = response.getOutputStream();ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);// 設置EXCEL名稱String fileName = new String(("SystemExcel").getBytes(), "UTF-8");// 設置SHEET名稱String sheetName = "系統列表sheet";// 設置標題Table table = new Table(1);List<List<String>> titles = new ArrayList<List<String>>();titles.add(Arrays.asList("系統名稱"));titles.add(Arrays.asList("系統標識"));titles.add(Arrays.asList("描述"));titles.add(Arrays.asList("狀態"));titles.add(Arrays.asList("創建人"));titles.add(Arrays.asList("創建時間"));table.setHead(titles);// 查詢總數并封裝相關變量(這塊直接拷貝就行了不要改)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++) {// 創建SHEETSheet sheet = new Sheet(i, 0);sheet.setSheetName(sheetName + i);// 寫數據 這個j的最大值判斷直接拷貝就行了,不要改動for (int j = 0; j < (i != sheetCount - 1 ? previousSheetWriteCount : lastSheetWriteCount); j++) {List<List<String>> dataList = new ArrayList<>();// 此處查詢并封裝數據即可 currentPage, pageSize這倆個變量封裝好的 不要改動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);}}// 下載EXCELresponse.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("導出系統列表EXCEL成功");}造的假數據,100W條記錄,18個字段,測試導出是70s。 在實際上產環境使用的時候,具體的還是要看自己寫的sql的性能。 sql性能快的話,會很快
數據量過大,在使用count(1)查詢總數的時候會很慢,可以通過調整mysql的緩沖池參數來加快查詢,,數據量大的時候,limit 0,20W; limit 20W,40W, limit 40W,60W, limit 60W,80W 查詢會很快。
到這里7 行代碼優雅地實現 Excel 文件導出功能?(阿里出品的 EasyExcel,安利一波),分享完畢了,快去試試吧!
最后
-
阿里的就是牛逼,阿里的就是牛逼,阿里的就是牛逼,重要的事說三遍!
-
更多參考精彩博文請看這里:《陳永佳的博客》
-
喜歡博主的小伙伴可以加個關注、點個贊哦,持續更新嘿嘿!
總結
以上是生活随笔為你收集整理的如何优雅地实现 Excel 文件导出功能?(阿里出品的 EasyExcel,安利一波)的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 思维导图结构化梳理java
- 下一篇: Postgresql监控插件pg_sta