iterate through all rows/columns openpyxl not working anymore with new version >2.3.5

Question

with the version 2.3.5 of openpyxl this code works:

from openpyxl import load_workbook

excel_file = load_workbook(excel_file_path,read_only=True)

sheet = excel_file["Sheet1"]

    mylist = []
    for row in sheet:
        for cell in row:
            mylist.append((cell.value, cell.row, cell.column))

i did this so i can process my large excel file faster. but with the new version since 2.4.x this doesnt work anymore, the first "for-loop" breaks immediately, what has changed in the version ? or how can i do the same with the latest version of openpyxl ?

Answer 1

This seems to be happening only when you have the read_only parameter set to True when loading your workbook. My workaround has been to use the max_row attribute of the sheet to create a range of values to loop through as row indices:

from openpyxl import load_workbook

excel_file = load_workbook(excel_file_path,read_only=True)
sheet = excel_file["Sheet1"]

mylist = []
row_num = sheet.max_row

for row_index in range(1,row_num):
    for cell in sheet[row_index]:
        mylist.append((cell.value, cell.row, cell.column))

Hopefully @CharlieClark can shed some light on this. But in the meantime, I hope this is helpful.