Betinget formatering, Rich Text og cellestile i Delphi med HotXLS

En betinget formateringsregel i OOXML er to adskilte ting med ét navn. Betingelsen (en sammenligning, en formel, et tekstmatch) afgør, hvilke celler der kvalificerer. Udseendet (en differential format-post, dxf i ECMA-376-termer) afgør, hvordan de celler ser ud. Excels dialogboks skjuler skellet ved at lade dig udfylde begge dele på én gang. Det gør HotXLS ikke. Opret en cellIs-regel fra Delphi og spring stilen over, og reglen er gyldig, området er korrekt, formlen evaluerer til sandt på præcis de rigtige celler, og intet skifter farve, fordi reglens instruks var "sandt, mal ingenting". Det hul mellem betingelse og konsekvens er det første, man skal have styr på, og det står bag de fleste regler, der ser korrekte ud i Manage Rules, men alligevel ikke fremhæver noget

HotXLS skriver betinget formatering nativt til både BIFF8 .xls- og OOXML .xlsx-filer, og det samme gælder rich text-runs og en pooled celle-stilmodel. De tre funktioner deler mere af den underliggende ledningsføring, end den flade API-overflade antyder, og de steder, hvor output afviger fra hensigten, er som regel samlingerne mellem dem

En betingelse kræver en konsekvens: dxf-stilen

På XLSX-arket kommer sammenligningsregler fra AddConditionalFormat, som tager et område, en operator fra TXLSXCfOperator og en formel eller literal, og derefter returnerer indekset for den nye regel i arkets ConditionalFormats-samling. Regelobjektet på det indeks eksponerer en Style-egenskab, og det er der, fremhævningen bor. Sæt en udfyldning på den, og kvalificerende celler får udfyldningen. Lad den stå urørt, og du har bygget den usynlige regel, der er beskrevet ovenfor

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

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

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

    // Brugerdefineret formelregel: fremhæv rækker, hvor faktisk rammer under 90% af mål
    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;

Farver her er 32-bit ARGB-værdier, så $FFFFC7CE er det Excel "light red", du kender fra dialogboksen, med en fuldt uigennemsigtig alpha-byte foran RGB-værdien. Enhver regeltype, der udløses af en per-celle-betingelse, følger samme opret-og-så-stil-mønster. Tekstmatcherne (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) returnerer et indeks, du efterfølgende stiler, og det samme gør AddCondFormatTop10, AddCondFormatAboveAverage samt detektorerne for tomme celler og fejl. Lær mønstret én gang, så opfører hele tekst- og sammenligningsfamilien sig ens

Datalinjer, farveskalaer og ikonsæt maler sig selv

De visuelle regeltyper virker modsat. De bærer deres udseende inde i regeldefinitionen og ignorerer Style-egenskaben fuldstændigt. Tildel en udfyldning til en datalinje-regel, og der sker ingenting, hvilket ligner en fejl, indtil taksonomien går op for én: AddCondFormatDataBar tager linjefarven som et direkte argument, de to- og trepunkts farveskalaer tager deres endepunktsfarver på samme måde, og AddCondFormatIconSet vælger en af 26 ikonsæt-typer såsom icsTrafficLights3. Der er ingen separat stilpost at glemme her, fordi der slet ikke findes nogen separat stilpost

De parametre, det er værd at tænke over ved disse kald, er værdiankrene, typet som TXLSCfValueKind. Et bar- eller skala-endepunkt kan sidde ved områdets minimum eller maksimum, ved et bogstaveligt tal, ved en procent eller en percentil, eller ved resultatet af en formel. Standardværdierne, min-of-range og max-of-range, opfører sig pænt på ryddelige demodata og svigter dig derefter på rigtige data med outliers: én løbsk værdi strækker skalaen og flader alle andre linjer ud til en stump. Når et dashboard skal kunne læses på tværs af perioder, skal endepunkterne i stedet ankres til faste tal eller percentiler, så en halv linje i marts betyder samme mængde som en halv linje i april. En autoskaleret linje kan kun sammenlignes med sig selv

XLS-skriveren dækker fire regeltyper, ikke flere

Den gamle BIFF8-side er ikke et mindre spejl af XLSX-siden; den er en bevidst delmængde. XLS-facaden kan oprette præcis fire betingede regeltyper, datalinjer, to-farveskalaer, tre-farveskalaer og ikonsæt, udskrevet som CF12-poster i strømmen. Den har ingen opret-API til cellIs-, udtryks- eller tekstregler. Regler af de typer, der allerede findes i en fil, du åbner, bliver læst, bevaret og skrevet tilbage uændret, så det at åbne og gemme en kundes .xls igen aldrig ødelægger formatering, filen bar med sig. Det, du ikke kan, er at generere tærskelfremhævning fra bunden i en .xls. Mulighederne dér er enten at simulere det med almindelige celleudfyldninger beregnet i koden, eller at gøre leverancen til en .xlsx, hvor hele regelfamilien er i spil

Dette er en begrænsning, der skal afklares, før datalaget eksisterer, ikke bagefter, fordi den ændrer filformatbeslutningen for alt, der er dashboard-formet. Et team, der valgte .xls af hensyn til kompatibilitet og derefter specificerer en KPI-rapport med cellIs-tærskler, har valgt to ting, der ikke passer sammen, og det billigste tidspunkt at opdage det på er ved formatbeslutningen frem for tre uger inde i byggeriet

Regel-stakning, prioritet og overlappende områder

Rigtige dashboards kører sjældent kun én regel pr. område. En afvigelseskolonne kan have en datalinje for størrelse, en cellIs-regel for den hårde tærskel og en række-niveau udtryksregel oven over begge til eskaleringer. Hver TXLSXConditionalFormat eksponerer en Priority-værdi, og Excel afgør konkurrerende regler i prioritetsrækkefølge. Når to regler vil male samme celle, afgøres vinderen af et tal, du sætter, ikke af den rækkefølge, en reviewer tilfældigvis scroller igennem i Manage Rules-dialogboksen

Behandl prioritet, som et tegneprogram behandler z-order. Tildel den bevidst, hvor end to regler kan ramme de samme celler, og lad der være huller mellem værdierne, så en senere regel kan skydes ind uden at omnummerere resten. Hvor regler ikke kan kollidere, sig en datalinje begrænset til kolonne E og en tekstregel begrænset til kolonne G, er oprettelsesrækkefølgen fin, og prioritet er ikke opmærksomheden værd. Brug den opmærksomhed på områdegrænser i stedet, for de dyre fejl her er næsten aldrig prioritetsinversioner. De er områder som B2:B200 på en rapport, der er vokset til 350 rækker, hvor den udækkede hale gengives som almindelige celler, der ser nøjagtigt ud som sunde data. Udled hvert regelområde fra den samme endelige-rækkeantal-værdi, der styrer diagramserier og valideringsområder andre steder i projektmappen, og halen holder op med at falde af

Én verifikationsvane er sin plads værd. Efter generering skal filen åbnes i Excel, det formaterede område markeres, og Manage Rules gennemgås én gang for hver skabelonændring. Betinget formatering er et af de få områder, hvor den eneste autoritative renderer er det program, der læser filen, så en unit test over XML'en beviser, at reglen blev skrevet, ikke at Excel maler den, som du mente. Et minuts øjesyn lukker det hul

Rich text: mange formater i én celle

En rich text-celle i XLSX-modellen indeholder en liste af runs, hvor hver run er et tekststykke plus sine egne skriftattributter. Du bygger listen til side som et TXLSXRichText-objekt, tilføjer runs til det og knytter derefter det hele til en celle. Ejerskabsreglen er det, der bider. At tildele til Cell.RichText overdrager ejerskabet af det objekt til cellen, og cellen frigør det under sin egen destruktion. Frigør du det selv også, har du en double-free, den slags der forbliver tavs gennem det run, der forårsagede den, og først dukker op som et nedbrud et helt andet sted meget 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;   // ejerskabet flyttes til cellen: kald ikke Free
end;

Den eksplicitte ColorIsAuto := False er ikke valgfri pynt. Et run bærer et automatisk-farve-flag, og en farvetildeling respekteres først, når det flag er ryddet. Sæt Color og glem ColorIsAuto, og runet kommer ud fed, men stædigt sort, uden fejl der peger på årsagen. Runs understøtter også gennemstregning, understregningsvarianterne og lodret justering til hævet og sænket skrift, mens PlainText flader hele listen tilbage til én streng, når du skal eksportere eller diffe tekstindholdet

Rich text på celleniveau er kun til XLSX. XLS-facaden har ingen offentlig API til at skrive det, selvom runs dér er tilgængelige på kommentarer og tekstbokse via TextRuns, og rich strings, der læses fra en eksisterende .xls, overlever en rundtur intakt. Trækket er det samme som med betinget formatering: alt, der blander formater inde i en celle, hører hjemme i XLSX-skriveren

Stilpuljen og off-by-one-fejlen, der følger med

Almindelig cellestyling i XLSX-modellen går gennem poolede samlinger på projektmappen. Fonts.Add, Fills.AddSolid og Borders.Add registrerer hver en definition og returnerer dens indeks i puljen. De indekser er 0-baserede. De celle-side-egenskaber, der bruger dem, såsom FontIndex, reserverer 0 til "standard", så den værdi, du tildeler en celle, er pool-indekset plus én:

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

Udelad + 1, og hver overskrift falder tilbage til standardskriften. Der er ingen undtagelse og ingen advarsel, kun en projektmappe, der ser ud, som om ingen har stylet den. Fejlen af anden orden gemmer sig i løkken: at kalde Fonts.Add én gang pr. række. Identiske skriftdefinitioner bliver dedupliceret, så filen ikke bliver ødelagt, men arbejdet er spildt, og især justeringspuljen returnerer et nyt objekt ved hvert kald i stedet for at folde dubletter sammen. Byg de få stilarter én gang før løkken, og genbrug deres indekser. På rapporter med hundredtusindvis af rækker er den ene ændring et af de greb, der er dækket i noter om ydeevne for store projektmapper i HotXLS. Når du kun har brug for et standard semantisk udseende, eksponerer begge facader ApplyBuiltinStyle på områder, som kortlægger til Excels indbyggede Good, Bad, Neutral og accent-stilarter uden at du rører puljerne overhovedet

Betinget formatering, rich text og poolede stilarter er den sidste del af en rapport, som anvendes, efter datamodellen og layoutet er på plads, og de tidligere trin er emnet for skabelonbaseret rapportgenerering med HotXLS. Den fulde reference til regler, runs og stilarter findes på produktsiden for HotXLS-Delphi-komponenten