Выравнивание и объединение ячеек в openpyxl: merge_cells

Шапка из пятого урока уже синяя и жирная, но название «Прайс-лист» всё ещё сидит в одной ячейке A1, а длинные описания товаров вылезают за границы столбца. В Excel эта проблема решается двумя кнопками: «Объединить и поместить в центре» и «Переносить текст». У openpyxl те же возможности — стиль Alignment и метод merge_cells. Обе работать умеют, но у объединения есть характер: три ловушки, на которых спотыкаются даже опытные.

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

Выравнивание: Alignment

Выравнивание — такой же стиль, как Font из прошлого урока: объект Alignment присваивается атрибуту alignment. Два главных параметра: horizontal — по горизонтали со значениями left, center, right и vertical — по вертикали: top, center, bottom.

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

ws["A1"] = "Прайс-лист"
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")

print("По горизонтали:", ws["A1"].alignment.horizontal)
print("Вертикально:", ws["A1"].alignment.vertical)

По горизонтали: center
Вертикально: center

Выравнивание никак не влияет на значение — только на отрисовку. vertical="center" особенно заметен на высоких строках: без него текст прилипает к нижнему краю, с ним — сидит по середине. Третий рабочий параметр — wrap_text, он заслуживает отдельного примера.

Перенос текста: wrap_text

Длинный текст в узком столбце без переноса либо обрезается, либо наезжает на соседние ячейки — смотря что правее. wrap_text=True включает перенос по словам: текст остаётся в своей ячейке, а строка визуально растёт в высоту.

Пара слов о том, где перенос нужен, а где вредит. Колонки с описаниями, комментариями и адресами просят wrap_text как милости: без него таблица разваливается. Числам и датам перенос не нужен никогда — они короткие, а выровненный вправо денежный столбец с переносом выглядит как ошибка вёрстки. Правило одного взгляда: текстовые колонки длиннее двадцати символов — кандидаты на перенос, остальным достаточно ширины, о которой поговорим в седьмом уроке.

Тонкость, которую замечают не сразу: перенос происходит на границе слов, и Excel подбирает высоту строки сам — если ты не задал её руками. Вредный совет наоборот: задать высоту вручную «с запасом» и забыть про неё при изменении текста. Если высотой не управляешь сознательно, оставляй её на откуп Excel: wrap_text работает и без настройки высоты.

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

ws["A2"] = "Длинное описание товара, которое не влезает в узкий столбец"
ws["A2"].alignment = Alignment(wrap_text=True, vertical="top")

print("Перенос включён:", ws["A2"].alignment.wrap_text)

Перенос включён: True

Перенос и выравнивание часто идут парой: описания товаров — wrap_text=True с прижимом к верху vertical="top", шапки и итоги — по центру. Параметры не исключают, а дополняют друг друга — их можно задать все разом в одном объекте Alignment. Вот самая ходовая комбинация — «по центру и с переносом».

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

ws["B2"] = "Комментарий клиента"
ws["B2"].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)

print(ws["B2"].alignment.horizontal, ws["B2"].alignment.wrap_text)

center True

Осталось научиться растягивать заголовок на всю таблицу.

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

Метод ws.merge_cells("A1:D1") склеивает диапазон в одно поле: четыре ячейки первой строки от A до D становятся единым заголовком. Значение после объединения живёт только в верхней левой ячейке — в A1.

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

ws.merge_cells("A1:D1")            # объединили четыре ячейки
ws["A1"] = "Отчёт за март"         # значение - только в верхней левой
ws["A1"].alignment = Alignment(horizontal="center")

print("A1:", ws["A1"].value)
print("C1:", ws["C1"].value)       # "скрытая" ячейка - None

A1: Отчёт за март
C1: None

Кроме строки-диапазона merge_cells понимает и числа: ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=4) — то же объединение A1:D1, но с координатами, которые удобно считать в цикле. Числовая форма незаменима, когда размер заголовка зависит от данных: три колонки сегодня, семь завтра. Список всех объединений листа живёт в ws.merged_cells.ranges — это множество, и по его длине удобно проверять, сколько полей уже склеено.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.merge_cells(start_row=1, start_column=1, end_row=2, end_column=3)
ws["A1"] = "Шапка на два ряда"

print(ws["A1"].value)
print(len(ws.merged_cells.ranges))

Шапка на два ряда
1

Получился прямоугольник два на три: A1:C2, шесть ячеек в одном поле. Многорядные объединения — это подзаголовки над парами колонок, общие названия для групп столбцов «Цены» и «Остатки». Правило живой ячейки прежнее: значение — в верхней левой, здесь тоже в A1.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.merge_cells("A1:D1")
ws.merge_cells("A3:B3")

print(len(ws.merged_cells.ranges))   # два объединения на листе

