关于空值替换

[tangcent]

场景/需求
某些情况下，字段为空，希望展示为-

尝试的解决方案

我尝试定义如下的CellWriteHandler:

public class NullNumberToHyphenCellWriteHandler extends AbstractCellWriteHandler {

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead || cellData == null) {
            return;
        }
        if (cellData.getType() == CellDataTypeEnum.NUMBER && cellData.getNumberValue() == null) {
            cell.setCellValue("-");
        }
    }
}

但是由于

easyexcel/src/main/java/com/alibaba/excel/write/executor/AbstractExcelWriteExecutor.java

Lines 35 to 37 in 756f16e

	if (value == null) {
	return new CellData(CellDataTypeEnum.EMPTY);
	}

null值并不会触发:

easyexcel/src/main/java/com/alibaba/excel/write/executor/AbstractExcelWriteExecutor.java

Line 48 in 756f16e

WriteHandlerUtils.afterCellDataConverted(writeContext, cellData, cell, head, relativeRowIndex, Boolean.FALSE);

是否有合适的解决方案?

[benadd]

目前遇到相同问题，有没有通用的解决方案

[tangcent]

对于简单需求，你可以尝试定义类似如下Converter:

public class NullNumberToHyphenConverter implements Converter<Double> {
    @Override
    public Class supportJavaTypeKey() {
        return Double.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.NUMBER;
    }

    @Override
    public Double convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return cellData.getNumberValue().doubleValue();
    }

    @Override
    public CellData convertToExcelData(Double value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        if (value == null) {
            return new CellData("——");
        }
        return new CellData(BigDecimal.valueOf(value));
    }
}

• 使用如下:

@NumberFormat("0.00")
@ExcelProperty(value = "xxx", converter = NullNumberToHyphenConverter.class)
private Double xxx;

---

