HotXLS voorwaardelijke opmaak en rich text in Delphi

Een regel voor voorwaardelijke opmaak in OOXML is twee losse dingen onder één naam. De voorwaarde (een vergelijking, een formule, een tekstovereenkomst) bepaalt welke cellen in aanmerking komen. Het uiterlijk (een differential format-record, dxf in ECMA-376-termen) bepaalt hoe die cellen eruitzien. Het dialoogvenster van Excel verbergt de naad door u beide tegelijk te laten invullen. HotXLS doet dat niet. Maak vanuit Delphi een cellIs-regel en sla de stijl over, en de regel is geldig, het bereik klopt, de formule evalueert op precies de juiste cellen naar waar, en er verandert niets van kleur, omdat de instructie van de regel luidde "waar, schilder niets". Dat gat tussen voorwaarde en gevolg is het eerste wat u goed moet krijgen, en het verklaart de meeste regels die in Regels beheren correct ogen en toch niets markeren

HotXLS schrijft voorwaardelijke opmaak natief in zowel BIFF8 .xls- als OOXML .xlsx-bestanden, en doet hetzelfde voor rich text-runs en een gepoold celstijlmodel. De drie functies delen meer bedrading dan het vlakke API-oppervlak suggereert, en de plekken waar de uitvoer van de bedoeling afdrijft zijn meestal de verbindingen ertussen

Een voorwaarde heeft een gevolg nodig: de dxf-stijl

Op het XLSX-werkblad komen vergelijkingsregels van AddConditionalFormat, die een bereik, een operator uit TXLSXCfOperator en een formule of letterlijke waarde neemt, en dan de index van de nieuwe regel binnen de ConditionalFormats-collectie van het blad retourneert. Het regelobject op die index stelt een Style-eigenschap beschikbaar, en daar woont de markering. Zet er een vulling op en de kwalificerende cellen krijgen die vulling. Laat haar ongemoeid en u hebt de hierboven beschreven onzichtbare regel gebouwd

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

    // Negatieve afwijking: lichtrode vulling
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Dubbele order-ID's worden op dezelfde manier gemarkeerd
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Eigen formuleregel: markeer rijen waar de realisatie onder 90% van het doel blijft
    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;

Kleuren zijn hier 32-bits ARGB-waarden, dus $FFFFC7CE is het "lichtrood" van Excel dat u uit het dialoogvenster kent, met een volledig ondoorzichtige alfabyte vóór de RGB. Elke regelsoort die op een voorwaarde per cel afgaat volgt dezelfde vorm van eerst maken, dan stylen. De tekstvergelijkers (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) retourneren een index die u daarna stylet, en dat geldt ook voor AddCondFormatTop10, AddCondFormatAboveAverage en de detectoren voor lege cellen en fouten. Leer het patroon één keer en de hele familie van tekst en vergelijkingen gedraagt zich hetzelfde

Gegevensbalken, kleurschalen en pictogramsets schilderen zichzelf

De visuele regelsoorten werken andersom. Zij dragen hun uiterlijk in de regeldefinitie zelf en negeren de Style-eigenschap volledig. Wijs een vulling toe aan een gegevensbalkregel en er gebeurt niets, wat als een bug leest totdat de taxonomie op zijn plaats valt: AddCondFormatDataBar neemt de balkkleur als direct argument, de kleurschalen met twee en drie punten nemen hun eindpuntkleuren op dezelfde manier, en AddCondFormatIconSet selecteert een van de 26 pictogramsettypen zoals icsTrafficLights3. Er is hier geen apart stijlrecord om te vergeten, omdat er helemaal geen apart stijlrecord is

