0

I have to delete some sheets from an Excel File.

I have used the below code to delete the unnecessary sheets from the Excel file.

public static boolean deleteUnnecessarySheets(Workbook workbook,
        boolean authorize) 
{
    if(workbook != null)
    {
        int noOfSheets = workbook.getNumberOfSheets();
        for( int sheetNo = noOfSheets - 1; sheetNo >= 0; sheetNo-- )
        {
            Sheet sheet = workbook.getSheetAt(sheetNo);
            String sheetName = sheet.getSheetName();
            if(sheetName.equalsIgnoreCase("User Defined Actions") || sheetName.equalsIgnoreCase("AUTHORIZE"))
            {
                if(!authorize && sheetName.equalsIgnoreCase("AUTHORIZE"))
                {
                    workbook.removeSheetAt(sheetNo);
                }
            }
            else
            {
                workbook.removeSheetAt(sheetNo);
            }
        }
        return true;
    }
    return false;
}

The code successfully deletes the sheets as expected. But on opening the Excel sheet manually I get the following error.

enter image description here

enter image description here

How can I prevent this from happening? Or what am I doing wrong?

EDIT: On clicking 'Click for more details' enter image description here

Sai Manoj Kadiyala
  • 151
  • 1
  • 12
  • There might be cases where your code delete ALL the sheets. This might be an issue – Bentaye May 29 '18 at 13:24
  • I know for sure that there will be at least one sheet remaining. I have even tried workbook.setActiveSheet(0); after deleting every sheet – Sai Manoj Kadiyala May 29 '18 at 13:26
  • 1
    What does it say when you click on `Click for more details.` ? – Bentaye May 29 '18 at 13:27
  • Another reason could be if you have macros or formulas in the remaining sheet that reference cells in some of the deleted sheets? – Bentaye May 29 '18 at 13:33
  • 1
    What `apache poi` version is used here? – Axel Richter May 29 '18 at 13:46
  • @AxelRichter apache poi 3.9 version – Sai Manoj Kadiyala May 30 '18 at 04:03
  • Then this is the fault. Version 3.9 is more than 7 years old now and ignoring 7 years of development leads to such problems. As you stated you have tried setting a existing sheet to be the active one after you probably have deleted the sheet which was the active one before. But there also were made changings on related method `private void onSheetDelete` since version 3.9. – Axel Richter May 30 '18 at 07:32

0 Answers0