一、前期配置

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>

二、使用步骤

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;}@Overridepublic String toString() {return "User{" +"id=" + id +", name='" + name + '\'' +", age=" + age +", email='" + email + '\'' +", createTime=" + createTime +", updateTime=" + updateTime +", deleteFlag=" + deleteFlag +'}';}
}

2、Mapper层

public interface UserMapper extends BaseMapper<User> {}

3、自定义AnalysisEventListener

public class ExcelListener extends AnalysisEventListener {//可以通过实例获取该值private List<Object> datas = new ArrayList<Object>();@Overridepublic 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;}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {// datas.clear();//解析结束销毁不用的资源}
}

4、Controller层

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 {@AutowiredUserMapper 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());//使用mybatis-plus添加到数据库userMapper.insert(user);}}} 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();}}}

5、Util工具类

package com.example.ceshi.utils;import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;import java.io.Serializable;@ApiModel("接口实体类")
public class JsonData implements Serializable{/*** 状态码 0表示成功过，-1,-2,-3、、、为失败*/@ApiModelProperty("状态码 0表示成功过，-1,-2,-3、、、为失败")private Integer code;/*** 业务数据*/@ApiModelProperty("业务数据")private Object data;/*** 信息表示*/@ApiModelProperty("信息表示")private String msg;public JsonData() {}public JsonData(Integer code, Object data, String msg) {this.code = code;this.data = data;this.msg = msg;}/*** 成功，不用返回数据** @return*/public static JsonData buildSuccess() {return new JsonData(0, null, null);}/*** 成功，返回数据** @param data* @return*/public static JsonData buildSuccess(Object data) {return new JsonData(0, data, null);}/*** 成功，返回数据** @param msg* @return*/public static JsonData buildSuccess(String msg) {return new JsonData(0, null, msg);}/*** 成功，返回数据，信息* @param data* @param msg* @return*/public static JsonData buildSuccess(Object data,String msg) {return new JsonData(0, data, msg);}/*** 失败，固定状态码** @param msg* @return*/public static JsonData buildError(String msg) {return new JsonData(-1, null, msg);}/*** 失败，自定义错误码和信息** @param code* @param msg* @return*/public static JsonData buildError(Integer code, String msg) {return new JsonData(code, null, msg);}public Integer getCode() {return code;}public void setCode(Integer code) {this.code = code;}public Object getData() {return data;}public void setData(Object data) {this.data = data;}public String getMsg() {return msg;}public void setMsg(String msg) {this.msg = msg;}
}