Openpyxl оптимизирует скорость поиска ячеек

Question

Мне нужно найти на листе Excel клетки, содержащие какой-либо шаблон. Это займет больше времени, чем я могу справиться. Наиболее оптимизированный код, который я мог написать, приведен ниже. Поскольку шаблоны данных обычно располагаются строка за строкой, я использую iter_rows(row_offset=x). К сожалению, приведенный ниже код находит данный шаблон все большее число раз в каждом цикле for (начиная с миллисекунд и заканчивая почти минутой). Что я делаю неправильно?

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

Многократное зацикливание на листе неэффективно. Причина, по которой поиск становится все медленнее, заключается в том, что в каждом цикле используется все больше памяти. Это потому что last_row = FindXlCell("Cell[0,0]", last_row) означает, что следующий поиск создаст новые ячейки в конце строк: openpyxl создает ячейки по требованию, поскольку строки могут быть технически пустыми, но ячейки в них по-прежнему адресуемы. В конце вашего скрипта рабочая таблица содержит 598000 строк, но вы всегда начинаете поиск с A1,

Если вы хотите многократно выполнять поиск текста в большом файле, то, вероятно, имеет смысл создать матрицу с текстом, координатами которой будет значение.

Что-то вроде:

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

В реальном примере вы, вероятно, захотите использовать defaultdict чтобы иметь возможность обрабатывать несколько ячеек с одним и тем же текстом.

Это может быть объединено с режимом "только чтение" для минимального использования памяти. За исключением, конечно, если вы хотите редактировать файл.