大数据量写入Excel,试试EasyExcel
By: Date: 2020年11月5日 Categories: 程序 标签:

一直认为大数据量,如几百万上千万的数据写入到Excel是一个极度不合理的需求。对于一个企业来说这么大的数据存在极易泄露的风险,同时也无法对转储的文件及使用环节进行安全管控。而通过线上各个业务系统的打通,形成线上的完整闭环,才能使数据的流转以及使用得到有效的监管和监控。但是在实际的项目中,这样的需求屡屡皆是,尤其出现在与第三方客户业务对接过程中。

尽管如此,以必须要支持的需求为前提,在必须要写入大数据量到Excel的背景下,就要对现有老旧的导出代码做出优化改造。原程序在写入上百万的数据时需要花费几个小时的时间,同时也因为其中掺杂了大量的逻辑判断,远程接口调用等,使得整体的导出效率非常低。在评估了几个Excel处理框架之后,决定还是选择easyexcel来代替原来的poi。它重写了poi对excel的解析,使得原本耗时以及耗内存的操作效率大大提高。通过测试代码,我们写入100W的数据量大概耗时也只在40-50秒左右,速度已经是原本使用poi效率的几十倍了,所以可以通过下面的测试代码来看看easyexcel的效果。

1. 准备工作

首先引入easyexcel,自不必说,这里引入了目前最新的2.2.6版本。

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

easyexcel支持注解的方式指定表头,但我们想要动态的指定表头,那表头自然就会变成动态的配置或者需要参数来传入,因此通过注解的方式指定表头就不是最好的方式,这里我还是不使用注解。
定义我们要写入的数据模型,假设我们有这么多的属性,29个。

public class FeeDetail {
    private String bizLine;
    private String bizType;
    private String feeType;
    private String bizId;
    private String productId;
    private String productName;
    private String customerId;
    private String customerName;
    private String direction;
    private BigDecimal amount;
    private Date dataTime;
    private String dealState;
    private String ynFlag;
    private String cardId;
    private String orgId;
    private String exOneOne;
    private String exOneTwo;
    private String exOneThree;
    private String exTwoOne;
    private String exTwoTwo;
    private String exTwoThree;
    private String exThreeOne;
    private String exThreeTwo;
    private String exThreeThree;
    private String creator;
    private String editor;
    private Date createdTime;
    private Date modifiedTime;
    private String dt;
	//此处省略所有get及set...
}

2. 动态列写入Excel

写入Excel

为了看起来清晰,这里每一个代码块我们单独来说。写入的代码其实非常简单,三两行即可搞定,而我们真正需要在意的是怎么组织数据,怎么修改样式。

@Test
public void dynamicHeadWrite() {
    System.out.println("测试开始时间:" + DateUtils.getCurrentDatetime());
    String fileName = "D:\\TempData\\" + "dynamicHeadWrite" + System.currentTimeMillis() + ".xlsx";

	//指定文件,自定义列宽,注册样式
    EasyExcel.write(fileName).registerWriteHandler(new CustomCellWriteHandler()).registerWriteHandler(this.createStyleStrategy())
            // 这里放入动态头,定义Sheet名称
            .head(this.createHeader()).sheet("模板")
            // 数据使用 List<List<Object>> 去传入,数据量为100W条记录
            .doWrite(this.convertList(createData(1000000)));
    System.out.println("测试结束时间:" + DateUtils.getCurrentDatetime());
}

可以看到,上面的代码我们自己创建表头,定义了Sheet名,已经模拟了查询DB并用来写入的数据。注释很清楚,比较好理解。接下来看看各个部分的代码。

2.1 自适应宽度

为了使我们写入的列能够自定义宽度,而不是所有列等宽,我们增加一个CustomCellWriteHandler类来自适应列宽度。这个类来自网络,已经忘了从哪里摘抄的,但是目前很好用。通过计算单元格值的长度,来设置列宽。

public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

2.2 构造表头

再来看看创建表头的方法,表头实际上代表每一列的含义,只是一个标题。测试中,我直接将表头写在了代码中,当然传参是一个很好的方式。

/**
 * 构造表头
 * @return
 */
