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

Uma regra Top 10 por cento sobre uma coluna de 27 números de vendas, e o Excel pinta três células. Dez por cento de 27 é 2,7, então um renderizador que arredonda dá três, um que pega o teto dá três e um que arredonda para baixo dá dois. Dois desses três chutes acertam nesta coluna, e ambos estão errados: o Excel arredonda a contagem para baixo, fica com dois e mesmo assim pinta a terceira célula, porque ela empata com a segunda em 88. Mude um valor e os chutes sortudos param de acertar

Essa é toda a dificuldade de desenhar formatação condicional fora do Excel. As regras estão no arquivo; a resposta para "quais células acendem, e em qual cor" não está. Se a sua saída é um relatório HTML, um PDF ou um grid que você mesmo pinta, você precisa calcular, e as famílias de regras agregadas são onde a aritmética se esconde. Criar regras é outro assunto, coberto em formatação condicional e estilos de rich text, e o que acontece com o intervalo de uma regra quando linhas se movem está coberto em partição de formatos condicionais ancorados. Esta nota é sobre avaliação

O que o elemento conditionalFormatting diz e não diz

ECMA-376 e ISO 29500-1 definem armazenamento, e param aí. Um elemento conditionalFormatting (§18.3.1.18) carrega um sqref e uma lista de filhos cfRule (§18.3.1.10). Cada regra tem um type, um operator opcional, uma priority, uma flag stopIfTrue, um ou dois filhos formula e, para as famílias visuais, um conjunto de limites cfvo. Tudo isso registra com fidelidade o que o usuário clicou no diálogo. Nada disso é um algoritmo

Para muitos tipos de regra a lacuna é inofensiva. cellIs com operator="greaterThan" significa maior que, e containsText significa que a substring está presente. O problema começa nas regras cujo veredito depende do resto do intervalo: top10 com percent="1" não diz como uma contagem fracionária arredonda, aboveAverage com stdDev="1" não diz se o desvio é amostral ou populacional, e nenhum dos dois diz se um valor igual ao corte se qualifica. Escolha errado e o seu PDF discorda da pasta de trabalho que o cliente tem aberta na janela ao lado

TCondFormatRule.Evaluate: o que uma célula decide sozinha

O HotXLS tem duas superfícies de avaliação, e elas diferem no que estão dispostas a responder. O motor clássico (.xls) expõe o TCondFormatRule.Evaluate em lxCondFormat.pas, que recebe uma calculadora, um índice de planilha, uma posição de célula e o valor da célula, e retorna um Boolean para uma regra. Ele cobre os oito operadores cellIs (between, not between, equal, not equal, greater, less, greater or equal, less or equal), regras expression de forma livre avaliadas na célula, de modo que referências relativas rebasem corretamente, os quatro predicados de texto e os predicados de vazio e de erro. Fórmulas de limite são resolvidas na posição da célula, e uma regra between cujos limites chegam invertidos os troca em vez de rejeitar. Comparações de texto ignoram maiúsculas e minúsculas, como no Excel

// Motor clássico: uma regra, uma célula, um Boolean
function AnyRuleMatches(Calculator: TXLSCalculator; CondFormat: TCondFormat;
  SheetIndex, Row, Col: Integer; const Value: Variant): Boolean;
var
  I: Integer;
begin
  Result := False;
  for I := 0 to CondFormat.RuleCount - 1 do
    // top10, médias, duplicados, períodos de tempo e os tipos visuais
    // respondem False aqui; eles precisam do intervalo inteiro
    if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      Exit(True);
end;

O método retorna False para top10, aboveAverage, belowAverage, duplicateValues e uniqueValues. Uma célula não pode saber se está nos dez por cento do topo sem ver os outros valores, então responder significaria chutar. Regras de data relativa (timePeriod) também respondem False nesse caminho. As quatro famílias visuais, dataBar, colorScale2, colorScale3 e iconSet, respondem False por outro motivo: elas nunca produzem um sim ou não, produzem uma cor, um comprimento de barra ou um índice de ícone, e um Boolean não tem onde guardar isso. Um False dessa chamada significa "não decidido aqui" tantas vezes quanto significa "não corresponde", o que serve para uma checagem rápida em regras de comparação e não é seguro para nada que você entrega

Como um avaliador de planilha evita reescanear a planilha a cada célula?

Ele faz todo o trabalho de intervalo uma vez, na criação. O TXLSXConditionalFormatEvaluator em lxHandleX.pas é um snapshot imutável dos formatos condicionais de uma planilha, obtido de TXLSXWorksheet.CreateConditionalFormatEvaluator; quem chama o possui e o libera. A alternativa ingênua, em que cada célula pintada dispara uma varredura do intervalo da regra para recalcular uma média ou um ranking, transforma uma exportação de 10.000 células em 10.000 varreduras de intervalo por regra

