Время прочтения: 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:\Users\User1' # Задаем путь к папке с 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можете сделать форматирование, создавать сводные таблицы и диаграммы, делать фильтрацию и сортировку, агрегировать значения ячеек, изменять шрифт, перемещение данные на новый лист. С этими возможностями можно ознакомиться в официальной документации.