Dynamiske matrixspildformler i Delphi med HotXLS

HotXLS evaluerer Excel 365 dynamiske matrixspildformler indfødt i Delphi og C++Builder: Du giver den en projektmappe, hvis celler indeholder XLOOKUP eller FILTER, og dens formelmotor beregner resultatsættet og spilder det ud over det forankrede outputrektangel, de samme værdier som Excel ville producere. HotXLS Delphi Excel-komponenten modellerer en spildformel som én rodcelle, der ejer beregningen, plus en blok af afhængige celler, der læser tilbage fra den, hvilket er hele det billede, du har brug for, før du skriver en enkelt linje kode

Denne model betyder noget, fordi den daglige opgave ikke er abstrakt. En kunde sender en .xlsx oprettet i Excel 365, dens ark er fulde af =FILTER(...) og =XLOOKUP(...), og din tjeneste skal gengive de nøjagtigt samme tal headless, på en maskine uden Excel installeret, og derefter enten læse de spildte værdier eller skrive et nyt spildområde selv. Dynamiske matricer ændrede beregningskontrakten fra "én formel, én celle" to "én formel, et rektangel af celler", og HotXLS-motoren følger denne kontrakt frem for at efterligne den med et forhåndsudvidet gitter

Hvordan evaluerer du XLOOKUP og FILTER i Delphi?

HotXLS sender enhver dynamisk matrixfunktion gennem et enkelt evaluator-indgangspunkt, CalcDynArrayFunc i lxCalc.pas, så hele familien deler én spildkodevej. Det understøttede sæt er XLOOKUP og FILTER (de oprindelige to), ledsaget af XMATCH, SORT, UNIQUE og SEQUENCE. Hver returnerer et 2-D variant-array frem for en skalar: SEQUENCE(3;2;1;1) giver et 3-by-2 gitter, SORT sorterer rækker efter en hvilken som helst nøglekolonne stigende eller faldende, UNIQUE sammenslår duplikerede rækker under bevarelse af den første forekomst, og XMATCH rapporterer den 1-baserede position af en værdi under de samme præcise, jokertegn-, næste-mindre- og næste-større-matchtilstande som XLOOKUP. I modsætning til de skalære statistiske distributionsfunktioner, som returnerer ét tal pr. kald, leverer disse funktioner en hel form tilbage

For at beregne en projektmappe, der allerede indeholder disse formler, skal du åbne den, kalde Recalculate én gang og læse de celler, spildet landede i. TXLSXWorkbook.Recalculate gennemgår afhængighedsgrafen og evaluerer hver snavset formel i topologisk rækkefølge, så en spildrod beregnes en enkelt gang, og dens elementer skrives direkte ind i medlems-cellerne

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;

Hvis projektmappen har brug for en funktion, som motoren ikke leverer, lader den samme evaluator dig tilslutte din egen logik via krogen til brugerdefinerede regnearksfunktioner, og en brugerdefineret funktion kan frit returnere et variant-array, så det spilder præcis som de indbyggede

Forankring af spildområdet med SetArrayFormula

HotXLS gætter aldrig på, hvor stort et spild skal være: Du navngiver outputrektanglet, og TXLSRange.SetArrayFormula forankrer formlen til det. Metoden kompilerer formlen én gang, gemmer syntakstræet på den øverste venstre rodcelle sammen med det forankrede område, og giver enhver anden celle i rektanglet en letvægtsformel, der svagt refererer til roden. Ved genberegning evaluerer roden én gang, og dens matrixelementer udsendes direkte til hver medlems-celle, hvilket også er, hvordan en spildrod optræder som én node i afhængighedsgrafen for trinvis genberegning. Dette er den sti, du skal bruge, når opgaven er at skrive et spildområde tilbage i filen frem for at læse et ud

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;

Konsekvensen af eksplicit forankring er en grænse, der er værd at sige tydeligt: HotXLS ændrer ikke automatisk størrelse på et spild, som interaktivt Excel gør. Excel udvider og krymper det spildte område, når dets input ændres, og rejser #SPILL!, når målcellerne er optaget. I HotXLS er det rektangel, du sender til SetArrayFormula, det rektangel, du får, så du skal tilpasse dets størrelse til det resultat, du forventer, og hvis en funktion producerer flere rækker, end du har forankret, er der intet sted for overskuddet at lande

