Excelin ehdollisen muotoilun arviointi Delphissä HotXLS:llä

HotXLS on natiivi taulukkolaskentakomponentti Delphille ja C++Builderille, ja versiosta 2.209.0 lähtien se osaa vastata kysymykseen, jonka Excel normaalisti pitää itsellään: mitkä ehdollisen muotoilun säännöt laukeavat tälle täsmälleen tälle solulle, ja mihin täyttöön, fonttiin, datapalkkiin tai kuvakkeeseen ne ratkeavat. Tuo vastaus on se, mitä tarvitset heti kun tulosteesi on HTML-raportti, PDF tai ruudukko, jonka maalaat itse

Tämä on eri ongelma kuin sääntöjen luominen. Kaksi aiempaa artikkelia käsittelevät laadintapuolta: ehdollinen muotoilu ja rikastekstityylit käsittelee sääntöjen ja erotteluformaattien liittämistä alueeseen, ja ankkuroitujen ehdollisten formaattien osiointi käsittelee sitä, mitä säännön alueelle tapahtuu, kun rivejä ja sarakkeita lisätään tai poistetaan. Molemmat ovat rakenteellisia. Tämä käsittelee semantiikkaa: kun työkirja jo kantaa sääntöjä, laske korostus

Miksi tiedostomuoto ei kerro, mitkä solut syttyvät

Lyhyt vastaus on, että ECMA-376 ja ISO 29500-1 määrittelevät tallennuksen, eivät arvioinnin. conditionalFormatting-elementti (§18.3.1.18) kantaa sqref:n ja luettelon cfRule-lapsia (§18.3.1.10), ja jokainen sääntö kantaa type:n, valinnaisen operator:n, priority:n, stopIfTrue-lipun, yhden tai kaksi formula-lasta, ja visuaalisille perheille joukon cfvo-kynnysarvoja. Jokainen näistä kuvaa uskollisesti sen, mitä käyttäjä konfiguroi, eikä yksikään niistä ole algoritmi. Puolelle sääntötyypeistä tuo kuilu ei merkitse: cellIs, jonka operator="greaterThan", tarkoittaa suurempi kuin, ja containsText tarkoittaa, että osamerkkijono on läsnä. Kuilu avautuu kokoavissa perheissä. top10-sääntö, jonka rank="10" ja percent="1", 27 täytetyn numeerisen solun yli, korostaa kuinka montaa solua? Kaksi pilkku seitsemän ei ole luku. Pyöristys, alaspäin vai ylöspäin — spesifikaatio on hiljaa, ja väärän valinnan tekeminen tarkoittaa, että PDF:si on eri mieltä työkirjan kanssa, joka asiakkaalla on auki vieressä

Yksisolusäännöt ja missä TCondFormatRule.Evaluate pysähtyy

HotXLS otti halvemman puoliskon ensin. TCondFormatRule.Evaluate tiedostossa lxCondFormat.pas, lisätty versiossa 2.199.0, vastaa, laukeaako yksi sääntö yhdelle solulle tietämättä mitään muusta alueesta. Se käsittelee kahdeksan BIFF-vertailuoperaattoria cellIs:n takana (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), vapaamuotoiset expression-säännöt arvioituna solussa, jotta suhteelliset viittaukset uudelleenankkuroituvat oikein, neljä tekstipredikaattia, sekä tyhjä- ja virhepredikaatit. Kynnysarvot tulevat FFormula1:stä ja FFormula2:sta ratkaistuina TXLSCalculator.GetRangeValue:n kautta solun sijainnissa, ja käänteiset rajat vaihdetaan eikä hylätä

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;

Rehellinen osa tuosta metodista on se, mitä se kieltäytyy arvaamasta. top10, aboveAverage, belowAverage, duplicateValues ja uniqueValues palauttavat False, ei siksi että ne olisivat vaikeita, vaan koska ne ovat ratkaisemattomia yhdestä solusta — jokainen niistä tarvitsee tilaston koko toimialueen yli. Neljä visuaalista perhettä, dataBar, colorScale2, colorScale3 ja iconSet, palauttavat False eri syystä: ne eivät koskaan tuota totuusarvoa lainkaan, ne tuottavat renderöintikuorman, ja Boolean-paluutyyppi on niille väärä muoto

Miten työkirjatason arvioija välttää arkin uudelleenskannaamisen?

Laskemalla jokaisen jaetun suureen kerran, rakennettaessa, eikä koskaan uudelleen. TXLSXConditionalFormatEvaluator tiedostossa lxHandleX.pas on muuttumaton tilannekuva yhdelle työarkille, rakennettu TXLSXWorksheet.CreateConditionalFormatEvaluator:n kautta, ja koko sen suunnittelu on puolustus naiivia toteutusta vastaan, jossa jokainen maalattu solu laukaisee täyden alueskannauksen

