Стиль ячеек в excel python

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

Содержание:

• Аспекты применения стилей модулем openpyxl.
• Cтили ячеек электронной таблицы.
  • Создания нового стиля на основе другого.
  • Цвета для шрифтов, фона, границ.
  • Применение стилей.
  • Горизонтальное и вертикальное выравнивание текста.
  • Оформление границ ячеек.
  • Заливка ячеек цветом и цвет текста.
• Именованные стили NamedStyle.
  • Создание именованного стиля.
• Встроенные в Excel стили.

Аспекты применения стилей модулем openpyxl.

Стили могут быть применены к следующим аспектам:

• font: устанавливает размер шрифта, цвет, стиль подчеркивания и т. д.
• fill: устанавливает шаблон или градиент цвета заливки ячейки.
• border: устанавливает стиль границы ячейки.
• alignment: устанавливает выравнивание ячейки.

Ниже приведены значения по умолчанию установленные модулем openpyxl:

from openpyxl.styles import (
                        PatternFill, Border, Side, 
                        Alignment, Font, GradientFill
                        )

# СТИЛЬ ШРИФТА
font = Font(
        name='Calibri',
        size=11,
        bold=False,
        italic=False,
        vertAlign=None,
        underline='none',
        strike=False,
        color='FF000000'
            )

# ЗАЛИВКА ЯЧЕЕК
fill = PatternFill(fill_type=None, fgColor='FFFFFFFF')

# ГРАНИЦЫ ЯЧЕЕК
border = Border(
            left=Side(border_style=None, color='FF000000'),
            right=Side(border_style=None, color='FF000000'),
            top=Side(border_style=None, color='FF000000'),
            bottom=Side(border_style=None, color='FF000000'),
            diagonal=Side(border_style=None, color='FF000000'),
            diagonal_direction=0, 
            outline=Side(border_style=None, color='FF000000'),
            vertical=Side(border_style=None, color='FF000000'),
            horizontal=Side(border_style=None, color='FF000000')
               )

# ВЫРАВНИВАНИЕ В ЯЧЕЙКАХ
alignment=Alignment(
                horizontal='general',
                vertical='bottom',
                text_rotation=0,
                wrap_text=False,
                shrink_to_fit=False,
                indent=0
                   )

Cтили ячеек электронной таблицы.

Существует два типа стилей: стили ячеек и именованные стили, также известные как шаблоны стилей.

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

Например:

>>> from openpyxl.styles import colors
>>> from openpyxl.styles import Font, Color
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> ws['A1'].value = 'Ячейка `A1`'
>>> ws['D4'].value = 'Ячейка `D4`'
# задаем стиль шрифта текста - цвет  ячейке
>>> ft = Font(color="FF0000")
# применяем стиль к ячейкам
>>> ws['A1'].font = ft
>>> ws['D4'].font = ft
# это изменение не сработает
>>> ws['D4'].font.italic = True 
# Если необходимо изменить шрифт, 
# то его необходимо переназначить новым стилем
ws['A1'].font = Font(color="FF0000", italic=True)
>>> wb.save('test.xlsx')

Создания нового стиля на основе другого.

Модуль openpyxl поддерживает копирование стилей.

Пример создания нового стиля на основе другого:

>>> from openpyxl.styles import Font
>>> from copy import copy
# задаем стиль
>>> ft1 = Font(name='Arial', size=14)
# копируем стиль
>>> ft2 = copy(ft1)
# а вот теперь на основе скопированного стиля 
# можно создать новый, изменив атрибуты 
>>> ft2.name = "Tahoma"
# имя шрифта первого стиля
>>> ft1.name
# 'Arial'

# имя шрифта нового стиля
>>> ft2.name
# 'Tahoma'

# размер остался как у первого
>>> ft2.size # copied from the
# 14.0

Цвета для шрифтов, фона, границ.

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

Цвета aRGB.

Цвета RGB устанавливаются с использованием шестнадцатеричных значений красного, зеленого и синего.

>>> from openpyxl.styles import Font
>>> font = Font(color="FF0000")

Альфа-значение теоретически относится к прозрачности цвета, но это не относится к стилям ячеек. Значение по умолчанию 00 будет добавлено к любому простому значению RGB:

>>> from openpyxl.styles import Font
>>> font = Font(color="00FF00")
>>> font.color.rgb
# '0000FF00'

