EasyExcel实现动态列解析和存表

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>3.1.0</version>
</dependency>

public class AgentDeliverOrderImportVo {
    @ExcelProperty(value = {"订单编号"}, order = 1)
    private String deliverNo;

    @ExcelProperty(value = {"发货数量", "采购数量(台)"}, order = 14)
    @ColumnName(name = {"发货数量", "采购数量(台)"})
    private Integer deliverCount;
    
    /**
     * 动态字段（业务线编号区分）
     */
    private String dynamicFields;

    private Date createTime;

    private String createBy;
}

public String test(MultipartFile file) throws IOException {
    //假设从字典中获取字典值
    Map<String, String> dictMap = new HashMap<>();
    dictMap.put("项目", "xm");
    dictMap.put("嗨一付订单编号", "hyfddbh");
    
    try (InputStream inputStream = file.getInputStream()) {
        EasyExcel.read(inputStream, new ReadListener<Map<String, String>>(){
            private Map<Integer, String> fieldHead;

            //获取表头
            @Override
            public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
                Map<Integer, String> integerStringMap = ConverterUtils.convertToStringMap(headMap, context);
                log.info("解析到一条头数据:{}", JSON.toJSONString(integerStringMap));
                fieldHead = ExcelParsing.setFieldHead(integerStringMap, AgentDeliverOrderImportVo.class);
                log.info("转化后头数据：{}", JSONObject.toJSONString(fieldHead));
            }

            //获取数据
            @Override
            public void invoke(Map<String, String> map, AnalysisContext analysisContext) {
                log.info("解析到一条数据:{}", JSON.toJSONString(map));
                Map<String, String> valueMap = ExcelParsing.setFieldValue(fieldHead, dictMap, map);
                log.info("转化一条数据：{}", JSONObject.toJSONString(valueMap));
                log.info("转化一条动态数据：{}", JSONObject.toJSONString(ExcelParsing.getValueMap(valueMap, AgentDeliverOrderImportVo.class)));
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {

            }
        }).sheet().doRead();
    }
    return "完成";
}

/**
 * @author Surpass
 * @Description: excel处理类
 * @date 27/07/2022 15:04
 */
class ExcelParsing {

    /**
     * 将公共字段中的中文转换成数据库表字段，动态字段（其他字段保留）
     * @param headMap               {1:"姓名", 2:"年龄"}
     * @param obj               	AgentDeliverOrderImportVo(导入实体类)
     * @return java.util.Map<java.lang.String, java.lang.String>       {1:"name", 2:"年龄"}
     * @author Surpass
     * @date 01/08/2022 17:10
     */
    public static Map<Integer, String> setFieldHead(Map<Integer, String> headMap, Class<?> obj) {
        Field[] fields = obj.getDeclaredFields();
        for (Field field : fields) {
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation == null) {
                continue;
            }
            //存在翻译字段的情况，一个字段对应好几个表头（尽量避免）
            List<String> valueList = Arrays.asList(annotation.value());
            for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
                if (valueList.contains(entry.getValue())) {
                    headMap.put(entry.getKey(), field.getName());
                }
            }
        }

        return headMap;
    }

    /**
     * 获取数据（平铺），指动态字段kv和公共字段kv在同一级
     * @param headMap               {1:"name", 2:"年龄"}
     * @param dictMap               {"年龄":"age"}
     * @param valueMap              {1:"广州****公司", 2:"23"}
     * @return java.util.Map<java.lang.String, java.lang.String>
     * @author Surpass
     * @date 01/08/2022 17:10
     */
    public static Map<String, String> setFieldValue(Map<Integer, String> headMap,
                                                    Map<String, String> dictMap,
                                                    Map<String, String> valueMap) {
        Map<Integer, String> valueIntegerMap = valueMap.entrySet().stream().collect(
                Collectors.toMap(item -> Integer.valueOf(String.valueOf(item.getKey())),
                        item -> StrUtil.nullToEmpty(item.getValue()))
        );

        Map<String, String> valueResultMap = new HashMap<>(valueMap.size());
        Iterator<Map.Entry<Integer, String>> iterator = valueIntegerMap.entrySet().iterator();
        
        while (iterator.hasNext()) {
            Map.Entry<Integer, String> entry = iterator.next();
            //动态字段
            if (dictMap != null && dictMap.containsKey(headMap.get(entry.getKey()))) {
                valueResultMap.put(dictMap.get(headMap.get(entry.getKey())), entry.getValue());
                continue;
            }
            //公共字段
            valueResultMap.put(headMap.get(entry.getKey()), entry.getValue());
            iterator.remove();
        }
        return valueResultMap;
    }

    /**
     * 获取数据（表结构），指动态字段kv已经加入到数据库表字段 dynamicFields 中
     * @param obj               	AgentDeliverOrderImportVo(导入实体类)
     * @param valueMap              {"name":"广州****公司", "age":"23"}
     * @return java.util.Map<java.lang.String, java.lang.String>  
     * 返回结果： {"name":"广州****公司","dynamicFields":{"age":"23"}}
     * @author Surpass
     * @date 01/08/2022 17:10
     */
    public static Map<String, Object> getValueMap(Map<String, String> valueMap,
                                                  Class<?> obj) {
        Map<String, Object> resultMap = new HashMap<>(valueMap);
        List<String> commonFieldList = new ArrayList<>();
        Field[] fields = obj.getDeclaredFields();
        for (Field field : fields) {
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation == null) {
                continue;
            }
            commonFieldList.add(field.getName());
        }
        //过滤掉实体中的公共字段
        Map<String, String> dynamicMap = valueMap.entrySet().stream()
                .filter(item -> !commonFieldList.contains(item.getKey()))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
        resultMap.put("dynamicFields", dynamicMap);;
        return resultMap;
    }
}