Un follower di formula condivisa in XLSX non porta alcun testo di formula. Il suo elemento <f t="shared" si="N"/> punta a una cella master altrove nel foglio, e il lettore deve ricostruire il testo spostando la formula master della differenza di riga e colonna. HotXLS Component per Delphi e C++Builder esegue quella espansione al momento dell'apertura, così ogni follower riporta una formula completa
Se hai mai caricato un XLSX reale in una libreria di terze parti e hai trovato che una colonna di mille formule ha testo in esattamente una cella e stringhe vuote nelle altre 999, hai incontrato questa funzionalità dal lato sbagliato. Nulla è corrotto. Il file sta facendo ciò che ECMA-376 gli permette di fare, e il lettore si è semplicemente fermato nel punto in cui si è fermato l'XML
Perché la cella della formula condivisa è vuota?
Perché il formato memorizza deliberatamente la formula una sola volta. In ECMA-376 Part 1 e ISO/IEC 29500-1, l'elemento <f> (§18.3.1.40) porta un attributo t di tipo ST_CellFormulaType, e il valore shared significa che questa cella partecipa a un gruppo identificato dall'attributo si. Esattamente una cella nel gruppo, il master, porta anche un attributo ref che dà l'intervallo a cui si applica il gruppo, e solo quella cella porta il testo della formula come contenuto dell'elemento. Ogni altra cella nel gruppo è un follower. Ripete t="shared" e lo stesso si, e il suo contenuto dell'elemento è vuoto. Excel scrive questi gruppi in modo aggressivo, perché un fill-down su una colonna di 200.000 righe collassa da 200.000 stringhe di formula a una stringa più 199.999 minuscoli elementi segnaposto. Il risparmio è reale e il costo ricade interamente sul lettore: senza espansione, il follower non ha alcun significato da solo
Lo spostamento è una traduzione, non una copia di testo
HotXLS risolve un follower localizzando il master registrato sotto lo stesso si, calcolando il delta di riga e colonna dall'ancora del master alla cella corrente, e traducendo ogni riferimento nella formula master di quel delta. Le dimensioni relative si spostano, quelle assolute no, e i riferimenti misti spostano solo la loro metà non assoluta. I letterali stringa vengono saltati interamente, così una formula che capita contenga il testo "A1" mantiene quel testo invariato in ogni follower
const
// xl/worksheets/sheet1.xml, trimmed to the interesting cells
SheetXml: WideString=
'<row r="1"><c r="A1"><v>1</v></c>'+
'<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
'A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)</f><v>7</v></c></row>'+
'<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
'<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';
var
Wb: TXLSXWorkbook;
Sh: TXLSXWorksheet;
begin
Wb:= TXLSXWorkbook.Create;
try
Wb.Open(FileName);
Sh:= Wb.Sheets[1];
// Master, verbatim
// B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
// Follower one row down: relative row moves, absolute row frozen,
// the mixed A$1 keeps its row, and the literal stays a literal
// B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
ShowMessage(Sh.Cells[2, 2].Formula);
finally
Wb.Free;
end;
end;L'attributo ref è un cancello, non una decorazione. Un follower le cui coordinate cadono fuori dall'intervallo applicabile del master non viene espanso, perché il file starebbe allora facendo un'affermazione che il gruppo non supporta. Allo stesso modo, quando uno spostamento spingerebbe un riferimento sopra la riga uno o a sinistra della colonna A, HotXLS emette #REF! per quel token anziché troncarlo silenziosamente, il che è ciò che Excel stesso produrrebbe per la stessa modifica. Questa traduzione è cugina stretta, ma non la stessa cosa, della riscrittura dei riferimenti che avviene quando inserisci o elimini righe. Quel percorso ha le proprie regole su cosa fa un intervallo quando una modifica lo taglia, ed è descritto separatamente in l'articolo sull'aggiustamento dei riferimenti di formula durante inserimento ed eliminazione. L'espansione condivisa è più semplice: è un puro offset da un'ancora nota, applicato una volta, al momento del parsing
Quali forme di riferimento deve coprire lo shifter?
Tutte, altrimenti l'espansione è un bug di perdita di dati travestito. Uno shifter ingenuo che capisce solo A1 e A1:B2 corromperà o perderà le forme più esotiche, e i workbook reali ne sono pieni. Il traduttore di formule condivise di HotXLS riconosce l'intera famiglia A1 prima di decidere cosa spostare. I riferimenti a workbook esterni come [Book.xlsx]Sheet1!A1 e i riferimenti 3D come Sheet1:Sheet3!A1 mantengono il proprio prefisso intatto mentre il riferimento di cella finale si sposta. I nomi di foglio tra virgolette sopravvivono, incluso il caso spinoso in cui il foglio è letteralmente chiamato A1, così 'A1'!A1 sposta solo la parte dopo il punto esclamativo. La colonna intera A:A sposta la sua dimensione colonna e nient'altro; la riga intera 1:1 sposta la sua dimensione riga e nient'altro; $A:$A non si sposta affatto. I riferimenti a tabella strutturata come Table[A1] vengono lasciati intoccati, perché la parte tra parentesi quadre è un nome di colonna, non una coordinata
// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1 : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3 : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]I nomi di funzione sono la trappola silenziosa qui. Uno scanner di token che afferra lettere seguite da cifre riscriverà tranquillamente LOG10 in LOG11 una riga più in basso. HotXLS richiede un confine di riferimento prima di un token candidato e dopo di esso, quindi un identificatore che continua in una lettera, cifra, underscore, punto, o una parentesi aperta non è un riferimento di cella. Se stai lavorando nell'altra famiglia di notazione, lo stesso problema di confine si presenta diversamente, e l'articolo sulla notazione R1C1 tratta dove i due modelli divergono
Perché un elemento f auto-chiuso inghiotte il valore successivo?
Perché un elemento auto-chiuso non produce alcun evento di fine elemento. Questo è il singolo bug più costoso nell'intera funzionalità, e non è specifico di nessun particolare parser XML. In TXMLReader, <f t="shared" si="4"/> genera esattamente un evento Element con IsEmptyElement impostato a True, e non genera mai l'EndElement corrispondente. Un parser che chiude il proprio stato di cattura formula solo su EndElement resta quindi dentro la formula, e il testo successivo che vede, cioè il risultato in cache dentro <v>, viene accodato al buffer della formula. Peggio ancora, lo stato sopravvive al confine della cella, quindi la cella successiva che possiede un vero <f> ha il proprio testo di formula assorbito dalla cella precedente. La correzione è terminare lo stato della formula proprio all'evento Element ogni volta che IsEmptyElement è True, ed eseguire lì l'intera risoluzione del follower anziché aspettare. Questo significa leggere t, si, ref, aca, e ca dagli attributi, applicare l'espansione condivisa, scrivere gli attributi di ricalcolo sulla cella, e azzerare lo stato condiviso, tutto dentro il ramo che gestisce l'elemento vuoto. Nota che il formato permette entrambe le grafie, <f t="shared" si="4"/> e <f t="shared" si="4"></f>, e la seconda genera davvero un EndElement. Un lettore corretto deve gestire la coppia in modo identico, motivo per cui HotXLS copre entrambe le grafie nello stesso file di regressione
Valori si sparsi, non ordinati, e la coda in sospeso
L'attributo si è un intero senza segno fornito dal file, non una posizione di array che controlli. Nulla nello schema richiede che gli indici condivisi siano densi, che inizino da zero, o che appaiano in ordine ascendente, e nulla impedisce a un file ostile o semplicemente strano di usare si="4294967290" sulla prima cella. Dimensionare un array di lookup dal più grande si osservato è quindi un primitivo di esaurimento memoria, non un'ottimizzazione. HotXLS mantiene invece il percorso di apertura del workbook su una tabella sparsa ordinata: i gruppi condivisi vengono registrati sotto la propria chiave intera in un TStringList ordinato, il che rende la ricerca una ricerca binaria sul numero di gruppi effettivamente esistenti, senza alcuna relazione con la dimensione numerica degli indici. L'ordine è la seconda metà del problema. Un master normalmente precede i suoi follower nell'ordine del documento, ma questa è una convenzione più che una regola, quindi qualunque follower che non può risolvere il proprio si nel momento in cui viene analizzato finisce in una coda in sospeso. Quando il foglio termina, la coda viene riprodotta contro la tabella ora completa, e i master tardivi risolvono i loro orfani. Le celle che non trovano mai un master mantengono una formula vuota, il che è il risultato onesto per un file che referenzia un gruppo che non ha mai definito
Espandere formule condivise senza caricare il workbook
I lettori in streaming affrontano lo stesso requisito con un budget di memoria molto più stretto, e lo risolvono con una tabella locale al worksheet. Sia TXLSDirectReader sia TXLSRowCursor espandono i follower in formule complete per cella preservando il proprio comportamento a memoria limitata e di proiezione, così un passaggio solo-in-avanti su un foglio di 300 MB ti consegna comunque testo di formula reale
var
Reader: TXLSDirectReader;
Cursor: TXLSRowCursor;
begin
// Projection: only rows 2..3, only column A. The master lives in row 1,
// outside the projection, and is still parsed so the followers resolve
Reader:= TXLSDirectReader.Create;
try
Reader.FirstRow:= 2;
Reader.LastRow:= 3;
Reader.IncludeColumn(1);
Reader.OnCell:= HandleCell; // Cell.Formula is fully expanded here
Reader.ReadFile(FileName);
finally
Reader.Free;
end;
// Forward-only row traversal, same expansion
Cursor:= TXLSRowCursor.Create;
try
Cursor.Open(FileName);
if Cursor.FindFirst then
repeat
if Cursor.CellCount > 0 then
WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
until not Cursor.FindNext;
finally
Cursor.Free;
end;
end;Due vincoli derivano da quel design. Primo, la proiezione non può mai saltare il master. Un filtro di righe impostato con FirstRow e LastRow, o un filtro di colonne costruito con IncludeColumn, può saltare l'emissione della cella master alla tua callback, ma il parser deve comunque registrare il suo si, le coordinate di ancoraggio, l'intervallo applicabile, e il testo della formula, altrimenti ogni follower dentro la proiezione si risolve in nulla. Solo il lavoro lato follower, lo spostamento e la decodifica del valore, è sicuro da saltare. Secondo, la tabella è per worksheet e la sua durata di vita deve essere gestita esplicitamente: TXLSRowCursor mantiene un'istanza per la durata di un passaggio sul foglio e la azzera al riavvio, al cambio foglio, a fine file, a eccezione, e alla chiusura, così un gruppo definito sul foglio uno non può mai fuoriuscire nel foglio due. Poiché il percorso in streaming è un ciclo caldo, usa un hash a interi con indirizzamento aperto anziché la tabella di stringhe ordinata, il che evita una conversione da intero a stringa per cella
Cosa succede al salvataggio, e dove sono i confini
Una volta che un follower è stato espanso è una formula ordinaria, e HotXLS lo riscrive come elemento <f> indipendente senza t="shared" e senza si. Il round trip è stabile e i risultati in cache <v> sopravvivono, ma l'output è più grande dell'input per un foglio pesantemente condiviso, e il raggruppamento creato da Excel non viene ricostruito al salvataggio. Se la fedeltà a livello di byte dei gruppi condivisi conta per te più che avere testo di formula reale in ogni cella, questo è il compromesso che stai accettando. Il lato XLS è diverso, incidentalmente: il record BIFF8 SHRFMLA ha una propria codifica e un proprio writer, con un interruttore di gruppo condiviso sul workbook
Due cose correlate esplicitamente non sono formule condivise anche se condividono l'elemento <f>. Le formule array CSE legacy usano t="array" con un ref che copre l'intervallo ancorato, e gli array dinamici usano la stessa grafia t="array" ma sono identificati da un attributo cm che si concatena tramite cellMetadata a un record XLDAPR. Trattare una cella di spill di array dinamico come un follower condiviso o CSE è un vero bug di correttezza, e la separazione è trattata in l'articolo sugli array dinamici e le formule di spill. Leggi i tre casi come tre parser che capita condividano un nome di tag, e il codice resta onesto
L'espansione delle formule condivise, i lettori in streaming, e il traduttore di riferimenti descritti qui vengono forniti come parte del componente Excel HotXLS per Delphi e C++Builder; la pagina prodotto riporta il riferimento API completo per formule e lettura diretta, incluse le proprietà di proiezione usate sopra