HotXLS je natívna komponenta pre tabuľky pre Delphi a C++Builder, a od verzie 2.209.0 dokáže odpovedať na otázku, ktorú si Excel bežne necháva pre seba: pre túto presnú bunku, ktoré pravidlá podmieneného formátovania sa spustia, a na akú výplň, font, dátový pruh alebo ikonu sa vyriešia. Táto odpoveď je to, čo potrebujete v momente, keď je váš výstup HTML správa, PDF, alebo mriežka, ktorú si maľujete sami
Toto je iný problém než vytváranie pravidiel. Dve skoršie poznámky pokrývajú stranu autorstva: podmienené formátovanie a štýly formátovaného textu sa venuje pripájaniu pravidiel a diferenciálnych formátov k rozsahu, a rozdeľovanie ukotvených podmienených formátov sa venuje tomu, čo sa stane s rozsahom pravidla pri vkladaní alebo mazaní riadkov a stĺpcov. Oba sú štrukturálne. Tento je o sémantike: pri zošite, ktorý už nesie pravidlá, vypočítať zvýraznenie
Prečo formát súboru nepovie, ktoré bunky sa rozsvietia
Krátka odpoveď je, že ECMA-376 a ISO 29500-1 definujú úložisko, nie vyhodnocovanie. Element conditionalFormatting (§18.3.1.18) nesie sqref a zoznam detí cfRule (§18.3.1.10), a každé pravidlo nesie type, voliteľný operator, priority, príznak stopIfTrue, jednu alebo dve detí formula, a pre vizuálne rodiny sadu prahov cfvo. Každé z toho verne opisuje, čo používateľ nakonfiguroval, a nič z toho nie je algoritmus. Pre polovicu typov pravidiel na tejto medzere nezáleží: cellIs s operator="greaterThan" znamená väčšie než, a containsText znamená, že podreťazec je prítomný. Medzera sa otvára pri agregátnych rodinách. Pravidlo top10 s rank="10" a percent="1" nad 27 vyplnenými numerickými bunkami zvýrazní koľko buniek? Dva celé sedem nie je číslo. Zaokrúhliť, orezať dole, alebo orezať hore — špecifikácia mlčí, a zlá voľba znamená, že váš PDF nesúhlasí so zošitom, ktorý má zákazník otvorený vedľa neho
Pravidlá pre jednu bunku a kde TCondFormatRule.Evaluate končí
HotXLS si vzal najprv lacnejšiu polovicu. TCondFormatRule.Evaluate v lxCondFormat.pas, pridané v 2.199.0, odpovedá na to, či sa jedno pravidlo spustí pre jednu bunku bez toho, aby čokoľvek vedelo o zvyšku rozsahu. Zvláda osem BIFF porovnávacích operátorov za cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), voľne formulovateľné pravidlá expression vyhodnotené v bunke, takže relatívne referencie sa správne prebazujú, štyri textové predikáty, a predikáty pre prázdne a chybové hodnoty. Prahy pochádzajú z FFormula1 a FFormula2 vyriešených cez TXLSCalculator.GetRangeValue na pozícii bunky, a obrátené hranice sa vymenia namiesto toho, aby boli odmietnuté
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;Úprimná časť tejto metódy je to, čo odmieta hádať. top10, aboveAverage, belowAverage, duplicateValues a uniqueValues vracajú False, nie preto, že sú ťažké, ale preto, že sú nerozhodnuteľné z jednej bunky — každá z nich potrebuje štatistiku nad celou doménou. Štyri vizuálne rodiny, dataBar, colorScale2, colorScale3 a iconSet, vracajú False z iného dôvodu: nikdy nevyprodukujú boolean vôbec, produkujú vykresľovací payload, a booleovský návratový typ je pre ne nesprávny tvar
Ako sa vyhodnocovač na úrovni hárku vyhne opätovnému prehľadávaniu hárku?
Výpočtom každej zdieľanej veličiny raz, pri konštrukcii, a nikdy znovu. TXLSXConditionalFormatEvaluator v lxHandleX.pas je nemenný snímok pre jeden pracovný hárok, zostavený cez TXLSXWorksheet.CreateConditionalFormatEvaluator, a jeho celý dizajn je obranou proti naivnej implementácii, kde každá vymaľovaná bunka spustí plný sken rozsahu
V konštruktore sa dejú štyri veci. Každý odlišný viacoblastný sqref sa parsuje presne raz do TXlsxCfRangeSnapshot, takže desať pravidiel zdieľajúcich jeden rozsah zdieľa jeden parse a jeden štatistický prechod. Tento prechod streamuje priemer, populačnú odchýlku, minimum a maximum nad vyplnenými bunkami v jedinom prechode, a udržiava usporiadané numerické pole iba vtedy, keď to pravidlo Top/Bottom alebo percentilu skutočne potrebuje pre poradové štatistiky. Kľúče pre duplicity a unikáty sa budujú unicode-bezpečne a dávkovo zoradia raz namiesto pri každom vyhľadaní. Potom sa os riadkov rozreže na pásma na každej hranici oblasti, takže EvaluateCell binárne vyhľadáva pásmo a navštívi iba pravidlá, ktorých rozsahy môžu tento riadok vôbec dosiahnuť
Štvrtá vec je tá, na ktorej záleží najviac pri väčšom rozsahu. Relatívny vzorec pravidla ako =A1>AVERAGE($A$1:$A$100) znamená v každej bunke domény niečo iné, a zjavná implementácia by kompilovala čerstvý syntaktický strom pre každú bunku. TXlsxCfRulePlan ho skompiluje raz a znovu vyhodnocuje ten istý strom cez reverzibilné súradnicové posuny, čo zachováva správanie Excel kotvy bez alokácie syntaktického stromu na bunku. Pravidlá sa potom vrstvia podľa priority, a zhoda na pravidle, ktorého StopIfTrue je nastavené, preruší slučku, presne tak ako Excel skratovo vyhodnocuje
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;Ako Excel v skutočnosti zaokrúhľuje pravidlo Top 10 percent?
Oreže smerom dole, s minimom jedna, a zahŕňa remízy na hranici. Toto nie je nikde zapísané v ISO 29500-1 — bolo to ustálené skúšaním Excelu 16 ručne zostavenými zošitmi a čítaním späť, ktoré bunky aplikácia zvýraznila. HotXLS implementuje presne to: počet zaradenia je Floor(Count * Min(Rank, 100) / 100), zvýšený na 1, keď pristane na nule, orezaný na vyplnený počet, a hraničná hodnota sa potom porovná pomocou >=, takže každá bunka rovná hranici je zvýraznená, aj keď to prekročí požadovaný počet. Dvadsaťsedem hodnôt a pravidlo 10 percent zvýrazní dve bunky, plus akékoľvek ďalšie bunky remizujúce s druhou
Pravidlá above-average skrývali druhú nejednoznačnosť: aboveAverage s stdDev="1" vyberá bunky jednu smerodajnú odchýlku nad priemerom, ale výberová a populačná odchýlka sa líšia Besselovou korekciou a viditeľne sa rozchádzajú na malých rozsahoch, čo je presne tam, kde sa podmienené formátovanie používa. Excel 16 používa populačnú odchýlku, a HotXLS sa mu prispôsobuje, s príznakom equalAverage, ktorý robí prísne porovnanie inkluzívnym iba vtedy, keď nie je v hre žiadne pásmo odchýlky. Pravidlá pre duplicity a unikáty sa naopak spoliehajú na identitu kľúča. Ak jedna bunka drží číslo 100 a druhá text „100“, Excel ich považuje za rovnaký duplicitný kľúč, takže HotXLS normalizuje numerický text do numerického priestoru kľúčov namiesto porovnávania surových reťazcov. Prázdne bunky sú zrkadlový prípad: skutočne prázdna bunka sa počíta do súčtu rozsahu, ale sama sa neštýluje, takže prázdne bunky v stĺpci sa navzájom nerozsvietia ako duplicity
Farebné škály a sady ikon: interpolácia a hraničné pravidlá
Vizuálne rodiny sa riešia na čísla pripravené na vykreslenie namiesto booleanov, a ich hraničné správanie bolo ustálené rovnakým spôsobom. Pre farebnú škálu s explicitnými numerickými prahmi HotXLS oreže pozičný zlomok na uzavretý interval nula až jeden, potom interpoluje po kanáloch orezaním namiesto zaokrúhlenia — hodnota pod minimálnym zastavením dostane minimálnu farbu namiesto extrapolovanej, trojbodová škála vyberá svoj pár porovnaním voči strednému zastaveniu, a degenerovaná škála, ktorej dva konce nesú rovnaký prah, sa zredukuje na hornú farbu namiesto delenia nulou. Sady ikon potrebovali opačný druh starostlivosti, pretože každé cfvo po prvom nesie vlastnú prísnosť porovnania: HotXLS číta ThresholdEqualsInclude pre každý prah a podľa toho aplikuje >= alebo >, postupujúc smerom nahor, takže najvyšší splnený prah vyhráva index ikony. Obrátená sada preklopí vyriešený index namiesto prahov, prepísania pre jednotlivé ikony môžu vytiahnuť glyf z inej rodiny, a akýkoľvek neplatný prah pravidlo preruší namiesto vyprodukovania vierohodne vyzerajúcej, ale nesprávnej ikony
Napájanie mriežky, HTML exportu a PDF jedným výsledkom
Pretože EvaluateCell vracia úplne vyriešený TXLSXCfCellResult — diferenciálnu výplň a farbu fontu s už aplikovaným tónovaním témy, tučné, kurzívu, podčiarknutie, id číselného formátu, smerové rozsahy kladného a záporného pruhu, pozíciu osi, rodinu ikony a index — každý konzument číta ten istý záznam a žiadny z nich nemusí rozumieť vnútornostiam pravidla. HotXLS používa túto jednu cestu pre HTML export, PDF export a interaktívny viewer, čo je jediný praktický spôsob, ako zabrániť trom rendererom rozísť sa. Verzia 2.210.0 to zapojila do TXLSWorkbookViewer, ktorý ukladá do cache jeden pripravený vyhodnocovač na aktívny pracovný hárok a opätovne ho používa naprieč scrollovaním, výberom a prekresľovaním, uvoľňujúc ho pri zmene zošita alebo hárku — opätovné zostavenie snímku pri každom Paint by porazilo celý dizajn založený na konštrukčnom čase. Táto cache je aj dôvodom, prečo existuje TXLSWorkbookViewer.RefreshConditionalFormats: snímok je nemenný, takže ak upravíte pripojený zošit na mieste, agregátne štatistiky a vyriešené prahy sú zastarané, kým to nezavoláte
// 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, repaintČo za vás vyhodnocovač neurobí
Tri hranice stojí za to vysloviť jasne. Klasický jednobunkový TCondFormatRule.Evaluate a vyhodnocovač na úrovni hárku TXLSXConditionalFormatEvaluator sú odlišné povrchy s odlišnými schopnosťami, a jednobunkový zámerne odmieta agregátne a vizuálne rodiny namiesto ich aproximácie — ak potrebujete Top/Bottom alebo farebnú škálu, zostavte vyhodnocovač. Relatívne časové obdobia závisia od hodín stroja v čase vyhodnotenia, takže pravidlo timePeriod sa vykreslí odlišne v PDF vygenerovanom dnes a v tom, ktorý vznikne budúci týždeň, čo je správne správanie a napriek tomu čakajúci ticket na podporu, ak sa od vášho archívu očakáva bajtová stabilita. Tretia je skôr gramatická než technická: gramatika vzorca podmieneného formátu zakazuje štruktúrované referencie na tabuľky, takže pravidlo nemôže adresovať stĺpec tabuľky menom tak, ako to dokáže vzorec hárku, a to je obmedzenie formátu, nie implementácie
Ak staviate výstup správ, exportnú pipeline alebo vlastnú mriežku, ktorá sa musí zhodovať s Excelom bunku po bunke, ten istý vyriešený výsledok poháňa aj vlastnú VCL mriežku pre tabuľky opísanú inde na tomto blogu. Úplná dokumentácia API, model pravidiel a skúšobné sťahovania pre HotXLS Delphi komponentu pre tabuľky sú dostupné na stránke produktu