Podmíněné formátování, RTF a styly buněk v Delphi s HotXLS

Pravidlo podmíněného formátování v OOXML jsou ve skutečnosti dvě oddělené věci nesoucí jedno jméno. Podmínka (porovnání, vzorec, shoda textu) rozhoduje, které buňky splňují kritéria. Vzhled (záznam diferenciálního formátu, v terminologii ECMA-376 dxf) rozhoduje, jak tyto buňky vypadají. Dialog Excelu tento šev skrývá tím, že vás donutí vyplnit obojí najednou. HotXLS ne. Vytvořte pravidlo cellIs z Delphi a vynechte styl, a pravidlo je platné, rozsah je správný, vzorec se vyhodnotí jako true přesně na těch správných buňkách, a přesto se nezmění barva ničeho, protože instrukce pravidla zněla „true, nekresli nic". Tato mezera mezi podmínkou a důsledkem je první věc, kterou je třeba udělat správně, a stojí za většinou pravidel, která v dialogu Manage Rules vypadají správně, a přesto nic nezvýrazní

HotXLS zapisuje podmíněné formátování nativně do souborů BIFF8 .xls i OOXML .xlsx, a totéž dělá pro úseky formátovaného textu (rich text runs) a poolovaný model stylů buněk. Tyto tři funkce sdílejí víc vnitřního propojení, než naznačuje ploché povrchové API, a místa, kde se výstup odchyluje od záměru, bývají zpravidla ve spojích mezi nimi

Podmínka potřebuje důsledek: styl dxf

Na listu XLSX pocházejí porovnávací pravidla z AddConditionalFormat, která přijímá rozsah, operátor z TXLSXCfOperator a vzorec nebo literál, a poté vrátí index nového pravidla uvnitř kolekce ConditionalFormats daného listu. Objekt pravidla na tomto indexu zpřístupňuje vlastnost Style, a právě tam žije zvýraznění. Nastavíte na ní výplň a vyhovující buňky výplň dostanou. Necháte ji nedotčenou a postavili jste neviditelné pravidlo popsané výše

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

    // Záporná odchylka: světle červená výplň
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Duplicitní ID objednávek se označí stejným způsobem
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Vlastní pravidlo se vzorcem: zvýrazní řádky, kde skutečnost nedosahuje 90 % cíle
    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;

Barvy jsou tu 32bitové hodnoty ARGB, takže $FFFFC7CE je to „světle červené", které znáte z dialogu Excelu, s plně neprůhledným bajtem alfa umístěným před RGB. Každý typ pravidla, které se spouští na základě podmínky u jednotlivé buňky, se řídí stejným tvarem vytvoř-a-pak-nastyluj. Textové porovnávače (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) vrací index, který stylujete dodatečně, a totéž platí pro AddCondFormatTop10, AddCondFormatAboveAverage a detektory prázdných buněk a chyb. Naučte se ten vzor jednou a celá rodina textových a porovnávacích pravidel se chová stejně

Datové pruhy, barevné škály a sady ikon se malují samy

Vizuální typy pravidel fungují opačně. Nesou svůj vzhled přímo v definici pravidla a vlastnost Style úplně ignorují. Přiřaďte výplň k pravidlu datového pruhu a nic se nestane, což zprvu působí jako chyba, dokud vám nedojde taxonomie: AddCondFormatDataBar přebírá barvu pruhu jako přímý argument, dvou- a tříbodové barevné škály přebírají barvy svých koncových bodů stejným způsobem a AddCondFormatIconSet vybírá jeden z 26 typů sad ikon, jako je icsTrafficLights3. Tady není žádný samostatný záznam stylu, na který by se dalo zapomenout, protože žádný samostatný záznam stylu vůbec neexistuje

