Pogojno oblikovanje in bogato besedilo HotXLS v Delphiju

Pravilo za pogojno oblikovanje v formatu OOXML sta dve ločeni zadevi pod enim imenom. Pogoj (primerjava, formula, ujemanje besedila) določa, katere celice izpolnjujejo pogoje. Izgled (zapis diferencialne oblike oz. dxf v terminologiji ECMA-376) pa določa, kako so te celice videti. Excelovo pogovorno okno ta šiv skrije tako, da zahteva vnos obeh hkrati. HotXLS tega ne počne. Če v Delphiju ustvarite pravilo cellIs in izpustite slog, bo pravilo veljavno, obseg pravilen, formula pa bo pravilno ovrednotena kot true za točno določene celice, vendar se barva ne bo spremenila. Navodilo pravila je namreč bilo "če je res, ne pobarvaj ničesar." Ta razkorak med pogojem in posledico je prva stvar, ki jo je treba pravilno razumeti, saj pojasnjuje večino pravil, ki so v upravitelju pravil videti pravilna, a ničesar ne označijo

HotXLS izvorno zapisuje pogojno oblikovanje v datoteke BIFF8 .xls in OOXML .xlsx ter počne enako za nize obogatenega besedila (rich text runs) in model združenih slogov celic. Te tri funkcije si delijo več skupnih povezav, kot kaže preprost API, mesta, kjer se izpis razlikuje od namena, pa so ujamejo v stike med njimi

Pogoj potrebuje posledico: slog dxf

Na delovnem listu XLSX pravila za primerjavo izhajajo iz funkcije AddConditionalFormat, ki sprejme obseg, operator iz TXLSXCfOperator ter formulo ali literal, nato pa vrne indeks novega pravila v zbirki ConditionalFormats delovnega lista. Objekt pravila na tem indeksu izpostavlja lastnost Style, kjer se nahaja označitev. Nastavite polnilo na njem in celice, ki izpolnjujejo pogoje, bodo prevzele to polnilo. Če lastnost pustite nedotaknjeno, ste ustvarili nevidno pravilo, opisano zgoraj

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;

Barve so tukaj 32-bitne vrednosti ARGB, zato je $FFFFC7CE Excelova 'svetlo rdeča' barva, ki jo poznate iz pogovornega okna, s popolnoma neprosojnim bajtom alfa pred vrednostjo RGB. Vsaka vrsta pravila, ki se sproži na podlagi pogoja celice, sledi enakemu vzorcu ustvari-in-oblikuj. Funkcije za ujemanje besedila (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) vrnejo indeks, ki ga nato oblikujete, enako pa velja za AddCondFormatTop10, AddCondFormatAboveAverage ter detektorje praznih celic in napak. Ko usvojite ta vzorec, celotna družina primerjav in besedil deluje enako

Podatkovne vrstice, barvne lestvice in nabori ikon se pobarvajo sami

Vizualna pravila delujejo ravno obratno. Svoj izgled nosijo znotraj definicije pravila in popolnoma prezrejo lastnost Style. Če dodelite polnilo pravilu podatkovne vrstice, se ne bo zgodilo nič, kar je videti kot napaka, dokler ne razumete razvrstitve: AddCondFormatDataBar sprejme barvo vrstice kot neposreden parameter, dvo- in trotočkovne barvne lestvice prav tako sprejmejo barve svojih končnih točk, AddCondFormatIconSet pa izbere enega od 26 tipov nabora ikon, kot je icsTrafficLights3. Tukaj ni ločenega zapisa sloga, ki bi ga lahko pozabili, saj ločen slog sploh ne obstaja

Parametri, o katerih velja razmisliti pri teh klicih, so sidra vrednosti tipa TXLSCfValueKind. Končna točka vrstice ali lestvice se lahko nahaja na minimumu ali maksimumu obsega, pri določeni številki, odstotku, percentilu ali rezultatu formule. Privzete nastavitve (minimum in maksimum obsega) delujejo pri urejenih predstavitvenih podatkih, nato pa odpovejo pri realnih podatkih z izstopajočimi vrednostmi: ena izjemno visoka vrednost raztegne lestvico in splošči vse ostale vrstice v kratke črte. Ko je nadzorna plošča namenjena primerjavi med obdobji, raje sidrajte končne točke na določene številke ali percentile, da bo polovična vrstica v marcu pomenila enako količino kot polovična vrstica v aprilu. Samodejno prilagojena vrstica je primerljiva le sama s seboj

