Vrednotenje pogojnega oblikovanja Excel v Delphiju s HotXLS

HotXLS je izvorna komponenta preglednic za Delphi in C++Builder, od različice 2.209.0 pa lahko odgovori na vprašanje, ki si ga Excel navadno pridrži zase: za natanko to celico, katera pravila pogojnega oblikovanja se sprožijo in v kakšno polnilo, pisavo, podatkovno vrstico ali ikono se razrešijo. Ta odgovor potrebujete v trenutku, ko je vaš izhod poročilo HTML, PDF ali mreža, ki jo rišete sami

To je drugačen problem od ustvarjanja pravil. Dve zgodnejši opombi pokrivata avtorsko stran: pogojno oblikovanje in slogi bogatega besedila obravnava pripenjanje pravil in diferencialnih oblik na obseg, razdelitev sidranih pogojnih oblik pa obravnava, kaj se zgodi z obsegom pravila, ko se vstavijo ali izbrišejo vrstice in stolpci. Obe sta strukturni. Ta je o semantiki: glede na delovni zvezek, ki že nosi pravila, izračunajte poudarek

Zakaj vam oblika datoteke ne pove, katere celice zasvetijo

Kratek odgovor je, da ECMA-376 in ISO 29500-1 opredeljujeta shrambo, ne vrednotenje. Element conditionalFormatting (§18.3.1.18) nosi sqref in seznam otrok cfRule (§18.3.1.10), vsako pravilo pa nosi type, neobvezen operator, priority, zastavico stopIfTrue, enega ali dva otroka formula, za vizualne družine pa nabor pragov cfvo. Vsak od teh zvesto opisuje, kaj je uporabnik nastavil, nobeden pa ni algoritem. Za polovico tipov pravil ta vrzel ni pomembna: cellIs z operator="greaterThan" pomeni večje od, containsText pa pomeni, da je podniz prisoten. Vrzel se odpre pri agregatnih družinah. Pravilo top10 z rank="10" in percent="1" nad 27 zapolnjenimi numeričnimi celicami poudari koliko celic? Dve celi sedem ni številka. Zaokroženje, pol navzdol ali pol navzgor — specifikacija molči, napačna izbira pa pomeni, da se vaš PDF ne ujema z delovnim zvezkom, ki ga ima stranka odprtega poleg

Pravila za posamezno celico in kje se ustavi TCondFormatRule.Evaluate

HotXLS je najprej prevzel cenejšo polovico. TCondFormatRule.Evaluate v lxCondFormat.pas, dodano v 2.199.0, odgovori, ali se eno pravilo sproži za eno celico, ne da bi kar koli vedelo o preostanku obsega. Obravnava osem primerjalnih operatorjev BIFF za cellIs (med, ni med, enako, ni enako, večje, manjše, večje ali enako, manjše ali enako), pravila expression proste oblike, vrednotena pri celici, tako da se relativne reference pravilno prestavijo, štiri besedilne predikate ter predikata za prazne in napake. Pragi izvirajo iz FFormula1 in FFormula2, razrešenih prek TXLSCalculator.GetRangeValue na položaju celice, obrnjene meje pa se zamenjajo namesto zavrnejo

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 del te metode je to, kar zavrne uganiti. top10, aboveAverage, belowAverage, duplicateValues in uniqueValues vrnejo False, ne ker bi bili težki, temveč ker jih iz ene celice ni mogoče odločiti — vsak od njih potrebuje statistiko čez celotno domeno. Štiri vizualne družine, dataBar, colorScale2, colorScale3 in iconSet, vrnejo False iz drugega razloga: nikoli ne proizvedejo logične vrednosti, temveč izrisni tovor, logičen tip vrnitve pa je zanje napačna oblika

Kako vrednotilnik na ravni delovnega lista prepreči ponovno pregledovanje lista?

