HotXLS wertet Excel 365 dynamische Array-Spill-Formeln nativ in Delphi und C++Builder aus: Sie übergeben der Komponente eine Arbeitsmappe, deren Zellen XLOOKUP oder FILTER enthalten, und ihre Berechnungs-Engine ermittelt die Ergebnismenge und verteilt (spillt) sie über das verankerte Ausgabe-Rechteck — genau die Werte, die Excel auch erzeugen würde. Die Excel-Komponente HotXLS für Delphi modelliert eine Spill-Formel als eine Wurzelzelle, die die Berechnung besitzt, plus einen Block abhängiger Zellen, die daraus lesen, was das gesamte Bild darstellt, das Sie vor dem Schreiben einer Codezeile benötigen
Dieses Modell ist wichtig, weil die alltägliche Aufgabe nicht abstrakt ist. Ein Kunde sendet eine in Excel 365 erstellte .xlsx-Datei, deren Blätter voller =FILTER(...) und =XLOOKUP(...) sind. Ihr Dienst muss dieselben Zahlen headless auf einem System ohne installiertes Excel reproduzieren und anschließend entweder die verteilten Werte lesen oder einen eigenen neuen Spill-Bereich schreiben. Dynamische Arrays änderten den Berechnungsvertrag von „eine Formel, eine Zelle“ zu „eine Formel, ein Rechteck aus Zellen“, und die HotXLS-Engine folgt diesem Vertrag, statt ihn mit einem im Voraus erweiterten Gitter zu simulieren
Wie wertet man XLOOKUP und FILTER in Delphi aus?
HotXLS leitet jede dynamische Array-Funktion über einen einzigen Einstiegspunkt des Auswerters weiter, CalcDynArrayFunc in lxCalc.pas, sodass die gesamte Funktionsfamilie denselben Spill-Pfad teilt. Die unterstützte Menge umfasst XLOOKUP und FILTER (die ursprünglichen zwei), ergänzt durch XMATCH, SORT, UNIQUE und SEQUENCE. Jede dieser Funktionen gibt anstelle eines Skalars ein 2D-Variant-Array zurück: SEQUENCE(3;2;1;1) liefert ein 3x2-Gitter, SORT sortiert Zeilen nach einer beliebigen Schlüsselspalte auf- oder absteigend, UNIQUE filtert doppelte Zeilen heraus und behält das erste Vorkommen bei, und XMATCH meldet die einsbasierte Position eines Wertes unter denselben genauen, Platzhalter-, nächstkleineren und nächstgrößeren Vergleichsmodi wie XLOOKUP. Im Gegensatz zu den skalaren statistischen Verteilungsfunktionen, die pro Aufruf eine Zahl zurückgeben, liefern diese Funktionen ein ganzes Datenfeld zurück
Um eine Arbeitsmappe zu berechnen, die bereits solche Formeln enthält, öffnen Sie diese, rufen Sie einmal Recalculate auf und lesen Sie die Zellen aus, in denen der Spill gelandet ist. TXLSXWorkbook.Recalculate durchläuft den Abhängigkeitsgraphen und wertet jede geänderte Formel in topologischer Reihenfolge aus, sodass eine Spill-Wurzel nur ein einziges Mal berechnet wird und ihre Elemente direkt in die Mitgliedszellen geschrieben werden
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;Wenn die Arbeitsmappe eine Funktion benötigt, die die Engine nicht mitliefert, können Sie über die Schnittstelle für benutzerdefinierte Tabellenfunktionen Ihre eigene Logik einbinden. Eine benutzerdefinierte Funktion kann problemlos ein Variant-Array zurückgeben, sodass sie sich genau wie die integrierten Funktionen verhält und den Spill ausführt
Verankern des Spill-Bereichs mit SetArrayFormula
HotXLS rät niemals, wie groß ein Spill sein sollte: Sie benennen das Ausgabe-Rechteck, und TXLSRange.SetArrayFormula verankert die Formel darauf. Die Methode kompiliert die Formel einmal, speichert den Syntaxbaum auf der Zelle oben links (der Wurzelzelle) zusammen mit dem verankerten Bereich und weist jeder anderen Zelle im Rechteck eine einfache Formel zu, die schwach auf die Wurzel verweist. Bei der Neuberechnung wertet die Wurzelzelle die Formel einmal aus, und ihre Matrixelemente werden direkt an jede Mitgliedszelle übertragen. So wird eine Spill-Wurzel auch als ein einzelner Knoten im Abhängigkeitsgraphen der inkrementellen Neuberechnung dargestellt. Dies ist der Pfad, den Sie nutzen, wenn die Aufgabe darin besteht, einen Spill-Bereich zurück in die Datei zu schreiben statt einen auszulesen
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;Die Folge der expliziten Verankerung ist eine Grenze, die man deutlich nennen sollte: HotXLS passt die Größe eines Spills nicht automatisch an, wie es das interaktive Excel tut. Excel vergrößert und verkleinert den Spill-Bereich, wenn sich seine Eingaben ändern, und gibt einen #SPILL!-Fehler aus, wenn die Zielzellen belegt sind. In HotXLS ist das Rechteck, das Sie an SetArrayFormula übergeben, das Rechteck, das Sie erhalten. Sie müssen es also auf das von Ihnen erwartete Ergebnis zuschneiden. Wenn eine Funktion mehr Zeilen erzeugt als Sie verankert haben, kann der Überschuss nirgendwo landen
Elementweise Array-Verteilung über die Operatoren
HotXLS wendet die arithmetischen Operatoren elementweise an, wenn eine der beiden Seiten der Operation ein Array ist. Die Hilfsfunktion ApplyArrayBinaryOp deckt die Operationen +, -, *, / und ^ über 1D- und 2D-Variant-Arrays ab: Ein skalarer Operand wird auf jedes Element verteilt, zwei Arrays müssen in der Form übereinstimmen (andernfalls wird die Operation abgelehnt), und eine Division durch null wird als Fehler weitergegeben, statt einen Absturz zu verursachen. Der skalare Arithmetikpfad bleibt unberührt, sodass diese Logik nur aktiviert wird, wenn mindestens ein Operand tatsächlich ein Array ist, wie ein Spill-Bereich oder ein Matrix-Funktionsergebnis. Das bedeutet, dass eine Formel wie =D2:D13*1.1, die über eine Spalte verankert ist, jedes Element nacheinander multipliziert, und =D2:D13*E2:E13 zwei gleich geformte Spalten Position für Position multipliziert
// 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;Was bewirkt der @-Operator im Vergleich zu einer klassischen CSE-Arrayformel?
HotXLS liest ein explizites @-Zeichen als Schnittpunkt-Operator (Reference Intersection) und nicht als zeilenwählenden impliziten Schnittpunkt von Excel. In der Engine gibt A1:A3 @ B1:B3 die einzelne Zelle zurück, an der sich die beiden Bereiche kreuzen, und der Operator bindet stärker als ^ und schwächer als %. Eine wichtige Einschränkung: Die implizite, durch Leerzeichen getrennte Schnittpunktform wird noch nicht in Token zerlegt, da der Lexer Leerzeichen überspringt. Den Schnittpunkt erhalten Sie also nur über das explizite @-Symbol
Der tiefer liegende Unterschied besteht zwischen einer klassischen CSE-Arrayformel (Ctrl+Shift+Enter) und einem modernen dynamischen Array. In HotXLS fließen beide durch dieselbe Verankerungsmaschinerie. Eine klassische Arrayformel war der {=...}-Block, der über eine vordefinierte Auswahl eingegeben wurde, wobei sich jede Zelle eine einzige kompilierte Formel teilte. Ein dynamisches Array ist eine einzelne Formel, deren Ergebnis ihre eigene Form bestimmt. HotXLS vereinheitlicht sie unter SetArrayFormula: Sie benennen immer das Rechteck, die Wurzelzelle besitzt den kompilierten Baum und die Mitglieder verweisen darauf, egal ob es sich um einen alten Matrixausdruck oder ein neues SORT handelt. Was Sie jedoch niemals erhalten, ist die automatische Anpassung von Excel, bei der sich der Bereich im Stillen vergrößert oder verkleinert. Wenn man sich diese Grenze vor Augen hält, verschwimmen die beiden Konzepte nicht
Wo die Unterstützung für dynamische Arrays aufhört
Die Grenzen zu kennen, spart viel Zeit beim Debuggen. SORT berücksichtigt eine Sortierrichtung pro Schlüsselspalte (standardmäßig aufsteigend und absteigend, wenn das Argument für die Reihenfolge negativ ist). Die Sortierrichtung ist ein Bereich, den Sie sorgfältig gegen Excel testen sollten, da ein umgekehrter Vergleich Zeilen im Stillen in umgekehrter Reihenfolge zurückgibt, statt einen Fehler auszugeben. UNIQUE behält das erste Vorkommen jeder eindeutigen Zeile bei, was die Engine überprüft, indem sie die vorherigen Zeilen scannt, statt sich auf eine einfache Duplikatzählung zu verlassen. Die Funktion TABLE (Mehrfachoperationen für Was-wäre-wenn-Analysen) wird erkannt, sodass sie in der Datei erhalten bleibt, sie wird jedoch als Platzhalter ausgewertet, da die tatsächlichen Substitutionsergebnisse die von Excel bereits gespeicherten Cache-Werte sind und HotXLS das Was-wäre-wenn-Gitter nicht neu berechnet
Die restriktivste Einschränkung betrifft LET. HotXLS registriert LET und verfügt über einen Auswertungspfad dafür, der Parser lehnt jedoch einen einfachen Variablennamen ab, der kein bereits definierter Name ist, sodass =LET(x;10;x) bereits beim Parsen fehlschlägt, bevor der Auswerter überhaupt erreicht wird. Betrachten Sie LET als nicht unterstützt, bis der Parser eine Bereichserkennung (Scope Awareness) für seine gebundenen Namen erhält. Ein kleiner Hinweis zur Portabilität: Die Formel-Zeichenketten in diesen Beispielen verwenden das Listentrennzeichen der Engine (ein Semikolon). Passen Sie es also an das Trennzeichen an, das Ihr Build erwartet, wenn Sie Formeltexte zusammensetzen. Die hier behandelten dynamischen Array-Funktionen von XLOOKUP und FILTER über SORT, UNIQUE, SEQUENCE bis hin zu XMATCH werden in der Excel-Komponente HotXLS für Delphi bereitgestellt, deren Formelreferenz den vollständigen Funktionskatalog und die unterstützten Argumentmodi auflistet