Как объединить ячейки excel в python

В материале рассказывается о методах модуля openpyxl, которые отвечают за такие свойства электронной таблицы как объединение/разъединение ячеек таблицы, а также особенности стилизации объединенных ячеек.

Содержание:

• Объединение/слияние нескольких ячеек и их разъединение модулем openpyxl.
• Оформление/стилизация разъединенных ячеек модулем openpyxl.

Объединение/слияние нескольких ячеек и их разъединение.

Модуль openpyxl поддерживает слияние/объединение нескольких ячеек, что очень удобно при записи в них текста, с последующим выравниванием. При слиянии/объединении ячеек, все ячейки, кроме верхней левой, удаляются с рабочего листа. Для переноса информации о границах объединенной ячейки, граничные ячейки объединенной ячейки, создаются как ячейки слияния, которые всегда имеют значение None.

Информацию о форматировании объединенных ячеек смотрите ниже, в подразделе «Оформление объединенных ячеек«.

Пример слияния/объединения ячеек с модулем openpyxl:

>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
# объединить ячейки, находящиеся 
# в диапазоне `B2 : E2`
>>> ws.merge_cells('B2:E2')
# записываем текст в оставшуюся
# после объединения ячейку 'B2'
>>> ws['B2'] = 'Объединенные ячейки `B2 : E2`'
# сохраняем документ и смотрим что получилось
>>> wb.save("merge.xlsx")

При открытии сохраненного документа и перехода по любой ячейки из диапазона 'B2:E2' видно, что этот диапазон ячеек стал единым. Дополнительно исчезла возможность добавить значения к другим ячейкам этого диапазона.

Теперь разъединим ячейки, которые были объединены ранее, для этого загрузим сохраненный документ.

Пример разъединения ячеек с модулем openpyxl:

>>> from openpyxl import load_workbook
# загружаем сохраненный документ
>>> wb = load_workbook(filename = 'merge.xlsx')
>>> ws = wb.active
# теперь разъединим ячейки, 
# в диапазоне `B2 : E2`
>>> ws.unmerge_cells('B2:E2')
# сохраняем документ и смотрим что получилось
>>> wb.save("merge.xlsx")

При открытии сохраненного документа и перехода по любой ячейки из диапазона 'B2:E2' видно, что текст, записанный ранее в ячейку 'B2' принадлежит только ей. Дополнительно появилась возможность добавить значения к другим ячейкам диапазона 'B2:E2'.

Методы слияния ws.merge_cells() и разъединения ws.unmerge_cells() ячеек, кроме диапазона/среза ячеек могут принимать аргументы:

• start_row: строка, с которой начинается слияние/разъединение.
• start_column: колонка, с которой начинается слияние/разъединение.
• end_row: строка, которой заканчивается слияние/разъединение.
• end_column: колонка, которой заканчивается слияние/разъединение.

Пример:

# объединение ячеек
>>> ws.merge_cells(start_row=2, start_column=2, end_row=4, end_column=6)
# посмотрите что получилось
>>> wb.save("merge.xlsx")
# и разъединение ячеек
>>> ws.unmerge_cells(start_row=2, start_column=2, end_row=4, end_column=6)

Оформление/стилизация разъединенных ячеек модулем openpyxl.

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

Пример форматирования объединенной ячейки:

>>> from openpyxl import Workbook
>>> from openpyxl.styles import Border, Side, PatternFill, Font, 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
# определим стили границ
>>> thins = Side(border_style="thin", color="0000ff")
>>> double = Side(border_style="double", color="ff0000")
# НАЧИНАЕМ ФОРМАТИРОВАНИЕ:
# границы объединенной ячейки
>>> megre_cell.border = Border(top=double, left=thins, right=thins, bottom=double)
# заливка ячейки
>>> 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("styled_megre.xlsx")

Дополнительно смотрите:

• Оформление/стилизация ячеек документа XLSX модулем openpyxl.
• Изменение размеров строки/столбца модулем openpyxl.

Документ электронной таблицы 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-разработка • Модуль

You all must have worked with Excel at some time in your life and must have felt the need for automating some repetitive or tedious task. Don’t worry in this tutorial we are going to learn about how to work with Excel using Python, or automating Excel using Python. We will be covering this with the help of the Openpyxl module.

Getting Started