Neljä asiaa tapahtuu konstruktorissa. Jokainen erillinen moniosainen sqref jäsennetään täsmälleen kerran TXlsxCfRangeSnapshot:iin, joten kymmenen sääntöä, jotka jakavat yhden alueen, jakavat yhden jäsennyksen ja yhden tilastokierroksen. Tuo kierros suoratoistaa keskiarvon, populaatiokeskihajonnan, minimin ja maksimin täytettyjen solujen yli yhdessä kävelyssä, ja säilyttää järjestetyn numeerisen taulukon vain, kun Top/Bottom- tai prosenttipistesääntö todella tarvitsee järjestystilastoja. Kaksoiskappale- ja uniikki-avaimet rakennetaan Unicode-turvallisesti ja eräjärjestetään kerran erikoisen hakemisen sijaan. Sitten rivi-akseli leikataan kaistoihin jokaisessa aluerajassa, joten EvaluateCell binäärihakee kaistan ja vierailee vain säännöissä, joiden alueet voivat mahdollisesti saavuttaa tuon rivin

Neljäs on se, joka merkitsee eniten laajassa mittakaavassa. Suhteellinen sääntökaava kuten =A1>AVERAGE($A$1:$A$100) tarkoittaa eri asiaa toimialueen jokaisessa solussa, ja ilmeinen toteutus kääntäisi tuoreen syntaksipuun per solu. TXlsxCfRulePlan kääntää sen kerran ja arvioi saman puun uudelleen palautuvien koordinaattisiirtymien kautta, mikä säilyttää Excelin ankkurikäyttäytymisen ilman syntaksipuun varausta per solu. Säännöt kerrostetaan sitten priority:n mukaan, ja osuma säännössä, jonka StopIfTrue on asetettu, katkaisee silmukan, täsmälleen kuten Excel oikosulkee

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;

Miten Excel todella pyöristää Top 10 -prosenttisäännön?

Se pyöristää alaspäin, vähintään yhteen, ja se sisällyttää tasapelit rajakohdassa. Sitä ei ole kirjoitettu mihinkään ISO 29500-1:ssä — se lukittiin luotaamalla Excel 16:ta käsintehdyillä työkirjoilla ja lukemalla takaisin, mitkä solut sovellus korosti. HotXLS toteuttaa täsmälleen sen: sijalukumäärä on Floor(Count * Min(Rank, 100) / 100), korotettuna yhteen kun se päätyy nollaan, rajattuna täytettyyn määrään, ja rajakohdan arvo verrataan sitten >=:llä, joten jokainen solu, joka on yhtä suuri kuin raja, korostetaan, vaikka se ylittäisi pyydetyn määrän. Kaksikymmentäseitsemän arvoa ja 10 prosentin sääntö korostavat kaksi solua, plus mitkä tahansa lisäsolut, jotka ovat tasapelissä toisen kanssa

Keskiarvon-yläpuoli-säännöt piilottivat toisen epäselvyyden: aboveAverage, jonka stdDev="1", valitsee solut yhden keskihajonnan keskiarvon yläpuolelle, mutta otos- ja populaatiokeskihajonta eroavat Bessel-korjauksella ja ne ovat näkyvästi eri mieltä pienillä alueilla, mikä on juuri se, missä ehdollista muotoilua käytetään. Excel 16 käyttää populaatiokeskihajontaa, ja HotXLS täsmää sen kanssa, ja equalAverage-lippu tekee tiukasta vertailusta sisällyttävän vain kun mikään hajontakaista ei ole käytössä. Kaksoiskappale- ja uniikki-säännöt kääntyivät sen sijaan avainidentiteettiin. Jos yksi solu pitää sisällään luvun 100 ja toinen tekstin "100", Excel kohtelee niitä samana kaksoiskappaleen avaimena, joten HotXLS normalisoi numeerisen tekstin numeeriseen avainavaruuteen raakojen merkkijonojen vertaamisen sijaan. Tyhjät solut ovat peilikuvatapaus: todellinen tyhjä solu osallistuu aluelaskentaan mutta ei ole itse tyylitelty, joten sarakkeen tyhjät solut eivät kaikki syty toistensa kaksoiskappaleina

Väriasteikot ja kuvakejoukot: interpolointi ja rajasäännöt

