5 библиотек Python для автоматизации Excel и Google Sheets

Для кого эта статья:

• Специалисты и аналитики, работающие с табличными данными
• Программисты и разработчики, интересующиеся автоматизацией процессов с помощью Python

• Владельцы и менеджеры бизнеса, стремящиеся оптимизировать рабочие процессы и увеличить эффективность команд

  Ручная работа с Excel-таблицами или Google Sheets — это прошлый век, который не должен занимать ваше время. Автоматизация этих процессов с помощью Python не только избавляет от рутины, но и минимизирует человеческие ошибки при обработке данных. Представьте: вместо того чтобы тратить часы на копирование формул и форматирование ячеек, вы запускаете скрипт, который делает всю работу за минуты — это не просто удобно, а критически важно для бизнеса, где время напрямую конвертируется в деньги. 🚀 Рассмотрим пять ключевых библиотек Python, которые превратят ваш код в мощный инструмент управления табличными данными.

Разработка на Python — это не только создание веб-приложений, но и мощная автоматизация рутинных бизнес-процессов, включая работу с Excel и Google Sheets. На курсе Обучение Python-разработке от Skypro вы освоите не только основы языка, но и получите практический опыт работы с библиотеками для обработки данных. Более 87% наших выпускников успешно внедряют автоматизацию в рабочие процессы компаний уже в первые месяцы после обучения.

Python и таблицы: почему это эффективная связка

Работа с табличными данными остаётся одной из самых распространённых задач в аналитике и бизнес-процессах. Python за последнее десятилетие стал инструментом №1 для автоматизации этих процессов по ряду весомых причин:

• Высокая производительность обработки больших массивов данных
• Возможность интеграции с другими системами и API
• Богатая экосистема специализированных библиотек
• Читаемый синтаксис даже для сложных операций с данными
• Кроссплатформенность и возможность запуска в облачной среде

Сочетание Python с Excel или Google Sheets даёт мощный синергетический эффект: табличные процессоры обеспечивают удобную визуализацию и организацию данных, а Python предоставляет инструменты для их анализа и манипуляции. Будь то создание ежедневных отчётов, агрегация данных из различных источников или автоматическое обновление информации — Python справляется с этими задачами элегантно и эффективно. 📊

Александр Петров, Lead Data Engineer

Недавно столкнулся с задачей, которая выглядела почти невыполнимой. Клиенту требовалось ежедневно обрабатывать 20+ Excel-файлов, каждый по 50-100 тысяч строк, агрегировать данные и формировать сводный отчёт. Вручную это занимало у трёх аналитиков весь рабочий день.

Я написал скрипт на Python с использованием pandas. Результат превзошёл ожидания: обработка стала занимать 8 минут вместо 8 человеко-часов. При этом исчезли ошибки, связанные с человеческим фактором, а формат выходных данных стал строго стандартизированным. Когда клиент увидел, что его команда теперь может тратить время на анализ данных, а не на их подготовку, он был по-настоящему впечатлён. Это классический случай, когда Python радикально трансформирует рабочие процессы.

Для эффективной работы с табличными данными необходимо выбрать правильные инструменты. Каждая библиотека имеет свои сильные стороны и оптимальные сценарии использования:

Теперь рассмотрим каждую из этих библиотек подробнее, чтобы вы могли выбрать оптимальное решение для своих задач.

Pandas: мощная обработка данных в Excel и CSV

Pandas — бесспорный лидер среди библиотек для анализа и манипуляции данными в Python. Ее главное преимущество заключается в объекте DataFrame — двумерной таблице с названиями столбцов и индексированными строками, которая идеально подходит для работы с табличными данными любой сложности. 🐼

Установка библиотеки проста:

pip install pandas

Базовые операции с Excel и CSV файлами в pandas выглядят следующим образом:

import pandas as pd

# Чтение Excel-файла
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# Чтение CSV-файла
df_csv = pd.read_csv('data.csv', delimiter=',')

# Базовый анализ
print(df.describe()) # Статистика по числовым столбцам
print(df.head()) # Первые 5 строк

# Фильтрация данных
filtered_df = df[df['Revenue'] > 10000]

# Группировка и агрегация
summary = df.groupby('Region').agg({'Sales': 'sum', 'Profit': 'mean'})

# Запись в Excel
summary.to_excel('summary_report.xlsx', index=True)

# Запись в CSV
filtered_df.to_csv('filtered_data.csv', index=False)

Pandas особенно эффективен при решении следующих задач:

• Очистка и предобработка данных (обработка пропусков, дубликатов, нормализация)
• Сложные фильтрации с множественными условиями
• Агрегация и группировка данных с применением различных функций
• Объединение нескольких таблиц (merge, join, concat)
• Трансформация структуры данных (pivot, melt, transpose)
• Временные ряды и работа с датами

