通过反射实现通用excel表格导入

在项目开发过程中，如果存在大量的数据输入就比较麻烦，所以使用excel导入的形式保存数据。

1.创建需要使用的实体对象的通用父类

// 通用类
@Data
@NoArgsConstructor
public class DataBase implements Serializable {
    private Integer createUserId;
    private Integer updateUserId;
    private String createTime;
    private String updateTime;
}

2.创建需要导入的实体类对象，并且继承DataBase 

@Data
@NoArgsConstructor
public class ContourMarkData extends DataBase {

    /**
     * 主键
     */
    private Integer id;
    /**
     * 起点桩号
     */
    private String startMark;
    /**
     * 终点桩号
     */
    private String endMark;
    /**
     * 类型
     */
    private String type;
}

3.创建xml文件，配置excel和xml对象对应关系

##配置excel
excelDataConfig:
  markConfig:
    - col: 1
      fieldName: startMark
      keyword: 起点桩号
    - col: 2
      fieldName: endMark
      keyword: 终点桩号
    - col: 3
      fieldName: type
      keyword: 护栏形式

4.创建对象，解析xml时的对象

@Data
@Component
@ConfigurationProperties(prefix = "excelConfig")
public class ExcelConfig {
    /**
     * 列
      */
    private Integer col;
    /**
     * 字段名称
     */
    private String fieldName;
    /**
     * 备注
     */
    private String keyword;

}

5.创建config，获取xml的对应配置项。通过 @ConfigurationProperties 注解，我们可以方便的获取这些参数值

@Data
@Component
@ConfigurationProperties(prefix = "excelDataConfig")
@PropertySource(value = {"classpath:application-excel.yml"}, factory = MixPropertySourceFactory.class)
public class ExcelDataConfig {
    /**
     *  类型1
     */
    private List<ExcelConfig> contourMarkConfig;

    /**
     * 根据识别类型获取识别配置信息
     * @param ocrType 识别类型，类型为该配置定义的属性名称
     * @return
     * @throws IllegalAccessException
     */
    public List<ExcelConfig> getExcelConfigs(String ocrType) throws IllegalAccessException {
        try {
            Field field = this.getClass().getDeclaredField(ocrType);
            List<ExcelConfig> ocrConfigs = (List<ExcelConfig>) field.get(this);
            return ocrConfigs;
        } catch (NoSuchFieldException e) {
            return null;
        }
    }
}

6.因为   @PropertySource 只能配置默认的  application.yml，如果要指向对应的yml，需要创建新的解析配置文件的factory，并继承DefaultPropertySourceFactory，重写createPropertySource方法

public class MixPropertySourceFactory extends DefaultPropertySourceFactory {

  @Override
  public PropertySource<?> createPropertySource(String name, EncodedResource resource) throws IOException {
    String sourceName = name != null ? name : resource.getResource().getFilename();
    if (!resource.getResource().exists()) {
      return new PropertiesPropertySource(sourceName, new Properties());
    } else if (sourceName.endsWith(".yml") || sourceName.endsWith(".yaml")) {
      Properties propertiesFromYaml = loadYml(resource);
      return new PropertiesPropertySource(sourceName, propertiesFromYaml);
    } else {
      return super.createPropertySource(name, resource);
    }
  }

  private Properties loadYml(EncodedResource resource) throws IOException {
    YamlPropertiesFactoryBean factory = new YamlPropertiesFactoryBean();
    factory.setResources(resource.getResource());
    factory.afterPropertiesSet();
    return factory.getObject();
  }
}

7.配置枚举类型，方便管理和配置数据

public enum  ExcelTypeEnum {
    /**
     * 类型1
     */
    EXCEL_TYPE_ENUM_1("markConfig","batchAddMark","tb_data_mark", MarkData.class,1),


    ;
    /**
     * 导入类型名称，对应config属性名称
     * application-excel.yml 对应
     */
    private String configName;
    /**
     * 识别数据存储对象的Class
     */
    private Class zClss;
    /**
     * 保存到数据库中，对应Mapper文件的id
     */
    private String sqlId;
    /**
     * 保存到数据库中，对应数据库表名
     */
    private String tableName;

    /**
     * 开始识别行，默认第三行
     */
    private Integer startRow = 3;

    ExcelTypeEnum(  String configName,  String sqlId,String tableName,Class zClss, Integer startRow) {
        this.configName = configName;
        this.zClss = zClss;
        this.sqlId = sqlId;
        this.tableName = tableName;
        this.startRow = startRow;
    }


