前言

之前出过一期easyExcel在SSM的环境下使用流程，本篇演示在springboot环境下easyExcel的使用
上篇链接
使用EasyExcel实现表格的导入导出【http://t.csdn.cn/0MLgt】

一、导入依赖

 <!-- easyexcel依赖--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency>

二、配置监听器

package com.lzl.idpac.listener;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.lzl.idpac.entity.Student;
import com.lzl.idpac.entity.StudentExcel;
import com.lzl.idpac.service.StudentService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;/*** --效率，是成功的核心关键--* 学生信息管理导入excel表格监听器** @Author lzl* @Date 2023/1/30 21:05*/
@Component
@Scope("prototype")//标记此处的监听器为多例的，防止并发读操作时出现错误
public class StudentReadListener implements ReadListener<StudentExcel> {@Autowiredprivate StudentService service;//注入学生管理业务层接口@Overridepublic void invoke(StudentExcel studentExcel, AnalysisContext analysisContext) {//每读取一行，就调用一次，把每一行数据封装到实体类中Student student = new Student();BeanUtils.copyProperties(studentExcel,student);//属性不一致时使用属性拷贝//导入之前做一个去重判断Integer key =  service.findBySno(student.getStuNo());if(key != 1 ){//数据库中没有该学号service.addNew(student);//调用新增方法}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {//数据解析完毕执行方法}
}

三、controller编写

package com.lzl.idpac.controller;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.lzl.idpac.entity.Student;
import com.lzl.idpac.entity.StudentExcel;
import com.lzl.idpac.listener.StudentReadListener;
import com.lzl.idpac.service.StudentService;
import com.lzl.idpac.utils.LayUiUtil;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;/*** --效率，是成功的核心关键--* excel表格上传控制层** @Author lzl* @Date 2023/1/30 21:18*/
@RestController
@RequestMapping("/excel")
public class ExcelController {@Autowiredprivate StudentReadListener listener;//注入监听器@Autowiredprivate StudentService service;//注入学生管理业务层/***excel数据写入数据库* @param file 获得前端上传的文件  EasyExcel.read 需要传入三个参数 文件流 操作实体类的字节码 监听器* @return 0 成功上传* @throws IOException*/@RequestMapping("/read")@ResponseBodypublic LayUiUtil<String> readExcel(MultipartFile file) throws IOException {      // 得到excel读取对象  //通过文件获得流， 获得读取文件的class    填入监听器 监听器每读取一行就执行一次新增ExcelReaderBuilder read = EasyExcel.read(file.getInputStream(), StudentExcel.class, listener);//获取表格ExcelReaderSheetBuilder sheet = read.sheet();//读取表格sheet.doRead();//设置容器LayUiUtil<String> layUiUtil = new LayUiUtil<>();//配置前端响应状态码layUiUtil.setCode(0);//配置服务器返回信息layUiUtil.setMsg("已成功导入");return layUiUtil;}/*** 导出选中的数据到excel表格* @param stuNos* @param stuCollegeNo* @param response* @throws IOException*/@RequestMapping("/write")public void writeExcel(String stuNos,String stuCollegeNo, HttpServletResponse response) throws IOException {//设置响应头response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");//导出的文件名String filename = URLEncoder.encode("学生信息","utf-8");//设置响应头response.setHeader("Content-Disposition","attachment;filename="+filename+".xlsx");//获得流对象ServletOutputStream outputStream = response.getOutputStream();//获得write对象ExcelWriterBuilder write = EasyExcel.write(outputStream, StudentExcel.class);//获得数据表对象ExcelWriterSheetBuilder sheet = write.sheet();//准备需要输出的数据List<StudentExcel> list = service.getExcelDataByNos(stuNos,stuCollegeNo);//生成表格文件sheet.doWrite(list);}
}

进行导入操作时，也可以不配置监听器，通过匿名内部类实现，代码如下：

    private StudentService service;//注入学生管理业务层/***excel数据写入数据库* @param file 获得前端上传的文件  EasyExcel.read 需要传入三个参数 文件流 操作实体类的字节码 监听器* @return 0 成功上传* @throws IOException*/@RequestMapping("/read")@ResponseBodypublic LayUiUtil<String> readExcel(MultipartFile file,String stuCollegeNo) throws IOException {//匿名内部类EasyExcel.read(file.getInputStream(), StudentExcel.class, new ReadListener<StudentExcel>() {/*** 单次缓存的数据量*/public static final int BATCH_COUNT = 100;/***临时存储*/private List<StudentExcel> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);@Overridepublic void invoke(StudentExcel data, AnalysisContext context) {cachedDataList.add(data);if (cachedDataList.size() >= BATCH_COUNT) {saveData();//调用存储数据// 存储完成清理 listcachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}/*** 读取完之后执行的方法* @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {saveData();}/*** 加上存储数据库*/private void saveData() {for(StudentExcel studentExcel : cachedDataList) {Student student = new Student();if(service.findBySno(studentExcel.getStuNo()) != 1){BeanUtils.copyProperties(studentExcel,student);student.setStuCollegeNo(stuCollegeNo);service.addNew(student);}}}}).sheet().doRead();
