Excel百万数据导入内存溢出(OOM)解决方案，以及HSSFworkbook，XSSFworkbook，SXSSFworkbook失败分析

2024-07-14 22:05| 来源: 网络整理| 查看: 265

一.问题背景

Excel百万数据导入，每行50+列，出现内存溢出，尝试HSSFworkbook，XSSFworkbook，SXSSFworkbook均未解决。

查阅资料，采取StreamingReader的方式，问题解决。

二.HSSFworkbook，XSSFworkbook，SXSSFworkbook分析

序号

类

版本

限制

描述

HSSFworkbook

Excel2003及以前，扩展名为.xls

65536行，256列

因为限制数据行数较少，一般不会发生OOM

XSSFworkbook

Excel2007以后，扩展名为.xlsx

1048576行，16384列

数据限制显著提升，但也正因可读写的数据量变大可能导致OOM

SXSSFworkbook

Excel2007以后，扩展名为.xlsx

大型Excel

用硬盘换内存，保存最新的rows在内存里，之前的rows写入硬盘，被写入硬盘的rows不可访问，想要访问就要指定读进内存数据的大小，以保证所有数据都能读取，因此，此方法适合大型Excel导出，对于导入还是会发生OOM。

表格整理参考官网：

https://poi.apache.org/apidocs/dev/org/apache/poi/ss/SpreadsheetVersion.html

https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFWorkbook.html

三.解决方案 1.文件分片读取

如上述介绍，HSSFworkbook数据上限太低，不可行；XSSFworkbook会发生OOM，不可行；SXSSFworkbook读取数据做导入还是会发生OOM，不可行。

使用StreamingReader可以解决大文件读取造成的OOM，它可以根据配置将一个大文件分成多个临时文件，再读取一部分Row到内存中，全部读取完成后将临时文件删掉。但是它仅支持XLSX文件，需要java8及以上运行环境，因为，StreamingReader是基于POI实现的，而POI 4.0需要java8及以上。

具体实现：

（1）导包

com.monitorjbl xlsx-streamer 2.2.0

（2）读取文件

import com.monitorjbl.xlsx.StreamingReader; InputStream is = new FileInputStream(new File("/path/to/workbook.xlsx")); Workbook workbook = StreamingReader.builder() .rowCacheSize(100) // 缓存到内存中的行数(默认是10) .bufferSize(4096) // 读取资源时，缓存到内存的字节大小(默认是1024) .open(is); // InputStream或者XLSX格式的File(必须)

（3）读取sheet、行和单元格

for (Sheet sheet : workbook){ System.out.println(sheet.getSheetName()); for (Row r : sheet) { for (Cell c : r) { System.out.println(c.getStringCellValue()); } } }

（4）读取指定sheet

Sheet sheet = workbook.getSheet("Sheet名称")

（5）关于关流

官方文档描述StreamingReader会自动关闭流，不需要手动关闭。但我在使用过程中发现，不关闭流删除临时文件时会报错，删除失败。手动关闭流问题解决。

官方文档及源码：https://github.com/monitorjbl/excel-streaming-reader

2.数据分片写入数据库

本例导入数据时采取的是insert values批量插入的方式，若一个Excel中数据量过大他，同样会产生性能问题，可采用数据分片的方式，每次写入一定数量的数据，分批写入。

代码片段：

private void test() { FileInputStream fis = null; try { fis = new FileInputStream(new File("/path/to/workbook.xlsx")); Workbook wb = StreamingReader.builder() .rowCacheSize(100) //缓存到内存中的行数，默认是10 .bufferSize(4096) //读取资源时，缓存到内存的字节大小，默认是1024 .open(fis); //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件 for (Sheet sheet : wb) { // sheet.getLastRowNum()获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1 int sumPageNum = (sheet.getLastRowNum() + 1) / 1000 + 1; //计算分片数 for (int i = 1; i = minRowNum && row.getRowNum()

【本文地址】

公司简介

联系我们