记录一次使用Easyexcel上传导入数据库和导出EXCEL的实现过程

　　这是一个最简单的例子，使用了Springboot/Thymeleaf/Mybatis等技术完成了初步的导入，给和我一样的初学者提供一点参考。
　　一、数据表结构 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;  -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ＂姓名＂,  `age` int(255) NULL DEFAULT NULL COMMENT ＂年龄＂,  PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 350 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;  SET FOREIGN_KEY_CHECKS = 1;
　　二、实体类 package pers.gl.entity;  import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data;  @Data public class User { private String id; @ExcelProperty(value = ＂姓名＂, index = 0) private String name; @ExcelProperty(value = ＂年龄＂, index = 1) private Integer age; }
　　三、mybatis的xml文件
　　使用了批量插入数据的语句foreach。<?xml version=＂1.0＂ encoding=＂UTF-8＂?>              select * from user order by id desc                insert into user(name,age) values(#{name},#{age})                  insert into user(name,age) values                   (#{item.name},#{item.age})             
　　四、mapper接口 package pers.gl.dao; import java.util.List; import pers.gl.entity.User; public interface UserMapper { // 全部数据 public List findAll(); // 插入数据 public int insertData(User user); // 批量增加保存 public int saveList(List list); }
　　五、Service package pers.gl.service;  import java.util.List; import javax.servlet.http.HttpServletResponse; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import com.alibaba.excel.EasyExcel; import pers.gl.dao.UserMapper; import pers.gl.entity.User;  @Service public class UserService { @Autowired private UserMapper userMapper; // 全部数据 public List findAll() { return userMapper.findAll(); } // 插入数据 public int insertData(User user) { return userMapper.insertData(user); } // 批量增加保存 public int saveList(List list) { return userMapper.saveList(list); }    //导出EXCEL文件 public void exportExcel(HttpServletResponse response) { // 需要导出的数据 List list = findAll(); // 设置响应类型 response.setContentType(＂application/vnd.ms-excel＂); // 设置字符编码 response.setCharacterEncoding(＂utf-8＂); // 设置文件名字 String fileName = ＂downFile_＂ + System.currentTimeMillis() + ＂.xlsx＂; try { // 设置响应头信息 response.setHeader(＂Content-Disposition＂, ＂attachment; filename=＂ + java.net.URLEncoder.encode(fileName, ＂UTF-8＂));  // 写入文件数据 EasyExcel.write(response.getOutputStream(), User.class).sheet(＂download＂).doWrite(list); } catch (Exception e) { throw new RuntimeException(e); } }  }
　　六、ExcelListener监听 package pers.gl.service;  import java.util.ArrayList; import java.util.List; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSON; import pers.gl.entity.User;  public class ExcelListener extends AnalysisEventListener { private static final Logger logger = LoggerFactory.getLogger(User.class); /**  * 每隔5条存储数据库，实际使用中可以3000条，然后清理list，方便内存回收  */ private static final int BATCH_COUNT = 5; List list = new ArrayList<>();  private UserService userService;  // 构造函数，一定要写，添加到监听中 public ExcelListener(UserService userService) { this.userService = userService; }  @Override public void invoke(User data, AnalysisContext context) { // TODO Auto-generated method stub logger.info(＂invoke方法被调用＂); logger.info(＂解析到一条数据:{}＂, JSON.toJSONString(data)); list.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list list.clear(); } }  /**  * 所有数据解析完成了 都会来调用  *  * @param context  */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // TODO Auto-generated method stub System.out.println(＂doAfterAllAnalysed方法 被调用＂); // 这里也要保存数据，确保最后遗留的数据也存储到数据库 saveData(); logger.info(＂所有数据解析完成！＂); }  /**  * 加上存储数据库  */ private void saveData() { logger.info(＂{}条数据，开始存储数据库！＂, list.size()); userService.saveList(list); logger.info(＂存储数据库成功！＂); }     }
　　七、Controller控制器 package pers.gl.controller;  import java.io.IOException; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.multipart.MultipartFile; import com.alibaba.excel.EasyExcel; import pers.gl.entity.User; import pers.gl.service.ExcelListener; import pers.gl.service.UserService;  @Controller @RequestMapping(＂user＂) public class UserController { @Autowired private UserService userService;  @GetMapping(＂list＂) public String list(Model model) { List users = userService.findAll(); model.addAttribute(＂users＂, users); return ＂userlist＂; }  @GetMapping(＂/download＂) public void excelExport02(HttpServletResponse response) { userService.exportExcel(response); }  @GetMapping(＂/import＂) public String toImportExcelPage() { return ＂importexcel＂; }  @PostMapping(＂/uploadExcel＂) public String upload(MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), User.class, new ExcelListener(userService)).sheet().doRead(); return ＂redirect:/user/list＂; } }
　　八、前台上传页面