HotXLS ora valuta i riferimenti strutturati alle tabelle, quindi =SUM(Table1[Amount]) produce un numero invece di essere saltato. Il resolver gestisce Table[Column], Table[[Column]], span di colonne come Table[[Q1]:[Q4]], e gli item specifier [#Data], [#All], [#Headers] e [#Totals], risolvendo ciascuno rispetto al table model della cartella di lavoro in fase di parsing, mentre il testo originale della formula torna invariato in un round-trip
Una forma è deliberatamente assente, ed è quella che le persone incontrano per prima. La scorciatoia current-row [@Column] non è supportata, per una ragione strutturale che vale la pena capire invece di aggirare alla cieca
Perché un riferimento strutturato non è solo un range con un nome amichevole?
Perché un nome definito congela un indirizzo mentre un riferimento a tabella no. Scrivi DataBlock come nome che punta a Sheet1!$A$2:$D$100 e resta quel rettangolo finché qualcosa non lo riscrive. Scrivi Sales[Amount] e significa «la colonna Amount della tabella Sales», qualunque sia l'estensione di quella tabella nel momento in cui la formula viene valutata. Aggiungi venti righe alla tabella e la somma le copre; non c'è alcun riferimento da aggiustare perché non c'è mai stato un indirizzo nella formula fin dall'inizio
Questa qualità simbolica è esattamente il motivo per cui il riferimento non può essere risolto per sostituzione di stringa. Il resolver deve trovare la tabella per nome nella cartella di lavoro, cercare la colonna in base al testo dell'intestazione, decidere quali righe copre l'item specifier richiesto, e produrre un rettangolo concreto. HotXLS fa questo durante la compilazione della formula tramite il table model, il che è il motivo per cui una formula scritta prima che la tabella cresca viene comunque valutata rispetto all'estensione attuale della tabella
La grammatica che HotXLS risolve
La grammatica supportata copre un singolo risultato rettangolare e vale la pena dichiararla con precisione, perché la documentazione di Excel presenta una superficie molto più ampia di quella che la maggior parte dei motori implementa. HotXLS accetta [Col] e la variante tra parentesi [[Col]], gli item specifier nudi [#Data], [#All], [#Headers] e [#Totals], la forma combinata [[#Data],[Col]], uno span dentro un item specifier come [[#Data],[Col1]:[Col2]], e uno span semplice [Col1]:[Col2]
Ciò che quell'insieme ti dà è ogni forma di riferimento che produce un singolo blocco contiguo: una colonna, una sequenza di colonne adiacenti, una porzione solo body o inclusiva di header di entrambe. Le unioni non adiacenti e i risultati multi-area sono fuori da esso. Quando un riferimento non può essere risolto, la formula mantiene il comportamento precedente skip-without-value invece di sostituire una supposizione, così un riferimento non risolvibile non diventa mai un numero sbagliato plausibile
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cols: TStringList;
begin
Book := TXLSXWorkbook.Create;
Cols := TStringList.Create;
try
Sheet := Book.Sheets.Add('Sales');
Cols.Add('Region');
Cols.Add('Q1');
Cols.Add('Q2');
Cols.Add('Amount');
Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
// ... write the header row and 24 data rows ...
Sheet.Cells[27, 4].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;Perché la forma current-row è esclusa di proposito?
[@Column] e [#This Row] significano «la cella di quella colonna sulla riga dove vive questa formula». Il valore dipende quindi dalla posizione della cella che valuta, non solo dalla tabella. È un tipo di riferimento diverso: non un rettangolo che il compilatore può risolvere una volta sola, ma una risoluzione per singola cella che deve essere rifatta per ogni riga occupata dalla formula
HotXLS restituisce False dal resolver dei table range per quelle forme, il che le instrada nel percorso skip-without-value. Il testo della formula viene preservato e riscritto invariato, così una cartella di lavoro che usa [@Amount] si apre correttamente in Excel dopo un round-trip attraverso la tua applicazione; è assente solo il valore calcolato da HotXLS. Data la scelta tra un valore assente e un valore calcolato rispetto alla riga sbagliata, l'assenza è quella che puoi rilevare
Il workaround pratico è meccanico: in una cartella di lavoro che generi, scrivi l'equivalente riferimento relativo in stile A1, che è comunque ciò che Excel memorizza internamente per gran parte della logica con scope di tabella. In una cartella di lavoro che ti limiti a elaborare, lascia stare la formula e leggi il valore in cache che Excel ha già memorizzato, che è ciò che di solito vuole una pipeline di caricamento e reportistica
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Table: TXLSXTable;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('sales.xlsx') <> 1 then Exit;
Sheet := Book.Sheets[1];
Table := Sheet.Tables.FindByName('SalesTable');
if Table <> nil then
begin
// Lookup in stile recordset sul body della tabella, risultato riga 1-based
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value));
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
end;
finally
Book.Free;
end;
end;Cosa succede quando la tabella cambia forma
I riferimenti strutturati vengono invalidati invece di essere ripuntati in silenzio quando la cosa che nominano scompare. Elimina una colonna e le formule che fanno riferimento a quella colonna vengono invalidate nello stesso modo in cui le invalida Excel; elimina o rinomina la tabella e i riferimenti a essa sono gestiti allo stesso modo. Questo è il comportamento corretto e rispecchia l'aggiustamento ordinario dei riferimenti, descritto in aggiustamento dei riferimenti di formula su inserimento ed eliminazione, dove il compito del motore è mantenere oneste le formule invece di farle solo sembrare valide
La crescita delle righe è il caso opposto e non richiede alcun aggiustamento. Poiché il riferimento nomina la tabella e non un rettangolo, aggiungere righe dentro il range della tabella allarga ciò che [#Data] copre senza toccare una sola formula. Questa è la proprietà che rende utili le tabelle in un template di report: la riga dei totali continua a sommare tutto ciò che l'import ha prodotto, qualunque sia il numero di righe risultato
Disciplina del round-trip
HotXLS mantiene il testo originale della formula. Una cartella di lavoro caricata con SUM(SalesTable[Amount]) viene salvata con SUM(SalesTable[Amount]), non con l'indirizzo risolto SUM(D2:D25). Questo conta più di quanto possa sembrare: un utente che apre il tuo output in Excel si aspetta di vedere la formula che ha scritto, e un indirizzo risolto convertirebbe silenziosamente un modello autosufficiente in uno fragile che smette di coprire le righe nuove
Due funzionalità correlate completano il quadro. Le definizioni di tabella stesse, incluse le tabelle senza intestazione e i commenti per singola tabella, fanno round-trip tramite il table model descritto in validazione dati, AutoFilter e tabelle Excel. E quando molte celle condividono uno stesso pattern, XLSX le memorizza una sola volta come formula condivisa, che viene espansa e riemessa come trattato in espansione si delle formule condivise. I riferimenti strutturati dentro le formule condivise passano attraverso entrambi i percorsi, quindi entrambi devono comportarsi correttamente, e lo fanno
HotXLS legge e scrive XLS, XLSX e ODS da Delphi e C++Builder senza installazione di Excel e senza automazione Office, valutando le formule nel proprio motore. Il table model, il motore di formule e l'API di ricalcolo sono documentati sulla pagina del componente foglio di calcolo Delphi HotXLS