什么，你的EasyExcel導(dǎo)出一萬(wàn)條數(shù)據(jù)就OOM了？
前言
前段時(shí)間在做一個(gè)導(dǎo)出的功能，本以為是平平無(wú)奇的一個(gè)功能。就用公司內(nèi)部的一個(gè)導(dǎo)出工具類(lèi)三下五除二就寫(xiě)完了，做法是直接查全量數(shù)據(jù)，然后直接往Excel里寫(xiě)。一開(kāi)始沒(méi)多少數(shù)據(jù)也沒(méi)什么問(wèn)題，但是當(dāng)數(shù)據(jù)量逐漸多了起來(lái)后，達(dá)到一萬(wàn)多條，導(dǎo)出的時(shí)候就會(huì)報(bào)OOM。然后我就換成了阿里開(kāi)源的EasyExcel，但是導(dǎo)出的時(shí)候也不太穩(wěn)定，偶爾也會(huì)OOM。所以應(yīng)該是數(shù)據(jù)量太大了，在寫(xiě)入的時(shí)候把內(nèi)存占滿了。然后我就放棄了查全量數(shù)據(jù)一次性寫(xiě)入Excel的做法，采用分頁(yè)查詢，分批次寫(xiě)入Excel的方式，果然不會(huì)出現(xiàn)OOM了。
雖然這種方式不會(huì)出現(xiàn)OOM，但是每次導(dǎo)出都寫(xiě)一遍重復(fù)的代碼著實(shí)有點(diǎn)麻煩，所以結(jié)合自己平時(shí)的使用場(chǎng)景，封裝了一個(gè)EasyExcel的導(dǎo)出工具類(lèi)，這樣只要在分頁(yè)查詢的基礎(chǔ)上寫(xiě)少量的代碼，就可以實(shí)現(xiàn)分批次寫(xiě)入Excel，簡(jiǎn)化代碼的編寫(xiě)并且解決OOM的問(wèn)題。
實(shí)現(xiàn)
@Slf4j
public abstract class EasyExcelExport<T, S> {
/**
* EasyExcel導(dǎo)出Excel表格,每個(gè)sheet默認(rèn)最大10萬(wàn)條數(shù)據(jù)
*
* @param fileName excel文件前綴名
* @param sheetName 表頁(yè)名
*/
public void easyExcelBatchExport(String fileName, String sheetName, HttpServletResponse response) {
this.easyExcelBatchExport(fileName, sheetName, 100000, response);
}
/**
* 分批次導(dǎo)出excel數(shù)據(jù)
*
* @param fileName excel文件前綴名
* @param sheetSize 每個(gè)sheet的數(shù)據(jù)量,默認(rèn)10萬(wàn),excel有限制不能大于1048576
* @param sheetName 表頁(yè)名
*/
public void easyExcelBatchExport(String fileName, String sheetName, Integer sheetSize, HttpServletResponse response) {
fileName = fileName + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".xlsx";
int currentSheet = 1; // 當(dāng)前處于第幾個(gè)sheet
int totalLine = 0; // 總共寫(xiě)入的條數(shù)
int currentBatch = 1; // 當(dāng)前寫(xiě)入excel的批次(第幾頁(yè))
int lineNum = 1; // 行號(hào),當(dāng)前寫(xiě)入的是第幾條數(shù)據(jù)
long startTime = System.currentTimeMillis();
try {
response.setCharacterEncoding("utf-8");
// 告訴瀏覽器用什么軟件可以打開(kāi)此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下載文件的默認(rèn)名稱
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0]).build();
WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();
while (true) {
// 獲取數(shù)據(jù)，然后currentBatch+1,下次調(diào)用就會(huì)獲取新的數(shù)據(jù)
List<S> sourceDataList = getData(currentBatch);
currentBatch++;
List<T> exportEntityList = new ArrayList<>();
if (CollUtil.isNotEmpty(sourceDataList)) {
totalLine += sourceDataList.size();
log.info("EasyExcel開(kāi)始寫(xiě)入第{}批數(shù)據(jù),當(dāng)前批次數(shù)據(jù)大小為{}", currentBatch - 1, sourceDataList.size());
for (S sourceData : sourceDataList) {
exportEntityList.add(convertSourceData2ExportEntity(sourceData, lineNum));
lineNum++;
// 當(dāng)前sheet數(shù)據(jù)已經(jīng)到達(dá)最大值，將當(dāng)前數(shù)據(jù)全寫(xiě)入當(dāng)前sheet，下一條數(shù)據(jù)就會(huì)寫(xiě)入新sheet
if (lineNum > sheetSize) {
excelWriter.write(exportEntityList, sheet);
exportEntityList.clear();
lineNum = 1;
currentSheet++;
sheet = EasyExcel.writerSheet(sheetName + currentSheet).build();
}
}
// 寫(xiě)入excel
excelWriter.write(exportEntityList, sheet);
} else {
// 未獲取到數(shù)據(jù),結(jié)束
break;
}
}
excelWriter.finish();
} catch (Exception e) {
log.error("EasyExcel導(dǎo)出異常", e);
}
log.info("EasyExcel導(dǎo)出數(shù)據(jù)結(jié)束,總數(shù)據(jù)量為{},耗時(shí){}ms", totalLine, (System.currentTimeMillis() - startTime));
}
/**
* 不分批次導(dǎo)出excel。一次性獲取所有數(shù)據(jù)寫(xiě)入excel,確定數(shù)據(jù)量不大時(shí)可以使用該方法，數(shù)據(jù)量過(guò)大時(shí)使用分批次導(dǎo)出，否則會(huì)OOM
*
* @param fileName excel文件前綴名
* @param sheetName 表頁(yè)名
*/
public void easyExcelExport(String fileName, String sheetName, HttpServletResponse response) {
fileName = fileName + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".xlsx";
int totalLine = 0; // 總共寫(xiě)入的條數(shù)
int lineNum = 1; // 行號(hào),當(dāng)前寫(xiě)入的是第幾條數(shù)據(jù)
long startTime = System.currentTimeMillis();
try {
response.setCharacterEncoding("utf-8");
// 告訴瀏覽器用什么軟件可以打開(kāi)此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下載文件的默認(rèn)名稱
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
List<S> sourceDataList = getData(1);
List<T> exportEntityList = new ArrayList<>();
if (CollUtil.isNotEmpty(sourceDataList)) {
totalLine += sourceDataList.size();
log.info("EasyExcel開(kāi)始寫(xiě)入數(shù)據(jù),數(shù)據(jù)大小為{}", sourceDataList.size());
for (S sourceData : sourceDataList) {
exportEntityList.add(convertSourceData2ExportEntity(sourceData, lineNum));
lineNum++;
}
}
response.setCharacterEncoding("utf-8");
// 告訴瀏覽器用什么軟件可以打開(kāi)此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下載文件的默認(rèn)名稱
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
EasyExcel.write(response.getOutputStream(), (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0]).sheet(sheetName).doWrite(exportEntityList);
} catch (Exception e) {
log.error("EasyExcel導(dǎo)出異常", e);
}
log.info("EasyExcel導(dǎo)出數(shù)據(jù)結(jié)束,總數(shù)據(jù)量為{},耗時(shí){}ms", totalLine, (System.currentTimeMillis() - startTime));
}
/**
* 將原數(shù)據(jù)對(duì)象轉(zhuǎn)換為需要導(dǎo)出的目標(biāo)對(duì)象
*
* @param sourceData 原對(duì)象
* @param lineNum 行號(hào)
*/
public abstract T convertSourceData2ExportEntity(S sourceData, Integer lineNum);
/**
* 獲取原始數(shù)據(jù),通過(guò)currentBatch參數(shù)分頁(yè)獲取數(shù)據(jù)。
*
* @param currentBatch 獲取第幾批(頁(yè))數(shù)據(jù),通過(guò)該參數(shù)分頁(yè)查詢,每次調(diào)用自動(dòng)遞增。不分批次導(dǎo)出時(shí)可以忽略該參數(shù)
*/
public abstract List<S> getData(int currentBatch);
}首先，這是EasyExcelExport是一個(gè)抽象類(lèi)，指定了泛型 T 和 S，T是target目標(biāo)類(lèi)，也就是導(dǎo)出時(shí)對(duì)應(yīng)的類(lèi)，S是source原對(duì)象所對(duì)應(yīng)的類(lèi)。
EasyExcelExport里還有兩個(gè)抽象方法，getData() 和 convertSourceData2ExportEntity() 。這兩個(gè)方法是需要在平時(shí)使用時(shí)自己去實(shí)現(xiàn)的，getData是數(shù)據(jù)查詢的方法，可以在這里面去實(shí)現(xiàn)分頁(yè)查詢的邏輯，currentBatch參數(shù)是用來(lái)控制分頁(yè)查詢頁(yè)碼的，從1開(kāi)始，會(huì)自動(dòng)遞增。如果確定數(shù)據(jù)量不大不需要分批次導(dǎo)出的話，那么getData()里只需要進(jìn)行普通的查詢即可，忽略currentBatch參數(shù)不用分頁(yè)查詢。還有一個(gè)方法是convertSourceData2ExportEntity(),這個(gè)是用來(lái)將對(duì)象S轉(zhuǎn)為對(duì)象T的方法，因?yàn)閺臄?shù)據(jù)庫(kù)查詢或者是從其他地方獲取到的對(duì)象類(lèi)型可能是S，而導(dǎo)出時(shí)需要的對(duì)象類(lèi)型是T，所以通過(guò)該方法進(jìn)行對(duì)象轉(zhuǎn)換。
最核心的是 easyExcelBatchExport() 方法，里面有一個(gè)while循環(huán)，while循環(huán)里首先會(huì)去調(diào)用getData()方法獲取數(shù)據(jù)，然后將currentBatch加1便于下次獲取數(shù)據(jù)，接下來(lái)有個(gè)for循環(huán)去進(jìn)行對(duì)象的轉(zhuǎn)換并添加到exportEntityList集合中，這個(gè)集合中裝的是最終寫(xiě)到Excel里的對(duì)象。當(dāng)轉(zhuǎn)換完成后就將當(dāng)前批次的數(shù)據(jù)寫(xiě)入Excel中，然后進(jìn)行下一次循環(huán)，當(dāng)getData()方法未獲取到數(shù)據(jù)時(shí)，就結(jié)束循環(huán)。
同時(shí)支持指定每個(gè)sheet頁(yè)的最大行數(shù)。在對(duì)對(duì)象進(jìn)行轉(zhuǎn)換時(shí)有一個(gè)判斷，當(dāng)前sheet頁(yè)的數(shù)據(jù)是否到達(dá)指定值，到達(dá)后，直接寫(xiě)入excel，然后新建一個(gè)sheet頁(yè)，這樣新的數(shù)據(jù)就會(huì)寫(xiě)入新的sheet頁(yè)。
使用
那么如何使用這個(gè)工具類(lèi)呢。很簡(jiǎn)單，只要new出EasyExcelExport的對(duì)象，然后實(shí)現(xiàn)一下 convertSourceData2ExportEntity() 方法和 getData() 方法即可，然后再根據(jù)需要去調(diào)用不同的導(dǎo)出方法即可。導(dǎo)出方法有指定和不指定sheet數(shù)據(jù)頁(yè)大小的分批寫(xiě)入方法 easyExcelBatchExport() 和不分批次直接一次性寫(xiě)入的 easyExcelExport() 方法。
下面通過(guò)一個(gè)小案例展示一下。假設(shè)現(xiàn)在有個(gè)導(dǎo)出用戶列表的需求，數(shù)據(jù)庫(kù)User表對(duì)應(yīng)的是UserPO類(lèi)：
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class UserPO {
private Long id;
/**
* 用戶編號(hào)
*/
private String code;
/**
* 姓名
*/
private String name;
/**
* 手機(jī)號(hào)碼
*/
private String phone;
/**
* 性別。1-男，2-女
*/
private Integer sex;
}導(dǎo)出對(duì)應(yīng)的類(lèi)是UserExportEntity：
@Data
public class UserExportEntity {
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 0, value = "序號(hào)")
private Integer line;
@ColumnWidth(35)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 1, value = "用戶編號(hào)")
private String code;
@ColumnWidth(35)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 2, value = "姓名")
private String name;
@ColumnWidth(35)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 3, value = "手機(jī)號(hào)碼")
private String phone;
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 4, value = "性別")
private String sexStr;
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 5, value = "fieldA")
private String fieldA;
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 6, value = "fieldB")
private String fieldB;
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 7, value = "fieldC")
private String fieldC;
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 8, value = "fieldD")
private String fieldD;
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 9, value = "fieldE")
private String fieldE;
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 10, value = "fieldF")
private String fieldF;
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 11, value = "fieldG")
private String fieldG;
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 12, value = "fieldH")
private String fieldH;
@ColumnWidth(10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ExcelProperty(index = 13, value = "fieldI")
private String fieldI;
}先測(cè)試一下不分批次導(dǎo)出，導(dǎo)出123456條數(shù)據(jù)。
@GetMapping("/testExport")
public void testExport(HttpServletResponse response) {
new EasyExcelExport<UserExportEntity, UserPO>() {
@Override
public UserExportEntity convertSourceData2ExportEntity(UserPO sourceData, Integer lineNum) {
UserExportEntity entity = new UserExportEntity();
entity.setLine(lineNum);
entity.setCode(sourceData.getCode());
entity.setName(sourceData.getName());
entity.setPhone(sourceData.getPhone());
entity.setSexStr(Objects.equals(sourceData.getSex(), 1) ? "男" : Objects.equals(sourceData.getSex(), 2) ? "女" : StrUtil.EMPTY);
return entity;
}
@Override
public List<UserPO> getData(int currentBatch) {
List<UserPO> userPOList = new ArrayList<>();
// 模擬查詢數(shù)據(jù)庫(kù)，假設(shè)每次查詢會(huì)查出123456條數(shù)據(jù)
for (int i = 0; i < 123456; i++) {
userPOList.add(UserPO.builder()
.code("USER_" + RandomUtil.randomString("1234567890", 6))
.name(RandomUtil.randomString("qwertyuiopasdfghjklzxcvbnm", 10))
.phone("138" + RandomUtil.randomString("1234567890", 8))
.sex(RandomUtil.randomInt(1, 3))
.build());
}
log.info("userPOList-->{}", JSONUtil.toJsonStr(userPOList));
return userPOList;
}
}.easyExcelExport("測(cè)試不分批次導(dǎo)出", "測(cè)試不分批次導(dǎo)出", response);
}為了更清晰地看到效果，我將內(nèi)存大小限制為128M。
調(diào)用一下測(cè)試接口，可以看到，導(dǎo)出十幾萬(wàn)條數(shù)據(jù)時(shí)發(fā)生了OOM。
再來(lái)看看分批次導(dǎo)出的效果，模擬一下分頁(yè)查詢，假設(shè)有200頁(yè)數(shù)據(jù)，每頁(yè)8888條，一共是170多萬(wàn)條數(shù)據(jù)。
@GetMapping("/testBatchExport")
public void testBatchExport(HttpServletResponse response) {
new EasyExcelExport<UserExportEntity, UserPO>() {
@Override
public UserExportEntity convertSourceData2ExportEntity(UserPO sourceData, Integer lineNum) {
UserExportEntity entity = new UserExportEntity();
entity.setLine(lineNum);
entity.setCode(sourceData.getCode());
entity.setName(sourceData.getName());
entity.setPhone(sourceData.getPhone());
entity.setSexStr(Objects.equals(sourceData.getSex(), 1) ? "男" : Objects.equals(sourceData.getSex(), 2) ? "女" : StrUtil.EMPTY);
return entity;
}
@Override
public List<UserPO> getData(int currentBatch) {
// 模擬分頁(yè)查詢，假設(shè)數(shù)據(jù)庫(kù)中有200頁(yè)數(shù)據(jù)
if (currentBatch <= 200) {
List<UserPO> userPOList = new ArrayList<>();
// 模擬查詢數(shù)據(jù)庫(kù)，假設(shè)每次查詢會(huì)查出8888條數(shù)據(jù)
for (int i = 0; i < 8888; i++) {
userPOList.add(UserPO.builder()
.code("USER_" + RandomUtil.randomString("1234567890", 6))
.name(RandomUtil.randomString("qwertyuiopasdfghjklzxcvbnm", 10))
.phone("138" + RandomUtil.randomString("1234567890", 8))
.sex(RandomUtil.randomInt(1, 3))
.build());
}
return userPOList;
} else {
return new ArrayList<>();
}
}
}.easyExcelBatchExport("測(cè)試分批次導(dǎo)出", "測(cè)試分批次導(dǎo)出", response);
}通過(guò)分批次寫(xiě)入Excel的方式，成功導(dǎo)出了170多萬(wàn)條數(shù)據(jù)，相較于不分批次導(dǎo)出，效果顯而易見(jiàn)。而且通過(guò)調(diào)用工具類(lèi)的方式，進(jìn)一步簡(jiǎn)化了導(dǎo)出時(shí)代碼的編寫(xiě)。