//第二种方法，采用监听器操作
//        // 得到excel读取对象  //通过文件获得流， 获得读取文件的class    填入监听器 监听器每读取一行就执行一次新增
//        ExcelReaderBuilder read = EasyExcel.read(file.getInputStream(), StudentExcel.class, listener);
//        //获取表格
//        ExcelReaderSheetBuilder sheet = read.sheet();
//        //读取表格
//        sheet.doRead();//设置容器LayUiUtil<String> layUiUtil = new LayUiUtil<>();//配置前端响应状态码layUiUtil.setCode(0);//配置服务器返回信息layUiUtil.setMsg("已成功导入");return layUiUtil;}

匿名内部类的方法虽然不如监听器那么简洁，但是可以让我们在controller层传入一些额外的参数，做一些别的操作。使用监听器显然是无法传入的。例如在本业务中，我在新增学生，需要同步给学生加一个学院id的属性，使用监听器无法传入学院id，但是使用匿名内部类却可以直接使用这个学院id。真实开发中这些业务逻辑还是要放在service层，此处只是学习之用，就无所谓了

四、Service业务层

接口

    /*** 根据学号，获取需要导出的数据* @param stuNos 学号字串* @return layui工具类 泛型StudentExcel*/List<StudentExcel> getExcelDataByNos(String stuNos,String stuCollegeNo);/*** 根据学号获得学生信息，用于导入去重判断* @param stuNo* @return 受影响行数*/Integer findBySno(String stuNo);/***新增学生信息* @param student 学生实体* @return layui工具类 泛型Student*/LayUiUtil<Student> addNew(Student student);

业务实现

    /*** 根据学号，获取需要导出的数据** @param stuNos* @return*/@Overridepublic List<StudentExcel> getExcelDataByNos(String stuNos,String stuCollegeNo) {//设置一个数组,存放需要导出的学号ArrayList<String> params = new ArrayList<>();if(stuNos != null && !stuNos.equals("")){//判断前端是否传递了学号String[] strList =  stuNos.split(",");//将需要导出数据的学号遍历添加进集合for (String s : strList) {params.add(s);}}//创建一个集合用来存放数据List<Student> list = new ArrayList<Student>();if(params.size()==0){//如果前端没有传递学号，即为下载模板操作，需要捏造一个测试数据list.add(new Student("202150915100","张三","男",18,"4102222000xxxxxxxx",null,"计算机科学与技术2001","慧苑1号楼101","138xxxxxxx9",null));}else {//如果前端传了学号，则为导出操作，执行持久层，获取需要导出的数据list = mapper.getStudentByStuNo(params);}//使用Lambda表达式把employee转换成employeeBoList<StudentExcel> collect = list.stream().map((student)->{StudentExcel studentExcel = new StudentExcel();BeanUtils.copyProperties(student,studentExcel);return studentExcel;}).collect(Collectors.toList());return collect;}/*** 根据学号获得学生信息，用于导入去重判断** @param stuNo* @return 受影响行数*/@Overridepublic Integer findBySno(String stuNo) {return mapper.findStudentByNo(stuNo);}/*** 新增学生信息实现类* @param student* @return*/@Overridepublic LayUiUtil<Student> addNew(Student student) {LayUiUtil<Student> layUiUtil = new LayUiUtil<>();String IDcard = student.getStuIDcard();//获取身份证号码int nums = IDcard.length()-6;//获取需要截取字串的下标位置String password = student.getStuIDcard().substring(nums);//对字串进行切割获得密码student.setStuPassword(password);//设置进实体类Integer key = mapper.addNew(student);if (key != 0){layUiUtil.setCode(1);layUiUtil.setMsg("新增成功！");}else{layUiUtil.setCode(0);layUiUtil.setMsg("新增失败！");}return layUiUtil;}

五、持久层不再赘述

六、前端

<!-- 上传excel表格 区域--><div class="layui-form-item" id="import" style="display:none;"><form class="layui-form" onsubmit="return false;" id="readExcel"><div class="layui-form-item"><label class="layui-form-label"></label><div class="layui-input-block"><div class="layui-upload"><button type="button" class="layui-btn" id="test1">上传文件</button><div style="width: 95px;"><div class="layui-progress layui-progress-big" lay-showpercent="yes" lay-filter="demo"><div class="layui-progress-bar" lay-percent=""></div></div></div></div></div></div></form></div><script type="text/javascript" src="../lib/layui-v2.6.3/layui.js"></script>
<script src="/static/js/jquery-1.12.4.min.js"></script>
<script type="text/javascript">layui.use(['table', 'layer', 'form', 'transfer','upload','element'], function () {//常规使用 - excel表格上传upload.render({elem: '#test1',accept: 'file' //普通文件,exts: 'xlsx' //上传文件格式, url: 'http://localhost:8080/excel/read' ,data: {stuCollegeNo: $userCollegeNo} //可选项。额外的参数，如：{id: 123, abc: 'xxx'}, done: function (res) {console.log(res);//如果上传失败if (res.code > 0) {layer.msg('上传失败');} else {active.reload();layer.msg(res.msg, {icon: 6,time: 1500 //2秒关闭（如果不配置，默认是3秒）}, function () {//关闭弹出层layer.closeAll();element.progress('demo', '0%'); //进度条复位});}}//进度条, progress: function (n, elem, e) {element.progress('demo', n + '%'); //可配合 layui 进度条元素使用if (n == 100) {//layer.msg('上传完毕', { icon: 1 });}}});});</script>