How to use StreamingResponseBody and try-with-resources?

I'd like to create an Excel sheet dynamically and return that to the client. I'd like to use the StreamingResponseBody as the return type in order to save some RAM. I'm also using try-with-resources to close the workbook automatically. Here is the code.

@RestController
public class ExcelController {

  @GetMapping("/hello")
  public ResponseEntity<StreamingResponseBody> excel() throws IOException {
    try (var workbook = new XSSFWorkbook();) {
      workbook.createSheet("hello world");
      return ResponseEntity.ok()
          .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=data.xlsx")
          .body(workbook::write);
    }
  }
}

Unfortunately this does not work and I'm getting the following error message.

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception

java.io.IOException: Cannot write data, document seems to have been closed already
        at org.apache.poi.ooxml.POIXMLDocument.write(POIXMLDocument.java:215) ~[poi-ooxml-5.1.0.jar:5.1.0]
        at org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBodyReturnValueHandler$StreamingResponseBodyTask.call(StreamingResponseBodyReturnValueHandler.java:111) ~[spring-webmvc-5.3.13.jar:5.3.13]
        at org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBodyReturnValueHandler$StreamingResponseBodyTask.call(StreamingResponseBodyReturnValueHandler.java:98) ~[spring-webmvc-5.3.13.jar:5.3.13]
        at org.springframework.web.context.request.async.WebAsyncManager.lambda$startCallableProcessing$4(WebAsyncManager.java:337) ~[spring-web-5.3.13.jar:5.3.13]
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) ~[na:na]
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[na:na]
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[na:na]
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[na:na]
        at java.base/java.lang.Thread.run(Thread.java:833) ~[na:na]

I already tried using my custom TaskExecutor as described by StreamingResponseBody but that didn't really help. In the end I found a solution that works but I'm wondering why the first solution including try-with-resources and method reference does not work.

@RestController
public class ExcelController {

  @GetMapping("/hello")
  public ResponseEntity<StreamingResponseBody> excel() throws IOException {
    var workbook = new XSSFWorkbook();
    workbook.createSheet("hello world");
    return ResponseEntity.ok()
        .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=data.xlsx")
        .body(
            out -> {
              workbook.write(out);
              workbook.close();
            });
  }
}

I highly appreciate your help! Thank you

Because the StreamingResponseBody works async and as your try-with-resources is outside of that part it might be that the resource is already closed (I would suspect in 99.9% of the cases?). To fix do all that stuff inside the StreamingResponseBody.

return ResponseEntity.ok()
    .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
    .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=data.xlsx")
    .body(
        out -> {
          try (var workbook = new XSSFWorkbook()) {
            workbook.createSheet("hello world");
            workbook.write(out);
          }
      });
}

To make it a bit more readable (and the fact that I don't like multiline lambdas) you can ofcourse also move that code to a method and call that from your lambda.

private void writeWorkbook(OutputStream out) {
  try (var workbook = new XSSFWorkbook()) {
    workbook.createSheet("hello world");
    workbook.write(out);
  }
}

return ResponseEntity.ok()
    .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
    .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=data.xlsx")
    .body( this::writeWorkbook);
}

This will open (and close) the Workbook at the proper time.