Библиотека openpyxl в Python: как работать с Excel-файлами

Openpyxl — библиотека Python для чтения и записи файлов Excel в форматах xlsx и xlsm. С ней можно открыть готовую таблицу, достать из нее данные, записать новые значения, оформить ячейки, добавить формулы и графики — без установленного Excel и без ручной работы. Библиотеку используют аналитики, бухгалтеры, которые автоматизируют отчеты, и разработчики, которым нужно выгрузить данные из системы в привычный пользователю формат. Разбираем установку, основные операции, стили, работу с большими файлами и типичные ошибки.

Курсы Python и анализа данных:

• Python для анализа данных — Яндекс Практикум, 45 780 ₽

• Python-разработчик — Академия Эдюсон, 93 120 ₽

• Python-разработчик с нуля — Нетология, 90 900 ₽

• Все курсы по Python — программы школ в каталоге РБК

Что умеет openpyxl и когда она нужна

Библиотека работает с файлом Excel как с набором объектов: книга (Workbook) состоит из листов (Worksheet), лист — из ячеек (Cell). Все, что можно сделать руками в Excel со структурой и оформлением, можно описать кодом.

Типичные задачи:

• собрать ежедневный отчет из базы данных или API и сохранить его в xlsx;

• пройтись по десяткам однотипных файлов от филиалов и свести их в один;

• заполнить шаблон: подставить значения в готовую таблицу с оформлением;

• проверить выгрузку: найти пустые ячейки, дубли, неверные форматы;

• раскрасить и отформатировать таблицу, которую потом откроет человек.

Чего она не делает: не пересчитывает формулы, не работает со старым форматом xls и не умеет запускать макросы. Об этом — в разделе про ошибки.

Установка openpyxl

Библиотека ставится через pip:

• pip install openpyxl

Если работаете с pandas и хотите, чтобы он читал и писал xlsx, ставьте обе:

• pip install pandas openpyxl

Проверить установку можно импортом в интерпретаторе: если команда import openpyxl выполняется без ошибки, все готово. Ошибка «No module named openpyxl» почти всегда значит, что библиотека поставлена в другое окружение: в другой Python или вне виртуального окружения проекта. Ставьте ее тем же интерпретатором, которым запускаете скрипт, например python -m pip install openpyxl. В облачном Google Colab библиотека обычно уже установлена.

Создание и открытие книги

Новая книга создается одной строкой, у нее сразу есть один активный лист:

• from openpyxl import Workbook

• wb = Workbook()

• ws = wb.active

• ws.title = "Отчет"

• wb.save("report.xlsx")

Готовый файл открывают функцией load_workbook:

• from openpyxl import load_workbook

• wb = load_workbook("sales.xlsx")

• ws = wb["Январь"]

После изменений файл обязательно сохраняют через wb.save(). Если сохранить под тем же именем, исходный файл перезапишется — для экспериментов лучше сохранять копию под новым именем. Файл, открытый в этот момент в Excel, на Windows сохранить не получится: система не даст его перезаписать.

Работа с листами

• wb.sheetnames — список имен листов;

• wb.create_sheet("Итоги") — новый лист в конце, wb.create_sheet("Итоги", 0) — в начале;

• wb.copy_worksheet(ws) — копия листа внутри той же книги;

• del wb["Черновик"] — удалить лист;

• ws.sheet_properties.tabColor = "1072BA" — цвет ярлыка.

Перебрать все листы можно обычным циклом for ws in wb. Это удобно, когда структура листов одинаковая — например, по месяцам.

Чтение и запись ячеек

К ячейке обращаются по адресу или по номерам строки и столбца, нумерация с единицы:

• ws["A1"] = "Товар"

• ws["B1"] = "Сумма"

• ws.cell(row=2, column=1, value="Ноутбук")

• price = ws["B2"].value

Добавить строку в конец таблицы удобнее методом append — он сам найдет первую пустую строку:

• ws.append(["Монитор", 15000])

• ws.append(["Клавиатура", 2500])

Диапазоны. Срез ws["A1:C10"] возвращает кортеж строк, каждая строка — кортеж ячеек. Отдельные столбцы и строки: ws["A"] и ws[5]. Границы заполненной области показывают ws.max_row и ws.max_column.

