OpenPyXL для работы с Excel в Python: разбираемся на практике

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

Python помогает решить эти проблему с помощью библиотек автоматизации работы с файлами Excel. Самая популярная из них — OpenPyXL.

Содержание

• Что такое OpenPyXL
• Как установить библиотеку
• Как читать и сохранять табличные файлы
• Как работать с листами Excel
• Особенности чтения и записи данных в ячейки
• Как перебрать строки и столбцы
• Стилизация таблиц в OpenPyXL
• Как использовать формулы
• Полезные сценарии использования OpenPyXL
• Совместимость и подводные камни

Что такое OpenPyXL

OpenPyXL — это библиотека на Python, которая помогает работать с файлами Excel с помощью кода в IDE (среда разработки). Она позволяет:

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

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

Как установить OpenPyXL

Перед тем как начать работать с OpenPyXL, библиотеку нужно установить. Для этого используется pip — стандартная программа для установки пакетов в Python.

Читайте также:

Как установить PIP для Python: инструкция и основные команды

Сначала убедимся, что Python и pip уже есть на компьютере. Для этого введём в терминале или командной строке команды проверки версий:

python --version
pip --version

Если обе команды возвращают номера версий, значит, всё в порядке. Теперь установим пакет openpyxl, который добавляет в Python поддержку работы с файлами Excel:

pip install openpyxl

После этого библиотека готова к использованию. Это можно проверить в интерактивной консоли Python или в любой IDE:

import openpyxl

Если ошибок не появилось, установка прошла успешно.

Начинаем работать

Для создания пустого файла Excel в OpenPyXL есть класс Workbook. Напишем код:

from openpyxl import Workbook

workbook = Workbook()
workbook.save("example.xlsx")

Мы создали файл и сохранили его с помощью метода save(). В скобках указывается наименование документа. Если открыть новый файл в Excel, там будет один пустой лист.

Чаще на практике требуется работать с уже существующим файлом, а не создавать его с нуля. Для загрузки документа используется функция load_workbook():

from openpyxl import load_workbook

workbook = load_workbook("example.xlsx")

Теперь переменная workbook ссылается на файл, с которым можно выполнять любые действия: читать данные, добавлять новые листы, менять значения в ячейках и так далее.

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

Когда мы создаём новую книгу, OpenPyXL автоматически добавляет один лист с именем Sheet. Чтобы к нему обратиться, напишем код:

page = workbook.active

Свойство active возвращает текущий рабочий лист.

Чтобы создать новый лист в существующем файле, используется метод create_sheet(). В скобках указывается имя нового листа:

workbook.create_sheet("Отчёт")
workbook.save("example.xlsx")

Теперь в файле будет два листа — Sheet, который создаётся по умолчанию, и Отчёт. К каждому из них можно обращаться по отдельности:

page = workbook["Отчёт"]

Если мы хотим удалить лист из файла, то используем метод remove(). В скобках указываем название переменной с таблицей и название листа:

workbook.remove(workbook["Sheet"])

После изменений сохраним файл:

workbook.save("example.xlsx")

Чтение и запись данных в ячейки

Основные задачи при работе с Excel — записывать и читать значения в таблице. В OpenPyXL доступ к ячейке осуществляется двумя способами: по адресу и через координаты. Рассмотрим оба варианта.

По адресу

Адрес ячейки в Excel состоит из буквы и числа: буква обозначает столбец, а число — строку. Например, A1 — это ячейка в первом столбце и первой строке, B3 — ячейка во втором столбце и третьей строке.

Запишем данные в ячейку А1 таблицы:

page["A1"] = "Привет, Excel!"

Теперь в первой строке и первом столбце появится текст.

Прочитать значение из ячейки можно так:

value = page["A1"].value
print(value)

На экран будет выведено её содержимое.

Через координаты

Координаты ячейки в Excel — это номер строки и номер столбца. Если адрес ячейки — B1, то её координаты — это (1, 2): первая строка и второй столбец.

Попробуем добавить число в ячейку, обратившись к ней по координатам:

page.cell(row=2, column=3, value=100)

Теперь в ячейку на пересечении второй строки и третьего столбца записано число 100. Координаты — удобный способ работать с ячейками, когда мы перебираем их циклами для чтения или добавления значений.