Применение стилей.

Стили применяются непосредственно к ячейкам.

>>> from openpyxl import Workbook
>>> from openpyxl.styles import Font, PatternFill
>>> wb = Workbook()
>>> ws = wb.active
>>> c = ws['A1']
>>> c.value = 'Ячейка `A1`'
>>> c.font = Font(size=12)
# можно напрямую
>>> ws['A2'].value = 'Ячейка `A2`'
>>> ws['A2'].font = Font(size=12, bold=True)
>>> wb.save('test.xlsx')

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

Это ограничение формата файла:

>>> col = ws.column_dimensions['A']
>>> col.font = Font(bold=True)
>>> row = ws.row_dimensions[1]
>>> row.font = Font(underline="single")

Горизонтальное и вертикальное выравнивание текста.

Горизонтальное и вертикальное выравнивание в ячейках выставляется атрибутом ячейки .alignment и классом Alignment().

Пример горизонтального выравнивания текста:

>>> from openpyxl import Workbook
>>> from openpyxl.styles import Alignment
>>> wb = Workbook()
>>> ws = wb.active
>>> ws['A1'].value = 1500
>>> ws['A2'].value = 1500
>>> ws['A3'].value = 1500
# выравниваем текст в ячейках стилями
>>> ws['A1'].alignment = Alignment(horizontal='left')
>>> ws['A2'].alignment = Alignment(horizontal='center')
>>> ws['A3'].alignment = Alignment(horizontal='right')
# сохраняем и смотрим что получилось
>>> wb.save('test.xlsx')

Вертикальное выравнивание в основном применяется когда изменена высота строки или были объединены несколько ячеек.

Пример вертикального выравнивания данных в ячейке:

>>> from openpyxl import Workbook
>>> from openpyxl.styles import Alignment
>>> wb = Workbook()
>>> ws = wb.active
# объединим ячейки в диапазоне `B2:E2`
>>> ws.merge_cells('B2:E2')
# в данном случае крайняя верхняя-левая ячейка это `B2`
>>> megre_cell = ws['B2']
# запишем в нее текст
>>> megre_cell.value = 'Объединенные ячейки `B2 : E2`'
# установить высоту строки
>>> ws.row_dimensions[2].height = 30
# установить ширину столбца
>>> ws.column_dimensions['B'].width = 40
# выравнивание текста
>>> megre_cell.alignment = Alignment(horizontal="center", vertical="center")
# сохраняем и смотрим что получилось
>>> wb.save("test.xlsx")

Оформление границ ячеек.

Цвет и стиль границ/бордюров ячеек выставляется атрибутом ячейки .border и классом Border() совместно с классом Side().

При этом аргумент стиля границ ячеек border_style может принимать ОДИН из следующих значений: ‘dashDotDot’, ‘medium’, ‘dotted’, ‘slantDashDot’, ‘thin’, ‘hair’, ‘mediumDashDotDot’, ‘dashDot’, ‘double’, ‘mediumDashed’, ‘dashed’, ‘mediumDashDot’ и ‘thick’.

Пример стилизации границ одной ячейки:

>>> from openpyxl import Workbook
>>> from openpyxl.styles import Border, Side
>>> wb = Workbook()
>>> ws = wb.active
>>> cell = ws['B2']
# установить высоту строки
>>> ws.row_dimensions[2].height = 30
# установить ширину столбца
>>> ws.column_dimensions['B'].width = 40
# определим стили сторон
>>> thins = Side(border_style="medium", color="0000ff")
>>> double = Side(border_style="dashDot", color="ff0000")
# рисуем границы
>>> cell.border = Border(top=double, bottom=double, left=thins, right=thins) 
>>> wb.save("styled_border.xlsx")

Пример стилизации границ нескольких ячеек:

from openpyxl import Workbook
from openpyxl.styles import Border, Side

wb = Workbook()
ws = wb.active

# определим стили сторон
thins = Side(border_style="thin", color="0000ff")
double = Side(border_style="double", color="ff0000")

