Formule de propagare (spill) a tablourilor dinamice în Delphi cu HotXLS

HotXLS evaluează în mod nativ în Delphi și C++Builder formulele de propagare (spill) a tablourilor dinamice din Excel 365: îi furnizați un registru de calcul (workbook) ale cărui celule conțin XLOOKUP sau FILTER, iar motorul său de formule calculează setul de rezultate și îl propagă pe dreptunghiul de ieșire ancorat, oferind aceleași valori pe care le-ar genera Excel. Componenta HotXLS Delphi Excel modelează o formulă de propagare ca o celulă rădăcină (root) care deține calculul, plus un bloc de celule dependente care citesc din aceasta, oferindu-vă imaginea de ansamblu de care aveți nevoie înainte de a scrie o linie de cod

Acest model este important deoarece sarcinile zilnice nu sunt abstracte. Un client trimite un fișier .xlsx creat în Excel 365, iar foile sale de calcul sunt pline de =FILTER(...) și =XLOOKUP(...); serviciul dvs. trebuie să reproducă exact aceleași numere headless, pe o mașină fără Excel instalat, then fie să citească valorile propagate, fie să scrie o zonă de propagare proprie. Tablourile dinamice au schimbat contractul de calcul de la „o formulă, o celulă” la „o formulă, un dreptunghi de celule”, iar motorul HotXLS respectă acest contract, fără a-l simula cu o grilă pre-extinsă

Cum se evaluează XLOOKUP și FILTER în Delphi?

HotXLS direcționează fiecare funcție de tablou dinamic printr-un singur punct de intrare al evaluatorului, CalcDynArrayFunc din lxCalc.pas, astfel încât întreaga familie partajează aceeași cale de cod pentru propagare. Setul acceptat este format din XLOOKUP și FILTER (cele două inițiale), completat de XMATCH, SORT, UNIQUE și SEQUENCE. Fiecare returnează un tablou bidimensional variant în loc de un scalar: SEQUENCE(3;2;1;1) generează o grilă de 3 pe 2, SORT reordonează rândurile după orice coloană cheie în ordine crescătoare sau descrescătoare, UNIQUE restrânge rândurile duplicate păstrând prima apariție, iar XMATCH raportează poziția bazată pe 1 a unei valori în aceleași moduri de potrivire exactă, cu caractere joker (wildcard), următoarea mai mică și următoarea mai mare ca XLOOKUP. Spre deosebire de funcțiile scalare de distribuție statistică, care returnează un singur număr per apel, aceste funcții returnează o formă completă

Ancorarea intervalului de propagare cu SetArrayFormula

HotXLS nu ghicește niciodată cât de mare ar trebui să fie o propagare: indicați dreptunghiul de ieșire, iar TXLSRange.SetArrayFormula ancorează formula de acesta. Metoda compilează formula o singură dată, stochează arborele sintactic în celula rădăcină stânga-sus împreună cu intervalul ancorat și oferă fiecărei alte celule din dreptunghi o formulă simplificată care face o referire slabă (weak reference) la rădăcină. La recalculare, rădăcina este evaluată o singură dată, iar elementele matricei sale sunt transmise direct către fiecare celulă membră, acesta fiind modul în care o rădăcină de propagare apare ca un singur nod în graful de dependențe al recalculării incrementale. Aceasta este calea de utilizat atunci când sarcina este de a scrie o zonă de propagare înapoi în fișier, în loc de a citi una existentă

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Size the rectangle to the result: a 3-row by 2-column grid.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // fill A1:B3 with 1..6 down the rows
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

Consecința ancorării explicite este o limită ce trebuie menționată clar: HotXLS nu redimensionează automat o zonă de propagare în modul în care o face Excel-ul interactiv. Excel mărește sau micșorează regiunea propagată pe măsură ce datele de intrare se modifică și generează eroarea #SPILL! atunci când celulele țintă sunt ocupate. În HotXLS, dreptunghiul pe care îl transmiteți la SetArrayFormula este dreptunghiul pe care îl obțineți, deci trebuie să-l dimensionați la rezultatul pe care îl anticipați, iar dacă o funcție produce mai multe rânduri decât cele ancorate, surplusul nu are unde să fie scris

Transmiterea element cu element în tablouri prin operatori

