Avaliação de Formatação Condicional Excel em Delphi com HotXLS

O HotXLS é um componente de folha de cálculo nativo para Delphi e C++Builder, e desde a versão 2.209.0 consegue responder à pergunta que o Excel normalmente guarda só para si: para esta célula exata, que regras de formatação condicional disparam, e a que preenchimento, tipo de letra, barra de dados ou ícone se resolvem. Essa resposta é o que precisa no momento em que o seu output é um relatório HTML, um PDF, ou uma grelha que pinta você mesmo

Este é um problema diferente de criar regras. Duas notas anteriores cobrem o lado da autoria: formatação condicional e estilos de texto formatado trata de anexar regras e formatos diferenciais a um intervalo, e particionamento de formatos condicionais ancorados trata do que acontece a um intervalo de regra quando linhas e colunas são inseridas ou eliminadas. Ambas são estruturais. Esta é sobre semântica: dado um livro de trabalho que já transporta regras, calcular o realce

Por que não lhe diz o formato do ficheiro que células se 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) transporta um sqref e uma lista de filhos cfRule (§18.3.1.10), e cada regra transporta um type, um operator opcional, uma priority, uma flag 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 utilizador 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 subcadeia está presente. A lacuna abre-se nas famílias agregadas. Uma regra top10 com rank="10" e percent="1" sobre 27 células numéricas preenchidas realça quantas células? Dois vírgula sete não é um número. Arredondar, floor, ou ceiling — a especificação é omissa, e escolher errado significa que o seu PDF discorda do livro de trabalho que o cliente tem aberto ao lado

Regras de célula única e onde para TCondFormatRule.Evaluate

O HotXLS pegou primeiro na metade barata. TCondFormatRule.Evaluate em lxCondFormat.pas, acrescentado na 2.199.0, responde se uma regra dispara para uma célula sem saber nada sobre o resto do intervalo. Trata os oito operadores de comparação BIFF por detrás de cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), regras expression livres avaliadas na célula para que as referências relativas rebasem corretamente, os quatro predicados de texto, e os predicados de vazios e erros. 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 é aquilo que se recusa a adivinhar. top10, aboveAverage, belowAverage, duplicateValues e uniqueValues devolvem False, não porque sejam difíceis mas porque são indecidíveis a partir de uma célula — cada uma delas precisa de uma estatística sobre todo o domínio. As quatro famílias visuais, dataBar, colorScale2, colorScale3 e iconSet, devolvem False por uma razão diferente: nunca produzem um booleano de todo, produzem uma carga de renderização, e um tipo de retorno Boolean é a forma errada para elas

Como evita um avaliador ao nível da folha reanalisar a folha?

Calculando cada quantidade partilhada uma vez, na construção, e nunca mais. TXLSXConditionalFormatEvaluator em lxHandleX.pas é um instantâneo imutável para uma folha de cálculo, construído através de TXLSXWorksheet.CreateConditionalFormatEvaluator, e todo o seu desenho é uma defesa contra a implementação ingénua onde cada célula pintada dispara uma análise completa do intervalo

Quatro coisas acontecem no construtor. Cada sqref multi-área distinto é analisado exatamente uma vez para um TXlsxCfRangeSnapshot, pelo que dez regras partilhando um intervalo partilham uma análise e uma passagem de estatísticas. Essa passagem calcula em fluxo a média, o desvio populacional, o mínimo e o máximo sobre as células preenchidas numa única passagem, e só retém um array numérico ordenado quando uma regra Top/Bottom ou de percentil realmente precisa de estatísticas de ordem. As chaves de duplicados e únicos são construídas de forma segura para Unicode e ordenadas em lote uma vez em vez de por pesquisa. Depois, o eixo das linhas é cortado em bandas em cada limite de área, pelo que EvaluateCell faz uma pesquisa binária numa banda e só visita regras cujos intervalos possam alcançar essa 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 compila-a uma vez e reavalia a mesma árvore através de deslocamentos de coordenadas reversíveis, o que preserva o comportamento de âncora do Excel sem uma alocação de árvore de sintaxe por célula. As regras são depois estratificadas por priority, e uma correspondência numa regra cujo StopIfTrue está definido interrompe o ciclo, 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 arredonda o Excel realmente uma regra Top 10 por cento?

Faz floor, com um mínimo de um, e inclui empates no corte. Isso não está escrito em lado nenhum na ISO 29500-1 — foi fixado ao sondar o Excel 16 com livros de trabalho construídos à mão e ao ler quais células a aplicação realçava. O HotXLS implementa exatamente isso: a contagem de posições é Floor(Count * Min(Rank, 100) / 100), elevada a 1 quando fica em zero, limitada à contagem preenchida, e o valor de corte é depois comparado com >= pelo que cada célula igual ao limite é realçada mesmo quando isso ultrapassa a contagem pedida. Vinte e sete valores e uma regra de 10 por cento realçam duas células, mais quaisquer células adicionais empatadas com a segunda

