流式读写Excel的数据【适用数据量大的导入导出操作时使用】

我们在研发项目或产品时，会有遇到要上传文件到系统的情况，系统会对上传的文件数据进行读取、解析、验证、做业务处理、最后导入数据到库的操作。

一般1W之内的数据，用apache中的POI包可以实现解析数据的功能，POI会将数据加载到内存中，虽然读取数据需要占用一些内存，但还是能比较轻松应对的，因为数据量比较小。

不过随着你上传的Excel的数据量的增大，2W、3W、4W、5W... 10W...50W, 甚至是100W以上，你的内存就会溢出了，内存虽然能扩大，但是如果系统并发的读取Excel的数据进行处理，系统照样会挂。

如何解决读取数据量较大的Excel文件而内存溢出的问题呢？

POI没有提供解决的包，因此有人就会想，采取部分的数据进行读取，这样就不用一次加载数据到内存中，github上有人提供的解决方案的包，他在POI的基础之上，先将上传的文件写到本地临时文件夹中，然后用POI的ZipPackage解析临时文件，读出格式为"application/vnd.openxmlformats-package.core-properties+xml"的内容，其实它是XML的格式数据，然后将拆解的XML数据写到本地文件中，再用XSSFReader读取ZipPackage中的workbook的XML对应的"/workbook/sheets/sheet"数据流，将每一个Sheet流封装成XMLEventReader，他将XMLEventReader封装到他自己定义的StreamingSheetReader，把StreamingSheetReader装入StreamingSheet，最后将StreamingSheet封装入StreamingWorkbook，而StreamingWorkbook实现Workbook接口，这样他就可以根据rowCacheSize控制每次通过XMLEventReader从XML数据流中迭代获取指定的数据到一个List中，用完了就清空List，这样就避免了一次将整个Excel全部读取到内存，从而形成像流水一样的读取方式。此人【Taylor Jones】开发的工具包如下所示：

流式读取包：Excel Streaming Reader

支持的文件格式：支持xlsx格式的文件，不支持xls格式的文件

GitHub地址：https://github.com/monitorjbl/excel-streaming-reader

Maven Repository：https://mvnrepository.com/artifact/com.monitorjbl/xlsx-streamer

此工具采取了策略设计模式+代理模式+流式读取改造了POI，利用POI内部处理OOXML的方法重新实现了一套Workbook，读取数据交由XMLEventReader代理，使用控制读取数量的方式利用实现了流式读取从而来控制内存的大小，使得再低的内存配置都能抗得住上百万的数据工作，而且多个文件多线程同时读取操作也没有问题。在代码写的方式上来看，除了构造出Workbook对象的写法有一些变化之化，其它操作写法和POI没有什么区别，对用户来说跟使用POI一样。

由于此工具只支持xlsx的Excel文件，不支持xls的Excel文件，在使用此工具时需要确认你的项目中是否必须支持xls的文件。

• XLSX格式： Microsoft Excel XML (2007+) 的文件格式(OOXML)，它遵循OOXML标准｜规范。
• XLS格式：Microsoft Excel 97 (-2003) 的文件格式(BIFF8)，它遵循OLE2标准｜规范。

关于OOXML 标准和OLE2标准的详细介绍，请参照：https://www.cnblogs.com/ZhangZiSheng001/p/12532074.html

流式读取Excel数据具体步骤如下：

1. 在POM文件中引入包

    <properties>
        <poi.version>4.1.0</poi.version>
        <xlsx-streamer.version>2.2.0</xlsx-streamer.version>
    </properties>

	<!-- Excel操作POI-->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>${poi.version}</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>${poi.version}</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml-schemas</artifactId>
		<version>${poi.version}</version>
	</dependency>
	<!--流式读取Excel数据-->
	<dependency>
		<groupId>com.monitorjbl</groupId>
		<artifactId>xlsx-streamer</artifactId>
		<version>${xlsx-streamer.version}</version>
	</dependency>

2. 流式读取数据

package com.crh.demo.service.batch.impl;

import com.crh.demo.service.batch.ExcelImportService;
import com.monitorjbl.xlsx.StreamingReader;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;

import java.io.InputStream;

/**
 * Excel导入服务实现类
 *
 * @author crh
 */
@Slf4j
@Service
public class ExcelImportServiceImpl implements ExcelImportService {

    @Override
    public void readExcelData(InputStream is) throws Exception {
        //流式读取每一行数据，每次加载部分数据到内存中。
        try (Workbook workbook = StreamingReader.builder()
                //读取Excel数据到缓存的行数，默认10条每次。
                .rowCacheSize(100)
                //读Excel数据写到临时文件的数据缓冲的字节大小，默认是1024
                .bufferSize(1024)
                .open(is)) {
            Sheet sheet = workbook.getSheet("导入数据");
            if (sheet == null) {
                throw new Exception("数据工作表不存在");
            } else {
                for (Row row : sheet) {
                    if (row.getRowNum() == 0) {
                        //获取标题行【第一行】名称列表
                    } else if (row.getRowNum() > 0) {
                        //获取数据行【第二行】数据
                        //业务校验，处理，插入数据库。
                    }
                }
            }
        } catch (Exception e) {
            log.error("导入数据时出现异常", e);
            throw new Exception(e.getMessage());
        }
    }
}