De parameters bij deze aanroepen die aandacht verdienen zijn de waardeankers, getypeerd als TXLSCfValueKind. Een balk- of schaaleindpunt kan op het minimum of maximum van het bereik liggen, op een letterlijk getal, op een percentage of percentiel, of op het resultaat van een formule. De standaardwaarden, minimum en maximum van het bereik, gedragen zich netjes op opgepoetste demodata en verraden u dan op echte data met uitschieters: één op hol geslagen waarde rekt de schaal op en plat elke andere balk tot een stompje. Wanneer een dashboard over perioden heen gelezen moet worden, veranker de eindpunten dan aan vaste getallen of percentielen, zodat een halve balk in maart dezelfde hoeveelheid betekent als een halve balk in april. Een automatisch geschaalde balk is alleen met zichzelf vergelijkbaar

De XLS-schrijver dekt vier regelsoorten, niet meer

De verouderde BIFF8-kant is geen kleinere spiegel van de XLSX-kant; het is een bewuste deelverzameling. De XLS-facade kan precies vier vormen van voorwaardelijke regels maken, gegevensbalken, tweekleurenschalen, driekleurenschalen en pictogramsets, uitgestoten als CF12-records in de stream. Zij heeft geen creatie-API voor cellIs-, expressie- of tekstregels. Regels van die soorten die al in een bestand leven dat u opent worden gelezen, bewaard en ongewijzigd teruggeschreven, dus het openen en opnieuw opslaan van de .xls van een klant beschadigt nooit de opmaak die zij meebracht. Wat u niet kunt doen is drempelmarkering vanaf nul in een .xls genereren. De keuzes daar zijn haar nabootsen met gewone celvullingen die in code worden berekend, of van het eindproduct een .xlsx maken, waar de volledige regelfamilie op tafel ligt

Dit is een beperking die u vóór het bestaan van de datalaag moet uitklaren en niet erna, want zij verandert de keuze van het bestandsformaat voor alles wat dashboardvormig is. Een team dat .xls koos voor compatibiliteit en vervolgens een KPI-rapport met cellIs-drempels specificeert, heeft twee dingen gekozen die niet bij elkaar passen, en het goedkopere moment om dat op te merken is bij de formaatbeslissing en niet drie weken de bouw in

Regels stapelen, prioriteit en overlappende bereiken

Echte dashboards draaien zelden één regel per bereik. Een afwijkingskolom kan een gegevensbalk voor de omvang dragen, een cellIs-regel voor de harde drempel, en boven beide een expressieregel op rijniveau voor escalaties. Elke TXLSXConditionalFormat stelt een Priority-waarde beschikbaar, en Excel lost concurrerende regels op in volgorde van prioriteit. Wanneer twee regels dezelfde cel willen schilderen, wordt de winnaar bepaald door een getal dat u instelt, niet door de volgorde waarin een beoordelaar toevallig door het dialoogvenster Regels beheren scrolt

Behandel prioriteit zoals een tekenprogramma z-volgorde behandelt. Wijs haar bewust toe overal waar twee regels dezelfde cellen kunnen raken, en laat gaten tussen de waarden zodat een latere regel ertussen past zonder de rest te hernummeren. Waar regels niet kunnen botsen, bijvoorbeeld een gegevensbalk beperkt tot kolom E en een tekstregel beperkt tot kolom G, is de aanmaakvolgorde prima en is prioriteit de aandacht niet waard. Besteed die aandacht liever aan bereikgrenzen, want de dure bugs hier zijn vrijwel nooit prioriteitsinversies. Het zijn bereiken als B2:B200 op een rapport dat naar 350 rijen is gegroeid, waar de niet-gedekte staart als gewone cellen rendert die er precies uitzien als gezonde data. Leid elk regelbereik af uit dezelfde waarde voor het aantal laatste rijen die elders in de werkmap ook de grafiekreeksen en validatiebereiken aanstuurt, en de staart valt niet meer af

Eén verificatiegewoonte verdient haar plek. Open na het genereren het bestand in Excel, selecteer het opgemaakte bereik, en loop Regels beheren één keer door bij elke sjabloonwijziging. Voorwaardelijke opmaak is een van de weinige gebieden waar de enige gezaghebbende renderer de applicatie is die het bestand verbruikt, dus een unittest over de XML bewijst dat de regel is geschreven, niet dat Excel haar schildert zoals u bedoelde. Een minuutje kijken dicht dat gat

