Автоматизация обработки Excel таблиц через openpyxl

Раздел: Офис -> Работа с Excel

Основы работы с openpyxl

Библиотека openpyxl позволяет читать, редактировать и создавать файлы Excel формата .xlsx. Установка выполняется командой pip install openpyxl. Рассмотрим основные возможности на примерах.

Самое распространенное решение - создание новой книги с данными и сохранение:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = 'Отчет'
ws['A1'] = 'Наименование'
ws['B1'] = 'Сумма'
ws['A2'] = 'Товар А'
ws['B2'] = 1500
wb.save('example.xlsx')

код python в excel (код python в excel)

Пояснения: создаётся пустая книга, выбирается активный лист, задаётся заголовок, заполняются ячейки. Сохранение выполняется методом save(). Если файл с таким именем уже открыт в Excel, возникнет PermissionError - перед сохранением следует закрыть файл.

Как прочитать данные из существующего файла?

Для чтения используется load_workbook:

from openpyxl import load_workbook

wb = load_workbook('example.xlsx')
ws = wb.active
val = ws['A1'].value
print(val)

Python работа с таблицами excel (работа с таблицами excel в python)

Пояснения: метод load_workbook открывает файл, далее обращение к ячейке через её адрес. value возвращает содержимое. Если лист не существует, возникнет KeyError - можно проверить наличие листа через wb.sheetnames.

Как изменить внешний вид ячеек?

Применение стилей, шрифтов, заливки:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
ws = wb.active
cell = ws['A1']
cell.value = 'Заголовок'
cell.font = Font(bold=True, color='FF0000')
cell.fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
cell.alignment = Alignment(horizontal='center')
wb.save('styled.xlsx')

Excel скрипты python (скрипты python для excel)

Пояснения: Font задаёт шрифт, PatternFill - заливку, Alignment - выравнивание. Важно задавать все атрибуты сразу, иначе предыдущие настройки могут перезаписаться. Типичная ошибка - использование fill_type='solid' без указания цвета.

Как добавить формулу Excel в ячейку?

Формула записывается как текст, начинающийся с =:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws['A1'] = 10
ws['B1'] = 20
ws['C1'] = '=SUM(A1:B1)'
wb.save('formula.xlsx')

Python сохранить в excel (сохранение данных в excel с помощью python)

Пояснения: openpyxl не вычисляет формулы, но сохраняет их как строки. При открытии в Excel они пересчитываются. Некоторые сложные формулы (например, ВПР) могут не поддерживаться старыми версиями. При чтении ячейки с формулой cell.value вернёт строку формулы, а не результат.

Как создать, переименовать или удалить листы?

Управление листами:

from openpyxl import Workbook

wb = Workbook()
ws1 = wb.active
ws1.title = 'Первый'
ws2 = wb.create_sheet('Второй')
ws3 = wb.create_sheet('Третий', 0)  # вставка в начало
wb.remove(ws2)  # удаление
wb.save('sheets.xlsx')

Python таблица excel (таблица excel в python)

Пояснения: create_sheet создаёт новый лист, параметр 0 задаёт позицию. Если удалить последний лист (единственный), возникнет ValueError - необходимо предварительно создать новый лист.

Как добавить диаграмму?

Пример создания гистограммы:

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
for i in range(1, 6):
    ws.cell(row=i, column=1, value=i)
    ws.cell(row=i, column=2, value=i*10)
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=5)
cats = Reference(ws, min_col=1, min_row=1, max_row=5)
chart.add_data(data, titles_from_data=False)
chart.set_categories(cats)
ws.add_chart(chart, 'D1')
wb.save('chart.xlsx')

Пояснения: BarChart создаёт гистограмму, Reference указывает диапазоны данных и категорий. Диаграмма размещается начиная с указанной ячейки. Проблемы могут возникнуть, если данные содержат строки - тогда значения интерпретируются как текст.

Объединение и разъединение ячеек

Для объединения нескольких ячеек в одну используется метод merge_cells. После объединения все данные сохраняются в левой верхней ячейке.

Пример

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.merge_cells('A1:C1')
ws['A1'] = 'Объединённый заголовок'
ws['A1'].alignment = Alignment(horizontal='center')
wb.save('merged.xlsx')

Результат: в Excel ячейки A1, B1, C1 становятся одной. Чтобы разъединить, вызовите unmerge_cells('A1:C1'). Ошибка: если разъединить ячейки, данные сохраняются только в левой верхней, остальные становятся пустыми.

Условное форматирование

Условное форматирование позволяет подсветить ячейки по правилу. Например, выделение ячеек со значениями больше 100:

Пример

from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
for i in range(1, 11):
    ws.cell(row=i, column=1, value=i*50)
rule = CellIsRule(operator='greaterThan', formula=['100'], fill=PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid'))
ws.conditional_formatting.add('A1:A10', rule)
wb.save('conditional.xlsx')

Пояснения: CellIsRule задаёт условие, operator определяет сравнение. Для более сложных правил можно использовать FormulaRule.

Создание выпадающих списков (Data Validation)

Выпадающий список ограничивает ввод данных пользователем:

Пример

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
dv = DataValidation(type='list', formula1='"Вариант1,Вариант2,Вариант3"', allow_blank=True)
dv.error = 'Выберите из списка'
dv.errorTitle = 'Ошибка'
ws.add_data_validation(dv)
dv.add('A1:A10')
ws['A1'] = 'Вариант1'
wb.save('validation.xlsx')

Результат: при клике на ячейки A1:A10 появляется стрелка выбора. Список значений задаётся строкой через запятую. Проблема: если список содержит много значений, формула становится длинной и может превысить лимит.

Потоковая запись больших файлов (WriteOnly)

Для уменьшения потребления памяти при записи сотен тысяч строк используется WriteOnlyWorkbook:

Пример

from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet('Большие данные')
for i in range(1, 100001):
    ws.append([i, i**2, i**0.5])
wb.save('big_data.xlsx')

Пояснения: write_only=True отключает кэширование, данные записываются сразу. Читать такие файлы можно только обычным способом. Нельзя модифицировать уже записанные строки.

Вставка изображений

Изображение добавляется через openpyxl.drawing.image.Image:

Пример

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active
img = Image('logo.png')
img.anchor = 'A1'
ws.add_image(img)
wb.save('image.xlsx')

Результат: изображение вставляется на лист по указанному адресу. Размеры можно менять через img.width и img.height.

Сортировка строк по данным столбца

Сортировка выполняется через метод sort объекта листа:

Пример

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(['Имя', 'Возраст'])
ws.append(['Анна', 25])
ws.append(['Борис', 20])
ws.append(['Виктор', 30])
ws.sort('A2:A4', descending=False)
wb.save('sorted.xlsx')

Пояснения: первый параметр - диапазон сортируемых ячеек, descending задаёт направление. Важно, чтобы диапазон не включал заголовки.

Автофильтр

Добавление автофильтра к строкам:

Пример

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(['Город', 'Население'])
ws.append(['Москва', 12000000])
ws.append(['СПб', 5000000])
ws.auto_filter.ref = 'A1:B3'
wb.save('filter.xlsx')

Пояснения: auto_filter.ref задаёт диапазон для фильтра. При открытии в Excel появляются стрелки для фильтрации.

Примеры демонстрируют гибкость библиотеки openpyxl для автоматизации офисных задач.

Библиотека openpyxl в Python - comments

библиотека openpyxl python (python)