# начинаем заполнение области ячеек 10x10 данными
# при этом будем стилизировать границы области 
for r, row in enumerate(range(5, 15), start=1):
    for c, col in enumerate(range(5, 15), start=1):
        # это значение, которое будем записывать в ячейку
        val_cell = r*c
        # левая верхняя ячейка
        if r == 1 and c == 1:
            ws.cell(row=row, column=col, value=val_cell).border = Border(top=double, left=thins)
        # правая верхняя ячейка
        elif r == 1 and c == 10:
            ws.cell(row=row, column=col, value=val_cell).border = Border(top=double, right=thins)
        # верхние ячейки
        if r == 1 and c != 1 and c != 10:
            ws.cell(row=row, column=col, value=val_cell).border = Border(top=double)
        # левая нижняя ячейка
        elif r == 10 and c == 1:
            ws.cell(row=row, column=col, value=val_cell).border = Border(bottom=double, left=thins)
        # правая нижняя ячейка
        elif r == 10 and c == 10:
            ws.cell(row=row, column=col, value=val_cell).border = Border(bottom=double, right=thins)
        # нижние ячейки
        elif r == 10 and c != 1 and c != 10:
            ws.cell(row=row, column=col, value=val_cell).border = Border(bottom=double)
        # левые ячейки
        elif c == 1 and r != 1 and r != 10:
            ws.cell(row=row, column=col, value=val_cell).border = Border(left=thins)
        # правые ячейки
        elif c == 10 and r != 1 and r != 10:
            ws.cell(row=row, column=col, value=val_cell).border = Border(right=thins)
        else:
            # здесь ячейки просто заполняются данными
            ws.cell(row=row, column=col, value=val_cell)

# сохраняем и смотрим что получилось
wb.save("styled_border.xlsx")

Заливка ячеек цветом и цвет текста.

Цвет заливки ячеек выставляется атрибутом ячейки .fill и классом PatternFill().

Обязательный аргумент fill_type (по умолчанию равен None) класса PatternFill() может принимать значения:

• если fill_type='solid', то нужно обязательно указывать аргумент цвета заливки fgColor.
• следующие значения аргумента fill_type применяются самостоятельно (без аргумента fgColor) и представляют собой предустановленные цвета заливки : ‘darkHorizontal’, ‘lightDown’, ‘lightGray’, ‘darkDown’, ‘darkGrid’, ‘darkUp’, ‘darkGray’, ‘darkVertical’, ‘darkTrellis’, ‘mediumGray’, ‘lightVertical’, ‘lightTrellis’, ‘lightGrid’, ‘lightHorizontal’, ‘gray0625’, ‘lightUp’, ‘gray125’.

Внимание: если аргумент fill_type не указан, то fgColor не будет иметь никакого эффекта!

Пример заливки одной ячейки:

>>> from openpyxl import Workbook
>>> from openpyxl.styles import PatternFill, Font, Alignment
>>> wb = Workbook()
>>> ws = wb.active
# объединим ячейки в диапазоне `B2:E2`
>>> ws.merge_cells('B2:E2')
>>> megre_cell = ws['B2']
# запишем в нее текст
>>> megre_cell.value = 'Объединенные ячейки `B2 : E2`'
# установить высоту строки
>>> ws.row_dimensions[2].height = 30
# установить ширину столбца
>>> ws.column_dimensions['B'].width = 40
# заливка ячейки цветом
>>> megre_cell.fill = PatternFill('solid', fgColor="DDDDDD")
# шрифт и цвет текста ячейки 
>>> megre_cell.font = Font(bold=True, color='FF0000', name='Arial', size=14)
# ну и для красоты выровним текст
>>> megre_cell.alignment = Alignment(horizontal='center', vertical='center')
# сохраняем и смотрим что получилось
>>> wb.save("cell_color.xlsx")

Именованные стили NamedStyle.

В отличие от простых стилей ячеек, именованные стили изменяемы и используется для объединения в себе нескольких стилей, таких как шрифты, границы, выравнивание и т. д. Они имеют смысл, когда необходимо применить форматирование к множеству разных ячеек одновременно. Об именованных стилях можно думать как о классах CSS при оформлении HTML-разметки. Именованные стили регистрируются в рабочей книге.

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

Как только именованный стиль зарегистрирован в рабочей книге, на него можно ссылаться просто по имени.

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

>>> from openpyxl.styles import NamedStyle, Font, Border, Side
# создание переменной именованного стиля
>>> name_style = NamedStyle(name="highlight")
# применение стилей к созданной переменной
>>> name_style.font = Font(bold=True, size=20)
>>> bd = Side(style='thick', color="000000")
>>> name_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)

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

