Evaluacija Excel uslovnog formatiranja u Delphi sa HotXLS

HotXLS je nativna komponenta tabelarnog proračuna za Delphi i C++Builder, i od verzije 2.209.0 može da odgovori na pitanje koje Excel obično čuva za sebe: za tačno ovu ćeliju, koja pravila uslovnog formatiranja se okidaju, i koju ispunu, font, data bar ili ikonu razrešavaju. Taj odgovor vam treba u trenutku kad je vaš izlaz HTML izveštaj, PDF, ili mreža koju sami crtate

Ovo je drugačiji problem od kreiranja pravila. Dve ranije beleške pokrivaju stranu autorstva: uslovno formatiranje i rich text stilovi se bavi prikačivanjem pravila i diferencijalnih formata na opseg, a particionisanje usidrenih uslovnih formata se bavi time šta se dešava sa opsegom pravila kad se redovi i kolone ubace ili obrišu. Oba su strukturalna. Ovo je o semantici: dat radni sveska koja već nosi pravila, izračunaj isticanje

Zašto vam format fajla ne govori koje se ćelije pale

Kratak odgovor je da ECMA-376 i ISO 29500-1 definišu skladištenje, ne evaluaciju. Element conditionalFormatting (§18.3.1.18) nosi sqref i listu dece cfRule (§18.3.1.10), a svako pravilo nosi type, opcioni operator, priority, zastavicu stopIfTrue, jedno ili dva deteta formula, i za vizuelne porodice skup pragova cfvo. Svaki od njih verno opisuje šta je korisnik konfigurisao, i nijedan nije algoritam. Za polovinu tipova pravila taj jaz nije bitan: cellIs sa operator="greaterThan" znači veće od, a containsText znači da je podstring prisutan. Jaz se otvara na agregatnim porodicama. Pravilo top10 sa rank="10" i percent="1" preko 27 popunjenih numeričkih ćelija ističe koliko ćelija? Dva zapeta sedam nije broj. Zaokruži, floor ili ceiling — specifikacija ćuti, a pogrešan izbor znači da se vaš PDF ne slaže sa radnom sveskom koju klijent ima otvorenu pored sebe

Pravila jedne ćelije i gde se TCondFormatRule.Evaluate zaustavlja

HotXLS je prvo uzeo jeftiniju polovinu. TCondFormatRule.Evaluate u lxCondFormat.pas, dodat u 2.199.0, odgovara da li se jedno pravilo okida za jednu ćeliju bez ikakvog znanja o ostatku opsega. Rukuje sa osam BIFF operatora poređenja iza cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), slobodnim pravilima expression evaluiranim na ćeliji tako da se relativne reference ispravno rebazuju, četiri tekstualna predikata, i predikatima za prazne i greške. Pragovi dolaze iz FFormula1 i FFormula2 razrešenih kroz TXLSCalculator.GetRangeValue na poziciji ćelije, a obrnute granice se zamenjuju umesto da se odbacuju

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 deo te metode je ono što odbija da nagađa. top10, aboveAverage, belowAverage, duplicateValues i uniqueValues vraćaju False, ne zato što su teški, već zato što su neodlučivi iz jedne ćelije — svaki od njih treba statistiku preko čitavog domena. Četiri vizuelne porodice, dataBar, colorScale2, colorScale3 i iconSet, vraćaju False iz drugog razloga: nikad uopšte ne proizvode boolean, proizvode payload za renderovanje, i boolean povratni tip je pogrešan oblik za njih

Kako evaluator na nivou radnog lista izbegava ponovno skeniranje lista?

Izračunavanjem svake deljene veličine jednom, pri konstrukciji, i nikad ponovo. TXLSXConditionalFormatEvaluator u lxHandleX.pas je nepromenljiv snimak za jedan radni list, izgrađen kroz TXLSXWorksheet.CreateConditionalFormatEvaluator, i njegov ceo dizajn je odbrana od naivne implementacije gde svaka obojena ćelija okida potpuno skeniranje opsega

Četiri stvari se dešavaju u konstruktoru. Svaki različit višeoblasni sqref se parsira tačno jednom u TXlsxCfRangeSnapshot, tako da deset pravila koja dele jedan opseg dele jedan parse i jedan prolaz statistike. Taj prolaz strimuje srednju vrednost, populacionu devijaciju, minimum i maksimum preko popunjenih ćelija u jednom obilasku, i zadržava uređen numerički niz samo kad pravilo Top/Bottom ili percentil zaista treba statistike reda. Ključevi duplikata i jedinstvenosti se grade Unicode-bezbedno i batch-sortirani jednom umesto po pretrazi. Zatim se osa reda seče na trake na svakoj granici oblasti, tako da EvaluateCell binarno pretražuje traku i posećuje samo pravila čiji opsezi eventualno mogu dosegnuti taj red

Četvrta stvar je najbitnija u razmeri. Relativna formula pravila poput =A1>AVERAGE($A$1:$A$100) znači nešto drugo u svakoj ćeliji domena, a očigledna implementacija kompajlira svež sintaksni stablo po ćeliji. TXlsxCfRulePlan ga kompajlira jednom i ponovo evaluira isto stablo kroz reverzibilne pomeraje koordinata, što čuva ponašanje Excel usidrenja bez alokacije sintaksnog stabla po ćeliji. Pravila se onda slažu po priority, a poklapanje na pravilu čiji je StopIfTrue postavljen prekida petlju, tač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 procenata?

