Vrednovanje Excel uvjetnog oblikovanja u Delphiju s HotXLS

HotXLS je izvorna komponenta proračunske tablice za Delphi i C++Builder, i od verzije 2.209.0 može odgovoriti na pitanje koje Excel obično čuva za sebe: za ovu točno stanicu, koja se pravila uvjetnog oblikovanja aktiviraju, i u koju se ispunu, font, traku podataka ili ikonu razrješavaju. Taj odgovor je ono što vam treba u trenutku kad je vaš izlaz HTML izvještaj, PDF, ili mreža koju sami crtate

Ovo je drugačiji problem od stvaranja pravila. Dvije ranije bilješke pokrivaju stranu autorstva: uvjetno oblikovanje i stilovi bogatog teksta bavi se pridruživanjem pravila i diferencijalnih formata rasponu, a particioniranje usidrenih uvjetnih formata bavi se time što se događa rasponu pravila kad se umeću ili brišu retci i stupci. Oboje je strukturno. Ovo je o semantici: dan radni sveščić koji već nosi pravila, izračunati isticanje

Zašto format datoteke ne govori koje se stanice pale

Kratak odgovor je da ECMA-376 i ISO 29500-1 definiraju pohranu, a ne vrednovanje. Element conditionalFormatting (§18.3.1.18) nosi sqref i popis djece cfRule (§18.3.1.10), a svako pravilo nosi type, neobvezni operator, priority, zastavicu stopIfTrue, jedno ili dva djeteta formula, a za vizualne obitelji skup pragova cfvo. Svaki od njih vjerno opisuje što je korisnik konfigurirao, a nijedan nije algoritam. Za polovicu tipova pravila taj jaz nije bitan: cellIs s operator="greaterThan" znači veće od, a containsText znači da je podniz prisutan. Jaz se otvara na obiteljima agregata. Pravilo top10 s rank="10" i percent="1" preko 27 popunjenih numeričkih stanica ističe koliko stanica? Dva zarez sedam nije broj. Zaokruživanje, floor, ili ceiling — specifikacija šuti, a pogrešan izbor znači da se vaš PDF ne slaže s radnim sveščićem koji klijent ima otvoren pored sebe

Pravila za jednu stanicu i gdje TCondFormatRule.Evaluate staje

HotXLS je prvo uzeo jeftiniju polovicu. TCondFormatRule.Evaluate u lxCondFormat.pas, dodan u 2.199.0, odgovara pali li se jedno pravilo za jednu stanicu bez da zna bilo što o ostatku raspona. Rukuje s osam BIFF operatora usporedbe iza cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), slobodnim pravilima expression vrednovanim na stanici tako da se relativne reference ispravno prebazuju, četiri tekstualna predikata, i predikatima za praznine i greške. Pragovi dolaze iz FFormula1 i FFormula2 razriješenih kroz TXLSCalculator.GetRangeValue na poziciji stanice, a obrnute granice se zamjenjuju, a ne odbijaju

var
  I: Integer;
  Rule: TCondFormatRule;
  Value: Variant;
begin
  Value := Sheet.Cells[Row, Col].Value;
  for I := 0 to CondFormat.RuleCount - 1 do
  begin
    Rule := CondFormat.Rule(I);
    // Single-cell verdict only. Aggregate and visual kinds answer False.
    if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      ApplyHighlight(Row, Col, Rule.Style);
  end;
end;

Iskren dio te metode je ono što odbija nagađati. top10, aboveAverage, belowAverage, duplicateValues i uniqueValues vraćaju False, ne zato jer su teški, nego zato jer su neodlučivi iz jedne stanice — svakom od njih treba statistika preko cijele domene. Četiri vizualne obitelji, dataBar, colorScale2, colorScale3 i iconSet, vraćaju False iz drugog razloga: uopće nikad ne proizvode bulean, proizvode payload za renderiranje, a bulean povratni tip je pogrešan oblik za njih

Kako evaluator na razini radnog lista izbjegava ponovno pretraživanje lista?

Izračunavanjem svake dijeljene veličine jednom, pri konstrukciji, i nikad više. TXLSXConditionalFormatEvaluator u lxHandleX.pas je nepromjenjiva snimka za jedan radni list, izgrađena kroz TXLSXWorksheet.CreateConditionalFormatEvaluator, a cijeli njen dizajn je obrana od naivne implementacije gdje svaka obojena stanica pokreće puno pretraživanje raspona

