openpyxl: чтение и запись Excel-файлов

openpyxl работает с файлами .xlsx напрямую, без установленного Excel. Вы открываете книгу, ходите по ячейкам и листам, пишете значения, формулы и стили — всё из Python.

Суть: книга → лист → ячейка. Ячейка адресуется как ws['B2'] или ws.cell(row, column). Формула — это просто строка, начинающаяся с =.

pandas отлично считает, но когда нужно именно оформить Excel — жирные заголовки, цветные ячейки, формулы, которые пересчитываются в самом Excel — в дело вступает openpyxl. Эта библиотека читает и пишет формат .xlsx на уровне отдельных ячеек и не требует, чтобы Excel был установлен. Идеально для серверов и отчётов.

Модель простая и иерархичная: Workbook (книга) содержит Worksheet (листы), а лист состоит из ячеек. К ячейке обращаются по адресу 'A1' либо по номерам строки и столбца. Поскольку openpyxl недоступен в браузере, его код — во врезках language-text.

from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
ws.title = 'Отчёт'

# Заголовки и данные
ws['A1'] = 'Город'
ws['B1'] = 'Продажи'
ws.append(['Москва', 2700])
ws.append(['Казань', 1900])

# Формула — обычная строка с '='
ws['B4'] = '=SUM(B2:B3)'
wb.save('report.xlsx')

# Чтение существующей книги:
wb2 = load_workbook('report.xlsx', data_only=True)
ws2 = wb2.active
print(ws2['A2'].value, ws2['B2'].value)

Адрес ячейки вроде B2 — это буква столбца и номер строки. В коде иногда удобнее числа: openpyxl умеет переводить номер столбца в букву и обратно. Эту арифметику адресов можно отработать в браузере на чистом Python.

Попробуй сам ▶

# Перевод номера столбца в букву Excel (1->A, 27->AA)
def col_letter(n):
    letters = ''
    while n > 0:
        n, rem = divmod(n - 1, 26)
        letters = chr(65 + rem) + letters
    return letters

for n in [1, 2, 26, 27, 28, 52, 53]:
    print(f'столбец {n:2} -> {col_letter(n)}')

# Построим адрес ячейки из строки и столбца
row, col = 5, 28
print(f'Ячейка: {col_letter(col)}{row}')   # AB5

МОДЕЛЬ EXCEL В OPENPYXL

  Workbook (файл .xlsx)
    +-- Worksheet 'Отчёт'
    |     +-- A1 'Город'   B1 'Продажи'
    |     +-- A2 'Москва'  B2  2700
    |     +-- B4  =SUM(B2:B3)
    +-- Worksheet 'Лист2'

  доступ: wb -> ws -> ws['B2'].value

Помимо значений и формул, openpyxl управляет оформлением, и для отчётов это важно не меньше цифр. Через объекты из openpyxl.styles можно задать жирный шрифт заголовкам, залить ячейки цветом для выделения отклонений, выровнять текст и настроить ширину столбцов под содержимое. Можно даже добавить условное форматирование и диаграммы прямо в книгу. Отчёт, который не только верен, но и читается с первого взгляда, ценится в разы выше сухой таблицы цифр. Поэтому связку «pandas посчитал — openpyxl оформил» применяют постоянно: каждая библиотека делает то, в чём сильна.

Как работает под капотом

Файл .xlsx — это на самом деле zip-архив с XML-файлами внутри: отдельно стили, отдельно данные каждого листа. openpyxl распаковывает архив, разбирает XML в объекты Python, даёт вам с ними работать, а при save() собирает всё обратно в zip. Поэтому openpyxl не нужен установленный Excel — он работает напрямую с форматом файла.

Важная тонкость — параметр data_only при чтении. Если он True, openpyxl вернёт последнее посчитанное Excel'ем значение формулы. Если False — саму строку формулы. Но кэш значений появляется только после того, как файл хоть раз открыли в Excel; openpyxl сам формулы не вычисляет.

Частые ошибки

• Ждать, что openpyxl посчитает формулу. Он только записывает её текст; вычисляет уже Excel при открытии.
• Забыть save(). Без сохранения все изменения остаются только в памяти.
• Путать индексацию. В openpyxl строки и столбцы нумеруются с 1, а не с 0, как в списках Python.

Best practices

• Для чистых расчётов берите pandas, для оформления и формул — openpyxl. Их часто комбинируют.
• Читаете значения формул — открывайте с data_only=True.
• Заголовки выносите отдельно и оформляйте стилем, чтобы отчёт читался.

Итоги. openpyxl даёт прямой доступ к ячейкам, листам, формулам и стилям .xlsx без Excel. Помните: формулы он пишет, но не считает. Теперь соберём всё вместе — построим готовый отчёт из данных.