Sledilec skupne formule v XLSX ne nosi besedila formule. Njegov element <f t="shared" si="N"/> kaže na glavno celico nekje drugje na listu, bralnik pa mora obnoviti besedilo s premikom glavne formule za razliko vrstice in stolpca. HotXLS Component za Delphi in C++Builder to širjenje izvede ob odpiranju, tako da vsak sledilec poroča popolno formulo
Če ste kdaj naložili resnično XLSX v knjižnico tretje osebe in ugotovili, da ima stolpec tisočih formul besedilo v natanko eni celici in prazne nize v preostalih 999, ste to funkcijo srečali z napačne strani. Nič ni poškodovano. Datoteka počne tisto, kar ji ECMA-376 dovoli, bralnik pa se je preprosto ustavil tam, kjer se je ustavil XML
Zakaj je celica skupne formule prazna?
Ker format formulo namerno shrani enkrat. V ECMA-376 Part 1 in ISO/IEC 29500-1 element <f> (§18.3.1.40) nosi atribut t tipa ST_CellFormulaType, vrednost shared pa pomeni, da ta celica sodeluje v skupini, identificirani z atributom si. Natanko ena celica v skupini, glavna, nosi tudi atribut ref, ki poda obseg, za katerega skupina velja, le ta celica pa nosi besedilo formule kot vsebino elementa. Vsaka druga celica v skupini je sledilec. Ponovi t="shared" in isti si, vsebina njenega elementa pa je prazna. Excel te skupine piše agresivno, ker se zapolnitev navzdol čez stolpec s 200.000 vrsticami skrči iz 200.000 nizov formul v en niz plus 199.999 majhnih nadomestnih elementov. Prihranek je resničen, cena pa v celoti pade na bralnika: brez širjenja sledilec sam po sebi nima pomena
Premik je prevod, ne kopija besedila
HotXLS sledilca razreši tako, da locira glavno formulo, registrirano pod istim si, izračuna razliko vrstice in stolpca od sidra glavne formule do trenutne celice ter za to razliko prevede vsako referenco v glavni formuli. Relativne dimenzije se premaknejo, absolutne se ne, mešane reference pa premaknejo le svojo neabsolutno polovico. Dobesedni nizi so povsem preskočeni, tako da formula, ki slučajno vsebuje besedilo "A1", to besedilo v vsakem sledilcu obdrži nespremenjeno
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 vratce, ne okrasek. Sledilec, čigar koordinate padejo zunaj veljavnega obsega glavne formule, ni razširjen, ker datoteka takrat postavlja trditev, ki je skupina ne podpira. Prav tako, kadar bi premik potisnil referenco nad vrstico ena ali levo od stolpca A, HotXLS za ta žeton izda #REF! namesto tihega omejevanja, kar je tisto, kar bi sam Excel proizvedel za isto urejanje. Ta prevod je bližnji sorodnik, ne pa isto kot prepisovanje referenc, ki se zgodi, ko vstavite ali izbrišete vrstice. Ta pot ima svoja lastna pravila o tem, kaj obseg stori, ko ga urejanje preseka, opisana pa je ločeno v članku o prilagoditvi referenc formul med vstavljanjem in brisanjem. Skupno širjenje je preprostejše: je čist odmik od znanega sidra, uporabljen enkrat, ob razčlenjevanju
Katere oblike referenc mora premikalnik pokriti?
Vse, sicer je širjenje zamaskiran hrošč izgube podatkov. Naiven premikalnik, ki razume le A1 in A1:B2, bo pokvaril ali izpustil bolj eksotične oblike, resnični delovni zvezki pa so jih polni. Prevajalnik skupnih formul HotXLS prepozna celotno družino A1, preden se odloči, kaj premakniti. Zunanje reference delovnega zvezka, kot je [Book.xlsx]Sheet1!A1, in 3D reference, kot je Sheet1:Sheet3!A1, obdržijo svojo predpono nedotaknjeno, medtem ko se sledeča referenca celice premakne. Imena listov v narekovajih preživijo, vključno z neprijetnim primerom, ko je list dobesedno poimenovan A1, tako da 'A1'!A1 premakne le del za klicajem. Celoten stolpec A:A premakne svojo dimenzijo stolpca in nič drugega; cela vrstica 1:1 premakne svojo dimenzijo vrstice in nič drugega; $A:$A se sploh ne premakne. Strukturirane reference tabele, kot je Table[A1], ostanejo nedotaknjene, ker je del v oglatih oklepajih ime stolpca, ne pa 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 funkcij so tukaj tiha past. Skener žetonov, ki pobere črke, katerim sledijo števke, bo z veseljem prepisal LOG10 v LOG11 eno vrstico navzdol. HotXLS zahteva mejo reference pred kandidatnim žetonom in za njim, tako da identifikator, ki se nadaljuje v črko, števko, podčrtaj, piko ali odpirajoč oklepaj, ni referenca celice. Če delate v drugi družini notacije, se ista težava meje pokaže drugače, članek o notaciji R1C1 pa pokriva, kje se oba modela razideta
Zakaj samozaključen element f pogoltne naslednjo vrednost?
Ker samozaključen element ne proizvede dogodka konca elementa. To je vsekakor najdražji hrošč v celotni funkciji in ni specifičen za noben posamezen razčlenjevalnik XML. V TXMLReader <f t="shared" si="4"/> sproži natanko en dogodek Element z IsEmptyElement, nastavljenim na True, in nikoli ne sproži ujemajočega EndElement. Razčlenjevalnik, ki svoje stanje zajemanja formule zapre le ob EndElement, torej ostane znotraj formule, naslednje besedilo, ki ga vidi, kar je predpomnjen rezultat znotraj <v>, pa se doda v medpomnilnik formule. Še huje, stanje preživi mejo celice, tako da naslednjo celico, ki poseduje pravi <f>, njeno besedilo formule absorbira prejšnja celica. Popravek je zaključiti stanje formule ob samem dogodku Element, kadarkoli je IsEmptyElement True, in tam izvesti celotno razreševanje sledilca namesto čakanja. To pomeni branje t, si, ref, aca in ca iz atributov, uporabo skupnega širjenja, zapis atributov preračunavanja na celico in čiščenje skupnega stanja, vse znotraj veje, ki obravnava prazen element. Opazite, da format dovoli oba zapisa, <f t="shared" si="4"/> in <f t="shared" si="4"></f>, drugi pa dejansko sproži EndElement. Pravilen bralnik mora oba obravnavati enako, zato HotXLS oba zapisa pokrije v isti regresijski datoteki
Redke, neurejene vrednosti si in čakalna vrsta na čakanju
Atribut si je s strani datoteke podano nepredznačeno celo število, ne pa položaj polja, ki ga nadzorujete. Nič v shemi ne zahteva, da so skupni indeksi gosti, da se začnejo pri nič ali da se pojavijo v naraščajočem vrstnem redu, nič pa ne prepreči sovražni ali preprosto čudni datoteki, da na prvi celici uporabi si="4294967290". Dimenzioniranje polja iskanja glede na največji opažen si je torej primitiv za izčrpanje pomnilnika, ne pa optimizacija. HotXLS pot odpiranja delovnega zvezka namesto tega drži na razvrščeni redki tabeli: skupine so registrirane pod svojim celoštevilskim ključem v razvrščenem TStringList, kar iskanje naredi za binarno iskanje čez kolikor koli skupin dejansko obstaja, brez povezave z numerično velikostjo indeksov. Vrstni red je druga polovica težave. Glavna formula ponavadi predhodi svojim sledilcem v vrstnem redu dokumenta, vendar je to konvencija, ne pa pravilo, zato vsak sledilec, ki svojega si ne more razrešiti v trenutku, ko je razčlenjen, gre v čakalno vrsto. Ko je list končan, je vrsta ponovno predvajana proti zdaj popolni tabeli, pozne glavne formule pa razrešijo svoje sirote. Celice, ki nikoli ne najdejo glavne formule, obdržijo prazno formulo, kar je pošten izid za datoteko, ki se sklicuje na skupino, ki je nikoli ni definirala
Širjenje skupnih formul brez nalaganja delovnega zvezka
Pretočni bralniki se soočajo z isto zahtevo pod veliko strožjim proračunom pomnilnika in jo rešujejo z lokalno tabelo delovnega lista. TXLSDirectReader in TXLSRowCursor oba razširita sledilce v popolne formule po celicah, medtem ko ohranjata svoje omejeno vedenje pomnilnika in projekcije, tako da vam prehod naprej samo skozi 300 MB list še vedno preda resnično besedilo 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;Iz te zasnove izpadeta dve omejitvi. Prvič, projekcija nikoli ne more preskočiti glavne formule. Filter vrstic, nastavljen z FirstRow in LastRow, ali filter stolpcev, zgrajen z IncludeColumn, lahko preskoči izdajo glavne celice vašemu povratnemu klicu, vendar mora razčlenjevalnik še vedno zabeležiti njen si, koordinate sidra, veljaven obseg in besedilo formule, sicer se vsak sledilec znotraj projekcije razreši v nič. Le delo na strani sledilca, premik in dekodiranje vrednosti, je varno preskočiti. Drugič, tabela je na delovni list, njeno trajanje pa je treba izrecno upravljati: TXLSRowCursor drži eno instanco za trajanje prehoda lista in jo počisti ob ponovnem zagonu, preklopu lista, koncu datoteke, izjemi in zaprtju, tako da skupina, definirana na listu ena, nikoli ne more uhajati na list dva. Ker je pretočna pot vroča zanka, uporablja celoštevilsko razpršilo z odprtim naslavljanjem namesto razvrščene tabele nizov, kar se izogne pretvorbi celo število-v-niz na celico
Kaj se zgodi ob shranjevanju in kje so meje
Ko je sledilec razširjen, je navadna formula, HotXLS pa ga zapiše nazaj kot neodvisen element <f> brez t="shared" in brez si. Krožno potovanje je stabilno, predpomnjeni rezultati <v> pa preživijo, vendar je izhod večji od vhoda za močno skupinjen list, skupljenje, ki ga je ustvaril Excel, pa ob shranjevanju ni obnovljeno. Če vam je bajtna zvestoba skupnih skupin pomembnejša od resničnega besedila formule v vsaki celici, je to menjava, ki jo sprejemate. Stran XLS je mimogrede drugačna: zapis BIFF8 SHRFMLA ima svoje lastno kodiranje in svojega lastnega pisca, s stikalom skupne skupine na delovnem zvezku
Dve sorodni stvari izrecno nista skupni formuli, čeprav si delita element <f>. Podedovane matrične formule CSE uporabljajo t="array" s ref, ki pokriva zasidran obseg, dinamična polja pa uporabljajo isti zapis t="array", vendar so identificirana z atributom cm, ki se prek cellMetadata veriži do zapisa XLDAPR. Obravnavanje celice razlitja dinamičnega polja kot skupnega ali CSE sledilca je resničen hrošč pravilnosti, ločitev pa je obravnavana v članku o dinamičnih poljih in formulah razlitja. Beri vse tri primere kot tri razčlenjevalnike, ki si slučajno delijo ime oznake, koda pa ostane poštena
Širjenje skupnih formul, pretočni bralniki in tukaj opisan prevajalnik referenc so izdani kot del HotXLS Excel component za Delphi in C++Builder; stran izdelka nosi celoten referenčni opis API-ja formul in neposrednega branja, vključno z zgoraj uporabljenimi lastnostmi projekcije