Java上传Excel文件原来还有这么多坑

Java上传Excel不当可能造成的影响

程序频繁的FullGC
程序直接内存溢出
影响用户体验
…….

重点来了，如何去排查呢？

直接码干货，最详细的排查和解决步骤来了

1. 文件大小控制

程序要支持动态调整上传Excel文件大小，并且调整后立即生效，相信大家都知道，如果上传文件太大解析时候必然会影响程序的稳定性和可用性。而对于动态调整是因为程序上线之后很难再修改代码，及时修改配置文件也要重启才能生效，而这些操作上线之后需要走发版等复杂流程。

2. 上传单个Excel文件行数限制

程序需要支持设置合理的参数控制单个Excel文件行数限制，如果查过行数限制应放弃对文件的解析，并给出适当错误信息提示用户处理。

应该如何检查文件行数呢？

当使用Apache POI usermodel api解析excel时，获得workbook对象之后必须先判断行数上限是否超过预设上限，超过则直接放弃对workbook对象解析，给jvm机会回收掉因本次大Excel文件解析造成的大对象，让系统能尽快恢复。因为整体加载excel会造成极大内存消耗，可能导致频繁的FullGC发生影响程序正常使用。
当使用Apache POI event api 或者其他流式解析api（如EasyExcel）解析excel时，由于是流式解析无法直接获取总行数，需要在流式解析式自行计算行数，超过行数上限的也应放弃解析,返回合适错误。

3. 数据不合法行数校验

此处主要针对将Excel每行数据转换成Java对象的场景，如果出现连续多行转换失败应放弃解析文件并返回适当错误信息。此处特别是出现连续大量空行的情况，很可能造成FullGC的产生，所以连续大量空行数据直接拒绝处理。

4. 检查是否关闭Workbook对象和流对象

通过Apache POI操作Excel对象完成之后，必须及时关闭Workbook对象以及相关流对象。

Apache POI操作Excel过程中会创建相关的Workbook和InputStream，OutputStream对象，这些对象都是Closeable对象，这类对象在使用完毕之后都应该被关闭（调用该对象的close方法），关闭时Closeable对象会释放其持有的资源（一般是文件句柄），否则就会造成资源泄露，关于为何关闭流，可以查看Java文件操作完为何要关闭流，正确的写法：

1  使用try-with-resources语法糖自动关闭 （适用java1.7及以后版本）:
File dstFile = new File("custom.xlsx");
try(InputStream inputStream = new FileInputStream(dstFile);
      Workbook workbook = WorkbookFactory.create(inputStream);
      OutputStream outputStream = new FileOutputStream(dstFile)) {
            Sheet sheet = workbook.getSheetAt(0);
            //……省略逻辑 修改sheet
            workbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            //error process
        }

5. 避免修改源文件

此处主要为使用POI处理从模板Excel复制内容然后改写生成新文件的场景，需先从模板文件复制生成新文件，然后直接读写新文件，避免模板文件被覆盖。

正确的操作：

File temp = new File("tpl.xlsx");
File dstFile = new File("dst.xlsx");
// 从模板文件tpl.xlsx复制出目标文件
try {
    FileUtils.copyFile(templateFile, dstFile);
} catch (IOException e) {
    throw new RuntimeException(e);
}
// 直接修改目标文件
try(InputStream inputStream = new FileInputStream(dstFile);
    Workbook workbook = WorkbookFactory.create(inputStream);
    OutputStream outputStream = new FileOutputStream(dstFile)) {
        Sheet sheet = workbook.getSheetAt(0);
       //……省略逻辑 修改sheet
        workbook.write(outputStream);
 } catch (Exception e) {
        // error process
 }

为什么这么做呢

由于POI基于文件创建Workbook对象时，部分API接口如WorkbookFactory.create(File file)和WorkbookFactory.create(File file,String password)创建的workbook对象修改之后，调用workbook.close()方法时会触发对源文件的修改，workbook的最新内容会覆盖源文件。

其中API接口WorkbookFactory.create(File file,String password,Boolean readOnly)的参数readyOnly设为true，也可以避免对workbook对象写操作调用close方法将最新内容覆盖源文件。

6. 控制文件上传的并发数量

此场景主要为程序并发请求较多，可能会存在同时上传多个Excel请求和解析，可能会造成频繁FullGC，影响程序的稳定性和可用性。因此需要根据程序的处理能力合理设置文件上传的并发数。

7. 程序生成大Excel文件

此场景主要为多个Excel文件数据或者大批量数据（达到万级别）生成一个Excel文件。此时确认使用SXSSFWorkbook对象进行写操作，写入完毕后必须显式调用SXSSFWorkbook.dispose()。

SXSSFWorkbook workbook = new SXSSFWorkbook(200);
OutputStream outputStream = null;
try {
    outputStream = new FileOutputStream("dstFile.xlsx");
    Sheet sheet = workbook.createSheet();
    //....此处省略代码向sheet写入数据
    workbook.write(outputStream);
    outputStream.flush();
} catch (Exception e) {
    // error process
} finally {
    try {
        outputStream.close();
    } catch (Throwable throwable) {
        // error process
    }
    try {
        workbook.dispose();
    } catch (Throwable throwable) {
        // error process
    }
    try {
        workbook.close();
    } catch (Throwable throwable) {
        // error process
    }
}

POI SXSSFWorkbook 提供了流式生成大型Excel文件的方法，通过提供一个固定大小的窗口保存当前数据，超过窗口行数上限的数据会被写入到临时文件.

//流式读取每一行数据，每次加载部分数据到内存中。
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) {
    // error process
}