一、需求背景
上传60M,100w行的Excel文档,读取数据到MySQL库
使用POI读取,直接内存溢出
于是使用阿里提供的easyExcel
二、easyExcel简介
官方api地址:https://www.yuque.com/easyexcel/doc/easyexcel
github地址:https://github.com/alibaba/easyexcel
简单来说 尽可能节约内存的来支持读写,使用磁盘空间换取内存空间
节约内存操作
1、如果ta接收的是一个文件流,需要缓存成文件,不然解析会很占内存
2、并拆分成多个xml文件
3、5M内 直接放到内存map,超过5M 使用 ehcache 存储成文件
4、逐行读取分析
三、easyExcel读取
1、Excel格式分析
分析得到标题: 字符串 数字 时间
对应的数据类型为:String Double Date
2、构建Excel数据实体类
@Data
public class ExcelData {
@ExcelProperty("字符串")
private String string;
@ExcelProperty("数字")
private Double doubleData;
@ExcelProperty("时间")
private Date date;
}
3、构建监听器
(1)简单版,单纯的数据读取
public class ExcelDataListener extends AnalysisEventListener<ExcelData> {
private static final Logger logger = LoggerFactory.getLogger(ExcelDataListener.class);
/**
* 解析到一行数据
*/
@Override
public void invoke(ExcelData data, AnalysisContext context) {
logger.info("解析到一条数据:{}", JSON.toJSONString(data));
}
/**
* 所有数据解析完成
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
logger.info("所有数据解析完成!");
}
}
(2)进阶版,数据存储
// DemoDataListener 不能被spring管理,无法注入Dao层进行数据存储,可以使用构造函数传递Dao层
public class ExcelDataListener extends AnalysisEventListener<ExcelData> {
private static final Logger logger = LoggerFactory.getLogger(ExcelDataListener.class);
private DataDAO dataDao;
/**
* 构造函数传递Dao层
*/
public DemoDataListener(DataDAO dataDao) {
this.dataDao = dataDao;
}
/**
* 解析到一行数据
*/
@Override
public void invoke(ExcelData data, AnalysisContext context) {
logger.info("解析到一条数据:{}", JSON.toJSONString(data));
dataDao.save(data);
logger.info("存储完成:{}", JSON.toJSONString(data));
}
/**
* 所有数据解析完成
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
logger.info("所有数据解析完成!");
}
}
(3)高级进阶版-批量存储
// DemoDataListener 不能被spring管理,无法注入Dao层进行数据存储,可以使用构造函数传递Dao层
public class ExcelDataListener extends AnalysisEventListener<ExcelData> {
private static final Logger logger = LoggerFactory.getLogger(ExcelDataListener.class);
/**
* 每隔1000条存储数据库
*/
private static final int MAX_COUNT = 10000;
List<ExcelData> list = new ArrayList<ExcelData>();
private DataDAO dataDao;
/**
* 构造函数传递Dao层
*/
public DemoDataListener(DataDAO dataDao) {
this.dataDao = dataDao;
}
/**
* 解析到一行数据
*/
@Override
public void invoke(ExcelData data, AnalysisContext context) {
logger.info("解析到一条数据:{}", JSON.toJSONString(data));
//存储到list中
list.add(data);
//到达我们设置的上限
if (list.size() >= MAX_COUNT) {
//批量存储
dataDao.saveAll(list);
logger.info("存储完成:{}", list.size());
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//批量存储 确保最后遗留的数据保存成功
dataDao.saveAll(list);
logger.info("存储完成:{}", list.size());
logger.info("所有数据解析完成!");
}
}
(4)高级进化-多线程
// 使用线程队列,先将数据推到队列,然后再使用多线程消费队列
public class ExcelDataListener extends AnalysisEventListener<ExcelData> {
private static final Logger logger = LoggerFactory.getLogger(ExcelDataListener.class);
private DataDAO dataDao;
private RedisTemplate redisTemplate;
private String key;
/**
* 构造函数传递Dao层 和 Redis层以及key
*/
public DemoDataListener(DataDAO dataDao,RedisTemplate redisTemplate,String key) {
this.dataDao = dataDao;
this.redisTemplate = redisTemplate;
this.key = key;
}
/**
* 解析到一行数据
*/
@Override
public void invoke(ExcelData data, AnalysisContext context) {
String value = JSON.toJSONString(data);
logger.info("解析到一条数据:{}", value);
redisTemplate.set(key,value);
logger.info("存储完成:{}", value);
}
/**
* 所有数据解析完成
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
logger.info("所有数据解析完成!");
}
}
多线程消费
ExecutorService newFixedThreadPool = Executors.newFixedThreadPool(15);
for (int i=0;i<15;i++){
newFixedThreadPool.execute(new Runnable() {
@Override
public void run() {
try {
while (true){
//从easyExcel填充的队列里读取
String value = redisTemplate.spop(key);
if(StringUtils.isEmpty(value)){
//队列为空暂时休眠,自己调控
Thread.sleep(30000);
}else{
//解析json并保存
ExcelData excelData = JSON.parseObject(value, ExcelData.class);
excelDao.save(excelData);
}
}
}catch (Exception e){
e.printStackTrace();
}
}
});
}
四、开始读取Excel
ExcelDataListener listener = new ExcelDataListener();
ExcelReader excelReader = null;
try {
logger.info("excelReader 构建");
excelReader = EasyExcel.read(file.getInputStream(), ExcelData.class, listener).build();
logger.info("readSheet 构建");
ReadSheet readSheet = EasyExcel.readSheet(0).build();
logger.info("readSheet 读取");
excelReader.read(readSheet);
}catch (Exception e){
e.printStackTrace();
}finally {
if (excelReader != null) {
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
}
}