Una regola Top 10 percento applicata a una colonna di 27 cifre di vendite fa sì che Excel colori tre celle. Il dieci percento di 27 è 2.7, quindi un renderer che arrotonda ne colora tre, uno che prende il soffitto tre, e uno che tronca per difetto due. Due delle tre ipotesi su questa colonna coincidono con Excel, ed entrambe sono sbagliate: Excel tronca il conteggio a due e poi colora anche la terza cella perché in pari con la seconda a 88. Cambiate un valore e le ipotesi fortunate smettono di coincidere
Questa è tutta la difficoltà di disegnare la formattazione condizionale fuori da Excel. Le regole stanno nel file; la risposta a "quali celle si accendono, e con quale colore" no. Se il vostro output è un report HTML, un PDF o una griglia che dipingete voi, dovete calcolarla, e le famiglie di regole aggregate sono dove si nasconde l'aritmetica. La creazione delle regole è un altro argomento, coperto in formattazione condizionale e stili rich text, e ciò che succede a un intervallo di regola quando le righe si spostano è coperto in partizionamento dei formati condizionali ancorati. Questa nota parla di valutazione
Cosa dice e cosa non dice l'elemento conditionalFormatting
ECMA-376 e ISO 29500-1 definiscono la memorizzazione, e lì si fermano. Un elemento conditionalFormatting (§18.3.1.18) porta un sqref e una lista di figli cfRule (§18.3.1.10). Ogni regola ha un type, un operator opzionale, una priority, un flag stopIfTrue, uno o due figli formula e, per le famiglie visive, un insieme di soglie cfvo. Tutto ciò registra fedelmente cosa l'utente ha cliccato nella finestra di dialogo. Nulla di ciò è un algoritmo
Per molti tipi di regola il divario è innocuo. cellIs con operator="greaterThan" significa maggiore di, e containsText significa che la sottostringa è presente. Il guaio inizia con le regole il cui verdetto dipende dal resto dell'intervallo: top10 con percent="1" non dice come si arrotonda un conteggio frazionario, aboveAverage con stdDev="1" non dice se la deviazione è campionaria o di popolazione, e nessuna delle due dice se un valore uguale al taglio si qualifica. Scegliete male e il vostro PDF non concorda con la cartella di lavoro che il cliente ha aperto nella finestra accanto
TCondFormatRule.Evaluate: cosa può decidere da sola una singola cella
HotXLS ha due superfici di valutazione, che differiscono in ciò che sono disposte a rispondere. Il motore classico (.xls) espone TCondFormatRule.Evaluate in lxCondFormat.pas, che prende una calcolatrice, un indice di foglio, una posizione di cella e il valore della cella, e restituisce un Boolean per una regola. Copre gli otto operatori cellIs (between, not between, equal, not equal, greater, less, greater or equal, less or equal), le regole expression in forma libera valutate alla cella così che i riferimenti relativi si ribasino correttamente, i quattro predicati testuali e i predicati di vuoto ed errore. Le formule soglia vengono risolte alla posizione della cella, e una regola between i cui limiti arrivano invertiti li vede scambiare piuttosto che rifiutare. I confronti testuali non distinguono maiuscole e minuscole, come in Excel
// Motore classico: una regola, una cella, un 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, medie, duplicati, periodi di tempo e i tipi visivi
// rispondono False qui; hanno bisogno dell'intero intervallo
if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
Exit(True);
end;Il metodo restituisce False per top10, aboveAverage, belowAverage, duplicateValues e uniqueValues. Una cella da sola non può sapere se sta nel dieci percento migliore senza vedere gli altri valori, quindi rispondere significherebbe tirare a indovinare. Le regole di data relativa (timePeriod) rispondono False anche loro su questo percorso. Le quattro famiglie visive, dataBar, colorScale2, colorScale3 e iconSet, rispondono False per un motivo diverso: non producono mai un sì o un no, producono un colore, una lunghezza di barra o un indice di icona, e un Boolean non ha spazio per questi. Un False di questa chiamata significa quindi "non deciso qui" almeno spesso quanto "non corrisponde", il che va bene per un controllo rapido su regole di confronto ed è insicuro per qualsiasi cosa spediate
Come fa un evaluator a livello di foglio a evitare di riscandire il foglio per ogni cella?
Fa tutto il lavoro a livello di intervallo una volta sola, quando viene creato. TXLSXConditionalFormatEvaluator in lxHandleX.pas è uno snapshot immutabile dei formati condizionali di un foglio di lavoro, ottenuto da TXLSXWorksheet.CreateConditionalFormatEvaluator; il chiamante lo possiede e lo libera. L'alternativa ingenua, in cui ogni cella dipinta innesca una scansione del proprio intervallo di regole per ricalcolare una media o un rank, trasforma un export di 10.000 celle in 10.000 scansioni di intervallo per regola
Alla costruzione l'evaluator analizza ogni sqref distinto una volta sola, così dieci regole sullo stesso intervallo condividono un'analisi e un passaggio sulle sue celle popolate. Quel passaggio raccoglie media, deviazione di popolazione, minimo e massimo in un'unica camminata, e conserva una copia ordinata dei numeri solo quando una regola Top/Bottom o una soglia percentile ha davvero bisogno di statistiche d'ordine. Le regole di duplicato e unicità costruiscono e ordinano le proprie chiavi di valore una volta sola, così ogni ricerca successiva è una ricerca binaria. Le formule delle regole vengono compilate una volta e rivalutate per cella applicando l'offset della cella, così =A1>AVERAGE($A$1:$A$100) mantiene le sue parti relative e assolute che si comportano esattamente come farebbero in Excel senza venire ri-analizzate per ogni riga. Le righe sono raggruppate così che EvaluateCell visiti solo le regole i cui intervalli possono raggiungere la riga richiesta
Le regole vengono poi applicate in ordine di priority, prima il numero più basso, con i pari risolti per posizione nel file. Ogni proprietà di formattazione viene presa dalla prima regola corrispondente che la imposta, e le regole a priorità più bassa riempiono solo le proprietà ancora vuote: una regola a priorità 1 che imposta il grassetto e una a priorità 2 che imposta un riempimento rosso si mostrano entrambe, e la cella esce in grassetto su rosso. Una corrispondenza su una regola con StopIfTrue termina la camminata per quella cella
procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
FirstRow, LastRow, FirstCol, LastCol: Integer);
var
Evaluator: TXLSXConditionalFormatEvaluator;
Res: TXLSXCfCellResult;
Row, Col: Integer;
begin
// Uno snapshot per passata di rendering; righe e colonne sono 1-based
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, stesso layout di TColor
if Res.HasDataBar then // DrawBar è una vostra routine
DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
if Res.HasIcon then // DrawIcon è una vostra routine
DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
if Res.ShowCellValue and Res.HasNumberFormat and
Sheet.Cells.HasCell(Row, Col) then // DrawCellText è una vostra routine
DrawCellText(Row, Col,
Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
end;
finally
Evaluator.Free;
end;
end;Lo snapshot viene costruito una volta per passata apposta. Crearlo dentro il ciclo interno ricompila e riscansiona tutto per ogni cella, che è esattamente il costo che il design esiste per evitare. ShowCellValue torna False quando una regola data bar ha showValue="0" o un set di icone è impostato per mostrare solo l'icona, così il renderer disegna la barra o il glifo e lascia fuori il numero
Quante celle evidenzia davvero una regola Top 10 percento?
Il conteggio viene troncato per difetto, mai permesso sotto uno, e ogni valore in pari con il taglio è incluso. La specifica non lo dice; è ciò che fa Excel, e potete confermarlo in un minuto digitando i 27 valori della figura in una colonna, applicando Top 10% e contando le celle colorate. TXLSXConditionalFormatEvaluator calcola il conteggio di rank come Floor(Count * Min(Rank, 100) / 100), lo porta a 1 quando atterra a zero, lo limita al numero di celle numeriche popolate, legge il taglio dai valori ordinati e poi confronta ogni cella con >=. Per la colonna da 27 valori questo significa un conteggio di due, un taglio di 88 e tre celle colorate perché due di loro contengono 88. Le regole Bottom-N fanno il speculare con <=
Le regole sopra la media portano la seconda ambiguità. Con stdDev="1" la regola seleziona le celle almeno una deviazione standard sopra la media, e la deviazione campionaria e quella di popolazione differiscono per la correzione di Bessel, cosa che conta di più sugli intervalli corti dove la gente usa davvero questa regola. Su cinque valori le due differiscono di circa il 12 percento. HotXLS usa la deviazione di popolazione, la stessa cifra che restituisce STDEV.P, quindi un modo rapido per controllare qualsiasi renderer è mettere =AVERAGE(r)+STDEV.P(r) accanto all'intervallo e vedere quali celle lo superano. I confronti contro una banda di deviazione sono inclusivi; il confronto semplice sopra o sotto è stretto a meno che la regola porti equalAverage="1"
Le regole di duplicato e unicità dipendono da ciò che conta come "lo stesso valore". Il numero 100 e il testo "100" condividono una chiave, perché il testo numerico viene normalizzato nello spazio delle chiavi numeriche; le chiavi testuali confrontano senza distinguere maiuscole; TRUE e 1 restano distinti, come gli errori di tipi diversi. Il testo contenente * o ? viene trattato come pattern con wildcard, in stile COUNTIF, così una cella che contiene Q* conta contro ogni valore che inizia con Q. Le celle vuote sono la trappola opposta: una cella veramente vuota dentro l'intervallo non viene mai colorata come duplicato, quindi una colonna incompleta non si accende dall'alto in basso, mentre una cella che memorizza una stringa vuota viene contata tra i vuoti
Scale di colore, data bar e set di icone tornano come numeri
Le famiglie visive si risolvono in valori pronti per il rendering, e i loro margini sono dove i renderer scritti a mano deviano. Per una scala di colore, EvaluateCell limita la posizione all'intervallo da 0 a 1 e interpola ogni canale RGB con troncamento, così un valore sotto la soglia minima prende il colore minimo invece di uno estrapolato. Una scala a tre soglie usa la coppia mediana-massimo per i valori sopra la soglia mediana e la coppia minimo-mediana altrimenti. Una scala il cui primo e ultimo limite risolvono allo stesso numero restituisce l'ultimo colore invece di dividere per zero. Le soglie percentile vengono interpolate linearmente tra i valori ordinati vicini, la definizione di PERCENTILE.INC
Le data bar vengono normalizzate alla larghezza della cella. Per un intervallo tutto positivo, DataBarAxis è 0 e DataBarEnd corre tra la lunghezza minima e massima di barra della regola, che per default valgono 10 e 90 percento, così il valore più piccolo prende comunque una barra corta e il più grande non riempie mai del tutto la cella. Quando l'intervallo attraversa lo zero, l'asse sta alla sua posizione proporzionale e DataBarEnd segna dove la barra si ferma, dal lato opposto dell'asse per i valori negativi. Direzione, gradiente, bordo e colori delle barre negative vengono riempiti sullo stesso record, con i colori di tema e le tinte già risolti
I set di icone richiedono attenzione alla rigidità di ogni soglia, perché ogni cfvo dopo il primo porta il proprio flag gte. L'evaluator percorre le soglie verso l'alto, applicando >= o > per soglia come dice ThresholdEqualsInclude, e la soglia più alta che il valore supera vince. Un set invertito capovolge l'indice risolto, gli override per icona possono sostituire un glifo di un'altra famiglia tramite IconSetType, e una soglia che non si può risolvere ferma la regola per quella cella, così la cella non prende nessuna icona invece di una sbagliata plausibile
Un solo risultato per la griglia, l'export HTML e il PDF
TXLSXCfCellResult porta tutto ciò che un renderer necesita: colore di riempimento e font con tinta di tema applicata, grassetto, corsivo, sottolineato, barrato, un id di formato numerico, la geometria e i colori della data bar, e la famiglia e l'indice dell'icona. HotXLS passa lo stesso record al suo export HTML, al suo export PDF e a TXLSWorkbookViewer, il che tiene i tre output in accordo cella per cella. Il viewer crea un evaluator per il foglio attivo la prima volta che dipinge e lo riutilizza attraverso scorrimento, selezione e ridisegno, e lo scarta quando passate a un altro foglio o assegnate un'altra cartella di lavoro
// Modifiche dietro un viewer live: lo snapshot in cache non vede il cambiamento
Sheet := Viewer.XlsxWorkbook.Sheets[1]; // indice di foglio 1-based
Sheet.Cells[5, 2].Value := 4200; // sposta la media, il massimo e la classifica
Viewer.RefreshConditionalFormats; // scarta lo snapshot e ridipingePoiché lo snapshot è immutabile, una modifica in place lascia medie, rank ed estremi delle scale di colore obsoleti finché RefreshConditionalFormats non gira. Lo stesso vale per il vostro codice: un evaluator che conservate attraverso le modifiche va liberato e ricreato, perché CreateConditionalFormatEvaluator cattura i valori nel momento in cui viene chiamato
Le regole di data relativa meritano una decisione presa in anticipo. L'evaluator a livello di foglio confronta le regole timePeriod con la data corrente della macchina, quindi una regola "questa settimana" viene renderizzata in un modo in un PDF prodotto oggi e in un altro quando la stessa cartella di lavoro viene esportata martedì prossimo. È un comportamento corretto e resta comunque un ticket di supporto se dal vostro archivio ci si aspetta riproducibilità. Per i report archiviati, sostituite le regole timePeriod con una regola expression che confronti con una data del report memorizzata in una cella, come =AND(A2>=$Z$1-6,A2<=$Z$1) per una finestra di sette giorni; l'output dipende allora dalla cartella di lavoro e da nient'altro. Excel rifiuta anche i riferimenti strutturati a tabelle dentro una formula di formattazione condizionale, quindi puntate la regola all'intervallo di celle della colonna
Se la destinazione è uno schermo anziché un file, lo stesso record di risultato guida anche la griglia foglio di calcolo VCL personalizzata descritta altrove su questo blog. Il modello di regole, il riferimento API dell'evaluator e le build di prova sono sulla pagina del componente foglio di calcolo Delphi HotXLS