Betinget formatering, rik tekst og cellestiler i Delphi med HotXLS

En regel for betinget formatering i OOXML er to atskilte ting som bærer ett navn. Betingelsen (en sammenligning, en formel, et tekstsvar) avgjør hvilke celler som kvalifiserer. Utseendet (en differensiell formatpost, dxf i ECMA-376-termer) avgjør hvordan de cellene ser ut. Excels dialogboks skjuler skjøten ved å tvinge deg til å fylle ut begge samtidig. HotXLS gjør ikke det. Lag en cellIs-regel fra Delphi og hopp over stilen, og regelen er gyldig, området er riktig, formelen evaluerer til sann på nøyaktig de riktige cellene, og ingenting endrer farge, fordi regelens instruks var «sann, mal ingenting». Det gapet mellom betingelse og konsekvens er det første som må stemme, og det står bak de fleste reglene som ser riktige ut i Manage Rules, men ikke fremhever noe

HotXLS skriver betinget formatering nativt inn i både BIFF8 .xls- og OOXML .xlsx-filer, og gjør det samme for rich text-strekk og en samlet cellestilmodell. De tre funksjonene deler mer kobling enn den flate API-overflaten antyder, og stedene der utdata avviker fra hensikten, er som regel skjøtene mellom dem

En betingelse trenger en konsekvens: dxf-stilen

På XLSX-arket kommer sammenligningsregler fra AddConditionalFormat, som tar et område, en operator fra TXLSXCfOperator, og en formel eller en literal, og deretter returnerer indeksen til den nye regelen inne i arkets ConditionalFormats-samling. Regelobjektet på den indeksen eksponerer en Style-egenskap, og det er der fremhevingen bor. Sett en fyll på den, og kvalifiserende celler tar fyllet. La den stå urørt, og du har bygget den usynlige regelen beskrevet ovenfor

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Idx: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('kpi.xlsx');
    Sheet := Book.Sheets[0];

    // Negativt avvik: lys rød fyll
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Duplikate ordre-ID-er flagges på samme måte
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Egendefinert formelregel: fremhev rader der faktisk verdi ikke når 90 % av målet
    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    Book.SaveAs('kpi-flagged.xlsx');
  finally
    Book.Free;
  end;
end;

Fargene her er 32-bits ARGB-verdier, så $FFFFC7CE er den «lyse røde» fra Excel du kjenner fra dialogboksen, med en fullstendig ugjennomsiktig alfabyte foran RGB-en. Hver regeltype som utløses av en per-celle-betingelse, følger samme opprett-og-så-still-form. Tekstmatcherne (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) returnerer en indeks du stiler etterpå, og det samme gjør AddCondFormatTop10, AddCondFormatAboveAverage, og detektorene for tomme celler og feil. Lær mønsteret én gang, så oppfører hele tekst- og sammenligningsfamilien seg likt

Datastolper, fargeskalaer og ikonsett maler seg selv

De visuelle regeltypene fungerer motsatt vei. De bærer utseendet sitt inne i regeldefinisjonen og ignorerer Style-egenskapen fullstendig. Tildel en fyll til en datastolperegel, og ingenting skjer, noe som virker som en feil helt til taksonomien klaffer: AddCondFormatDataBar tar stolpefargen som et direkte argument, de to- og trepunkts fargeskalaene tar endepunktfargene sine på samme måte, og AddCondFormatIconSet velger en av 26 ikonsett-typer som icsTrafficLights3. Det finnes ingen separat stilpost å glemme her, fordi det ikke finnes noen separat stilpost i det hele tatt

