Podmienené formátovanie a formátovaný text v Delphi cez HotXLS

Pravidlo podmieneného formátovania v OOXML pozostáva z dvoch samostatných vecí spojených pod jedným názvom. Podmienka (porovnanie, vzorec, zhoda textu) určuje, ktoré bunky spĺňajú kritériá. Vzhľad (záznam rozdielového formátu, v termínoch ECMA-376 označovaný ako dxf) určuje, ako tieto bunky vyzerajú. Dialógové okno v Exceli tento prechod skrýva tým, že vás núti vyplniť obe časti naraz. HotXLS to však nerobí. Ak v Delphi vytvoríte pravidlo typu cellIs a vynecháte štýl, pravidlo bude platné, rozsah správny, vzorec sa vyhodnotí ako pravdivý pre presne tie správne bunky, ale nič nezmení farbu, pretože inštrukcia pravidla znela „pravda, nevyfarbuj nič“. Táto medzera medzi podmienkou a následkom je prvá vec, ktorú musíte vyriešiť správne, a vysvetľuje väčšinu pravidiel, ktoré vyzerajú správne v správcovi pravidiel (Manage Rules), ale napriek tomu nič nezvýrazňujú.

HotXLS zapisuje podmienené formátovanie natívne do súborov BIFF8 .xls aj OOXML .xlsx, a to isté platí pre segmenty formátovaného textu (rich text runs) a zdieľaný model štýlov buniek. Tieto tri funkcie zdieľajú viac spoločného prepojenia, než by naznačovalo ploché rozhranie API, a miesta, kde sa výstup líši od zámeru, sú zvyčajne práve spojenia medzi nimi.

Podmienka potrebuje následok: dxf štýl

Na hárku XLSX pochádzajú porovnávacie pravidlá z metódy AddConditionalFormat, ktorá prijíma rozsah, operátor z TXLSXCfOperator a vzorec alebo literál, a potom vracia index nového pravidla v kolekcii ConditionalFormats daného hárka. Objekt pravidla na tomto indexe sprístupňuje vlastnosť Style, a to je miesto, kde sa nachádza zvýraznenie. Nastavte na ňom výplň a bunky, ktoré spĺňajú podmienku, túto výplň prevezmú. Ak ho necháte nedotknutý, vytvorili ste neviditeľné pravidlo popísané vyššie.

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

    // Negative variance: light red fill
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Duplicate order IDs get flagged the same way
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Custom formula rule: highlight rows where actual misses 90% of 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;

Farby sú tu 32-bitové hodnoty ARGB, takže $FFFFC7CE je svetločervená farba z Excelu, ktorú poznáte z dialógového okna, kde plne nepriehľadný alfa bajt stojí pred zložkami RGB. Každý typ pravidla, ktorý sa spúšťa na základe podmienky pre konkrétnu bunku, sa riadi rovnakým vzorom „vytvoriť a potom naštýlovať“. Textové porovnávače (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) vracajú index, ktorý následne naštýlujete, a to isté platí pre metódy AddCondFormatTop10, AddCondFormatAboveAverage a detektory prázdnych hodnôt alebo chýb. Osvojte si tento vzor raz a celá skupina textových a porovnávacích pravidiel sa bude správať rovnako.

Dátové pruhy, farebné škály a sady ikon sa vykresľujú samy

Vizuálne typy pravidiel fungujú opačne. Nesú svoj vzhľad priamo v definícii pravidla a vlastnosť Style úplne ignorujú. Priradenie výplne pravidlu dátového pruhu nič neurobí, čo môže vyzerať ako chyba, kým nepochopíte ich klasifikáciu: AddCondFormatDataBar prijíma farbu pruhu ako priamy argument, dvoj- a trojbodové farebné škály prijímajú svoje koncové farby rovnakým spôsobom a AddCondFormatIconSet vyberá jeden z 26 typov sád ikon, napríklad icsTrafficLights3. Tu neexistuje žiadny samostatný záznam štýlu, na ktorý by ste mohli zabudnúť, pretože tu vôbec žiadny samostatný záznam štýlu nie je.

