HotXLS uslovno formatiranje i bogat tekst (rich text) u

Pravilo uslovnog formatiranja (conditional formatting) u OOXML formatu predstavlja dve odvojene stvari pod jednim nazivom. Uslov (poređenje, formula ili podudaranje teksta) odlučuje koje ćelije ispunjavaju kriterijum. Izgled (zapis o diferencijalnom formatu, dxf prema standardu ECMA-376) određuje kako te ćelije izgledaju. Excel-ov dijalog krije ovaj šav tako što vas primorava da popunite oba dela odjednom. HotXLS to ne radi. Napravite pravilo cellIs iz Delphi-ja i preskočite stil — pravilo će biti važeće, opseg ispravan, formula će se procenjivati kao tačna (true) za tačno odgovarajuće ćelije, ali ništa neće promeniti boju, jer je instrukcija pravila glasila: "ako je tačno, nemoj obojiti ništa". Ta razlika između uslova i posledice je prva stvar koju treba pravilno shvatiti, i ona je uzrok većine pravila koja izgledaju ispravno u menadžeru pravila (Manage Rules), a ipak ne označavaju ništa

HotXLS piše uslovno formatiranje izvorno u BIFF8 .xls i OOXML .xlsx fajlove, a isto radi i za delove bogatog teksta (rich text runs) i objedinjeni model stilova ćelija (pooled cell-style model). Ove tri funkcije dele više internih veza nego što to sugeriše jednostavan API interfejs, a mesta gde se izlaz razlikuje od namere su obično spojevi između njih

Uslovu je potrebna posledica: dxf stil

Na XLSX radnom listu, pravila poređenja dolaze iz metode AddConditionalFormat, koja uzima opseg, operater iz TXLSXCfOperator i formulu ili literal, a zatim vraća indeks novog pravila unutar kolekcije ConditionalFormats na tom listu. Objekat pravila na tom indeksu izlaže svojstvo Style, i tu leži definicija isticanja ćelije. Podesite ispunu (fill) na njemu i ćelije koje ispunjavaju uslov preuzimaju tu ispunu. Ostavite ga netaknutim i napravićete 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, tako da je $FFFFC7CE ona Excel-ova "svetlocrvena" boja koju poznajete iz dijaloga, sa potpuno neprovidnim (opaque) bajtom za alfu koji stoji ispred RGB-a. Svaka vrsta pravila koja se aktivira na osnovu uslova po ćeliji prati isti oblik: kreiraj, pa stilizuj. Metode za podudaranje teksta (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) vraćaju indeks koji kasnije stilizujete, a isto važi i za AddCondFormatTop10, AddCondFormatAboveAverage, kao i detektore praznih ćelija i grešaka. Naučite ovaj obrazac jednom i čitava porodica pravila za tekst i poređenje ponašaće se isto

Trake podataka, skale boja i kompleti ikona se sami iscrtavaju

Vizuelne vrste pravila funkcionišu na suprotan način. One nose svoj izgled unutar same definicije pravila i potpuno ignorišu svojstvo Style. Dodelite ispunu pravilu sa trakama podataka (data bar) i ništa se neće dogoditi, što izgleda kao bag sve dok ne shvatite taksonomiju: metoda AddCondFormatDataBar uzima boju trake kao direktan argument, skale boja sa dve i tri tačke uzimaju svoje krajnje boje na isti način, a AddCondFormatIconSet bira jedan od 26 tipova kompleta ikona, kao što je icsTrafficLights3. Ovde nema posebnog zapisa o stilu koji biste mogli da zaboravite, jer poseban zapis o stilu uopšte ne postoji

