問題描述
偶發(fā)性sql超時
線上發(fā)生OOM
原因分析
先說結(jié)論: 經(jīng)排查是由于線上導(dǎo)出excel報表相關(guān)接口導(dǎo)致.主要點
- 歷史excel工具類采用一次性將所有數(shù)據(jù)加載方式,加載結(jié)果集量大,很多報表數(shù)據(jù)處理幾萬,導(dǎo)致內(nèi)存占用高
- 月初,報表業(yè)務(wù)請求較多
- 歷史債務(wù),很多報表業(yè)務(wù)采用原來的excel工具類
以下是在本地模擬以及分析過程
-
本地模擬參數(shù)相關(guān)配置(方便復(fù)現(xiàn))
image.png -
本地接口壓測配置
image.png -
控制臺可以看到很快出現(xiàn)OOM(模擬報表數(shù)據(jù)10749條)
image.png -
jconsole查看內(nèi)存情況
可以看到發(fā)生161次full gc
image.png -
MAT分析堆快照文件
先看Leak Suspects(泄漏報告建議-這個非常有用)
image.png
下面2個類的實例各占內(nèi)存26.18%和23.61%
image.png
查看線程對象的內(nèi)存占比信息,可以看到OOM棧信息,以及能快速定位到項目中的對應(yīng)位置
image.png
image.png
查看對象內(nèi)存大小列表,可以看到內(nèi)存大的TaskThread實例中存在2個list大對象
image.png
image.png
查看類柱狀圖,可以比較明顯看到下面類的占比可能存在異常
image.png
解決方案
主要以下幾點
- 臨時減少最大記錄數(shù)以及加大線上堆內(nèi)存配置
- 編寫了導(dǎo)出模板(核心代碼在最后面)
- 針對歷史的數(shù)據(jù)量大的導(dǎo)出業(yè)務(wù)逐步進(jìn)行改造
效果
- 采用批次讀寫方式,大大減少了內(nèi)存消耗以及基本解決由于導(dǎo)出導(dǎo)致的OOM的問題
- 導(dǎo)出導(dǎo)致的sql超時問題也得到解決(采用批次處理)
- 可支持百萬記錄級別報表
- 采用模板設(shè)計模式,接入后大致只需編寫獲取數(shù)據(jù)以及轉(zhuǎn)換數(shù)據(jù)邏輯,大大提升開發(fā)效率
- 表頭采用模板excel生成,無需像之前需要開發(fā)大量的表頭格式代碼
- 在多個部門都得到了落地使用
- 支持異步和同步
- 支持多種批次讀寫方式,如分頁方式,do-while方式,從excel文件讀取數(shù)據(jù)方式等
可以支持百萬級別報表數(shù)據(jù)
image.png
image.png
同樣的堆配置(64m),生成百萬數(shù)據(jù)報表沒有發(fā)生OOM(當(dāng)然,由于堆內(nèi)存配置過小,新生代的GC次數(shù)比較多,生產(chǎn)上不可能配那么小,另外,生產(chǎn)默認(rèn)限制最大記錄數(shù)20萬)
image.png
image.png
導(dǎo)出模板核心代碼
使用案例
/**
* 讀后后再寫
*/
@GetMapping("/testReadAllThenWrite")
public void testReadAllThenWrite(@RequestParam("startTime") String startTime, @RequestParam("endTime") String endTime) {
ExportExcelParam exportParam = new ExportExcelParam();
// 文件名,默認(rèn) exportData時間戳.xlsx
exportParam.setFileName("用戶信息報表" + System.currentTimeMillis() + ".xlsx");
// 生成文件方式,默認(rèn)PAGE_READ_AND_WRITE
exportParam.setCreateFileTypeEnum(CreateFileTypeEnum.READ_ALL_THEN_WRITE);
// 模板文件路徑
exportParam.setTemplateFileName("excel/exceltemplate/zcjUserTemplate.xlsx");
// 文件處理方式
exportParam.setHandleFileEnum(HandleFileEnum.UPLOAD_FILE_CENTER);
// 發(fā)送郵件參數(shù)(handleFileEnum為SEND_MAIL時必填)
// exportParam.setMailSendParam(mailSendParam);
userExport.export(exportParam,
// 設(shè)置獲取記錄函數(shù)
() -> userService.getUserListRangeTime(startTime, endTime),
// 設(shè)置記錄數(shù)據(jù)轉(zhuǎn)換excel數(shù)據(jù)函數(shù)
p -> {
List<ZcjUserExcelModel> excelDataList = new ArrayList<>();
for (ZcjUser c : p) {
ZcjUserExcelModel zcjUserExcelModel = new ZcjUserExcelModel();
BeanUtils.copyProperties(c, zcjUserExcelModel);
excelDataList.add(zcjUserExcelModel);
}
return excelDataList;
});
}
/**
* 邊讀邊寫(分頁讀寫)方式
*/
@GetMapping("/testPageReadAndWrite")
public void testPageReadAndWrite(@RequestParam("startTime") String startTime, @RequestParam("endTime") String endTime) {
ExportExcelParam exportParam = new ExportExcelParam();
// 批次大小,默認(rèn)200
exportParam.setBatchSize(200);
// 最大記錄行數(shù),默認(rèn)1000000
exportParam.setMaxSize(1000000);
// 文件名,默認(rèn) exportData時間戳.xlsx
exportParam.setFileName("用戶信息報表" + System.currentTimeMillis() + ".xlsx");
// 生成文件方式,默認(rèn)PAGE_READ_AND_WRITE
exportParam.setCreateFileTypeEnum(CreateFileTypeEnum.PAGE_READ_AND_WRITE);
// 模板文件路徑
exportParam.setTemplateFileName("excel/exceltemplate/zcjUserTemplate.xlsx");
// 文件處理方式
exportParam.setHandleFileEnum(HandleFileEnum.UPLOAD_FILE_CENTER);
// 發(fā)送郵件參數(shù)(handleFileEnum為SEND_MAIL時必填)
// exportParam.setMailSendParam(mailSendParam);
userExport.exportAsync(exportParam,
// 獲取記錄函數(shù)
() -> userService.getUserListRangeTime(startTime, endTime),
// 記錄數(shù)據(jù)轉(zhuǎn)換excel數(shù)據(jù)函數(shù)
p -> {
List<ZcjUserExcelModel> excelDataList = new ArrayList<>();
for (ZcjUser c : p) {
ZcjUserExcelModel zcjUserExcelModel = new ZcjUserExcelModel();
BeanUtils.copyProperties(c, zcjUserExcelModel);
excelDataList.add(zcjUserExcelModel);
}
return excelDataList;
});
// 同步導(dǎo)出
// userExport.export(...)
}
/**
* 邊讀邊寫(do-while方式讀)
*/
@GetMapping("/testDoWhileReadAndWrite")
public void testDoWhileReadAndWrite() {
ExportExcelParam exportParam = new ExportExcelParam();
exportParam.setCreateFileTypeEnum(CreateFileTypeEnum.DO_WHILE_READ_AND_WRITE);
exportParam.setTemplateFileName("excel/exceltemplate/zcjUserTemplate.xlsx");
exportParam.setHandleFileEnum(HandleFileEnum.BROWSER_DOWNLOAD);
UserParam userParam = new UserParam(0L, exportParam.getBatchSize(), "2022-09-26 20:00:00", "2022-09-26 20:55:34");
// 異步導(dǎo)出
userExport.exportDoWhileAsync(exportParam,
// 獲取記錄函數(shù)
() -> userService.getUserListByIdRangeTime(userParam),
// 記錄數(shù)據(jù)轉(zhuǎn)換excel數(shù)據(jù)函數(shù)
p -> {
List<ZcjUserExcelModel> excelDataList = new ArrayList<>();
for (ZcjUser c : p) {
ZcjUserExcelModel zcjUserExcelModel = new ZcjUserExcelModel();
BeanUtils.copyProperties(c, zcjUserExcelModel);
excelDataList.add(zcjUserExcelModel);
}
return excelDataList;
},
// 更新查詢數(shù)據(jù)參數(shù)函數(shù)
p -> {
ZcjUser lastRecord = p.get(p.size() - 1);
userParam.setId(lastRecord.getId());
});
}
/**
* 邊讀邊寫(分頁讀寫)方式
*/
@GetMapping("/testExportByExcel")
public void testExportByExcel() {
ExportExcelParam exportParam = new ExportExcelParam();
// 批次大小,默認(rèn)200
exportParam.setBatchSize(200);
// 最大記錄行數(shù),默認(rèn)1000000
exportParam.setMaxSize(1000000);
// 從excel文件獲取數(shù)據(jù)時指定的文件
exportParam.setSourceFileName("excel/excelsource/用戶信息報表1664367466324.xlsx");
// 文件名,默認(rèn) exportData時間戳.xlsx
exportParam.setFileName("用戶信息報表" + System.currentTimeMillis() + ".xlsx");
// 生成文件方式,默認(rèn)PAGE_READ_AND_WRITE
exportParam.setCreateFileTypeEnum(CreateFileTypeEnum.READ_FROM_EXCEL);
// 模板文件路徑
exportParam.setTemplateFileName("excel/exceltemplate/zcjUserWithProvinceTemplate.xlsx");
// 文件處理方式
exportParam.setHandleFileEnum(HandleFileEnum.BROWSER_DOWNLOAD);
// 發(fā)送郵件參數(shù)(handleFileEnum為SEND_MAIL時必填)
// exportParam.setMailSendParam(mailSendParam);
// 異步導(dǎo)出
userAddSomethingExport.exportReadFromExcelAsync(exportParam,
// 記錄數(shù)據(jù)轉(zhuǎn)換excel數(shù)據(jù)函數(shù)
p -> {
List<ZcjUserExcelV2Model> excelDataList = new ArrayList<>();
for (ZcjUserExcelModel c : p) {
ZcjUserExcelV2Model zcjUserExcelV2Model = new ZcjUserExcelV2Model();
BeanUtils.copyProperties(c, zcjUserExcelV2Model);
zcjUserExcelV2Model.setProvince("廣東省");
excelDataList.add(zcjUserExcelV2Model);
}
return excelDataList;
});
// 同步導(dǎo)出
// userAddSomethingExport.exportReadFromExcel(...)
}
@Data
public class ExportExcelParam {
/**
* 批次大小
*/
private int batchSize = 200;
/**
* 最大記錄行數(shù)
*/
private int maxSize = 200000;
/**
* 生成文件方式
*/
private CreateFileTypeEnum createFileTypeEnum = CreateFileTypeEnum.PAGE_READ_AND_WRITE;
/**
* 指定模板文件路徑時根據(jù)模板文件生成文件(相對路徑,如exceltemplate/XXXTemplate.xlsx)
*/
private String templateFileName;
/**
* 從excel文件獲取數(shù)據(jù)時指定的文件(resource相對路徑,如excel/excelsource/用戶信息報表1664367466324.xlsx)
*/
private String sourceFileName;
/**
* 生成excel文件的文件名
*/
private String fileName;
/**
* 文件處理方式:SEND_MAIL-發(fā)送郵件,BROWSER_DOWNLOAD-瀏覽器直接下載,UPLOAD_FILE_CENTER-上送下載中心
*/
private HandleFileEnum handleFileEnum = HandleFileEnum.SEND_MAIL;
/**
* 發(fā)送郵件參數(shù)(handleFileEnum為SEND_MAIL時必填)
*/
private MailSendParam mailSendParam;
public String getFileName() {
if (StringUtils.isBlank(fileName)) {
return "exportData" + System.currentTimeMillis() + ".xlsx";
}
return fileName;
}
}
/**
* 導(dǎo)出模板類
*/
@Slf4j
public abstract class AbstractExcelExport<Param extends ExportExcelParam, RecordData, ExcelData> {
/**
* 臨時文件路徑(絕對路徑)
*/
@Value("${export.tempFilePath}")
private String tempFilePath;
@Autowired
private ExportCenterService exportCenterService;
@Autowired
private MailService mailService;
public static ThreadLocal<ExportContextData> threadLocal = new ThreadLocal<>();
/**
* 讀后再寫獲取記錄函數(shù)
*/
private Supplier<List<RecordData>> listSupplier;
/**
* 數(shù)據(jù)轉(zhuǎn)換excel數(shù)據(jù)函數(shù)
*/
private Function<List<RecordData>, List<ExcelData>> transFunction;
/**
* 如: do-while方式更新listSupplier參數(shù)的函數(shù)
*/
private Consumer<List<RecordData>> recordDataConsumer;
/**
* 異步方式導(dǎo)出
* 注意: 文件處理方式為BROWSER_DOWNLOAD依然采用同步方式
*/
public final void exportAsync(Param param, Supplier<List<RecordData>> listSupplier, Function<List<RecordData>, List<ExcelData>> transFunction) {
if (param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD) {
log.warn("BROWSER_DOWNLOAD類型需使用同步導(dǎo)出,或者使用UPLOAD_FILE_CENTER方式實現(xiàn)異步瀏覽器下載, param->{}", JSON.toJSONString(param));
export(param, listSupplier, transFunction);
} else {
ThreadPoolConfig.exportExecutor.execute(() -> export(param, listSupplier, transFunction));
}
}
/**
* DO_WHILE_READ_AND_WRITE(需傳recordDataConsumer)-異步方式導(dǎo)出
* 注意: 文件處理方式為BROWSER_DOWNLOAD依然采用同步方式
*/
public final void exportDoWhileAsync(Param param, Supplier<List<RecordData>> listSupplier, Function<List<RecordData>, List<ExcelData>> transFunction, Consumer<List<RecordData>> recordDataConsumer) {
if (param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD) {
log.warn("BROWSER_DOWNLOAD類型需使用同步導(dǎo)出,或者使用UPLOAD_FILE_CENTER方式實現(xiàn)異步瀏覽器下載, param->{}", JSON.toJSONString(param));
exportDoWhile(param, listSupplier, transFunction, recordDataConsumer);
} else {
ThreadPoolConfig.exportExecutor.execute(() -> exportDoWhile(param, listSupplier, transFunction, recordDataConsumer));
}
}
/**
* READ_FROM_EXCEL-異步方式導(dǎo)出
* 注意: 文件處理方式為BROWSER_DOWNLOAD依然采用同步方式
*/
public final void exportReadFromExcelAsync(Param param, Function<List<RecordData>, List<ExcelData>> transFunction) {
if (param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD) {
log.warn("BROWSER_DOWNLOAD類型需使用同步導(dǎo)出,或者使用UPLOAD_FILE_CENTER方式實現(xiàn)異步瀏覽器下載, param->{}", JSON.toJSONString(param));
exportReadFromExcel(param, transFunction);
} else {
ThreadPoolConfig.exportExecutor.execute(() -> exportReadFromExcel(param, transFunction));
}
}
/**
* 同步方式導(dǎo)出
*/
public final void export(Param param, Supplier<List<RecordData>> listSupplier, Function<List<RecordData>, List<ExcelData>> transFunction, HttpServletResponse... servletResponse) {
this.listSupplier = listSupplier;
this.transFunction = transFunction;
doExport(param, servletResponse);
}
/**
* 同步方式導(dǎo)出(如READ_FROM_EXCEL方式調(diào)用)
*/
public final void exportReadFromExcel(Param param, Function<List<RecordData>, List<ExcelData>> transFunction, HttpServletResponse... servletResponse) {
this.transFunction = transFunction;
doExport(param, servletResponse);
}
private void doExport(Param param, HttpServletResponse[] servletResponse) {
// 初始化參數(shù)（文件名靠胜，郵箱參數(shù)等）
initParam(param);
// 校驗參數(shù)
if (!validateParam(param)) {
log.error("export error 參數(shù)不符合,param->{}", param);
return;
}
// 如: 處理方式為上傳至下載中心,生成文件記錄
createFileBefore(param);
File file = null;
try {
// 生成臨時文件
file = createFile(param, servletResponse);
// 處理文件(發(fā)送郵件,上傳至下載中心等)
if (validateFile(param, file)) {
handleFile(param, file);
}
log.info("文件導(dǎo)出結(jié)束,param->{}", JSON.toJSONString(param));
} finally {
// 刪除文件
// FileUtil.del(file);
}
}
/**
* 同步方式導(dǎo)出(需傳recordDataConsumer)
*/
public final void exportDoWhile(Param param, Supplier<List<RecordData>> listSupplier, Function<List<RecordData>, List<ExcelData>> transFunction, Consumer<List<RecordData>> recordDataConsumer, HttpServletResponse... servletResponse) {
this.recordDataConsumer = recordDataConsumer;
export(param, listSupplier, transFunction, servletResponse);
}
protected void initParam(Param param) {
}
public boolean validateParam(Param param) {
if (param == null) {
log.error("export error 參數(shù)不符合,param is null");
return false;
}
if (param.getCreateFileTypeEnum() != CreateFileTypeEnum.READ_FROM_EXCEL && listSupplier == null) {
log.error("export error 參數(shù)不符合,listSupplier is null");
return false;
}
if (transFunction == null) {
log.error("export error 參數(shù)不符合,transFunction is null");
return false;
}
if (param.getCreateFileTypeEnum() == CreateFileTypeEnum.DO_WHILE_READ_AND_WRITE && recordDataConsumer == null) {
log.error("export error 參數(shù)不符合,do-while方式時,recordDataConsumer函數(shù)不能為null");
return false;
}
if (param.getCreateFileTypeEnum() == CreateFileTypeEnum.READ_FROM_EXCEL && StringUtils.isBlank(param.getSourceFileName())) {
log.error("export error 參數(shù)不符合,數(shù)據(jù)從excel文件獲取方式時,sourceFileName不能為null");
}
HandleFileEnum handleFileEnum = param.getHandleFileEnum();
if (handleFileEnum == HandleFileEnum.SEND_MAIL) {
MailSendParam mailSendParam = param.getMailSendParam();
if (mailSendParam == null
|| StringUtils.isBlank(mailSendParam.getToAddress())
|| StringUtils.isBlank(mailSendParam.getSubject())) {
log.error("export error mailSendParam參數(shù)不符合");
return false;
}
}
return true;
}
/**
* 生成文件前處理邏輯
* 如: 處理方式為上傳至下載中心,生成文件記錄
*/
protected void createFileBefore(Param param) {
// 處理方式為上傳至下載中心,先生成文件記錄
if (param.getHandleFileEnum() == HandleFileEnum.UPLOAD_FILE_CENTER) {
Integer recordId = exportCenterService.createRecord(param);
ExportContextData exportContextData = new ExportContextData(recordId);
threadLocal.set(exportContextData);
}
}
private File createFile(Param param, HttpServletResponse[] servletResponse) {
// 不存在則創(chuàng)建對應(yīng)目錄
FileUtil.mkdir(tempFilePath);
switch (param.getCreateFileTypeEnum()) {
case READ_ALL_THEN_WRITE:
return createFileAfterRead(param, servletResponse);
case DO_WHILE_READ_AND_WRITE:
return createFileDoWhileRead(param, servletResponse);
case READ_FROM_EXCEL:
return createFileByExcel(param, servletResponse);
case PAGE_READ_AND_WRITE:
default:
return createFilePageRead(param, servletResponse);
}
}
/**
* 邊讀邊寫
*/
private File createFilePageRead(Param param, HttpServletResponse[] servletResponse) {
log.info("createFilePageRead start,param->{}", JSON.toJSONString(param));
ExcelWriter excelWriter = null;
String fileName = param.getFileName();
OutputStream outputStream = getOutPutStream(param, servletResponse, fileName);
try {
if (StringUtils.isNoneBlank(param.getTemplateFileName())) {
excelWriter = EasyExcel.write(outputStream)
.needHead(false).withTemplate(ResourceUtil.getStream(param.getTemplateFileName())).build();
} else {
excelWriter = EasyExcel.write(outputStream)
.needHead(true).head(getTypeClass(2)).build();
}
WriteSheet writeSheet = EasyExcel.writerSheet().build();
PageParam request = new PageParam();
request.setPageSize(param.getBatchSize());
// 分批寫入
int pageNo = 1;
while ((pageNo - 1) * param.getBatchSize() < param.getMaxSize()) {
request.setPageNo(pageNo);
PageInfo<RecordData> pageResult = PageHelper.startPage(request.getPageNo(), request.getPageSize()).doSelectPageInfo(() -> listSupplier.get());
log.info("export end batch->{}, totalCount->{},request->{}", pageNo, pageResult.getTotal(), JSON.toJSONString(request));
if (CollectionUtils.isEmpty(pageResult.getList())) {
return param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD ? null : new File(tempFilePath + fileName);
}
// 轉(zhuǎn)換成excel數(shù)據(jù)實例并寫excel
List<ExcelData> excelDataList = transFunction.apply(pageResult.getList());
excelWriter.write(excelDataList, writeSheet);
if (pageResult.getList().size() < request.getPageSize()) {
break;
}
pageNo++;
}
return param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD ? null : new File(tempFilePath + fileName);
} catch (Exception e) {
log.error("導(dǎo)出過程異常,param->{},異常:", JSON.toJSONString(param), e);
return null;
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
// 不能先于excelWriter關(guān)閉,因此不能用try-with-resource方式
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("導(dǎo)出過程異常,param->{},異常:", JSON.toJSONString(param), e);
}
}
}
}
/**
* 邊讀邊寫(do-while方式讀)
*/
private File createFileDoWhileRead(Param param, HttpServletResponse[] servletResponse) {
ExcelWriter excelWriter = null;
String fileName = param.getFileName();
OutputStream outputStream = getOutPutStream(param, servletResponse, fileName);
try {
if (StringUtils.isNoneBlank(param.getTemplateFileName())) {
excelWriter = EasyExcel.write(outputStream)
.needHead(false).withTemplate(ResourceUtil.getStream(param.getTemplateFileName())).build();
} else {
excelWriter = EasyExcel.write(outputStream)
.needHead(true).head(getTypeClass(2)).build();
}
WriteSheet writeSheet = EasyExcel.writerSheet().build();
int totalSize = 0;
List<RecordData> recordDataList = null;
do {
recordDataList = listSupplier.get();
if (CollectionUtils.isEmpty(recordDataList)) {
return param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD ? null : new File(tempFilePath + fileName);
}
int recordSize = recordDataList.size();
totalSize += recordSize;
// 基于當(dāng)前列表結(jié)果調(diào)用consumer函數(shù)
recordDataConsumer.accept(recordDataList);
// 轉(zhuǎn)換成excel數(shù)據(jù)實例并寫excel
List<ExcelData> excelDataList = transFunction.apply(recordDataList);
excelWriter.write(excelDataList, writeSheet);
} while (totalSize < param.getMaxSize() && CollectionUtils.isNotEmpty(recordDataList) && recordDataList.size() >= param.getBatchSize());
log.info("do-while方式生成數(shù)據(jù)結(jié)束,totalSize->{},param->{}", totalSize, JSON.toJSONString(param));
return param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD ? null : new File(tempFilePath + fileName);
} catch (Exception e) {
log.error("導(dǎo)出過程異常,param->{},異常:", JSON.toJSONString(param), e);
return null;
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
// 不能先于excelWriter關(guān)閉,因此不能用try-with-resource方式
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("導(dǎo)出過程異常,param->{},異常:", JSON.toJSONString(param), e);
}
}
}
}
/**
* 讀后再寫
*/
private File createFileAfterRead(Param param, HttpServletResponse[] servletResponse) {
ExcelWriter excelWriter = null;
String fileName = param.getFileName();
OutputStream outputStream = getOutPutStream(param, servletResponse, fileName);
try {
if (StringUtils.isNoneBlank(param.getTemplateFileName())) {
excelWriter = EasyExcel.write(outputStream)
.needHead(false).withTemplate(ResourceUtil.getStream(param.getTemplateFileName())).build();
} else {
excelWriter = EasyExcel.write(outputStream)
.needHead(true).head(getTypeClass(2)).build();
}
WriteSheet writeSheet = EasyExcel.writerSheet().build();
List<RecordData> recordDataList = listSupplier.get();
List<ExcelData> excelDataList = transFunction.apply(recordDataList);
excelWriter.write(excelDataList, writeSheet);
return param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD ? null : new File(tempFilePath + fileName);
} catch (Exception e) {
log.error("導(dǎo)出過程異常,param->{},異常:", JSON.toJSONString(param), e);
return null;
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
// 不能先于excelWriter關(guān)閉,因此不能用try-with-resource方式
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("導(dǎo)出過程異常,param->{},異常:", JSON.toJSONString(param), e);
}
}
}
}
protected File createFileByExcel(Param param, HttpServletResponse[] servletResponse) {
log.info("createFileByExcel start,param->{}", JSON.toJSONString(param));
ExcelReader excelReader = null;
ExcelWriter excelWriter = null;
String fileName = param.getFileName();
OutputStream outputStream = getOutPutStream(param, servletResponse, fileName);
List<ExcelData> excelDataList = new ArrayList<>(param.getBatchSize());
try {
if (StringUtils.isNoneBlank(param.getTemplateFileName())) {
excelWriter = EasyExcel.write(outputStream)
.needHead(false).withTemplate(ResourceUtil.getStream(param.getTemplateFileName())).build();
} else {
excelWriter = EasyExcel.write(outputStream)
.needHead(true).head(getTypeClass(2)).build();
}
WriteSheet writeSheet = EasyExcel.writerSheet().build();
ExcelWriter finalExcelWriter = excelWriter;
excelReader = EasyExcelFactory.read(ResourceUtil.getStream(param.getSourceFileName()), getTypeClass(1), new AnalysisEventListener<RecordData>() {
int batch = 0;
int totalSize = 0;
List<RecordData> recordDataList = new ArrayList<>(param.getBatchSize());
@Override
public void invoke(RecordData recordData, AnalysisContext context) {
if (totalSize >= param.getMaxSize()) {
return;
}
recordDataList.add(recordData);
if (recordDataList.size() >= param.getBatchSize()) {
writeExcelRecordBatch();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
writeExcelRecordBatch();
}
private void writeExcelRecordBatch() {
if (CollectionUtils.isEmpty(recordDataList) || totalSize >= param.getMaxSize()) {
return;
}
batch++;
log.info("批次->{},批次記錄數(shù)->{},已處理總記錄數(shù)->{}", batch, recordDataList.size(), totalSize);
// 轉(zhuǎn)換數(shù)據(jù)
excelDataList.addAll(transFunction.apply(recordDataList));
// 寫數(shù)據(jù)
finalExcelWriter.write(excelDataList, writeSheet);
totalSize += excelDataList.size();
recordDataList.clear();
excelDataList.clear();
}
}).build();
// 構(gòu)建一個sheet 這里可以指定名字或者no
ReadSheet readSheet = EasyExcel.readSheet(0).build();
// 讀取一個sheet
excelReader.read(readSheet);
return param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD ? null : new File(tempFilePath + fileName);
} catch (Exception e) {
log.error("導(dǎo)出過程異常,param->{},異常:", JSON.toJSONString(param), e);
return null;
} finally {
if (excelReader != null) {
// 這里千萬別忘記關(guān)閉涡尘，讀的時候會創(chuàng)建臨時文件灶芝，到時磁盤會崩的
excelReader.finish();
}
if (excelWriter != null) {
excelWriter.finish();
}
// 不能先于excelWriter關(guān)閉,因此不能用try-with-resource方式
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("導(dǎo)出過程異常,異常:", e);
}
}
}
}
protected OutputStream getOutPutStream(Param param, HttpServletResponse[] servletResponse, String fileName) {
try {
if (param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD) { // 瀏覽器下載
HttpServletResponse response;
if (servletResponse != null && servletResponse.length > 0) {
response = servletResponse[0];
} else {
ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
response = servletRequestAttributes.getResponse();
}
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
// URLEncoder.encode防止中文亂碼
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-Disposition", "attachment; filename= " + fileName);
return response.getOutputStream();
} else {
return new FileOutputStream(tempFilePath + param.getFileName());
}
} catch (Exception e) {
log.error("導(dǎo)出過程異常,param->{},異常:", JSON.toJSONString(param), e);
return null;
}
}
/**
* 校驗文件
*/
protected boolean validateFile(Param param, File file) {
HandleFileEnum handleFileEnum = param.getHandleFileEnum();
if (handleFileEnum != HandleFileEnum.BROWSER_DOWNLOAD && (file == null || !file.exists())) {
log.error("未生成文件或者文件不存在");
return false;
}
return true;
}
/**
* 處理文件
*/
protected void handleFile(Param param, File file) {
HandleFileEnum handleFileEnum = param.getHandleFileEnum();
if (handleFileEnum != HandleFileEnum.BROWSER_DOWNLOAD && (file == null || !file.exists())) {
log.error("未生成文件或者文件不存在");
return;
}
if (handleFileEnum == HandleFileEnum.UPLOAD_FILE_CENTER) {
// 上傳文件并更新下載中心記錄
Integer recordId = threadLocal.get().getRecordId();
String url = exportCenterService.uploadFile(file);
exportCenterService.updateRecordState(recordId, StringUtils.isNoneBlank(url) ? "SUCCESS" : "FAIL");
} else if (handleFileEnum == HandleFileEnum.SEND_MAIL) {
// 發(fā)送郵件
mailService.sendAsync(param.getMailSendParam(), file);
}
}
private Class getTypeClass(int index) {
Class clazz = getClass();
Type t = clazz.getGenericSuperclass();
if (t instanceof ParameterizedType) {
Type[] args = ((ParameterizedType) t).getActualTypeArguments();
if (args[index] instanceof Class) {
return (Class) args[index];
}
}
return null;
}
}
@Getter
public enum CreateFileTypeEnum {
/**
* 讀后后再寫
*/
READ_ALL_THEN_WRITE,
/**
* 邊讀邊寫(分頁讀寫)
*/
PAGE_READ_AND_WRITE,
/**
* 邊讀邊寫(do-while方式讀)
*/
DO_WHILE_READ_AND_WRITE,
/**
* 數(shù)據(jù)從excel文件獲取
*/
READ_FROM_EXCEL;
}
@Getter
public enum HandleFileEnum {
/**
* 發(fā)送郵件
*/
SEND_MAIL,
/**
* 瀏覽器直接下載
*/
BROWSER_DOWNLOAD,
/**
* 上送下載中心
*/
UPLOAD_FILE_CENTER
}
