Главная › Блог › Автоматизация Excel на Python

Автоматизация Excel на Python: полный гайд по openpyxl и pandas

📅 11 апреля 2026 ⏱ 13 минут чтения ✍️ Команда Nexora

Введение: почему автоматизация Excel важна

Excel остаётся одним из самых популярных инструментов для работы с данными в бизнесе. Однако ручное заполнение, обработка и анализ таблиц требует много времени и подвержено ошибкам. Автоматизация Excel на Python позволяет значительно повысить производительность, минимизировать ошибки и освободить время на более важные задачи.

Python имеет мощные библиотеки для работы с Excel файлами: openpyxl для чтения и записи .xlsx файлов, pandas для анализа данных и xlwt для создания файлов в формате .xls. В этом гайде мы разберёмся, как использовать эти инструменты для автоматизации рутинных задач с Excel.

Установка необходимых библиотек

Для работы с Excel на Python установите следующие пакеты:

pip install openpyxl pandas xlwt openpyxl xlrd

Разберёмся, что нужно каждое:

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

openpyxl — это главная библиотека для работы с современными файлами Excel. Давайте начнём с основных операций:

Создание нового Excel файла

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

# Создаём новую рабочую книгу
wb = Workbook()
ws = wb.active
ws.title = 'Данные'

# Добавляем заголовки
ws['A1'] = 'Имя'
ws['B1'] = 'Возраст'
ws['C1'] = 'Должность'

# Форматируем заголовки
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
header_font = Font(bold=True, color='FFFFFF')

for cell in ['A1', 'B1', 'C1']:
    ws[cell].fill = header_fill
    ws[cell].font = header_font
    ws[cell].alignment = Alignment(horizontal='center', vertical='center')

# Добавляем данные
data = [
    ['Иван Петров', 28, 'Разработчик'],
    ['Мария Сидорова', 32, 'Менеджер проектов'],
    ['Алексей Иванов', 25, 'QA инженер']
]

for row_idx, row in enumerate(data, start=2):
    for col_idx, value in enumerate(row, start=1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# Устанавливаем ширину колонок
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 12
ws.column_dimensions['C'].width = 20

# Сохраняем файл
wb.save('employees.xlsx')

Чтение существующего Excel файла

from openpyxl import load_workbook

# Открываем существующий файл
wb = load_workbook('employees.xlsx')
ws = wb.active

# Читаем данные из ячеек
print(f'Название листа: {ws.title}')

# Итерируемся по строкам
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, values_only=True):
    print(row)

# Читаем конкретную ячейку
name = ws['A2'].value
print(f'Первый сотрудник: {name}')

# Получаем значение по координатам
age = ws.cell(row=2, column=2).value
print(f'Возраст: {age}')

Продвинутое форматирование с openpyxl

openpyxl предоставляет мощные возможности для форматирования ячеек, включая цвета, границы, шрифты и числовые форматы:

from openpyxl.styles import Border, Side, Font, PatternFill, Alignment, Numbers

# Определяем стили
border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

# Применяем форматирование к диапазону ячеек
for row in ws.iter_rows(min_row=1, max_row=4, min_col=1, max_col=3):
    for cell in row:
        cell.border = border
        cell.alignment = Alignment(horizontal='center', vertical='center')

# Форматирование чисел
ws['D1'] = 'Зарплата'
ws['D2'] = 50000
ws['D2'].number_format = '#,##0.00 ₽'

# Условное форматирование для важных ячеек
important_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
ws['D2'].fill = important_fill

Работа с формулами в Excel через Python

Python позволяет добавлять формулы в Excel файлы, которые будут вычисляться при открытии файла:

# Добавляем колонку с формулами
ws['D1'] = 'Бонус (20% от зарплаты)'

# Добавляем зарплаты
ws['D2'] = 50000
ws['D3'] = 60000
ws['D4'] = 45000

# Добавляем формулы для расчета бонуса
ws['E2'] = '=D2*0.2'
ws['E3'] = '=D3*0.2'
ws['E4'] = '=D4*0.2'

# Формула суммирования
ws['D5'] = '=SUM(D2:D4)'
ws['E5'] = '=SUM(E2:E4)'

# Формула усреднения
ws['D6'] = '=AVERAGE(D2:D4)'

# Условная формула
ws['F2'] = '=IF(D2>55000,"Старший","Младший")'

wb.save('employees_with_formulas.xlsx')

Использование pandas для обработки Excel данных

pandas — это мощная библиотека для анализа данных. Она позволяет легко работать с большими объёмами данных в Excel:

Чтение Excel файла в DataFrame

import pandas as pd

# Читаем Excel файл в DataFrame
df = pd.read_excel('employees.xlsx', sheet_name='Данные')

# Просматриваем данные
print(df)
print(df.head())  # Первые 5 строк
print(df.info())  # Информация о типах данных
print(df.describe())  # Статистика по колонкам

Фильтрация и трансформация данных

# Фильтруем данные
developers = df[df['Должность'] == 'Разработчик']
young_employees = df[df['Возраст'] < 30]

# Создаём новые колонки
df['Возраст_категория'] = df['Возраст'].apply(
    lambda x: 'Молодой' if x < 30 else 'Опытный'
)

# Группировка по должностям
by_position = df.groupby('Должность').agg({
    'Возраст': ['mean', 'min', 'max'],
    'Имя': 'count'
})

print(by_position)

