Diagnosticando planilhas do Excel lentas?

Question

Um cliente entrou em contato comigo, reclamando das planilhas do Excel que estavam demorando muito para serem abertas. Eles usam o Excel para criar faturas, então eles têm centenas de planilhas do Excel com formatação leve e cálculos muito simples. Ao classificar as planilhas por tamanho, percebi que, enquanto a maioria das planilhas variava de 10 a 250k, havia algumas planilhas com arquivos de 2 a 3 MB e mais. Estranhamente, os filesizes não eram enormes, eles não contêm muitos dados, apenas um pouco de formatação, talvez duas ou três páginas de faturas impressas, mas a quantidade de dados era quase idêntica à menor planilhas dimensionadas (e de abertura normal).

Ao abrir o arquivo, a quantidade de RAM necessária aumentaria de 3 MB para 400 MB e ocuparia completamente um único núcleo (testado em um dual-core no escritório e meu laptop quad-core), enquanto iniciando. Eu pensei que eles tinham de alguma forma pegaram algum código VBA, mas não há macros, nem código VBA. Ctrl + End mostra 39 linhas e cerca de 12 colunas (termina em M). Até deletei os dados, linha por linha ou coluna por coluna, até que não restem mais dados, e ainda me dá o mesmo problema.

Revisei muitas pesquisas no Google, mas não cheguei a lugar nenhum. Alguém pode oferecer alguma ajuda?

    

Answer 1

Eu encontrei a resposta para o meu problema!

Usando as pistas fornecidas por Allix, abri os arquivos xlsx no 7-Zip e comparei os tamanhos dos arquivos. Havia um arquivo que era muito maior que os outros. O arquivo xl \ drawings \ drawing1.xml era um arquivo que continha várias referências a formatos repetidas vezes.

Passei algumas horas tentando descobrir um padrão, mas não consegui. Nada que eu fosse capaz de fazer funcionaria! Então, depois de um ataque de agravamento, acabei de apagar o maldito arquivo e tentei reabrir no Excel (2010- não o testei no Excel 2007).

Ele reclamou que o arquivo estava danificado e perguntou se eu gostaria de tentar um reparo. Reparar o arquivo simplesmente apagou a forma, mas não alterou a formatação do arquivo. Eu tive que salvar novamente o arquivo como o mesmo arquivo, o que foi um pouco estranho, mas funcionou!

Como eu mencionei antes, eu não tinha testado essa solução para nenhum outro produto além do Excel 2010, então não sei se o arquivo de forma era crítico para o Excel 2k7 ou qualquer produto do OpenOffice. Mas, se você tiver um problema parecido, espero que isso seja útil.

    

Answer 2

Você está usando o formato .xls binário ou o novo formato .xlsx baseado em XML? Em geral, o formato .xlsx resulta em uma redução drástica no tamanho do arquivo.

Verifique se há coisas como um número excessivo de estilos armazenados no documento.

Tente "remover informações pessoais" (um recurso do Excel / Word / etc.) para limpar certos tipos de arquivos que possam estar no arquivo.

Se a planilha é ou já foi compartilhada anteriormente, ela pode ter dados de compartilhamento antigos armazenados nela.

Uma correção simples é copiar e colar apenas os dados relevantes dessa planilha para um novo, salvá-lo no formato .xlsx e ver como ele é pequeno. Se for muito pequeno, então você tem a sua resposta - o Excel está fazendo uma contabilidade pobre das estruturas de dados de seu formato de arquivo interno.

Verifique também se há fórmulas complicadas ou circulares e referências a folhas externas (especialmente aquelas em unidades de rede). Se você acha que uma fórmula pode ser lenta, pode percorrê-la usando a ferramenta de auditoria de fórmulas.

Última coisa: se você salvá-lo como um .xlsx e ainda é grande, tente baixar a ferramenta de Produtividade do OpenXML SDK: link

Abra o .xlsx nele e dê uma olhada em todos os elementos dentro do arquivo e veja se algo é obviamente estranho. Isso pode exigir conhecimento de XML e dos esquemas específicos do OpenXML, mas é uma maneira infalível de descobrir o que está causando o inchaço.

P.S. - Se esse tipo de coisa incomodar você, pare de usar os formatos / programas da Microsoft ou sugira ao seu cliente que o faça. Se você pesquisar no Google por "inchaço de banco de dados do Microsoft Access", verá que a Microsoft tem um longo histórico de permitir que seus formatos proprietários vazem toneladas de dados inúteis para o disco que nunca são limpos. É como um vazamento de memória realmente desagradável que come seu disco em vez de sua memória RAM.

    

Answer 3

Outra solução possível seria:

1. Crie uma cópia do arquivo do Excel problemático
2. Abra essa cópia
3. Pressione CTRL + A e clique em "Limpar" - > "Limpar formatos"
4. Repita a etapa 3 em todas as planilhas
5. Salve o arquivo e tente reabri-lo

Quando há muita formatação, especialmente se houver muitos estilos diferentes aplicados a muitas células individuais, o Excel realmente se esforça para tentar aplicar a formatação ao abrir arquivos.

Naturalmente, quando você remove toda a formatação, você terá apenas texto com o estilo padrão aplicado a ela em todas as células com bordas, sombreamento etc. Mas desta forma você pode identificar a causa do problema.

    

Answer 4

Para uma redução drástica no tamanho, você pode tentar o formato .xlsb. Deve reduzir mais do que qualquer outro formato baseado em XML fornecido pelo Excel.

Abra uma dessas planilhas e dê uma olhada no formato. Procure por qualquer coisa normal. Tente copiar toda a coluna para outra planilha do Excel e cole apenas os valores (sem formatação). Veja se isso ajuda.

    

Answer 5

No meu caso, o problema estava relacionado a "Formatar como tabela". A tabela tinha 16000 colunas vazias. Eu acho que essa é uma das razões mais frequentes para esse tipo de problema.

    

Answer 6

Eu dei uma olhada em todas as regras de formatação condicional, em toda a planilha, e havia cargas. Eu passei por isso e limpei todos eles, e depois criei os que eu realmente precisava, e agora é muito rápido. Também me livrei de todas as 'Conexões' que eu não precisei, e guardei como .xlsb depois que tudo funcionou bem, e agora está mais rápido: o)