1.easyexcel简介–POI
EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。
源码：https://github.com/alibaba/easyexcel
文档：https://easyexcel.opensource.alibaba.com
项目介绍
a.目录：
b.依赖(pom)：
4.0.0 org.springframework.boot spring-boot-starter-parent 2.3.12.RELEASE com.lmy qy163-easyexcel1 0.0.1-SNAPSHOT qy163-easyexcel1 qy163-easyexcel1 8 org.springframework.boot spring-boot-starter-web com.alibaba easyexcel 3.0.5 com.alibaba fastjson 1.2.83 org.projectlombok lombok true org.springframework.boot spring-boot-starter-test test mysql mysql-connector-java com.baomidou mybatis-plus-boot-starter 3.5.1 org.springframework.boot spring-boot-maven-plugin org.projectlombok lombok
2.easyexcel完成写操作
（1）引入easyexcel的依赖
com.alibaba easyexcel 3.0.5
（2）创建一个实体类
a.ExcelDemo
package com.lmy.excel;import com.alibaba.excel.annotation.ExcelIgnore;import com.alibaba.excel.annotation.ExcelProperty;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructorpublic class ExcelDemo { @ExcelProperty(value = "编号") //value 表示标头 private Integer id; @ExcelProperty(value = "姓名") //value 表示标头 private String name; @ExcelProperty(value = "年龄") //value 表示标头 private Integer age; @ExcelIgnore //表示该属性的值不会被写入到excel private String address;} （3）编写代码
a.TestWrite1
package com.lmy.excel;import com.alibaba.excel.EasyExcel;import java.util.ArrayList;import java.util.List;public class TestWrite1 { public static void main(String[] args) { //excel 存在哪个位置 String fileName = "E:JavaWorkqy163-easyexcel1qy163.xlsx"; // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭 // 如果这里想使用03 则 传入excelType参数即可 List data = new ArrayList<>(); data.add(new ExcelDemo(1,"喜羊羊",10,"羊村")); data.add(new ExcelDemo(2,"美羊羊",5,"羊村")); data.add(new ExcelDemo(3,"熊大",18,"狗熊岭")); data.add(new ExcelDemo(4,"熊二",15,"狗熊岭")); EasyExcel.write(fileName, ExcelDemo.class).sheet("lmy").doWrite(data); }} 3.easyexcel的web操作
a.UploadController
package com.lmy.controller;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.util.MapUtils;import com.alibaba.fastjson.JSON;import com.lmy.excel.ExcelDemo;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.GetMapping;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.net.URLEncoder;import java.util.ArrayList;import java.util.List;import java.util.Map;@Controllerpublic class UploadController { @GetMapping("/upload") public void downloadFailedUsingJson(HttpServletResponse response) throws IOException { List data = new ArrayList<>(); data.add(new ExcelDemo(1,"喜羊羊",10,"羊村")); data.add(new ExcelDemo(2,"美羊羊",5,"羊村")); data.add(new ExcelDemo(3,"熊大",18,"狗熊岭")); data.add(new ExcelDemo(4,"熊二",15,"狗熊岭")); upload(response,"qy163五组学员信息222",data); } public void upload(HttpServletResponse response,String title,List data) throws IOException { // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode(title, "UTF-8").replaceAll("+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); // 这里需要设置不关闭流 EasyExcel.write(response.getOutputStream(), ExcelDemo.class).autoCloseStream(Boolean.FALSE).sheet("模板") .doWrite(data); } catch (Exception e) { // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map map = MapUtils.newHashMap(); map.put("status", "failure"); map.put("message", "下载文件失败" + e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); } }} 4.easyexcel完成读操作
（1）监听器
a.ExcelDemoListener
package com.lmy.excel;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.read.listener.ReadListener;import com.alibaba.excel.util.ListUtils;import com.alibaba.fastjson.JSON;import lombok.extern.slf4j.Slf4j;import java.util.List;// 有个很重要的点 ExcelDemoListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去@Slf4jpublic class ExcelDemoListener implements ReadListener{ private static final int BATCH_COUNT = 100; private List cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); private DemoDAO demoDAO; public ExcelDemoListener() { // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 demoDAO = new DemoDAO(); } public ExcelDemoListener(DemoDAO demoDAO) { this.demoDAO = demoDAO; } @Override public void invoke(ExcelDemo data, AnalysisContext context) { log.info("解析到一条数据:{}", JSON.toJSONString(data)); cachedDataList.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (cachedDataList.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 log.info("~~~~~~~~~~~~~~~~~~~~~~~~"); saveData(); log.info("所有数据解析完成！"); } private void saveData() { log.info("{}条数据，开始存储数据库！", cachedDataList.size()); demoDAO.save(cachedDataList); log.info("存储数据库成功！"); }}
（2）持久层
a.DemoDAO
package com.lmy.excel;import java.util.List;public class DemoDAO { public void save(List list) { // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入 System.out.println("保存数据到数据库=====批量保存"); }} （3）测试代码
a.TestRead1
package com.lmy.excel;import com.alibaba.excel.EasyExcel;public class TestRead1 { public static void main(String[] args) { // 写法3： String fileName = "E:JavaWorkqy163-easyexcel1qy163.xlsx"; // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 EasyExcel.read(fileName, ExcelDemo.class, new ExcelDemoListener()).sheet().doRead(); }} 5.easyexcel完成读操作并保存到数据库
a.application.properties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.username=rootspring.datasource.password=123456spring.datasource.url=jdbc:mysql:///qy163?serverTimezone=Asia/Shanghai
b.User
package com.lmy.pojo;import com.alibaba.excel.annotation.ExcelProperty;import com.baomidou.mybatisplus.annotation.TableId;import com.baomidou.mybatisplus.annotation.TableName;import lombok.Data;@Data@TableName("tbl_user")public class User { @TableId @ExcelProperty("编号") private int id; @ExcelProperty("姓名") private String name; @ExcelProperty("年龄") private Integer age;} c.UserDao
package com.lmy.dao;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.lmy.pojo.User;import java.util.List;public interface UserDao extends BaseMapper{ void batchSave(List cachedDataList);}
d.UserDataListener
package com.lmy.listener;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.read.listener.ReadListener;import com.alibaba.excel.util.ListUtils;import com.alibaba.fastjson.JSON;import com.lmy.pojo.User;import com.lmy.dao.UserDao;import lombok.extern.slf4j.Slf4j;import java.util.List;// 有个很重要的点 UserListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去@Slf4jpublic class UserDataListener implements ReadListener{ private static final int BATCH_COUNT = 100; private List cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); private UserDao userDao; public UserDataListener() { // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 //userDao = new UserDao(); } public UserDataListener(UserDao userDao) { this.userDao = userDao; } @Override public void invoke(User data, AnalysisContext context) { log.info("解析到一条数据:{}", JSON.toJSONString(data)); cachedDataList.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (cachedDataList.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 log.info("~~~~~~~~~~~~~~~~~~~~~~~~"); saveData(); log.info("所有数据解析完成！"); } private void saveData() { log.info("{}条数据，开始存储数据库！", cachedDataList.size()); userDao.batchSave(cachedDataList); log.info("存储数据库成功！"); }}
e.TblUserMapper.xml
insert into tbl_user values (#{user.id},#{user.name},#{user.age})
f.Qy163Easyexcel1Application
package com.lmy;import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication@MapperScan(basePackages = "com.lmy.dao")public class Qy163Easyexcel1Application { public static void main(String[] args) { SpringApplication.run(Qy163Easyexcel1Application.class, args); }} g.Qy163Easyexcel1ApplicationTests
package com.lmy;import com.alibaba.excel.EasyExcel;import com.lmy.dao.UserDao;import com.lmy.listener.UserDataListener;import com.lmy.pojo.User;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;@SpringBootTestclass Qy163Easyexcel1ApplicationTests { @Autowired UserDao userDao; @Test void contextLoads() { // 写法3： String fileName = "E:JavaWorkqy163-easyexcel1qy163.xlsx"; // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 EasyExcel.read(fileName, User.class, new UserDataListener(userDao)).sheet().doRead(); }} 6.web中的读
a.UploadController
package com.lmy.controller;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.util.MapUtils;import com.alibaba.fastjson.JSON;import com.lmy.dao.UserDao;import com.lmy.excel.ExcelDemo;import com.lmy.listener.UserDataListener;import com.lmy.pojo.User;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.net.URLEncoder;import java.util.ArrayList;import java.util.List;import java.util.Map;@Controllerpublic class UploadController { @Autowired private UserDao userDao; @PostMapping("upload2") @ResponseBody public String upload2(MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), User.class, new UserDataListener(userDao)).sheet().doRead(); return "success"; } @GetMapping("/upload") public void downloadFailedUsingJson(HttpServletResponse response) throws IOException { List data = new ArrayList<>(); data.add(new ExcelDemo(1,"喜羊羊",10,"羊村")); data.add(new ExcelDemo(2,"美羊羊",5,"羊村")); data.add(new ExcelDemo(3,"熊大",18,"狗熊岭")); data.add(new ExcelDemo(4,"熊二",15,"狗熊岭")); upload(response,"qy163五组学员信息",data); } public void upload(HttpServletResponse response,String title,List data) throws IOException { // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode(title, "UTF-8").replaceAll("+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); // 这里需要设置不关闭流 EasyExcel.write(response.getOutputStream(), ExcelDemo.class).autoCloseStream(Boolean.FALSE).sheet("模板") .doWrite(data); } catch (Exception e) { // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map map = MapUtils.newHashMap(); map.put("status", "failure"); map.put("message", "下载文件失败" + e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); } }} postman测试
postman测试
