write large HSSFWorkbook into ServletOutputStream

Question

i have the below code. this works fine when i try to export a list of records to excel with small number of records (around 200 records):

  @Path("/toExcel")
  @GET
    public Response excelCustomersReport(@QueryParam("page") Integer page, @QueryParam("start") Integer start, @QueryParam("limit") Integer limit, @QueryParam("filter") FilterWrapper filter, @QueryParam("sort") SortWrapper sort) throws Exception {
        response.setContentType("application/octet-stream");
        DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
        String currentDateTime = dateFormatter.format(new Date());
        String headerKey = "Content-Disposition";
        String headerValue = "attachment; filename=report_" + currentDateTime + ".xlsx";
        response.setHeader(headerKey, headerValue);
        HSSFWorkbook workbook=userService.export(page,start,limit,filter,sort);
        ServletOutputStream outputStream=response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();

        return ResponseHelper.ok(response);
    }

the tragedy begins when the list size becomes larger (more than 200 records for ex). it gives the following error:

 <An I/O error has occurred while writing a response message entity to the container output stream.
org.glassfish.jersey.server.internal.process.MappableException: java.io.IOException: CLOSED

i searched a lot and ended up with this solution:

 public Response excelCustomersReport(@QueryParam("page") Integer page, @QueryParam("start") Integer start, @QueryParam("limit") Integer limit, @QueryParam("filter") FilterWrapper filter, @QueryParam("sort") SortWrapper sort) throws Exception {
        response.setContentType("application/octet-stream");
        DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
        String currentDateTime = dateFormatter.format(new Date());
        String headerKey = "Content-Disposition";
        String headerValue = "attachment; filename=report_" + currentDateTime + ".xlsx";
        response.setHeader(headerKey, headerValue);
        HSSFWorkbook workbook=userService.export(page,start,limit,filter,sort);
        response.setHeader("Content-Length", String.valueOf(workbook.getBytes().length));
        InputStream is=new ByteArrayInputStream(workbook.getBytes());
        ServletOutputStream sos = response.getOutputStream();
        long byteRead = 0;
        try {
            byte[] buf = new byte[1000];
            while (true) {
                int r = is.read(buf);
                if (r == -1)
                    break;
                sos.write(buf, 0, r);
                byteRead +=r;
                if(byteRead > 1024*1024){ //flushes after 1mb
                    byteRead = 0;
                    sos.flush();
                }
            }
        }catch (Exception e) {
           e.printStackTrace();
        }finally{
            if(sos != null){
                sos.flush();
            }
            try{is.close();}catch(Exception e){}
            try{sos.close();}catch(Exception e){}
        }

        return ResponseHelper.ok(response);
    }

flushing the stream was the only way most people were pointing to. now this solution does not work not only for large data but also for smaller ones(not working for both cases). it gives this error:

An I/O error has occurred while writing a response message entity to the container output stream.
org.glassfish.jersey.server.internal.process.MappableException: com.fasterxml.jackson.databind.JsonMappingException: Direct self-reference leading to cycle 

i tried XSSFWorkbook instead of HSSFWorkbook . but weblogic did not even build the project and threw an error relating to meta-model.

anyway, what's wrong with this code. how can i write larger files into a stream? my apache poi version is 3.11.

Answer 1

as S.O link commented by Paul Samsotha says, i should have used StreamingOutput instead of ServletOutputStream. so the method should be rewritten as so:

    @Path("/toExcel")
    @GET
    public Response excelCustomersReport(@QueryParam("page") Integer page, @QueryParam("start") Integer start, @QueryParam("limit") Integer limit, @QueryParam("filter") FilterWrapper filter, @QueryParam("sort") SortWrapper sort) throws Exception {
        response.setContentType("application/octet-stream");
        DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
        String currentDateTime = dateFormatter.format(new Date());
        String headerKey = "Content-Disposition";
        String headerValue = "attachment; filename=report_" + currentDateTime + ".xlsx";
        response.setHeader(headerKey, headerValue);
        HSSFWorkbook workbook=userService.export(page,start,limit,filter,sort);
        StreamingOutput output = new StreamingOutput() {
            @Override
            public void write(OutputStream out)
                    throws IOException, WebApplicationException {
                workbook.write(out);
                out.flush();
            }
        };
        return Response.ok(output)
                .build();
    }