Автоматизация Excel с помощью Python и openpyxl

Кратко: этот материал показывает, как автоматизировать обработку множества файлов Excel с помощью Python и библиотеки openpyxl. Вы получите рабочий скрипт, рекомендации по улучшению, контроль ошибок и варианты альтернатив (Pandas, xlwings). Используйте примеры кода и чек‑лист для быстрой интеграции в рабочий процесс.

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

Ключевые понятия в одной строке:

• openpyxl: Python‑библиотека для работы с файлами Excel (.xlsx/.xlsm).
• Workbook/Worksheet: книга и лист Excel.
• cell.value: чтение/запись значения ячейки.

Установка openpyxl

Важно: используйте современную версию Python (3.7+ рекомендовано). Установка в большинстве случаев делается одной командой.

Шаги:

1. Откройте терминал (Командная строка, PowerShell или терминал в VS Code).
2. Выполните:

pip install openpyxl

Если система использует несколько версий Python, вместо pip может понадобиться python -m pip или py -m pip:

python -m pip install openpyxl
# или
py -m pip install openpyxl

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

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

py setup.py install

Примечание: локальная установка требует прав и может записать пакеты в системные каталоги; при необходимости используйте виртуальное окружение (venv).

Важно: openpyxl работает с форматом Office Open XML: .xlsx, .xlsm, .xltx, .xltm. Он не поддерживает старые бинарные .xls.

Подготовка Excel‑файлов и тестовых данных

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

Структура примера: строки 4–9 — годовые значения, столбцы B–D содержат данные по трём странам. Идея — программно заполнить суммарные и статистические ячейки под таблицей.

Советы по тестовым наборам:

• Создайте 3–10 файлов с разными пограничными значениями (пустые ячейки, текст вместо чисел, очень большие числа).
• Оставьте хотя бы один файл с защищённым листом, чтобы проверить обработку ошибок.

Создание Python‑скрипта (основная версия)

Создайте файл dataAnalysisScript.py и добавьте необходимые импорты.

import openpyxl
import os
import sys

Структура программы с основным циклом:

# Main, start of the program
if __name__ == "__main__":
    while True:
        # Ask for folder path and process files
        sys.exit()

Добавим ввод пути и получение списка файлов:

if __name__ == "__main__":
    while True:
        # Запрашиваем путь к папке с файлами
        filePath = input('Please enter the path of the folder where the excel files are stored: ')
        # Переходим в эту папку
        os.chdir(filePath)
        # Получаем строковый список файлов в текущей папке
        excelFiles = os.listdir('.')

Важно: в реальном сценарии лучше фильтровать по расширению и использовать pathlib/glob (см. улучшенный пример ниже).

Чтение и запись данных в файлы Excel

Основная идея: перебрать файлы, открыть книгу методом load_workbook, работать с активным листом (или конкретным по имени), читать и записывать значения ячеек.

Пример цикла, открывающего книгу:

# For each Excel file
for i in range(0, len(excelFiles)):
    # Открываем книгу
    wb = openpyxl.load_workbook(excelFiles[i])
    sheet = wb.active

Чтение конкретной ячейки:

cellValue = sheet['B3'].value

Запись в ячейку по адресу:

sheet['A10'].value = 56

Запись через row/column:

sheet.cell(row=1, column=6).value = 2

Совет: перед записью проверяйте тип данных и преобразуйте значения (float/int/None), чтобы избежать записи неожиданных типов.

Использование встроенных функций Excel через формулы

Вы можете записать в ячейку текст формулы — Excel рассчитает его при следующем открытии файла в Excel:

sheet['B11'].value = '=SUM(B4:B9)'
sheet['C11'].value = '=SUM(C4:C9)'
sheet['D11'].value = '=SUM(D4:D9)'

Аналогично для AVERAGE и STDEV:

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

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

Обратите внимание: openpyxl сохраняет формулу как строку. Если вы хотите получить значение формулы сразу в Python, нужно либо вычислять сами (например, numpy), либо открыть файл в Excel/Excel Online, либо использовать сторонние движки.

Сохранение изменений и закрытие книги

После изменений сохраните книгу:

wb.save(excelFiles[i])
print(excelFiles[i] + ' completed.')

Когда все файлы обработаны, завершите скрипт:

sys.exit()

Полный упрощённый код (как в оригинале):

# Main, start of the program
if __name__ == "__main__":
  while True:
    filePath = input('Please enter the path of the folder where the excel files are stored: ')
    os.chdir(filePath)
    excelFiles = os.listdir('.')

    # For each excel file
    for i in range(0, len(excelFiles)):

        wb = openpyxl.load_workbook(excelFiles[i])
        sheet = wb.active

        # Perform all required Excel functions as shown above here

        # Save and close the workbook
        wb.save(excelFiles[i])
        print(excelFiles[i] + ' completed.')

    # When the code finishes, close the program.
    sys.exit()

Рекомендации и улучшения (реалистичный production‑скрипт)

Ниже — улучшенный, более безопасный и удобный пример. Что добавлено: фильтрация по расширению, обработка ошибок, использование pathlib и glob, логирование, безопасное сохранение.

# improved_data_analysis.py
import openpyxl
from pathlib import Path
import sys