Перебор строк и столбцов

Когда данных много, работать с ними по одной ячейке неудобно. В OpenPyXL есть методы для перебора строк и столбцов.

Чтобы пройтись по строкам, используем цикл for и метод iter_rows:

for row in page.iter_rows(min_row=1, max_row=3, values_only=True):
    print(row)

Цикл будет последовательно получать группы ячеек — по одной строке за раз. Внутри iter_rows мы указываем, какие строки нужно прочитать:

• Параметр min_row задаёт начальную строку, а max_row — последнюю.
• Параметр values_only=True означает, что вместо объектов ячеек метод вернёт только содержащиеся в них данные без информации о стиле, координатах или других свойствах.

В теле цикла print(row) выводит каждую строку в виде кортежа, где каждый элемент — это значение отдельной ячейки.

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

for col in page.iter_cols(min_col=1, max_col=2, values_only=True):
    print(col)

С помощью этих методов информацию из таблиц Excel можно превратить в различные типы данных Python: списки, словари, кортежи — и работать с ними дальше.

Стилизация таблиц в OpenPyXL

В OpenPyXL доступно управление внешним видом таблицы: изменение ширины столбцов, настройка шрифтов и цветов, а также объединение ячеек. Это помогает оформить файл Excel, не открывая его в табличном редакторе.

Изменение ширины и высоты столбцов и строк

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

page.column_dimensions["A"].width = 20

Теперь ширина первого столбца будет равна 20 символам. Схожим образом настраивается высота строк:

page.row_dimensions[3].height = 25

Настройка шрифта и цвета

Для оформления текста используется класс Font, а для заливки ячеек — PatternFill.

В OpenPyXL цвета задаются в HEX-системе RGB. Каждый цвет записывается шестнадцатеричным кодом из шести символов, где два символа отвечают за красный канал, два — за зелёный и два — за синий. Учить кодировку цветов наизусть не обязательно, можно быстро найти их на HTML Color Picker на W3Schools.

Выделим в таблице первую строку: ячейки сделаем с синей заливкой, а текст — белым:

from openpyxl.styles import Font, PatternFill

page["A1"].font = Font(bold=True, color="FFFFFF")  # белый жирный текст
page["A1"].fill = PatternFill("solid", fgColor="4F81BD")  # синяя заливка ячейки

Объединение ячеек

Иногда требуется объединить несколько ячеек в одну, например для заголовка. Для этого используется метод merge_cells()

page.merge_cells("A1:C1")
page["A1"] = "Отчёт за месяц"

Он принимает диапазон ячеек для объединения. В нашем случае — от A1 до C1. Обратите внимание, что ячейка получает адрес по первому значению — A1.

Формулы и вычисления в OpenPyXL

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

Как записать формулу

Формула в OpenPyXL задаётся как строка, начиная со знака =. Напишем код для сложения значений двух ячеек:

page["A1"] = 10
page["A2"] = 20
page["A3"] = "=SUM(A1:A2)"

В ячейке A3 появится формула =SUM(A1:A2). Если открыть файл в Excel, программа автоматически посчитает сумму и покажет результат — 30.

Чтение формул

Попробуем прочесть значение ячейки с формулой:

print(page["A3"].value)

Получим результат в виде строки =SUM(A1:A2), а не число 30. OpenPyXL хранит формулу, но не результат вычисления.

Если требуется провести анализ данных, то придётся воспользоваться другими инструментами, например библиотекой Pandas или Matplotlib.

Полезные сценарии использования OpenPyXL

OpenPyXL применяют в разных задачах. Посмотрим несколько примеров, где библиотека экономит время.

Импорт данных из Excel в Python

Чтобы работать с данными в Python, необходимо перенести их из табличного файла. Сделать это можно с помощью знакомого цикла for и метода iter_rows. Посмотрим на пример их использования.

Представим, что у нас есть таблица акционеров компании, где в первом столбце записано имя человека, а во втором — фамилия. Наша задача — написать каждому из них приглашение на собрание акционеров. Это можно сделать вручную, но адресатов так много, что придётся очень долго копировать и перепроверять данные во избежание ошибок. Чтобы сэкономить время, напишем код, который будет читать таблицу построчно, собирать из двух ячеек одно обращение и выводить его на экран.