Elementvis matrix-broadcasting på tværs af operatorerne

HotXLS anvender de aritmetiske operatorer element for element, når en af siderne i operationen er en matrix. Hjælperen ApplyArrayBinaryOp dækker +, -, *, / og ^ over 1-D og 2-D variant-matricer: En skalær operand udsendes til hvert element, to matricer skal matche i form, ellers afvises operationen, og division med nul udbredes som en fejl i stedet for at crashe. Den skalære aritmetiske sti er uberørt, så dette aktiveres kun, når mindst én operand reelt er en matrix, såsom et spildt område eller et matrixfunktionsresultat. Det betyder, at en formel som =D2:D13*1.1 forankret over en kolonne multiplicerer hvert element efter tur, og =D2:D13*E2:E13 multiplicerer to ensartede kolonner position for position

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

Hvad gør @-operatoren i forhold til en ældre CSE-matrix?

HotXLS læser et eksplicit @ as reference-skæringspunktoperatoren, not as den rækkevælgende implicitte skæring i live Excel. I motoren returnerer A1:A3 @ B1:B3 den enkelte celle, hvor de to områder krydser hinanden, og operatoren binder stærkere end ^ og svagere end %. En ærlig note følger med det: Den implicitte rum-separerede skæringsform er ikke tokeniseret endnu, fordi lexeren stadig springer hvide felter over, så du får kun skæring via det eksplicitte @-symbol

Den dybere skelnen er mellem en ældre CSE-matrixformel og en moderne dynamisk matrix, og i HotXLS flyder begge gennem det samme forankringsmaskineri. En klassisk matrixformel var {=...}-blokken indtastet med Ctrl+Shift+Enter over et forhåndsdefineret valg, hvor hver celle delte én kompileret formel. En dynamisk matrix er en enkelt formel, hvis resultat bestemmer dens egen form. HotXLS forener dem under SetArrayFormula: Du navngiver altid rektanglet, roden ejer det kompilerede træ, og medlemmerne refererer til det, uanset om formlen er et matrixudtryk af den gamle skole eller en ny SORT. Hvad du aldrig får, er Excels automatiske ringvirkning, hvor området lydløst vokser igen, og ved at holde fast i den linje forhindrer du de to mentale modeller i at flyde sammen

Hvor den dynamiske matrixunderstøttelse ophører

At kende kanterne sparer en eftermiddag med fejlfinding. SORT respekterer en retning pr. nøgle, som standard stigende og faldende, når rækkefølge-argumentet er negativt; sammenligningsretningen er et sted, der er værd at teste dine egne nøglekolonner mod Excel, da en omvendt sammenligning lydløst returnerer rækker i den modsatte rækkefølge i stedet for at fejle. UNIQUE beholder den første forekomst af hver særskilt række, hvilket motoren bekræfter by scanning the earlier rows rather than trusting a raw duplicate count. TABLE-funktionen (hvad-hvis-analysedatatabeller) genkendes, så den kører tur-retur gennem filen, men den evalueres til en pladsholder, fordi de reelle substitutionsresultater er de cachede værdier, som Excel allerede har gemt, og HotXLS genkører ikke hvad-hvis-gitteret

LET er den skarpeste grænse at fremhæve. HotXLS registrerer LET og har en evalueringssti til den, men parseren afviser et råt navn, der ikke er et i forvejen defineret navn, så =LET(x;10;x) fejler under fortolkning, før evaluatoren overhovedet nås. Behandl LET som ikke-understøttet, indtil parseren opnår rækkeviddebevidsthed for sine bundne navne. En mindre note om portabilitet: Formelstrengene i disse eksempler bruger motorens listeseparator, et semikolon, så match den separator, dit build forventer, når du samler formeltekst. De dynamiske matrixfunktioner, der er dækket her, fra XLOOKUP og FILTER via SORT, UNIQUE, SEQUENCE og XMATCH, leveres i HotXLS Delphi Excel Component, hvis formelreference viser det fulde funktionskatalog og hver funktions understøttede argumenttilstande