EasyExcel
定义:
EasyExcel 是一个基于 Java 的简单、省内存的读写 Excel 的阿里开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
文件的导入: 把数据从 Excel 导入到数据库的过程称为导入
使用场景:
项目中涉及到Excel 文件,CVS 文件大多数的读写操作,均可以使用。
文件的导出:把数据从数据库导出到 Excel 个过程称为导出
优点:
读取数据量大,占用内存低(相对POI来说)
案例:
如何使用
阿里官方文档:
https://easyexcel.opensource.alibaba.com/
快速入门
导出数据到Excel
步骤1:
创建maven 工程
引入相关坐标
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.30</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.1</version>
<scope>compile</scope>
</dependency>
</dependencies>
参考官方API完成功能
步骤2:
编写模型类并加入注解
编写获取测试数据的方法
调用官方API完成写功能
核心代码:
EasyExcel.write(fileName, DemoData.class).sheet("测试").doWrite(data(100000));
导出demo
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
@ExcelProperty("员工编号")
private int id;
@ExcelProperty("员工姓名")
private String name;
@ExcelProperty("入职日期")
private Date date;
@ExcelProperty("员工薪水")
private double salary;
@ExcelProperty("是否在职")
private int isWork;
}
public class SimpleWrite {
//TODO ListUtils.newArrayList() 比 new arrayList() 好用吗?为什么此处用这个
private List<Employee> data(int count) {
List<Employee> list = ListUtils.newArrayList();
for (int i = 1; i <= count; i++) {
list.add(new Employee(i, "测试数据"+i, new Date(), 6.6*i, i/2==0 ? 0: 1));
}
return list;
}
@Test
public void doWrite() {
String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, Employee.class).sheet("模板").doWrite(data(10));
}
}
Excel 导入数据
步骤1:
编写模型类并加入注解
监听器介绍
调用官方API完成写功能
导入demo1
public class SimpleReader {
@Test
public void read() {
String fileName = TestFileUtil.getPath() + "simpleWrite1716000977493.xlsx";
// 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行
// 具体需要返回多少行可以在`PageReadListener`的构造函数设置
EasyExcel.read(fileName, Employee.class, new PageReadListener<Employee>(dataList -> {
for (Employee employee : dataList) {
System.out.println(employee);
}
})).sheet().doRead();
}
}
导入demo2
@RestController
public class EasyExcelController {
@PostMapping("/import")
public void importEasyExcel(@RequestParam("file") MultipartFile file) {
try {
EasyExcel.read(file.getInputStream(), Employee.class, new PageReadListener<Employee>(dataList -> {
for (Employee employee : dataList) {
System.out.println(employee);
}
})).sheet().doRead();
} catch (Exception e) {
e.printStackTrace();
}
}
}
进阶操作
批量写入数据
编写模型类
调用官方API完成写功能
主要代码
try(ExcelWriter writer = EasyExcel.write(fileName, DemoData.class).build()){
WriteSheet writeSheet = EasyExcel.writerSheet("测试表1").build();
for(int i=0; i<3; i++) {
ExcelWriter write = writer.write(data(100000),writeSheet);
}
}
百万数据批量写入demo
public class ManyWrite {
private List<Employee> data(int count) {
List<Employee> list = ListUtils.newArrayList();
for (int i = 1; i <= count; i++) {
list.add(new Employee(i, "测试数据"+i, new Date(), 6.6*i, i/2==0 ? 0: 1));
}
return list;
}
@Test
public void doWrite() {
String fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
// 指定文件,关联模型
try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("测试数据").build();
long t1 = System.currentTimeMillis();
for (int i = 0; i < 100; i++) {
List<Employee> data = data(10000);
excelWriter.write(data, writeSheet);
}
long t2 = System.currentTimeMillis();
System.out.println(t2-t1);
}
}
}
批量填充
编写模型类并加入注解
按要求编写模板文件
调用官方API完成写功能
主要代码:
EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(fillData);
填充百万数据demo
public class FillWrite {
private List<Employee> data(int count) {
List<Employee> list = ListUtils.newArrayList();
for (int i = 1; i <= count; i++) {
list.add(new Employee(i, "测试数据"+i, new Date(), 6.6*i, i/2==0 ? 0: 1));
}
return list;
}
@Test
public void doWrite() {
// 方案2 分多次 填充 会使用文件缓存(省内存)
String fileName = TestFileUtil.getPath() + "listFill" + System.currentTimeMillis() + ".xlsx";
String templateFileName = TestFileUtil.getPath() + "muban.xlsx";
try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet().build();
long t1 = System.currentTimeMillis();
for (int i = 0; i < 100; i++) {
excelWriter.fill(data(10000), writeSheet);
}
long t2 = System.currentTimeMillis();
System.out.println(t2-t1);
}
}
}
自定义监听器读海量数据并监控内存消耗
步骤
编写模型类并加入注解
自定义监听器
调用官方API完成写功能
主要代码
EasyExcel.read(fileName, IndexOrNameData.class, new IndexOrNameDataListener()).sheet().doRead();
自定义监听器读取海量数据demo
public class EmployeeListener implements ReadListener<Employee> {
private ArrayList<Employee> list = new ArrayList<>(COUNT);
private static final int COUNT = 100;
private EmployeeDao employeeDao;
public EmployeeListener(EmployeeDao dao) {
this.employeeDao = dao;
}
@Override
public void invoke(Employee employee, AnalysisContext analysisContext) {
list.add(employee);
if(list.size() >= COUNT) {
employeeDao.save(list);
list = new ArrayList<>(COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
if(list.size() > 0) {
employeeDao.save(list);
}
}
}
//模拟数据库操作
public class EmployeeDao {
public void save(List<Employee> list) {
System.out.println(list.size() + "模拟操作数据库-----");
}
}
public class ManyReder {
@Test
public void read() {
String fileName = TestFileUtil.getPath() + "listFill1716030902665.xlsx";
// EasyExcel.read(fileName, Employee.class, new EmployeeListener(new EmployeeDao())).sheet().doRead();
long t1 = System.currentTimeMillis();
ExcelReader reader = EasyExcel.read(fileName, Employee.class, new EmployeeListener(new EmployeeDao())).build();
ReadSheet sheet = EasyExcel.readSheet().build();
reader.read(sheet);
long t2 = System.currentTimeMillis();
System.out.println(t2-t1);
}
}
打开 jconsole 控制台