常用的poi工具,如easy-excel,hutool读取excel时都是先将整个excel加载到内存中分析,然后再一行行遍历,当excel文件太大时读取的时间就会更长,如果我们只需要读取excel的前几行来进行预览就不能使用这种方式,应该按需读取。
- 引入依赖
<!-- 按需读取用到的工具类 -->
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
- 编写代码
public class ReadBigExcel {
public static List<List<String>> test(File file,Integer sheetIndex,Integer readRow) throws Exception{
FileInputStream in = new FileInputStream(file);
Workbook wk = StreamingReader.builder()
.rowCacheSize(100) //缓存到内存中的行数,默认是10
.bufferSize(4096) //读取资源时,缓存到内存的字节大小,默认是1024
.open(in); //打开资源,必须,可以是InputStream或者是File,支持xls和xlsx
//获取sheet数量
//int sheetNums = wk.getNumberOfSheets();
//获取第n个sheet
Sheet sheet = wk.getSheetAt(sheetIndex);
Integer cursor = 1;
List<List<String>> excelData = new ArrayList<>();
List<List<String>> sheetDataList = CollectionUtil.streamOf(sheet.iterator()).map(row ->
CollectionUtil.streamOf(row.cellIterator())
.map(Cell::getStringCellValue)
.collect(Collectors.toList())
).limit(readRow).collect(Collectors.toList());
return sheetDataList;
}
public static void main(String[] args) throws Exception {
File file = new File("D:\\数据预览5万行.xls");
long t1 = System.currentTimeMillis();
List<List<String>> test = test(file, 0, 5);
long t2 = System.currentTimeMillis();
System.out.println((t2-t1)/1000+ "秒");
System.out.println(test);
}
}