Parametrene som er verdt å tenke over ved disse kallene, er verdiankrene, typet som TXLSCfValueKind. Et stolpe- eller skalaendepunkt kan ligge ved områdets minimum eller maksimum, ved et literal-tall, ved en prosent eller en persentil, eller ved resultatet av en formel. Standardverdiene, min-av-området og maks-av-området, oppfører seg pent på ryddige demodata og forråder deg så på ekte data med uteliggere: én løpsk verdi strekker skalaen og flater ut hver eneste andre stolpe til en stubb. Når et dashbord er ment å leses på tvers av perioder, ankre endepunktene til faste tall eller persentiler i stedet, slik at en halv stolpe i mars betyr samme mengde som en halv stolpe i april. En autoskalert stolpe er bare sammenlignbar med seg selv

XLS-skriveren dekker fire regeltyper, ikke flere

Den gamle BIFF8-siden er ikke et mindre speilbilde av XLSX-siden; det er en bevisst delmengde. XLS-fasaden kan lage nøyaktig fire former for betinget regel, datastolper, tofargede skalaer, trefargede skalaer og ikonsett, sendt ut som CF12-poster i strømmen. Den har ingen opprettelses-API for cellIs-, uttrykks- eller tekstregler. Regler av de typene som allerede finnes i en fil du åpner, blir lest, beholdt og skrevet tilbake uendret, så å åpne og lagre en kundes .xls på nytt skader aldri formateringen den bar med seg inn. Det du ikke kan gjøre, er å generere terskelfremheving fra bunnen av inn i en .xls. Valgene der er å simulere det med ordinære cellefyll beregnet i koden, eller å gjøre leveransen til en .xlsx, der hele regelfamilien er tilgjengelig

Dette er en begrensning som må avklares før datalaget finnes, ikke etterpå, fordi den endrer filformatbeslutningen for alt som er dashbordformet. Et team som valgte .xls for kompatibilitet og deretter spesifiserer en KPI-rapport med cellIs-terskler, har valgt to ting som ikke passer sammen, og det billigste tidspunktet å oppdage det på, er ved formatbeslutningen fremfor tre uker inn i byggingen

Regelstabling, prioritet og overlappende områder

Ekte dashbord kjører sjelden bare én regel per område. En avvikskolonne kan bære en datastolpe for størrelsesorden, en cellIs-regel for den harde terskelen, og en uttrykksregel på radnivå over begge for eskaleringer. Hvert TXLSXConditionalFormat eksponerer en Priority-verdi, og Excel løser konkurrerende regler i prioritetsrekkefølge. Når to regler vil male samme celle, avgjøres vinneren av et tall du setter, ikke av hvilken rekkefølge en gjennomgår tilfeldigvis blar gjennom i Manage Rules-dialogen

Behandle prioritet slik et tegneprogram behandler z-rekkefølge. Tildel den med hensikt overalt hvor to regler kan nå de samme cellene, og la det stå gap mellom verdiene slik at en senere regel kan skyves inn uten å nummerere resten på nytt. Der regler ikke kan kollidere, si en datastolpe begrenset til kolonne E og en tekstregel begrenset til kolonne G, er opprettelsesrekkefølgen grei, og prioritet er ikke verdt oppmerksomheten. Bruk den oppmerksomheten på områdegrenser i stedet, fordi de kostbare feilene her nesten aldri er prioritetsinversjoner. De er områder som B2:B200 på en rapport som har vokst til 350 rader, der den udekkede halen gjengis som vanlige celler som ser akkurat ut som sunne data. Utled hvert regelområde fra den samme sluttradantallverdien som styrer diagramserier og valideringsområder andre steder i arbeidsboken, og halen slutter å falle av

Én verifiseringsvane er verdt kostnaden. Etter generering, åpne filen i Excel, velg det formaterte området, og gå gjennom Manage Rules én gang for hver malendring. Betinget formatering er et av de få områdene der den eneste autoritative renderen er applikasjonen som konsumerer filen, så en enhetstest over XML-en beviser at regelen ble skrevet, ikke at Excel maler den slik du mente. Ett minutt med visuell kontroll lukker det gapet

Rich text: mange formater inne i én celle

