需求:
需要一个读excel文件中多sheet的工具类,返回List/Map型的数据;同时也可以进行反向写操作
具体步骤
1. 引入maven依赖
<!--excel插件-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
<!--简化实体插件-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
</dependency>
<!--日志-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.22</version>
</dependency>
<!--json-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.58</version>
</dependency>
2.1 有实体类型
建立实体类,建立监听器类(通用型/实体类型)
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* @author :maple
* @description:学生类
* @date :Created in 2020/11/17 16:41
*/
@Data
public class Student {
//这个注解用于对应表头,value为表头值,index为列值
@ExcelProperty(value = "姓名",index = 0)
private String name;
@ExcelProperty(value = "年龄",index = 1)
private int age;
public Student(String name, int age) {
this.name = name;
this.age = age;
}
}
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* @author :maple
* @description:
* @date :Created in 2020/11/17 16:45
*/
@Data
public class Cat {
@ExcelProperty(value = "昵称",index = 0)
private String name;
@ExcelProperty(value = "年龄",index = 1)
private int age;
public Cat(String name, int age) {
this.name = name;
this.age = age;
}
}
通用型(Object)监听器:
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 通用监听器
*/
public class ObjectListener extends AnalysisEventListener<Object> {
private final static Logger LOGGER = LoggerFactory.getLogger(ObjectListener.class);
private static final int BATCH_COUNT = 5;
//存储最终数据
List<Object> objectList = new ArrayList<>();
//存储表头数据
List<Object> headList = new ArrayList<>();
//存储sheet名
private String sheetName;
//暂时存储数据
List<Object> datas = new ArrayList<Object>();
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(o));
//一条数据添加到暂时存储的存储结构中
datas.add(o);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (datas.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
datas.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//获取sheetName
sheetName = context.readSheetHolder().getSheetName();
// LOGGER.info("所有数据解析完成!");
}
//获取表头
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
//把表头数据加入到存储结构中
headList.add(headMap);
}
/**
* 入库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", datas.size());
//添加到返回的存储结构中,也可直接存储到数据库
objectList.addAll(datas);
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
public List<Object> getObjectList() {
return objectList;
}
public void setObjectList(List<