SXSSFWorkbook Cannot write data, document seems to have been closed already

Question

I'm getting an exception while writing data into an SXSSFWorkbook file. I could do the same using XSSFWorkbook and it worked just fine.

java.io.IOException: Cannot write data, document seems to have been closed already
at org.apache.poi.ooxml.POIXMLDocument.write(POIXMLDocument.java:230)
at org.apache.poi.xssf.streaming.SXSSFWorkbook.write(SXSSFWorkbook.java:953)
at org.glassfish.jersey.message.internal.StreamingOutputProvider.writeTo(StreamingOutputProvider.java:79)
at org.glassfish.jersey.message.internal.StreamingOutputProvider.writeTo(StreamingOutputProvider.java:61)
at org.glassfish.jersey.message.internal.WriterInterceptorExecutor$TerminalWriterInterceptor.invokeWriteTo(WriterInterceptorExecutor.java:266)

Here is my code.

public static Response createResponseUsingStreaming() throws IOException {
    SXSSFWorkbook workbook = report.generateStreamingExcelReport(100);
    StreamingOutput outputStream = workbook::write;
    final String contentType = "application/vnd.ms-excel";

    Response.ResponseBuilder responseBuilder = Response.ok(outputStream);
    responseBuilder.header("Content-Disposition", "attachment; filename=test");
    responseBuilder.header("Access-Control-Expose-Headers", "Content-Disposition");
    responseBuilder.header("Content-Type", contentType);
    Response response = responseBuilder.build();
    if (null != workbook) {
        workbook.dispose();
        workbook.close();
    }
    return response;
}

public SXSSFWorkbook generateStreamingExcelReport(int rowAccessWindowSize) {
    List<List<String>> rows = createRawData();
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(rowAccessWindowSize);

    createExcelSummaryPage(sxssfWorkbook);
    createExcelDetailPage(rows, sxssfWorkbook);

    return sxssfWorkbook;
}

OPCPackage is set when creating a new SXSSFWorkbook, wondering where this is getting set to null.

public final void write(OutputStream stream) throws IOException {
    OPCPackage p = getPackage();
    if(p == null) {
        throw new IOException("Cannot write data, document seems to have been closed already");
    }

    //force all children to commit their changes into the underlying OOXML Package
    // TODO Shouldn't they be committing to the new one instead?
    Set<PackagePart> context = new HashSet<>();
    onSave(context);
    context.clear();

    //save extended and custom properties
    getProperties().commit();

    p.save(stream);
}

Answer 1

If you can work with XSSFWorkbook you should be able to work with SXSSFWorkbook using your XSSFWorkbook.

Here is a simple working example:

public void createExcelFileUsingSXSSFWorkbook(XSSFWorkbook wb) {

    SXSSFWorkbook wbSXSSF = new SXSSFWorkbook(wb);

    try {
        Path path = Files.createFile(Path.of("test.xlsx"));
        try (OutputStream outputStream = Files.newOutputStream(path)) {
            wbSXSSF.write(outputStream);
            wbSXSSF.close();
        } catch (IOException e2) {
            e2.printStackTrace();
        }
    } catch (IOException e1) {
        e1.printStackTrace();
    }

}

Answer 2

I was able to resolve this as below

outputStream = outputStream1 -> {
            sxssfWorkbook.write(outputStream1);
            sxssfWorkbook.dispose();
            sxssfWorkbook.close();
        };

The workbook was being written into on responseBuilder.build() so I had to bake write(), dispose(), and close() into the outputstream for this to work