Vyhodnocení podmíněného formátování Excelu v Delphi s HotXLS

HotXLS je nativní komponenta pro tabulkové procesory v Delphi a C++Builder a od verze 2.209.0 umí odpovědět na otázku, kterou si Excel jinak nechává pro sebe: pro tuto konkrétní buňku, která pravidla podmíněného formátování se spustí a na jakou výplň, font, datový pruh nebo ikonu se vyhodnotí. Přesně tuto odpověď potřebujete ve chvíli, kdy je vaším výstupem HTML report, PDF nebo mřížka, kterou si vykreslujete sami

Jde o jiný problém než vytváření pravidel. Dvě dřívější poznámky se věnují autorské straně: podmíněné formátování a styly formátovaného textu se zabývá připojováním pravidel a diferenciálních formátů k rozsahu, a rozdělování ukotvených podmíněných formátů se zabývá tím, co se s rozsahem pravidla stane při vkládání nebo mazání řádků a sloupců. Obojí je strukturální. Tento článek je o sémantice: máte-li sešit, který už pravidla nese, spočítat zvýraznění

Proč vám formát souboru neřekne, které buňky se rozsvítí

Krátká odpověď zní, že ECMA-376 a ISO 29500-1 definují uložení, ne vyhodnocení. Element conditionalFormatting (§18.3.1.18) nese sqref a seznam potomků cfRule (§18.3.1.10), a každé pravidlo nese type, volitelný operator, priority, příznak stopIfTrue, jednu nebo dvě položky formula a u vizuálních rodin sadu prahů cfvo. Každá z nich věrně popisuje, co uživatel nastavil, a žádná z nich není algoritmus. U poloviny typů pravidel tato mezera nevadí: cellIs s operator="greaterThan" znamená větší než, a containsText znamená, že podřetězec je přítomen. Mezera se otevírá u agregačních rodin. Pravidlo top10 s rank="10" a percent="1" nad 27 vyplněnými číselnými buňkami zvýrazní kolik buněk? Dva celé sedm desetin není číslo. Zaokrouhlit, zaokrouhlit dolů, nebo nahoru — specifikace mlčí, a chybná volba znamená, že se váš PDF neshodne se sešitem, který má zákazník otevřený vedle

Pravidla pro jednu buňku a kde končí TCondFormatRule.Evaluate

HotXLS si nejprve vzal levnější polovinu. TCondFormatRule.Evaluate v lxCondFormat.pas, přidané ve verzi 2.199.0, odpovídá, zda se jedno pravidlo spustí pro jednu buňku, aniž by cokoli vědělo o zbytku rozsahu. Zpracovává osm porovnávacích operátorů BIFF za cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), volně formulovaná pravidla expression vyhodnocená v místě buňky tak, aby se relativní odkazy správně přepočítaly, čtyři textové predikáty a predikáty pro prázdné buňky a chyby. Prahy pocházejí z FFormula1 a FFormula2 vyhodnocených přes TXLSCalculator.GetRangeValue na pozici buňky, a obrácené meze se prohodí místo odmítnutí

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;

Poctivá stránka této metody je to, co odmítá odhadovat. top10, aboveAverage, belowAverage, duplicateValues a uniqueValues vracejí False, ne proto, že by byly obtížné, ale proto, že jsou z jedné buňky nerozhodnutelné — každé z nich potřebuje statistiku nad celou doménou. Čtyři vizuální rodiny, dataBar, colorScale2, colorScale3 a iconSet, vracejí False z jiného důvodu: nikdy neprodukují booleovskou hodnotu vůbec, produkují vykreslovací payload, a booleovský návratový typ je pro ně nesprávný tvar

Jak se vyhodnocovač na úrovni listu vyhne opakovanému skenování listu?

