Un urmaș de formulă partajată din XLSX nu poartă niciun text de formulă. Elementul său <f t="shared" si="N"/> indică spre o celulă master aflată în altă parte a foii, iar reader-ul trebuie să reconstruiască textul deplasând formula master cu diferența de rând și coloană. HotXLS Component pentru Delphi și C++Builder face această expandare la deschidere, astfel încât fiecare urmaș raportează o formulă completă
Dacă ai încărcat vreodată un XLSX real într-o bibliotecă terță și ai găsit o coloană cu o mie de formule având text în exact o singură celulă și șiruri goale în celelalte 999, ai întâlnit această funcționalitate din partea greșită. Nimic nu este corupt. Fișierul face exact ceea ce îi permite ECMA-376, iar reader-ul pur și simplu s-a oprit acolo unde s-a oprit XML-ul
De ce este goală celula formulei partajate?
Pentru că formatul stochează deliberat formula o singură dată. În ECMA-376 Part 1 și ISO/IEC 29500-1, elementul <f> (§18.3.1.40) poartă un atribut t de tip ST_CellFormulaType, iar valoarea shared înseamnă că această celulă participă la un grup identificat de atributul si. Exact o celulă din grup, masterul, poartă și un atribut ref care dă intervalul căruia i se aplică grupul, și doar acea celulă poartă textul formulei ca și conținut de element. Fiecare altă celulă din grup este un urmaș. Ea repetă t="shared" și același si, iar conținutul ei de element este gol. Excel scrie aceste grupuri agresiv, pentru că o umplere descendentă pe o coloană de 200.000 de rânduri se restrânge de la 200.000 de șiruri de formulă la un singur șir plus 199.999 de elemente-substituent minuscule. Economia este reală, iar costul cade în întregime asupra reader-ului: fără expandare, urmașul nu are niciun sens de sine stătător
Deplasarea este o translație, nu o copiere de text
HotXLS rezolvă un urmaș localizând masterul înregistrat sub același si, calculând delta de rând și coloană de la ancora masterului la celula curentă și translatând fiecare referință din formula master cu acea delta. Dimensiunile relative se deplasează, cele absolute nu, iar referințele mixte își deplasează doar jumătatea neabsolută. Literalii de tip șir sunt complet ignorați, așa că o formulă care conține textul "A1" păstrează acel text neschimbat în fiecare urmaș
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;Atributul ref este o poartă, nu o decorație. Un urmaș ale cărui coordonate cad în afara intervalului aplicabil al masterului nu este expandat, pentru că fișierul ar face astfel o afirmație pe care grupul nu o susține. La fel, atunci când o deplasare ar împinge o referință deasupra rândului unu sau la stânga coloanei A, HotXLS emite #REF! pentru acel token, în loc să îl limiteze silențios, exact ceea ce ar produce Excel însuși pentru aceeași editare. Această translație este o rudă apropiată, dar nu identică, cu rescrierea referințelor care are loc atunci când inserezi sau ștergi rânduri. Acel traseu are propriile reguli despre ce se întâmplă cu un interval atunci când o editare îl taie, și este descris separat în articolul despre ajustarea referințelor de formulă la inserare și ștergere. Expandarea partajată este mai simplă: este un simplu decalaj față de o ancoră cunoscută, aplicat o singură dată, la analiză
Ce forme de referință trebuie să acopere translatorul?
Toate, altfel expandarea este o eroare de pierdere de date deghizată. Un translator naiv care înțelege doar A1 și A1:B2 va corupe sau va pierde formele mai exotice, iar registrele de lucru reale sunt pline de ele. Translatorul de formule partajate al HotXLS recunoaște întreaga familie A1 înainte să decidă ce anume mută. Referințele către registre de lucru externe, precum [Book.xlsx]Sheet1!A1, și referințele 3D, precum Sheet1:Sheet3!A1, își păstrează prefixul intact în timp ce referința de celulă finală se deplasează. Numele de foi între ghilimele supraviețuiesc, inclusiv cazul neplăcut în care foaia se numește literalmente A1, așa că 'A1'!A1 deplasează doar partea de după semnul exclamării. Coloana întreagă A:A își deplasează doar dimensiunea de coloană și nimic altceva; rândul întreg 1:1 își deplasează doar dimensiunea de rând și nimic altceva; $A:$A nu se deplasează deloc. Referințele structurate de tabel, precum Table[A1], rămân neatinse, pentru că partea dintre paranteze drepte este un nume de coloană, nu o coordonată
// 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]Numele de funcții sunt capcana discretă de aici. Un scaner de token-uri care prinde litere urmate de cifre va rescrie fericit LOG10 în LOG11 cu un rând mai jos. HotXLS cere o graniță de referință înainte de un token candidat și după el, așa că un identificator care continuă cu o literă, o cifră, o liniuță de subliniere, un punct sau o paranteză deschisă nu este o referință de celulă. Dacă lucrezi în cealaltă familie de notație, aceeași problemă de graniță apare diferit, iar articolul despre notația R1C1 acoperă unde diverg cele două modele
De ce un element f auto-închis înghite valoarea următoare?
Pentru că un element auto-închis nu produce niciun eveniment de sfârșit de element. Aceasta este cea mai costisitoare eroare din întreaga funcționalitate și nu este specifică unui anumit parser XML. În TXMLReader, <f t="shared" si="4"/> declanșează exact un eveniment Element cu IsEmptyElement setat pe True, și nu declanșează niciodată EndElement-ul corespunzător. Un parser care își închide starea de capturare a formulei doar la EndElement rămâne, prin urmare, în interiorul formulei, iar următorul text pe care îl vede, rezultatul din cache aflat în <v>, este adăugat la bufferul formulei. Mai rău, starea supraviețuiește peste granița celulei, așa că următoarea celulă care are un <f> real are textul formulei sale absorbit de celula anterioară. Remediul este să închizi starea formulei chiar la evenimentul Element, ori de câte ori IsEmptyElement este True, și să rulezi acolo întreaga rezolvare a urmașului, în loc să aștepți. Asta înseamnă citirea t, si, ref, aca și ca din atribute, aplicarea expandării partajate, scrierea atributelor de recalculare pe celulă și golirea stării partajate, toate în interiorul ramurii care gestionează elementul gol. Observă că formatul permite ambele scrieri, <f t="shared" si="4"/> și <f t="shared" si="4"></f>, iar a doua chiar declanșează un EndElement. Un reader corect trebuie să trateze perechea identic, motiv pentru care HotXLS acoperă ambele scrieri în același fișier de regresie
Valori si dispersate, neordonate, și coada de așteptare
Atributul si este un întreg fără semn furnizat de fișier, nu o poziție de tablou pe care o controlezi. Nimic în schemă nu cere ca indecșii partajați să fie denși, să înceapă de la zero sau să apară în ordine crescătoare, iar nimic nu împiedică un fișier ostil sau pur și simplu ciudat să folosească si="4294967290" pe prima celulă. Dimensionarea unui tablou de căutare după cel mai mare si observat este deci o primitivă de epuizare a memoriei, nu o optimizare. HotXLS păstrează în schimb traseul de deschidere a registrului de lucru pe o tabelă dispersată sortată: grupurile partajate sunt înregistrate sub cheia lor întreagă într-un TStringList sortat, ceea ce face din căutare o căutare binară peste câte grupuri există efectiv, fără nicio legătură cu mărimea numerică a indecșilor. Ordinea este cealaltă jumătate a problemei. Un master precede în mod normal urmașii săi în ordinea documentului, dar aceasta este o convenție, nu o regulă, așa că orice urmaș care nu își poate rezolva si-ul în momentul analizei intră într-o coadă de așteptare. Când foaia se termină, coada este reluată față de tabela acum completă, iar masterii întârziați își rezolvă orfanii. Celulele care nu găsesc niciodată un master păstrează o formulă goală, rezultatul onest pentru un fișier care referențiază un grup pe care nu l-a definit niciodată
Expandarea formulelor partajate fără a încărca registrul de lucru
Reader-ele de streaming se confruntă cu aceeași cerință, dar cu un buget de memorie mult mai strâns, și o rezolvă cu o tabelă locală foii de lucru. Atât TXLSDirectReader, cât și TXLSRowCursor expandează urmașii în formule complete per celulă, păstrându-și în același timp comportamentul de memorie limitată și de proiecție, așa că o trecere doar-înainte peste o foaie de 300 MB tot îți oferă text de formulă real
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;Din acest design rezultă două constrângeri. Prima: proiecția nu poate sări niciodată peste master. Un filtru de rând stabilit cu FirstRow și LastRow, sau un filtru de coloană construit cu IncludeColumn, poate sări emiterea celulei master către callback-ul tău, dar parserul tot trebuie să îi înregistreze si-ul, coordonatele ancorei, intervalul aplicabil și textul formulei, altfel fiecare urmaș din interiorul proiecției se rezolvă la nimic. Doar munca de partea urmașului, deplasarea și decodarea valorii, este sigur de sărit. A doua: tabela este per foaie de lucru, iar durata ei de viață trebuie gestionată explicit: TXLSRowCursor păstrează o singură instanță pe durata unei treceri prin foaie și o golește la repornire, schimbare de foaie, sfârșit de fișier, excepție și închidere, astfel încât un grup definit pe foaia unu nu poate scăpa niciodată pe foaia doi. Pentru că traseul de streaming este o buclă fierbinte, folosește un hash de întregi cu adresare deschisă, nu tabela de șiruri sortată, ceea ce evită o conversie întreg-în-șir pentru fiecare celulă
Ce se întâmplă la salvare și unde sunt limitele
Odată ce un urmaș a fost expandat, el este o formulă obișnuită, iar HotXLS îl scrie înapoi ca un element <f> independent, fără t="shared" și fără si. Round-trip-ul este stabil, iar rezultatele din cache <v> supraviețuiesc, dar rezultatul este mai mare decât intrarea pentru o foaie puternic partajată, iar gruparea creată de Excel nu este reconstruită la salvare. Dacă fidelitatea la nivel de octet a grupurilor partajate contează pentru tine mai mult decât a avea text de formulă real în fiecare celulă, acesta este compromisul pe care îl accepți. Partea de XLS este diferită, de altfel: înregistrarea BIFF8 SHRFMLA are propria codificare și propriul writer, cu un comutator de grup partajat la nivelul registrului de lucru
Două lucruri înrudite nu sunt, în mod explicit, formule partajate, deși împart elementul <f>. Formulele matrice CSE clasice folosesc t="array" cu un ref care acoperă intervalul ancorat, iar matricele dinamice folosesc aceeași scriere t="array", dar sunt identificate printr-un atribut cm care înlănțuie prin cellMetadata spre o înregistrare XLDAPR. Tratarea unei celule de revărsare a unei matrice dinamice ca urmaș partajat sau CSE este o eroare reală de corectitudine, iar separarea este acoperită în articolul despre matricele dinamice și formulele de revărsare. Citește cele trei cazuri ca trei parsere care se întâmplă să împartă un nume de etichetă, iar codul rămâne onest
Expandarea formulelor partajate, reader-ele de streaming și translatorul de referințe descrise aici fac parte din componenta Excel HotXLS pentru Delphi și C++Builder; pagina produsului conține referința completă a API-ului de formule și citire directă, inclusiv proprietățile de proiecție folosite mai sus