from openpyxl import load_workbook

# открываем Excel-файл
book = load_workbook("people.xlsx")
page = book.active

# перебираем строки с именами и фамилиями
for row in page.iter_rows(min_row=2, values_only=True):
    name, surname = row

    # пропускаем пустые строки
    if not name or not surname:
        continue

    message = f"Уважаемый(ая) {name} {surname}, приглашаем вас на собрание акционеров!"
    print(message)

Файл загружается знакомым load_workbook, и чтение данных идёт построчно. Первая строка обычно бывает заголовком таблицы, поэтому чтение начинается со второй. В сам цикл мы добавили оператор continue для пропуска строк, в которых нет имени или фамилии.

Внутри цикла формируется строка с приглашением, которая выводится на печать. Теперь можем не переживать, что пропустим кого-то из таблицы.

Генерация отчёта с использованием Pandas

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

Логика работы будет такой:

• Откроем файл в Pandas, превратим данные в DataFrame (структура данных библиотеки) и проведём необходимый расчёт. Готовую таблицу сохраним в новый файл Excel report.xlsx.
• Откроем его в OpenPyXL и приведём в порядок: укажем шрифт заголовков, добавим заливку и увеличим ширину первого столбца для удобства чтения.

Чтобы это сделать, напишем код:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

# Читаем исходный файл через Pandas
df = pd.read_excel("sales.xlsx")

# Создаём итоговый столбец с результатом расчёта
df["Стоимость"] = df["Количество"] * df["Цена"]

# Записываем результат в новый файл Excel
df.to_excel("report.xlsx", index=False)

# Открываем файл через OpenPyXL
wb = load_workbook("report.xlsx")
ws = wb.active

# Задаём шрифт, заливку и ширину первого столбца
ws["A1"].font = Font(bold=True)
ws["A1"].fill = PatternFill("solid", fgColor="CCCCCC")
ws.column_dimensions["A"].width = 25

# Сохраняем изменения
wb.save("report.xlsx")

Читайте также:

Работаем с Pandas: основные понятия и реальные данные

Совместимость и подводные камни

Работая с файлами Excel через OpenPyXL, важно понимать, какие форматы поддерживаются и какие есть ограничения. Это поможет избежать ошибок и сэкономить время.

Поддерживаемые форматы

• .xlsx — основной формат, с которым работает OpenPyXL. Это современный стандарт Excel, начиная с версии 2007.
• .xlsm — файлы с макросами. OpenPyXL открывает и сохраняет их, но не умеет работать с самими макросами.

Ограничения и особенности

• Формулы. Как мы уже обсуждали, OpenPyXL не пересчитывает формулы, а лишь сохраняет их в файле. Итоговые значения появятся только при открытии документа в Excel. Поэтому делать сложные расчёты с помощью библиотеки не получится.
• Стили и оформление. OpenPyXL поддерживает большинство базовых стилей (шрифты, цвета, заливки), но не все сложные элементы, доступные в табличном редакторе. Например, условное форматирование реализовано частично, поэтому лучше всего делать его в OpenPyXL.

Практические советы

• Если у вас старый файл .xls, сохраните его в Excel как .xlsx перед работой.
• Для сложных задач — например, с анализом данных — используйте комбинацию: Pandas для обработки данных и OpenPyXL для оформления и экспорта файла.
• Проверяйте результат в Excel, особенно если используете формулы или сложное форматирование.

Что в итоге

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

Советы для новичков:

• Начинайте с простых практических задач: создание файла, заполнение ячеек, сохранение документа и так далее.
• Не бойтесь официальной документации: примеры кода понятны даже на английском, а перевод можно сделать онлайн-сервисами.
• Комбинируйте OpenPyXL с Pandas для анализа.
• Для построения сложных визуализаций комбинируйте OpenPyXL с Matplotlib. OpenPyXL умеет создавать стандартные графики Excel, но поддерживает только базовые типы диаграмм: линейные, столбчатые, круговые и гистограммы. В Matplotlib можно работать с точной настройкой шкал, менять отдельные элементы графика, комбинировать несколько систем координат, накладывать слои и создавать сложные визуализации.