Excel解析框架迭代改进

曾经终败给现在 2022-06-04 04:43 80阅读 0赞

这版本主要是修复上个版本的一个bug以及扩展了一些功能。

1.  修复了Excel单元格以字符串形式存储的数字，解析时出错BUG。

public class Excel { 
        /** * 无损数字解析，可能返回类型：Integer, Long, BigInteger, BigDecimal * * @param value * @param defaultValue * @return 如果 s 为空值(null)、"null"、空串("")或空白串(" ")，则返回null，存在格式异常则返回 defaultValue */
        private Number numberOf(final Object value, final Number defaultValue) {
            if (null != value) {
                if (value instanceof Number) {
                    if (value instanceof Integer || value instanceof Long) {
                        return (Integer)value;
                    } else if (value instanceof BigDecimal || value instanceof BigInteger) {
                        return (BigDecimal)value;
                    } else if (value instanceof Double) {
                        return (Double)value;
                    } else if (value instanceof Float) {
                        return (Float)value;
                    } else if (value instanceof String && isNumber((String)value)) {  //这部分代码是添加的。
                        return Double.valueOf((String)value);
                    }
                }
            }
            return null;
        }
    
        /** * 字符串是否是数字 * * @param value * @return */
        private boolean isNumber(String value) {
            char[] chars = ((String)value).toCharArray();
            for (char c : chars) {
                if (!Character.isDigit(c)) {
                    return false;
                }
            }
            return true;
        }
    
    }

2.ExcelColumn注解移除last属性

@Retention(RetentionPolicy.RUNTIME)
    @Target({ ElementType.FIELD, ElementType.METHOD })
    public @interface ExcelColumn { 
    
        /** * 配置列的名称,对应A,B,C,D.... */
        Column value() default Column.DEFAULT_NONE;
    
        /** * 是否可作为唯一性的标识<br> * 默认 notNull() = true * * @return */
        boolean key() default false;
    
        /** * 非空校验 <br> * null or '' or ' ' ? true: false * * @return */
        boolean notNull() default true;
    
        ......
    }

3.ExcelMappingProcessor增加属性转换器，将Excel单元格内的数据转换为自己想要的数据，比如将String转换为Integer,Enum等，转换的逻辑使用Converter,用户自己注册想要的转换器；对Filed和ExcelColumn的映射做排序

public final class ExcelMappingProcessor<T extends PropertyInitializer<T>> { 
    
