Referências de Fórmulas ao Inserir Linhas no HotXLS

O HotXLS ajusta automaticamente as referências das fórmulas quando insere ou elimina linhas ou colunas numa folha de cálculo XLSX. Os métodos InsertRows, DeleteRows, InsertCols e DeleteCols do motor 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 depois da edição estrutural, e as referências para dentro de um bloco eliminado passam a #REF!, tal como no Excel

O erro que isto evita é dos mais silenciosos na geração de relatórios. Um gerador escreve os valores diários em C2:C9 com =SUM(C2:C9) por baixo, e depois um passo posterior insere uma linha de cabeçalho no topo. Se o motor só mover os valores das células e deixar o texto das fórmulas em paz, essa SUM continua a ler C2:C9 enquanto os dados passaram a viver em C3:C10 — pelo que o total perde em silêncio o último dia e conta o cabeçalho a dobrar. Nada levanta uma exceção, o ficheiro abre bem, e o número está simplesmente errado. Antes da versão 2.160, o motor 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á nenhum sinalizador para definir

O que acontece às fórmulas quando se insere uma linha no Excel?

A regra do Excel é que as referências seguem os dados, não os endereços. Quando se insere uma linha, todas as referências cujo índice de linha esteja no ponto de inserção ou abaixo dele descem tantas linhas quantas as inseridas; as referências totalmente acima do ponto de inserção ficam intocadas. Eliminar linhas aplica a mesma regra ao contrário: as referências abaixo do bloco eliminado sobem, e as referências para dentro do próprio bloco eliminado passam a #REF! porque as células que nomeavam já não existem. As colunas comportam-se de forma idêntica no outro eixo. Uma biblioteca de folhas de cálculo que queira que a sua saída sobreviva ao contacto com utilizadores de Excel tem de reproduzir esta mecânica exatamente, porque os utilizadores raciocinam sobre as suas fórmulas nestes termos sem sequer pensar nisso

A parte que surpreende os programadores é 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 para outra célula — não fazem nada durante edições estruturais. Insira uma linha acima da linha 2 e o Excel reescreve $B$2 como $B$3, com os cifrões intactos, porque o valor de que a fórmula depende se mudou fisicamente para a linha 3. Um motor que deslocasse apenas as referências relativas corromperia precisamente as fórmulas que as pessoas ancoram com mais deliberação. O HotXLS desloca as duas formas e preserva os marcadores $ no texto reescrito

Como é que o HotXLS desloca automaticamente as referências das fórmulas?

Os quatro métodos de edição estrutural de TXLSXWorksheet delegam num único motor de geometria: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). O InsertRows(BeforeRow, Count) chama-o 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 começa por relocalizar as próprias células — descartando qualquer célula que caia dentro de um bloco eliminado — e depois percorre todas as células de fórmula sobreviventes e passa o seu texto por XlsxAdjustFormulaRowColRefs, um analisador que encontra referências ao estilo A1 e reescreve as suas componentes de linha e coluna face ao deslocamento. A mesma passagem relocaliza intervalos unidos, hiperligações, comentários, imagens, gráficos, formatos condicionais, validações de dados e intervalos de tabela, para que toda a folha se mova como uma unidade

Comparacao em grelha a mostrar o HotXLS em Delphi a deslocar referencias de folha de calculo depois de uma linha inserida: os valores diarios de C2:C9 descem para C3:C10, o texto da formula SUM e reescrito em conformidade, e a ancora absoluta $B$2 tambem acompanha os seus dados — Uma chamada a InsertRows deixa cair uma linha em branco acima do bloco e o motor reescreve =SUM(C2:C9) como =SUM(C3:C10) para que o total siga os dados, enquanto as âncoras absolutas do tipo $B$2 descem para $B$3

// Disposição 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
// Disposição depois da chamada:
//   C3..C10 valores diários
//   C11     =SUM(C3:C10)    -- o intervalo acompanhou os dados

Vale a pena conhecer alguns pormenores do analisador. O XlsxAdjustFormulaRowColRefs reconhece referências a uma só célula nas quatro formas de ancoragem (A1, $A1, A$1, $A$1) e intervalos de dois cantos como A1:B3, ajustando cada extremidade de forma independente. As fórmulas cujo texto já começa por # — um marcador de erro de uma edição anterior — são ignoradas em vez de reanalisadas. E o InsertCols acrescenta um requinte de paridade com o Excel: as colunas acabadas de inserir herdam a largura da vizinha à esquerda, que é o que o comando Inserir Colunas de Folha do Excel faz

Quando é que uma referência eliminada passa a #REF!?

A regra de deslocamento para um único índice de linha ou coluna tem três desfechos. Um índice anterior ao ponto de edição fica inalterado. Um índice no ponto de edição ou depois dele move-se pelo delta. E, no caso da eliminação, um índice que caia dentro do bloco eliminado não tem novo valor com significado — a célula desapareceu — pelo que o analisador reescreve a referência inteira como #REF!. Numa referência de intervalo, ambas as extremidades passam pela mesma regra e, se qualquer delas cair dentro do bloco eliminado, a referência é reescrita como #REF! em vez de ficar meio válida

Vista em forma de decisao da regra de reescrita do HotXLS quando codigo Delphi elimina as linhas 5 a 7 de uma folha de calculo: o termo A4 esta acima do bloco e mantem-se, A6 cai dentro e passa a #REF!, A10 sobe tres linhas para A7, pelo que a formula relocalizada em A9 fica =A4+#REF!+A7 — O DeleteRows arruma todas as referências sobreviventes em três destinos: acima do bloco sem alteração, dentro do bloco reescrita de forma bem visível para #REF!, e abaixo do bloco a subir pelo número de linhas eliminadas

// A12 contém  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // elimina as linhas 5..7
// A fórmula, agora em A9, fica  =A4+#REF!+A7
//   A4  : acima do bloco eliminado, sem alteração
//   A6  : dentro das linhas 5..7, desapareceu      -> #REF!
//   A10 : abaixo do bloco, sobe                    -> A7

Produzir um #REF! bem visível em vez de reapontar em silêncio é o compromisso correto, e é o que o Excel faz. Uma fórmula que aponte para uma célula vizinha depois de a sua verdadeira entrada ter sido eliminada devolveria um número de aspeto plausível; o #REF! propaga-se pelas fórmulas dependentes e aparece no primeiro teste rápido. A mesma conversão se aplica no eixo das colunas

// E1 contém  =B1*$C$1
Sheet.DeleteCols(3, 1);      // remove a coluna C
// A fórmula, agora em D1, fica  =B1*#REF!
// A âncora absoluta não protegeu $C$1 -- a própria célula desapareceu

Que formas de referência não são reescritas?

O analisador visa referências A1 da mesma folha com uma forma explícita de letra de coluna mais número de linha, e vale a pena ser preciso sobre o que fica de fora. As referências a colunas inteiras como A:A e a linhas inteiras como 1:1 não têm uma das duas componentes, pelo que o analisador as deixa tal como estão. As referências estruturadas de tabela (Table1[Amount]) passam igualmente intocadas. A reescrita opera também estritamente sobre a notação A1 — se o seu código constrói fórmulas em estilo R1C1, converta-as para A1 antes de uma edição estrutural, como se descreve no artigo companheiro sobre a notação de fórmulas R1C1 em Delphi

As construções entre folhas e ao nível da pasta de trabalho são tratadas por passagens separadas e não pelo analisador do texto das células. Depois de ajustar a folha editada, o ShiftSheetGeometry propaga a mesma alteração de geometria às fórmulas de outras folhas que referenciem a folha editada, aos intervalos de séries de gráficos, aos destinos de hiperligações internas e aos nomes definidos. Os nomes definidos recebem proteção adicional ao nível do ciclo de vida da folha: desde a versão 2.150, eliminar uma folha de cálculo reescreve para #REF! todos os qualificadores SheetN! dentro da fórmula de um nome definido, e mudar o nome de uma folha reescreve o qualificador para o nome novo, de modo que os nomes nunca apontem para uma folha que já não existe. Como os nomes e as fórmulas entre folhas se conjugam é tratado no artigo sobre nomes definidos e fórmulas entre folhas

Recalcular depois do deslocamento

O ajuste de referências reescreve o texto das fórmulas; não recalcula resultados. Depois de uma edição estrutural, os valores em cache guardados junto às fórmulas descrevem a geometria antiga, pelo que a sequência fiável é: fazer primeiro todas as inserções e eliminações, depois desencadear o recálculo uma vez, e só então gravar. Correr o deslocamento antes do recálculo mantém também honesta a informação de dependências — cada referência reescrita nomeia o seu verdadeiro precedente, que é exatamente o que o motor de recálculo incremental e o seu grafo de dependências precisam para recalcular o conjunto mínimo de células afetadas. Se uma fórmula reescrita passar a conter #REF!, o recálculo faz aparecer o valor de erro de imediato em vez de deixar um número obsoleto no ficheiro

O ajuste de referências de fórmulas é comportamento padrão do motor XLSX do 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 folhas de cálculo, incluindo os métodos de inserção e eliminação aqui mostrados