使用 Apache Poi 生成批量 excel 文件时发生套接字异常 [英] Socket Exception while generating bulk excel file using Apache Poi

问题描述

我在为批量数据(超过 50 万条记录)生成 Excel 文件时收到 SocketConnectionException.

I am getting SocketConnectionException while generating Excel file for bulk data (more than 0.5 million records).

我的 Web 应用程序的代码写入 `outputstream.下面是一段代码:

The code of my web application writes to `outputstream. Here's a snippet of code:

while (sr.next()) {
      counter++;  //advance counter
      view = (DataClass) sr.get(0);
      try {
          //writing fields values for Activity Report file
          reportService.writeExcelFieldsValue(rowCounter,sheet,view,user,exportedFields);
          rowCounter++;
      } catch (Exception e) {
          throw new RuntimeException(e);
      }

      if (counter == chunkSize || sr.isLast()) {
          counter = 0;  //reset counter
          //Clear the session after a chunk and before next chunk
          getSession().clear();
      }
}
wb.write(bos);
bos.flush();

推荐答案

POI 提供了一个构建在 XSSF 之上的低内存占用 SXSSF API.

POI provides a low-memory footprint SXSSF API built on top of XSSF.

SXSSF 是 XSSF 的 API 兼容流扩展，可在必须生成非常大的电子表格且堆空间有限时使用.SXSSF 通过限制对滑动窗口内的行的访问来实现其低内存占用，而 XSSF 允许访问文档中的所有行.不再出现在窗口中的旧行将无法访问，因为它们被写入磁盘.

SXSSF is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document. Older rows that are no longer in the window become inaccessible, as they are written to the disk.

在自动刷新模式下，可以指定访问窗口的大小，以在内存中保存一定数量的行.当达到该值时，创建附加行会导致索引最低的行从访问窗口中删除并写入磁盘.或者，窗口大小可以设置为动态增长；可以根据需要通过显式调用 flushRows(int keepRows) 定期修剪它.

In auto-flush mode the size of the access window can be specified, to hold a certain number of rows in memory. When that value is reached, the creation of an additional row causes the row with the lowest index to to be removed from the access window and written to disk. Or, the window size can be set to grow dynamically; it can be trimmed periodically by an explicit call to flushRows(int keepRows) as needed.

由于实现的流式特性，与 XSSF 相比有以下限制:

Due to the streaming nature of the implementation, there are the following limitations when compared to XSSF:

• 在一个时间点只能访问有限数量的行.
• 不支持Sheet.clone().不支持公式求值
• 我认为此链接可能对您有所帮助

我认为您会想要使用 XSSF EventModel 代码.请参阅POI 文档以开始使用.或欲知更多详情，请点击这里

I think you'll want to use the XSSF EventModel code. See the POI documentation to get started. or For more details, click here

此链接也可能对您有所帮助.使用 POI 将大型结果集写入 Excel 文件

Also this link may help you. Writing a large resultset to an Excel file using POI

这篇关于使用 Apache Poi 生成批量 excel 文件时发生套接字异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！