easyexcel的底层是poi,它是对poi的封装,原理是在读取excel文件达到一定的数量后进行清空内存,并再继续读取内容,从而达到防止内存溢出的问题。
64M内存1分钟内读取75M(46W行25列)的Excel:
简单上手三步骤:
1、编写监听器,当读取数据达到一定数量时进行操作,并清空内存空间
package com.alibaba.easyexcel.test.temp.read;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.fastjson.JSON;
/**
* 模板的读取类
*
* @author
*/
public class HDListener extends AnalysisEventListener<HeadReadData> {
private static final Logger LOGGER = LoggerFactory.getLogger(HDListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
LOGGER.info("HEAD:{}", JSON.toJSONString(headMap));
LOGGER.info("total:{}", context.readSheetHolder().getTotal());
}
@Override
public void invoke(HeadReadData data, AnalysisContext context) {
LOGGER.info("index:{}", context.readRowHolder().getRowIndex());
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
LOGGER.info("所有数据解析完成!");
}
}
2、编写读取excel文件的对象,会根据该对象进行数据封装返回
package com.alibaba.easyexcel.test.temp.read;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.experimental.Accessors;
/**
* 临时测试
*
* @author
**/
@Data
@Accessors(chain = true)
public class HeadReadData {
@ExcelProperty("头1")
private String h1;
@ExcelProperty({"头", "头2"})
private String h2;
}
3、创建easyexcel读取对象读取文件内容
package com.alibaba.easyexcel.test.temp.read;
import java.io.File;
import org.junit.Ignore;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.cache.Ehcache;
/**
* 临时测试
*
* @author
**/
@Ignore
public class HeadReadTest {
private static final Logger LOGGER = LoggerFactory.getLogger(HeadReadTest.class);
@Test
public void test() throws Exception {
File file = new File("D:\\test\\headt1.xls");
EasyExcel.read(file, HeadReadData.class, new HDListener()).sheet(0).doRead();
}
}