Parametri o kojima vredi razmisliti kod ovih poziva su sidra vrednosti, tipa TXLSCfValueKind. Krajnja tačka trake ili skale može ležati na minimumu ili maksimumu opsega, na doslovnom broju, na procentu ili percentilu, ili na rezultatu formule. Podrazumevane vrednosti, minimum i maksimum opsega, dobro se ponašaju na urednim demo podacima, a zatim vas izneveravaju na stvarnim podacima sa ekstremnim vrednostima (outliers): jedna ogromna vrednost rasteže skalu i spljoštava svaku drugu traku u patrljak. Kada je kontrolna tabla (dashboard) namenjena za čitanje kroz različite periode, umesto toga usidrite krajnje tačke za fiksne brojeve ili percentile, tako da pola trake u martu označava istu količinu kao i pola trake u aprilu. Automatski skalirana traka je uporediva samo sa samom sobom

XLS pisač pokriva četiri vrste pravila i ništa više

Zastarela BIFF8 strana nije umanjeno ogledalo XLSX strane, već namerno definisan podskup. XLS fasada može da kreira tačno četiri oblika uslovnih pravila: trake podataka, dvobojne skale, trobojne skale i komplete ikona, koji se u tok (stream) upisuju kao CF12 zapisi. Ona nema API za kreiranje cellIs pravila, izraza (expression) ili tekstualnih pravila. Pravila tih vrsta koja već postoje u fajlu koji otvorite se čitaju, zadržavaju i upisuju nazad nepromenjena, tako da otvaranje i ponovno čuvanje klijentovog .xls fajla nikada ne oštećuje formatiranje koje je on nosio. Ono što ne možete jeste da generišete isticanje pragova od nule u .xls fajlu. Izbori se svode na simulaciju pomoću običnih ispuna ćelija izračunatih u kodu, ili na to da isporučeni fajl bude u .xlsx formatu, gde je na raspolaganju čitava porodica pravila

Ovo je ograničenje koje treba razrešiti pre nego što nivo podataka uopšte nastane, a ne nakon toga, jer to menja odluku o formatu fajla za bilo šta u obliku kontrolne table. Tim koji je izabrao .xls radi kompatibilnosti, a zatim specificira KPI izveštaj sa cellIs pragovima, izabrao je dve stvari koje ne idu zajedno, a jeftinije vreme da se to primeti je pri donošenju odluke o formatu, a ne tri nedelje nakon početka izrade

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 magnitudu, pravilo cellIs za čvrsti prag, i pravilo izraza na nivou reda iznad oba za eskalacije. Svaki TXLSXConditionalFormat izlaže vrednost Priority (prioritet), i Excel razrešava sukobljena pravila prema redosledu prioriteta. Kada dva pravila žele da oboje istu ćeliju, pobednik se odlučuje na osnovu broja koji sami postavite, a ne prema redosledu kojim recenzent slučajno prolazi u dijalogu za upravljanje pravilima (Manage Rules)

Tretirajte prioritet onako kako grafički program tretira redosled po z-osi (z-order). Dodelite ga namerno svuda gde dva pravila mogu dosegnuti iste ćelije, i ostavite praznine između vrednosti kako bi se kasnije pravilo moglo ubaciti bez menjanja brojeva ostalih. Tamo gde pravila ne mogu da se sudare, recimo kod trake podataka ograničene na kolonu E i tekstualnog pravila ograničenog na kolonu G, redosled kreiranja je sasvim u redu i prioritet ne zaslužuje pažnju. Umesto toga, posvetite pažnju granicama opsega, jer skupe greške ovde skoro nikada nisu obrnuti prioriteti. To su opsezi poput B2:B200 na izveštaju koji je porastao na 350 redova, gde se nepokriveni kraj renderuje kao obične ćelije koje izgledaju baš kao i ispravni podaci. Izvedite svaki opseg pravila iz iste vrednosti ukupnog broja redova koja pokreće serije grafikona i opsege validacije na drugim mestima u radnoj svesci, i rep više neće otpadati

Jedna navika proveravanja se višestruko isplati. Nakon generisanja, otvorite fajl u Excel-u, izaberite formatirani opseg i prođite kroz dijalog Manage Rules jednom za svaku izmenu šablona. Uslovno formatiranje je jedna od retkih oblasti gde je jedini autoritativni program za renderovanje sama aplikacija koja konzumira fajl, tako da unit test nad XML-om dokazuje samo da je pravilo upisano, a ne i da ga Excel crta onako kako ste zamislili. Jedan minut vizuelnog pregleda rešava taj problem

