EasyExcel文件导入

EasyExcel文件导入

导入对象定义(设置标题头模板)

public class AppInfoAuthMachineExcelDTO {

    @ExcelProperty(value = "机器名称")
    @NotBlank(message = "机器名称不能为空")
    private String machineName;

    @ExcelProperty(value = "机器码")
    @NotBlank(message = "机器码不能为空")
    private String machineCode;

    public String getMachineName() {
        return machineName;
    }

    public void setMachineName(String machineName) {
        this.machineName = machineName;
    }

    public String getMachineCode() {
        return machineCode;
    }

    public void setMachineCode(String machineCode) {
        this.machineCode = machineCode;
    }
}

导入监听器(异常处理与数据处理)

public class AppInfoAuthMachineImportListener extends AnalysisEventListener<AppInfoAuthMachineExcelDTO> {

    private static final Logger logger = LoggerFactory.getLogger(AppInfoAuthMachineImportListener.class);

    private final List<AppInfoAuthMachineExcelDTO> result;


    public AppInfoAuthMachineImportListener(List<AppInfoAuthMachineExcelDTO> result) {
        this.result = result;
    }

    @Override
    public void onException(Exception e, AnalysisContext analysisContext) {
        logger.error("文件导入解析失败，失败原因:", e);
        // 更新导入状态
        String exceptionMsg = ExceptionUtils.parseException(e);
        if (e instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) e;
            exceptionMsg = "导入文件解析失败，第" + (excelDataConvertException.getRowIndex() + 1) + "行,第" + (excelDataConvertException.getColumnIndex() + 1) + "列数据解析异常，数据为"
                    + excelDataConvertException.getCellData().getStringValue() + ",失败原因：" + parseExceptionMsg(e);
        }
        throw new BusinessException(CommonEnums.BUSINESS_ERROR, exceptionMsg);
    }

    /**
     * 转换ExcelDataConvertException中的英文错误信息
     *
     * @param e excel导入异常
     * @return 转换后的错误信息
     */
    private String parseExceptionMsg(Exception e) {
        if (StringUtils.contains(e.getMessage(), "to class java.time.LocalDate error")) {
            return "时间格式错误！";
        }

        if (StringUtils.contains(e.getMessage(), "to class java.math.BigDecimal error")) {
            return "数字格式错误！";
        }
        return ExceptionUtils.parseException(e);
    }

    @Override
    public void invokeHead(Map<Integer, CellData> map, AnalysisContext analysisContext) {
        // 获取数据实体的字段列表
        Field[] fields = AppInfoAuthMachineExcelDTO.class.getDeclaredFields();
        // 导入模板对象表头列表
        List<String> tempHeadList = Arrays.stream(fields)
                // 只选取有ExcelProperty注解的字段
                .filter(field -> Objects.nonNull(field.getAnnotation(ExcelProperty.class)))
                // 通过ExcelProperty注解获取规定的表头名列表
                .map(field -> CollectionUtils.firstElement(Arrays.asList(field.getAnnotation(ExcelProperty.class).value())))
                .collect(Collectors.toList());
        // 导入文件表头列表
        List<String> fileHeadList = map.values().stream().map(CellData::getStringValue).collect(Collectors.toList());
        // 判断用户导入表格的标题头是否完全包含模板的标题头
        if (!fileHeadList.containsAll(tempHeadList)) {
            tempHeadList.removeAll(fileHeadList);
            logger.error("文件导入校验失败，失败原因: {}[{}]", "导入文件与系统模板不匹配，缺少标题头", String.join(", ", tempHeadList));
            throw new BusinessException(CommonEnums.BUSINESS_ERROR,
                    String.format("Excel文件读取失败，失败原因: 导入文件与系统模板不匹配，缺少标题头[%s]",String.join(", ", tempHeadList)));
        }
    }

    @Override
    public void invoke(AppInfoAuthMachineExcelDTO data, AnalysisContext context) {
        // 字段注解校验
        String errMsgPrefix = "导入文件读取失败, 第" + context.readRowHolder().getRowIndex() + "行数据异常";
        ValidatorUtil.validate(data, errMsgPrefix);
        result.add(data);
    }

    @Override
    public void extra(CellExtra cellExtra, AnalysisContext analysisContext) {

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

}

Service使用

public List<AppInfoAuthMachineExcelDTO> parseAppInfoMachineExcel(MultipartFile file) {
    List<AppInfoAuthMachineExcelDTO> result = new ArrayList<>();
    try {
        // 读取excel数据到result集合中
        EasyExcel.read(file.getInputStream(), AppInfoAuthMachineExcelDTO.class, new AppInfoAuthMachineImportListener(result))
                .sheet()
                // 去除文件中的一行标题行
                .headRowNumber(1)
                .doRead();
    } catch (IOException e) {
        throw new BusinessException(CommonEnums.BUSINESS_ERROR, msg -> "Excel文件读取失败", e);
    }
    // excel数据不可为空
    if (CollectionUtils.isEmpty(result)) {
        throw new BusinessException(CommonEnums.BUSINESS_ERROR, "应用授权机器导入数据为空");
    }
    return result;
}

字段校验工具类

public class ValidatorUtil {

    private static final Validator validator = Validation.buildDefaultValidatorFactory().getValidator();

    public static void validate(Object obj, String errMsgPrefix) {
        Map<String, String> validMsg = new LinkedHashMap<>();
        Set<ConstraintViolation<Object>> constraintViolations = validator.validate(obj);
        for (ConstraintViolation<Object> c : constraintViolations) {
            validMsg.put(c.getPropertyPath().toString(), c.getMessage());
        }
        if (ObjectUtils.isNotEmpty(constraintViolations)) {
            throw new BusinessException(CommonEnums.BUSINESS_ERROR, errMsgPrefix + " " + validMsg.values().toString());
        }
    }
}