Evaluering av Excel betinget formatering i Delphi med HotXLS

HotXLS er en native regnearkkomponent for Delphi og C++Builder, og siden versjon 2.209.0 kan den svare på spørsmålet Excel normalt holder for seg selv: for akkurat denne cellen, hvilke regler for betinget formatering utløses, og hvilken fylling, font, datastolpe eller ikon løser de seg til. Det svaret er hva du trenger i det øyeblikket utdataen din er en HTML-rapport, en PDF, eller et rutenett du tegner selv

Dette er et annet problem enn å opprette regler. To tidligere notater dekker forfattersiden: betinget formatering og rikeksttekststiler tar for seg å knytte regler og differensielle formater til et område, og partisjonering av forankrede betingede formater tar for seg hva som skjer med et regelområde når rader og kolonner settes inn eller slettes. Begge er strukturelle. Denne handler om semantikk: gitt en arbeidsbok som allerede bærer regler, beregne uthevingen

Hvorfor filformatet ikke forteller deg hvilke celler som lyser opp

Det korte svaret er at ECMA-376 og ISO 29500-1 definerer lagring, ikke evaluering. Et conditionalFormatting-element (§18.3.1.18) bærer en sqref og en liste med cfRule-barn (§18.3.1.10), og hver regel bærer en type, en valgfri operator, en priority, et stopIfTrue-flagg, ett eller to formula-barn, og for de visuelle familiene et sett med cfvo-terskler. Hver eneste av disse beskriver trofast hva brukeren konfigurerte, og ingen av dem er en algoritme. For halvparten av regeltypene betyr ikke det gapet noe: cellIs med operator="greaterThan" betyr større enn, og containsText betyr at delstrengen er til stede. Gapet åpner seg på aggregatfamiliene. En top10-regel med rank="10" og percent="1" over 27 utfylte numeriske celler uthever hvor mange celler? To komma sju er ikke et tall. Rund av, avrund nedover eller oppover — spesifikasjonen tier, og å velge feil betyr at PDF-en din er uenig med arbeidsboken kunden har åpen ved siden av

Enkeltcelle-regler og hvor TCondFormatRule.Evaluate stopper

HotXLS tok den billige halvparten først. TCondFormatRule.Evaluate i lxCondFormat.pas, lagt til i 2.199.0, svarer på om én regel utløses for én celle uten å vite noe om resten av området. Den håndterer de åtte BIFF-sammenligningsoperatorene bak cellIs (mellom, ikke mellom, lik, ulik, større enn, mindre enn, større eller lik, mindre eller lik), frittstående expression-regler evaluert ved cellen slik at relative referanser omregnes korrekt, de fire tekstpredikatene, og blank- og feil-predikatene. Terskler kommer fra FFormula1 og FFormula2 løst opp gjennom TXLSCalculator.GetRangeValue ved celleposisjonen, og omvendte grenser byttes i stedet for å avvises

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;

Den ærlige delen av den metoden er hva den nekter å gjette. top10, aboveAverage, belowAverage, duplicateValues og uniqueValues returnerer False, ikke fordi de er vanskelige, men fordi de er ubestembare fra én celle — hver av dem trenger en statistikk over hele domenet. De fire visuelle familiene, dataBar, colorScale2, colorScale3 og iconSet, returnerer False av en annen grunn: de produserer aldri en boolean i det hele tatt, de produserer en render-nyttelast, og en boolsk returtype er feil form for dem

Hvordan unngår en evaluator på arkenivå å skanne arket på nytt?

Ved å beregne hver delt størrelse én gang, ved konstruksjon, og aldri igjen. TXLSXConditionalFormatEvaluator i lxHandleX.pas er et uforanderlig øyeblikksbilde for ett regneark, bygget gjennom TXLSXWorksheet.CreateConditionalFormatEvaluator, og hele designet dens er et forsvar mot den naive implementasjonen hvor hver malte celle utløser en full skanning av området

Fire ting skjer i konstruktøren. Hver distinkte flerområdes-sqref parses nøyaktig én gang inn i en TXlsxCfRangeSnapshot, slik at ti regler som deler ett område deler én parsing og ett statistikkpass. Det passet strømmer gjennomsnitt, populasjonsavvik, minimum og maksimum over de utfylte cellene i én enkelt gjennomgang, og beholder bare et ordnet numerisk array når en Top/Bottom- eller persentilregel faktisk trenger ordensstatistikk. Duplikat- og unike nøkler bygges Unicode-sikkert og batch-sorteres én gang i stedet for per oppslag. Deretter deles radaksen inn i bånd ved hver områdegrense, slik at EvaluateCell binærsøker et bånd og bare besøker regler hvis områder mulig kan nå den raden

Det fjerde er det som betyr mest i stor skala. En relativ regelformel som =A1>AVERAGE($A$1:$A$100) betyr noe forskjellig i hver celle i domenet, og den opplagte implementasjonen kompilerer et ferskt syntaktre per celle. TXlsxCfRulePlan kompilerer det én gang og re-evaluerer samme tre gjennom reversible koordinatforskyvninger, som bevarer Excels ankeroppførsel uten en syntaktre-allokering per celle. Regler lagdeles deretter etter priority, og et treff på en regel hvis StopIfTrue er satt bryter løkken, nøyaktig slik Excel kortslutter

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;

Hvordan runder Excel egentlig av en Top 10-prosent-regel?

