Streaming a POI SXSSFWorkbook to the servlet output stream

Question

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 ?

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();

Is this answer helpful https://stackoverflow.com/a/15800625/1776132 ? — Smile, Jan 09 '20 at 09:29
I've used direct write to the servlet output stream, it worked fine. Can you please show the code for `response.getOutputStream()` case? There could be variety of the causes, for example whether the content length is specified? — edwgiz, Jan 09 '20 at 10:59
Hi @Smile, thanks for the idea. If we decide to first save the entire file on disk, this would be the efficient way to go. I'm still hoping to find a way to stream it directly from memory. — chirina, Jan 09 '20 at 17:57
Hi @edwgiz, I actually didn't add the content-length header as I thought it implies having access to the entire file first. I updated my question with the code snippet for `response.getOutputStream()`. Do you spot anything different from your version, something that might tamper the file ? — chirina, Jan 09 '20 at 18:05

score 1 · Accepted Answer · answered Jan 10 '20 at 17:47

I've just used your code as template and created controller that works fine

@RestController
public class XlsxController {

    @RequestMapping(path = "/download/xls", method = RequestMethod.GET, produces = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    public void downloadXls(HttpServletResponse r) throws IOException {
        r.setHeader("Content-Description", "File Transfer");
        r.setHeader("Content-Disposition", "attachment; filename=justAFile.xlsx");
        r.setHeader("Content-Transfer-Encoding", "binary");
        r.setHeader("Connection", "Keep-Alive");

        try (SXSSFWorkbook w = getWorkbook()) {
            w.write(r.getOutputStream());
        }
    }

The workbook with 1 million rows weights over 40 Mb

org.springframework:spring-webmvc:5.2.2.RELEASE
org.apache.poi:poi-ooxml:4.1.1

Yep, this worked. I actually had some problems with the method creating the workbook and they randomly manifested when trying out writing to the output stream. Thanks @edwgiz for confirming the solution! — chirina, Jan 16 '20 at 10:30

Streaming a POI SXSSFWorkbook to the servlet output stream

1 Answers1