CODEWITHSUNDEEP
javaexcelapache-poi
Ashley
Ashley

Reputation: 491

Creating an excel workbook with multiple sheets using SXSSFWorkbook

I have to create an excel workbook with two sheets. I use the following code to create the excel and later it gets downloaded. However, the first sheet is created but the second sheet is not being created. I'm not able to understand the exact reason. The following is the part through which I create the two sheets. 

import java.io.OutputStream;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
private static final String SECOND_SHEET_NAME = "Sheet 2";

private static final String FIRST_SHEET_NAME = "Sheet 1"

protected Sheet firstSheet;
protected Sheet secondSheet;
protected Workbook outWorkbook;

//creating workbook
outWorkbook = new SXSSFWorkbook(1);
//create first sheet
firstSheet = outWorkbook.createSheet(FIRST_SHEET_NAME);

//create second sheet
secondSheet = outWorkbook.createSheet(SECOND_SHEET_NAME);
//setting the second sheet as unhidden

outWorkbook.setSheetHidden(outWorkbook.getSheetIndex(SECOND_SHEET_NAME), false);
try
{
   outWorkbook.write(outputStream);
}
catch(IOException)
{
    String errorMsg = "Failed to write to workbook";
    log.error(errorMsg, e);
}

Upvotes: 2

Views: 9069

Answers (1)

Justin Daschner
Justin Daschner

Reputation: 11

The issue is that you are not exporting the sheet correctly here.

outWorkbook.setSheetHidden(outWorkbook.getSheetIndex(SECOND_SHEET_NAME), 
false);
try
{
   outWorkbook.write(outputStream);
}
catch(IOException)
{
    String errorMsg = "Failed to write to workbook";
    log.error(errorMsg, e);
}

If dispose is not called before all the sheets are created then the sheets should write. Here is an example that I managed to come up with for reference.

static String outPath = "path";
static String fileName = "test.xlsx";

public static void main(String[] args) throws Throwable {

    ApacheExample1 a = new ApacheExample1();

    a.runExport("test");

}

private void runExport(String tag) throws IOException {
    SXSSFWorkbook wb = new SXSSFWorkbook();
    Sheet sh1 = wb.createSheet("Sheet" + 1);
    Sheet sh2 = wb.createSheet("Sheet" + 2);
    String[] v1 = {"test", "one"};
    String[] v2 = {"test", "two"};
    writeSXSSLRow(0, 0, sh1, v1);
    writeSXSSLRow(0, 0, sh2, v2);

    writeToFile(wb);

}

private static void writeToFile(SXSSFWorkbook wb) throws IOException {
    File f = new File(outPath);
    if (!f.exists()) {
        f.createNewFile();
    }
    FileOutputStream out = new FileOutputStream(outPath + fileName);
    wb.write(out);
    out.close();

    //After everything is written, then we dispose the temp file.
    wb.dispose();
}

private static void writeSXSSLRow(int colStart, int cellRow, Sheet ws, String[] v) throws IOException {
    Row row = ws.createRow(cellRow);
    for (int col = colStart; col < colStart + v.length; col++) {
        Cell cell = row.createCell(col);
        cell.setCellValue(v[col - colStart]);
    }

}

Upvotes: 1

Related Questions