Python для Excel: автоматизация работы с таблицами и отчетами

pip install pandas openpyxl xlwings XlsxWriter

import pandas as pd

# Чтение всего файла
df = pd.read_excel('data.xlsx')

# Чтение конкретного листа
df = pd.read_excel('data.xlsx', sheet_name='Продажи')

# Чтение нескольких листов в словарь DataFrame'ов
all_sheets = pd.read_excel('data.xlsx', sheet_name=None)

# Чтение определенного диапазона
df = pd.read_excel('data.xlsx', skiprows=2, usecols="B:E", nrows=100)

print(df.head())

# Создание DataFrame
data = {
'Имя': ['Анна', 'Иван', 'Мария', 'Петр'],
'Возраст': [25, 30, 45, 35],
'Зарплата': [50000, 60000, 75000, 82000]
}
df = pd.DataFrame(data)

# Простая запись в Excel
df.to_excel('отчет.xlsx', sheet_name='Сотрудники', index=False)

# Запись нескольких DataFrame в разные листы
with pd.ExcelWriter('полный_отчет.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Сотрудники', index=False)
df.query('Возраст > 30').to_excel(writer, sheet_name='Старшие сотрудники', index=False)
df.groupby('Возраст').mean().to_excel(writer, sheet_name='Средняя зарплата по возрасту')

from openpyxl import load_workbook

# Загрузка рабочей книги
wb = load_workbook('data.xlsx')

# Получение конкретного листа
ws = wb['Продажи']

# Чтение значения конкретной ячейки
cell_value = ws['A1'].value
print(f'Значение ячейки A1: {cell_value}')

# Итерация по строкам
for row in ws.iter_rows(min_row=2, max_col=3, max_row=5):
for cell in row:
print(cell.value, end="\t")
print()

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

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

# Добавление данных
ws['A1'] = "Продукт"
ws['B1'] = "Регион"
ws['C1'] = "Продажи (₽)"

data = [
['Ноутбук', 'Москва', 120000],
['Смартфон', 'Санкт-Петербург', 85000],
['Планшет', 'Казань', 45000],
['Монитор', 'Новосибирск', 65000]
]

for i, row in enumerate(data, start=2):
ws.cell(row=i, column=1, value=row[0])
ws.cell(row=i, column=2, value=row[1])
ws.cell(row=i, column=3, value=row[2])

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

# Добавление границ
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=len(data)+1, max_col=3):
for cell in row:
cell.border = border

# Изменение ширины столбцов
ws.column_dimensions['A'].width = 15
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 15

# Сохранение файла
wb.save('форматированный_отчет.xlsx')

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

# Создаем книгу и лист
wb = Workbook()
ws = wb.active
ws.title = "Продажи по кварталам"

# Добавляем данные
rows = [
['Квартал', 'Москва', 'Санкт-Петербург', 'Екатеринбург'],
['Q1', 8500, 7200, 4300],
['Q2', 9100, 7800, 4800],
['Q3', 7800, 7100, 4100],
['Q4', 9500, 8300, 5200]
]

for row in rows:
ws.append(row)

# Создаем столбчатую диаграмму
chart = BarChart()
chart.title = "Квартальные продажи по городам"
chart.y_axis.title = "Продажи (тыс. ₽)"
chart.x_axis.title = "Квартал"

# Определяем данные для диаграммы
data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=5)
cats = Reference(ws, min_col=1, min_row=2, max_row=5)

# Добавляем данные и категории
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4 # Стиль диаграммы

# Размещаем диаграмму на листе
ws.add_chart(chart, "F2")

# Сохраняем файл
wb.save("квартальный_отчет.xlsx")

from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule

wb = Workbook()
ws = wb.active

# Добавляем данные о продажах
sales_data = [
["Менеджер", "План", "Факт", "Выполнение (%)"],
["Иванов А.П.", 150000, 142000, "=C2/B2"],
["Петрова И.С.", 120000, 175000, "=C3/B3"],
["Сидоров К.В.", 200000, 163000, "=C4/B4"],
["Козлова А.А.", 180000, 210000, "=C5/B5"]
]

for row in sales_data:
ws.append(row)

# Форматируем проценты
for row in range(2, 6):
ws[f'D{row}'].number_format = '0.00%'

# Добавляем условное форматирование
# 1. Цветовая шкала для столбца "Факт"
colorscale = ColorScaleRule(
start_type='min',
start_color='FFAAAA',
end_type='max',
end_color='AAFFAA'
)
ws.conditional_formatting.add(f'C2:C5', colorscale)

# 2. Правило для выполнения плана
# Если < 90% – красный
# Если >= 90% и < 100% – желтый
# Если >= 100% – зеленый
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')

