Como Automatizar Relatórios Excel com Python: Guia Completo 2024
🤖 Por que Automatizar Relatórios Excel?
Se você passa horas criando relatórios Excel manualmente, este tutorial vai revolucionar seu fluxo de trabalho. A automação de relatórios não é apenas uma tendência, é uma necessidade para profissionais que querem se destacar no mercado.
Os Problemas dos Relatórios Manuais
- ⏰ Tempo perdido: Horas gastas em tarefas repetitivas
- 🐛 Erros humanos: Copy/paste incorreto, fórmulas quebradas
- 😴 Trabalho tedioso: Desmotivação da equipe
- 📊 Atrasos: Relatórios entregues fora do prazo
- 💰 Custo alto: Tempo = dinheiro
🛠️ Ferramentas Necessárias
Antes de começar, vamos instalar as bibliotecas essenciais:
# Instalação das dependências
pip install pandas openpyxl xlsxwriter matplotlib seaborn python-dotenv
Bibliotecas que Vamos Usar
- pandas: Manipulação e análise de dados
- openpyxl: Leitura/escrita de arquivos Excel (.xlsx)
- xlsxwriter: Criação de planilhas com formatação avançada
- matplotlib/seaborn: Geração de gráficos
- python-dotenv: Gerenciamento de variáveis de ambiente
📊 Estrutura do Projeto
Nosso automatizador terá as seguintes funcionalidades:
projeto_automacao/
├── src/
│ ├── __init__.py
│ ├── database.py # Conexão com banco
│ ├── excel_processor.py # Processamento Excel
│ └── report_generator.py # Gerador de relatórios
├── templates/
│ └── template_relatorio.xlsx
├── output/
├── .env
└── main.py
💻 Implementação Passo a Passo
1. Configuração do Ambiente
Primeiro, vamos criar o arquivo .env para nossas configurações:
# .env
DATABASE_HOST=localhost
DATABASE_NAME=vendas_db
DATABASE_USER=usuario
DATABASE_PASSWORD=senha123
SMTP_SERVER=smtp.gmail.com
[email protected]
SMTP_PASSWORD=senha_email
2. Conexão com Banco de Dados
# src/database.py
import pandas as pd
import sqlite3
from sqlalchemy import create_engine
import os
from dotenv import load_dotenv
load_dotenv()
class DatabaseManager:
def __init__(self):
self.host = os.getenv('DATABASE_HOST', 'localhost')
self.database = os.getenv('DATABASE_NAME')
self.user = os.getenv('DATABASE_USER')
self.password = os.getenv('DATABASE_PASSWORD')
def get_connection_string(self):
return f"mysql+pymysql://{self.user}:{self.password}@{self.host}/{self.database}"
def execute_query(self, query):
"""Executa query e retorna DataFrame"""
try:
engine = create_engine(self.get_connection_string())
df = pd.read_sql(query, engine)
print(f"✅ Query executada: {len(df)} registros retornados")
return df
except Exception as e:
print(f"❌ Erro na query: {e}")
return pd.DataFrame()
def get_sales_data(self, start_date, end_date):
"""Obtém dados de vendas do período"""
query = f"""
SELECT
v.data_venda,
v.valor_total,
p.nome as produto,
c.nome as cliente,
ve.nome as vendedor,
v.quantidade
FROM vendas v
INNER JOIN produtos p ON v.produto_id = p.id
INNER JOIN clientes c ON v.cliente_id = c.id
INNER JOIN vendedores ve ON v.vendedor_id = ve.id
WHERE v.data_venda BETWEEN '{start_date}' AND '{end_date}'
ORDER BY v.data_venda DESC
"""
return self.execute_query(query)
3. Processador de Excel
# src/excel_processor.py
import pandas as pd
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.chart import BarChart, Reference
import matplotlib.pyplot as plt
import seaborn as sns
class ExcelProcessor:
def __init__(self):
self.workbook = None
self.filename = None
def create_workbook(self, filename):
"""Cria nova planilha"""
self.filename = filename
self.workbook = openpyxl.Workbook()
# Remove planilha padrão
self.workbook.remove(self.workbook.active)
def add_data_sheet(self, df, sheet_name, format_as_table=True):
"""Adiciona planilha com dados"""
ws = self.workbook.create_sheet(title=sheet_name)
# Escrever dados
for r_idx, row in enumerate(df.itertuples(index=False), 1):
for c_idx, value in enumerate(row, 1):
ws.cell(row=r_idx, column=c_idx, value=value)
# Formatação do cabeçalho
if len(df) > 0:
for col in range(1, len(df.columns) + 1):
cell = ws.cell(row=1, column=col)
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
cell.alignment = Alignment(horizontal="center")
# Auto-ajustar larguras
for column in ws.columns:
max_length = 0
column_letter = column[0].column_letter
for cell in column:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = min(max_length + 2, 50)
ws.column_dimensions[column_letter].width = adjusted_width
def add_summary_sheet(self, sales_data):
"""Cria planilha de resumo com KPIs"""
ws = self.workbook.create_sheet(title="Resumo Executivo")
# Calcular KPIs
total_vendas = sales_data['valor_total'].sum()
media_vendas = sales_data['valor_total'].mean()
qtd_vendas = len(sales_data)
ticket_medio = total_vendas / qtd_vendas if qtd_vendas > 0 else 0
# Top 5 produtos
top_produtos = sales_data.groupby('produto')['valor_total'].sum().sort_values(ascending=False).head(5)
# Top 5 vendedores
top_vendedores = sales_data.groupby('vendedor')['valor_total'].sum().sort_values(ascending=False).head(5)
# Escrever KPIs
kpis = [
["KPI", "Valor"],
["Total de Vendas", f"R$ {total_vendas:,.2f}"],
["Quantidade de Vendas", qtd_vendas],
["Ticket Médio", f"R$ {ticket_medio:,.2f}"],
["Média por Venda", f"R$ {media_vendas:,.2f}"]
]
for row_idx, row_data in enumerate(kpis, 1):
for col_idx, value in enumerate(row_data, 1):
cell = ws.cell(row=row_idx, column=col_idx, value=value)
if row_idx == 1: # Cabeçalho
cell.font = Font(bold=True)
cell.fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid")
# Adicionar tabelas de top performers
start_row = len(kpis) + 3
# Top Produtos
ws.cell(row=start_row, column=1, value="Top 5 Produtos").font = Font(bold=True, size=14)
for idx, (produto, valor) in enumerate(top_produtos.items(), start_row + 2):
ws.cell(row=idx, column=1, value=produto)
ws.cell(row=idx, column=2, value=f"R$ {valor:,.2f}")
# Top Vendedores
ws.cell(row=start_row, column=4, value="Top 5 Vendedores").font = Font(bold=True, size=14)
for idx, (vendedor, valor) in enumerate(top_vendedores.items(), start_row + 2):
ws.cell(row=idx, column=4, value=vendedor)
ws.cell(row=idx, column=5, value=f"R$ {valor:,.2f}")
def add_chart_sheet(self, sales_data):
"""Adiciona planilha com gráficos"""
ws = self.workbook.create_sheet(title="Gráficos")
# Dados para gráfico de vendas por produto
product_sales = sales_data.groupby('produto')['valor_total'].sum().sort_values(ascending=False).head(10)
# Escrever dados para o gráfico
ws.cell(row=1, column=1, value="Produto")
ws.cell(row=1, column=2, value="Vendas")
for idx, (produto, valor) in enumerate(product_sales.items(), 2):
ws.cell(row=idx, column=1, value=produto)
ws.cell(row=idx, column=2, value=valor)
# Criar gráfico de barras
chart = BarChart()
chart.title = "Top 10 Produtos por Vendas"
chart.x_axis.title = "Produtos"
chart.y_axis.title = "Valor Total (R$)"
data = Reference(ws, min_col=2, min_row=1, max_row=len(product_sales) + 1)
categories = Reference(ws, min_col=1, min_row=2, max_row=len(product_sales) + 1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "D2")
def save_workbook(self):
"""Salva a planilha"""
if self.workbook and self.filename:
self.workbook.save(self.filename)
print(f"✅ Relatório salvo: {self.filename}")
4. Gerador de Relatórios Principal
# src/report_generator.py
from datetime import datetime, timedelta
import os
from .database import DatabaseManager
from .excel_processor import ExcelProcessor
class ReportGenerator:
def __init__(self):
self.db = DatabaseManager()
self.excel = ExcelProcessor()
def generate_monthly_report(self, year, month):
"""Gera relatório mensal"""
print(f"🔄 Gerando relatório para {month:02d}/{year}")
# Calcular datas
start_date = f"{year}-{month:02d}-01"
if month == 12:
end_date = f"{year + 1}-01-01"
else:
end_date = f"{year}-{month + 1:02d}-01"
# Obter dados
sales_data = self.db.get_sales_data(start_date, end_date)
if sales_data.empty:
print("❌ Nenhum dado encontrado para o período")
return False
# Criar relatório Excel
output_dir = "output"
os.makedirs(output_dir, exist_ok=True)
filename = f"{output_dir}/relatorio_vendas_{year}_{month:02d}.xlsx"
self.excel.create_workbook(filename)
# Adicionar planilhas
self.excel.add_data_sheet(sales_data, "Dados Detalhados")
self.excel.add_summary_sheet(sales_data)
self.excel.add_chart_sheet(sales_data)
# Salvar
self.excel.save_workbook()
print(f"✅ Relatório concluído: {filename}")
return True
def generate_custom_report(self, start_date, end_date, report_name="custom"):
"""Gera relatório personalizado"""
print(f"🔄 Gerando relatório personalizado: {start_date} a {end_date}")
sales_data = self.db.get_sales_data(start_date, end_date)
if sales_data.empty:
print("❌ Nenhum dado encontrado para o período")
return False
output_dir = "output"
os.makedirs(output_dir, exist_ok=True)
filename = f"{output_dir}/relatorio_{report_name}_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
self.excel.create_workbook(filename)
self.excel.add_data_sheet(sales_data, "Dados")
self.excel.add_summary_sheet(sales_data)
self.excel.add_chart_sheet(sales_data)
self.excel.save_workbook()
return True
5. Script Principal
# main.py
from src.report_generator import ReportGenerator
from datetime import datetime
import argparse
def main():
parser = argparse.ArgumentParser(description='Gerador de Relatórios Automatizado')
parser.add_argument('--tipo', choices=['mensal', 'custom'], default='mensal',
help='Tipo de relatório')
parser.add_argument('--ano', type=int, default=datetime.now().year,
help='Ano do relatório')
parser.add_argument('--mes', type=int, default=datetime.now().month,
help='Mês do relatório')
parser.add_argument('--inicio', type=str, help='Data início (YYYY-MM-DD)')
parser.add_argument('--fim', type=str, help='Data fim (YYYY-MM-DD)')
args = parser.parse_args()
generator = ReportGenerator()
try:
if args.tipo == 'mensal':
success = generator.generate_monthly_report(args.ano, args.mes)
else:
if not args.inicio or not args.fim:
print("❌ Para relatório personalizado, forneça --inicio e --fim")
return
success = generator.generate_custom_report(args.inicio, args.fim)
if success:
print("🎉 Relatório gerado com sucesso!")
else:
print("❌ Falha na geração do relatório")
except Exception as e:
print(f"❌ Erro: {e}")
if __name__ == "__main__":
main()
🚀 Como Usar
Execução Manual
# Relatório do mês atual
python main.py
# Relatório de mês específico
python main.py --ano 2024 --mes 3
# Relatório personalizado
python main.py --tipo custom --inicio 2024-01-01 --fim 2024-03-31
Automação com Cron (Linux/Mac)
# Editar crontab
crontab -e
# Executar todo dia 1 às 08:00
0 8 1 * * cd /caminho/projeto && python main.py
Automação com Task Scheduler (Windows)
- Abra o Agendador de Tarefas
- Crie Nova Tarefa
- Configure para executar mensalmente
- Ação: Executar
python main.py
📈 Benefícios Alcançados
Após implementar essa automação, você vai experimentar:
⏰ Economia de Tempo
- Antes: 4-6 horas para criar relatório mensal
- Depois: 5 minutos de execução automática
- Economia: 95% do tempo
🎯 Precisão
- Eliminação de erros manuais
- Consistência na formatação
- Padronização de cálculos
📊 Escalabilidade
- Adicionar novos KPIs facilmente
- Suporte a múltiplas fontes de dados
- Relatórios personalizados sob demanda
🔧 Próximos Passos
Funcionalidades Avançadas
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
def send_report_email(filename, recipients):
# Código de envio de email
pass
- Envio Automático por Email
# Conectar com CRM, ERP, etc.
import requests
def fetch_crm_data():
response = requests.get('https://api.crm.com/sales')
return response.json()
- Integração com APIs
import streamlit as st
import plotly.express as px
def create_dashboard():
st.title("Dashboard de Vendas")
# Interface web interativa
- Dashboard Web
💡 Dicas Pro
1. Tratamento de Erros Robusto
try:
# Operação
pass
except pd.errors.EmptyDataError:
print("❌ Arquivo vazio")
except FileNotFoundError:
print("❌ Arquivo não encontrado")
except Exception as e:
print(f"❌ Erro inesperado: {e}")
2. Logging Detalhado
import logging
logging.basicConfig(
level=logging.INFO,
format='%(asctime)s - %(levelname)s - %(message)s',
handlers=[
logging.FileHandler('relatorios.log'),
logging.StreamHandler()
]
)
3. Configuração Flexível
# config.yaml
database:
host: localhost
port: 3306
reports:
output_dir: ./output
template_dir: ./templates
🎯 Conclusão
A automação de relatórios Excel com Python não é apenas uma otimização - é uma transformação na forma como você trabalha. Com este guia, você pode:
✅ Eliminar tarefas manuais repetitivas
✅ Reduzir erros em 99%
✅ Economizar dezenas de horas por mês
✅ Impressionar clientes e gestores
✅ Escalar seus serviços como freelancer
🚀 Próximo Nível
Quer levar suas habilidades ainda mais longe? A Techzentrix oferece consultoria especializada em automação de processos empresariais. Entre em contato e descubra como podemos transformar seus processos manuais em soluções automatizadas eficientes.
Transforme tempo em valor. Automatize hoje mesmo! 🚀
---
Gostou do tutorial? Compartilhe com colegas e deixe sua dúvida nos comentários. Em breve, mais conteúdo sobre automação e Python no blog da Techzentrix!