Prelivne formule dinamičnih polj v Delphi s HotXLS

HotXLS izvorno vrednoti prelivne formule dinamičnih polj Excel 365 v Delphi in C++Builder: predate mu delovni zvezek, katerega celice vsebujejo XLOOKUP ali FILTER, njegov motor za formule pa izračuna nabor rezultatov in jih prelije čez sidrani izhodni pravokotnik, kar so enake vrednosti, kot bi jih ustvaril Excel. Delphi Excel komponenta HotXLS modelira prelivno formulo kot eno korensko celico, ki ima v lasti izračun, ter blok odvisnih celic, ki berejo iz nje, kar je celotna slika, ki jo potrebujete pred pisanjem vrstice kode

Ta model je pomemben, ker vsakodnevno opravilo ni abstraktno. Stranka pošlje datoteko .xlsx, zgrajeno v Excelu 365, njeni listi pa so polni formul =FILTER(...) in =XLOOKUP(...), vaša storitev pa mora poustvariti povsem enake številke brez glave (headless), na stroju brez nameščenega Excela, nato pa prebrati prelite vrednosti ali zapisati novo prelivno območje po vaši meri. Dinamična polja so spremenila pogodbo izračuna iz "ena formula, ena celica" v "ena formula, pravokotnik celic", motor HotXLS pa sledi tej pogodbi, namesto da bi jo hlinil s vnaprej razširjeno mrežo

Kako vrednotiti XLOOKUP in FILTER v Delphi?

HotXLS odpošlje vsako funkcijo dinamičnega polja prek ene same vstopne točke vrednotilnika, CalcDynArrayFunc v lxCalc.pas, tako da si celotna družina deli eno pot prelivne kode. Podprt nabor sestavljata XLOOKUP in FILTER (prva dva), ki se jima pridružujejo XMATCH, SORT, UNIQUE in SEQUENCE. Vsaka funkcija vrne dvodimenzionalno polje variant namesto skalarja: SEQUENCE(3;2;1;1) ustvari mrežo 3-krat-2, SORT prerazvrsti vrstice po katerem koli ključnem stolpcu naraščajoče ali padajoče, UNIQUE strne podvojene vrstice in ohrani prvo pojavitev, XMATCH pa poroča o 1-indeksiranem položaju vrednosti pod enakimi načini ujemanja (natančno, nadomestni znaki, naslednji manjši in naslednji večji) kot XLOOKUP. Za razliko od skalarnih statističnih distribucijskih funkcij, ki vrnejo eno številko na klic, te funkcije vrnejo celotno obliko

Če želite izračunati delovni zvezek, ki že prenaša te formule, ga odprite, enkrat pokličete Recalculate in preberite celice, v katerih je pristal preliv. TXLSXWorkbook.Recalculate prehodi graf odvisnosti in ovrednoti osako umazano formulo v topološkem vrstnem redu, tako da se koren preliva izračuna le enkrat, njegovi elementi pa se zapišejo neposredno v celice članice

Če delovni zvezek potrebuje funkcijo, ki je motor ne dobavlja, vam isti vrednotilnik omogoča priklop lastne logike prek kavlja za uporabniške funkcije delovnega lista, uporabniška funkcija pa lahko vrne polje variant, tako da se prelije natanko tako kot vgrajene

Sidranje prelivnega območja s SetArrayFormula

HotXLS nikoli ne ugiba, kako velik bi moral biti preliv: poimenujete izhodni pravokotnik, TXLSRange.SetArrayFormula pa nanj sidra formulo. Metoda enkrat prevede formulo, shrani skladenjsko drevo na korensko celico zgoraj levo skupaj s sidranim območjem, vsaki drugi celici v pravokotniku pa dodeli lahko formulo, ki se šibko sklicuje na koren. Pri preračunavanju se koren ovrednoti enkrat, njegovi matrični elementi pa se predvajajo neposredno na vsako celico članico, kar je tudi način, kako se koren preliva pojavi kot eno vozlišče v grafu odvisnosti inkrementalnega preračunavanja. To je pot, ki jo uporabite, ko je delo zapisati prelivno regijo nazaj v datoteko in ne le brati iz nje

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 eksplicitnega sidranja je meja, ki jo je vredno odkrito zapisati: HotXLS ne spreminja samodejno velikosti preliva, kot to počne interaktivni Excel. Excel širi in krči prelivno območje, ko se njegovi vhodi spreminjajo, in sproži napako #SPILL!, ko so ciljne celice zasedene. V HotXLS je pravokotnik, ki ga prenesete v SetArrayFormula, pravokotnik, ki ga dobite, zato ga prilagodite rezultatu, ki ga pričakujete, in če funkcija ustvari več vrstic, kot ste jih sidrali, presežek nima mesta, kjer bi pristal