>>> wb.add_named_style(name_style)

Именованные стили также будут автоматически зарегистрированы при первом назначении их ячейке:

>>> ws['A1'].style = name_style

После регистрации стиля в рабочей книге, применять его можно только по имени:

>>> ws['D5'].style = 'highlight'

Встроенные стили в Excel.

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

Использование встроенных в Excel стилей здесь не рассматривается, так как при их применении могу возникать существенные искажения.

Документ электронной таблицы Excel называется рабочей книгой. Каждая книга может хранить некоторое количество листов. Лист, просматриваемый пользователем в данный момент, называется активным. Лист состоит из из столбцов (адресуемых с помощью букв, начиная с A) и строк (адресуемых с помощью цифр, начиная с 1).

Модуль OpenPyXL не поставляется вместе с Python, поэтому его предварительно нужно установить:

> pip install openpyxl

Чтение файлов Excel

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

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> type(wb)
<class 'openpyxl.workbook.workbook.Workbook'>
>>> wb.sheetnames
['Лист1', 'Лист2', 'Лист3']
>>> sheet = wb.active
>>> sheet
<Worksheet "Лист1">
>>> sheet['A1']
<Cell Лист1.A1>

А теперь небольшой скрипт:

import openpyxl

# читаем excel-файл
wb = openpyxl.load_workbook('example.xlsx')

# печатаем список листов
sheets = wb.sheetnames
for sheet in sheets:
    print(sheet)

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

# печатаем значение ячейки A1
print(sheet['A1'].value)
# печатаем значение ячейки B1
print(sheet['B1'].value)

Результат работы:

Лист1
Лист2
Лист3

2015-04-05 13:34:02
Яблоки

Как получить другой лист книги:

# получаем другой лист
sheet2 = wb['Лист2']
# печатаем значение ячейки A1
print(sheet2['A2'].value)

Как сделать лист книги активным:

# делаем третий лист активным
wb.active = 2

Как задать имя листа:

sheet.title = 'Третий лист'

Объект Cell имеет атрибут value, который содержит значение, хранящееся в ячейке. Объект Cell также имеет атрибуты row, column и coordinate, которые предоставляют информацию о расположении данной ячейки в таблице.

# получаем ячейку листа B2
cell = sheet['B2']
print('Строка: ' + str(cell.row))
print('Столбец: ' + cell.column)
print('Ячейка: ' + cell.coordinate)
print('Значение: ' + cell.value)

Строка: 2
Столбец: B
Ячейка: B2
Значение: Вишни

К отдельной ячейке можно также обращаться с помощью метода cell() объекта Worksheet, передавая ему именованные аргументы row и column. Первому столбцу или первой строке соответствует число 1, а не 0:

# получаем ячейку листа B2
cell = sheet.cell(row = 2, column = 2)
print(cell.value)

Вишни

Размер листа можно получить с помощью атрибутов max_row и max_column объекта Worksheet:

rows = sheet.max_row
cols = sheet.max_column

for i in range(1, rows + 1):
    string = ''
    for j in range(1, cols + 1):
        cell = sheet.cell(row = i, column = j)
        string = string + str(cell.value) + ' '
    print(string)

2015-04-05 13:34:02 Яблоки 73 
2015-04-05 03:41:23 Вишни 85 
2015-04-06 12:46:51 Груши 14 
2015-04-08 08:59:43 Апельсины 52 
2015-04-10 02:07:00 Яблоки 152 
2015-04-10 18:10:37 Бананы 23 
2015-04-10 02:40:46 Земляника 98

openpyxl.utils.column_index_from_string()

openpyxl.utils.get_column_letter()

>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> get_column_letter(1)
'A'
>>> get_column_letter(27)
'AA'
>>> column_index_from_string('A')
1
>>> column_index_from_string('AA')
27

Используя срезы объектов Worksheet, можно получить все объекты Cell, принадлежащие определенной строке, столбцу или прямоугольной области.

>>> sheet['A1':'C3']
((<Cell 'Лист1'.A1>, <Cell 'Лист1'.B1>, <Cell 'Лист1'.C1>),
 (<Cell 'Лист1'.A2>, <Cell 'Лист1'.B2>, <Cell 'Лист1'.C2>),
 (<Cell 'Лист1'.A3>, <Cell 'Лист1'.B3>, <Cell 'Лист1'.C3>))

