解析指定模板的Excel

解析指定模板的Excel，模板如下：

首先定义一个注解

import java.lang.annotation.Documented;
import java.lang.annotation.Retention;
import java.lang.annotation.Target;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.ElementType;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {
    
       //名称
    String value() default "";
       //行数
        int col() default 0;
}

再新建对应的实体

/**   
 * Excel配置文件，行实体类
 */
public class ConfigExcelRow {
    @ExcelColumn(value = "映射标识", col = 1)
    private String mappingKey;
    
    @ExcelColumn(value = "源标识", col = 2)
    private String sourceKey;

    @ExcelColumn(value = "目标标识", col = 3)
    private String targetkey;

    @ExcelColumn(value = "源字段", col = 4)
    private String sourceField;

    @ExcelColumn(value = "目标字段", col = 5)
    private String targetField;
    
    @ExcelColumn(value = "类型", col = 6)
    private String type;
    
    @ExcelColumn(value = "功能模块", col = 7)
    private String functionModule;
    
    @ExcelColumn(value = "源数据库", col = 8)
    private String sourceDbInfo;
    
    @ExcelColumn(value = "目标数据库", col = 9)
    private String targetDbInfo;
    
    @ExcelColumn(value = "查询条件", col = 10)
    private String condition;
    
    @ExcelColumn(value = "源基础数据标识", col = 11)
    private String sourceBaseKey;
    
    @ExcelColumn(value = "目标基础数据标识", col = 12)
    private String targetBasekey;
    
    public String getMappingKey() {
        return mappingKey;
    }

    public void setMappingKey(String mappingKey) {
        this.mappingKey = mappingKey;
    }

    public String getFunctionModule() {
        return functionModule;
    }

    public void setFunctionModule(String functionModule) {
        this.functionModule = functionModule;
    }

    public String getSourceDbInfo() {
        return sourceDbInfo;
    }

    public void setSourceDbInfo(String sourceDbInfo) {
        this.sourceDbInfo = sourceDbInfo;
    }
    
    public String getTargetDbInfo() {
        return targetDbInfo;
    }

    public void setTargetDbInfo(String targetDbInfo) {
        this.targetDbInfo = targetDbInfo;
    }

    @Override
    public String toString() {
        return "MigrationRow [sourceKey=" + sourceKey + ", targetkey=" + targetkey + ", sourceField=" + sourceField
                + ", targetField=" + targetField + ", type=" + type + "]";
    }
    
    public String getSourceBaseKey() {
        return sourceBaseKey;
    }

    public void setSourceBaseKey(String sourceBaseKey) {
        this.sourceBaseKey = sourceBaseKey;
    }

    public String getTargetBasekey() {
        return targetBasekey;
    }

    public void setTargetBasekey(String targetBasekey) {
        this.targetBasekey = targetBasekey;
    }

    public String getSourceKey() {
        return sourceKey;
    }

    public void setSourceKey(String sourceKey) {
        this.sourceKey = sourceKey;
    }

    public String getTargetkey() {
        return targetkey;
    }

    public void setTargetkey(String targetkey) {
        this.targetkey = targetkey;
    }

    public String getSourceField() {
        return sourceField;
    }

    public void setSourceField(String sourceField) {
        this.sourceField = sourceField;
    }

    public String getTargetField() {
        return targetField;
    }

    public void setTargetField(String targetField) {
        this.targetField = targetField;
    }

    public String getType() {
        return type==null?DEFAULT:type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getCondition() {
        return condition;
    }

    public void setCondition(String condition) {
        this.condition = condition;
    }
}

定义接口

public interface ExcelService {
    /**  
     * 解析Excel数据
     */ 
    public <T> List<T> readExcel(String path, Class<T> cls, File file);

    public <T> List<T> readExcel(String path, Class<T> cls, MultipartFile file);
}

实现类

/*
 * 解析，把文件转化成ConfigExcelRow
 * */

@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    FileService fileService;
    
    private static final Log logger = LogFactory.getLog(BillDefine.class);

    public <T> List<T> readExcel(String path, Class<T> cls, File file) {
        MultipartFile multipartFile = fileService.getMultipartFile(file);
        return readExcel(path, cls, multipartFile);
    }

    public <T> List<T> readExcel(String path, Class<T> cls, MultipartFile file) {
        try {
            return _readExcel(path, cls, file);
        } catch (Exception e) {
            logger.info(String.format("初始化配置文件失败！"));
        }
        return null;
    }