Den runder ned, med et minimum på én, og den inkluderer likestillinger ved terskelen. Det står ikke skrevet noe sted i ISO 29500-1 — det ble fastslått ved å teste Excel 16 med håndbygde arbeidsbøker og lese tilbake hvilke celler applikasjonen uthevet. HotXLS implementerer nøyaktig det: rangantallet er Floor(Count * Min(Rank, 100) / 100), hevet til 1 når det havner på null, klemt til det utfylte antallet, og terskelverdien sammenlignes deretter med >= slik at hver celle lik grensen uthevet selv når det overstiger det forespurte antallet. Tjuesju verdier og en 10 prosent-regel uthever to celler, pluss eventuelle ytterligere celler som er likestilt med den andre

Over gjennomsnitt-regler skjulte en andre tvetydighet: aboveAverage med stdDev="1" velger celler ett standardavvik over gjennomsnittet, men utvalgs- og populasjonsavvik skiller seg med Bessel-korreksjonen og de er synlig uenige på små områder, som er nøyaktig hvor betinget formatering blir brukt. Excel 16 bruker populasjonsavviket, og HotXLS matcher det, med equalAverage-flagget som gjør den strenge sammenligningen inkluderende bare når ikke noe avviksbånd er i spill. Duplikat- og unike regler viste seg å avhenge av nøkkelidentitet i stedet. Hvis én celle inneholder tallet 100 og en annen inneholder teksten «100», behandler Excel dem som samme duplikat-nøkkel, så HotXLS normaliserer numerisk tekst inn i det numeriske nøkkelrommet i stedet for å sammenligne rå strenger. Tomme celler er speiltilfellet: en genuint tom celle deltar i områdetellingen, men er ikke selv stilsatt, så de tomme cellene i en kolonne lyser ikke alle opp som duplikater av hverandre

Fargeskalaer og ikonsett: interpolasjon og grenseregler

De visuelle familiene løser seg til render-klare tall i stedet for booleaner, og deres kantoppførsel ble fastslått på samme måte. For en fargeskala med eksplisitte numeriske terskler klemmer HotXLS posisjonsbrøken til det lukkede intervallet null til én, og interpolerer deretter per kanal med avkortning i stedet for avrunding — en verdi under minimum får minimumsfargen i stedet for en ekstrapolert en, en tre-stopp-skala velger sitt par ved å sammenligne mot midtpunktstoppen, og en degenerert skala hvis to ender bærer samme terskel kollapser til toppfargen i stedet for å dele på null. Ikonsett trengte den motsatte typen omsorg, fordi hver cfvo etter den første bærer sin egen sammenligningsstrenghet: HotXLS leser ThresholdEqualsInclude per terskel og anvender >= eller > deretter, og går oppover slik at den høyeste oppfylte terskelen vinner ikonindeksen. Et reversert sett vender den løste indeksen i stedet for terskler, per-ikon-overstyringer kan hente et symbol fra en annen familie, og enhver ugyldig terskel avbryter regelen i stedet for å produsere et plausibelt-utseende feil ikon

Å mate et rutenett, en HTML-eksport og en PDF fra ett resultat

Fordi EvaluateCell returnerer en fullstendig løst TXLSXCfCellResult — differensiell fyll- og fontfarge med temanyanse allerede anvendt, fet, kursiv, understreket, tallformat-id, retningsbestemte positive og negative stolpelengder, akseposisjon, ikonfamilie og indeks — leser hver forbruker samme post, og ingen av dem trenger å forstå regelinterna. HotXLS bruker den samme stien for HTML-eksport, PDF-eksport og den interaktive visningen, som er den eneste praktiske måten å holde tre renderere fra å drive fra hverandre. Versjon 2.210.0 koblet det inn i TXLSWorkbookViewer, som cacher én forberedt evaluator per aktivt regneark og gjenbruker den på tvers av rulling, utvalg og omtegning, og frigir den når arbeidsboken eller regnearket endres — å gjenoppbygge øyeblikksbildet ved hver Paint ville beseire hele konstruksjonstids-designet. Den cachen er også grunnen til at TXLSWorkbookViewer.RefreshConditionalFormats finnes: øyeblikksbildet er uforanderlig, så hvis du muterer den tilknyttede arbeidsboken direkte, er de aggregerte statistikkene og løste tersklene foreldede til du kaller den

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

Hva evaluatoren ikke gjør for deg

Tre grenser er verdt å nevne tydelig. Den klassiske enkeltcelle-TCondFormatRule.Evaluate og arknivå-TXLSXConditionalFormatEvaluator er forskjellige flater med forskjellige egenskaper, og enkeltcelle-varianten avslår bevisst aggregat- og visuelle familiene i stedet for å tilnærme dem — hvis du trenger Top/Bottom eller en fargeskala, bygg evaluatoren. Relative datoperioder avhenger av maskinklokken ved evalueringstidspunktet, så en timePeriod-regel rendres forskjellig i en PDF generert i dag og en generert neste uke, som er korrekt oppførsel, men fortsatt en supportsak som venter på å skje hvis arkivet ditt forventes å være byte-stabilt. Den tredje er grammatisk snarere enn teknisk: grammatikken for betinget formatering-formler forbyr strukturerte tabellreferanser, så en regel kan ikke adressere en tabellkolonne ved navn slik en regnearksformel kan, og det er en begrensning ved formatet snarere enn ved implementasjonen

Hvis du bygger rapportutdata, en eksportpipeline eller et tilpasset rutenett som må stemme overens med Excel celle for celle, driver det samme løste resultatet også det tilpassede VCL-regnearksrutenettet beskrevet andre steder på denne bloggen. Full API-dokumentasjon, regelmodellen og prøvenedlastinger for HotXLS Delphi-regnearkkomponent er tilgjengelig på produktsiden