大数据量的Excel导入和导出
涉及到的技术
- poi：使用SXSSFWorkbook，对数据进行分页且批量写入到磁盘(可以是任何outputsteam)，减少数据在内存中的存储
- easyExcel：从磁盘上读取Excel文件，通过监听器的方式，可以读取表头，以及一行一行的数据，当满足一定条件(如1000行)，在进行业务逻辑的处理并入库，返回错误数据，并将错误数据回写到Excel中，供用户下载，让用户知道导入失败数据的失败原因。
- groovy：扩展导入导出标题列配置，基础导入导出配置为一个被ExportExcelProperty或ImportExcelProperty注解修饰的类，而通过groovy动态生成class对象，导入导出标题列可以是任何形式的key/value对（必须是有序的，如LinkedHashMap），key为字段名称，value为Excel表格的标题。
maven依赖
compile ('com.alibaba:easyexcel:2.2.10') {
exclude group: 'org.apache.poi'
exclude group: 'com.alibaba', module: 'fastjson'
exclude group: 'org.projectlombok'
exclude group: 'org.springframework.boot'
}
compile('org.apache.poi:poi:3.17')
compile('org.apache.poi:poi-ooxml:3.17')
compile('org.codehaus.groovy:groovy-all:2.4.13')导入导出门面类
@Component
@Slf4j
public class MyExcelContent {
@Resource
private ExcelHandleTmplate excelHandleTmplate;
/**
* Excel导入导出线程池
*/
private ExecutorService excelThreadPool = new ThreadPoolExecutor(5, 10, 60L,
TimeUnit.SECONDS, new LinkedBlockingQueue(10));
/**
* Excel导入入口
* @param configDto
* @return
*/
public ResponseMessage exelImport(ExcelImportConfigDto configDto){
if(configDto==null || configDto.getExcelFile() == null){
return ResponseMessage.faild("导入文件为空");
}
try {
//导入Excel验证 包括Excel格式 大小 是否加密等常规验证，如果验证通过，返回一个将MultipartFile类型转换成本地文件的句柄，是一个临时文件
File file = excelHandleTmplate.commonCheck(configDto.getExcelFile(), configDto.getMaxLineLimit());
//生成文件名，该文件名为最后在下载管理中看到的文件名
String fileName = getFileName(configDto.getUserId(),configDto.getOperType());
configDto.setUpload(new FileInputStream(file));
configDto.setFileName(fileName);
excelThreadPool.execute(() -> {
try {
log.info("导入异步处理开始,{}", configDto);
long startTime = System.currentTimeMillis();
excelHandleTmplate.importExcel(configDto, configDto.getExcelImportBusinessHandle()::handleBusiness);
long endTime = System.currentTimeMillis();
log.info("导入异步处理完成,{},耗时:{}ms", configDto, endTime - startTime);
} catch (Exception e) {
log.info("导入异步处理异常,{}", configDto, e);
}finally {
file.delete();
}
});
}catch (BusinessException e) {
log.error("导入异常!", e);
return ResponseMessage.faild(e.getMessage());
} catch (Exception e){
return ResponseMessage.faild("导入异常!");
}
return ResponseMessage.success(null, "导入成功,请稍后在下载查询查看导入结果!");
}
/**
* Excel导出入口
* @param configDto
* @return
*/
public ResponseMessage exelExport(ExcelExportConfigDto configDto){
//获取最大导出量 如果有配置 则取配置 如果没有 则取默认
long count = ExcelExportBusinessHandle.MAX_EXCEL_EXPORT_LINES_LIMIT;
// 限制导出数据量
if (configDto.getExcelExportBusinessHandle().getCount() > count) {
return ResponseMessage.faild("总量超过" + count + "，不支持导出");
}
//生成文件名，该文件名为最后在下载管理中看到的文件名
String fileName = getFileName(configDto.getUserId(),configDto.getOperType());
configDto.setFileName(fileName);
try {
excelThreadPool.execute(() -> {
try {
log.info("导出异步处理开始,{}", configDto);
long startTime = System.currentTimeMillis();
excelHandleTmplate.exportExcel(configDto);
long endTime = System.currentTimeMillis();
log.info("导出异步处理完成,{},耗时:{}ms", configDto, endTime - startTime);
} catch (Exception e) {
log.info("导出异步处理异常,{}", configDto, e);
}
});
}catch (BusinessException e) {
log.error("导出异常!", e);
return ResponseMessage.faild(e.getMessage());
} catch (Exception e){
return ResponseMessage.faild("导出异常!");
}
return ResponseMessage.success(null, "导出成功,请稍后在下载查询查看导入结果!");
}
/**
* 生成Excel文件名
* @param userId
* @param typeEn
* @return
*/
private String getFileName(String userId,String typeEn) {
StringJoiner nameJoiner = new StringJoiner("_", "", ".xlsx");
nameJoiner.add(typeEn).add(userId).add(String.valueOf(System.nanoTime())).toString();
return nameJoiner.toString();
}
}导入导出业务处理模板类
@Component
@Slf4j
public class ExcelHandleTmplate {
@Resource
private FileDownloadMapper fileDownloadMapper;
private static final Object EXCEL_PWD_LOCK = new Object();
/**
* 文件保存的根目录
*/
private static final String FILE_ROOT_PATH="D:/temp/";
/**
* 导入文件最多大小
*/
private static final Integer IMPORT_FILE_SIZE=10*1024*1024;
public Optional<Integer> exportExcel(ExcelExportConfigDto configDto) {
Integer taskId = initExcelTask(configDto.getFileRelativePath(),configDto.getFileName(), configDto.getOperType());
boolean sucessFlag = true;
try {
log.info("[excel export start], fileId:{}, ExcelExportConfigDto:{}", taskId, configDto);
String filePath = FILE_ROOT_PATH+ File.separator +configDto.getFileRelativePath()+ File.separator +configDto.getFileName();
Optional<Integer> exportCount = new ExcelExporter().exportExcel(configDto.getExcelConfig(), filePath, configDto.getExcelExportBusinessHandle());
return exportCount;
} catch (Exception e) {
log.error("[excel export error]，fileId:{}, ExcelImportConfigDto：{}", taskId, configDto,e);
sucessFlag = false;
throw new BusinessException("导出异常");
}finally {
postExcelTask(taskId,sucessFlag);
configDto.getExcelExportBusinessHandle().postExport(sucessFlag,configDto);
}
}
public Optional<Integer> importExcel(ExcelImportConfigDto configDto, Function<List<?>, List<?>> impl){
Integer fileId = initExcelTask(configDto.getFileRelativePath(),configDto.getFileName(), configDto.getOperType());
boolean sucessFlag = true;
try {
log.info("[excel import start], fileId:{}, ExcelImportConfigDto：{}", fileId, configDto);
String filePath = FILE_ROOT_PATH+ File.separator +configDto.getFileRelativePath()+ File.separator +configDto.getFileName();
Optional<Integer> importCount = new ExcelImporter().importExcel(filePath,configDto, impl);
return importCount;
}catch (Exception e){
log.error("[excel import error]，fileId:{}, ExcelImportConfigDto：{}", fileId, configDto,e);
sucessFlag = false;
throw new BusinessException("导入异常");
}finally {
postExcelTask(fileId, sucessFlag);
configDto.getExcelImportBusinessHandle().postImportData(sucessFlag, configDto);
}
}
/**
* 初始化导入导出数据供页面查询
* @param fileName
* @param operType
* @return
*/
private Integer initExcelTask(String localFileAbsPath,String fileName, String operType) {
FileDownload fileDownload = new FileDownload();
fileDownload.setInsertUser("当前操作用户ID");
fileDownload.setFileName(fileName);
fileDownload.setStatus(FileStatusEnum.GENERATE_ING.getKey());
fileDownload.setStatusRemark(FileStatusEnum.GENERATE_ING.getValue());
fileDownload.setType(operType);
fileDownload.setFilePath(localFileAbsPath);
fileDownload.setIsEncrypted((byte) 1);
fileDownloadMapper.save(fileDownload);
return fileDownload.getId();
}
/**
* 更新最终状态到fileDownload中供页面查询 且将文件加密保存到磁盘上
* @param fileId fileDownload的ID
* @param successFlag 正常还是异常
*/
private void postExcelTask(int fileId, boolean successFlag){
//更新文件记录表
FileDownload fd = fileDownloadMapper.findOneById(fileId);
if (fd == null) {
log.error("导入生成下载文件不存在,fileDownload:{}", fileId);
return;
}
if(!successFlag){
fd.setStatus(FileStatusEnum.GENERATE_FAILURE.getKey());
fd.setStatusRemark(FileStatusEnum.GENERATE_FAILURE.getValue());
fileDownloadMapper.updateByPrimaryKey(fd);
return;
}
try {
String filePath = FILE_ROOT_PATH+ File.separator +fd.getFilePath()+ File.separator +fd.getFileName();
//加密密码
String password = getUUIDByRandReplacedLower();
setExcelPassword(filePath, filePath, password);
fd.setStatus(FileStatusEnum.GENERATE_SUCCESS.getKey());
fd.setStatusRemark(FileStatusEnum.GENERATE_SUCCESS.getValue());
fd.setFilePassword(password);
fileDownloadMapper.updateByPrimaryKey(fd);
} catch (Exception e) {
log.error("导入导出报错", e);
fd.setStatus(FileStatusEnum.GENERATE_FAILURE.getKey());
fd.setStatusRemark(FileStatusEnum.GENERATE_FAILURE.getValue());
fileDownloadMapper.updateByPrimaryKey(fd);
}
}
/**
* 为Excel加密
* @param filepath
* @param targetPath
* @param password
* @throws
*/
private void setExcelPassword(String filepath, String targetPath, String password) throws IOException {
// 防止多线程情况下， JVM cash
synchronized (EXCEL_PWD_LOCK) {
File tempFile = copyTmpFile(filepath);
if (tempFile == null) {
throw new RuntimeException("setExcelPassword copy temp file Error!");
}
try (POIFSFileSystem fs = new POIFSFileSystem()) {
EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
// EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile, CipherAlgorithm.aes192, HashAlgorithm.sha384, -1, -1, null);
Encryptor enc = info.getEncryptor();
enc.confirmPassword(password);
// Read in an existing OOXML file and write to encrypted output stream
// don't forget to close the output stream otherwise the padding bytes aren't added
try (OPCPackage opc = OPCPackage.open(tempFile, PackageAccess.READ_WRITE)) {
// 这个流不能在这里关
OutputStream os = enc.getDataStream(fs);
opc.save(os);
} catch (GeneralSecurityException | InvalidFormatException ex) {
throw new IOException(ex);
}
// Write out the encrypted version
try (FileOutputStream fos = new FileOutputStream(org.springframework.util.StringUtils.isEmpty(targetPath) ? filepath : targetPath)) {
fs.writeFilesystem(fos);
}
}finally {
if(tempFile != null && tempFile.exists()) {
tempFile.delete();
}
}
}
}
/**
* 导入时 对Excel进行常规验证
* @param upload 上传的文件
* @param maxLimitLines
* @return
*/
public File commonCheck(MultipartFile upload, Integer maxLimitLines) {
if (null == upload) {
throw new BusinessException("文件为空");
}
// 文件上传大小限制，10M
if (upload.getSize() > IMPORT_FILE_SIZE) {
throw new BusinessException("文件不能大于" + IMPORT_FILE_SIZE + " M");
}
//校验文件
String extension = FilenameUtils.getExtension(upload.getOriginalFilename()).toLowerCase();
if (!"xls".equals(extension) && !"xlsx".equals(extension)) {
throw new BusinessException("请上传[.xls,.xlsx]格式的文件!");
}
File tempFile = copyTmpFile(upload);
try {
boolean hasNoPwd = checkHasNoPassword(new FileInputStream(tempFile));
if (!hasNoPwd) {
throw new BusinessException("导入提示, 该文件已加密，不能导入!");
}
} catch (Exception e) {
throw new BusinessException("导入提示, 导入文件不存在");
}
try {
if (maxLimitLines == null) {
maxLimitLines = ExcelImportBusinessHandle.MAX_EXCEL_EXPORT_LINES_LIMIT;
}
// 通过SAX获取Excel总行数，防止内存溢出
int rowCount = ExcelImporter.getRowCount(new FileInputStream(tempFile));
if (rowCount > maxLimitLines) {
throw new BusinessException("导入提示, 总行数不能超过" + maxLimitLines + "行!");
}
} catch (Exception e) {
throw new BusinessException("导入提示, EXCEL解析异常！");
}
return tempFile;
}
/**
* 验证Excel是否加密
* @param is
* @return
*/
private static boolean checkHasNoPassword(InputStream is) {
try {
EncryptionInfo info = new EncryptionInfo(new POIFSFileSystem(is));
Decryptor d = info.getDecryptor();
if (!d.verifyPassword(Decryptor.DEFAULT_PASSWORD)) {
return false;
}
} catch (GeneralSecurityException ex) {
log.error("Unable to process encrypted document", ex);
} catch (OfficeXmlFileException ex3) {
// 该文件是明文文件，会抛出这个异常
return true;
} catch (Exception ex3) {
// 其他的异常说明Excel
return false;
}
return false;
}
/**
* 复制文件
* @param path
* @return
*/
private File copyTmpFile(String path) {
try {
File source = new File(path);
if(source.exists()) {
// 临时文件名
String newFileName = UUID.randomUUID().toString() + "_" + Thread.currentThread().getId() + "_" + source.getName() + "_.tmp";
// 临时文件全路径
File file = new File(getFilePath(newFileName));
// 不存在就创建
if (!file.exists()) {
file.createNewFile();
}
FileUtils.copyInputStreamToFile(new FileInputStream(new File(path)), file);
return file;
}else {
return null;
}
} catch (Exception e) {
log.error("Temporary file copy exception, And exception information", e);
return null;
}
}
/**
* 复制文件
* @param upload
* @return
*/
private File copyTmpFile(MultipartFile upload) {
try {
// 减少并发产生的文件覆盖操作
String newFilePath = System.nanoTime() + "_import.tmp";
File file = new File(getFilePath(newFilePath));
if(!file.exists()){
file.createNewFile();
}
FileUtils.copyInputStreamToFile(upload.getInputStream(), file);
// 记录Excel文件上传后，本地磁盘的临时文件地址（该文件保留3天），便于出现问题后，可以直接从服务器上直接拿对应有问题的文件
log.info("拷贝后的文件路径：{}", file.getAbsolutePath());
return file;
} catch (Exception e) {
log.error("Temporary file copy exception, And exception information", e);
}
return null;
}
/**
* 获取文件路径
* @param fileName
* @return
*/
private String getFilePath(String fileName) {
return FILE_ROOT_PATH + File.separator + fileName;
}
/**
* 生成Excel加密密码
* @return
*/
private String getUUIDByRandReplacedLower() {
//文件密码
return UUID.randomUUID().toString().replace("-", "").toLowerCase().substring(0, 10);
}
}导出处理器
public class ExcelExporter<T> extends CommonForkJoinService<T, Integer> {
private static final Logger logger = LoggerFactory.getLogger(ExcelExporter.class);
/**
* 单次Excel导出的最大行数
*/
public static final int MAX_COUNT_IN_SHEET = 1000000;
private CellStyle xssfCellStyle;
public ExcelExporter() {
super.setMaxCount(MAX_COUNT_IN_SHEET);
}
/**
* 分页导出数据
* @param cls
* @param fileAbsPath
* @param scanner
* @return
* @throws
*/
public Optional<Integer> exportExcel(Class<T> cls, String fileAbsPath, ExcelExportBusinessHandle<T> scanner) throws IOException {
// 存放excel标题
List<String> titleList = Lists.newArrayList();
// 存放Filed[]
List<Field> fieldList = Lists.newArrayList();
// 解析导出excel配置
parseEasyExcelConfig(cls, titleList, fieldList);
// 将数据写入excel
return writeData2Excel(fileAbsPath, titleList, fieldList, scanner);
}
/**
* 将数据写入到Excel里
* @param fileAbsPath
* @param titleList
* @param fieldList
* @param scanner
* @return
* @throws
*/
private Optional<Integer> writeData2Excel(String fileAbsPath, List<String> titleList, List<Field> fieldList, ExcelExportBusinessHandle<T> scanner) throws IOException {
SXSSFWorkbook sxssfWorkbook = null;
Optional<Integer> result = null;
long excelStart = System.currentTimeMillis();
try (FileOutputStream stream = new FileOutputStream(new File(fileAbsPath));) {
// the number of rows that are kept in memory until flushed out
sxssfWorkbook = new SXSSFWorkbook(100);
xssfCellStyle = setStyle(sxssfWorkbook);
// 本地导出的总行数
// 创建一个sheet
Sheet currentSheet = sxssfWorkbook.createSheet(scanner.sheetNamePrefix());
// Excel已经处理到的行
AtomicInteger currentRowIndex = new AtomicInteger(-1);
// 创建title行
Row firstRow = currentSheet.createRow(currentRowIndex.incrementAndGet());
for (int j = 0; j < titleList.size(); j++) {
setCellValue(firstRow,j,titleList.get(j));
}
// forkJoin处理的每个list
Function<List<T>, Integer> impl = ts -> {
int dealCount = 0;
// 遍历每一行，导出行数据到excel
try {
if (CollectionUtils.isNotEmpty(ts)) {
for (T record : ts) {
dealSingleRow(fieldList, currentSheet, record, currentRowIndex);
dealCount++;
}
}
} catch (Exception e) {
logger.error("Excel {} 写入异常,", fileAbsPath, e);
}
return dealCount;
};
long start = System.currentTimeMillis();
// 将数据写入到excel， 并返回总条数
result = super.forkJoinWithScanner(scanner, impl, Integer::sum);
logger.info("Export Data to {} , cost:{} ms", fileAbsPath, (System.currentTimeMillis() - start));
// 写入到磁盘
sxssfWorkbook.write(stream);
logger.info("Export Data to {} finish ,total cost:{} ms", fileAbsPath, (System.currentTimeMillis() - excelStart));
} catch (InterruptedException | ExecutionException e) {
logger.error("Export data to {} error", fileAbsPath,e);
throw new IOException(e);
} finally {
if (sxssfWorkbook != null) {
sxssfWorkbook.close();
// SXSSF会分配临时文件，必须始终通过调用dispose方法来明确清理这些文件。
sxssfWorkbook.dispose();
}
}
return result;
}
/**
* 设置Excel样式
* @param workbook
* @return
*/
private CellStyle setStyle(SXSSFWorkbook workbook) {
CellStyle style = workbook.createCellStyle();
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
XSSFColor color = new XSSFColor(java.awt.Color.BLACK);
style.setTopBorderColor(color.getIndex());
style.setLeftBorderColor(color.getIndex());
style.setBottomBorderColor(color.getIndex());
style.setRightBorderColor(color.getIndex());
return style;
}
/**
* 创建单行excecl row
* @param fieldList
* @param currentSheet
* @param record
* @param currentRowIndex
* @throws IllegalAccessException
*/
private void dealSingleRow(List<Field> fieldList, Sheet currentSheet, T record, AtomicInteger currentRowIndex) throws IllegalAccessException {
Row row = null;
// 由于createRow不是线程安全的, 对单个sheet进行创建row时，可能会并发
synchronized (this) {
row = currentSheet.createRow(currentRowIndex.incrementAndGet());
}
int cellIndex = 0;
for (Field field : fieldList) {
if (!field.isAccessible()) {
field.setAccessible(true);
}
// 通过反射 调用record的get方法获取值
Object valueObj = field.get(record);
String value = null;
if (valueObj == null) {
value = "";
} else {
// TODO 其他类型扩展？
if (valueObj instanceof Date) {
value = DateUtil.format((Date) valueObj, "yyyy-MM-dd HH:mm:ss");
} else {
value = valueObj.toString();
}
}
setCellValue(row, cellIndex++, value);
}
}
/**
* 处理单个单元格
* @param row
* @param cellIndex
* @param value
*/
private void setCellValue(Row row, int cellIndex, String value) {
Cell cell = row.createCell(cellIndex);
cell.setCellStyle(xssfCellStyle);
cell.setCellValue(value);
}
/**
* 解析Excel导出配置DTO， 获取title等信息
* @param cls
* @param titleList
* @param fieldList
*/
protected void parseEasyExcelConfig(Class<T> cls, List<String> titleList, List<Field> fieldList) {
Assert.notNull(cls, "Excel 配置类不能为空！");
Field[] titleFieldSet = cls.getDeclaredFields();
if (titleFieldSet.length <= 0) {
throw new IllegalArgumentException(cls.getName() + " 配置错误！");
}
// titleOrder 排序顺序按照cell中的index存放
TreeMap<Integer, ExportExcelProperty> titleOrder = new TreeMap<>();
// FieldOrder 排序顺序按照cell中的index存放
TreeMap<Integer, Field> fieldOrder = new TreeMap<>();
for (Field field : titleFieldSet) {
ExportExcelProperty yes = field.getAnnotation(ExportExcelProperty.class);
if (null != yes) {
titleOrder.put(yes.index(), yes);
fieldOrder.put(yes.index(), field);
}
}
// 将解析出的title信息放到list
for (Integer index : titleOrder.keySet()) {
titleList.add(titleOrder.get(index).value()[0]);
}
// 将解析出来的get方法存到list
for (Integer index : fieldOrder.keySet()) {
fieldList.add(fieldOrder.get(index));
}
Assert.notEmpty(titleList, cls.getName() + " Title未配置");
Assert.notEmpty(fieldList, cls.getName() + " Field未配置");
Assert.isTrue(fieldList.size() == titleList.size(), " @ExcelProperty配置错误，请检查是否含有null");
}
}导出处理器父类
public class CommonForkJoinService<T, R> {
private static final Logger logger = LoggerFactory.getLogger(CommonForkJoinService.class);
/**
* 默认forkJoin线程池
*/
private static final ForkJoinPool DEFAULT_POOL = new ForkJoinPool(CpuCountUtil.getCpuNumber("CommonForkJoinService", null));
/**
* 每次从数据库扫描的条数
*/
private Integer pageSize = 20000;
/**
* 当导出数据大于此值时， 强制启动forkjoin
*/
private Integer useForkJoinGt = 20000;
private ForkJoinPool customPool ;
/**
* 数据扫描的最大条数
*/
private Integer maxCount = 1000000;
public Integer getMaxCount() {
return maxCount;
}
public void setMaxCount(Integer maxCount) {
this.maxCount = maxCount;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getUseForkJoinGt() {
return useForkJoinGt;
}
public void setUseForkJoinGt(Integer useForkJoinGt) {
this.useForkJoinGt = useForkJoinGt;
}
public ForkJoinPool getCustomPool() {
if(this.customPool == null) {
return DEFAULT_POOL;
}
return customPool;
}
/**
* 子类可以重写改方法，达到自定义fork-join线程池
* @param customPool
*/
public void setCustomPool(ForkJoinPool customPool) {
this.customPool = customPool;
}
/**
* 通用数据扫描，如果超过了 <code>useForkJoinGt</code> 则强制启动fork-join的方式
* T- 输入类型 R-返回类型
* @param scanner
* @param impl
* @param join
* @return
* @throws
* @throws InterruptedException
*/
public Optional<R> forkJoinWithScanner(ExcelExportBusinessHandle<T> scanner, Function<List<T>, R> impl, BiFunction<R, R, R> join) throws ExecutionException, InterruptedException {
// 调用查询总条数
long start = System.currentTimeMillis();
Integer totalCount = scanner.getCount();
long end = (System.currentTimeMillis() - start);
if(end > 100) {
logger.info("forkJoinWithScanner 调用getCount()耗时：{}",end );
}
if (totalCount == null || totalCount == 0) {
logger.warn("Empty total count impl, just return empty!");
return Optional.empty();
}
// 数据总条数, 如果总条数超过100W，则只导出100W, 子类可以修改此值
if (totalCount > maxCount) {
totalCount = maxCount;
}
logger.info("forkJoinWithScanner 限制总条数：{}", new DecimalFormat("#,###").format(totalCount.longValue()));
// 每次查询条
int pageSize = scanner.getPageSize() == 0 ? getPageSize() : scanner.getPageSize() ;
// 符合条件的总页数
int totalPages = totalCount / pageSize + (totalCount % pageSize == 0 ? 0 : 1);
// 当前扫描页
int currentPage = -1;
// 如果总条数大于指定条数，则强制启动fork-join
boolean useForkJoin = totalCount.compareTo(useForkJoinGt) > 0;
// 如果客户端明确要求不使用fork-join，则关闭
if(scanner.useForkJoinPerPage() == false) {
useForkJoin = false;
}
// 分页查询符合条件的数据
List<T> dbList = null;
// 返回值
R returnValue = null;
// 总页数是从1开始的，程序这里要从0开始，故改为大于
while (totalCount > 0 && totalPages > ++currentPage) {
long pageStart = System.currentTimeMillis();
// 根据分页参数，获取数据, currentPage 从0开始
dbList = scanner.getDetailList(currentPage, pageSize);
logger.info("forkJoinWithScanner getDetailList()耗时：{}", (System.currentTimeMillis() - pageStart));
if (CollectionUtils.isNotEmpty(dbList)) {
if(dbList.size() > pageSize) {
logger.error("[开发人员请注意][严重]分页查询出来的数据行数大于pageSize, 数据大小：{} 分页参数：{}", dbList.size(), pageSize);
throw new RuntimeException("分页查询出来的数据行数大于" + getPageSize());
}
if (useForkJoin) {
// 生成fork-join任务
CommonRecursiveTask<T, R> task = new CommonRecursiveTask<>(dbList, impl, join);
// 提交fork-join任务
ForkJoinTask<R> result = getCustomPool().submit(task);
// 将fork-join返回的值封装
if (returnValue == null) {
returnValue = result.get();
} else {
if (join != null) {
returnValue = join.apply(returnValue, result.get());
}
}
} else {
R temp = null;
if (impl != null) {
temp = impl.apply(dbList);
}
if(returnValue == null) {
returnValue = temp;
}else {
returnValue = join.apply(returnValue, temp);
}
}
} else {
break;
}
}
return Optional.ofNullable(returnValue);
}
}导出大量数据时的forkjoin类
public class CommonRecursiveTask<T, R> extends RecursiveTask<R> {
/**
* 最小执行的任务数
*/
private static final int MIN_TASK_COUNT = 100;
/**
* 需要处理的数据
*/
private List<T> dataList;
/**
* 每个最小单元的List对应的处理逻辑函数
*/
private Function<List<T>, R> impl;
/**
* 每个最小单元的List处理完成后，结果合并函数
*/
private BiFunction<R, R, R> join;
public CommonRecursiveTask(List<T> dataList, Function<List<T>, R> impl, BiFunction<R, R, R> join) {
this.dataList = dataList;
this.impl = impl;
this.join = join;
}
public CommonRecursiveTask() {
}
public void setDataList(List<T> dataList) {
this.dataList = dataList;
}
public void setImpl(Function<List<T>, R> impl) {
this.impl = impl;
}
public void setJoin(BiFunction<R, R, R> join) {
this.join = join;
}
@Override
protected R compute() {
// 是否还要继续拆分List
boolean canCompute = dataList.size() <= MIN_TASK_COUNT;
if (canCompute) {
if (impl != null) {
return impl.apply(this.dataList);
} else {
throw new IllegalArgumentException("fork-join处理异常，业务代码为空！");
}
} else {
final List<T> leftTempList = dataList.subList(0, dataList.size() / 2);
final List<T> rightTempList = dataList.subList(dataList.size() / 2, dataList.size());
CommonRecursiveTask<T, R> leftTask = new CommonRecursiveTask<T, R>(leftTempList, impl, join);
CommonRecursiveTask<T, R> rightTask = new CommonRecursiveTask<T, R>(rightTempList, impl, join);
// 执行子任务
leftTask.fork();
rightTask.fork();
// 等待任务执行结束合并其结果
R leftResult = leftTask.join();
R rightResult = rightTask.join();
if (join != null) {
// 合并子任务
return join.apply(leftResult, rightResult);
} else {
throw new IllegalArgumentException("fork-join处理异常，合并分片代码为空！");
}
}
}
}导出配置类
@Getter
@Setter
public class ExcelExportConfigDto {
/**
* 生成的本地文件相对路径 需要调用端设置
*/
private String fileRelativePath;
/**
* 当前导入操作用户名 需要调用端设置
*/
private String userId;
/**
* 文件名 自动设置
*/
private String fileName;
/**
* 操作类型 需要调用端设置
*/
private String operType;
/**
* 数据扫苗方式 需要调用端设置
*/
private ExcelExportBusinessHandle excelExportBusinessHandle;
/**
* excel的配置dto， 需要调用端设置
*/
private Class excelConfig;
public ExcelExportConfigDto(String fileRelativePath, String userId,String operType, Class excelConfig, ExcelExportBusinessHandle excelExportBusinessHandle) {
this.fileRelativePath = fileRelativePath;
this.operType = operType;
this.excelExportBusinessHandle = excelExportBusinessHandle;
this.excelConfig = excelConfig;
this.userId = userId;
}
}导出钩子类
public interface ExcelExportBusinessHandle<T> {
/**
* 默认最大Excel导出行数
*/
int MAX_EXCEL_EXPORT_LINES_LIMIT = 1_000_000;
/**
* 获取数据总条数，会根据这个返回的结果计算分页参数
*/
Integer getCount();
/**
* 根据计算出的分页参数，获取数据
* @param pageIndex 当前页
* @param pageSize 当前页大小
* @return 分页查询结果集
*/
List<T> getDetailList(Integer pageIndex, Integer pageSize);
/**
* 分页扫描时默认扫描的行数
* @return
*/
default Integer getPageSize() {
return 1000;
}
/**
* 导出时默认的sheet名称
*/
default String sheetNamePrefix() {
return "Sheet-";
}
/**
* 默认不开启fork-join 因为投诉工单系统这边很多导出都要排序
*
* @return
*/
default boolean useForkJoinPerPage() {
return false;
}
/**
* 数据导出结束后 的操作
* @param flag 导出操作是否正常结束 true为正常结束，false为导出异常
* @param configDto
*/
default void postExport(boolean flag,ExcelExportConfigDto configDto){}
}导入处理器
public class ExcelImporter<T>{
/**
* slf4j 日志组件
*/
private static final Logger logger = LoggerFactory.getLogger(ExcelImporter.class);
/**
* 导出excel SXSSFWorkbook
*/
SXSSFWorkbook sxssfWorkbook = null;
/**
* 导出excel 当前操作的sheet
*/
private Sheet currentSheet;
/**
* Excel已经处理到的行
*/
private AtomicInteger currentRowIndex = new AtomicInteger(-1);
/**
* 输出到本地文件的流
*/
private FileOutputStream stream;
/**
* 存放excel标题
*/
private List<String> titleList = Lists.newArrayList();
/**
* 存放Filed[]
*/
private List<Field> fieldList = Lists.newArrayList();
private Field errorInfo;
/**
* 每次从Excel读取1000行后处理业务
*/
private int MAX_SIZE_PER_IMPORT = 1000;
/**
* 导入数据
* @param localFailedPath
* @param configDto
* @param impl
* @return
* @throws Exception
*/
public Optional<Integer> importExcel(String localFailedPath, ExcelImportConfigDto configDto, Function<List<T>, List<T>> impl ) throws Exception {
Integer pageSize = configDto.getPageSize();
if(pageSize!=null){
MAX_SIZE_PER_IMPORT = pageSize;
}
// 解析导出excel配置
parseEasyExcelConfig(configDto.getExcelConfig());
// 初始化导入校验失败的EXCEL
initFailedExcel(localFailedPath, "失败列表");
long start = System.currentTimeMillis();
// 已经解析成功的Excel行数
AtomicInteger execCount = new AtomicInteger(0);
EasyExcel.read(configDto.getUpload(), new ReadListener() {
// 每次从Excel转换出来的数据
List<T> dtoList = new ArrayList<>(MAX_SIZE_PER_IMPORT);
Map<Integer, String> headerMap = new HashMap<>();
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
logger.error("EasyExcel解析时发生异常:{}", configDto.getOperType(), exception);
throw new BusinessException("EasyExcel解析时发生异常");
}
@Override
public void invokeHead(Map headMap, AnalysisContext context) {
Map<Integer, CellData> formatHeadMap = (Map<Integer, CellData>)headMap;
for (Map.Entry<Integer, CellData> entry : formatHeadMap.entrySet()) {
headerMap.put(entry.getKey(), entry.getValue().getStringValue());
}
}
@Override
public void invoke(Object data, AnalysisContext context) {
if(data !=null && data instanceof Map) {
Map<Integer,String> dataMap = (Map) data;
Map<String, String> header2DataMap = new HashMap<>();
for (Map.Entry<Integer, String> entry : dataMap.entrySet()) {
header2DataMap.put(headerMap.get(entry.getKey()), entry.getValue());
}
try {
T dto = (T)configDto.getExcelConfig().newInstance();
readExcelDataFromMap(dto, header2DataMap);
dtoList.add(dto);
} catch (Exception e) {
logger.error("通过反射读取Excel数据异常, type:{}", configDto.getOperType(), e);
throw new BusinessException("EasyExcel解析时发生异常");
}
execCount.incrementAndGet();
} else {
logger.warn("通过EasyExcel读取数据，返回的数据为空 或者不是Map类型 type:{}",configDto.getOperType());
throw new BusinessException("通过EasyExcel读取数据，返回的数据为空");
}
if (dtoList.size() < MAX_SIZE_PER_IMPORT) {
return;
}
doWithBusinessCode();
}
private void doWithBusinessCode() {
// 超过指定条数，调用业务代码
List<T> failedList = new ArrayList<>();
try {
failedList = impl.apply(dtoList);
}catch (Exception e){
logger.error("EasyExcel写入错误信息到Excel异常:{}", configDto.getOperType(), e);
failedList = dtoList;
for(T t:failedList){
errorInfo.setAccessible(true);
try {
Object o = errorInfo.get(t);
if(o==null){
errorInfo.set(t, "处理数据业务异常");
}
} catch (IllegalAccessException e1) {
logger.error("处理数据业务异常", configDto.getOperType(), e);
}
}
}
Optional.ofNullable(failedList).orElse(Collections.emptyList()).forEach(failedData -> {
try {
dealSingleRow(fieldList,currentSheet,failedData,currentRowIndex);
} catch (Exception e) {
logger.error("EasyExcel写入错误信息到Excel异常:{}", configDto.getOperType(), e);
throw new BusinessException("EasyExcel写入错误信息到Excel异常");
}
});
dtoList = new ArrayList<>(MAX_SIZE_PER_IMPORT);
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (CollectionUtils.isNotEmpty(dtoList)) {
doWithBusinessCode();
}
// Excel解析完毕
try {
sxssfWorkbook.write(stream);
} catch (IOException e) {
logger.error("EasyExcel写入错误信息到Excel异常 刷盘阶段 {}", configDto.getOperType(), e);
}
}
@Override
public boolean hasNext(AnalysisContext context) {
return true;
}
}).ignoreEmptyRow(true).excelType(ExcelTypeEnum.XLSX).sheet(0).doRead();
logger.info("EasyExcel导入读取excel，导入类型：{}, 处理条数：{}, 耗时：{} ms",configDto.getOperType(), execCount.get(), (System.currentTimeMillis() - start));
return Optional.of(execCount.get());
}
/**
* 获取Excel文件总行数
* @param file
* @return
*/
public static int getRowCount(InputStream file) {
AtomicInteger count = new AtomicInteger(0);
EasyExcel.read(file, new ReadListener() {
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
logger.error("EasyExcel获取Excel总行数失败", exception);
throw new BusinessException("EasyExcel获取Excel总行数失败");
}
@Override
public void invokeHead(Map headMap, AnalysisContext context) {
}
@Override
public void invoke(Object data, AnalysisContext context) {
count.incrementAndGet();
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
@Override
public boolean hasNext(AnalysisContext context) {
return true;
}
}).excelType(ExcelTypeEnum.XLSX).ignoreEmptyRow(false).sheet(0).doRead();
if (count.get() > 500000) {
logger.warn("[开发人员请注意][严重]获取Excel的行数超过50W，可能存在性能问题，请检查！");
}
return count.get();
}
/**
* 初始化导入失败的excel，新建本地excel，初始化title
* @param fileAbsPath
* @param sheetName
* @throws Exception
*/
public void initFailedExcel(String fileAbsPath,String sheetName) throws Exception {
stream = new FileOutputStream(new File(fileAbsPath));
// the number of rows that are kept in memory until flushed out
sxssfWorkbook = new SXSSFWorkbook(100);
// 本地导出的总行数
// 创建一个sheet
currentSheet = sxssfWorkbook.createSheet(sheetName);
// 创建title行
Row firstRow = currentSheet.createRow(currentRowIndex.incrementAndGet());
for (int j = 0; j < titleList.size(); j++) {
firstRow.createCell(j).setCellValue(titleList.get(j));
}
}
/**
* 解析Excel导出配置DTO， 获取title等信息
* @param cls
*/
protected void parseEasyExcelConfig(Class<T> cls) {
Assert.notNull(cls, "Excel 配置类不能为空！");
Field[] titleFieldSet = cls.getDeclaredFields();
if (titleFieldSet.length <= 0) {
throw new IllegalArgumentException(cls.getName() + " 配置错误！");
}
// 解决父类中有属性的情况
titleFieldSet = ArrayUtils.addAll(titleFieldSet, cls.getFields());
// titleOrder 排序顺序按照cell中的index存放
TreeMap<Integer, ImportExcelProperty> titleOrder = new TreeMap<>();
// FieldOrder 排序顺序按照cell中的index存放
TreeMap<Integer, Field> fieldOrder = new TreeMap<>();
for (Field field : titleFieldSet) {
ImportExcelProperty yes = field.getAnnotation(ImportExcelProperty.class);
if (null != yes) {
titleOrder.put(yes.index(), yes);
fieldOrder.put(yes.index(), field);
}
}
// 将解析出的title信息放到list
for (Integer index : titleOrder.keySet()) {
titleList.add(titleOrder.get(index).value()[0]);
}
// 将解析出来的get方法存到list
for (Integer index : fieldOrder.keySet()) {
Field field = fieldOrder.get(index);
fieldList.add(field);
if(field.getName().equalsIgnoreCase("errorInfo")){
errorInfo = field;
}
}
Assert.notEmpty(titleList, cls.getName() + " Title未配置");
Assert.notEmpty(fieldList, cls.getName() + " Field未配置");
Assert.isTrue(fieldList.size() == titleList.size(), " @Cell配置错误，请检查是否含有null");
}
/**
* 创建单行excecl row
* @param fieldList
* @param currentSheet
* @param record
* @param currentRowIndex
* @throws IllegalAccessException
*/
private void dealSingleRow(List<Field> fieldList, Sheet currentSheet, T record, AtomicInteger currentRowIndex) throws IllegalAccessException {
Row row = null;
// 由于createRow不是线程安全的, 对单个sheet进行创建row时，可能会并发
synchronized (this) {
row = currentSheet.createRow(currentRowIndex.incrementAndGet());
}
int cellIndex = 0;
for (Field field : fieldList) {
if (!field.isAccessible()) {
field.setAccessible(true);
}
// 通过反射 调用record的get方法获取值
Object valueObj = field.get(record);
String value = null;
if (valueObj == null) {
value = "";
} else {
if (valueObj instanceof Date) {
value = DateUtil.format((Date) valueObj,"yyyy-MM-dd HH:mm:ss");
} else {
value = valueObj.toString();
}
}
setCellValue(row, cellIndex++, value);
}
}
/**
* 处理单个单元格
* @param row
* @param cellIndex
* @param value
*/
private void setCellValue(Row row, int cellIndex, String value) {
row.createCell(cellIndex).setCellValue(value);
}
/**
* 通过反射，从map中获取数据到dto
* @param dto
* @param dataMap
*/
private void readExcelDataFromMap(T dto, Map<String, String> dataMap) {
try {
Field[] fields = dto.getClass().getDeclaredFields();
for (Field field : fields) {
if (!field.isAccessible()) {
field.setAccessible(true);
}
ImportExcelProperty cell = field.getAnnotation(ImportExcelProperty.class);
if (cell != null) {
String data = dataMap.get(cell.value()[0]);
field.set(dto, stringToObject(data,field.getType(),cell.format()));
}
}
} catch (Exception e) {
logger.error("读取Excel数据异常！ ", e);
throw new BusinessException("读取Excel数据异常");
}
}
/**
* 类型转换
* @param data
* @param clazz
* @param format
* @return
*/
private Object stringToObject(String data,Class<?> clazz,String format){
if(clazz.isAssignableFrom(String.class)){
return data;
}
if(clazz.isAssignableFrom(Integer.class)){
return Integer.valueOf(data);
}
if(clazz.isAssignableFrom(Long.class)){
return Long.valueOf(data);
}
if(clazz.isAssignableFrom(Float.class)){
return Float.valueOf(data);
}
if(clazz.isAssignableFrom(Double.class)){
return Double.valueOf(data);
}
if(clazz.isAssignableFrom(Boolean.class)){
return Boolean.valueOf(data);
}
if(clazz.isAssignableFrom(Date.class)){
if(StringUtils.isNotBlank(format)){
return DateUtil.parse(data,format);
}
return DateUtil.parse(data,"yyyy-MM-dd");
}
return null;
}
}导入配置类
@Getter
@Setter
public class ExcelImportConfigDto<T> {
/**
* 操作类型 需要调用端设置
*/
private String operType;
/**
* 当前导入操作用户名 需要调用端设置
*/
private String userId;
/**
* excel的配置dto 需要调用端设置
*/
private Class<T> excelConfig;
/**
* Excel业务处理类 需要调用端设置
*/
private ExcelImportBusinessHandle<T> excelImportBusinessHandle;
/**
* 导入数据最大行数 需要调用端设置 默认为100W行
*/
private Integer maxLineLimit;
/**
* 每页处理数据，默认为1000行 导入是一页一页的导入，如果不同页的数据有依赖关系 那么可以把这个参数设置大些
*/
private Integer pageSize;
/**
* 上传最原始的文件 需要调用端设置
*/
private MultipartFile excelFile;
/**
* 文件相对路径 需要调用端设置
*/
private String fileRelativePath;
/**
* 文件名称
*/
private String fileName;
/**
* 上传的输入流
*/
private InputStream upload;
public ExcelImportConfigDto(MultipartFile excelFile,String userId,String operType,String fileRelativePath, Class<T> excelConfig, ExcelImportBusinessHandle<T> excelImportBusinessHandle) {
this.operType = operType;
this.excelConfig = excelConfig;
this.fileRelativePath = fileRelativePath;
this.excelImportBusinessHandle = excelImportBusinessHandle;
this.excelFile = excelFile;
this.userId = userId;
}
}导入钩子类
public interface ExcelImportBusinessHandle<T> {
/**
* 默认最大Excel导入行数
*/
int MAX_EXCEL_EXPORT_LINES_LIMIT = 1_000_000;
List<T> handleBusiness(List<T> list);
/**
* 数据导入结束后 的操作
* @param flag 导入操作是否正常结束 true为正常结束，false为导出异常
* @param configDto
*/
default void postImportData(boolean flag,ExcelImportConfigDto<T> configDto){}
}自定义注解
//1. 模拟的ExcelProperty注解，在原生的easyExcel里，用的是ExcelProperty注解表示导入导出字段这里分开了，因为导入和导出可能对应的字段不一样
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
//模拟的ExcelProperty注解
public @interface ImportExcelProperty {
String[] value() default {""};
int index() default -1;
int order() default Integer.MAX_VALUE;
Class<? extends Converter> converter() default AutoConverter.class;
/**
* 日期类型的格式化
* @return
*/
String format() default "";
}
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
//模拟的ExcelProperty注解
public @interface ExportExcelProperty {
String[] value() default {""};
int index() default -1;
int order() default Integer.MAX_VALUE;
Class<? extends Converter> converter() default AutoConverter.class;
String format() default "";
}其他辅助类
public class BusinessException extends RuntimeException{
private Integer code;
private String message;
public BusinessException(String message) {
this.code = 1;
this.message = message;
}
public BusinessException(Integer code,String message) {
this.code = code;
this.message = message;
}
public Integer getCode() {
return code;
}
@Override
public String getMessage() {
return message;
}
}
@Slf4j
public class CpuCountUtil {
/**
* 用于使用forkjoin时获取最大并行数
* @param forkjoinName 调用方
* @param threadNumber 有值使用自定义并行数，无值、小于等于0、大于最大CPU核数使用一半的CPU线程数
* @return
*/
public static int getCpuNumber(String forkjoinName,Integer threadNumber) {
long start = System.nanoTime();
try {
int cpuNumber = Runtime.getRuntime().availableProcessors();
if (null == threadNumber) {
threadNumber = (cpuNumber/2)+1;
}else if (threadNumber >= cpuNumber) {
threadNumber = cpuNumber;
}
} catch (Exception e) {
threadNumber = 2;
log.warn("forkjoinName：{}获取CPU核数异常", forkjoinName);
}
log.info("forkjoinName：{}获取{}个CPU耗时（纳秒）：{}", forkjoinName, threadNumber, System.nanoTime()-start);
return threadNumber;
}
}
@Data
public class FileDownload {
private int id;
/**
* 操作人姓名
*/
private String insertUser;
/**
* 文件路径
*/
private String filePath;
/**
* 文件名称
*/
private String fileName;
/**
* 状态
*/
private String status;
/**
* 状态描述
*/
private String statusRemark;
/**
* 类型
*/
private String type;
/**
* 是否加密：1:不加密；2:加密
*/
private byte isEncrypted;
/**
* 文件打开密码
*/
private String filePassword;
/**
* 操作时间
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") //取日期时使用
@DateTimeFormat(pattern = "yyyy-MM-dd")//存日期时使用
private Date insertTime;
/**
* 更新时间
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") //取日期时使用
@DateTimeFormat(pattern = "yyyy-MM-dd")//存日期时使用
private Date updateTime;
}
@Mapper
public interface FileDownloadMapper {
/**
* 存储一条信息
* @param entity
*/
void save(@Param("entity") FileDownload entity);
/**
* 通过操作人姓名分页查询列表
* @param insertUser
* @param pageStart
* @param pageSize
* @return
*/
List<FileDownload> findByInsertUser(@Param("insertUser") String insertUser, @Param("pageStart") int pageStart
, @Param("pageSize") int pageSize);
/**
* 通过操作人姓名查询总数
* @param insertUser
* @return
*/
int countByInsertUser(@Param("insertUser") String insertUser);
/**
* 通过id查询一条数据
* @param id
* @return
*/
FileDownload findOneById(@Param("id") int id);
/**
* 通过主键id更新一条信息
* @param fileDownload
* @return
*/
int updateByPrimaryKey(@Param("entity") FileDownload fileDownload);
}
public enum FileStatusEnum {
GENERATE_ING("in", "生成中"),
GENERATE_SUCCESS("success", "生成成功"),
GENERATE_FAILURE("fail", "生成失败");
private final String key;
private final String value;
FileStatusEnum(String key, String value) {
this.key = key;
this.value = value;
}
public String getKey() {
return key;
}
public String getValue() {
return value;
}
public static String getValue(String key) {
for (FileStatusEnum fileStatusEnum : values()) {
if (key.equals(fileStatusEnum.getKey())) {
return fileStatusEnum.getValue();
}
}
return null;
}
}
@Data
public class ResponseMessage implements Serializable {
private int code = 1;
private Object data;
private String message = "执行成功";
public ResponseMessage() {
}
public ResponseMessage(int code, String message) {
this.code = code;
this.message = message;
}
public ResponseMessage(int code, Object data, String message) {
this.code = code;
this.data = data;
this.message = message;
}
public static ResponseMessage faild(String message){
ResponseMessage result = new ResponseMessage();
result.setCode(0);
result.setMessage(message);
return result;
}
public static ResponseMessage success(Object data,String message){
ResponseMessage result = new ResponseMessage();
result.setMessage(message);
result.setData(data);
return result;
}
}示例
@Data
public class ExcelImportParentConfig {
@ImportExcelProperty(index = Integer.MAX_VALUE,value = "错误信息")
public String errorInfo;
}
public class MyDbDto {
}
@Data
public class MyImportExportConfig extends ExcelImportParentConfig {
//导入导出都有这个字段
@ImportExcelProperty(index = 10,value = "字段1")
@ExportExcelProperty(index = 10,value = "字段1")
private String f1;
//导入导出都有这个字段
@ImportExcelProperty(index = 20,value = "字段2")
@ExportExcelProperty(index = 20,value = "字段2")
private String f2;
//导出特有字段
@ExportExcelProperty(index = 30,value = "字段3")
private String f3;
//导入特有字段
@ImportExcelProperty(index = 30,value = "字段4")
private String f4;
}
@Service
public class Test {
@Resource
private MyExcelContent myExcelContent;
private String PATH_TO_EXCEL="test";
public ResponseMessage importExcel(String userId, MultipartFile excelFile) throws Exception {
ExcelImportConfigDto<MyImportExportConfig> configDto = new ExcelImportConfigDto(excelFile, userId,"测试导入", "", MyImportExportConfig.class,
new ExcelImportBusinessHandle<MyImportExportConfig>() {
@Override
public List<MyImportExportConfig> handleBusiness(List<MyImportExportConfig> list) {
//数据验证时，异常的数据集合
List<MyImportExportConfig> errors = new ArrayList<>();
//需要入库的数据集合
List<MyImportExportConfig> success = new ArrayList<>();
//数据验证，即把list里的数据分成异常数据和正确数据
if(CollectionUtils.isNotEmpty(success)){
//把正确数据批量入库
}
return errors;
}
});
return myExcelContent.exelImport(configDto);
}
public ResponseMessage exportExcel(String userId) {
//根据查询条件查询数据总数
Long dataCount = null;
ExcelExportConfigDto configDto = new ExcelExportConfigDto(PATH_TO_EXCEL, userId,"测试导出", MyImportExportConfig.class,
new ExcelExportBusinessHandle<MyImportExportConfig>() {
@Override
public Integer getCount() {
return dataCount.intValue();
}
@Override
public List<MyImportExportConfig> getDetailList(Integer pageIndex, Integer pageSize) {
//根据查询条件分页查询数据
List<MyDbDto> dbResults = null;
List<MyImportExportConfig> results = new ArrayList<>();
if(CollectionUtils.isNotEmpty(dbResults)){
dbResults.forEach(data->{
MyImportExportConfig config = new MyImportExportConfig();
//将数据库查询出的数据转换成需要导出的数据类型
results.add(config);
});
}
return results;
}
});
ResponseMessage results = myExcelContent.exelExport(configDto);
return results;
}
}扩展
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelEnum {
}
//上述示例，是需要先创建导入导出字段配置类，如MyImportExportConfig，但如果导入导出来源于配置文件或者数据库或者以前为枚举配置的，怎么办呢
//为了保证底层架构不变，我们可以在此基础上进行扩展
/**
* 类功能描述：将由ExcelEnum注解修饰的类的类缓存起来，
* key为由ExcelEnum注解修饰的Class
* value
* key为导入导出标识 value为该类的getExportFieldMap（或者getImportFieldMap）静态方法返回的数据构造成新的Class，该Class主要是为了导入导出而生成的
*/
@Component
@Slf4j
public class MyExcelHandleRunner implements ApplicationRunner {
public static final Map<Class<?>,Map<String,Class<?>>> EXCEL_EXPORT_IMPORT_CLASS_MAP = new HashMap<>();
//和springboot main方法所在类的package一致即可
private static final String BASE_PACKAGE = "";
private static final String RESOURCE_PATTERN = "/**/*.class";
public static final String EXPORT="export";
public static final String IMPORT="import";
@Override
public void run(ApplicationArguments args) throws Exception {
handle();
}
/**
* 为EXCEL_EXPORT_IMPORT_CLASS_MAP初始化值
* key为由ExcelEnum注解修饰的Class
* value
* key为导入导出标识 value为该类的getExportFieldMap（或者getImportFieldMap）静态方法返回的数据构造成新的Class，该Class主要是为了导入导出而生成的
*
*
* @throws Exception
*/
private void handle()throws Exception {
//使用groovy进行动态类生成
GroovyClassLoader groovyClassLoader = new GroovyClassLoader();
//获取所有被ExcelEnum注解修饰的类
List<Class<?>> allExcelEnum = getAllExcelEnum();
//遍历被ExcelEnum修饰的类
for(Class<?> enumClazz:allExcelEnum){
Map<String,Class<?>> map = new HashMap<>();
String enumName = enumClazz.getSimpleName();
//获取静态方法getExportFieldMap并执行该方法，获得一个map，key为字段属性，value为导入导出标题
Method exportMethod = enumClazz.getDeclaredMethod("getExportFieldMap");
Map<String,String> exportMap = (Map<String,String>)exportMethod.invoke(null);
//动态创建用ImportExcelProperty注解修饰的导入导出配置类
Class<?> aClass = initExport(enumName, exportMap, groovyClassLoader);
if(aClass!=null){
map.put(EXPORT, aClass);
}
Method importMethod = enumClazz.getDeclaredMethod("getImportFieldMap");
Map<String,String> importMap = (Map<String,String>)importMethod.invoke(null);
Class<?> bClass = initImport(enumName, importMap, groovyClassLoader);
if(bClass!=null){
map.put(IMPORT, bClass);
}
EXCEL_EXPORT_IMPORT_CLASS_MAP.put(enumClazz, map);
}
}
/**
* 构造导出配置类
* @param enumName
* @param exportMap
* @param groovyClassLoader
* @return
*/
private Class<?> initExport(String enumName,Map<String,String> exportMap,GroovyClassLoader groovyClassLoader){
if(exportMap==null||exportMap.isEmpty()){
return null;
}
StringBuilder stringBuilder = new StringBuilder("package com.test.pojo.excel;\n");
stringBuilder.append("import com.test.excel.ExcelImportParentConfig;\n" +
"import com.test.excel.ExportExcelProperty;\n" +
"import com.test.excel.ImportExcelProperty;\n");
stringBuilder.append("public class "+ enumName +"ExportConfig extends ExcelImportParentConfig{\n");
stringBuilder.append("\n");
int i=0;
for(Map.Entry<String,String> entry:exportMap.entrySet()){
String k = entry.getKey();
String v = entry.getValue();
stringBuilder.append("@ExportExcelProperty(index = " + i + ", value = \"" + v + "\")\n");
stringBuilder.append("private String "+k+";\n");
i++;
}
stringBuilder.append("}\n");
Class<?> clazz = groovyClassLoader.parseClass(stringBuilder.toString());
return clazz;
}
/**
* 构造导入配置类
* @param enumName
* @param importMap
* @param groovyClassLoader
* @return
*/
private Class<?> initImport(String enumName,Map<String,String> importMap,GroovyClassLoader groovyClassLoader){
if(importMap==null||importMap.isEmpty()){
return null;
}
StringBuilder stringBuilder = new StringBuilder("package com.test.pojo.excel;\n");
stringBuilder.append("import com.test.excel.ExcelImportParentConfig;\n" +
"import com.test.excel.ExportExcelProperty;\n" +
"import com.test.excel.ImportExcelProperty;\n");
stringBuilder.append("public class "+ enumName +"ImportConfig extends ExcelImportParentConfig{\n");
stringBuilder.append("\n");
int i=0;
for(Map.Entry<String,String> entry:importMap.entrySet()){
String k = entry.getKey();
String v = entry.getValue();
stringBuilder.append("@ImportExcelProperty(index = " + i + ", value = \"" + v + "\")\n");
stringBuilder.append("private String "+k+";\n");
i++;
}
stringBuilder.append("}\n");
Class<?> clazz = groovyClassLoader.parseClass(stringBuilder.toString());
return clazz;
}
/**
* 获取com.test路径下所有被ExcelEnum修饰的类
* @return
*/
private List<Class<?>> getAllExcelEnum(){
//spring工具类，可以获取指定路径下的全部类
ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
List<Class<?>> results = new ArrayList<>();
try {
String pattern = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX +
ClassUtils.convertClassNameToResourcePath(BASE_PACKAGE) + RESOURCE_PATTERN;
Resource[] resources = resourcePatternResolver.getResources(pattern);
//MetadataReader 的工厂类
MetadataReaderFactory readerfactory = new CachingMetadataReaderFactory(resourcePatternResolver);
for (Resource resource : resources) {
//用于读取类信息
MetadataReader reader = readerfactory.getMetadataReader(resource);
//扫描到的class
String classname = reader.getClassMetadata().getClassName();
Class<?> clazz = Class.forName(classname);
//判断是否有指定主解
ExcelEnum anno = clazz.getAnnotation(ExcelEnum.class);
if (anno != null) {
results.add(clazz);
}
}
} catch (IOException | ClassNotFoundException e) {
log.error("获取导入导出枚举异常",e);
}
return results;
}
}
//必须要加注解ExcelEnum，这样在spring启动的时候会被自定义的runner动态生成底层框架需要的导入导出配置类
@ExcelEnum
@Component
public class FromDbExcelConfig {
private static Map<String,String> importHeaderMaps;
private static Map<String,String> exportHeaderMaps;
@PostConstruct
public void init() {
//从数据库获取导入导出配置
importHeaderMaps = null;
exportHeaderMaps = null;
}
/**
* 为自动构建导出配置类调用
* @return
*/
public static Map<String,String> getExportFieldMap(){
return exportHeaderMaps;
}
/**
* 为自动构建导出配置类调用
* @return
*/
public static Map<String,String> getImportFieldMap(){
return importHeaderMaps;
}
}
@Service
@Slf4j
public class Test {
@Resource
private MyExcelContent myExcelContent;
private static final String PATH_TO_EXCEL="test1";
public ResponseMessage exportExcel() {
//根据查询条件查询总数
Integer dataCount = null;
//导出字段和标题
Map<String, String> headerMap = FromDbExcelConfig.getExportFieldMap();
//获取动态生成的导出配置类
Class<?> configClass = MyExcelHandleRunner.EXCEL_EXPORT_IMPORT_CLASS_MAP.get(FromDbExcelConfig.class).get(MyExcelHandleRunner.EXPORT);
ExcelExportConfigDto configDto = new ExcelExportConfigDto(PATH_TO_EXCEL, "userId","测试扩展导出", configClass,
new ExcelExportBusinessHandle() {
@Override
public Integer getCount() {
return dataCount;
}
@Override
public List getDetailList(Integer pageIndex, Integer pageSize) {
List result = new ArrayList();
try {
//分页查询
List<MyDbDto> exportList = null;
for (MyDbDto data : exportList) {
Object o = configClass.newInstance();
//类型转换
for (Map.Entry<String, String> entry : headerMap.entrySet()) {
String key = entry.getKey();
Field declaredField = configClass.getDeclaredField(key);
declaredField.setAccessible(true);
Object value = declaredField.get(data);
String columnValue = "-";
if (value != null) {
if (StringUtils.isNotBlank(value.toString())) {
columnValue = value.toString();
}
}
declaredField.set(o, columnValue);
}
result.add(o);
}
}catch (Exception e) {
log.error("导出异常", e);
}
return result;
}
});
return myExcelContent.exelExport(configDto);
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
