Delete multiple sheets from workbook

Question

I have an workbook which has multiple sheets within it. What I need is to process only the first sheet and dont need those remaining sheets. one can remove a sheet at particular index using,

workbook.removeSheetAt(sheetIndex);

Is there simpler any method to remove all sheets except one sheet. Or do I have to loop through the above method to remove sheet everytime.

Answer 1

I would just move the one sheet to keep to a new workbook, then close without saving the old workbook and then save the new workbook with the original name so overwriting the old one.

No need to find out how many sheets exist or much else.

You might find something useful in this macro: https://stackoverflow.com/a/30605765/4961700

Years ago, we had a delete macro to remove hundreds then a few thousand files from the filesystem directory structure ready for the monthly update. Then we realised that it was much faster to just delete the filesystem at the upper level and re-create the directory structure, the time that saved...

Answer 2

You will have to loop over all the sheets you want to delete. That said, it isn't too much code :

File f = new File("path-to-your-workbook");
        
try (FileInputStream file = new FileInputStream(f)) {

    XSSFWorkbook workbook = new XSSFWorkbook(file);
            
    int totalSheets = workbook.getNumberOfSheets();
            
    for (int i = 1; i < totalSheets; i++ ) {
        workbook.removeSheetAt(1);
    }
            
    try (FileOutputStream output = new FileOutputStream(f)) {
        workbook.write(output);
    }
 }