七.Easyexcel的使用
1.为什么程序中需要使用excel
2.技术选型
3.具体使用
七.Easyexcel的使用
1.为什么程序中需要使用excel
- 企业用excel表格导入批量数据
- 保存日志出勤记录等不变数据
- 异构系统间数据传输
PHP系统无法访问数据库,开发JAVA系统来增加功能,需要数据,则PHP导出excel数据,然后JAVA系统导入数据库
2.技术选型
- POI技术
过于耗内存,一次性读完所有数据,容易发生OOM或者JVM频繁full gc
- easy excel
一行一行读取数据,节省内存,观察者模式处理
3.具体使用
①流程
- 定义实体类(可用@ExcelProperty声明列名)
- 直接写出excel(xls旧版,xlsx新版,新版节省空间,但是仍然有很多旧版需要兼容旧版)
- 写入excel(需要监听器来获取每一条数据和完成读取)
②引入依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.like</groupId><artifactId>alibaba-execel</artifactId><version>1.0-SNAPSHOT</version><dependencies><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.7</version></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-simple</artifactId><version>1.7.5</version></dependency><dependency><groupId>org.apache.xmlbeans</groupId><artifactId>xmlbeans</artifactId><version>3.1.0</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.12</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version></dependency></dependencies>
</project>
③创建实体类
package com.atguigu.easyexecel.dto;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;import java.util.Date;@Data
public class ExcelStudentDTO {@ExcelProperty("姓名")private String name;@ExcelProperty("生日")private Date birthday;@ExcelProperty("薪资")private Double salary;
}
④创建监听器
package com.atguigu.easyexecel.listener;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.easyexecel.dto.ExcelStudentDTO;
import lombok.extern.slf4j.Slf4j;@Slf4j
public class ExcelStudentDTOListener extends AnalysisEventListener<ExcelStudentDTO> {@Overridepublic void invoke(ExcelStudentDTO data, AnalysisContext context) {log.info("解析到一条数据:{}", data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {log.info("所有数据解析完成!");}
}
⑤读excel和写excel
package com.atguigu.easyexecel;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.atguigu.easyexecel.dto.ExcelStudentDTO;
import com.atguigu.easyexecel.listener.ExcelStudentDTOListener;
import org.junit.Test;public class ExcelReadTest {@Testpublic void simpleReadXlsx() {String fileName = "d:/excel/simpleWrite.xlsx";EasyExcel.read(fileName, ExcelStudentDTO.class, new ExcelStudentDTOListener()).sheet().doRead();}@Testpublic void simpleReadXls() {String fileName = "d:/excel/simpleWrite.xls";EasyExcel.read(fileName, ExcelStudentDTO.class, new ExcelStudentDTOListener()).excelType(ExcelTypeEnum.XLS).sheet().doRead();}
}
package com.atguigu.easyexecel;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.atguigu.easyexecel.dto.ExcelStudentDTO;
import org.junit.Test;import java.util.ArrayList;
import java.util.Date;
import java.util.List;public class ExecelWriteTest {@Testpublic void simpleWriteXlsx() {String fileName = "d:/excel/simpleWrite.xlsx"; EasyExcel.write(fileName, ExcelStudentDTO.class).sheet("模板").doWrite(data());}@Testpublic void simpleWriteXls() {String fileName = "d:/excel/simpleWrite.xls"; EasyExcel.write(fileName, ExcelStudentDTO.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(data());}private List<ExcelStudentDTO> data(){List<ExcelStudentDTO> list = new ArrayList<>();for (int i = 0; i < 65535; i++) {ExcelStudentDTO data = new ExcelStudentDTO();data.setName("Helen" + i);data.setBirthday(new Date());data.setSalary(123456.1234);list.add(data);}return list;}
}
未更新
未更新
未更新
未更新
未更新
未更新
未更新
未更新
未更新
未更新
未更新