openpyxl: как работать с таблицами Excel из Python

Библиотека openpyxl в Python читает и создает файлы Excel формата xlsx без установленного Microsoft Excel, поэтому скрипт одинаково работает на Windows, Linux и сервере. Ниже показано, как создать книгу и записать данные, прочитать готовый файл, перебрать строки, управлять листами, добавить формулы и оформление. Отдельно разобраны режимы для больших файлов и частые ошибки.

Установка и основные объекты

Пакет ставится из PyPI командой ниже. Если вы еще не пользовались менеджером пакетов, загляните в статью про pip.

pip install openpyxl

openpyxl понимает форматы .xlsx, .xlsm, .xltx и .xltm. Старый двоичный формат .xls из Excel 97-2003 библиотека не открывает: такой файл нужно пересохранить в xlsx или читать другим пакетом.

В коде вы постоянно встречаете три объекта:

• Workbook - книга, то есть весь файл;
• Worksheet - отдельный лист книги;
• Cell - ячейка с адресом, значением и стилем.

Строки и столбцы нумеруются с 1, а не с 0, как списки в Python. Ячейку можно получить по буквенному адресу ws["B3"] или по номерам ws.cell(row=3, column=2).

Создание книги и запись данных

Новая книга создается вызовом Workbook(), в ней сразу есть один лист, доступный через свойство active. Метод append() дописывает строку после последней заполненной, это самый удобный способ выгрузить список записей.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Продажи"

ws.append(["Товар", "Кол-во", "Цена"])
rows = [("Ноутбук", 2, 55000), ("Мышь", 10, 900), ("Монитор", 3, 18000)]
for row in rows:
    ws.append(row)

ws["E1"] = "Создано скриптом"
ws.cell(row=6, column=1, value="Итого")
wb.save("sales.xlsx")

Метод save() перезаписывает файл без предупреждения. Если файл в этот момент открыт в Excel на Windows, вы получите PermissionError: закройте его и запустите скрипт снова.

Значения записываются с типами Python: числа остаются числами, строки строками, объекты datetime превращаются в даты Excel. Поэтому не переводите числа в строки заранее, иначе в таблице не получится их суммировать.

Чтение существующего файла

Функция load_workbook() открывает книгу. Значение ячейки хранится в атрибуте .value, пустая ячейка вернет None. Свойства max_row и max_column подсказывают размер заполненной области.

from openpyxl import load_workbook

wb = load_workbook("sales.xlsx")
ws = wb["Продажи"]

print(ws["A2"].value)              # Ноутбук
print(ws.max_row, ws.max_column)   # 6 5

for name, qty, price in ws.iter_rows(min_row=2, max_row=4, max_col=3, values_only=True):
    print(name, qty * price)
# Ноутбук 110000
# Мышь 9000
# Монитор 54000

Перебор строк, столбцов и диапазонов

Метод iter_rows() отдает строки как кортежи ячеек, а с параметром values_only=True сразу как кортежи значений. Границы задаются параметрами min_row, max_row, min_col, max_col. Для обхода по столбцам есть iter_cols() с теми же параметрами.

Срез по адресу тоже работает: ws["A2:C4"] вернет кортеж строк, а ws["B"] все ячейки столбца B. Чтобы перевести номер столбца в букву и обратно, используйте get_column_letter() и column_index_from_string() из модуля openpyxl.utils.

Формулы и data_only

По умолчанию в ячейке с формулой вы прочитаете саму формулу, например =B2*C2. Параметр load_workbook(path, data_only=True) возвращает значения, которые Excel вычислил при последнем сохранении. openpyxl формулы не пересчитывает, поэтому в файле, созданном скриптом и ни разу не открытом в Excel, вместо результата будет None.

Управление листами

Список имен листов хранится в wb.sheetnames, обращение к листу по имени выглядит как обращение к словарю. Новые листы создает create_sheet(), второй аргумент задает позицию.

from openpyxl import load_workbook

wb = load_workbook("sales.xlsx")
report = wb.create_sheet("Отчет", 0)      # первым в книге
backup = wb.copy_worksheet(wb["Продажи"])  # копия внутри той же книги
backup.title = "Продажи (архив)"

print(wb.sheetnames)  # ['Отчет', 'Продажи', 'Продажи (архив)']
wb.remove(wb["Продажи (архив)"])
wb.save("sales.xlsx")

Строки и столбцы вставляются и удаляются методами insert_rows(idx, amount), delete_rows(idx, amount), insert_cols() и delete_cols(). Формулы, которые ссылаются на сдвинутые ячейки, при этом автоматически не исправляются, учитывайте это в шаблонах отчетов.

Формулы, стили и ширина столбцов

Формулу записывают строкой, начинающейся со знака равенства. Excel посчитает ее при открытии файла. Названия функций пишутся на английском: SUM, а не СУММ, разделитель аргументов запятая.

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = load_workbook("sales.xlsx")
ws = wb["Продажи"]

ws["D1"] = "Сумма"
for r in range(2, 5):
    ws[f"D{r}"] = f"=B{r}*C{r}"
    ws[f"D{r}"].number_format = "#,##0"
ws["D6"] = "=SUM(D2:D4)"

header_fill = PatternFill(fill_type="solid", start_color="DDEBF7")
for cell in ws[1]:
    cell.font = Font(bold=True)
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

ws.column_dimensions["A"].width = 18
ws.freeze_panes = "A2"   # закрепить строку заголовков
wb.save("sales.xlsx")

Объединение ячеек делается методом ws.merge_cells("A8:D8"), значение хранится в левой верхней ячейке диапазона. Ширину столбца openpyxl не подбирает автоматически, ее приходится задавать вручную, например по длине самого длинного значения в столбце.

Большие файлы и выбор инструмента

При обычном открытии вся книга загружается в память, и файл на сотни тысяч строк может занять гигабайты. Для таких случаев есть два облегченных режима: load_workbook(path, read_only=True) читает строки потоком, а Workbook(write_only=True) пишет их потоком через append(). В режиме только для чтения книгу нужно закрыть вызовом wb.close(), иначе файл останется занятым.

pandas для чтения и записи xlsx сам использует openpyxl как движок, поэтому эти библиотеки часто работают в паре: данные готовит pandas, а оформление делает openpyxl. Если оформление не нужно, проще сохранить данные в CSV-файл. Все параметры функций описаны в документации openpyxl.

Частые вопросы

Ответы на вопросы, которые чаще всего задают при работе с Excel из Python.

Почему вместо результата формулы я получаю None?

Файл открыт с data_only=True, но формулу никто не вычислял: openpyxl ее только записал. Откройте и сохраните файл в Excel или LibreOffice, после этого кешированное значение появится. Другой путь: посчитать результат в Python и записать число.

Как найти последнюю заполненную строку?

Используйте ws.max_row. Учтите, что строка считается занятой, даже если в ней осталось только форматирование, поэтому для точного результата пройдите по строкам с конца и найдите первую, где есть значение.

Сохраняются ли картинки и диаграммы при пересохранении?

Не всегда. openpyxl читает не все объекты Excel: фигуры, элементы управления и часть сложного оформления при пересохранении теряются. Проверяйте результат на копии файла и не перезаписывайте оригинал.

Как записать дату в ячейку?

Присвойте объект datetime.date или datetime.datetime, Excel покажет его как дату. Формат вывода задается через cell.number_format = "DD.MM.YYYY". О работе с датами подробнее в статье про модуль datetime.