Java 读取超大excel文件

注意：此参考解决方案只是针对xlsx格式的excel文件！

Maven

<dependency><groupId>com.monitorjbl</groupId><artifactId>xlsx-streamer</artifactId><version>2.2.0</version>
</dependency>

读取方式1

private static void readBigExcel(String filePath) throws FileNotFoundException {FileInputStream in = new FileInputStream(filePath);StreamingReader reader = StreamingReader.builder().rowCacheSize(100)    // number of rows to keep in memory (defaults to 10).bufferSize(4096)     // buffer size to use when reading InputStream to file (defaults to 1024).sheetIndex(0)        // index of sheet to use (defaults to 0).read(in);            // InputStream or File for XLSX file (required)for (Row r : reader) {for (Cell c : r) {System.out.print(c.getStringCellValue() + "  ");}System.out.println();}
}

读取方式2

public static Workbook getStreamWorkbook(String filePath) throws FileNotFoundException {FileInputStream in = new FileInputStream(filePath);Workbook wk = StreamingReader.builder().rowCacheSize(100)  //缓存到内存中的行数，默认是10.bufferSize(4096)   //读取资源时，缓存到内存的字节大小，默认是1024.open(in);          //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件return wk;
}

获取到Workbook后，余下读取cell方法参考 java 读取excel，因为超大文件，所以只能使用for each循环方式遍历excel。

参考

Java中使用POI读取大的Excel文件或者输入流时发生out of memory异常参考解决方案
How to load a large xlsx file with Apache POI?