Главная > Блог > Web-разработка > Python и Flask >

Работа с файлами Excel в Python

23.03.2018

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

Документ электронной таблицы 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
'Здравствуй, мир!'

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

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.

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

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

Примеры формул Excel: =27+36, =А1+А2-АЗ, =SUM(А1:А5), =MAX(АЗ:А5), =(А1+А2)/АЗ.

Хранящуюся в ячейке формулу можно читать, как любое другое значение. Однако, если нужно получить результат расчета по формуле, а не саму формулу, то при вызове функции 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 или строка с координатами ячеек. Все строки и столбцы, расположенные выше и левее, будут заблокированы.

Значение атрибута freeze_panes	Заблокированные строки и столбцы
`sheet.freeze_panes = 'A2'`	Строка 1
`sheet.freeze_panes = 'B1'`	Столбец A
`sheet.freeze_panes = 'C1'`	Столбцы A и B
`sheet.freeze_panes = 'C2'`	Строка 1 и столбцы A и B
`sheet.freeze_panes = None`	Закрепленные области отсутствуют

Диаграммы

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

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

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

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

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-разработка • Модуль