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

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()

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)