Python e openpyxl: reúna milhares de pastas de Excel em um único arquivo
Clique aqui para ler a versão em inglês deste artigo.
INTRODUÇÃO
O Microsoft Excel consolidou-se como um dos programas mais populares do mundo, de modo que ele é largamente utilizado no mercado de trabalho e para fins privados. Talvez em sua empresa ou em casa você ainda tenha a necessidade de manipular arquivos de extensão .xlsx e, por algum motivo, você deseja que os seus dados permaneçam armazenados nesse formato.
O Excel possui, nativamente, a linguagem VBA, que pode realizar boa parte de demandas de automação de processos em arquivos xlsx. Contudo, o desenvolvimento dessa linguagem foi descontinuado pela Microsoft há vários anos, ainda que, por hora, ela continue disponível para ser utilizada nas versões desktop do Excel.
De todo modo, você não precisa saber VBA para conseguir se tornar um mago das planilhas! Com Python e a biblioteca openpyxl, é possível escrever código que irá poupar muito do seu tempo e trará grandes resultados para o seu trabalho. Com a vantagem de se estar utilizando uma das linguagens de programação mais modernas do mundo, com uma das maiores taxas de crescimento no mercado e que possui uma enorme e ativa comunidade de desenvolvedores.
CENÁRIO DE USO E DADOS DE EXEMPLO
O presente artigo trata de um cenário muito comum, infelizmente, de manipulação de dados em Excel: a dispersão, de uma mesma base de dados, por diferentes planilhas ou até mesmo por vários arquivos Excel diferentes. Caso sejam poucas planilhas ou pastas de trabalho e nenhum dos usuários conheça programação, uma abordagem “copia e cola” ainda pode ser viável para juntar todos esses dados em um só lugar. Contudo, tal procedimento não é de modo algum recomendável, pelo maior risco de ocorrência de erro humano, fora o grande desperdício de tempo.
E se você estiver trabalhando com informações provenientes de uma mesma base de dados, que deveriam estar juntas, consolidadas, e essas informações estiverem espalhadas por centenas ou até milhares de planilhas ou arquivos diferentes? Aí, de fato, a abordagem manual se torna totalmente inviável. Muito melhor é deixar a biblioteca openpyxl te ajudar a realizar esse tipo de tarefa em poucos segundos, quando os arquivos forem de extensão .xlsx, mais moderna. Para arquivos xls, bem mais antigos, deve-se utilizar outras bibliotecas Python na sua leitura e manipulação.
Neste repositório do Github, vocês encontrarão dois diretórios (workbooks_by_days e workbooks_by_months), com arquivos xlsx que serão utilizados para mostrar esse poder do openpyxl em reunir, em um só lugar, dados de Excel dispersos por milhares de fontes.
Eu criei esses dados de maneira aleatória. Portanto, eles não correspondem a nenhum dado real que deva ser posteriormente analisado e entendido. Mas todos os arquivos estão bem padronizados, respeitando a mesma estrutura: no diretório workbooks_by_days, há uma pasta de trabalho do Excel para cada dia entre as datas de 01/01/2020 e 28/03/2021. Os nomes dos arquivos possuem a informação da data na sequência ano-mês-dia. Há aqui apenas uma planilha por arquivo, simulando um relatório de dados metereológicos coletados de hora em hora. Já na pasta workbooks_by_months, os dados foram agrupados por mês, como poderá ser observado, de modo que cada pasta de trabalho do Excel possui dezenas de planilhas, uma para cada dia daquele mês.
Assim, baixe esses arquivos Excel para a sua máquina, caso você deseje acompanhar a nossa explicação do código abaixo e, ao mesmo tempo, testá-lo localmente.
APRESENTAÇÃO DO CÓDIGO
Iniciaremos com a importação dos recursos do openpyxl que necessitaremos em nosso código, bem como do módulo os:
Agora, iremos reproduzir o caminho para o diretório onde encontram-se os arquivos Excel que desejamos reunir e, então, utilizaremos um for loop sobre todos os itens deste diretório, de modo que, quando encontrarmos uma string terminada em .xlsx (e que não seja o nome de um arquivo temporário, que costumam apresentar o prefixo “~$”), nós iremos chamar a função load_workbook, criando uma instância de objeto Workbook, do openpyxl. Esse objeto Workbook será apensado à lista wbs, que reunirá assim, em memória, os dados de todos os arquivos que precisamos consolidar.
Terminado o for loop, criaremos então, com código Python, um nova pasta de trabalho/workbook do Excel, a qual será preenchida com os dados de todos os workbooks que foram salvos na lista wbs. Após criarmos esse workbook de consolidação e salvá-lo na variável final_wb, acessaremos a sua primeira planilha e salvaremos esse objeto, do tipo Worksheet, na variável final_ws. Abriremos também o primeiro workbook da lista wbs, salvando seus dados nas variáveis wb1 e ws1:
Cada objeto Worksheet, do openpyxl, tem dois atributos muito importantes para o código que estamos desenvolvendo: max_column e max_row, os quais salvam, respectivamente, as informações do número máximo de colunas e de linhas presentes naquela planilha. Os dados desses atributos serão utilizados bastante no nosso código, pois definirão os limites máximos quando estivermos iterando por linhas e por colunas de uma determinada planilha.
O código abaixo, com um for loop que executa apenas uma linha de código a cada iteração, foi criado apenas para copiar o cabeçalho das colunas e transferi-lo para final_ws:
Agora vem a parte central do código. Nesse caso, por se tratar de quatro estruturas de repetição aninhadas (nested for loops), para que não fiquemos perdidos nas indentações, iremos primeiro apresentar o código e depois faremos comentários sobre ele.
No primeiro loop, iteramos sobre todos os objetos Workbook salvos na lista wbs. Para cada wb (abreviação comum para workbook), iremos iterar por todos os seus objetos Worksheets, que representam as planilhas (wb.worksheets retorna uma lista desses objetos). Por fim, para cada planilha, iteraremos por todas as suas linhas e colunas, recuperando a informação de cada célula e inserindo-a no lugar correto de final_ws. A variável current_row controlará para qual linha da planilha final_ws as informações que estão sendo copiadas naquele momento devem ser transferidas.
O último passo do nosso código é salvar os dados de final_wb, o que criará um arquivo .xlsx na nossa máquina, com os dados compilados.
CONSOLIDANDO O NOSSO CÓDIGO EM UMA FUNÇÃO
Podemos encapsular todo o código apresentado até agora em uma única função, que receba como parâmetros o diretório onde estão os arquivos xlsx a serem compilados (workbooks_path), bem como o nome a ser dado para o novo arquivo Excel, com o resultado da compilação dos dados (final_filename). Tal função é apresentada abaixo. Notem ainda a inclusão de cinco estruturas condicionais, no início do corpo da função, que fazem uma série de checagem quanto ao tipo e formato dos argumentos passados a ela.
Acredito que a função apresentada possui um grau de generalidade suficiente para vir a auxiliar outras pessoas que precisem resolver problemas similares como o apresentado aqui.
Meu muito obrigado a você, por ter prestigiado meu texto com a sua leitura.
Happy coding!
P.S.: Você encontrará mais informações sobre o meu trabalho no LinkedIn, Medium e Github:
https://www.linkedin.com/in/fabriciobrasil
