CODEWITHSUNDEEP
pythonopenpyxl
BLNT
BLNT

Reputation: 146

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

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

Upvotes: 6

Views: 5451

Answers (4)

Long Tran
Long Tran

Reputation: 11

I'm not sure what's wrong with your excel file and I also didn't meet the same problem but just try this one, I believe it will work well:

Go to the _reader.py (.\Python\Python311\Lib\site-packages\openpyxl\worksheet)

Find the bind_cells() in class WorksheetReader and simply apply try/except like as:

 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: style = StyleArray('i', [0, 0, 0, 0, 0, 0, 0, 0, 0])
            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

    if self.ws._cells:
        self.ws._current_row = self.ws.max_row # use cells not row dimensions

Upvotes: 0

Long Tran
Long Tran

Reputation: 11

I meet the same problem and this is my solution: Go to the _reader.py (.\Python\Python311\Lib\site-packages\openpyxl\worksheet)

Find the bind_row_dimensions() in class WorksheetReader and simply apply try/except like as:

def bind_row_dimensions(self):
    for row, rd in self.parser.row_dimensions.items():
        if 's' in rd:
            key = int(rd['s'])
            try: rd['s'] = self.ws.parent._cell_styles[key]
            except: rd['s'] = None
        self.ws.row_dimensions[int(row)] = RowDimension(self.ws, **rd)

It works for me and hope you so.

Upvotes: 0

Scott
Scott

Reputation: 1688

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)

Upvotes: 0

speedplane
speedplane

Reputation: 16141

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

Upvotes: 4

Related Questions