Dynamische matrix-overloopformules in Delphi met HotXLS

HotXLS evalueert Excel 365 dynamische-matrix-overloopformules (spill-formules) systeemeigen in Delphi en C++Builder: u overhandigt het een werkmap waarvan de cellen XLOOKUP of FILTER bevatten, en de formule-engine berekent de resultaatset en laat deze overlopen (spillen) over de verankerde uitvoerrechthoek, met exact dezelfde waarden die Excel zou produceren. De HotXLS Delphi Excel-component modelleert een overloopformule als één basiscel die de berekening bezit plus een blok afhankelijke cellen die daaruit teruglezen, wat het volledige beeld is dat u nodig hebt voordat u een regel code schrijft

Dat model is belangrijk omdat de dagelijkse taak niet abstract is. Een klant stuurt een .xlsx-bestand dat is gebouwd in Excel 365, de bladen staan vol met =FILTER(...) en =XLOOKUP(...), en uw service moet exact dezelfde getallen headless reproduceren, op een machine waarop geen Excel is geïnstalleerd, om vervolgens de overgelopen waarden te lezen of zelf een nieuw overloopgebied te schrijven. Dynamische matrices veranderden het berekeningscontract van "één formule, één cel" naar "één formule, een rechthoek van cellen", en de HotXLS-engine volgt dat contract in plaats van het te simuleren met een vooraf uitgevouwen raster

Hoe evalueert u XLOOKUP en FILTER in Delphi?

HotXLS stuurt elke dynamische-matrixfunctie aan via één centraal toegangspunt voor de evaluator, CalcDynArrayFunc in lxCalc.pas, zodat de hele familie één overloop-codepad deelt. De ondersteunde set bestaat uit XLOOKUP en FILTER (de oorspronkelijke twee), aangevuld met XMATCH, SORT, UNIQUE en SEQUENCE. Elk daarvan retourneert een 2D-variantmatrix in plaats van een scalaire waarde: SEQUENCE(3;2;1;1) levert een 3-bij-2-raster op, SORT rangschikt rijen opnieuw op basis van een sleutelkolom in oplopende of aflopende volgorde, UNIQUE voegt dubbele rijen samen met behoud van de eerste instantie, en XMATCH rapporteert de 1-gebaseerde positie van een waarde onder dezelfde exacte, wildcard-, volgende kleinere en volgende grotere match-modi als XLOOKUP. In tegenstelling tot de scalaire statistische verdelingsfuncties, die per aanroep één getal retourneren, geven deze functies een hele vorm terug

Om een werkmap te berekenen die deze formules al bevat, opent u deze, roept u eenmalig Recalculate aan en leest u de cellen waarin de overloop is geland. TXLSXWorkbook.Recalculate doorloopt de afhankelijkheidsgrafiek en evalueert elke gewijzigde formule in topologische volgorde, zodat een overloopbasiscel slechts eenmalig wordt berekend en de elementen ervan rechtstreeks in de lidcellen worden geschreven

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;

Als de werkmap een functie nodig heeft die de engine niet levert, laadt en evalueert dezelfde evaluator u in staat uw eigen logica in te pluggen via de hook voor aangepaste werkbladfuncties, en een aangepaste functie is vrij om een variantmatrix te retourneren, zodat deze precies zo overloopt als de ingebouwde functies

Het overloopbereik verankeren met SetArrayFormula

HotXLS raadt nooit hoe groot een overloop moet zijn: u geeft de uitvoerrechthoek op, en TXLSRange.SetArrayFormula verankert de formule daaraan. De methode compileert de formule eenmalig, slaat de syntaxisboom op in de cel linksboven (de basiscel) samen met het verankerde bereik, en geeft elke andere cel in de rechthoek een lichtgewicht formule die zwak verwijst naar de basiscel. Bij herberekening evalueert de basiscel eenmalig en worden de matrixelementen ervan rechtstreeks naar elke lidcel verzonden, wat ook de manier is waarop een overloopbasiscel verschijnt als één knooppunt in de afhankelijkheidsgrafiek voor incrementele herberekening. Dit is de te volgen weg wanneer het de taak is om een overloopgebied terug in het bestand te schrijven in plaats van er een uit te lezen

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;

Het gevolg van expliciete verankering is een grens die duidelijk moet worden vermeld: HotXLS past de grootte van een overloop niet automatisch aan zoals het interactieve Excel dat doet. Excel vergroot en verkleint het overgelopen gebied als de invoer verandert en geeft de foutmelding #SPILL! wanneer de doelcellen bezet zijn. In HotXLS is de rechthoek die u doorgeeft aan SetArrayFormula is de rechthoek die u krijgt, dus u stemt de grootte af op het verwachte resultaat, en als een functie meer rijen produceert dan u hebt verankerd, kunnen de overtollige gegevens nergens landen