Bogat tekst: više formata unutar jedne ćelije

Ćelija sa bogatim tekstom u XLSX modelu sadrži listu delova (runs), gde je svaki deo sekcija teksta plus sopstveni atributi fonta. Ovu listu gradite sa strane kao objekat TXLSXRichText, dodajete delove u nju, a zatim sve to prikačite ćeliji. Pravilo o vlasništvu nad objektima je deo koji može da napravi problem. Dodeljivanje svojstvu Cell.RichText prenosi vlasništvo nad tim objektom na ćeliju, i ćelija ga oslobađa (free) tokom sopstvenog uništavanja. Ako ga i sami oslobodite, imaćete dvostruko oslobađanje memorije (double-free) — vrstu greške koja miruje tokom izvršavanja koda koji ju je izazvao, a isplivava kao pad sistema na nekom sasvim nepovezanom mestu mnogo 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 podešavanje ColorIsAuto := False nije opciona dekoracija. Deo teksta (run) nosi zastavicu automatske boje, i dodeljivanje boje se poštuje tek kada se ta zastavica očisti. Podesite Color i zaboravite ColorIsAuto, i tekst će ispasti podebljan ali tvrdoglavo crn, bez ikakve greške koja bi ukazala na uzrok. Delovi teksta takođe podržavaju precrtavanje (strikethrough), varijante podvlačenja i vertikalno poravnanje za superscript i subscript, dok funkcija PlainText ravna čitavu listu nazad u jedan običan string kada je potrebno da izvezete tekstualni sadržaj ili uporedite razlike (diff)

Bogat tekst na nivou ćelije je dostupan samo za XLSX. XLS fasada nema javni API za njegovo pisanje, iako su delovi teksta tamo dostupni na komentarima i tekstualnim poljima (text boxes) preko TextRuns, a bogati stringovi pročitani iz postojećeg .xls fajla preživljavaju kružni tok neostupljeno. Pravilo je isto kao i kod uslovnog formatiranja: sve što meša formate unutar ćelije pripada XLSX pisaču

Baza stilova (style pool) i greška off-by-one koja odlazi u produkciju

Obično stilizovanje ćelija u XLSX modelu prolazi kroz objedinjene kolekcije na radnoj svesci. Metode Fonts.Add, Fills.AddSolid i Borders.Add svaka registruju definiciju i vraćaju njen indeks u bazi (pool). Ti indeksi počinju od 0. Svojstva na strani ćelije koja ih koriste, kao što je FontIndex, rezervišu 0 za "default" (podrazumevano), tako da je vrednost koju dodeljujete ćeliji zapravo indeks iz baze plus 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 i nema upozorenja, samo radna sveska koja izgleda kao da je niko nije stilizovala. Greška drugog reda se krije u petlji: pozivanje Fonts.Add jednom po redu. Idententične definicije fontova se dedupliciraju, tako da fajl nije oštećen, ali je rad uzaludan, a baza za poravnanje (alignment pool) posebno vraća novi objekat pri svakom pozivu umesto da spaja duplikate. Napravite tih nekoliko stilova jednom pre petlje i ponovo koristite njihove indekse. Kod izveštaja sa sto hiljada redova, ta jedna izmena je jedna od poluga pokrivenih u članku o podešavanju performansi velikih radnih svezaka za HotXLS. Kada vam je potreban samo standardni semantički izgled, obe fasade izlažu metodu ApplyBuiltinStyle na opsezima, koja mapira na ugrađene Excel stilove Good, Bad, Neutral i stilove akcenta, a da uopšte ne dotičete baze stilova

Uslovno formatiranje, bogat tekst i objedinjeni stilovi su poslednji koraci izveštaja, koji se primenjuju nakon što se model podataka i raspored stabilizuju, a ti raniji koraci su tema članka o generisanju izveštaja na osnovu šablona pomoću HotXLS-a. Kompletna referenca za pravila, delove teksta i stilove nalazi se na stranici proizvoda HotXLS komponenta