Streaming a POI SXSSFWorkbook to the servlet output stream

[Ramona Petricu <ra...@metrosystems.net>]

Hi,

We are building a Spring boot REST endpoint that generates a large XLS file (may contain ~ 1mil lines) and provides it for download.
The current solution uses the SXSSF API of Apache POI library for creating the workbook;
after that we write the workbook to an output stream, collect the stream in to an array of bytes and then provide this one for download.

How could the content of the workbook be streamed, as we are adding more rows, so that we don't keep the entire file in memory ?
I have found in the list of messages this answer https://lists.apache.org/thread.html/ee085ea108af4f0db4b7b5c90a3558a4974f96ee908c7d254155ecd6%40%3Cuser.poi.apache.org%3E
Is it still up to date? Is this the way for doing the streaming or meanwhile something has changed and we could do it directly from the memory?

Code for current solution

@RequestMapping(path = "/download/xls", method = RequestMethod.GET, produces = org.springframework.http.MediaType.APPLICATION_OCTET_STREAM_VALUE)
public ResponseEntity<InputStreamResource> downloadXls(HttpServletResponse response, XlsRequest request) throws FileNotFoundException, InternalServerErrorException {

byte[] data = downloadIssuesAsExcel(response, request);

HttpHeaders headers = new HttpHeaders();
headers.add("Content-Description", "File Transfer");
headers.add("Content-Disposition", "attachment; filename=justAFile.xlsx");
headers.add("Content-Transfer-Encoding", "binary");
headers.add("Connection", "Keep-Alive");
headers.setContentType(
org.springframework.http.MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
InputStreamResource isr = new InputStreamResource(new ByteArrayInputStream(data));
return ResponseEntity.ok().contentLength(data.length).headers(headers).body(isr);
}

public byte[] downloadIssuesAsExcel(HttpServletResponse response, XlsRequest request)
throws InternalServerErrorException {
try {
SXSSFWorkbook workbook = createExcel(request, response);
ByteArrayOutputStream stream = new ByteArrayOutputStream();
workbook.write(stream);
workbook.dispose();
workbook.close();
stream.close();
return stream.toByteArray();
} catch (Exception e) {
throw new InternalServerErrorException("IO exception while downloading XLS file", e);
}
}

Also tried to write the workbook content directly in the response.getOutputStream() but the file gets corrupted somehow.

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

response.setHeader("Content-Description", "File Transfer");
response.setHeader("Content-Disposition", "attachment; filename=" + issueDataService.getExcelName(request));
response.setHeader("Content-Transfer-Encoding", "binary");
response.setHeader("Connection", "Keep-Alive");

SXSSFWorkbook workbook = createExcel(request, response);
workbook.write(response.getOutputStream());
workbook.dispose();
workbook.close();

Thank you!

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Streaming a POI SXSSFWorkbook to the servlet output stream

[Jörn Franke <jo...@gmail.com>]

I think you can’t due to the Excel format. SXSSF creates temporary files and does not need to keep everything in memory. I recommend though to activate compression for those as they are heavy uncompressed 

> Am 13.01.2020 um 11:19 schrieb Ramona Petricu <ra...@metrosystems.net>:
> 
> Hi,
> 
> We are building a Spring boot REST endpoint that generates a large XLS file (may contain ~ 1mil lines) and provides it for download.
> The current solution uses the SXSSF API of Apache POI library for creating the workbook;
> after that we write the workbook to an output stream, collect the stream in to an array of bytes and then provide this one for download.
> 
> How could the content of the workbook be streamed, as we are adding more rows, so that we don't keep the entire file in memory ?
> I have found in the list of messages this answer https://lists.apache.org/thread.html/ee085ea108af4f0db4b7b5c90a3558a4974f96ee908c7d254155ecd6%40%3Cuser.poi.apache.org%3E
> Is it still up to date? Is this the way for doing the streaming or meanwhile something has changed and we could do it directly from the memory?
> 
> Code for current solution
> 
> @RequestMapping(path = "/download/xls", method = RequestMethod.GET, produces = org.springframework.http.MediaType.APPLICATION_OCTET_STREAM_VALUE)
> public ResponseEntity<InputStreamResource> downloadXls(HttpServletResponse response, XlsRequest request) throws FileNotFoundException, InternalServerErrorException {
> 
> byte[] data = downloadIssuesAsExcel(response, request);
> 
> HttpHeaders headers = new HttpHeaders();
> headers.add("Content-Description", "File Transfer");
> headers.add("Content-Disposition", "attachment; filename=justAFile.xlsx");
> headers.add("Content-Transfer-Encoding", "binary");
> headers.add("Connection", "Keep-Alive");
> headers.setContentType(
> org.springframework.http.MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
> InputStreamResource isr = new InputStreamResource(new ByteArrayInputStream(data));
> return ResponseEntity.ok().contentLength(data.length).headers(headers).body(isr);
> }
> 
> public byte[] downloadIssuesAsExcel(HttpServletResponse response, XlsRequest request)
> throws InternalServerErrorException {
> try {
> SXSSFWorkbook workbook = createExcel(request, response);
> ByteArrayOutputStream stream = new ByteArrayOutputStream();
> workbook.write(stream);
> workbook.dispose();
> workbook.close();
> stream.close();
> return stream.toByteArray();
> } catch (Exception e) {
> throw new InternalServerErrorException("IO exception while downloading XLS file", e);
> }
> }
> 
> Also tried to write the workbook content directly in the response.getOutputStream() but the file gets corrupted somehow.
> 
> response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
> 
> response.setHeader("Content-Description", "File Transfer");
> response.setHeader("Content-Disposition", "attachment; filename=" + issueDataService.getExcelName(request));
> response.setHeader("Content-Transfer-Encoding", "binary");
> response.setHeader("Connection", "Keep-Alive");
> 
> SXSSFWorkbook workbook = createExcel(request, response);
> workbook.write(response.getOutputStream());
> workbook.dispose();
> workbook.close();
> 
> Thank you!
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org