EasyExcel学习

一、EasyExcel简介

一、EasyExcel是什么

EasyExcel是一个基于Java的简单、省内存的读写Excel的阿里开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

官网：https://easyexcel.opensource.alibaba.com/

学习Easyexcel前需要了解导入和导出是什么意思：

导入：一般我们会把数据从excel到数据库的过程称为导入!

导出：一般我们会把数据从数据库到excel的过程称为导出!

1.2、 EasyExcel 能用在哪里

项目中涉及到Excel文件，CVS文件大多数的读写操作，均可以使用！

1.3、为什么要选用EasyExcel解析excel

二、EasyExcel的使用

2.1、快速入门

首先创建项目，并配置maven。

导入easyexcel的依赖：

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.2</version>
</dependency>

创建一个实体类Employee，与excel表格对应：

/*** 与excel文件相对应的模型类*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {@ExcelProperty("员工编号")private int id;@ExcelProperty("员工姓名")private String name;@ExcelProperty("入职日期")private Date date;@ExcelProperty("员工工资")private double salary;}

这里需要一个工具类，用户获取到模块这一级的磁盘路径：

/*** 获取代码路径的工具类，可以获取到模块这一级的磁盘路径；*/
public class TestFileUtil {public static String getPath() {return TestFileUtil.class.getResource("/").getPath().replace("classes/","");}
}

简单写excel：

首先需要准备测试数据：

// 准备测试数据的方法
private List<Employee> data(int count) {List<Employee> list = ListUtils.newArrayList();for (int i = 1; i <= count; i++) {list.add(new Employee(i,"测试数据"+i,new Date(),6.6*i));}return list;
}

实现写操作：

@Test
public void write(){//文件的路径和名字String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";EasyExcel.write(fileName, Employee.class).sheet("模板666").doWrite(data(10)); //sheet：表格名字
}

到对应的路径下打开excel，查看测试结果：

简单读excel：

读操作需要传入监听器，这里使用EasyExcel提供的PageReadListener：