Openpyxl is a Python library that provides various methods to interact with Excel Files using Python. It allows operations like reading, writing, arithmetic operations, plotting graphs, etc.

This module does not come in-built with Python. To install this type the below command in the terminal.

pip install openpyxl

Reading from Spreadsheets

To read an Excel file you have to open the spreadsheet using the load_workbook() method. After that, you can use the active to select the first sheet available and the cell attribute to select the cell by passing the row and column parameter. The value attribute prints the value of the particular cell. See the below example to get a better understanding. 

Note: The first row or column integer is 1, not 0.

Dataset Used: It can be downloaded from here.

Example:

Output:

Name

Reading from Multiple Cells

There can be two ways of reading from multiple cells. 

Method 1: We can get the count of the total rows and columns using the max_row and max_column respectively. We can use these values inside the for loop to get the value of the desired row or column or any cell depending upon the situation. Let’s see how to get the value of the first column and first row.

Example:

Output:

Total Rows: 6
Total Columns: 4

Value of first column
Name
Ankit
Rahul
Priya
Nikhil
Nisha

Value of first row
Ankit  B.Tech CSE 4 

Method 2: We can also read from multiple cells using the cell name. This can be seen as the list slicing of Python.

Output:

Name Course
Ankit  B.Tech
Rahul M.Tech
Priya MBA
Nikhil B.Tech
Nisha B.Tech

Refer to the below article to get detailed information about reading excel files using openpyxl.

• Reading an excel file using Python openpyxl module

Writing to Spreadsheets

First, let’s create a new spreadsheet, and then we will write some data to the newly created file. An empty spreadsheet can be created using the Workbook() method. Let’s see the below example.

Example:

Output:

After creating an empty file, let’s see how to add some data to it using Python. To add data first we need to select the active sheet and then using the cell() method we can select any particular cell by passing the row and column number as its parameter. We can also write using cell names. See the below example for a better understanding.

Example:

Output:

Refer to the below article to get detailed information about writing to excel.

• Writing to an excel file using openpyxl module

Appending to the Spreadsheet

In the above example, you will see that every time you try to write to a spreadsheet the existing data gets overwritten, and the file is saved as a new file. This happens because the Workbook() method always creates a new workbook file object. To write to an existing workbook you must open the file with the load_workbook() method. We will use the above-created workbook.

Example:

Output:

We can also use the append() method to append multiple data at the end of the sheet.

Example:

Output:

Arithmetic Operation on Spreadsheet

Arithmetic operations can be performed by typing the formula in a particular cell of the spreadsheet. For example, if we want to find the sum then =Sum() formula of the excel file is used.

Example:

Output:

Refer to the below article to get detailed information about the Arithmetic operations on Spreadsheet.

• Arithmetic operations in excel file using openpyxl

Adjusting Rows and Column

Worksheet objects have row_dimensions and column_dimensions attributes that control row heights and column widths. A sheet’s row_dimensions and column_dimensions are dictionary-like values; row_dimensions contains RowDimension objects and column_dimensions contains ColumnDimension objects. In row_dimensions, one can access one of the objects using the number of the row (in this case, 1 or 2). In column_dimensions, one can access one of the objects using the letter of the column (in this case, A or B).

Example:

Output:

Merging Cells

A rectangular area of cells can be merged into a single cell with the merge_cells() sheet method. The argument to merge_cells() is a single string of the top-left and bottom-right cells of the rectangular area to be merged.

Example:

Output:

Unmerging Cells

To unmerge cells, call the unmerge_cells() sheet method.

Example:

Output:

Setting Font Style

To customize font styles in cells, important, import the Font() function from the openpyxl.styles module.

Example:

Output:

Refer to the below article to get detailed information about adjusting rows and columns.

• Adjusting rows and columns of an excel file using openpyxl module

Plotting Charts

Charts are composed of at least one series of one or more data points. Series themselves are comprised of references to cell ranges. For plotting the charts on an excel sheet, firstly, create chart objects of specific chart class( i.e BarChart, LineChart, etc.). After creating chart objects, insert data in it, and lastly, add that chart object in the sheet object.

Example 1:

Output:

Example 2:

Output:

Refer to the below articles to get detailed information about plotting in excel using Python.

