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

亦凉 2024-03-27 17:27 95阅读 0赞

#### 一.问题背景 ####

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

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

#### 二.HSSFworkbook，XSSFworkbook，SXSSFworkbook分析 ####

<table style=""> 
 <tbody> 
 <tr> 
 <td style="width:68px;vertical-align:top;height:52px;">序号</td> 
 <td style="width:139px;vertical-align:top;height:52px;">类</td> 
 <td style="width:142px;vertical-align:top;height:52px;">版本</td> 
 <td style="width:119px;vertical-align:top;height:52px;">限制</td> 
 <td style="width:412px;vertical-align:top;height:52px;">描述</td> 
 </tr> 
 <tr> 
 <td style="width:68px;vertical-align:top;height:52px;">1</td> 
 <td style="width:139px;vertical-align:top;height:52px;">HSSFworkbook</td> 
 <td style="width:142px;vertical-align:top;height:52px;">Excel2003及以前，扩展名为.xls</td> 
 <td style="width:119px;vertical-align:top;height:52px;">65536行，256列</td> 
 <td style="width:412px;vertical-align:top;height:52px;">因为限制数据行数较少，一般不会发生OOM</td> 
 </tr> 
 <tr> 
 <td style="width:68px;vertical-align:top;height:52px;">2</td> 
 <td style="width:139px;vertical-align:top;height:52px;">XSSFworkbook</td> 
 <td style="width:142px;vertical-align:top;height:52px;">Excel2007以后，扩展名为.xlsx</td> 
 <td style="width:119px;vertical-align:top;height:52px;">1048576行，16384列</td> 
 <td style="width:412px;vertical-align:top;height:52px;">数据限制显著提升，但也正因可读写的数据量变大可能导致OOM</td> 
 </tr> 
 <tr> 
 <td style="width:68px;vertical-align:top;height:52px;">3</td> 
 <td style="width:139px;vertical-align:top;height:52px;">SXSSFworkbook</td> 
 <td style="width:142px;vertical-align:top;height:52px;">Excel2007以后，扩展名为.xlsx</td> 
 <td style="width:119px;vertical-align:top;height:52px;">大型Excel</td> 
 <td style="width:412px;vertical-align:top;height:52px;">用硬盘换内存，保存最新的rows在内存里，之前的rows写入硬盘，被写入硬盘的rows不可访问，想要访问就要指定读进内存数据的大小，以保证所有数据都能读取，因此，此方法适合大型Excel导出，对于导入还是会发生OOM。</td> 
 </tr> 
 </tbody> 
</table>

表格整理参考官网：

[https://poi.apache.org/apidocs/dev/org/apache/poi/ss/SpreadsheetVersion.html][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][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）导包**

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

**（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][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 <= sumPageNum; i++) { //读取每片范围内的内容
 try {
 int minRowNum = 1000 * (i - 1) + 1; //计算每片读取的行数范围
 int maxRowNum = 1000 * i;
 for (Row row : sheet) {
 if (isRowEmpty(row)) {
 continue;
 }
 Map<String, Object> columnValueMap = new HashMap<>();
 if (row.getRowNum() >= minRowNum && row.getRowNum() <= maxRowNum) {//控制每片读取的行
 /**
 * 此处读取每行、每个单元格的内容，组装要写入数据库的对象
 */
 if (row.getRowNum() == maxRowNum) {//读取到每片最大行数限制，跳出，执行数据写入
 break;
 }
 }
 }
 /**
 * 此处执行数据写入
 */
 } catch (Exception e) {
 e.printStackTrace();
 } finally {
 /**
 * 清空对象
 * commonTableEntity = null;
 * columnValueList.clear();
 */
 }
 }
 }
 wb.close();
 } catch (Exception e) {
 e.printStackTrace();
 } finally {
 IOUtils.closeQuietly(fis);
 }
 }

##### 3.及时清空对象和集合，以便被GC回收 #####

将对象置为null或调用集合的clear()方法，此处不再赘述。

[https_poi.apache.org_apidocs_dev_org_apache_poi_ss_SpreadsheetVersion.html]: 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]: https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFWorkbook.html
[https_github.com_monitorjbl_excel-streaming-reader]: https://github.com/monitorjbl/excel-streaming-reader