使用方法

public ImportResultResponseVo importExcel(MultipartFile file) {
    File tempFile = null;
    try {
        tempFile = File.createTempFile(String.valueOf(UUID.randomUUID()), file.getOriginalFilename());
        file.transferTo(tempFile);

        ExcelHelper<RoadExcelVo> helper = new ExcelHelper<>(ROAD_SHEET_NAME, tempFile, RoadExcelVo.class);

        List<BaseExcelData<RoadExcelVo>> excelDataList = helper.readExcel();
        Boolean b1 = helper.checkDuplicateData(RoadExcelVo::getRoadId, RoadExcelVo::getRoadName);
        Boolean b2 = helper.checkDataFormat(validator);
        Boolean b3 = helper.checkDataInDb(RoadExcelVo::getRoadName, roadName -> roadMapper.checkRoadNameExist(roadName));
        Boolean b4 = helper.checkDataInDb(RoadExcelVo::getRoadId, roadName -> roadMapper.checkRoadIdExist(roadName));
        ImportResultResponseVo responseVo = helper.getResponseVo();

        if (b1 || b2 || b3 || b4 || responseVo.getFailCount() > 0) {
            return responseVo;
        }

        UserDetail userDetail = UserContext.userDetail();
        String creator = userDetail == null ? "" : (StringUtils.isNotBlank(userDetail.getUserName()) ? userDetail.getUserName() : userDetail.getRealName());

        helper.convertAndInsert(
                // convert
                (e) -> {
                    Road road = new Road();
                    BeanUtils.copyProperties(e.getExcelVo(), road);
                    road.setRoadNo(snowflake.nextIdStr());
                    road.setCreator(creator);

                    initRoadRangeAndLen(road);
                    return road;
                },
                // check data
                dataInset -> {
                    Set<String> checkAreaNos = dataInset.stream().map(Road::getAreaNo).collect(Collectors.toSet());
                    Set<String> existAreaNos = areaMapper.selectByAreaNos(checkAreaNos);
                    if (existAreaNos.size() != checkAreaNos.size()) {
                        throw DEVICE_AREA_ERROR_0004.buildException();
                    }
                    existAreaNos.forEach(areaMapper::increaseRef);
                },
                // batch insert
                roads -> roadMapper.batchInsert(roads)
        );

        return responseVo;
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        return new ImportResultResponseVo();
    } finally {
        FileUtils.deleteQuietly(tempFile);
    }

}

util

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.xxx.iag.v2x.device.ImportError;
import com.xxx.iag.v2x.device.ImportResultResponseVo;
import com.xxx.iag.v2x.util.hutool.core.ArrayUtil;
import com.xxx.iag.v2x.util.hutool.reflect.ReflectUtil;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.ListUtils;

import javax.validation.ConstraintViolation;
import javax.validation.Validator;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.lang.invoke.SerializedLambda;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.nio.file.Files;
import java.util.*;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.function.Predicate;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static com.xxx.iag.v2x.device.info.constant.Constant.BATCH_INSERT_SIZE;


@Slf4j
public class ExcelHelper<T> {
    private static final String XLSX_ROW_ERROR_INFO = "%s 第%s行 %s 导入错误";
    private static final String DB_CHECK_TEMPLATE = "导入失败，数据库数据校验失败 %s";
    public static final String EXCEL_SAME_MSG_TEMPLATE = "第%s行 %s 相同,请删除表格中相同 %s 再导入";

    private final String sheetName;
    private final InputStream inputStream;
    private final Class<T> c;
    @Getter
    private ImportResultResponseVo responseVo;
    @Getter
    private List<BaseExcelData<T>> excelDataList;
    private final Map<String, String> excelPropertyMap;


    public ExcelHelper(String sheetName, File file, Class<T> c) throws IOException {
        this(sheetName, Files.newInputStream(file.toPath()), c);
    }

