A HotXLS natívan kiértékeli az Excel 365 dinamikus tömb túlcsorduló (spill) képleteit Delphiben és C++Builderben: átad neki egy munkafüzetet, amelynek cellái XLOOKUP vagy FILTER képleteket tartalmaznak, a képletmotorja pedig kiszámítja az eredményhalmazt, és szétteríti azt a rögzített kimeneti téglalapon, pontosan azokat az értékeket előállítva, amelyeket az Excel is produkálna. A HotXLS Delphi Excel komponens a túlcsorduló képletet egyetlen, a számítást birtokló gyökércellaként, valamint az abból visszaolvasó függő cellák blokkjaként modellezi, ami a teljes kép, amire szüksége van a kódírás megkezdése előtt
Ez a modell azért fontos, mert a mindennapi feladat nem absztrakt. Egy ügyfél küld egy Excel 365-ben készült .xlsx fájlt, amelynek munkalapjai tele vannak =FILTER(...) és =XLOOKUP(...) képletekkel, a szolgáltatásának pedig pontosan ugyanazokat a számokat kell előállítania headless módban egy olyan gépen, amelyre nincs Excel telepítve, majd vagy beolvasni a túlcsorduló értékeket, vagy saját új túlcsordulási tartományt írni. A dinamikus tömbök megváltoztatták a számítási szerződést „egy képlet, egy cella” helyett „egy képlet, egy téglalapnyi cella” formára, és a HotXLS motor ezt a szerződést követi, ahelyett, hogy egy előre kibővített rácskal színlelné azt
Hogyan értékelhető ki az XLOOKUP és a FILTER Delphiben?
A HotXLS minden dinamikustömb-függvényt egyetlen kiértékelő belépési ponton keresztül indít el, ami a CalcDynArrayFunc az lxCalc.pas-ban, így the teljes család egyetlen túlcsordulási kódútvonalon osztozik. A támogatott készlet az XLOOKUP és a FILTER (az eredeti kettő), kiegészülve az XMATCH, SORT, UNIQUE és SEQUENCE függvényekkel. Mindegyik egy kétdimenziós variant tömböt ad vissza skalár érték helyett: a SEQUENCE(3;2;1;1) egy 3-szor 2-es rácsot eredményez, a SORT sorba rendezi a sorokat bármely kulcsoszlop alapján növekvő vagy csökkenő sorrendben, a UNIQUE összevonja a duplikált sorokat az első előfordulást megtartva, a XMATCH pedig jelzi egy érték 1-alapú pozícióját ugyanazokkal a pontos, helyettesítő karakteres (wildcard), következő legkisebb és következő legnagyobb egyezési módokkal, mint az XLOOKUP. Eltérően a skalár statisztikai eloszlásfüggvényektől, amelyek hívásonként egyetlen számot adnak vissza, ezek a függvények egy egész formát (alakzatot) adnak vissza
Egy olyan munkafüzet kiszámításához, amely már tartalmazza ezeket a képleteket, nyissa meg azt, hívja meg egyszer a Recalculate metódust, majd olvassa be azokat a cellákat, amelyekbe a túlcsordulás landolt. A TXLSXWorkbook.Recalculate bejárja a függőségi gráfot, és topológiai sorrendben értékeli ki az egyes változtatott képleteket, így a túlcsordulás gyökere egyetlen alkalommal kerül kiszámításra, elemei pedig közvetlenül beíródnak a tagcellákba
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
r, c: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('from-customer.xlsx');
Book.Recalculate; // evaluate every formula, spills included
Sheet := Book.Sheets[1]; // Sheets is 1-based
// Read back the block the XLOOKUP / FILTER spilled into, say D2:F9.
for r := 2 to 9 do
for c := 4 to 6 do
Writeln(Sheet.Cells[r, c].Text);
finally
Book.Free;
end;
end;Ha a munkafüzetnek olyan funkcióra van szüksége, amelyet a motor nem szállít, ugyanez a kiértékelő lehetővé teszi saját logika beillesztését a személyre szabott munkalap-függvények hook-ján keresztül, és az egyedi függvények visszaadhatnak variant tömböt is, így pontosan úgy túlcsordulnak, mint a beépített társaik
A túlcsordulási tartomány rögzítése a SetArrayFormula segítségével
A HotXLS sosem találgatja, hogy mekkora legyen a túlcsordulás: Ön megadja a kimeneti téglalapot, a TXLSRange.SetArrayFormula pedig hozzá rögzíti a képletvet. A metódus egyszer lefordítja a képletet, eltárolja a szintaxisfát a bal felső gyökércellán a rögzített tartománnyal együtt, és a téglalap minden más cellájának egy könnyűsúlyú képletet ad, amely gyengén hivatkozik a gyökérre. Újraszámításkor a gyökér egyszer értékelődik ki, mátrixelemei pedig közvetlenül az egyes tagcellákra sugározzák magukat, és a túlcsordulás gyökere így jelenik meg egyetlen csomópontként az inkrementális újraszámítás függőségi gráfjában. Ezt az utat kell használnia, ha a feladat a túlcsordulási tartomány visszaírása a fájlba, ahelyett, hogy kiolvasna egyet
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;Az explicit rögzítés következménye egy olyan korlát, amelyet érdemes nyíltan kimondani: a HotXLS nem méretezi át automatikusan a túlcsordulást úgy, ahogy az interaktív Excel teszi. Az Excel növeli és csökkenti a túlcsordulási tartományt a bemenetek változásával, és #SPILL! hibát jelez, ha a célcellák foglaltak. A HotXLS-ben a SetArrayFormula metódusnak átadott téglalap az a téglalap, amelyet kap, így a várt eredményhez kell méreteznie, és ha egy függvény több sort állít elő, mint amennyit rögzített, a feleslegnek nincs hová kerülnie
Elemenkénti tömbsugárzás az operátorokon keresztül
A HotXLS elemenként alkalmazza az aritmetikai operátorokat, ha a művelet bármelyik oldala tömb. Az ApplyArrayBinaryOp segédfüggvény lefedi a +, -, *, / és ^ műveleteket az 1D és 2D variant tömbök felett: a skalár operandus minden elemre kisugárzódik, a két tömbnek egyeznie kell alakban, különben a művelet elutasításra kerül, a nullával való osztás pedig hibaként terjed tovább összeomlás helyett. A skalár aritmetikai útvonal érintetlen marad, így ez csak akkor aktiválódik, ha legalább az egyik operandus valóban tömb, mint például egy túlcsorduló tartomány vagy egy mátrixfüggvény eredménye. Ez azt jelenti, hogy egy oszlop felett rögzített =D2:D13*1.1 képlet sorban megszorozza az egyes elemeket, a =D2:D13*E2:E13 pedig pozícióról pozícióra szoroz össze két azonos alakú oszlopot
// 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;Mit csinál a @ operátor a klasszikus CSE tömbhöz képest?
A HotXLS az explicit @ karaktert hivatkozásmetszet-operátorként (reference-intersection operator) olvassa be, nem pedig az élő Excel sorválasztó implicit metszeteként. A motorban az A1:A3 @ B1:B3 azt az egyetlen cellát adja vissza, ahol a két tartomány keresztezi egymást, és az operátor szorosabban köt, mint a ^ és lazábban, mint a %. Egy őszinte megjegyzés kapcsolódik ehhez: az implicit, szóközzel elválasztott metszetforma még nincs tokenizálva, mivel a lexer továbbra is kihagyja a szóközöket, így metszetet csak az explicit @ szimbólumon keresztül kap
A mélyebb különbség a klasszikus CSE tömbképlet és a modern dinamikus tömb között van, és a HotXLS-ben mindkettő ugyanazon a rögzítési mechanizmuson halad át. Egy klasszikus tömbképlet a Ctrl+Shift+Enter billentyűkombinációval beírt {=...} blokk volt egy előre méretezett kijelölés felett, ahol minden cella ugyanazon a lefordított képleten osztozott. A dinamikus tömb egyetlen képlet, amelynek eredménye határozza meg a saját alakját. A HotXLS egyesíti őket a SetArrayFormula alatt: Ön mindig megnevezi a téglalapot, a gyökér birtokolja a lefordított fát, a tagok pedig hivatkoznak rá, legyen a képlet régi stílusú mátrixkifejezés vagy új SORT. Amit soha nem kap meg, az az Excel automatikus hullámzása, ahol a tartomány csendben újranő, és ezen határ észben tartása megakadályozza a két mentális modell elmosódását
Ahol a dinamikustömb-támogatás véget ér
A határok ismerete megspórolhat egy délutánnyi hibakeresést. A SORT tiszteletben tartja a kulcsonkénti irányt: alapértelmezetten növekvő, és csökkenő, ha az order argumentum negatív; a hasonlítási irány az a hely, ahol érdemes letesztelnie a saját kulcsoszlopait az Excelhez képest, mivel a megfordított összehasonlítás csendben fordított sorrendben adja vissza a sorokat ahelyett, hogy meghiúsulna. A UNIQUE megtartja az egyes eltérő sorok első előfordulását, amit a motor az előző sorok átvizsgálásával igazol, ahelyett, hogy bízna a nyers duplikátumszámlálásban. A TABLE függvény (Mi lenne, ha vizsgálat adattáblák) fel van ismerve, így végigmegy oda-vissza a fájlon, de helyőrzővé értékelődik ki, mivel a valódi behelyettesítési eredmények az Excel által már tárolt gyorsítótárazott értékek, és a HotXLS nem futtatja újra a „mi lenne, ha” rácsot
A LET a legélesebb korlát, amelyet meg kell említeni. A HotXLS regisztrálja a LET-et, és hordoz hozzá kiértékelési útvonalat, de az elemző (parser) elutasít minden olyan puszta nevet, amely nem egy már definiált név, így a =LET(x;10;x) elbukik az elemzés során, még mielőtt elérné a kiértékelőt. Kezelje a LET-et nem támogatottként, amíg az elemző hatókör-tudatosságot nem kap a kötött neveihez. Egy kisebb hordozhatósági megjegyzés: a képlet-karakterláncok ezekben a példákban a motor listaelválasztóját, a pontosvesszőt használják, így egyeztesse a képletszöveg összeállításakor azzal az elválasztóval, amelyet az Ön buildje elvár. Az itt tárgyalt dinamikustömb-függvények az XLOOKUP-tól és FILTER-től kezdve a SORT, UNIQUE, SEQUENCE és XMATCH függvényekig a Delphihez készült HotXLS Delphi Excel Component részeként érkeznek, amelynek képlet-referenciája felsorolja a teljes függvénykatalógust és az egyes függvények támogatott argumentum-módjait