Predvajanje polja po elementih čez operatorje

HotXLS uveljavlja aritmetične operatorje element za elementom, ko je katera koli stran operacije polje. Pomočnik ApplyArrayBinaryOp pokriva +, -, *, / in ^ čez enodimenzionalna in dvodimenzionalna polja variant: skalarni operand se predvaja na vsak element, dve polji se morata ujemati v obliki, sicer se operacija zavrne, deljenje z nič pa se širi kot napaka in ne povzroči sesutja. Skalarna aritmetična pot ostane nedotaknjena, zato se to aktivira le takrat, ko je vsaj en operand resnično polje, kot je prelito območje ali rezultat matrične funkcije. To pomeni, da formula, kot je =D2:D13*1.1, sidrana čez stolpec, pomnoži vsak element po vrsti, =D2:D13*E2:E13 pa pomnoži dva enako oblikovana stolpca od mesta do mesta

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

Kaj počne operator @ nasproti dedovani matrični formuli CSE?

HotXLS bere eksplicitni @ kot operator presečišča sklicev in ne kot implicitno presečišče vrstic v živem Excelu. V motorju A1:A3 @ B1:B3 vrne eno celico, kjer se območji križata, operator pa se veže močneje kot ^ in šibkeje kot %. Spremlja pa ga pošteno opozorilo: implicitna oblika presečišča, ločena s presledkom, še ni tokenizirana, ker leksični analizator še vedno preskakuje presledke, zato dobite presečišče le prek eksplicitnega simbola @

Globja razlika je med dedovano matrično formulo CSE in sodobnim dinamičnim poljem, v HotXLS pa obe potekata skozi isto sidrno kolesje. Klasična matrična formula je bil blok {=...}, vnesen s Ctrl+Shift+Enter čez vnaprej določen izbor, pri čemer si je vsaka celica delila eno prevedeno formulo. Dinamično polje je ena formula, katere rezultat določa njeno lastno obliko. HotXLS ju združuje pod SetArrayFormula: vedno poimenujete pravokotnik, koren ima v lasti prevedeno drevo, člani pa se nanj sklicujejo, naj gre za staro matrično izražanje ali nov SORT. Wat tisto, česar nikoli ne dobite, je Excelovo samodejno valovanje, kjer območje tiho ponovno zraste, ohranjanje te meje v mislih pa preprečuje zameglitev obeh miselnih modelov

Kje se podpora za dinamična polja ustavi

SORT upošteva smer na ključ (privzeto naraščajoče in padajoče, ko je argument vrstnega reda negativen); smer primerjave je mesto, kjer je vredno preizkusiti lastne ključne stolpce glede na Excel, saj obrnjena primerjava tiho vrne vrstice v nasprotnem vrstnem redu in ne spodleti. UNIQUE ohrani prvo pojavitev vsake različne vrstice, kar motor potrdi s pregledovanjem prejšnjih vrstic, namesto da bi zaupal surovemu številu dvojnikov. Funkcija TABLE (podatkovne tabele analize What-If) je prepoznana, tako da opravi pot skozi datoteko, vendar se ovrednoti kot placeholder, ker so dejanski rezultati zamenjave predpomnjene vrednosti, ki jih je Excel že shranil, HotXLS pa ne zažene ponovno mreže what-if

LET je najostrejša omejitev, ki jo je treba izpostaviti. HotXLS registrira LET in prenaša pot vrednotenja zanj, vendar parser zavrne golo ime, ki ni že definirano ime, zato =LET(x;10;x) spodleti med razčlenjevanjem, še preden se doseže vrednotilnik. Obravnavajte LET kot nepodprt, dokler parser ne pridobi zavedanja o dosegu svojih vezanih imen. Manjša opomba o prenosljivosti: nizi formul v teh primerih uporabljajo ločilo seznama motorja, podpičje, zato pri sestavljanju besedila formul uskladite katero koli ločilo, ki ga vaša različica pričakuje. Tukaj zajete funkcije dinamičnih polj, od XLOOKUP in FILTER do SORT, UNIQUE, SEQUENCE in XMATCH, se dobavljajo v komponenti HotXLS Delphi Excel Component, katere referenca formul navaja celoten katalog funkcij in podprte načine argumentov vsake funkcije