O HotXLS, o componente Excel para Delphi e C++Builder, divide automaticamente uma regra de formatação condicional ou de validação de dados em dois ou mais objetos de regra separados sempre que uma inserção ou eliminação de linha ou coluna corta o intervalo coberto pela regra em pedaços que precisam de âncoras de fórmula relativas diferentes, e depois reatribui a cada regra de formatação condicional um número de prioridade novo e único. Este comportamento foi lançado na versão 2.196 do motor XLSX e corre automaticamente, sem qualquer definição para o desativar. O gatilho é restrito mas comum: uma regra cellIs ou de expressão cuja fórmula lê uma célula relativamente ao seu próprio intervalo, num ficheiro de cálculo onde mais tarde se insere ou remove uma linha algures a meio desse mesmo intervalo
A maioria dos textos sobre automação do Excel para no problema do texto da fórmula: deslocar os números de linha e coluna dentro de cada SUM() e cada VLOOKUP() para que as referências continuem a apontar para as células certas. Essa metade da história é real, e é abordada em o artigo complementar sobre como o HotXLS reescreve referências de fórmula quando linhas e colunas se movem, mas uma regra de formatação condicional ou de validação de dados não é apenas uma fórmula pousada numa célula. Ela emparelha uma fórmula com um intervalo, sqref em termos ECMA-376, e os dois têm de se mover juntos. Quando uma edição estrutural corta esse intervalo em duas partes que precisariam de dois deslocamentos relativos diferentes para se manterem corretas, manter um único objeto de regra com uma única cadeia de fórmula deixa de ser uma opção, e fingir o contrário é a forma como uma regra de realce começa silenciosamente a comparar as linhas erradas
Porque divide a inserção de uma linha uma regra de formatação condicional em vez de simplesmente a deslocar?
Uma regra de formatação condicional ou de validação de dados mantém exatamente uma fórmula para todo o seu intervalo, avaliada relativamente a uma única célula-âncora, pelo que assim que uma edição obriga duas partes desse intervalo a precisarem de dois deslocamentos relativos diferentes, uma única fórmula deixa de conseguir descrever corretamente ambas as partes. A ECMA-376 exprime a cobertura de uma regra através do atributo sqref no elemento conditionalFormatting ou dataValidation, e o Excel avalia Formula1 e Formula2 como se o texto tivesse sido escrito na célula superior esquerda desse sqref e preenchido pelo resto dele, da mesma forma que uma fórmula relativa comum se preenche ao longo de uma coluna. Imagine um realce de variação sobre B2:B50 que assinala qualquer valor real que exceda o seu orçamento, construído como uma regra cellIs cujo Formula1 é o texto literal C2, significando comparar a célula B da linha atual com a célula C dessa mesma linha
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25Inserir essa única linha separadora na antiga linha 25 faz com que as linhas acima do ponto de inserção não se movam, pelo que a sua parte da regra continua a ler Formula1 como C2 corretamente. As linhas que antes eram 25 a 50 deslizam para 26 a 51, e para elas C2 é agora uma célula completamente errada, uma vez que a linha 26 precisa de comparar com C26, e não com um valor de orçamento duas dezenas de linhas acima
Como decide o HotXLS se uma regra precisa de ser dividida?
O HotXLS só cria objetos de regra adicionais quando a geometria genuinamente o exige: uma rotina interna, XlsxBuildShiftedRuleParts, percorre cada área disjunta no sqref da regra, calcula qual era a célula-âncora dessa área antes da edição e qual passa a ser depois, e verifica se todos os pedaços resultantes precisariam da mesma correção de deslocamento relativo. Se todos os pedaços concordarem, sobrevive uma única regra, com o seu sqref reconstruído como a união dos pedaços deslocados e a sua fórmula reancorada uma vez. Uma divisão genuína só acontece quando os pedaços discordam, exatamente o caso B2:B50 acima, onde o bloco superior mantém a sua âncora original e o bloco inferior precisa de uma nova
Reancorar a fórmula de um pedaço é um movimento em dois passos que reutiliza mecanismos que o HotXLS já possui para grupos de fórmulas partilhadas OOXML: primeiro a fórmula é traduzida como se tivesse sido originalmente ancorada na própria célula superior esquerda desse pedaço, usando a mesma aritmética de deslocamento relativo que expande uma fórmula partilhada ao longo do seu intervalo, depois o resultado passa pelo mesmo verificador de deslocamento de linhas e colunas que reescreve fórmulas de folha de cálculo comuns. É assim que Formula1 passa de C2 para C26 em dois movimentos em vez de um caso especial escrito à mão: traduzir C2 para a frente 23 linhas para obter C25, como se a regra tivesse sempre começado ali, e depois deixar o deslocamento comum na linha 25 empurrá-la até C26. Todas as outras propriedades, cor de preenchimento, parar-se-verdadeiro, o próprio operador, seguem inalteradas para o novo objeto de regra, pelo que ambas as metades continuam a pintar as células com a cor que sempre tiveram
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Dividem-se as barras de dados e os conjuntos de ícones da mesma forma que as regras cellIs?
Não: o HotXLS só particiona os tipos de regra cuja correção efetivamente depende de uma fórmula relativa por região, comparações cellIs e regras de expressão, e deixa todos os outros tipos de formatação condicional como um único objeto de regra cujo sqref simplesmente cresce para cobrir os pedaços deslocados como uma união multi-área. Internamente, o ramo é uma simples verificação de Kind, cf.Kind in [cfkCellIs, cfkExpression], nada mais exótico do que isso. Barras de dados, escalas de duas e três cores, conjuntos de ícones, classificações de topo e fundo, e os detetores de duplicados, células em branco, e erros transportam uma carga útil, uma cor de barra, um conjunto de paragens de escala, uma família de ícones, que descreve todo o intervalo coberto de uma só vez em vez de uma comparação relativa por célula, pelo que dividi-los em vários objetos de regra priorizados não traria qualquer benefício de correção e só acrescentaria regras a gerir. Quando uma edição divide o seu intervalo, o HotXLS recombina os pedaços numa única regra com um sqref multi-área e reancora a carga útil como uma única unidade, em vez de clonar um novo objeto de regra por pedaço. A distinção está alinhada com a taxonomia de tipos de regra em o artigo sobre os fundamentos de formatação condicional e texto formatado: as barras de dados, as escalas de cor, e os conjuntos de ícones já se distinguem das regras cellIs por ignorarem inteiramente a propriedade Style, e agora verifica-se que se distinguem também da reancoragem por região pela mesma razão subjacente
Porque mudam as prioridades das regras após uma edição estrutural?
As prioridades mudam porque cada clone começa por deter exatamente o mesmo valor de prioridade da regra de onde se dividiu, e o HotXLS executa depois uma passagem de normalização que resolve os duplicados resultantes numa ordenação limpa e sem lacunas, em vez de deixar duas regras empatadas na mesma posição. Uma segunda rotina interna, XlsxNormalizeConditionalFormatPriorities, recolhe a prioridade atual de cada formato condicional, recua para a posição dessa regra na coleção para qualquer regra que nunca tenha tido uma definida explicitamente, ordena toda a lista de forma estável para que os empates mantenham a sua ordem relativa original, e renumera o resultado ordenado para uma sequência densa 1, 2, 3, sem lacunas nem repetições. O HotXLS executa-a uma vez antes de um deslocamento começar, para que a clonagem parta de uma base limpa, e novamente depois de cada divisão e de cada regra esvaziada ser removida, para que o ficheiro guardado nunca tenha duas entradas de regra a reivindicar a mesma prioridade. Isto importa se seguiu o conselho no artigo sobre os fundamentos de formatação condicional de deixar lacunas entre os valores de prioridade para que uma regra posterior se possa encaixar sem renumerar o resto: as lacunas sobrevivem até a próxima edição de linha ou coluna tocar nessa folha, e depois colapsam, porque a normalização só garante unicidade e ordem estável, não que o seu esquema de numeração original regresse inalterado
As regras de validação de dados também se dividem, sem prioridade a renumerar
As regras de validação de dados passam pela mesma lógica de particionamento de intervalo que as regras cellIs e de expressão, e ao contrário da formatação condicional, todos os tipos de validação seguem esse caminho de forma uniforme: o HotXLS não tem uma família separada sem fórmula para validação de dados, como acontece com as barras de dados e os conjuntos de ícones na formatação condicional, pelo que uma regra simples de lista ou de número inteiro é particionada pela mesma rotina idêntica que trata uma fórmula personalizada relativa. O que difere é a prioridade: a ECMA-376 não atribui ao elemento dataValidation qualquer atributo priority, pelo que não existe um passo de renumeração para validações como existe para formatos condicionais. Imagine uma validação de fórmula personalizada que impede o valor real de cada linha de exceder o seu próprio orçamento na coluna ao lado
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)Isto importa pela mesma razão pela qual o artigo sobre os fundamentos de validação de dados avisa contra anexar uma regra antes de a contagem de linhas ser final: uma validação cobre apenas as células literais que lhe foram dadas, e uma edição estrutural posterior pode deixar duas ou mais regras a fazer o trabalho que uma costumava fazer. Nada quebra funcionalmente: cada célula no intervalo original continua a ser validada por algo, mas código que assuma uma entrada de DataValidations por coluna começará a indexar mal assim que a primeira edição a tocar. Existe um teto rígido para até onde isto pode ir: se a divisão empurrasse uma folha de cálculo para além de 65.534 regras de validação de dados, o HotXLS levanta uma exceção em vez de escrever um ficheiro que o Excel rejeitaria silenciosamente, o que é a biblioteca a recusar-se a fabricar um livro de cálculo corrompido, em vez de um limite que uma utilização comum tenha probabilidade de atingir
O que verificar após uma inserção ou eliminação em massa
As duas coisas que vale a pena verificar depois de um script executar um lote de edições de linha ou coluna sobre uma folha cheia de formatos condicionais e validações são a contagem total de regras e a ordem de prioridade, uma vez que ambas podem desviar-se de formas fáceis de não notar numa revisão de código e óbvias assim que alguém abre Gerir Regras no Excel. Uma única edição raramente causa muito estrago: uma única inserção a meio de uma regra cellIs produz no máximo dois objetos de regra onde havia um. O risco acumula-se quando uma rotina de geração de relatórios insere linhas uma a uma num ciclo sobre uma folha que já transporta várias regras ancoradas em fórmula: cada passagem pode voltar a dividir regras que uma passagem anterior já dividiu, e cinco regras cellIs originais podem acabar em várias vezes esse número de fragmentos de baixo valor a cobrir lascas do intervalo original. Agrupar edições estruturais, inserindo todo o novo bloco numa única chamada em vez de linha a linha, mantém a contagem de regras ligada ao número de âncoras genuinamente distintas, e não ao número de edições executadas
O particionamento de regras e a normalização de prioridades são distribuídos como comportamento standard do motor XLSX no componente Excel HotXLS para Delphi, 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 formatação condicional e validação de dados aqui descritos