Excel voorwaardelijke opmaak evalueren in Delphi met HotXLS

HotXLS is een native spreadsheetcomponent voor Delphi en C++Builder, en sinds versie 2.209.0 kan het de vraag beantwoorden die Excel normaal voor zichzelf houdt: voor deze exacte cel, welke voorwaardelijkeopmaakregels vuren, en welke opvulling, font, databalk of icoon lossen ze op. Dat antwoord heb je nodig zodra je output een HTML-rapport, een PDF of een raster is dat je zelf tekent

Dit is een ander probleem dan regels aanmaken. Twee eerdere artikelen behandelen de auteurskant: voorwaardelijke opmaak en rich-text-stijlen gaat over het koppelen van regels en differentiële formaten aan een bereik, en verankerde voorwaardelijkeopmaakpartitionering gaat over wat er met een regelbereik gebeurt wanneer rijen en kolommen worden ingevoegd of verwijderd. Beide zijn structureel. Dit artikel gaat over semantiek: gegeven een werkmap die al regels draagt, bereken de markering

Waarom het bestandsformaat je niet vertelt welke cellen oplichten

Het korte antwoord is dat ECMA-376 en ISO 29500-1 opslag definiëren, geen evaluatie. Een conditionalFormatting-element (§18.3.1.18) draagt een sqref en een lijst met cfRule-kinderen (§18.3.1.10), en elke regel draagt een type, een optionele operator, een priority, een stopIfTrue-vlag, één of twee formula-kinderen, en voor de visuele families een set cfvo-drempels. Elk daarvan beschrijft trouw wat de gebruiker geconfigureerd heeft, en geen ervan is een algoritme. Voor de helft van de regeltypen doet dat gat er niet toe: cellIs met operator="greaterThan" betekent groter dan, en containsText betekent dat de substring aanwezig is. Het gat gaat open bij de aggregaatfamilies. Een top10-regel met rank="10" en percent="1" over 27 gevulde numerieke cellen markeert hoeveel cellen? Twee komma zeven is geen getal. Afronden, naar beneden of naar boven — de specificatie zwijgt, en het verkeerd kiezen betekent dat je PDF het oneens is met de werkmap die de klant ernaast open heeft staan

Enkele-celregels en waar TCondFormatRule.Evaluate ophoudt

HotXLS pakte eerst de goedkope helft. TCondFormatRule.Evaluate in lxCondFormat.pas, toegevoegd in 2.199.0, beantwoordt of één regel vuurt voor één cel zonder iets van de rest van het bereik te weten. Het behandelt de acht BIFF-vergelijkingsoperators achter cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), vrije-vorm-expression-regels geëvalueerd op de cel zodat relatieve referenties correct herbasen, de vier tekstpredicaten, en de leeg- en foutpredicaten. Drempels komen van FFormula1 en FFormula2 opgelost via TXLSCalculator.GetRangeValue op de celpositie, en omgekeerde grenzen worden verwisseld in plaats van geweigerd

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;

Het eerlijke deel van die methode is wat hij weigert te gokken. top10, aboveAverage, belowAverage, duplicateValues en uniqueValues retourneren False, niet omdat ze moeilijk zijn maar omdat ze onbeslisbaar zijn vanuit één cel — elk van hen heeft een statistiek over het hele domein nodig. De vier visuele families, dataBar, colorScale2, colorScale3 en iconSet, retourneren False om een andere reden: ze produceren nooit een boolean, ze produceren een renderpayload, en een booleaans retourtype heeft de verkeerde vorm voor hen

Hoe voorkomt een evaluator op werkbladniveau een herhaalde scan van het blad?

Door elke gedeelde grootheid één keer te berekenen, bij constructie, en daarna nooit meer. TXLSXConditionalFormatEvaluator in lxHandleX.pas is een onveranderlijke momentopname voor één werkblad, gebouwd via TXLSXWorksheet.CreateConditionalFormatEvaluator, en het hele ontwerp is een verdediging tegen de naïeve implementatie waarbij elke geschilderde cel een volledige bereikscan triggert

Vier dingen gebeuren in de constructor. Elke afzonderlijke multi-area-sqref wordt precies één keer geparseerd in een TXlsxCfRangeSnapshot, zodat tien regels die één bereik delen ook één parse en één statistiekpas delen. Die pas streamt gemiddelde, populatie-afwijking, minimum en maximum over de gevulde cellen in één doorloop, en behoudt alleen een geordende numerieke array wanneer een Top/Bottom- of percentielregel daadwerkelijk ordestatistieken nodig heeft. Duplicaat- en unieke sleutels worden Unicode-veilig gebouwd en één keer batchgesorteerd in plaats van per opzoeking. Vervolgens wordt de rij-as in banden gesneden bij elke area-grens, zodat EvaluateCell binair zoekt in een band en alleen regels bezoekt waarvan de bereiken die rij mogelijk kunnen bereiken

Het vierde punt weegt het zwaarst op schaal. Een relatieve regelformule zoals =A1>AVERAGE($A$1:$A$100) betekent iets anders in elke cel van het domein, en de voor de hand liggende implementatie compileert per cel een verse syntaxboom. TXlsxCfRulePlan compileert die één keer en herevalueert dezelfde boom via omkeerbare coördinaatverschuivingen, wat het ankergedrag van Excel behoudt zonder een syntaxboomallocatie per cel. Regels worden dan gelaagd op priority, en een match op een regel waarvan StopIfTrue ingesteld is, doorbreekt de lus, precies zoals Excel kortsluit

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;

Hoe rondt Excel een Top-10-percent-regel eigenlijk af?

Naar beneden, met een minimum van één, en het sluit gelijkspelers op het afkappunt in. Dat staat nergens in ISO 29500-1 opgeschreven — het werd vastgepind door Excel 16 te testen met handgebouwde werkmappen en terug te lezen welke cellen de applicatie markeerde. HotXLS implementeert precies dat: het rangaantal is Floor(Count * Min(Rank, 100) / 100), opgehoogd naar 1 wanneer het op nul uitkomt, geclamped op het gevulde aantal, en de afkapwaarde wordt dan vergeleken met >= zodat elke cel gelijk aan de grens gemarkeerd wordt, zelfs wanneer dat het gevraagde aantal overschrijdt. Zevenentwintig waarden en een 10-procentregel markeren twee cellen, plus elke verdere cel die gelijk staat aan de tweede

Boven-gemiddelde-regels verborgen een tweede dubbelzinnigheid: aboveAverage met stdDev="1" selecteert cellen één standaardafwijking boven het gemiddelde, maar steekproef- en populatieafwijking verschillen door de Bessel-correctie en ze zijn het zichtbaar oneens bij kleine bereiken, precies waar voorwaardelijke opmaak gebruikt wordt. Excel 16 gebruikt de populatieafwijking, en HotXLS komt daarmee overeen, waarbij de equalAverage-vlag de strikte vergelijking alleen inclusief maakt wanneer er geen afwijkingsband in het spel is. Duplicaat- en unieke regels bleken te draaien op sleutelidentiteit in plaats daarvan. Als één cel het getal 100 bevat en een andere de tekst "100", behandelt Excel ze als dezelfde duplicaatsleutel, dus HotXLS normaliseert numerieke tekst naar de numerieke sleutelruimte in plaats van ruwe strings te vergelijken. Lege cellen zijn het spiegelgeval: een echt lege cel telt mee in de bereiktelling maar wordt zelf niet opgemaakt, dus de lege cellen in een kolom lichten niet allemaal op als duplicaten van elkaar

Kleurschalen en iconensets: interpolatie en grensregels