Parametre, nad ktorými sa oplatí pri týchto volaniach premýšľať, sú ukotvenia hodnôt (value anchors) typu TXLSCfValueKind. Koncový bod pruhu alebo škály sa môže nachádzať na minime alebo maxime rozsahu, na konkrétnom čísle, percente, percentile alebo na výsledku vzorca. Predvolené hodnoty (minimum a maximum rozsahu) fungujú dobre na ukážkových dátach, ale pri reálnych dátach s extrémnymi odchýlkami zlyhajú: jedna výrazná hodnota roztiahne škálu a sploští každý iný pruh na minimum. Ak je ovládací panel určený na čítanie v rôznych obdobiach, ukotvite koncové body radšej k fixným číslam alebo percentilom, aby polovičný pruh v marci vyjadroval rovnaké množstvo ako polovičný pruh v apríli. Automaticky škálovaný pruh je porovnateľný iba sám so sebou.

Zapisovač XLS podporuje iba štyri typy pravidiel

Staršia strana BIFF8 nie je len menším zrkadlom strany XLSX; je to jej zámerný podmnožina. Rozhranie XLS dokáže vytvoriť presne štyri typy podmienených pravidiel: dátové pruhy, dvojfarebné škály, trojfarebné škály a sady ikon, ktoré sa zapisujú do streamu ako záznamy CF12. Nemá žiadne API na vytváranie pravidiel typu cellIs, výrazov alebo textových pravidiel. Pravidlá týchto typov, ktoré sa už nachádzajú v otváranom súbore, sa prečítajú, zachovajú a zapíšu späť nezmenené, takže otvorenie a opätovné uloženie zákazníckeho súboru .xls nikdy nepoškodí formátovanie, ktoré obsahoval. Čo však nemôžete urobiť, je generovať prahové zvýraznenie od nuly do súboru .xls. Možnosťami sú buď ho simulovať bežnými výplňami buniek vypočítanými v kóde, alebo zvoliť formát .xlsx, kde je k dispozícii kompletná rodina pravidiel.

Toto je obmedzenie, ktoré je potrebné vyriešiť predtým, ako vznikne dátová vrstva, a nie potom, pretože mení rozhodnutie o formáte súboru pre čokoľvek, čo pripomína ovládací panel. Tím, ktorý si vybral formát .xls kvôli kompatibilite a potom navrhne KPI report s prahmi cellIs, si zvolil dve veci, ktoré k sebe nepasujú, a je oveľa lacnejšie si to všimnúť pri rozhodovaní o formáte než tri týždne po začatí vývoja.

Vrstvenie pravidiel, priorita a prekrývajúce sa rozsahy

Reálne ovládacie panely málokedy spúšťajú iba jedno pravidlo na rozsah. Stĺpec odchýlok môže obsahovať dátový pruh pre vyjadrenie veľkosti, pravidlo cellIs pre pevný prah a nad oboma ešte pravidlo výrazu na úrovni riadka pre eskalácie. Každý objekt TXLSXConditionalFormat vystavuje hodnotu Priority a Excel rieši konfliktné pravidlá v poradí podľa priority. Keď chcú dve pravidlá vyfarbiť rovnakú bunku, o víťazovi rozhoduje číslo, ktoré nastavíte, a nie poradie, v akom ich recenzent vidí v dialógovom okne Správca pravidiel.

Pristupujte k priorite tak, ako kresliaci program pristupuje k poradiu vykresľovania z-order. Priraďte ju zámerne všade tam, kde môžu dve pravidlá zasiahnuť rovnaké bunky, a nechajte medzi hodnotami medzery, aby sa neskoršie pravidlo dalo vložiť bez prečíslovania ostatných. Tam, kde sa pravidlá nemôžu stretnúť (napríklad dátový pruh obmedzený na stĺpec E a textové pravidlo obmedzené na stĺpec G), stačí poradie vytvorenia a priorite netreba venovať pozornosť. Venujte túto pozornosť radšej hraniciam rozsahu, pretože drahé chyby tu takmer nikdy nie sú spôsobené nesprávnou prioritou. Sú to rozsahy ako B2:B200 na reporte, ktorý sa rozrástol na 350 riadkov, kde nepokrytý koniec vyzerá ako obyčajné bunky so zdanlivo zdravými dátami. Odvoďte každý rozsah pravidiel od rovnakej hodnoty počtu riadkov, ktorá riadi dátové rady grafov a rozsahy overenia na iných miestach zošita, a konce reportov už nebudú chýbať.

Jeden zvyk pri overovaní sa skutočne vyplatí. Po vygenerovaní otvorte súbor v Exceli, vyberte naformátovaný rozsah a prejdite správcu pravidiel (Manage Rules) pri každej zmene šablóny. Podmienené formátovanie je jednou z mála oblastí, kde jediným rozhodujúcim vykresľovačom je aplikácia, ktorá súbor spracováva, takže unit test XML štruktúry dokazuje iba to, že pravidlo bolo zapísané, nie to, že ho Excel vykreslí tak, ako ste zamýšľali. Minúta vizuálnej kontroly túto medzeru spoľahlivo uzavrie.