Pisatelj XLS pokriva le štiri vrste pravil in nič več

Starejši vmesnik BIFF8 ni pomanjšano ogledalo različice XLSX, temveč premišljen podnabor. Vmesnik XLS lahko ustvari natanko štiri oblike pogojnih pravil: podatkovne vrstice, dvobarvne lestvice, trobarvne lestvice in nabore ikon, ki jih v tok zapiše kot zapise CF12. Nima pa API-ja za ustvarjanje pravil cellIs, izrazov ali pravil za besedilo. Pravila teh vrst, ki že obstajajo v odprti datoteki, se preberejo, ohranijo in zapišejo nazaj nespremenjena, tako da odpiranje in ponovno shranjevanje strankine datoteke .xls nikoli ne poškoduje obstoječega oblikovanja. Ne moči pa ustvariti pogojnega označevanja pragov na novo v datoteki .xls. V tem primeru se morate odločiti za simulacijo z običajnimi polnili celic, izračunanimi v kodi, ali pa za izvoz v formatu .xlsx, kjer je na voljo celotna družina pravil

To je omejitev, ki jo je treba določiti pred vzpostavitvijo podatkovne plasti in ne pozneje, saj vpliva na odločitev o formatu datoteke za vse nadzorne plošče. Ekipa, ki je zaradi združljivosti izbrala format .xls, nato pa načrtuje poročilo KPI s pragovi cellIs, je izbrala dve stvari, ki ne sodita skupaj. Čas, ko je to najlažje in najceneje opaziti, je ob izbiri formata in ne tri tedne po začetku gradnje

Zlaganje pravil, prioriteta in prekrivajoči se obsegi

Realne nadzorne plošče redko izvajajo le eno pravilo na obseg. Stolpec odstopanja lahko vsebuje podatkovno vrstico za magnitudo, pravilo cellIs za fiksni prag in pravilo izraza na ravni vrstice nad obema za primere eskalacije. Vsak TXLSXConditionalFormat izpostavlja vrednost Priority, Excel ova konkurenčna pravila razrešuje po vrstnem redu prioritete. Ko želita dve pravili pobarvati isto celico, zmagovalca določi številka, ki jo nastavite, in ne vrstni red, ki ga ocenjevalec vidi v pogovornem oknu za upravljanje pravil

Prioriteto obravnavajte na enak način kot program za risanje obravnava vrstni red z-order. Dodelite jo načrtno povsod, kjer lahko dve pravili dosežejo iste celice, in pustite vrzeli med vrednostmi, da lahko poznejše pravilo vstavite brez spreminjanja ostalih številk. Kjer pravila ne movie trčiti (npr. podatkovna vrstica v stolpcu E in pravilo besedila v stolpcu G), je vrstni red ustvarjanja povsem v redu in prioriteta ne zahteva posebne pozornosti. To pozornost raje usmerite na meje obsega, saj najdražje napake tukaj skoraj nikoli niso zamenjave prioritet. Gre za obsege, kot je B2:B200 v poročilu, ki je zraslo na 350 vrstic, kjer se nezaščiten konec izriše kot navadne celice, ki so videti enako kot zdravi podatki. Vsak obseg pravila izpeljite iz iste končne vrednosti števila vrstic, ki poganja serije grafikonov in obsege validacij drugje v delovnem zvezku, pa se konec ne bo več izgubil

Ena navada preverjanja se vsekakor obrestuje. Po generiranju odprite datoteko v Excelu, izberite oblikovano območje in preverite upravljanje pravil ob vsaki spremembi predloge. Pogojno oblikovanje je eno redkih področij, kjer je edini avtoritativni upodabljalnik aplikacija, ki datoteko porabi, zato preizkus enote nad XML-jem dokazuje le, da je bilo pravilo zapisano, ne pa, da ga Excel izriše tako, kot ste si zamislili. Minuta vizualnega pregleda odpravi ta razkorak