@Test
public void read(){String fileName = TestFileUtil.getPath() + "simpleWrite1750300831958.xlsx";EasyExcel.read(fileName, Employee.class, new PageReadListener<Employee>(dataList -> {//读取数据后对数据执行的操作，这里直接输出for (Employee demoData : dataList) {System.out.println(demoData);}})).sheet().doRead();
}

在控制台查看测试结果：

2.2、EasyExcel进阶操作

批量写数据：

这里写100万数据，每次写1万，执行100次：

// 批量写数据  100万
@Test
public void write(){String fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class).build()) {WriteSheet writeSheet = EasyExcel.writerSheet("测试数据").build();long t1 = System.currentTimeMillis();for (int i = 0; i < 100; i++) {List<Employee> data = data(10000);excelWriter.write(data, writeSheet);}long t2 = System.currentTimeMillis();//测试执行效率：共耗时10秒System.out.println("共耗时" + (t2-t1)/1000 + "秒"); }
}

测试结果显示写100万数据共耗时10秒。

按模版填充单个对象数据：

如果上面写的数据格式不好看怎么办?

可以利用阿里提供的模版写数据，模版设置的样式新添加的数据会自动包含样式。

可以使用{}来填充数据：

如果传入的不是单个对象，而是一个集合可以在字段前面加一个点就可以：

首先，需要准备一个execl模版：

// 批量写数据  100万
@Test
public void write(){//分多次填充，会使用文件缓存（省内存）String fileName = TestFileUtil.getPath() + "listFill" + System.currentTimeMillis() + ".xlsx";String templateFileName = TestFileUtil.getPath() + "模版.xlsx";try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {WriteSheet writeSheet = EasyExcel.writerSheet().build();long t1 = System.currentTimeMillis();for (int i = 0; i < 100; i++) {excelWriter.fill(data(10000), writeSheet);}long t2 = System.currentTimeMillis();System.out.println(t2-t1); //测试执行效率}
}

测试结果：

自定义监听器读海量数据：

自定义监听器读数据：

/*** 自定义监听器读数据*/
public class EmployeeListener implements ReadListener<Employee> {private int count = 100; //缓存量private ArrayList<Employee> list = new ArrayList<>(count);private EmployeeDao dao;public EmployeeListener(EmployeeDao dao) {this.dao = dao;}/*** 每读一行数据,都会调用这个方法*/@Overridepublic void invoke(Employee employee, AnalysisContext analysisContext) {// 将读取到的一行数据添加到集合list.add(employee);// 判断是不是到达缓存量了if(list.size()>=100){// 操作数据库dao.save(list);// 清空缓存list= new ArrayList<>(count);}}/*** 读完整个excel之后,会调用这个方法* 最后list中还会存在元素，执行这个方法处理剩余的元素*/@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {if(list.size()>0){// 操作数据库dao.save(list);list= new ArrayList<>(count);}}}

这里只是模拟了一下操作数据库的操作：

/*** 模拟操作数据库*/
public class EmployeeDao {public void save(List<Employee> list){System.out.println(list.size()+"模拟操作数据库......");}
}

读取海量数据：

/*** 自定义监听器,读海量数据*/
public class ManyRead {@Testpublic void read(){String fileName = TestFileUtil.getPath()+"repeatedWrite1750302016738.xlsx";ExcelReader reader = EasyExcel.read(fileName, Employee.class, new EmployeeListener(new EmployeeDao())).build();ReadSheet sheet = EasyExcel.readSheet().build();reader.read(sheet);}}

测试，在控制台查看输出结果！

2.3、EasyExcel综合应用

需求：

• 实现文件导入功能。

• 实现文件导出功能。

功能实现：

1. 配置持久层配置，并引入相关依赖：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///easyexcel?useSSL=false&useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=newpass

2. 创建实体类Employee：

/*** 员工实体类*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {@ExcelProperty("员工工号")private int id;@ExcelProperty("员工姓名")private String name;@ExcelProperty("员工工资")private double salary;@ExcelProperty("入职日期")private Date date;}

3. 创建工具类TestFileUtil来获取文件路径：

/*** 获取代码路径的工具类，可以获取到模块这一级的磁盘路径；*/
public class TestFileUtil {public static String getPath() {return TestFileUtil.class.getResource("/").getPath().replace("classes/","");}public static void main(String[] args) {System.out.println(getPath());}}

4. 创建持久层EmployeeMapper：

/*** 持久层*/
public interface EmployeeMapper {/*** 批量插入数据*/void beathInsert(@Param("list") List<Employee> list);/*** 查询数据*/@Select("select * from employee")@ResultType(Employee.class)List<Employee> getData();}

5. 创建EmployeeMapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmployeeMapper"><!--批量插入数据--><insert id="beathInsert">insert into employee (id,name,salary,date) values<foreach collection="list" item="demoData" separator=",">(null,#{demoData.name},#{demoData.salary},#{demoData.date})</foreach></insert></mapper>

6. 创建接口层EmployeeService：

/*** Service接口层*/
public interface EmployeeService {public List<Employee> getData();public void addData(List<Employee> list);}

7. 创建业务实现类EmployeeServiceImpl：

/*** 业务层*/
@Service
public class EmployeeServiceImpl implements EmployeeService {@Autowiredprivate EmployeeMapper dao;@Overridepublic List<Employee> getData() {return dao.getData();}@Overridepublic void addData(List<Employee> list) {dao.beathInsert(list);}}

8. 创建监听器EmployeeListener：

public class EmployeeListener implements ReadListener<Employee> {private int count = 10000;private EmployeeService dao ;private List<Employee> list = new ArrayList<>(count);public EmployeeListener(EmployeeService dao) {this.dao = dao;}@Overridepublic void invoke(Employee employee, AnalysisContext analysisContext) {list.add(employee);if(list.size()>=count){dao.addData(list);list = new ArrayList<>(count);}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {if(list.size()>0){dao.addData(list);}}}

9. 创建控制层MyController：

@Controller
@RequestMapping("/")
public class MyController {@Autowiredprivate EmployeeService service;/*** Excel读操作*/@RequestMapping("/upload")@ResponseBodypublic void upload(MultipartFile file, HttpServletResponse response) throws IOException {long t1 = System.currentTimeMillis();//Excel读操作EasyExcel.read(file.getInputStream(), Employee.class, new EmployeeListener(service)).sheet().doRead();long t2 = System.currentTimeMillis();response.setContentType("text/html;charset=utf-8");response.getWriter().print("导入数据成功,共用时:"+(t2-t1));}/*** Excel写操作*/@RequestMapping("/download")public void download(HttpServletResponse response) throws IOException {//设置Excel文件下载的类型response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("测试666", "UTF-8").replaceAll("\\+", "%20");//Content-disposition：可以控制文件是直接在浏览器中显示还是作为附件下载，这里是直接作为附件下载，文件名字是fileName.xlsxresponse.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");//Excel写操作EasyExcel.write(response.getOutputStream(), Employee.class).sheet("模板").doWrite(service.getData());}}

10. 测试：

前端页面：

导入100万条数据的excel，查看数据库：

可以看到，导入100万数据仅仅耗时26s：

并且内存占用为20多兆：