easyexcel是阿里巴巴开源的excel处理工具,重写了poi对07版Excel的解析,减少了内存的消耗,同时对模型转换进行封装,极大简化了用户的使用。
文章目录
1、创建一个Maven项目;
2、向pom文件中,导入依赖;
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
</dependency>
3、编写excel文件;
4、Excel文件的读取;
首先创建一个类;
package com.baidou;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
/**
* @ClassName: Test
* @Author: lenovo
* @Date: 2021/3/28 07:40
* Version: 1.0
*/
public class Test {
public static void main(String[] args) {
// 创建 ExcelReaderBuilder 实例
ExcelReaderBuilder readerBuilder = EasyExcel.read();
// 获取文件对象
readerBuilder.file("D:\\aaa\\用户表.xlsx");
// 指定sheet
readerBuilder.sheet("用户表");
// 自动关闭输入流
readerBuilder.autoCloseStream(true);
// 设置 Excel 文件格式
readerBuilder.excelType(ExcelTypeEnum.XLSX);
// 注册监听器进行数据的解析
readerBuilder.registerReadListener(new AnalysisEventListener() {
@Override
public void invoke(Object o, AnalysisContext analysisContext) { // 一行数据读取完之后的回调
// 这个 o 默认 LinkedHashMap 类型
System.out.println(o);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) { // 通知文件读取完毕
System.out.println("数据读取完毕!!!");
}
});
// 构建读取器
ExcelReader reader = readerBuilder.build();
// 读取数据
reader.readAll();
// 读取完毕
reader.finish();
}
}
ok我们运行这个类:
通过泛型指定数据类型;
package com.baidou;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
/**
* @ClassName: Test
* @Author: lenovo
* @Date: 2021/3/28 07:40
* Version: 1.0
*/
public class Test {
public static void main(String[] args) {
// 读取文件
// 创建 ExcelReaderBuilder 实例
ExcelReaderBuilder readerBuilder = EasyExcel.read();
// 获取文件对象
readerBuilder.file("D:\\aaa\\用户表.xlsx");
// 指定sheet
readerBuilder.sheet("用户表");
// 自动关闭输入流
readerBuilder.autoCloseStream(true);
// 设置 Excel 文件格式
readerBuilder.excelType(ExcelTypeEnum.XLSX);
// 注册监听器进行数据的解析
readerBuilder.registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {
@Override
public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) { // 一行数据读取完之后的回调
Set<Integer> keySet = integerStringMap.keySet();
Iterator<Integer> iterator = keySet.iterator();
while (iterator.hasNext()) {
Integer key = iterator.next();
System.out.print(key + ":" + integerStringMap.get(key)+", ");
}
System.out.println();
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) { // 通知文件读取完毕
System.out.println("数据读取完毕!!!");
}
});
// 构建读取器
ExcelReader reader = readerBuilder.build();
// 读取数据
reader.readAll();
// 读取完毕
reader.finish();
}
}
简化版:
package com.baidou;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.*;
/**
* @ClassName: ExcelTest
* @Description: 简化版
* @Author: lenovo
* @Date: 2021/3/28 08:26
* Version: 1.0
*/
public class ExcelTest {
public static void main(String[] args) {
List<Map<Integer, String>> list = new LinkedList<>();
EasyExcel.read("D:\\aaa\\用户表.xlsx")
.sheet()
.registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {
@Override
public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
list.add(integerStringMap);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕!!!");
}
}).doRead();
for (Map<Integer, String> integerStringMap : list) {
Set<Integer> keySet = integerStringMap.keySet();
Iterator<Integer> iterator = keySet.iterator();
while (iterator.hasNext()) {
Integer key = iterator.next();
System.out.print(key +":"+integerStringMap.get(key) + ", ");
}
System.out.println();
}
}
}
5、映射成指定对象,创建Excel文件对应的实体类;
@Data
public class ExcelData {
@ExcelProperty("ID")
private String id;
@ExcelProperty("用户名")
private String name;
@ExcelProperty("积分")
private Integer score;
@ExcelProperty("性别")
private String gender;
@ExcelProperty("职业")
private String occupation;
@ExcelProperty("财富")
private Integer wealth;
}
解析Excel文件的时候直接指定实体类:
package com.baidou;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baidou.entity.ExcelData;
import java.util.*;
/**
* @ClassName: ExcelTest
* @Description: 简化版
* @Author: lenovo
* @Date: 2021/3/28 08:26
* Version: 1.0
*/
public class ExcelTest {
public static void main(String[] args) {
List<ExcelData> list = new LinkedList<>();
EasyExcel.read("D:\\aaa\\用户表.xlsx")
.head(ExcelData.class)
.sheet()
.registerReadListener(new AnalysisEventListener<ExcelData>() {
@Override
public void invoke(ExcelData excelData, AnalysisContext analysisContext) {
list.add(excelData);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕!!!");
}
}).doRead();
for (ExcelData excelData : list) {
System.out.println(excelData);
}
}
}
6、写数据操作;
package com.baidou;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baidou.entity.ExcelData;
import java.util.*;
/**
* @ClassName: ExcelTest
* @Description: 简化版
* @Author: lenovo
* @Date: 2021/3/28 08:26
* Version: 1.0
*/
public class ExcelTest {
public static void main(String[] args) {
List<ExcelData> list = parseData();
// list 写入 Exlcel 文件
EasyExcel.write("D:\\aaa\\用户表_副本.xlsx")
.head(ExcelData.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("用户表")
.doWrite(list);
}
public static List<ExcelData> parseData() {
List<ExcelData> list = new LinkedList<>();
EasyExcel.read("D:\\aaa\\用户表.xlsx")
.head(ExcelData.class)
.sheet()
.registerReadListener(new AnalysisEventListener<ExcelData>() {
@Override
public void invoke(ExcelData excelData, AnalysisContext analysisContext) {
list.add(excelData);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕!!!");
}
}).doRead();
return list;
}
}