Como utilizar Python para automatizar planilhas do Excel

O Python tem se tornado cada vez mais popular entre os entusiastas da ciência de dados e desenvolvedores de software. De acordo com o índice Tiobe (2022), Python é a 1ª linguagem de programação mais popular no mundo. Uma das razões para essa classificação é devido a linguagem suportar as áreas mais inovadoras de desenvolvimento de software, como IA, aprendizado de máquina e aprendizado profundo.

Além disso, Python é uma linguagem fácil de usar. Iniciantes com pouco conhecimento em programação podem facilmente aprender a sintaxe Python e utilizá-la para criar programas simples.

Como a linguagem possui várias bibliotecas, pacotes úteis e funções prontas para uso para automação que facilitam muito os testes, por que não utilizar Python para trabalhar com Excel e criar automatizações?

Pyhton e Excel

O Excel é uma das ferramentas de dados mais usadas nas empresas. Trabalhar com dados em Python tem várias vantagens, portanto, encontrar uma maneira de trabalhar com o Excel usando código é fundamental. Podemos dizer que já existe uma ótima ferramenta para usar o Excel com Python chamada Pandas.

Neste artigo, usaremos o pacote Pandas para realizar algumas manipulações básicas e criar Tabelas Dinâmicas como relatórios automatizados com base em um arquivo de dados em Excel.

Pré-requisitos

Para acompanhar este tutorial, você precisará ter:

• Conhecimento básico da linguagem de programação Python.
• Conhecimento em Excel.

1º Passo: analisando o conjunto de dados do Excel

Neste tutorial, usaremos um arquivo Excel criado pelo Frank Andrade para utilizar como exemplo. Vamos imaginar  a situação em que você use um documento em seu trabalho como entrada para fazer relatórios mensais através de tabelas dinâmicas das vendas. Você pode baixar o arquivo aqui.

Como essa planilha contém os dados com os quais você trabalhará, é importante revisá-la para estar familiarizado com o seu propósito.

1.1 Importando as bibliotecas e preparando o ambiente

Precisaremos importar as bibliotecas em Python para o nosso ambiente de trabalho a fim de criar as ações no Excel como a Tabela Dinâmica. Mas antes, é necessário instalá-las por meio do seu terminal:

pip install pandas
pip install openpyxl

Pandas é um pacote Python que fornece estruturas de dados rápidas, flexíveis e expressivas projetadas para tornar o trabalho com dados fácil e intuitivo. Sendo uma das bibliotecas mais usadas para criar essa integração entre Python e Excel, tratando a planilha como uma base de dados.

O Openpyxl trata o Excel como uma planilha, editando como um VBA e mantendo a estrutura original dos arquivos. Com esse módulo é possível fazer cálculos do Excel e criar gráficos e tabelas. Lembrando que vamos utilizar a plataforma Jupyter para executar o código, caso queira utilizar o mesmo ambiente, sugiro seguir o guia de instalação aqui.

Com o seu ambiente aberto, crie um arquivo e execute os seguintes comandos para importar as bibliotecas:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Para ler nosso arquivo ““supermarket_sales.xlsx”, usaremos a função pd.read_excel() conforme demonstrado no trecho de código abaixo:

table = pd.read_excel(“supermarket_sales.xlsx”)

Atenção: esse código precisa estar na mesma pasta do arquivo. Se necessário, indique o caminho no nome do arquivo, exemplo:

”C:\Users\Public\supermarket_sales.xlsx”

O resultado será a apresentação da tabela dentro do arquivo:

2º Passo: criando nossa Tabela Dinâmica a partir dos dados de entrada

O arquivo tem muitas colunas, mas para  simplificar o processo, usaremos apenas as colunas Gênero, Linha de produto e Total para o relatório que vamos criar.

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

Para criar nossa Tabela Dinâmica, usaremos a função .pivot_table() para mostrar, por exemplo, o dinheiro total gasto por homens e mulheres nas diferentes linhas de produtos.

Com isso em mente, executaremos o trecho de código abaixo:

report_table = excel_file.pivot_table(index='Gender', 
                                      columns='Product line', 
                                      values='Total', 
                                      aggfunc='sum').round(0)
display(report_table)

O relatório ficará como mostrado abaixo:

A função aggfun = 'sum' do pivot_table acima é usada para calcular a soma dos pontos nas colunas agrupada. Já a função .round(0) serve para retorno nulo.

2.1 Exportando a nossa Tabela Dinâmica para um arquivo em Excel