Tím, že spočítá každou sdílenou veličinu jednou, při konstrukci, a už nikdy víc. TXLSXConditionalFormatEvaluator v lxHandleX.pas je neměnný snímek pro jeden list, vytvořený přes TXLSXWorksheet.CreateConditionalFormatEvaluator, a celý jeho návrh je obranou proti naivní implementaci, kde každá vykreslovaná buňka spustí plné skenování rozsahu

V konstruktoru se dějí čtyři věci. Každý odlišný víceoblastní sqref je zpracován přesně jednou do TXlsxCfRangeSnapshot, takže deset pravidel sdílejících jeden rozsah sdílí jedno zpracování a jeden průchod statistikami. Tento průchod v jediném procházení streamuje průměr, populační směrodatnou odchylku, minimum a maximum přes vyplněné buňky a pořadové číselné pole si ponechá pouze tehdy, když jej pravidlo Top/Bottom nebo percentilu skutečně potřebuje. Klíče pro duplicitu a jedinečnost se sestavují bezpečně pro Unicode a řadí se dávkově jednou místo při každém vyhledávání. Poté se osa řádků rozřeže na pásma na každé hranici oblasti, takže EvaluateCell binárně vyhledává pásmo a navštíví pouze pravidla, jejichž rozsahy mohou daný řádek vůbec zasáhnout

Čtvrtá věc má ve velkém měřítku největší význam. Relativní vzorec pravidla jako =A1>AVERAGE($A$1:$A$100) znamená v každé buňce domény něco jiného, a zjevná implementace by pro každou buňku kompilovala nový syntaktický strom. TXlsxCfRulePlan jej kompiluje jednou a znovu vyhodnocuje tentýž strom přes vratné posuny souřadnic, čímž zachovává chování ukotvení Excelu bez alokace syntaktického stromu na buňku. Pravidla se pak vrství podle priority, a shoda na pravidle, jehož StopIfTrue je nastaveno, přeruší smyčku, přesně tak, jak Excel zkracuje vyhodnocení

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;

Jak Excel skutečně zaokrouhluje pravidlo Top 10 procent?

Zaokrouhluje dolů, s minimem jedna, a zahrnuje shody na hranici. Nikde v ISO 29500-1 to není napsáno — bylo to zjištěno sondováním Excelu 16 pomocí ručně sestavených sešitů a zpětným čtením, které buňky aplikace zvýraznila. HotXLS implementuje přesně toto: počet zařazených je Floor(Count * Min(Rank, 100) / 100), zvýšený na 1, pokud vyjde nula, oříznutý na počet vyplněných buněk, a hraniční hodnota se pak porovnává pomocí >=, takže každá buňka rovná hranici je zvýrazněna, i když to překročí požadovaný počet. Dvacet sedm hodnot a pravidlo 10 procent zvýrazní dvě buňky, plus jakékoli další buňky vázané se druhou v pořadí

Pravidla nadprůměru skrývala druhou nejednoznačnost: aboveAverage s stdDev="1" vybírá buňky o jednu směrodatnou odchylku nad průměrem, ale výběrová a populační odchylka se liší o Besselovu korekci a na malých rozsazích se viditelně rozcházejí — a přesně tam se podmíněné formátování používá. Excel 16 používá populační odchylku a HotXLS ji napodobuje, s příznakem equalAverage, který dělá striktní porovnání inkluzivní jen tehdy, když není v hře žádné pásmo odchylky. Pravidla pro duplicity a jedinečnost se místo toho spoléhají na identitu klíče. Drží-li jedna buňka číslo 100 a druhá text „100", Excel je považuje za stejný duplicitní klíč, takže HotXLS normalizuje číselný text do prostoru číselných klíčů místo porovnávání syrových řetězců. Prázdné buňky jsou zrcadlovým případem: skutečně prázdná buňka se počítá do celkového součtu rozsahu, ale sama se nestyluje, takže prázdné buňky ve sloupci se nerozsvítí navzájem jako duplicity

Barevné škály a sady ikon: interpolace a hraniční pravidla

