Python e Excel: 4 dicas avançadas para automatizar suas planilhas

Já sabe o básico de Excel e Python? Então, leia este artigo para aprender como aplicar ferramentas avançadas de Excel através de Python, incluindo fórmulas, validação de dados e formatação condicional.

No artigo sobre automatização do Excel com Python, aprendemos o básico da biblioteca openpyxl. Descobrimos como criar um arquivo Excel, escrever dados em suas células e salvar o resultado em disco. Essas operações, embora fundamentais, são ainda bastante básicas para o uso avançado de Excel e Python.

Quem usa Excel no dia a dia sabe que a força do programa está em ferramentas como fórmulas, validação de entrada de dados e formatação condicional. Portanto, vamos aprender a replicar essas funcionalidades avançadas de Excel em Python para automatizar a criação de planilhas complexas e bem estruturadas.

Este artigo foi escrito com base na biblioteca openpyxl (versão 3.0.1). Dessa forma, se você não está familiarizado com o uso dessa biblioteca, recomendamos a leitura do nosso artigo introdutório. Caso contrário, vamos em frente!

Como escrever uma função de Excel em Python

Em todos os exemplos, trabalharemos com o arquivo “vendas.xlsx”, o qual você pode fazer o download clicando no botão abaixo:

Esse arquivo é uma planilha de Excel contendo uma única aba Vendas, conforme a imagem a seguir:

A planilha representa vendas de produtos de uma loja e cada linha corresponde a uma venda. É uma planilha comum, sem muita formatação, mas vamos trabalhar para deixá-la mais prática e informativa.

Adicionando o Valor Total

Logo de início, percebemos que uma informação importante está faltando: o valor total de cada venda (isto é, o produto entre as colunas Preço e Quantidade).

Esse resultado é facilmente obtido com uma fórmula de Excel. Se estivéssemos trabalhando diretamente nesse software, a fórmula da célula E2 seria =C2*D2, para célula E3 seria =C3*D3 e assim por diante.

Para reproduzir uma fórmula de Excel em Python, simplesmente escrevemos a fórmula como um string dentro da célula. O código abaixo utiliza um for loop simples para inserir a fórmula progressivamente nas células da coluna E, a qual chamamos de Valor total:

import openpyxl

MAX_LINHAS = 1000

wb = openpyxl.load_workbook('vendas.xlsx')
planilha_vendas = wb['Vendas']
planilha_vendas['E1'] = 'Valor total'
for i in range(2, MAX_LINHAS+1):
    formula = f'=C{i} * D{i}'
    planilha_vendas[f'E{i}'] = formula


wb.save('vendas_formatado.xlsx')

Observações sobre o código

• Usamos a variável auxiliar MAX_LINHAS = 1000 para aplicar a fórmula mesmo para linhas que ainda estão sem dados. Se quiséssemos aplicar a fórmula apenas até a última linha com dados, poderíamos utilizar o atributo planilha_vendas.max_row.
• Como comentado anteriormente, as fórmulas são inseridas como strings. Utilizamos a formatação conhecida como f-strings para construir a fórmula a cada iteração do loop. Assim, na primeira iteração a fórmula  da célula E2 é =C2*D2, na segunda a fórmula da célula E3 é =C3*D3 e assim por diante.

Abaixo, a apresentação da planilha até aqui:

Importante

Por padrão, ao abrirmos uma planilha com openpyxl, as fórmulas são carregadas como strings. Ou seja, é possível ver a descrição da fórmula, mas não o seu resultado. Veja o exemplo:

import openpyxl

wb = openpyxl.load_workbook('vendas_formatado.xlsx')
planilha_vendas = wb['Vendas']
for celula in planilha_vendas['E']:
    print(celula.value)


# output:
# Valor total
# =C2 * D2
# =C3 * D3
# ...

Por outro lado, se quisermos carregar os valores resultantes das fórmulas, usamos o argumento data_only=True. Nesse caso, não temos mais acesso à definição da fórmula. Portanto, cuidado para não sobrescrever uma planilha e perder uma fórmula importante!

import openpyxl

wb = openpyxl.load_workbook('vendas_formatado.xlsx', data_only=True)
planilha_vendas = wb['Vendas']
for celula in planilha_vendas['E']:
    print(celula.value)


# output:
# Valor total
# 500
# 1600
# ...

Como aplicar formatação numérica em células de Excel

Em Excel, é possível formatar células como dinheiro e controlar o número de casas decimais, mas também podemos realizar a mesma operação via Python ao definirmos o atributo number_format de uma célula.

