问题描述
偶发性sql超时
线上发生OOM
原因分析
先说结论: 经排查是由于线上导出excel报表相关接口导致.主要点
- 历史excel工具类采用一次性将所有数据加载方式,加载结果集量大,很多报表数据处理几万,导致内存占用高
- 月初,报表业务请求较多
- 历史债务,很多报表业务采用原来的excel工具类
以下是在本地模拟以及分析过程
-
本地模拟参数相关配置(方便复现)
image.png -
本地接口压测配置
image.png -
控制台可以看到很快出现OOM(模拟报表数据10749条)
image.png -
jconsole查看内存情况
可以看到发生161次full gc
image.png -
MAT分析堆快照文件
先看Leak Suspects(泄漏报告建议-这个非常有用)
image.png
下面2个类的实例各占内存26.18%和23.61%
image.png
查看线程对象的内存占比信息,可以看到OOM栈信息,以及能快速定位到项目中的对应位置
image.png
image.png
查看对象内存大小列表,可以看到内存大的TaskThread实例中存在2个list大对象
image.png
image.png
查看类柱状图,可以比较明显看到下面类的占比可能存在异常
image.png
解决方案
主要以下几点
- 临时减少最大记录数以及加大线上堆内存配置
- 编写了导出模板(核心代码在最后面)
- 针对历史的数据量大的导出业务逐步进行改造
效果
- 采用批次读写方式,大大减少了内存消耗以及基本解决由于导出导致的OOM的问题
- 导出导致的sql超时问题也得到解决(采用批次处理)
- 可支持百万记录级别报表
- 采用模板设计模式,接入后大致只需编写获取数据以及转换数据逻辑,大大提升开发效率
- 表头采用模板excel生成,无需像之前需要开发大量的表头格式代码
- 在多个部门都得到了落地使用
- 支持异步和同步
- 支持多种批次读写方式,如分页方式,do-while方式,从excel文件读取数据方式等
可以支持百万级别报表数据
image.png
image.png
同样的堆配置(64m),生成百万数据报表没有发生OOM(当然,由于堆内存配置过小,新生代的GC次数比较多,生产上不可能配那么小,另外,生产默认限制最大记录数20万)
image.png
image.png
导出模板核心代码
使用案例
/**
* 读后后再写
*/
@GetMapping("/testReadAllThenWrite")
public void testReadAllThenWrite(@RequestParam("startTime") String startTime, @RequestParam("endTime") String endTime) {
ExportExcelParam exportParam = new ExportExcelParam();
// 文件名,默认 exportData时间戳.xlsx
exportParam.setFileName("用户信息报表" + System.currentTimeMillis() + ".xlsx");
// 生成文件方式,默认PAGE_READ_AND_WRITE
exportParam.setCreateFileTypeEnum(CreateFileTypeEnum.READ_ALL_THEN_WRITE);
// 模板文件路径
exportParam.setTemplateFileName("excel/exceltemplate/zcjUserTemplate.xlsx");
// 文件处理方式
exportParam.setHandleFileEnum(HandleFileEnum.UPLOAD_FILE_CENTER);
// 发送邮件参数(handleFileEnum为SEND_MAIL时必填)
// exportParam.setMailSendParam(mailSendParam);
userExport.export(exportParam,
// 设置获取记录函数
() -> userService.getUserListRangeTime(startTime, endTime),
// 设置记录数据转换excel数据函数
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();
// 批次大小,默认200
exportParam.setBatchSize(200);
// 最大记录行数,默认1000000
exportParam.setMaxSize(1000000);
// 文件名,默认 exportData时间戳.xlsx
exportParam.setFileName("用户信息报表" + System.currentTimeMillis() + ".xlsx");
// 生成文件方式,默认PAGE_READ_AND_WRITE
exportParam.setCreateFileTypeEnum(CreateFileTypeEnum.PAGE_READ_AND_WRITE);
// 模板文件路径
exportParam.setTemplateFileName("excel/exceltemplate/zcjUserTemplate.xlsx");
// 文件处理方式
exportParam.setHandleFileEnum(HandleFileEnum.UPLOAD_FILE_CENTER);
// 发送邮件参数(handleFileEnum为SEND_MAIL时必填)
// exportParam.setMailSendParam(mailSendParam);
userExport.exportAsync(exportParam,
// 获取记录函数
() -> userService.getUserListRangeTime(startTime, endTime),
// 记录数据转换excel数据函数
p -> {
List<ZcjUserExcelModel> excelDataList = new ArrayList<>();
for (ZcjUser c : p) {
ZcjUserExcelModel zcjUserExcelModel = new ZcjUserExcelModel();
BeanUtils.copyProperties(c, zcjUserExcelModel);
excelDataList.add(zcjUserExcelModel);
}
return excelDataList;
});
// 同步导出
// 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");
// 异步导出
userExport.exportDoWhileAsync(exportParam,
// 获取记录函数
() -> userService.getUserListByIdRangeTime(userParam),
// 记录数据转换excel数据函数
p -> {
List<ZcjUserExcelModel> excelDataList = new ArrayList<>();
for (ZcjUser c : p) {
ZcjUserExcelModel zcjUserExcelModel = new ZcjUserExcelModel();
BeanUtils.copyProperties(c, zcjUserExcelModel);
excelDataList.add(zcjUserExcelModel);
}
return excelDataList;
},
// 更新查询数据参数函数
p -> {
ZcjUser lastRecord = p.get(p.size() - 1);
userParam.setId(lastRecord.getId());
});
}
/**
* 边读边写(分页读写)方式
*/
@GetMapping("/testExportByExcel")
public void testExportByExcel() {
ExportExcelParam exportParam = new ExportExcelParam();
// 批次大小,默认200
exportParam.setBatchSize(200);
// 最大记录行数,默认1000000
exportParam.setMaxSize(1000000);
// 从excel文件获取数据时指定的文件
exportParam.setSourceFileName("excel/excelsource/用户信息报表1664367466324.xlsx");
// 文件名,默认 exportData时间戳.xlsx
exportParam.setFileName("用户信息报表" + System.currentTimeMillis() + ".xlsx");
// 生成文件方式,默认PAGE_READ_AND_WRITE
exportParam.setCreateFileTypeEnum(CreateFileTypeEnum.READ_FROM_EXCEL);
// 模板文件路径
exportParam.setTemplateFileName("excel/exceltemplate/zcjUserWithProvinceTemplate.xlsx");
// 文件处理方式
exportParam.setHandleFileEnum(HandleFileEnum.BROWSER_DOWNLOAD);
// 发送邮件参数(handleFileEnum为SEND_MAIL时必填)
// exportParam.setMailSendParam(mailSendParam);
// 异步导出
userAddSomethingExport.exportReadFromExcelAsync(exportParam,
// 记录数据转换excel数据函数
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;
});
// 同步导出
// userAddSomethingExport.exportReadFromExcel(...)
}
@Data
public class ExportExcelParam {
/**
* 批次大小
*/
private int batchSize = 200;
/**
* 最大记录行数
*/
private int maxSize = 200000;
/**
* 生成文件方式
*/
private CreateFileTypeEnum createFileTypeEnum = CreateFileTypeEnum.PAGE_READ_AND_WRITE;
/**
* 指定模板文件路径时根据模板文件生成文件(相对路径,如exceltemplate/XXXTemplate.xlsx)
*/
private String templateFileName;
/**
* 从excel文件获取数据时指定的文件(resource相对路径,如excel/excelsource/用户信息报表1664367466324.xlsx)
*/
private String sourceFileName;
/**
* 生成excel文件的文件名
*/
private String fileName;
/**
* 文件处理方式:SEND_MAIL-发送邮件,BROWSER_DOWNLOAD-浏览器直接下载,UPLOAD_FILE_CENTER-上送下载中心
*/
private HandleFileEnum handleFileEnum = HandleFileEnum.SEND_MAIL;
/**
* 发送邮件参数(handleFileEnum为SEND_MAIL时必填)
*/
private MailSendParam mailSendParam;
public String getFileName() {
if (StringUtils.isBlank(fileName)) {
return "exportData" + System.currentTimeMillis() + ".xlsx";
}
return fileName;
}
}
/**
* 导出模板类
*/
@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<>();
/**
* 读后再写获取记录函数
*/
private Supplier<List<RecordData>> listSupplier;
/**
* 数据转换excel数据函数
*/
private Function<List<RecordData>, List<ExcelData>> transFunction;
/**
* 如: do-while方式更新listSupplier参数的函数
*/
private Consumer<List<RecordData>> recordDataConsumer;
/**
* 异步方式导出
* 注意: 文件处理方式为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类型需使用同步导出,或者使用UPLOAD_FILE_CENTER方式实现异步浏览器下载, param->{}", JSON.toJSONString(param));
export(param, listSupplier, transFunction);
} else {
ThreadPoolConfig.exportExecutor.execute(() -> export(param, listSupplier, transFunction));
}
}
/**
* DO_WHILE_READ_AND_WRITE(需传recordDataConsumer)-异步方式导出
* 注意: 文件处理方式为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类型需使用同步导出,或者使用UPLOAD_FILE_CENTER方式实现异步浏览器下载, param->{}", JSON.toJSONString(param));
exportDoWhile(param, listSupplier, transFunction, recordDataConsumer);
} else {
ThreadPoolConfig.exportExecutor.execute(() -> exportDoWhile(param, listSupplier, transFunction, recordDataConsumer));
}
}
/**
* READ_FROM_EXCEL-异步方式导出
* 注意: 文件处理方式为BROWSER_DOWNLOAD依然采用同步方式
*/
public final void exportReadFromExcelAsync(Param param, Function<List<RecordData>, List<ExcelData>> transFunction) {
if (param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD) {
log.warn("BROWSER_DOWNLOAD类型需使用同步导出,或者使用UPLOAD_FILE_CENTER方式实现异步浏览器下载, param->{}", JSON.toJSONString(param));
exportReadFromExcel(param, transFunction);
} else {
ThreadPoolConfig.exportExecutor.execute(() -> exportReadFromExcel(param, transFunction));
}
}
/**
* 同步方式导出
*/
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);
}
/**
* 同步方式导出(如READ_FROM_EXCEL方式调用)
*/
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) {
// 初始化参数（文件名，邮箱参数等）
initParam(param);
// 校验参数
if (!validateParam(param)) {
log.error("export error 参数不符合,param->{}", param);
return;
}
// 如: 处理方式为上传至下载中心,生成文件记录
createFileBefore(param);
File file = null;
try {
// 生成临时文件
file = createFile(param, servletResponse);
// 处理文件(发送邮件,上传至下载中心等)
if (validateFile(param, file)) {
handleFile(param, file);
}
log.info("文件导出结束,param->{}", JSON.toJSONString(param));
} finally {
// 删除文件
// FileUtil.del(file);
}
}
/**
* 同步方式导出(需传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 参数不符合,param is null");
return false;
}
if (param.getCreateFileTypeEnum() != CreateFileTypeEnum.READ_FROM_EXCEL && listSupplier == null) {
log.error("export error 参数不符合,listSupplier is null");
return false;
}
if (transFunction == null) {
log.error("export error 参数不符合,transFunction is null");
return false;
}
if (param.getCreateFileTypeEnum() == CreateFileTypeEnum.DO_WHILE_READ_AND_WRITE && recordDataConsumer == null) {
log.error("export error 参数不符合,do-while方式时,recordDataConsumer函数不能为null");
return false;
}
if (param.getCreateFileTypeEnum() == CreateFileTypeEnum.READ_FROM_EXCEL && StringUtils.isBlank(param.getSourceFileName())) {
log.error("export error 参数不符合,数据从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参数不符合");
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) {
// 不存在则创建对应目录
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);
}
// 转换成excel数据实例并写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("导出过程异常,param->{},异常:", JSON.toJSONString(param), e);
return null;
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
// 不能先于excelWriter关闭,因此不能用try-with-resource方式
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("导出过程异常,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;
// 基于当前列表结果调用consumer函数
recordDataConsumer.accept(recordDataList);
// 转换成excel数据实例并写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方式生成数据结束,totalSize->{},param->{}", totalSize, JSON.toJSONString(param));
return param.getHandleFileEnum() == HandleFileEnum.BROWSER_DOWNLOAD ? null : new File(tempFilePath + fileName);
} catch (Exception e) {
log.error("导出过程异常,param->{},异常:", JSON.toJSONString(param), e);
return null;
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
// 不能先于excelWriter关闭,因此不能用try-with-resource方式
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("导出过程异常,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("导出过程异常,param->{},异常:", JSON.toJSONString(param), e);
return null;
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
// 不能先于excelWriter关闭,因此不能用try-with-resource方式
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("导出过程异常,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("批次->{},批次记录数->{},已处理总记录数->{}", batch, recordDataList.size(), totalSize);
// 转换数据
excelDataList.addAll(transFunction.apply(recordDataList));
// 写数据
finalExcelWriter.write(excelDataList, writeSheet);
totalSize += excelDataList.size();
recordDataList.clear();
excelDataList.clear();
}
}).build();
// 构建一个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("导出过程异常,param->{},异常:", JSON.toJSONString(param), e);
return null;
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
}
if (excelWriter != null) {
excelWriter.finish();
}
// 不能先于excelWriter关闭,因此不能用try-with-resource方式
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("导出过程异常,异常:", 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("导出过程异常,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) {
// 发送邮件
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,
/**
* 数据从excel文件获取
*/
READ_FROM_EXCEL;
}
@Getter
public enum HandleFileEnum {
/**
* 发送邮件
*/
SEND_MAIL,
/**
* 浏览器直接下载
*/
BROWSER_DOWNLOAD,
/**
* 上送下载中心
*/
UPLOAD_FILE_CENTER
}
