Como ler centenas de milhares de linhas no Excel sem encher a memória de uma só vez

Como ler uma tabela grande sem consumir memória? Este artigo é destinado a desenvolvedores e equipes de dados que precisam usar Python para processar centenas de milhares de linhas do Excel. Após a leitura, você saberá os motivos da explosão de memória de arquivos grandes e do truncamento de dados, e como usar o modo somente leitura, reparo de dimensão e liberação de recursos do openpyxl para evitar esses problemas.

Os exemplos neste artigo usam amostras sintéticas. O código foi verificado com exemplos em um ambiente isolado, mas isso não é garantia de todos os arquivos reais ou do desempenho do produto. Antes de processar documentos importantes, guarde os originais e revise-os de acordo com os limites e itens de aceitação do texto.

Como ler uma tabela grande sem estourar a memória? Conclusão de uma frase

Use read_only=True para transmitir arquivos grandes lidos e fechar explicitamente a pasta de trabalho após a leitura; se o número de linhas lidas for obviamente muito pequeno, primeiro verifique as informações de dimensão do arquivo e depois combine-as com a comparação manual para confirmar se os dados estão completos.

A causa direta da explosão de memória ou truncamento de dados ao ler centenas de milhares de linhas no Excel

Ao usar load_workbook('huge.xlsx') para abrir uma tabela com meio milhão de linhas, o programa pode usar até 8 GB de memória em alguns segundos ou pode terminar após ler apenas as primeiras 1.000 linhas.

A razão para o primeiro é que o modo padrão carrega todos os objetos de célula e seus estilos, comentários e outros metadados na memória de uma só vez. O último ocorre porque no modo read_only=True, openpyxl confia totalmente no atributo dimensions declarado dentro do arquivo para buscar desempenho.

Se o sistema que gerou o arquivo (como algum módulo de exportação de ERP antigo ou uma biblioteca Python não padrão) gravar esses metadados incorretamente, o iterador será encerrado prematuramente sem gerar nenhuma exceção. Este não é um bug de código, mas uma lacuna entre as especificações de formato e os dados do mundo real que precisa ser superada por meio de programação defensiva.

Método correto de abertura e liberação de recursos do modo somente leitura

read_only=True é o caminho principal para lidar com arquivos grandes, mas altera o gerenciamento do ciclo de vida dos objetos. As pastas de trabalho comuns liberarão recursos automaticamente durante a coleta de lixo, enquanto as pastas de trabalho somente leitura mantêm referências ao fluxo de arquivos ZIP e ao analisador XML na parte inferior e devem chamar explicitamente wb.close(). Para obter instruções oficiais sobre o modo somente leitura, consulte Documentação oficial dos modos otimizados openpyxl.

Esquecer esta etapa em um script ETL que percorre vários arquivos pode resultar em vazamentos de identificador de arquivo, eventualmente acionando OSError: Too many open files. Esse tipo de erro costuma ser exposto após várias horas de execução e é extremamente difícil de solucionar.

Além disso, o modo somente leitura retorna ReadOnlyCell em vez do objeto Cell padrão. Não suporta acesso a atributos como estilos e anotações, nem permite pesquisa reversa ou modificação de valores através de coordenadas. Se você obtiver informações de área mesclada ou definir a cor da célula durante a iteração, obterá AttributeError. Esse limite é intencional para manter um consumo de memória quase constante.

📊 Comparação principal

Exemplo de síntese: reparo de dimensão e verificação de segurança de iteração

O código a seguir mostra um processo típico de leitura defensiva. Observe que este é um exemplo sintético usado para demonstrar a estrutura lógica, não o resultado do processamento de dados reais de produção. Você precisa preparar um arquivo chamado input_large.xlsx para executar este script, e ele deve ser executado em um ambiente isolado que bloqueie versões dependentes. A amostra não inclui operações de rede, comandos shell ou credenciais confidenciais.

from openpyxl import load_workbook

# 合成示例：演示read_only模式下的安全读取与维度校验
# 依赖：openpyxl (需在项目requirements中锁定具体版本)
# 注意：需读者自备 input_large.xlsx，本示例不包含真实数据

def safe_read_large_excel(filepath, expected_min_rows=1000):
    """
    安全读取大Excel文件，包含维度修复与完整性校验。
    :param filepath: Excel文件路径
    :param expected_min_rows: 业务预期的最小行数阈值
    :return: 实际读取的行数
    """
    wb = load_workbook(filename=filepath, read_only=True)
    try:
        ws = wb.active
        # 检查并修复可能错误的维度声明
        # calculate_dimension() 可能返回 'A1:A1' 或其他不准确范围
        dim = ws.calculate_dimension()
        if dim == "A1:A1" or not dim:
            # 重置维度以强制扫描实际数据区域
            # 警告：若文件完全无维度信息，此操作可能遍历全表(104万行)
            ws.reset_dimensions()

        row_count = 0
        header = None
        for row in ws.iter_rows(values_only=True):
            if row_count == 0:
                header = row
                assert header is not None and any(v is not None for v in header), \
                    "首行不应为空或全None"
            row_count += 1

            # 模拟业务处理，避免全量加载到内存
            if row_count % 10000 == 0:
                print(f"已处理 {row_count} 行")

        # 校验读取完整性
        assert row_count >= expected_min_rows, \
            f"行数不足: 期望>={expected_min_rows}, 实际={row_count}。请检查源文件或维度设置。"
        return row_count
    finally:
        # 必须显式关闭，防止句柄泄漏
        wb.close()

