Excel voorwaardelijke opmaak evalueren in Delphi met HotXLS

Een Top-10-percent-regel staat op een kolom met 27 verkoopcijfers, en Excel kleurt drie cellen. Tien procent van 27 is 2,7, dus een renderer die gewond afrondt krijgt er drie, een renderer die naar boven afrondt krijgt er drie, en een renderer die naar beneden afrondt krijgt er twee. Twee van die drie gissingen matchen Excel op deze kolom, en allebei zijn ze fout: Excel rondt het aantal af naar beneden tot twee en kleurt daarna alsnog de derde cel mee, omdat die met de tweede gelijk staat op 88. Verander één waarde en de geluksgissingen stoppen met kloppen

Dat is de hele moeilijkheid van voorwaardelijke opmaak tekenen buiten Excel om. De regels staan in het bestand; het antwoord op de vraag "welke cellen kleuren, en in welke kleur" staat er niet in. Als uw uitvoer een HTML-rapport, een PDF of een zelf geschilderd grid is, moet u het berekenen, en in de geaggregeerde regelfamilies zit de rekenkunde verscholen. Regels opstellen is een apart onderwerp, behandeld in voorwaardelijke opmaak en rich text-stijlen, en wat er met een regelbereik gebeurt als rijen verschuiven staat in het partitioneren van verankerde voorwaardelijke opmaak. Deze notitie gaat over evaluatie

Wat het conditionalFormatting-element wel en niet zegt

ECMA-376 en ISO 29500-1 definiëren opslag, en daar houden ze op. Een conditionalFormatting-element (§18.3.1.18) bevat een sqref en een lijst cfRule-kinderen (§18.3.1.10). Elke regel heeft een type, een optionele operator, een priority, een stopIfTrue-vlag, een of twee formula-kinderen en, voor de visuele families, een set cfvo-drempels. Alles daarvan legt trouw vast wat de gebruiker in het dialoogvenster aanklikte. Geen ervan is een algoritme

Voor veel regeltypen is dat gat onschadelijk. cellIs met operator="greaterThan" betekent groter dan, en containsText betekent dat de substring voorkomt. De problemen beginnen bij regels waarvan het oordeel van de rest van het bereik afhangt: top10 met percent="1" zegt niet hoe een gebroken aantal afrondt, aboveAverage met stdDev="1" zegt niet of de deviatie steekproef- of populatiegebonden is, en geen van beide zegt of een waarde die gelijk is aan de grens meedoet. Kiest u verkeerd, dan spreekt uw PDF de werkmap tegen die de klant in het volgende venster open heeft staan

TCondFormatRule.Evaluate: wat één cel op zichzelf kan beslissen

HotXLS kent twee evaluatievlakken, en ze verschillen in wat ze willen beantwoorden. De klassieke (.xls)-engine stelt TCondFormatRule.Evaluate beschikbaar in lxCondFormat.pas, dat een calculator, een bladindex, een celpositie en de celwaarde neemt en voor één regel een Boolean teruggeeft. Het dekt de acht cellIs-operatoren (between, not between, equal, not equal, greater, less, greater or equal, less or equal), vrije expression-regels die op de cel worden geëvalueerd zodat relatieve verwijzingen correct herbaseren, de vier tekstpredicaten en de leeg- en foutpredicaten. Drempelformules worden op de celpositie opgelost, en een between-regel waarvan de grenzen omgekeerd binnenkomen verwisselt ze in plaats van ze te weigeren. Tekstvergelijkingen zijn case-insensitive, net als in Excel

// Klassieke engine: één regel, één cel, één Boolean
function AnyRuleMatches(Calculator: TXLSCalculator; CondFormat: TCondFormat;
  SheetIndex, Row, Col: Integer; const Value: Variant): Boolean;
var
  I: Integer;
begin
  Result := False;
  for I := 0 to CondFormat.RuleCount - 1 do
    // top10, gemiddelden, duplicaten, tijdsperioden en de visuele soorten
    // antwoorden hier False; ze hebben het hele bereik nodig
    if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      Exit(True);
end;

De methode geeft False terug voor top10, aboveAverage, belowAverage, duplicateValues en uniqueValues. Eén cel kan niet weten of hij in de top tien procent zit zonder de andere waarden te zien, dus antwoorden zou gokken zijn. Relatieve datumregels (timePeriod) antwoorden op dit pad ook False. De vier visuele families, dataBar, colorScale2, colorScale3 en iconSet, antwoorden False om een andere reden: ze produceren nooit ja of nee, ze produceren een kleur, een balklengte of een iconindex, en een Boolean heeft daar geen plek voor. Een False van deze aanroep betekent dus net zo vaak "hier niet beslist" als "matcht niet", wat prima is voor een snelle check op vergelijkingsregels en onveilig voor alles wat u verzendt

Hoe vermijdt een werkblad-evaluator dat het blad voor elke cel opnieuw wordt gescand?

Ze doet al het bereikbrede werk één keer, bij het aanmaken. TXLSXConditionalFormatEvaluator in lxHandleX.pas is een onveranderlijke momentopname van de voorwaardelijke opmaak van één werkblad, verkregen via TXLSXWorksheet.CreateConditionalFormatEvaluator; de aanroeper is eigenaar en geeft hem vrij. Het naïeve alternatief, waarbij elke geschilderde cel een scan van zijn regelbereik triggert om een gemiddelde of rang opnieuw te berekenen, maakt van een export van 10.000 cellen 10.000 bereikscans per regel

Bij de constructie parseert de evaluator elke unieke sqref één keer, zodat tien regels over hetzelfde bereik één parse en één doorgang over zijn gevulde cellen delen. Die doorgang verzamelt gemiddelde, populatiedeviatie, minimum en maximum in één keer lopen, en bewaart alleen een gesorteerde kopie van de getallen als een Top/Bottom-regel of een percentieldrempel echt ordelijk-statistiek nodig heeft. Duplicate- en unique-regels bouwen en sorteren hun waardesleutels één keer, zodat elke opzoek daarna een binaire zoekactie is. Regelformules worden één keer gecompileerd en per cel her-evalueerd met de offset van de cel toegepast, zodat =A1>AVERAGE($A$1:$A$100) zijn relatieve en absolute delen exact laat gedragen zoals in Excel, zonder per rij opnieuw te worden geparseerd. Rijen worden gegroepeerd zodat EvaluateCell alleen regels bezoekt waarvan de bereiken de gevraagde rij kunnen bereiken

Daarna worden de regels toegepast in priority-volgorde, laagste nummer eerst, met gelijke standen opgelost op positie in het bestand. Elke opmaakeigenschap komt van de eerste matchende regel die haar zet, en regels met lagere prioriteit vullen alleen eigenschappen die nog leeg zijn: een prioriteit-1-regel die vettig zet en een prioriteit-2-regel die een rode vulling zet komen allebei tot uiting, en de cel komt eruit als vettig op rood. Een match op een regel met StopIfTrue beëindigt de doorgang voor die cel

procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
  FirstRow, LastRow, FirstCol, LastCol: Integer);
var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
  Row, Col: Integer;
