Easyexcel
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel
项目github地址：https://github.com/alibaba/easyexcel
官网地址：https://easyexcel.opensource.alibaba.com/docs/current/
先吐槽一下，官方文档写的太烂了，缺少依赖、逻辑不清晰、复制到idea很多跑起来报错
写Excel
创建项目，在pom文件中添加以下依赖：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| <dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
</dependencies>
|
创建一个实体类，用于写入excel
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| @Data
public class Student implements Serializable {
@ExcelProperty(value = {"学生编号"},order = 10)
private Integer id;
@ExcelProperty(value = {"学生姓名"},order = 2)
private String name;
@ExcelProperty(value = {"学生薪水"},order = 1)
private Double salary;
@ExcelProperty(value = {"学生生日"},order = 11)
private Date birthday;
}
|
最简单的写入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
public List<Student> getData() {
List<Student> lists = new ArrayList<>();
for(int i = 0; i <= 10; i++) {
Student student = new Student();
student.setId(i + 1);
student.setName("李四" + i);
student.setBirthday(new Date());
student.setSalary(1500.00D);
lists.add(student);
}
return lists;
}
@Test
void contextLoads() {
EasyExcel.write("学生信息表.xlsx", Student.class).sheet().doWrite(getData());
}
|
PS:一下方法都是有关最简单写入的具体方法解释，不感兴趣的可以跳过
write方法
代码解释：
EasyExcel.write源码如下：两个重载的方法，接收文件名（含路径）或文件名和Excel头文件
，new 了一个ExcelWriterBuilder对象，将pathName或head写入excelWriterBuilder并返回
- pathName:文件名
- head:写入表格的class对象，如上方Student.class对象
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
| public class EasyExcelFactory {
public static ExcelWriterBuilder write() {
return new ExcelWriterBuilder();
}
public static ExcelWriterBuilder write(File file) {
return write(file, null);
}
public static ExcelWriterBuilder write(File file, Class head) {
ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder();
excelWriterBuilder.file(file);
if (head != null) {
excelWriterBuilder.head(head);
}
return excelWriterBuilder;
}
public static ExcelWriterBuilder write(String pathName) {
return write(pathName, null);
}
public static ExcelWriterBuilder write(String pathName, Class head) {
ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder();
excelWriterBuilder.file(pathName);
if (head != null) {
excelWriterBuilder.head(head);
}
return excelWriterBuilder;
}
public static ExcelWriterBuilder write(OutputStream outputStream) {
return write(outputStream, null);
}
public static ExcelWriterBuilder write(OutputStream outputStream, Class head) {
ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder();
excelWriterBuilder.file(outputStream);
if (head != null) {
excelWriterBuilder.head(head);
}
return excelWriterBuilder;
}
}
|
sheet()方法
1
2
3
4
5
6
7
| @Test
void contextLoads() {
EasyExcel
.write("学生信息表.xlsx", Student.class)
.sheet()
.doWrite(getData());
}
|
sheet()方法代表要在excel那个sheet页写入数据，如果不指定，，默认写在第一个sheet页，默认sheet名：0
源码解释：所有重载的方法可归为一个方法，接收sheetNo(第几个sheet（Integer）)，sheetName(sheet页名称(String))，返回ExcelWriterSheetBuilder
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
|
public ExcelWriterSheetBuilder sheet() {
return sheet(null, null);
}
public ExcelWriterSheetBuilder sheet(Integer sheetNo) {
return sheet(sheetNo, null);
}
public ExcelWriterSheetBuilder sheet(String sheetName) {
return sheet(null, sheetName);
}
public ExcelWriterSheetBuilder sheet(Integer sheetNo, String sheetName) {
ExcelWriter excelWriter = build();
ExcelWriterSheetBuilder excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
if (sheetNo != null) {
excelWriterSheetBuilder.sheetNo(sheetNo);
}
if (sheetName != null) {
excelWriterSheetBuilder.sheetName(sheetName);
}
return excelWriterSheetBuilder;
|
doWrite方法
源码如下：
1
2
3
4
5
6
7
8
| public void doWrite(List data) {
if (this.excelWriter == null) {
throw new ExcelGenerateException("Must use 'EasyExcelFactory.write().sheet()' to call this method");
} else {
this.excelWriter.write(data, this.build());
this.excelWriter.finish();
}
}
|
如上可见，doWrite方法只是简单判断了一下当前ExcelWriterSheetBuilder类是否为空，然后调用了write方法，再关闭excelWriter
我们在深入看一下write方法
1
2
3
4
5
6
7
8
| public ExcelWriter write(List data, WriteSheet writeSheet) {
return this.write(data, (WriteSheet)writeSheet, (WriteTable)null);
}
public ExcelWriter write(List data, WriteSheet writeSheet, WriteTable writeTable) {
this.excelBuilder.addContent(data, writeSheet, writeTable);
return this;
}
|
本质上这两个方法属于一个方法，都是接收三个参数，返回ExcelWriter对象
- List data: 要写入的数据
- WriteSheet writeSheet: doWrite方法返回ExcelWriterSheetBuilder中的一个属性，存放了sheet信息
- writeTable: 存放table信息，暂不讨论
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
@Deprecated
public ExcelWriter write(List data, Sheet sheet) {
return this.write(data, (Sheet)sheet, (Table)null);
}
@Deprecated
public ExcelWriter write(List data, Sheet sheet, Table table) {
WriteSheet writeSheet = null;
if (sheet != null) {
writeSheet = new WriteSheet();
writeSheet.setSheetNo(sheet.getSheetNo() - 1);
writeSheet.setSheetName(sheet.getSheetName());
writeSheet.setClazz(sheet.getClazz());
writeSheet.setHead(sheet.getHead());
writeSheet.setTableStyle(sheet.getTableStyle());
writeSheet.setRelativeHeadRowIndex(sheet.getStartRow());
writeSheet.setColumnWidthMap(sheet.getColumnWidthMap());
}
return this.write(data, writeSheet, writeTable);
}
|
已过时方法，本质如上,根据return语句看，还是调用以上方法，
接着看一下addContent
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| public void addContent(List data, WriteSheet writeSheet) {
this.addContent(data, writeSheet, (WriteTable)null);
}
public void addContent(List data, WriteSheet writeSheet, WriteTable writeTable) {
try {
this.context.currentSheet(writeSheet, WriteTypeEnum.ADD);
this.context.currentTable(writeTable);
if (this.excelWriteAddExecutor == null) {
this.excelWriteAddExecutor = new ExcelWriteAddExecutor(this.context);
}
this.excelWriteAddExecutor.add(data);
} catch (RuntimeException var5) {
this.finishOnException();
throw var5;
} catch (Throwable var6) {
this.finishOnException();
throw new ExcelGenerateException(var6);
}
}
|
addContent接收了Excel原始数据，通过excelWriteAddExecutor.add写入，具体写入过程可看此方法
表头操作
属性注解
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| @Data
public class Student implements Serializable {
@ExcelProperty(value = {"学生编号"},order = 10)
@NumberFormat("#,###")
private Integer id;
@ExcelProperty(value = {"学生姓名"},order = 2)
private String name;
@ExcelProperty(value = {"学生薪水"},order = 1)
@ExcelIgnore
private Double salary;
@ExcelProperty(value = {"学生生日"},order = 11)
@DateTimeFormat("yyyy-mm-dd")
private Date birthday;
}
|
如上所见，我们在每个属性上加入了@ExcelProperty注解，表示当前属性是一个excel字段，easyexcel采用了注解的方式定义excel 表头字段，给我们开发带来了极大的便利。
ExcelProperty index 指定写到第几列，默认根据成员变量排序。value指定写入的名称，默认成员变量的名字，多个value可以参照快速开始中的复杂头ExcelIgnore 默认所有字段都会写入excel，这个注解会忽略这个字段DateTimeFormat 日期转换，将Date写到excel会调用这个注解。里面的value参照java.text.SimpleDateFormatNumberFormat 数字转换，用Number写excel会调用这个注解。里面的value参照java.text.DecimalFormatExcelIgnoreUnannotated 默认不加ExcelProperty 的注解的都会参与读写，加了不会参与
还有其他注解，地址都在：com.alibaba.excel.annotation