En rich text-celle i XLSX-modellen holder en liste med strekk, der hvert strekk er et tekstspenn pluss sine egne fontattributter. Du bygger listen til side som et TXLSXRichText-objekt, legger til strekk i den, og fester deretter hele greia til en celle. Eierskapsregelen er det som biter. Å tildele til Cell.RichText overfører eierskapet til det objektet til cellen, og cellen frigjør det under sin egen destruksjon. Frigjør det selv i tillegg, og du har en dobbelfrigjøring, den typen som forblir stille gjennom kjøringen som forårsaket den og dukker opp som en krasj et helt annet sted mye senere

var
  Rich: TXLSXRichText;
  Run: TXLSXRichTextRun;
begin
  Rich := TXLSXRichText.Create;
  Rich.AddRunText('Status: ');
  Run := Rich.AddRunText('OVERDUE');
  Run.Bold := True;
  Run.Color := $FFC00000;
  Run.ColorIsAuto := False;
  Run := Rich.AddRunText(' (escalated to regional manager)');
  Run.Italic := True;
  Sheet.Cells[2, 7].RichText := Rich;   // eierskapet flyttes til cellen: ikke kall Free
end;

Den eksplisitte ColorIsAuto := False er ikke valgfri pynt. Et strekk bærer et flagg for automatisk farge, og en fargetildeling respekteres først når det flagget er nullstilt. Sett Color og glem ColorIsAuto, og strekket kommer ut fet, men hardnakket svart, uten noen feilmelding som peker på årsaken. Strekk støtter også gjennomstreking, understrekingsvariantene og vertikal justering for hevet og senket skrift, mens PlainText flater hele listen tilbake til én enkelt streng når du trenger å eksportere eller diffe tekstinnholdet

Rich text på cellenivå finnes bare i XLSX. XLS-fasaden har ingen offentlig API for å skrive det, selv om strekk er tilgjengelig der på kommentarer og tekstbokser gjennom TextRuns, og rich strings lest fra en eksisterende .xls overlever en tur-retur intakt. Draget er det samme som med betinget formatering: alt som blander formater inne i én celle, hører hjemme i XLSX-skriveren

Stilbassenget og off-by-one-feilen som blir med i leveransen

Vanlig cellestiling i XLSX-modellen går gjennom samlede samlinger på arbeidsboken. Fonts.Add, Fills.AddSolid og Borders.Add registrerer hver sin definisjon og returnerer indeksen i bassenget. Disse indeksene er 0-baserte. Celleegenskapene som konsumerer dem, som FontIndex, reserverer 0 for «standard», så verdien du tildeler en celle, er bassengindeksen pluss én:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // bassengindeks, 0-basert
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // celleindeks, 1-basert

Dropp + 1, og hver overskrift faller tilbake til standardfonten. Det er ingen feilmelding og ingen advarsel, bare en arbeidsbok som ser ut som om ingen stilet den. Andreordensfeilen skjuler seg i løkken: å kalle Fonts.Add én gang per rad. Identiske fontdefinisjoner dedupliseres, så filen blir ikke korrupt, men arbeidet er bortkastet, og spesielt justeringsbassenget gir tilbake et ferskt objekt på hvert kall i stedet for å folde sammen duplikater. Bygg den håndfullen stiler én gang før løkken, og gjenbruk indeksene deres. På rapporter med hundretusener av rader er den ene endringen en av spakene som dekkes i ytelsesoptimalisering for store arbeidsbøker i HotXLS. Når du bare trenger et standard semantisk utseende, eksponerer begge fasadene ApplyBuiltinStyle på områder, som kartlegger til Excels innebygde Good-, Bad-, Neutral- og aksentstiler uten at du rører bassengene i det hele tatt

Betinget formatering, rich text og samlede stiler er den siste milen av en rapport, brukt etter at datamodellen og oppsettet er avgjort, og disse tidligere stadiene er temaet for malbasert rapportgenerering med HotXLS. Den fullstendige regel-, strekk- og stilreferansen finnes på produktsiden for HotXLS Delphi Component