• Plotting charts in excel sheet using openpyxl module | Set  1
• Plotting charts in excel sheet using openpyxl module | Set  2
• Plotting charts in excel sheet using openpyxl module | Set 3

Adding Images

For the purpose of importing images inside our worksheet, we would be using openpyxl.drawing.image.Image. The method is a wrapper over PIL.Image method found in PIL (pillow) library. Due to which it is necessary for the PIL (pillow) library to be installed in order to use this method.

Image Used:

Example:

Output:

Refer to the below article to get detailed information about adding images.

• Openpyxl – Adding Image

Some More Functionality of Excel using Python

• How to delete one or more rows in excel using Openpyxl?
• Trigonometric operations in excel file using openpyxl
• How to copy data from one excel sheet to another
• How to Automate an Excel Sheet in Python?

Время прочтения: 5 мин.

Ответ приходит в виде openpyxl, библиотеки Python, которая позволяет вам читать файлы Excel и управлять ими. Помимо базовых задач, таких как поиск и изменение значений ячеек, вы также можете агрегировать диапазоны ячеек, добавлять стиль к своим шрифтам и даже строить графики, используя всего несколько строк кода.

Загрузка рабочей книги и листа

Сначала нужно установить библиотеку openpyxl. Чтобы загрузить рабочую книгу из существующего файла Excel, сначала нужно импортировать модуль load_workbook, затем создать экземпляр load_workbook(), указав путь к файлу Excel в качестве единственного аргумента. Также нужно будет указать лист, к которому необходимо получить доступ из рабочей книги. Выполнение команды печати в переменной sheet вернёт имя активного листа.

#импортируем модули
from openpyxl import Workbook, load_workbook

#создаем экземпляр с именем пути к файлу excel
wb = load_workbook("/users/user1/downloads/example.xlsx")

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

print(sheet)

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

print(sheet["A1"].value)
print(sheet["A2"].value)
print(sheet["B3"].value)

Изменение значения ячейки Можно изменить значение ячейки, обратившись к значению, а затем указав новое значение с помощью оператора equal. Необходимо запустить метод save(), чтобы изменения вступили в силу. Не забывайте всегда закрывать файл в Excel перед внесением каких-либо изменений, чтобы Python не выдавал ошибку.

#изменяем значение ячейки
sheet["A2"] = "TestTest"

#сохраняем файл
wb.save("/users/user1/downloads/example1.xlsx")

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

#создаем новый лист
wb.create_sheet("New_sheet")

#возвращаем список листов
print(wb.sheetnames)

Создание нового файла

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

#создаем новую рабочую книгу
new_wb = Workbook()

#выбираем активный лист
ws = new_wb.active

#переименовываем активный лист
ws.title  = "New_sheet2"

#добавляем данные в активный лист
ws.append(["1","2","3","4"])

#сохраняем
new_wb.save("/users/user1/downloads/example3.xlsx")

Каждый раз, когда я использую метод append(), он добавляет новую строку на лист.

#заполняем несколько строк
ws.append(["5","6","7","8"])
ws.append(["9","10","11","12"])
ws.append(["13"])

# сохраняем
new_wb.save("/users/user1/downloads/example3.xlsx")

Вставка и удаление строк

Можно вставить новую строку в лист, используя метод insert_rows(), а затем указав номер строки, по которой нужно выполнить вставку.

#вставляем новую строку
ws.insert_rows(1)

#сохраняем
new_wb.save("/users/user1/downloads/example3.xlsx")

Таким же образом можно удалять строки, используя метод delete_rows() и указав позицию строки.

#удаляем строку
ws.delete_rows(1)

#сохраняем
new_wb.save("/users/user1/downloads/example3.xlsx")

Объединение ячеек и разъединение ячеек

Объединить ячейки так же просто, как использовать метод merge_cells() и указать диапазон.

#объединяем ячейки
ws.merge_cells("A1:A2")

#сохраняем
new_wb.save("/users/user1/downloads/example3.xlsx")

Можно отключить их с помощью метода unmerge_cells().

#разъединяем ячейки
ws.unmerge_cells("A1:A2")

#сохраняем
new_wb.save("/users/user1/downloads/example3.xlsx")

Вставка и удаление столбцов

Можно вставлять столбцы в любую позицию, указав номер в методе insert_cols().

#вставляем столбец
ws.insert_cols(2)