ws.conditional_formatting.add(f'D2:D5', CellIsRule(
operator='lessThan',
formula=['0.9'],
fill=red_fill
))

ws.conditional_formatting.add(f'D2:D5', CellIsRule(
operator='between',
formula=['0.9', '1'],
fill=yellow_fill
))

ws.conditional_formatting.add(f'D2:D5', CellIsRule(
operator='greaterThanOrEqual',
formula=['1'],
fill=green_fill
))

# Сохраняем файл
wb.save("отчет_по_продажам.xlsx")

import pandas as pd

# Создаем тестовые данные о продажах
data = {
'Дата': pd.date_range(start='2023-01-01', periods=100, freq='D'),
'Регион': ['Москва', 'Санкт-Петербург', 'Новосибирск', 'Екатеринбург'] * 25,
'Менеджер': ['Иванов', 'Петров', 'Сидоров', 'Козлова', 'Морозова'] * 20,
'Продукт': ['Ноутбук', 'Смартфон', 'Планшет', 'Монитор'] * 25,
'Количество': [round(abs(pd.np.random.randn())) + 1 for _ in range(100)],
'Сумма': [round(pd.np.random.randint(10000, 100000), -2) for _ in range(100)]
}

df = pd.DataFrame(data)
df['Месяц'] = df['Дата'].dt.strftime('%Y-%m')

# Создаем сводную таблицу
pivot1 = pd.pivot_table(
df, 
values='Сумма',
index=['Регион', 'Менеджер'],
columns=['Месяц'],
aggfunc='sum',
fill_value=0,
margins=True,
margins_name='Итого'
)

# Создаем вторую сводную таблицу
pivot2 = pd.pivot_table(
df,
values=['Количество', 'Сумма'],
index='Продукт',
columns='Регион',
aggfunc={'Количество': 'sum', 'Сумма': 'sum'},
fill_value=0,
margins=True
)

# Записываем результаты в Excel
with pd.ExcelWriter('сводные_отчеты.xlsx', engine='openpyxl') as writer:
pivot1.to_excel(writer, sheet_name='Продажи по регионам')
pivot2.to_excel(writer, sheet_name='Продажи по продуктам')

# Получаем рабочую книгу для дополнительного форматирования
workbook = writer.book
worksheet1 = writer.sheets['Продажи по регионам']
worksheet2 = writer.sheets['Продажи по продуктам']

# Устанавливаем ширину столбцов
for ws in [worksheet1, worksheet2]:
for column in ws.columns:
max_length = 0
column_letter = column[0].column_letter
for cell in column:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
adjusted_width = (max_length + 2) * 1.2
ws.column_dimensions[column_letter].width = adjusted_width

import pandas as pd
import glob
import os

def consolidate_excel_files(directory_path, output_filename):
"""Объединяет все Excel-файлы из указанной директории в один."""
# Получаем список всех Excel-файлов в директории
all_files = glob.glob(os.path.join(directory_path, "*.xlsx"))

# Создаем пустой список для хранения DataFrame
all_data = []

# Обрабатываем каждый файл
for file in all_files:
file_name = os.path.basename(file)
print(f"Обработка файла: {file_name}")

# Читаем данные из файла
df = pd.read_excel(file)

# Добавляем столбец с именем исходного файла
df['Источник'] = file_name

# Добавляем данные в общий список
all_data.append(df)

# Объединяем все данные в один DataFrame
if all_data:
consolidated_df = pd.concat(all_data, ignore_index=True)

# Записываем результат в новый Excel-файл
consolidated_df.to_excel(output_filename, index=False)
print(f"Консолидированные данные сохранены в {output_filename}")
return consolidated_df
else:
print("Excel-файлы не найдены в указанной директории!")
return None

# Пример использования
consolidated_data = consolidate_excel_files("./regional_reports/", "consolidated_report.xlsx")

# Если нужно, можем сразу сделать базовый анализ
if consolidated_data is not None:
# Подсчитываем статистику по регионам
regional_stats = consolidated_data.groupby('Регион').agg({
'Продажи': ['sum', 'mean', 'min', 'max', 'count']
})

# Сохраняем статистику в отдельный лист
with pd.ExcelWriter('consolidated_report.xlsx', mode='a', engine='openpyxl') as writer:
regional_stats.to_excel(writer, sheet_name='Статистика по регионам')

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.chart import BarChart, Reference
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
import os

def create_sales_report(sales_data, manager_name, output_file):
"""Создает персонализированный отчет по продажам для менеджера."""
# Фильтруем данные для конкретного менеджера
manager_data = sales_data[sales_data['Менеджер'] == manager_name].copy()

