EasyExcel导入及自定义导入

1.maven

        <!-- Excel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.1</version>
        </dependency>

2.默认使用

            List<CrmInvestmentImport> crmInvestmentImportList = EasyExcel.read(FileConfig.Path + importJobAvg.getFilePath())
                    .head(CrmInvestmentImport.class).sheet("Sheet1").doReadSync();

         指定导入excel中指定列名对应字段

@Data
public class CrmInvestmentImport {

    @ExcelProperty("来源")
    private String client_source;

    @ExcelProperty("省")
    private String province;
}

3.自定导入-匹配注解标签中指定的任意一个

        当有多个导入模板时,一个字段title对应多个excel中的列名,比如模板1中的标题,模板2中的故障代码,模板3中的测试项目;这种方式默认是不支持的,需要自定义匹配标签中的任意一个.

@Data
public class QualityIssuesImport {

    //region 标准部分
    @ExcelProperty("行号（必填）")
    private String line_no;

    @ExcelProperty("单据类型")
    private String order_type;//单据类型，客诉/实验室品质/制程品质/售后品质

    @ExcelProperty({"标题（必填）", "故障代码", "测试项目"})
    private String title;//问题
}

        自定义监听器EasyExcelCustomListener

package mis.shared.config;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.util.*;

public class EasyExcelCustomListener extends AnalysisEventListener<Map<Integer, String>> {
    private Map<String, Integer> fieldValue = new HashMap<>();
    public List<Object> list = new ArrayList<>();
    private Map<Integer, String> headMap;
    public Class<?> classType;

    public EasyExcelCustomListener() {
    }

    public EasyExcelCustomListener(Class<?> classType) {
        this.classType = classType;
    }

    /**
     * 数据表头获取，表头位置对应
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        this.headMap = headMap;
        fieldValue.putAll(fieldValueSet(headMap, classType));
        super.invokeHeadMap(headMap, context);
    }

    /**
     * 数据一条一条解析
     */
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext analysisContext) {
        if (fieldValue.isEmpty()) {
            throw new ExcelAnalysisException("模板错误");
        }
        Object obj = null;
        try {
            obj = classType.newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        setFieldValue(data, fieldValue, obj);
        list.add(obj);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }

    /**
     * 根据表头获取列所在位置
     * 匹配注解ExcelProperty指定的任意一个,原生的不支持
     *
     * @param headMap 表头map
     * @param cla     对应解析类
     */
    public static Map<String, Integer> fieldValueSet(Map<Integer, String> headMap, Class<?> cla) {
        Map<String, Integer> fieldValue = new HashMap<>();
        Field[] fields = cla.getDeclaredFields();
        for (Field field : fields) {
            //遍历每个属性
            if (field.isAnnotationPresent(ExcelProperty.class)) {
                ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
                    if (entry == null || !StringUtils.hasLength(entry.getValue())) {
                        continue;
                    }
                    String headValue = entry.getValue();
                    //兼容阿里巴巴全部匹配,原始数据
                    if (Arrays.asList(excelProperty.value()).contains(headValue)) {
                        fieldValue.put(field.getName(), entry.getKey());
                        break;
                    }
                    //去掉空格、换行符后再匹配
                    headValue = entry.getValue().replace(" ", "").replace("\n", "");
                    if (Arrays.asList(excelProperty.value()).contains(headValue)) {
                        fieldValue.put(field.getName(), entry.getKey());
                        break;
                    }
                }
            }
        }
        return fieldValue;
    }

    /**
     * 字段值赋值
     *
     * @param valueMap   值对应所在位置
     * @param obj        实体类
     * @param fieldValue 表头对应所在位置
     */
    public static void setFieldValue(Map<Integer, String> valueMap, Map<String, Integer> fieldValue, Object obj) {
        Field[] fields = obj.getClass().getDeclaredFields();
        for (Field field : fields) {
            //遍历每个属性
            if (field.isAnnotationPresent(ExcelProperty.class) && fieldValue.containsKey(field.getName())) {
                field.setAccessible(true);
                try {
                    Class<?> fieldType = field.getType();
                    String value = valueMap.get(fieldValue.get(field.getName()));
                    field.set(obj, stringToTarget(value, fieldType));
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //String转换为指定类型
    public static Object stringToTarget(String string, Class<?> t) throws Exception {
        boolean nullOrEmpty = !StringUtils.hasLength(string);
        if (String.class.equals(t)) {
            return string;
        }

        if (double.class.equals(t)) {
            return nullOrEmpty ? 0 : Double.parseDouble(string);
        } else if (long.class.equals(t)) {
            return nullOrEmpty ? 0 : Long.parseLong(string);
        } else if (int.class.equals(t)) {
            return nullOrEmpty ? 0 : Integer.parseInt(string);
        } else if (float.class.equals(t)) {
            return nullOrEmpty ? 0 : Float.parseFloat(string);
        } else if (short.class.equals(t)) {
            return nullOrEmpty ? 0 : Short.parseShort(string);
        } else if (boolean.class.equals(t)) {
            return nullOrEmpty ? 0 : Boolean.parseBoolean(string);
        } else if (Number.class.isAssignableFrom(t)) {
            return t.getConstructor(String.class).newInstance(nullOrEmpty ? "0" : string);
        } else {
            return nullOrEmpty ? "" : t.getConstructor(String.class).newInstance(string);
        }
    }

    public List<Object> getList() {
        return list;
    }

    public Map<Integer, String> getHeadMap() {
        return this.headMap;
    }
}

        兼容多种模板的自定义导入主代码

           EasyExcelCustomListener easyExcelCustomListener = new EasyExcelCustomListener(QualityIssuesImport.class);
            ExcelReader excelReader = EasyExcel.read(FileConfig.Path + importJobAvg.getFilePath()).build();
            ReadSheet readSheet = EasyExcel.readSheet("Sheet1")
                    .registerReadListener(easyExcelCustomListener).build();
            excelReader.read(readSheet);
            excelReader.finish();
            excelReader.close();
            List<Object> qualityIssuesList = easyExcelCustomListener.getList();