锐客网

  1. 首页 > it技术 > >

Java大批量导出Excel数据的优化过程

目录

  • 背景
  • 问题和解决方案
    • 遇到的问题
    • 解决步骤
  • 整理工具类
    • 参考资料

      背景
      团队目前在做一个用户数据看板（下面简称看板），基本覆盖用户的所有行为数据，并生成分析报表，用户行为由多个数据来源组成（餐饮、生活日用、充值消费、交通出行、通讯物流、交通出行、医疗保健、住房物业、运动健康...），
      基于大量数据的组合、排序和统计。根据最新的统计报告，每天将近100W+的行为数据产生，所以这个数据基数是非常大的。
      而这个数据中心，对接很多的业务团队，这些团队根据自己的需要，对某些维度进行筛选，然后直接从我们的中心上下载数据（excel）文档进行分析。所以下个几十万上百万行的数据是很常见的。

      问题和解决方案

      遇到的问题

      目前遇到的主要问题是，随着行为能力逐渐的完善闭环，用户数据沉淀的也越来越多了，同时业务量的也在不断扩大。
      业务团队有时候会下载超量的数据来进行分析，平台上的数据下载能力就显得尤为重要了。而我们的问题是下载效率太慢，10W的数据大约要5分钟以上才能下载下来，这显然有问题了。

      解决步骤

      代码是之前团队遗留的，原先功能没开放使用，没有数据量，所以没有发现问题。以下是原来的导出模块，原程序如下，我做了基本还原。
      现在如何保证数据的高效导出是我们最重要的目标，这个也是业务团队最关心的。
      /*** 获取导出的Excel的文件流信息* @param exportData* @return* @throws Exception*/private OutputStream getExportOutPutStream(List exportData) throws Exception {JSONObject object = new JSONObject(); List 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
      看看标红的代码，这个生成Excel的方式是对Excel中的每一个cell进行渲染，逐行的进行数据填充，效率太慢了，根据日志分析发现：基本时间都耗费在数据生成Excel上。每生成1W左右的数据基本
      【Java大批量导出Excel数据的优化过程】消耗1分钟的时间。原来在其他业务中他只是作为简量数据导出来使用，比如几百条的数据，很快就出来了，但是遇到大量数据导出的情况，性能问题就立马现形了。
      团队内讨论了一下并参考了资料，发现原来业内有很多好用强大的Excel处理组件，我们优先选用阿里的easy excel来做一下尝试。
      Pom添加 easyexcel 如下：
      com.alibabaeasyexcel2.1.4

      代码：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; /** * Description:XX表基本信息
      * Copyright: Copyright (c) 2021
      * Company: XX Co., Ltd.
      * * @author brand * @date 2021-06-26 10:07:46 * Update Time:
      * Updater:
      * Update Comments:
      */@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 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@Asyncpublic Object uploadWorkLogData(PrmWorkLogExport prmWorkLogExport,ExportTaskDomain domain, String order, String orderType,String suid) {try {log.info(String.format("ExportWorkLog->：%s", "开始获取数据")); List logList = getLogList(prmWorkLogExport,order,orderType); log.info(String.format("ExportWorkLog->：结束获取数据，总 %d 条数据", logList.size())); byte[] bytes = getEasyExcelOutPutStream(logList); log.info(String.format("ExportWorkLog->：%s","完成数据转excel文件流")); /* 暂时作废 Todoint 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分钟 ，以下为各个步骤时间点的日志记录，可以看出时间消耗：
      Java大批量导出Excel数据的优化过程
      文章图片

      Java大批量导出Excel数据的优化过程
      文章图片


      整理工具类
      工具类和使用说明

      参考网上整理的工具类，有些类、方法在之前的版本是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 lessThan1000Row(String filePath){return lessThan1000RowBySheet(filePath,null); }/*** 读小于1000行数据, 带样式* filePath 文件存放的绝对路径* initSheet ：*sheetNo: sheet页码，默认为1*headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取*clazz: 返回数据List 中Object的类名*/public static List 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 mareThan1000Row(String filePath){return moreThan1000RowBySheet(filePath,null); }/*** 读大于1000行数据, 带样式* @param filePath 文件存放的绝对路径* @return*/public static List 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> data, List 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> data, List head, Sheet sheet){sheet = (sheet != null) ? sheet : initSheet; if(head != null){List 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 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 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 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 data; private Sheet sheet; }/*** 解析监听器，* 每解析一行会回调invoke()方法。* 整个excel解析结束会执行doAfterAllAnalysed()方法** @author: chenmingjian* @date: 19-4-3 14:11*/@Getter@Setterpublic static class ExcelListener extends AnalysisEventListener {private List datas = new ArrayList<>(); /*** 逐行解析* object : 当前行的数据*/@Overridepublic void invoke(Object object, AnalysisContext context) {//当前行// context.getCurrentRowNum()if (object != null) {datas.add(object); }}/*** 解析完所有数据后会调用该方法*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//解析结束销毁不用的资源}}}

      参考资料
      语雀例子文档：https://www.yuque.com/easyexcel/doc/easyexcel
      easyexcel GitHub地址：https://github.com/alibaba/easyexcel

      到此这篇关于Java大批量导出Excel数据的优化过程的文章就介绍到这了,更多相关Java大批量导出Excel 内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

        推荐阅读


        上一篇：Python机器学习NLP自然语言处理基本操作关键词

        下一篇：pyTorch深度学习多层感知机的实现