python - Openpyxl optimizing cells search speed

Question

I need to search the Excel sheet for cells containing some pattern. It takes more time than I can handle. The most optimized code I could write is below. Since the data patterns are usually row after row so I use iter_rows(row_offset=x). Unfortunately the code below finds the given pattern an increasing number of times in each for loop (starting from milliseconds and getting up to almost a minute). What am I doing wrong?

import openpyxl
import datetime
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "test_sheet"

print("Generating quite big excel file")

for i in range(1,10000):
    for j in range(1,20):
        ws.cell(row = i, column = j).value = "Cell[{},{}]".format(i,j)

print("Saving test excel file")
wb.save('test.xlsx')

def FindXlCell(search_str, last_r):
    t = datetime.datetime.utcnow()
    for row in ws.iter_rows(row_offset=last_r):
        for cell in row:
            if (search_str == cell.value):
                print(search_str, last_r, cell.row, datetime.datetime.utcnow() - t)
                last_r = cell.row
                return last_r
    print("record not found ",search_str, datetime.datetime.utcnow() - t)
    return 1

wb = openpyxl.load_workbook("test.xlsx", data_only=True)
t = datetime.datetime.utcnow()
ws = wb["test_sheet"]
last_row = 1
print("Parsing excel file in a loop for 3 cells")
for i in range(1,100,1):
    last_row = FindXlCell("Cell[0,0]", last_row)
    last_row = FindXlCell("Cell[1000,6]", last_row)
    last_row = FindXlCell("Cell[6000,6]", last_row)

Answer 1

Looping over a worksheet multiple times is inefficient. The reason for the search getting progressively slower looks to be increasingly more memory being used in each loop. This is because last_row = FindXlCell("Cell[0,0]", last_row) means that the next search will create new cells at the end of the rows: openpyxl creates cells on demand because rows can be technically empty but cells in them are still addressable. At the end of your script the worksheet has a total of 598000 rows but you always start searching from A1.

If you wish to search a large file for text multiple times then it would probably make sense to create a matrix keyed by the text with the coordinates being the value.

Something like:

matrix = {}
for row in ws:
    for cell in row:
         matrix[cell.value] = (cell.row, cell.col_idx)

In a real-world example you'd probably want to use a defaultdict to be able to handle multiple cells with the same text.

This could be combined with read-only mode for a minimal memory footprint. Except, of course, if you want to edit the file.