private List<List<String>> createHeader() {
    System.out.println("定义表头开始时间:" + DateUtils.getCurrentDatetime());
    List<List<String>> list = new ArrayList<>();
    String[] headers = new String[]{"业务类型","费用名称","产品ID","产品名称","客户ID","客户名称","业务单号","业务时间",
            "汇总金额","主体ID","扩展1-1","扩展1-2","扩展1-3","扩展2-1","扩展2-2","扩展2-3","扩展3-1","扩展3-2","扩展3-3"};
    for(String title:headers){
        List<String> head = new ArrayList<>();
        head.add(title);
        list.add(head);
    }
    System.out.println("定义表头开始时间:" + DateUtils.getCurrentDatetime());
    return list;
}

2.3 定义样式

为了让我们写入的表格好看一点,我们还需要做一些样式上的调整。设置了表头的背景,以及内容的边框,以及字体大小。

/**
 * 定义样式header 和body的样式
 * @return
 */
private HorizontalCellStyleStrategy createStyleStrategy(){
    // 头的策略
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // 背景设置为红色
    headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short)10);
    headWriteCellStyle.setWriteFont(headWriteFont);
    // 内容的策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);  //底边框
    contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //左边框
    contentWriteCellStyle.setBorderRight(BorderStyle.THIN);  //右边框
    contentWriteCellStyle.setBorderTop(BorderStyle.THIN);  //顶边框
    WriteFont contentWriteFont = new WriteFont();
    // 字体大小
    contentWriteFont.setFontHeightInPoints((short)10);
    contentWriteCellStyle.setWriteFont(contentWriteFont);
    // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);

    return horizontalCellStyleStrategy;
}

2.4 构造表格数据

这里的数据理应来自于数据库,这里我直接模拟生成查询出来的数据,是一个List类型,而我们将要按照顺序写入内容,那么我们还需要做一个到List的转换。

/**
 * 模拟查询数据库数据
 * @param rowCount  行数
 * @return
 */
private List<FeeDetail> createData(int rowCount) {
    System.out.println("模拟查询数据开始时间:" + DateUtils.getCurrentDatetime());
    List<FeeDetail> list = new ArrayList<>();
    for (int i = 0; i < rowCount; i++) {
        FeeDetail data = new FeeDetail();
        data.setBizBType("金融科技");
        data.setBizType("信贷业务");
        data.setFeeType("信用贷款");
        data.setProductId("jsb_0001023");
        data.setProductName("社会XX银行-免费送产品");
        data.setCustomerId("CRPL_JSB");
        data.setCustomerName("社会XX银行股份有限公司");
        data.setBizId("10JI01043HKWER0127843JIWER");
        data.setDataTime(new Date());
        data.setAmount(new BigDecimal("10231.21"));
        list.add(data);
    }
    System.out.println("模拟查询数据结束时间:" + DateUtils.getCurrentDatetime());
    return list;
}
/**
 * 模拟查询数据库数据
 * @param rowCount  行数
 * @return
 */
private List<FeeDetail> createData(int rowCount) {
    System.out.println("模拟查询数据开始时间:" + DateUtils.getCurrentDatetime());
    List<FeeDetail> list = new ArrayList<>();
    for (int i = 0; i < rowCount; i++) {
        FeeDetail data = new FeeDetail();
        data.setBizBType("金融科技");
        data.setBizType("信贷业务");
        data.setFeeType("信用贷款");
        data.setProductId("jsb_0001023");
        data.setProductName("社会XX银行-免费送产品");
        data.setCustomerId("CRPL_JSB");
        data.setCustomerName("社会XX银行股份有限公司");
        data.setBizId("10JI01043HKWER0127843JIWER");
        data.setDataTime(new Date());
        data.setAmount(new BigDecimal("10231.21"));
        list.add(data);
    }
    System.out.println("模拟查询数据结束时间:" + DateUtils.getCurrentDatetime());
    return list;
}
/**
 * 转换List<Po>到List<List<Object>>
 * @param demoDataList
 * @return
 */
