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.

Solar Mike · Accepted Answer · 2020-07-17T09:46:45.253

2

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...

edited Jul 17 '20 at 09:46

answered Jul 17 '20 at 09:28

Solar Mike

7,156
4
17
32

And how would you add an existing sheet to a workbook? – pirho Jul 17 '20 at 09:30
@pirho when you open a new file it has 1 or several sheets in it already. `See what I added to my answer. – Solar Mike Jul 17 '20 at 09:36
But with Apache POI & Java? – pirho Jul 17 '20 at 09:38
@pirho you tagged excel so that was where my answer comes from. If you tag excel expect answers... – Solar Mike Jul 17 '20 at 09:39
I am not the OP. But I guess that OP tagged apache poi for a reason. – pirho Jul 17 '20 at 09:41
@pirho down to using tags carefully. and sorry for thinking you were the op. I still think my approach of keeping the one sheet and just deleting the rest is more efficient. – Solar Mike Jul 17 '20 at 09:42
Yes it would be and I was thinking exactly the same. But the issue is how to do it in POI. – pirho Jul 17 '20 at 09:46
@SolarMike thank you for your suggestion. But I need the same with Apache POI in Java . And sorry if I tagged wrong. – Hunt Jul 17 '20 at 11:07
1

I'm not understanding the comment confusion. Creating a new workbook can be done in poi, so the suggested workflow is perfectly do-able. – Roddy of the Frozen Peas Jul 17 '20 at 11:36
@RoddyoftheFrozenPeas There is no confusion nor solution for POI yet. The problem is how to add the existing sheet to a new workbook in POI. So in theory it works but in practise? – pirho Jul 20 '20 at 11:29
1

... In practice it's identical to copying a sheet from one workbook to another (you just need to create the second workbook obviously.) https://stackoverflow.com/questions/848212/copying-excel-worksheets-in-poi – Roddy of the Frozen Peas Jul 20 '20 at 11:32

Tom Mac · Answer 2 · 2020-07-20T12:52:11.480

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

Delete multiple sheets from workbook

2 Answers2