團隊目前在做一個用戶數據看板（下面簡稱看板），基本覆蓋用戶的所有行為數據，并生成分析報表，用戶行為由多個數據來源組成（餐飲、生活日用、充值消費、交通出行、通訊物流、交通出行、醫療保健、住房物業、運動健康...），

基于大量數據的組合、排序和統計。根據最新的統計報告，每天將近100W+的行為數據產生，所以這個數據基數是非常大的。

而這個數據中心，對接很多的業務團隊，這些團隊根據自己的需要，對某些維度進行篩選，然后直接從我們的中心上下載數據（excel）文檔進行分析。所以下個幾十萬上百萬行的數據是很常見的。

目前遇到的主要問題是，隨著行為能力逐漸的完善閉環，用戶數據沉淀的也越來越多了，同時業務量的也在不斷擴大。

業務團隊有時候會下載超量的數據來進行分析，平臺上的數據下載能力就顯得尤為重要了。而我們的問題是下載效率太慢，10W的數據大約要5分鐘以上才能下載下來，這顯然有問題了。

代碼是之前團隊遺留的，原先功能沒開放使用，沒有數據量，所以沒有發現問題。以下是原來的導出模塊，原程序如下，我做了基本還原。

現在如何保證數據的高效導出是我們最重要的目標，這個也是業務團隊最關心的。