Elementsgewijze matrix-broadcasting over de operatoren

HotXLS past de rekenkundige operatoren element voor element toe wanneer een van de zijden van de bewerking een matrix is. Het hulpprogramma ApplyArrayBinaryOp dekt +, -, *, / en ^ over 1D- en 2D-variantmatrices: een scalaire operand wordt naar elk element verzonden (broadcast), twee matrices moeten qua vorm overeenkomen anders wordt de bewerking geweigerd, en deling door nul plant zich voort als een fout in plaats van te crashen. Het scalaire rekenkundige pad blijft onaangetast, dus dit wordt alleen geactiveerd wanneer ten minste één operand echt een matrix is, zoals een overgelopen bereik of een resultaat van een matrixfunctie. Dat betekent dat een formule zoals =D2:D13*1.1 verankerd over een kolom elk element om beurten vermenigvuldigt, en =D2:D13*E2:E13 twee kolommen van gelijke vorm positie voor positie vermenigvuldigt

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

Wat doet de @-operator versus een legacy CSE-matrix?

HotXLS leest een expliciete @ als de referentiekruisingsoperator, niet als de rijselecterende impliciete kruising van live Excel. In de engine retourneert A1:A3 @ B1:B3 de enkele cel waar de twee bereiken elkaar kruisen, en de operator bindt sterker dan ^ en losser dan %. Een eerlijke opmerking hierbij: de impliciete, door spaties gescheiden kruisingsvorm is nog niet getokeniseerd, omdat de lexer nog steeds witruimte overslaat, dus u krijgt kruising alleen via het expliciete @-symbool

Het diepere onderscheid ligt tussen een legacy CSE-matrixformule en een moderne dynamische matrix, en in HotXLS stromen beide door dezelfde verankeringsmechanismen. Een klassieke matrixformule was het {=...}-blok dat werd ingevoerd met Ctrl+Shift+Enter over een vooraf bemeten selectie, waarbij elke cel één gecompileerde formule deelde. Een dynamische matrix is een enkele formule waarvan het resultaat zijn eigen vorm bepaalt. HotXLS verenigt ze onder SetArrayFormula: u geeft altijd de rechthoek op, de basiscel bezit de gecompileerde boomstructuur, en de leden verwijzen daarnaar, of de formule nu een matrixexpressie in oude stijl is of een nieuwe SORT. Wat u nooit krijgt, is Excel's automatische aanpassing waarbij het gebied stilletjes opnieuw groeit, en het in gedachten houden van die grens voorkomt dat de twee conceptuele modellen in elkaar overvloeien

Waar de ondersteuning voor dynamische matrices ophoudt

Het kennen van de grenzen bespaart een middag debuggen. SORT respecteert een richting per sleutel, standaard oplopend en aflopend wanneer het sorteerargument negatief is; de vergelijkingsrichting is iets dat de moeite waard is om te testen met uw eigen sleutelkolommen tegenover Excel, aangezien een omgekeerde vergelijking stilletjes rijen in de omgekeerde volgorde retourneert in plaats van te falen. UNIQUE behoudt de eerste instantie van elke unieke rij, wat de engine bevestigt door de eerdere rijen te scannen in plaats van te vertrouwen op een ruwe telling van duplicaten. De functie TABLE (wat-als-analysedatatafels) wordt herkend zodat deze correct door het bestand reist (round-tripped), maar deze evalueert naar een tijdelijke aanduiding (placeholder) omdat de werkelijke substitutieresultaten de gecachte waarden zijn die Excel al heeft opgeslagen, en HotXLS het wat-als-raster niet opnieuw uitvoert

LET is de meest duidelijke limiet om te noemen. HotXLS registreert LET en bevat een evaluatiepad daarvoor, maar the parser weigert een kale naam dat geen reeds gedefinieerde naam is, waardoor =LET(x;10;x) faalt tijdens het parsen nog voordat de evaluator ooit wordt bereikt. Behandel LET als niet-ondersteund totdat de parser scope-bewustzijn krijgt voor zijn gebonden namen. Een kleine opmerking over portabiliteit: de formule-strings in deze voorbeelden gebruiken het lijstscheidingsteken van de engine, een puntkomma, dus zorg dat dit overeenkomt met het scheidingsteken dat uw build verwacht wanneer u de formule-tekst samenstelt. De functies voor dynamische matrices die hier worden behandeld, van XLOOKUP en FILTER tot en met SORT, UNIQUE, SEQUENCE en XMATCH, worden geleverd in de HotXLS Delphi Excel-component, waarvan de formulereferentie de volledige functiecatalogus en de ondersteunde argument-modi van elke functie vermeldt