Automatização de uma planilha do Excel

Python, Excel

Photo by Carlos Muza on Unsplash

O objetivo desse post é automatizar uma planilha do Excel utilizando Python como linguagem. Vamos utilizar uma planilha com as vendas de três supermercados (o dataset completo e o código pode ser baixado no link do meu github no final do post) para gerar um gráfico de barras com a soma de cada tipo de produto dividido entre homem e mulher. Temos uma visão geral da planilha na figura 1.

Figura 1 - Parte da planilha de vendas

Fonte: O autor

O primeiro passo é importar os pacotes necessários para poder automatizar os processos no Excel. Os principais são: Pandas e openpyxl. Com eles, podemos, primeiramente, ler a planilha e selecionar quais colunas vamos trabalhar. Aqui, vamos só utilizar as colunas ‘Gender’, ‘Product line’ e ‘Total’, vistas na tabela 1, a fim de calcular os gastos total de homens e mulheres de cada linha de produto.

Tabela 1 - Colunas selecionadas

Fonte: O autor

Após esse passo, vamos fazer uma transposição da tabela e utilizar uma função de soma para calcular o total gasto. Vemos o resultado na tabela 2.

Tabela 2 - Tabela transposta

Fonte: O autor

O próximo passo é atribuir dinamicamente o valor mínimo e máximo de colunas e linhas da planilha. Depois, vamos adicionar o gráfico de barras e atribuir os valores obtidos anteriormente para preencher o gráfico. O código é o seguinte:

   barchart = BarChart()

   data = Reference(sheet,
                 min_col=min_column+1,
                 max_col=max_column,
                 min_row=min_row,
                 max_row=max_row) 
   categories = Reference(sheet,
                        min_col=min_column,
                        max_col=min_column,
                       min_row=min_row+1,
                       max_row=max_row)

Depois desse bloco de código, temos o gráfico na planilha, visto na figura 2.

Figura 2 - Gráfico de barras adicionado no Excel

Fonte: O autor

Para fazermos a linha com o total somado entre homens e mulheres, temos o seguinte bloco de código:

Primeiro gerar um ‘alfabeto’ com as letras A-G para podermos manipular as células no Excel.

alphabet =   list(string.ascii_uppercase)
excel_alphabet =   alphabet[0:max_column] 
print(excel_alphabet)

E, em seguida, um for loop para somar as células do total gasto por homens e mulheres.

for i in excel_alphabet:
    if i!='A':
        sheet2[f'{i}{max_row+1}'] =   f'=SUM({i}{min_row+1}:{i}{max_row})' 
         sheet2[f'{i}{max_row+1}'].number_format = 'R$ #,##0.00'
sheet[f'{excel_alphabet[0]}{max_row+1}']   = 'Total'

Na figura 3, temos o resultado do código.

Figura 3 - Planilha com a coluna ‘total’ adicionada

Fonte: O autor

Agora, para automatizarmos e poder fazer isso para todas as planilhas, temos que colocar todo o código numa função e aplicá-la para a planilha que deseja. O código é o seguinte:

def excel_auto(file_name):
    
    # ler o arquivo excel
    excel_f = pd.read_excel(file_name)
    
    # pivot table
    tabela1 =   excel_f.pivot_table(index='Gender', columns='Product   line', values='Total',   aggfunc='sum').round(0)
    
    # separando o nome do arquivo da   sua extensao
    mes_extensao = file_name.split('_')[1]
    
    # salvando o arquivo como excel
    tabela1.to_excel(f'relatorio_{mes_extensao}', sheet_name='vendas',startrow=4)
    
    # load no workbook e   selecionando a aba
    wb = load_workbook(f'relatorio_{mes_extensao}')
    sheet = wb['vendas']
    
    # referencia das celulas
    min_column = wb.active.min_column
    max_column = wb.active.max_column
    min_row = wb.active.min_row
    max_row = wb.active.max_row
    
    # adicionando o grafico de   barras
    barchart = BarChart()
    data = Reference(sheet,
                      min_col=min_column+1,
                      max_col=max_column,
                     min_row=min_row,
                     max_row=max_row) 
    categories = Reference(sheet,
                            min_col=min_column,
                            max_col=min_column,
                            min_row=min_row+1,
                            max_row=max_row) 
    
    # adicionando os dados e categorias
    barchart.add_data(data,   titles_from_data=True)
    barchart.set_categories(categories)
    
    sheet.add_chart(barchart, "B12")   #location   chart
    barchart.title = 'Vendas por tipo de produto'
    barchart.style = 10  #choose the chart style
    
    # aplicando a formula (soma)
    # criando o alfabeto para usar   como referencia
    
    alphabet =   list(string.ascii_uppercase)
    excel_alphabet = alphabet[0:max_column] 
    # somatoria nas colunas B-G
    for i in   excel_alphabet:
        if i!='A':
            sheet[f'{i}{max_row+1}'] =  f'=SUM({i}{min_row+1}:{i}{max_row})' 
            sheet[f'{i}{max_row+1}'].number_format = 'R$ #,##0.00'
    sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
    
    # pegando o nome do mes/ano
    mes = mes_extensao.split('.')[0]
    
    # formatação do relatorio
    sheet['A1']   = 'Relatório de Vendas'
    sheet['A2']   = '2021'
    sheet['A1'].font   = Font('Arial', bold=True,   size=20)
    sheet['A2'].font   = Font('Arial', bold=True,   size=10)
    wb.save(f'relatorio_{mes_extensao}')
    return 

Acima, temos o código para gerar o gráfico de barras, coluna ‘Total’ e, também, para formatar o nome do arquivo a ser gerado. O resultado final é visto na figura 4.

Figura 4 - Resultado final

Fonte: O autor

Para termos o mesmo resultado da figura acima em qualquer planilha, apenas precisamos executar a função excel_auto.

Vale lembrar que para utilizar o script que foi feito, os dados de entrada tem que estar dispostos da mesma maneira do arquivo original, o qual foi utilizado como base para o script.

Vou colocar 3 arquivos diferentes do original (com a mesma disposição) do arquivo original para vocês testarem, se quiserem.

Todo o código, planilhas geradas e esse pdf pode ser baixado no link do meu Github