При работе с большими объемами данных следует помнить о некоторых оптимизациях:

# Чтение только нужных столбцов экономит память
df = pd.read_excel('large_file.xlsx', usecols=['Date', 'Revenue', 'Product'])

# Чтение по частям для очень больших файлов
chunks = pd.read_csv('huge_data.csv', chunksize=10000)
for chunk in chunks:
process_data(chunk)

Pandas совмещает простоту использования с широкими возможностями, что делает его идеальным выбором для большинства задач по обработке табличных данных, особенно когда требуется провести сложный анализ перед экспортом обратно в Excel или Google Sheets.

Openpyxl: полный контроль над файлами Excel

Openpyxl предоставляет низкоуровневый доступ к файлам Excel формата .xlsx, что делает его незаменимым, когда требуется точный контроль над форматированием, формулами и структурой документа. В отличие от pandas, ориентированного преимущественно на данные, openpyxl позволяет взаимодействовать со всеми аспектами Excel-файла. 📝

Установка библиотеки:

pip install openpyxl

Основные операции с файлами Excel:

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.chart import BarChart, Reference

# Создание нового файла
wb = Workbook()
ws = wb.active
ws.title = "Отчет по продажам"

# Заполнение данными
ws['A1'] = "Продукт"
ws['B1'] = "Регион"
ws['C1'] = "Продажи"

data = [
("Ноутбук", "Москва", 1200),
("Смартфон", "Санкт-Петербург", 950),
("Планшет", "Москва", 430),
("Ноутбук", "Казань", 670)
]

for row, (product, region, sales) in enumerate(data, start=2):
ws.cell(row=row, column=1).value = product
ws.cell(row=row, column=2).value = region
ws.cell(row=row, column=3).value = sales

# Форматирование
header_font = Font(bold=True, size=12)
for cell in ws[1]:
cell.font = header_font
cell.alignment = Alignment(horizontal='center')
cell.fill = PatternFill(start_color="DDDDDD", end_color="DDDDDD", fill_type="solid")

# Добавление формул
last_row = len(data) + 1
ws[f'C{last_row + 1}'] = f'=SUM(C2:C{last_row})'
ws[f'A{last_row + 1}'] = 'Итого:'
ws[f'A{last_row + 1}'].font = Font(bold=True)

# Создание диаграммы
chart = BarChart()
chart.title = "Продажи по регионам"
data_ref = Reference(ws, min_col=3, min_row=1, max_col=3, max_row=last_row)
cats_ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=last_row)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats_ref)
ws.add_chart(chart, "E2")

# Сохранение файла
wb.save("sales_report.xlsx")

# Чтение существующего файла
existing_wb = load_workbook("existing_file.xlsx")
sheet = existing_wb["Sheet1"]
for row in sheet.iter_rows(min_row=2, values_only=True):
print(row)

Ирина Соколова, Финансовый аналитик

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

Я предложила автоматизировать процесс с помощью openpyxl. Сначала было сложно убедить коллег, что Python справится с такой "дизайнерской" задачей. Мы разработали скрипт, который не только заполнял данные, но и в точности воспроизводил все форматирование, создавал нужные диаграммы и даже настраивал параметры печати.

Первый автоматически сгенерированный отчет вызвал настоящий фурор – он был неотличим от ручной работы, но создавался за 3 минуты вместо двух дней. Финансовый директор был настолько впечатлен, что инициировал программу обучения Python для всего аналитического отдела. Сейчас наш скрипт стал более сложным – он автоматически берет данные из нескольких источников, проводит нужные расчеты и формирует полностью готовый к презентации документ.

Openpyxl особенно полезен в следующих сценариях:

• Создание сложных, хорошо отформатированных отчетов
• Работа с несколькими листами в одном файле
• Добавление и настройка диаграмм
• Использование сложных формул Excel
• Настройка параметров страницы для печати
• Защита листов и ячеек паролем
• Работа с именованными диапазонами и сводными таблицами

Openpyxl — идеальный инструмент, когда внешний вид и структура документа Excel имеют такое же значение, как и данные в нем.

XlsxWriter: создание сложных отчетов и визуализаций

XlsxWriter специализируется на создании новых Excel-файлов с продвинутым форматированием и визуализацией. Это оптимальный выбор, когда необходимо генерировать сложные отчеты с графиками, таблицами и форматированными данными. ⚡

Важно отметить, что XlsxWriter, в отличие от openpyxl, не поддерживает чтение существующих файлов — он полностью ориентирован на создание новых документов. Однако в своей специализации он предлагает больше возможностей и лучшую производительность.

Установка:

pip install xlsxwriter

Пример создания отчета с форматированием и диаграммой:

import xlsxwriter

