700字范文 > SpringBoot整合easyexcel进行excel报表导入导出

SpringBoot整合easyexcel进行excel报表导入导出

时间：2019-03-08 04:34:20

`easyexcel`简介

Java解析、生成Excel比较有名的框架有Apache POI，但存在一个严重的问题就是非常的耗内存，针对这个问题阿里出品了用来处理Excel的开源工具easyexcel

GitHub官网：/alibaba/easyexcel

官方文档：https://alibaba-easyexcel.github.io/

`EasyExcel`和`POI`的对比

相比于POI，EasyExcel简化了开发量，能够用更少的代码实现更多的功能相比于POI，EasyExcel使用简单相比于POI，EasyExcel能够使用更少的内存占用

`SpringBoot`整合`easyexcel`

`Maven`依赖

主要依赖如下，其他依赖自行依赖导入

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency>

数据库`SQL`脚本

CREATE TABLE `excel_user` (`user_id` int(11) NOT NULL AUTO_INCREMENT,`dept_id` int(11) NOT NULL COMMENT '部门ID',`loginName` varchar(255) NOT NULL COMMENT '登录账号\r\n',`phone` varchar(255) NOT NULL,`password` varchar(255) NOT NULL COMMENT '密码',`status` int(255) NOT NULL COMMENT '帐号状态',`createTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`updateTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`user_id`),UNIQUE KEY `index_ln` (`loginName`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

实体类

@Datapublic class ExcelUser {// 定义表头名称和位置，0代表第一列@ExcelProperty(value = "编号", index = 0)private Integer userId;@ExcelProperty(value = "部门编号", index = 1)private Integer deptId;@ExcelProperty(value = "登录账号", index = 2)private String loginname;@ExcelProperty(value = "用户手机号", index = 3)private String phone;@ExcelProperty(value = "密码", index = 4)private String password;@ExcelProperty(value = "账户状态", index = 5)private Integer status;@ExcelProperty(value = "创建时间",index = 6)private Date createtime;@ExcelProperty(value = "更新时间",index = 7)private Date updatetime;}

easyexcel中的常用注解：/weixin_45151960/article/details/109095332

导入导出`dao`层

mapper接口

@Mapperpublic interface ExcelUserMapper {List<ExcelUser> getAll();int insertAll(List<ExcelUserDto> excelUserDtoList);}

mapper.xml

<select id="getAll" resultType="org.example.pojo.ExcelUser">SELECT<include refid="Base_Column_List"></include>FROMexcel_user</select><insert id="insertAll" parameterType="java.util.List">insert into excel_user (dept_id, loginName, phone, password, status) values<foreach collection="list" item="excelUserDto" index="index" separator=",">(#{excelUserDto.deptId}, #{excelUserDto.loginname},#{excelUserDto.phone}, #{excelUserDto.password}, #{excelUserDto.status})</foreach></insert>

导入导出`service`层

@Slf4j@Servicepublic class ExcelServiceImpl implements ExcelService {@Autowiredprivate ExcelUserMapper excelUserMapper;/*** 导出用户信息** @param response response*/@Overridepublic ResultMap excelExport(@NotNull HttpServletResponse response) {// 获取需要导出的数据List<ExcelUser> excelUserList = excelUserMapper.getAll();log.info("记录导出数据行数：{}", excelUserList.size());try {response.setContentType("application/json");response.setCharacterEncoding("UTF-8");String fileName = URLEncoder.encode("用户名单表", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");// 向Excel中写入数据ExcelWriterBuilder write = EasyExcel.write(response.getOutputStream(), ExcelUser.class);ExcelWriterSheetBuilder excelWriterSheetBuilder = write.sheet("用户名单表");excelWriterSheetBuilder.doWrite(excelUserList);return new ResultMap().success();} catch (Exception e) {log.error("数据导出失败！！！");return new ResultMap().fail();}}/*** 导入用户信息** @param multipartFile multipartFile*/@Transactional@Overridepublic ResultMap excelImport(@NotNull MultipartFile multipartFile) {try {ExcelListener excelListener = new ExcelListener();// 读取数据ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(multipartFile.getInputStream(), ExcelUser.class, excelListener);ExcelReaderSheetBuilder excelReaderSheetBuilder = excelReaderBuilder.sheet();excelReaderSheetBuilder.doRead();// 获取excel读取到的数据List<ExcelUser> list = excelListener.getList();// 比较loginname字段去重，数据库有唯一索引List<ExcelUser> excelUserList = this.removeDuplicateOrder(list);// 数据库表不用插入userId列，它是主键列List<ExcelUserDto> excelUserDtoList = new ArrayList<>();excelUserList.forEach(item -> {ExcelUserDto excelUserDto = new ExcelUserDto();BeanUtils.copyProperties(item, excelUserDto);excelUserDtoList.add(excelUserDto);});// 数据的持久化int i = excelUserMapper.insertAll(excelUserDtoList);if (i > 0) {return new ResultMap().success();}return new ResultMap().fail();} catch (IOException e) {log.error("数据导入失败！！！");return new ResultMap().fail();}}/*** 比较loginname字段去重，数据库有唯一索引** @param list list* @return List*/@NotNullprivate List<ExcelUser> removeDuplicateOrder(List<ExcelUser> list) {Set<ExcelUser> set = new TreeSet<>((a, b) -> {// 等于0表示重复int compareToResult = 1;if (a.getLoginname().equals(b.getLoginname())) {compareToResult = 0;}return compareToResult;});set.addAll(list);return new ArrayList<>(set);}}

导入导出`controller`层

@Slf4j@Controllerpublic class ExcelController {@Autowiredprivate ExcelService excelService;/*** 导出用户信息** @param response response* @return ResultMap*/@GetMapping(path = "/excelExport")@ResponseBodypublic ResultMap excelExport(HttpServletResponse response) {return excelService.excelExport(response);}@GetMapping(path = "/fileUpload")public String uploadPage(){return "fileUpload";}/*** 导入用户信息** @param file multipartFile* @return ResultMap*/@PostMapping(path = "/excelImport")@ResponseBodypublic ResultMap excelImport(MultipartFile file) {return excelService.excelImport(file);}}

表格事件监听器

@Slf4jpublic class ExcelListener extends AnalysisEventListener<ExcelUser> {@Getter@Setterprivate List<ExcelUser> list = new ArrayList<>();@Overridepublic void invoke(ExcelUser excelUser, AnalysisContext analysisContext) {log.info("--------------------------------------------");log.info("导入数据{}", JSON.toJSONString(excelUser));// 数据存储到list，供批量处理，或后续自己业务逻辑处理list.add(excelUser);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}

导入`excel`报表页面

<!DOCTYPE html><html lang="en" xmlns:th=""><head><meta charset="UTF-8" /><title>Insert title here</title></head><body><h1 th:inlines="text">文件上传</h1><form th:action="@{excelImport}" method="post" enctype="multipart/form-data"><p>选择文件: <input type="file" name="file"/></p><p><input type="submit" value="提交"/></p></form></body></html>

此处的选择文件的input标签中的name属性值务必要与controller接口中的入参变量名MultipartFile file保持一致，否则会报错参数MultipartFile不能为null