Agora, a função _to_excel() será usada para exportar nosso arquivo gerado via Python. Nesse método, especificaremos o nome do arquivo Excel de saída sedo “report_2022.xlsx” o nome que vamos nomear para ele.

Assim, é possível especificar o nome da planilha que queremos criar e em qual célula a Tabela Dinâmica será gravada.

report_table.to_excel('report_2022.xlsx', 
                      sheet_name='Report', 
                      startrow=4)

Após executar esse trecho, ao olhar para a pasta, você verá um novo arquivo excel criado a partir do método to_excel():

3º Passo: utilizando a biblioteca Openpyxl para gerar relatórios e criar referências

Entendemos até aqui um pouco do uso da biblioteca Pandas. Nesta seção, usaremos funções da biblioteca Openpyxl como load_workbook, quem cuidará de carregar o conteúdo do arquivo XLSX, a pasta de trabalho, para a memória; e a função .save() para salvá-la após a edição.

3.1 Criando referência de linha e coluna

Desse modo, para automatizar o relatório, precisamos identificar as colunas e linhas mínimas e máximas que ficarão ativas para garantir que, depois de adicionar mais dados à planilha, o código continue funcionando.

Carregamos, então, a pasta de trabalho utilizando o load_workbook() e localizamos a planilha que queremos trabalhar por meio do wb[‘name_of_sheet’],  por fim, acessamos as células ativas com .active.

wb = load_workbook('report_2022.xlsx')
sheet = wb['Report']


# Referência para a planilha original
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

4º Passo: automatizando o relatório em Excel utilizando Python

Agora que temos um relatório apresentado por meio de uma Tabela Dinâmica, a próxima e mais importante parte é automatização da sua criação. Então, na próxima vez que você quiser fazer esse relatório, basta digitar o nome do arquivo e executá-lo com o código Python.

Nesta parte, vamos compor todo o código utilizando uma função para simplificar a automatização do nosso relatório. Vamos imaginar que o arquivo original que baixamos tem o nome “sales_2022.xlsx” no lugar de  “supermarket_sales.xlsx”.

Com isso podemos aplicar a fórmula ao relatório escrevendo o seguinte:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
    # para ler o arquivo em excel
    excel_file = pd.read_excel(file_name)
    # para fazer a Tabela Dinâmica
    report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
    # dividindo o mês e a extensão do nome do arquivo
    month_and_extension = file_name.split('_')[1]
    # enviar a tabela do relatório para um arquivo excel
    report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
    # carregando pasta de trabalho e selecionando planilha
    wb = load_workbook(f'report_{month_and_extension}')
    sheet = wb['Report']
    # definindo as células de referência da planilha original
    min_column = wb.active.min_column
    max_column = wb.active.max_column
    min_row = wb.active.min_row
    max_row = wb.active.max_row
    # realizando get no nome do mês
    month_name = month_and_extension.split('.')[0]
    #salvando
    wb.save(f'report_{month_and_extension}')
    return

automate_excel('sales_2022.xlsx')

Depois de executar esse código, você verá um arquivo do Excel chamado “report_2022.xlsx” na mesma pasta em que o script Python está localizado:

Para aplicar a função em vários arquivos, é só aplicar a fórmula um por um, exemplo:

automate_excel('sales_january.xlsx') 
automate_excel('sales_february.xlsx') 
automate_excel('sales_march.xlsx')

Você também pode incrementar quais atividades deseja adicionar cada vez que for chamar a função para uma planilha, podendo formatar a tabela, criar gráficos, novas tabelas e muito mais. Neste site, você pode encontrar uma lista de estilos de formatação disponíveis.

Isso é só o começo

Espero que após a leitura deste artigo você possa usar o básico da automação de arquivos do Excel por meio de scripts Python. Lembrando que é possível fazer muito mais do que criar Tabelas Dinâmicas através dessas interações. Este foi apenas um exemplo para facilitar o aprendizado.

A biblioteca Pandas pode realizar diversas operações em sua base de dados, como análises e manipulações complexas. Dependendo da sua necessidade e experiência, é possível ir além do que se pode alcançar se estiver apenas usando o Excel. Um dos principais benefícios de usar essas bibliotecas em Python é automatizar e processar arquivos do Excel por meio de scripts, integrando os resultados ao seu fluxo de trabalho de dados de forma automatizada.

Aprenda mais sobre o tema

Separei aqui algumas referências importantes que podem te ajudar a se aprofundar sobre o universo de Python e Excel:

• A Simple Guide to Automate Your Excel Reporting with Python
• Automating Excel Sheet in Python
• Using Pandas in Excel