一、初识EasyExcel
1. Apache POI介绍
先说POI
,有过报表导入导出经验的同学,应该听过或者使用。
Apache POI
是Apache软件基金会的开源函式库,提供跨平台的Java API
实现Microsoft Office
格式档案读写。但是存在如下一些问题:
1.1 学习使用成本较高
对POI有过深入了解的才知道原来POI还有SAX模式(Dom解析模式)。但SAX模式相对比较复杂,excel有03和07两种版本,两个版本数据存储方式截然不同,sax解析方式也各不一样。
想要了解清楚这两种解析方式,才去写代码测试,估计两天时间是需要的。再加上即使解析完,要转换到自己业务模型还要很多繁琐的代码。总体下来感觉至少需要三天,由于代码复杂,后续维护成本巨大。
POI的SAX模式的API可以一定程度的解决一些内存溢出的问题,但是POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大,一个3M的Excel用POI的SAX解析,依然需要100M左右内存。
1.2 POI的内存消耗较大
大部分使用POI都是使用他的userModel模式。userModel的好处是上手容易使用简单,随便拷贝个代码跑一下,剩下就是写业务转换了,虽然转换也要写上百行代码,相对比较好理解。然而userModel模式最大的问题是在于非常大的内存消耗,一个几兆的文件解析要用掉上百兆的内存。现在很多应用采用这种模式,之所以还正常在跑一定是并发不大,并发上来后一定会OOM或者频繁的full gc。
总体上来说,简单写法重度依赖内存,复杂写法学习成本高。
1.3 特点
-
功能强大
-
代码书写冗余繁杂
-
读写大文件耗费内存较大,容易OOM
2. EasyExcel
2.1 重写了POI对07版Excel的解析
-
EasyExcel重写了POI对07版Excel的解析,可以把内存消耗从100M左右降低到10M以内,并且再大的Excel不会出现内存溢出,03版仍依赖POI的SAX模式。
-
下图为64M内存1分钟内读取75M(46W行25列)的Excel(当然还有急速模式能更快,但是内存占用会在100M多一点)
- 在上层做了模型转换的封装,让使用者更加简单方便
2.2 特点
- 在数据模型层面进行了封装,使用简单
- 重写了07版本的Excel的解析代码,降低内存消耗,能有效避免OOM
- 只能操作Excel
- 不能读取图片
二、常用API及注解
1、常用类
- EasyExcel 入口类,用于构建开始各种操作;
- ExcelReaderBuilder 构建出一个ReadWorkbook对象,即一个工作簿对象,对应的是一个Excel文件;
- ExcelWriterBuilder 构建出一个WriteWorkbook对象,即一个工作簿对象,对应的是一个Excel文件;
- ExcelReaderSheetBuilder 构建出一个ReadSheet对象,即一个工作表的对象,对应的Excel中的每个sheet,一个工作簿可以有多个工作表;
- ExcelWriterSheetBuilder 构建出一WriteSheet对象,即一个工作表的对象,对应的Excel中的每个sheet,一个工作簿可以有多个工作表;
- ReadListener 在每一行读取完毕后都会调用ReadListener来处理数据,我们可以把调用service的代码可以写在其
invoke
方法内部; - WriteHandler 在每一个操作包括创建单元格、创建表格等都会调用WriteHandler来处理数据,对使用者透明不可见;
注意:
所有配置都是继承的 Workbook的配置会被Sheet继承。所以在用EasyExcel设置参数的时候,在EasyExcel…sheet()方法之前作用域是整个sheet,之后针对单个sheet。
2、读取时的注解
@ExcelProperty
使用位置:标准作用在成员变量上
可选属性:
属性名 | 含义 | 说明 |
---|---|---|
index | 对应Excel表中的列数 | 默认-1,建议指定时从0开始 |
value | 对应Excel表中的列头 | |
converter | 成员变量转换器 | 自定义转换器需要实Converter接口 |
使用效果:index属性可以指定当前字段对应excel中的哪一列,可以根据列名value去匹配,也可以不写。
如果不使用@ExcelProperty注解,成员变量从上到下的顺序,对应表格中从左到右的顺序;
使用建议:要么全部不写,要么全部用index,要么全部用名字去匹配,尽量不要三个混着用。
@ExcelIgnore
标注在成员变量上,默认所有字段都会和excel去匹配,加了这个注解会忽略该字段
@DateTimeForma
标注在成员变量上,日期转换,代码中用String类型的成员变量
去接收excel中日期格式的数据
会调用这个注解。里面的value
参照java.text.SimpleDateFormat
// 5. 按照指定的格式写入Excel内容
@NumberFormat
标注在成员变量上,数字转换,代码中用String类型的成员变量
去接收excel数字格式的数据
会调用这个注解。里面的value
参照java.text.DecimalFormat
@ExcelIgnoreUnannotated
标注在类上。
不标注该注解时,默认类中所有成员变量都会参与读写,无论是否在成员变量上加了@ExcelProperty
的注解。
标注该注解后,类中的成员变量如果没有标注@ExcelProperty
注解将不会参与读写。
3、 读取时通用参数
ReadWorkbook
,ReadSheet
都会有的参数,如果为空,默认使用上级。
-
converter
转换器,默认加载了很多转换器。也可以自定义。 -
readListener
监听器,在读取数据的过程中会不断的调用监听器。 -
headRowNumber
指定需要读表格的 列头行数。默认有一行头,也就是认为第二行开始起为数据。 -
head
与class
二选一。读取文件头对应的列表,会根据列表匹配数据。建议使用class,就是文件中每一行数据对应的代码中的实体类型。 -
class
与head
二选一。读取文件的头对应的class,也可以使用注解。如果两个都不指定,则会读取全部数据。 -
autoTrim
字符串、表头等数据自动trim -
password
读的时候是否需要使用密码
4、ReadWorkbook(工作簿对象)参数
excelType
当前excel的类型,读取时会自动判断,无需设置。inputStream
与file
二选一。建议使用file。file
与inputStream
二选一。读取文件的文件。autoCloseStream
自动关闭流。readCache
默认小于5M用 内存,超过5M会使用EhCache
,不建议使用这个参数。useDefaultListener
@since 2.1.4
默认会加入ModelBuildEventListener
来帮忙转换成传入class
的对象,设置成false
后将不会协助转换对象,自定义的监听器会接收到Map<Integer,CellData>
对象,如果还想继续接听到class
对象,请调用readListener
方法,加入自定义的beforeListener
、ModelBuildEventListener
、 自定义的afterListener
即可。
5、ReadSheet(工作表对象)参数
sheetNo
需要读取Sheet的编号,建议使用这个来指定读取哪个SheetsheetName
根据名字去匹配Sheet,excel 2003不支持根据名字去匹配
6、写入时的注解
@ExcelProperty
使用位置:标准作用在成员变量上
可选属性:
属性名 | 含义 | 说明 |
---|---|---|
index | 对应Excel表中的列数 | 默认-1,指定时建议从0开始 |
value | 对应Excel表中的列头 | |
converter | 成员变量转换器 | 自定义转换器需要实Converter接口 |
使用效果:index
指定写到第几列,如果不指定则根据成员变量位置排序;
value
指定写入的列头,如果不指定则使用成员变量的名字作为列头;
如果要设置复杂的头,可以为value指定多个值。
其他注解:
基本和读取时一致
-
@ContentRowHeight() 标注在类上或属性上,指定内容行高
-
@HeadRowHeight() 标注在类上或属性上,指定列头行高
-
@ColumnWidth() 标注在类上或属性上,指定列宽
-
ExcelIgnore` 默认所有字段都会写入excel,这个注解会忽略这个字段
-
DateTimeFormat
日期转换,将Date
写到excel会调用这个注解。里面的value
参照java.text.SimpleDateFormat
-
NumberFormat
数字转换,用Number
写excel会调用这个注解。里面的value
参照java.text.DecimalFormat
-
ExcelIgnoreUnannotated
默认不加ExcelProperty
的注解的都会参与读写,加了不会参与
7、写入时通用参数
WriteWorkbook
、WriteSheet
都会有的参数,如果为空,默认使用上级。
-
converter
转换器,默认加载了很多转换器。也可以自定义。 -
writeHandler
写的处理器。可以实现WorkbookWriteHandler
,SheetWriteHandler
,RowWriteHandler
,CellWriteHandler
,在写入excel的不同阶段会调用,对使用者透明不可见。 -
relativeHeadRowIndex
距离多少行后开始。也就是开头空几行 -
needHead
是否导出头 -
head
与class
二选一。写入文件的头列表,建议使用class。 -
class
与head
二选一。写入文件的头对应的class,也可以使用注解。 -
autoTrim
字符串、表头等数据自动trim
8、WriteWorkbook(工作簿对象)参数
-
excelType
当前excel的类型,默认为xlsx
-
outputStream
与file
二选一。写入文件的流 -
file
与outputStream
二选一。写入的文件 -
templateInputStream
模板的文件流 -
templateFile
模板文件 -
autoCloseStream
自动关闭流。 -
password
写的时候是否需要使用密码 -
useDefaultStyle
写的时候是否是使用默认头
9、WriteSheet(工作表对象)参数
-
sheetNo
需要写入的编号。默认0 -
sheetName
需要些的Sheet名称,默认同sheetNo
三、快速入门–QuickStart
1、导入依赖坐标
<!-- EasyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
</dependency>
<!-- junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
1、最简单的读
1.1、需求、准备工作
/**
* 需求:单实体导入
* 导入Excel学员信息到系统。
* 包含如下列:姓名、性别、出生日期
* 模板详见:杭州黑马在线202003班学员信息.xls
*/
1.2、编写导出数据的实体
// 基于lombok
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
/**
* 学生姓名
*/
private String name;
/**
* 学生性别
*/
private String gender;
/**
* 学生出生日期
*/
private Date birthday;
/**
* id
*/
private String id;
}
1.3、读取Excel文件
调用EasyExcel
的API
读取的Excel
文件的测试类StudentReadDemo
package com.itheima.demo;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.itheima.domain.Student;
import com.itheima.listener.StudentReadListener;
import java.io.FileNotFoundException;
public class StudentReadDemo {
@Test
public void simpleRead() {
// 读取文件,读取完之后会自动关闭
/*
pathName 文件路径;
head 每行数据对应的实体;Student.class
readListener 读监听器,每读一样就会调用一次该监听器的invoke方法
sheet方法参数: 工作表的顺序号(从0开始)或者工作表的名字,不传默认为0
*/
// 封装工作簿对象
ExcelReaderBuilder workBook = EasyExcel.read
("D:\\IDEA WorkSpace\\EasyExcel_Test\\src\\main\\resources\\读数据测试.xlsx", Student.class, new StudentReadListener());
// 封装工作表
ExcelReaderSheetBuilder sheet1 = workBook.sheet();
// 读取
sheet1.doRead();
}
}
读取Excel的监听器,用于处理读取产生的数据
package com.itheima.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.itheima.domain.Student;
public class StudentReadListener extends AnalysisEventListener<Student> {
// 每读一样,会调用该invoke方法一次
@Override
public void invoke(Student data, AnalysisContext context) {
System.out.println("解析到一条数据:" + data);
}
// 全部读完之后,会调用该方法
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
System.out.println("全部解析完成");
}
}
测试结果:
2、最简单的写
2.1 需求、准备工作
/**
* 需求:单实体导出
* 导出多个学生对象到Excel表格
* 包含如下列:姓名、性别、出生日期
* 模板详见:杭州黑马在线202003班学员信息.xlsx
*/
2.2、编写导出数据的实体
// 使用lombok
package com.itheima.domain;
@Data
@AllArgsConstructor
@NoArgsConstructor
@ColumnWidth(20)
public class Student2 {
/**
* id
*/
//@ExcelProperty(value = "编号",index = 3)
@ExcelIgnore
private String id;
/**
* 学生姓名
*/
@ExcelProperty(value = "学生姓名", index = 0)
//@ColumnWidth(30)
private String name;
/**
* 学生性别
*/
@ExcelProperty(value = "学生性别", index = 2)
private String gender;
/**
* 学生出生日期
*/
@ExcelProperty(value = "学生出生日期", index = 1)
//@ColumnWidth(20)
private Date birthday;
}
2.3、 准备数据并写入到文件
public class ExcelTest {
//生成模拟数据
private static List<Student> initData() {
List<Student> students = new ArrayList<Student>();
for (int i = 0; i < 10; i++) {
Student data = new Student();
data.setName("写入数据0" + i);
data.setBirthday(new Date());
data.setGender("男");
students.add(data);
}
return students;
}
@Test
public void simpleWrite(){
List<Student> students = initData();
/*
String pathName 写入文件的路径
Class head 写入文件的对象类型
默认写入到07的xlsx中,如果想要写入xls,可以指定类型(待验证)
*/
ExcelWriterBuilder workBook = EasyExcel.write("D:\\IDEA WorkSpace\\EasyExcel_Test\\src\\main\\resources\\写数据测试.xlsx", Student.class);
// sheet方法参数: 工作表的顺序号(从0开始)或者工作表的名字
workBook.sheet("测试数据表").doWrite(students);
System.out.println("写入完成!");
}
}
测试结果:
注意:windows系统中进行写入数据时需要把已打开的文件关闭,否则会报错:
2.1读取多个sheet
@Test
public void repeatedRead() {
String fileName = PATH+"demo.xlsx";
// 1.读取全部sheet
// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();
// 2.读取部分sheet
fileName =PATH+ "demo.xlsx";
ExcelReader excelReader = EasyExcel.read(fileName).build();
// 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
// readSheet参数设置读取sheet的序号
ReadSheet readSheet1 =
EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
ReadSheet readSheet2 =
EasyExcel.readSheet(2).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
// 这里注意 一定要把sheet1 sheet2 一起传进去,不然有个问题就是03版的excel 会读取多次,浪费性能
excelReader.read(readSheet1, readSheet2);
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
}
全部读取测试结果:
部分读取测试结果:读取第一张和第三张表的数据
2.2自定义格式转换
实体类
@Data
public class ConverterData {
/**
* converter属性定义自己的字符串转换器
*/
@ExcelProperty(converter = CustomStringConverter.class)
private String string;
/**
* 这里用string 去接日期才能格式化
*/
@DateTimeFormat("yyyy年MM月dd日 HH时mm分ss秒")
private String date;
/**
* 我想接收百分比的数字
*/
@NumberFormat("#.##%")
private String doubleData;
}
转换器
public class ConverterDataListener extends AnalysisEventListener<ConverterData> {
List<ConverterData> list = new ArrayList<ConverterData>();
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*/
public ConverterDataListener() {}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* @param context
*/
@Override
public void invoke(ConverterData data, AnalysisContext context) {
System.out.println("解析到一条数据:"+JSON.toJSONString(data));
list.add(data);
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
System.out.println("数据全部解析完成!");
}
}
单元测试
/**
* 测试自定义数据格式
*/
@Test
public void converterRead() {
String fileName =PATH+ "自定义格式测试.xlsx";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet
EasyExcel.read(fileName, ConverterData.class, new ConverterDataListener())
// 这里注意 我们也可以registerConverter来指定自定义转换器, 但是这个转换变成全局了, 所有java为string,excel为string的都会用这个转换器。
// 如果就想单个字段使用请使用@ExcelProperty 指定converter
// .registerConverter(new CustomStringStringConverter())
// 读取sheet
.sheet().doRead();
}
Excel数据:
测试结果:
2.3 图片导出
实体类
@Data
@ContentRowHeight(200)
@ColumnWidth(200 / 8)
public class ImageData {
// 图片导出方式有5种
private File file;
private InputStream inputStream;
/**
* 如果string类型 必须指定转换器,string默认转换成string,该转换器是官方支持的
*/
@ExcelProperty(converter = StringImageConverter.class)
private String string;
private byte[] byteArray;
/**
* 根据url导出 版本2.1.1才支持该种模式
*/
private URL url;
}
单元测试
/**
* 图片导出
* @throws Exception
*/
@Test
public void imageWrite() throws Exception {
String fileName = "imageWrite" + System.currentTimeMillis() + ".xlsx";
// 如果使用流 记得关闭
InputStream inputStream = null;
try {
List<ImageData> list = new ArrayList<ImageData>();
ImageData imageData = new ImageData();
list.add(imageData);
// String imagePath = "converter" + File.separator + "img.jpg";
// 放入五种类型的图片 根据实际使用只要选一种即可
// imageData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
// imageData.setFile(new File(imagePath));
// imageData.setString(imagePath);
// inputStream = FileUtils.openInputStream(new File(imagePath));
imageData.setInputStream(inputStream);
imageData.setUrl(new URL(
"https://cdn-dyq.ebiaoji.com/dianyouquan/20201229/1609210430378.png"));
EasyExcel.write(fileName, ImageData.class).sheet().doWrite(list);
} finally {
if (inputStream != null) {
inputStream.close();
}
}
}
说明:这里我只设置了url属性,其他属性暂时不设置
测试结果:
2.4 填充模板写入
简单的填充
EXcel模板
实体类:
@Data
public class FillData {
private String name;
private double number;
}
单元测试:
/**
* 填充模板写入
*/
@Test
public void simpleFill() {
// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
String templateFileName =PATH+ "simple.xlsx";
// 方案1 根据对象填充
String fileName =PATH+ System.currentTimeMillis() + ".xlsx";
// 这里 会填充到第一个sheet, 然后文件流会自动关闭
FillData fillData = new FillData();
fillData.setName("知春秋");
fillData.setNumber(25);
EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(fillData);
// 方案2 根据Map填充
fileName = System.currentTimeMillis() + ".xlsx";
// 这里 会填充到第一个sheet, 然后文件流会自动关闭
Map<String, Object> map = new HashMap<String, Object>();
map.put("name", "知春秋");
map.put("number", 25);
EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);
}
测试结果
参考链接:https://blog.csdn.net/sinat_32366329/article/details/103109058