As regras acima da média escondiam uma segunda ambiguidade: aboveAverage com stdDev="1" seleciona células um desvio-padrão acima da média, mas o desvio de amostra e o desvio 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 corresponde-lhe, com a flag equalAverage a tornar a comparação estrita inclusiva apenas quando nenhuma banda de desvio está em jogo. As regras de duplicados e únicos ligaram-se antes à identidade da chave. Se uma célula contém o número 100 e outra contém o texto "100", o Excel trata-os como a mesma chave de duplicado, pelo que o HotXLS normaliza texto numérico para o espaço de chave numérico em vez de comparar strings em bruto. As células vazias são o caso espelho: uma célula verdadeiramente vazia participa na contagem do intervalo mas não é ela própria formatada, pelo que as células vazias numa coluna não se acendem todas como duplicadas umas das outras

Escalas de cor e conjuntos de ícones: interpolação e regras de limite

As famílias visuais resolvem-se para números prontos a renderizar em vez de booleanos, e o seu comportamento de limite 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 mínimo obtém a cor mínima em vez de uma extrapolada, uma escala de três paragens escolhe o seu par comparando com a paragem intermédia, e uma escala degenerada cujas duas extremidades transportam o mesmo limiar colapsa para a cor superior em vez de dividir por zero. Os conjuntos de ícones precisaram do cuidado oposto, porque cada cfvo depois do primeiro transporta a sua própria rigidez de comparação: o HotXLS lê ThresholdEqualsInclude por limiar e aplica >= ou > em conformidade, subindo para que o limiar mais alto satisfeito ganhe o índice do ícone. Um conjunto invertido inverte o índice resolvido em vez dos limiares, as substituições por ícone podem retirar um glifo de uma família diferente, e qualquer limiar inválido aborta a regra em vez de produzir um ícone erradamente plausível

Alimentar uma grelha, uma exportação HTML e um PDF a partir de um resultado

Porque EvaluateCell devolve um TXLSXCfCellResult totalmente resolvido — cor de preenchimento e tipo de letra diferenciais com tinta de tema já aplicada, negrito, itálico, sublinhado, id de formato numérico, extensões de barra positiva e negativa direcionais, posição do eixo, família e índice de ícone — cada consumidor lê o mesmo registo e nenhum deles precisa de entender os internos das regras. O HotXLS usa esse único percurso para exportação HTML, exportação PDF e o visualizador interativo, que é a única forma prática de impedir que três renderizadores se afastem uns dos outros. A versão 2.210.0 ligou-o a TXLSWorkbookViewer, que armazena em cache um avaliador preparado por folha de cálculo ativa e reutiliza-o entre deslocamentos, seleção e repintura, libertando-o quando o livro de trabalho ou a folha muda — reconstruir o instantâneo a cada Paint derrotaria todo o desenho em tempo de construção. Essa cache é também a razão pela qual TXLSWorkbookViewer.RefreshConditionalFormats existe: o instantâneo é imutável, pelo que se mutar o livro de trabalho anexado no lugar, as estatísticas agregadas e os limiares resolvidos ficam obsoletos até o chamar

// 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, repaint

O que o avaliador não faz por si

Três limites valem a pena ser ditos com clareza. O clássico TCondFormatRule.Evaluate de célula única e o TXLSXConditionalFormatEvaluator ao nível da folha são superfícies diferentes com capacidades diferentes, e o de célula única declina deliberadamente as famílias agregadas e visuais em vez de as aproximar — se precisar de Top/Bottom ou de uma escala de cor, construa o avaliador. Os períodos de data relativos dependem do relógio da máquina no momento da avaliação, pelo que uma regra timePeriod renderiza de forma diferente num PDF gerado hoje e num gerado na próxima semana, o que é comportamento correto e ainda assim um ticket de suporte à espera de acontecer se o seu arquivo é suposto ser estável byte a byte. O terceiro é gramatical em vez de técnico: a gramática de fórmula do formato condicional proíbe referências a tabelas estruturadas, pelo que uma regra não consegue endereçar uma coluna de tabela pelo nome da forma que uma fórmula de folha de cálculo consegue, e isso é uma restrição do formato em vez da implementação

Se estiver a construir output de relatório, um pipeline de exportação ou uma grelha personalizada que tem de concordar com o Excel célula a célula, o mesmo resultado resolvido conduz também a a grelha de folha de cálculo VCL personalizada descrita noutro artigo deste blog. A documentação completa da API, o modelo de regras e transferências de avaliação para o componente de folha de cálculo Delphi HotXLS estão disponíveis na página do produto