【对EasyExcel 文件读取的封装】

@easy excel

概述

主要是对easy excel 文件读取的监听器的处理、主要是基于注解

1. 支持自定义注解验证excel单元格值是否符合业务规范
2. 支持JSR-303注解验证
3. 数据过滤
4. 非法数据提示

暂时只实现了同步等待，还不支持异步监听读取

用法：

1. action 写法：

    @ApiOperation("upload data file.")
    @PostMapping("/upload/resourceFile")
    public JsonResponse uploadResourceFile(@RequestParam("file") MultipartFile file) {

        //1. def listener,.
        ManualReClassListener defaultListener = new ManualReClassListener();
        //2. read excel
        EasyExcelUtil.readFile(file, ManualReClassExcelTO.class, defaultListener,2);
        //3. mapping result.
        return Optional.of(defaultListener)
                .filter(ManualReClassListener::hasError)
                .map(ManualReClassListener::getResult)
                .map(Tuple2::getSecond)
                .map(String::valueOf)
                .map(this::error)
                .orElseGet(() -> success(defaultListener.getResult().getFirst()));
    }

2. 实体类声明：

@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class ManualReClassExcelTO {

    @JsonProperty("No.")
    private String no;

    @NotBlank
    @ExcelProperty(value = "Company Code")
    private String companyCode;

    @NotBlank
    @ExcelProperty(value = "Fiscal Year")
    private String fiscalYear;

    @NotBlank
    @ExcelProperty(value = "GL Account")
    private String glAccount;

    @NotBlank
    @ExcelProperty(value = "Profit Center")
    private String profitCenter;

    @ExcelProperty(value = "Cost Center")
    private String costCenter;

    @NotBlank
    @ExcelProperty(value = "Trans Currency")
    private String transCurrency;

    @Number
    @NotBlank
    @ExcelProperty(value = "Trans Amount")
    private String transAmount;

    public ManualReClassExcelTO fillNo(String no) {
        this.no = no;
        return this;
    }
}

3. 监听器定义

这个监听器是当初组里leader (gaobl) 非要这么写，这个实现的多线程将计数器置零的操作 肯定有问题
原来的做法是基于 redis 的 cas 机制，以及quarz的定时任务
即：

1. 声明一个job，配合cron 表达式，每个月或每天执行一次任务
2. 每个job都去尝试将计数器设置为初始值，但是通过cas设置，且自增同样是通过原子自增设置
3. cas赋值= 通过带有时间戳的cas 防止aba问题
4. 此cas 非 juc下提供的cas 工具类

/**
 * @author w
 * @version 1.0
 */
@SuppressWarnings("ConstantConditions")
@Slf4j
public class ManualReClassListener extends AbstractAnalysisEventListener<ManualReClassExcelTO> {

    private final static String MANUAL_RE_CLASS_SEQ = "BILLING_MANUAL_RE_CLASS_SEQ";

    private final static Long DEFAULT_COUNTER_NUMBER = 1L;

    @SuppressWarnings("unchecked")
    private final RedisTemplate<String, Integer> redisClient = SpringContextHolder.getBean(RedisTemplate.class);

    /**
     * filer data. and fill No.
     */
    @Override
    protected List<ManualReClassExcelTO> saveAll(List<ManualReClassExcelTO> data) {
        return data.stream()
                .filter(x -> StringUtils.isBlank(x.getNo()))
                .map(this::fillNo)
                .collect(Collectors.toList());
    }

    // fill No.
    @SneakyThrows
    public ManualReClassExcelTO fillNo(ManualReClassExcelTO to) {
        Integer integer = redisClient.opsForValue().get(MANUAL_RE_CLASS_SEQ);
        Long next = Optional.ofNullable(integer)
                .map(unused -> redisClient.opsForValue().increment(MANUAL_RE_CLASS_SEQ, DEFAULT_COUNTER_NUMBER))
                .orElseGet(() -> {
                    redisClient.opsForValue().set(MANUAL_RE_CLASS_SEQ, DEFAULT_COUNTER_NUMBER.intValue());
                    redisClient.expire(MANUAL_RE_CLASS_SEQ, duration(), TimeUnit.SECONDS);
                    // maybe a problem
                    log.info("reset manual re class No. and successful 🙂");
                    return DEFAULT_COUNTER_NUMBER;
                });
        return to.fillNo(String.format("RC_%s_%s%s",
                to.getCompanyCode(),
                DateUtil.dateFormat(new Date(), "yyyyMM"),
                String.format("%04d", next)
        ));
    }

    @Override
    Boolean saveData(List<ManualReClassExcelTO> data) {
        return Boolean.TRUE;
    }

    // design by:
    // cal duration from now to next month ,unit seconds
    private long duration() {
        LocalDateTime next = LocalDateTime.now().with(TemporalAdjusters.firstDayOfNextMonth());
        LocalDateTime time = LocalDateTime.of(next.getYear(), next.getMonth(), next.getDayOfMonth(), 0, 0, 0, 0);
        return ChronoUnit.SECONDS.between(LocalDateTime.now(), time);
    }

}

