Коммуникация с Excel на Python без VBA

Для многих VBA является неудобным языком программирования с непривычным синтаксисом, однако он является стандартом при выполнении сложных манипуляций с файлами Microsoft Office. В то же время тем, кто не желает мигрировать с языка Python, неоценимую помощь может оказать модуль openpyxl, который позволяет работать с Excel на уровне столбцов, строк и ячеек.

Установка модуля осуществляется просто - pip install openpyxl. Создадим демонстрационный файл с двумя листами вида:

Чтение и запись

Чтение

Происходит посредством вызова функции load_workbook с именем файла в качестве параметра:

import openpyxl
wb = openpyxl.load_workbook('file.xlsx')

Запись

Осуществляется методом save объекта рабочей книги:

wb.save('file_new.xlsx')

Создание

Также можно создать и сохранить рабочую книгу самому. Для этого возвращается объект класса Workbook и впоследствии сохраняется методом save:

wb = openpyxl.Workbook()
wb.save('file_new.xlsx')

Листы и их свойства

Продолжим работу с демонстрационным файлом file.xlsx.

Cписок листов

Для получения достаточно вызвать свойство sheetnames рабочей книги:

wb.sheetnames

Получение

Происходит через свойство active (возвращается лист, запускаемый при загрузке книги) либо, указав его в квадратных скобках после имени рабочей книги (по аналогии с адресацией списков):

wb[wb.sheetnames[1]], wb.active

Создание

Осуществляется методом create_sheet. Создадим новую рабочую книгу, новый лист и сохраним в файле:

wb = openpyxl.Workbook()
wb.create_sheet(title='новый_лист')
wb.save('file_new.xlsx')

Количество строк и столбцов листа

Эти значения доступны через атрибуты max_row и max_column листа (далее wb - объект рабочей книги файла file.xlsx):

sheet = wb.active
sheet.max_row, sheet.max_column

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

Ячейки и их свойства

Получение ячеек

Осуществляется путем адресации по имени либо по номеру строки и столбца (начиная с 1):

sheet['B2'], sheet.cell(2,2)

Значение и координаты

У каждой ячейки есть значение (value), а также местоположение/координаты (coordinate), определяемые строкой (row), столбцом (column) на пересечении которых она находится:

sheet['B2'].value, sheet['B2'].coordinate, sheet['B2'].row, sheet['B2'].column 

Диапазоны, строки и столбцы

Диапазон ячеек

Для получения обращаемся к объекту листа с указанием левой верхней и правой нижней ячеек:

 sheet['A1':'D4']

Так можно вывести на экран значения ячеек:

for row in sheet['A1':'D4']:
    for cell in row:
        print(cell.coordinate, cell.value)
        

Строки и столбцы

Используя свойства rows, columns объекта листа, можно получить генераторы всех ячеек (отличие - первое измерение по строкам или столбцам):

sheet.rows, sheet.columns

Так перебираются элементы по столбцам:

for col in sheet.columns:
    for cell in col:
        print(cell.coordinate, cell.value)

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

tuple(sheet.columns)[1]
[it.value for it in tuple(sheet.columns)[1]]

Преобразование буквенных обозначений столбцов в числа и обратно

Для этого используются функции column_index_from_string и get_column_letter модуля openpyxl.utils:

from openpyxl.utils import get_column_letter, column_index_from_string
column_index_from_string('ABC'), get_column_letter(731)