Работа с файлами Excel в Python
Документ электронной таблицы Excel называется рабочей книгой. Каждая книга может хранить некоторое количество листов. Лист, просматриваемый пользователем в данный момент, называется активным. Лист состоит из из столбцов (адресуемых с помощью букв, начиная с A) и строк (адресуемых с помощью цифр, начиная с 1).
Модуль OpenPyXL не поставляется вместе с Python, поэтому его предварительно нужно установить:
Чтение файлов Excel
>>> import openpyxl >>> wb = openpyxl.load_workbook(‘example.xlsx’) >>> type(wb) >>> wb.sheetnames [‘Лист1’, ‘Лист2’, ‘Лист3’] >>> sheet = wb.active >>> sheet >>> sheet[‘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
Объект 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 , принадлежащие определенной строке, столбцу или прямоугольной области.
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
Выводим значения второй колонки:
for cell in sheet['B']: print(cell.value)
Яблоки Вишни Груши Апельсины Яблоки Бананы Земляника
Выводим строки с первой по третью:
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 .
for row in sheet.rows: print(row)
for column in sheet.columns: print(column)
Выводим значения всех ячеек листа:
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))
Выводим значения второй колонки (индекс 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) >>> 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 'Здравствуй, мир!'
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')
Добавление формул
Формулы, начинающиеся со знака равенства, позволяют устанавливать для ячеек значения, рассчитанные на основе значений в других ячейках.
Эта инструкция сохранит =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_dimension s и 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')
Аналогично можно создавать графики, точечные и круговые диаграммы, вызывая методы:
