Evaluarea formatării condiționale Excel în Delphi cu HotXLS

HotXLS este o componentă nativă de foaie de calcul pentru Delphi și C++Builder, iar începând cu versiunea 2.209.0 poate răspunde la întrebarea pe care Excel o păstrează de obicei pentru sine: pentru această celulă exactă, care reguli de formatare condițională se declanșează și la ce umplere, font, bară de date sau pictogramă se rezolvă. Acel răspuns este de care aveți nevoie chiar în momentul în care ieșirea dumneavoastră este un raport HTML, un PDF sau o grilă pe care o pictați singuri

Aceasta este o problemă diferită de crearea regulilor. Două note anterioare acoperă partea de elaborare: formatarea condițională și stilurile de text îmbogățit tratează atașarea regulilor și a formatelor diferențiale la un interval, iar partiționarea formatelor condiționale ancorate tratează ce se întâmplă cu intervalul unei reguli când sunt inserate sau șterse rânduri și coloane. Ambele sunt structurale. Aceasta este despre semantică: dată fiind o registru de calcul care poartă deja reguli, calculați evidențierea

De ce formatul fișierului nu vă spune care celule se aprind

Răspunsul scurt este că ECMA-376 și ISO 29500-1 definesc stocarea, nu evaluarea. Un element conditionalFormatting (§18.3.1.18) poartă un sqref și o listă de copii cfRule (§18.3.1.10), iar fiecare regulă poartă un type, un operator opțional, o priority, un flag stopIfTrue, unul sau doi copii formula, iar pentru familiile vizuale un set de praguri cfvo. Fiecare dintre acestea descrie fidel ce a configurat utilizatorul, și niciunul nu este un algoritm. Pentru jumătate dintre tipurile de reguli, acel gol nu contează: cellIs cu operator="greaterThan" înseamnă mai mare decât, iar containsText înseamnă că substringul este prezent. Golul se deschide la familiile agregate. O regulă top10 cu rank="10" și percent="1" peste 27 de celule numerice populate evidențiază câte celule? Doi virgulă șapte nu este un număr. Rotunjire, trunchiere sau plafonare — specificația tace, iar alegerea greșită înseamnă că PDF-ul dumneavoastră nu se potrivește cu registrul de calcul pe care clientul îl are deschis alături

Reguli cu o singură celulă și unde se oprește TCondFormatRule.Evaluate

HotXLS a luat mai întâi jumătatea ieftină. TCondFormatRule.Evaluate din lxCondFormat.pas, adăugată în 2.199.0, răspunde dacă o regulă se declanșează pentru o celulă fără să știe nimic despre restul intervalului. Gestionează cei opt operatori de comparație BIFF din spatele lui cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), regulile de tip liber expression evaluate la celulă astfel încât referințele relative se rebazează corect, cele patru predicate de text, și predicatele de goluri și erori. Pragurile vin din FFormula1 și FFormula2 rezolvate prin TXLSCalculator.GetRangeValue la poziția celulei, iar limitele inversate sunt interschimbate, nu respinse

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;

Partea onestă a acelei metode este ce refuză să ghicească. top10, aboveAverage, belowAverage, duplicateValues și uniqueValues returnează False, nu pentru că sunt greu de calculat, ci pentru că sunt indecidabile dintr-o singură celulă — fiecare dintre ele are nevoie de o statistică pe întregul domeniu. Cele patru familii vizuale, dataBar, colorScale2, colorScale3 și iconSet, returnează False dintr-un motiv diferit: nu produc niciodată deloc un boolean, produc un payload de randare, iar un tip de returnare Boolean este forma greșită pentru ele

Cum evită un evaluator la nivel de foaie rescanarea foii?

Calculând fiecare cantitate partajată o singură dată, la construcție, și niciodată din nou. TXLSXConditionalFormatEvaluator din lxHandleX.pas este un instantaneu imutabil pentru o foaie de calcul, construit prin TXLSXWorksheet.CreateConditionalFormatEvaluator, iar întregul lui design este o apărare împotriva implementării naive unde fiecare celulă pictată declanșează o scanare completă a intervalului

Patru lucruri se întâmplă în constructor. Fiecare sqref distinct cu zone multiple este analizat exact o dată într-un TXlsxCfRangeSnapshot, astfel încât zece reguli care partajează un interval partajează o analiză și o trecere de statistici. Acea trecere calculează în flux media, deviația de populație, minimul și maximul peste celulele populate într-o singură parcurgere, și reține un array numeric ordonat doar când o regulă Top/Bottom sau de percentilă chiar are nevoie de statistici de ordine. Cheile duplicate și unice sunt construite Unicode-safe și sortate în lot o singură dată, în loc de per căutare. Apoi axa de rânduri este tăiată în benzi la fiecare graniță de zonă, astfel încât EvaluateCell caută binar o bandă și vizitează doar regulile ale căror intervale pot ajunge posibil la acel rând

Al patrulea este cel care contează cel mai mult la scară. O formulă de regulă relativă precum =A1>AVERAGE($A$1:$A$100) înseamnă altceva în fiecare celulă a domeniului, iar implementarea evidentă compilează un arbore de sintaxă nou per celulă. TXlsxCfRulePlan îl compilează o singură dată și reevaluează același arbore prin deplasări de coordonate reversibile, ceea ce păstrează comportamentul de ancorare Excel fără o alocare de arbore de sintaxă per celulă. Regulile sunt apoi stratificate după priority, iar o potrivire pe o regulă al cărei StopIfTrue este setat oprește bucla, exact cum Excel scurtcircuitează

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;

Cum rotunjește de fapt Excel o regulă Top 10 procente?

