Formatação Condicional, Rich Text e Estilos de Célula em Delphi com o HotXLS

Uma regra de formatação condicional em OOXML é composta por dois elementos independentes sob o mesmo nome: a condição (a comparação, fórmula ou correspondência de texto) determina as células visadas; e o aspeto visual (um registo de formato diferencial, ou dxf nos termos da especificação ECMA-376) define a estética dessas células. O assistente do Excel esconde esta separação ao exigir a configuração conjunta de ambos os elementos. O HotXLS, contudo, não o faz: se criar uma regra cellIs em Delphi e omitir o estilo, a regra será válida, o intervalo estará correto e a fórmula será avaliada como verdadeira nas células indicadas, mas nenhuma mudará de cor porque a regra apenas dita "se verdadeiro, não aplique formatação". Compreender esta separação entre a condição e o efeito visual é o primeiro passo para evitar regras que parecem corretas no gestor de regras mas não aplicam qualquer destaque

O HotXLS escreve formatação condicional de forma nativa em ficheiros BIFF8 .xls e OOXML .xlsx, aplicando o mesmo suporte a blocos de rich text e a um modelo consolidado de estilos de células. Estas três funcionalidades partilham mais ligações estruturais do que a API simplificada sugere, e as falhas entre o comportamento programado e o resultado obtido residem frequentemente nos pontos de contacto entre elas

Uma condição necessita de um efeito: o estilo dxf

Na folha de cálculo XLSX, as regras de comparação são criadas com o método AddConditionalFormat, que recebe o intervalo, um operador de TXLSXCfOperator e a fórmula ou literal, devolvendo o índice da nova regra na coleção ConditionalFormats da folha. O objeto da regra nesse índice expõe a propriedade Style, e é aí que reside a formatação de destaque. Associe-lhe um preenchimento para que as células visadas o adotem; omitir este passo resulta na regra invisível descrita acima

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Idx: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('kpi.xlsx');
    Sheet := Book.Sheets[0];

    // Variação negativa: preenchimento vermelho claro
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Os IDs de encomenda duplicados são sinalizados da mesma forma
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Regra de fórmula personalizada: destacar linhas onde o valor real falhe 90% do objetivo
    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    Book.SaveAs('kpi-flagged.xlsx');
  finally
    Book.Free;
  end;
end;

As cores neste formato são valores ARGB de 32 bits; assim, $FFFFC7CE corresponde ao "vermelho claro" padrão do Excel, contendo um byte alfa totalmente opaco posicionado antes do RGB. Cada tipo de regra ativado por uma condição por célula segue a mesma lógica de criação seguida de estilização. Os métodos de comparação de texto (AddCondFormatContainsText, AddCondFormatBeginsWith e AddCondFormatEndsWith) devolvem um índice que pode estilizar posteriormente, tal como os métodos AddCondFormatTop10, AddCondFormatAboveAverage e os detetores de células vazias e erros. Uma vez compreendido este padrão, toda a família de regras de comparação de texto e valores segue a mesma lógica

Barras de dados, escalas de cores e conjuntos de ícones têm estilo próprio

As regras de teor exclusivamente visual funcionam ao contrário: estas incluem a formatação na própria definição da regra e ignoram por completou a propriedade Style. Associar um preenchimento a uma regra de barra de dados não produz efeitos (o que pode parecer uma falha até compreender a hierarquia do motor): o método AddCondFormatDataBar recebe a cor da barra como parâmetro direto; as escalas de duas e três cores recebem as respetivas cores limite de igual modo; e AddCondFormatIconSet seleciona um dos 26 conjuntos de ícones disponíveis (como icsTrafficLights3). Não há registos de estilo independentes neste cenário porque toda a formatação é integrada