Bogato besedilo: več formatov znotraj ene celice

Celica z bogatim besedilom v modelu XLSX vsebuje seznam tekov (runs), kjer vsak tek predstavlja del besedila s svojimi lastnostmi pisave. Seznam zgradite ločeno kot objekt TXLSXRichText, nanj dodate teke in nato celotno stvar pripnete celici. Pravilo o lastništvu je tisti del, ki lahko povzroči težave. Dodelitev lastnosti Cell.RichText prenese lastništvo tega objekta na celico, celica pa ga sprosti ob lastnem uničenju. Če ga sprostite tudi sami, povzročite dvojno sprostitev (double-free), kar ob izvedbi običajno ostane neopaženo, pozneje pa povzroči sesutje programa na povsem nepovezanem mestu

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;

Eksplicitna nastavitev ColorIsAuto := False ni le okras. Tek vsebuje zastavico za samodejno barvo in dodelitev barve se upošteva šele, ko je ta zastavica očiščena. Nastavite Color in pozabite na ColorIsAuto, pa bo izpis krepak, a trmasto črn brez kakršne koli napake, ki bi opozorila na vzrok. Teki podpirajo tudi prečrtano besedilo, različice podčrtanega besedila ter navpično poravnavo za nadpisano in podpisano besedilo, lastnost PlainText pa splošči celoten seznam nazaj v en niz, ko morate izvoziti ali primerjati vsebino besedila

Obogateno besedilo na ravni celice je na voljo le v formatu XLSX. Vmesnik XLS nima javnega API-ja za njegovo pisanje, čeprav so teki tam na voljo pri komentarjih in besedilnih poljih prek TextRuns, obogateni nizi, prebrani iz obstoječe datoteke .xls, pa preživijo pretvorbo nespremenjeni. Pravilo je enako kot pri pogojnem oblikovanju: vse, kar meša formate znotraj posamezne celice, sodi v pisatelj XLSX

Zbirka slogov in napaka za ena, ki se pojavi v produkciji

Običajno oblikovanje celic v modelu XLSX poteka prek združenih zbirk (pools) v delovnem zvezku. Metode Fonts.Add, Fills.AddSolid in Borders.Add registrirajo definicijo in vrnejo njen indeks v zbirki. Ti indeksi so osnovani na 0. Lastnosti na strani celic, ki jih porabljajo (npr. FontIndex), pa vrednost 0 rezervirajo za 'privzeto', zato je vrednost, ki jo dodelite celici, indeks zbirke plus ena:

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

Izpustite + 1 in vsaka glava se bo vrnila na privzeto pisavo. Brez izjeme in opozorila, dobili boste le delovni zvezek, ki je videti neoblikovan. Druga pogosta napaka se skriva v zanki: klicanje Fonts.Add enkrat za vsako vrstico. Enake definicije pisav se odstranijo (deduplicate), zato datoteka ni poškodovana, vendar je delo izgubljeno, zbirka poravnav (alignment pool) pa ob vsakem klicu vrne nov objekt, namesto da bi združevala dvojnike. Pred zanko enkrat zgradite nekaj slogov in nato ponovno uporabite njihove indekse. Pri poročilih s sto tisoč vrsticami je ta sprememba eden od ključev, opisanih v članku prilagajanje zmogljivosti velikih delovnih zvezkov v HotXLS. Če potrebujete le standardni semantični izgled, oba vmesnika ponujata metodo ApplyBuiltinStyle na obsegih, ki se preslika v Excelove vgrajene sloge (Good, Bad, Neutral in poudarke) brez dotikanja samih zbirk

Pogojno oblikovanje, obogateno besedilo in združeni slogi so zadnji del poročila, ki se uveljavi po tem, ko so podatkovni model in postavitve že določeni. Ta zgodnejša obdobja pa so tema članka generiranje poročil na podlagi predlog s HotXLS. Celotna referenca pravil, tekov in slogov je na voljo na strani izdelka HotXLS Component