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