Java 将 POI 工作簿流式传输到 servlet 输出流
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2673751/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Streaming a POI workbook to the servlet output stream
提问by The Machine
I build a very large POI workbook, on my web server. Holding the entire workbook in memory , will not scale for multiple concurrent requests. Is there a way i can progressively write the workbook to the servlet output stream. That should reduce the response time , as well as make the process memory efficient.
我在我的 Web 服务器上构建了一个非常大的 POI 工作簿。将整个工作簿保存在内存中，不会针对多个并发请求进行扩展。有没有一种方法可以逐步将工作簿写入 servlet 输出流。这应该会减少响应时间，并使进程内存高效。
回答by BalusC
Unfortunately, that's impossible when there's no means of sequential data. I'd suggest to look for another format, e.g. CSV or XML. Both can be written out sequentially. If it's coming from a DB, it can even be done more efficient since a decent DB has builtin facilities to efficiently export to those formats. You just have to stream the bytes from one to other side.
不幸的是，当没有顺序数据的方法时，这是不可能的。我建议寻找另一种格式，例如 CSV 或 XML。两者都可以顺序写出。如果它来自数据库，它甚至可以更有效地完成，因为一个体面的数据库具有内置设施来有效地导出到这些格式。您只需要将字节从一侧流式传输到另一侧。
回答by Romain Hippeau
If you use JExcelIt has sample code to read stream code to and from a Servlet. http://jexcelapi.sourceforge.net/resources/faq/
如果您使用JExcel它有示例代码可以从 Servlet 读取流代码。 http://jexcelapi.sourceforge.net/resources/faq/
The only downside to this API looks like it only supports up to Excel 2003 inclusive.
此 API 的唯一缺点是它最多只支持 Excel 2003（含）。
Using POI - Can you not create the file and serve the file's bytes to the servlet output stream ?
使用 POI - 您不能创建文件并将文件的字节提供给 servlet 输出流吗？
回答by hlg
If you are about to generate Excel 2007 (xslx) then you could adapt the approach of BigGridDemo.java as described here: http://web.archive.org/web/20110821054135/http://www.realdevelopers.com/blog/code/excel
如果您要生成 Excel 2007 (xslx)，那么您可以采用此处所述的 BigGridDemo.java 方法：http://web.archive.org/web/20110821054135/http: //www.realdevelopers.com/blog /代码/excel
The solution is to let POI generate a container xslx as a template only and stream the actual spreadsheet data as XML into a zip output stream. Streamlining the XML generation is then up to you.
解决方案是让 POI 仅生成一个容器 xslx 作为模板，并将实际电子表格数据作为 XML 流式传输到 zip 输出流中。简化 XML 生成取决于您。
回答by mtrovo
Did you tried with the write method direct to the HttpServletResponse.getOutputStream()?
您是否尝试将 write 方法直接写入 HttpServletResponse.getOutputStream()？
Please take a look at the following example:
请看下面的例子：
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
...
OutputStream out = response.getOutputStream();
wb.write(out);
out.close();
回答by Timothy Jones
The situation has improved considerably since the rest of the answers were written - Streaming is now part of Apache Poi.
自从写了其余的答案以来，情况已经有了很大改善 - Streaming 现在是 Apache Poi 的一部分。
See the SXSSFWorkbookclass, and the documentation here. It uses a streaming window over the sheet, flushing old rows outside the window to temporary files.
请参阅SXSSFWorkbook类和此处的文档。它在工作表上使用流窗口，将窗口外的旧行刷新到临时文件。
This is based on the BigGridDemoapproach used in hlg's answer, but now part of the official distribution.
这是基于hlg's answer 中BigGridDemo使用的方法，但现在是官方发行版的一部分。
Here's the example from the documentation:
这是文档中的示例：
public static void main(String[] args) throws Throwable {
// keep 100 rows in memory, exceeding rows will be flushed to disk
SXSSFWorkbook wb = new SXSSFWorkbook(100);
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 1000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
}
// Rows with rownum < 900 are flushed and not accessible
for(int rownum = 0; rownum < 900; rownum++){
Assert.assertNull(sh.getRow(rownum));
}
// ther last 100 rows are still in memory
for(int rownum = 900; rownum < 1000; rownum++){
Assert.assertNotNull(sh.getRow(rownum));
}
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
// dispose of temporary files backing this workbook on disk
wb.dispose();
}
