在ServletOutputStream中写入大型HSSFWorkbook

Question

我有下面的代码。当我尝试将记录列表导出到具有少量记录(大约200条记录)的excel时，这种方法工作得很好：

  @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);
    }

当列表变得更大(ex的记录超过200条)时，悲剧就开始了。它会给出以下错误：

 <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

我搜索了很多，最终得到了这样的解决方案：

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

冲洗小溪是大多数人所指的唯一方法。现在，这种解决方案不仅适用于大数据，也适用于较小的数据(不适用于这两种情况)。它会给出这个错误：

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 

我尝试了XSSFWorkbook而不是HSSFWorkbook。但weblogic甚至没有构建该项目，并抛出了一个与meta-model相关的错误。

不管怎样，这段代码出了什么问题。如何将较大的文件写入流中？我的apache poi版本是3.11。

Answer 1

正如Paul Samsotha评论的S.O链接所说，我应该使用StreamingOutput而不是ServletOutputStream。因此，该方法应该重写为：

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