O HotXLS é um componente de planilha nativo para Delphi e C++Builder, e desde a versão 2.209.0 ele consegue responder à pergunta que o Excel normalmente mantém para si: para esta célula exata, quais regras de formatação condicional disparam, e para qual preenchimento, fonte, barra de dados ou ícone elas resolvem. Essa resposta é o que você precisa no momento em que sua saída é um relatório HTML, um PDF, ou uma grade que você mesmo pinta
Este é um problema diferente de criar regras. Duas notas anteriores cobrem o lado da autoria: formatação condicional e estilos de rich text trata de anexar regras e formatos diferenciais a um intervalo, e particionamento de formatos condicionais ancorados trata do que acontece com o intervalo de uma regra quando linhas e colunas são inseridas ou excluídas. Ambos são estruturais. Este é sobre semântica: dada uma pasta de trabalho que já carrega regras, calcule o destaque
Por que o formato de arquivo não diz quais células acendem
A resposta curta é que a ECMA-376 e a ISO 29500-1 definem armazenamento, não avaliação. Um elemento conditionalFormatting (§18.3.1.18) carrega um sqref e uma lista de filhos cfRule (§18.3.1.10), e cada regra carrega um type, um operator opcional, uma priority, um sinalizador stopIfTrue, um ou dois filhos formula, e para as famílias visuais um conjunto de limiares cfvo. Cada um deles descreve fielmente o que o usuário configurou, e nenhum deles é um algoritmo. Para metade dos tipos de regra essa lacuna não importa: cellIs com operator="greaterThan" significa maior que, e containsText significa que a substring está presente. A lacuna se abre nas famílias agregadas. Uma regra top10 com rank="10" e percent="1" sobre 27 células numéricas preenchidas destaca quantas células? Dois vírgula sete não é um número. Arredondar, arredondar para baixo, ou arredondar para cima — a especificação silencia, e escolher errado significa que seu PDF discorda da pasta de trabalho que o cliente tem aberta ao lado
Regras de célula única e onde TCondFormatRule.Evaluate para
O HotXLS pegou a metade barata primeiro. TCondFormatRule.Evaluate em lxCondFormat.pas, adicionado na 2.199.0, responde se uma regra dispara para uma célula sem saber nada sobre o resto do intervalo. Ele trata os oito operadores de comparação BIFF por trás de cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), regras expression livres avaliadas na célula para que referências relativas se realinhem corretamente, os quatro predicados de texto, e os predicados de vazio e erro. Os limiares vêm de FFormula1 e FFormula2 resolvidos através de TXLSCalculator.GetRangeValue na posição da célula, e limites invertidos são trocados em vez de rejeitados
var
I: Integer;
Rule: TCondFormatRule;
Value: Variant;
begin
Value := Sheet.Cells[Row, Col].Value;
for I := 0 to CondFormat.RuleCount - 1 do
begin
Rule := CondFormat.Rule(I);
// Single-cell verdict only. Aggregate and visual kinds answer False.
if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
ApplyHighlight(Row, Col, Rule.Style);
end;
end;A parte honesta desse método é o que ele se recusa a adivinhar. top10, aboveAverage, belowAverage, duplicateValues e uniqueValues retornam False, não porque são difíceis mas porque são indecidíveis a partir de uma célula — cada uma delas precisa de uma estatística sobre o domínio inteiro. As quatro famílias visuais, dataBar, colorScale2, colorScale3 e iconSet, retornam False por um motivo diferente: elas nunca produzem um booleano, elas produzem um payload de renderização, e um tipo de retorno Boolean é a forma errada para elas
Como um avaliador em nível de planilha evita reescanear a planilha?
Calculando cada quantidade compartilhada uma única vez, na construção, e nunca mais. TXLSXConditionalFormatEvaluator em lxHandleX.pas é um instantâneo imutável para uma planilha, construído através de TXLSXWorksheet.CreateConditionalFormatEvaluator, e todo o seu design é uma defesa contra a implementação ingênua onde toda célula pintada dispara uma varredura completa do intervalo
Quatro coisas acontecem no construtor. Cada sqref multiárea distinto é analisado exatamente uma vez em um TXlsxCfRangeSnapshot, então dez regras compartilhando um intervalo compartilham uma análise e uma passagem de estatísticas. Essa passagem transmite média, desvio populacional, mínimo e máximo sobre as células preenchidas em uma única varredura, e só retém um array numérico ordenado quando uma regra Top/Bottom ou de percentil realmente precisa de estatísticas de ordem. Chaves de duplicata e únicas são construídas de forma segura para Unicode e ordenadas em lote uma única vez em vez de por busca. Depois o eixo de linhas é cortado em bandas em cada limite de área, então EvaluateCell busca binariamente uma banda e só visita regras cujos intervalos podem possivelmente alcançar aquela linha
A quarta é a que mais importa em escala. Uma fórmula de regra relativa como =A1>AVERAGE($A$1:$A$100) significa algo diferente em cada célula do domínio, e a implementação óbvia compila uma nova árvore de sintaxe por célula. TXlsxCfRulePlan a compila uma vez e reavalia a mesma árvore através de deslocamentos de coordenada reversíveis, o que preserva o comportamento de âncora do Excel sem uma alocação de árvore de sintaxe por célula. As regras então são sobrepostas por priority, e uma correspondência em uma regra cujo StopIfTrue está definido interrompe o laço, exatamente como o Excel faz curto-circuito
var
Evaluator: TXLSXConditionalFormatEvaluator;
Res: TXLSXCfCellResult;
begin
Evaluator := Sheet.CreateConditionalFormatEvaluator;
try
if Evaluator.EvaluateCell(Row, Col, Res) then
begin
if Res.HasFillColor then
Canvas.Brush.Color := TColor(Res.FillColor);
if Res.HasIcon then
// IconIndex is zero-based inside Res.IconSetType
DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
if Res.HasDataBar then
// DataBarAxis and DataBarEnd are normalised to 0..1
DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
if not Res.ShowCellValue then
Exit; // showValue="0" on the rule hides the number
end;
finally
Evaluator.Free;
end;
end;Como o Excel realmente arredonda uma regra Top 10 por cento?
Ele arredonda para baixo, com um mínimo de um, e inclui empates no corte. Isso não está escrito em lugar nenhum na ISO 29500-1 — foi fixado sondando o Excel 16 com pastas de trabalho construídas à mão e lendo de volta quais células a aplicação destacava. O HotXLS implementa exatamente isso: a contagem de posição é Floor(Count * Min(Rank, 100) / 100), elevada a 1 quando cai em zero, limitada à contagem preenchida, e o valor de corte então é comparado com >= para que toda célula igual ao limite seja destacada mesmo quando isso ultrapasse a contagem solicitada. Vinte e sete valores e uma regra de 10 por cento destacam duas células, mais quaisquer células adicionais empatadas com a segunda
Regras de acima da média esconderam uma segunda ambiguidade: aboveAverage com stdDev="1" seleciona células um desvio padrão acima da média, mas desvio amostral e populacional diferem pela correção de Bessel e discordam visivelmente em intervalos pequenos, que é exatamente onde a formatação condicional é usada. O Excel 16 usa o desvio populacional, e o HotXLS o corresponde, com o sinalizador equalAverage tornando a comparação estrita inclusiva apenas quando nenhuma banda de desvio está em jogo. Regras de duplicata e únicas ativam identidade de chave. Se uma célula contém o número 100 e outra contém o texto "100", o Excel os trata como a mesma chave duplicada, então o HotXLS normaliza texto numérico no espaço de chave numérica em vez de comparar strings brutas. Células vazias são o caso espelho: uma célula verdadeiramente vazia participa da contagem do intervalo mas não é estilizada em si, então as células vazias em uma coluna não acendem todas como duplicatas umas das outras
Escalas de cor e conjuntos de ícones: interpolação e regras de limite
As famílias visuais resolvem para números prontos para renderização em vez de booleanos, e seu comportamento de borda foi fixado da mesma forma. Para uma escala de cor com limiares numéricos explícitos, o HotXLS limita a fração de posição ao intervalo fechado zero a um, depois interpola por canal com truncamento em vez de arredondamento — um valor abaixo do limiar mínimo recebe a cor mínima em vez de uma extrapolada, uma escala de três pontos escolhe seu par comparando com o limiar do meio, e uma escala degenerada cujas duas pontas carregam o mesmo limiar colapsa para a cor superior em vez de dividir por zero. Conjuntos de ícones precisaram do tipo oposto de cuidado, porque cada cfvo depois do primeiro carrega sua própria rigidez de comparação: o HotXLS lê ThresholdEqualsInclude por limiar e aplica >= ou > conforme apropriado, subindo para que o limiar mais alto satisfeito vença o índice do ícone. Um conjunto invertido inverte o índice resolvido em vez dos limiares, sobreposições por ícone podem puxar um glifo de uma família diferente, e qualquer limiar inválido aborta a regra em vez de produzir um ícone errado mas de aparência plausível
Alimentando uma grade, uma exportação HTML e um PDF a partir de um resultado
Como EvaluateCell retorna um TXLSXCfCellResult totalmente resolvido — preenchimento diferencial e cor de fonte com o tom de tema já aplicado, negrito, itálico, sublinhado, id de formato numérico, extensões direcionais positiva e negativa de barra, posição do eixo, família e índice de ícone — todo consumidor lê o mesmo registro e nenhum deles precisa entender os internos das regras. O HotXLS usa esse único caminho para exportação HTML, exportação PDF e o visualizador interativo, que é a única forma prática de impedir que três renderizadores se distanciem. A versão 2.210.0 o conectou ao TXLSWorkbookViewer, que armazena em cache um avaliador preparado por planilha ativa e o reutiliza durante rolagem, seleção e repintura, liberando-o quando a pasta de trabalho ou planilha muda — reconstruir o instantâneo em toda Paint derrotaria todo o design em tempo de construção. Esse cache também é o motivo pelo qual TXLSWorkbookViewer.RefreshConditionalFormats existe: o instantâneo é imutável, então se você alterar a pasta de trabalho anexada no local, as estatísticas agregadas e os limiares resolvidos ficam obsoletos até que você o chame
// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200; // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats; // drop evaluator, repaintO que o avaliador não fará por você
Três limites merecem ser ditos claramente. O clássico TCondFormatRule.Evaluate de célula única e o TXLSXConditionalFormatEvaluator em nível de planilha são superfícies diferentes com capacidades diferentes, e o de célula única deliberadamente recusa as famílias agregadas e visuais em vez de aproximá-las — se você precisa de Top/Bottom ou uma escala de cor, construa o avaliador. Períodos de data relativos dependem do relógio da máquina no momento da avaliação, então uma regra timePeriod renderiza diferente em um PDF gerado hoje e em um gerado na próxima semana, o que é comportamento correto e ainda assim um chamado de suporte esperando para acontecer se seu arquivo é esperado para ser estável byte a byte. O terceiro é gramatical em vez de técnico: a gramática de fórmula de formatação condicional proíbe referências de tabela estruturada, então uma regra não pode endereçar uma coluna de tabela pelo nome da forma que uma fórmula de planilha pode, e essa é uma restrição do formato em vez da implementação
Se você está construindo saída de relatório, um pipeline de exportação ou uma grade personalizada que precisa concordar com o Excel célula por célula, o mesmo resultado resolvido também conduz a grade VCL de planilha personalizada descrita em outro lugar neste blog. A documentação completa da API, o modelo de regras e downloads de avaliação para o componente de planilha HotXLS para Delphi estão disponíveis na página do produto