private List<List<Object>> convertList(List<FeeDetail> demoDataList) {
    System.out.println("数据转换List执行开始时间:" + DateUtils.getCurrentDatetime());
    List<List<Object>> rowList = new ArrayList<>();
    String[] properties = new String[]{"bizType", "feeType", "productId", "productName", "customerId", "customerName", "bizId", "dataTime",
            "amount", "orgId", "exOneOne", "exOneTwo", "exOneThree", "exTwoOne", "exTwoTwo", "exTwoThree", "exThreeOne", "exThreeTwo", "exThreeThree"};

    //根据属性名得倒get方法名
    String[] getters = new String[properties.length];
    for (int i = 0; i < properties.length; i++) {
        getters[i] = "get" + properties[i].substring(0, 1).toUpperCase() + properties[i].substring(1);
    }
    //循环调用get方法,添加到List<Object>中
    List<Object> colList;
    try {
        for (FeeDetail o : demoDataList) {
            colList = new ArrayList<>();
            for (String getter : getters) {
                Method method = o.getClass().getMethod(getter, new Class[]{});
                Object value = method.invoke(o, new Object[]{});
                colList.add(value);
            }
            rowList.add(colList);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    System.out.println("数据转换List执行结束时间:" + DateUtils.getCurrentDatetime());
    return rowList;
}

到这里,我们已经完成了写入Excel所需要的所有操作,执行启动,那么效果就是这样:

而我们实际上是想测试写入的效率,那么,看看我们的执行时间,从开始写入到真正的写入完成,100W的数据量所需要的时间也只是在45秒左右,已经是相当的快了。


3. 多个Sheet页写入

多个Sheet写入其实类似,大体代码如下:

@Test
public void dynamicHeadBatchWrite() {
    System.out.println("测试开始时间:" + DateUtils.getCurrentDatetime());
    String fileName = "D:\\TempData\\" + "dynamicHeadWrite" + System.currentTimeMillis() + ".xlsx";
	// 定义文件
    ExcelWriter excelWriter = EasyExcel.write(fileName).registerWriteHandler(this.createStyleStrategy()).registerWriteHandler(new CustomCellWriteHandler()).build();
    // 这里指定第一个Sheet,放入动态头
    WriteSheet writeSheet = EasyExcel.writerSheet(0,"模板-1").head(this.createHeader()).build();
    // 数据依旧用 List<List<Object>> 去传入,写入1000行数据
    excelWriter.write(this.convertList(createData(1000)),writeSheet);
    // 这里指定第二个Sheet,放入动态头
    WriteSheet writeSheet2 = EasyExcel.writerSheet(1,"模板-2").head(this.createHeader()).build();
    // 依旧用List<List<Object>> 去传入,写入1W行数据
    excelWriter.write(this.convertList(createData(10000)),writeSheet2);
    excelWriter.finish();
}

多说一句,easyexcel目前我没找到有什么办法可以指定从第几个Sheet开始写入,比如写完Sheet1之后,关闭文件即流被关闭后,再重新添加Sheet2写入该文件的Sheet2中。如果按照这个思路去做,当按照这个想法写入Sheet2时,发现Sheet1已经不存在或者被覆盖了。因此只能一次性的将所有Sheet全部写入后关闭,不能关闭流之后再次指定文件写入。


4. 分文件分Sheet写入

其实上面的过程都是easyexcel具体的使用了,那么来看看我们现在真实,更加复杂的,大数据量以及动态写入场景:

  1. 按客户导出数据,即可以单个客户数据写入Excel,也可以多个客户合并写入Excel。
  2. 在1的基础上,按照不同的业务,将不同业务的数据根据配置放在同一个Sheet或者不同的Sheet中。
  3. 数据量在500W左右,那么每个sheet显示50W数据,超出50W新创建一个文件来写入。

原本在使用poi导出时,由于是一行一行写入,我们可以很方便的控制每一行的写入,也可以很方便的控制写入到哪个Sheet。当一个Sheet页写满之后,我们创建新的文件来再次写入,当这个Sheet的数据全部写完之后,再回到第一个文件,开始写入第二个Sheet,以此类推。然而当使用easyexcel时,这样的操作就无法实现了,我们需要从原先的按Sheet维度写入,变成按照文件的维度写入,即由纵向变成了横向。也就是说,当我们的第一个文件Sheet写到上限之后,我们需要把这个文件需要写入的其他Sheet全部写完之后,才能创建一个新的文件去继续写入之前Sheet没有写完的剩余数据。

那么到这里,其实理解了我们的用意,那么剩下的就是建模与实际的操作了。直接上代码,先来创建我们的模型。

/**
 * 定义每个文件的Sheet信息
 */
public class SheetBaseInfo {
    /**
     * Sheet名称
     */
    String sheetName;
    /**
     * 当前sheet已写入行数,每个Sheet假设只允许写入50W
     */
    Integer usedRowCount;
    /**
     * 当前sheet的表头
     */
    ExcelContentHandler excelContentHandler;
    /**
     * 当前sheet所需要写入的所有数据查询信息
     */
    List<SheetDataFilterInfo> recordsFilter;
}
/**
 * 定义每个Sheet中所需要的数据(数据以查询条件的形式存放)
 */
public class SheetDataFilterInfo {
    /**
     * 当前sheet费用类型,不按费用类型拆分则为null
     */
    String feeType;
    /**
     * 当前条件下数据库总数据分页数量
     */
    Integer pageCount;
    /**
     * 当前已写入到第几页
     */
    Integer currentPageIndex;
    /**
     * 查询条件查询的总记录行数
     */
    Integer recordCount;
    /**
     * 当前页写入时需要跳过多少个记录
     */
    Integer skipCount;
    /**
     * 汇总查询条件,包含一次汇总和二次汇总
     */
    EntityWrapper<FeeDetailBase> queryBaseWrapper;
    /**
     * 明细查询条件
     */
    EntityWrapper<FeeDetailSource> querySourceWrapper;
    /**
     * 数据查询条件相关联的规则信息
     */
    SrmCollectDetailConditionVo vo;

    public SheetDataFilterInfo(String feeType, Integer pageCount, Integer currentPageIndex, Integer recordCount, Integer skipCount, EntityWrapper<FeeDetailBase> queryBaseWrapper, EntityWrapper<FeeDetailSource> querySourceWrapper, SrmCollectDetailConditionVo vo) {
        //...省略赋值...
    }
}

queryBaseWrapperquerySourceWrapper是假定我们的不同的Sheet可能放不同类型的数据。比如第一个Sheet放入的是汇总的数据,第二个Sheet写入的是明细的数据,表头是不一样的。

/**
 * 分Sheet分文件写入
 * @param baseDirPath
 * @param vo
 * @param baseQuery
 * @param conditionVoList
 * @param custRuleRelSet
 */
private void doExportExcel(String baseDirPath, SrmCollectDetailConditionVo vo, FeeDetailBase baseQuery, List<SrmCollectDetailConditionVo> conditionVoList, Set<Long> custRuleRelSet) {
	// ...省略对Sheet信息的获取...

	// 获取需要写入的所有Sheet信息以及他们的查询数据条件
	List<SheetBaseInfo> sheetBaseInfoList = contextTL.get().getFileSheetInfoMap().get(vo.getCollectRule().getRuleName());
	// 当前文件索引,用于分文件时,记录当前写到了第几个文件
	int fileIndex = 1;
	boolean needWriteFile= true;
	while(needWriteFile){
		// 假设当前文件都能全部写完,所以只跑一次。
		// 进来之后改为false,即不需要再写入
		needWriteFile=false;
		
		// 设置当前写入的是第几个文件,从1开始
		contextTL.get().setFileIndex(fileIndex);
		// 指定文件
		ExcelFileInfo excelFileInfo = this.generateFile(baseDirPath,vo.getCollectRule());
		ExcelWriter excelWriter = EasyExcelUtils.buildDynamicHeadExcelWriter(excelFileInfo.getFileNamePath());
		// Excel文件中sheet索引,从0开始
		int sheetIndex=0;

		//需要写入到Sheet中的内容数据
		List<List<Object>> records;

		//遍历每个Sheet
		for (SheetBaseInfo sheetDetail : sheetBaseInfoList) {
			//指定sheet
			logger.info(String.format("开始写入新的sheet页:%s...",sheetDetail.getSheetName()));
			WriteSheet writeSheet = EasyExcel.writerSheet(sheetIndex, sheetDetail.getSheetName()).head(EasyExcelUtils.convertArrToListHeader(sheetDetail.getExcelContentHandler().generateTitles())).build();
			//遍历每个Sheet 中的数据查询条件
			for (SheetDataFilterInfo dataFilter : sheetDetail.getRecordsFilter()) {
				//判断是否按方向汇总
				boolean collectWithDirection = false;
				if (YNFlagStatusEnum.VALID.getCode().equals(dataFilter.getVo().getCollectRule().getDirectionCollect())) {
					//按照收付方向求和
					collectWithDirection = true;
				}

				//分页查询,从记录的页数开始,到总页数结束
				for (int pageNo = dataFilter.getCurrentPageIndex(); pageNo <= dataFilter.getPageCount(); pageNo++) {
					logger.info(String.format("分页写入 logicName=%s page=%d pageNo=%d", contextTL.get().getLogicName(), dataFilter.getPageCount(), pageNo));
					
					if (dataFilter.getQueryBaseWrapper() != null) {
						Page<FeeDetailBase> queryPage = new Page(pageNo, srmConfigProperties.getExcelFilterPageSize());
						queryPage = srmFeeDetailBaseService.selectPage(queryPage, dataFilter.getQueryBaseWrapper());
						for (FeeDetailBase ffd : queryPage.getRecords()) {
							//...省略对数据的填充处理...
						}
						//转换数据,从List<Po>转换成List<List<Object>>
						records = EasyExcelUtils.convertPoToList(queryPage.getRecords(), sheetDetail.getExcelContentHandler().generateFieldNames());
					} else {
						Page<FeeDetailSource> queryPage = new Page(pageNo, srmConfigProperties.getExcelFilterPageSize());
						queryPage = srmFeeDetailSourceService.selectPage(queryPage, dataFilter.getQuerySourceWrapper());

						for (FeeDetailSource ffd : queryPage.getRecords()) {
							//...省略对数据的填充处理
						}
						//转换数据,从List<Po>转换成List<List<Object>>
						records = EasyExcelUtils.convertPoToList(queryPage.getRecords(), sheetDetail.getExcelContentHandler().generateFieldNames());
					}
					if (records.size() == 0) {
						// 这里起始可以不要
						continue;
					}

					int skip = dataFilter.getSkipCount();  //跳过指定数量的记录

					//当前分页记录总的剩余数(当前页总数据量-跳过数) > 每个文件的剩余可用行数(每个文件的最大行数-已使用行数)? 当前分页记录总的剩余数 : 每个文件的剩余使用行数
					int limit = records.size() - skip < srmConfigProperties.getExcelSheetSize() - sheetDetail.getUsedRowCount() ? records.size() - skip : srmConfigProperties.getExcelSheetSize() - sheetDetail.getUsedRowCount();
					if (limit > 0) {
						List<List<Object>> subList = records.stream().skip(skip).limit(limit).collect(Collectors.toList());
						excelWriter.write(subList, writeSheet);

						if (records.size() == limit + skip) {
							//表示当前页写完了,页数+1
							dataFilter.setCurrentPageIndex(pageNo);
							//设置当前页已使用量
							sheetDetail.setUsedRowCount(sheetDetail.getUsedRowCount() + limit);
							if(pageNo==dataFilter.getPageCount()){
								//判断当前页是否是最后一页
								//设置跳过行数
								dataFilter.setSkipCount(limit + skip);
								break;
							}else {
								//设置跳过行数为0
								dataFilter.setSkipCount(0);
								dataFilter.setCurrentPageIndex(pageNo+1);
								//继续下一页
								continue;
							}
						}
					}
					//当前页,写不下了
					//记录当前写到的位置
					dataFilter.setSkipCount(skip + limit);
					//记录当前条件已经写入到第几页
					dataFilter.setCurrentPageIndex(pageNo);
					//那么就需要再写入一个新的文件中,另起一个sheet
					needWriteFile = true;
					//还要设置新的sheet的已使用行数为0
					sheetDetail.setUsedRowCount(0);
					//不写了,继续写下一个sheet
					break;
				}
			}

			//移除当前sheet的已经写完的数据查询条件
			List<SheetDataFilterInfo> finishedSheetDetailList = sheetDetail.getRecordsFilter().stream().filter(o -> o.getCurrentPageIndex() == o.getPageCount() &amp;&amp; o.getRecordCount() == ((o.getCurrentPageIndex()-1) * srmConfigProperties.getExcelFilterPageSize() + o.getSkipCount())).collect(Collectors.toList());
			sheetDetail.getRecordsFilter().removeAll(finishedSheetDetailList);

			sheetIndex++;
		}
		if(needWriteFile){
			// 设置下一个文件的索引++
			fileIndex++;
		}

		//移除已经全部写完的sheet信息
		List<SheetBaseInfo> finishedSheetBaseInfoList = sheetBaseInfoList.stream().filter(o->o.getRecordsFilter().size()==0).collect(Collectors.toList());
		sheetBaseInfoList.removeAll(finishedSheetBaseInfoList);

		//关闭当前文件写入
		excelWriter.finish();
	}
}

到这里,我们就已经可以实现了上百万及千万的数据,按照每个Sheet页50W的数据,分文件以及分Sheet写入。其实最重要的是构造好写入的文件的位置,保证位置的正确,导出的数据能正确的衔接。好了,上面只是抛砖引玉,每个需求有自己的复杂性,以实际情况来评估。


5. 参考资料

  1. alibaba/easyexcel https://github.com/alibaba/easyexcel
  2. easyexcel官网 https://www.yuque.com/easyexcel/doc/easyexcel

发表评论

电子邮件地址不会被公开。 必填项已用*标注