O HotXLS ajusta as referências de fórmulas automaticamente quando você insere ou exclui linhas ou colunas em uma planilha XLSX. Os métodos InsertRows, DeleteRows, InsertCols e DeleteCols do mecanismo reescrevem cada fórmula sobrevivente para que suas referências A1 — relativas, absolutas e de intervalo — continuem apontando para os mesmos dados após a edição estrutural, e as referências a um bloco excluído tornam-se #REF!, correspondendo ao comportamento do Excel
O bug que isso evita é um dos mais silenciosos na geração de relatórios. Um gerador grava valores diários em C2:C9 com =SUM(C2:C9) logo abaixo, e em uma etapa posterior insere uma linha de cabeçalho no topo. Se o mecanismo apenas mover os valores das células e deixar o texto da fórmula intacto, essa SOMA ainda lerá C2:C9 enquanto os dados agora vivem em C3:C10 — de modo que o total ignora silenciosamente o último dia e conta duas vezes o cabeçalho. Nada gera uma exceção, o arquivo abre sem problemas e o número simplesmente está errado. Antes da versão 2.160, o mecanismo XLSX do HotXLS deixava as fórmulas intocadas durante as edições estruturais; desde a 2.160, a reescrita é automática e não há nenhuma opção a ser configurada
O que acontece com as fórmulas quando você insere uma linha no Excel?
A regra do Excel é que as referências seguem os dados, não os endereços. Quando uma linha é inserida, cada referência cujo índice de linha esteja no ponto de inserção ou abaixo dele desce pelo número de linhas inseridas; as referências totalmente acima do ponto de inserção permanecem intocadas. A exclusão de linhas segue a mesma regra no sentido inverso: as referências abaixo do bloco excluído deslizam para cima, e as referências ao próprio bloco excluído tornam-se #REF! porque as células que elas nomeavam não existem mais. As colunas se comportam de forma idêntica ao longo do outro eixo. Uma biblioteca de planilhas que deseja que sua saída sobreviva ao contato com os usuários do Excel deve reproduzir esses mecanismos exatamente, pois os usuários raciocinam sobre suas fórmulas nesses termos sem sequer pensar nisso
A parte que surpreende os desenvolvedores é que as referências absolutas também se movem. As âncoras $ em $B$2 controlam o que acontece quando uma fórmula é copiada ou preenchida em outra célula — elas não fazem nada durante edições estruturais. Insira uma linha acima da linha 2 e o Excel reescreverá $B$2 para $B$3, com os cifrões intactos, porque o valor do qual a fórmula depende moveu-se fisicamente para a linha 3. Um mecanismo que deslocasse apenas referências relativas corromperia exatamente as fórmulas que as pessoas ancoram de forma mais deliberada. O HotXLS desloca ambas as formas e preserva os marcadores $ no texto reescrito
Como o HotXLS desloca as referências de fórmulas automaticamente?
Todos os quatro métodos de edição estrutural no TXLSXWorksheet delegam para um único mecanismo de geometria: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). O InsertRows(BeforeRow, Count) o chama com um delta de linha positivo, o DeleteRows(StartRow, Count) com um negativo, e os métodos de coluna fazem o mesmo no eixo das colunas. A rotina primeiro realoca as próprias células — descartando qualquer célula que caia dentro de um bloco excluído — e então percorre cada célula de fórmula sobrevivente e passa seu texto por XlsxAdjustFormulaRowColRefs, um scanner que localiza referências no estilo A1 e reescreve seus componentes de linha e coluna em relação ao deslocamento. A mesma passagem realoca intervalos mesclados, hiperlinks, comentários, imagens, gráficos, formatos condicionais, validações de dados e intervalos de tabelas, de modo que toda a planilha se move como uma única unidade
// Layout before the edit:
// C2..C9 daily figures
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // one blank row before row 2
// Layout after the call:
// C3..C10 daily figures
// C11 =SUM(C3:C10) -- the range moved with the dataVale a pena conhecer alguns detalhes do scanner. O XlsxAdjustFormulaRowColRefs reconhece referências de célula única em todas as quatro formas de âncora (A1, $A1, A$1, $A$1) e intervalos de dois cantos como A1:B3, ajustando cada ponto final independentemente. Fórmulas cujo texto já começa com # — um marcador de erro de uma edição anterior — são ignoradas em vez de serem verificadas novamente. E o InsertCols adiciona um detalhe de paridade com o Excel por conta própria: as colunas recém-inseridas herdam a largura de seu vizinho esquerdo, que é o que o comando Inserir Colunas da Planilha do Excel faz
Quando uma referência excluída torna-se #REF!?
A regra de deslocamento para um único índice de linha ou coluna tem três resultados. Um índice antes do ponto de edição permanece inalterado. Um índice no ponto de edição ou após ele se move pelo delta. E sob exclusão, um índice que cai dentro do bloco excluído não tem nenhum novo valor significativo — a célula se foi — então o scanner reescreve toda a referência como #REF!. Para uma referência de intervalo, ambos os pontos finais são submetidos à mesma regra e, se qualquer um dos pontos finais cair dentro do bloco excluído, a referência é reescrita como #REF! em vez de ser deixada meio válida
// A12 holds =A4+A6+A10
Sheet.DeleteRows(5, 3); // delete rows 5..7
// The formula, now in A9, reads =A4+#REF!+A7
// A4 : above the deleted block, unchanged
// A6 : inside rows 5..7, gone -> #REF!
// A10 : below the block, slides up -> A7Produzir um #REF! visível em vez de redirecionar silenciosamente é a escolha correta, e é a mesma que o Excel faz. Uma fórmula que aponta para uma célula vizinha após a exclusão de sua entrada real retornaria um número de aparência plausível; o #REF! se propaga através das fórmulas dependentes e surge no primeiro teste de fumaça. A mesma conversão se aplica ao eixo da coluna
// E1 holds =B1*$C$1
Sheet.DeleteCols(3, 1); // remove column C
// The formula, now in D1, reads =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is goneQuais formas de referência não são reescritas?
O scanner visa referências A1 na mesma planilha com um formato explícito de letra de coluna mais número de linha, e vale a pena ser preciso sobre o que fica fora disso. Referências a colunas inteiras como A:A e referências a linhas inteiras como 1:1 carecem de um dos dois componentes, de modo que o scanner as deixa como foram gravadas. Referências de tabela estruturadas (Table1[Amount]) também passam intocadas. A reescrita também opera estritamente na notação A1 — se o seu código constrói fórmulas no estilo R1C1, converta-as para A1 antes de uma edição estrutural, conforme descrito no artigo complementar sobre notação de fórmula R1C1 no Delphi
Construções no nível de planilhas cruzadas e pastas de trabalho são tratadas por passagens separadas, e não pelo scanner de texto de célula. Depois de ajustar a planilha editada, o ShiftSheetGeometry propaga a mesma alteração de geometria para fórmulas em outras planilhas que fazem referência à planilha editada, para intervalos de séries de gráficos, para destinos de hiperlinks internos e para nomes definidos. Nomes definidos recebem proteção adicional no nível do ciclo de vida da planilha: desde a versão 2.150, a exclusão de uma planilha reescreve cada qualificador SheetN! dentro da fórmula de um nome definido para #REF!, e renomear uma planilha reescreve o qualificador para o novo nome, para que os nomes nunca apontem para uma planilha que não existe mais. Como os nomes e as fórmulas de planilhas cruzadas se encaixam é abordado no artigo sobre nomes definidos e fórmulas entre planilhas
Recalcular após o deslocamento
O ajuste de referência reescreve o texto da fórmula; ele não recalcula os resultados. Após uma edição estrutural, os valores em cache armazenados ao lado das fórmulas descrevem a geometria antiga, portanto a sequência confiável é: realizar todas as inserções e exclusões primeiro, depois acionar o recálculo uma vez e, em seguida, salvar. Executar o deslocamento antes do recálculo também mantém as informações de dependência íntegras — cada referência reescrita nomeia seu verdadeiro precedente, que é exatamente o que o mecanismo de recálculo incremental e seu gráfico de dependência precisam para recalcular o conjunto mínimo de células afetadas. Se uma fórmula reescrita agora contiver #REF!, o recálculo apresenta o valor do erro imediatamente em vez de deixar um número desatualizado no arquivo
O ajuste de referência de fórmulas é fornecido como comportamento padrão do mecanismo XLSX no HotXLS Delphi Excel Component para Delphi e C++Builder; a página do produto traz a referência completa da API de edição de planilhas, incluindo os métodos de inserção e exclusão mostrados aqui