Rich text: meerdere opmaken binnen één cel

Een rich text-cel in het XLSX-model bevat een lijst van runs, waarbij elke run een stuk tekst is plus zijn eigen lettertypeattributen. U bouwt die lijst apart op als een TXLSXRichText-object, voegt er runs aan toe, en koppelt het geheel daarna aan een cel. De eigendomsregel is het deel dat toeslaat. Toewijzen aan Cell.RichText draagt het eigendom van dat object over aan de cel, en de cel geeft het vrij tijdens haar eigen vernietiging. Geeft u het zelf ook vrij, dan hebt u een double free, het soort dat stil blijft tijdens de run die het veroorzaakte en veel later als een crash ergens anders opduikt

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;   // eigendom gaat naar de cel: niet vrijgeven
end;

De expliciete ColorIsAuto := False is geen optionele versiering. Een run draagt een vlag voor automatische kleur, en een kleurtoewijzing wordt pas gehonoreerd zodra die vlag is gewist. Zet Color en vergeet ColorIsAuto en de run komt er vet uit maar koppig zwart, zonder fout die op de oorzaak wijst. Runs ondersteunen ook doorhalen, de onderstreepvarianten en verticale uitlijning voor superscript en subscript, terwijl PlainText de hele lijst terugplat tot één tekenreeks wanneer u de tekstinhoud moet exporteren of vergelijken

Rich text op celniveau is alleen voor XLSX. De XLS-facade heeft er geen publieke API voor om het te schrijven, hoewel runs daar wel beschikbaar zijn op opmerkingen en tekstvakken via TextRuns, en rich strings die uit een bestaande .xls worden gelezen een rondgang intact overleven. De aantrekkingskracht is dezelfde als bij voorwaardelijke opmaak: alles wat opmaken binnen een cel mengt hoort in de XLSX-schrijver

De stijlpool en de off-by-one die de deur uitgaat

Gewone celopmaak in het XLSX-model loopt via gepoolde collecties op de werkmap. Fonts.Add, Fills.AddSolid en Borders.Add registreren elk een definitie en retourneren haar index in de pool. Die indexen beginnen bij 0. De eigenschappen aan de celkant die ze verbruiken, zoals FontIndex, reserveren 0 voor "standaard", dus de waarde die u aan een cel toewijst is de poolindex plus één:

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

Laat de + 1 weg en elke koptekst valt terug op het standaardlettertype. Er is geen exceptie en geen waarschuwing, alleen een werkmap die eruitziet alsof niemand haar heeft opgemaakt. De fout van de tweede orde schuilt in de lus: Fonts.Add één keer per rij aanroepen. Identieke lettertypedefinities worden ontdubbeld, dus het bestand raakt niet beschadigd, maar het werk is verspild, en juist de uitlijnpool geeft bij elke aanroep een vers object terug in plaats van duplicaten samen te vouwen. Bouw het handjevol stijlen één keer vóór de lus en hergebruik hun indexen. Op rapporten van honderdduizend rijen is die ene wijziging een van de hefbomen die worden behandeld in prestatie-afstemming voor grote werkmappen met HotXLS. Hebt u alleen een standaard semantisch uiterlijk nodig, dan stellen beide facades ApplyBuiltinStyle op bereiken beschikbaar, dat afbeeldt op de ingebouwde stijlen Goed, Ongunstig, Neutraal en de accentstijlen van Excel zonder dat u de pools ook maar aanraakt

Voorwaardelijke opmaak, rich text en gepoolde stijlen zijn de laatste kilometer van een rapport, toegepast nadat het datamodel en de opmaak vastliggen, en die eerdere fasen zijn het onderwerp van sjabloongebaseerde rapportgeneratie met HotXLS. De volledige referentie voor regels, runs en stijlen staat op de productpagina van het HotXLS Delphi Component