    //首行为汉语注解，默认只读取第一个sheet，
    public <T> List<T> _readExcel(String path, Class<T> cls, MultipartFile file) {
        long t1 = System.currentTimeMillis();
        Workbook workbook = getWorkbook(file);
        Assert.notNull(workbook, "workbook must not be null");
        List<T> dataList = new ArrayList<>();
        try {
            List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
            Map<String, List<Field>> classMap = getClassMap(fields);
            Map<Integer, List<Field>> reflectionMap = null;        //索引-->columns
            Sheet sheet = workbook.getSheetAt(0);                //默认读取第一个sheet
            boolean firstRow = true;
            for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null)
                    continue;        //忽略空白行
                if (firstRow) {      //首行提取注解
                    reflectionMap = getReflectionMap(row, classMap);
                    firstRow = false;
                    continue;
                }
                try {
                    T t = cls.newInstance();
                    if (handleField(t, row, reflectionMap))
                        dataList.add(t);
                    else
                        logger.info(String.format("row:%s is blank ignore!", i));
                } catch (Exception e) {
                    logger.error(e);
                }
            }
        } catch (Exception e) {
            logger.error(e);
        }
        long t2 = System.currentTimeMillis();
        logger.info(String.format(file.getName()+"读取完成!   花费:%sms", (t2 - t1)));
        return dataList;
    }

    //处理具体的变量
    <T> boolean handleField(T t, Row row, Map<Integer, List<Field>> reflectionMap) {
        boolean allBlank = true;
        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
            if (reflectionMap.containsKey(j)) {
                Cell cell = row.getCell(j);
                String cellValue = getCellValue(cell);
                if (StringUtils.hasText(cellValue))
                    allBlank = false;
                List<Field> fieldList = reflectionMap.get(j);
                fieldList.forEach(x -> {
                    try {
                        handleField(t, cellValue, x);
                    } catch (Exception e) {
                        logger.error(e);
                    }
                });
            }
        }
        return !allBlank;
    }

    Map<Integer, List<Field>> getReflectionMap(Row row, Map<String, List<Field>> classMap) {
        Map<Integer, List<Field>> reflectionMap = new HashMap<>(16);
        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            String cellValue = getCellValue(cell);
            if (classMap.containsKey(cellValue)) {
                reflectionMap.put(j, classMap.get(cellValue));
            }
        }
        return reflectionMap;
    }

    //根据注解组装classMap
    Map<String, List<Field>> getClassMap(List<Field> fields) {
        Map<String, List<Field>> classMap = new HashMap<>();
        fields.forEach(field -> {
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation != null) {
                String value = annotation.value();
                if (!StringUtils.hasText(value))
                    return;
                if (!classMap.containsKey(value)) {
                    classMap.put(value, new ArrayList<>());
                }
                field.setAccessible(true);
                classMap.get(value).add(field);
            }
        });
        return classMap;
    }

    void fileNameCheck(String fileName) {
        if (!fileName.matches("^.+\.(?i)(xls)$") && !fileName.matches("^.+\.(?i)(xlsx)$")) {
            throw new IllegalArgumentException("上传文件格式不正确");
        }
    }
    
    //获取Workbook，兼容EXCEL2007和EXCEL2003
    Workbook getWorkbook(MultipartFile file) {
        fileNameCheck(file.getOriginalFilename());
        String fileName = file.getOriginalFilename();
        Workbook workbook = null;
        InputStream is;
        try {
            is = file.getInputStream();
            if (fileName.endsWith(EXCEL2007)) {//String EXCEL2007 = "xlsx";
                workbook = new XSSFWorkbook(is);
            } else if (fileName.endsWith(EXCEL2003)) {//String EXCEL2003 = "xlx";
                workbook = new HSSFWorkbook(is);
            }
        } catch (IOException e) {
            logger.error(e);
        }
        return workbook;
    }

    //处理具体属性，根据类型处理
    <T> void handleField(T t, String value, Field field) throws Exception {
        Class<?> type = field.getType();
        if (type == null || type == void.class || !StringUtils.hasText(value)) {
            return;
        }
        if (type == Object.class) {
            field.set(t, value);
            // 数字类型
        } else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
            if (type == int.class || type == Integer.class) {
                field.set(t, Integer.parseInt(value));
            } else if (type == long.class || type == Long.class) {
                field.set(t, Long.parseLong(value));
            } else if (type == byte.class || type == Byte.class) {
                field.set(t, Byte.parseByte(value));
            } else if (type == short.class || type == Short.class) {
                field.set(t, Short.parseShort(value));
            } else if (type == double.class || type == Double.class) {
                field.set(t, Double.parseDouble(value));
            } else if (type == float.class || type == Float.class) {
                field.set(t, Float.parseFloat(value));
            } else if (type == char.class || type == Character.class) {
                field.set(t, value.charAt(0));
            } else if (type == boolean.class) {
                field.set(t, Boolean.parseBoolean(value));
            } else if (type == BigDecimal.class) {
                field.set(t, new BigDecimal(value));
            }
        } else if (type == Boolean.class) {
            field.set(t, Boolean.valueOf(value));
        } else if (type == Date.class) {
            field.set(t, value);
        } else if (type == String.class) {
            field.set(t, value);
        } else {
            Constructor<?> constructor = type.getConstructor(String.class);
            field.set(t, constructor.newInstance(value));
        }
    }
    
    //获取具体cell的值，根据类型获取
    String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {//数字和日期均为CELL_TYPE_NUMERIC
            if (DateUtil.isCellDateFormatted(cell)) {
                return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
            } else {
                return new BigDecimal(cell.getNumericCellValue()).toString();
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return StringUtils.trimWhitespace(cell.getStringCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return StringUtils.trimWhitespace(cell.getCellFormula());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return "";
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            return "ERROR";
        } else {
            return cell.toString().trim();
        }

    }

}