Formule prelivanja dinamičkih nizova u Delphi-ju pomoću HotXLS-a

HotXLS procenjuje formule prelivanja (spill) dinamičkih nizova u Excelu 365 izvorno u Delphi-ju i C++Builder-u: dajete joj radnu svesku (workbook) čije ćelije sadrže XLOOKUP ili FILTER, a njen motor za formule izračunava skup rezultata i preliva ga preko sidrenog (anchored) izlaznog pravougaonika, dajući iste vrednosti koje bi Excel proizveo. HotXLS Delphi Excel komponenta modelira formulu prelivanja kao jednu korensku ćeliju koja poseduje proračun plus blok zavisnih ćelija koje čitaju podatke iz nje, što predstavlja kompletnu sliku koja vam je potrebna pre pisanja ijedne linije koda

Taj model je važan jer svakodnevni zadatak nije apstraktan. Klijent šalje .xlsx datoteku kreiranu u Excelu 365, njeni listovi su puni formula =FILTER(...) i =XLOOKUP(...), a vaš servis mora da reprodukuje potpuno iste brojeve bez interfejsa (headless), na mašini na kojoj Excel nije instaliran, a zatim ili da pročita prelivene vrednosti ili da upiše novu sopstvenu oblast prelivanja. Dinamički nizovi su promenili ugovor o proračunu sa "jedna formula, jedna ćelija" na "jedna formula, pravougaonik ćelija", a HotXLS motor prati taj ugovor umesto da ga simulira pomoću unapred proširene mreže

Kako proceniti XLOOKUP i FILTER u Delphi-ju?

HotXLS prosleđuje svaku funkciju dinamičkog niza kroz jednu ulaznu tačku evaluatora, CalcDynArrayFunc u lxCalc.pas, tako da cela grupa deli jednu putanju koda za prelivanje. Podržani skup čine XLOOKUP i FILTER (originalne dve), kojima se pridružuju XMATCH, SORT, UNIQUE i SEQUENCE. Svaka od njih vraća dvodimenzionalni variant niz (array) umesto skalarne vrednosti: SEQUENCE(3;2;1;1) daje mrežu 3-sa-2, SORT ponovo sortira redove po bilo kojoj ključnoj koloni rastuće ili opadajuće, UNIQUE sažima duplirane redove zadržavajući prvo pojavljivanje, a XMATCH prijavljuje poziciju vrednosti zasnovanu na indeksu 1 pod potpuno istim režimima podudaranja (exact, wildcard, next-smaller i next-larger) kao i XLOOKUP. Za razliku od skalarnih funkcija statističke raspodele, koje vraćaju jedan broj po pozivu, ove funkcije vraćaju ceo oblik

If the workbook needs a function the engine does not ship, the same evaluator lets you plug your own logic in through the custom worksheet functions hook, and a custom function is free to return a variant array so it spills exactly like the built-ins

Sidrenje opsega prelivanja pomoću SetArrayFormula

HotXLS never guesses how large a spill should be: you name the output rectangle, and TXLSRange.SetArrayFormula anchors the formula to it. The method compiles the formula once, stores the syntax tree on the top-left root cell together with the anchored range, and gives every other cell in the rectangle a lightweight formula that weak-references the root. At recalculation the root evaluates once and its matrix elements are broadcast directly onto each member cell, which is also how a spill root shows up as one node in the incremental recalculation dependency graph. This is the path to use when the job is to write a spill region back into the file rather than read one out

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;

Posledica eksplicitnog sidrenja je granica koju vredi jasno istaći: HotXLS ne menja automatski veličinu prelivanja na način na koji to radi interaktivni Excel. Excel proširuje i skuplja prelivenu oblast kako se njegovi unosi menjaju i prijavljuje grešku #SPILL! kada su ciljne ćelije zauzete. U HotXLS-u, pravougaonik koji prosledite u SetArrayFormula je pravougaonik koji dobijate, tako da ga prilagođavate rezultatu koji očekujete, a ako funkcija proizvede više redova nego što ste usidrili, višak nema gde da se smesti

Emitovanje niza po elementima kroz operatore