我使用了多列表头,由于You can only choose one of the {@link #head(List)} and {@link #head(Class)} ：

easyexcel/src/main/java/com/alibaba/excel/metadata/AbstractParameterBuilder.java

Lines 15 to 35 in 756f16e

	/**
	* You can only choose one of the {@link #head(List)} and {@link #head(Class)}
	*
	* @param head
	* @return
	*/
	public T head(List<List<String>> head) {
	parameter().setHead(head);
	return self();
	}
	/**
	* You can only choose one of the {@link #head(List)} and {@link #head(Class)}
	*
	* @param clazz
	* @return
	*/
	public T head(Class clazz) {
	parameter().setClazz(clazz);
	return self();
	}

导致我无法使用通过设置headClass来使用com.alibaba.excel.annotation.ExcelProperty指定converter,
所以我转而寻求通过自定义CellWriteHandler来达到我的目的.

---

下面是我目前使用的方案

• 声明注解

/**
 * 默认值
 *
 * @author tangcent
 * @create 2021-01-29
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Default {
    String value();//默认值
}

• 定义抽象的AnnotatedCellWriteHandler

/**
 * 扩展CellWriteHandler方法，仅回调具有指定注解的字段
 *
 * @author tangcent
 * @create 2021-01-29
 */
public class AnnotatedCellWriteHandler<T extends Annotation> implements CellWriteHandler {

    private Map<Integer, Pair<Field, T>> annMapper;

    @SuppressWarnings("unchecked")
    public AnnotatedCellWriteHandler(Class headClass) {
        this.annMapper = getFieldMapper(headClass, this.getClass());
    }


    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        Pair<Field, T> pair = annMapper.get(columnIndex);
        if (pair == null) {
            return;
        }
        beforeCellCreate(writeSheetHolder, writeTableHolder, row, head, columnIndex,
                relativeRowIndex, isHead, pair.getKey(), pair.getValue());
    }

    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead, Field key, T ann) {
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        Pair<Field, T> pair = annMapper.get(cell.getColumnIndex());
        if (pair == null) {
            return;
        }
        afterCellCreate(writeSheetHolder, writeTableHolder, cell, head, relativeRowIndex, isHead,
                pair.getKey(), pair.getValue());
    }

    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead, Field key, T ann) {
    }


    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        Pair<Field, T> pair = annMapper.get(cell.getColumnIndex());
        if (pair == null) {
            return;
        }
        afterCellDataConverted(writeSheetHolder, writeTableHolder, cellData, cell, head, relativeRowIndex, isHead,
                pair.getKey(), pair.getValue());
    }

    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead, Field key, T ann) {
    }


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        Pair<Field, T> pair = annMapper.get(cell.getColumnIndex());
        if (pair == null) {
            return;
        }
        afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead,
                pair.getKey(), pair.getValue());
    }

    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead, Field key, T ann) {
    }


    private static Map<String, Map<Integer, ?>> FIELDS_CACHE = new ConcurrentHashMap<>();
    private static Map<Class, Class> HANDLER_ANNOTATION_CACHE = new ConcurrentHashMap<>();

    @SuppressWarnings("unchecked")
    private static <T> Map<Integer, Pair<Field, T>> getFieldMapper(Class cls, Class handlerClass) {
        String key = cls.getName() + "-" + handlerClass.getName();
        Map fieldMapper = FIELDS_CACHE.get(key);
        if (fieldMapper != null) {
            return (Map<Integer, Pair<Field, T>>) fieldMapper;
        }
        Class annCls = HANDLER_ANNOTATION_CACHE.computeIfAbsent(handlerClass, k -> getAnnotationClass(handlerClass));
        FIELDS_CACHE.putIfAbsent(key, parseFields(cls, annCls));
        return (Map<Integer, Pair<Field, T>>) FIELDS_CACHE.get(key);
    }

    @SuppressWarnings("unchecked")
    protected static Class getAnnotationClass(Class handlerClass) {

        Type genericSuperclass = handlerClass.getGenericSuperclass();
        if (genericSuperclass instanceof Class) {
            // try to climb up the hierarchy until meet something useful
            if (AnnotatedCellWriteHandler.class != genericSuperclass) {
                return getAnnotationClass(handlerClass.getSuperclass());
            }

            throw new TypeException("'" + handlerClass + "' extends AnnotatedCellWriteHandler but misses the type parameter. "
                    + "Remove the extension or add a type parameter to it.");
        }

        Type rawType = ((ParameterizedType) genericSuperclass).getActualTypeArguments()[0];
        if (rawType instanceof ParameterizedType) {
            rawType = ((ParameterizedType) rawType).getRawType();
        }

        if (!(rawType instanceof Class)) {
            throw new TypeException("'" + handlerClass + "' extends AnnotatedCellWriteHandler but can not find the type parameter. "
                    + "Remove the extension or add a type parameter to it.");
        }

        return (Class) rawType;
    }

    private static Map<Integer, Object> parseFields(Class cls, Class<? extends Annotation> ann) {
        final Map<Integer, Object> fieldMap = new HashMap<>();
        final Field[] fields = cls.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            Annotation annotation = field.getAnnotation(ann);
            if (annotation == null) {
                continue;
            }
            Pair pair = Pair.of(field, annotation);
            ExcelProperty excelProperty = AnnotationUtils.getAnnotation(field, ExcelProperty.class);
            if (excelProperty != null) {
                int index = excelProperty.index();
                if (index != -1) {
                    Asserts.isNull(fieldMap.put(index, pair), BasicErrorCode.PARAM_ERROR.code(), "duplicated column:" + index);
                    continue;
                }
            }
            Asserts.isNull(fieldMap.put(i, pair), BasicErrorCode.PARAM_ERROR.code(), "duplicated column:" + i);
        }
        return fieldMap;

    }
}

• 定义DefaultWriteHandler

/**
 * 支持默认值
 *
 * @author tangcent
 * @create 2021-01-29
 */
public class DefaultWriteHandler extends AnnotatedCellWriteHandler<Default> {

    public DefaultWriteHandler(Class headClass) {
        super(headClass);
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead, Field key, Default ann) {
        if (isHead) {
            return;
        }
        if (cellDataList != null && cellDataList.size() == 1) {
            CellData cellData = cellDataList.get(0);
            cellData.checkEmpty();
            if (cellData.getType() == CellDataTypeEnum.EMPTY) {
                cell.setCellValue(ann.value());
            }
        }
    }
}

• 在创建WriteSheet时注册DefaultWriteHandler:

        final WriteSheet xxxSheet = EasyExcel.writerSheet(1, "xxx")
                ...
                .registerWriteHandler(new DefaultWriteHandler(XxxExcelVO.class))
                .build()

• 在你的XxxExcelVO类中需要设置空值替换的字段上注解Default:

@Default("——")
@ExcelProperty(value = "xxx")
private Double xxx;

---

如果你有更好的方案或者新的想法，请在此issue告知我

[wuhuaizai]

如果使用converter能处理单元格为空的情况吗？我发现单元格为空时，没有经过converter

[zhuangjiaju]

已经在3.0.0-beta1 版本修复，beta版本会在一个月内升级成正式版。
实现接口NullableObjectConverter