for row in sheet['A1':'C3']:
    string = ''
    for cell in row:
        string = string + str(cell.value) + ' '
    print(string)

2015-04-05 13:34:02 Яблоки 73 
2015-04-05 03:41:23 Вишни 85 
2015-04-06 12:46:51 Груши 14 

Выводим значения второй колонки:

>>> sheet['B']
(<Cell 'Лист1'.B1>, <Cell 'Лист1'.B2>, <Cell 'Лист1'.B3>, <Cell 'Лист1'.B4>, ..., <Cell 'Лист1'.B7>)

for cell in sheet['B']:
    print(cell.value)

Яблоки
Вишни
Груши
Апельсины
Яблоки
Бананы
Земляника

Выводим строки с первой по третью:

>>> sheet[1:3]
((<Cell 'Лист1'.A1>, <Cell 'Лист1'.B1>, <Cell 'Лист1'.C1>),
 (<Cell 'Лист1'.A2>, <Cell 'Лист1'.B2>, <Cell 'Лист1'.C2>),
 (<Cell 'Лист1'.A3>, <Cell 'Лист1'.B3>, <Cell 'Лист1'.C3>))

for row in sheet[1:3]:
    string = ''
    for cell in row:
        string = string + str(cell.value) + ' '
    print(string)

2015-04-05 13:34:02 Яблоки 73 
2015-04-05 03:41:23 Вишни 85 
2015-04-06 12:46:51 Груши 14 

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

>>> list(sheet.rows)
[(<Cell 'Лист1'.A1>, <Cell 'Лист1'.B1>, <Cell 'Лист1'.C1>),
 (<Cell 'Лист1'.A2>, <Cell 'Лист1'.B2>, <Cell 'Лист1'.C2>),
 ..........
 (<Cell 'Лист1'.A6>, <Cell 'Лист1'.B6>, <Cell 'Лист1'.C6>),
 (<Cell 'Лист1'.A7>, <Cell 'Лист1'.B7>, <Cell 'Лист1'.C7>)]

for row in sheet.rows:
    print(row)

(<Cell 'Лист1'.A1>, <Cell 'Лист1'.B1>, <Cell 'Лист1'.C1>)
(<Cell 'Лист1'.A2>, <Cell 'Лист1'.B2>, <Cell 'Лист1'.C2>)
..........
(<Cell 'Лист1'.A6>, <Cell 'Лист1'.B6>, <Cell 'Лист1'.C6>)
(<Cell 'Лист1'.A7>, <Cell 'Лист1'.B7>, <Cell 'Лист1'.C7>)

>>> list(sheet.columns)
[(<Cell 'Лист1'.A1>, <Cell 'Лист1'.A2>, <Cell 'Лист1'.A3>, <Cell 'Лист1'.A4>, ..., <Cell 'Лист1'.A7>),
 (<Cell 'Лист1'.B1>, <Cell 'Лист1'.B2>, <Cell 'Лист1'.B3>, <Cell 'Лист1'.B4>, ..., <Cell 'Лист1'.B7>),
 (<Cell 'Лист1'.C1>, <Cell 'Лист1'.C2>, <Cell 'Лист1'.C3>, <Cell 'Лист1'.C4>, ..., <Cell 'Лист1'.C7>)]

for column in sheet.columns:
    print(column)

(<Cell 'Лист1'.A1>, <Cell 'Лист1'.A2>, <Cell 'Лист1'.A3>, <Cell 'Лист1'.A4>, ..., <Cell 'Лист1'.A7>)
(<Cell 'Лист1'.B1>, <Cell 'Лист1'.B2>, <Cell 'Лист1'.B3>, <Cell 'Лист1'.B4>, ..., <Cell 'Лист1'.B7>)
(<Cell 'Лист1'.C1>, <Cell 'Лист1'.C2>, <Cell 'Лист1'.C3>, <Cell 'Лист1'.C4>, ..., <Cell 'Лист1'.C7>)

Выводим значения всех ячеек листа:

for row in sheet.rows:
    string = ''
    for cell in row:
        string = string + str(cell.value) + ' '
    print(string)

