easyexcel 到mysql 导入_GitHub - Cyanss/excel: 使用阿里的EasyExcel编写的excel导入导出的web项目demo...

SpringBoot+Jpa实现Excel的导入导出(动态Sql、分页查询、联表join)

最近做了一个项目甲方需求中要求数据的导入导出到Excel文件，Excel的复杂表头和数据格式一直是个头疼的问题，

使用poi或者jexcelapi的话就需要花费大量时间处理表头以及数据格式问题，但是整个项目的开发时间只有一周，对接和联调一周，

那就只能去找快速的“黑科技”了--EasyExcel,虽然EasyExcel只是对poi的再封装，但是EasyExcel中有一个模板写入的功能，

是可以很好的解决复杂表头和一些基本的数据格式问题的。抽了点时间整理了一个demo,也包含一些对Jpa的进阶用法(动态Sql分页查询、联表join等)。

开发环境

环境: windows 10

编译器: IDEA 2018

数据库: MySQL 5.6

JDK: jdk1.8.0_92

Maven: 3.6

项目结构树

│ .gitignore

│ LICENSE

│ pom.xml --项目pom文件

│ README.md --readme.md文件

├─github

│ └─image --用于存放readme.md中链接的图片

├─other

│ Excel.sql --数据库建表Sql语句

│ Excel数据导入测试[PostMan].postman_collection.json --信息导入接口PostMan导出的json文件

│ 个人信息导入.xlsx --个人信息导入测试数据

│ 毕业信息导入.xlsx --毕业信息导入测试数据

│ 目录结构树.txt

├─src

│ ├─main

│ │ ├─java

│ │ │ └─com

│ │ │ └─cyan

│ │ │ └─excel

│ │ │ │ ExcelApplication.java --SpringBoot启动类

│ │ │ │

│ │ │ ├─controller

│ │ │ │ ExcelController.java --Controller层

│ │ │ │

│ │ │ ├─entity

│ │ │ │ │ GraduateInfo.java --毕业信息实体

│ │ │ │ │ UserInfo.java --用户信息实体

│ │ │ │ │

│ │ │ │ ├─joint

│ │ │ │ │ GraduateUserJoint.java --毕业和用户联表Join实体

│ │ │ │ │

│ │ │ │ └─model

│ │ │ │ DetailModel.java --详细信息Excel文件对应模型

│ │ │ │ GraduateModel.java --毕业信息Excel文件对应模型

│ │ │ │ UserModel.java --用户信息Excel文件对应模型

│ │ │ │

│ │ │ ├─enums

│ │ │ │ ExcelFileEnum.java --Excel文件类型枚举

│ │ │ │ SexTypeEnum.java --性别类型枚举

│ │ │ │

│ │ │ ├─exception

│ │ │ │ │ ExcelException.java --全局异常

│ │ │ │ │

│ │ │ │ └─handler

│ │ │ │ ExcelExceptionHandler.java --全局异常捕获处理

│ │ │ │

│ │ │ ├─listener

│ │ │ │ ExcelListener.java --excel数据处理

│ │ │ │

│ │ │ ├─repository

│ │ │ │ │ GraduateInfoRepository.java --毕业信息DAO层(jpa)

│ │ │ │ │ GraduateUserJointRepository.java --用户和毕业联表joinDAO层(jpa)

│ │ │ │ │ UserInfoRepository.java --用户信息DAO层(jpa)

│ │ │ │ │

│ │ │ │ └─Impl

│ │ │ │ GraduateUserJointRepositoryImpl.java--用户和毕业联表joinDAO层(代码实现分页/动态Sql)

│ │ │ │

│ │ │ ├─result

│ │ │ │ ResponseResult.java --返回结果体

│ │ │ │ ResponseResultEnum.java --返回结果异常枚举

│ │ │ │ ResponseResultUtils.java --返回结果工具类

│ │ │ │

│ │ │ ├─service

│ │ │ │ │ DetailService.java --详细信息的Service层

│ │ │ │ │ GraduateService.java --毕业信息的Service层

│ │ │ │ │ UserService.java --用户信息的Service层

│ │ │ │ │

│ │ │ │ ├─handler

│ │ │ │ │ ImportHandler.java --Excel数据导入处理

│ │ │ │ │

│ │ │ │ └─Impl

│ │ │ │ DetailServiceImpl.java --详细信息的Service实现

│ │ │ │ GraduateServiceImpl.java --毕业信息的Service实现

│ │ │ │ UserServiceImpl.java --用户信息的Service实现

│ │ │ │

│ │ │ ├─utils

│ │ │ │ ConvertUtils.java --数据转换工具类

