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

一.问题背景

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

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

二.HSSFworkbook，XSSFworkbook，SXSSFworkbook分析

序号	类	版本	限制	描述
1	HSSFworkbook	Excel2003及以前，扩展名为.xls	65536行，256列	因为限制数据行数较少，一般不会发生OOM
2	XSSFworkbook	Excel2007以后，扩展名为.xlsx	1048576行，16384列	数据限制显著提升，但也正因可读写的数据量变大可能导致OOM
3	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）导包

        <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

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()方法，此处不再赘述。