Una tabella Excel la cui colonna Amount tiene =[@Qty]*[@Price] non memorizza [@Qty] da nessuna parte nel file salvato. Scomponete il workbook e l'elemento <f> nell'XML del foglio mostra ciò che Excel scrive davvero: la lunga grafia qualificata alla tabella Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]], perché la grammatica delle formule in ECMA-376 Part 1, §18.17 ha una parola chiave #This Row e nessun @. Una cella di riepilogo più in basso tiene SUM(Lines[Amount]). Nessuna delle due stringhe contiene un solo indirizzo di cella, quindi un motore che analizza solo riferimenti A1 non ha nulla da calcolare finché non sa dove sta la tabella, quale riga porta le intestazioni e su quale riga vive la formula stessa
Quell'ultimo pezzo di informazione è quello scomodo. Un riferimento di colonna può essere trasformato in un rettangolo una volta per tabella. Un riferimento di riga corrente no; significa una cella diversa per ogni riga in cui viene copiato
Perché un riferimento strutturato non è solo un range con un nome amichevole?
Un defined name congela un indirizzo e un riferimento a tabella no. Puntate il nome DataBlock su Sheet1!$A$2:$D$100 e resta quel rettangolo finché qualcosa non lo riscrive. Sales[Amount] significa "la colonna Amount della tabella Sales", qualunque sia l'estensione della tabella al momento in cui la formula viene valutata. Aggiungete venti righe alla tabella e la somma le copre; non c'è riferimento da aggiustare perché la formula non ha mai tenuto un indirizzo in primo luogo
HotXLS risolve questi riferimenti ogni volta che compila una formula per la valutazione. TXLSXWorkbook cerca la tabella per nome nel modello di tabelle del workbook (TXLSXTable.Name o DisplayName, confrontati case-insensitive), abbina la colonna al testo dell'intestazione memorizzato in TXLSXTable.Columns, applica l'item specifier per scegliere la banda di righe, e consegna al compilatore un intervallo assoluto come 'Sales'!$D$2:$D$25. Il testo riscritto esiste solo per quella compilazione. La cella conserva la formula esattamente com'è stata scritta, e poiché la ricerca gira contro TXLSXTable.Range così com'è al momento del ricalcolo, una formula scritta prima che la tabella crescesse valuta comunque contro la tabella più grande
La grammatica del rettangolo: colonne, span e item specifier
Ogni forma a livello di tabella che HotXLS valuta produce un blocco contiguo. Le grafie accettate sono [Col] e la sua gemella 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]]. Uno span può essere scritto da destra a sinistra; [[Q2]:[Q1]] si risolve nelle stesse due colonne di [[Q1]:[Q2]]
La banda di righe che ogni specifier seleziona segue due flag sulla tabella. TXLSXTable.HasHeaderRow decide se la prima riga di Range è un'intestazione, e TXLSXTable.TotalsRowShown decide se l'ultima riga è una riga dei totali. Un riferimento di colonna semplice e [#Data] coprono le righe tra quelle due. [#All] prende l'intero Range, intestazione e totali inclusi. Chiedete [#Headers] su una tabella senza riga di intestazione, [#Totals] su una tabella senza riga dei totali, o una colonna di una tabella il cui corpo non ha righe, e il riferimento diventa #REF!. Questo corrisponde a ciò che Excel mostra per la stessa richiesta, e impedisce a un riferimento impossibile di trasformarsi in silenzio in un intervallo vuoto che somma a zero
I nomi di colonna che contengono parentesi quadre, un cancelletto, una chiocciola o un apostrofo seguono la regola di escape di Excel: ciascun carattere di questo tipo è prefissato da un apostrofo dentro il riferimento, quindi un'intestazione chiamata Unit [USD] viene scritta Sales[Unit '[USD']]. Le forme a colonna singola e a riga corrente decodificano quegli escape prima di abbinare. Gli span e le forme combinate [#Data],[Col] si risolvono contro le tabelle del foglio stesso della formula, quindi un foglio di riepilogo che vuole uno span sulla tabella di un altro foglio sta meglio con un riferimento di colonna semplice per colonna
L'esempio sotto costruisce una tabella vendite di 24 righe e colloca cinque formule di riepilogo accanto, in F2 a F6, sullo stesso foglio della tabella. La riga 1 è la riga di intestazione, le righe da 2 a 25 sono il corpo, e non c'è riga dei totali, quindi SalesTable[#All] copre 25 righe mentre SalesTable[Amount] ne copre 24
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);
// ... scrivete la riga di intestazione e le 24 righe di dati ...
Sheet.Cells[2, 6].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
Valori attesi per quel layout: F5 è 25 e F6 è 4, un conteggio per cella di intestazione. F2 a F4 dipendono dai dati che scrivete
Come sa [@Column] quale riga leggere?
Un riferimento di riga corrente prende la sua riga dalla cella che viene valutata. Quando Recalculate raggiunge una formula contenente Lines[@Qty] nella riga 7, HotXLS la risolve alla colonna Qty della riga 7; lo stesso testo di formula nella riga 8 legge la riga 8. La lunga grafia che Excel scrive su disco, Lines[[#This Row],[Qty]], viene trattata come lo stesso riferimento, e così Lines[@[Unit Price]] per un'intestazione che contiene uno spazio
La forma non qualificata [@Qty], senza nome di tabella davanti, vuole un fatto in più: a quale tabella appartiene. HotXLS risponde per posizione. Se la cella in valutazione sta dentro le righe del corpo e lo span di colonne di una tabella sullo stesso foglio, quella tabella fornisce la colonna, e dentro una simile tabella [@[Q1]:[Q2]] diventa un intervallo di una riga attraverso quelle colonne. Ovunque altro il riferimento resta non risolto. Un defined name la cui formula usa un riferimento di riga corrente prende la sua riga dalla cella che richiama il nome, che è lo stesso comportamento che una formula avrebbe inline
Tenete le formule a riga corrente sulle righe del corpo della tabella stessa, e qualificatele con il nome della tabella quando le genera il codice. I riferimenti qualificati sopravvivono allo spostamento, si leggono nello stesso modo in cui Excel li memorizza, e una formula sulle righe della tabella stessa è l'unico caso in cui Excel e qualunque altro consumer del file concordano su cosa significa @
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cols: TStringList;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
Cols := TStringList.Create;
try
Sheet := Book.Sheets.Add('Orders');
Cols.Add('Item');
Cols.Add('Qty');
Cols.Add('Price');
Cols.Add('Amount');
Sheet.Tables.Add('Lines', 'A1:D13', Cols);
// ... scrivete la riga di intestazione e articoli, quantità, prezzi nelle righe 2..13 ...
// Un testo di formula, dodici risultati diversi: ogni cella legge la propria riga
for Row := 2 to 13 do
Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';
// Controllo incrociato sotto la tabella: totale di colonna contro un prodotto ricalcolato
Sheet.Cells[15, 4].Formula :=
'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';
Book.Recalculate;
// Atteso: D15 valuta a 0 quando ogni cella Amount corrisponde a Qty * Price
Book.SaveAs('orders.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
Il controllo in D15 costa poco e vale la pena lasciarlo in un report generato mentre viene costruito. Una formula per riga che avesse preso la riga sbagliata farebbe discordare i due totali, e la differenza compare come numero diverso da zero in una cella che chiunque può leggere
Cosa succede quando la tabella cambia forma
La crescita è il caso facile, purché la definizione della tabella cresca davvero. Scrivere un valore nella riga 26 sotto una tabella il cui Range è A1:D25 non rende la riga 26 parte della tabella. L'auto-espansione è una feature di editing di Excel innescata da qualcuno che digita accanto alla tabella, e scrivere celle attraverso HotXLS non la innesca. Impostate TXLSXTable.Range alla nuova estensione dopo un import, o inserite le righe dentro la tabella con TXLSXWorksheet.InsertRows, che sposta Tables[i].Range insieme a ogni altro riferimento sul foglio. In entrambi i casi [#Data] si allarga e non una formula cambia
Il restringimento è dove i riferimenti possono rompersi, e HotXLS segue la regola di Excel. DeleteCols su una colonna di tabella riscrive ogni riferimento strutturato che nomina quella colonna in #REF!, nelle formule delle celle, nei formati condizionali, nelle regole di convalida dati, nelle serie dei grafici e nei defined names. Un DeleteRows che rimuove la riga di intestazione elimina la tabella e invalida i suoi riferimenti allo stesso modo, e uno che rimuove la riga dei totali cancella TotalsRowShown, dopodiché [#Totals] non ha più una riga a cui puntare. I riferimenti A1 ordinari passano per lo stesso aggiustamento, descritto in aggiustamento dei riferimenti delle formule su inserimento ed eliminazione
TXLSXTables.DeleteByName e la rinomina tramite TXLSXTable.Name lavorano solo sul modello di tabelle. Le formule che menzionano il vecchio nome conservano il loro testo, quindi rinominate quelle formule nello stesso passaggio quando il vostro codice rinomina una tabella
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]; // i fogli del workbook sono 1-based
Table := Sheet.Tables.FindByName('SalesTable');
if Table = nil then Exit;
// ... scrivete 20 righe importate in A26:D45 ...
Table.Range := 'A1:D45'; // il corpo ora va dalla riga 2 alla riga 45
Book.Recalculate; // SUM(SalesTable[Amount]) in F2 ora copre 44 righe
// Ricerca in stile recordset sul corpo della tabella, risultato riga 1-based, 0 = nessuna
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value)); // Log è una routine vostra
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
Book.SaveAs('sales.xlsx');
finally
Book.Free;
end;
end;
FindFirst e FindNext confrontano le stringhe case-insensitive e i numeri per valore, e scandiscono solo le righe del corpo, quindi il testo dell'intestazione Region non viene mai restituito come corrispondenza
Disciplina del round-trip
Un workbook caricato con SUM(SalesTable[Amount]) viene salvato con SUM(SalesTable[Amount]), e una cella che tiene Lines[[#This Row],[Qty]] torna fuori con quel testo intatto. L'indirizzo risolto non sostituisce mai ciò che l'utente ha scritto. Se HotXLS salvasse SUM($D$2:$D$25) invece, il file si aprirebbe in Excel sembrando a posto e poi smetterebbe di coprire le nuove righe la prima volta che qualcuno estende la tabella, che è il tipo di rottura più lento da far risalire alla causa
Le definizioni delle tabelle stesse, incluse le tabelle senza intestazione e i commenti per tabella, fanno round-trip attraverso il modello di tabelle trattato in convalida dati, AutoFilter e tabelle Excel. Quando molte celle condividono un pattern di formula, XLSX può memorizzarlo una volta come shared formula, e l'espansione di quelle celle è trattata in espansione si delle shared formula. Una colonna calcolata salvata da Excel arriva spesso così, quindi un riferimento strutturato in un corpo di tabella passa per entrambi i percorsi prima di essere valutato: la shared formula si espande prima in testo per cella, poi ogni cella risolve la propria riga
Il modello di tabelle, il motore Recalculate e le chiamate di inserimento ed eliminazione del foglio usate qui fanno parte di HotXLS, che legge e scrive XLS, XLSX e ODS da Delphi e C++Builder senza installazione di Excel. Il riferimento API completo sta sulla pagina del componente foglio di calcolo Delphi HotXLS