该博客介绍了如何设计一个通用的Excel导入导出组件，包括下载模板、上传文件、文件校验和业务处理四个关键步骤。组件支持异步处理大文件、列头匹配、错误和警告提示，以及数值格式校验。在处理过程中，遇到数据量大、文件格式不正确、列头混乱等问题时，提供了相应的解决方案。此外，还展示了具体的Java代码实现，包括临时文件管理、异步任务执行和流控策略。
文章目录
前言
提示：Excel导入导出是系统常见的功能，如果我们不希望每次Excel导入导出对文件进行解析、取值、校验数据类型。为了避免重复，就需要对这个功能进行封装，做成一个通用组件，把公共部分抽象出来，封装共同点。在使用时，只需要关注相关业务以及在此基础上进行扩展即可。
效果图：
1、导入策略：增量式更新、覆盖式更新等等。
2、导入时，返回匹配上的列头
3、导入时，日志（错误、警告等）提醒
Excel导入导出，大致步骤为：
1、下载模板（附带下拉值）
2、根据模板赋值，上传Excel文件（作为临时文件）
3、解析文件并取内容，校验数据类型、数值等
4、对文件内容进行业务处理（批量存入数据库）
下面我们考虑几个问题：
1、当数据量很大，导入时间过长，客户不能等待，那么我们该如何处理？
答：采用异步处理，完成后发送站内消息通知操作员。
可以只设定一个阈值，当数据小于某个阈值时，进行异步等待，以达到同步效果（同步即前端等待完成）。
2、客户上传时总是不严格按模板格式，模板列头顺序混乱或存在多余列或少列头，如何解决这个问题呢？
答：通过列名进行匹配取值，列名不存在则默认跳过。
3、提示信息，有些只需要警告提示，有些信息是错误不能继续往下执行，该如何处理？
答：参考日记处理即可，采用提示、警告、错误等日记级别返回，当有错误日记时，不执行业务方法。
4、数值格式校验，如：日期、数值、下拉选值等为处理Excel功能，能否统一封装，后面只考虑逻辑业务即可？
答：能，就是需要这样做，把公共部分封装起来，让后面的程序员略过技术判断，只需要关注业务逻辑。
基于以上的思考，我们设置程序的代码为：
1、下载模板：downloadTpl（ExportFileInput input）
2、上传Excel文件方法：uploadFile(MultipartFile file)
3、校验文件方法：ImportExcelResultOutput checkTpl（ImportFileInput input）
4、业务处理方法：ImportExcelResultOutput importTpl（ImportFileInput input）
备注：
1、通过“校验文件方法”，当检测到有错误信息，则弹出框阻止执行下一步。如果存在警告信息，点击确认可继续执行（第4、业务处理方法）
2、如果开启异步，完成后发站内消息通知操作员
一、下载模板：downloadTpl（ExportFileInput input）
创建临时文件并返回文件id，然后再通过文件id下载
构建临时文件
通过redis设置有效期，创建临时文件并返回文件id
// ExportExcelUtil工具类，请看Excel导入导出工具类
public String createTempMoreSheetExcel(String realName, List<ExcelSheet> sheets, long activeTime) {
ExportExcelUtil excel = new ExportExcelUtil(sheets);
return createTempFileAndCache(excel.getWb(), realName, activeTime);
}
public String createTempFileAndCache(@NonNull Workbook workbook, String realName, long activeTime) {
String fileId = Identities.objectId();
Path tmpFile;
try {
tmpFile = Files.createTempFile(null, fileId);
} catch (IOException ex) {
log.error("Excel文件创建失败[{}->{}]", ex.getClass(), ex.getLocalizedMessage());
throw BusinessException.withArgs(ErrorCode.ERR_10024, "文件创建失败");
}
try (FileOutputStream fos = new FileOutputStream(tmpFile.toAbsolutePath().toString())) {
workbook.write(fos);
String fileData = ObjectUtils.toJsonString(new TempFile(tmpFile.toAbsolutePath().toString(), realName));
stringRedisTemplate.opsForValue().set(fileId, fileData, activeTime, TimeUnit.SECONDS);
return fileId;
} catch (IOException ex) {
log.error("Excel文件创建失败[{}->{}]", ex.getClass(), ex.getLocalizedMessage());
throw BusinessException.withArgs(ErrorCode.ERR_10024, "文件创建失败");
}
}
下载文件方法
@GetApi({"/bas_common_service/download", "/website/download"})
public void fileDown(HttpServletResponse response, @RequestParam(value = "fileId") String fileId) {
FileOutput file = fileManageApp.smartFileDownload(fileId);
Downloads.file(response, file.getCompletePath(), file.getRealName());
}
// 贴出从缓存里获取文件
public Optional<FileOutput> tempFileDown(String fileId) {
if (Objects.isNull(stringRedisTemplate)) return Optional.empty();
// [filePath, fileName]
try {
String rawJson = stringRedisTemplate.opsForValue().get(fileId);
if (Strings.isNullOrEmpty(rawJson)) return Optional.empty();
TempFile tempFile = ObjectUtils.parseJson(rawJson, TempFile.class);
return Optional.of(new FileOutput(tempFile.getPath(), tempFile.getRealName()));
} catch (Exception ex) {
log.debug("临时文件读取失败, ex = {} msg = {}", ex.getClass(), ex.getLocalizedMessage());
return Optional.empty();
}
}
二、上传Excel文件方法：uploadFile(MultipartFile file)
上传文件
@PostMapping(value = {"/bas_common_service/upload_temp", "/website/upload"})
public ApiResult<?> fileUploadTmp(@RequestParam(value = "file", required = false) MultipartFile file) {
return ApiResult.SUCCESS(fileManageApp.tempFileUpload(file));
}
/**
* 上传文件, 写入临时文件夹
*
* @return 返回有activeTime s 限制的ticket
*/
public String tempFileUpload(MultipartFile file, long activeTime) {
try (InputStream is = file.getInputStream()) {
Path tmpFile = Files.createTempFile(null, ".".concat(StringUtils.substringAfterLast(file.getOriginalFilename(), ".")));
FileUtils.copyInputStreamToFile(is, tmpFile.toFile());
String fileId = Identities.objectId();
stringRedisTemplate.opsForValue().set(fileId, ObjectUtils.toJsonString(new TempFile(
tmpFile.toAbsolutePath().toString(),
file.getOriginalFilename()
)), activeTime, TimeUnit.SECONDS);
return fileId;
} catch (IOException ex) {
log.error("文件上传失败[{}->{}]", ex.getClass(), ex.getLocalizedMessage());
throw BusinessException.withArgs(ErrorCode.ERR_10024, "上传失败");
}
}
三、ImportExcelResultOutput checkTpl（ImportFileInput input）
1、文件格式只能为：.xls、.xlsx，不是则异常返回；
2、调用工具（Excel导入导出工具类）转为Java List，转换过程中记录转换错误数据，主要处理以下类型
- 日期型（各种日期格式）
- 数值型
- 下拉值转换
备注：具体实现请参考系列文章的“Excel导入导出工具类（多sheet、多表头、单元格下拉选择、根据列名匹配转为List）”
3、业务逻辑判断
- 重复列校验
- 判空（实体校验、提供字段校验）
- 业务判断（身份证、人员信息是否存在）
备注：具体如何控制，根据具体业务而定
代码如下：
@PostApi(value = "/check_tpl", name = "检查模板文件")
public ApiResult<ImportExcelResultOutput> checkTpl(@Validated @RequestBody ImportFileInput input) {
return ApiResult.SUCCESS(employeeApp.checkTpl(input));
}
//*************************以下贴出部分主要代码*****************************//
// 获取临时文件
public ExportExcelUtil getTempMoreSheetExcelFile(@NotBlank String fileKey) {
return getTempFile(fileKey, toMoreSheetExcelXlsx)
.orElseThrow(BusinessException.constraintException("文件已失效, 请重新上传"));
}
private static Function<TempFile, ExportExcelUtil> toMoreSheetExcelXlsx = file -> {
String path = file.getPath();
try (InputStream is = new BufferedInputStream(new FileInputStream(path))) {
if (StringUtils.endsWith(path, ".xlsx")) return new ExportExcelUtil(is);
else if (StringUtils.endsWith(path, ".xls")) return new ExportExcelUtil(is);
else BusinessException.throwConstraintException("模板文件格式非法, 只可以使用[.xlsx , .xls]类型的模板文件");
return null;
} catch (IOException ex) {
log.error("文件读写错误->[{}->{}]", ex.getClass(), ex.getLocalizedMessage());
String content = String.format("获取临时文件失败, 请联系管理员, 错误代码 = %s msg = %s", ex.getClass(), ex.getLocalizedMessage());
throw BusinessException.withArgs(ErrorCode.ERR_10022, content);
}
};
/**
* 加载临时区文件
*/
private <T> Optional<T> getTempFile(@NotBlank String fileKey, Function<TempFile, T> mapper) {
String filePath = stringRedisTemplate.opsForValue().get(fileKey);
if (Strings.isNullOrEmpty(filePath)) return Optional.empty();
TempFile fileInCache = ObjectUtils.parseJson(filePath, TempFile.class);
return Optional.ofNullable(mapper.apply(fileInCache));
}
四、业务处理方法：ImportExcelResultOutput importTpl（ImportFileInput input）
1、通过Redis存入锁定业务，完成后再解锁
checkAndAcquireLock(config).ifPresent(config::setTaskLock);
2、设置阈值，大于阈值是开启异步，小于等于时进行阻塞（等待结果返回）
可以参考CompletableFuture这个技术实现，具体细节请自行查找相关技术文档
主要代码如下：
CompletableFuture<TaskResult> result = task.execute(dataSource, taskContext, config);
if (dataSource.size() < config.getAsyncIfSizeOver()) {
log.debug("数据源记录[{}]条, 小于阀值[{}], 开始阻塞等待结果响应", dataSource.size(), config.getAsyncIfSizeOver());
try {
return result.get(); //阻塞
} catch (InterruptedException | ExecutionException e) {
e.printStackTrace();
throw BusinessException.withArgs(ErrorCode.ERR_10024, "任务执行失败");
}
}
return TaskResult.builder().isAsyncTask(true).build();
3、批量处理业务时限流
protected RateLimiter get10QpsLimiter() {
return RateLimiter.create(10);
}
// 在for循环里使用：
RateLimiter limiter = get10QpsLimiter();
for (int i = 0; i < dataSource.size(); i++) {
limiter.acquire();
// 具体业务实现
}
4、具体实现
任务执行器, 封装基础执行流程代码如下：
/**
* 导入导出任务执行器, 封装基础执行流程
*/
@Slf4j
@Component
public class ImportTaskExecutor {
@Resource private CacheLockService cacheLockService;
@Transactional
public TaskResult execute(BaseAsyncImportTask task, List<?> dataSource, TaskContext taskContext, TaskConfig config) {
// 上一个任务未完成，不能重复导入
checkAndAcquireLock(config).ifPresent(config::setTaskLock);
CompletableFuture<TaskResult> result = task.execute(dataSource, taskContext, config);
if (dataSource.size() < config.getAsyncIfSizeOver()) {
log.debug("数据源记录[{}]条, 小于阀值[{}], 开始阻塞等待结果响应", dataSource.size(), config.getAsyncIfSizeOver());
try {
return result.get(); //阻塞
} catch (InterruptedException | ExecutionException e) {
e.printStackTrace();
throw BusinessException.withArgs(ErrorCode.ERR_10024, "任务执行失败");
}
}
return TaskResult.builder().isAsyncTask(true).build();
}
private Optional<String> checkAndAcquireLock(TaskConfig config) {
try {
if (cacheLockService.hasLock(config.getNamespace()))
BusinessException.throwConstraintException("上一个" + config.getName() + "任务尚未完成, 请稍后重试");
final String lock = cacheLockService.acquireLock(config.getNamespace(), config.getTaskLockTime());
return Optional.of(lock);
} catch (Exception ex) {
log.warn("缓存锁检测失败, 跳过锁分配...");
return Optional.empty();
}
}
}
/**
* BaseAsyncImportTask 类内容--》
* 封装导入Excel任务流程
* 子类必须标注为 @Async
*/
@Slf4j
public abstract class BaseAsyncImportTask<T> {
@Resource private CacheLockService cacheLockService;
@Autowired(required = false) private INotificationClient notificationClient;
private void unlock(TaskConfig config) {
if (Strings.isNullOrEmpty(config.getTaskLock())) return;
try {
cacheLockService.unlock(config.getNamespace(), config.getTaskLock());
} catch (Exception ex) {
log.warn("缓存lock访问失败, 解锁失败...");
}
}
@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
public CompletableFuture<TaskResult> execute(List<T> dataSource, TaskContext context, TaskConfig config) {
final long beginTime = System.currentTimeMillis();
return doImport(dataSource, context)
.thenApply(i -> {
log.debug("任务[{}]执行结束, 耗时[{}]ms", config.getName(), System.currentTimeMillis() - beginTime);
unlock(config);
if (dataSource.size() >= config.getAsyncIfSizeOver()) {
if (Objects.isNull(notificationClient)) return i;
String message = config.getMessageProducer().apply(i);
if (!Strings.isNullOrEmpty(message) && !Strings.isNullOrEmpty(context.getOperator())) {
String defaultTitle = String.format("[%s]任务提醒", config.getName());
notificationClient.sendNotice(createAdminNotice(Topic.BATCH_TASK, context.getOperator(), defaultTitle, message));
}
}
return i;
})
.exceptionally(ex -> {
log.debug("任务[{}]异常执行结束, 耗时[{}]ms", config.getName(), System.currentTimeMillis() - beginTime);
unlock(config);
ex.printStackTrace();
List<TaskLog> logs = ImmutableList.of(TaskLog.error("系统错误, 任务执行失败, 代码[{}->{}]", ex.getClass(), ex.getLocalizedMessage()));
return TaskResult.builder().logs(logs).build();
});
}
protected RateLimiter get10QpsLimiter() {
return RateLimiter.create(10);
}
/**
* 导入任务详细
*/
public abstract CompletableFuture<TaskResult> doImport(List<T> dataSource, TaskContext context);
}
5、具体实现例子
TaskResult taskResult = importTaskExecutor.execute(importEmployeeTask, dataSource, context, ImportEmployeeTask.getTaskConfig());
// 具体导入人员信息类：
@Slf4j
@Component
public class ImportEmployeeTask extends BaseAsyncImportTask<Employee> {
@Resource private IEntityCacheFacade<Unit> unitCacheFacade;
@Resource private DepartmentRepository departmentRepository;
@Resource private EmployeeRepository employeeRepository;
@Resource private SaveEmployeeService saveEmployeeService;
public static TaskConfig getTaskConfig() {
TaskConfig taskConfig = TaskConfig.of("导入人员信息", Constants.CACHE_LOCK_NAMESPACE_IMPORT_EMPLOYEE);
taskConfig.setMessageProducer(i -> {
String messages = i.getLogs().stream()
.map(TaskLog::getContent)
.collect(joining(", "));
return "导入已完成->" + messages;
});
return taskConfig;
}
@Override
public CompletableFuture<TaskResult> doImport(List<Employee> dataSource, TaskContext context) {
Stopwatch sw = Stopwatch.createStarted();
ImportFileInput input = (ImportFileInput) context.getParameter("config");
log.debug("开始导入人员信息, 配置信息->[{}]", input);
List<TaskLog> logs = Lists.newArrayList();
logs.add(TaskLog.info("开始导入人员信息, 配置信息:[导入策略->{}]", input.getStrategy().getLabel()));
logs.addAll(importDepartments(dataSource));
logs.addAll(importEmployee(dataSource, input));
TaskResult result = TaskResult.builder()
.logs(logs)
.build();
long costTime = sw.stop().elapsed(TimeUnit.SECONDS);
logs.add(TaskLog.info("导入完成, 耗时[ {} ]秒", costTime));
return CompletableFuture.completedFuture(result);
}
/**
* 导入人员
*/
private List<TaskLog> importEmployee(List<Employee> dataSource, ImportFileInput config) {
List<TaskLog> logs = Lists.newArrayList();
int insertCount = 0;
int skipCount = 0;
int updateCount = 0;
RateLimiter limiter = get10QpsLimiter();
for (int i = 0; i < dataSource.size(); i++) {
limiter.acquire();
Employee row = dataSource.get(i);
log.trace("开始导入第[{}]个人员->[{}]", i, row);
Optional<Employee> entityOpt = employeeRepository.findByIdNum(row.getIdNum());
if (entityOpt.isPresent()) {
if (Objects.equals(config.getStrategy(), ImportStrategy.SKIP)) {
log.trace("[{}]跳过重复数据[{}]", i, row);
skipCount++;
continue;
}
log.trace("[{}]更新人员[{}]信息", i, row);
Employee entity = entityOpt.get();
if (!Objects.equals(entity.getOnUnitId(), row.getOnUnitId())) {
logs.add(TaskLog.error("无法导入第[ {} ]行, 原因->人员[{}/{}]已存在, 原有单位与导入单位不一致", i + 1, row.getName(), row.getIdNum()));
skipCount++;
continue;
}
SaveEmployeeBasicInput input = new SaveEmployeeBasicInput();
input.setEmployee(Jsons.toJsonObject(row));
saveEmployeeService.save(input);
updateCount++;
continue;
}
log.trace("[{}]新增人员[{}]", i, row);
SaveEmployeeBasicInput input = new SaveEmployeeBasicInput();
input.setEmployee(Jsons.toJsonObject(row));
saveEmployeeService.save(input);
insertCount++;
}
logs.add(TaskLog.info("成功新增人员[{}]个", insertCount));
logs.add(TaskLog.info("根据导入策略[{}], 更新了[{}]个人员, 跳过了[{}]个人员", config.getStrategy().getLabel(), updateCount, skipCount));
return logs;
}
扫一扫
1010
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