/** * 獲取導出的Excel的文件流信息 * @param exportData * @return * @throws Exception */ private OutputStream getExportOutPutStream(List<UBehavDto> exportData) throws Exception {JSONObject object = new JSONObject();List<ExcelCell[]> excelCells = new ArrayList<>();String[] headers = new String[] { 'A字段','B字段','C字段','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE字段','AF字段','AG字段' };ExcelCell[] headerRow = getHeaderRow(headers);excelCells.add(headerRow);String pattern = 'yyyy-MM-dd hh:mm:ss';for (UBehavDto uBehavDto:exportData) { String[] singleRow = new String[] { uBehavDto.getA(),uBehavDto.getB(),uBehavDto.getC(),uBehavDto.getD(),uBehavDto.getE(),uBehavDto.getF(), DateFormatUtils.format(uBehavDto.getAddTime(), pattern),DateFormatUtils.format(uBehavDto.getDate(), pattern), uBehavDto.getG(),uBehavDto.getH(),uBehavDto.getI(),uBehavDto.getJ(),uBehavDto.getK(),uBehavDto.getL(),uBehavDto.getM(), uBehavDto.getN(),uBehavDto.getO(),uBehavDto.getP(), uBehavDto.getQ(),uBehavDto.getR(),uBehavDto.getS(),String.valueOf(uBehavDto.getT()),uBehavDto.getMemo(),uBehavDto.getU(),uBehavDto.getV(), uBehavDto.getW(),uBehavDto.getX(), uBehavDto.getY(),uBehavDto.getZ(),uBehavDto.getAA(),uBehavDto.getAB(),uBehavDto.getAC() }; ExcelCell[] cells = new ExcelCell[singleRow.length]; ExcelCell getA=new ExcelCell();getA.setValue(uBehavDto.getA()); ExcelCell getB=new ExcelCell();getB.setValue(uBehavDto.getB()); ExcelCell getC=new ExcelCell();getC.setValue(uBehavDto.getC()); ExcelCell getD=new ExcelCell();getD.setValue(uBehavDto.getD()); ExcelCell getE=new ExcelCell();getE.setValue(uBehavDto.getE()); ExcelCell getF=new ExcelCell();getF.setValue(uBehavDto.getF()); ExcelCell getAddTime=new ExcelCell();getAddTime.setValue(DateFormatUtils.format(uBehavDto.getAddTime(), pattern)); ExcelCell getDate=new ExcelCell();getDate.setValue(DateFormatUtils.format(uBehavDto.getDate(), pattern)); ExcelCell getG=new ExcelCell();getG.setValue(uBehavDto.getG()); ExcelCell getH=new ExcelCell();getH.setValue(uBehavDto.getH()); ExcelCell getI=new ExcelCell();getI.setValue(uBehavDto.getI()); ExcelCell getJ=new ExcelCell();getJ.setValue(uBehavDto.getJ()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getK()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getL()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getM()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getN()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getO()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getP()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getQ()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getR()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getS()); ExcelCell a=new ExcelCell();a.setValue(String.valueOf(uBehavDto.getT())); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getMemo()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getU()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getV()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getW()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getX()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getY()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getZ()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getAA()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getAB()); ExcelCell a=new ExcelCell();a.setValue(uBehavDto.getAC()); ExcelCell[] cells = { new ExcelCell(uBehavDto.getA()), new ExcelCell().setValue(uBehavDto.getB()), new ExcelCell().setValue(uBehavDto.getC()), new ExcelCell().setValue(uBehavDto.getD()), new ExcelCell().setValue(uBehavDto.getE()), new ExcelCell().setValue(uBehavDto.getF()), new ExcelCell().setValue(DateFormatUtils.format(uBehavDto.getAddTime(), pattern)), new ExcelCell().setValue(DateFormatUtils.format(uBehavDto.getDate(), pattern)), new ExcelCell().setValue(uBehavDto.getG()), new ExcelCell().setValue(uBehavDto.getH()), new ExcelCell().setValue(uBehavDto.getI()), new ExcelCell().setValue(uBehavDto.getJ()), new ExcelCell().setValue(uBehavDto.getK()), new ExcelCell().setValue(uBehavDto.getL()), new ExcelCell().setValue(uBehavDto.getM()), new ExcelCell().setValue(uBehavDto.getN()), new ExcelCell().setValue(uBehavDto.getO()), new ExcelCell().setValue(uBehavDto.getP()), new ExcelCell().setValue(uBehavDto.getQ()), new ExcelCell().setValue(uBehavDto.getR()), new ExcelCell().setValue(uBehavDto.getS()), new ExcelCell().setValue(String.valueOf(uBehavDto.getT())), new ExcelCell().setValue(uBehavDto.getMemo()), new ExcelCell().setValue(uBehavDto.getU()), new ExcelCell().setValue(uBehavDto.getV()), new ExcelCell().setValue(uBehavDto.getW()), new ExcelCell().setValue(uBehavDto.getX()), new ExcelCell().setValue(uBehavDto.getY()), new ExcelCell().setValue(uBehavDto.getZ()), new ExcelCell().setValue(uBehavDto.getAA()), new ExcelCell().setValue(uBehavDto.getAB()), new ExcelCell().setValue(uBehavDto.getAC()) }; for(int idx=0;idx<singleRow.length;idx++) {ExcelCell cell = new ExcelCell();cell.setValue(singleRow[idx]);cells[idx] = cell; } excelCells.add(cells);}object.put('行為數據', excelCells);ExcelUtils utils = new ExcelUtils();OutputStream outputStream = utils.writeExcel(object);return outputStream; }

看看標紅的代碼，這個生成Excel的方式是對Excel中的每一個cell進行渲染，逐行的進行數據填充，效率太慢了，根據日志分析發現：基本時間都耗費在數據生成Excel上。每生成1W左右的數據基本

消耗1分鐘的時間。原來在其他業務中他只是作為簡量數據導出來使用，比如幾百條的數據，很快就出來了，但是遇到大量數據導出的情況，性能問題就立馬現形了。

團隊內討論了一下并參考了資料，發現原來業內有很多好用強大的Excel處理組件，我們優先選用阿里的easy excel來做一下嘗試。

Pom添加 easyexcel 如下：

<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.4</version> </dependency>

代碼：dto內容（中文為配置好的表頭）：

