Pratilac deljene formule u XLSX-u ne nosi tekst formule. Njegov element <f t="shared" si="N"/> pokazuje na master ćeliju negde drugde na listu, a čitač mora rekonstruisati tekst pomeranjem master formule za razliku u redu i koloni. HotXLS Component za Delphi i C++Builder radi tu ekspanziju u trenutku otvaranja, tako da svaki pratilac prijavljuje kompletnu formulu
Ako ste ikada učitali stvaran XLSX u biblioteci trećeg proizvođača i otkrili da kolona od hiljadu formula ima tekst u tačno jednoj ćeliji, a prazne stringove u ostalih 999, sreli ste ovu funkciju sa pogrešne strane. Ništa nije oštećeno. Fajl radi ono što mu ECMA-376 dozvoljava, a čitač je jednostavno stao na mestu gde je XML stao
Zašto je ćelija deljene formule prazna?
Zato što format namerno čuva formulu jednom. U ECMA-376 Delu 1 i ISO/IEC 29500-1, element <f> (§18.3.1.40) nosi atribut t tipa ST_CellFormulaType, a vrednost shared znači da ova ćelija učestvuje u grupi identifikovanoj atributom si. Tačno jedna ćelija u grupi, master, takođe nosi atribut ref koji daje opseg na koji se grupa primenjuje, i samo ta ćelija nosi tekst formule kao sadržaj elementa. Svaka druga ćelija u grupi je pratilac. Ponavlja t="shared" i isti si, a njen sadržaj elementa je prazan. Excel piše ove grupe agresivno, jer fill-down preko kolone od 200.000 redova kolabira sa 200.000 stringova formula na jedan string plus 199.999 sićušnih placeholder elemenata. Ušteda je stvarna, a trošak u potpunosti pada na čitača: bez ekspanzije, pratilac nema značenje sam po sebi
Pomeranje je translacija, ne kopija teksta
HotXLS razrešava pratioca tako što locira master registrovan pod istim si, izračunava deltu reda i kolone od sidra mastera do trenutne ćelije, i translira svaku referencu u master formuli za tu deltu. Relativne dimenzije se pomeraju, apsolutne ne, a mešane reference pomeraju samo svoju ne-apsolutnu polovinu. String literali se u potpunosti preskaču, pa formula koja slučajno sadrži tekst "A1" zadržava taj tekst nepromenjen u svakom pratiocu
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;Atribut ref je kapija, ne ukras. Pratilac čije koordinate padaju van primenljivog opsega mastera se ne ekspanduje, jer fajl tada daje tvrdnju koju grupa ne podržava. Isto tako, kada bi pomeranje gurnulo referencu iznad reda jedan ili levo od kolone A, HotXLS emituje #REF! za taj token umesto da ga tiho ograniči, što je tačno ono što bi sam Excel proizveo za istu izmenu. Ova translacija je bliski rođak, ali ne i ista stvar, kao prepisivanje referenci koje se dešava kada ubacite ili obrišete redove. Ta putanja ima sopstvena pravila o tome šta opseg radi kada izmena preseca kroz njega, i opisana je posebno u članku o prilagođavanju referenci formula prilikom ubacivanja i brisanja. Deljena ekspanzija je jednostavnija: to je čist ofset od poznatog sidra, primenjen jednom, u trenutku parsiranja
Koje oblike referenci pomerač mora pokriti?
Sve njih, ili je ekspanzija bag gubitka podataka prerušen. Naivan pomerač koji razume samo A1 i A1:B2 će oštetiti ili izgubiti egzotičnije oblike, a stvarni workbookovi su puni njih. HotXLS prevodilac deljenih formula prepoznaje celu A1 porodicu pre nego što odluči šta da pomeri. Eksterne reference workbooka poput [Book.xlsx]Sheet1!A1 i 3D reference poput Sheet1:Sheet3!A1 zadržavaju svoj prefiks netaknut dok se prateća referenca ćelije pomera. Imena listova pod navodnicima prežive, uključujući ružan slučaj kada je list bukvalno imenovan A1, pa se 'A1'!A1 pomera samo u delu posle uzvičnika. Cela kolona A:A pomera svoju dimenziju kolone i ništa drugo; ceo red 1:1 pomera svoju dimenziju reda i ništa drugo; $A:$A se uopšte ne pomera. Strukturirane reference tabela poput Table[A1] ostaju netaknute, jer je deo u zagradama ime kolone, ne koordinata
// 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]Imena funkcija su tiha zamka ovde. Token skener koji zgrabi slova praćena ciframa će rado prepisati LOG10 u LOG11 jedan red niže. HotXLS zahteva granicu reference pre kandidata za token i posle njega, pa identifikator koji se nastavlja u slovo, cifru, donju crtu, tačku, ili otvorenu zagradu nije referenca ćelije. Ako radite u drugoj porodici notacije, isti problem granice se pojavljuje drugačije, a članak o R1C1 notaciji pokriva gde se ta dva modela razilaze
Zašto samozatvarajući f element proguta sledeću vrednost?
Zato što samozatvarajući element ne proizvodi događaj kraja elementa. Ovo je pojedinačno najskuplji bag u čitavoj funkciji, i nije specifičan ni za jedan XML parser. U TXMLReader, <f t="shared" si="4"/> podiže tačno jedan događaj Element sa IsEmptyElement postavljenim na True, i nikad ne podiže odgovarajući EndElement. Parser koji zatvara svoje stanje hvatanja formule samo na EndElement zato ostaje unutar formule, a sledeći tekst koji vidi, što je keširan rezultat unutar <v>, biva dodat u bafer formule. Gore od toga, stanje preživljava granicu ćelije, pa sledeća ćelija koja poseduje pravi <f> ima svoj tekst formule apsorbovan od prethodne ćelije. Popravka je da se stanje formule završi na samom događaju Element kad god je IsEmptyElement True, i da se celo razrešavanje pratioca izvrši tamo umesto čekanja. To znači čitanje t, si, ref, aca, i ca iz atributa, primenu deljene ekspanzije, pisanje atributa prekalkulacije na ćeliju, i čišćenje deljenog stanja, sve unutar grane koja obrađuje prazan element. Obratite pažnju da format dozvoljava oba zapisa, <f t="shared" si="4"/> i <f t="shared" si="4"></f>, a drugi zaista podiže EndElement. Ispravan čitač mora obraditi taj par identično, zato HotXLS pokriva oba zapisa u istom regresionom fajlu
Retke, neuređene si vrednosti i red na čekanju
Atribut si je celobrojna vrednost bez znaka koju daje fajl, ne pozicija niza koju vi kontrolišete. Ništa u šemi ne zahteva da deljeni indeksi budu gusti, da počinju od nule, ili da se pojavljuju u rastućem redosledu, i ništa ne sprečava neprijateljski ili prosto čudan fajl da koristi si="4294967290" na prvoj ćeliji. Dimenzionisanje niza pretrage prema najvećem posmatranom si je zato primitivac za iscrpljivanje memorije, ne optimizacija. HotXLS umesto toga drži putanju otvaranja workbooka na sortiranoj retkoj tabeli: deljene grupe se registruju pod svojim celobrojnim ključem u sortiranom TStringList, što čini pretragu binarnom pretragom preko koliko god grupa zaista postoji, bez veze sa numeričkom veličinom indeksa. Redosled je druga polovina problema. Master obično prethodi svojim pratiocima u redosledu dokumenta, ali to je konvencija, a ne pravilo, pa svaki pratilac koji ne može razrešiti svoj si u trenutku kada se parsira ide u red čekanja. Kada se list završi, red se ponovo pušta protiv sada kompletne tabele, i kasni masteri razrešavaju svoje siročiće. Ćelije koje nikad ne pronađu master zadržavaju praznu formulu, što je iskren ishod za fajl koji referencira grupu koju nikad nije definisao
Ekspanzija deljenih formula bez učitavanja workbooka
Streaming čitači se suočavaju sa istim zahtevom pod mnogo strožim budžetom memorije, i rešavaju to tabelom lokalnom za worksheet. TXLSDirectReader i TXLSRowCursor oba ekspanduju pratioce u kompletne formule po ćeliji dok čuvaju svoje ponašanje ograničene memorije i projekcije, pa prolaz samo-unapred preko lista od 300 MB i dalje daje pravi tekst formule
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;Dva ograničenja proizlaze iz tog dizajna. Prvo, projekcija nikad ne sme preskočiti master. Filter reda postavljen sa FirstRow i LastRow, ili filter kolone izgrađen sa IncludeColumn, može preskočiti emitovanje master ćelije vašem callbacku, ali parser i dalje mora zabeležiti njen si, koordinate sidra, primenljiv opseg, i tekst formule, inače se svaki pratilac unutar projekcije razrešava ni u šta. Samo posao na strani pratioca, pomeranje i dekodiranje vrednosti, je bezbedno preskočiti. Drugo, tabela je po worksheetu i njen životni vek se mora eksplicitno upravljati: TXLSRowCursor drži jednu instancu za trajanje prolaska kroz list i briše je pri restartu, prelasku na drugi list, kraju fajla, izuzetku, i zatvaranju, pa grupa definisana na listu jedan nikad ne može procuriti u list dva. Pošto je streaming putanja vrela petlja, koristi celobrojni heš sa otvorenim adresiranjem umesto sortirane string tabele, čime se izbegava konverzija celi-broj-u-string po ćeliji
Šta se dešava pri čuvanju, i gde su granice
Kada je pratilac jednom ekspandovan, on je obična formula, a HotXLS je piše nazad kao nezavisan element <f> bez t="shared" i bez si. Round trip je stabilan, a keširani rezultati <v> prežive, ali je izlaz veći od ulaza za jako deljen list, a grupisanje koje je Excel kreirao se ne rekonstruiše pri čuvanju. Ako vam je bajt-nivo vernost deljenih grupa važnija od toga da imate pravi tekst formule u svakoj ćeliji, to je trampa koju prihvatate. XLS strana je uzgred drugačija: BIFF8 zapis SHRFMLA ima sopstveno enkodiranje i sopstveni writer, sa prekidačem deljene grupe na workbooku
Dve povezane stvari su eksplicitno ne deljene formule iako dele element <f>. Zastarele CSE formule niza koriste t="array" sa ref koji pokriva usidren opseg, a dinamički nizovi koriste isti zapis t="array", ali se identifikuju atributom cm koji se lančano vezuje kroz cellMetadata na zapis XLDAPR. Tretiranje ćelije prelivanja dinamičkog niza kao deljenog ili CSE pratioca je pravi bag ispravnosti, a razdvajanje je pokriveno u članku o dinamičkim nizovima i formulama prelivanja. Čitajte ta tri slučaja kao tri parsera koja slučajno dele ime taga, i kod ostaje iskren
Ekspanzija deljenih formula, streaming čitači, i prevodilac referenci opisani ovde isporučuju se kao deo HotXLS Excel komponente za Delphi i C++Builder; proizvodna stranica nosi kompletnu referencu formula i API-ja za direktno čitanje, uključujući svojstva projekcije korišćena gore