        ......
        //持有属性转换器集合,类的最下方有新增的几个注册转换器的方法
        private static final Map<String, Converter<?, ?>> FIELD_CONVERTERS = new ConcurrentHashMap<String, Converter<?, ?>>();
        ......
    
    
        /**
         * 创建Excel到Model的映射
         */
        private void buildExcelMapping() {
            Map<Field, ExcelColumn> fieldColumns = new HashMap<Field, ExcelColumn>();
            //解析标识了@ExcelColumn注解的属性
            ReflectionUtils.doWithLocalFields(clazz, (field) -> { ExcelColumn column = field.getAnnotation(ExcelColumn.class); if (null == column) { return; } else if (Modifier.isStatic(field.getModifiers())) { LOGGER.warn("[{}]注解不适用于静态属性[{}]", EXCELCOLUMN_ANN_NAME, field.getName()); return; } fieldColumns.put(field, column); }); //解析标识了@ExcelColumn注解的getter()方法 ReflectionUtils.doWithLocalMethods(clazz, (method) -> { ExcelColumn column = method.getAnnotation(ExcelColumn.class); if (null == column) { return; } else if (Modifier.isStatic(method.getModifiers())) { LOGGER.warn("[{}]注解不适用于静态方法[{}]", EXCELCOLUMN_ANN_NAME, method.getName()); return; } if (!isGetter(method)) { LOGGER.warn("[{}]注解不适用于非getter方法[{}]", EXCELCOLUMN_ANN_NAME, method.getName()); return; } Field field = getFieldFromGetter(method); if (fieldColumns.containsKey(field)) { LOGGER.warn("[{}]属性被重复解析，略过", field.getName()); return; } fieldColumns.put(field, column); }); //对属性集合映射做排序 LinkedHashMap<Field, ExcelColumn> fieldMappings = new LinkedHashMap<Field, ExcelColumn>(); List<ExcelColumn> values = new ArrayList<ExcelColumn>(fieldColumns.values()); Collections.sort(values, new Comparator<ExcelColumn>() { @Override public int compare(ExcelColumn o1, ExcelColumn o2) { return o1.value().value - o2.value().value; } }); for (ExcelColumn excelColumn : values) { for (Entry<Field, ExcelColumn> entry : fieldColumns.entrySet()) { if (entry.getValue() == excelColumn) { fieldMappings.put(entry.getKey(), entry.getValue()); fieldColumns.remove(entry.getKey()); break; } } } EXCEL_MAPPINGS.put(clazz, fieldMappings); } /** * Excel解析 * * @return hasError ? true : false; */ public boolean process() throws Exception { // 循环每一行 循环每一列，获取每一个单元格的数值，通过注解设置到指定属性中 final int physRow = excel.getSheetAt(sheetAt).getPhysicalNumberOfRows(); Assert.isTrue(physRow > dataRow, String.format("解析Excel错误，Excel实际可读取的物理行数%d小于指定的数据行数%d", physRow, dataRow)); LinkedHashMap<Field, ExcelColumn> fieldColumns = this.getExcelMapping(); for (int i = dataRow; i < physRow; i++) { final int rowIndex = i; if (exceptionResolver.excelEditorMode()) { this.removeErrorMsg(rowIndex); } final T newInstance = BeanUtils.instantiate(clazz).initProperties(); final StringBuilder keyBuilder = new StringBuilder(); boolean hasRowError = false; //遍历Key Column for (Entry<Field, ExcelColumn> entry : fieldColumns.entrySet()) { Field field = entry.getKey(); ExcelColumn excelColumn = entry.getValue(); if (excelColumn.value().value + 1 == fieldColumns.size() && null == lastExcelColumn) { lastExcelColumn = excelColumn; } ExcelColumn.Column column = excelColumn.value(); Cell cell = excel.getCell(rowIndex, column.value); if (excelColumn.notNull()) { Assert.notNull(cell, String.format("获取Excel单元格%d行%s列为空", rowIndex + 1, column.name)); } Object value = null; try { value = getCellValue(cell, field, excelColumn); } catch (Exception e) { hasRowError = true; if (!exceptionResolver.handleCellTypeMismatch(new ExcelMappingException(e.getMessage(), rowIndex, column.value, this))) { LOGGER.warn("因类型不匹配中止解析解析Excel，当前解析到第[{}]行第[{}]列", rowIndex, column.value); return false; } continue; } boolean isKey = excelColumn.key(); if (rowIndex == dataRow && isKey) { keyFieldColumns.put(field.getName(), excelColumn); } if (isKey) { keyBuilder.append("[").append(field.getName()).append(":").append(value).append("]"); } field.setAccessible(true); ReflectionUtils.setField(field, newInstance, value); // 1.4 parse current remove old cell prompt if (exceptionResolver.excelEditorMode()) { removeErrorPrompt(cell); } } if (hasRowError) { this.setError(); continue; } if (keyFieldColumns.isEmpty()) { correctResult.put(String.valueOf(rowIndex), new ExcelInstance<T>(rowIndex, newInstance)); continue; } // 2.唯一性校验 String key = keyBuilder.toString(); if (correctResult.containsKey(key)) { this.setError(); int dupRowIndex = correctResult.get(key).getRowIndex() + 1; String errorMsg = "此行与第" + dupRowIndex + "行的数据存在重复情况"; if (!exceptionResolver.handleRowUniqueConflict(new ExcelMappingException(errorMsg, rowIndex, 0, this))) { LOGGER.warn("因行唯一性冲突中止解析解析Excel，当前解析到第[{}]行", rowIndex); return false; } continue; } correctResult.put(keyBuilder.toString(), new ExcelInstance<T>(rowIndex, newInstance)); } // 在标题行中标记唯一键 if (exceptionResolver.excelEditorMode()) { markKeyColumnsPrompt(); } // return !hasError; } /** * 根据对象属性类型解析ExcelColumn值 * * @param cell * @param field * @param excelColumn * @return */ private Object getCellValue(Cell cell, Field field, ExcelColumn excelColumn) { // 1. Class<?> fieldType = field.getType(); String fieldName = field.getName(); if (this.containsConverter(fieldName)) { //使用转换器的源类型当做属性类型去获取单元格的值 fieldType = this.getSourceClass(fieldName); } Object value = excel.getCellValue(cell); Object strValue = excel.getCellString(cell); if (null == value || null == strValue) { Assert.state(!excelColumn.key() || !excelColumn.notNull(), "解析{" + excelColumn.value().name + "}列错误，值为空"); return value; } // 2. if (fieldType.isAssignableFrom(String.class)) { value = strValue; } else if (fieldType.isAssignableFrom(Integer.class)) { value = excel.getCellInteger(cell, null); Assert.notNull(value, "解析{" + strValue + "}错误，值应为[整型]类型"); } else if (fieldType.isAssignableFrom(Long.class)) { value = excel.getCellLong(cell, null); Assert.notNull(value, "解析{" + strValue + "}错误，值应为[长整型]类型"); } else if (fieldType.isAssignableFrom(Boolean.class)) { value = excel.getCellBoolean(cell); Assert.notNull(value, "解析{" + strValue + "}错误，值应为[布尔]类型"); } else if (fieldType.isAssignableFrom(Date.class)) { value = excel.getCellDate(cell); Assert.notNull(value, "解析{" + strValue + "}错误，值应为[日期]类型"); } else if (fieldType.isAssignableFrom(BigDecimal.class)) { value = excel.getCellDecimal(cell); Assert.notNull(value, "解析{" + strValue + "}错误，值应为[数值]类型"); } else { throw new IllegalArgumentException("解析{" + strValue + "}错误，暂不支持[" + field.getType().getName() + "]类型"); } if (this.containsConverter(fieldName)) { Converter converter = this.getConverter(fieldName); value = converter.convert(value); } return value; } /** * 注册指定类的指定属性转换器 * * @param fieldName * @param converter */ public void registerFieldConverter(String fieldName, Converter<?, ?> converter) { checkFieldConverter(fieldName, converter); String fieldPath = clazz.getName() + "." + fieldName; if (FIELD_CONVERTERS.containsKey(fieldPath)) { LOGGER.warn("{}的{}属性已存在相应的转换器", clazz.getName(), fieldName); return; } FIELD_CONVERTERS.put(fieldPath, converter); } /** * 覆盖已存在的属性转换器 * * @param fieldName * @param converter */ public void overrideFieldConverter(String fieldName, Converter<?, ?> converter) { checkFieldConverter(fieldName, converter); String fieldPath = clazz.getName() + "." + fieldName; if (!FIELD_CONVERTERS.containsKey(fieldPath)) { LOGGER.warn("{}的{}属性不存在相应的转换器", clazz.getName(), fieldName); return; } FIELD_CONVERTERS.put(fieldPath, converter); } /** * 情况指定类的所有属性转换器 */ public void clearTargetClassConverterCache() { for (Map.Entry<String, Converter<?, ?>> entry : FIELD_CONVERTERS.entrySet()) { if (entry.getKey().contains(clazz.getName())) { LOGGER.info("移除指定类{}的{}属性的转换器", clazz.getName(), entry.getKey()); FIELD_CONVERTERS.remove(entry.getKey()); } } } /** * 获取指定类指定属性的转换器 * * @param fieldName * @return */ private Converter<?, ?> getConverter(String fieldName) { return FIELD_CONVERTERS.get(clazz.getName() + "." + fieldName); } /** * 校验转换器和属性类型的一致性 * 注册转换器时，请不要使用lambda表达式，这可能会造成泛型类型丢失 * * @param fieldName * @param converter */ private void checkFieldConverter(String fieldName, Converter<?, ?> converter) { Assert.notNull(converter, "属性转换器不能为空"); Field field = ReflectionUtils.findField(clazz, fieldName); Assert.notNull(field, String.format("[%s]类的[%s]属性不存在", clazz.getName(), fieldName)); Class<?> targetType = getTargetClass(converter); Assert.state(field.getType().isAssignableFrom(targetType), String.format("[%s]属性的类型[%s]和转换器的目标类型[%s]不匹配", fieldName, field.getType().getName(), targetType.getName())); } /** * 获取目标类型 * * @param fieldName * @return */ private Class<?> getTargetClass(String fieldName) { Converter<?, ?> converter = this.getConverter(fieldName); return converter == null ? null : getGenericType(converter, 1); } /** * @param converter * @return */ private Class<?> getTargetClass(Converter<?, ?> converter) { return this.getGenericType(converter, 1); } /** * 获取源类型 * * @param fieldName * @return */ private Class<?> getSourceClass(String fieldName) { Converter<?, ?> converter = this.getConverter(fieldName); return converter == null ? null : getGenericType(converter, 0); } /** * 获取指定位置的泛型 * * @param converter * @param index * @return */ private Class<?> getGenericType(Converter<?, ?> converter, int index) { return ResolvableType.forClass(Converter.class, converter.getClass()).resolveGeneric(index); //return GenericTypeResolver.resolveTypeArguments(converter.getClass(), Converter.class)[index]; } /** * 是否含有指定属性的转换器 * * @param fieldName * @return */ private boolean containsConverter(String fieldName) { return FIELD_CONVERTERS.containsKey(clazz.getName() + "." + fieldName); } }

4.新增了一个抛出异常的MappingExceptionResolver实现类，同时修改原有一个实现类

import java.util.LinkedHashMap;
    import java.util.Map;
    
    /** * 异常信息合并处理器 * * @author dell-7359 * @create 2017-10-22 19:11 */
    public class ErrorCollectionExceptionResolver implements MappingExceptionResolver { 
    
        private static final LinkedHashMap<Integer, String> ROW_ERROR_MAPPINGS = new LinkedHashMap<Integer, String>(16);
        private Integer maxErrorSize = Integer.MAX_VALUE;
    
        public ErrorCollectionExceptionResolver() {
        }
    
        public ErrorCollectionExceptionResolver(Integer maxErrorSize) {
            this.maxErrorSize = maxErrorSize;
        }
    
        @Override
        public boolean excelEditorMode() {
            return false;
        }
    
        @Override
        public boolean handleCellTypeMismatch(ExcelMappingException ex) throws Exception {
            return combineErrorMsg(ex.getRowIndex(),
                String.format("单元格[%s]异常,%s", (char)(ex.getColumnIndex() + 65) + "" + (ex.getRowIndex() + 1), ex.getMessage()));
        }
    
        @Override
        public boolean handleRowUniqueConflict(ExcelMappingException ex) throws Exception {
            return combineErrorMsg(ex.getRowIndex(), "此行与" + ex.getMessage() + "行存在数据重复的情况，可查看标题栏上的唯一列批注");
        }
    
        /** * 获取组合好的错误信息以供前端展示 * * @return */
        public String getCombinedMsg() {
            StringBuilder sb = new StringBuilder();
            for (Map.Entry<Integer, String> entry : ROW_ERROR_MAPPINGS.entrySet()) {
                sb.append(String.format("第%d行:%s\n", entry.getKey() + 1, entry.getValue()));
            }
            return sb.toString();
        }
    
        private boolean combineErrorMsg(int rowIndex, String msg) {
            if (!ROW_ERROR_MAPPINGS.containsKey(rowIndex)) {
                ROW_ERROR_MAPPINGS.put(rowIndex, msg);
            } else {
                ROW_ERROR_MAPPINGS.put(rowIndex, ROW_ERROR_MAPPINGS.get(rowIndex) + ";" + msg);
            }
            return ROW_ERROR_MAPPINGS.size() < maxErrorSize;
        }
    
        public LinkedHashMap<Integer, String> getRowErrorMappings() {
            return ROW_ERROR_MAPPINGS;
        }
    }
    
    /** * 以异常的形式抛出错误信息的异常解析器 * * @author wb-jjb318191 * @create 2017-11-30 13:22 */
    public class ErrorThrowingExceptionResolver implements MappingExceptionResolver { 
    
        @Override
        public boolean excelEditorMode() {
            return false;
        }
    
        @Override
        public boolean handleCellTypeMismatch(ExcelMappingException ex) throws Exception {
            throw new IllegalStateException(String.format("单元格[%s]异常,%s", (char)(ex.getColumnIndex() + 65) + "" + (ex.getRowIndex() + 1), ex.getMessage()));
        }
    
        @Override
        public boolean handleRowUniqueConflict(ExcelMappingException ex) throws Exception {
            throw new IllegalStateException("第" + (ex.getRowIndex() + 1) + "行与" + ex.getMessage() + "行存在数据重复的情况，可查看标题栏上的唯一列批注");
        }
    }

以ErrorThrowingExceptionResolver的使用为例(碰到异常就中断解析)，说明这个版本的使用方法:

private Collection<ExcelInstance<SpaceUnit>> parseSpaceUnitsFromExcel(Workbook workbook) throws Exception {
            //使用ErrorThrowingExceptionResolver作为异常处理器
            ExcelMappingProcessor<SpaceUnit> processor = new ExcelMappingProcessor<SpaceUnit>(new Excel(workbook), SpaceUnit.class,
                new ErrorThrowingExceptionResolver());
            //如果指定属性不存在转换器，则注册一个转换器
            if (!processor.containsConverter("category")) {
                processor.registerFieldConverter("category", new Converter<String, Integer>() {
                    @Override
                    public Integer convert(String source) {
                        SpaceUnitTypeEnum typeEnum = SpaceUnitTypeEnum.valueof(source);
                        Assert.state(typeEnum != null, "空间类别不符合规范");
                        return typeEnum.getCode();
                    }
                });
            }
            processor.process();
            return processor.getCorrectResult();
        }