-
导入前验证数据
1、创建通用EasyExcel监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import java.util.Map;
/**
* easyExcel通用监听
* @author: HouGJ
* @date: 2023/5/12 15:02
* @Description: CommonExcelListener
*/
public class CommonEasyExcelListener<T> implements ReadListener<T> {
private T t;
public CommonEasyExcelListener(T t) {
this.t = t;
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
ReadListener.super.onException(exception, context);
}
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
ReadListener.super.invokeHead(headMap, context);
}
@Override
public void invoke(T data, AnalysisContext context) {
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
ReadListener.super.extra(extra, context);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
@Override
public boolean hasNext(AnalysisContext context) {
return ReadListener.super.hasNext(context);
}
}
2.使用EasyExcel读取到文件里的数据
doReadSync()这个方法可以同步返回文档的结果
其中 PartBillVO是对应文件字段的实体类,类里的的字段用了EasyExcel的注解。
具体可以去EasyExcel官网查看官方文档。
List<PartBillVO> list = EasyExcel
.read(file.getInputStream(), PartBillVO.class, new CommonEasyExcelListener(new PartBillVO()))
.sheet()
.doReadSync();
/**
* @author: HouGJ
* @date: 2023/5/22 13:23
* @Description: PartBill
*/
@Data
public class PartBillVO {
/**
* id
*/
@ExcelIgnore // 导入、导出时忽略该字段
private Long id;
/**
* 编号
*/
@ExcelProperty(value = "备件编号")
private String code;
@ExcelProperty(value = "备注")
private String remarks;
}
3.对导入数据进行操作
/**
* 导入验证
*
* @param list 列表
* @return {@link Object}
*/
@Override
public ExportMsgVO verifyImportData(List<PartOutVO> list) {
int i = 0;
StringBuilder sb = new StringBuilder();
// 保存重复结果
List<PartOut> temporaryList = Lists.newArrayList();
// 保存成功结果集
List<PartOut> successList = Lists.newArrayList();
for (PartOutVO vo : list) {
i++;
// 验证数据(这里写你自己的验证方法)
String msg = PartOutUtils.verifyData(i, vo);
if (StringUtils.isNotBlank(msg)) {
sb.append(msg);
continue;
}
// 是否重复(这里写你自己的防止重复验证方法)
PartOut exist = this.exist(vo.getCode(), vo.getContent(), null, Boolean.FALSE);
if (Objects.nonNull(exist)) {
temporaryList.add(exist);
// 跳过本次循环
continue;
}
// 保存新增的数据
PartOut entity = new PartOut();
BeanUtil.copyProperties(vo, entity);
successList.add(entity);
}
// 返回结果集
ImportDataUtils importDataUtils = new ImportDataUtils(redisTemplate);
ExportMsgVO exportMsgVO = importDataUtils.resultExportMsgVO(
sb,
ExcelConstant.PART_OUT_IMPORT_SUCCESS,
successList,
ExcelConstant.PART_OUT_IMPORT_TEMPORARY,
temporaryList);
return exportMsgVO;
}
PartOutUtils.verifyData
/**
* @author: HouGJ
* @date: 2023/5/23 17:42
* @Description: PartBuyUtils
*/
public class PartOutUtils {
/**
* 验证数据
*
* @param i 行号
* @param vo 导入的数据vo
* @return {@link String}
*/
public static String verifyData(int i, PartOutVO vo) {
if(StringUtils.isBlank(vo.getCode())){
return "第" + i + "行出库单号不能为空;";
}
if(StringUtils.isBlank(vo.getContent())){
return "第" + i + "行内容不能为空;";
}
if(StringUtils.isNotBlank(vo.getOutTypeName())){
PartOutTypeEnum partOutTypeEnum = BaseEnum.fromInit(PartOutTypeEnum.class, vo.getOutTypeName());
if(Objects.isNull(partOutTypeEnum)){
return "第" + i + "行出库类型数据不规范;";
}else{
vo.setOutType(partOutTypeEnum.getKey());
}
}
return null;
}
}
返回的结果集 importDataUtils.resultExportMsgVO()
/**
* @author: HouGJ
* @date: 2023/5/23 18:14
* @Description: ImportDataUtils
*/
public class ImportDataUtils {
@Resource
private final RedisTemplate redisTemplate;
public ImportDataUtils(RedisTemplate redisTemplate) {
this.redisTemplate = redisTemplate;
}
/**
* 结果导出消息签证官
*
* @param sb 消息集合
* @param successKey 成功Key
* @param successList 成功列表
* @param temporary 重复key
* @param temporaryList 重复表
* @return {@link ExportMsgVO}
*/
public <T> ExportMsgVO resultExportMsgVO(StringBuilder sb, String successKey, List<T> successList, String temporary, List<T> temporaryList){
// 获取正确数据与重复数据最终结果集
ExportMsgVO exportMsgVO = new ExportMsgVO();
// 是否存在重复数据
boolean temporaryFlag = CollectionUtils.isNotEmpty(temporaryList);
// 是否有新增数据
boolean successFlag = CollectionUtils.isNotEmpty(successList);
// 缓存重复数据
if (temporaryFlag) {
String temporaryUUID = UUID.fastUUID().toString();
redisTemplate.opsForValue().set(temporary.concat(temporaryUUID), temporaryList, 1, TimeUnit.DAYS);
exportMsgVO.setTemporaryUUID(temporaryUUID);
}
// 缓存新增数据
if (successFlag) {
String successUUID = UUID.fastUUID().toString();
redisTemplate.opsForValue().set(successKey.concat(successUUID), successList, 1, TimeUnit.DAYS);
exportMsgVO.setSuccessUUID(successUUID);
}
exportMsgVO.setMsg(sb.toString());
exportMsgVO.setSuccess(StringUtils.isBlank(sb.toString()));
exportMsgVO.setTemporary(temporaryFlag);
return exportMsgVO;
}
}
-
导入数据
/** * 导入数据 * * @param successUUID 成功uuid * @param temporaryUUID 临时uuid * @return {@link R}<{@link Boolean}> */ @GetMapping("/importData") public R<Boolean> importData(String successUUID, String temporaryUUID){ return R.ok(partOutService.importData(successUUID, temporaryUUID, getLoggedUserInfo())); }
读取我们验证时返回的数据集id
/** * 导入数据 * * @param successUUID 成功uuid * @param temporaryUUID 临时uuid * @param loggedUserInfo * @return {@link Boolean} */ @Override @GlobalTransactional(rollbackFor = Exception.class) public Boolean importData(String successUUID, String temporaryUUID, LoggedUserInfo loggedUserInfo) { ImportDataUtils importDataUtils = new ImportDataUtils(redisTemplate); // 获取存储到redis里的数据 List<PartOut> importData = importDataUtils.getImportData(PartOut.class, ExcelConstant.PART_OUT_IMPORT_SUCCESS, successUUID, ExcelConstant.PART_OUT_IMPORT_TEMPORARY, temporaryUUID); importData.forEach( e ->{ if(Objects.nonNull(e.getId())){ CommonUtil.mergeUserInfo(e, loggedUserInfo, CommonUtil.MERGE_TYPE_UPDATE); }else{ CommonUtil.mergeUserInfo(e, loggedUserInfo, CommonUtil.MERGE_TYPE_INSERT); } }); // 重新获取代理对象 防止事务失效 PartOutServiceImpl aop = (PartOutServiceImpl) AopContext.currentProxy(); boolean flag = aop.saveOrUpdateBatch(importData); return flag;
importDataUtils.getImportData 实现
/** * 导入数据 * * @param successUUID 成功uuid * @param temporaryUUID 临时uuid * @return {@link List}<{@link T}> */ public <T> List<T> getImportData(Class<T> clazz, String successKey, String successUUID, String temporaryKey, String temporaryUUID){ List<T> allList = Lists.newArrayList(); successKey = successKey.concat(successUUID); temporaryKey = temporaryKey.concat(temporaryUUID); // 获取正确数据 if (StringUtils.isNotBlank(successUUID)) { List<T> successList = (List<T>) redisTemplate .opsForValue() .get(successKey.concat(successUUID)); allList.addAll(successList); redisTemplate.delete(successKey); } // 获取重复数据 if (StringUtils.isNotBlank(temporaryUUID)) { List<T> temporaryList = (List<T>) redisTemplate .opsForValue() .get(temporaryKey.concat(temporaryUUID)); allList.addAll(temporaryList); redisTemplate.delete(temporaryKey); } return allList; }