Formatação Condicional e Rich Text do HotXLS no Delphi

Uma regra de formatação condicional no OOXML consiste em dois elementos distintos sob o mesmo nome. A condição (uma comparação, uma fórmula, uma correspondência de texto) determina as células elegíveis. A aparência (um registo de formato diferencial, ou dxf nos termos da norma ECMA-376) determina o aspeto dessas células. A caixa de diálogo do Excel esconde essa divisão ao obrigar a preencher ambos em simultâneo. O HotXLS não o faz. Se criar uma regra cellIs a partir do Delphi e ignorar o estilo, a regra será válida, o intervalo estará correto, a fórmula será avaliada como verdadeira precisamente nas células certas, mas nenhuma delas mudará de cor, pois a instrução da regra foi "se for verdade, não pinte nada". Esta separação entre a condição e a consequência é o primeiro aspeto a compreender, sendo a causa da maioria dos casos em que as regras parecem corretas na gestão de regras (Manage Rules) mas não aplicam qualquer destaque.

O HotXLS escreve formatação condicional nativamente tanto em ficheiros BIFF8 .xls como em OOXML .xlsx, fazendo o mesmo com trechos de rich text e com um modelo unificado de estilos de células. Estas três funcionalidades partilham mais ligações internas do que a API sugere à partida, e os pontos onde os resultados divergem do esperado coincidem normalmente com a ligação entre elas.

Uma condição necessita de uma consequência: o estilo dxf

Na folha de cálculo XLSX, as regras de comparação são geradas com AddConditionalFormat, que aceita um intervalo, um operador de TXLSXCfOperator e uma fórmula ou literal, devolvendo o índice da nova regra na coleção ConditionalFormats da folha. O objeto de regra nesse índice expõe a propriedade Style, e é aí que reside o destaque. Configure um preenchimento (fill) nesta propriedade e as células qualificadas adotarão esse preenchimento. Se a deixar inalterada, terá construído a 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];

    // Negative variance: light red fill
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Duplicate order IDs get flagged the same way
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Custom formula rule: highlight rows where actual misses 90% of target
    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 aqui são valores ARGB de 32 bits, pelo que $FFFFC7CE é o "vermelho claro" do Excel que conhece da caixa de diálogo, com um byte alfa totalmente opaco posicionado antes do RGB. Cada tipo de regra que atua numa condição por célula segue o mesmo padrão de criação seguida de estilização. Os correspondentes de texto (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) devolvem um índice que pode estilizar posteriormente, tal como acontece com AddCondFormatTop10, AddCondFormatAboveAverage e os detetores de erros e de células em branco. Compreendendo esta estrutura, toda a família de regras de texto e de comparação funciona de forma idêntica.

Barras de dados, escalas de cores e conjuntos de ícones pintam-se a si mesmos

As regras de natureza visual funcionam ao contrário. Estas contêm a sua própria aparência na definição da regra e ignoram totalmente a propriedade Style. Se atribuir um preenchimento a uma regra de barra de dados, nada acontece, o que parece um erro até se compreender a sua classificação: o AddCondFormatDataBar aceita a cor da barra como um argumento direto, as escalas de cores de dois e três pontos aceitam as suas cores limites da mesma forma, e o AddCondFormatIconSet seleciona um de 26 tipos de conjuntos de ícones, como icsTrafficLights3. Não existe um registo de estilo independente a configurar, porque não existe sequer um registo de estilo associado.

Os parâmetros a ter em conta nestas chamadas são as referências de valor, tipificadas como TXLSCfValueKind. O limite de uma barra ou de uma escala pode ser definido no mínimo ou máximo do intervalo, num número literal, numa percentagem ou percentil, ou no resultado de uma fórmula. As definições padrão, mínimo e máximo do intervalo, funcionam bem em demonstrações limpas, mas revelam falhas em dados reais com valores discrepantes (outliers): um valor excessivo esticará a escala e reduzirá as restantes barras a pequenos traços. Quando um painel se destina a ser comparado entre períodos, fixe os limites em números ou percentis predefinidos, para que metade de uma barra em março represente a mesma quantidade que metade de uma barra em abril. Uma barra com dimensionamento automático só se pode comparar com ela própria.

O gravador XLS cobre apenas quatro tipos de regras

O lado BIFF8 legado não é um espelho reduzido do lado XLSX; é um subconjunto deliberado. A fachada XLS pode criar exatamente quatro formatos de regras condicionais: barras de dados, escalas de duas cores, escalas de três cores e conjuntos de ícones, emitidos como registos CF12 no fluxo do ficheiro. Não possui uma API de criação para regras cellIs, de expressão ou de texto. As regras destes tipos que já existam num ficheiro que abra são lidas, mantidas e gravadas novamente sem alterações, pelo que abrir e voltar a gravar o ficheiro .xls de um cliente nunca danifica a formatação preexistente. O que não consegue fazer é gerar realces de limite do zero num ficheiro .xls. Nestes casos, as alternativas são simular os destaques com preenchimentos de células calculados por código ou definir a saída como .xlsx, onde toda a família de regras está disponível.

Esta é uma limitação que deve ser resolvida antes do desenvolvimento da camada de dados, pois condiciona o formato do ficheiro para qualquer relatório do tipo painel. Uma equipa que selecione .xls por motivos de compatibilidade e depois desenhe um relatório KPI com limites cellIs escolheu duas opções incompatíveis, sendo muito mais vantajoso aperceber-se disso no momento da decisão do formato do que três semanas após o início da implementação.

Regra empilhamento de regras, prioridade e intervalos sobrepostos

