Чтение Excel в openpyxl: load_workbook и обход данных

Создавать файлы мы научились в первом уроке, записывать значения — в третьем. Но половина реальной работы с Excel начинается с чужого файла: бухгалтерия прислала реестр, поставщик — прайс, коллега — отчёт. Сегодня openpyxl читает готовые таблицы: откроем файл, обойдём его строку за строкой и перельём данные в обычные списки Python, где с ними можно делать что угодно.

Как открыть существующий файл: load_workbook

За чтение отвечает load_workbook(путь) — из того же модуля openpyxl. Возвращает она знакомый объект книги, только наполненный содержимым файла: те же листы, ячейки, значения. В песочнице урока готовых файлов нет, поэтому в каждом примере мы сначала создадим маленький файл, а потом откроем его как «чужой» — на твоём компьютере первый шаг не нужен, файл уже лежит на диске.

from openpyxl import Workbook, load_workbook
import os

# сначала создадим файл, чтобы было что читать
wb = Workbook()
ws = wb.active
ws.append(["Товар", "Цена"])
ws.append(["Кофе", 350])
wb.save("price-l4.xlsx")

# а теперь откроем его как существующий
book = load_workbook("price-l4.xlsx")
sheet = book.active

print("Лист:", sheet.title)
print("B2:", sheet["B2"].value)

os.remove("price-l4.xlsx")

Лист: Sheet
B2: 350

Всё, что ты уже знаешь о работе с книгой и листами, работает и при чтении: book.active, доступ к листам по имени, sheet["B2"].value. Отличие в направлении данных: Workbook() создаёт пустую книгу, load_workbook — заполняет из файла. Переменную для книги здесь назвали book, а не wb, не случайно: так в коде различают «новую книгу, которую я наполняю» и «чужую, которую я читаю». Практика мелкая, но код читается заметно легче — в скриптах, где есть и то и другое, это спасает от перепутанных переменных.

И ещё одно следствие того, что load_workbook возвращает обычный объект книги: весь арсенал из прошлых уроков применим без оговорок. Можно добавить лист в чужой файл, переименовать, докрасить шапку — а потом save записать правки. Чтение и запись в openpyxl — не два разных мира, а одна и та же модель книги с разными источниками содержимого.

iter_rows: обходим таблицу строками

Читать ячейки по одной адресно — верный способ промучиться с большим файлом до вечера. Метод iter_rows отдаёт таблицу построчно, а с аргументом values_only=True — сразу значениями: каждая строка превращается в кортеж Python.

from openpyxl import Workbook, load_workbook
import os

wb = Workbook()
ws = wb.active
for row in [["Товар", "Цена"], ["Кофе", 350], ["Чай", 120]]:
    ws.append(row)
wb.save("price-l4.xlsx")

book = load_workbook("price-l4.xlsx")
sheet = book.active

for row in sheet.iter_rows(values_only=True):
    print(row)

os.remove("price-l4.xlsx")

('Товар', 'Цена')
('Кофе', 350)
('Чай', 120)

Три строки файла — три кортежа. Кортежи можно распаковывать в именованные переменные: for name, price in ... — и дальше работать с ними, как с обычными данными. Границы обхода настраиваются: min_row=2 пропустит шапку, max_col=1 ограничит первый столбцом, а iter_cols обойдёт таблицу по столбцам вместо строк. Пустые ячейки приходят в кортеже как None — так же, как при адресном чтении.

max_row и max_column: размеры таблицы

Прежде чем обходить, полезно знать, сколько в таблице строк и столбцов. Открытый лист отвечает свойствами max_row и max_column — это номера последней занятой строки и последнего занятого столбца.

from openpyxl import Workbook, load_workbook
import os

wb = Workbook()
ws = wb.active
for row in [["Товар", "Цена", "Остаток"], ["Кофе", 350, 7], ["Чай", 120, 40]]:
    ws.append(row)
wb.save("price-l4.xlsx")

book = load_workbook("price-l4.xlsx")
sheet = book.active

print("Строк:", sheet.max_row)
print("Столбцов:", sheet.max_column)
last = sheet.cell(row=sheet.max_row, column=sheet.max_column)
print("Правый нижний угол:", last.value)

os.remove("price-l4.xlsx")