package com.xxx.xxx.modules.worklog.dto;import com.alibaba.excel.annotation.ExcelProperty;import lombok.Getter;import lombok.Setter;import java.io.Serializable;import java.util.Date;/** * <p>Description:XX表基本信息 </p> * <p>Copyright: Copyright (c) 2021 </p> * <p>Company: XX Co., Ltd. </p> * * @author brand * @date 2021-06-26 10:07:46 * <p>Update Time: </p> * <p>Updater: </p> * <p>Update Comments: </p> */@Setter@Getterpublic class WorkLogDto implements Serializable { private static final long serialVersionUID = -5523294561640180605L; @ExcelProperty('A字段') private String aClolumn; @ExcelProperty('B字段') private String BColumn; @ExcelProperty('C字段') private String cColumn; @ExcelProperty('D字段') private String dColumn; @ExcelProperty('E字段') private String eColumn; @ExcelProperty('F字段') private String fColumn; @ExcelProperty('G字段') private Date gColumn; @ExcelProperty('H字段') private Date hColumn; @ExcelProperty('I字段') private String iColumn; @ExcelProperty('J字段') private String jColumn; @ExcelProperty('K字段') private String kColumn; @ExcelProperty('L字段') private String lColumn; @ExcelProperty('M字段') private String mColumn; @ExcelProperty('N字段') private String nColumn; @ExcelProperty('O字段') private String oColumn; @ExcelProperty('P字段') private String pColumn; @ExcelProperty('Q字段') private String qColumn; @ExcelProperty('R字段') private String rColumn; @ExcelProperty('S字段') private String sColumn; @ExcelProperty('T字段') private String tColumn; @ExcelProperty('U字段') private String uColumn; @ExcelProperty('V字段') private double vColumn; @ExcelProperty('W字段') private String wColumn; @ExcelProperty('X字段') private String xClumn; @ExcelProperty('Y字段') private String yColumn; @ExcelProperty('Z字段') private String zColumn;}

生成文件流的步驟（代碼很清晰了）：

/** * EasyExcel 生成文件流 * @param exportData * @return */ private byte[] getEasyExcelOutPutStream(List<WorkLogDto> exportData) {try { WriteCellStyle headWriteCellStyle = new WriteCellStyle(); WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); contentWriteCellStyle.setWrapped(true); HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); EasyExcel.write(outputStream, WorkLogDto.class).sheet('行為業務數據') // Sheet名稱 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .registerWriteHandler(horizontalCellStyleStrategy) .doWrite(exportData); byte[] bytes = outputStream.toByteArray(); // 關閉流 outputStream.close(); return bytes;}catch (Exception ex) { log.error('輸出Excel文件流失敗:'+ex.getMessage()); return null;} }

完整生成Excel文件流并上傳：

