openpyxl: remove_sheet causes IndexError: list index out of range error on saving sheet

Question

I am trying to use openpyxl to:

1. Open an Excel (2016) workbook which contains 3 worksheets (Sheet1,Sheet2,Sheet3)
2. Remove a worksheet (Sheet2)

3. Save the workbook to a different workbook minus Sheet2

   from openpyxl import load_workbook
   wb = load_workbook("c:/Users/me/book1.xlsx")
   ws = wb.get_sheet_by_name('Sheet2')
   wb.remove_sheet(ws)
   wb.save("c:/Users/me/book2.xlsx")

The wb.save will generate an IndexError: list index out of range error and produce a corrupted book2.xlsx file which Excel cannot open.

Answer 1

I run into similar problem, only with xlwt library. Regardless, the cause is the same, You remove the sheet which is set as active sheet. So, to fix this, before saving workbook, set some other sheet as active. In openpyxl, it would be something like this:

from openpyxl import load_workbook
wb = load_workbook("c:/Users/me/book1.xlsx")
ws = wb.get_sheet_by_name('Sheet2')
wb.remove_sheet(ws)
wb._active_sheet_index = 0
wb.save("c:/Users/me/book2.xlsx")

I must mention that this is not very good programming practice, but there is no method to set active sheet, only to get one.

EDIT: Just found out that this repo was moved to bitbucket, and found that it has method for setting active sheet. Just use:

wb.active = 0