Строк: 3
Столбцов: 3
Правый нижний угол: 40

Пара max_row и max_column задаёт прямоугольник данных: левый верхний угол у таблиц всегда A1, правый нижний — вот он. Комбинация с cell(row=..., column=...) из прошлого урока — стандартный способ добраться до последнего значения без обхода.

iter_cols: те же данные, но по столбцам

Двойник iter_rows — метод iter_cols с теми же аргументами, только порции идут по вертикали: каждая колонка приходит одним кортежем. Границы задаются так же, только min_col и max_col вместо строк.

from openpyxl import Workbook, load_workbook
import os
wb = Workbook()
ws = wb.active
for row in [["Товар", "Цена"], ["Кофе", 350], ["Чай", 120]]:
    ws.append(row)
wb.save("price-l4.xlsx")
book = load_workbook("price-l4.xlsx")
sheet = book.active
for col in sheet.iter_cols(min_col=1, max_col=1, values_only=True):
    print(col)
os.remove("price-l4.xlsx")

('Товар', 'Кофе', 'Чай')

Один кортеж — весь столбец «Товар». Строчный обход остаётся рабочей лошадкой, но столбцовый выручает в задачах вроде «собери все телефоны из колонки C» или «проверь, что в столбце с ценами нет отрицательных»: без него пришлось бы прыгать по строкам индексами.

Один столбец из iter_rows

Границы работают в обе стороны: можно оставить все строки, но сузить столбцы. min_col=2, max_col=2 отдаст только вторую колонку — по-прежнему кортежами, по-прежнему строка за строкой.

from openpyxl import Workbook, load_workbook
import os

wb = Workbook()
ws = wb.active
ws.append(["Товар", "Цена"])
ws.append(["Кофе", 350])
wb.save("price-l4.xlsx")

book = load_workbook("price-l4.xlsx")
sheet = book.active
for col in sheet.iter_rows(min_col=2, max_col=2, values_only=True):
    print(col)

os.remove("price-l4.xlsx")

('Цена',)
(350,)

Обрати внимание: значения по-прежнему завёрнуты в кортежи, пусть и одноэлементные — метод не разворачивает кортеж с одним полем. Распаковка for (price,) in ... или индекс [0] — на твой вкус. Такой точечный обход — половина пути к фильтрам и агрегатам, которые мы строили в блоке про накопление.

Накопление: из таблицы в список Python

Настоящая сила связки openpyxl плюс Python — в том, что данные из таблицы перетекают в привычные структуры: списки, словари, числа. Обход с накоплением — шаблон, который встретится в каждом втором скрипте курса.

from openpyxl import Workbook, load_workbook
import os

wb = Workbook()
ws = wb.active
for row in [
    ["Товар", "Цена"],
    ["Кофе", 350],
    ["Чай", 120],
    ["Сироп", 90],
    ["Какао", 280],
]:
    ws.append(row)
wb.save("price-l4.xlsx")

book = load_workbook("price-l4.xlsx")
sheet = book.active

cheap = []                 # сюда соберём дешёвые позиции
for name, price in sheet.iter_rows(min_row=2, values_only=True):
    if price < 200:
        cheap.append(name)

print("Дешевле 200:", ", ".join(cheap))

os.remove("price-l4.xlsx")

Дешевле 200: Чай, Сироп

min_row=2 пропустил шапку, распаковка for name, price разложила кортежи по переменным, условие отсекло дорогое — и данные файла стали обычным списком строк Python. Замени cheap.append(name) на что угодно: подсчёт итогов, запись в базу, отправку письма — таблица из Excel перестаёт быть таблицей и становится данными.

• распаковка кортежа требует точного числа переменных: три колонки — три имени, иначе ValueError;
• колонки, которых может не быть, читай по индексу: row[2], если вернётся None — обходи условием;
• пустые строки из «хвоста» файла фильтруй условием if name is None: continue;
• суммы и средние копи в переменных прямо во время обхода — второй проход по файлу не нужен.

from openpyxl import Workbook, load_workbook
import os

wb = Workbook()
ws = wb.active
ws.append(["Товар", "Цена"])
ws.append(["Кофе", 350])
wb.save("price-l4.xlsx")

book = load_workbook("price-l4.xlsx")
sheet = book.active

