一直认为大数据量，如几百万上千万的数据写入到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具体的使用了，那么来看看我们现在真实，更加复杂的，大数据量以及动态写入场景：
- 按客户导出数据，即可以单个客户数据写入Excel，也可以多个客户合并写入Excel。
- 在1的基础上，按照不同的业务，将不同业务的数据根据配置放在同一个Sheet或者不同的Sheet中。
- 数据量在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) {
//...省略赋值...
}
}
queryBaseWrapper和querySourceWrapper是假定我们的不同的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() && 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. 参考资料
- alibaba/easyexcel https://github.com/alibaba/easyexcel
- easyexcel官网 https://www.yuque.com/easyexcel/doc/easyexcel