    public ExcelHelper(String sheetName, InputStream inputStream, Class<T> c) {
        this.sheetName = sheetName;
        this.inputStream = inputStream;
        this.c = c;
        this.responseVo = new ImportResultResponseVo();
        this.excelDataList = new ArrayList<>();
        this.excelPropertyMap = Arrays.stream(c.getDeclaredFields()).filter(f -> f.isAnnotationPresent(ExcelProperty.class))
                .collect(Collectors.toMap(Field::getName, f -> Optional.ofNullable(f.getAnnotation(ExcelProperty.class).value()).map(arr -> arr[0]).orElse(f.getName())));
    }

    public List<BaseExcelData<T>> readExcel() {
        try (ExcelReader excelReader = EasyExcel.read(inputStream).build()) {
            ReadSheet sheet = EasyExcel.readSheet(sheetName)
                    .head(c)
                    .registerReadListener(new MyAnalysisListener<T>(excelDataList, responseVo)).build();
            excelReader.read(sheet);
        }
        return excelDataList;
    }

    /**
     * 根据类的 getter 方法检测重复数据
     *
     * @param func
     * @return
     */
    @SafeVarargs
    public final Boolean checkDuplicateData(PropertyFunc<T, Object>... func) {

        if (func.length == 0) {
            log.warn("needCheckFields is empty");
            return true;
        }

        Map<String, PropertyFunc<T, ?>> funcMap = Stream.of(func)
                .collect(Collectors.toMap(
                        t -> String.valueOf(getFieldName(t)),
                        Function.identity()));


        Map<String, Map<String, List<Integer>>> sameNameMap = new HashMap<>(funcMap.size());


        for (BaseExcelData<T> vo : excelDataList) {
            T excelVo = vo.getExcelVo();
            funcMap.forEach((name, f) -> {
                Object fieldVal = f.apply(excelVo);
                Map<String, List<Integer>> map = sameNameMap.computeIfAbsent(name, v -> Maps.newHashMap());
                map.merge(String.valueOf(fieldVal), Collections.singletonList(vo.getRowIndex()), ListUtils::union);
            });
        }

        sameNameMap.forEach((fieldName, map) -> {
            String excelPropertyVal = excelPropertyMap.get(fieldName);
            map.forEach((fieldVal, dupList) -> {
                if (dupList.size() < 2) {
                    return;
                }
                for (Integer rowIndex : dupList) {
                    ImportError importError = new ImportError(sheetName, rowIndex, excelPropertyVal, String.format(EXCEL_SAME_MSG_TEMPLATE, rowIndex, excelPropertyVal, excelPropertyVal));
                    responseVo.add(importError);
                }
            });

        });


        return responseVo.getFailCount() > 0;

    }

    /**
     * 根据类的 字段名字检测重复数据
     *
     * @return
     */
    public Boolean checkDuplicateData(String... needCheckFields) {
        if (needCheckFields.length == 0) {
            log.warn("needCheckFields is empty");
            return true;
        }
        HashSet<String> checkSet = Sets.newHashSet(needCheckFields);
        Map<String, Field> fieldsNeedCheckDup = Arrays.stream(c.getDeclaredFields())
                .filter(field -> checkSet.contains(field.getName()))
                .collect(Collectors.toMap(Field::getName, f -> f));
        Collection<Field> fieldsWithAnno = fieldsNeedCheckDup.values();
        Map<String, Map<String, List<Integer>>> sameNameMap = new HashMap<>(fieldsWithAnno.size());

        for (Field field : fieldsWithAnno) {
            String name = field.getName();
            sameNameMap.put(name, new HashMap<>());
        }

        for (BaseExcelData<T> vo : excelDataList) {
            T excelVo = vo.getExcelVo();
            for (Field field : fieldsWithAnno) {
                String name = field.getName();
                Object fieldVal = null;
                try {
                    field.setAccessible(true);
                    fieldVal = field.get(excelVo);
                } catch (IllegalAccessException e) {
                    log.error(e.getMessage(), e);
                    throw new RuntimeException(e);
                }
                Map<String, List<Integer>> map = sameNameMap.get(name);
                map.merge(String.valueOf(fieldVal), Collections.singletonList(vo.getRowIndex()), ListUtils::union);
            }
        }

        sameNameMap.forEach((fieldName, map) -> {
            Field field = fieldsNeedCheckDup.get(fieldName);
            String excelPropertyVal = Optional.ofNullable(field.getAnnotation(ExcelProperty.class))
                    .map(ExcelProperty::value)
                    .map(arr -> arr[0])
                    .orElse(fieldName);

            map.forEach((fieldVal, dupList) -> {
                if (dupList.size() < 2) {
                    return;
                }
                for (Integer rowIndex : dupList) {
                    ImportError importError = new ImportError(sheetName, rowIndex, excelPropertyVal, String.format(EXCEL_SAME_MSG_TEMPLATE, rowIndex, excelPropertyVal, excelPropertyVal));
                    responseVo.add(importError);
                }
            });

        });


        return responseVo.getFailCount() > 0;
    }