Formátovaný text: viacero formátov v jednej bunke

Bunka s formátovaným textom (rich-text) v modeli XLSX obsahuje zoznam segmentov (runs), kde každý segment je úsek textu s vlastnými atribútmi písma. Tento zoznam si vytvoríte samostatne ako objekt TXLSXRichText, pridáte doň segmenty a potom celý objekt pripojíte k bunke. Pravidlo vlastníctva (ownership rule) je tu však zradné. Priradenie k vlastnosti Cell.RichText odovzdá vlastníctvo tohto objektu bunke a bunka ho uvoľní pri svojom vlastnom zničení. Ak ho uvoľníte aj vy sami v kóde, dôjde k chybe double-free (dvojité uvoľnenie pamäte), ktorá zostane tichá počas behu, ktorý ju spôsobil, ale neskôr sa prejaví ako pád programu na úplne inom mieste.

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;   // ownership moves to the cell: do not Free
end;

Explicitné nastavenie ColorIsAuto := False nie je len voliteľná ozdoba. Segment nesie príznak automatickej farby a priradenie farby sa rešpektuje až po zrušení tohto príznaku. Ak nastavíte vlastnosť Color a zabudnete na ColorIsAuto, segment bude síce tučný, ale zostane čierny bez akejkoľvek chyby, ktorá by poukázala na príčinu. Segmenty podporujú aj prečiarknutie, varianty podčiarknutia a vertikálne zarovnanie pre horný a dolný index, zatiaľ čo vlastnosť PlainText sploští celý zoznam späť na jediný reťazec, keď potrebujete exportovať alebo porovnať textový obsah.

Formátovaný text na úrovni buniek je podporovaný len vo formáte XLSX. Rozhranie XLS nemá žiadne verejné API na jeho zápis, hoci segmenty (runs) sú tam dostupné pre komentáre a textové polia cez TextRuns a formátované reťazce načítané z existujúceho súboru .xls prežijú konverziu bez poškodenia. Logika je rovnaká ako pri podmienenom formátovaní: čokoľvek, čo mieša rôzne formáty v rámci jednej bunky, patrí do zapisovača XLSX.

Kolekcia štýlov a chyba posunu o jedna

Bežné štýlovanie buniek v modeli XLSX prebieha cez zdieľané kolekcie v zošite. Metódy Fonts.Add, Fills.AddSolid a Borders.Add zaregistrujú definíciu a vrátia jej index v kolekcii. Tieto indexy začínajú od 0. Vlastnosti na strane bunky, ktoré ich konzumujú (napríklad FontIndex), si rezervujú hodnotu 0 pre predvolený stav (default), takže hodnota, ktorú priradíte bunke, je index v kolekcii plus jedna:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // pool index, 0-based
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // cell index, 1-based

Vynechajte + 1 a každé záhlavie sa vráti k predvolenému písmu. Nedôjde k žiadnej výnimke ani varovaniu, iba zošit bude vyzerať, akoby ho nikto nenaštýloval. Druhá chyba sa skrýva v cykle: volanie Fonts.Add pre každý riadok. Identické definície písiem sa síce deduplikujú, takže súbor sa nepoškodí, ale práca je zbytočná. Najmä kolekcia zarovnaní vracia pri každom volaní nový objekt namiesto spájania duplikátov. Vytvorte tých niekoľko málo štýlov raz pred cyklom a potom znova používajte ich indexy. Pri reportoch s viac ako stotisíc riadkami je táto jediná zmena jednou z pák popísaných v článku o optimalizácii výkonu veľkých zošitov v HotXLS. Keď potrebujete len štandardný sémantický vzhľad, obe rozhrania ponúkajú metódu ApplyBuiltinStyle na rozsahoch, ktorá mapuje bunky na vstavané štýly Excelu ako Dobrý (Good), Zlý (Bad), Neutrálny (Neutral) a prízvukové štýly bez toho, aby ste sa vôbec dotkli kolekcií štýlov.

Podmienené formátovanie, formátovaný text a zdieľané štýly sú posledným krokom pri tvorbe reportu, ktoré sa aplikujú po dokončení dátového modelu a rozvrhnutia. Tieto skoršie fázy sú témou článku o generovaní reportov na základe šablón v HotXLS. Celá referenčná príručka pre pravidlá, segmenty a štýly sa nachádza na produktovej stránke HotXLS Component.