java读取大数据量Excel按需读取(按需加载，速度快)

常用的poi工具，如easy-excel，hutool读取excel时都是先将整个excel加载到内存中分析，然后再一行行遍历，当excel文件太大时读取的时间就会更长，如果我们只需要读取excel的前几行来进行预览就不能使用这种方式，应该按需读取。

1. 引入依赖

<!-- 按需读取用到的工具类 -->
<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>

2. 编写代码

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);

    }
}