Aprenda a automatizar o Excel com Python utilizando a biblioteca openpyxl neste tutorial completo, com explicações passo a passo.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
Como Automatizar o Excel com Python – openpyxl: Tutorial Completo
Na aula de hoje, vou te mostrar como automatizar o Excel com Python utilizando a biblioteca openpyxl.
Neste tutorial completo que preparei para você, vamos aprender como abrir e ler um arquivo Excel, selecionar a aba atual, acessar uma célula, editar informações e muito mais.
Depois disso, partiremos para um projeto prático de automação do Excel com Python, onde o objetivo será separar os dados, criar uma aba para cada categoria e organizar as informações em suas respectivas abas.
Essa automação pode te economizar muito tempo no trabalho, especialmente se é uma tarefa que você precisa executar com frequência.
Então, faça o download do material disponível e venha comigo aprender a criar essa automação com Python para o Excel, uma habilidade que você pode adquirir em cursos de programação da Hashtag e que irá te tornar muito mais eficiente e ágil em seus trabalhos.
Apresentando a Base de Dados e Explicando o Projeto
No material disponível para download, você encontrará dois arquivos Excel que utilizaremos para esta aula: Alunos.xlsx e Bairros.xlsx.
O primeiro arquivo, Alunos.xlsx, será utilizado para aprender os comandos iniciais e as principais operações da biblioteca openpyxl.
Já o arquivo Bairros.xlsx será a base de dados que utilizaremos para o nosso projeto prático de automação. Nele, temos o registro de diversas pessoas com a data de nascimento, o nome e o bairro onde residem.
O objetivo da nossa automação será criar uma aba para cada um dos bairros e separar as informações em suas respectivas abas. Ou seja, teremos uma planilha apenas para o bairro Leblon, outra para o Jardim Botânico, e assim por diante.
Biblioteca Openpyxl no Python
A biblioteca openpyxl do Python é uma poderosa ferramenta para ler, escrever e manipular arquivos Excel (.xlsx). Com ela, podemos criar automações de diversas tarefas relacionadas às planilhas em Excel.
O openpyxl permite interagirmos com os arquivos do Excel de forma nativa, semelhante ao VBA, mas sem precisar utilizar o VBA diretamente.
Com o openpyxl, podemos facilmente ler e criar arquivos Excel, manipular dados dentro das planilhas, criar gráficos, adicionar imagens e formatar as planilhas conforme desejarmos.
Essa biblioteca nos possibilita interagir facilmente com os valores e células das planilhas, através da manipulação direta das células e suas propriedades.
Podemos também usar variáveis para armazenar referências às células ou valores específicos, para utilizar posteriormente dentro do código e do projeto que estivermos construindo.
Além disso, ao utilizarmos o Python para automações com Excel, temos à disposição diversas outras ferramentas e possibilidades de integração e bibliotecas para realizar várias tarefas automaticamente.
Para começar a usar a openpyxl, primeiro você precisa instalá-la. Isso pode ser feito através do comando pip no terminal do seu editor de código.
pip install openpyxl
Função load_workbook
Ao trabalhar com a biblioteca openpyxl, temos a possibilidade de carregar um arquivo Excel existente ou criar um novo arquivo.
Para criar um arquivo Excel do zero, utilizamos a função Workbook. Já para carregar um arquivo existente para leitura e modificação, usamos a função load_workbook.
Essa função nos permite carregar e manipular um arquivo Excel no Python, acessando suas planilhas, células, dados e formatações, seja apenas para leitura ou para modificação.
Para usar a função load_workbook, podemos importá-la diretamente da biblioteca openpyxl, sem a necessidade de importar a biblioteca inteira.
Como nosso objetivo é trabalhar com um arquivo já existente, será essa a opção que iremos utilizar. Para carregar o arquivo, basta chamar a função load_workbook e passar para ela o nome do arquivo desejado.
from openpyxl import load_workbook
arquivo = load_workbook("Alunos.xlsx")No momento de carregar seu arquivo, é importante que você tome alguns cuidados. O primeiro deles é verificar a extensão do arquivo que você deseja abrir, pois versões diferentes do Excel podem ter extensões diferentes, como .xlsm, .xls ou .xlsx.
Outro ponto importante é verificar se o arquivo desejado e o código Python estão na mesma pasta. Caso contrário, será necessário fornecer o caminho completo do arquivo e não apenas o nome dele.
Por fim, um erro comum é deixar o arquivo Excel aberto durante a automação. Certifique-se de que nenhuma instância do Excel esteja ativa antes de executar o código para evitar erros.
Visualizando as Abas do Arquivo
Após carregar o arquivo, você pode visualizar as abas disponíveis nele através da propriedade sheetnames.
from openpyxl import load_workbook
arquivo = load_workbook("Alunos.xlsx")
# ver as abas
print(arquivo.sheetnames)Essa propriedade retorna uma lista com os nomes das abas contidas no documento. No nosso exemplo, teremos duas abas chamadas: Planilha1 e Planilha2.
Selecionando a Aba Ativa
Quando abrimos um arquivo Excel, normalmente temos uma aba que já está selecionada. Essa aba é referida como aba ativa.
Para selecionar e verificar qual é a aba ativa com o openpyxl, vamos acessar a propriedade active.
# pegar a aba ativa aba_atual = arquivo.active print(aba_atual)
Executando esse código, obteremos que a aba atualmente ativa no nosso arquivo Excel é a Planilha2.
Selecionando uma Aba Específica
Para selecionar uma aba específica sem precisar ativá-la manualmente, podemos usar uma sintaxe semelhante à que utilizamos em um dicionário Python. Isso nos permite acessar diretamente qualquer aba pelo seu nome.
# selecionar uma aba específica aba_alunos = arquivo["Planilha1"] print(aba_alunos)
Dessa forma, conseguimos alternar facilmente entre as abas presentes na nossa planilha.
Selecionar Células
Como vimos, para selecionar uma aba específica em uma planilha, utilizamos o nome da aba como se fosse uma chave de dicionário.
Da mesma forma, podemos fazer a seleção de células dentro dessa aba. Por exemplo, a célula A1 pode ser referenciada diretamente como [“A1”].
Para acessar o conteúdo dessa, ou de outras células, usamos a propriedade value.
from openpyxl import load_workbook
arquivo = load_workbook("Alunos.xlsx")
# selecionar células
valor_a1 = aba_alunos["A1"].value
print(valor_a1)
Além disso, a seleção de células pode ser feita utilizando a função cell, passando como parâmetros o número da linha e da coluna desejada.
from openpyxl import load_workbook
arquivo = load_workbook("Alunos.xlsx")
# selecionar células
valor_b1 = aba_alunos.cell(row=1, column=2).value
print(valor_b1)
Edição de Células
Para editar o valor em uma célula específica é bastante simples. Basta selecionar a célula desejada, seja pelo nome ou utilizando a função cell, e atribuir um novo valor àquela célula.
Após realizar a alteração, é importante salvar o arquivo; caso contrário, as edições feitas não serão refletidas quando o arquivo for aberto novamente. Para isso, utilizamos o método .save().
from openpyxl import load_workbook
arquivo = load_workbook("Alunos.xlsx")
# selecionar uma aba específica
aba_alunos = arquivo["Planilha1"]
# Editar célula
aba_alunos.cell(row=1, column=2).value = "Prova 1"
print(aba_alunos.cell(row=1, column=2).value)
arquivo.save("Alunos2.xlsx")Executando esse código, teremos como resultado o texto “Prova 1” ao invés de “P1”, como tínhamos anteriormente.
Além disso, ao abrir o arquivo Alunos2.xlsx, veremos que essa mudança foi aplicada e salva.
Neste exemplo, salvei o arquivo com um novo nome, Alunos2.xlsx. Dessa forma, criamos uma nova versão do documento com as alterações aplicadas. Isso nos permite manter versões anteriores do arquivo enquanto trabalhamos nas edições necessárias.
Caso você quisesse substituir o arquivo antigo pelo novo, bastaria salvar como Alunos.xlsx.
Como Encontrar a Última Linha em uma Planilha
Quando criamos uma automação para interagir com o Excel, um ponto muito importante é descobrir qual é a última linha preenchida da sua planilha.
Existem duas formas principais de fazer isso usando o openpyxl: verificando a propriedade max_row da aba desejada ou utilizando o tamanho(len) de uma coluna específica.
Utilizando a propriedade max_row:
from openpyxl import load_workbook
arquivo = load_workbook("Alunos.xlsx")
# selecionar uma aba específica
aba_alunos = arquivo["Planilha1"]
# ultima linha
print(aba_alunos.max_row)
Verificando o tamanho da coluna A:
from openpyxl import load_workbook
arquivo = load_workbook("Alunos.xlsx")
# selecionar uma aba específica
aba_alunos = arquivo["Planilha1"]
# ultima linha
print(len(aba_alunos["A"]))Com ambos os métodos, obtemos o resultado correto de 5, que é, de fato, a última linha preenchida do nosso arquivo.
O que o openpyxl tenta fazer é identificar a última linha editada ou que contém valores, seja utilizando a propriedade max_row, que retorna o máximo de linhas, ou a função len(), que retorna o tamanho da coluna selecionada.
Porém, precisamos tomar cuidado, pois, dependendo do arquivo, o openpyxl pode não reconhecer corretamente os valores, apresentando um valor maior e incorreto sobre a última linha preenchida.
Por exemplo, você pode ter deletado uma linha preenchida, mas o Excel ainda considera essa linha presente por conta dos metadados.
Vale notar que, caso esse valor venha incorreto, ele sempre será um valor maior, nunca menor.
Automatizando o Excel com Python – Desafio Prático
Agora que você já compreendeu as principais funcionalidades e usos da biblioteca openpyxl, vamos partir para a construção da nossa automação.
Para isso, crie um novo arquivo Python na mesma pasta onde está o arquivo Bairros.xlsx e vamos começar!
A lógica da automação será a seguinte: Percorreremos toda a base de dados e, para cada linha, verificaremos se já existe uma aba para aquele bairro. Se não existir, essa aba será criada. Em seguida, copiaremos os dados dessa linha para a aba correspondente.
Dessa forma, teremos uma aba para cada um dos bairros presentes na tabela, com as informações organizadas e separadas corretamente.
O primeiro passo será importar a função load_workbook da biblioteca openpyxl e carregar a base de dados Bairros.xlsx, armazenando o conteúdo na variável arquivo_bairros.
Em seguida, selecionamos e definimos a aba Base de Dados como a nossa aba ativa, a aba com a qual iremos trabalhar e manipular. Vamos armazená-la na variável aba_basedados.
Mesmo que o arquivo com o qual você esteja trabalhando tenha apenas uma aba, essa é uma etapa importante para garantir que estamos sempre editando a aba correta, independentemente de novas abas serem criadas posteriormente.
Além disso, vamos determinar qual é a última linha preenchida na nossa planilha utilizando a propriedade max_row.
from openpyxl import load_workbook
arquivo_bairros = load_workbook("Bairros.xlsx")
aba_basedados = arquivo_bairros["Base de Dados"]
ultima_linha = aba_basedados.max_rowLembre-se de verificar se a última linha identificada pelo openpyxl é realmente a última linha da sua tabela. Você pode fazer isso através do comando print.
print(ultima_linha)
No nosso exemplo, a biblioteca openpyxl está identificando corretamente a última linha preenchida da planilha.
Feito isso, vamos iterar por todas as linhas da aba Base de Dados utilizando um loop for com a função range, passando para ela o valor inicial de 2 e o valor de parada como ultima_linha + 1.
A função range gera uma sequência de números que começa no valor inicial e vai até o valor de parada, mas não inclui esse valor final.
Portanto, se passarmos range(2, ultima_linha), não teremos a última linha sendo percorrida, pois o valor de parada é exclusivo.
Além disso, usamos o valor inicial de 2 porque a primeira linha corresponde ao cabeçalho, que não desejamos percorrer.
Dessa forma, garantimos que o loop itere corretamente desde a segunda linha até a última linha preenchida, inclusive.
Dentro desse loop, para cada linha, vamos extrair o valor da célula na coluna C. Para obter a célula correspondente, utilizaremos o método cell, passando o parâmetro row como a variável linha e o parâmetro column como 3, que corresponde à coluna C.
Em seguida, vamos verificar se a coluna bairro está preenchida. Caso não esteja, a automação deve ser interrompida através de um comando break.
Caso contrário, se encontrarmos um bairro válido, continuaremos com as operações de criar uma aba para o bairro e transferir as informações para a aba.
from openpyxl import load_workbook
arquivo_bairros = load_workbook("Bairros.xlsx")
aba_basedados = arquivo_bairros["Base de Dados"]
ultima_linha = aba_basedados.max_row
for linha in range(2, ultima_linha + 1):
bairro = aba_basedados.cell(row=linha, column=3).value
if not bairro:
break
# criar uma aba para o bairro
# transferir as informações para a abaPara organizar melhor o nosso código, vamos separar essas duas etapas em duas funções separadas: uma para criar a aba para o bairro e outra para transferir as informações correspondentes.
Função Criar Aba
A função criar_aba será responsável por criar uma nova aba para cada bairro presente na tabela. Ela recebe como parâmetros o bairro e o arquivo Excel (arquivo_bairros) com o qual estamos trabalhando.
Dentro da função, verificamos se já existe uma aba com o nome do bairro na lista de nomes das abas do arquivo (sheetnames). Caso não exista, criamos uma nova aba com o nome do bairro.
Em seguida, definimos os valores das células A1, B1 e C1 com as informações correspondentes ao cabeçalho.
def criar_aba(bairro, arquivo_bairros): if bairro not in arquivo_bairros.sheetnames: arquivo_bairros.create_sheet(bairro) nova_aba = arquivo_bairros[bairro] nova_aba["A1"].value = "Data de Nascimento" nova_aba["B1"].value = "Pessoa" nova_aba["C1"].value = "Bairro"
Função Transferir Informações
A função transferir_informacoes_aba será responsável por transferir os dados de uma linha específica da aba original para a aba de destino, que são as abas dos bairros.
Essa função recebe como parâmetros a aba de origem (aba_origem), a aba de destino (aba_destino) e a linha de origem (linha_origem).
Dentro dessa função, é necessário determinar qual linha na aba de destino receberá as informações extraídas da aba de origem.
Como sempre preencheremos a última linha disponível na aba de destino, a linha_destino será a última linha preenchida da aba de destino mais uma linha (aba_destino.max_row + 1).
Ou seja, selecionaremos a próxima linha abaixo da última linha preenchida na aba de destino.
Feito isso, vamos criar um loop for para percorrer cada uma das colunas disponíveis, de 1 até a coluna 4 (lembrando que o último valor é excludente). Dessa forma, percorreremos e preencheremos as colunas A, B e C.
Nessa iteração, pegaremos a célula de origem e atribuiremos o valor dela à célula de destino. Vamos obter essas células aplicando o método cell nas abas correspondentes.
def transferir_informacoes_aba(aba_origem, aba_destino, linha_origem): linha_destino = aba_destino.max_row + 1 for coluna in range(1, 4): celula_origem = aba_origem.cell(row=linha_origem, column=coluna) celula_destino = aba_destino.cell(row=linha_destino, column=coluna) celula_destino.value = celula_origem.value
Aplicando as Funções e Executando a Automação
Com as funções definidas, podemos aplicá-las dentro do loop for que havíamos criado anteriormente.
Primeiro, após verificarmos que a coluna bairro está preenchida, chamamos a função criar_aba, passando para ela a variável bairro e o arquivo Excel com o qual estamos trabalhando.
Em seguida, definimos a aba de destino utilizando o bairro atual e chamamos a função transferir_informacoes_aba, passando como argumentos a aba_basedados como aba de origem, a aba_destino que acabamos de definir, e a linha atual percorrida.
Por fim, salvamos o arquivo. Você pode substituir o arquivo atual ou salvar em um novo arquivo Excel.
from openpyxl import load_workbook
def criar_aba(bairro, arquivo_bairros):
if bairro not in arquivo_bairros.sheetnames:
arquivo_bairros.create_sheet(bairro)
nova_aba = arquivo_bairros[bairro]
nova_aba["A1"].value = "Data de Nascimento"
nova_aba["B1"].value = "Pessoa"
nova_aba["C1"].value = "Bairro"
def transferir_informacoes_aba(aba_origem, aba_destino, linha_origem):
linha_destino = aba_destino.max_row + 1
for coluna in range(1, 4):
celula_origem = aba_origem.cell(row=linha_origem, column=coluna)
celula_destino = aba_destino.cell(row=linha_destino, column=coluna)
celula_destino.value = celula_origem.value
arquivo_bairros = load_workbook("Bairros.xlsx")
aba_basedados = arquivo_bairros["Base de Dados"]
ultima_linha = aba_basedados.max_row
for linha in range(2, ultima_linha + 1):
bairro = aba_basedados.cell(row=linha, column=3).value
if not bairro:
break
# criar uma aba pro bairro
criar_aba(bairro, arquivo_bairros)
# transferir as informacoes pra aba
aba_destino = arquivo_bairros[bairro]
transferir_informacoes_aba(aba_basedados, aba_destino, linha)
arquivo_bairros.save("Bairros2.xlsx")Executando esse código, veremos que nosso arquivo Excel agora será composto pela aba original com todos os bairros, e uma aba nova para cada um dos bairros existentes com as informações preenchidas corretamente.
No entanto, se verificarmos cada uma dessas novas abas, veremos que, apesar de terem copiado as informações, as novas abas não estão formatadas como a aba original.
Isso acontece porque o openpyxl não possui uma função direta para copiar o formato de uma célula para outra.
Para formatar uma célula utilizando o openpyxl, é necessário especificar cada um dos atributos manualmente, como alinhamento, cor de fundo, fonte e assim por diante.
Porém, podemos utilizar a biblioteca copy, padrão do Python, para copiar as informações das nossas células formatadas e colá-las em outra célula.
Biblioteca Copy – Copiando os Estilos das Células
A biblioteca copy é uma biblioteca padrão do Python, ou seja, já vem instalada.
Essa biblioteca permite copiar informações de um objeto em Python e colá-las em outro. Isso nos permite copiar as informações de formatação e estilo das células e aplicá-las em outras células.
Os estilos de uma célula podem ser acessados através da propriedade _style. Essa é uma propriedade privada que contém todas as informações sobre a formatação visual da célula, como fonte, cor de fundo, bordas, alinhamento e número de formato.
Por ser um atributo interno, essa propriedade não está documentada oficialmente para uso público, o que indica que ela pode ser instável ou estar sujeita a mudanças nas versões futuras da biblioteca.
No entanto, ainda assim é possível acessá-la e manipulá-la normalmente, o que, no nosso caso, irá simplificar o processo de estilização das células.
A primeira etapa para copiarmos os estilos de uma célula será importar a biblioteca copy.
Feito isso, dentro da função criar_aba, vamos definir um terceiro parâmetro que será o estilos_cabecalho. Esse será o estilo que copiaremos das células do cabeçalho original e aplicaremos aos cabeçalhos das novas abas.
Além disso, dentro da função transferir_informacoes_aba, também acessaremos o estilo da célula de origem e o atribuiremos ao estilo da célula de destino.
Por fim, dentro do código, iremos copiar o estilo da célula A1 e armazenar na variável estilos_cabecalho para aplicar dentro da chamada da função criar_aba.
from openpyxl import load_workbook
from copy import copy
def criar_aba(bairro, arquivo_bairros, estilos_cabecalho):
if bairro not in arquivo_bairros.sheetnames:
arquivo_bairros.create_sheet(bairro)
nova_aba = arquivo_bairros[bairro]
nova_aba["A1"].value = "Data de Nascimento"
nova_aba["B1"].value = "Pessoa"
nova_aba["C1"].value = "Bairro"
nova_aba["A1"]._style = estilos_cabecalho
nova_aba["B1"]._style = estilos_cabecalho
nova_aba["C1"]._style = estilos_cabecalho
def transferir_informacoes_aba(aba_origem, aba_destino, linha_origem):
linha_destino = aba_destino.max_row + 1
for coluna in range(1, 4):
celula_origem = aba_origem.cell(row=linha_origem, column=coluna)
celula_destino = aba_destino.cell(row=linha_destino, column=coluna)
celula_destino.value = celula_origem.value
celula_destino._style = copy(celula_origem._style)
arquivo_bairros = load_workbook("Bairros.xlsx")
aba_basedados = arquivo_bairros["Base de Dados"]
ultima_linha = aba_basedados.max_row
estilos_cabecalho = copy(aba_basedados["A1"]._style)
for linha in range(2, ultima_linha + 1):
bairro = aba_basedados.cell(row=linha, column=3).value
# bairro = aba_basedados[f"C{linha}"].value
if not bairro:
break
# criar uma aba pro bairro
criar_aba(bairro, arquivo_bairros, estilos_cabecalho)
# transferir as informacoes pra aba
aba_destino = arquivo_bairros[bairro]
transferir_informacoes_aba(aba_basedados, aba_destino, linha)
arquivo_bairros.save("Bairros2.xlsx")Se executarmos nossa automação agora, as novas abas estarão formatadas e estilizadas conforme a aba original.
Conclusão – Como Automatizar o Excel com Python – openpyxl: Tutorial Completo
Na aula de hoje, eu te mostrei como automatizar o Excel com Python utilizando a biblioteca openpyxl.
Vimos as principais funcionalidades dessa biblioteca e, em seguida, aplicamos os conhecimentos obtidos na construção de uma automação completa em Python.
A biblioteca openpyxl é extremamente útil para qualquer tarefa que envolva manipulação de arquivos Excel. Combinada com as demais capacidades do Python, ela é capaz de criar automações que tornarão seu trabalho muito mais fácil e prático. Com essa biblioteca, você é capaz de criar planilhas, automatizar relatórios, criar análises de dados e muito mais.
Hashtag Treinamentos
Para acessar outras publicações de Python, clique aqui!
Posts mais recentes da Hashtag Treinamentos
- 5 Boas Práticas de SQL para Iniciantes: Otimize Suas Consultas e Organize Seu Código
Se você está começando com MySQL ou busca melhorar suas habilidades em SQL, este post traz cinco boas práticas de escrita de SQL para iniciantes. - Função SE Excel: Exemplos e Dicas para Uso [Guia]
A função SE Excel é muito útil para definir condições e criar uma planilha mais completa e funcional. Confira nesta aula! - Exercícios de Listening em Inglês – Como Melhorar sua Compreensão Faça estes exercícios de listening em inglês e melhore sua compreensão! Descubra técnicas e estratégias para praticar e evoluir no aprendizado do idioma!
- TCP e UDP: o que são, diferenças e quando usar Entenda o que é TCP e UDP, as diferenças entre os protocolos, suas vantagens e quando usar cada um. Aprenda com exemplos práticos e linguagem acessível.
- Biblioteca NumPy: o que é, vantagens e como usar Aprenda como usar a biblioteca NumPy no Python para otimizar o processamento de dados e melhorar sua eficiência em projetos de ciência de dados.
- Power Apps: o que é, benefícios e como usar Descubra o que é Power Apps, como a ferramenta revoluciona a criação de aplicativos e como você pode começar a usá-la hoje mesmo!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.