Java Check workbook contains a specific spreadsheet or not using Apache POI

Question

I'm using apache POI to create a excel file which contains multiple spreadsheet. Firstly, this method should check the workbook is empty or not after check its existence. If empty, create new sheet name "sheet1"; if not, check any sheets named "sheet1" in this workbook. If yes, adding new rows to the sheet; if not, create a sheet name "sheet1". Below is my current code, which I think is not the right method to achieve my goal:

    File file = new File("Main Data.xlsx");
    // Retrieve the workbook for the main report
    XSSFWorkbook workbook;
    // Check file existence 
    if (file.exists() == false) {
        // Create new file if it does not exist
        workbook = new XSSFWorkbook();
    } else {
        try ( 
            // Make current input to exist file
            InputStream is = new FileInputStream(file)) {
                workbook = new XSSFWorkbook(is);
            }
    }
    XSSFSheet spreadsheet;
    // Check if the workbook is empty or not
    if (workbook.getNumberOfSheets() != 0) {
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
           if (workbook.getSheetName(i).equals(" Enc " + deviceLabel.getText() + " Info. ")) {
                spreadsheet = workbook.getSheet(" Enc " + deviceLabel.getText() + " Info. ");
            } else spreadsheet = workbook.createSheet(" Enc " + deviceLabel.getText() + " Info. ");
        }
    }
    else {
        // Create new sheet to the workbook if empty
        spreadsheet = workbook.createSheet(" Enc " + deviceLabel.getText() + " Info. ");
    }

    // Error here (variable spreadsheet may not have been initialized
    spreadsheet.shiftRows(0, spreadsheet.getLastRowNum(), 23);

Tow problems I am getting.

The for loop is obviously wrong to achieve my goal. Is there any way I can check empty spreadsheet or named spreadsheet exists already in the same time in one if statement?

e.g.

  if ((workbook.getNumberOfSheets() == 0) && !workbook.getSheet("sheet1").exists()) {
    workbook.createSheet("sheet1");
  } else workbook.getSheet("sheet1")

I keep getting error that said spreadsheet is not initialized, but if I delete the for loop, the error will disappear.

"The for loop is obviously wrong to achieve my goal." - It may be obvious to you, but it isn't obvious to us. — Joe C
"I keep getting error that said spreadsheet is not initialized" - is that the exact error message? If so, on which line? — Joe C
@JoeC the for loop here may create multiple sheet if none of the exist sheet named "sheet1", which will be an error to create a sheet in a workbook. And, I also comment the line has error which is the last line of my code. — Minwu Yu

Minwu Yu Minwu Yu · Accepted Answer · 2017-04-26T23:12:29

I figured my problem with following code:

    File file = new File("Main Data.xlsx");
    // Retrieve the workbook for the main report
    XSSFWorkbook workbook;
    // Check file existence 
    if (file.exists() == false) {
        // Create new file if it does not exist
        workbook = new XSSFWorkbook();
    } else {
        try ( 
            // Make current input to exist file
            InputStream is = new FileInputStream(file)) {
                workbook = new XSSFWorkbook(is);
            }
    }
    XSSFSheet spreadsheet = workbook.getSheet("sheet1");
    // Check if the workbook is empty or not
    if (workbook.getNumberOfSheets() != 0) {
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
           if (workbook.getSheetName(i).equals("sheet1")) {
                spreadsheet = workbook.getSheet("sheet1");
            } else spreadsheet = workbook.createSheet("sheet1");
        }
    }
    else {
        // Create new sheet to the workbook if empty
        spreadsheet = workbook.createSheet("sheet1");
    }

    // Shift rows down if exist
    spreadsheet.shiftRows(0, spreadsheet.getLastRowNum(), 23);

Java Check workbook contains a specific spreadsheet or not using Apache POI

4 Answers