Excel villkorsstyrd formatering-utvärdering i Delphi med HotXLS

HotXLS är en native kalkylbladskomponent för Delphi och C++Builder, och sedan version 2.209.0 kan den besvara frågan Excel normalt håller för sig självt: för exakt den här cellen, vilka villkorsstyrda formateringsregler avfyras, och vilken fyllning, typsnitt, databalk eller ikon löser de upp till. Det svaret är vad du behöver i samma ögonblick din utdata är en HTML-rapport, en PDF, eller ett rutnät du målar själv

Det här är ett annat problem än att skapa regler. Två tidigare anteckningar täcker skapandesidan: villkorsstyrd formatering och rich text-stilar handlar om att fästa regler och differentialformat på ett intervall, och partitionering av förankrade villkorsstyrda format handlar om vad som händer med ett regelintervall när rader och kolumner infogas eller tas bort. Båda är strukturella. Den här handlar om semantik: givet en arbetsbok som redan bär regler, beräkna markeringen

Varför filformatet inte talar om för dig vilka celler som lyser upp

Det korta svaret är att ECMA-376 och ISO 29500-1 definierar lagring, inte utvärdering. Ett conditionalFormatting-element (§18.3.1.18) bär en sqref och en lista med cfRule-barn (§18.3.1.10), och varje regel bär en type, en valfri operator, en priority, en stopIfTrue-flagga, en eller två formula-barn, och för de visuella familjerna en uppsättning cfvo-trösklar. Var och en av dessa beskriver troget vad användaren konfigurerade, och ingen av dem är en algoritm. För hälften av regeltyperna spelar det gapet ingen roll: cellIs med operator="greaterThan" betyder större än, och containsText betyder att delsträngen finns. Gapet öppnar sig på aggregatfamiljerna. En top10-regel med rank="10" och percent="1" över 27 befolkade numeriska celler markerar hur många celler? Två komma sju är inte ett tal. Avrunda, golva, eller taka — specifikationen tiger, och att välja fel betyder att din PDF är oense med arbetsboken kunden har öppen bredvid sig

Enskilda cellregler och var TCondFormatRule.Evaluate stannar

HotXLS tog den billiga halvan först. TCondFormatRule.Evaluate i lxCondFormat.pas, tillagd i 2.199.0, svarar om en regel avfyras för en cell utan att veta något om resten av intervallet. Den hanterar de åtta BIFF-jämförelseoperatorerna bakom cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), fritt formade expression-regler utvärderade vid cellen så att relativa referenser omankras korrekt, de fyra textpredikaten, och tomrums- och felpredikaten. Trösklar kommer från FFormula1 och FFormula2 upplösta genom TXLSCalculator.GetRangeValue vid cellpositionen, och omvända gränser byts snarare än avvisas

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;

Den ärliga delen av den metoden är vad den vägrar gissa. top10, aboveAverage, belowAverage, duplicateValues och uniqueValues returnerar False, inte för att de är svåra utan för att de är oavgörbara från en cell — var och en av dem behöver en statistik över hela domänen. De fyra visuella familjerna, dataBar, colorScale2, colorScale3 och iconSet, returnerar False av en annan anledning: de producerar aldrig en boolean alls, de producerar en renderingsnyttolast, och en boolesk returtyp är fel form för dem

Hur undviker en kalkylbladsnivå-utvärderare att skanna om bladet?

Genom att beräkna varje delad kvantitet en gång, vid konstruktion, och aldrig igen. TXLSXConditionalFormatEvaluator i lxHandleX.pas är en oföränderlig ögonblicksbild för ett kalkylblad, byggd genom TXLSXWorksheet.CreateConditionalFormatEvaluator, och hela dess design är ett försvar mot den naiva implementationen där varje målad cell utlöser en fullständig intervallskanning

Fyra saker sker i konstruktorn. Varje distinkt flerområdes-sqref tolkas exakt en gång till en TXlsxCfRangeSnapshot, så tio regler som delar ett intervall delar en tolkning och ett statistikpass. Det passet strömmar medelvärde, populationsavvikelse, minimum och maximum över de befolkade cellerna i en enda vandring, och behåller bara en ordnad numerisk array när en Top/Bottom- eller percentilregel faktiskt behöver ordningsstatistik. Dubblett- och unika nycklar byggs Unicode-säkert och batchsorteras en gång i stället för per uppslagning. Sedan skärs radaxeln i band vid varje områdesgräns, så EvaluateCell binärsöker ett band och besöker bara regler vars intervall möjligen kan nå den raden

Den fjärde är den som spelar störst roll i skala. En relativ regelformel som =A1>AVERAGE($A$1:$A$100) betyder något annat i varje cell i domänen, och den uppenbara implementationen kompilerar ett färskt syntaxträd per cell. TXlsxCfRulePlan kompilerar det en gång och omutvärderar samma träd genom reversibla koordinatförskjutningar, vilket bevarar Excels ankarbeteende utan en syntaxträdsallokering per cell. Regler lagras sedan efter priority, och en matchning på en regel vars StopIfTrue är satt bryter loopen, precis som Excel kortsluter

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;

Hur avrundar Excel egentligen en Top 10 procent-regel?

