一个通用的Excel导入导出功能

前言

Excel表单导入，是将Excel文件中的数据导入到数据库；而Excel表单导出，是将数据库的数据导出到Excel文件中。
在本文中开发的功能是基于SpringBoot + JPA框架进行开发，而在系统中，数据库表结构又分成了两种情况，一种是单表结构，另一种是存在@OneToMany注解的主外键关联的主辅表结构，所以本次开发目的是可以同时兼容两种情况。
另外，因为不同的数据表对应的Excel表单的数据和格式都不同，但是为每一个数据表定制化开发Excel导入导出，工作重复度高，所以本次开发另一个目的是能够做到所有数据表通用Excel表单导入导出。

起步

SpringBoot和JPA的环境请读者自行准备，在这里引入poi依赖来协助开发，版本选择4.0.1。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${poi.version}</version>
</dependency>
 
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>

开始

设计思路

功能设计

导入和导出功能都能通过传入的服务参数，完成对指定表结构的导入或导出。
如：http://127.0.0.1/excel/{SERVER_NAME}/import （导入）
       http://127.0.0.1/excel/{SERVER_NAME}/export （导出）

一、Excel导出

1、导出目标

可以导出两种形式的Excel文件：
（1）数据模版，用于数据填入后进行Excel导入功能，命名规则为：Excel-‘name’-Template.xlsx（如：Excel-Project-Template.xlsx）；
（2）指定时间区间的数据，命名规则为：Excel-‘name’.xlsx（如：Excel-Project.xlsx）。

2、导出格式
将数据表的字段以字段名+‘-’的格式（如：id-），依次插入到表单的首行单元格。如果导出的是指定区间的数据，则会在首行行尾追加’createTime’和‘createBy’两个字段，分别表示数据创建时间和数据创建人（关联关系表结构中，只在主表中追加），再将数据逐行插入。
*注：表头字段的设计（如：id-），是为了方便用户在填入自己需要导入数据的过程中，可以在表头字段后加上自己的注解（如：id-编号），并且不影响Excel表单的数据导入，数据填入过程中，请保留’-'符号。

3、导出结构
单表结构：在Entity字段定义中，不存在@OneToMany注解，在该结构下，只有一张Excel工作簿。
关联关系表结构：在Entity字段定义中，存在@OneToMany注解，在该结构下，需要新建Excel工作薄，再将字段或数据填入新的工作簿中。

二、Excel导入
1、导入规则
将数据表的字段以字段名+‘-’的格式（如：id-），依次插入到表单的首行单元格，再将数据逐行填入字段对应的同列单元格即可。表头字段名后可以加上自己的注解（如：id-编号），不影响表单的正常导入。

*注：为避免用户由于Excel表单格式的问题，数据导入失败，请用户先使用Excel导出功能，导出一份表单模板，再将数据填入模板后进行导入操作，保证数据可正常导入。

*注：若导入的数据为关联关系表结构，则辅表工作簿中的关联字段数据需要和主表工作薄中的主键字段数据保持一致。

2、导入操作
根据@Id注解和@GeneratedValue注解判断主键的类型，对主键做特殊处理，若是自增主键，会将主键置为空，保证数据只做插入操作，不做更新操作；而若是非自增主键，不做处理，数据可以进行插入操作，或者对数据库已经存在该主键的数据进行更新操作。

3、导入反馈
在数据转化后，会将数据逐条存入数据库，而在这个过程中，可能会存在数据的错误或者别的问题导致部分数据的存储失败，但是部分数据的存储失败不会导致整个Excel表单数据存储的失败回滚，而是将存储失败的数据对应的Excel表单单元行和失败原因反馈给用户，如：导入数据总数，成功导入数据总数，失败导入数据总数以及失败导入原因。

服务设计

• 内部服务设计
  

（1）RIA报表服务抽象接口

/**
 * RIA报表服务抽象接口
 * @author xinyao.zhang
 */
public interface BaseRiaExcelService {
    /**
     * 导入Excel文件数据
     * @param file 文件
     * @return JSONObject
     */
    default JSONObject importExcelData(MultipartFile file){return null;}