No exemplo abaixo, definimos a função formatar_coluna_como_reais, que aceita uma planilha e uma letra de coluna como argumentos. A função itera sobre todas as células da coluna e define o atributo number_format de cada célula (o formato utilizado é uma sintaxe própria do Excel, que pode ser consultada clicando com o botão direito em uma célula e escolhendo “Formato de número” -> “Personalizado”):

import openpyxl

MAX_LINHAS = 1000
wb = openpyxl.load_workbook('vendas.xlsx')
planilha_vendas = wb['Vendas']

# ...
# Código anterior aqui
# ...

def formatar_coluna_como_reais(planilha, coluna):
    for i in range(2, MAX_LINHAS + 1):
        celula = planilha[f'{coluna}{i}']
        celula.number_format = 'R$ #,#0.#0'


formatar_coluna_como_reais(planilha=planilha_vendas, coluna='C')
formatar_coluna_como_reais(planilha=planilha_vendas, coluna='E')

wb.save('vendas_formatado.xlsx')

Agora, nossas colunas C e E possuem a formatação de dinheiro desejada:

Como utilizar Python para validar dados em Excel

A validação de dados do Excel impede um usuário de digitar valores indesejados em alguma célula. No nosso exemplo, podemos forçar o usuário a digitar um valor não negativo nas colunas de preço e quantidade.

Em Python, é possível criar validações de dados com os objetos DataValidation. Assim, ao criarmos esse objeto, podemos definir o tipo de validação (data, texto, numérica), uma mensagem customizada para guiar o usuário e uma mensagem de erro.

Após a criação da validação, precisamos adicioná-la a uma planilha através do método Worksheet.add_data_validation(). Em seguida, usamos DataValidation.add() para indicar o intervalo de células a ser validado.

No exemplo abaixo, criamos duas validações: uma para números inteiros e outra para números decimais. Para facilitar a aplicação das validações às respectivas colunas, criamos a função adicionar_validacao_a_coluna:

import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation

MAX_LINHAS = 1000
wb = openpyxl.load_workbook('vendas.xlsx')
planilha_vendas = wb['Vendas']

# ...
# Código anterior aqui
# ...

def adicionar_validacao_a_coluna(validacao, planilha, coluna):
    planilha.add_data_validation(validacao)
    validacao.add(f"{coluna}2:{coluna}{MAX_LINHAS}")


validacao_dinheiro = DataValidation(
    type="decimal",
    operator="greaterThanOrEqual",
    formula1=0.0,
    prompt="Insira o valor da compra...",
    error="O valor inserido está incorreto. Por favor, insira um valor (em R$) válido.",
)

validacao_quantidade = DataValidation(
    type="whole",
    operator="greaterThanOrEqual",
    formula1=0,
    prompt="Insira a quantidade de produtos comprados...",
    error="O valor inserido está incorreto. Por favor, insira um valor válido (maior ou igual a zero).",
)

adicionar_validacao_a_coluna(validacao=validacao_dinheiro, planilha=planilha_vendas, coluna='C')
adicionar_validacao_a_coluna(validacao=validacao_quantidade, planilha=planilha_vendas, coluna='D')

wb.save('vendas_formatado.xlsx')

Agora, as colunas Preço e Quantidade apenas aceitam valores numéricos maiores que zero. Além disso, a coluna Quantidade apenas aceita números inteiros. A imagem abaixo mostra o que acontece quando tentamos inserir o valor “???” na célula C14:

Como usar Python para criar uma formatação condicional em Excel

Outra funcionalidade muito útil de Excel é a formatação condicional, pois por meio dela formatamos células automaticamente, de acordo com seu valor. No caso do openpyxl, podemos utilizar os objetos Rule (regra) para especificarmos as regras de formatação condicional.

Vamos criar objetos do tipo ColorScaleRule para colorir as colunas Quantidade e Valor Total de acordo com os valores exibidos. Para isso, precisamos apenas definir as cores de início e fim da escala (como strings hexadecimais).

Em seguida, adicionamos a formatação condicional a um intervalo de células com o método Worksheet.conditional_formatting.add(). Novamente, criamos a função adicionar_formatacao_condicional_a_coluna para nos ajudar com o processo:

import openpyxl
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.worksheet.datavalidation import DataValidation

MAX_LINHAS = 1000

wb = openpyxl.load_workbook('vendas.xlsx')
planilha_vendas = wb['Vendas']

# ...
# Código anterior aqui
# ...

