HotXLS è un componente foglio di calcolo nativo per Delphi e C++Builder, e dalla versione 2.209.0 può rispondere alla domanda che Excel normalmente tiene per sé: per questa esatta cella, quali regole di formattazione condizionale si attivano, e a quale riempimento, font, data bar o icona si risolvono. Questa risposta è ciò di cui hai bisogno nel momento in cui il tuo output è un report HTML, un PDF, o una griglia che dipingi tu stesso
Questo è un problema diverso dal creare regole. Due note precedenti coprono il lato della creazione: formattazione condizionale e stili rich text tratta l'attaccare regole e formati differenziali a un intervallo, e partizionamento dei formati condizionali ancorati tratta cosa succede all'intervallo di una regola quando righe e colonne vengono inserite o eliminate. Entrambe sono strutturali. Questa riguarda la semantica: dato un workbook che già porta regole, calcola l'evidenziazione
Perché il formato file non ti dice quali celle si accendono
La risposta breve è che ECMA-376 e ISO 29500-1 definiscono la memorizzazione, non la valutazione. Un elemento conditionalFormatting (§18.3.1.18) porta un sqref e un elenco di figli cfRule (§18.3.1.10), e ogni regola porta 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. Ognuno di questi descrive fedelmente ciò che l'utente ha configurato, e nessuno di essi è un algoritmo. Per metà dei tipi di regola quel divario non conta: cellIs con operator="greaterThan" significa maggiore di, e containsText significa che la sottostringa è presente. Il divario si apre sulle famiglie aggregate. Una regola top10 con rank="10" e percent="1" su 27 celle numeriche popolate evidenzia quante celle? Due virgola sette non è un numero. Arrotonda, tronca per difetto o per eccesso — la specifica tace, e scegliere male significa che il tuo PDF non concorda con il workbook che il cliente ha aperto accanto
Regole a singola cella e dove si ferma TCondFormatRule.Evaluate
HotXLS ha affrontato per primo la metà economica. TCondFormatRule.Evaluate in lxCondFormat.pas, aggiunto in 2.199.0, risponde se una regola si attiva per una cella senza sapere nulla del resto dell'intervallo. Gestisce gli otto operatori di confronto BIFF dietro cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), regole expression in forma libera valutate alla cella così che i riferimenti relativi si ribasino correttamente, i quattro predicati testuali, e i predicati blanks ed errors. Le soglie vengono da FFormula1 e FFormula2 risolte tramite TXLSCalculator.GetRangeValue alla posizione della cella, e i limiti invertiti vengono scambiati piuttosto che rifiutati
var
I: Integer;
Rule: TCondFormatRule;
Value: Variant;
begin
Value := Sheet.Cells[Row, Col].Value;
for I := 0 to CondFormat.RuleCount - 1 do
begin
Rule := CondFormat.Rule(I);
// Single-cell verdict only. Aggregate and visual kinds answer False.
if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
ApplyHighlight(Row, Col, Rule.Style);
end;
end;La parte onesta di quel metodo è ciò che si rifiuta di indovinare. top10, aboveAverage, belowAverage, duplicateValues e uniqueValues restituiscono False, non perché siano difficili ma perché sono indecidibili da una sola cella — ognuna di esse ha bisogno di una statistica sull'intero dominio. Le quattro famiglie visive, dataBar, colorScale2, colorScale3 e iconSet, restituiscono False per un motivo diverso: non producono mai un booleano affatto, producono un payload di rendering, e un tipo di ritorno Booleano è la forma sbagliata per esse
Come evita un evaluator a livello foglio di riscandire il foglio?
Calcolando ogni quantità condivisa una sola volta, alla costruzione, e mai più. TXLSXConditionalFormatEvaluator in lxHandleX.pas è uno snapshot immutabile per un foglio di lavoro, costruito tramite TXLSXWorksheet.CreateConditionalFormatEvaluator, e l'intero suo design è una difesa contro l'implementazione ingenua dove ogni cella dipinta innesca una scansione completa dell'intervallo
Quattro cose accadono nel costruttore. Ogni sqref multi-area distinto viene analizzato esattamente una volta in uno TXlsxCfRangeSnapshot, così dieci regole che condividono un intervallo condividono un'analisi e un passaggio di statistiche. Quel passaggio calcola in streaming media, deviazione di popolazione, minimo e massimo sulle celle popolate in un'unica scansione, e mantiene un array numerico ordinato solo quando una regola Top/Bottom o percentile ne ha effettivamente bisogno per le statistiche d'ordine. Le chiavi di duplicato e unicità vengono costruite in modo sicuro per Unicode e ordinate in batch una sola volta invece che per ogni ricerca. Poi l'asse delle righe viene tagliato in bande a ogni confine di area, così EvaluateCell esegue una ricerca binaria di una banda e visita solo le regole i cui intervalli possono potenzialmente raggiungere quella riga
Il quarto è quello che conta di più su larga scala. Una formula di regola relativa come =A1>AVERAGE($A$1:$A$100) significa qualcosa di diverso in ogni cella del dominio, e l'implementazione ovvia compila un nuovo albero sintattico per cella. TXlsxCfRulePlan lo compila una sola volta e rivaluta lo stesso albero attraverso offset di coordinate reversibili, il che preserva il comportamento di ancoraggio di Excel senza un'allocazione di albero sintattico per cella. Le regole vengono poi stratificate per priority, e una corrispondenza su una regola il cui StopIfTrue è impostato interrompe il ciclo, esattamente come Excel esegue lo short-circuit
var
Evaluator: TXLSXConditionalFormatEvaluator;
Res: TXLSXCfCellResult;
begin
Evaluator := Sheet.CreateConditionalFormatEvaluator;
try
if Evaluator.EvaluateCell(Row, Col, Res) then
begin
if Res.HasFillColor then
Canvas.Brush.Color := TColor(Res.FillColor);
if Res.HasIcon then
// IconIndex is zero-based inside Res.IconSetType
DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
if Res.HasDataBar then
// DataBarAxis and DataBarEnd are normalised to 0..1
DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
if not Res.ShowCellValue then
Exit; // showValue="0" on the rule hides the number
end;
finally
Evaluator.Free;
end;
end;Come arrotonda realmente Excel una regola Top 10 percento?
Tronca per difetto, con un minimo di uno, e include i pareggi al taglio. Questo non è scritto da nessuna parte in ISO 29500-1 — è stato fissato sondando Excel 16 con workbook costruiti a mano e leggendo quali celle l'applicazione evidenziava. HotXLS implementa esattamente questo: il conteggio di rank è Floor(Count * Min(Rank, 100) / 100), elevato a 1 quando atterra a zero, limitato al conteggio popolato, e il valore di taglio viene poi confrontato con >= così ogni cella uguale al confine viene evidenziata anche quando ciò supera il conteggio richiesto. Ventisette valori e una regola del 10 percento evidenziano due celle, più qualsiasi altra cella in pareggio con la seconda
Le regole above-average nascondevano una seconda ambiguità: aboveAverage con stdDev="1" seleziona celle una deviazione standard sopra la media, ma la deviazione campionaria e quella di popolazione differiscono per la correzione di Bessel e sono visibilmente in disaccordo su intervalli piccoli, che è esattamente dove viene usata la formattazione condizionale. Excel 16 usa la deviazione di popolazione, e HotXLS la replica, con il flag equalAverage che rende inclusivo il confronto stretto solo quando nessuna banda di deviazione è in gioco. Le regole di duplicato e unicità puntano invece sull'identità della chiave. Se una cella contiene il numero 100 e un'altra contiene il testo "100", Excel le tratta come la stessa chiave duplicata, quindi HotXLS normalizza il testo numerico nello spazio della chiave numerica invece di confrontare stringhe grezze. Le celle vuote sono il caso speculare: una cella veramente vuota partecipa al conteggio dell'intervallo ma non viene essa stessa stilizzata, così le celle vuote in una colonna non si accendono tutte come duplicate l'una dell'altra
Color scale e icon set: interpolazione e regole di confine
Le famiglie visive si risolvono in numeri pronti per il rendering piuttosto che booleani, e il loro comportamento ai margini è stato fissato allo stesso modo. Per una scala colore con soglie numeriche esplicite, HotXLS limita la frazione di posizione all'intervallo chiuso da zero a uno, poi interpola per canale con troncamento piuttosto che arrotondamento — un valore sotto la soglia minima ottiene il colore minimo invece di uno estrapolato, una scala a tre soglie sceglie la propria coppia confrontandola con la soglia mediana, e una scala degenere i cui due estremi portano la stessa soglia collassa al colore superiore invece di dividere per zero. Gli icon set hanno richiesto il tipo opposto di attenzione, perché ogni cfvo dopo il primo porta la propria rigidità di confronto: HotXLS legge ThresholdEqualsInclude per soglia e applica >= o > di conseguenza, procedendo verso l'alto così che la soglia più alta soddisfatta vince l'indice dell'icona. Un set invertito capovolge l'indice risolto piuttosto che le soglie, gli override per icona possono prelevare un glifo da una famiglia diversa, e qualsiasi soglia non valida interrompe la regola invece di produrre un'icona sbagliata dall'aspetto plausibile
Alimentare una griglia, un export HTML e un PDF da un solo risultato
Poiché EvaluateCell restituisce un TXLSXCfCellResult completamente risolto — colore di riempimento e font differenziale con tinta di tema già applicata, grassetto, corsivo, sottolineato, id del formato numerico, estensioni di barra direzionali positive e negative, posizione dell'asse, famiglia e indice dell'icona — ogni consumatore legge lo stesso record e nessuno di essi ha bisogno di capire gli interni delle regole. HotXLS usa quell'unico percorso per l'export HTML, l'export PDF e il visualizzatore interattivo, che è l'unico modo pratico per impedire a tre renderer di divergere tra loro. La versione 2.210.0 lo ha collegato a TXLSWorkbookViewer, che mette in cache un evaluator preparato per foglio di lavoro attivo e lo riutilizza durante lo scorrimento, la selezione e il ridisegno, rilasciandolo quando il workbook o il foglio cambiano — ricostruire lo snapshot a ogni Paint vanificherebbe l'intero design a tempo di costruzione. Quella cache è anche il motivo per cui esiste TXLSWorkbookViewer.RefreshConditionalFormats: lo snapshot è immutabile, quindi se muti il workbook collegato sul posto le statistiche aggregate e le soglie risolte restano obsolete finché non lo chiami
// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200; // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats; // drop evaluator, repaintCosa non farà l'evaluator per te
Vale la pena dichiarare chiaramente tre confini. Il classico TCondFormatRule.Evaluate a singola cella e il TXLSXConditionalFormatEvaluator a livello foglio sono superfici diverse con capacità diverse, e quello a singola cella declina deliberatamente le famiglie aggregate e visive piuttosto che approssimarle — se ti servono Top/Bottom o una scala colore, costruisci l'evaluator. I periodi di data relativi dipendono dall'orologio della macchina al momento della valutazione, quindi una regola timePeriod viene renderizzata diversamente in un PDF generato oggi e uno generato la settimana prossima, il che è un comportamento corretto ma resta un ticket di supporto in attesa di accadere se il tuo archivio deve essere byte-stabile. Il terzo è grammaticale piuttosto che tecnico: la grammatica delle formule di formattazione condizionale proibisce i riferimenti a tabelle strutturate, quindi una regola non può indirizzare una colonna di tabella per nome come può fare una formula di foglio di lavoro, e quello è un vincolo del formato piuttosto che dell'implementazione
Se stai costruendo output di report, una pipeline di export o una griglia personalizzata che deve concordare con Excel cella per cella, lo stesso risultato risolto guida anche la griglia foglio di calcolo VCL personalizzata descritta altrove su questo blog. La documentazione API completa, il modello delle regole e i download di prova per il componente foglio di calcolo Delphi HotXLS sono disponibili sulla pagina prodotto