    /**
     * 根据识别类型获取枚举配置对象
     * @param ocrType 识别类型
     * @return
     */
    public static ExcelTypeEnum getExcelTypeEnumByOcrType(Integer ocrType){
        return ExcelTypeEnum.valueOf("EXCEL_TYPE_ENUM_" + ocrType);
    }

8.excel转对象的通用方法

public class ExcelCommonUtils {
    /**
     * excel转list
     * @param filePath 文件路径
     * @return;
     * @throws Exception
     */
    public static <T extends DataBase> List<T> excel2List(String filePath, DataBase dataBase,
                                                          ExcelTypeEnum excelTypeEnum, List<ExcelConfig> excelConfigs) throws
            Exception {

        List<T> lists = new ArrayList<>();
        File file = new File(filePath);
        Workbook workbook = null;
        FileInputStream stream = null;
        Row row = null;
        Integer serialNo = dataBase.getSerialNo();
        try {
            stream = new FileInputStream(file);
            // 获取Ecle对象
            workbook = WorkbookFactory.create(stream);
            // 循环选项卡中的值
            Sheet sheet = workbook.getSheetAt(0);
            for (int j = excelTypeEnum.getStartRow(); j <= sheet.getLastRowNum(); j++) {
                //0读取一行
                row = sheet.getRow(j);
                //去掉空行和表头
                if (row == null || row.getFirstCellNum() == j ) {
                    continue;
                }
                serialNo++;
                T data = (T) getExcelInstance(dataBase,serialNo,excelTypeEnum.getzClss());
                //创建实体类对象 并设置值
                for (ExcelConfig excelConfig : excelConfigs) {
                    Cell cell = row.getCell(excelConfig.getCol());
                    String filedName = excelConfig.getFieldName();
                    setValue(data,filedName,cell);
                }

                lists.add(data);
            }
        } finally {
            if (!SeageUtils.isEmpty(stream)) {
                try {
                    stream.close();
                    file.delete();
                } catch (IOException e) {
                    log.error("文件流关闭失败", e);
                }
            }
        }
        return lists;
    }

    /**
     * 获取实例对象
     * @throws Exception
     */
    private static <T extends DataBase> T getExcelInstance(DataBase data,Integer serialNo, Class<T> tClass) throws Exception{
        T dataBase = tClass.newInstance();
        dataBase.setCreateTime(data.getCreateTime());
        dataBase.setCreateUserId(data.getCreateUserId());
        dataBase.setUpdateTime(data.getCreateTime());
        dataBase.setUpdateUserId(data.getUpdateUserId());
        return dataBase;
    }


    /**
     * 给对象赋值
     * @param ob 对象
     * @param name 字段名称
     * @param cell excel的表数据
     * @throws Exception
     */
    public static void setValue(Object ob, String name,Cell cell) throws Exception{
        Field[] fields = ob.getClass().getDeclaredFields();
        String value ="";
        for(Field field : fields){
            field.setAccessible(true);
            String fieldName = field.getName();
            if(fieldName.equals(name)){
                String fieldType = field.getType().getSimpleName();
                if("Long".equals(fieldType) || "long".equals(fieldType)){
                    field.set(ob, Long.parseLong(cell.getStringCellValue()));
                } else if ("Integer".equals(fieldType) || "int".equals(fieldType)){
                    String s = Objects.requireNonNull(getExcelDoubleValue(cell)).toString();
                    if(s.contains(".")){
                        value = s.substring(0,s.indexOf("."));
                    }
                    field.set(ob,Integer.parseInt(value));
                } else if ("String".equals(fieldType)){
                    field.set(ob,cell.getStringCellValue());
                } else if("Double".equals(fieldType) || "double".equals(fieldType)){
                    String s = Objects.requireNonNull(getExcelDoubleValue(cell)).toString();
                    field.set(ob,Double.parseDouble(s));
                } else if ("Boolean".equalsIgnoreCase(fieldType) || "boolean".equals(fieldType)) {
                    field.set(ob,Boolean.parseBoolean(cell.getStringCellValue()));
                } else if ("Date".equals(fieldType)) {
                    field.set(ob,SeageUtils.parseDate(cell.getDateCellValue().toString()));
                }else{
                    field.set(ob,value);
                }
                break;
            }
        }
    }

}

9.使用  只是service的逻辑，没有页面内容

 @Override
    @Transactional(rollbackFor = SeageException.class )
    public <T extends DataBase> void uploadExcelData(HttpServletRequest request, MultipartFile dataPics, DataBase dataBase, Integer modelType) throws Exception{
        // 获取文件路径
        String filePath = fileAbsolutePath(dataPics, request);
        // 根据枚举配置，获取枚举类型
        ExcelTypeEnum excelTypeEnum = ExcelTypeEnum.getExcelTypeEnumByOcrType(modelType);
        // 从枚举类中获取表名
        String tableName = excelTypeEnum.getTableName();
        // 获取主键
        String primaryKey=dataDao.getPrimaryKey(tableName);
        // 获取xml配置的excel-config配置内容
        List<ExcelConfig> excelConfigs = excelDataConfig.getExcelConfigs(excelTypeEnum.getConfigName());
        // 调用excel转data方法
        List<DataBase> dataBases = ExcelCommonUtils.excel2List(filePath, dataBase, excelTypeEnum, excelConfigs);
        if (CollectionUtils.isEmpty(dataBases)){
            throw new SeageException(ConstantUtils.ERROR_CODE_PARAMS,"导入数据为空");
        }
        generalDao.batchInsert(saveSql,dataBases);

    }

10.获取图片路径方法

 private String fileAbsolutePath(MultipartFile dataPics, HttpServletRequest request) throws IOException {
        String path = request.getSession().getServletContext().getRealPath("tempFile");
        String fileName = dataPics.getOriginalFilename();
        File file = new File(path);
        if (!file.exists()){
            file.mkdirs();
        }
        File file1 = new File(path+ File.separator+System.currentTimeMillis()+"_"+fileName);
        if (file1.exists()){
            file1.delete();
        }
        dataPics.transferTo(file1);
        return file1.getAbsolutePath();
    }

11.获取表主键方法

SELECT column_name
        FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE`
        WHERE table_name='${tableName}'
        AND CONSTRAINT_SCHEMA = '这里改成自己的仓库'
        AND constraint_name='PRIMARY';