IndexError: list index out of range when using load_workbook

[Stephen Hill]

Hi,

I have read around a little, and have seen that the above error can be caused by style errors etc. in the file, but none of the monkey patches seem to work!. I was wondering whether anyone had any workarounds etc. or ways to change the file so that it can be opened by openpyxl. The file i am having trouble with is attached.

Thanks in advance for your help!

Steve

[Charlie Clark]

On 12 Jul 2022, at 11:24, Stephen Hill wrote:

Hi,

I have read around a little, and have seen that the above error can be

caused by style errors etc. in the file, but none of the monkey patches

seem to work!. I was wondering whether anyone had any workarounds etc. or

ways to change the file so that it can be opened by openpyxl. The file i am

having trouble with is attached.

Hi Steve,

thanks for the e-mail and the file and best of all by writing to the mailing list first.

The exception is coming from the defined names and I think this is due to the combination of chartsheets abdand the weird indexing that Excel uses in the workbook part:

• there are 8 worksheets but 20 or child objects in total, so the index 19 probably refers to the last item, which is probably "New Monthly Metals", which itself has been handily given the sheetId 61!

• but we use the index to refer to the actual worksheet (or chartsheet)

So the code probably needs updating to take into account the "modules" that we don't load and wouldn't persist with as sheets anyway: I mean, why would you? I assume these are some kind of VBA modules.

The quickest workaround for this would be to ignore the defined names if you can, ie. monkey patch the reader so that these are not processed.

The file also throws up some errors related to the new custom properties which I'll need to investigate, at least openpyxl has got better at telling you which things it doesn't like.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Stephen Hill]

Thank you for your quick and helpful response! I'm kind of glad it wasn't anything silly stopping it from working! I'll be honest, I will have a look at writing a monkey patch! I don't really know how to though! I'll let you know how I get on!

[Stephen Hill]

Hi Again! Sorry to bombard you with messages!

I have had a look, and I am guessing I need to patch the parse function in reader?  I appreciate you don't have have time to hold my hand - but a little pointer would be greatly appreciated!

Many thanks again!

Steve

[Charlie Clark]

On 12 Jul 2022, at 15:11, Stephen Hill wrote:

> Hi Again! Sorry to bombard you with messages!
>
> I have had a look, and I am guessing I need to patch the parse function in
>
> reader? I appreciate you don't have have time to hold my hand - but a
>
> little pointer would be greatly appreciated!

Basically you need to need to stop assign_names() in the parser from running so simply replacing the method so that it doesn't handle print titles or areas should be sufficient.

[Stephen Hill]

Here is what I have done - I think I have done it wrong as it still doesn't work!

from openpyxl.worksheet import _reader

def assign_names(self):
        """
        Bind reserved names to parsed worksheets
        """
        defns = []

        #Comment out this part to prevent handling of definednames
        #for defn in self.wb.defined_names.definedName:
        #    reserved = defn.is_reserved
        #    if reserved in ("Print_Titles", "Print_Area"):
        #        sheet = self.wb._sheets[defn.localSheetId]
        #        if reserved == "Print_Titles":
        #            rows, cols = _unpack_print_titles(defn)
        #            sheet.print_title_rows = rows
        #            sheet.print_title_cols = cols
        #        elif reserved == "Print_Area":
        #            sheet.print_area = _unpack_print_area(defn)
        #    else:
        #        defns.append(defn)
        self.wb.defined_names.definedName = defns

    _reader.WorkSheetParser.assign_names = assign_names

[Stephen Hill]

I realised after posting I have the wrong library imported - however I can't the the WorkbookParser class to import...

[Stephen Hill]

OK... i think I have it - i changed the above to this... no more out of range error at least!

import openpyxl.reader.workbook as wBook

   
    def assign_names(self):
        """
        Bind reserved names to parsed worksheets
        """
        defns = []

        #Comment out this part to prevent handling of definednames
        #for defn in self.wb.defined_names.definedName:
        #    reserved = defn.is_reserved
        #    if reserved in ("Print_Titles", "Print_Area"):
        #        sheet = self.wb._sheets[defn.localSheetId]
        #        if reserved == "Print_Titles":
        #            rows, cols = _unpack_print_titles(defn)
        #            sheet.print_title_rows = rows
        #            sheet.print_title_cols = cols
        #        elif reserved == "Print_Area":
        #            sheet.print_area = _unpack_print_area(defn)
        #    else:
        #        defns.append(defn)
        self.wb.defined_names.definedName = defns

    wBook.WorkbookParser.assign_names = assign_names

Thank you so much for your help! It really is appreciated!

[Charlie Clark]

On 12 Jul 2022, at 16:42, Stephen Hill wrote:

> wBook.WorkbookParser.assign_names = assign_names
>
> Thank you so much for your help! It really is appreciated!

Glad you got it working in the end.