XLSX service to write recursive from String array Java POI

Question

I'm trying to implement a recursive function to add each subsequent array to xlsx file path. I have a ResultSet converted to String arrays that needs to be put into this file. I've got:

java.lang.RuntimeException: java.io.IOException: Stream closed

I found this error comes from workbook.write()
How to separate that in recursive?

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;

public class XlsxService {

    private final Path outputFilePath;
    private SXSSFWorkbook workbook;
    private SXSSFSheet sheet;
    private Cell cell;
    private int rowNum = 0;

    public XlsxService(Path outputFilePath) {
        this.outputFilePath = outputFilePath;
    }

    public void generate(String[] resultSet) {
        if(workbook == null) {
            workbook = new SXSSFWorkbook();
            sheet = workbook.createSheet("Sheet");
        }
        writeToFile(resultSet);
        try (OutputStream fos = Files.newOutputStream(outputFilePath)) {
            workbook.write(fos);
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public void writeToFile(String[] params) {       
        Row dataRow = sheet.createRow(rowNum);
        for (int i = 0; i < params.length; i++) {
            dataRow.createCell(i).setCellValue(params[i]);         
        }
        rowNum++;
    }
}

ResultSet examples:

    String[] resultSet1 = ["A1", "B1", "C1"]
    String[] resultSet2 = ["A2", "B2", "C2"]
    ...
    String[] resultSetN = ["AN", "BN", "CN"]

Updated code:

    public void generate(String[] resultSet) {
        addRow(resultSet);
        save();
    }

    public void save() throws IOException {
        try (OutputStream fos = Files.newOutputStream(outputFilePath)) {
            workbook.write(fos);
        }
        workbook.close();
    }

    public void addRow(String[] params) {       
        Row dataRow = sheet.createRow(rowNum);
        for (int i = 0; i < params.length; i++) {
            dataRow.createCell(i).setCellValue(params[i]);         
        }
        rowNum++;
    }

Answer 1

You are writing same workbook each time, it get closed after first write - hence exception on second attempt.

As you intend to write multiple rows a better structure would replace writeToFile by addRow, and replace generate by save:

// Initialise once in constructor:
private final SXSSFWorkbook workbook = new SXSSFWorkbook();
private final SXSSFSheet sheet = workbook.createSheet("Sheet");
...
// save replaces generate(String[])
public void save() throws IOException {
    try (OutputStream fos = Files.newOutputStream(outputFilePath)) {
        workbook.write(fos);
    }
    workbook.close();
}
// addRow replaces writeToFile 
public void addRow(String[] params) {
    Row dataRow = sheet.createRow(rowNum);
    for (int i = 0; i < params.length; i++) {
        dataRow.createCell(i).setCellValue(params[i]);
    }
    rowNum++;
}

Then your calls are simpler :- use addRow as many times as needed, then call save once.

XlsxService app = new XlsxService(Path.of("some.xlsx"));

// Multiple rows to add
app.addRow(new String[] {"A1", "B1", "C1"});
app.addRow(new String[] {"A2", "B2", "C2"});
app.addRow(new String[] {"A3", "B3", "C3"});

// One save:
app.save();

if you are looking for all in one call, set Path in the constructor and pass a collection of rows for whatever type is appropriate:

public void generate(String[][] rows) {
    for (var row : rows)
        addRow(row);
    save();
}
// or
public void generate(List<String[]> rows) {
    for (var row : rows)
        addRow(row);
    save();
}