Formattazione condizionale e rich text in HotXLS

Una regola di formattazione condizionale in OOXML è composta da due cose distinte che portano un solo nome. La condizione (un confronto, una formula, una corrispondenza di testo) decide quali celle si qualificano. L'aspetto (un record di formato differenziale, dxf nella terminologia ECMA-376) decide come appaiono quelle celle. La finestra di dialogo di Excel nasconde la giuntura obbligandovi a compilare entrambe le parti insieme. HotXLS no. Create una regola cellIs da Delphi e saltate lo stile: la regola è valida, l'intervallo è corretto, la formula risulta vera esattamente sulle celle giuste e niente cambia colore, perché l'istruzione della regola era "vero, non dipingere nulla". Quello scarto fra condizione e conseguenza è la prima cosa da mettere a posto, e spiega la maggior parte delle regole che sembrano corrette in Gestisci regole eppure non evidenziano nulla

HotXLS scrive la formattazione condizionale in modo nativo sia nei file BIFF8 .xls sia negli OOXML .xlsx, e fa lo stesso per i run rich text e per un modello di stili di cella a pool. Le tre funzionalità condividono più cablaggio di quanto la superficie piatta della API suggerisca, e i punti in cui il risultato si scosta dalle intenzioni sono di solito le giunzioni fra di esse

Una condizione ha bisogno di una conseguenza: lo stile dxf

Sul foglio XLSX le regole di confronto nascono da AddConditionalFormat, che accetta un intervallo, un operatore da TXLSXCfOperator e una formula o un letterale, poi restituisce l'indice della nuova regola dentro la collezione ConditionalFormats del foglio. L'oggetto regola a quell'indice espone una proprietà Style, ed è lì che vive l'evidenziazione. Impostatevi un riempimento e le celle che si qualificano lo assumono. Lasciatela intatta e avete costruito la regola invisibile descritta sopra

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

    // Scostamento negativo: riempimento rosso chiaro
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Gli ID ordine duplicati vengono segnalati allo stesso modo
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Regola con formula personalizzata: evidenzia le righe in cui il valore effettivo non raggiunge il 90% del 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;

Qui i colori sono valori ARGB a 32 bit, quindi $FFFFC7CE è il "rosso chiaro" di Excel che conoscete dalla finestra di dialogo, con un byte alfa completamente opaco davanti alle componenti RGB. Ogni tipo di regola che scatta su una condizione per cella segue la stessa forma crea-poi-stila. Le corrispondenze di testo (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) restituiscono un indice che stilate dopo, e lo stesso vale per AddCondFormatTop10, AddCondFormatAboveAverage e per i rilevatori di celle vuote ed errori. Imparate lo schema una volta e tutta la famiglia di testo e confronti si comporta allo stesso modo

Barre dati, scale di colori e set di icone si dipingono da soli

I tipi di regola visuali funzionano al contrario. Portano il proprio aspetto dentro la definizione della regola e ignorano completamente la proprietà Style. Assegnate un riempimento a una regola con barre dati e non succede nulla, cosa che sembra un difetto finché la tassonomia non si chiarisce: AddCondFormatDataBar prende il colore della barra come argomento diretto, le scale di colori a due e tre punti prendono allo stesso modo i colori dei loro estremi, e AddCondFormatIconSet seleziona uno dei 26 tipi di set di icone come icsTrafficLights3. Qui non c'è alcun record di stile separato da dimenticare, perché non esiste affatto un record di stile separato

I parametri su cui vale la pena ragionare in queste chiamate sono gli ancoraggi dei valori, tipizzati come TXLSCfValueKind. L'estremo di una barra o di una scala può stare al minimo o al massimo dell'intervallo, a un numero letterale, a una percentuale o a un percentile, oppure al risultato di una formula. I valori predefiniti, minimo e massimo dell'intervallo, si comportano bene su dati dimostrativi ordinati e poi vi tradiscono su dati reali con valori anomali: un singolo valore fuori scala allunga la scala e appiattisce ogni altra barra a un moncone. Quando un cruscotto deve essere letto attraverso più periodi, ancorate gli estremi a numeri fissi o a percentili, così che mezza barra a marzo significhi la stessa quantità di mezza barra ad aprile. Una barra a scala automatica è confrontabile solo con se stessa

Il writer XLS copre quattro tipi di regola, non di più

Il lato BIFF8 legacy non è uno specchio ridotto del lato XLSX; è un sottoinsieme deliberato. La facciata XLS può creare esattamente quattro forme di regola condizionale, barre dati, scale a due colori, scale a tre colori e set di icone, emesse come record CF12 nello stream. Non ha alcuna API di creazione per regole cellIs, di espressione o di testo. Le regole di quei tipi già presenti in un file che aprite vengono lette, conservate e riscritte immutate, quindi aprire e risalvare un .xls di un cliente non danneggia mai la formattazione che il file conteneva. Quello che non potete fare è generare da zero evidenziazioni a soglia dentro un .xls. Le alternative sono simularle con normali riempimenti di cella calcolati nel codice, oppure fare del deliverable un .xlsx, dove la famiglia completa di regole è disponibile

È un vincolo da risolvere prima che esista il livello dati, non dopo, perché cambia la decisione sul formato di file per qualsiasi cosa abbia la forma di un cruscotto. Un gruppo che ha scelto .xls per compatibilità e poi specifica un report KPI con soglie cellIs ha scelto due cose che non stanno insieme, e il momento più economico per accorgersene è quello della decisione sul formato, non tre settimane dopo l'inizio della realizzazione