Os painéis de controlo raramente aplicam apenas uma regra por intervalo. Uma coluna de variação pode conter uma barra de dados para magnitude, uma regra cellIs para o limite crítico e uma regra de expressão ao nível da linha para escalamentos. Cada TXLSXConditionalFormat expõe um valor Priority, e o Excel processa as regras concorrentes na ordem de prioridade definida. Quando duas regras concorrem para pintar a mesma célula, a vencedora é decidida pelo número que configurou, e não pela ordem com que o revisor percorre a caixa de diálogo de Gestão de Regras.

Trate a prioridade da mesma forma que um programa de desenho trata a ordem z (z-order). Defina-a intencionalmente sempre que duas regras possam aplicar-se às mesmas células e deixe intervalos entre os valores para que uma regra posterior possa ser inserida sem ter de renumerar as restantes. Nos casos em que as regras não colidem (por exemplo, uma barra de dados limitada à coluna E e uma regra de texto na coluna G), a ordem de criação é suficiente e a prioridade não exige preocupações adicionais. Foque antes a sua atenção nos limites dos intervalos, pois as falhas mais críticas não costumam ser inversões de prioridade, mas sim intervalos como B2:B200 num relatório que cresceu para 350 linhas, fazendo com que as linhas adicionais sejam apresentadas como células sem qualquer formatação, simulando dados saudáveis. Defina o intervalo de cada regra com base no número final de linhas que rege as séries de gráficos e os intervalos de validação noutras secções do livro, evitando que o final dos dados fique sem formatação.

Um hábito de verificação útil consiste em abrir o ficheiro no Excel após a geração, selecionar o intervalo formatado e inspecionar a Gestão de Regras uma vez por cada alteração do modelo. A foi concebida de forma que a formatação condicional é uma das poucas áreas em que o único renderizador fidedigno é a própria aplicação que consome o ficheiro, pelo que um teste unitário sobre o XML comprova que a regra foi escrita, mas não que o Excel a apresenta da forma pretendida. Uma revisão visual rápida resolve essa lacuna.

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

Uma célula com rich text no modelo XLSX contém uma lista de trechos (runs), em que cada trecho é uma secção de texto com os seus próprios atributos de fonte. A lista é construída separadamente como um objeto TXLSXRichText, adicionando-lhe trechos antes de a atribuir à célula. A regra de propriedade (ownership) é o detalhe que requer cuidado. Atribuir o objeto a Cell.RichText transfere a propriedade do mesmo para a célula, e esta liberta-o automaticamente durante a sua própria destruição. Se o libertar também no seu código, provocará uma dupla libertação (double-free), o tipo de erro que não gera avisos imediatos mas resulta em falhas inesperadas de execução 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;   // ownership moves to the cell: do not Free
end;

A instrução explícita ColorIsAuto := False não é uma decoração opcional. Um trecho possui uma flag de cor automática, e a atribuição de cor só é respeitada quando esta flag é limpa. Se definir Color e esquecer a propriedade ColorIsAuto, o trecho será exibido a negrito mas teimosamente preto, sem qualquer erro que indique a causa. Os trechos também suportam rasurado (strikethrough), as variantes de sublinhado e alinhamento vertical para sobrescrito e subscrito, enquanto a propriedade PlainText converte toda a lista num texto simples único quando necessita de exportar ou comparar conteúdos textuais.

O rich text ao nível das células é exclusivo do formato XLSX. A fachada XLS não possui uma API pública para gravação do mesmo, embora os trechos estejam disponíveis em comentários e caixas de texto através de TextRuns, e as strings formatadas lidas de um ficheiro .xls existente sobrevivam sem alterações a uma conversão. A conclusão é a mesma que na formatação condicional: qualquer elemento que misture formatos dentro de uma célula deve ser gerido no gravador XLSX.

A pool de estilos e o erro "off-by-one" no envio

O estilo de células simples no modelo XLSX processa-se através de coleções partilhadas (pools) no livro. As funções Fonts.Add, Fills.AddSolid e Borders.Add registam cada definição e devolvem o seu índice correspondente na pool. Estes índices baseiam-se em 0. No entanto, as propriedades do lado da célula que os utilizam, como FontIndex, reservam o valor 0 para a opção "predefinida", pelo que o valor atribuído à célula deve ser o índice da pool mais um:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // pool index, 0-based
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // cell index, 1-based

Se omitir o + 1, todos os cabeçalhos assumirão a fonte predefinida. Não há exceções ou avisos, obtendo-se apenas um livro com o aspeto de não ter sido estilizado. O segundo erro comum reside no ciclo de repetição: chamar Fonts.Add uma vez por linha. Definições de fontes idênticas são simplificadas por eliminação de duplicados, pelo que o ficheiro não é corrompido, mas o esforço é desperdiçado, e a pool de alinhamentos em particular devolve um novo objeto a cada chamada em vez de reutilizar duplicados. Defina os poucos estilos de que necessita uma vez antes do ciclo e reutilize os seus índices. Em relatórios de cem mil linhas, essa alteração simples constitui um dos fatores detalhados no artigo otimização de desempenho de livros de grandes dimensões no HotXLS. Quando necessita apenas de um aspeto semântico padrão, ambas as fachadas expõem a função ApplyBuiltinStyle em intervalos, mapeando os estilos predefinidos do Excel como Bom, Mau, Neutro e realces sem necessidade de aceder às pools de estilos.

A formatação condicional, o rich text e os estilos em pool constituem a fase final de um relatório, aplicados após a definição do modelo de dados e do layout, sendo essas etapas iniciais o tema do artigo geração de relatórios orientada a modelos com o HotXLS. A referência completa sobre regras, trechos e estilos está disponível na página de produto HotXLS Component.