if manager_data.empty:
print(f"Данные для менеджера {manager_name} не найдены!")
return False

# Создаем новую книгу Excel
wb = Workbook()
ws = wb.active
ws.title = f"Отчет {manager_name}"

# Добавляем заголовок
ws.merge_cells('A1:E1')
title_cell = ws['A1']
title_cell.value = f"Отчет по продажам: {manager_name}"
title_cell.font = Font(size=16, bold=True)
title_cell.alignment = Alignment(horizontal='center')

# Добавляем заголовки столбцов
headers = ['Дата', 'Продукт', 'Регион', 'Количество', 'Сумма']
for col_num, header in enumerate(headers, 1):
cell = ws.cell(row=3, column=col_num)
cell.value = header
cell.font = Font(bold=True)
cell.fill = PatternFill(start_color="DDDDDD", end_color="DDDDDD", fill_type="solid")

# Добавляем данные
for row_num, (_, row) in enumerate(manager_data.iterrows(), 4):
ws.cell(row=row_num, column=1).value = row['Дата'].strftime('%Y-%m-%d')
ws.cell(row=row_num, column=2).value = row['Продукт']
ws.cell(row=row_num, column=3).value = row['Регион']
ws.cell(row=row_num, column=4).value = row['Количество']
ws.cell(row=row_num, column=5).value = row['Сумма']

# Добавляем итоги
total_row = len(manager_data) + 4
ws.cell(row=total_row, column=3).value = 'ИТОГО:'
ws.cell(row=total_row, column=3).font = Font(bold=True)
ws.cell(row=total_row, column=4).value = manager_data['Количество'].sum()
ws.cell(row=total_row, column=4).font = Font(bold=True)
ws.cell(row=total_row, column=5).value = manager_data['Сумма'].sum()
ws.cell(row=total_row, column=5).font = Font(bold=True)

# Создаем график продаж по продуктам
product_sales = manager_data.groupby('Продукт')['Сумма'].sum().reset_index()

# Добавляем данные для графика на отдельном листе
ws_chart = wb.create_sheet(title="График продаж")
ws_chart['A1'] = "Продукт"
ws_chart['B1'] = "Сумма продаж"

for i, (product, sales_sum) in enumerate(zip(product_sales['Продукт'], product_sales['Сумма']), 2):
ws_chart[f'A{i}'] = product
ws_chart[f'B{i}'] = sales_sum

# Создаем график
chart = BarChart()
chart.title = "Продажи по продуктам"
chart.y_axis.title = "Сумма продаж (₽)"
chart.x_axis.title = "Продукт"

data = Reference(ws_chart, min_col=2, min_row=1, max_row=len(product_sales)+1)
cats = Reference(ws_chart, min_col=1, min_row=2, max_row=len(product_sales)+1)

chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

ws_chart.add_chart(chart, "D2")

# Настройка ширины столбцов
for column in ws.columns:
max_length = 0
column_letter = column[0].column_letter
for cell in column:
if cell.value:
max_length = max(max_length, len(str(cell.value)))
adjusted_width = (max_length + 2) * 1.2
ws.column_dimensions[column_letter].width = adjusted_width

# Сохраняем файл
wb.save(output_file)
return True

def send_email(to_email, subject, body, attachment_path):
"""Отправляет email с вложением."""
from_email = "your_email@example.com" # Замените на ваш email
password = "your_password" # Замените на ваш пароль

msg = MIMEMultipart()
msg['From'] = from_email
msg['To'] = to_email
msg['Subject'] = subject

msg.attach(MIMEText(body, 'plain'))

# Прикрепляем файл
with open(attachment_path, "rb") as attachment:
part = MIMEBase("application", "octet-stream")
part.set_payload(attachment.read())

encoders.encode_base64(part)
part.add_header(
"Content-Disposition",
f"attachment; filename= {os.path.basename(attachment_path)}",
)

msg.attach(part)

# Отправляем письмо
try:
server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login(from_email, password)
server.send_message(msg)
server.quit()
print(f"Email успешно отправлен на {to_email}")
return True
except Exception as e:
print(f"Ошибка при отправке email: {e}")
return False

# Основная функция для создания и рассылки отчетов
def generate_and_send_reports(data_file):
# Загружаем данные
sales_data = pd.read_excel(data_file)

# Получаем список уникальных менеджеров
managers = sales_data['Менеджер'].unique()

# Словарь с email-адресами менеджеров (в реальности, это может быть загружено из БД)
manager_emails = {
'Иванов': 'ivanov@example.com',
'Петров': 'petrov@example.com',
'Сидоров': 'sidorov@example.com'
# ... и т.д.
}