# Создание нового файла
workbook = xlsxwriter.Workbook('quarterly_report.xlsx')
worksheet = workbook.add_worksheet('Q1 Results')

# Форматирование
bold = workbook.add_format({'bold': True})
money = workbook.add_format({'num_format': '#,##0 ₽'})
percent = workbook.add_format({'num_format': '0.0%'})
header_format = workbook.add_format({
'bold': True,
'bg_color': '#D7E4BC',
'border': 1,
'align': 'center'
})

# Установка ширины столбцов
worksheet.set_column('A:A', 20)
worksheet.set_column('B:D', 15)

# Заголовки
worksheet.write('A1', 'Отдел', header_format)
worksheet.write('B1', 'План', header_format)
worksheet.write('C1', 'Факт', header_format)
worksheet.write('D1', 'Выполнение', header_format)

# Данные
departments = ['Продажи', 'Маркетинг', 'Разработка', 'Поддержка', 'Администрация']
planned = [150000, 80000, 240000, 95000, 36000]
actual = [165400, 72300, 238600, 100800, 35200]

# Заполнение данных
for row, (dept, plan, act) in enumerate(zip(departments, planned, actual), start=1):
worksheet.write(row, 0, dept)
worksheet.write(row, 1, plan, money)
worksheet.write(row, 2, act, money)
worksheet.write_formula(row, 3, f'=C{row+1}/B{row+1}', percent)

# Итоги
total_row = len(departments) + 1
worksheet.write(total_row, 0, 'Всего', bold)
worksheet.write_formula(total_row, 1, f'=SUM(B2:B{total_row})', money)
worksheet.write_formula(total_row, 2, f'=SUM(C2:C{total_row})', money)
worksheet.write_formula(total_row, 3, f'=C{total_row+1}/B{total_row+1}', percent)

# Создание диаграммы
chart = workbook.add_chart({'type': 'column'})

# Добавление серий данных
chart.add_series({
'name': '=Q1 Results!$B$1',
'categories': f'=Q1 Results!$A$2:$A${total_row}',
'values': f'=Q1 Results!$B$2:$B${total_row}',
'fill': {'color': '#8EB4E3'}
})

chart.add_series({
'name': '=Q1 Results!$C$1',
'categories': f'=Q1 Results!$A$2:$A${total_row}',
'values': f'=Q1 Results!$C$2:$C${total_row}',
'fill': {'color': '#ED7D31'}
})

# Настройка диаграммы
chart.set_title({'name': 'Квартальный отчет: план vs факт'})
chart.set_x_axis({'name': 'Отдел'})
chart.set_y_axis({'name': 'Сумма (₽)', 'num_format': '#,##0 ₽'})
chart.set_style(11)

# Вставка диаграммы в лист
worksheet.insert_chart('F2', chart, {'x_scale': 1.5, 'y_scale': 1.5})

# Условное форматирование
worksheet.conditional_format(f'D2:D{total_row}', {
'type': '3_color_scale',
'min_color': '#FF9999',
'mid_color': '#FFFF99',
'max_color': '#99CC99'
})

workbook.close()

XlsxWriter особенно хорош для следующих задач:

• Создание отчетов, ориентированных на печать или презентацию
• Генерация сложных диаграмм различных типов
• Применение условного форматирования и форматирования данных
• Добавление изображений, фигур и комментариев в документы
• Внедрение VBA-макросов в генерируемые файлы
• Создание сводных таблиц
• Обработка больших объемов данных с минимальным использованием памяти

XlsxWriter оптимизирован для производительности и имеет меньший расход памяти по сравнению с openpyxl при создании больших файлов. Если вам нужно только создавать (а не читать) Excel-файлы с богатым форматированием и визуализацией, XlsxWriter часто является лучшим выбором. 🚀

Gspread и Pygsheets: автоматизация работы с Google Sheets

Когда речь заходит о работе с Google Sheets, две библиотеки заслуживают особого внимания: gspread и pygsheets. Обе предоставляют Python-интерфейс к API Google Sheets, но имеют разный уровень абстракции и функциональность. 🌐

Установка библиотек:

pip install gspread oauth2client
pip install pygsheets

Для работы с обеими библиотеками требуется аутентификация через Google Cloud Platform:

1. Создайте проект в Google Cloud Console
2. Включите Google Sheets API и Google Drive API
3. Создайте учетные данные Service Account и скачайте JSON-файл с ключами
4. Поделитесь нужными Google-таблицами с email-адресом Service Account

Базовый пример работы с gspread:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Авторизация
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(credentials)

# Открытие таблицы по названию или URL
sheet = client.open('Отчет по продажам').sheet1

# Чтение данных
all_values = sheet.get_all_values() # Все значения
cell_value = sheet.acell('B2').value # Конкретная ячейка
row_values = sheet.row_values(3) # Строка целиком
col_values = sheet.col_values(2) # Столбец целиком