Parametry, nad kterými se v těchto voláních vyplatí přemýšlet, jsou kotvy hodnot, typované jako TXLSCfValueKind. Koncový bod pruhu nebo škály může sedět na minimu nebo maximu rozsahu, na doslovném čísle, na procentu nebo percentilu, nebo na výsledku vzorce. Výchozí hodnoty, min-of-range a max-of-range, se chovají slušně na upravených ukázkových datech a pak vás zradí na reálných datech s odlehlými hodnotami: jedna vymykající se hodnota roztáhne škálu a zploští každý ostatní pruh na pahýl. Pokud má být dashboard čitelný napříč obdobími, zakotvěte koncové body raději na pevných číslech nebo percentilech, aby polovina pruhu v březnu znamenala stejné množství jako polovina pruhu v dubnu. Automaticky škálovaný pruh je porovnatelný jen sám se sebou

Zapisovač XLS pokrývá čtyři typy pravidel, nic víc

Starší strana BIFF8 není zmenšeným zrcadlem strany XLSX; je to záměrná podmnožina. Fasáda XLS umí vytvořit přesně čtyři tvary podmíněných pravidel, datové pruhy, dvoubarevné škály, tříbarevné škály a sady ikon, vydávané do proudu jako záznamy CF12. Nemá žádné API pro vytváření pravidel cellIs, výrazových ani textových pravidel. Pravidla těchto typů, která už žijí v souboru, jenž otevřete, se přečtou, zachovají a zapíší zpět beze změny, takže otevření a opětovné uložení zákaznického .xls nikdy nepoškodí formátování, které v sobě nese. Co dělat nemůžete, je vygenerovat prahové zvýraznění od nuly do .xls. Možnosti tam jsou buď to napodobit obyčejnými výplněmi buněk vypočítanými v kódu, nebo udělat výstupem .xlsx, kde je k dispozici celá rodina pravidel

Toto je omezení, které je třeba vyřešit dřív, než datová vrstva vznikne, ne až potom, protože mění rozhodnutí o formátu souboru pro cokoli ve tvaru dashboardu. Tým, který zvolil .xls kvůli kompatibilitě a poté navrhne KPI report s prahy cellIs, si zvolil dvě věci, které k sobě nepasují, a levnější okamžik na to přijít je při rozhodování o formátu, ne tři týdny hluboko ve stavbě

Skládání pravidel, priorita a překrývající se rozsahy

Reálné dashboardy zřídkakdy mají jen jedno pravidlo na rozsah. Sloupec s odchylkou může nést datový pruh pro velikost, pravidlo cellIs pro pevný práh a nad oběma ještě výrazové pravidlo na úrovni řádku pro eskalace. Každý TXLSXConditionalFormat zpřístupňuje hodnotu Priority a Excel řeší soupeřící pravidla v pořadí podle priority. Když dvě pravidla chtějí vymalovat stejnou buňku, o vítězi rozhoduje číslo, které nastavíte vy, ne pořadí, ve kterém recenzent náhodou proscrolluje dialogem Manage Rules

K prioritě přistupujte tak, jako kreslicí program přistupuje k pořadí vrstev (z-order). Nastavte ji záměrně všude tam, kde se dvě pravidla mohou dotknout stejných buněk, a mezi hodnotami nechávejte mezery, aby se pozdější pravidlo dalo zasunout bez přečíslování ostatních. Tam, kde se pravidla nemohou střetnout, řekněme datový pruh omezený na sloupec E a textové pravidlo omezené na sloupec G, je pořadí vytváření v pořádku a priorita nestojí za pozornost. Tu pozornost věnujte raději hranicím rozsahů, protože nákladné chyby zde skoro nikdy nejsou inverze priority. Jsou to rozsahy jako B2:B200 u reportu, který narostl na 350 řádků, kde se nepokrytý ocas vykreslí jako obyčejné buňky, jež vypadají přesně jako zdravá data. Odvoďte každý rozsah pravidla ze stejné hodnoty konečného počtu řádků, která jinde v sešitu řídí datové řady grafů a rozsahy validace, a ocas přestane odpadávat