Запись DataFrame обратно в Excel

# Сохраняем DataFrame в Excel
df.to_excel('employees_processed.xlsx', sheet_name='Обработанные данные', index=False)

# Запись нескольких листов
with pd.ExcelWriter('multi_sheet.xlsx') as writer:
    df.to_excel(writer, sheet_name='Все сотрудники')
    developers.to_excel(writer, sheet_name='Разработчики')
    young_employees.to_excel(writer, sheet_name='Молодые сотрудники')

Автоматизация реальных задач

Пример 1: Генерация отчётов по продажам

import pandas as pd
from datetime import datetime

# Загружаем данные о продажах
sales_df = pd.read_excel('sales.xlsx')

# Вычисляем статистику
total_sales = sales_df['Сумма'].sum()
avg_sale = sales_df['Сумма'].mean()
top_product = sales_df['Товар'].value_counts().idxmax()

# Создаём отчёт
with pd.ExcelWriter('sales_report.xlsx') as writer:
    sales_df.to_excel(writer, sheet_name='Данные', index=False)

    # Лист со статистикой
    stats_data = {
        'Метрика': ['Общие продажи', 'Средний чек', 'Топ товар'],
        'Значение': [total_sales, avg_sale, top_product]
    }
    stats_df = pd.DataFrame(stats_data)
    stats_df.to_excel(writer, sheet_name='Статистика', index=False)

print(f'Отчёт создан: {datetime.now()}')

Пример 2: Массовое обновление данных в нескольких файлах

import os
import pandas as pd
from pathlib import Path

# Обрабатываем все Excel файлы в директории
excel_dir = './data'

for file_path in Path(excel_dir).glob('*.xlsx'):
    try:
        df = pd.read_excel(file_path)

        # Добавляем новую колонку с датой обновления
        df['Обновлено'] = pd.Timestamp.now()

        # Преобразуем даты в стандартный формат
        if 'Дата' in df.columns:
            df['Дата'] = pd.to_datetime(df['Дата'])

        # Удаляем дубликаты
        df = df.drop_duplicates()

        # Сохраняем обновлённый файл
        df.to_excel(file_path, index=False)
        print(f'✓ Обновлён: {file_path.name}')

    except Exception as e:
        print(f'✗ Ошибка при обработке {file_path.name}: {e}')

Пример 3: Создание сводной таблицы (Pivot Table)

import pandas as pd

# Загружаем данные
df = pd.read_excel('sales.xlsx')

# Создаём сводную таблицу
pivot_table = df.pivot_table(
    values='Сумма',
    index='Категория',
    columns='Месяц',
    aggfunc='sum',
    margins=True
)

# Сохраняем результат
with pd.ExcelWriter('pivot_report.xlsx') as writer:
    pivot_table.to_excel(writer, sheet_name='Сводная таблица')
    df.to_excel(writer, sheet_name='Исходные данные', index=False)

Работа с большими файлами

При работе с большими файлами важно оптимизировать использование памяти:

import pandas as pd

# Читаем файл по частям для экономии памяти
chunk_size = 10000
chunks = []

for chunk in pd.read_excel('large_file.xlsx', chunksize=chunk_size):
    # Обрабатываем каждый chunk
    chunk['Обработано'] = True
    chunks.append(chunk)

# Объединяем все chunks
df = pd.concat(chunks, ignore_index=True)

# Сохраняем результат
df.to_excel('large_file_processed.xlsx', index=False)
print(f'Обработано строк: {len(df)}')

Обработка ошибок при работе с Excel

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

from openpyxl import load_workbook
from openpyxl.utils.exceptions import InvalidFileException
import pandas as pd

def safe_read_excel(file_path):
    """Безопасное чтение Excel файла с обработкой ошибок"""
    try:
        df = pd.read_excel(file_path)
        return df
    except FileNotFoundError:
        print(f'Ошибка: файл {file_path} не найден')
        return None
    except InvalidFileException:
        print(f'Ошибка: {file_path} не является валидным Excel файлом')
        return None
    except Exception as e:
        print(f'Неожиданная ошибка: {e}')
        return None

# Используем функцию
df = safe_read_excel('data.xlsx')
if df is not None:
    print(f'Успешно загружено {len(df)} строк')

Лучшие практики автоматизации Excel

Планирование и автоматизация скриптов

Для регулярного выполнения скриптов используйте планировщик задач операционной системы:

Linux/Mac (cron)

# Добавьте в crontab (crontab -e)
0 9 * * 1 /usr/bin/python3 /path/to/update_excel_data.py

Windows (Task Scheduler)

Создайте .bat файл и добавьте в Task Scheduler для автоматического запуска.

Интеграция с другими инструментами

Python скрипты можно интегрировать с другими системами:

Заключение

Автоматизация Excel на Python значительно упрощает работу с данными и повышает производительность. Используя openpyxl для создания и форматирования файлов, а также pandas для анализа данных, вы можете автоматизировать практически любые задачи с Excel.

Ключевые поисковые запросы: автоматизация Excel на Python, openpyxl примеры, pandas DataFrame, работа с Excel файлами на Python, массовая обработка Excel, создание отчётов Excel, формулы в Excel через Python, сводные таблицы в pandas.

Нужна помощь с автоматизацией Excel?

Команда Nexora разрабатывает системы автоматизации и обработки данных на Python. Получите консультацию по вашему проекту.

Рассчитать стоимость Написать нам