HotXLS uvjetno oblikovanje i bogati tekst u Delphiju

Pravilo uvjetnog oblikovanja (conditional formatting) u OOXML-u sastoji se od dviju zasebnih stvari koje nose jedno ime. Uvjet (usporedba, formula, podudaranje teksta) odlučuje koje ćelije ispunjavaju uvjete. Izgled (zapis o diferencijalnom formatu, odnosno dxf u ECMA-376 pojmovima) odlučuje kako te ćelije izgledaju. Excelov dijaloški okvir skriva taj šav tjerajući vas da popunite oboje odjednom. HotXLS to ne čini. Ako stvorite pravilo cellIs iz Delphija i preskočite stil, pravilo je valjano, raspon je ispravan, formula se procjenjuje kao istinita (true) na točno određenim ćelijama, ali ništa ne mijenja boju jer je uputa pravila bila 'istina, ne bojaj ništa'. Taj jaz između uvjeta i posljedice prva je stvar koju treba ispravno postaviti, i on je zaslužan za većinu pravila koja izgledaju ispravno u upravitelju pravila (Manage Rules), a ipak ništa ne ističu.

HotXLS izvorno zapisuje uvjetno oblikovanje u datoteke BIFF8 .xls i OOXML .xlsx, a isto čini i za nizove bogatog teksta (rich text runs) te model stilova ćelija u zajedničkom spremniku (pooled cell-style model). Ove tri značajke dijele više unutarnjeg povezivanja nego što to sugerira jednostavna površina API-ja, a mjesta na kojima izlaz odstupa od namjere obično su spojevi između njih.

Uvjet treba posljedicu: dxf stil

Na XLSX radnom listu, pravila usporedbe dolaze iz AddConditionalFormat, koji prima raspon, operator iz TXLSXCfOperator te formulu ili doslovnu vrijednost (literal), a zatim vraća indeks novog pravila unutar kolekcije ConditionalFormats na listu. Objekt pravila na tom indeksu izlaže svojstvo Style, i tu živi isticanje. Postavite ispunu na njega i ćelije koje ispunjavaju uvjete preuzet će tu ispunu. Ostavite ga netaknutim i izgradili ste nevidljivo pravilo opisano gore.

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 ovdje 32-bitne ARGB vrijednosti, pa je $FFFFC7CE Excelova 'svijetlocrvena' koju poznajete iz dijaloškog okvira, s potpuno neprozirnim alfa bajtom ispred RGB-a. Svaka vrsta pravila koja se aktivira na temelju uvjeta po ćeliji slijedi isti oblik stvori-pa-stiliziraj. Tekstualni usklađivači (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) vraćaju indeks koji naknadno stilizirate, a to čine i AddCondFormatTop10, AddCondFormatAboveAverage te detektori praznina i pogrešaka. Naučite uzorak jednom i cijela obitelj pravila za tekst i usporedbu ponaša se isto.

Podatkovne trake, skale boja i skupovi ikona sami se iscrtavaju

Vizualne vrste pravila rade obrnuto. One nose svoj izgled unutar same definicije pravila i potpuno ignoriraju svojstvo Style. Dodijelite ispunu pravilu podatkovne trake i ništa se neće dogoditi, što izgleda kao bug sve dok ne shvatite podjelu: AddCondFormatDataBar prima boju trake kao izravan argument, dvotočkaste i trotočkaste skale boja primaju svoje krajnje boje na isti način, a AddCondFormatIconSet odabire jednu od 26 vrsta skupova ikona kao što je icsTrafficLights3. Ovdje nema zasebnog zapisa stila koji biste mogli zaboraviti, jer zaseban zapis stila uopće ne postoji.