Trunchiază, cu un minim de unu, și include egalitățile la punctul de tăiere. Asta nu este scris nicăieri în ISO 29500-1 — a fost fixat prin testarea Excel 16 cu registre de calcul construite manual și citind înapoi care celule le-a evidențiat aplicația. HotXLS implementează exact asta: numărul de rang este Floor(Count * Min(Rank, 100) / 100), ridicat la 1 când ajunge la zero, plafonat la numărul populat, iar valoarea de tăiere este apoi comparată cu >= astfel încât fiecare celulă egală cu granița este evidențiată chiar și atunci când aceasta depășește numărul cerut. Douăzeci și șapte de valori și o regulă de 10 procente evidențiază două celule, plus orice alte celule egale cu a doua

Regulile peste medie ascundeau o a doua ambiguitate: aboveAverage cu stdDev="1" selectează celulele cu o deviație standard peste medie, dar deviația de eșantion și cea de populație diferă prin corecția Bessel, și se contrazic vizibil pe intervale mici, exact unde se folosește formatarea condițională. Excel 16 folosește deviația de populație, iar HotXLS se potrivește, cu flag-ul equalAverage care face comparația strictă inclusivă doar când nicio bandă de deviație nu este în joc. Regulile de duplicate și unic au activat în schimb identitatea cheii. Dacă o celulă conține numărul 100 și alta conține textul "100", Excel le tratează ca aceeași cheie duplicat, așa că HotXLS normalizează textul numeric în spațiul cheii numerice, în loc să compare șiruri brute. Celulele goale sunt cazul oglindă: o celulă cu adevărat goală participă la totalul intervalului, dar nu este ea însăși stilizată, așa că celulele goale dintr-o coloană nu se aprind toate ca duplicate una a alteia

Scale de culoare și seturi de pictograme: interpolare și reguli de graniță

Familiile vizuale se rezolvă la numere gata de randare, nu la booleeni, iar comportamentul lor de margine a fost fixat în același mod. Pentru o scală de culoare cu praguri numerice explicite, HotXLS plafonează fracția de poziție la intervalul închis zero la unu, apoi interpolează per canal cu trunchiere, nu cu rotunjire — o valoare sub pragul minim primește culoarea minimă, nu una extrapolată, o scală cu trei praguri își alege perechea comparând cu pragul median, iar o scală degenerată ale cărei două capete poartă același prag colapsează la culoarea superioară în loc să împartă la zero. Seturile de pictograme au avut nevoie de un tip opus de grijă, deoarece fiecare cfvo după primul poartă propria stricteță de comparație: HotXLS citește ThresholdEqualsInclude per prag și aplică >= sau > corespunzător, parcurgând în sus astfel încât cel mai înalt prag satisfăcut câștigă indexul de pictogramă. Un set inversat inversează indexul rezolvat, nu pragurile, suprascrierile per pictogramă pot trage o glifă dintr-o familie diferită, iar orice prag invalid abandonează regula în loc să producă o pictogramă greșită dar plauzibilă

Alimentarea unei grile, a unui export HTML și a unui PDF dintr-un singur rezultat

Deoarece EvaluateCell returnează un TXLSXCfCellResult complet rezolvat — culoare de umplere și font diferențială cu nuanța de temă deja aplicată, aldin, cursiv, subliniat, id de format numeric, întinderi de bară pozitive și negative direcționale, poziție de axă, familie și index de pictogramă — fiecare consumator citește aceeași înregistrare și niciunul nu are nevoie să înțeleagă internele regulilor. HotXLS folosește acea singură cale pentru exportul HTML, exportul PDF și vizualizatorul interactiv, care este singurul mod practic de a împiedica cele trei randoare să diverge. Versiunea 2.210.0 a conectat-o în TXLSWorkbookViewer, care memorează în cache un evaluator pregătit per foaie de calcul activă și îl reutilizează pe durata derulării, selecției și repictării, eliberându-l când registrul de calcul sau foaia se schimbă — reconstruirea instantaneului la fiecare Paint ar înfrânge întregul design la momentul construcției. Acel cache este și motivul pentru care există TXLSWorkbookViewer.RefreshConditionalFormats: instantaneul este imutabil, așa că dacă modificați registrul de calcul atașat pe loc, statisticile agregate și pragurile rezolvate rămân învechite până când o apelați

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

Ce nu va face evaluatorul pentru dumneavoastră

Trei limite merită enunțate clar. TCondFormatRule.Evaluate clasic pentru o singură celulă și TXLSXConditionalFormatEvaluator la nivel de foaie sunt suprafețe diferite cu capacități diferite, iar cel pentru o singură celulă refuză deliberat familiile agregate și vizuale, în loc să le aproximeze — dacă aveți nevoie de Top/Bottom sau de o scală de culoare, construiți evaluatorul. Perioadele de dată relative depind de ceasul mașinii la momentul evaluării, așa că o regulă timePeriod se randează diferit într-un PDF generat azi și unul generat săptămâna viitoare, ceea ce este un comportament corect și totuși un tichet de suport care așteaptă să se întâmple dacă arhiva dumneavoastră este așteptată să fie stabilă pe biți. A treia este gramaticală, nu tehnică: gramatica formulei de format condițional interzice referințele de tabel structurate, așa că o regulă nu poate adresa o coloană de tabel după nume așa cum poate o formulă de foaie de calcul, iar aceasta este o constrângere a formatului, nu a implementării

Dacă construiți ieșire de raport, un pipeline de export sau o grilă personalizată care trebuie să se potrivească cu Excel celulă cu celulă, același rezultat rezolvat alimentează și grila VCL personalizată de foaie de calcul descrisă în altă parte pe acest blog. Documentația API completă, modelul de reguli și descărcările de probă pentru componenta de foaie de calcul HotXLS pentru Delphi sunt disponibile pe pagina de produs