Impilamento delle regole, priorità e intervalli sovrapposti

I cruscotti reali raramente usano una sola regola per intervallo. Una colonna di scostamento potrebbe portare una barra dati per la grandezza, una regola cellIs per la soglia rigida e una regola di espressione a livello di riga sopra entrambe per le escalation. Ogni TXLSXConditionalFormat espone un valore Priority, ed Excel risolve le regole in competizione in ordine di priorità. Quando due regole vogliono dipingere la stessa cella, il vincitore è deciso da un numero che impostate voi, non dall'ordine in cui un revisore capita di scorrere la finestra Gestisci regole

Trattate la priorità come un programma di disegno tratta l'ordine di sovrapposizione. Assegnatela di proposito ovunque due regole possano raggiungere le stesse celle, e lasciate spazi fra i valori così che una regola successiva si inserisca senza rinumerare le altre. Dove le regole non possono entrare in collisione, poniamo una barra dati confinata alla colonna E e una regola di testo confinata alla colonna G, l'ordine di creazione va bene e la priorità non merita attenzione. Spendete quell'attenzione sui confini degli intervalli, perché i difetti costosi qui non sono quasi mai inversioni di priorità. Sono intervalli come B2:B200 su un report cresciuto a 350 righe, dove la coda scoperta si rende come celle normali che sembrano esattamente dati sani. Derivate ogni intervallo di regola dallo stesso valore di conteggio finale delle righe che guida le serie dei grafici e gli intervalli di convalida altrove nella cartella di lavoro, e la coda smette di cadere fuori

Una abitudine di verifica ripaga il suo costo. Dopo la generazione, aprite il file in Excel, selezionate l'intervallo formattato e percorrete Gestisci regole una volta per ogni modifica al modello. La formattazione condizionale è una delle poche aree in cui l'unico renderer autorevole è l'applicazione che consuma il file, quindi uno unit test sull'XML dimostra che la regola è stata scritta, non che Excel la dipinga come volevate. Un minuto di controllo visivo chiude quello scarto

Rich text: molti formati dentro una sola cella

Una cella rich text nel modello XLSX contiene un elenco di run, dove ogni run è un tratto di testo più i suoi attributi di font. Costruite l'elenco a parte come oggetto TXLSXRichText, gli aggiungete i run e poi agganciate il tutto a una cella. La regola sulla proprietà è la parte che morde. Assegnare a Cell.RichText passa la proprietà di quell'oggetto alla cella, e la cella lo libera durante la propria distruzione. Liberatelo anche voi e avete una doppia liberazione, quella che resta silenziosa per tutta l'esecuzione che la ha causata e riemerge come crash da tutt'altra parte molto più tardi

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;   // la proprietà passa alla cella: non chiamate Free
end;

L'esplicito ColorIsAuto := False non è una decorazione opzionale. Un run porta un flag di colore automatico, e un colore assegnato viene onorato solo una volta azzerato quel flag. Impostate Color e dimenticate ColorIsAuto e il run esce in grassetto ma ostinatamente nero, senza alcun errore che indichi la causa. I run supportano anche il barrato, le varianti di sottolineatura e l'allineamento verticale per apice e pedice, mentre PlainText appiattisce l'intero elenco in una sola stringa quando vi serve esportare o confrontare il contenuto testuale

Il rich text a livello di cella è solo XLSX. La facciata XLS non ha una API pubblica per scriverlo, sebbene i run siano disponibili lì su commenti e caselle di testo tramite TextRuns, e le stringhe rich lette da un .xls esistente sopravvivano intatte a un ciclo di andata e ritorno. La spinta è la stessa della formattazione condizionale: tutto ciò che mescola formati dentro una cella appartiene al writer XLSX

Il pool di stili e lo scarto di uno che finisce in produzione

Lo stile semplice delle celle nel modello XLSX passa da collezioni a pool sulla cartella di lavoro. Fonts.Add, Fills.AddSolid e Borders.Add registrano ciascuno una definizione e ne restituiscono l'indice nel pool. Quegli indici partono da 0. Le proprietà lato cella che li consumano, come FontIndex, riservano lo 0 al valore predefinito, quindi il valore che assegnate a una cella è l'indice del pool più uno:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // indice del pool, in base 0
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // indice di cella, in base 1

Togliete il + 1 e ogni intestazione ripiega sul font predefinito. Non c'è eccezione né avviso, solo una cartella di lavoro che sembra non essere stata stilata da nessuno. L'errore di secondo ordine si nasconde nel ciclo: chiamare Fonts.Add una volta per riga. Le definizioni di font identiche vengono deduplicate, quindi il file non è corrotto, ma il lavoro è sprecato, e il pool di allineamenti in particolare restituisce un oggetto nuovo a ogni chiamata invece di fondere i duplicati. Costruite la manciata di stili una volta prima del ciclo e riusatene gli indici. Su report da centomila righe quella singola modifica è una delle leve trattate in ottimizzazione delle prestazioni sulle cartelle di lavoro grandi con HotXLS. Quando vi serve solo un aspetto semantico predefinito, entrambe le facciate espongono ApplyBuiltinStyle sugli intervalli, che mappa sugli stili Buono, Non valido, Neutrale e sugli stili accento incorporati di Excel senza che tocchiate affatto i pool

Formattazione condizionale, rich text e stili a pool sono l'ultimo miglio di un report, applicati dopo che il modello dati e il layout sono stabiliti, e quelle fasi precedenti sono il tema di generazione di report da modelli con HotXLS. Il riferimento completo di regole, run e stili sta sulla pagina di prodotto HotXLS Delphi Component