HotXLS primenjuje aritmetičke operatore element po element kada je bilo koja strana operacije niz. Pomoćna funkcija ApplyArrayBinaryOp pokriva operatore +, -, *, / i ^ nad jednodimenzionalnim i dvodimenzionalnim variant nizovima: skalarni operand se emituje na svaki element, dva niza moraju da se podudaraju u obliku ili se operacija odbija, a deljenje nulom se prenosi kao greška umesto da dovede do rušenja programa. Putanja skalarne aritmetike je netaknuta, tako da se ovo aktivira samo kada je barem jedan operand zaista niz, kao što je preliveni opseg ili rezultat matrične funkcije. To znači da formula kao što je =D2:D13*1.1 usidrena preko kolone množi svaki element redom, a =D2:D13*E2:E13 množi dve kolone istog oblika poziciju po poziciju

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

Šta radi operator @ u poređenju sa starim CSE nizom?

HotXLS čita eksplicitno @ kao operator preseka referenci (reference-intersection), a ne kao implicitni presek koji bira red u aktivnom Excelu. U motoru, A1:A3 @ B1:B3 vraća pojedinačnu ćeliju gde se ova dva opsega presecaju, i operator se vezuje jače od ^ a slabije od %. Uz to ide i jedna iskrena napomena: implicitni oblik preseka odvojen razmakom još uvek nije tokenizovan, jer lekser i dalje preskače razmake, tako da presek dobijate samo preko eksplicitnog simbola @

Dublja razlika je između stare CSE formule niza i modernog dinamičkog niza, a u HotXLS-u oba prolaze kroz istu mašineriju sidrenja. Klasična formula niza je bila blok {=...} unet pomoću Ctrl+Shift+Enter preko unapred određene selekcije, gde svaka ćelija deli jednu kompajliranu formulu. Dinamički niz je jedna formula čiji rezultat određuje sopstveni oblik. HotXLS ih objedinjuje pod SetArrayFormula: uvek navodite pravougaonik, koren poseduje kompajlirano stablo, a članovi ga referenciraju, bilo da je formula matrični izraz starog stila ili novi SORT. Ono što nikada ne dobijate jeste Excelovo automatsko širenje gde se oblast tiho ponovo izgrađuje, a držanje te granice na umu sprečava da se ova dva mentalna modela zamagle

Gde se zaustavlja podrška za dinamičke nizove

Poznavanje granica štedi popodne provedeno u otklanjanju grešaka. SORT poštuje smer po ključu (per-key direction), rastući podrazumevano i opadajući kada je argument redosleda negativan; Smer poređenja je stvar koju vredi testirati na sopstvenim ključnim kolonama u poređenju sa Excelom, pošto obrnuto poređenje tiho vraća redove u suprotnom redosledu umesto da prijavi grešku. Funkcija UNIQUE zadržava prvo pojavljivanje svakog jedinstvenog reda, što motor potvrđuje skeniranjem prethodnih redova umesto da veruje sirovom broju duplikata. Funkcija TABLE (tabele podataka za What-If analizu) se prepoznaje tako da prolazi kroz datoteku u oba smera, ali se procenjuje kao čuvar mesta (placeholder) jer su stvarni rezultati supstitucije keširane vrednosti koje je Excel već sačuvao, a HotXLS ne pokreće ponovo mrežu what-if analize

Funkcija LET je najizraženije ograničenje koje treba istaći. HotXLS registruje LET i ima putanju evaluacije za nju, ali parser odbija ogoljeno ime koje nije već definisano, tako da =LET(x;10;x) ne uspeva tokom parsiranja, pre nego što se uopšte stigne do evaluatora. Tretirajte LET kao nepodržanu funkciju sve dok parser ne dobije svest o opsegu (scope) za svoja povezana imena. Mala napomena o portabilnosti: stringovi formula u ovim primerima koriste separator liste motora, tačka-zarez, pa uskladite sa separatorom koji vaš build očekuje kada sastavljate tekst formule. Funkcije dinamičkih nizova koje su ovde pokrivene, od XLOOKUP i FILTER, pa kroz SORT, UNIQUE, SEQUENCE i XMATCH, isporučuju se u HotXLS Delphi Excel komponenti, čija referenca formula navodi kompletan katalog funkcija i podržane režime argumenata za svaku funkciju