first_row = next(sheet.iter_rows(min_row=2, max_row=2))
print(type(first_row[0]).__name__)   # это ячейка, а не строка!
print(first_row[0].value)            # значение достаём через .value

os.remove("price-l4.xlsx")

Cell
Кофе

Объекты ячеек не бесполезны: вместе со значением они несут стили, адрес и координаты — то, что нужно, когда ты не читаешь файл, а анализируешь его оформление. Но для обхода данных по умолчанию пиши values_only=True и не думай дважды.

Как быстро заметить эту ошибку в чужом коде? По запаху: если в цикле по iter_rows вызывается .value — кто-то забыл values_only, и распаковка тянет ячейки вместо значений. Обратная ошибка тоже встречается: values_only=True и попытка вызвать у строки .value — упадёт с AttributeError, ведь кортежи значений уже ничего не знают про стили и адреса. Определись на входе функции, что она получает: значения или ячейки — и держись выбора до конца.

Чтение всех листов книги

У настоящих файлов листов несколько — обходить нужно все. Связка book.sheetnames и доступ по имени из урока 2 решает это циклом. Типичный сценарий — месячные отчёты, где каждый месяц лежит на своём листе с именем «Январь», «Февраль» и дальше: один цикл собирает их в общий список строк, из которого потом рождается сводный файл.

from openpyxl import Workbook, load_workbook
import os

wb = Workbook()
wb.active.title = "Прайс"
wb.active.append(["Товар", "Цена"])
wb.create_sheet("Клиенты")["A1"] = "ООО Ромашка"
wb.save("shop-l4.xlsx")

book = load_workbook("shop-l4.xlsx")
for name in book.sheetnames:       # обходим все листы книги
    sheet = book[name]
    print(name, "->", sheet["A1"].value)

os.remove("shop-l4.xlsx")

Прайс -> Товар
Клиенты -> ООО Ромашка

Первая программа: итог по ведомости

Классическая задача бухгалтерии: в ведомости позиции с ценами и количеством, нужен итог. Соберём файл, откроем его и посчитаем сумму, обходя строки кортежами.

from openpyxl import Workbook, load_workbook
import os

# собираем ведомость: товар, цена, количество
wb = Workbook()
ws = wb.active
for row in [
    ["Товар", "Цена", "Штук"],
    ["Степлер", 240, 12],
    ["Папка", 80, 40],
    ["Бумага", 350, 6],
]:
    ws.append(row)
wb.save("stock-l4.xlsx")

book = load_workbook("stock-l4.xlsx")
sheet = book.active

total = 0
for name, price, count in sheet.iter_rows(min_row=2, values_only=True):
    total += price * count

print("Позиций:", sheet.max_row - 1)
print("Итого:", total, "руб.")

os.remove("stock-l4.xlsx")

Позиций: 3
Итого: 8180 руб.

Весь скрипт — пара циклов: один наполняет файл, другой считает. Формулы в Excel сделали бы то же самое, но openpyxl формулы записывает, а не вычисляет — об этом отдельный урок позже по курсу; пока считаем в Python, и это даже надёжнее: итог виден в момент работы скрипта.

Отдельное слово про большие файлы. Всё, что ты выучил здесь, работает и на реестрах в сотни тысяч строк — openpyxl читает их целиком в память. Когда-нибудь упрёшься в объём: у load_workbook есть режим read_only=True, который жертвует удобством ради скорости и не тянет файл целиком. В учебных и офисных задачах он не понадобится, но знать о нём стоит — упоминаю, чтобы сюрпризом не стал.

from openpyxl import load_workbook

book = load_workbook("net-takogo-fajla.xlsx")

FileNotFoundError: [Errno 2] No such file or directory: 'net-takogo-fajla.xlsx'

Что дальше

Полный цикл данных замкнулся: создаём, сохраняем, открываем, обходим. load_workbook и iter_rows(values_only=True) — это девяносто процентов всего чтения Excel в Python. Дальше файлы перестанут быть чёрно-белыми: урок 5 оденет шапку прайса в шрифт, заливку и границы — Font, PatternFill и Border, из-за которых генератор отчётов впервые заменит ручную раскраску целиком.

iter_rows с values_only=True отдаёт таблицу кортежами значений: данные Excel становятся обычными списками Python, дальше — любая логика.