Автоматизация Excel с Python и openpyxl

Люди за столом смотрят на экран компьютера с графиками Excel

TL;DR

Openpyxl — это библиотека Python для чтения, изменения и записи файлов Excel (.xlsx/.xlsm). Установите её через pip, напишите скрипт, который открывает файлы в папке, вставляет формулы (SUM/AVERAGE/STDEV) и сохраняет изменения. В статье есть готовые примеры кода, проверки ошибок, советы по масштабированию и альтернативы (pandas, xlwings).

Excel мощен для аналитики, но при работе с множеством файлов или повторяющимися расчётами удобнее автоматизировать процесс. Openpyxl — распространённая библиотека для работы с форматами Excel (xlsx/xlsm) из Python: читать листы, изменять ячейки, записывать формулы и сохранять рабочие книги.

Важно: openpyxl не исполняет макросы VBA и ограниченно работает с некоторыми типами графиков и сводных таблиц. Если вам нужны макросы или интерактивные объекты, рассмотрите другие инструменты (см. раздел “Альтернативы”).

Что вы получите в этом руководстве

Быстрая установка и проверка openpyxl.
Пример рабочего скрипта для пакетной обработки Excel-файлов в папке.
Шаблоны кода: чтение/запись ячеек, вставка формул, сохранение.
Советы по обработке ошибок, тестовые сценарии и рекомендации по безопасности.

Установка модуля openpyxl

Самый простой способ — через pip. Откройте терминал или командную строку и выполните:

pip install openpyxl

Если установка через pip невозможна (корпоративный брандмауэр, офлайн-среда), скачайте архив с релизом и установите локально:

Браузер открыт на странице загрузки Openpyxl

Скачайте архив openpyxl-версия.tar.gz
Распакуйте содержимое
В командной строке перейдите в папку с распакованными файлами и выполните:

py setup.py install

Примечание: используйте py или python в зависимости от настройки вашей среды.

Подготовьте тестовые Excel-файлы

Создайте несколько Excel-файлов в одной папке. В примере используются пять файлов, каждый содержит таблицу с данными по населению нескольких стран.

Проводник Windows открыт, показаны несколько файлов Excel в папке

Добавьте в каждый файл табличные данные похожей структуры (например, столбцы: Год, Страна A, Страна B, Страна C). Пример таблицы:

Открыт Excel-файл с таблицей данных по населению

Это упрощает массовую обработку: скрипт выполняет одни и те же формулы для каждой книги.

Базовая структура Python-скрипта

Создайте файл dataAnalysisScript.py и откройте его в любом редакторе.

Импорты и базовая логика:

import openpyxl
import os
import sys

# Main, start of the program
if __name__ == "__main__":
    while True:
        # Write code here
        # When the code finishes, close the program.
        sys.exit()

Обратите внимание: в Python важны отступы — используйте 4 пробела или табуляцию последовательно.

Ввод пути к папке и фильтрация файлов

Добавим запрос пути и отбор только Excel-файлов (.xlsx и .xlsm):

if __name__ == "__main__":
    while True:
        filePath = input('Введите путь к папке с Excel-файлами: ')
        if not os.path.isdir(filePath):
            print('Путь не найден. Попробуйте ещё раз.')
            continue
        os.chdir(filePath)
        # Список файлов в папке, фильтруем по расширению
        excelFiles = [f for f in os.listdir('.') if f.lower().endswith(('.xlsx', '.xlsm'))]
        if not excelFiles:
            print('Excel-файлы не найдены в папке.')
            continue
        # Далее обработка файлов
        break

Чтение и запись данных в ячейки

Откроем каждую книгу, прочитаем значение и запишем новые данные:

for file_name in excelFiles:
    try:
        wb = openpyxl.load_workbook(file_name)
    except Exception as e:
        print(f'Не удалось открыть {file_name}: {e}')
        continue
    sheet = wb.active

    # Чтение значения из B3
    cellValue = sheet['B3'].value
    print(f'{file_name}: значение B3 = {cellValue}')

    # Запись числа в A10
    sheet['A10'].value = 56
    # Или запись по координатам (строка=1, столбец=6 -> F1)
    sheet.cell(row=1, column=6).value = 2

    # Сохранение и закрытие
    wb.save(file_name)
    try:
        wb.close()
    except AttributeError:
        # старые версии openpyxl могут не иметь close(), сохранять достаточно
        pass
    print(file_name + ' completed.')

Ключевые моменты:

Используйте os.path.isfile/os.path.isdir для валидации.
Ловите исключения при открытии/сохранении — файлы могут быть заняты другим приложением.
wb.close() безопасно вызывать, но не во всех версиях обязательно.

Вставка формул Excel из Python

Openpyxl позволяет записать в ячейку формулу в виде строки. Excel пересчитает формулы при следующем открытии файла.

# Записываем сумму, среднее и стандартное отклонение
sheet['B11'].value = '=SUM(B4:B9)'
sheet['C11'].value = '=SUM(C4:C9)'
sheet['D11'].value = '=SUM(D4:D9)'

sheet['B12'].value = '=AVERAGE(B4:B9)'

sheet['B13'].value = '=STDEV(B4:B9)'

Формулы записываются как строки в формате Excel (например, ‘=SUM(B4:B9)’).
При открытии файла в Excel формулы будут вычислены автоматически (если в Excel включено автоматическое пересчитывание).

Excel-файл открыт с заполненными полями Сумма, Среднее и Стандартное отклонение

Полный пример скрипта (устойчивый к ошибкам)

Ниже более полный и устойчивый пример, объединяющий вышеописанные шаги:

import openpyxl
import os
import sys

if __name__ == "__main__":
    while True:
        filePath = input('Введите путь к папке с Excel-файлами: ')
        if not os.path.isdir(filePath):
            print('Путь не найден. Попробуйте ещё раз.')
            continue
        os.chdir(filePath)
        excelFiles = [f for f in os.listdir('.') if f.lower().endswith(('.xlsx', '.xlsm'))]
        if not excelFiles:
            print('Excel-файлы не найдены в папке.')
            continue

        for file_name in excelFiles:
            print('Обрабатывается:', file_name)
            try:
                wb = openpyxl.load_workbook(file_name)
            except Exception as e:
                print(f'Не удалось открыть {file_name}: {e}')
                continue

            sheet = wb.active

            # Пример чтения/записи
            try:
                cellValue = sheet['B3'].value
                print(f'{file_name}: значение B3 = {cellValue}')
            except Exception as e:
                print('Ошибка чтения B3:', e)

            sheet['A10'].value = 56
            sheet.cell(row=1, column=6).value = 2

            # Вставка формул
            sheet['B11'].value = '=SUM(B4:B9)'
            sheet['B12'].value = '=AVERAGE(B4:B9)'
            sheet['B13'].value = '=STDEV(B4:B9)'

            try:
                wb.save(file_name)
                try:
                    wb.close()
                except AttributeError:
                    pass
                print(file_name + ' completed.')
            except Exception as e:
                print(f'Не удалось сохранить {file_name}: {e}')

        # После обработки всех файлов завершаем программу
        sys.exit()

Как запускать скрипт

Откройте командную строку и перейдите в папку со скриптом. Пример Windows:

cd C:\Users\Sharl\Desktop

Запустите скрипт:

python dataAnalysisScript.py

Введите путь к папке с Excel-файлами, например:

C:\Users\Sharl\Desktop\CountryData

Важно: закройте все Excel-файлы перед запуском скрипта — иначе сохранение может провалиться из-за блокировки файла.

Когда openpyxl подходит, а когда нет

Когда подходит:

Пакетная обработка десятков/сотен файлов с однородной структурой.
Запись формул, простая модификация ячеек, копирование значений.