2015-04-05 13:34:02 Яблоки 73 
2015-04-05 03:41:23 Вишни 85 
2015-04-06 12:46:51 Груши 14 
2015-04-08 08:59:43 Апельсины 52 
2015-04-10 02:07:00 Яблоки 152 
2015-04-10 18:10:37 Бананы 23 
2015-04-10 02:40:46 Земляника 98 

Выводим значения второй строки (индекс 1):

for cell in list(sheet.rows)[1]:
    print(str(cell.value))

2015-04-05 03:41:23
Вишни
85

Выводим значения второй колонки (индекс 1):

for row in sheet.rows:
    print(str(row[1].value))

Яблоки
Вишни
Груши
Апельсины
Яблоки
Бананы
Земляника

Запись файлов Excel

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.sheetnames
['Sheet']
>>> wb.create_sheet(title = 'Первый лист', index = 0)
<Worksheet "Первый лист">
>>> wb.sheetnames
['Первый лист', 'Sheet']
>>> wb.remove(wb['Первый лист'])
>>> wb.sheetnames
['Sheet']
>>> wb.save('example.xlsx')

Метод create_sheet() возвращает новый объект Worksheet, который по умолчанию становится последним листом книги. С помощью именованных аргументов title и index можно задать имя и индекс нового листа.

Метод remove() принимает в качестве аргумента не строку с именем листа, а объект Worksheet. Если известно только имя листа, который надо удалить, используйте wb[sheetname]. Еще один способ удалить лист — использовать инструкцию del wb[sheetname].

Не забудьте вызвать метод save(), чтобы сохранить изменения после добавления или удаления листа рабочей книги.

Запись значений в ячейки напоминает запись значений в ключи словаря:

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.create_sheet(title = 'Первый лист', index = 0)
>>> sheet = wb['Первый лист']
>>> sheet['A1'] = 'Здравствуй, мир!'
>>> sheet['A1'].value
'Здравствуй, мир!'

Заполняем таблицу 3×3:

import openpyxl

# создаем новый excel-файл
wb = openpyxl.Workbook()

# добавляем новый лист
wb.create_sheet(title = 'Первый лист', index = 0)

# получаем лист, с которым будем работать
sheet = wb['Первый лист']

for row in range(1, 4):
    for col in range(1, 4):
        value = str(row) + str(col)
        cell = sheet.cell(row = row, column = col)
        cell.value = value

wb.save('example.xlsx')

Можно добавлять строки целиком:

sheet.append(['Первый', 'Второй', 'Третий'])
sheet.append(['Четвертый', 'Пятый', 'Шестой'])
sheet.append(['Седьмой', 'Восьмой', 'Девятый'])

Стилевое оформление

Для настройки шрифтов, используемых в ячейках, необходимо импортировать функцию Font() из модуля openpyxl.styles:

from openpyxl.styles import Font

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

import openpyxl
from openpyxl.styles import Font

# создаем новый excel-файл
wb = openpyxl.Workbook()
# добавляем новый лист
wb.create_sheet(title = 'Первый лист', index = 0)
# получаем лист, с которым будем работать
sheet = wb['Первый лист']

font = Font(name='Arial', size=24, italic=True, color='FF0000')
sheet['A1'].font = font
sheet['A1'] = 'Здравствуй мир!'

# записываем файл
wb.save('example.xlsx')

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

import openpyxl
from openpyxl.styles import NamedStyle, Font, Border, Side

# создаем новый excel-файл
wb = openpyxl.Workbook()
# добавляем новый лист
wb.create_sheet(title = 'Первый лист', index = 0)
# получаем лист, с которым будем работать
sheet = wb['Первый лист']

# создаем именованный стиль
ns = NamedStyle(name='highlight')
ns.font = Font(bold=True, size=20)
border = Side(style='thick', color='000000')
ns.border = Border(left=border, top=border, right=border, bottom=border)

# вновь созданный именованный стиль надо зарегистрировать
# для дальнейшего использования
wb.add_named_style(ns)

# теперь можно использовать именованный стиль
sheet['A1'].style = 'highlight'

# записываем файл
wb.save('example.xlsx')

Добавление формул

Формулы, начинающиеся со знака равенства, позволяют устанавливать для ячеек значения, рассчитанные на основе значений в других ячейках.

sheet['B9'] = '=SUM(B1:B8)'

