Как использовать библиотеку OpenPyxl в Python для работы с Excel

Когда в Excel приходится работать с большими объемами обновляемых данных, управлять ими вручную становится неэффективно.

Тут на помощь приходит OpenPyxl — библиотека Python, разработанная для чтения и записи файлов Excel в формате .xlsx. Аналогичные библиотеки встречаются и в других языках программирования. Например, в Java используют OpenXLS.

С помощью OpenPyxl можно:

• Создавать и редактировать рабочие книги и листы.
• Читать и записывать данные ячеек.
• Форматировать и применять стили к содержимому ячеек.
• Объединять ячейки и управлять их размерами.
• Работать с формулами и строить диаграммы.

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

Проверьте, что установлен Python

Перед работой с OpenPyxl убедитесь, что у вас на компьютере установлен Python. Гайд по его установке вы можете найти здесь.

Проверить, что Python установлен можно через командную строку (cmd) или PowerShell в Windows и через терминал в Linux. Введите команду:

python3 --version

Возможная ошибка: Python version mismatch — ошибка несовместимости. Может возникнуть, если у вас установлены несколько версий Python.

Важно: OpenPyxl поддерживает только Python 3 и выше.

Устанавливаем pip

Далее нужно выяснить, установлен ли pip. Это менеджер пакетов Python, который нужен для установки OpenPyxl.

• Проверяем наличие pip на компьютере:

pip --version

или:

python -m pip --version

Возможная ошибка

pip not recognized as an internal or external command — не найдена команда. Это может означать, что pip не установлен или не добавлен в переменную окружения PATH.

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

• Введите команду:

python -m ensurepip -upgrade

• Убедимся, что установлена последняя версия pip:

python -m pip install --upgrade pip

Как добавить в PATH

Windows

• Перейдите в «Панель управления» -> «Система» -> «Дополнительные параметры системы» -> «Переменные среды».
• В разделе «Системные переменные» найдите переменную Path, выберите ее и нажмите «Изменить».
• Добавьте путь к папке Scripts, которая находится в каталоге установки Python (например, C:\Python39\Scripts).
• Нажмите OK, чтобы сохранить изменения.

Linux

• Откройте терминал и добавьте путь к папке Python (обычно это /usr/local/bin/python3) в файл .bashrc или .zshrc:

export PATH="/usr/local/bin:$PATH"

• После этого выполните:

source ~/.bashrc   # Для bash
source ~/.zshrc    # Для zsh

Переходим к установке OpenPyxl

Для установки библиотеки OpenPyxl в Python введите в командной строке (Windows) или терминале (macOS, Linux или в самой среде разработки) команду:

pip install openpyxl

Если используете виртуальное окружение

Виртуальное окружение — это изолированная среда для работы с Python-проектами. Это позволяет избежать конфликтов зависимостей между проектами. 

• Виртуальное окружение можно создать с помощью команды:

python -m venv myenv

• Далее нужно активировать окружение:

Windows

.\myenv\Scripts\activate

Linux

source myenv/bin/activate

• Установите OpenPyxl в виртуальном окружении с помощью команды установки.

После установки OpenPyxl проверьте, что все работает. 

• Запустите Python в терминале:

python

Это откроет интерактивный режим Python.

• Внутри Python (терминале или консоли) выполните команды:

import openpyxl
print(openpyxl.__version__)

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

Чем полезен OpenPyxl

При работе с OpenPyxl мы не запускаем Excel, а создаем и манипулируем файлами Excel (.xlsx) через Python. После создания файла можно открыть его в Excel, но сам процесс манипуляции файлами выполняется без него.

Перед работой с OpenPyxl в среде разработки нужно импортировать библиотеку:

import openpyxl

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

1. Как создать новый Excel-файл

Чтобы создать новый Excel-файл, нужно сначала создать рабочую книгу (Workbook), а затем добавлять листы и записывать данные в ячейки. 

Workbook в библиотеке openpyxl — это класс, который используется для создания нового Excel-файла. 

Для работы с Workbook, нужно импортировать специальные библиотеки.

• Перед созданием нового файла импортируйте класс Workbook:

from openpyxl import Workbook

• Для работы с существующим файлом нужно использовать load_workbook

from openpyxl import load_workbook

Можно обойтись и импортом библиотеки openpyxl, но тогда перед функциями придется писать openpyxl. Вместо load_workbook будет openpyxl.load_workbook.

Пример:

from openpyxl import Workbook

# Создаем новую рабочую книгу (файл)
wb = openpyxl.Workbook()

# Получаем активный лист (по умолчанию создается один лист)
sheet = wb.active

# Устанавливаем имя листа
sheet.title = "Лист1"

# Записываем данные в ячейки
sheet['A1'] = "Привет"
sheet['A2'] = "Мир"

# Сохраняем файл на диск
wb.save('новый_файл.xlsx')

В методе save() можно указать либо только имя файла, либо полный путь к нему.

— Если указано только имя файла, как в примере («новый_файл.xlsx»), файл будет сохранен в текущей рабочей директории программы (папке, из которой запускается скрипт).

— Если нужно сохранить файл в определенной папке, то в кавычках стоит указать полный путь к файлу, например:

wb.save(r"C:\Users\User\Documents\новый_файл.xlsx")

Символ r перед строкой пути используют, чтобы избежать проблем с символами обратного слэша (\).

Итак, мы создали новый файл new_file.xlsx, добавили лист с данными в ячейки A1 и A2 и сохранили файл.

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

Для открытия существующего файла используется метод load_workbook(). 

Пример:

wb = load_workbook('file.xlsx')

Эта строка откроет файл file.xlsx.

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

1. Получение активного листа

Активный лист — это тот, который открыт по умолчанию, когда вы загружаете или создаете файл. Получить его можно с помощью метода active.

Пример:

from openpyxl import load_workbook

# Открываем существующий файл
wb = load_workbook('file.xlsx')

# Получаем активный лист
sheet = wb.active

# Выводим название активного листа
print(f"Активный лист: {sheet.title}")

2. Создание нового листа

Можно создать новый лист с определенным именем при помощи функции create_sheet().

Пример:

from openpyxl import Workbook

# Создаем новую книгу
wb = Workbook()

# Или загружаем готовую
wb = load_workbook('file.xlsx')

# Добавляем новый лист
new_sheet = wb.create_sheet(title="Новый Лист")

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

3. Переименование листа

Чтобы изменить название листа, просто присвойте новое имя в его свойство title.

Пример:

# Открываем файл
wb = load_workbook("file_with_new_sheet.xlsx")

# Получаем лист по имени
sheet = wb["Новый Лист"]

# Меняем название листа
sheet.title = "Переименованный Лист"

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

4. Удаление листа

Удалить лист можно с помощью метода remove().

Пример:

# Открываем файл
wb = load_workbook("renamed_sheet.xlsx")

# Получаем лист для удаления
sheet_to_remove = wb["Переименованный Лист"]

# Удаляем лист
wb.remove(sheet_to_remove)

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

Работа с данными

1. Чтение данных из одной ячейки

Вы можете получить значение из конкретной ячейки, указав ее координаты (например, A1).

Пример:

from openpyxl import load_workbook

# Открываем существующий файл
wb = load_workbook("file.xlsx")

# Получаем активный лист
sheet = wb.active

# Читаем значение ячейки A1
value = sheet["A1"].value
print(f"Значение ячейки A1: {value}")

2. Перебор всех ячеек в диапазоне

Для чтения значений нескольких ячеек можно использовать перебор по диапазону.

Пример:

from openpyxl import load_workbook

# Открываем файл
wb = load_workbook("file.xlsx")
sheet = wb.active

# Перебираем ячейки в диапазоне A1:C3
for row in sheet["A1:C3"]:  # Указываем диапазон
    for cell in row:
        print(f"Ячейка {cell.coordinate}: {cell.value}")

3. Запись данных в ячейки

Для записи данных в ячейки используйте объект листа и укажите координаты ячейки.

Пример:

from openpyxl import Workbook

# Создаем новую книгу
wb = Workbook()
sheet = wb.active

# Записываем текст и числа
sheet["A1"] = "Текст"  # Записываем текст
sheet["B1"] = 123      # Записываем число
sheet["C1"] = 45.67    # Записываем дробное число

# Сохраняем файл
wb.save("file_with_data.xlsx")

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

Вы можете задать ширину столбцов и высоту строк для улучшения читаемости данных.

Пример:

# Устанавливаем ширину столбца A = 20 и высоту строки 1 = 30
sheet.column_dimensions["A"].width = 20
sheet.row_dimensions[1].height = 30

5. Настройка стилей ячеек (жирный шрифт, цвет текста, выравнивание)

Для форматирования используйте модуль openpyxl.styles.

Пример:

from openpyxl.styles import Font, Alignment

# Применяем жирный шрифт
sheet["A1"].font = Font(bold=True)

# Изменяем цвет текста (например, синий)
sheet["B1"].font = Font(color="0000FF")

# Выравнивание текста по центру
sheet["C1"].alignment = Alignment(horizontal="center", vertical="center")

Работа с таблицами и формулами

1. Создание таблицы данных

Для создания таблицы данных используйте объект Table из модуля openpyxl.worksheet.table.

Пример:

from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

# Создаем новую книгу и активный лист
wb = Workbook()
sheet = wb.active

# Заполняем данные
data = [
    ["Имя", "Возраст", "Город"],
    ["Иван", 25, "Москва"],
    ["Анна", 30, "Санкт-Петербург"],
    ["Петр", 35, "Новосибирск"]
]

for row in data:
    sheet.append(row)

# Определяем диапазон для таблицы
table = Table(displayName="MyTable", ref="A1:C4")

# Настраиваем стиль таблицы
style = TableStyleInfo(
    name="TableStyleMedium9", showFirstColumn=False,
    showLastColumn=False, showRowStripes=True, showColumnStripes=True
)
table.tableStyleInfo = style

# Добавляем таблицу на лист
sheet.add_table(table)

# Сохраняем файл
wb.save("file_with_table.xlsx")

2. Использование формул

Для работы с формулами просто задайте в ячейке формулу как строку (например, =SUM(A1:A10)).

