Villkorsstyrd formatering och rik text i HotXLS för Delphi

En regel för villkorsstyrd formatering i OOXML är två skilda saker under ett och samma namn. Villkoret (en jämförelse, en formel, en textmatchning) avgör vilka celler som kvalificerar sig. Utseendet (en differentiell formatpost, dxf i ECMA-376:s termer) avgör hur de cellerna ser ut. Excels dialogruta döljer skarven genom att låta dig fylla i båda samtidigt. HotXLS gör inte det. Skapa en cellIs-regel från Delphi och hoppa över stilen, så är regeln giltig, området korrekt, formeln utvärderas till sant på precis rätt celler, och ingenting byter färg, eftersom regelns instruktion var ”sant, måla ingenting”. Den glipan mellan villkor och följd är det första att få rätt, och den förklarar de flesta regler som ser korrekta ut i Hantera regler men ändå inte markerar något

HotXLS skriver villkorsstyrd formatering inbyggt i både BIFF8 .xls och OOXML .xlsx, och den gör detsamma för rika textkörningar och en poolad cellstilsmodell. De tre funktionerna delar mer kopplingar än den platta API-ytan antyder, och de ställen där utdatan glider från avsikten är oftast fogarna mellan dem

Ett villkor behöver en följd: dxf-stilen

I XLSX-kalkylbladet kommer jämförelseregler från AddConditionalFormat, som tar ett område, en operator ur TXLSXCfOperator, och en formel eller ett literalvärde, och sedan returnerar indexet för den nya regeln inuti bladets samling ConditionalFormats. Regelobjektet på det indexet exponerar en Style-egenskap, och det är där markeringen bor. Sätt en fyllning på den så tar kvalificerade celler fyllningen. Låt den vara orörd så har du byggt den osynliga regel som beskrevs ovan

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

    // Negativ avvikelse: ljusröd fyllning
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Dubblerade order-ID flaggas på samma sätt
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Egen formelregel: markera rader där utfallet missar 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;

Färger här är 32-bitars ARGB-värden, så $FFFFC7CE är den ”ljusröda” från Excel du känner igen från dialogrutan, med en helt ogenomskinlig alfabyte framför RGB-delen. Varje regelsort som utlöses på ett villkor per cell följer samma form av skapa-sedan-styla. Textmatcharna (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) returnerar ett index som du stylar efteråt, och det gör även AddCondFormatTop10, AddCondFormatAboveAverage samt detektorerna för tomma celler och fel. Lär dig mönstret en gång så beter sig hela familjen för text och jämförelser likadant

Datastaplar, färgskalor och ikonuppsättningar målar sig själva

De visuella regelsorterna fungerar tvärtom. De bär sitt utseende inuti regeldefinitionen och ignorerar egenskapen Style helt. Tilldela en fyllning till en datastapelregel och ingenting händer, vilket läses som en bugg tills taxonomin klickar på plats: AddCondFormatDataBar tar stapelfärgen som ett direkt argument, färgskalorna med två och tre punkter tar sina ändpunktsfärger på samma sätt, och AddCondFormatIconSet väljer en av 26 ikonuppsättningstyper såsom icsTrafficLights3. Det finns ingen separat stilpost att glömma här, eftersom det inte finns någon separat stilpost alls

Parametrarna värda att tänka på i de här anropen är värdeankarna, typade som TXLSCfValueKind. En stapel- eller skaländpunkt kan sitta vid områdets minimum eller maximum, vid ett literalt tal, vid en procent eller en percentil, eller vid resultatet av en formel. Standardvärdena, områdets min och max, uppför sig på städad demodata och sviker dig sedan på verklig data med avvikare: ett skenande värde sträcker ut skalan och plattar till varje annan stapel till en stump. När en instrumentpanel är tänkt att läsas över perioder, förankra ändpunkterna vid fasta tal eller percentiler istället, så att en halv stapel i mars betyder samma kvantitet som en halv stapel i april. En autoskalad stapel är bara jämförbar med sig själv

XLS-skrivaren täcker fyra regelsorter, inte fler

Den äldre BIFF8-sidan är ingen mindre spegelbild av XLSX-sidan; den är en medveten delmängd. XLS-fasaden kan skapa exakt fyra former av villkorsregler, datastaplar, tvåfärgsskalor, trefärgsskalor och ikonuppsättningar, avgivna som CF12-poster i strömmen. Den har inget skapande-API för cellIs-, uttrycks- eller textregler. Regler av de sorterna som redan bor i en fil du öppnar läses, behålls och skrivs tillbaka oförändrade, så att öppna och spara om en kunds .xls skadar aldrig formatering den bar med sig. Vad du inte kan göra är att generera tröskelmarkering från grunden in i en .xls. Valen där är att fejka det med vanliga cellfyllningar beräknade i kod, eller att göra leveransen till en .xlsx, där hela regelfamiljen ligger på bordet

Det här är en begränsning att avgöra innan datalagret finns, inte efter, eftersom den ändrar valet av filformat för allt som har form av en instrumentpanel. Ett team som valde .xls för kompatibilitetens skull och sedan specificerar en KPI-rapport med cellIs-trösklar har valt två saker som inte passar ihop, och den billigare tidpunkten att märka det är vid formatvalet snarare än tre veckor in i bygget

Regelstapling, prioritet och överlappande områden