Перебор строк: iter_rows и values_only

Для чтения больших таблиц используют iter_rows. Параметр values_only=True возвращает сразу значения, а не объекты ячеек, — так код короче и быстрее:

• for row in ws.iter_rows(min_row=2, values_only=True):

• name, amount = row[0], row[1]

• print(name, amount)

Аналогично работает iter_cols для обхода по столбцам. Типичная ошибка — забыть min_row=2 и обработать строку заголовков как данные.

Пустые ячейки возвращают None. Перед арифметикой их стоит отфильтровать, иначе получите ошибку сложения числа и None.

Формулы: запись и чтение результата

Формулу записывают как строку, начинающуюся со знака равенства:

• ws["B10"] = "=SUM(B2:B9)"

Excel посчитает ее при открытии файла. Сама библиотека формулы не вычисляет, поэтому при чтении из ячейки вернется текст формулы. Чтобы получить результат, файл открывают с параметром data_only=True:

• wb = load_workbook("sales.xlsx", data_only=True)

Важная деталь: результат берется из кеша, который Excel сохранил при последнем сохранении файла. Если файл создан или изменен кодом и после этого не открывался в Excel, значения будут None. И еще: если открыть книгу с data_only=True и сохранить ее, формулы в файле заменятся значениями — исходные формулы пропадут.

Оформление: шрифты, заливка, рамки, выравнивание

Стили импортируются из openpyxl.styles и присваиваются ячейке:

• from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

• ws["A1"].font = Font(bold=True, size=12, color="FFFFFF")

• ws["A1"].fill = PatternFill("solid", fgColor="1F4E78")

• thin = Side(style="thin", color="999999")

• ws["A1"].border = Border(left=thin, right=thin, top=thin, bottom=thin)

• ws["A1"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)

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

Ширина столбцов и высота строк:

• ws.column_dimensions["A"].width = 30

• ws.row_dimensions[1].height = 24

Автоподбора ширины, как двойной щелчок в Excel, в библиотеке нет: ширину считают сами, например по длине самого длинного значения в столбце.

Объединение ячеек и закрепление областей:

• ws.merge_cells("A1:D1")

• ws.freeze_panes = "A2"

Первая строка объединяет ячейки под заголовок, вторая закрепляет шапку при прокрутке. Значение у объединенной области хранится только в левой верхней ячейке.

Excel и аналитика

• Excel и Google-таблицы — Академия Эдюсон, 17 300 ₽

• Excel academy + Power BI для анализа данных — SF Education, 24 999 ₽

• Аналитик данных — Академия Эдюсон, 109 900 ₽

• Курсы по Excel — программы школ в каталоге РБК

Числовые форматы и даты

Формат отображения числа задается строкой, как в окне «Формат ячеек» Excel:

• ws["B2"].number_format = "# ##0.00"

• ws["C2"].number_format = "0%"

• ws["D2"].number_format = "DD.MM.YYYY"

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

Графики в openpyxl

В модуле openpyxl.chart есть основные типы диаграмм: столбчатая, линейная, круговая, точечная. Схема одна: создать диаграмму, указать диапазон данных и подписей, добавить на лист.

• from openpyxl.chart import BarChart, Reference

• chart = BarChart()

• data = Reference(ws, min_col=2, min_row=1, max_row=10)

• cats = Reference(ws, min_col=1, min_row=2, max_row=10)

• chart.add_data(data, titles_from_data=True)

• chart.set_categories(cats)

• ws.add_chart(chart, "E2")

Диаграммы получаются «живыми»: это настоящие диаграммы Excel, которые обновятся, если поменять данные в таблице.

Большие файлы: read_only и write_only

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

• load_workbook("big.xlsx", read_only=True) — потоковое чтение, строки читаются по мере обхода. Изменять файл в этом режиме нельзя, а после работы книгу нужно закрыть через wb.close().

• Workbook(write_only=True) — потоковая запись. Строки добавляются только через append, к произвольной ячейке обратиться нельзя.

Если задача — просто прочитать большую таблицу для анализа, быстрее и удобнее pandas: он читает xlsx через ту же openpyxl, но сразу превращает данные в таблицу с типами. Анализ данных на Python мы подробнее разбираем в разделе «Аналитика на Python».