Četiri se stvari događaju u konstruktoru. Svaki različit višepodručni sqref raščlanjuje se točno jednom u TXlsxCfRangeSnapshot, tako da deset pravila koja dijele jedan raspon dijeli jednu raščlambu i jedan prolaz statistike. Taj prolaz strimira srednju vrijednost, populacijsko odstupanje, minimum i maksimum preko popunjenih stanica u jednom prolazu, i zadržava poredani numerički niz samo kad pravilo Top/Bottom ili percentila to doista treba. Ključevi duplikata i jedinstvenih vrijednosti grade se Unicode-sigurno i skupno sortiraju jednom umjesto po pretrazi. Zatim se os retka reže u trake na svakoj granici područja, tako da EvaluateCell binarno pretražuje traku i posjećuje samo pravila čiji rasponi mogu doseći taj redak

Četvrto je ono što je najbitnije pri opsegu. Relativna formula pravila poput =A1>AVERAGE($A$1:$A$100) znači nešto drugo u svakoj stanici domene, a očita implementacija sastavlja svježe sintaktičko stablo po stanici. TXlsxCfRulePlan sastavlja ga jednom i ponovno vrednuje isto stablo kroz reverzibilne pomake koordinata, što čuva Excel ponašanje sidra bez alokacije sintaktičkog stabla po stanici. Pravila se zatim slojevito nižu po priority, a poklapanje na pravilu čiji je StopIfTrue postavljen prekida petlju, točno kao što Excel kratkospaja

var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
begin
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    if Evaluator.EvaluateCell(Row, Col, Res) then
    begin
      if Res.HasFillColor then
        Canvas.Brush.Color := TColor(Res.FillColor);
      if Res.HasIcon then
        // IconIndex is zero-based inside Res.IconSetType
        DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
      if Res.HasDataBar then
        // DataBarAxis and DataBarEnd are normalised to 0..1
        DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
      if not Res.ShowCellValue then
        Exit;  // showValue="0" on the rule hides the number
    end;
  finally
    Evaluator.Free;
  end;
end;

Kako Excel zapravo zaokružuje pravilo Top 10 posto?

Floor-a, s minimumom od jedan, i uključuje izjednačenja na granici. To nigdje nije zapisano u ISO 29500-1 — fiksirano je ispitivanjem Excela 16 ručno izgrađenim radnim sveščićima i čitanjem koje je stanice aplikacija istaknula. HotXLS implementira upravo to: broj ranga je Floor(Count * Min(Rank, 100) / 100), podignut na 1 kad sleti na nulu, stegnut na popunjeni broj, a granična vrijednost se zatim uspoređuje s >= tako da se svaka stanica jednaka granici istakne čak i kad to premaši traženi broj. Dvadeset sedam vrijednosti i pravilo od 10 posto ističu dvije stanice, plus svaku daljnju stanicu izjednačenu s drugom

Pravila iznad prosjeka skrivala su drugu nejednoznačnost: aboveAverage s stdDev="1" bira stanice jedno standardno odstupanje iznad srednje vrijednosti, ali odstupanje uzorka i odstupanje populacije razlikuju se Besselovom korekcijom i vidljivo se ne slažu na malim rasponima, što je upravo gdje se uvjetno oblikovanje koristi. Excel 16 koristi populacijsko odstupanje, a HotXLS se s tim poklapa, s tim da zastavica equalAverage čini strogu usporedbu uključivom samo kad nikakav pojas odstupanja nije u igri. Pravila duplikata i jedinstvenih vrijednosti umjesto toga oslanjaju se na identitet ključa. Ako jedna stanica drži broj 100, a druga tekst "100", Excel ih tretira kao isti ključ duplikata, pa HotXLS normalizira numerički tekst u numerički prostor ključeva umjesto uspoređivanja sirovih nizova. Prazne stanice su zrcalni slučaj: prava prazna stanica sudjeluje u brojanju raspona, ali sama nije stilizirana, tako da se prazne stanice u stupcu ne pale sve kao duplikati jedna druge

Skale boja i skupovi ikona: interpolacija i pravila granica

