Работа с Excel-файлами с помощью Openpyxl в языке Python
Основы работы с openpyxl
Библиотека openpyxl позволяет создавать, читать и изменять файлы формата .xlsx. Она поддерживает форматирование, формулы, диаграммы и большие объёмы данных.
Установка и импорт
Установите библиотеку с помощью pip:
pip install openpyxl
Openpyxl python (openpyxl для работы с excel в python)
Импортируйте модули:
import openpyxl from openpyxl import Workbook, load_workbook from openpyxl.styles import Font, Alignment, PatternFill from openpyxl.chart import BarChart, Reference
Основное решение: создание файла и запись данных
Как создать новый файл Excel, записать данные в ячейки и сохранить?
# Создание рабочей книги wb = Workbook() ws = wb.active ws.title = \"Данные\" # Запись значений ws[\"A1\"] = \"Имя\" ws[\"B1\"] = \"Возраст\" ws.append([\"Анна\", 28]) ws.append([\"Иван\", 35]) # Сохранение wb.save(\"example.xlsx\")
Как прочитать данные из существующего файла?
wb = load_workbook(\"example.xlsx\")
ws = wb.active
for row in ws.iter_rows(min_row=1, max_row=3, values_only=True):
print(row)(\"Имя\", \"Возраст\") (\"Анна\", 28) (\"Иван\", 35)
Типичная ошибка: при открытии файла с кириллицей может возникнуть ошибка кодировки. Решение – указать encoding при открытии?
На самом деле openpyxl корректно обрабатывает Unicode, но проблема может быть в путях к файлу. Используйте абсолютные пути или проверьте, что файл не повреждён.
Вариант 1: чтение данных с пропуском пустых строк
Как пропускать пустые строки при чтении?
ws = load_workbook(\"example.xlsx\").active
for row in ws.iter_rows(values_only=True):
if any(cell is not None for cell in row):
print(row)Вариант 2: запись с форматированием
Как выделить заголовки жирным шрифтом и задать цвет фона?
from openpyxl.styles import Font, PatternFill bold_font = Font(bold=True) header_fill = PatternFill(start_color=\"FFC000\", end_color=\"FFC000\", fill_type=\"solid\") ws[\"A1\"].font = bold_font ws[\"B1\"].font = bold_font ws[\"A1\"].fill = header_fill ws[\"B1\"].fill = header_fill
Вариант 3: работа с несколькими листами
Как создать несколько листов и переключаться между ними?
wb = Workbook() ws1 = wb.active ws1.title = \"Первый\" ws2 = wb.create_sheet(\"Второй\") ws2[\"A1\"] = \"Данные второго листа\" wb.save(\"multi_sheet.xlsx\")
Распространённая проблема: при попытке открыть файл, созданный другой библиотекой (xlsxwriter), openpyxl может не распознать формат. Решение – использовать единый формат .xlsx и проверять, что файл не повреждён.
Расширенные примеры использования openpyxl
1. Преобразование дат и работа с формулами
from datetime import datetime wb = Workbook() ws = wb.active # Запись даты (автоматически преобразуется в серийный номер Excel) ws[\"A1\"] = datetime.now() ws[\"B1\"] = \"=TODAY()\" # формула ws[\"C1\"] = \"=A1+1\" # формула с датой # Установка числового формата ячейки для отображения даты from openpyxl.styles import numbers ws[\"A1\"].number_format = \"DD.MM.YYYY\" wb.save(\"dates_and_formulas.xlsx\")
В Excel ячейка A1 покажет текущую дату, B1 – дату на момент открытия, C1 – следующий день.
2. Объединение ячеек и выравнивание
ws.merge_cells(\"A1:D1\") cell = ws[\"A1\"] cell.value = \"Заголовок\" cell.alignment = Alignment(horizontal=\"center\", vertical=\"center\") wb.save(\"merged.xlsx\")
При объединении данных других ячеек (кроме верхней левой) будут потеряны. Разъединение: ws.unmerge_cells(\"A1:D1\").
3. Условное форматирование
from openpyxl.formatting.rule import CellIsRule, FormulaRule
# Правило: выделить ячейки, у которых значение больше 50
ws.conditional_formatting.add(
\"A1:A10\",
CellIsRule(operator=\"greaterThan\", formula=[\"50\"], fill=PatternFill(start_color=\"FF0000\", end_color=\"FF0000\", fill_type=\"solid\"))
)
# Правило с формулой: если ячейка больше среднего
ws.conditional_formatting.add(
\"B1:B10\",
FormulaRule(formula=[\"B1>AVERAGE($B$1:$B$10)\"], fill=PatternFill(start_color=\"00FF00\", end_color=\"00FF00\", fill_type=\"solid\"))
)
wb.save(\"conditional.xlsx\")4. Создание диаграммы (столбчатая)
# Подготовка данных
for i in range(1, 6):
ws.cell(row=i, column=1, value=i)
ws.cell(row=i, column=2, value=i*10)
# Создание столбчатой диаграммы
chart = BarChart()
chart.type = \"col\"
chart.title = \"Пример\"
chart.y_axis.title = \"Значения\"
# Указываем данные: столбец B (значения) и столбец A (категории)
data = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=5)
cats = Reference(ws, min_col=1, max_col=1, min_row=2, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
ws.add_chart(chart, \"D1\")
wb.save(\"chart.xlsx\")На листе появится диаграмма в области D1:L15.
5. Работа с большими файлами (WriteOnly режим)
from openpyxl import Workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet()
# Добавление строк без хранения всего документа в памяти
ws.append([\"Строка\", 1])
for i in range(2, 100000):
ws.append([f\"Данные {i}\", i])
wb.save(\"big_file.xlsx\")Примечание: в WriteOnly режиме нельзя читать или изменять уже добавленные данные, подходит только для записи.
6. Защита листа и книги
# Защита листа паролем ws.protection.password = \"12345\" ws.protection.sheet = True # защитить структуру листа # Защита книги (структуры) wb.security.workbookPassword = \"pass\" wb.security.lockStructure = True wb.save(\"protected.xlsx\")
При открытии файла в Excel будет запрошен пароль на изменение структуры.
7. Вставка изображения
from openpyxl.drawing.image import Image img = Image(\"logo.png\") img.anchor = \"C3\" # левый верхний угол ячейки C3 ws.add_image(img) wb.save(\"image.xlsx\")
Изображение вставляется как объект, его размеры можно менять через img.width, img.height.