Extrair dados de planilha em Python

Extrair planilha Exel com Python

De maneira automatizada, realizaremos a extração dos dados de uma planilha. Em nosso exemplo, lidaremos com questões; é necessário extrair as perguntas e suas respectivas alternativas, identificando qual é a resposta correta. Esses dados serão salvos de maneira organizada em um arquivo JSON para possibilitar um acesso e consulta mais eficientes. Todos os códigos estão disponíveis no GitHub, inclusive o arquivo xlsx para realizar testes e utilizá-lo como base para o desenvolvimento do seu código.

É possível implementar este script em outras linguagens; no entanto, a utilização do openpyxl em Python proporciona uma automação mais eficiente, além de fornecer uma sólida base para projetos futuros.

Vamos então analisar algumas partes importantes do código!

Aqui está o início, solicitando as importações necessárias das bibliotecas como openpyxl e json.

import json
from openpyxl import load_workbook

Carrega um arquivo Excel e lê uma planilha específica

arquivo = 'planilhaQuestoes.xlsx'
planilha = 'Planilha1'
wb = load_workbook(filename=arquivo, read_only=True, data_only=True)
ws = wb[planilha]

line = 1

Observe bem a nova variável LINE e planilha = 'Planilha1'; estamos indicando que desejamos ler a aba com esse mesmo nome.

Agora, vamos iniciar o código propriamente dito. Optei por chamar uma função principal para termos um código organizado. Na função process_line(ws, line), será realizado tudo o que precisamos para separar e estruturar nosso JSON. Passamos o ws (Worksheet), que é onde temos toda a planilha cuja aba já selecionamos acima com 'Planilha1', e a linha correspondente à planilha em que estamos no momento. No meu exemplo, vamos percorrer 171 linhas, começando da linha 1.

try:
    while line <= 171:
        print(f"process_line {line}")
        line = process_line(ws, line)
        line += 1
except Exception as e:
    print(f"Erro na linha {line}: {str(e)}")

Processo principal

Vamos então analisar como é realizado o processo para obter a linha e realizar suas devidas validações.

def process_line(ws, line):
    evento.append(get_cell_value(ws, line, 1))
    if evento[-1] is not None:
        questao = concatena_valores_celulas(ws, line)
        line += 1
        resp = 0
        alternativas = []
        for i in range(4):
            if resp == 0:
                resp = isResponse(ws, line, i)
            alternativa = concatena_valores_celulas(ws, line)
            alternativas.append(alternativa)
            line += 1
        data.append({
            'pergunta': questao,
            'alternativas': alternativas,
            'resposta': resp
        })
        return line

• evento.append(get_cell_value(ws, line, 1)): Aqui, você está chamando uma função get_cell_value e anexando o valor retornado à lista chamada evento.
• if evento[-1] is not None:: Esta linha verifica se o último elemento da lista evento não é None. O uso de evento[-1] obtém o último elemento da lista. Se este elemento não for None, o bloco de código dentro do if será executado.
• Dentro de evento[-1], você terá o último elemento adicionado à lista evento. No contexto do seu código, esse último elemento é o valor da célula na coluna 1 da linha especificada na planilha. Quando você usa evento[-1], está se referindo ao último item adicionado à lista evento até o momento. get_cell_value retornar diferentes valores em cada iteração, então evento[-1] representará o valor mais recente adicionado à lista evento naquele momento específico do loop. Portanto, o valor de evento[-1] será dinâmico e dependerá do contexto de execução do código. Pode ser um número, uma string, ou qualquer tipo de dado que a função get_cell_value retorna para a coluna 1 na linha especificada da planilha.

get_cell_value

def get_cell_value(ws, line, col):
    value = ws.cell(line, col).value
    return "" if value is None else value

Obtém o valor de uma célula. É bastante simples: passamos apenas o ws (Worksheet), o valor da linha e a direção na coluna.

Sendo

• A = 1
• B = 2
• C = 3

e assim por diante

concatena_valores_celulas

Em meu exemplo obtive problemas em algumas linhas ao organizar os valores da coluna A com a B ( col = 1 e 2) por isso transformei em DEF somente para concatenas os valores e verificar se esta tudo correto. A função está concatenando os valores de cell_value_1 e cell_value_2 com um espaço entre eles, formando uma string. Essa string é retornada pela função, a menos que um dos valores seja None. Se qualquer um dos valores for None, a função retorna uma string vazia, isso pode ser um problema em alguns casos, mas para o nosso caso esta perfeito!

def concatena_valores_celulas(ws, line):
    cell_value_1 = ws.cell(line, 1).value
    cell_value_2 = ws.cell(line, 2).value
    return f"{cell_value_1} {cell_value_2}" if cell_value_1 is not None and cell_value_2 is not None else ""

isResponse

Nosso método fundamental, aquele que irá determinar qual é a verdadeira questão correta, pode ser observado em nosso arquivo planilhaQuestoes.xlsx, onde temos uma marcação verde indicando a questão correta.

Com isso em mente, para cada questão, analisamos se a célula contém a resposta correta, verificando se está marcada.

def isResponse(ws, line, value):
    fill = ws.cell(line, 1).fill
    if fill is not None and fill.start_color is not None:
        color_in_hex = ws.cell(line, 1).fill.start_color.index
        if color_in_hex == "FF00B050":
            return value
    return 0

Se a marcação estiver presente, retornamos um valor de 0 a 3 para obter o índice da resposta correta no formato JSON. Dessa forma, alcançamos nosso resultado final em JSON.

 {
        "pergunta": "1. Este signo significa ",
        "alternativas": [
            "(A) No hay que girar a la izquierda. ",
            "(B) No hay giros a la izquierda. ",
            "(C) Ruta de camiones a la izquierda. ",
            "(D) No entres. "
        ],
        "resposta": 1
    },