Java使用easyexcel操作Excel

easyexcel简介

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。easyexcel重写了poi对07版Excel的解析,能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别,并且再大的excel不会出现内存溢出,03版依赖POI的sax模式。在上层做了模型转换的封装,让使用者更加简单方便

easyexcel项目主页:https://github.com/alibaba/easyexcel

easyexcel使用

Maven依赖

1
2
3
4
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
</dependency>

写入Excel

无表头实现

通过com.alibaba.excel.ExcelWriter类实现,每行数据是List

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Test
public void writeWithoutHead() throws IOException {
OutputStream out = new FileOutputStream("withoutHead.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, false);
Sheet sheet1 = new Sheet(1, 0);
sheet1.setSheetName("sheet1");
List<List<String>> data = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<String> item = new ArrayList<>();
item.add("item0" + i);
item.add("item1" + i);
item.add("item2" + i);
data.add(item);
}
writer.write0(data, sheet1);
writer.finish();
}

有表头实现

很多时候,我们在生成Excel的时候都是需要添加表头的,使用easyexcel可以很容易的实现,我们可以对上面的例子进行简单的改造,为其添加表头

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
@Test
public void writeWithHead() throws IOException {
try (OutputStream out = new FileOutputStream("withHead.xlsx");) {
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
Sheet sheet1 = new Sheet(1, 0);
sheet1.setSheetName("sheet1");
List<List<String>> data = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<String> item = new ArrayList<>();
item.add("item0" + i);
item.add("item1" + i);
item.add("item2" + i);
data.add(item);
}
List<List<String>> head = new ArrayList<List<String>>();
List<String> headCoulumn1 = new ArrayList<String>();
List<String> headCoulumn2 = new ArrayList<String>();
List<String> headCoulumn3 = new ArrayList<String>();
headCoulumn1.add("第一列");
headCoulumn2.add("第二列");
headCoulumn3.add("第三列");
head.add(headCoulumn1);
head.add(headCoulumn2);
head.add(headCoulumn3);
Table table = new Table(1);
table.setHead(head);
writer.write0(data, sheet1, table);
writer.finish();
}
}

ExcelProperty注解生成表头