Parametri o kojima vrijedi razmisliti kod ovih poziva su sidra vrijednosti (value anchors), tipa TXLSCfValueKind. Krajnja točka trake ili skale može se nalaziti na minimumu ili maksimumu raspona, na doslovnom broju, na postotku ili percentilu, ili na rezultatu formule. Zadani načini rada, minimum i maksimum raspona, ponašaju se dobro na urednim demo podacima, a zatim vas izdaju na stvarnim podacima s ekstremnim vrijednostima: jedna ekstremna vrijednost rasteže skalu i spljošti svaku drugu traku u patrljak. Kada je kontrolna ploča namijenjena čitanju kroz razdoblja, radije usidrite krajnje točke za fiksne brojeve ili percentile, tako da pola trake u ožujku znači istu količinu kao pola trake u travnju. Automatski skalirana traka usporediva je samo sama sa sobom.

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

Naslijeđena BIFF8 strana nije manje ogledalo XLSX strane; to je svjestan podskup. XLS fasada može stvoriti točno četiri oblika uvjetnih pravila: podatkovne trake, dvobojne skale, trobojne skale i skupove ikona, emitirane kao CF12 zapisi u tok. Nema API za stvaranje pravila cellIs, izraza (expression) ili tekstualnih pravila. Pravila tih vrsta koja već žive u datoteci koju otvorite se čitaju, čuvaju i zapisuju natrag nepromijenjena, tako da otvaranje i ponovno spremanje korisničke .xls datoteke nikada ne oštećuje oblikovanje koje je nosila. Ono što ne možete učiniti jest generirati isticanje pragova ispočetka u .xls datoteci. Izbori su simuliranje toga običnim ispunama ćelija izračunatim u kodu ili isporuka u XLSX formatu, gdje je cijela obitelj pravila na raspolaganju.

Ovo je ograničenje koje treba riješiti prije nego što podatkovni sloj uopće postoji, a ne nakon toga, jer mijenja odluku o formatu datoteke za sve što je u obliku kontrolne ploče. Tim koji je odabrao .xls radi kompatibilnosti, a zatim specificira KPI izvješće s pragovima cellIs, odabrao je dvije stvari koje ne idu zajedno, a jeftinije vrijeme za uočavanje toga je pri donošenju odluke o formatu radije nego tri tjedna nakon početka izgradnje.

Slaganje pravila, prioritet i preklapajući rasponi

Stvarne kontrolne ploče rijetko pokreću jedno pravilo po rasponu. Stupac odstupanja može nositi podatkovnu traku za magnitudu, pravilo cellIs za čvrsti prag i pravilo izraza na razini retka iznad obojega za eskalacije. Svaki TXLSXConditionalFormat izlaže vrijednost Priority, a Excel rješava suprotstavljena pravila redoslijedom prioriteta. Kada dva pravila žele obojiti istu ćeliju, pobjednik se odlučuje brojem koji postavite, a ne bilo kojim redoslijedom kojim recenzent slučajno prolazi u Manage Rules dijaloškom okviru.

Tretirajte prioritet na način na koji program za crtanje tretira z-redoslijed. Dodijelite ga namjerno gdje god dva pravila mogu dosegnuti iste ćelije i ostavite praznine između vrijednosti kako bi se kasnije pravilo uklopilo bez ponovnog numeriranja ostatka. Tamo gdje se pravila ne mogu sudariti, recimo podatkovna traka ograničena na stupac E i tekstualno pravilo ograničena na stupac G, redoslijed stvaranja je sasvim u redu i prioritet nije vrijedan pažnje. Usmjerite tu pažnju na granice raspona umjesto toga, jer skupi bugovi ovdje gotovo nikada nisu inverzije prioriteta. To su rasponi poput B2:B200 na izvješću koje je naraslo na 350 redaka, gdje se nepokriveni rep prikazuje kao obične ćelije koje izgledaju točno poput zdravih podataka. Izvedite svaki raspon pravila iz iste vrijednosti konačnog broja redaka (final-row-count) koja pokreće serije grafikona i raspone validacije drugdje u radnoj knjizi, i rep više neće otpadati.

Jedna navika provjere opravdava svoje postojanje. Nakon generiranja otvorite datoteku u Excelu, odaberite formatirani raspon i prođite kroz Manage Rules jednom za svaku promjenu predloška. Uvjetno oblikovanje jedno je od rijetkih područja u kojima je jedini mjerodavni render preglednik aplikacija koja troši datoteku, pa jedinični test (unit test) nad XML-om dokazuje da je pravilo zapisano, a ne da ga Excel slika onako kako ste zamislili. Minuta vizualne provjere rješava taj problem.

