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

Issue #748 resolved
Jim Pisano
created an issue

Python 3.4.4 / Excel 2016 / openpyxl 2.4.1

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.

The above code generates the following error

C:\Python34\python.exe C:/Users/jpisano/PycharmProjects/junk/test.py
Traceback (most recent call last):
  File "C:/Users/jpisano/PycharmProjects/junk/test.py", line 18, in <module>
    wb.save("c:/Users/jpisano/book2.xlsx")
  File "C:\Python34\lib\site-packages\openpyxl\workbook\workbook.py", line 339, in save
    save_workbook(self, filename)
  File "C:\Python34\lib\site-packages\openpyxl\writer\excel.py", line 268, in save_workbook
    writer.save(filename)
  File "C:\Python34\lib\site-packages\openpyxl\writer\excel.py", line 250, in save
    self.write_data()
  File "C:\Python34\lib\site-packages\openpyxl\writer\excel.py", line 93, in write_data
    archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
  File "C:\Python34\lib\site-packages\openpyxl\writer\workbook.py", line 88, in write_workbook
    active = get_active_sheet(wb)
  File "C:\Python34\lib\site-packages\openpyxl\writer\workbook.py", line 60, in get_active_sheet
    sheet = wb.active
  File "C:\Python34\lib\site-packages\openpyxl\workbook\workbook.py", line 130, in active
    return self._sheets[self._active_sheet_index]
IndexError: list index out of range

Process finished with exit code 1

Comments (7)

  1. Charlie Clark

    The problem with this is that we use a numerical index for the active sheet and this doesn't get updated when the sheet is removed. As a workaround, until we release a fix for this, you can manually reset the the index: wb.active = 1. This will allow you to save the file.

  2. David C

    the following is the error code:

    C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-packages\ope
    npyxl-2.4.1-py3.5.egg\openpyxl\reader\worksheet.py:307: UserWarning: Unknown ext
    ension is not supported and will be removed
      warn(msg)
    Traceback (most recent call last):
      File "Extract_test.py", line 97, in <module>
        wb2.save(database + '_tlm_test.xlsx')
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\workbook\workbook.py", line 339, in save
        save_workbook(self, filename)
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\writer\excel.py", line 268, in save_workb
    ook
        writer.save(filename)
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\writer\excel.py", line 250, in save
        self.write_data()
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\writer\excel.py", line 81, in write_data
        self._write_worksheets()
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\writer\excel.py", line 199, in _write_wor
    ksheets
        xml = ws._write()
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\worksheet\worksheet.py", line 866, in _wr
    ite
        return write_worksheet(self)
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\writer\worksheet.py", line 100, in write_
    worksheet
        cols = ws.column_dimensions.to_tree()
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\worksheet\dimensions.py", line 230, in to
    _tree
        obj = col.to_tree()
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\worksheet\dimensions.py", line 182, in to
    _tree
        attrs = dict(self)
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\worksheet\dimensions.py", line 51, in __i
    ter__
        value = getattr(self, key, None)
      File "C:\Users\dco\AppData\Local\Programs\Python\Python35-32\lib\site-pack
    ages\openpyxl-2.4.1-py3.5.egg\openpyxl\styles\styleable.py", line 91, in __get__
    
        return coll.names[idx]
    IndexError: list index out of range
    

    I can't provide the file due to content, but I am just trying to delete a sheet as the OP was

  3. Log in to comment