Когда openpyxl не подходит:

Нужна работа с макросами VBA (openpyxl не выполняет макросы).
Сложная генерация интерактивных графиков и сводных таблиц: часто проще перекладывать данные в Excel и собирать визуализации вручную или использовать COM-интерфейс (Windows).

Альтернативы и сочетание инструментов

Pandas: удобна для анализа и агрегации данных в памяти; сохраняет в Excel через to_excel, но управление формулами и форматами ограничено.
Xlwings: взаимодействует с реальным Excel (COM), может выполнять макросы и управлять интерфейсом Excel (только Windows/Mac с установленным Excel).
openpyxl + Pandas: используйте pandas для обработки больших наборов данных, а openpyxl — для записи результатов в шаблон Excel и вставки формул/форматирования.

Проверки, тесты и критерии приёмки

Критерии приёмки перед развёртыванием:

Скрипт корректно обрабатывает хотя бы 10 файлов: открывает, изменяет и сохраняет без ошибок.
Для одного тестового файла формулы в нужных ячейках соответствуют ожидаемым (SUM/AVERAGE/STDEV).
Скрипт адекватно обрабатывает не-Excel файлы — пропускает их.
Скрипт логирует ошибки открытия/сохранения и не падает при единичной ошибке.

Тестовые кейсы:

Папка с 5 корректными .xlsx файлами → все файлы обновлены, формулы присутствуют.
Папка содержит .txt и .csv → эти файлы игнорируются.
Один файл открыт в Excel → при сохранении обработка этого файла должна выдавать понятную ошибку и пропустить его.

Безопасность и конфиденциальность

Обрабатывайте персональные данные в соответствии с локальными требованиями (GDPR/локальные законы). Скрипт читает и записывает файлы на диске — контролируйте доступ к папке.
Работайте копиями данных для тестирования, чтобы избежать порчи исходных файлов.

Чек-листы по ролям

Для разработчика:

Проверить обработку исключений при открытии/сохранении
Добавить логирование (файл лога)
Покрыть тестами сценарии с ошибками файловая блокировка

Для аналитика:

Подготовить шаблон Excel с ожидаемой структурой столбцов
Проверить корректность формул и диапазонов

Короткая методология внедрения (mini-playbook)

Создать копию исходных файлов.
Запустить скрипт на копиях в тестовой папке.
Проверить несколько файлов вручную в Excel.
Настроить плановый запуск (cron/Task Scheduler) и оповещения об ошибках.

Решение для часто встречающихся проблем (микро-руководство)

Файл занят Excel: убедитесь, что все книги закрыты, или используйте xlwings для взаимодействия с открытыми книгами.
Формулы не пересчитались в Excel: откройте книгу и принудительно пересчитайте (F9) или включите автоматическое пересчитывание.
Разные структуры таблиц: добавьте проверку схемы (наличие заголовков) перед вставкой формул.

Ментальные модели и рекомендации

Модель “шаблон + данные”: держите шаблон Excel с расположением формул и просто подставляйте новые данные в заранее определённые диапазоны.
“Fail fast”: обнаруживайте и логируйте первые ошибки, не игнорируйте исключения silently.

Краткий словарь терминов

openpyxl — библиотека Python для работы с файлами Excel (.xlsx/.xlsm).
Workbook — рабочая книга Excel (файл).
Sheet — лист внутри книги.
cell[‘A1’].value — чтение/запись значения ячейки.

Итог

Openpyxl — надёжный инструмент для автоматизации рутинной обработки Excel-файлов. Он особенно удобен, когда нужно обновить формулы или массово изменить значения в однообразных книгах. При необходимости более сложной визуализации, макросов или взаимодействия с открытым Excel рассмотрите сочетание pandas и xlwings.

Важно: всегда работайте на копиях данных и добавьте в скрипт логирование и обработку ошибок перед запуском на продуктиве.