--- # 主题列表：juejin, github, smartblue, cyanosis, channing-cyan, fancy, hydrogen, condensed-night-purple, greenwillow, v-green, vue-pro, healer-readable, mk-cute, jzman, geek-black, awesome-green, qklhk-chocolate # 贡献主题：https://github.com/xitu/juejin-markdown-themes theme: channing-cyan highlight: --- # 前言感谢阿里巴巴项目组提供了easyexcel工具类，github地址：https://github.com/alibaba/easyexcel # 简介 EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 # 引入依赖本项目用的是gradle ![](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/0339219af29040408a70f4b62fadddaf~tplv-k3u1fbpfcp-watermark.image) compile group: 'com.alibaba', name: 'easyexcel', version: '2.2.6' # 编写实体类对应导入的excel的字段 ``` public class TeacherData { @ExcelProperty(index = 0 , value = "电话号码") private String phone; @ExcelProperty(index = 1 , value = "姓名") private String name; @ExcelProperty(index = 2 , value = "身份证") private String idCard; public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getIdCard() { return idCard; } public void setIdCard(String idCard) { this.idCard = idCard; } } ``` # 编写监听类 ``` public class TeacherDataListener extends AnalysisEventListener { private static final Logger LOGGER = LoggerFactory.getLogger(TeacherDataListener.class); /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 */ private static final int BATCH_COUNT = 5; List list = new ArrayList(); /** * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 */ private TeacherBookimportService teacherBookimportService; /** * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 * * @param */ public TeacherDataListener(TeacherBookimportService teacherBookimportService) { this.teacherBookimportService = teacherBookimportService; } /** * 这个每一条数据解析都会来调用 * * @param data * one row value. Is is same as {@link AnalysisContext#readRowHolder()} * @param context */ @Override public void invoke(TeacherData data, AnalysisContext context) { 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) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 saveData(); LOGGER.info("所有数据解析完成！"); } /** * 加上存储数据库 */ private void saveData() { LOGGER.info("{}条数据，开始存储数据库！", list.size()); teacherBookimportService.save(list); LOGGER.info("存储数据库成功！"); } } ``` # 编写业务接口：入库的逻辑省略接口 ``` public interface TeacherBookimportService { void save(List list); } ``` 实现类 ``` @Service public class teacherBookimportServiceImpl implements TeacherBookimportService { @Override public void save(List list) { if (list != null && list.size() > 0) { for (TeacherData teacherData : list) { System.out.println("日期：" + teacherData.getPhone()); System.out.println("姓名：" + teacherData.getName()); System.out.println("身份证：" + teacherData.getIdCard()); } } } } ``` # 编写Controller调用方法/teacherUpload/upload进行文件上传 ``` @RestController @RequestMapping("/teacherUpload") public class TeacherUploadController { @Autowired private TeacherBookimportService teacherBookimportService; /** * 文件下载（失败了会返回一个有部分数据的Excel） *

* 1. 创建excel对应的实体对象参照{@link } *

* 2. 设置返回的参数 *

* 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大 */ @GetMapping("/download") public void download(HttpServletResponse response) throws IOException { // 这里注意有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码当然和easyexcel没有关系 String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), TeacherData.class).sheet("模板").doWrite(data()); } /** * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel） * * @since 2.1.1 */ @GetMapping("/downloadFailedUsingJson") public void downloadFailedUsingJson(HttpServletResponse response) throws IOException { // 这里注意有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman try { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码当然和easyexcel没有关系 String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); // 这里需要设置不关闭流 EasyExcel.write(response.getOutputStream(), TeacherData.class).autoCloseStream(Boolean.FALSE).sheet("模板") .doWrite(data()); } catch (Exception e) { // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map map = new HashMap(); map.put("status", "failure"); map.put("message", "下载文件失败" + e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); } } /** * 文件上传 *

* 1. 创建excel对应的实体对象参照{@link } *

* 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link } *

* 3. 直接读即可 */ @PostMapping("/upload") @ResponseBody public String upload(@RequestParam("file") MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), TeacherData.class, new TeacherDataListener(teacherBookimportService)).sheet().headRowNumber(2).doRead(); return "success"; } private List data() { List list = new ArrayList(); for (int i = 0; i < 10; i++) { TeacherData data = new TeacherData(); list.add(data); } return list; } } ``` # 导入的excel ![](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/975a506089a5479aa187aad9cdffeabd~tplv-k3u1fbpfcp-watermark.image) # 导入结果 ![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/ee6d1183233a47a8a4434377611d55ca~tplv-k3u1fbpfcp-watermark.image) # 导入简单前端页面让你直接调用后端上传接口把下面代码保存在txt中，将txt的后缀改成html，在里面调用后端上传的接口就可以自己测试 ![](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6bebf981e92843219ea86059e91e5d9a~tplv-k3u1fbpfcp-watermark.image) ```

```

File to upload: