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 exclusão de linha ou coluna corta o intervalo coberto pela regra em partes que precisam de âncoras de fórmula relativas diferentes, e depois reatribui a cada regra de formatação condicional um novo número de prioridade único. O comportamento chegou na versão 2.196 do motor XLSX e roda automaticamente, sem nenhuma configuração para desativá-lo. O gatilho é específico, mas comum: uma regra cellIs ou de expressão cuja fórmula lê uma célula relativa ao seu próprio intervalo, em uma planilha que depois recebe uma linha inserida ou removida em algum lugar no meio exatamente desse 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 SOMA() e cada PROCV() para que as referências ainda apontem para as células certas. Essa metade da história é real, e é coberta em o artigo complementar sobre como o HotXLS reescreve referências de fórmula quando linhas e colunas se movem, mas uma formatação condicional ou uma regra de validação de dados não é apenas uma fórmula sentada em uma célula. Ela pareia uma fórmula com um intervalo, sqref nos termos da ECMA-376, e os dois precisam se mover juntos. Quando uma edição estrutural fatia esse intervalo em duas partes que precisariam de dois deslocamentos relativos diferentes para permanecerem corretas, manter um único objeto de regra com uma única string de fórmula deixa de ser uma opção, e fingir o contrário é como uma regra de destaque começa silenciosamente a comparar as linhas erradas
Por que inserir uma linha divide uma regra de formatação condicional em vez de simplesmente movê-la?
Uma regra de formatação condicional ou de validação de dados mantém exatamente uma fórmula para todo o seu intervalo, avaliada em relação a uma única célula âncora, de modo que, assim que uma edição força duas partes desse intervalo a precisarem de dois deslocamentos relativos diferentes, uma única fórmula não consegue mais descrever ambas as partes corretamente. A ECMA-376 expressa a cobertura de uma regra como o atributo sqref no elemento conditionalFormatting ou dataValidation, e o Excel avalia Formula1 e Formula2 como se o texto tivesse sido digitado na célula superior esquerda daquele sqref e preenchido pelo resto dele, da mesma forma que uma fórmula relativa comum se preenche ao longo de uma coluna. Imagine um destaque de variância sobre B2:B50 que sinaliza qualquer valor real que exceda seu orçamento, construído como uma regra cellIs cuja Formula1 é o texto literal C2, significando comparar a célula B da linha atual contra 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 25Insira essa linha separadora única na antiga linha 25, e as linhas acima do ponto de inserção não se movem, então a parte delas na regra continua lendo Formula1 como C2 corretamente. As linhas que antes eram 25 a 50 deslizam para baixo, para 26 a 51, e para elas C2 agora é totalmente a célula errada, já que a linha 26 precisa comparar contra C26, não contra um valor de orçamento duas dezenas de linhas acima
Como o HotXLS decide se uma regra precisa se dividir
O HotXLS só cria objetos de regra extras quando a geometria genuinamente exige isso: uma rotina interna, XlsxBuildShiftedRuleParts, percorre cada área disjunta no sqref da regra, calcula qual era a célula âncora daquela área antes da edição e o que ela se torna depois, e verifica se cada pedaço resultante precisaria da mesma correção de deslocamento relativo. Se todos os pedaços concordam, uma única regra sobrevive, com seu sqref reconstruído como a união dos pedaços deslocados e sua fórmula rebaseada 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 sua âncora original e o bloco inferior precisa de uma nova
Rebasear a fórmula de um pedaço é um movimento em duas etapas que reutiliza a maquinaria que o HotXLS já carrega para grupos de fórmulas compartilhadas OOXML: primeiro a fórmula é traduzida como se tivesse sido originalmente ancorada na própria célula superior esquerda daquele pedaço, usando a mesma matemática de deslocamento relativo que expande uma fórmula compartilhada por todo seu intervalo, depois o resultado passa pelo mesmo escaneador de deslocamento de linha e coluna que reescreve fórmulas comuns de planilha. É assim que Formula1 vai de C2 para C26 em dois movimentos em vez de um caso especial escrito à mão: traduzir C2 para frente por 23 linhas para obter C25, como se a regra sempre tivesse começado ali, depois deixar o deslocamento comum na linha 25 empurrá-la para C26. Toda outra propriedade — cor de preenchimento, interromper-se-verdadeiro, o próprio operador — segue inalterada para o novo objeto de regra, de modo que ambas as metades continuam pintando as células da mesma cor de sempre
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Barras de dados e conjuntos de ícones se dividem da mesma forma que regras cellIs?
Não: o HotXLS só particiona os tipos de regra cuja correção realmente depende de uma fórmula relativa por região — comparações cellIs e regras de expressão — e deixa todo outro tipo 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 que isso. Barras de dados, escalas de duas e três cores, conjuntos de ícones, classificações de topo e fundo, e os detectores de duplicata, célula em branco e erro carregam um payload — uma cor de barra, um conjunto de pontos de escala, uma família de ícones — que descreve todo o intervalo coberto de uma vez, em vez de uma comparação relativa por célula, de modo que dividi-los em vários objetos de regra priorizados não traria nenhum ganho de correção e apenas adicionaria regras para gerenciar. Quando uma edição divide seu intervalo, o HotXLS recombina os pedaços em uma única regra com um sqref multi-área e reancora o payload como uma única unidade, em vez de clonar um novo objeto de regra por pedaço. A distinção se alinha com a taxonomia de tipos de regra em o artigo sobre fundamentos de formatação condicional e rich text: barras de dados, escalas de cor e conjuntos de ícones já se distinguem das regras cellIs por ignorarem completamente a propriedade Style, e agora se revela que eles também se distinguem no reancoramento por região, pelo mesmo motivo de fundo
Por que as prioridades de regra mudam depois de uma edição estrutural?
As prioridades mudam porque todo clone começa mantendo exatamente o mesmo valor de prioridade da regra da qual se dividiu, e o HotXLS roda uma passada de normalização depois, resolvendo as duplicatas resultantes em uma ordenação limpa e sem lacunas, em vez de deixar duas regras empatadas na mesma posição. Uma segunda rotina interna, XlsxNormalizeConditionalFormatPriorities, pega a prioridade atual de cada formatação condicional, recorre à posição dessa regra na coleção para qualquer regra que nunca teve uma definida explicitamente, ordena a lista inteira de forma estável para que os empates mantenham sua ordem relativa original, e renumera o resultado ordenado para uma sequência densa 1, 2, 3, sem lacunas e sem repetições. O HotXLS a roda uma vez antes de um deslocamento começar, de modo que a clonagem parte de uma base limpa, e novamente depois de cada divisão e depois que cada regra esvaziada é removida, de modo que o arquivo salvo nunca tem duas entradas de regra reivindicando a mesma prioridade. Isso importa se você seguiu o conselho no artigo de fundamentos de formatação condicional de deixar lacunas entre valores de prioridade para que uma regra posterior possa se encaixar sem renumerar o resto: as lacunas sobrevivem até que a próxima edição de linha ou coluna toque naquela planilha, depois colapsam, porque a normalização só garante unicidade e ordem estável, não que seu esquema de numeração original volte inalterado
Regras de validação de dados também se dividem, sem uma prioridade para renumerar
As regras de validação de dados passam pela mesma lógica de particionamento de intervalo que as formatações condicionais cellIs e de expressão, e, ao contrário da formatação condicional, todo tipo de validação segue esse caminho uniformemente: o HotXLS não tem uma família separada sem fórmula para validação de dados da forma como barras de dados e conjuntos de ícones são para formatação condicional, de modo 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 dá ao elemento dataValidation nenhum atributo priority, de modo que não há etapa de renumeração para validações da forma que há para formatações condicionais. Imagine uma validação de fórmula personalizada que mantém o valor real de cada linha de não exceder 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)Isso importa pelo mesmo motivo que o artigo de 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 você deu a ela, e uma edição estrutural posterior pode deixar duas ou mais regras fazendo o trabalho que uma costumava fazer. Nada quebra funcionalmente: toda célula no intervalo original ainda é validada por algo, mas código que assume uma entrada de DataValidations por coluna vai começar a indexar errado depois que a primeira edição a tocar. Há um teto rígido para até onde isso pode ir: se a divisão empurrar uma planilha para além de 65.534 regras de validação de dados, o HotXLS levanta uma exceção em vez de escrever um arquivo que o Excel rejeitaria silenciosamente, o que é a biblioteca se recusando a fabricar uma pasta de trabalho corrompida, em vez de um limite que o uso comum provavelmente alcançaria
O que verificar depois de uma inserção ou exclusão em lote
As duas coisas que vale a pena verificar depois que um script roda um lote de edições de linha ou coluna sobre uma planilha cheia de formatações condicionais e validações são a contagem total de regras e a ordem de prioridade, já que ambas podem se desviar de formas fáceis de passar despercebidas em revisão de código e óbvias no instante em que alguém abre Gerenciar Regras no Excel. Uma única edição raramente causa muito dano: uma única inserção no meio de uma regra cellIs produz no máximo dois objetos de regra onde havia um. O risco se acumula quando uma rotina de geração de relatório insere linhas uma de cada vez em um loop sobre uma planilha que já carrega várias regras ancoradas em fórmula: cada passada pode redividir regras que uma passada anterior já dividiu, e cinco regras cellIs originais podem acabar em várias vezes esse número de fragmentos de baixo valor cobrindo lascas do intervalo original. Agrupar edições estruturais, inserindo todo o bloco novo em uma única chamada em vez de uma linha de cada vez, mantém a contagem de regras atrelada ao número de âncoras genuinamente distintas, em vez do número de edições realizadas
O particionamento de regras e a normalização de prioridade vêm como comportamento padrão 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 planilha, incluindo os métodos de formatação condicional e validação de dados descritos aqui