SpringBoot整合EasyExcel+MyBatis-Plus实现Excel批量导入和导出
一、前期配置
1.依赖
<!--SpringBoot整合easyExcel实现Excel的导入出-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
<!-- mybatis-plus依赖-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
二、使用步骤
2.1.Entity实体类
@ColumnWidth(30)//注释在具体属性上,设置单独列。注释在类上,统一设置列宽
@HeadRowHeight(30)//设置表头行高
@ContentRowHeight(20)//统一设置数据行行高
@ApiModel(value = "User对象", description = "")
public class User implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty(value = "主键ID", index = 0)
@ApiModelProperty(value = "主键ID")
@TableId(value = "id", type = IdType.ASSIGN_ID)
private Long id;
@ExcelProperty(value = "姓名", index = 1)
@ApiModelProperty(value = "姓名")
private String name;
@ExcelProperty(value = "年龄", index = 2)
@ApiModelProperty(value = "年龄")
private Integer age;
@ExcelProperty(value = "邮箱", index = 3)
@ApiModelProperty(value = "邮箱")
private String email;
@ExcelProperty(value = "创建时间", index = 4)
@TableField(fill = FieldFill.INSERT)
@ApiModelProperty(value = "创建时间")
private Date createTime;
@ExcelProperty(value = "最后修改时间", index = 5)
@TableField(fill = FieldFill.INSERT_UPDATE)
@ApiModelProperty(value = "最后修改时间")
private Date updateTime;
/**
* 逻辑删除（0 未删除、1 删除）
*/
@ExcelProperty(value = "逻辑删除", index = 6)
@TableField(fill = FieldFill.INSERT)
@ApiModelProperty(value = "逻辑删除（0 未删除、1 删除）")
private Integer deleteFlag;
@ExcelProperty(value = "最后修改时间", index = 7)
@Version
@TableField(fill = FieldFill.INSERT)
@ApiModelProperty(value = "版本号（用于乐观锁， 默认为 1）")
private Integer version;
public Integer getVersion() {
return version;
}
public void setVersion(Integer version) {
this.version = version;
}
public static long getSerialVersionUID() {
return serialVersionUID;
}
public Integer getDeleteFlag() {
return deleteFlag;
}
public void setDeleteFlag(Integer deleteFlag) {
this.deleteFlag = deleteFlag;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", email='" + email + '\'' +
", createTime=" + createTime +
", updateTime=" + updateTime +
", deleteFlag=" + deleteFlag +
'}';
}
}
2.2Mapper层
public interface UserMapper extends BaseMapper<User> {
}
2.3自定义AnalysisEventListener
public class ExcelListener extends AnalysisEventListener {
//可以通过实例获取该值
private List<Object> datas = new ArrayList<Object>();
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
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;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// datas.clear();//解析结束销毁不用的资源
}
}
2.4Controller层
package com.example.ceshi.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.ceshi.entity.User;
import com.example.ceshi.mapper.UserMapper;
import com.example.ceshi.utils.ExcelListener;
import com.example.ceshi.utils.JsonData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
/**
* <p>
* 前端控制器
* </p>
*
* @author lcy
* @since 2021-02-25
*/
@RestController
public class UserController {
@Autowired
UserMapper userMapper;
@RequestMapping("myImport")
public JsonData myImport(MultipartFile file) {
try {
//获取文件名
String filename = file.getOriginalFilename();
//获取文件流
InputStream inputStream = file.getInputStream();
//实例化实现了AnalysisEventListener接口的类
ExcelListener listener = new ExcelListener();
EasyExcelFactory.read(inputStream, User.class, listener).headRowNumber(1).build().readAll();
//获取数据
List<Object> list = listener.getDatas();
if (list.size() > 1) {
for (int i = 0; i < list.size(); i++) {
User user = (User) list.get(i);
System.out.println(user.toString());
}
}
} catch (Exception e) {
e.printStackTrace();
}
return JsonData.buildSuccess();
}
@RequestMapping("myExport")
public void myExport(HttpServletResponse response, HttpServletRequest request) {
try {
String filenames = "111111";
String userAgent = request.getHeader("User-Agent");
if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
filenames = URLEncoder.encode(filenames, "UTF-8");
} else {
filenames = new String(filenames.getBytes("UTF-8"), "ISO-8859-1");
}
response.setContentType("application/json.ms-exce");
response.setCharacterEncoding("utf-8");
response.addHeader("Content-Disposition", "filename=" + filenames + ".xlsx");
// Step1：创建一个 QueryWrapper 对象
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// Step2： 构造查询条件
queryWrapper
.select("*");
// Step3：执行查询
List<User> userList = userMapper.selectList(queryWrapper);
EasyExcel.write(response.getOutputStream(), User.class).sheet("sheet").doWrite(userList);
} catch (Exception e) {
e.printStackTrace();
}
}
}
三、数据与接口截图
3.1Excel
3.2数据库中信息
3.3批量导入接口
## 3.4批量导出Excel接口