Na construção o avaliador interpreta cada sqref distinto uma vez, então dez regras sobre o mesmo intervalo compartilham um parse e uma passada pelas células preenchidas. Essa passada coleta média, desvio populacional, mínimo e máximo em um único passeio, e guarda uma cópia ordenada dos números apenas quando uma regra Top/Bottom ou um limite de percentil realmente precisa de estatísticas de ordem. Regras de duplicado e único constroem e ordenam as chaves de valor uma vez, então cada consulta depois é uma busca binária. Fórmulas de regra são compiladas uma vez e reavaliadas por célula com o offset da célula aplicado, então =A1>AVERAGE($A$1:$A$100) mantém as partes relativa e absoluta se comportando exatamente como no Excel sem ser reinterpretada a cada linha. As linhas são agrupadas para que o EvaluateCell visite apenas regras cujos intervalos alcançam a linha pedida

As regras são então aplicadas em ordem de priority, número menor primeiro, com empates decididos pela posição no arquivo. Cada propriedade de formatação vem da primeira regra correspondente que a define, e regras de prioridade menor só preenchem propriedades ainda vazias: uma regra de prioridade 1 que define negrito e uma regra de prioridade 2 que define um preenchimento vermelho aparecem as duas, e a célula sai negrito sobre vermelho. Uma correspondência em uma regra com StopIfTrue encerra o passeio daquela célula

procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
  FirstRow, LastRow, FirstCol, LastCol: Integer);
var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
  Row, Col: Integer;