Пример:

from openpyxl import Workbook

# Создаем новую книгу
wb = Workbook()
sheet = wb.active

# Заполняем данные
sheet["A1"] = 10
sheet["A2"] = 20
sheet["A3"] = 30

# Добавляем формулу
sheet["A4"] = "=SUM(A1:A3)"  # Сумма ячеек A1, A2 и A3

# Сохраняем файл
wb.save("file_with_formula.xlsx")

Примеры задач с применением OpenPyxl

Генерация отчета с динамическими данными

1. Создаем новую книгу и активный лист

Используем Workbook для создания нового файла Excel. Активный лист выбирается автоматически с помощью sheet = wb.active.

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
sheet = wb.active
sheet.title = "Отчет"

2. Добавляем заголовок отчета

Записываем текст в ячейку A1. Делаем шрифт жирным и увеличиваем размер с помощью Font.

sheet["A1"] = "Отчет по продажам"
sheet["A1"].font = Font(bold=True, size=14)

3. Создаем таблицу с данными

Вручную заполняем строки. Используем формулы Excel для расчета столбца «Сумма».

data = [
    ["Товар", "Количество", "Цена", "Сумма"],
    ["Телефон", 10, 20000, "=B2*C2"],
    ["Ноутбук", 5, 50000, "=B3*C3"],
    ["Планшет", 7, 15000, "=B4*C4"]
]

for row in data:
    sheet.append(row)

4. Добавляем итоговую строку

Записываем формулу в ячейку D6 для расчета суммы столбца «Сумма». Стилизуем ячейку «Итого».

sheet["D6"] = "=SUM(D2:D4)"
sheet["C6"] = "Итого:"
sheet["C6"].font = Font(bold=True)

5. Сохраняем отчет в файл

wb.save("sales_report.xlsx")

Объединение данных из нескольких файлов Excel

1. Создаем список файлов для объединения

Прописываем список путей к Excel-файлам.

files = ["file1.xlsx", "file2.xlsx"]

2. Создаем итоговую книгу

Используем Workbook для новой книги. Настраиваем первый лист и добавляем заголовки.

from openpyxl import Workbook
wb_result = Workbook()
sheet_result = wb_result.active
sheet_result.title = "Объединенные данные"
sheet_result.append(["Имя", "Возраст", "Город"])

3. Читаем данные из файлов

Используем load_workbook для открытия каждого файла.

Пропускаем первую строку (заголовки) и добавляем данные в итоговый файл.

from openpyxl import load_workbook

for file in files:
    wb = load_workbook(file)
    sheet = wb.active
    for row in sheet.iter_rows(min_row=2, values_only=True):  # Пропускаем заголовок
        sheet_result.append(row)

4. Сохраняем итоговый файл

wb_result.save("merged_data.xlsx")

Автозаполнение таблицы

1. Создаем новую книгу, задаем активный лист

from openpyxl import Workbook
wb = Workbook()
sheet = wb.active

2. Добавляем заголовок таблицы

Используем append для добавления строки заголовков.

Sheet.append([«Число», «Квадрат», «Куб»])

3. Заполняем таблицу данными

С помощью цикла вычисляем квадрат и куб для чисел от 1 до 10. Добавляем каждую строку в таблицу.

for i in range(1, 11):
    sheet.append([i, i**2, i**3])

4. Настраиваем ширину столбцов

Используем column_dimensions для изменения ширины.

sheet.column_dimensions["A"].width = 10
sheet.column_dimensions["B"].width = 15
sheet.column_dimensions["C"].width = 15

5. Сохраняем файл

wb.save("auto_filled_table.xlsx")

Документация по OpenPyxl и частые ошибки

Документация OpenPyxl — основной источник информации о функциях, методах и возможностях библиотеки. Ее разделы включают:

• Основные понятия: книга (Workbook), лист (Worksheet), ячейка (Cell).
• Методы для работы с файлами, листами, таблицами.
• Инструменты для оптимизации производительности

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

1. Попытка открыть файл, который уже используется другой программой

Если Excel-файл открыт в другой программе (например, MS Excel), то при попытке открыть его с помощью библиотеки OpenPyxl, вы можете столкнуться с ошибками, связанными с доступом к файлу. Поэтому закройте все сторонние программы, которые могут использовать файл.

2. Изменения не сохраняются в файле, потому что их не внесли

После внесения изменений не забывайте вызвать метод save(), чтобы сохранить эти изменения.

wb.save('file.xlsx')

3. Использование несуществующих диапазонов или ячеек

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

if "A1" in sheet:
    print(sheet["A1"].value)
else:
    print("Ячейка отсутствует.")

4. Загрузка файлов с неподдерживаемым форматом

Если вы пытаетесь открыть файл в старом формате Excel .xls с помощью библиотеки OpenPyxl, которая поддерживает только формат .xlsx (и .xlsm для файлов с макросами), то возникнет ошибка, поскольку OpenPyxl не поддерживает работу с форматом .xls. 

Конвертируйте файл:

• Откройте файл .xls в Excel или другой программе, поддерживающей этот формат.
• Сохраните его как .xlsx.