使用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屋！