How can i write in the same excelbook but different sheet each time i run this in a loop?XSSF Apache POI

Question

When i use below method this will print the arraylist in the excelsheet. When i call this method in a loop, second iteration prints in the same sheet. I want to print next iteration in different sheet, possibly create a new sheet and write there. What changes do i have to make to make it print the iteration in different sheet of same excel workbook.

for (int indexSelect=1;indexSelect>=10; indexSelect++){
excelWrite(Prints, indexSelect)
}


public static void excelWrite(ArrayList<Object> Prints, int indexSelect) {
        XSSFWorkbook workbook = new XSSFWorkbook();

        XSSFSheet sheet = workbook.createSheet("Achievers"+indexSelect);
        System.out.println("Getting sheet at ..:" +indexSelect);
        //Sheet sheet = workbook.getSheetAt(indexSelect);
        int rowCount = 0;

        //  for (Object Names : Prints) {
        //      org.apache.poi.ss.usermodel.Row row = sheet.createRow(++rowCount);
        //org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowCount);
        int columnCount = indexSelect;

        for (Object field :Prints) {
            org.apache.poi.ss.usermodel.Row row = sheet.createRow(++rowCount);
            Cell cell = row.createCell(columnCount);
            if (field instanceof String) {
                cell.setCellValue((String) field);
            } else if (field instanceof Integer) {
                cell.setCellValue((Integer) field);
            }
        }

        //  }
        System.out.println("Current indexSelect is: "+indexSelect);

        try {
            FileOutputStream outputStream = new FileOutputStream("C:\\Softwares\\DataSource\\AchieversDataCopy.xlsx") ;

            workbook.write(outputStream);
            workbook.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


    }

}

On a quick look, it appears as if you're overriding the same xlsx file by creating a new workbook everytime and thus only the last iteration persists. What's the title of the sheet after the program completes ? — coding_idiot, Aug 10 '17 at 21:33
https://stackoverflow.com/a/15148013/566092 - How to add a new sheet to an existing workbook — coding_idiot, Aug 10 '17 at 21:35
@coding_idiot the last sheet is Achievers10 the last iterator of for loop iteration;yes it is overwriting. — PJAutomator, Aug 10 '17 at 22:56
Possible duplicate of [How to add new sheets to existing excel workbook using apache POI?](https://stackoverflow.com/questions/12459181/how-to-add-new-sheets-to-existing-excel-workbook-using-apache-poi) — coding_idiot, Aug 11 '17 at 18:47

score 0 · Answer 1 · answered Aug 11 '17 at 12:18

Just take the create and save of the workbook out of the ExcelWrite method:

XSSFWorkbook workbook = new XSSFWorkbook();

for (int indexSelect=1;indexSelect>=10; indexSelect++){
    excelWrite(workbook, Prints, indexSelect)
}
try {
    FileOutputStream outputStream = new FileOutputStream("C:\\Softwares\\DataSource\\AchieversDataCopy.xlsx") ;
    workbook.write(outputStream);
    workbook.close();
} catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}


public static void excelWrite(XSSFWorkbook workbook, ArrayList<Object> Prints, int indexSelect) {

    XSSFSheet sheet = workbook.createSheet("Achievers"+indexSelect);
    System.out.println("Getting sheet at ..:" +indexSelect);
    int rowCount = 0;

    int columnCount = indexSelect;

    for (Object field :Prints) {
        Row row = sheet.createRow(++rowCount);
        Cell cell = row.createCell(columnCount);
        if (field instanceof String) {
            cell.setCellValue((String) field);
        } else if (field instanceof Integer) {
            cell.setCellValue((Integer) field);
        }
    }

    System.out.println("Current indexSelect is: "+indexSelect);
}

How can i write in the same excelbook but different sheet each time i run this in a loop?XSSF Apache POI

1 Answers1