/** * 上傳用戶數據報表 * @param prmWorkLogExport * @param order * @param orderType * @return */ @Override @Async public Object uploadWorkLogData(PrmWorkLogExport prmWorkLogExport,ExportTaskDomain domain, String order, String orderType,String suid) {try { log.info(String.format('ExportWorkLog->：%s', '開始獲取數據')); List<WorkLogDto> logList = getLogList(prmWorkLogExport,order,orderType); log.info(String.format('ExportWorkLog->：結束獲取數據，總 %d 條數據', logList.size())); byte[] bytes = getEasyExcelOutPutStream(logList); log.info(String.format('ExportWorkLog->：%s','完成數據轉excel文件流')); /* 暫時作廢 Todo int max=55;int min=40; Random random = new Random(); int rd = random.nextInt(max)%(max-min+1) + min; modifyExportTask(domain.getId(),0,rd);//計算生成數據的進度 */ //開始投遞文件集群服務器，并將結果反寫到數據庫 log.info(String.format('ExportWorkLog->：%s','開始將數據寫入文件服務系統')); Dentry dentry = csService.coverUploadByByteArrayByToken(domain, bytes); //執行異步記錄，以免連接池關閉 executor.execute(() -> {try { asynworkService.finishExportTask(domain.getId(),domain.getFileName(), dentry);} catch (Exception e) { log.error('更新任務狀態失敗:', e.getMessage());} });} catch (Exception ex) { // 1完成 0進行中 2生產錯誤 String updateSql = String.format(' update exporttask set statu=2 where taskid=%s;',domain.getId()); Query query = entityManager.createNativeQuery(updateSql); query.executeUpdate(); entityManager.flush(); entityManager.clear(); log.info(String.format('ExportWorkLog->：上傳文件異常：%s',ex.getMessage()));}return null; }

改用阿里 easyexcel 組件后，10W+ 的數據從生成Excel文件流到上傳只要8秒，原來約要8分鐘 ，以下為各個步驟時間點的日志記錄，可以看出時間消耗：

工具類和使用說明

參考網上整理的工具類，有些類、方法在之前的版本是ok的，新版本下被標記為過時了

package com.nd.helenlyn.common.utils;import com.alibaba.excel.EasyExcelFactory;import com.alibaba.excel.ExcelWriter;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.alibaba.excel.metadata.BaseRowModel;import com.alibaba.excel.metadata.Sheet;import lombok.Data;import lombok.Getter;import lombok.Setter;import lombok.extern.slf4j.Slf4j;import org.springframework.util.CollectionUtils;import org.springframework.util.StringUtils;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.util.ArrayList;import java.util.Collections;import java.util.List;/** * @author brand * @Description: * @Copyright: Copyright (c) 2021 * @Company: XX, Inc. All Rights Reserved. * @date 2021/7/10 3:54 下午 * @Update Time: * @Updater: * @Update Comments: */@Slf4jpublic class EasyExcelUtil {private static Sheet initSheet;static { initSheet = new Sheet(1, 0); initSheet.setSheetName('sheet'); //設置自適應寬度，避免表頭重疊情況 initSheet.setAutoWidth(Boolean.TRUE);}/** * 讀取少于1000行數據的情況 * @param filePath 文件存放的絕對路徑 * @return */public static List<Object> lessThan1000Row(String filePath){ return lessThan1000RowBySheet(filePath,null);}/** * 讀小于1000行數據, 帶樣式 * filePath 文件存放的絕對路徑 * initSheet ： * sheetNo: sheet頁碼，默認為1 * headLineMun: 從第幾行開始讀取數據，默認為0, 表示從第一行開始讀取 * clazz: 返回數據List<Object> 中Object的類名 */public static List<Object> lessThan1000RowBySheet(String filePath, Sheet sheet){ if(!StringUtils.hasText(filePath)){return null; } sheet = sheet != null ? sheet : initSheet; InputStream fileStream = null; try {fileStream = new FileInputStream(filePath);return EasyExcelFactory.read(fileStream, sheet); } catch (FileNotFoundException e) {log.info('找不到文件或文件路徑錯誤, 文件：{}', filePath); }finally {try { if(fileStream != null){fileStream.close(); }} catch (IOException e) { log.info('excel文件讀取失敗, 失敗原因：{}', e);} } return null;}/** * 讀大于1000行數據 * @param filePath 文件存放的絕對路徑 * @return */public static List<Object> mareThan1000Row(String filePath){ return moreThan1000RowBySheet(filePath,null);}/** * 讀大于1000行數據, 帶樣式 * @param filePath 文件存放的絕對路徑 * @return */public static List<Object> moreThan1000RowBySheet(String filePath, Sheet sheet){ if(!StringUtils.hasText(filePath)){return null; } sheet = sheet != null ? sheet : initSheet; InputStream fileStream = null; try {fileStream = new FileInputStream(filePath);ExcelListener excelListener = new ExcelListener();EasyExcelFactory.readBySax(fileStream, sheet, excelListener);return excelListener.getDatas(); } catch (FileNotFoundException e) {log.error('找不到文件或文件路徑錯誤, 文件：{}', filePath); }finally {try { if(fileStream != null){fileStream.close(); }} catch (IOException e) { log.error('excel文件讀取失敗, 失敗原因：{}', e);} } return null;}/** * 生成excle * @param filePath 絕對路徑, 如：/home/{user}/Downloads/123.xlsx * @param data 數據源 * @param head 表頭 */public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head){ writeSimpleBySheet(filePath,data,head,null);}/** * 生成excle * @param filePath 絕對路徑, 如：/home/{user}/Downloads/123.xlsx * @param data 數據源 * @param sheet excle頁面樣式 * @param head 表頭 */public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet){ sheet = (sheet != null) ? sheet : initSheet; if(head != null){List<List<String>> list = new ArrayList<>();head.forEach(h -> list.add(Collections.singletonList(h)));sheet.setHead(list); } OutputStream outputStream = null; ExcelWriter writer = null; try {outputStream = new FileOutputStream(filePath);writer = EasyExcelFactory.getWriter(outputStream);writer.write1(data,sheet); } catch (FileNotFoundException e) {log.error('找不到文件或文件路徑錯誤, 文件：{}', filePath); }finally {try { if(writer != null){writer.finish(); } if(outputStream != null){outputStream.close(); }} catch (IOException e) { log.error('excel文件導出失敗, 失敗原因：{}', e);} }}/** * 生成excle * @param filePath 文件存放的絕對路徑, 如：/home/{user}/Downloads/123.xlsx * @param data 數據源 */public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data){ writeWithTemplateAndSheet(filePath,data,null);}/** * 生成excle * @param filePath 文件存放的絕對路徑, 如：/home/user/Downloads/123.xlsx * @param data 數據源 * @param sheet excle頁面樣式 */public static void writeWithTemplateAndSheet(String filePath, List<? extends BaseRowModel> data, Sheet sheet){ if(CollectionUtils.isEmpty(data)){return; } sheet = (sheet != null) ? sheet : initSheet; sheet.setClazz(data.get(0).getClass()); OutputStream outputStream = null; ExcelWriter writer = null; try {outputStream = new FileOutputStream(filePath);writer = EasyExcelFactory.getWriter(outputStream);writer.write(data,sheet); } catch (FileNotFoundException e) {log.error('找不到文件或文件路徑錯誤, 文件：{}', filePath); }finally {try { if(writer != null){writer.finish(); } if(outputStream != null){outputStream.close(); }} catch (IOException e) { log.error('excel文件導出失敗, 失敗原因：{}', e);} }}/** * 生成多Sheet的excle * @param filePath 絕對路徑, 如：/home/{user}/Downloads/123.xlsx * @param multipleSheelPropetys */public static void writeWithMultipleSheel(String filePath,List<MultipleSheelPropety> multipleSheelPropetys){ if(CollectionUtils.isEmpty(multipleSheelPropetys)){return; } OutputStream outputStream = null; ExcelWriter writer = null; try {outputStream = new FileOutputStream(filePath);writer = EasyExcelFactory.getWriter(outputStream);for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) { Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet; if(!CollectionUtils.isEmpty(multipleSheelPropety.getData())){sheet.setClazz(multipleSheelPropety.getData().get(0).getClass()); } writer.write(multipleSheelPropety.getData(), sheet);} } catch (FileNotFoundException e) {log.error('找不到文件或文件路徑錯誤, 文件：{}', filePath); }finally {try { if(writer != null){writer.finish(); } if(outputStream != null){outputStream.close(); }} catch (IOException e) { log.error('excel文件導出失敗, 失敗原因：{}', e);} }}/*********************以下為內部類，可以提取到獨立類中******************************/@Datapublic static class MultipleSheelPropety{ private List<? extends BaseRowModel> data; private Sheet sheet;}/** * 解析監聽器， * 每解析一行會回調invoke()方法。 * 整個excel解析結束會執行doAfterAllAnalysed()方法 * * @author: chenmingjian * @date: 19-4-3 14:11 */@Getter@Setterpublic static class ExcelListener extends AnalysisEventListener { private List<Object> datas = new ArrayList<>(); /** * 逐行解析 * object : 當前行的數據 */ @Override public void invoke(Object object, AnalysisContext context) {//當前行// context.getCurrentRowNum()if (object != null) { datas.add(object);} } /** * 解析完所有數據后會調用該方法 */ @Override public void doAfterAllAnalysed(AnalysisContext context) {//解析結束銷毀不用的資源 }}}參考資料

語雀例子文檔：https://www.yuque.com/easyexcel/doc/easyexceleasyexcel GitHub地址：https://github.com/alibaba/easyexcel

到此這篇關于Java大批量導出Excel數據的優化過程的文章就介紹到這了,更多相關Java大批量導出Excel 內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！