解决POI的XSSFWorkbook导入大excel的内存消耗过大问题

方式1:使用SXSSFWorkbook ,经过测试,这个情况无效,因为本质上SXSSFWorkbook 也是通过XSSFWorkbook来的,他可以解决写出excel的场景,但是解决不了我们这种用户上传且读取excel中的内容的场景

参考:
记一次解决poi XSSFWorkbook导出excel内存溢出换成SXSSFWorkbook还是内存溢出的问题
https://blog.csdn.net/weixin_38008837/article/details/120703835

SXSSFWorkbook workBook1 = new SXSSFWorkbook();
SXSSFSheet sheet1 = workBook1.createSheet("sheetName");
//具体要写入的业务逻辑等
//创建行 设置行样式等
...
//写入数据 设置单元格样式等
...
//不写此句，没有写入磁盘依然占用内存 之前就是没有写这句，生成的临时文件都是0kb，加上即解决了问题
sheet1.flushRows();

//正确的获取需要使用getRow的sheet的方法
Sheet sheet = workBook.getXSSFWorkbook().getSheetAt(i);

        XSSFWorkbook XSSFWorkbook =  new XSSFWorkbook(fileInputStream);
        System.gc();
        SXSSFWorkbook SXSSFWorkbook = new SXSSFWorkbook(XSSFWorkbook);

方式2:使用xlsx-streamer的方式,分段读取.
参考:https://www.cnblogs.com/longronglang/p/13942394.html

1
测试步骤
1.导入依赖

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

2.设置jvm参数:-Xmx100m -Xms100M

package com.hgh;

import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileInputStream;
import java.io.IOException;

/**
 * -Xmx100m -Xms100M
 *
 */
public class POIDemo3 {
    public static void main(String[] args) throws IOException {
        long maxUserMemory = 0;
        FileInputStream in = new FileInputStream("F://测试excel-50000.xlsx");
        long heapSize = Runtime.getRuntime().totalMemory();
        long heapMaxSize = Runtime.getRuntime().maxMemory();
        long heapFreeSize = Runtime.getRuntime().freeMemory();
        long memoryUse = heapSize - heapFreeSize;
        if (memoryUse >maxUserMemory){
            maxUserMemory = memoryUse;
        }
        System.out.println("heapsize"+formatSizeUtil.formatSize(heapSize));
        System.out.println("heapmaxsize"+formatSizeUtil.formatSize(heapMaxSize));
        System.out.println("heapFreesize"+formatSizeUtil.formatSize(heapFreeSize));
        System.out.println("使用内存"+formatSizeUtil.formatSize(memoryUse));
        Workbook wk = StreamingReader.builder()
                .rowCacheSize(100)  //缓存到内存中的行数，默认是10
                .bufferSize(8192)  //读取资源时，缓存到内存的字节大小，默认是1024
                .open(in);  //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
        heapSize = Runtime.getRuntime().totalMemory();
        heapMaxSize = Runtime.getRuntime().maxMemory();
        heapFreeSize = Runtime.getRuntime().freeMemory();
        memoryUse = heapSize - heapFreeSize;
        if (memoryUse >maxUserMemory){
            maxUserMemory = memoryUse;
        }
        System.out.println("heapsize"+formatSizeUtil.formatSize(heapSize));
        System.out.println("heapmaxsize"+formatSizeUtil.formatSize(heapMaxSize));
        System.out.println("heapFreesize"+formatSizeUtil.formatSize(heapFreeSize));
        System.out.println("使用内存"+formatSizeUtil.formatSize(memoryUse));
        Sheet sheet = wk.getSheetAt(0);
        //遍历所有的行
        for (Row row : sheet) {
           // System.out.println("开始遍历第" + row.getRowNum() + "行数据：");
            //遍历所有的列
            for (Cell cell : row) {
                //System.out.print(cell.getStringCellValue() + " ");
            }
            heapSize = Runtime.getRuntime().totalMemory();
            heapMaxSize = Runtime.getRuntime().maxMemory();
            heapFreeSize = Runtime.getRuntime().freeMemory();
            memoryUse = heapSize - heapFreeSize;
            if (memoryUse >maxUserMemory){
                maxUserMemory = memoryUse;
            }
        }
        heapSize = Runtime.getRuntime().totalMemory();
        heapMaxSize = Runtime.getRuntime().maxMemory();
        heapFreeSize = Runtime.getRuntime().freeMemory();
        if (memoryUse >maxUserMemory){
            maxUserMemory = memoryUse;
        }
        System.out.println("heapsize"+formatSizeUtil.formatSize(heapSize));
        System.out.println("heapmaxsize"+formatSizeUtil.formatSize(heapMaxSize));
        System.out.println("heapFreesize"+formatSizeUtil.formatSize(heapFreeSize));
        System.out.println("使用内存"+formatSizeUtil.formatSize(heapSize - heapFreeSize));
        System.out.println("最大使用内存=" + formatSizeUtil.formatSize(maxUserMemory));
    }
}