之前写过一篇《JAVA操作Excel》,介绍了jxl
和poi
读写Excel的实现,今天为大家介绍一下使用easyexcel
对Excel进行读写,项目主页地址:https://github.com/alibaba/easyexcel
作者对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,首先我们需要添加maven依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.0.1</version>
</dependency>
首先,我们先来看看如何写Excel,写入Excel,我们可以通过com.alibaba.excel.ExcelWriter
类实现,下面我们来看一下最简单的无表头的实现
package test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
public class ExcelWriteTest {
/**
* 每行数据是List<String>无表头
*
* @throws IOException
*/
@Test
public void writeWithoutHead() throws IOException {
try (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 < 100; 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样式如下:
很多时候,我们在生成Excel的时候都是需要添加表头的,使用easyexcel可以很容易的实现,我们可以对上面的例子进行简单的改造,为其添加表头
package test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
public class ExcelWriteTest {
@Test
public void writeWithoutHead() 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 < 100; 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();
}
}
}
效果如下:
除了上面添加表头的方式,我们还可以使用实体类,为其添加com.alibaba.excel.annotation.ExcelProperty
注解来生成表头,实体类数据作为Excel数据
package test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
public class ExcelWriteTest {
@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, ExcelPropertyIndexModel.class);
sheet1.setSheetName("sheet1");
List<ExcelPropertyIndexModel> data = new ArrayList<>();
for (int i = 0; i < 100; 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.heigh = "heigh" + i;
item.last = "last" + i;
data.add(item);
}
writer.write(data, sheet1);
writer.finish();
}
}
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 heigh;
@ExcelProperty(value = "备注", index = 6)
private String last;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.ema