Эта инструкция сохранит =SUM(B1:B8) в качестве значения в ячейке B9. Тем самым для ячейки B9 задается формула, которая суммирует значения, хранящиеся в ячейках от B1 до B8.

Хранящуюся в ячейке формулу можно читать, как любое другое значение. Однако, если нужно получить результат расчета по формуле, а не саму формулу, то при вызове функции load_workbook() ей следует передать именованный аргумент data_only со значением True.

Настройка строк и столбцов

С помощью модуля OpenPyXL можно задавать высоту строк и ширину столбцов таблицы, закреплять их на месте (чтобы они всегда были видны на экране), полностью скрывать из виду, объединять ячейки.

Настройка высоты строк и ширины столбцов

Объекты Worksheet имеют атрибуты row_dimensions и column_dimensions, которые управляют высотой строк и шириной столбцов.

sheet['A1'] = 'Высокая строка'
sheet['B2'] = 'Широкий столбец'

sheet.row_dimensions[1].height = 70
sheet.column_dimensions['B'].width = 30

Атрибуты row_dimensions и column_dimensions представляют собой значения, подобные словарю. Атрибут row_dimensions содержит объекты RowDimensions, а атрибут column_dimensions содержит объекты ColumnDimensions. Доступ к объектам в row_dimensions осуществляется с использованием номера строки, а доступ к объектам в column_dimensions — с использованием буквы столбца.

Для указания высоты строки разрешено использовать целые или вещественные числа в диапазоне от 0 до 409. Для указания ширины столбца можно использовать целые или вещественные числа в диапазоне от 0 до 255. Столбцы с нулевой шириной и строки с нулевой высотой невидимы для пользователя.

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

Ячейки, занимающие прямоугольную область, могут быть объединены в одну ячейку с помощью метода merge_cells() рабочего листа:

sheet.merge_cells('A1:D3')
sheet['A1'] = 'Объединены двенадцать ячеек'

sheet.merge_cells('C5:E5')
sheet['C5'] = 'Объединены три ячейки'

Чтобы отменить слияние ячеек, надо вызвать метод unmerge_cells():

sheet.unmerge_cells('A1:D3')
sheet.unmerge_cells('C5:E5')

Закрепление областей

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

У объекта Worksheet имеется атрибут freeze_panes, значением которого может служить объект Cell или строка с координатами ячеек. Все строки и столбцы, расположенные выше и левее, будут заблокированы.

Диаграммы

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

1. создать объект Reference на основе ячеек в пределах выделенной прямоугольной области;
2. создать объект Series, передав функции Series() объект Reference;
3. создать объект Chart;
4. дополнительно можно установить значения переменных drawing.top, drawing.left, drawing.width, drawing.height объекта Chart, определяющих положение и размеры диаграммы;
5. добавить объект Chart в объект Worksheet.

Объекты Reference создаются путем вызова функции openpyxl.charts.Reference(), принимающей пять аргуменов:

1. Объект Worksheet, содержащий данные диаграммы.
2. Два целых числа, представляющих верхнюю левую ячейку выделенной прямоугольной области, в которых содержатся данные диаграммы: первое число задает строку, второе — столбец; первой строке соответствует 1, а не 0.
3. Два целых числа, представляющих нижнюю правую ячейку выделенной прямоугольной области, в которых содержатся данные диаграммы: первое число задает строку, второе — столбец.

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

# создаем новый excel-файл
wb = Workbook()
# добавляем новый лист
wb.create_sheet(title = 'Первый лист', index = 0)
# получаем лист, с которым будем работать
sheet = wb['Первый лист']

sheet['A1'] = 'Серия 1'
# это колонка с данными
for i in range(1, 11):
    cell = sheet.cell(row = i + 1, column = 1)
    cell.value = i * i

# создаем диаграмму
chart = BarChart()
chart.title = 'Первая серия данных'
data = Reference(sheet, min_col = 1, min_row = 1, max_col = 1, max_row = 11)
chart.add_data(data, titles_from_data = True)

# добавляем диаграмму на лист
sheet.add_chart(chart, 'C2')

# записываем файл
wb.save('example.xlsx')

Аналогично можно создавать графики, точечные и круговые диаграммы, вызывая методы:

• openpyxl.chart.LineChart()
• openpyxl.chart.ScatterChart()
• openpyxl.chart.PieChart()

Поиск:
Excel • MS • Python • Web-разработка • Модуль