4. 默认的监听器

@SuppressWarnings("UnusedReturnValue")
@Slf4j
public abstract class AbstractAnalysisEventListener<T> extends AnalysisEventListener<T> {


    // TODO: consider to use a LinkedList?
    public ArrayList<T> list = new ArrayList<>();

    public ArrayList<T> allList = new ArrayList<>(1024);

    /**
     * hold error data,
     */
    public ArrayList<T> errorData;

    protected String errorMessage;

    Tuple2<ArrayList<T>, Object> result;

    private final CountDownLatch f = new CountDownLatch(1);

    /**
     * for: batch opera signal
     */
    protected Integer threshold = 1000;

    /**
     * TODO: waiting for fix: #1611
     */
    private final AtomicInteger counter = new AtomicInteger(1);

    /**
     * map easyExcel's headNumber.
     */
    private Integer skipNumber = -1;

    /**
     * consumer validator delegator.
     */
    private final DelegateValidator validate = SpringContextHolder.getBean(DelegateValidator.class);




    @Override
    public void invoke(T data, AnalysisContext context) {

        if (skipNumber > 0 && counter.getAndIncrement() < skipNumber)
            return;

        log.debug("parse data:{}", JSON.toJSONString(data));
        try {
            if (StringUtils.isNotBlank(preValidate(data)))
                throw new ExcelListenerException(errorMessage);
        } catch (NoSuchFieldException e) {
            f.countDown();
            throw new ExcelListenerException(e.getMessage());
        }
        list.add(data);
        Optional.of(list)
                .filter(unused -> unused.size() > threshold)
                .filter(this::saveData0)
                .ifPresent(List::clear);
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        doAfterAllAnalysed0();
    }

    protected final void doAfterAllAnalysed0() {

        Optional.ofNullable(list)
                .filter(CollectionUtils::isNotEmpty)
                .map(allList::addAll);

        saveAll(allList);

        log.info("task finished!");

        Optional.ofNullable(errorMessage)
                .filter(StringUtils::isNotBlank)
                .map(unused -> result = new Tuple2<>(errorData, errorMessage))
                .orElseGet(() -> result = new Tuple2<>(newArrayList(allList), null));
        bothClear();
        f.countDown();
    }

    /**
     * @return default return all data.
     */
    protected final ArrayList<T> data() {
        try {
            f.await();
            return allList;
        } catch (Exception e) {
            errorMessage = e.getMessage();
        }
        return newArrayList();
    }

    public Tuple2<ArrayList<T>, Object> getResult() {
        try {
            f.await();
            return result;
        } catch (Exception e) {
            errorMessage = e.getMessage();
        }
        return result;
    }

    public Boolean hasError() {
        return StringUtils.isNotBlank(errorMessage);
    }


    private void bothClear() {
        list.clear();
        allList.clear();
    }

    /**
     * 处理单词的批量操作..
     */
    private Boolean saveData0(List<T> data) {
        Optional.ofNullable(data)
                .filter(CollectionUtils::isNotEmpty)
                .map(allList::addAll);

        return saveData(data);
    }

    protected List<T> saveAll(List<T> data) {
        // nop
        return allList;
    }

    /**
     * if save data success, should return true, signal data had persistence.
     * then this component will clear the {@link AbstractAnalysisEventListener#list}
     * and when ret false, nothing to do.
     */
    abstract Boolean saveData(List<T> data);


    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        result = new Tuple2<>(null, errorMessage = exception.getMessage());
        if (!f.await(20, TimeUnit.MILLISECONDS))
            f.countDown();
        throw exception;
    }


    /**
     * pre validate
     * @param obj will validate
     */
    public String preValidate(T obj) throws NoSuchFieldException {
        requireNonNull(obj);
        // 1. consumer.
        List<Tuple2<Boolean, String>> tuple2List = requireNonNull(validate).validateConsumerAnnotation(obj);
        if (CollectionUtils.isNotEmpty(tuple2List))
            return errorMessage = tuple2List
                    .parallelStream()
                    .map(Tuple2::getSecond)
                    .findFirst()
                    .orElse(null);
        // 2. jsr 303
        Validator validator = SpringContextHolder.getBean(Validator.class);
        StringBuilder result = new StringBuilder();
        Set<ConstraintViolation<T>> set = requireNonNull(validator).validate(obj, Default.class);

        if (CollectionUtils.isNotEmpty(set))
            for (ConstraintViolation<T> cv : set) {
                Field declaredField = obj.getClass().getDeclaredField(cv.getPropertyPath().toString());
                ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
                result.append(annotation.value()[0]).append(cv.getMessage()).append(";");
            }
        return isNotBlank(errorMessage) ? errorMessage : (errorMessage = result.toString());
    }

    public void setSkipNumber(Integer integer) {
        this.skipNumber = integer;
    }

}