def adicionar_formatacao_condicional_a_coluna(formatacao, planilha, coluna):
    regiao = f'{coluna}2:{coluna}{MAX_LINHAS}'
    planilha.conditional_formatting.add(regiao, formatacao)


formatacao_quantidade = ColorScaleRule(
    start_type='min',
    end_type='max',
    start_color='EEEEFF',
    end_color='8888FF',
)
formatacao_dinheiro = ColorScaleRule(
    start_type='min',
    end_type='max',
    start_color='FFC7CE',
    end_color='C6EFCE',
)

adicionar_formatacao_condicional_a_coluna(formatacao=formatacao_quantidade, planilha=planilha_vendas, coluna='D')
adicionar_formatacao_condicional_a_coluna(formatacao=formatacao_dinheiro, planilha=planilha_vendas, coluna='E')

wb.save('vendas_formatado.xlsx')

Este é o resultado final:

Como não utilizamos nenhuma formatação direta, as células se adaptam ao conteúdo inserido nas linhas novas. Crie novos valores nas colunas Preço e Quantidade para ver as cores das colunas mudarem automaticamente.

Para facilitar, a seguir está o código inteiro que gera a planilha de saída a partir de “vendas.xlsx”:

import openpyxl
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.worksheet.datavalidation import DataValidation

# FUNÇÕES USADAS
def formatar_coluna_como_reais(planilha, coluna):
    for i in range(2, MAX_LINHAS + 1):
        celula = planilha[f'{coluna}{i}']
        celula.number_format = 'R$ #,#0.#0'


def adicionar_validacao_a_coluna(validacao, planilha, coluna):
    validacao.add(f"{coluna}2:{coluna}{MAX_LINHAS}")
    planilha.add_data_validation(validacao)


def adicionar_formatacao_condicional_a_coluna(formatacao, planilha, coluna):
    regiao = f'{coluna}2:{coluna}{MAX_LINHAS}'
    planilha.conditional_formatting.add(regiao, formatacao)


# PASSO 0:  carregar a planilha
MAX_LINHAS = 1000
wb = openpyxl.load_workbook('vendas.xlsx')
planilha_vendas = wb['Vendas']

# PASSO 1: criar coluna "Valor total" usando fórmula
planilha_vendas['E1'] = 'Valor total'
for i in range(2, MAX_LINHAS+1):
    formula = f'=C{i} * D{i}'
    planilha_vendas[f'E{i}'] = formula
    
# PASSO 2: formatar valores monetários como reais
formatar_coluna_como_reais(planilha=planilha_vendas, coluna='C')
formatar_coluna_como_reais(planilha=planilha_vendas, coluna='E')

# PASSO 3: adicionar validação de dados
validacao_dinheiro = DataValidation(
    type="decimal",
    operator="greaterThanOrEqual",
    formula1=0.0,
    prompt="Insira o valor da compra...",
    error="O valor inserido está incorreto. Por favor, insira um valor (em R$) válido.",
)
validacao_quantidade = DataValidation(
    type="whole",
    operator="greaterThanOrEqual",
    formula1=0,
    prompt="Insira a quantidade de produtos comprados...",
    error="O valor inserido está incorreto. Por favor, insira um valor válido (maior ou igual a zero).",
)
adicionar_validacao_a_coluna(validacao=validacao_dinheiro, planilha=planilha_vendas, coluna='C')
adicionar_validacao_a_coluna(validacao=validacao_quantidade, planilha=planilha_vendas, coluna='D')

# PASSO 4: adicionar formatação condicional
formatacao_quantidade = ColorScaleRule(
    start_type='min',
    end_type='max',
    start_color='EEEEFF',
    end_color='8888FF',
)
formatacao_dinheiro = ColorScaleRule(
    start_type='min',
    end_type='max',
    start_color='FFC7CE',
    end_color='C6EFCE',
)
adicionar_formatacao_condicional_a_coluna(formatacao=formatacao_quantidade, planilha=planilha_vendas, coluna='D')
adicionar_formatacao_condicional_a_coluna(formatacao=formatacao_dinheiro, planilha=planilha_vendas, coluna='E')

# PASSO 5: salvar a planilha
wb.save('vendas_formatado.xlsx')

Vá além de Excel com a Asimov Academy!

Excel é de fato muito poderoso e sua integração com Python via openpyxl faz todo sentido para criar tabelas poderosas e dinâmicas. No entanto, o aprendizado não deve se limitar a isso!
Siga aprendendo a automatizar tarefas e a criar ferramentas que trabalham para você com os cursos e projetos da nossa Trilha Python Office. Estamos te esperando!