ALLOWED_EXT = {'.xlsx', '.xlsm', '.xltx', '.xltm'}

def process_file(path: Path):
    try:
        wb = openpyxl.load_workbook(path)
        sheet = wb.active

        # Пример: записать формулы для сумм, средних и stdev для диапазона B4:B9
        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['C12'].value = '=AVERAGE(C4:C9)'
        sheet['D12'].value = '=AVERAGE(D4:D9)'

        sheet['B13'].value = '=STDEV(B4:B9)'
        sheet['C13'].value = '=STDEV(C4:C9)'
        sheet['D13'].value = '=STDEV(D4:D9)'

        # Сохраняем в безопасный временный файл, затем заменяем оригинал
        temp_path = path.with_suffix(path.suffix + '.tmp')
        wb.save(temp_path)
        temp_path.replace(path)
        print(f"Processed: {path.name}")
    except Exception as e:
        print(f"Failed to process {path.name}: {e}")

if __name__ == '__main__':
    folder = input('Enter folder path with Excel files: ').strip()
    p = Path(folder)
    if not p.is_dir():
        print('Folder not found')
        sys.exit(1)

    files = [f for f in p.iterdir() if f.suffix.lower() in ALLOWED_EXT]
    if not files:
        print('No Excel files found in folder')
        sys.exit(0)

    for f in files:
        process_file(f)

Пояснения:

• pathlib.Path удобна и переносима между ОС.
• Безопасное сохранение через временный файл уменьшает риск повреждения оригинала.
• Обработка исключений позволяет продолжить обработку следующих файлов.

Когда этот подход не подойдёт (примеры и ограничения)

• Формат файлов .xls (старый бинарный Excel) — openpyxl не поддерживает. Используйте xlrd/xlwt (для чтения/записи .xls) или конвертируйте в .xlsx.
• Когда нужно выполнить макрос VBA — openpyxl не выполняет код VBA; можно сохранить макросы в .xlsm, но выполнение макроса потребует Excel/COM (pywin32) или запуск внутри Excel.
• Очень большие наборы данных (миллионы строк) — лучше использовать базу данных или Pandas с оптимизированными чтениями, иначе память и скорость станут узким местом.

Альтернативные подходы

• Pandas: удобен для анализа и агрегирования, может читать/записывать через openpyxl или engine=’openpyxl’. Лучший выбор для табличной аналитики.
• xlwings/pywin32: управление живым экземпляром Excel (включая выполнение макросов) — работает только на Windows с установленным Excel.
• Apache POI (через Java) или коммерческие SDK для специфических корпоративных задач.

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

• Не обрабатывайте файлы с личными данными без согласия; если данные содержат персональные сведения, соблюдайте местные правила по защите данных (например, GDPR в ЕС).
• Скрипт читает и записывает файлы локально; убедитесь, что у пользователя есть соответствующие права доступа и резервные копии.
• Временные файлы должны сохраняться в безопасном месте и удаляться после успешной операции.

Чек‑лист перед запуском (роль‑ориентированная)

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

• Установить виртуальное окружение и зависимости.
• Протестировать скрипт на нескольких тестовых файлах.

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

• Проверить формат входных таблиц (диапазоны, заголовки).
• Подготовить список ожидаемых выходных метрик.

Для администратора/оператора:

• Убедиться в наличии резервных копий.
• Проверить права доступа к папке с файлами.

Критерии приёмки

• Скрипт успешно обрабатывает 90% тестовых файлов без вмешательства.
• При ошибках скрипт логирует причину и продолжает обработку следующих файлов.
• Обновлённые файлы содержат формулы/значения в ожидаемых ячейках.

Тестовые сценарии (примеры)

1. Нормальный файл .xlsx с числовыми данными — ожидаемая запись формул.
2. Файл с пустыми ячейками в диапазоне — формула записана, Excel вернёт корректный результат при открытии.
3. Защищённый лист — скрипт должен поймать ошибку и продолжить.
4. Наличие файлов .xls — скрипт игнорирует такие файлы (по расширению).

Часто задаваемые вопросы

Нужен ли установленный Microsoft Excel для работы openpyxl?

Нет. openpyxl работает с файлами формата Open XML напрямую и не требует установленного Excel для чтения/записи.

Какие форматы файлов поддерживаются?

openpyxl предназначен для .xlsx, .xlsm, .xltx, .xltm. Старые .xls не поддерживаются.

Можно ли получить вычисленный результат формулы сразу в Python?

openpyxl не вычисляет формулы — он записывает их в файл. Для немедленного вычисления используйте библиотеки для вычислений (numpy/pandas) или откройте файл в Excel.

Краткое резюме

• openpyxl — удобный инструмент для автоматизации Excel в Python при работе с .xlsx/.xlsm.
• Для production‑скрипта используйте pathlib, обработку ошибок и безопасное сохранение через временные файлы.
• Если требуется выполнение макросов или работа с .xls — рассмотрите xlwings, pywin32 или специализированные инструменты.

Полезные источники и следующие шаги: изучите Pandas для агрегирования данных, настройте CI/cron‑задачу для регулярного запуска и добавьте логирование/уведомления при ошибках.