# 使用示例（需替换为真实文件路径）
# total = safe_read_large_excel("input_large.xlsx", expected_min_rows=50000)

A chave para este código não é a leitura em si, mas as três camadas de proteção:

• Redefinição de dimensão: dimensions corrigido na declaração do arquivo para evitar o fim prematuro do iterador.
• Contagem de fluxo: conte o número de linhas durante a leitura para facilitar a comparação com as expectativas.
• Fechamento forçado em finally: Independentemente de a leitura ser bem-sucedida ou ocorrer um erro no meio, os recursos do arquivo são liberados.

expected_min_rows não é um limite padrão do setor, mas um resultado comercial baseado em seu conhecimento prévio dos dados de origem. Se o número real de linhas for inferior a esse valor, o arquivo poderá estar danificado ou o reparo da dimensão falhou e você deverá abortar neste momento, em vez de aceitar silenciosamente dados incompletos.

Critérios de aceitação e limites de revisão manual

Só porque o código pode ser executado não significa que os dados estejam corretos. A tabela a seguir lista exceções típicas que podem ser encontradas em projetos reais e como lidar com elas, distinguindo o escopo que a automação pode cobrir e os cenários que requerem intervenção manual.

Nota especial: reset_dimensions() não é uma panacéia. Se nenhuma informação de dimensão for gravada no próprio arquivo, esse método poderá estender o intervalo até o limite superior da planilha (1.048.576 linhas), resultando em passagem inválida ou até mesmo em tempo limite. Neste momento, max_row deve ser usado para definir manualmente um limite superior razoável ou deve ser encerrado ativamente quando N linhas em branco forem encontradas em uma linha. Esses julgamentos não podem ser feitos apenas por código e requerem suporte do conhecimento do domínio.

🎯 Qual método é adequado para você?

Se você precisar processar tabelas com centenas de milhares de linhas de uma só vez

Use a leitura de streaming read_only=True para evitar explosão de memória no modo padrão.

Se o seu script percorrer vários arquivos

Chame wb.close() explicitamente em um bloco finally para evitar vazamentos de identificador.

Se o número de linhas lidas for significativamente menor que os dados de origem

Primeiro verifique se dimensions está escrito incorretamente, tente reset_dimensions(), releia-o e compare-o com o log de exportação do sistema de origem.

Se você precisar ler estilos ou comentários

Essas propriedades não são suportadas no modo somente leitura e devem ser usadas no modo padrão ou tratadas separadamente em outro processo.

Entrada de execução de amostra de memória

O código a seguir é executado após a função acima e usa apenas dados sintéticos e não lê o arquivo do cliente.

from io import BytesIO
from openpyxl import Workbook
source = Workbook()
source.active.append(["id", "value"])
source.active.append(["A", 1])
source.active.append(["B", 2])
out = BytesIO()
source.save(out)
out.seek(0)
assert safe_read_large_excel(out, expected_min_rows=3) == 3

Use esta lista de verificação para tradução de documentos

Primeiro decida qual conteúdo é texto e qual é fórmula e codificação comercial e, em seguida, traduza a tabela. A verificação pós-tradução não apenas verifica se o chinês foi convertido para o idioma de destino, mas também confirma se as fórmulas, identificadores e relações de referência foram alteradas.

OmniDoc (portal de produtos) fornece um portal de tradução de documentos multiformato e o site oficial apresenta funções de OCR, glossário e comparação bilíngue. Para equipes que desejam trabalhar diretamente a partir dos arquivos, vale a pena avaliar esse caminho com exemplos da vida real. Use arquivos dessensibilizados que você tem permissão para carregar e use os itens de verificação neste artigo para revisar os resultados. Este artigo não afirma que o OmniDoc usa as bibliotecas de código aberto ou algoritmos internos do artigo, nem usa exemplos para substituir os testes de desempenho do produto.

❓ Perguntas frequentes (FAQ)

1. Por que a leitura termina na metade e nenhum erro é relatado?

Porque o modo somente leitura confia no dimensions declarado pelo arquivo. Se o sistema de compilação gravar esses metadados incorretamente, o iterador será encerrado prematuramente sem lançar uma exceção.

2. Todos os arquivos grandes podem ser resolvidos com reset_dimensions()?

não pode. Se o arquivo em si não tiver informações de dimensão, esse método poderá estender o intervalo para 1.048.576 linhas, causando um tempo limite e precisa ser combinado com o max_row ou a política de encerramento de linha em branco contínuo.

3. Por que não consigo ler estilos ou modificar células no modo somente leitura?

O modo somente leitura retorna ReadOnlyCell, não suporta acesso de estilo e anotação e não permite modificação de valores. Isso é para manter um consumo de memória quase constante.

4. O que devo verificar após traduzir o formulário?

Além da linguagem correta, verifique se fórmulas, identificadores e relações de referência foram alterados.

📝 Resumo

A chave para a leitura de tabelas grandes está no streaming da leitura, na verificação de dimensões e na liberação de recursos em tempo hábil. O código é apenas o ponto de partida e a integridade dos dados depende de comparação e revisão manual.

Experimente agora: carregue o documento para iniciar a tradução, mantendo o layout original