CODEWITHSUNDEEP
pythonexcelpython-3.xopenpyxl
jim99
jim99

Reputation: 89

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

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.

Upvotes: 1

Views: 2954

Answers (1)

Fejs
Fejs

Reputation: 2888

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

Upvotes: 1

Related Questions