# Создаем и отправляем отчеты для каждого менеджера
for manager in managers:
output_file = f"report_{manager}.xlsx"

# Создаем отчет
if create_sales_report(sales_data, manager, output_file):
# Если есть email для этого менеджера, отправляем отчет
if manager in manager_emails:
subject = f"Ваш отчет по продажам за {pd.Timestamp.now().strftime('%B %Y')}"
body = f"""
Уважаемый(-ая) {manager},

Во вложении находится ваш ежемесячный отчет по продажам.

С уважением,
Система автоматической рассылки отчетов
"""

send_email(manager_emails[manager], subject, body, output_file)
else:
print(f"Email для менеджера {manager} не найден в базе!")
else:
print(f"Не удалось создать отчет для менеджера {manager}")

# Запускаем процесс
generate_and_send_reports("sales_data.xlsx")

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
import os

def analyze_financial_data(input_file, output_file):
"""Анализирует финансовые данные и создает отчет с визуализациями."""
# Загружаем данные
df = pd.read_excel(input_file)

# Преобразуем даты в формат datetime
df['Дата'] = pd.to_datetime(df['Дата'])
df['Месяц'] = df['Дата'].dt.strftime('%Y-%m')

# Подготовка данных для анализа
monthly_revenue = df.groupby('Месяц')['Доход'].sum()
monthly_expenses = df.groupby('Месяц')['Расходы'].sum()
monthly_profit = monthly_revenue – monthly_expenses

# Расчет дополнительных метрик
profit_margin = (monthly_profit / monthly_revenue) * 100
cumulative_profit = monthly_profit.cumsum()

# Создаем визуализации
plt.figure(figsize=(10, 6))
plt.plot(monthly_revenue.index, monthly_revenue.values, 'b-', label='Доход')
plt.plot(monthly_expenses.index, monthly_expenses.values, 'r-', label='Расходы')
plt.plot(monthly_profit.index, monthly_profit.values, 'g-', label='Прибыль')
plt.title('Финансовые показатели по месяцам')
plt.xlabel('Месяц')
plt.ylabel('Сумма (₽)')
plt.grid(True)
plt.legend()
plt.xticks(rotation=45)
plt.tight_layout()
plt.savefig('financial_trends.png')

plt.figure(figsize=(10, 6))
plt.bar(profit_margin.index, profit_margin.values)
plt.title('Маржинальность по месяцам (%)')
plt.xlabel('Месяц')
plt.ylabel('Маржинальность (%)')
plt.grid(True, axis='y')
plt.xticks(rotation=45)
plt.tight_layout()
plt.savefig('profit_margin.png')

# Создаем сводную таблицу для анализа по категориям
category_pivot = pd.pivot_table(
df,
values=['Доход', 'Расходы'],
index='Категория',
columns='Месяц',
aggfunc='sum',
fill_value=0,
margins=True,
margins_name='Итого'
)

# Сохраняем результаты в Excel
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
# Сохраняем основные данные
df.to_excel(writer, sheet_name='Исходные данные', index=False)

# Создаем сводную таблицу с ежемесячными показателями
monthly_summary = pd.DataFrame({
'Доход': monthly_revenue,
'Расходы': monthly_expenses,
'Прибыль': monthly_profit,
'Маржинальность (%)': profit_margin,
'Накопительная прибыль': cumulative_profit
})
monthly_summary.to_excel(writer, sheet_name='Ежемесячные показатели')

# Сохраняем сводную таблицу по категориям
category_pivot.to_excel(writer, sheet_name='Анализ по категориям')

# Добавляем графики в существующий файл Excel
wb = load_workbook(output_file)

# Создаем новый лист для графиков
if 'Графики' in wb.sheetnames:
ws_graphs = wb['Графики']
else:
ws_graphs = wb.create_sheet('Графики')

# Добавляем заголовок
ws_graphs['A1'] = 'Визуализация финансовых показателей'
ws_graphs['A1'].font = wb['Исходные данные']['A1'].font.copy(size=16, bold=True)

# Добавляем графики
img1 = Image('financial_trends.png')
ws_graphs.add_image(img1, 'A3')

img2 = Image('profit_margin.png')
ws_graphs.add_image(img2, 'A20')

# Сохраняем изменения
wb.save(output_file)

# Удаляем временные файлы с графиками
os.remove('financial_trends.png')
os.remove('profit_margin.png')

print(f"Анализ финансовых данных завершен. Результаты сохранены в {output_file}")

# Пример использования
analyze_financial_data('financial_data.xlsx', 'financial_analysis.xlsx')