HotXLS uslovno formatiranje i Rich Text u Delphi-ju

Pravilo uslovnog formatiranja u OOXML formatu čine dve odvojene stvari koje nose jedno ime. Uslov (poređenje, formula, podudaranje teksta) odlučuje koje ćelije se kvalifikuju. Izgled (zapis diferencijalnog formata, dxf u ECMA-376 terminologiji) odlučuje kako te ćelije izgledaju. Excel-ov dijalog krije taj šav primoravajući vas da popunite oba odjednom. HotXLS to ne radi. Kreirajte cellIs pravilo iz Delphi-ja i preskočite stil, i pravilo će biti validno, opseg ispravan, formula će se procenjivati kao tačna na tačno određenim ćelijama, ali ništa neće promeniti boju, jer je instrukcija pravila bila "ako je tačno, ne boj ništa". Taj jaz između uslova i posledice je prva stvar koju treba razumeti, i on je zaslužan za većinu pravila koja izgledaju ispravno u menadžeru pravila (Manage Rules), a ipak ništa ne ističu.

HotXLS prirodno upisuje uslovno formatiranje i u BIFF8 .xls i u OOXML .xlsx fajlove, a isto važi i za segmente bogatog teksta (rich text runs) i model stilova ćelija iz baze. Ove tri funkcije dele više zajedničkog koda nego što to jednostavni API sugeriše, a mesta gde se izlaz razlikuje od namere su obično spojevi između njih.

Uslov zahteva posledicu: dxf stil

Na XLSX radnom listu, pravila poređenja dolaze iz funkcije AddConditionalFormat, koja prihvata opseg, operator iz TXLSXCfOperator i formulu ili literal, a zatim vraća indeks novog pravila unutar kolekcije ConditionalFormats na listu. Objekat pravila na tom indeksu izlaže svojstvo Style, i tu leži definicija isticanja. Postavite ispunu (fill) na stil i ćelije koje ispunjavaju uslov će dobiti tu ispunu. Ostavite ga netaknutim i napravili ste nevidljivo pravilo opisano iznad.

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;

Boje su ovde 32-bitne ARGB vrednosti, pa je $FFFFC7CE ona Excel-ova "svetlocrvena" boja koju poznajete iz dijaloga, sa potpuno neprovidnim alfa bajtom ispred RGB-a. Svaka vrsta pravila koja se aktivira na osnovu uslova po ćeliji prati isti šablon: kreiraj-pa-stilizuj. Funkcije za pronalaženje teksta (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) vraćaju indeks koji naknadno stilizujete, a isto važi i za AddCondFormatTop10, AddCondFormatAboveAverage, kao i detektore praznih ćelija i grešaka. Naučite ovaj obrazac jednom i cela porodica pravila za tekst i poređenje ponašaće se isto.

Trake podataka, skale boja i setovi ikona boje sami sebe

Vizuelne vrste pravila funkcionišu obrnuto. Ona nose svoj izgled unutar same definicije pravila i potpuno ignorišu svojstvo Style. Dodelite ispunu pravilu za traku podataka (data bar) i ništa se neće dogoditi, što deluje kao bag sve dok ne razumete taksonomiju: AddCondFormatDataBar prihvata boju trake kao direktan argument, skale boja sa dve i tri tačke prihvataju boje svojih krajnjih tačaka na isti način, a AddCondFormatIconSet bira jedan od 26 tipova setova ikona kao što je icsTrafficLights3. Ovde ne postoji poseban zapis stila koji biste mogli zaboraviti, jer poseban zapis stila uopšte i ne postoji.

Parametri o kojima vredi razmisliti kod ovih poziva jesu sidra vrednosti, tipizirana kao TXLSCfValueKind. Krajnja tačka trake ili skale može ležati na minimumu ili maksimumu opsega, na fiksnom broju, na procentu ili percentilu, ili na rezultatu formule. Podrazumevane vrednosti (minimum i maksimum opsega) rade na urednim demo podacima, a zatim vas izdaju na stvarnim podacima sa ekstremnim vrednostima (outliers): jedna izrazito visoka vrednost rasteže skalu i spljoštava svaku drugu traku na minijaturni ostatak. Kada kontrolnu tablu treba čitati kroz više perioda, sidrite krajnje tačke za fiksne brojeve ili percentile, tako da pola trake u martu znači istu količinu kao pola trake u aprilu. Automatski skalirana traka je uporediva samo sama sa sobom.

