CODEWITHSUNDEEP
javaapache-poi
Momo
Momo

Reputation: 2491

Delete sheet from WorkBook in POI

I have a WorkBook and I'm trying to delete sheets which names don't match with a specific String.

Here is my code

XSSFWorkbook book = new XSSFWorkbook(new FileInputStream(excelName));                   
            for(int i=0;i<book.getNumberOfSheets();i++){
                System.out.println(book.getSheetAt(i).getSheetName());
                if(!book.getSheetAt(i).getSheetName().equals(sheetName)){
                    book.removeSheetAt(i);
                }
            }

The code runs well, but it doesn't perform the desired task

EDIT

The solution as said is to reverse the loop order. Here is a cleaner code

private void removeOtherSheets(String sheetName, XSSFWorkbook book) {       
            for(int i=book.getNumberOfSheets()-1;i>=0;i--){
                XSSFSheet tmpSheet =book.getSheetAt(i);
                if(!tmpSheet.getSheetName().equals(sheetName)){
                    book.removeSheetAt(i);
                }
            }       
    }

Upvotes: 5

Views: 16130

Answers (2)

S Krishna
S Krishna

Reputation: 1333

Delete a sheet using Apache POI

//Open file
  String filePath = "C:\\filePath\\excelFileName.xlsx";
  String sheetName = "NameOfSheetToBeDeleted";
  FileInputStream inputStream = new FileInputStream(new File(filePath));
  XSSFWorkbook workBook = new XSSFWorkbook(inputStream);

//DELETE SHEET
  workBook.removeSheetAt(resultWorkbook.getSheetIndex(sheetName));

//Save the file
  FileOutputStream outFile =new FileOutputStream(new File(filePath));
  workBook.write(filePath);
  outFile.close();

Upvotes: -1

Gagravarr
Gagravarr

Reputation: 48326

You should delete in reverse order, not forward order. Otherwise, you remove a sheet from the list and change the state of it for future loops.

Thing about the case of sheets A, B, C and D. You iterate to i=1, and get B. You delete B. If you don't re-fetch the row at 1, and move on to i=2, you'll be on D and will have skipped C! Iterating through in reverse avoids this problem.

Upvotes: 8

Related Questions