2

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.merge_cells("A1:B1")

try:
    ws["B1"] = "сюда нельзя"       # B1 - MergedCell
    print("записалось")
except AttributeError:
    print("AttributeError: ячейка B1 внутри объединения только для чтения")

AttributeError: ячейка B1 внутри объединения только для чтения

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "Заголовок"
ws["B1"] = "мусор"
ws.merge_cells("A1:B1")            # openpyxl чистит скрытые ячейки

print("B1 после объединения:", ws["B1"].value)

B1 после объединения: None

Заголовок на всю ширину — лишь половина пользы объединения. Вторая половина — подзаголовки над группами колонок: над «Цена» и «Скидка» клеится общий «Деньги», над «Остаток» и «Резерв» — «Склад». Такая двухэтажная шапка читается замечательно, но именно она чаще всего ломает автоматическую обработку, поэтому держи её подальше от строк данных — а лучше дублируй смысл плоскими именами колонок вроде «Цена, руб.».

Разъединение: unmerge_cells

Обратная операция — ws.unmerge_cells с тем же диапазоном. Ячейки становятся обычными, значение остаётся в верхней левой, а все прочие снова принимают значения.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.merge_cells("A1:D1")
ws["A1"] = "Шапка"
ws.unmerge_cells("A1:D1")          # вернули как было

ws["C1"] = "теперь можно писать"   # ячейки снова отдельные
print("C1:", ws["C1"].value)
print("A1:", ws["A1"].value)

C1: теперь можно писать
A1: Шапка

unmerge тоже требует точный диапазон: разъединять нужно ровно то, что объединяли — «A1:D1», а не «A1:C1». Скриптам, которые правят чужие файлы, диапазон удобно взять из того же ws.merged_cells.ranges, а не угадывать.

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

Последний пункт списка — частый источник сюрпризов в скриптах, которые «дочищают» чужие файлы: скопировал строку вместе с объединённой шапкой — получил на листе ещё одно объединение, о котором не просил. Перед массовыми правками листа полезно печатать ws.merged_cells.ranges и смотреть, с чем имеешь дело: неожиданное множество объединений объясняет странности лучше долгой отладки.

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

И ещё одна причина не склеивать данные: файлы живут дольше своих генераторов. Через полгода коллега захочет отсортировать твой прайс или прогнать его через pandas — и объединённые ячейки в теле превратятся в его проблему. Шапка склеена и красива, данные плоские и машинно-читаемые: так файл работает и для человека, и для кода.

Первая программа: шапка прайса с заголовком

Теперь финальная картинка: заголовок на всю ширину таблицы по центру, под ним — шапка и данные. Всё вместе: merge, Alignment, стили из прошлого урока.

Обрати внимание на порядок операций в этой программе: сначала объединение, потом оформление, потом данные. Так безопаснее всего: живая ячейка A1 существует с самого начала, и ни один append не рискует наступить на объединённый диапазон. Приучи себя читать подобные скрипты сверху вниз — порядок строк здесь и есть порядок работы.

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
import os

wb = Workbook()
ws = wb.active

ws.merge_cells("A1:D1")
top = ws["A1"]                     # у объединения - одна живая ячейка
top.value = "Прайс-лист: компьютерный отдел"
top.font = Font(bold=True, size=14, color="FFFFFF")
top.fill = PatternFill(start_color="1F4E79", end_color="1F4E79", fill_type="solid")
top.alignment = Alignment(horizontal="center", vertical="center")

ws.append(["Товар", "Цена", "Штук"])
ws.append(["Монитор", 9500, 7])

wb.save("price-l6.xlsx")

print("A1:", ws["A1"].value)
print("Строка данных:", [c.value for c in ws[2]])

os.remove("price-l6.xlsx")

A1: Прайс-лист: компьютерный отдел
Строка данных: ['Товар', 'Цена', 'Штук', None]

Заголовок растянулся на четыре колонки и центрируется по ним — ровно то, что в Excel зовётся «Объединить и поместить в центре». В списке данных четвёртым элементом пришёл None: строка с товарами лежит под объединённой ячейкой, и её четвёртый столбец пуст. Стили применились к top — верхней левой ячейке, этого достаточно: заливка и шрифт объединённого поля берутся из неё.

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

Что дальше

Заголовок по центру, перенос длинных описаний, знание трёх ловушек MergedCell — таблица выглядит по-взрослому. Осталась последняя деталь внешнего вида: длинные названия по-прежнему обрезаются узкими столбцами. Урок 7 настраивает ширины столбцов и высоты строк — включая автоматический подбор по самой длинной строке, и внешний вид прайса закрыт полностью.

Объединённый диапазон — одно поле с одной живой ячейкой: значение в верхней левой, всё остальное — MergedCell без значений.