    /**
     * 检测数据是否存在db
     *
     * @param valTransFunc 转换为mapper查询参数的函数
     * @param mapperCheck  查询函数
     * @param <F>
     * @return
     */
    public <F> Boolean checkDataInDb(PropertyFunc<T, F> valTransFunc,
                                     Predicate<F> mapperCheck) {
        String fieldName = getFieldName(valTransFunc);
        String excelProperty = excelPropertyMap.getOrDefault(fieldName, fieldName);
        excelDataList.forEach(x -> {
            if (mapperCheck.test(valTransFunc.apply(x.getExcelVo()))) {
                responseVo.add(new ImportError(sheetName, x.getRowIndex(), excelProperty, String.format(DB_CHECK_TEMPLATE, valTransFunc.apply(x.getExcelVo()))));
            }
        });
        return responseVo.getFailCount() > 0;
    }

    /**
     * 检测数据是否存在db
     *
     * @param valTransFunc 转换为mapper查询参数的函数
     * @param mapperCheck  查询函数
     * @param msg          错误消息
     * @param <F>
     * @return
     */
    public <F> Boolean checkDataInDb(PropertyFunc<T, F> valTransFunc,
                                     Predicate<F> mapperCheck,
                                     String msg) {
        excelDataList.forEach(x -> {
            if (mapperCheck.test(valTransFunc.apply(x.getExcelVo()))) {
                responseVo.add(new ImportError(sheetName, x.getRowIndex(), msg, String.format(DB_CHECK_TEMPLATE, valTransFunc.apply(x.getExcelVo()))));
            }
        });
        return responseVo.getFailCount() > 0;
    }

    /**
     * 检测是否 符合 validator 格式
     *
     * @param validator
     * @return
     */
    public Boolean checkDataFormat(Validator validator) {
        Iterator<BaseExcelData<T>> iterator = excelDataList.iterator();
        while (iterator.hasNext()) {
            BaseExcelData<T> red = iterator.next();
            T excelVo = red.getExcelVo();
            Set<ConstraintViolation<T>> violations = validator.validate(excelVo);
            if (CollectionUtils.isNotEmpty(violations)) {
                for (ConstraintViolation<T> violation : violations) {
                    String columnName = getColumnName(violation.getPropertyPath().toString(), ReflectUtil.getFields(violation.getRootBeanClass()));
                    String errorMsg = violation.getMessage();
                    responseVo.add(new ImportError(sheetName, red.getRowIndex(), columnName, errorMsg));
                }
                iterator.remove();
            }
        }
        return responseVo.getFailCount() > 0;
    }

    public <ENTITY> void convertAndInsert(
            Function<BaseExcelData<T>, ENTITY> dataConvert,
            Function<List<ENTITY>, Integer> consumer) {
        convertAndInsert(dataConvert, null, consumer);
    }

    public <ENTITY> void convertAndInsert(
            Function<BaseExcelData<T>, ENTITY> dataConvert,
            Consumer<List<ENTITY>> check,
            Function<List<ENTITY>, Integer> consumer) {

        // convert data
        List<ENTITY> dataInset = excelDataList.stream().map(dataConvert).collect(Collectors.toList());

        // check data
        if (check != null) {
            check.accept(dataInset);
        }

        // insert data
        List<List<ENTITY>> partitions = ListUtils.partition(dataInset, BATCH_INSERT_SIZE);
        for (List<ENTITY> partition : partitions) {
            Integer effectRows = consumer.apply(partition);
            int size = partition.size();
            if (effectRows != size) {
                log.warn("batch insert row not eq list size, rows affected: {}, list size: {}", effectRows, size);
            }
            responseVo.setSuccessCount(responseVo.getSuccessCount() + size);
        }
    }

