list index out of range in Openpyxl related to Style while loading workbook

Question

I'm trying to load the workbook, I can't read this specific file.

input_workbook = openpyxl.load_workbook(input_file)

Error i'm receiving is:

Traceback (most recent call last):
  File "/home/admin/Development/pythonProject/venv/RFQ_Analysis_R01.py", line 121, in <module>
    input_workbook = openpyxl.load_workbook(input_file)
  File "/home/admin/Development/pythonProject/venv/lib/python3.7/site-packages/openpyxl/reader/excel.py", line 315, in load_workbook
    reader.read()
  File "/home/admin/Development/pythonProject/venv/lib/python3.7/site-packages/openpyxl/reader/excel.py", line 280, in read
    self.read_worksheets()
  File "/home/admin/Development/pythonProject/venv/lib/python3.7/site-packages/openpyxl/reader/excel.py", line 228, in read_worksheets
    ws_parser.bind_all()
  File "/home/admin/Development/pythonProject/venv/lib/python3.7/site-packages/openpyxl/worksheet/_reader.py", line 434, in bind_all
    self.bind_cells()
  File "/home/admin/Development/pythonProject/venv/lib/python3.7/site-packages/openpyxl/worksheet/_reader.py", line 339, in bind_cells
    style = self.ws.parent._cell_styles[cell['style_id']]
IndexError: list index out of range

Answer 1

I've run into the same issue. It does not appear to be an issue with the file format, as the file opens just fine in Excel. This just looks like it's a bug with openpyxl. (I've opened a issue request here)

You can workaround it with the following monkey patch. Just put it into your startup code somewhere.

def monkey_patch_openpyxl():
    '''Openpyxl has a bug with workbooks that have wrong cell styling information.
    Monkey patch the library so it can handle these types of workbooks.'''
    from openpyxl.worksheet import _reader
    from openpyxl.cell import Cell
    def bind_cells(self):
        for idx, row in self.parser.parse():
            for cell in row:
                try:
                    style = self.ws.parent._cell_styles[cell['style_id']]
                except:  ## This is the patch, original doesn't have a try/except here
                    style = None
                c = Cell(self.ws, row=cell['row'], column=cell['column'], style_array=style)
                c._value = cell['value']
                c.data_type = cell['data_type']
                self.ws._cells[(cell['row'], cell['column'])] = c
        self.ws.formula_attributes = self.parser.array_formulae
        if self.ws._cells:
            self.ws._current_row = self.ws.max_row # use cells not row dimensions

    _reader.WorksheetReader.bind_cells = bind_cells

monkey_patch_openpyxl()

Answer 2

tldr - if you are using XlsxWriter make sure you are not writing None as the format for a cell.

---

I ran in to this while generating a simple xlsx file while using XlsxWriter. The xlsx files always opened fine but failed to load with openpyxl

Turns out I had a helper function like this

def writerow(worksheet, row, cols, fmt=None):
  for i, val in enumerate(cols):
    worksheet.write(r, i, val, fmt)

If you notice I was essentially calling worksheet.write(r, i, val, None) when I did not explicitly pass a format.

The fix is to just make sure you always have a format when you are writing a row.

default_format = workbook.add_format()
worksheet.write(row, col, value, default_format)