HotXLS valuta le formule con matrice dinamica (spill) di Excel 365 nativamente in Delphi e C++Builder: gli passi una cartella di lavoro le cui celle contengono XLOOKUP o FILTER, e il suo motore di formule calcola il set di risultati e lo distribuisce (spill) sul rettangolo di output ancorato, fornendo gli stessi valori che Excel produrrebbe. Il componente Excel per Delphi HotXLS modella una formula spill come una singola cella radice proprietaria del calcolo più un blocco di celle dipendenti che leggono da essa, che rappresenta l'intero quadro necessario prima di scrivere una sola riga di codice
Questo modello è importante perché l'attività quotidiana non è astratta. Un cliente invia un file .xlsx creato in Excel 365, i cui fogli sono pieni di =FILTER(...) e =XLOOKUP(...), e il tuo servizio deve riprodurre gli stessi identici numeri in modalità headless, su una macchina senza Excel installato, per poi leggere i valori distribuiti o scrivere una nuova area spill personalizzata. Le matrici dinamiche hanno cambiato il contratto di calcolo da "una formula, una cella" a "una formula, un rettangolo di celle", e il motore di HotXLS segue questo contratto anziché simularlo con una griglia pre-espansa
Come si valutano XLOOKUP e FILTER in Delphi?
HotXLS instrada ogni funzione a matrice dinamica attraverso un unico punto di ingresso del valutatore, CalcDynArrayFunc in lxCalc.pas, in modo che l'intera famiglia condivida lo stesso percorso di codice per lo spill. L'insieme supportato comprende XLOOKUP e FILTER (i due originali), affiancati da XMATCH, SORT, UNIQUE e SEQUENCE. Ciascuna restituisce una matrice variante bidimensionale anziché uno scalare: SEQUENCE(3;2;1;1) produce una griglia 3x2, SORT riordina le righe in base a qualsiasi colonna chiave in ordine crescente o decrescente, UNIQUE comprime le righe duplicate mantenendo la prima occorrenza, e XMATCH segnala la posizione a base 1 di un valore secondo le stesse modalità di corrispondenza esatta, caratteri jolly, elemento successivo più piccolo ed elemento successivo più grande di XLOOKUP. A differenza delle funzioni di distribuzione statistica scalari, che restituiscono un singolo numero per chiamata, queste funzioni restituiscono un'intera forma
Per elaborare una cartella di lavoro che contiene già queste formule, aprila, chiama una volta Recalculate e leggi le celle in cui è atterrato lo spill. TXLSXWorkbook.Recalculate percorre il grafo delle dipendenze e valuta ogni formula modificata in ordine topologico, in modo che la radice dello spill venga calcolata una sola volta e i suoi elementi vengano scritti direttamente nelle celle membro
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
r, c: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('from-customer.xlsx');
Book.Recalculate; // valuta ogni formula, inclusi gli spill
Sheet := Book.Sheets[1]; // Sheets è a base 1
// Rilegge il blocco in cui si è distribuito XLOOKUP / FILTER, ad esempio 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;Se la cartella di lavoro necessita di una funzione non fornita dal motore, lo stesso valutatore consente di inserire la propria logica personalizzata tramite l'hook delle funzioni personalizzate del foglio di lavoro, e una funzione personalizzata è libera di restituire una matrice variante in modo che si distribuisca esattamente come quelle integrate
Ancorare l'intervallo spill con SetArrayFormula
HotXLS non indovina mai quanto deve essere grande uno spill: specifichi il rettangolo di output e TXLSRange.SetArrayFormula vi ancora la formula. Il metodo compila la formula una sola volta, memorizza l'albero sintattico sulla cella radice in alto a sinistra insieme all'intervallo ancorato e assegna a ogni altra cella del rettangolo una formula leggera che fa riferimento debole alla radice. Al ricalcolo, la radice viene valutata una sola volta e i suoi elementi di matrice vengono trasmessi direttamente a ciascuna cella membro, che è anche il modo in cui una radice spill si presenta come un singolo nodo nel grafo delle dipendenze per il ricalcolo incrementale. Questo è il percorso da utilizzare quando il compito consiste nello scrivere un'area spill nel file anziché leggerne una
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('report.xlsx');
Sheet := Book.Sheets[1];
// Ridimensiona il rettangolo in base al risultato: una griglia a 3 righe per 2 colonne.
if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
Book.Recalculate; // riempie A1:B3 con 1..6 lungo le righe
Book.SaveAs('report-out.xlsx');
finally
Book.Free;
end;
end;La conseguenza dell'ancoraggio esplicito è un limite che vale la pena dichiarare chiaramente: HotXLS non ridimensiona automaticamente uno spill come fa Excel interattivo. Excel ingrandisce e rimpicciolisce l'area dello spill al variare dei relativi input e genera l'errore #SPILL! quando le celle di destinazione sono occupate. In HotXLS, il rettangolo passato a SetArrayFormula è esattamente il rettangolo che si ottiene, quindi devi ridimensionarlo in base al risultato previsto, e se una funzione produce più righe di quelle ancorate, l'eccedenza non avrà spazio per essere inserita
Broadcasting di matrici elemento per elemento tra gli operatori
HotXLS applica gli operatori aritmetici elemento per elemento quando uno dei due lati dell'operazione è una matrice. L'helper ApplyArrayBinaryOp copre +, -, *, / e ^ su matrici varianti mono e bidimensionali: un operando scalare viene trasmesso a ogni elemento, le due matrici devono corrispondere nella forma altrimenti l'operazione viene rifiutata, e la divisione per zero si propaga come errore anziché mandare in crash l'applicazione. Il percorso aritmetico scalare rimane inalterato, quindi questo si attiva solo quando almeno un operando è effettivamente una matrice, come un intervallo spill o il risultato di una funzione di matrice. Ciò significa che una formula come =D2:D13*1.1 ancorata su una colonna moltiplica ciascun elemento a sua volta, e =D2:D13*E2:E13 moltiplica due colonne della stessa forma posizione per posizione
// Broadcast scalare: ogni cella dell'ancoraggio ottiene D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Due matrici di forma identica si moltiplicano elemento per elemento.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;Cosa fa l'operatore @ rispetto a una matrice CSE legacy?
HotXLS legge un operatore @ esplicito come operatore di intersezione di riferimento, non come intersezione implicita di selezione delle righe di Excel attivo. Nel motore, A1:A3 @ B1:B3 restituisce la singola cella in cui i due intervalli si incrociano, e l'operatore ha una precedenza maggiore rispetto a ^ e minore rispetto a %. Una nota di onestà: la forma di intersezione implicita separata da spazi non è ancora tokenizzata, poiché il lexer salta comunque gli spazi vuoti, quindi si ottiene l'intersezione solo tramite il simbolo @ esplicito
La distinzione più profonda è tra una formula di matrice CSE legacy e una moderna matrice dinamica, e in HotXLS entrambe fluiscono attraverso lo stesso meccanismo di ancoraggio. Una classica formula di matrice era il blocco {=...} inserito con Ctrl+Shift+Invio su una selezione pre-dimensionata, in cui ogni cella condivideva una formula compilata. Una matrice dinamica è una singola formula il cui risultato determina la propria forma. HotXLS le unifica sotto SetArrayFormula: indichi sempre il rettangolo, la radice possiede l'albero compilato e i membri fanno riferimento ad essa, sia che la formula sia un'espressione di matrice vecchio stile o un nuovo SORT. Ciò che non si ottiene mai è la propagazione automatica di Excel in cui l'area si ridimensiona silenziosamente, e tenere a mente questo limite evita di confondere i due modelli mentali
Dove si ferma il supporto alle matrici dinamiche
Conoscere i limiti evita un pomeriggio di debug. SORT rispetta la direzione per chiave, crescente per impostazione predefinita e decrescente quando l'argomento dell'ordine è negativo; la direzione di confronto merita di essere testata sulle tue colonne chiave rispetto a Excel, poiché un confronto invertito restituisce silenziosamente le righe in ordine opposto anziché fallire. UNIQUE mantiene la prima occorrenza di ciascuna riga distinta, operazione confermata dal motore tramite la scansione delle righe precedenti anziché affidarsi a un conteggio grezzo dei duplicati. La funzione TABLE (tabelle dati dell'analisi di simulazione) viene riconosciuta in modo da completare il ciclo nel file, ma restituisce un segnaposto poiché i risultati effettivi della sostituzione sono i valori memorizzati nella cache che Excel ha già salvato, e HotXLS non esegue nuovamente la griglia di simulazione
LET è il limite più netto da segnalare. HotXLS registra LET e include un percorso di valutazione ad esso dedicato, ma il parser rifiuta un nome semplice che non sia già definito, quindi =LET(x;10;x) fallisce durante l'analisi prima ancora che venga raggiunto il valutatore. Considera LET como non supportato fino a quando il parser non otterrà la consapevolezza dell'ambito per i suoi nomi associati. Una nota minore sulla portabilità: le stringhe delle formule in questi esempi utilizzano il separatore di elenchi del motore, il punto e virgola, quindi adegua il separatore a quello previsto dalla tua compilazione quando assembli il testo della formula. Le funzioni a matrice dinamica qui trattate, da XLOOKUP e FILTER a SORT, UNIQUE, SEQUENCE e XMATCH, sono incluse in HotXLS Delphi Excel Component, il cui riferimento delle formule elenca il catalogo completo delle funzioni e le modalità degli argomenti supportate per ciascuna di esse