#сохраняем
new_wb.save("/users/user1/downloads/example3.xlsx")

Метод delete_cols() удалит указанные столбцы.

#удаляем столбцы
ws.delete_cols(1,2)

#сохраняем
new_wb.save("/users/user1/downloads/example3.xlsx")

Копирование и перемещение значений ячеек

Можно скопировать значения в любом диапазоне ячеек и переместить их в новую позицию с помощью метода move_range(). Он принимает три аргумента: диапазон перемещаемых данных, затем строки, которые могут быть положительным (количество строк вниз) или отрицательным (количество строк вверх) целым числом. Последний аргумент — cols, который также является либо положительным (количество столбцов справа), либо отрицательным (количество столбцов слева) целым числом. Перемещу диапазон A1:B1 на четыре строки вниз и на один столбец вправо.

#диапазон перемещения
ws.move_range("A1:B1",rows=4,cols=1)

#сохраняем
new_wb.save("/users/user1/downloads/example3.xlsx")

А теперь некоторые функции разберу на примере.

Кейс – делаю расчет в Excel по формуле

Многие сталкивались с ситуацией, когда было множество готовых excel—файлов и в них необходимо провести одинаковую операцию. Сейчас рассмотрю пример, когда нужно применить одинаковую формулу во множестве файлов. Может быть 5,10,100 файлов по которым нужно рассчитать, например, сумму значений в двух столбцах. С помощью python и библиотеки openpyxl можно избавиться от ручного применения формул автоматизировав этот процесс. Ниже представлен код, который по очереди к каждому excel-файлу из папки применяет формулу и записывает новый excel-файл с результатом расчёта по формуле.

import openpyxl
import os

path = r'C:UsersUser1' # Задаем путь к папке с excel-файлами, в которых будем рассчитывать формулу

files = [i for i in os.listdir(path) if 'xlsx' in i]  # В этой папке выбираем названия только excel-файлов и создаем из них список

for i in files: # Идем по каждому excel-файлу
    
    x = openpyxl.open(i) # Открываем этот файл

    sheet = x.active # Выбираем лист для работы

    cell = sheet.cell(row= sheet.max_row+1, column = 2) # Создаем ячейку с координатами, где показываем результаты расчета
    cell.value = "=СУММ(A2:B{})".format(sheet.max_row)  # Пишем формулу в ячейку

    cell.font = cell.font.copy(bold = True) # Изменяем стиль шрифта

    x.save('formulas_{}.xlsx'.format(i.split('.')[0])) # сохраняем результат в файл

В моём примере расположения столбцов для расчёта в каждом файле одинаковое (столбец A и столбец B), но количество строк может быть разным, соответственно результат по формуле тоже будет располагаться в разной строке под данными. Чтобы не высчитывать количество строк в каждом файле, применяю метод sheet.max_row, когда задаю ячейку для записи результата +1 строка в переменную cell, чтобы результат на следующей строке, под данными.

Надеюсь, читатели, которые только погружаются в Python, смогли получить общее представление о том, как работать с файлами Excel на Python. С openpyxl вы cможете сделать форматирование, создавать сводные таблицы и диаграммы, делать фильтрацию и сортировку, агрегировать значения ячеек, изменять шрифт, перемещение данные на новый лист. С этими возможностями можно ознакомиться в официальной документации.

##############################################################################
#
# A simple example of merging cells with the XlsxWriter Python module.
#
# SPDX-License-Identifier: BSD-2-Clause
# Copyright 2013-2023, John McNamara, jmcnamara@cpan.org
#
import xlsxwriter


# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook("merge1.xlsx")
worksheet = workbook.add_worksheet()

# Increase the cell size of the merged cells to highlight the formatting.
worksheet.set_column("B:D", 12)
worksheet.set_row(3, 30)
worksheet.set_row(6, 30)
worksheet.set_row(7, 30)


# Create a format to use in the merged range.
merge_format = workbook.add_format(
    {
        "bold": 1,
        "border": 1,
        "align": "center",
        "valign": "vcenter",
        "fg_color": "yellow",
    }
)


# Merge 3 cells.
worksheet.merge_range("B4:D4", "Merged Range", merge_format)

# Merge 3 cells over two rows.
worksheet.merge_range("B7:D8", "Merged Range", merge_format)


workbook.close()