O HotXLS ajusta as referências de fórmulas automaticamente quando insere ou elimina linhas ou colunas numa folha de cálculo XLSX. Os métodos InsertRows, DeleteRows, InsertCols e DeleteCols reescrevem todas as fórmulas sobreviventes para que as suas referências A1 — relativas, absolutas e de intervalo — continuem a apontar para os mesmos dados após a edição estrutural, e as referências a blocos eliminados convertem-se em #REF!, replicando o comportamento do Excel
Esta lógica previne erros silenciosos em relatórios. Se um gerador de dados escrever valores em C2:C9 com a fórmula =SUM(C2:C9) por baixo e, num passo posterior, inserir uma linha de cabeçalho no topo, a ausência de correção faria com que a soma continuasse a apontar para C2:C9 (enquanto os dados passaram para C3:C10), omitindo o último valor e duplicando o cabeçalho. Nenhum erro seria gerado e o ficheiro abriria normalmente com dados incorretos. Até à versão 2.160 do motor XLSX do HotXLS, as fórmulas não sofriam alterações em edições estruturais; a partir desta versão, o ajuste passou a ser automático
O que acontece às fórmulas quando se insere uma linha no Excel?
O que acontece às fórmulas quando se insere uma linha no Excel?
A regra do Excel determina que as referências acompanham os dados e não as coordenadas. Quando uma linha é inserida, todas as referências com índice de linha igual ou superior ao ponto de inserção recuam o número de linhas adicionadas; referências posicionadas acima do ponto de inserção mantêm-se inalteradas. A eliminação de linhas segue o raciocínio inverso: referências abaixo do bloco eliminado sobem e referências que apontassem para o bloco apagado convertem-se em #REF! uma vez que as células deixaram de existir. As colunas comportam-se de forma equivalente no seu eixo. Uma biblioteca de folhas de cálculo que pretenda gerar ficheiros compatíveis com utilizadores de Excel deve replicar esta lógica com exatidão
Uma particularidade relevante é que as referências absolutas também são ajustadas. A âncora $ em $B$2 controla apenas o comportamento da fórmula quando esta é copiada ou arrastada para outra célula, não exercendo influência em edições estruturais. Ao inserir uma linha acima da linha 2, o Excel reescreve $B$2 para $B$3 (preservando o cifrão), dado que o valor de que a fórmula depende mudou fisicamente para a linha 3. Ignorar a translação de referências absolutas corromperia as fórmulas ancoradas de forma intencional pelos utilizadores. O HotXLS ajusta ambas as variantes preservando a marcação $
Como o HotXLS ajusta as referências das fórmulas automaticamente?
Os quatro métodos de edição estrutural em TXLSXWorksheet recorrem ao mesmo motor geométrico: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). A chamada InsertRows(BeforeRow, Count) define um delta positivo, enquanto DeleteRows(StartRow, Count) indica um valor negativo. O processo reposiciona as células fisicamente (descartando as que se situem em blocos eliminados), lê as fórmulas sobreviventes e processa os textos via XlsxAdjustFormulaRowColRefs, ajustando os componentes de linha e coluna em referências A1. A rotina atualiza também intervalos fundidos, hiperligações, comentários, imagens, gráficos, formatação condicional, validação de dados e limites de tabelas
// Estrutura antes da edição:
// C2..C9 valores diários
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // uma linha em branco antes da linha 2
// Estrutura após a chamada:
// C3..C10 valores diários
// C11 =SUM(C3:C10) -- o intervalo acompanhou os dadosO analisador XlsxAdjustFormulaRowColRefs reconhece referências de células nas quatro variantes de ancoragem (A1, $A1, A$1, $A$1) e intervalos de dois cantos (como A1:B3), corrigindo cada coordenada separadamente. Fórmulas iniciadas com # (marcações de erro de edições anteriores) são ignoradas. O método InsertCols replica ainda o comportamento do Excel ao aplicar às novas colunas a largura da coluna imediatamente à esquerda
Quando é que uma referência eliminada se converte em #REF!?
O ajuste geométrico resulta em três cenários: índices anteriores ao ponto de edição mantêm-se inalterados; índices iguais ou superiores deslocam-se de acordo com o delta definido; e, em caso de eliminação, coordenadas que se situem no bloco apagado perdem a sua referência (dado que a célula deixou de existir), reescrevendo-se como #REF!. Em referências de intervalos, ambas as pontas são avaliadas e se uma delas residir na área apagada a referência completa é convertida em #REF! para evitar inconsistências
// A12 contém =A4+A6+A10
Sheet.DeleteRows(5, 3); // eliminar as linhas de 5 a 7
// A fórmula, agora em A9, lê =A4+#REF!+A7
// A4 : acima do bloco eliminado, inalterado
// A6 : dentro das linhas 5..7, removido -> #REF!
// A10 : abaixo do bloco, sobe para A7Exibir um erro #REF! explícito é a solução correta adotada pelo Excel. Apontar para células vizinhas após a eliminação da origem de dados geraria valores plausíveis mas errados; o erro #REF! propaga-se nas fórmulas dependentes e acusa falhas nos testes preliminares. O mesmo se aplica no eixo das colunas:
// E1 contém =B1*$C$1
Sheet.DeleteCols(3, 1); // remover a coluna C
// A fórmula, agora em D1, lê =B1*#REF!
// A âncora absoluta não protegeu $C$1 -- a célula foi removidaQue tipos de referências não são ajustados?
O analisador foca-se em referências A1 da própria folha com formato explícito de letra de coluna e número de linha. Referências a colunas inteiras (como A:A) ou linhas inteiras (como 1:1) são preservadas na sua redação original, bem como referências estruturadas de tabelas (como Table1[Amount]). O ajuste opera exclusivamente na notação A1; se a sua aplicação gerar fórmulas em formato R1C1, converta-as para A1 antes de edições estruturais, nos termos descritos no artigo sobre notação R1C1 em Delphi
Fórmulas entre folhas e propriedades ao nível do livro (workbook) são tratadas por rotinas dedicadas. Após ajustar a folha editada, ShiftSheetGeometry propaga a alteração geométrica a fórmulas de outras folhas que referenciem a folha editada, a séries de gráficos, destinos de hiperligações internas e nomes definidos. Os nomes definidos possuem proteção adicional: a eliminação de uma folha atualiza o qualificador SheetN! para #REF! na fórmula do nome definido, e a alteração do nome da folha atualiza as referências. O relacionamento entre nomes e fórmulas interfolhas é abordado no artigo sobre nomes definidos e fórmulas interfolhas
Recalcular após o ajuste geométrico
O ajuste geométrico reescreve o texto da fórmula mas não recalcula os valores. Após alterações estruturais, os resultados gravados refletem a geometria anterior, sendo a sequência recomendada: efetuar todas as inserções e eliminações, invocar o recálculo e, por fim, gravar o ficheiro. O ajuste prévio garante a integridade da árvore de dependências necessária ao motor de recálculo incremental. Se a fórmula contiver erros #REF!, o recálculo propaga a falha de imediato evitando dados obsoletos
O ajuste de referências integra as rotinas padrão do motor XLSX no HotXLS Delphi Excel Component para Delphi e C++Builder