Bogati tekst: više formata unutar jedne ćelije

Bogata tekstualna ćelija (rich-text cell) u XLSX modelu sadrži popis nizova (runs), gdje je svaki niz raspon teksta plus vlastiti atributi fonta. Popis gradite sa strane kao objekt TXLSXRichText, dodajete mu nizove i potom cijelu stvar pridružujete ćeliji. Pravilo vlasništva je dio koji stvara probleme. Dodjela svojstvu Cell.RichText predaje vlasništvo nad tim objektom ćeliji, a ćelija ga oslobađa tijekom vlastitog uništavanja. Ako ga i sami oslobodite, imat ćete dvostruko oslobađanje (double-free), vrstu pogreške koja ostaje tiha kroz izvođenje koje ju je uzrokovalo i pojavljuje se kao rušenje na nekom nepovezanom mjestu 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;

Eksplicitni poziv ColorIsAuto := False nije opcionalni ukras. Niz nosi zastavicu automatske boje, a dodjela boje se poštuje tek nakon što se ta zastavica očisti. Postavite Color i zaboravite ColorIsAuto i niz će ispasti podebljan, ali tvrdoglavo crn, bez ikakve pogreške koja bi ukazala na uzrok. Nizovi također podržavaju precrtavanje (strikethrough), varijante podcrtavanja te okomito poravnanje za eksponente (superscript) i indekse (subscript), dok PlainText izravnava cijeli popis natrag u jedan niz kada trebate izvesti ili usporediti (diff) tekstualni sadržaj.

Bogati tekst na razini ćelije je isključivo XLSX opcija. XLS fasada nema javni API za njegovo pisanje, iako su nizovi tamo dostupni na komentarima i tekstualnim okvirima putem TextRuns, a bogati nizovi (rich strings) pročitani iz postojećeg .xls formata preživljavaju povratno putovanje netaknuti. Smjernica je ista kao i kod uvjetnog oblikovanja: sve što miješa formate unutar ćelije pripada XLSX piscu.

Spremnik stilova i pogreška off-by-one koja se isporučuje

Obično oblikovanje ćelija u XLSX modelu teče kroz zbirke u spremniku na razini radne knjige. Metode Fonts.Add, Fills.AddSolid i Borders.Add svaka registriraju definiciju i vraćaju njezin indeks u spremniku. Ti su indeksi na bazi 0. Svojstva na strani ćelije koja ih konzumiraju, kao što je FontIndex, rezerviraju 0 za 'zadano', pa je vrijednost koju dodjeljujete ćeliji indeks u spremniku 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

Izbacite + 1 i svako zaglavlje se vraća na zadani font. Nema iznimke i nema upozorenja, samo radna knjiga koja izgleda kao da je nitko nije stilizirao. Sljedeća pogreška skriva se u petlji: pozivanje Fonts.Add jednom po retku. Identificirane definicije fontova se dedupliciraju, pa datoteka nije oštećena, ali posao je uzaludan, a spremnik poravnanja (alignment pool) posebno vraća novi objekt pri svakom pozivu umjesto preklapanja duplikata. Izgradite šačicu stilova jednom prije petlje i ponovno koristite njihove indekse. Na izvješćima od sto tisuća redaka ta je jedna promjena jedna od poluga pokrivenih u članku podešavanje performansi velikih radnih knjiga za HotXLS. Kada vam treba samo uobičajeni semantički izgled, obje fasade izlažu ApplyBuiltinStyle na rasponima, što se preslikava na Excelove ugrađene stilove Good, Bad, Neutral i naglašene stilove bez vašeg doticaja sa spremnicima.

Uvjetno oblikovanje, bogati tekst i stilovi u spremniku zadnji su korak izvješća, koji se primjenjuje nakon što su podatkovni model i izgled dogovoreni, a te ranije faze tema su članka generiranje izvješća na temelju predložaka s HotXLS-om. Cjelokupna dokumentacija klasa i metoda nalazi se na stranici proizvoda HotXLS Component.