Os limites de valores (âncoras), tipados como TXLSCfValueKind, merecem atenção especial. O limite de uma barra ou escala pode ser associado ao mínimo ou máximo do intervalo, a um número fixo, a uma percentagem ou percentil, ou ao resultado de uma fórmula. Os valores padrão (mínimo e máximo do intervalo) funcionam bem com dados de teste, mas falham com dados reais contendo desvios acentuados (outliers): um único valor desproporcionado estende a escala e reduz as restantes barras a pequenos segmentos. Se o painel de bordo (dashboard) for lido ao longo de vários períodos, vincule os limites a números fixos ou percentis para que metade de uma barra em março represente a mesma proporção que metade de uma barra em abril. Uma barra com dimensionamento automático apenas é comparável consigo própria

O gerador XLS suporta apenas quatro tipos de regras

A interface BIFF8 legada não é uma miniatura da estrutura XLSX, mas sim um subconjunto específico. A faceta XLS consegue criar exatamente quatro tipos de regras condicionais: barras de dados, escalas de duas cores, escalas de três cores e conjuntos de ícones, gravados no fluxo como registos CF12. Não dispõe de APIs para criar regras do tipo cellIs, expressões ou comparações de texto. Contudo, regras deste tipo já existentes em ficheiros abertos são lidas, mantidas e gravadas sem alterações; como tal, abrir e gravar um ficheiro .xls não corrompe a formatação existente. O que não é possível é gerar novas regras de destaque a partir do código num ficheiro .xls. As alternativas consistem em simular este comportamento aplicando preenchimentos de células comuns calculados em Pascal, ou optar pelo formato .xlsx, onde todos os tipos de regras estão disponíveis

Esta limitação deve ser avaliada antes de desenhar a camada de dados, pois afeta a escolha do formato de ficheiro em relatórios do tipo painel. Escolher o formato .xls por compatibilidade e projetar um relatório de KPIs com regras cellIs constitui uma incompatibilidade; o momento ideal para identificar este detalhe é na fase de especificação do formato e não a meio do desenvolvimento

Sobreposição de regras, prioridade e intervalos coincidentes

Painéis reais raramente utilizam uma única regra por intervalo. Uma coluna de variação pode conter uma barra de dados para escala visual, uma regra cellIs para limites críticos e uma expressão ao nível da linha para situações de escalamento. Cada objeto TXLSXConditionalFormat disponibiliza a propriedade Priority, e o Excel avalia regras concorrentes por ordem de prioridade. Se duas regras se aplicarem à mesma célula, o resultado visual é ditado pelo valor numérico de prioridade que definiu, e não pela ordem apresentada no gestor de regras

Gira a prioridade da mesma forma que um programa de desenho gere a ordem sobreposta (z-order). Atribua este valor deliberadamente sempre que duas regras se possam aplicar às mesmas células, deixando intervalos numéricos livres para que novas regras possam ser inseridas sem necessidade de reordenar as existentes. Onde não há risco de colisão (por exemplo, uma barra de dados na coluna E e uma regra de texto na coluna G), a ordem de criação padrão é suficiente. Concentre a atenção nos limites dos intervalos: os erros graves raramente decorrem de prioridades incorretas, mas sim de limites fixos (como configurar B2:B200 num relatório que passou a ter 350 linhas, no qual o bloco final exibe células planas sem formatação). Associe os intervalos ao número total de linhas geradas — tal como faz com séries de gráficos e intervalos de validação — e evitará lacunas de formatação no final das tabelas

Um hábito simples de validação revela-se útil: após a geração do ficheiro, abra-o no Excel, selecione o intervalo formatado e reveja o gestor de regras a cada alteração de modelo. A formatação condicional é das poucas áreas onde a única aplicação com capacidade de validação real é o próprio leitor do documento; como tal, um teste unitário ao XML valida que a regra foi gravada, mas não que o Excel a exibe corretamente. Uma inspeção rápida no ecrã resolve esta dúvida

Rich text: múltiplos formatos numa única célula

