前言:
之前写过一篇springboot集成poi导出表格的文章,但是当数据量变大后发现,有jvm内存溢出的现象,导致服务宕机;就此现象,必定要做出相应的解决方案才行,这时发现了阿里巴巴提供的easyexcel工具类,github地址:https://github.com/alibaba/easyexcel
本篇文章只讲解easyexecl的使用,apache poi和jxl,excelPOI优缺点请自行百度,这里也不一一描述了,咱们直接进入正题。
实现效果:
导出接口:5w条数据花费6秒钟左右成功导出!
导入数据:
准备要导入的数据如下
调用接口执行导入
查看数据库,导入成功
实现过程:
1.环境搭建(引入相关依赖):
我这里使用的是Springboot版本为 2.3.2.RELEASE
数据库使用的mysql
<!--导出导入easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
<!--fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.58</version>
</dependency>
2.控制层代码
本次演示共计两个接口,分别为导出/导入
import com.king.science.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
* @author xf
* @version 1.0.0
* @ClassName TestController
* @Description TODO
* @createTime 2020.09.06 13:49
*/
@RequestMapping("/excel")
@RestController
public class ExcelController {
@Autowired
private ExcelService excelService;
/**
* 导出用户信息
* @param response
* @throws IOException
*/
@GetMapping("/user/excelExport")
public void excelExport(HttpServletResponse response) throws IOException {
excelService.excelExport(response);
}
/**
* 导入用户信息
* @param file
* @return
* @throws IOException
*/
@PostMapping("/user/excelImport")
public String excelImport(@RequestParam("file") MultipartFile file) throws IOException {
excelService.excelImport(file);
return "success";
}
}
3.表格监听工具类
负责监听表格的导出/导入的数据
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
/**
* @author xf
* @version 1.0.0
* @ClassName ExcelListener 表格监听工具类
* @Description TODO
* @createTime 2020.09.02 13:51
*/
@Component
public class ExcelListener extends AnalysisEventListener {
private static Logger logger = LoggerFactory.getLogger(ExcelListener.class);
//可以通过实例获取该值
private List<Object> datas = new ArrayList<Object>();
public void invoke(Object o, AnalysisContext analysisContext) {
logger.info("导入数据{}", JSON.toJSONString(o));
datas.add(o);//数据存储到list,供批量处理,或后续自己业务逻辑处理。
// doSomething(o);//根据自己业务做处理
}
private void doSomething(Object object) {
//1、入库调用接口,可在这里写,也可在业务层写
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// datas.clear();//解析结束销毁不用的资源
}
}
4.具体实现过程,接口实现类
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.king.science.dao.SysUserDao;
import com.king.science.dto.User;
import com.king.science.entity.SysUser;
import com.king.science.service.ExcelService;
import com.king.science.util.ExcelListener;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.TreeSet;
import java.util.stream.Collectors;
/**
* @author xf
* @version 1.0.0
* @ClassName ExcelServiceImpl
* @Description TODO
* @createTime 2020.09.06 13:50
*/
@Service
public class ExcelServiceImpl implements ExcelService {
private static final Logger logger = LoggerFactory.getLogger(ExcelServiceImpl.class);
@Autowired
private SysUserDao sysUserDao;
/**
* 导出用户信息
* @param response
* @throws IOException
*/
@Override
public void excelExport(HttpServletResponse response) throws IOException {
//准备导出的数据
List<SysUser> list = sysUserDao.getListByType();
logger.info("记录导出数据行数:{}",list.size());
String fileName = "用户名单";
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xls");
ServletOutputStream out = response.getOutputStream();
//xls 一个 Sheet 最多支持 65535 行,如果数据要在一个 Sheet 中可以通过指定 ExcelFormat.Xlsx 来导出
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLS,true);
Sheet sheet = new Sheet(1,0,SysUser.class);
//设置自适应宽度
sheet.setAutoWidth(Boolean.TRUE);
sheet.setSheetName("用户名单");
writer.write(list,sheet);
writer.finish();
out.flush();
response.getOutputStream().close();
out.close();
}
/**
* 导入用户信息
* @param file
* @throws IOException
*/
@Override
public void excelImport(MultipartFile file) throws IOException {
InputStream inputStream = file.getInputStream();
//实例化实现了AnalysisEventListener接口的类
ExcelListener listener = new ExcelListener();
//传入参数
ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
//读取信息
excelReader.read(new Sheet(1, 1, User.class));
//获取数据
List<Object> list = listener.getDatas();
List<User> originalList = new ArrayList<User>();
logger.info("记录导入数据行数:{}",originalList.size());
User catagory = new User();
//转换数据类型
for (int i = 0; i < list.size(); i++) {
catagory = (User) list.get(i);
originalList.add(catagory);
}
//对list进行去重并拿到新的list
List<User> lists = originalList.stream()
.filter(s -> StringUtils.isNotBlank(s.getPhoneNumber()))
.collect(Collectors.collectingAndThen(Collectors.toCollection(
() -> new TreeSet<User>(Comparator.comparing(User::getPhoneNumber))), ArrayList::new));
//执行批量插入
if (lists.size() > 0){
logger.info("执行批量入库");
sysUserDao.addLists(lists);
return;
}
logger.info("解析数据为空");
}
}
5.用户表实体类
注意:注解 @ExcelProperty
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.util.Date;
/**
* 用户信息表(SysUser)表实体类
*
* @author makejava
* @since 2020-08-30 16:53:53
*/
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("sys_user")
@Getter
@Setter
public class SysUser extends BaseRowModel {
/**
* 注解 @ExcelProperty(value = String[], index = int):
* 设置表头信息
* value: 表名称
* index: 列号
*/
//用户ID
// @TableId(type = IdType.ID_WORKER_STR)
@TableId("user_id")
@ExcelProperty(value = "ID", index = 0)
private Integer userId;
//部门ID
@TableField("dept_id")
@ExcelProperty(value = "部门id", index = 1)
private Integer deptId;
//登录账号
@TableField("login_name")
@ExcelProperty(value = "登录账号", index = 2)
private String loginName;
//用户昵称
@TableField("user_name")
@ExcelProperty(value = "用户昵称", index = 3)
private String userName;
//用户邮箱
@TableField("email")
@ExcelProperty(value = "用户邮箱", index = 4)
private String email;
//手机号码
@TableField("phone_number")
@ExcelProperty(value = "用户手机号", index = 5)
private String phoneNumber;
//用户性别:0男,1女
@TableField("sex")
@ExcelProperty(value = "用户性别", index = 6)
private String sex;
//头像路径
@ExcelProperty(value = "头像地址", index = 8)
@TableField("avatar")
private String avatar;
//密码
@TableField("password")
@ExcelProperty(value = "密码", index = 9)
private String password;
//盐加密
@TableField("salt")
@ExcelProperty(value = "盐", index = 10)
private String salt;
//类型:Y默认用户,N非默认用户
@TableField("user_type")
@ExcelProperty(value = "用户类型", index = 11)
private String userType;
//帐号状态:0正常,1禁用
@ExcelProperty(value = "账户状态", index = 7)
@TableField("status")
private Integer status;
//拒绝登录描述
@TableField("refuse_des")
@ExcelProperty(value = "拒绝登录描述", index = 12)
private String refuseDes;
//创建者
@TableField("create_by")
private String createBy;
//创建时间
@TableField("create_time")
private Date createTime;
//更新者
@TableField("update_by")
private String updateBy;
//更新时间
@TableField("update_time")
private Date updateTime;
}
如上就是 本次使用springboot集成easyExcel的核心流程!