5. 自定义注解的校验器

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import groovy.lang.Tuple2;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ReflectionUtils;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.*;


/**
 * A delegate for validate
 *
 * @author w
 * @version 1.0
 */
@Slf4j
@Component
public class DelegateValidator {

    private final Set<ConsumerValidator> validators;

    private final static Map<String, List<Holder>> ENTITY_FILED_ANNOTATIONS = Maps.newConcurrentMap();

    public DelegateValidator(ObjectProvider<List<ConsumerValidator>> vs) {
        validators = Sets.newHashSet(vs.getIfAvailable());
    }

    public List<Tuple2<Boolean, String>> validateConsumerAnnotation(Object obj) {

        List<Tuple2<Boolean, String>> ts = Lists.newArrayList();
        List<Holder> cacheHolder = getCacheHolder(obj);
        if (CollectionUtils.isEmpty(cacheHolder))
            return null;
        cacheHolder.forEach(f -> {
            try {
                Field field = obj.getClass().getDeclaredField(f.getFiledName());
                ReflectionUtils.makeAccessible(field);
                Object value = field.get(obj);
                validators.stream()
                        .filter(validator -> validator.support(f.getAnnotations()))
                        .filter(validator -> !validator.validate(value))
                        .map(unused -> new Tuple2<>(false, "[" + f.getFiledName() + "] = [" + value + "] validate failure"))
                        .findFirst().ifPresent(ts::add);
            } catch (Exception e) {
                log.error(e.getMessage());
            }
        });
        return ts;
    }

    // cache and get object's metadata
    List<Holder> getCacheHolder(Object obj) {
        String objKey = obj.getClass().getName();
        List<Holder> holders = ENTITY_FILED_ANNOTATIONS.get(objKey);
        if (Objects.isNull(holders))
            //noinspection SynchronizationOnGetClass
            synchronized (obj.getClass()) {
                holders = ENTITY_FILED_ANNOTATIONS.get(objKey);
                if (Objects.isNull(holders)) {
                    Field[] fs = obj.getClass().getDeclaredFields();
                    holders = Lists.newLinkedList();
                    for (Field f : fs) {
                        ReflectionUtils.makeAccessible(f);
                        try {
                            Set<? extends Class<? extends Annotation>> as = Arrays.stream(f.getAnnotations())
                                    .map(Annotation::annotationType)
                                    .collect(LinkedHashSet::new, Set::add, Set::addAll);
                            holders.add(new Holder(f.getName(), as));
                        } catch (Exception ex) {
                            log.error(ex.getMessage());
                        }
                    }
                }
                ENTITY_FILED_ANNOTATIONS.put(objKey, holders);
            }
        return holders;
    }

    @Getter
    @AllArgsConstructor
    static class Holder {
        String filedName;
        Set<? extends Class<? extends Annotation>> annotations;
    }
}

6. 自定义注解验证器的规范接口定义

import java.lang.annotation.Annotation;
import java.util.Set;

/**
 * @author w
 * @version 1.0
 */
public interface ConsumerValidator {

    boolean support(Set<? extends Class<? extends Annotation>> acs);

    boolean validate(Object value);
}

7. 自定义注解的验证器

这些验证其必须是 spring的一个bean

import org.springframework.stereotype.Component;

import java.lang.annotation.Annotation;
import java.util.Set;
import java.util.regex.Pattern;

/**
 * @author w
 * @version 1.0
 */
@Component
public class NumberValidator implements ConsumerValidator {
    private final static Pattern pattern = Pattern.compile("^[-+]?[\\d]+[.]?[\\d]*$");

    @Override
    public boolean support(Set<? extends Class<? extends Annotation>> acs) {
        return acs.contains(Number.class);
    }

    @Override
    public boolean validate(Object value) {
        if (value instanceof String)
            return pattern.matcher((String) value).matches();
        else
            return true;
    }
}

8. 自定义注解：

import java.lang.annotation.*;

/**
 * @author w
 * @version 1.0
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Number {
}

9. 异常定义

@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class ExcelListenerException extends ExcelAnalysisException {


    public ExcelListenerException(String message) {
        super(message);
    }
}

10. excel 工具类 调用

这一块其实可以不写，就是为调用做一些简单的数据组装而已，

    public static void readFile(MultipartFile file,
                                final Class clazz,
                                final AbstractAnalysisEventListener listener) {
        readFile(file, clazz, listener, 1);
    }

    @SneakyThrows
    public static void readFile(MultipartFile file,
                                final Class clazz,
                                final AbstractAnalysisEventListener listener,
                                final int headRowNumber) {
        // TODO: fixed #1611
        listener.setSkipNumber(headRowNumber);
        EasyExcel.read(file.getInputStream(), clazz, listener)
                .sheet()
                .doRead();
    }

11. 全局异常捕获

    @ResponseBody
    @ExceptionHandler(ExcelListenerException.class)
    public JsonResponse ExcelListenerExceptionHandler(ExcelListenerException e){
        return error(e.getMessage());
    }

End