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

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","AEMhWogTjF字段","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

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 如下：

com.alibaba

easyexcel

2.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表基本信息

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 如下：

com.alibaba

easyexcel

2.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

@Getter

public 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

@Async

public 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文件流"));

/* 暂时作废 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:

*/

@Slf4j

public 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

return lessThan1000RowBySheet(filePath,null);

}

/**

* 读小于1000行数据, 带样式

* filePath 文件存放的绝对路径

* initSheet ：

* sheetNo: sheet页码，默认为1

* headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取

* clazz: 返回数据List

*/

public static List

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

return moreThan1000RowBySheet(filePath,null);

}

/**

* 读大于1000行数据, 带样式

* @param filePath 文件存放的绝对路径

* @return

*/

public static List

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 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 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);

}

}

}

/*********************以下为内部类，可以提取到独立类中******************************/

@Data

public static class MultipleSheelPropety{

private List extends BaseRowModel> data;

private Sheet sheet;

}

/**

* 解析监听器，

* 每解析一行会回调invoke()方法。

* 整个excel解析结束会执行doAfterAllAnalysed()方法

*

* @author: chenmingjian

* @date: 19-4-3 14:11

*/

@Getter

@Setter

public static class ExcelListener extends AnalysisEventListener {

private List

/**

* 逐行解析

* object : 当前行的数据

*/

@Override

public void invoke(Object object, AnalysisContext context) {

//当前行

// context.getCurrentRowNum()

if (object != null) {

datas.add(object);

}

}

/**

* 解析完所有数据后会调用该方法

*/

@Override

public void doAfterAllAnalysed(AnalysisContext context) {

//解析结束销毁不用的资源

}

}

}

参考资料

语雀例子文档：https://yuque.com/easyexcel/doc/easyexcel

easyexcel github地址：https://github.com/alibaba/easyexcel

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。