Floor-uje, sa minimumom od jedan, i uključuje izjednačenja na granici. To nigde nije zapisano u ISO 29500-1 — fiksirano je probanjem Excel 16 sa ručno napravljenim radnim sveskama i čitanjem koje ćelije je aplikacija istakla. HotXLS implementira tačno to: broj ranga je Floor(Count * Min(Rank, 100) / 100), podignut na 1 kad sleti na nulu, ograničen na popunjeni broj, a granična vrednost se onda poredi sa >= tako da se svaka ćelija jednaka granici ističe čak i kad to premaši traženi broj. Dvadeset sedam vrednosti i pravilo od 10 procenata ističu dve ćelije, plus svaku dalju ćeliju izjednačenu sa drugom

Pravila iznad proseka su krila drugu nejasnoću: aboveAverage sa stdDev="1" bira ćelije jednu standardnu devijaciju iznad srednje vrednosti, ali uzoračka i populaciona devijacija se razlikuju za Bessel korekciju i vidljivo se ne slažu na malim opsezima, što je tačno gde se uslovno formatiranje koristi. Excel 16 koristi populacionu devijaciju, i HotXLS je poklapa, sa zastavicom equalAverage koja čini strogo poređenje inkluzivnim samo kad nijedan pojas devijacije nije u igri. Pravila duplikata i jedinstvenosti su se okrenula identitetu ključa. Ako jedna ćelija drži broj 100, a druga tekst "100", Excel ih tretira kao isti ključ duplikata, tako da HotXLS normalizuje numerički tekst u numerički prostor ključeva umesto da poredi sirove stringove. Prazne ćelije su ogledalski slučaj: prava prazna ćelija učestvuje u zbiru opsega, ali sama nije stilizovana, tako da se prazne ćelije u koloni ne pale sve kao duplikati jedna druge

Color scale i icon set: interpolacija i granična pravila

Vizuelne porodice se razrešavaju u brojeve spremne za renderovanje umesto u boolean, a njihovo granično ponašanje je fiksirano na isti način. Za color scale sa eksplicitnim numeričkim pragovima, HotXLS steže razlomak pozicije na zatvoren interval nula do jedan, zatim interpolira po kanalu sa skraćivanjem umesto zaokruživanja — vrednost ispod minimalnog praga dobija minimalnu boju umesto ekstrapolisane, skala sa tri praga bira svoj par poređenjem sa srednjim pragom, a degenerativna skala čija oba kraja nose isti prag se svodi na gornju boju umesto deljenja nulom. Icon set-ovi su tražili suprotnu vrstu pažnje, jer svaki cfvo posle prvog nosi sopstvenu strogost poređenja: HotXLS čita ThresholdEqualsInclude po pragu i primenjuje >= ili > u skladu s tim, idući naviše tako da najviši zadovoljen prag pobeđuje indeks ikone. Obrnuti set okreće razrešen indeks umesto pragova, prepisivanja po ikoni mogu povući glif iz drugačije porodice, a bilo koji nevažeći prag prekida pravilo umesto da proizvede uverljivo pogrešnu ikonu

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

Pošto EvaluateCell vraća potpuno razrešen TXLSXCfCellResult — diferencijalna ispuna i boja fonta sa već primenjenim tint-om teme, bold, italic, underline, id formata broja, direkcioni pozitivni i negativni domašaj bara, pozicija ose, porodica i indeks ikone — svaki potrošač čita isti zapis i nijednom ne treba da razume internu logiku pravila. HotXLS koristi tu jednu putanju za HTML izvoz, PDF izvoz i interaktivni pregledač, što je jedini praktičan način da se spreči da se tri renderera razilaze. Verzija 2.210.0 ju je ožičila u TXLSWorkbookViewer, koji keš-uje jedan pripremljen evaluator po aktivnom radnom listu i ponovo ga koristi kroz skrolovanje, selekciju i repaint, oslobađajući ga kad se radna sveska ili radni list promeni — ponovno građenje snimka na svakom Paint-u bi porazilo ceo dizajn u vreme konstrukcije. Taj keš je i razlog zašto postoji TXLSWorkbookViewer.RefreshConditionalFormats: snimak je nepromenljiv, tako da ako izmenite prikačenu radnu svesku na licu mesta, agregatna statistika i razrešeni pragovi su zastareli 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

Šta evaluator neće uraditi za vas

Vredi jasno navesti tri granice. Klasičan TCondFormatRule.Evaluate za jednu ćeliju i TXLSXConditionalFormatEvaluator na nivou radnog lista su različite površine sa različitim mogućnostima, a onaj za jednu ćeliju namerno odbija agregatne i vizuelne porodice umesto da ih aproksimira — ako vam treba Top/Bottom ili color scale, izgradite evaluator. Relativni vremenski periodi zavise od časovnika mašine u trenutku evaluacije, tako da se pravilo timePeriod renderuje drugačije u PDF-u generisanom danas i onom generisanom sledeće nedelje, što je ispravno ponašanje, ali i dalje tiket podrške koji čeka da se desi ako se očekuje da vaša arhiva bude bajt-stabilna. Treća je gramatička, a ne tehnička: gramatika formule uslovnog formata zabranjuje strukturirane reference tabele, tako da pravilo ne može adresirati kolonu tabele 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 izveštaja, pipeline izvoza ili prilagođenu mrežu koja se mora slagati sa Excel-om ćelija po ćeliju, isti razrešen rezultat pokreće i prilagođenu VCL mrežu tabelarnog proračuna opisanu na drugom mestu na ovom blogu. Kompletna API dokumentacija, model pravila i probne verzije za HotXLS Delphi komponentu tabelarnog proračuna dostupni su na stranici proizvoda