Den golvar, med ett minimum på ett, och den inkluderar oavgjort vid brytpunkten. Det står inte skrivet någonstans i ISO 29500-1 — det fastställdes genom att sondera Excel 16 med handbyggda arbetsböcker och läsa av vilka celler applikationen markerade. HotXLS implementerar exakt det: rangantalet är Floor(Count * Min(Rank, 100) / 100), höjt till 1 när det landar på noll, klämt till det befolkade antalet, och brytvärdet jämförs sedan med >= så att varje cell lika med gränsen markeras även när det överskrider det begärda antalet. Tjugosju värden och en 10-procentsregel markerar två celler, plus alla ytterligare celler oavgjort med den andra

Över-genomsnitt-regler dolde en andra tvetydighet: aboveAverage med stdDev="1" väljer celler en standardavvikelse över medelvärdet, men sampel- och populationsavvikelse skiljer sig genom Bessel-korrektionen och de är oense synligt på små intervall, vilket är precis där villkorsstyrd formatering används. Excel 16 använder populationsavvikelsen, och HotXLS matchar den, med equalAverage-flaggan som gör den strikta jämförelsen inkluderande bara när ingen avvikelseband är i spel. Dubblett- och unika regler slog på nyckelidentitet i stället. Om en cell håller talet 100 och en annan håller texten "100" behandlar Excel dem som samma dubblettnyckel, så HotXLS normaliserar numerisk text till det numeriska nyckelutrymmet snarare än att jämföra råa strängar. Tomma celler är spegelfallet: en verkligt tom cell deltar i intervallräkningen men stilsätts inte själv, så de tomma cellerna i en kolumn lyser inte alla upp som dubbletter av varandra

Färgskalor och ikonuppsättningar: interpolering och gränsregler

De visuella familjerna löser upp till renderingsklara tal snarare än booleaner, och deras kantbeteende fastställdes på samma sätt. För en färgskala med explicita numeriska trösklar klämmer HotXLS positionsandelen till det slutna intervallet noll till ett, interpolerar sedan per kanal med trunkering snarare än avrundning — ett värde under minimistoppet får minimifärgen snarare än en extrapolerad, en tre-stopps-skala väljer sitt par genom att jämföra mot mittstoppet, och en degenererad skala vars två ändar bär samma tröskel kollapsar till toppfärgen i stället för att dividera med noll. Ikonuppsättningar behövde motsatt sorts omsorg, eftersom varje cfvo efter den första bär sin egen jämförelsestränghet: HotXLS läser ThresholdEqualsInclude per tröskel och tillämpar >= eller > därefter, vandrande uppåt så att den högsta uppfyllda tröskeln vinner ikonindexet. En omvänd uppsättning vänder det upplösta indexet snarare än trösklarna, per-ikon-överstyrningar kan dra en glyf från en annan familj, och varje ogiltig tröskel avbryter regeln i stället för att producera en rimligt utseende fel ikon

Att mata ett rutnät, en HTML-export och en PDF från ett resultat

Eftersom EvaluateCell returnerar ett fullständigt upplöst TXLSXCfCellResult — differentialfyllning och typsnittsfärg med temanyans redan tillämpad, fet, kursiv, understruken, talformats-id, riktningsvisa positiva och negativa balkutsträckningar, axelposition, ikonfamilj och index — läser varje konsument samma post och ingen av dem behöver förstå regelinternals. HotXLS använder den enda vägen för HTML-export, PDF-export och den interaktiva visaren, vilket är det enda praktiska sättet att hindra tre renderare från att driva isär. Version 2.210.0 kopplade in det i TXLSWorkbookViewer, som cachar en förberedd utvärderare per aktivt kalkylblad och återanvänder den över scrollning, markering och omritning, och släpper den när arbetsboken eller kalkylbladet ändras — att bygga om ögonblicksbilden vid varje Paint skulle omintetgöra hela konstruktionstidsdesignen. Den cachen är också varför TXLSWorkbookViewer.RefreshConditionalFormats finns: ögonblicksbilden är oföränderlig, så om du muterar den fästa arbetsboken på plats är aggregatstatistiken och de upplösta trösklarna föråldrade tills du anropar den

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

Vad utvärderaren inte kommer göra åt dig

Tre gränser är värda att uttrycka rakt på sak. Den klassiska enskilda-cell-TCondFormatRule.Evaluate och den kalkylbladsnivå-TXLSXConditionalFormatEvaluator är olika ytor med olika förmågor, och den enskilda-cell-varianten avböjer avsiktligt aggregat- och visuella familjerna snarare än att approximera dem — om du behöver Top/Bottom eller en färgskala, bygg utvärderaren. Relativa datumperioder beror på maskinklockan vid utvärderingstillfället, så en timePeriod-regel renderas olika i en PDF genererad idag och en genererad nästa vecka, vilket är korrekt beteende och ändå ett supportärende som väntar på att hända om ditt arkiv förväntas vara byte-stabilt. Den tredje är grammatisk snarare än teknisk: grammatiken för villkorsformelsformat förbjuder strukturerade tabellreferenser, så en regel kan inte adressera en tabellkolumn med namn på det sätt en kalkylbladsformel kan, och det är en begränsning i formatet snarare än i implementationen

Om du bygger rapportutdata, en exportpipeline eller ett anpassat rutnät som måste hålla med Excel cell för cell driver samma upplösta resultat också det anpassade VCL-kalkylbladsrutnätet beskrivet annanstans på den här bloggen. Fullständig API-dokumentation, regelmodellen och provnedladdningar för HotXLS Delphi-kalkylbladskomponent finns på produktsidan