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

Question

I am trying to use openpyxl to:

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

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.

If you think there is a bug then you should submit a bug report. — Charlie Clark
– Charlie Clark, Commented Dec 18, 2016 at 10:00
I am new to openpyxlI am happy to submit a bug report but just wanted to make sure my code is valid usage of openpyxl. — jim99
– jim99, Commented Dec 18, 2016 at 11:51
Code looks okay, though del wb['Sheet2'] is an easier way to work. You'll need to supply a file. — Charlie Clark
– Charlie Clark, Commented Dec 18, 2016 at 12:16

Fejs · Accepted Answer · 2016-12-19 14:20:53Z

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

edited Dec 19, 2016 at 14:20

answered Dec 19, 2016 at 13:49

Fejs

2,8983 gold badges27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related