- EasyExcel 入门
1.1 EasyExcel概述
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
官网:https://www.yuque.com/easyexcel/doc/easyexcel
github地址:https://github.com/alibaba/easyexcel
1.2 EasyExcel 特点
Java解析、生成Excel比较有名的框架有Apache poi、jxl,但他们都存在一个严重的问题就是非常的耗内存。
EasyExcel 重写了poi,使一个3M的excel只需要几M内存,并且再大的excel不会出现内存溢出。
64M内存1分钟内读取75M(46W行25列)的Excel。
1.3.0小案例(按照下面步骤完成读写操作)
1.3 环境搭建
1.3.1 测试父项目
项目名:zx-test-parent
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UzpADPJr-1617865963314)(assets/image-20210105221329491.png)]
修改pom文件
<dependencies>
<!-- 测试 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
</dependencies>
1
2
3
4
5
6
7
1.3.2 测试excel项目
项目名:zx-test-excel
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-np155RUX-1617865963316)(assets/image-20210105221522412.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Dog2hnD7-1617865963318)(assets/image-20210105221549990.png)]
修改pom,添加依赖
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
1
2
3
4
5
6
7
8
9
10
11
1.4 基本操作
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6hBpz1BA-1617865963321)(assets/image-20210105224749228.png)]
1.4.1 测试JavaBean
package com.czxy.zx.demo01;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
/**
-
Created by liangtong.
*/
@Data
public class Student {
@ExcelProperty(“编号”)
private String id;@ExcelProperty(“姓名”)
private String name;@ExcelProperty(“年龄”)
private Integer age;@ExcelProperty(“电话”)
private String telephone;@ExcelProperty(“邮箱”)
private String email;@ExcelProperty(“生日”)
private Date brithday;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
1.4.2 测试文件路径
package com.czxy.zx.demo01;
import org.junit.jupiter.api.Test;
/**
-
@author 桐叔
-
@email liangtong@itcast.cn
/
public class TestExcel {
/*- 获得根路径
- @return
*/
public String getPath() {
return this.getClass().getResource("/").getPath();
}
@Test
public void testPath() {
// 测试文件路径
String path = getPath() + “student_demo.xls”;
System.out.println(path);
}
}
~~~java
/**
* 准备数据
* @return
*/
private List<Student> getData(){
List<Student> list = new ArrayList<Student>();
for(int i = 0 ; i < 10 ; i ++){
Student student = new Student();
student.setId("stu" + i);
student.setName("wang" + i);
student.setAge( 18 + i );
student.setTelephone("1361234" + i);
student.setEmail("wang" + i + "@czxy.com");
student.setBrithday(new Date());
list.add(student);
}
return list;
}
@Test
public void testWrite(){
String file = getPath() + "student_demo.xls";
//EasyExcel.write(位置,对象).sheet("表名").doWrite(数据);
EasyExcel.write(file,Student.class).sheet("Java34").doWrite(getData());
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V0pYXhJ3-1617866219721)(assets/image-20210105224831176.png)]
1.4.3 读操作
处理类:
处理类需要实现 AnalysisEventListener 接口
package com.czxy.zx.demo01;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.czxy.zx.domain.Student;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class StudentListener extends AnalysisEventListener<Student> {
@Override
public void invoke(Student student, AnalysisContext analysisContext) {
System.out.println(student);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成");
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
测试
@Test
public void testRead(){
String file = getPath() + "student_demo.xls";
//EasyExcel.read(文件, 封装对象, 处理类).sheet("表").doRead();
EasyExcel.read(file, Student.class, new StudentListener()).sheet("Java34").doRead();
}
1
2
3
4
5
6
1.5 复杂操作
1.5.1 复合表头
package com.czxy.zx.demo02;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import java.util.Date;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
@Data
@ContentRowHeight(10)
@HeadRowHeight(20) //行高
@ColumnWidth(25) //列宽
public class Student2 {
@ExcelProperty("编号")
private String id;
@ExcelProperty({"基本信息","姓名"}) //复制表头
private String name;
@ExcelProperty({"基本信息","年龄"})
private Integer age;
@ExcelProperty("电话")
private String telephone;
@ExcelProperty("邮箱")
private String email;
@ExcelProperty("生日")
@DateTimeFormat("yyyy年MM月dd日")
private Date brithday;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
1.5.2 写操作:多表
package com.czxy.zx.demo02;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.czxy.zx.demo01.Student;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class TestExcel2 {
/**
* 获得根路径
* @return
*/
public String getPath() {
return this.getClass().getResource("/").getPath();
}
/**
* 准备数据
* @return
*/
private List<Student2> getData(Integer flag){
List<Student2> list = new ArrayList<Student2>();
for(int m = 0 ; m < 10 ; m ++){
String i = "" + flag + m ;
Student2 student = new Student2();
student.setId("stu" + i);
student.setName("wang" + i);
student.setAge( 18 );
student.setTelephone("1361234" + i);
student.setEmail("wang" + i + "@czxy.com");
student.setBrithday(new Date());
list.add(student);
}
return list;
}
@Test
public void testMoreSheetWrite(){
String file = getPath() + "student_demo2.xls";
ExcelWriter excelWriter = EasyExcel.write(file).build();
for (int i = 0; i < 5; i++) {
WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(Student2.class).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
List<Student2> data = getData(i);
excelWriter.write(data, writeSheet);
}
excelWriter.finish();
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
1.5.3 读操作:多表
具有缓存处理类
package com.czxy.zx.demo02;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* Created by liangtong.
*/
public class Student2Listener extends AnalysisEventListener<Student2> {
// 批量操作数
private static final int BATCH_COUNT = 10;
// 用于缓存信息
private List<Student2> cache = new ArrayList<Student2>();
public void invoke(Student2 student, AnalysisContext analysisContext) {
//保存学生信息
cache.add(student);
if(cache.size() >= BATCH_COUNT){
// 保存数据
saveData();
}
}
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//最后的不够 BATCH_COUNT 倍数
saveData();
}
private void saveData() {
// 集合不为空
if(! cache.isEmpty()) {
// 处理缓存数据
System.out.println(cache);
// 清空缓存
cache.clear();
}
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
读操作
@Test
public