HotXLS procjenjuje Excel 365 formule s dinamičkim poljem prelijevanja izvorno u Delphiju i C++Builderu: predajete mu radnu knjigu čije ćelije sadrže XLOOKUP ili FILTER, a njegov mehanizam za formule izračunava skup rezultata i prelijeva ga preko usidrenog izlaznog pravokutnika, što su iste vrijednosti koje bi Excel proizveo. HotXLS Delphi Excel komponenta modelira formulu prelijevanja kao jednu korijensku ćeliju koja posjeduje izračun te blok ovisnih ćelija koje čitaju podatke iz nje, što je cijela slika koja vam je potrebna prije pisanja koda
Taj model je važan jer svakodnevni zadatak nije apstraktan. Korisnik šalje .xlsx datoteku stvorenu u Excelu 365, čiji su listovi puni funkcija =FILTER(...) i =XLOOKUP(...), a vaš servis mora reproducirati točno iste brojeve bez grafičkog sučelja (headless), na računalu bez instaliranog Excela, te potom pročitati prelivene vrijednosti ili zapisati novo područje prelijevanja. Dinamička polja promijenila su ugovor o izračunu s "jedna formula, jedna ćelija" na "jedna formula, pravokutnik ćelija", a HotXLS pokretač slijedi taj ugovor umjesto da ga lažira s unaprijed proširenom mrežom
Kako procijeniti XLOOKUP i FILTER u Delphiju?
HotXLS prosljeđuje svaku funkciju dinamičkog polja kroz jednu ulaznu točku procjenitelja, CalcDynArrayFunc u lxCalc.pas, pa cijela obitelj dijeli istu putanju koda za prelijevanje. Podržani skup čine XLOOKUP i FILTER (izvorne dvije), kojima se pridružuju XMATCH, SORT, UNIQUE i SEQUENCE. Svaka vraća dvodimenzionalno polje varijanti (variant array) umjesto skalara: SEQUENCE(3;2;1;1) daje mrežu 3-kroz-2, SORT ponovno razvrstava retke prema bilo kojem ključnom stupcu uzlazno ili silazno, UNIQUE sažima duplicirane retke zadržavajući prvo pojavljivanje, a XMATCH javlja poziciju vrijednosti počevši od 1 pod istim načinima točnog, zamjenskog, sljedećeg manjeg i sljedećeg većeg podudaranja kao i XLOOKUP. Za razliku od skalarnih funkcija statističke raspodjele, koje vraćaju jedan broj po pozivu, ove funkcije vraćaju cijeli oblik
Da biste izračunali radnu knjigu koja već sadrži ove formule, otvorite je, pozovite Recalculate jednom i pročitajte ćelije u koje je sletjelo prelijevanje. TXLSXWorkbook.Recalculate prolazi kroz grafikon ovisnosti i procjenjuje svaku promijenjenu formulu u topološkom redoslijedu, tako da se korijen prelijevanja izračunava samo jednom, a njegovi se elementi upisuju izravno u pripadajuće ćelije
Ako radna knjiga zahtijeva funkciju koju pokretač ne isporučuje, isti vam procjenitelj omogućuje da priključite vlastitu logiku putem kuke za prilagođene funkcije radnog lista, a prilagođena funkcija može slobodno vratiti polje varijanti tako da se prelijeva točno poput ugrađenih
Sidrenje raspona prelijevanja pomoću SetArrayFormula
HotXLS nikada ne pogađa koliko bi prelijevanje trebalo biti veliko: vi imenujete izlazni pravokutnik, a TXLSRange.SetArrayFormula sidri formulu za njega. Metoda jednom prevodi formulu, pohranjuje sintaksno stablo u gornju lijevu korijensku ćeliju zajedno s usidrenim rasponom i daje svakoj drugoj ćeliji u pravokutniku laganu formulu koja se slabo referencira na korijen. Pri ponovnom izračunu korijen se procjenjuje jednom i njegovi elementi matrice šalju se izravno u svaku pojedinu ćeliju, što je ujedno i način na koji se korijen prelijevanja prikazuje kao jedan čvor u grafikonu ovisnosti inkrementalnog ponovnog izračuna. To je put koji treba koristiti kada je zadatak zapisati regiju prelijevanja natrag u datoteku, a ne je samo pročitati
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;Posljedica eksplicitnog sidrenja je granica koju vrijedi jasno istaknuti: HotXLS ne mijenja automatski veličinu prelijevanja na način na koji to čini interaktivni Excel. Excel širi i smanjuje preliveno područje kako se njegovi ulazi mijenjaju i podiže pogrešku #SPILL! kada su ciljne ćelije zauzete. U HotXLS-u, pravokutnik koji proslijedite u SetArrayFormula je pravokutnik koji dobivate, pa ga morate prilagoditi rezultatu koji očekujete, a ako funkcija proizvede više redaka nego što ste usidrili, višak nema kamo sletjeti
Emitiranje polja po elementima kroz operatore
HotXLS primjenjuje aritmetičke operatore element po element kada je bilo koja strana operacije polje. Pomoćna funkcija ApplyArrayBinaryOp pokriva +, -, *, / i ^ nad jednodimenzionalnim i dvodimenzionalnim poljima varijanti: skalarni operand se šalje svakom elementu, dva polja moraju odgovarati po obliku ili se operacija odbija, a dijeljenje s nulom se prenosi kao pogreška umjesto rušenja sustava. Putanja skalarne aritmetike je netaknuta, tako da se ovo aktivira samo kada je barem jedan operand uistinu polje, poput prelivenog raspona ili rezultata matrične funkcije. To znači da formula poput =D2:D13*1.1 usidrena preko stupca množi svaki element redom, a =D2:D13*E2:E13 množi dva stupca 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;Što radi operator @ nasuprot naslijeđenog CSE polja?
HotXLS čita eksplicitni znak @ kao operator sjecišta referenci, a ne kao implicitno sjecište za odabir redaka u stvarnom Excelu. U pokretaču, A1:A3 @ B1:B3 vraća jednu ćeliju u kojoj se dva raspona sijeku, a operator se veže jače od ^ i slabije od %. Uz to ide i jedna iskrena napomena: implicitni oblik sjecišta odvojen razmakom još nije tokeniziran, jer leksički analizator i dalje preskače razmake, pa sjecište dobivate samo putem eksplicitnog simbola @
Dublja razlika je između naslijeđene CSE formule polja i modernog dinamičkog polja, a u HotXLS-u obje teku kroz isti mehanizam sidrenja. Klasična formula polja bila je blok {=...} unesen s Ctrl+Shift+Enter preko unaprijed odabranog područja, pri čemu svaka ćelija dijeli jednu prevedenu formulu. Dinamičko polje je jedna formula čiji rezultat određuje njezin vlastiti oblik. HotXLS ih objedinjuje pod SetArrayFormula: uvijek imenujete pravokutnik, korijen posjeduje prevedeno stablo, a članovi ga referenciraju, bilo da se radi o starinskom izrazu matrice ili novoj funkciji SORT. Ono što nikada ne dobivate je Excelovo automatsko širenje u kojem se regija tiho ponovno povećava, a držanje te granice na umu sprječava zamagljivanje ova dva mentalna modela
Gdje se zaustavlja podrška za dinamička polja
Poznavanje granica štedi poslijepodne provedeno u uklanjanju pogrešaka. SORT poštuje smjer po svakom ključu, uzlazno prema zadanim postavkama i silazno kada je argument redoslijeda negativan; smjer usporedbe vrijedi testirati s vlastitim ključnim stupcima u Excelu, jer obrnuta usporedba tiho vraća retke u obrnutom redoslijedu umjesto neuspjeha. UNIQUE zadržava prvo pojavljivanje svakog različitog retka, što pokretač potvrđuje skeniranjem prethodnih redaka umjesto oslanjanja na sirovi broj duplikata. Funkcija TABLE (tablice podataka analize "Što-ako") prepoznaje se pa prolazi kroz datoteku u oba smjera, ali se procjenjuje na rezervirano mjesto (placeholder) jer su stvarni rezultati supstitucije predmemorirane vrijednosti koje je Excel već spremio, a HotXLS ne pokreće ponovno mrežu "što-ako"
LET je najizraženije ograničenje koje treba istaknuti. HotXLS registrira LET i ima putanju procjene za njega, ali raščlanjivač odbija golo ime koje nije već definirano ime, pa =LET(x;10;x) ne uspijeva tijekom raščlanjivanja prije nego što se uopće dođe do procjenitelja. Tretirajte LET kao nepodržan dok raščlanjivač ne dobije svijest o opsegu za svoja vezana imena. Mala napomena o prenosivosti: nizovi formula u ovim primjerima koriste separator popisa pokretača, točku-zarez, pa uskladite separator s onim što vaš sustav očekuje kada sastavljate tekst formule. Funkcije dinamičkog polja koje su ovdje obrađene, od XLOOKUP i FILTER do SORT, UNIQUE, SEQUENCE i XMATCH, isporučuju se u HotXLS Delphi Excel komponente, čija referentna dokumentacija formula sadrži potpuni katalog funkcija i podržane načine argumenata za svaku funkciju