Vizualne obitelji razrješavaju se u brojeve spremne za renderiranje umjesto u bulean vrijednosti, a njihovo rubno ponašanje fiksirano je na isti način. Za skalu boja s eksplicitnim numeričkim pragovima, HotXLS steže razlomak pozicije na zatvoreni interval nula do jedan, zatim interpolira po kanalu skraćivanjem umjesto zaokruživanja — vrijednost ispod minimalnog praga dobiva minimalnu boju umjesto ekstrapolirane, skala od tri praga bira svoj par uspoređujući s srednjim pragom, a degenerirana skala čija dva kraja nose isti prag kolapsira u vrhunsku boju umjesto dijeljenja s nulom. Skupovi ikona trebali su suprotnu vrstu pažnje, jer svaki cfvo nakon prvog nosi vlastitu strogost usporedbe: HotXLS čita ThresholdEqualsInclude po pragu i primjenjuje >= ili > u skladu s tim, hodajući prema gore tako da najviši zadovoljeni prag pobjeđuje indeks ikone. Obrnuti skup okreće razriješeni indeks, a ne pragove, prepisivanja po ikoni mogu povući glif iz druge obitelji, a svaki nevaljani prag prekida pravilo umjesto proizvodnje uvjerljivo pogrešne ikone

Hranjenje mreže, HTML izvoza i PDF-a iz jednog rezultata

EvaluateCell vraća posve razriješen TXLSXCfCellResult — diferencijalna boja ispune i fonta s već primijenjenim tonom teme, podebljano, kurziv, podcrtano, id formata broja, usmjereni pozitivni i negativni dosezi trake, poziciju osi, obitelj ikone i indeks — pa svaki potrošač čita isti zapis i nijedan ne treba razumjeti unutarnjost pravila. HotXLS koristi taj jedan put za HTML izvoz, PDF izvoz i interaktivni preglednik, što je jedini praktičan način da se tri renderera drže zajedno umjesto da se razilaze. Verzija 2.210.0 uklopila je to u TXLSWorkbookViewer, koji predmemorira jedan pripremljeni evaluator po aktivnom radnom listu i ponovno ga koristi kroz pomicanje, odabir i ponovno crtanje, otpuštajući ga kad se radni sveščić ili radni list promijeni — obnavljanje snimke pri svakom Paintu poništilo bi cijeli dizajn s vremenom konstrukcije. Ta je predmemorija i razlog zašto postoji TXLSWorkbookViewer.RefreshConditionalFormats: snimka je nepromjenjiva, pa ako mutirate pridruženi radni sveščić na mjestu, agregatna statistika i razriješeni pragovi zastarjeli su dok ga ne pozovete

// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200;         // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats;        // drop evaluator, repaint

Što evaluator neće učiniti umjesto vas

Tri granice vrijedi jasno navesti. Klasični TCondFormatRule.Evaluate za jednu stanicu i TXLSXConditionalFormatEvaluator na razini radnog lista različite su površine s različitim mogućnostima, a onaj za jednu stanicu namjerno odbija obitelji agregata i vizualne obitelji umjesto da ih aproksimira — ako trebate Top/Bottom ili skalu boja, izgradite evaluator. Relativna vremenska razdoblja ovise o satu stroja u trenutku vrednovanja, pa se pravilo timePeriod renderira drugačije u PDF-u generiranom danas i onom generiranom sljedeći tjedan, što je ispravno ponašanje, a i dalje tiket podrške koji čeka da se dogodi ako se od vaše arhive očekuje bajt-stabilnost. Treća je gramatička, a ne tehnička: gramatika formule uvjetnog oblikovanja zabranjuje strukturirane reference tablica, tako da pravilo ne može adresirati stupac tablice po imenu na način na koji to može formula radnog lista, i to je ograničenje formata, a ne implementacije

Ako gradite izlaz izvještaja, pipeline izvoza ili prilagođenu mrežu koja se mora slagati s Excelom stanica po stanicu, isti razriješeni rezultat pokreće i prilagođenu VCL mrežu proračunske tablice opisanu drugdje na ovom blogu. Puna API dokumentacija, model pravila i probne preuzimanja za HotXLS Delphi komponentu proračunske tablice dostupni su na stranici proizvoda