Электронные таблицы Excel - это интуитивно понятный и удобный способ манипулирования большими наборами данных без какой-либо предварительной технической подготовки. По этому, это один из форматов, с которым, в какой-то момент времени, вам придется иметь дело. Часто будут стоять задачи по извлечению каких-то данных из базы данных или файла логов в электронную таблицу Excel, или наоборот, преобразовывать электронную таблицу Excel в какую-либо более удобную программную форму, примеров этому масса.
Модуль openpyxl - это библиотека Python для чтения/записи форматов Office Open XML (файлов Excel 2010) с расширениями xlsx/xlsm/xltx/xltm.
openpyxl в виртуальное окружение.Модуль openpyxl размещен на PyPI, поэтому установка относительно проста.
# создаем виртуальное окружение, если нет $ python3 -m venv .venv --prompt VirtualEnv # активируем виртуальное окружение $ source .venv/bin/activate # ставим модуль openpyxl (VirtualEnv):~$ python3 -m pip install -U openpyxl
Чтобы начать работу с модулем openpyxl, нет необходимости создавать файл электронной таблицы в файловой системе. Нужно просто импортировать класс Workbook и создать его экземпляр. Рабочая книга всегда создается как минимум с одним рабочим листом, его можно получить, используя свойство Workbook.active:
>>> from openpyxl import Workbook # создаем книгу >>> wb = Workbook() # делаем единственный лист активным >>> ws = wb.active
Новые рабочие листы можно создавать, используя метод Workbook.create_sheet():
# вставить рабочий лист в конец (по умолчанию) >>> ws1 = wb.create_sheet("Mysheet") # вставить рабочий лист в первую позицию >>> ws2 = wb.create_sheet("Mysheet", 0) # вставить рабочий лист в предпоследнюю позицию >>> ws3 = wb.create_sheet("Mysheet", -1)
Листам автоматически присваивается имя при создании. Они нумеруются последовательно (Sheet, Sheet1, Sheet2, …). Эти имена можно изменить в любое время с помощью свойства Worksheet.title:
>>> ws.title = "NewPage"
Цвет фона вкладки с этим заголовком по умолчанию белый. Можно изменить этот цвет, указав цветовой код RRGGBB для атрибута листа Worksheet.sheet_properties.tabColor:
>>> ws.sheet_properties.tabColor = "1072BA"
Рабочий лист можно получить, используя его имя в качестве ключа экземпляра созданной книги Excel:
>>> ws3 = wb["NewPage"]
Что бы просмотреть имена всех рабочих листов книги, необходимо использовать атрибут Workbook.sheetname. Также можно итерироваться по рабочим листам книги Excel.
>>> wb.sheetnames # ['Mysheet1', 'NewPage', 'Mysheet2', 'Mysheet'] >>> for sheet in wb: ... print(sheet.title) # Mysheet1 # NewPage # Mysheet2 # Mysheet
Для создания копии рабочих листов в одной книге, необходимо воспользоваться методом Workbook.copy_worksheet():
>>> source_page = wb.active >>> target_page = wb.copy_worksheet(source_page)
Примечание. Копируются только ячейки (значения, стили, гиперссылки и комментарии) и определенные атрибуты рабочего листа (размеры, формат и свойства). Все остальные атрибуты книги/листа не копируются, например, изображения или диаграммы.
Поддерживается возможность копирования рабочих листов между книгами. Нельзя скопировать рабочий лист, если рабочая книга открыта в режиме только для чтения или только для записи.
Очевидно, что встает необходимость удалить лист электронной таблицы, который уже существует. Модуль openpyxl дает возможность удалить лист по его имени. Следовательно, сначала необходимо выяснить, какие листы присутствуют в книге, а потом удалить ненужный. За удаление листов книги отвечает метод Workbook.remove().
Смотрим пример:
# выясним, названия листов присутствуют в книге >>> name_list = wb.sheetnames >>> name_list # ['Mysheet1', 'NewPage', 'Mysheet2', 'Mysheet', 'Mysheet1 Copy'] # допустим, что нам не нужны первый и последний # удаляем первый лист по его имени с проверкой # существования такого имени в книге >>> if 'Mysheet1' in wb.sheetnames: # Если лист с именем `Mysheet1` присутствует # в списке листов экземпляра книги, то удаляем ... wb.remove(wb['Mysheet1']) ... >>> wb.sheetnames # ['NewPage', 'Mysheet2', 'Mysheet', 'Mysheet1 Copy'] # удаляем последний лист через оператор # `del`, имя листа извлечем по индексу # полученного списка `name_list` >>> del wb[name_list[-1]] >>> wb.sheetnames # ['NewPage', 'Mysheet2', 'Mysheet']
После того как выбран рабочий лист, можно начинать изменять содержимое ячеек. К ячейкам можно обращаться непосредственно как к ключам рабочего листа, например ws['A4']. Это вернет ячейку на A4 или создаст ее, если она еще не существует. Значения могут быть присвоены напрямую:
>>> ws['A4'] = 5 >>> ws['A4'] # <Cell 'NewPage'.A4> >>> ws['A4'].value # 5 >>> ws['A4'].column # 1 >>> ws['A4'].row # 4
Если объект ячейки присвоить переменной, то этой переменной, также можно присваивать значение:
>>> c = ws['A4'] >>> c.value = c.value * 2 >>> c.value # 10
Существует также метод Worksheet.cell(). Он обеспечивает доступ к ячейкам с непосредственным указанием значений строк и столбцов:
>>> d = ws.cell(row=4, column=2, value=10) >>> d # <Cell 'NewPage'.B4> >>> d.value = 3.14 >>> print(d.value) # 3.14
Примечание. При создании рабочего листа в памяти, он не содержит ячеек. Ячейки создаются при первом доступе к ним.
Важно! Из-за такого поведения, простой перебор ячеек в цикле, создаст объекты этих ячеек в памяти, даже если не присваивать им значения.
Не запускайте этот пример, поверьте на слово:
# создаст в памяти 100x100=10000 пустых объектов # ячеек, просто так израсходовав оперативную память. >>> for x in range(1,101): ... for y in range(1,101): ... ws.cell(row=x, column=y)
Диапазон с ячейками активного листа электронной таблицы можно получить с помощью простых срезов. Эти срезы будут возвращать итераторы объектов ячеек.
>>> cell_range = ws['A1':'C2'] >>> cell_range # ((<Cell 'NewPage'.A1>, <Cell 'NewPage'.B1>, <Cell 'NewPage'.C1>), # (<Cell 'NewPage'.A2>, <Cell 'NewPage'.B2>, <Cell 'NewPage'.C2>))
Аналогично можно получить диапазоны имеющихся строк или столбцов на листе:
# Все доступные ячейки в колонке `C` >>> colC = ws['C'] # Все доступные ячейки в диапазоне колонок `C:D` >>> col_range = ws['C:D'] # Все доступные ячейки в строке 10 >>> row10 = ws[10] # Все доступные ячейки в диапазоне строк `5:10` >>> row_range = ws[5:10]
Можно также использовать метод Worksheet.iter_rows():
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2): ... for cell in row: ... print(cell) # <Cell Sheet1.A1> # <Cell Sheet1.B1> # <Cell Sheet1.C1> # <Cell Sheet1.A2> # <Cell Sheet1.B2> # <Cell Sheet1.C2>
Точно так же метод Worksheet.iter_cols() будет возвращать столбцы:
>>> for col in ws.iter_cols(min_row=1, max_col=3, max_row=2): ... for cell in col: ... print(cell) # <Cell Sheet1.A1> # <Cell Sheet1.A2> # <Cell Sheet1.B1> # <Cell Sheet1.B2> # <Cell Sheet1.C1> # <Cell Sheet1.C2>
Примечание. Из соображений производительности метод Worksheet.iter_cols() недоступен в режиме только для чтения.
Если необходимо перебрать все строки или столбцы файла, то можно использовать свойство Worksheet.rows:
>>> ws = wb.active >>> ws['C9'] = 'hello world' >>> tuple(ws.rows) # ((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>), # (<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>), # (<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>), # ... # (<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>), # (<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>), # (<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))
или свойство Worksheet.columns:
>>> tuple(ws.columns) # ((<Cell Sheet.A1>, # <Cell Sheet.A2>, # ... # <Cell Sheet.B8>, # <Cell Sheet.B9>), # (<Cell Sheet.C1>, # <Cell Sheet.C2>, # ... # <Cell Sheet.C8>, # <Cell Sheet.C9>))
Примечание. Из соображений производительности свойство Worksheet.columns недоступно в режиме только для чтения.
Если просто нужны значения из рабочего листа, то можно использовать свойство активного листа Worksheet.values. Это свойство перебирает все строки на листе, но возвращает только значения ячеек:
for row in ws.values: for value in row: print(value)
Для возврата только значения ячейки, методы Worksheet.iter_rows() и Worksheet.iter_cols(), представленные выше, могут принимать аргумент values_only:
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True): ... print(row) # (None, None, None) # (None, None, None)
Модуль openpyxl дает возможность супер просто и удобно добавлять данные в конец листа электронной таблицы. Такое удобство обеспечивается методом объекта листа Worksheet.append(iterable), где аргумент iterable - это любой итерируемый объект (список, кортеж и т.д.). Такое поведение позволяет, без костылей, переносить в электронную таблицу данные из других источников, например CSV файлы, таблицы баз данных, дата-фреймы из Pandas и т.д.
Метод Worksheet.append() добавляет группу значений в последнюю строку, которая не содержит данных.
Варианты использования:
.append([‘ячейка A1’, ‘ячейка B1’, ‘ячейка C1’]).append({‘A’ : ‘ячейка A1’, ‘C’ : ‘ячейка C1’}), в качестве ключей используются буквы столбцов..append({1 : ‘ячейка A1’, 3 : ‘ячейка C1’}), в качестве ключей используются цифры столбцов.Пример добавление данных из списка:
# существующие листы рабочей книги >>> wb.sheetnames # ['NewPage', 'Mysheet2', 'Mysheet'] # добавим данные в лист с именем `Mysheet2` >>> ws = wb["Mysheet2"] # создадим произвольные данные, используя # вложенный генератор списков >>> data = [[row*col for col in range(1, 10)] for row in range(1, 31)] >>> data # [ # [1, 2, 3, 4, 5, 6, 7, 8, 9], # [2, 4, 6, 8, 10, 12, 14, 16, 18], # ... # ... # [30, 60, 90, 120, 150, 180, 210, 240, 270] # ] # добавляем данные в выбранный лист >>> for row in data: ... ws.append(row) ...
Вот и все, данные добавлены... Просто? Не просто, а супер просто!
Самый простой и безопасный способ сохранить книгу, это использовать метод Workbook.save() объекта Workbook:
>>> wb = Workbook() >>> wb.save('test.xlsx')
Внимание. Эта операция перезапишет существующий файл без предупреждения!!!
После сохранения, можно открыть полученный файл в Excel и посмотреть данные, выбрав лист с именем NewPage.
Примечание. Расширение имени файла не обязательно должно быть xlsx или xlsm, хотя могут возникнуть проблемы с его открытием непосредственно в другом приложении. Поскольку файлы OOXML в основном представляют собой ZIP-файлы, их также можно открыть с помощью своего любимого менеджера ZIP-архивов.
Если необходимо сохранить файл в поток, например, при использовании веб-приложения, такого как Flask или Django, то можно просто предоставить tempfile.NamedTemporaryFile():
from tempfile import NamedTemporaryFile from openpyxl import Workbook wb = Workbook() with NamedTemporaryFile() as tmp: wb.save(tmp.name) tmp.seek(0) stream = tmp.read()
Можно указать атрибут template=True, чтобы сохранить книгу как шаблон:
>>> from openpyxl import load_workbook >>> wb = load_workbook('test.xlsx') >>> wb.template = True >>> wb.save('test_template.xltx')
Примечание. Атрибут wb.template по умолчанию имеет значение False, это означает - сохранить как документ.
Внимание. Следующее не удастся:
>>> from openpyxl import load_workbook >>> wb = load_workbook('test.xlsx') # Необходимо сохранить с расширением *.xlsx >>> wb.save('new_test.xlsm') # MS Excel не может открыть документ # Нужно указать атрибут `keep_vba=True` >>> wb = load_workbook('test.xlsm') >>> wb.save('new_test.xlsm') >>> wb = load_workbook('test.xltm', keep_vba=True) # Если нужен шаблон документа, то необходимо указать расширение *.xltm. >>> wb.save('new_test.xlsm') # MS Excel не может открыть документ
Чтобы открыть существующую книгу Excel необходимо использовать функцию openpyxl.load_workbook():
>>> from openpyxl import load_workbook >>> wb2 = load_workbook('test.xlsx') >>> print(wb2.sheetnames) # ['Mysheet1', 'NewPage', 'Mysheet2', 'Mysheet']
Есть несколько флагов, которые можно использовать в функции openpyxl.load_workbook().
data_only: определяет, будут ли содержать ячейки с формулами - формулу (по умолчанию) или только значение, сохраненное/посчитанное при последнем чтении листа Excel.keep_vba определяет, сохраняются ли какие-либо элементы Visual Basic (по умолчанию). Если они сохранены, то они не могут изменяться/редактироваться.