Verkliga instrumentpaneler kör sällan en regel per område. En avvikelsekolumn kan bära en datastapel för storleksordning, en cellIs-regel för den hårda tröskeln, och en uttrycksregel på radnivå ovanför båda för eskaleringar. Varje TXLSXConditionalFormat exponerar ett Priority-värde, och Excel löser konkurrerande regler i prioritetsordning. När två regler vill måla samma cell avgörs vinnaren av ett tal du sätter, inte av vilken ordning en granskare råkar skrolla igenom i dialogrutan Hantera regler

Behandla prioritet som ett ritprogram behandlar z-ordning. Tilldela den med avsikt överallt där två regler kan nå samma celler, och lämna luckor mellan värdena så att en senare regel glider in utan att resten behöver numreras om. Där regler inte kan kollidera, säg en datastapel begränsad till kolumn E och en textregel begränsad till kolumn G, duger skapandeordningen och prioriteten är inte värd uppmärksamheten. Lägg den uppmärksamheten på områdesgränser istället, eftersom de dyra buggarna här nästan aldrig är prioritetsinversioner. De är områden som B2:B200 i en rapport som vuxit till 350 rader, där den otäckta svansen renderas som vanliga celler som ser precis ut som frisk data. Härled varje regelområde ur samma värde för slutligt radantal som driver diagramserier och valideringsområden på andra ställen i arbetsboken, så slutar svansen falla av

En verifieringsvana förtjänar sin plats. Öppna filen i Excel efter generering, markera det formaterade området och gå igenom Hantera regler en gång för varje mallförändring. Villkorsstyrd formatering är ett av få områden där den enda auktoritativa renderaren är applikationen som konsumerar filen, så ett enhetstest över XML:en bevisar att regeln skrevs, inte att Excel målar den som du menade. En minuts ögonkontroll täpper till den luckan

Rik text: många format inuti en cell

En cell med rik text i XLSX-modellen håller en lista av körningar, där varje körning är ett textspann plus sina egna typsnittsattribut. Du bygger listan vid sidan om som ett TXLSXRichText-objekt, lägger till körningar i den, och fäster sedan hela saken på en cell. Ägarskapsregeln är den del som biter. Tilldelning till Cell.RichText överlämnar ägarskapet av objektet till cellen, och cellen frigör det under sin egen destruktion. Frigör det själv också så har du en dubbelfrigöring, den sorten som håller tyst genom körningen som orsakade den och dyker upp som en krasch någon helt annanstans mycket senare

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;   // ägarskapet flyttar till cellen: anropa inte Free
end;

Det uttryckliga ColorIsAuto := False är ingen valfri utsmyckning. En körning bär en flagga för automatisk färg, och en färgtilldelning respekteras först när den flaggan rensats. Sätt Color och glöm ColorIsAuto så kommer körningen ut fet men envist svart, utan något fel att peka på orsaken. Körningar stöder också genomstrykning, understrykningsvarianterna och lodrät justering för upphöjd och nedsänkt text, medan PlainText plattar tillbaka hela listan till en enda sträng när du behöver exportera eller jämföra textinnehållet

Rik text på cellnivå finns bara i XLSX. XLS-fasaden har inget publikt API för att skriva den, även om körningar finns där på kommentarer och textrutor genom TextRuns, och rika strängar som lästs från en befintlig .xls överlever en rundtur intakta. Dragningen är densamma som med villkorsstyrd formatering: allt som blandar format inuti en cell hör hemma i XLSX-skrivaren

Stilpoolen och det plus-ett-fel som levereras

Vanlig cellstyling i XLSX-modellen går genom poolade samlingar på arbetsboken. Fonts.Add, Fills.AddSolid och Borders.Add registrerar var och en en definition och returnerar dess index i poolen. De indexen är 0-baserade. Egenskaperna på cellsidan som konsumerar dem, såsom FontIndex, reserverar 0 för ”standard”, så värdet du tilldelar en cell är poolindexet plus ett:

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

Utelämna + 1 och varje rubrik faller tillbaka på standardtypsnittet. Det finns inget undantag och ingen varning, bara en arbetsbok som ser ut som om ingen stylat den. Andrahandsmisstaget gömmer sig i loopen: att anropa Fonts.Add en gång per rad. Identiska typsnittsdefinitioner avdupliceras, så filen blir inte korrupt, men arbetet är bortkastat, och särskilt justeringspoolen lämnar tillbaka ett färskt objekt vid varje anrop istället för att vika ihop dubbletter. Bygg den handfull stilar du behöver en gång före loopen och återanvänd deras index. På rapporter med hundratusen rader är just den ändringen en av de spakar som täcks i prestandatrimning av stora arbetsböcker i HotXLS. När du bara behöver ett färdigt semantiskt utseende exponerar båda fasaderna ApplyBuiltinStyle på områden, vilket mappar mot Excels inbyggda stilar Bra, Dålig, Neutral och accentstilarna utan att du rör poolerna alls

Villkorsstyrd formatering, rik text och poolade stilar är sista milen i en rapport, tillämpade efter att datamodellen och layouten är avgjorda, och de tidigare stegen är ämnet för mallbaserad rapportgenerering med HotXLS. Den fullständiga referensen för regler, körningar och stilar finns på produktsidan för HotXLS Delphi Component