De visuele families lossen op tot renderklare getallen in plaats van booleans, en hun randgedrag werd op dezelfde manier vastgepind. Voor een kleurschaal met expliciete numerieke drempels clampt HotXLS de positiefractie op het gesloten interval nul tot één, interpoleert dan per kanaal met afkapping in plaats van afronding — een waarde onder het minimumpunt krijgt de minimumkleur in plaats van een geëxtrapoleerde, een schaal met drie punten kiest zijn paar door te vergelijken met het middelste punt, en een gedegenereerde schaal waarvan de twee uiteinden dezelfde drempel dragen valt terug op de bovenste kleur in plaats van door nul te delen. Iconensets vroegen om een andere soort zorg, omdat elke cfvo na de eerste zijn eigen vergelijkingsstrictheid draagt: HotXLS leest ThresholdEqualsInclude per drempel en past dienovereenkomstig >= of > toe, en loopt omhoog zodat de hoogste voldane drempel de icoonindex wint. Een omgekeerde set spiegelt de opgeloste index in plaats van de drempels, per-icoon-overrides kunnen een glyph uit een andere familie halen, en elke ongeldige drempel breekt de regel af in plaats van een plausibel ogend verkeerd icoon te produceren

Eén resultaat voedt een raster, een HTML-export en een PDF

Omdat EvaluateCell een volledig opgelost TXLSXCfCellResult retourneert — differentiële opvul- en fontkleur met theme-tint al toegepast, vet, cursief, onderstreping, getalnotatie-id, richtingsafhankelijke positieve en negatieve balklengtes, aspositie, iconenfamilie en -index — leest elke consument hetzelfde record en hoeft geen van hen regelinternals te begrijpen. HotXLS gebruikt dat ene pad voor HTML-export, PDF-export en de interactieve viewer, wat de enige praktische manier is om te voorkomen dat drie renderers uit elkaar drijven. Versie 2.210.0 verbond het met TXLSWorkbookViewer, die één voorbereide evaluator per actief werkblad cachet en hergebruikt over scrollen, selectie en herschildering, en die vrijgeeft wanneer de werkmap of het werkblad verandert — de momentopname bij elke Paint herbouwen zou het hele constructietijd-ontwerp teniet doen. Die cache is ook waarom TXLSWorkbookViewer.RefreshConditionalFormats bestaat: de momentopname is onveranderlijk, dus als je de gekoppelde werkmap ter plekke wijzigt, zijn de aggregaatstatistieken en opgeloste drempels verouderd totdat je die aanroept

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

Wat de evaluator niet voor je doet

Drie grenzen zijn het waard om nadrukkelijk te noemen. De klassieke enkele-cel-TCondFormatRule.Evaluate en de TXLSXConditionalFormatEvaluator op werkbladniveau zijn verschillende oppervlakken met verschillende mogelijkheden, en de enkele-cel-variant weigert bewust de aggregaat- en visuele families in plaats van ze te benaderen — heb je Top/Bottom of een kleurschaal nodig, bouw dan de evaluator. Relatieve tijdsperioden hangen af van de systeemklok op evaluatietijdstip, dus een timePeriod-regel rendert anders in een PDF die vandaag gegenereerd wordt en een die volgende week gegenereerd wordt, wat correct gedrag is en toch een supportticket in wording als je archief byte-stabiel hoort te zijn. De derde is grammaticaal in plaats van technisch: de grammatica van de voorwaardelijkeopmaakformule verbiedt gestructureerde tabelreferenties, dus een regel kan geen tabelkolom bij naam adresseren zoals een werkbladformule dat kan, en dat is een beperking van het formaat, niet van de implementatie

Bouw je rapportoutput, een exportpipeline of een aangepast raster dat cel voor cel met Excel moet overeenstemmen, dan drijft hetzelfde opgeloste resultaat ook het aangepaste VCL-spreadsheetraster aan dat elders op deze blog beschreven wordt. Volledige API-documentatie, het regelmodel en proefdownloads voor het HotXLS Delphi-spreadsheetcomponent zijn beschikbaar op de productpagina