除了上面添加表头的方式,我们还可以使用实体类,为其添加com.alibaba.excel.annotation.ExcelProperty注解来生成表头,实体类数据作为Excel数据

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
@Test
public void writeWithModel() throws IOException {
OutputStream out = new FileOutputStream("withModel.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
Sheet sheet1 = new Sheet(1, 0, ExcelPropertyIndexModel.class);
sheet1.setSheetName("sheet1");
List<ExcelPropertyIndexModel> data = new ArrayList<>();
for (int i = 0; i < 10; i++) {
ExcelPropertyIndexModel item = new ExcelPropertyIndexModel();
item.name = "name" + i;
item.age = "age" + i;
item.email = "email" + i;
item.address = "address" + i;
item.sax = "sax" + i;
item.height = "height" + i;
item.last = "last" + i;
data.add(item);
}
writer.write(data, sheet1);
writer.finish();
}

@Data
public static class ExcelPropertyIndexModel extends BaseRowModel {
@ExcelProperty(value = "姓名", index = 0)
private String name;

@ExcelProperty(value = "年龄", index = 1)
private String age;

@ExcelProperty(value = "邮箱", index = 2)
private String email;

@ExcelProperty(value = "地址", index = 3)
private String address;

@ExcelProperty(value = "性别", index = 4)
private String sax;

@ExcelProperty(value = "身高", index = 5)
private String height;

@ExcelProperty(value = "备注", index = 6)
private String last;
}

多行复杂表头

如果单行表头表头还不满足需求,没关系,还可以使用多行复杂的表头

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
@Test
public void writeWithMultiHead() throws IOException {
OutputStream out = new FileOutputStream("withMultiHead.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
Sheet sheet1 = new Sheet(1, 0, MultiLineHeadExcelModel.class);
sheet1.setSheetName("sheet1");
List<MultiLineHeadExcelModel> data = new ArrayList<>();
for (int i = 0; i < 10; i++) {
MultiLineHeadExcelModel item = new MultiLineHeadExcelModel();
item.p1 = "p1" + i;
item.p2 = "p2" + i;
item.p3 = "p3" + i;
item.p4 = "p4" + i;
item.p5 = "p5" + i;
item.p6 = "p6" + i;
item.p7 = "p7" + i;
item.p8 = "p8" + i;
item.p9 = "p9" + i;
data.add(item);
}
writer.write(data, sheet1);
writer.finish();
}

@Data
public static class MultiLineHeadExcelModel extends BaseRowModel {
@ExcelProperty(value = { "表头1", "表头1", "表头31" }, index = 0)
private String p1;

@ExcelProperty(value = { "表头1", "表头1", "表头32" }, index = 1)
private String p2;

@ExcelProperty(value = { "表头3", "表头3", "表头3" }, index = 2)
private String p3;

@ExcelProperty(value = { "表头4", "表头4", "表头4" }, index = 3)
private String p4;

@ExcelProperty(value = { "表头5", "表头51", "表头52" }, index = 4)
private String p5;

@ExcelProperty(value = { "表头6", "表头61", "表头611" }, index = 5)
private String p6;

@ExcelProperty(value = { "表头6", "表头61", "表头612" }, index = 6)
private String p7;

@ExcelProperty(value = { "表头6", "表头62", "表头621" }, index = 7)
private String p8;

@ExcelProperty(value = { "表头6", "表头62", "表头622" }, index = 8)
private String p9;
}

添加多个表

easyexcel还支持在一个sheet中添加多个表

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
@Test
public void writeWithMultiTable() throws IOException {
OutputStream out = new FileOutputStream("withMultiTable.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
Sheet sheet1 = new Sheet(1, 0);
sheet1.setSheetName("sheet1");

// 数据全是List<String> 无模型映射关系
Table table1 = new Table(1);
List<List<String>> data1 = new ArrayList<>();
for (int i = 0; i < 5; i++) {
List<String> item = new ArrayList<>();
item.add("item0" + i);
item.add("item1" + i);
item.add("item2" + i);
data1.add(item);
}
writer.write0(data1, sheet1, table1);

// 模型上有表头的注解
Table table2 = new Table(2);
table2.setClazz(MultiLineHeadExcelModel.class);
List<MultiLineHeadExcelModel> data2 = new ArrayList<>();
for (int i = 0; i < 5; i++) {
MultiLineHeadExcelModel item = new MultiLineHeadExcelModel();
item.p1 = "p1" + i;
item.p2 = "p2" + i;
item.p3 = "p3" + i;
item.p4 = "p4" + i;
item.p5 = "p5" + i;
item.p6 = "p6" + i;
item.p7 = "p7" + i;
item.p8 = "p8" + i;
item.p9 = "p9" + i;
data2.add(item);
}
writer.write(data2, sheet1, table2);

// 模型上没有注解,表头数据动态传入,此情况下模型field顺序与excel现实顺序一致
List<List<String>> head = new ArrayList<List<String>>();
List<String> headCoulumn1 = new ArrayList<String>();
List<String> headCoulumn2 = new ArrayList<String>();
List<String> headCoulumn3 = new ArrayList<String>();
headCoulumn1.add("第一列");
headCoulumn2.add("第二列");
headCoulumn3.add("第三列");
head.add(headCoulumn1);
head.add(headCoulumn2);
head.add(headCoulumn3);
Table table3 = new Table(3);
table3.setHead(head);
writer.write0(data1, sheet1, table3);

writer.finish();
}

表头样式

如果表头的样式不满足我们的需求,需要调整,我们可以使用com.alibaba.excel.metadata.TableStyle定义我们需要的样式,然后调用table对象的setTableStyle方法进行设置。

读取excel

ExcelReader工具类

使用easyexcel读取excel时需要设置excel的版本,但有时我们无法预知excel的版本,所以模仿poi写了一个用于获取com.alibaba.excel.ExcelReader对象的工具类

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
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.apache.poi.EmptyFileException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.DocumentFactoryHelper;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.util.IOUtils;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;

/**
* @Author: yezhou
* @Date: 2019/5/13 18:47
* @Version 1.0
*/
public class ExcelReaderFactory {

/**
* @param in
* 文件输入流
* @param customContent
* 自定义模型可以在
* {@link AnalysisEventListener#invoke(Object, AnalysisContext) }
* AnalysisContext中获取用于监听者回调使用
* @param eventListener
* 用户监听
* @throws IOException
* @throws EmptyFileException
* @throws InvalidFormatException
*/
public static ExcelReader getExcelReader(InputStream in, Object customContent,
AnalysisEventListener<?> eventListener) throws EmptyFileException, IOException, InvalidFormatException {
// 如果输入流不支持mark/reset,需要对其进行包裹
if (!in.markSupported()) {
//in = new PushbackInputStream(in, 8);
in = new BufferedInputStream(in, 8);
}

// 确保至少有一些数据
byte[] header8 = IOUtils.peekFirst8Bytes(in);
ExcelTypeEnum excelTypeEnum = null;
if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
excelTypeEnum = ExcelTypeEnum.XLS;
}
if (DocumentFactoryHelper.hasOOXMLHeader(in)) {
excelTypeEnum = ExcelTypeEnum.XLSX;
}
if (excelTypeEnum != null) {
return new ExcelReader(in, excelTypeEnum, customContent, eventListener);
}
throw new InvalidFormatException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
}

/**
* @param in
* 文件输入流
* @param customContent
* 自定义模型可以在
* {@link AnalysisEventListener#invoke(Object, AnalysisContext) }
* AnalysisContext中获取用于监听者回调使用
* @param eventListener
* 用户监听
* @param trim
* 是否对解析的String做trim()默认true,用于防止 excel中空格引起的装换报错。
* @throws IOException
* @throws EmptyFileException
* @throws InvalidFormatException
*/
public static ExcelReader getExcelReader(InputStream in, Object customContent,
AnalysisEventListener<?> eventListener, boolean trim)
throws EmptyFileException, IOException, InvalidFormatException {
// 如果输入流不支持mark/reset,需要对其进行包裹
if (!in.markSupported()) {
//in = new PushbackInputStream(in, 8);
in = new BufferedInputStream(in, 8);
}

// 确保至少有一些数据
byte[] header8 = IOUtils.peekFirst8Bytes(in);
ExcelTypeEnum excelTypeEnum = null;
if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
excelTypeEnum = ExcelTypeEnum.XLS;
}
if (DocumentFactoryHelper.hasOOXMLHeader(in)) {
excelTypeEnum = ExcelTypeEnum.XLSX;
}
if (excelTypeEnum != null) {
return new ExcelReader(in, excelTypeEnum, customContent, eventListener, trim);
}
throw new InvalidFormatException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
}
}

按行读取Excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@Test
public void readRow() throws Exception {
InputStream in = new FileInputStream("withoutHead.xlsx");
AnalysisEventListener<List<String>> listener = new AnalysisEventListener<List<String>>() {

@Override
public void invoke(List<String> object, AnalysisContext context) {
System.err.println("Row:" + context.getCurrentRowNum() + " Data:" + object);
}

@Override
public void doAfterAllAnalysed(AnalysisContext context) {
System.err.println("doAfterAllAnalysed...");
}
};
ExcelReader excelReader = ExcelReaderFactory.getExcelReader(in, null, listener);
excelReader.read();
}

读取Excel到数据模型

正如写入Excel的时候可以使用数据模型一样,在读取Excel的时候也可以直接将数据映射为模型对象,区别在于要使用ExcelReader #read的重载方法。

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 void readModel() throws Exception {
InputStream in = new FileInputStream("withModel.xlsx");
AnalysisEventListener<ExcelPropertyIndexModel> listener = new AnalysisEventListener<ExcelPropertyIndexModel>() {

@Override
public void invoke(ExcelPropertyIndexModel object, AnalysisContext context) {
System.err.println("Row:" + context.getCurrentRowNum() + " Data:" + object);
}

@Override
public void doAfterAllAnalysed(AnalysisContext context) {
System.err.println("doAfterAllAnalysed...");
}
};
ExcelReader excelReader = ExcelReaderFactory.getExcelReader(in, null, listener);
// 第二个参数为表头行数,按照实际设置
excelReader.read(new Sheet(1, 1, ExcelPropertyIndexModel.class));
}

@Data
public static class ExcelPropertyIndexModel extends BaseRowModel {
@ExcelProperty(value = "姓名", index = 0)
private String name;

@ExcelProperty(value = "年龄", index = 1)
private String age;

@ExcelProperty(value = "邮箱", index = 2)
private String email;

@ExcelProperty(value = "地址", index = 3)
private String address;

@ExcelProperty(value = "性别", index = 4)
private String sax;

@ExcelProperty(value = "高度", index = 5)
private String height;

@ExcelProperty(value = "备注", index = 6)
private String last;
}

Powered by AppBlog.CN     浙ICP备14037229号

Copyright © 2012 - 2020 APP开发技术博客 All Rights Reserved.

访客数 : | 访问量 :