# Запись данных
sheet.update_cell(2, 3, 100) # Обновление ячейки (строка, столбец, значение)
sheet.update_cells('A5:C7', [[1, 2, 3], [4, 5, 6], [7, 8, 9]]) # Диапазон

# Добавление строк
new_row = ['Продукт X', 'Регион Y', 5000]
sheet.append_row(new_row)

# Форматирование (через batch_update)
request = {
"requests": [
{
"repeatCell": {
"range": {
"startRowIndex": 0,
"endRowIndex": 1
},
"cell": {
"userEnteredFormat": {
"backgroundColor": {
"red": 0.8,
"green": 0.8,
"blue": 0.8
},
"textFormat": {
"bold": True
}
}
},
"fields": "userEnteredFormat(backgroundColor,textFormat)"
}
}
]
}
sheet.spreadsheet.batch_update(request)

Пример использования pygsheets (более высокоуровневый API):

import pygsheets

# Авторизация
gc = pygsheets.authorize(service_file='credentials.json')

# Открытие таблицы
sh = gc.open('Отчет по продажам')
wks = sh.sheet1

# Чтение данных
df = wks.get_as_df() # Получить как pandas DataFrame
cell = wks.cell('B2') # Получить объект ячейки
value = wks.get_value('B2') # Получить только значение

# Запись данных
wks.update_cell('B2', 'Новое значение')
wks.update_values('A5:C7', [[1, 2, 3], [4, 5, 6], [7, 8, 9]])

# Работа с pandas DataFrame
import pandas as pd
df = pd.DataFrame({'Продукт': ['A', 'B', 'C'], 
'Продажи': [100, 200, 300]})
wks.set_dataframe(df, 'A1', copy_head=True)

# Форматирование
cell = wks.cell('B2')
cell.color = (0.8, 0.8, 0.8, 0) # RGB + альфа
cell.text_format['bold'] = True
cell.update()

# Создание диаграмм (через batch_update)
chart_request = {
"requests": [
{
"addChart": {
"chart": {
"spec": {
"title": "Продажи по продуктам",
"basicChart": {
"chartType": "COLUMN",
"series": [
{
"series": {
"sourceRange": {
"sources": [
{
"sheetId": wks.id,
"startRowIndex": 0,
"endRowIndex": 4,
"startColumnIndex": 1,
"endColumnIndex": 2
}
]
}
}
}
],
"domains": [
{
"domain": {
"sourceRange": {
"sources": [
{
"sheetId": wks.id,
"startRowIndex": 0,
"endRowIndex": 4,
"startColumnIndex": 0,
"endColumnIndex": 1
}
]
}
}
}
]
}
},
"position": {
"overlayPosition": {
"anchorCell": {
"sheetId": wks.id,
"rowIndex": 10,
"columnIndex": 3
}
}
}
}
}
}
]
}
sh.batch_update(chart_request)

Сравнение возможностей gspread и pygsheets:

• gspread — более легковесная библиотека с простым API, подходит для базовых операций
• pygsheets — более абстрагированный и полный API, имеет интеграцию с pandas, удобнее для сложных задач

Обе библиотеки имеют ограничения, связанные с квотами API Google (100 запросов в минуту на проект по умолчанию). Для оптимизации работы с большими данными рекомендуется:

• Использовать пакетные операции вместо обновления ячеек по одной
• Кэшировать данные на стороне Python, когда это возможно
• Применять метод batch_update для множественных изменений
• При работе с большими объёмами данных использовать pygsheets с pandas

Автоматизация Google Sheets особенно полезна для следующих задач:

• Централизованный доступ к данным из различных источников
• Автоматическое обновление дашбордов и отчетов
• Интеграция с другими сервисами Google (например, Forms)
• Совместная работа команды с автоматически обновляемыми данными
• Создание веб-приложений на базе Google Sheets как простой базы данных

С помощью этих библиотек вы можете полностью автоматизировать работу с Google Sheets, интегрировать их в бизнес-процессы и создавать мощные решения для совместной работы с данными. 🔄

Автоматизация работы с табличными данными через Python — это не просто удобство, а критический фактор эффективности современного бизнеса. Выбрав правильную библиотеку под конкретную задачу, вы можете сократить время обработки данных в десятки раз и полностью исключить человеческий фактор из рутинных процессов. Pandas идеально подойдёт для анализа и трансформации данных, openpyxl и XlsxWriter позволят создавать профессиональные отчёты в Excel с точным контролем форматирования, а gspread и pygsheets обеспечат интеграцию с облачными решениями Google. Инвестировав время в освоение этих инструментов сейчас, вы получите долгосрочное конкурентное преимущество через оптимизацию бизнес-процессов и возможность сосредоточиться на аналитике вместо механической обработки таблиц.