Visuaaliset perheet ratkeavat renderöintivalmiiksi luvuiksi totuusarvojen sijaan, ja niiden reunakäyttäytyminen lukittiin samalla tavalla. Väriasteikolle, jossa on nimenomaiset numeeriset kynnysarvot, HotXLS rajaa sijaintimurtoluvun suljettuun väliin nolla–yksi, sitten interpoloi per kanava katkaisemalla pyöristämisen sijaan — alle minimipysäkin oleva arvo saa minimivärin ekstrapoloidun sijaan, kolmipysäkkinen asteikko valitsee parinsa vertaamalla keskipysäkkiin, ja rappeutunut asteikko, jonka molemmat päät kantavat saman kynnysarvon, romahtaa ylimpään väriin nollalla jakamisen sijaan. Kuvakejoukot tarvitsivat vastakkaisenlaista huolellisuutta, koska jokainen cfvo ensimmäisen jälkeen kantaa oman vertailun tiukkuutensa: HotXLS lukee ThresholdEqualsInclude:n per kynnysarvo ja soveltaa >= tai > sen mukaisesti, kävellen ylöspäin niin että korkein täytetty kynnysarvo voittaa kuvakeindeksin. Käänteinen joukko kääntää ratkaistun indeksin kynnysarvojen sijaan, kuvakekohtaiset ohitukset voivat poimia glyyfin eri perheestä, ja mikä tahansa virheellinen kynnysarvo keskeyttää säännön sen sijaan, että tuottaisi uskottavan näköisen väärän kuvakkeen

Ruudukon, HTML-viennin ja PDF:n ruokkiminen yhdestä tuloksesta

Koska EvaluateCell palauttaa täysin ratkaistun TXLSXCfCellResult:n — erotteluttäyttö ja fonttiväri teemasävytys jo sovellettuna, lihavointi, kursivointi, alleviivaus, lukumuoto-id, suunnalliset positiiviset ja negatiiviset palkin ulottuvuudet, akselin sijainti, kuvakeperhe ja indeksi — jokainen kuluttaja lukee saman tietueen, eikä yhdenkään niistä tarvitse ymmärtää säännön sisäisiä toimintoja. HotXLS käyttää tuota yhtä polkua HTML-vientiin, PDF-vientiin ja interaktiiviseen katseluohjelmaan, mikä on ainoa käytännöllinen tapa estää kolmea renderöijää ajautumasta erilleen. Versio 2.210.0 kytki sen TXLSWorkbookViewer:iin, joka välimuistittaa yhden valmisteltu arvioijan per aktiivinen työarkki ja käyttää sitä uudelleen vierityksen, valinnan ja uudelleenmaalauksen yli, vapauttaen sen kun työkirja tai työarkki vaihtuu — tilannekuvan uudelleenrakentaminen jokaisella Paint-kutsulla tekisi tyhjäksi koko rakennushetken suunnittelun. Tuo välimuisti on myös syy, miksi TXLSWorkbookViewer.RefreshConditionalFormats on olemassa: tilannekuva on muuttumaton, joten jos muokkaat liitettyä työkirjaa paikallaan, kootut tilastot ja ratkaistut kynnysarvot ovat vanhentuneita, kunnes kutsut sitä

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

Mitä arvioija ei tee puolestasi

Kolme rajaa on syytä sanoa suoraan. Klassinen yksisolu-TCondFormatRule.Evaluate ja työkirjatason TXLSXConditionalFormatEvaluator ovat eri pintoja eri kyvyillä, ja yksisolu-versio kieltäytyy tarkoituksella kokoavista ja visuaalisista perheistä sen sijaan, että approksimoisi niitä — jos tarvitset Top/Bottom-sääntöä tai väriasteikkoa, rakenna arvioija. Suhteelliset ajanjaksot riippuvat konekellosta arviointihetkellä, joten timePeriod-sääntö renderöityy eri tavalla tänään generoidussa PDF:ssä ja ensi viikolla generoidussa, mikä on oikea käyttäytyminen mutta silti tukipyyntö odottamassa tapahtumista, jos arkistosi odotetaan olevan tavu tavulta vakaa. Kolmas on kieliopillinen eikä tekninen: ehdollisen muotoilun kaavakielioppi kieltää strukturoidut taulukkoviittaukset, joten sääntö ei voi osoittaa taulukon saraketta nimellä samalla tavalla kuin työarkin kaava voi, ja se on formaatin rajoitus eikä toteutuksen

Jos rakennat raporttitulostetta, vientiputkea tai mukautettua ruudukkoa, jonka täytyy täsmätä Excelin kanssa solu solulta, sama ratkaistu tulos ohjaa myös mukautettua VCL-taulukkolaskentaruudukkoa, joka on kuvattu muualla tällä blogilla. Täysi API-dokumentaatio, sääntömalli ja kokeiluversion lataukset HotXLS Delphi -taulukkolaskentakomponentille löytyvät tuotesivulta