begin
  // Um snapshot por passada de renderização; linhas e colunas são base 1
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    for Row := FirstRow to LastRow do
      for Col := FirstCol to LastCol do
      begin
        if not Evaluator.EvaluateCell(Row, Col, Res) then
          Continue;
        if Res.HasFillColor then
          Canvas.Brush.Color := TColor(Res.FillColor and $00FFFFFF);  // BGR, mesmo layout do TColor
        if Res.HasDataBar then  // DrawBar é rotina sua
          DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
        if Res.HasIcon then     // DrawIcon é rotina sua
          DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
        if Res.ShowCellValue and Res.HasNumberFormat and
           Sheet.Cells.HasCell(Row, Col) then  // DrawCellText é rotina sua
          DrawCellText(Row, Col,
            Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
      end;
  finally
    Evaluator.Free;
  end;
end;

O snapshot é construído uma vez por passada de propósito. Criá-lo dentro do loop interno compila e escaneia tudo de novo a cada célula, que é exatamente o custo que o design existe para evitar. O ShowCellValue volta False quando uma regra de barra de dados tem showValue="0" ou um conjunto de ícones está configurado para mostrar apenas o ícone, então o renderizador desenha a barra ou o glifo e deixa o número de fora

Quantas células uma regra Top 10 por cento realmente destaca?

A contagem é arredondada para baixo, nunca fica abaixo de um, e todo valor empatado com o corte é incluído. A especificação não diz isso; é o que o Excel faz, e você confirma em um minuto digitando os 27 valores da figura em uma coluna, aplicando Top 10% e contando as células pintadas. O TXLSXConditionalFormatEvaluator calcula a contagem de ranking como Floor(Count * Min(Rank, 100) / 100), levanta para 1 quando cai em zero, limita ao número de células numéricas preenchidas, lê o corte nos valores ordenados e então compara cada célula com >=. Para a coluna de 27 valores isso significa contagem de dois, corte em 88 e três células pintadas, porque duas delas guardam 88. Regras Bottom-N espelham isso com <=

Regras de acima da média carregam a segunda ambiguidade. Com stdDev="1" a regra seleciona células a pelo menos um desvio padrão acima da média, e o desvio amostral e o populacional diferem pela correção de Bessel, o que mais importa nos intervalos curtos onde as pessoas realmente usam essa regra. Em cinco valores os dois diferem cerca de 12 por cento. O HotXLS usa o desvio populacional, o mesmo número que o STDEV.P retorna, então um jeito rápido de conferir qualquer renderizador é colocar =AVERAGE(r)+STDEV.P(r) ao lado do intervalo e ver quais células o ultrapassam. Comparações contra uma faixa de desvio são inclusivas; a comparação simples de acima ou abaixo é estrita, a menos que a regra carregue equalAverage="1"

Regras de duplicado e único dependem do que conta como "o mesmo valor". O número 100 e o texto "100" compartilham uma chave, porque texto numérico é normalizado para o espaço de chaves numérico; chaves de texto comparam sem diferenciar maiúsculas e minúsculas; TRUE e 1 permanecem distintos, assim como valores de erro de tipos diferentes. Texto contendo * ou ? casa como padrão com curinga, estilo COUNTIF, então uma célula com Q* conta contra todo valor que começa com Q. Células vazias são a armadilha oposta: uma célula verdadeiramente vazia dentro do intervalo nunca é pintada como duplicada, então uma coluna inacabada não acende de cima a baixo, enquanto uma célula que armazena uma string vazia é contada junto dos vazios

Escalas de cores, barras de dados e conjuntos de ícones voltam como números

As famílias visuais se resolvem em valores prontos para renderizar, e as bordas deles são onde renderizadores escritos à mão divergem. Para uma escala de cores, o EvaluateCell prende a posição ao intervalo de 0 a 1 e interpola cada canal RGB com truncamento, então um valor abaixo da parada mínima recebe a cor mínima em vez de uma extrapolada. Uma escala de três paradas usa o par meio-máximo para valores acima do ponto médio e o par mínimo-meio caso contrário. Uma escala cuja primeira e última paradas resolvem para o mesmo número retorna a última cor em vez de dividir por zero. Limites de percentil são interpolados linearmente entre valores ordenados vizinhos, a definição do PERCENTILE.INC

Barras de dados são normalizadas à largura da célula. Para um intervalo todo positivo, o DataBarAxis é 0 e o DataBarEnd corre entre o comprimento mínimo e máximo de barra da regra, que por padrão são 10 e 90 por cento, então o menor valor ainda ganha uma barra curta e o maior nunca enche a célula de todo. Quando o intervalo cruza o zero, o eixo fica na posição proporcional e o DataBarEnd marca onde a barra para, do lado oposto do eixo para valores negativos. Direção, gradiente, borda e cores de barra negativa vêm preenchidos no mesmo registro, com cores de tema e tint já resolvidos

Conjuntos de ícones pedem cuidado com a estriteza de cada limite, porque todo cfvo depois do primeiro carrega o próprio flag gte. O avaliador caminha pelos limites para cima, aplicando >= ou > por limite conforme o ThresholdEqualsInclude diz, e o limite mais alto que o valor ultrapassa vence. Um conjunto invertido inverte o índice resolvido, sobrescritas por ícone podem substituir um glifo de outra família via IconSetType, e um limite que não se resolve interrompe a regra para aquela célula, então a célula fica sem ícone nenhum em vez de ganhar um errado plausível

Um resultado para o grid, o export HTML e o PDF

O TXLSXCfCellResult carrega tudo que um renderizador precisa: cor de preenchimento e de fonte com tint de tema aplicado, negrito, itálico, sublinhado, tachado, um id de formato numérico, a geometria e as cores da barra de dados, e a família e o índice do ícone. O HotXLS alimenta o mesmo registro ao export HTML, ao export PDF e ao TXLSWorkbookViewer, o que mantém as três saídas de acordo célula a célula. O viewer cria um avaliador para a planilha ativa na primeira pintura e o reutiliza através de rolagem, seleção e repintura, descartando-o quando você troca de planilha ou atribui outra pasta de trabalho

// Editando atrás de um viewer vivo: o snapshot em cache não enxerga a mudança
Sheet := Viewer.XlsxWorkbook.Sheets[1];   // índice de planilha base 1
Sheet.Cells[5, 2].Value := 4200;          // move a média, o máximo e o ranking
Viewer.RefreshConditionalFormats;         // descarta o snapshot e repinta

Como o snapshot é imutável, uma edição in place deixa médias, rankings e extremos da escala de cores obsoletos até o RefreshConditionalFormats rodar. O mesmo vale para o seu próprio código: um avaliador que você mantém através de edições precisa ser liberado e recriado, já que o CreateConditionalFormatEvaluator captura os valores no momento em que é chamado

Regras de data relativa pedem uma decisão no começo. O avaliador de planilha compara regras timePeriod contra a data atual da máquina, então uma regra "esta semana" renderiza de um jeito em um PDF produzido hoje e de outro quando a mesma pasta de trabalho for exportada na terça que vem. Esse é o comportamento correto e ainda assim vira um ticket de suporte se o seu arquivo morto precisa ser reproduzível. Para relatórios arquivados, troque as regras timePeriod por uma regra expression que compara contra uma data de relatório guardada em uma célula, como =AND(A2>=$Z$1-6,A2<=$Z$1) para uma janela de sete dias; a saída então depende da pasta de trabalho e de nada mais. O Excel também recusa referências estruturadas de tabela dentro de uma fórmula de formato condicional, então aponte a regra para o intervalo de células da coluna

Se o alvo é uma tela em vez de um arquivo, o mesmo registro de resultado também dirige o grid de planilha VCL personalizado descrito em outro lugar neste blog. O modelo de regras, a referência da API do avaliador e builds de teste estão na página do componente de planilha Delphi HotXLS