    private static String getColumnName(String fieldName, Field[] fields) {
        for (Field f : fields) {
            if (f.getName().equals(fieldName)) {
                ExcelProperty excelProperty = f.getAnnotation(ExcelProperty.class);
                if (excelProperty != null && ArrayUtil.isNotEmpty(excelProperty.value())) {
                    return excelProperty.value()[0];
                }
            }
        }
        return null;
    }

    public static class MyAnalysisListener<T> extends AnalysisEventListener<T> {
        List<BaseExcelData<T>> excelDataList;
        ImportResultResponseVo responseVo;

        public MyAnalysisListener(List<BaseExcelData<T>> excelDataList, ImportResultResponseVo responseVo) {
            this.excelDataList = excelDataList;
            this.responseVo = responseVo;
        }

        @Override
        public void onException(Exception exception, AnalysisContext context) {
            if (exception instanceof ExcelDataConvertException) {
                ExcelDataConvertException ex = (ExcelDataConvertException) exception;
                Integer rowIndex = ex.getRowIndex();
                ExcelProperty excelProperty = ex.getExcelContentProperty().getField().getDeclaredAnnotation(ExcelProperty.class);
                String columnName = excelProperty.value()[0];
                String errorMsg = String.format(XLSX_ROW_ERROR_INFO, context.readSheetHolder().getSheetName(), rowIndex, columnName);
                responseVo.add(new ImportError(context.readSheetHolder().getSheetName(), rowIndex, columnName, errorMsg));
            } else {
                log.error("read excel data fail", exception);
            }
        }

        @Override
        public void invoke(T excelVo, AnalysisContext context) {
            Integer rowIndex = context.readRowHolder().getRowIndex();
            BaseExcelData<T> excelData = new BaseExcelData<>();
            excelData.setRowIndex(rowIndex);
            excelData.setExcelVo(excelVo);
            excelDataList.add(excelData);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {

        }
    }

    @FunctionalInterface
    public interface PropertyFunc<T, R> extends Function<T, R>, Serializable {
    }

    private static <T> String getFieldName(PropertyFunc<T, ?> field) {
        Class<?> clazz = field.getClass();
        try {
            Method method = clazz.getDeclaredMethod("writeReplace");
            method.setAccessible(true);
            SerializedLambda lambda = (SerializedLambda) method.invoke(field);
            return uncapitalize(lambda.getImplMethodName());
        } catch (Exception e) {
            if (!Object.class.isAssignableFrom(clazz.getSuperclass())) {
                return getFieldName(field);
            }
            throw new RuntimeException("current property is not exists");
        }
    }

    /**
     * 去掉方法的 get/is 前缀，首字母小写
     */
    private static String uncapitalize(String str) {
        if (str == null || str.length() < 4) {
            return str;
        }
        String fieldName = str.startsWith("get") ?
                str.substring(3) : str.startsWith("is") ? str.substring(2) : str;
        return fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1);
    }
}

@Builder
@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel
public class ImportResultResponseVo {

    @ApiModelProperty("导入成功数量")
    private int successCount;

    @ApiModelProperty("导入失败详情描述")
    private Set<ImportError> errorResultList = new TreeSet<>();

    @ApiModelProperty("导入失败个数")
    private int failCount;

    public void add(ImportError errorResult) {
        this.errorResultList.add(errorResult);
        this.failCount = this.errorResultList.size();
    }

    public void add(List<ImportError> errorResults) {
        this.errorResultList.addAll(errorResults);
        this.failCount = this.errorResultList.size();
    }
}

@Data
public class BaseExcelData<T> {

    private Integer rowIndex;

    private T excelVo;
}