Tako da izračuna vsako skupno količino enkrat, ob konstrukciji, in nikoli več. TXLSXConditionalFormatEvaluator v lxHandleX.pas je nespremenljiv posnetek za en delovni list, zgrajen prek TXLSXWorksheet.CreateConditionalFormatEvaluator, njegova celotna zasnova pa je obramba pred naivno implementacijo, kjer vsaka pobarvana celica sproži pregled celotnega obsega

V konstruktorju se zgodijo štiri stvari. Vsak različen večobmočni sqref se razčleni natanko enkrat v TXlsxCfRangeSnapshot, tako da si deset pravil, ki si delijo en obseg, deli en razčlenilni in en statistični prehod. Ta prehod pretoči povprečje, populacijski odklon, minimum in maksimum čez zapolnjene celice v enem samem sprehodu, urejeno numerično polje pa obdrži le, kadar ga pravilo Top/Bottom ali percentil dejansko potrebuje za statistike vrstnega reda. Ključi podvojitev in edinstvenosti se gradijo Unicode-varno in paketno razvrstijo enkrat namesto za vsako iskanje. Nato se os vrstic razreže na pasove ob vsaki meji območja, tako da EvaluateCell binarno išče pas in obišče le pravila, katerih obsegi lahko sploh dosežejo to vrstico

Četrta stvar je tista, ki je v obsegu najpomembnejša. Relativna formula pravila, kot je =A1>AVERAGE($A$1:$A$100), pomeni nekaj drugega v vsaki celici domene, očitna implementacija pa prevede sveže sintaktično drevo na celico. TXlsxCfRulePlan ga prevede enkrat in ponovno ovrednoti isto drevo prek povratljivih koordinatnih zamikov, kar ohrani obnašanje sider Excela brez alokacije sintaktičnega drevesa na celico. Pravila so nato razvrščena po priority, zadetek na pravilu z nastavljenim StopIfTrue pa prekine zanko, natanko tako, kot Excel skrajša krožno vrednotenje

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 dejansko zaokroži pravilo Top 10 odstotkov?

Navzdol, z minimumom ena, in vključi izenačenja na meji. To ni zapisano nikjer v ISO 29500-1 — pripeto je bilo s testiranjem Excela 16 z ročno zgrajenimi delovnimi zvezki in branjem, katere celice je aplikacija poudarila. HotXLS implementira natanko to: število uvrstitev je Floor(Count * Min(Rank, 100) / 100), dvignjeno na 1, kadar pristane pri nič, priklenjeno na zapolnjeno število, mejna vrednost pa se nato primerja z >=, tako da je vsaka celica, enaka meji, poudarjena, tudi kadar to preseže zahtevano število. Sedemindvajset vrednosti in pravilo 10 odstotkov poudarita dve celici, plus katere koli nadaljnje celice, izenačene z drugo

Pravila nad povprečjem so skrivala drugo nejasnost: aboveAverage z stdDev="1" izbere celice en standardni odklon nad povprečjem, vzorčni in populacijski odklon pa se razlikujeta zaradi Besselove popravke in se opazno ne strinjata na majhnih obsegih, kar je natanko tam, kjer se pogojno oblikovanje uporablja. Excel 16 uporablja populacijski odklon, HotXLS pa se ujema z njim, zastavica equalAverage pa naredi strogo primerjavo vključujočo le, kadar pas odklona ni v igri. Pravila podvojitev in edinstvenosti so vklopila identiteto ključa. Če ena celica nosi število 100, druga pa besedilo "100", ju Excel obravnava kot isti ključ podvojitve, zato HotXLS normalizira numerično besedilo v numerični prostor ključev namesto da bi primerjal surove nize. Prazne celice so zrcalni primer: pravo prazna celica sodeluje pri štetju obsega, sama pa ni oblikovana, zato prazne celice v stolpcu ne zasvetijo vse kot podvojitve druga druge

Barvne lestvice in nabori ikon: interpolacija in mejna pravila