    /**
     * Excel文件 转List数据
     * @param file
     * @return
     */
    default List excelDataConvert(MultipartFile file){return null;}

    /**
     * 导出Excel文件数据
     * @param flag 0：导出Excel格式 1：导出指定时间区间数据
     * @param beginTime 开始时间
     * @param endTime   结束时间
     * @param response  返回数据
     */
    default void exportExcelData(Integer flag, Long beginTime, Long endTime, HttpServletResponse response){}

    /**
     * 数据构建Excel文件
     * @param flag
     * @param beginTime
     * @param endTime
     */
    default XSSFWorkbook dataToExcel(Integer flag, Long beginTime, Long endTime){return null;}

}

（2）RIA基础服务类

/**
 * RIA基础服务类
 * @author xinyao.zhang
 */
public interface BaseRiaService extends BaseService,BaseRiaExcelService{
}

（3）基础服务接口

/**
 * 基础服务接口
 * @author xinyao.zhang
 */
public interface BaseService<T, ID extends Serializable> {

    /**
     * 查找单个实例
     * @param id 实例ID
     * @return T
     */
    T find(ID id);
}

（4）基础服务逻辑层

/**
 * 基础服务逻辑层
 * @author xinyao.zhang
 */
public class BaseServiceImpl<T, ID extends Serializable> implements BaseService<T, ID> {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    protected Class<T> entityClass = (Class<T>) GenericClassUtils.getSuperClassGenericType(getClass(), 0);

    protected Class<ID> idClass = (Class<ID>) GenericClassUtils.getSuperClassGenericType(getClass(), 1);

    protected BaseJpaRepository<T, ID> baseJpaRepository;

    public BaseServiceImpl(BaseJpaRepository baseJpaRepository){
        this.baseJpaRepository = baseJpaRepository;
    }

    @Override
    public T find(ID id){
        T optional = null;
        //ID类型转换
        if(idClass.isInstance(id)){
            optional = baseJpaRepository.findOne(idClass.cast(id));
        }else{
            switch (idClass.getName()) {
                case "java.lang.String":
                    optional = baseJpaRepository.findOne(idClass.cast(String.valueOf(id)));
                    break;
                case "java.lang.Integer":
                    optional = baseJpaRepository.findOne(idClass.cast(Integer.valueOf(id.toString())));
                    break;
                case "java.lang.Long":
                    optional = baseJpaRepository.findOne(idClass.cast(Long.valueOf(id.toString())));
                    break;
                default:
                    break;
            }
        }
        if(null != optional){
            return optional;
        }
        return null;
    }
}

（5）泛型工具类

/**
 * 泛型工具类
 * @author xinyao.zhang
 */
@SuppressWarnings("rawtypes")
public class GenericClassUtils {

    public static Class getSuperClassGenericType(Class clazz, int index) {
        Type genType = clazz.getGenericSuperclass();
        if (!(genType instanceof ParameterizedType)) {
            return null;
        }

        Type[] params = ((ParameterizedType) genType).getActualTypeArguments();
        if (index >= params.length || index < 0) {
            return null;
        }

        if (!(params[index] instanceof Class)) {
            return null;
        }
        return (Class) params[index];
    }
}

（6）RIA基础实现基类

/**
 * RIA基础实现基类
 * @author xinyao.zhang
 * @param <T>
 * @param <ID>
 */
@SuppressWarnings("unchecked")
public abstract class BaseRiaServiceImpl<T, ID> extends BaseServiceImpl implements BaseRiaService {

    private static final Logger logger = LoggerFactory.getLogger(BaseRiaServiceImpl.class);

    private BaseRiaExcel<T,ID> tBaseRiaExcel;

    public BaseRiaServiceImpl(BaseJpaRepository baseJpaRepository) {
        super(baseJpaRepository);
        tBaseRiaExcel = new BaseRiaExcel(entityClass,idClass,baseJpaRepository);
    }
    
    protected BaseRiaExcel baseRiaExcel() {
        return tBaseRiaExcel;
    }
    
    @Override
    public JSONObject importExcelData(MultipartFile file) {
        return tBaseRiaExcel.importExcelData(excelDataConvert(file));
    }

    @Override
    public List excelDataConvert(MultipartFile file) {