XLS pisac pokriva četiri vrste pravila, ne više

Nasleđena BIFF8 strana nije umanjena slika XLSX strane; to je svesno napravljen podskup. XLS interfejs može da kreira tačno četiri oblike uslovnih pravila: trake podataka, dvobojne skale, trobojne skale i setove ikona, koji se emituju kao CF12 zapisi u tok. On nema API za kreiranje cellIs pravila, izraza ili tekstualnih pravila. Pravila tih vrsta koja već žive u fajlu koji otvorite čitaju se, čuvaju i upisuju nazad nepromenjena, tako da otvaranje i ponovno čuvanje korisnikovog .xls fajla nikada ne oštećuje formatiranje koje je on sadržao. Ono što ne možete jeste da generišete isticanje pragova od nule u .xls fajlu. Izbori su da to simulirate običnim ispunama ćelija izračunatim u kodu, ili da izlazni dokument bude .xlsx, gde je na raspolaganju kompletna porodica pravila.

Ovo je ograničenje koje treba definisati pre nego što sloj podataka uopšte nastane, a ne nakon toga, jer to menja odluku o formatu fajla za bilo šta što ima oblik kontrolne table. Tim koji je odabrao .xls zbog kompatibilnosti, a zatim specifikovao KPI izveštaj sa cellIs pragovima, izabrao je dve stvari koje ne idu zajedno, a mnogo je bezbolnije to primetiti pri odlučivanju o formatu nego tri nedelje nakon početka razvoja.

Slaganje pravila, prioritet i preklapajući opsezi

Stvarne kontrolne table retko izvršavaju samo jedno pravilo po opsegu. Kolona odstupanja može nositi traku podataka za intenzitet, cellIs pravilo za čvrst prag, i pravilo izraza na nivou reda iznad oba za eskalacije. Svaki TXLSXConditionalFormat izlaže vrednost Priority, a Excel rešava sukobljena pravila prema prioritetu. Kada dva pravila žele da oboje istu ćeliju, pobednik se odlučuje brojem koji ste postavili, na osnovu dijaloga za upravljanje pravilima.

Tretirajte prioritet onako kako grafički program tretira z-redosled (z-order). Dodelite ga ciljano svuda gde dva pravila mogu obuhvatiti iste ćelije, i ostavite razmake između vrednosti kako bi se kasnije pravilo moglo umetnuti bez potrebe za prenumerisanjem ostalih. Tamo gde se pravila ne mogu sudariti, recimo traka podataka ograničena na kolonu E i tekstualno pravilo ograničeno na kolonu G, redosled kreiranja je sasvim u redu i prioritetu ne treba pridavati pažnju. Usmerite tu pažnju na granice opsega, jer skupi bagovi ovde skoro nikada nisu inverzije prioriteta. To su opsezi poput B2:B200 na izveštaju koji je porastao na 350 redova, gde se nepokriveni kraj prikazuje kao obične ćelije koje izgledaju isto kao i zdravi podaci. Izvedite svaki opseg pravila iz iste vrednosti konačnog broja redova koja pokreće serije grafikona i opsege validacije na drugim mestima u radnoj svesci, i kraj izveštaja više neće ostajati neobojen.

Jedna navika verifikacije se višestruko isplati. Nakon generisanja, otvorite fajl u Excel-u, izaberite formatirani opseg i prođite kroz dijalog Manage Rules za svaku izmenu šablona. Uslovno formatiranje je jedna od retkih oblasti gde je jedini autoritativni renderer sama aplikacija koja konzumira fajl, pa test jedinice (unit test) nad XML-om samo dokazuje da je pravilo upisano, ali ne i da ga Excel renderuje onako kako ste zamislili. Minut vizuelne provere rešava ovaj problem.