Jeden ověřovací zvyk se vyplácí. Po vygenerování otevřete soubor v Excelu, vyberte formátovaný rozsah a při každé změně šablony jednou projděte Manage Rules. Podmíněné formátování je jedna z mála oblastí, kde jediným autoritativním vykreslovačem je aplikace, která soubor konzumuje, takže jednotkový test nad XML dokazuje, že bylo pravidlo zapsáno, ne že jej Excel vykreslí tak, jak jste zamýšleli. Minuta okometrické kontroly tuto mezeru uzavře

Rich text: mnoho formátů uvnitř jedné buňky

Buňka s rich textem drží v modelu XLSX seznam úseků (runs), kde je každý úsek rozsah textu plus jeho vlastní atributy písma. Seznam si sestavíte stranou jako objekt TXLSXRichText, přidáte do něj úseky a pak celou věc připojíte k buňce. Kousek, který kouše, je pravidlo vlastnictví. Přiřazení do Cell.RichText předá vlastnictví tohoto objektu buňce a buňka jej uvolní během vlastní destrukce. Uvolníte-li jej ještě sami, máte dvojité uvolnění (double-free), takové, které mlčí po celý běh, jenž jej způsobil, a projeví se jako pád někde úplně jinde a mnohem později

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;   // vlastnictví přechází na buňku: nevolejte Free
end;

Explicitní ColorIsAuto := False není volitelná ozdoba. Úsek nese příznak automatické barvy a přiřazení barvy se respektuje až po vymazání tohoto příznaku. Nastavíte Color a zapomenete na ColorIsAuto, a úsek vyjde tučný, ale tvrdohlavě černý, bez jakékoli chyby, která by na příčinu ukázala. Úseky také podporují přeškrtnutí, varianty podtržení a svislé zarovnání pro horní a dolní index, zatímco PlainText zplošťuje celý seznam zpět na jediný řetězec, když potřebujete exportovat nebo porovnat textový obsah

Rich text na úrovni buňky je záležitost výhradně XLSX. Fasáda XLS nemá žádné veřejné API pro jeho zápis, i když úseky jsou tam dostupné u komentářů a textových polí přes TextRuns, a rich stringy načtené z existujícího .xls přežijí cyklus tam a zpět nedotčené. Vodítko je stejné jako u podmíněného formátování: cokoli, co uvnitř buňky míchá formáty, patří do zapisovače XLSX

Pool stylů a chyba o jedna, která se dostane do produkce

Obyčejné stylování buněk v modelu XLSX prochází přes poolované kolekce na sešitu. Fonts.Add, Fills.AddSolid a Borders.Add každá zaregistruje definici a vrátí její index v poolu. Tyto indexy jsou 0-based. Vlastnosti na straně buňky, které je konzumují, jako FontIndex, rezervují 0 pro „výchozí", takže hodnota, kterou přiřadíte buňce, je index v poolu plus jedna:

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

Vynechte + 1 a každé záhlaví spadne zpět na výchozí písmo. Není žádná výjimka ani varování, jen sešit, který vypadá, jako by ho nikdo nestyloval. Chyba druhého řádu se skrývá ve smyčce: volání Fonts.Add jednou na řádek. Identické definice písma se deduplikují, takže soubor není poškozený, ale práce je zbytečná, a zejména pool zarovnání vrací při každém volání nový objekt místo slučování duplicit. Postavte hrstku stylů jednou před smyčkou a jejich indexy pak jen znovu použijte. U reportů se sto tisíci řádky je právě tato jediná změna jednou z pák popsaných v článku o ladění výkonu velkých sešitů v HotXLS. Když potřebujete jen běžný sémantický vzhled, obě fasády zpřístupňují na rozsazích ApplyBuiltinStyle, které mapuje na vestavěné styly Excelu Good, Bad, Neutral a akcentové styly, aniž byste se poolů vůbec dotkli

Podmíněné formátování, rich text a poolované styly jsou poslední míle reportu, aplikovaná až poté, co jsou datový model a rozvržení usazené, a těmto dřívějším fázím se věnuje článek o generování reportů ze šablon v HotXLS. Kompletní referenci pravidel, úseků a stylů najdete na stránce produktu HotXLS Delphi Component