CODEWITHSUNDEEP
javaexcelapache-poi
Minwu Yu
Minwu Yu

Reputation: 311

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

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.

  1. 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")
  1. I keep getting error that said spreadsheet is not initialized, but if I delete the for loop, the error will disappear.

Upvotes: 2

Views: 24249

Answers (4)

mailtime
mailtime

Reputation: 151

your code is wrong a priori. You probably add pluses yourself for your answers?

public XSSFSheet factorySheet(final String name) {
         return Optional.ofNullable((XSSFSheet) workbook.getSheet(name))
                .orElseGet(()->(XSSFSheet) workbook.createSheet(name));
    }

Upvotes: 0

Divyaja Kakarla
Divyaja Kakarla

Reputation: 51

You can simply check if a sheet is created or not with 

workbook.getSheetIndex(sheetName)

returns -1 if the sheet is not in the work book or Index if it is present in the workbook.

Upvotes: 5

Minwu Yu
Minwu Yu

Reputation: 311

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);

Upvotes: 6

TuyenNTA
TuyenNTA

Reputation: 1204

You have declared workbook as final, it can not be initial for work!

Upvotes: 0

Related Questions