Rich text: više formata unutar jedne ćelije

Rich-text ćelija u XLSX modelu sadrži listu segmenata (runs), pri čemu je svaki segment tekstualni raspon sa sopstvenim atributima fonta. Listu gradite sa strane kao objekat TXLSXRichText, dodajete segmente u nju, a zatim ceo objekat povezujete sa ćelijom. Pravilo o vlasništvu (ownership) je ono na šta morate paziti. Dodela svojstvu Cell.RichText prenosi vlasništvo nad tim objektom na ćeliju, i ćelija ga oslobađa tokom sopstvene destrukcije. Ako ga i sami oslobodite, dobićete grešku dvostrukog oslobađanja (double-free), vrstu greške koja ostaje nečujna u delu koji ju je izazvao, a isplivava kao rušenje programa na nekom sasvim nepovezanom mestu znatno kasnije.

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;

Eksplicitno ColorIsAuto := False nije opcioni ukras. Segment nosi zastavicu automatske boje, i dodela boje se uvažava tek kada se ta zastavica očisti. Postavite Color i zaboravite na ColorIsAuto, i segment će ispasti podebljan ali tvrdoglavo crn, bez ikakve greške koja bi ukazala na uzrok. Segmenti takođe podržavaju precrtavanje (strikethrough), varijante podvlačenja i vertikalno poravnanje za eksponente (superscript) i indekse (subscript), dok svojstvo PlainText spljoštava celu listu nazad u jedan string kada je potrebno da izvezete ili uporedite tekstualni sadržaj.

Rich text na nivou ćelije je dostupan samo za XLSX. XLS interfejs nema javni API za njegovo pisanje, iako su segmenti tamo dostupni na komentarima i tekstualnim okvirima kroz TextRuns, a bogati stringovi pročitani iz postojećeg .xls fajla preživljavaju kružno putovanje netaknuti. Zaključak je isti kao i kod uslovnog formatiranja: sve što meša formate unutar ćelije pripada XLSX piscu.

Baza stilova i greška "off-by-one" koja se isporučuje

Obično stilizovanje ćelija u XLSX modelu odvija se kroz kolekcije baze stilova (pools) na nivou radne sveske. Funkcije Fonts.Add, Fills.AddSolid i Borders.Add registruju definiciju i vraćaju njen indeks u bazi. Ti indeksi su zasnovani na nuli. Svojstva na strani ćelije koja ih konzumiraju, kao što je FontIndex, rezervišu 0 za "podrazumevano", tako da je vrednost koju dodeljujete ćeliji indeks iz baze uvećan za jedan:

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

Izostavite + 1 i svako zaglavlje će se vratiti na podrazumevani font. Nema izuzetka niti upozorenja, samo radna sveska koja izgleda kao da je niko nije stilizovao. Druga česta greška se krije u petlji: pozivanje Fonts.Add jednom po svakom redu. Idententične definicije fontova se dedupliraju, tako da fajl nije oštećen, ali je rad uzaludan, a baza poravnanja (alignment pool) posebno vraća nov objekat pri svakom pozivu umesto da spaja duplikate. Kreirajte tih nekoliko stilova jednom pre petlje i ponovo koristite njihove indekse. Na izveštajima od sto hiljada redova, ta jedna izmena je jedna od poluga pokrivenih u članku o podešavanju performansi velikih radnih svezaka za HotXLS-u. Kada vam je potreban samo standardni semantički izgled, oba interfejsa izlažu metodu ApplyBuiltinStyle na opsezima, koja se mapira na Excel-ove ugrađene stilove (Good, Bad, Neutral i stilove akcenta) bez potrebe da uopšte dotičete baze stilova.

Uslovno formatiranje, rich text i stilovi iz baze su poslednji korak u kreiranju izveštaja, koji se primenjuje nakon što su model podataka i raspored definisani, a te ranije faze su tema članka o generisanju izveštaja na osnovu šablona pomoću HotXLS-a. Kompletna referenca o pravilima, segmentima i stilovima nalazi se na stranici proizvoda HotXLS Component.