1.自定义导入注解
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.FIELD})
@Documented
public @interface FieldDispose {
/**
* 解析类型
*/
ExcelFieldType type() default ExcelFieldType.NORMAL;
/**
*绑定的数据，类型dict对应字典(传入的map),其他类型对应实体类字段
*/
String label();
/**
* 字段不存在默认值
*/
String defaultValue() default "";
/**
* 错误提示
*/
String errorMessage() default "";
/**
*仅拼接下使用，拼接间隔符，数组第几位即代表第几位后添加
*/
String[] mark() default {};
}
2.导入实体类对象
@Data
public class GoodsImportVo {
@ExcelProperty("*商品分类编码")
@FieldDispose(label = "商品分类编码")
private String goodsCategory;
@ExcelProperty("*商品编码")
@FieldDispose(label = "商品编码")
private String goodsCode;
@ExcelProperty("*B端商品名称")
@FieldDispose(label = "*B端商品名称")
private String bGoodsName;
@ExcelProperty("*C端商品名称")
@FieldDispose(label = "*C端商品名称")
private String cGoodsName;
@ExcelProperty("保质期")
@FieldDispose(label = "保质期",defaultValue = "12")
private Integer shelfLife;
//省略字段......
}
3.导入解析器
@Slf4j
@Getter
public class CommonListener<T> extends AnalysisEventListener<T> {
/**
* 执行的插入的service
*/
private final Consumer<List<T>> p;
/**
* 字典
*/
private final Map<String, Map<String,Object>> itemMap;
/**
* 中间类的字段
*/
Field[] fields ;
/**
* 初始化参数，server 执行
* @param itemMap 字典数据,从里面去对应的数据
* @param p 执行方法
*/
public CommonListener(Map<String,Map<String,Object>> itemMap,Class<T> tClass, Consumer<List<T>> p) {
this.p = p;
this.itemMap = itemMap;
this.fields = tClass.getDeclaredFields();
}
/**
* 批量插入数据的大小
*/
private final int BATCH_COUNT = 1000;
/**
* 存储数据的集合
*/
private final List<T> rows = new ArrayList<>();
/**
* 存储数据的集合
*/
private final Map<String, Set<String>> unique = new HashMap<>();
/**
* 存放失败的数据及原因
*/
private final List<String> msgList = new ArrayList<>();
@Override
public void onException(Exception exception, AnalysisContext context) {
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
msgList.add(String.format("第%s行，第%s列解析异常，数据为:%s",excelDataConvertException.getRowIndex(),
excelDataConvertException.getColumnIndex(),excelDataConvertException.getCellData()));
}
}
@Override
public void invoke(T data, AnalysisContext context) {
ReadRowHolder readRowHolder = context.readRowHolder();
try {
for ( Field field : fields ) {
//得到属性值
field.setAccessible(true);
String value = (String) field.get(data);
String type = field.getGenericType().toString();
if(field.isAnnotationPresent(FieldDispose.class)){
//获取字段注解
FieldDispose annotation = field.getAnnotation(FieldDispose.class);
switch (annotation.type()){
//字典替换
case DICT:
if(!itemMap.get(annotation.label()).containsKey(value)){
msgList.add(String.format("第%s行异常，原因%s，数据为:%s",readRowHolder.getRowIndex()+1,annotation.errorMessage(),value));
return;
}
field.set(data, itemMap.get(annotation.label()).get(value));
break;
//唯一值判断
case UNIQUE:
if(unique.containsKey(annotation.label())){
if(unique.get(annotation.label()).contains(value)){
msgList.add(String.format("第%s行异常，原因%s，数据为:%s",readRowHolder.getRowIndex()+1,annotation.errorMessage(),value));
return;
}
}else {
Set<String> m = new HashSet<>();
m.add(value);
unique.put(annotation.label(),m);
}
break;
default:
if (Objects.isNull(value)){
String defaultValue = annotation.defaultValue();
if ("class java.lang.String".equals(type)) { //如果type是类类型，则前面包含"class "，后面跟类名
try {
if (StrUtil.isNotBlank(defaultValue)){
field.set(data, defaultValue);
}
} catch (Exception e) {
e.printStackTrace();
}
}else if ("class java.math.BigDecimal".equals(type)) { //如果type是类类型，则前面包含"class "，后面跟类名
try {
if (StrUtil.isNotBlank(defaultValue)){
field.set(data, new BigDecimal(defaultValue));
}
} catch (Exception e) {
e.printStackTrace();
}
}else if ("class java.lang.Integer".equals(type)) { //如果type是类类型，则前面包含"class "，后面跟类名
try {
if (StrUtil.isNotBlank(defaultValue)){
field.set(data, Integer.valueOf(defaultValue) );
}
} catch (Exception e) {
e.printStackTrace();
}
}else if ("class java.lang.Double".equals(type)) { //如果type是类类型，则前面包含"class "，后面跟类名
try {
if (StrUtil.isNotBlank(defaultValue)){
field.set(data, Double.valueOf(defaultValue) );
}
} catch (Exception e) {
e.printStackTrace();
}
}else if ("class java.lang.Long".equals(type)) { //如果type是类类型，则前面包含"class "，后面跟类名
try {
if (StrUtil.isNotBlank(defaultValue)){
field.set(data, Long.valueOf(defaultValue) );
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
break;
}
}
}
rows.add(data);
if(rows.size()>=BATCH_COUNT){
this.saveBatch();
}
}catch (Exception e){
msgList.add(String.format("第%s行异常，原因:%s",readRowHolder.getRowIndex()+1,e.getMessage()));
}
}
/**
* 数据解析完成执行
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if(rows.size()>0){
this.saveBatch();
}
}
private void saveBatch(){
try {
p.accept(rows);
}catch (Exception e){
rows.clear();
msgList.add(e.getMessage());
}
}
}
4.实际使用
@Slf4j
@Service
@RequiredArgsConstructor
public class GoodsServiceImpl extends ServiceImpl<GoodsMapper, Goods> implements GoodsService {
@Resource
private SqlSessionTemplate sqlSessionTemplate;
@Resource
private goodsCategoryMapper goodsCategoryMapper;
@Override
public R importData(MultipartFile file) throws IOException {
//解析器
CommonListener<GoodsImportVo> commonListener = new CommonListener<>(null, GoodsImportVo.class,this::excelBatch);
EasyExcel.read(file.getInputStream())
.head(GoodsImportVo.class)
.registerReadListener(commonListener)
.sheet(0)//读第几个工作表，从0开始
.headRowNumber(1)
.doRead();
List<String> msgList = commonListener.getMsgList();
if(msgList.size()>0){
return R.failed(StringUtils.join(msgList,","));
}
return R.ok();
}
public void excelBatch(List<GoodsImportVo> list) {
//数据处理及批量添加
List<String> categories = list.stream().map(GoodsImportVo::getGoodsCategory).distinct().collect(Collectors.toList());
List<String> categoryList = goodsCategoryMapper.selectList(Wrappers.<GoodsCategory>query().lambda().in(GoodsCategory::getGoodsCategory,categories)).stream().map(GoodsCategory::getGoodsCategory).collect(Collectors.toList());;
if (!CollectionUtils.isEmpty(categoryList)){
list = list.stream().filter(v -> categoryList.contains(v.getGoodsCategory())).collect(Collectors.toList());
List<String> codes = list.stream().map(GoodsImportVo::getGoodsCode).collect(Collectors.toList());
List<Goods> repeatData = baseMapper.selectList((Wrappers.<Goods>query().lambda().in(Goods::getGoodsCode, codes)));
if(repeatData.size()>0){
log.info("=====数据库已存在数据,正在过滤...====");
List<String> repeatCodes = repeatData.stream().map(Goods::getGoodsCode).collect(Collectors.toList());
list = list.stream().filter(v -> !repeatCodes.contains(v.getGoodsCode())).collect(Collectors.toList());
}
if (!CollectionUtils.isEmpty(list)){
//如果自动提交设置为true,将无法控制提交的条数，改为最后统一提交
SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, true);
GoodsAttributeMapper goodsAttributeMapper = session.getMapper(GoodsAttributeMapper.class);
GoodsSpecialMapper goodsSpecialMapper = session.getMapper(GoodsSpecialMapper.class);
List<Goods> goodsList = new ArrayList<>(list.size());
List<GoodsSpecial> specials = new ArrayList<>(list.size());
List<GoodsAttribute> attributes = new ArrayList<>(list.size());
for (GoodsImportVo goodsImportVo : list) {
Goods goods = new Goods();
if (Objects.nonNull(goodsBrand)){
goodsImportVo.setGoodsBrand(goodsBrand.getId());
}
BeanUtils.copyProperties(goodsImportVo, goods);
goodsList.add(goods);
}
baseMapper.insertBatchSomeColumn(goodsList);
for (Goods goods : goodsList) {
Long goodsId = goods.getId();
GoodsSpecial goodsSpecial = new GoodsSpecial();
goodsSpecial.setGoodsId(goodsId);
goodsSpecial.setDelFlag("0");
goodsSpecial.setBMinimumQuantity(1);
goodsSpecial.setCMinimumQuantity(1);
specials.add(goodsSpecial);
GoodsAttribute attribute = new GoodsAttribute();
attribute.setGoodsId(goodsId);
attribute.setDelFlag("0");
attribute.setBIntroduction("");
attribute.setCIntroduction("");
attributes.add(attribute);
}
//insertBatchSomeColumn方法mybatis不直接支持，自行百度
goodsAttributeMapper.insertBatchSomeColumn(attributes);
goodsSpecialMapper.insertBatchSomeColumn(specials);
session.commit();
String key = String.format("%s::%s",CacheConstants.PARAMS_DETAILS, CacheConstants.SYNC_GOODS_IMPORT);
String sync= RedisUtils.get(key, String.class);
log.info("-----------同步第三方配置是否开启：{}-----------",sync);
if (Objects.equals(sync,"1")){
log.info("-----------同步第三方商品-----------");
//耗时多，最好改为异步
syncGoods(goodsList);
}
}
return;
}
throw new BaseException(ResponseEnum.GOODS_CATEGORY_NOT_EXIST.getMsg());
}
}
302
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