HotXLS aplică operatorii aritmetici element cu element atunci când oricare dintre părțile operațiunii este un tablou. Funcția ajutătoare ApplyArrayBinaryOp acoperă operatorii +, -, *, / și ^ pe tablourile variante unidimensionale și bidimensionale: un operand scalar este transmis fiecărui element, două tablouri trebuie să aibă aceeași formă, altfel operațiunea este respinsă, iar împărțirea la zero se propagă ca o eroare în loc să blocheze aplicația. Calea aritmetică scalară este nemodificată, astfel încât aceasta se activează doar atunci când cel puțin un operand este cu adevărat un tablou, cum ar fi un interval propagat sau rezultatul unei funcții matriceale. Asta înseamnă că o formulă precum =D2:D13*1.1 ancorată pe o coloană înmulțește fiecare element la rând, iar =D2:D13*E2:E13 înmulțește două coloane de forme egale poziție cu poziție

// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

Ce face operatorul @ în comparație cu un tablou CSE moștenit (legacy)?

HotXLS citește un simbol @ explicit ca operator de intersecție a referințelor, nu ca intersecție implicită de selectare a rândurilor din Excel. În motor, A1:A3 @ B1:B3 returnează singura celulă în care se intersectează cele două intervale, iar operatorul are o prioritate mai mare decât ^ și mai mică decât %. Menționăm în mod onest că forma de intersecție implicită separată prin spații nu este încă lexicalizată, deoarece parserul lexical ignoră spațiile albe, astfel încât obțineți intersecție doar prin simbolul @ explicit

Distanța mai profundă este între o formulă de tablou CSE moștenită (legacy) și un tablou dinamic modern, iar în HotXLS ambele trec prin același mecanism de ancorare. O formulă de tablou clasică era blocul {=...} introdus cu Ctrl+Shift+Enter pe o selecție pre-dimensionată, fiecare celulă partajând o singură formulă compilată. Un tablou dinamic este o singură formulă al cărei rezultat își determină propria formă. HotXLS le unește sub SetArrayFormula: indicați întotdeauna dreptunghiul, rădăcina deține arborele compilat, iar membrii îl referențiază, fie că formula este o expresie matriceală de tip vechi, fie un SORT nou. Ceea ce nu veți obține este modificarea automată din Excel în care regiunea se redimensionează silențios, iar reținerea acestei reguli împiedică suprapunerea celor două modele mentale

Unde se oprește suportul pentru tablouri dinamice

Cunoașterea limitelor vă poate scuti de ore întregi de depanare. SORT respectă o direcție per cheie, crescătoare în mod implicit și descrescătoare când argumentul de ordine este negativ; direcția de comparare este un detaliu ce merită testat pentru coloanele dvs. cheie în raport cu Excel, deoarece o comparare inversată returnează rândurile în ordine inversă fără a genera o eroare. UNIQUE păstrează prima apariție a fiecărui rând distinct, lucru pe care motorul îl confirmă prin scanarea rândurilor anterioare, în loc să se bazeze pe o numărătoare brută a duplicatelor. Funcția TABLE (tabele de date de analiză What-If) este recunoscută și round-tripped prin fișier, dar este evaluată ca un substituent deoarece rezultatele reale ale substituțiilor sunt valorile stocate în cache pe care Excel le-a salvat deja, iar HotXLS nu rulează din nou grila what-if

Funcția LET este cea mai clară limitare de menționat. HotXLS înregistrează LET și deține o cale de evaluare pentru aceasta, dar parserul respinge un nume simplu care nu este deja definit, astfel încât =LET(x;10;x) eșuează la analiză înainte ca evaluatorul să fie accesat. Tratați LET ca fiind neacceptat până când parserul va dobândi recunoașterea domeniului de aplicare (scope awareness) pentru numele sale asociate. O mică notă de portabilitate: șirurile de formule din aceste exemple folosesc separatorul de liste al motorului, punct și virgulă, așa că alegeți separatorul pe care îl așteaptă structura dvs. când asamblați textul formulelor. Funcțiile de tablouri dinamice acoperite aici, de la XLOOKUP și FILTER până la SORT, UNIQUE, SEQUENCE și XMATCH, sunt livrate în HotXLS Delphi Excel Component, a cărui documentație de formule enumeră catalogul complet de funcții și modurile de argumentare acceptate pentru fiecare funcție