begin
  // Eén momentopname per renderpass; rijen en kolommen zijn 1-based
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    for Row := FirstRow to LastRow do
      for Col := FirstCol to LastCol do
      begin
        if not Evaluator.EvaluateCell(Row, Col, Res) then
          Continue;
        if Res.HasFillColor then
          Canvas.Brush.Color := TColor(Res.FillColor and $00FFFFFF);  // BGR, zelfde indeling als TColor
        if Res.HasDataBar then  // DrawBar is uw eigen routine
          DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
        if Res.HasIcon then     // DrawIcon is uw eigen routine
          DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
        if Res.ShowCellValue and Res.HasNumberFormat and
           Sheet.Cells.HasCell(Row, Col) then  // DrawCellText is uw eigen routine
          DrawCellText(Row, Col,
            Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
      end;
  finally
    Evaluator.Free;
  end;
end;

De momentopname wordt met opzet één keer per pass gebouwd. Maakt u hem binnen de binnenste lus aan, dan wordt alles opnieuw gecompileerd en gescand voor elke cel, precies de kosten die dit ontwerp wil vermijden. ShowCellValue komt False terug wanneer een databar-regel showValue="0" heeft of een iconenset op alleen het icoon staat, zodat de renderer de balk of het teken schildert en het getal weglaat

Hoeveel cellen markeert een Top-10-percent-regel werkelijk?

Het aantal wordt afgerond naar beneden, nooit onder de één gelaten, en elke waarde die met de grens gelijk staat wordt meegenomen. De specificatie zegt dit niet; het is wat Excel doet, en u bevestigt het in een minuut door de 27 waarden uit de figuur in een kolom te typen, Top 10% toe te passen en de gekleurde cellen te tellen. TXLSXConditionalFormatEvaluator berekent de rangtelling als Floor(Count * Min(Rank, 100) / 100), verheft die naar 1 als hij op nul uitkomt, klemmt hem op het aantal gevulde numerieke cellen, leest de grens uit de gesorteerde waarden en vergelijkt daarna elke cel met >=. Voor de kolom met 27 waarden betekent dat een telling van twee, een grens van 88 en drie gekleurde cellen omdat twee ervan 88 bevatten. Bottom-N-regels spiegelen dit met <=

Bovengemiddelde-regels dragen de tweede ambiguïteit. Met stdDev="1" selecteert de regel cellen die ten minste één standaarddeviatie boven het gemiddelde zitten, en steekproef- en populatiedeviatie verschillen door de Bessel-correctie, wat het meest telt op de korte bereiken waar mensen deze regel feitelijk gebruiken. Op vijf waarden lopen de twee ongeveer 12 procent uiteen. HotXLS gebruikt de populatiedeviatie, hetzelfde cijfer dat STDEV.P teruggeeft, dus een snelle manier om elke renderer te controleren is =AVERAGE(r)+STDEV.P(r) naast het bereik zetten en kijken welke cellen erboven uitkomen. Vergelijkingen tegen een deviatieband zijn inclusief; de kale boven-of-onder-vergelijking is streng tenzij de regel equalAverage="1" draagt

Duplicate- en unique-regels hangen af van wat als "dezelfde waarde" telt. Het getal 100 en de tekst "100" delen een sleutel, want numerieke tekst wordt genormaliseerd naar de numerieke sleutelruimte; textsleutels vergelijken case-insensitive; TRUE en 1 blijven onderscheiden, net als foutwaarden van verschillende soorten. Tekst met * of ? erin wordt als wildcardpatroon gematcht, COUNTIF-stijl, dus een cel met Q* telt mee tegen elke waarde die met Q begint. Lege cellen zijn de tegengestelde valkuil: een werkelijk lege cel in het bereik wordt nooit als duplicaat gekleurd, dus een onafgewerkte kolom licht niet van boven tot onder op, terwijl een cel die een lege string opslaat met de lege waarden meegeteld wordt

Kleurschalen, databars en iconensets komen terug als getallen

De visuele families werken uit tot rendervrije waarden, en hun randgevallen zijn waar handgeschreven renderers afdwalen. Voor een kleurschaal klemt EvaluateCell de positie naar het interval 0 tot 1 en interpoleert elk RGB-kanaal met truncatie, zodat een waarde onder de onderste stop de minimumkleur krijgt in plaats van een geëxtrapoleerde. Een schaal met drie stops gebruikt het midden-naar-max-paar voor waarden boven de middendrempel en het min-naar-midden-paar anders. Een schaal waarvan de eerste en laatste drempel op hetzelfde getal uitkomen, geeft de laatste kleur terug in plaats van te delen door nul. Percentieldrempels worden lineair geïnterpoleerd tussen naburige gesorteerde waarden, de PERCENTILE.INC-definitie

Databars worden genormaliseerd naar de celbreedte. Voor een bereik dat volledig positief is, is DataBarAxis 0 en loopt DataBarEnd tussen de minimum- en maximumbalklengte van de regel, die standaard op 10 en 90 procent staan, zodat de kleinste waarde nog steeds een korte balk krijgt en de grootste de cel nooit helemaal vult. Loopt het bereik door nul, dan zit de as op zijn proportionele positie en markeert DataBarEnd waar de balk stopt, aan de verre kant van de as voor negatieve waarden. Richting, verloop, rand en negatieve-balkkleuren worden op hetzelfde record ingevuld, met themakleuren en tints al opgelost

Iconensets vragen aandacht voor de striktheid van elke drempel, want elke cfvo na de eerste draagt zijn eigen gte-vlag. De evaluator loopt de drempels naar boven af, past per drempel >= of > toe zoals ThresholdEqualsInclude zegt, en de hoogste drempel die de waarde haalt wint. Een omgekeerde set klapt de uitgerekende index om, per-icoon-overrides kunnen via IconSetType een teken uit een andere familie substitueren, en een drempel die niet uitkomt stopt de regel voor die cel, zodat de cel helemaal geen icoon krijgt in plaats van een plausibel verkeerd icoon

Eén resultaat voor het grid, de HTML-export en de PDF

TXLSXCfCellResult draagt alles wat een renderer nodig heeft: vullings- en fontkleur met thematint toegepast, vettig, cursief, onderstreep, doorhalen, een nummeropmaak-id, de databar-geometrie en -kleuren, en de iconenfamilie en -index. HotXLS voedt hetzelfde record aan zijn HTML-export, zijn PDF-export en TXLSWorkbookViewer, wat de drie uitvoeren cel voor cel in overeenstemming houdt. De viewer maakt één evaluator voor het actieve werkblad bij de eerste schilderbeurt en hergebruikt hem bij scrollen, selectie en herschilderen, en laat hem vallen zodra u van blad wisselt of een andere werkmap toewijst

// Bewerken achter een live viewer: de gecachte momentopname ziet de wijziging niet
Sheet := Viewer.XlsxWorkbook.Sheets[1];   // bladindex is 1-based
Sheet.Cells[5, 2].Value := 4200;          // verschuift gemiddelde, maximum en de rangschikking
Viewer.RefreshConditionalFormats;         // laat de momentopname vallen en herteken

Omdat de momentopname onveranderlijk is, laat een bewerking ter plekke gemiddelden, rangen en kleurschaal-eindpunten verouderd achter tot RefreshConditionalFormats draait. Hetzelfde geldt voor uw eigen code: een evaluator die u over bewerkingen heen vasthoudt, moet worden vrijgegeven en opnieuw aangemaakt, want CreateConditionalFormatEvaluator vangt de waarden op het moment van aanroep

Relatieve datumregels verdienen een besluit vooraf. De werkblad-evaluator vergelijkt timePeriod-regels met de huidige datum van de machine, dus een "deze week"-regel rendert vandaag anders dan wanneer dezelfde werkmap volgende dinsdag wordt geëxporteerd. Dat is correct gedrag en toch een supportticket als van uw archief verwacht wordt dat het reproduceerbaar is. Vervang voor gearchiveerde rapporten timePeriod-regels door een expression-regel die vergelijkt met een rapportdatum in een cel, zoals =AND(A2>=$Z$1-6,A2<=$Z$1) voor een venster van zeven dagen; de uitvoer hangt dan van de werkmap af en van niets anders. Excel weigert ook gestructureerde tabelverwijzingen binnen een voorwaardelijke-opmaakformule, dus richt de regel op het celbereik van de kolom

Is het doel een scherm in plaats van een bestand, dan drijft hetzelfde resultaatrecord ook het aangepaste VCL-spreadsheetgrid dat elders op deze blog wordt beschreven. Het regelmodel, de evaluator-API-referentie en trialbuilds staan op de pagina van het HotXLS Delphi spreadsheetcomponent