Vizualne družine se razrešijo v številke, pripravljene za izris, ne v logične vrednosti, njihovo robno obnašanje pa je bilo pripeto na enak način. Za barvno lestvico z izrecnimi numeričnimi pragi HotXLS priklene delež položaja na zaprt interval nič do ena, nato pa interpolira po kanalu z obrezovanjem namesto zaokroževanja — vrednost pod minimalnim pragom dobi minimalno barvo namesto ekstrapolirane, tristopenjska lestvica izbere svoj par s primerjavo proti srednji stopnji, degenerirana lestvica, katere dva konca nosita isti prag, pa se strne v vrhnjo barvo namesto deljenja z nič. Nabori ikon so potrebovali nasprotno vrsto skrbi, ker vsak cfvo po prvem nosi svojo lastno strogost primerjave: HotXLS prebere ThresholdEqualsInclude na prag in ustrezno uporabi >= ali >, se sprehodi navzgor, tako da najvišji izpolnjeni prag zmaga indeks ikone. Obrnjen nabor obrne razrešeni indeks, ne pragov, prepisi za posamezno ikono lahko potegnejo simbol iz druge družine, vsak neveljaven prag pa prekine pravilo namesto da bi proizvedel verjetno videti napačno ikono

Hranjenje mreže, izvoza HTML in PDF iz enega rezultata

Ker EvaluateCell vrne popolnoma razrešen TXLSXCfCellResult — diferencialno polnilo in barvo pisave z že uporabljenim tonom teme, krepko, ležeče, podčrtano, id oblike števila, smerne pozitivne in negativne razsežnosti vrstice, položaj osi, družino in indeks ikone — vsak porabnik bere isti zapis in nihče od njih ne mora razumeti notranjosti pravil. HotXLS uporablja to eno pot za izvoz HTML, izvoz PDF in interaktivni pregledovalnik, kar je edini praktičen način, da preprečite trem izrisovalnikom, da bi se razšli. Različica 2.210.0 jo je vgradila v TXLSWorkbookViewer, ki predpomni en pripravljen vrednotilnik na aktiven delovni list in ga ponovno uporabi čez pomikanje, izbiro in ponovno risanje, ter ga sprosti, ko se delovni zvezek ali delovni list spremeni — ponovna gradnja posnetka ob vsakem Paint bi porazila celotno zasnovo v času konstrukcije. Ta predpomnilnik je tudi razlog, zakaj obstaja TXLSWorkbookViewer.RefreshConditionalFormats: posnetek je nespremenljiv, zato če pripeti delovni zvezek spremenite na mestu, so agregatne statistike in razrešeni pragi zastareli, dokler tega ne pokličete

// 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

Kaj vrednotilnik ne bo naredil namesto vas

Vredno je jasno navesti tri meje. Klasični TCondFormatRule.Evaluate za posamezno celico in TXLSXConditionalFormatEvaluator na ravni delovnega lista sta različni površini z različnimi zmogljivostmi, tisti za posamezno celico pa namerno zavrne agregatne in vizualne družine namesto da bi jih približal — če potrebujete Top/Bottom ali barvno lestvico, zgradite vrednotilnik. Relativna časovna obdobja so odvisna od ure stroja v trenutku vrednotenja, zato se pravilo timePeriod v PDF-ju, ustvarjenem danes, izriše drugače kot v tistem, ustvarjenem naslednji teden, kar je pravilno obnašanje in vseeno zahtevek za podporo, ki čaka, da se zgodi, če pričakujete, da bo vaš arhiv bajtno stabilen. Tretja meja je slovnična, ne tehnična: slovnica formule pogojne oblike prepoveduje strukturirane sklice na tabele, zato se pravilo ne more sklicevati na stolpec tabele po imenu, kot to lahko formula delovnega lista, kar je omejitev formata, ne implementacije

Če gradite izhod poročila, cevovod izvoza ali mrežo po meri, ki se mora ujemati z Excelom celica za celico, isti razrešeni rezultat poganja tudi mrežo VCL preglednice po meri, opisano drugje na tem blogu. Celotna API dokumentacija, model pravil in preizkusni prenosi za komponento preglednic HotXLS Delphi so na voljo na strani izdelka