这是我参与更文挑战的第17天,活动详情查看: 更文挑战
一、简介
1.1 EasyExcel 概述
1.1.1 官网
快速开始:www.yuque.com/easyexcel/d…
1.1.2 EasyExcel 特点
-
Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行,但是一旦并发上来后一定会OOM或者JVM频繁的full gc。
-
EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。
-
EasyExcel采用一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)。
1.2 应用场景
-
数据导入:减轻录入工作量
-
数据导出:统计信息归档
-
数据传输:异构系统之间数据传输
1.3 常用注解
-
@ExcelProperty 指定当前字段对应excel中的那一列。
-
@ExcelIgnore 默认所有字段都会和excel去匹配,加了这个注解会忽略该字段
-
@DateTimeFormat 日期转换,用String去接收excel日期格式的数据会调用这个注解。
-
@NumberFormat 数字转换,用String去接收excel数字格式的数据会调用这个注解。
-
@ExcelIgnoreUnannotated默认不加ExcelProperty 的注解的都会参与读写,加了不会参与
二、写Excel
2.1 创建项目
1、创建一个普通的maven 项目
项目名:alibaba_easyExcel
2、pom中引入xml相关依赖
<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.10</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
复制代码
2.2 最简单的写
1、创建实体类
@Data
public class ExcelStudentData {
@ExcelProperty("姓名")
private String name;
@ExcelProperty("生日")
private Date birthday;
@ExcelProperty("薪水")
private Double salary;
@ExcelProperty
private String password;
}
复制代码
2、测试用例
- 07版本的Excel和03版本的写入方式有所不同
- 03 版本的Excel写入最多一次可写65536行
@Test
public void simpleWrite(){
String fileName="E:\\lei\\学习\\学生.xlsx";
EasyExcel.write(fileName, ExcelStudentData.class)
.sheet("学生信息")
.doWrite(data());
}
private List<ExcelStudentData> data(){
ArrayList<ExcelStudentData> list = new ArrayList<ExcelStudentData>();
for (int i = 0; i < 10; i++) {
ExcelStudentData excelStudentData = new ExcelStudentData();
excelStudentData.setName("潇雷"+i);
excelStudentData.setBirthday(new Date());
excelStudentData.setSalary(10000.90);
list.add(excelStudentData);
}
return list;
}
}
复制代码
结果:
经过测试:
- 1、已经存在的excel会进行清空并重新赋值。不会追加
- 2、打开的文件不能被写入
- 3、需要先建立目录
2.3 指定写入列
为列配置index属性
@Data
public class ExcelStudentData {
@ExcelProperty(value = "姓名",index = 2)
private String name;
@ExcelProperty(value = "生日",index = 5)
private Date birthday;
@ExcelProperty(value = "薪水",index = 6)
private Double salary;
@ExcelIgnore
private String password;
}
复制代码
2.4 自定义格式转换
配置@DateTimeFormat 和 @NumberFormat
@Data
public class ExcelStudentData {
@ExcelProperty(value = "姓名")
private String name;
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
@ExcelProperty(value = "生日")
private Date birthday;
@NumberFormat("#.##%")//百分比表示,保留两位小数
@ExcelProperty(value = "薪资")
private Double salary;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String password;
}
复制代码
三、读Excel
3.1 参考文档
3.2 创建监听器
@Slf4j
public class ExcelStudentListener extends AnalysisEventListener<ExcelStudentData> {
private static final int BATCH_COUNT=2;
List<ExcelStudentData> list =new ArrayList<ExcelStudentData>();
/**
* 遍历每行的记录
* @param analysisContext
* @return void
**/
public void invoke(ExcelStudentData o, AnalysisContext analysisContext) {
log.info("读取到一条记录,{}",o);
list.add(o);
if(list.size()>=BATCH_COUNT){
log.info("存储数据库");
list.clear();
}
}
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("存储剩余数据到数据库");
log.info("全部数据解析完成");
}
}
复制代码
3.3 测试用例
public class ExcelReadTest {
@Test
public void test(){
String fileName="E:\\lei\\学习\\学生.xlsx";
EasyExcel.read(fileName, ExcelStudentData.class,new ExcelStudentListener())
.sheet().doRead();
}
}
复制代码
四、项目实战
在项目中,用到EasyExcel ,主要用于多级目录的excel的组装应用。
例如这样的数据:
存在五级目录:光伏进线编号--> 箱变编号 --> 汇流箱编号 --> 逆变器编号 --> 光伏组件包。
总共2000多条数据,组装成多级目录。
读取数据:
public void WriteData() {
String fileName="E:\\绿巨人\\研发部\\开发资料\\平顶山设备表.xlsx";
EasyExcel.read(fileName, ExcelPhotovoData.class,new ExcelDeviceListener(wiringPointMapper,deviceInfoMapper))
.sheet().doRead();
}
复制代码
监听器传入mapper,最终完成目录的封装。
@Slf4j
public class ExcelDeviceListener extends AnalysisEventListener<ExcelPhotovoData> {
private WiringPointMapper wiringPointMapper;
private DeviceInfoMapper deviceInfoMapper;
//定义临界值
private static final int BATCH_COUNT=500;
List<DeviceInfo> xbList=new ArrayList<DeviceInfo>();
public ExcelDeviceListener (){}
public ExcelDeviceListener(DeviceInfoMapper deviceInfoMapper){
this.deviceInfoMapper=deviceInfoMapper;
}
public ExcelDeviceListener(WiringPointMapper wiringPointMapper,DeviceInfoMapper deviceInfoMapper){
this.wiringPointMapper=wiringPointMapper;
this.deviceInfoMapper=deviceInfoMapper;
};
@Override
public void invoke(ExcelPhotovoData excelPhotovoData, AnalysisContext analysisContext) {
log.info("读取到一条记录,{}"+excelPhotovoData);
String level=excelPhotovoData.getJx();
String level2=excelPhotovoData.getXb();
String level3=excelPhotovoData.getHl();
String level4=excelPhotovoData.getNb();
String level5=excelPhotovoData.getGfb();
String level6=excelPhotovoData.getNumber();
Integer parentId=null;
Integer parentId2=null;
Integer parentId3=null;
Integer parentId4=null;
Integer parentId5=null;
Integer parentId6=null;
//查看一级目录是否存在
WiringPoint isExistLevel=this.getByLevel(level);
if(ObjectUtil.isNull(isExistLevel)){
// 组装一级目录
WiringPoint wiringPoint = new WiringPoint();
wiringPoint.setSysId(41040020001L);
wiringPoint.setPointName(level);
wiringPoint.setDeleteFlag(0);
wiringPoint.setCreateTime(DateUtil.date());
wiringPoint.setUpdateTime(DateUtil.date());
wiringPoint.setParentId(0);
wiringPoint.setType("0");
wiringPointMapper.insert(wiringPoint);
parentId=wiringPoint.getId();
}else{
parentId=isExistLevel.getId();
}
// 判断二级节点是否存在
WiringPoint isExistLevel2 = this.getByLevel2(level2,parentId);
if(ObjectUtil.isNull(isExistLevel2)){
// 查找dev_id 和sn_id
QueryWrapper<DeviceInfo> deviceInfoQueryWrapper = new QueryWrapper<>();
deviceInfoQueryWrapper.eq("sys_id",41040020001L);
deviceInfoQueryWrapper.eq("device_type","xb");
deviceInfoQueryWrapper.eq("sn",level2);
DeviceInfo deviceInfo = deviceInfoMapper.selectOne(deviceInfoQueryWrapper);
// 组装二级目录
WiringPoint wiringPoint = new WiringPoint();
wiringPoint.setSysId(41040020001L);
wiringPoint.setPointName(level2);
wiringPoint.setDeleteFlag(0);
wiringPoint.setCreateTime(DateUtil.date());
wiringPoint.setUpdateTime(DateUtil.date());
wiringPoint.setParentId(parentId);
wiringPoint.setSnId(deviceInfo.getId());
wiringPoint.setDevId(deviceInfo.getDevId());
wiringPointMapper.insert(wiringPoint);
parentId2=wiringPoint.getId();
}else{
parentId2=isExistLevel2.getId();
}
// 判断三级节点是否存在
WiringPoint isExistLevel3 = this.getByLevel2(level3,parentId2);
if(ObjectUtil.isNull(isExistLevel3)){
// 查找dev_id 和sn_id
QueryWrapper<DeviceInfo> deviceInfoQueryWrapper = new QueryWrapper<>();
deviceInfoQueryWrapper.eq("sys_id",41040020001L);
deviceInfoQueryWrapper.eq("device_type","hl");
deviceInfoQueryWrapper.eq("sn",level3);
DeviceInfo deviceInfo = deviceInfoMapper.selectOne(deviceInfoQueryWrapper);
// 组装三级目录
WiringPoint wiringPoint = new WiringPoint();
wiringPoint.setSysId(41040020001L);
wiringPoint.setPointName(level3);
wiringPoint.setDeleteFlag(0);
wiringPoint.setCreateTime(DateUtil.date());
wiringPoint.setUpdateTime(DateUtil.date());
wiringPoint.setParentId(parentId2);
wiringPoint.setSnId(deviceInfo.getId());
wiringPoint.setDevId(deviceInfo.getDevId());
wiringPointMapper.insert(wiringPoint);
parentId3=wiringPoint.getId();
}else{
parentId3=isExistLevel3.getId();
}
// 判断四级节点是否存在
WiringPoint isExistLevel4 = this.getByLevel2(level4,parentId3);
if(ObjectUtil.isNull(isExistLevel4)){
// 查找dev_id 和sn_id
QueryWrapper<DeviceInfo> deviceInfoQueryWrapper = new QueryWrapper<>();
deviceInfoQueryWrapper.eq("sys_id",41040020001L);
deviceInfoQueryWrapper.eq("device_type","nb");
deviceInfoQueryWrapper.eq("sn",level4);
DeviceInfo deviceInfo = deviceInfoMapper.selectOne(deviceInfoQueryWrapper);
// 组装三级目录
WiringPoint wiringPoint = new WiringPoint();
wiringPoint.setSysId(41040020001L);
wiringPoint.setPointName(level4);
wiringPoint.setDeleteFlag(0);
wiringPoint.setCreateTime(DateUtil.date());
wiringPoint.setUpdateTime(DateUtil.date());
wiringPoint.setParentId(parentId3);
wiringPoint.setSnId(deviceInfo.getId());
wiringPoint.setDevId(deviceInfo.getDevId());
wiringPointMapper.insert(wiringPoint);
parentId4=wiringPoint.getId();
}else{
parentId4=isExistLevel4.getId();
}
// 判断五级节点是否存在
WiringPoint isExistLevel5 = this.getByLevel2(level5,parentId4);
if(ObjectUtil.isNull(isExistLevel5)){
// 查找dev_id 和sn_id
QueryWrapper<DeviceInfo> deviceInfoQueryWrapper = new QueryWrapper<>();
deviceInfoQueryWrapper.eq("sys_id",41040020001L);
deviceInfoQueryWrapper.eq("device_type","gf");
deviceInfoQueryWrapper.eq("sn",level5);
DeviceInfo deviceInfo = deviceInfoMapper.selectOne(deviceInfoQueryWrapper);
// 组装五级目录
WiringPoint wiringPoint = new WiringPoint();
wiringPoint.setSysId(41040020001L);
wiringPoint.setPointName(level5);
wiringPoint.setDeleteFlag(0);
wiringPoint.setCreateTime(DateUtil.date());
wiringPoint.setUpdateTime(DateUtil.date());
wiringPoint.setParentId(parentId4);
wiringPoint.setSnId(deviceInfo.getId());
wiringPoint.setDevId(deviceInfo.getDevId());
wiringPointMapper.insert(wiringPoint);
parentId5=wiringPoint.getId();
}else{
parentId5=isExistLevel5.getId();
}
}
//根据一级目录查询二级目录是否存在
private WiringPoint getByLevel2(String level2, Integer parentId) {
QueryWrapper<WiringPoint> wrapper = new QueryWrapper<>();
wrapper.eq("point_name",level2);
wrapper.eq("sys_id",41040020001L);
wrapper.eq("parent_id",parentId);
WiringPoint wiringPoint = wiringPointMapper.selectOne(wrapper);
return wiringPoint;
}
//根据一级目录查询是否存在
private WiringPoint getByLevel(String level) {
QueryWrapper<WiringPoint> wrapper = new QueryWrapper<>();
wrapper.eq("point_name",level);
wrapper.eq("sys_id",41040020001L);
wrapper.eq("parent_id",0);
WiringPoint wiringPoint = wiringPointMapper.selectOne(wrapper);
return wiringPoint;
}
}
复制代码
近期评论