Uma célula com formato rich text no modelo XLSX armazena uma lista de segmentos (runs), onde cada segmento é constituído por texto e atributos de tipo de letra próprios. Pode construir a lista declarando um objeto TXLSXRichText, adicionando-lhe segmentos e associando depois o objeto à célula. A gestão de propriedade do objeto requer cuidado: ao atribuir a propriedade Cell.RichText, a célula assume a posse do objeto e liberta-o na sua destruição. Se tentar libertar o objeto manualmente no seu código, provocará um erro de libertação dupla (double-free) que costuma permanecer silencioso no momento da chamada e causar falhas em áreas de memória não relacionadas mais tarde

var
  Rich: TXLSXRichText;
  Run: TXLSXRichTextRun;
begin
  Rich := TXLSXRichText.Create;
  Rich.AddRunText('Status: ');
  Run := Rich.AddRunText('OVERDUE');
  Run.Bold := True;
  Run.Color := $FFC00000;
  Run.ColorIsAuto := False;
  Run := Rich.AddRunText(' (escalated to regional manager)');
  Run.Italic := True;
  Sheet.Cells[2, 7].RichText := Rich;   // a propriedade do objeto é transferida para a célula: não utilizar Free
end;

A instrução ColorIsAuto := False é obrigatória e não meramente estética. Cada segmento possui um marcador de cor automática, e qualquer atribuição de cor apenas é aplicada após a limpeza deste marcador. Se definir Color e omitir ColorIsAuto, o segmento será exibido a negrito mas na cor preta padrão, sem gerar avisos de erro. Os segmentos suportam também estilos como rasurado, variações de sublinhado e alinhamento vertical para expoentes e índices (superscript e subscript), enquanto o método PlainText converte a lista numa string comum quando necessita de exportar ou comparar o texto

O suporte a rich text ao nível da célula é exclusivo do formato XLSX. A interface XLS não disponibiliza APIs públicas para a sua gravação, embora os segmentos estejam disponíveis em comentários e caixas de texto através da propriedade TextRuns, e strings ricas lidas de ficheiros .xls existentes sejam preservadas sem alterações. A lógica mantém-se idêntica à da formatação condicional: qualquer operação que misture formatações numa célula deve recorrer ao gerador XLSX

O pool de estilos e a armadilha do desfasamento de índice

A estilização simples de células no modelo XLSX recorre a coleções agrupadas (pools) no livro de cálculo. Os métodos Fonts.Add, Fills.AddSolid e Borders.Add registam a definição e devolvem o respetivo índice no pool. Estes índices baseiam-se em 0. Contudo, as propriedades da célula que os consomem (como FontIndex) reservam o valor 0 para designar "padrão"; como tal, o valor a atribuir à célula deve ser o índice do pool incrementado em uma unidade:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // índice do pool, baseado em 0
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // índice da célula, baseado em 1

Se omitir o + 1, todos os cabeçalhos assumirão o tipo de letra padrão. Não ocorrem exceções ou avisos, obtendo apenas um livro sem qualquer estilização visível. Outro erro comum é invocar o método Fonts.Add dentro do ciclo iterativo para cada linha: embora definições idênticas sejam consolidadas automaticamente (evitando corromper o ficheiro), a operação consome recursos desnecessários; além disso, o pool de alinhamentos devolve um objeto novo a cada chamada em vez de consolidar duplicados. Defina os estilos necessários uma vez fora do ciclo e reutilize os índices obtidos. Em relatórios de grandes dimensões, esta medida constitui uma das técnicas abordadas no artigo de otimização de desempenho com o HotXLS. Se necessitar apenas de estilos semânticos comuns, ambas as interfaces expõem o método ApplyBuiltinStyle em intervalos, mapeando os estilos padrão do Excel (como Good, Bad, Neutral e tons de destaque) sem necessidade de interagir com os pools

A formatação condicional, o rich text e os pools de estilos constituem a fase de acabamento do relatório, aplicados após a definição dos dados e da estrutura, aspetos abordados no artigo sobre geração de relatórios com modelos. A referência completa para regras, segmentos e estilos está disponível na página do HotXLS Component