Vizuální rodiny se vyhodnocují na čísla připravená k vykreslení, ne na booleovské hodnoty, a jejich okrajové chování bylo stanoveno stejným způsobem. U barevné škály s explicitními číselnými prahy HotXLS ořízne poziční zlomek do uzavřeného intervalu nula až jedna, poté interpoluje po kanálech s useknutím místo zaokrouhlování — hodnota pod minimálním prahem dostane minimální barvu místo extrapolované, tříbodová škála vybírá svou dvojici porovnáním se středním prahem a degenerovaná škála, jejíž oba konce nesou stejný práh, se zhroutí na horní barvu místo dělení nulou. Sady ikon vyžadovaly opačný typ opatrnosti, protože každý cfvo po prvním nese vlastní přísnost porovnání: HotXLS čte ThresholdEqualsInclude pro každý práh a podle toho aplikuje >= nebo >, prochází směrem nahoru, takže nejvyšší splněný práh vyhrává index ikony. Obrácená sada převrací vyhodnocený index, ne prahy, přepsání jednotlivé ikony může vzít glyf z jiné rodiny a jakýkoli neplatný práh přeruší pravidlo, místo aby vytvořil věrohodně vypadající, ale chybnou ikonu

Napájení mřížky, exportu do HTML a PDF z jednoho výsledku

Protože EvaluateCell vrací plně vyhodnocený TXLSXCfCellResult — diferenciální barvu výplně a fontu s už aplikovaným odstínem motivu, tučné, kurzívu, podtržení, id číselného formátu, směrové kladné a záporné rozsahy pruhu, pozici osy, rodinu ikon a index — každý konzument čte stejný záznam a žádný z nich nemusí rozumět vnitřnostem pravidel. HotXLS používá tuto jednu cestu pro export do HTML, export do PDF i interaktivní prohlížeč, což je jediný praktický způsob, jak zabránit, aby se tři renderery od sebe odchýlily. Verze 2.210.0 to zapojila do TXLSWorkbookViewer, který ukládá jeden připravený vyhodnocovač na aktivní list do cache a znovu jej používá při rolování, výběru a překreslení, a uvolní jej při změně sešitu nebo listu — přestavování snímku při každém Paint by zmařilo celý návrh postavený na konstrukčním čase. Tato cache je také důvodem, proč existuje TXLSWorkbookViewer.RefreshConditionalFormats: snímek je neměnný, takže pokud připojený sešit upravíte na místě, souhrnné statistiky a vyhodnocené prahy jsou zastaralé, dokud tuto metodu 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

Co za vás vyhodnocovač neudělá

Za zmínku stojí otevřeně tři hranice. Klasické jednobuňkové TCondFormatRule.Evaluate a vyhodnocovač na úrovni listu TXLSXConditionalFormatEvaluator jsou různá rozhraní s různými schopnostmi, a to jednobuňkové záměrně odmítá agregační a vizuální rodiny místo toho, aby je aproximovalo — potřebujete-li Top/Bottom nebo barevnou škálu, postavte si vyhodnocovač. Relativní časová období závisí na hodinách stroje v okamžiku vyhodnocení, takže pravidlo timePeriod se v PDF vygenerovaném dnes vykreslí jinak než v tom vygenerovaném příští týden, což je správné chování, a přesto potenciální tiket podpory, pokud se od vašeho archivu očekává bajtová stálost. Třetí hranice je spíš gramatická než technická: gramatika vzorců podmíněného formátu zakazuje strukturované odkazy na tabulky, takže pravidlo nemůže adresovat sloupec tabulky jménem tak, jak to umí vzorec listu, a to je omezení formátu, ne implementace

Pokud stavíte výstup reportů, exportní pipeline nebo vlastní mřížku, která se musí shodovat s Excelem buňku po buňce, stejný vyhodnocený výsledek pohání i vlastní VCL mřížku pro tabulkové procesory popsanou jinde na tomto blogu. Kompletní dokumentace API, model pravidel a zkušební stahování komponenty HotXLS pro Delphi jsou k dispozici na stránce produktu