│ │ │ │ CopyUtils.java --Bean拷贝工具类(不拷贝null值)

│ │ │ │ ExcelUtils.java --Excel相关工具类

│ │ │ │ FileUtils.java --文件相关工具类

│ │ │ │ PathUtils.java --路径相关工具类

│ │ │ │ StreamUtils.java --流处理工具类

│ │ │ │

│ │ │ └─vo

│ │ │ ImportResultVO.java --Excel导入结果VO层

│ │ │

│ │ └─resources

│ │ │ application.yml --项目配置文件

│ │ │

│ │ ├─static

│ │ │ └─model --Excel导入导出模板

│ │ │ 个人信息模板.xlsx

│ │ │ 分类信息模板.xlsx

│ │ │ 毕业信息模板.xlsx

│ │ │ 详细信息模板.xlsx

Excel模板

1、个人信息模板

2、分类信息模板

3、毕业信息模板

4、详细信息模板

项目测试

1、导入数据测试

数据导入时，接收一个formData格式的excel文件对象，参数为excel，后台接收MultipartFile类型，暂时只做了单文件导出，有多文件导入需求的可以去度娘查询。

Excel的序号一列是不会导入数据库的，UserInfo使用UserId身份证作为主键， GraduateInfo使用自增主键，数据库里两表不设外键关系，只在业务层控制外键关系。设计数据库时只是为了做两表join的功能，把一对一的数据拆分到俩个表里了，导致GraduateInfo不控制的话数据会出现大量重复，因此在Service>handler下做了逻辑上的控制，因此GraduateInfo表参考意义不大。

/** 数据导入信息 返回结果体data */

public class ImportResultVO {

/** 成功数量 */

private Integer success = 0;

/** 失败数量 */

private Integer failure = 0;

/** 重复数量 */

private Integer repeat = 0;

/** 失败数据 */

private List failureList;

/** 重复数据 */

private List repeatList;

//...

}

1、个人信息导入测试

模拟数据:

PostMan测试:

数据库验证:

2、毕业信息导入测试

模拟数据:

PostMan测试:

数据库验证:

注：数据库之前没做覆盖，就存在很多重复数据，手动删除了一些，又重新导入，所以id不连续

2、导出测试

数据导出测试没有返回结果体，直接返回Response,会将生成好的excel文件直接以字节流的形式写入到Response，通过设置Response的Header属性让浏览器自行解析文件。

关于请求头相关知识请自行度娘

{

"Content-Disposition":"attachment;filename=文件名.xlsx",

"Content-Type": "application/octet-stream"

}

1、个人信息导出测试

2、毕业信息导出测试

3、分类信息导出测试

按性别导出主要是为了满足，不同的数据分别写入到同一个excel的不同sheet中的这个需求。

按性别分类导出(男):

按性别分类导出(女):

4、详细信息导出测试

详细信息导出用到了联表Join,用原生SQL查询的话这其实不算个问题，我这里最想展示的是当用Jpa时，分页+动态sql+联表Join的较为复杂的实现方式。

public class GraduateUserJointRepositoryImpl {

@PersistenceContext

private EntityManager entityManager;

/**

* 动态SQL查询数据分页

* @param whereSql

* @param pageable

* @return

*/

@SuppressWarnings("unchecked")

public Page findAllByGraduateUserJointWithWhereSql(String whereSql, Pageable pageable) {

/** 原生SQL语句结合外部动态构建的whereSql实现动态SQL拼接(在这里拼也不是不可以) */

/** 联表Join时根据需求确定 left/right/inner */

/** whereSql 中最少要包含 1=1 类似的逻辑表达式 防止SQL报错 */

String dataSql = "select * from graduate_info g inner join user_info u on u.user_id = g.user_id where " + whereSql;

String countSql = "select count(1) from graduate_info g inner join user_info u on u.user_id = g.user_id where " + whereSql;

Query dataQuery = entityManager.createNativeQuery(dataSql, GraduateUserJoint.class);

Query countQuery = entityManager.createNativeQuery(countSql);

/** 分页供能实现 */

dataQuery.setFirstResult((int) pageable.getOffset());

dataQuery.setMaxResults(pageable.getPageSize());

/** 分页总数统计 */

BigInteger count = (BigInteger) countQuery.getSingleResult();

long total = count.longValue();

/** 分页数据 */

List graduateUserJointList = total > pageable.getOffset() ? dataQuery.getResultList() : Collections. emptyList();

return new PageImpl<>(graduateUserJointList, pageable, total);

}

//...

}

存在bug的详细信息导出：

修复bug的详细信息导出：