Пример: свести файлы филиалов в один отчет

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

• from pathlib import Path

• from openpyxl import Workbook, load_workbook

• report = Workbook()

• out = report.active

• out.append(["Филиал", "Товар", "Сумма"])

• for path in Path("branches").glob("*.xlsx"):

• src = load_workbook(path, read_only=True)

• for name, amount in src.active.iter_rows(min_row=2, max_col=2, values_only=True):

• if name and amount is not None:

• out.append([path.stem, name, amount])

• src.close()

• out.append(["Итого", "", f"=SUM(C2:C{out.max_row})"])

• report.save("report.xlsx")

Что здесь важно: исходные файлы открываются в режиме только чтения и сразу закрываются, пустые строки отсекаются проверкой, а итог считается формулой — Excel пересчитает его при открытии. Дальше к отчету можно применить оформление из разделов выше: жирную шапку, ширину столбцов, денежный формат.

Фильтры, условное форматирование и проверка данных

Автофильтр на таблицу ставится одной строкой — ws.auto_filter.ref = "A1:C100". Кнопки фильтра появятся в шапке, сама фильтрация при этом не применяется: строки скрывает уже пользователь в Excel.

Условное форматирование подсвечивает ячейки по правилу, как в Excel. Например, правило CellIsRule из модуля openpyxl.formatting.rule закрашивает красным суммы ниже нуля, а ColorScaleRule строит цветовую шкалу от минимума к максимуму. Правило добавляется на диапазон через ws.conditional_formatting.add.

Проверка данных ограничивает ввод: выпадающий список, только числа в диапазоне, только даты. Класс DataValidation из openpyxl.worksheet.datavalidation задает правило, а ws.add_data_validation привязывает его к листу. Это удобно для шаблонов, которые потом заполняют люди: в ячейку «Статус» нельзя будет ввести ничего, кроме значений из списка.

openpyxl, pandas или xlsxwriter

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

Типичные ошибки

• Старый формат xls не открывается. Библиотека работает только с xlsx, xlsm, xltx и xltm. Файл xls нужно пересохранить в Excel или прочитать другой библиотекой, например через pandas с движком xlrd.

• Формулы возвращают текст или None. Библиотека не вычисляет формулы, см. раздел про data_only.

• Пропали формулы после сохранения. Книгу открыли с data_only=True и сохранили — формулы заменились значениями.

• Ошибка доступа при сохранении. Файл открыт в Excel. Закройте его или сохраните под другим именем.

• Слетели макросы. Файл xlsm открыли без параметра keep_vba=True и пересохранили.

• Нумерация с нуля. Строки и столбцы в книге начинаются с 1, а индексы в кортеже из iter_rows — с 0. Путаница дает сдвиг на одну колонку.

• Пустые строки в max_row. Если в файле когда-то форматировали ячейки ниже таблицы, max_row будет больше реального числа строк с данными.

Автоматизация отчетов — одна из первых задач, которую поручают начинающему разработчику или аналитику; о том, какие навыки нужны в профессии, мы писали в материале «Профессия программист».

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

Нужен ли установленный Excel? Нет. Библиотека работает с файлом напрямую, скрипт запускается и на сервере с Linux.

Можно ли работать с Google Таблицами? Напрямую — нет. Таблицу можно скачать в xlsx, обработать и загрузить обратно, но для регулярной работы удобнее API Google Sheets.

Как прочитать только значения, без формул? Открыть книгу с data_only=True. Значения будут, если файл перед этим сохранялся в Excel.

Как сделать автоширину столбцов? Встроенной функции нет. Пройдите по столбцу, найдите самое длинное значение и задайте ширину чуть больше его длины.

Почему скрипт работает медленно? Чаще всего из-за обращения к ячейкам по одной в большом файле. Используйте iter_rows с values_only=True, а для больших таблиц — read_only.

Чем openpyxl отличается от pandas? Pandas — инструмент анализа данных, а эта библиотека — инструмент работы с самим файлом: ячейками, листами, оформлением. Pandas использует openpyxl под капотом для чтения xlsx.

Подробнее: курсы по Python на портале РБК Образование.