En shared-formula-følger i XLSX bærer ingen formeltekst. Dens <f t="shared" si="N"/>-element peger på en master-celle et andet sted i arket, og læseren skal genopbygge teksten ved at forskyde master-formlen med række- og kolonneforskellen. HotXLS Component til Delphi og C++Builder udfører den ekspansion ved åbningstidspunktet, så hver følger rapporterer en komplet formel
Har man nogensinde indlæst en virkelig XLSX i et tredjeparts-bibliotek og fundet, at en kolonne med tusind formler har tekst i præcis én celle og tomme strenge i de andre 999, har man mødt denne funktion fra den forkerte side. Intet er korrupt. Filen gør, hvad ECMA-376 tillader den at gøre, og læseren stoppede simpelthen der, hvor XML'en stoppede
Hvorfor er den delte formel-celle tom?
Fordi formatet bevidst gemmer formlen én gang. I ECMA-376 Part 1 og ISO/IEC 29500-1 bærer <f>-elementet (§18.3.1.40) et t-attribut af typen ST_CellFormulaType, og værdien shared betyder, at denne celle deltager i en gruppe identificeret af si-attributten. Præcis én celle i gruppen, masteren, bærer også et ref-attribut, der giver det interval, gruppen gælder for, og kun den celle bærer formelteksten som elementindhold. Hver anden celle i gruppen er en følger. Den gentager t="shared" og samme si, og dens elementindhold er tomt. Excel skriver disse grupper aggressivt, fordi en udfyld-ned over en 200.000-rækkers kolonne kollapser fra 200.000 formelstrenge til én streng plus 199.999 små pladsholderelementer. Besparelsen er reel, og omkostningen lander helt hos læseren: uden ekspansion har følgeren ingen betydning på egen hånd
Forskydningen er en oversættelse, ikke en tekstkopi
HotXLS opløser en følger ved at lokalisere den master, der er registreret under samme si, beregne række- og kolonnedeltaet fra masterankeret til den nuværende celle, og oversætte hver reference i masterformlen med det delta. Relative dimensioner flytter sig, absolutte dimensioner gør ikke, og blandede referencer flytter kun deres ikke-absolutte halvdel. Strengliteraler springes helt over, så en formel, der tilfældigvis indeholder teksten "A1", beholder den tekst uændret i hver følger
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;ref-attributten er en port, ikke dekoration. En følger, hvis koordinater falder uden for masterens gældende interval, ekspanderes ikke, fordi filen så fremsætter en påstand, gruppen ikke understøtter. På samme måde, når en forskydning ville skubbe en reference over række et eller til venstre for kolonne A, udsender HotXLS #REF! for det token frem for stille at klemme det fast, hvilket er hvad Excel selv ville producere for samme redigering. Denne oversættelse er nært beslægtet med, men ikke det samme som, den referenceomskrivning der sker, når man indsætter eller sletter rækker. Den sti har sine egne regler for, hvad et interval gør, når en redigering skærer igennem det, og den er beskrevet separat i artiklen om formelreference-justering ved indsæt og slet. Delt ekspansion er simplere: det er en ren offset fra et kendt anker, anvendt én gang, ved parse-tidspunktet
Hvilke referenceformer skal forskyderen dække?
Alle sammen, ellers er ekspansionen en datatabsfejl i forklædning. En naiv forskyder, der kun forstår A1 og A1:B2, vil korrumpere eller droppe de mere eksotiske former, og rigtige arbejdsbøger er fulde af dem. HotXLS' shared-formula-oversætter genkender hele A1-familien, før den beslutter hvad der skal flyttes. Eksterne arbejdsbogsreferencer såsom [Book.xlsx]Sheet1!A1 og 3D-referencer såsom Sheet1:Sheet3!A1 beholder deres prefiks intakt, mens den efterfølgende cellereference forskydes. Citerede arknavne overlever, inklusive det ubehagelige tilfælde hvor arket bogstaveligt er navngivet A1, så 'A1'!A1 forskyder kun delen efter udråbstegnet. Hel-kolonne A:A flytter sin kolonnedimension og intet andet; hel-række 1:1 flytter sin rækkedimension og intet andet; $A:$A flytter sig slet ikke. Strukturerede tabelreferencer som Table[A1] lades urørt, fordi den parentetiske del er et kolonnenavn, ikke en koordinat
// 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]Funktionsnavne er den tavse fælde her. En token-scanner, der griber bogstaver efterfulgt af cifre, vil glad omskrive LOG10 til LOG11 én række ned. HotXLS kræver en referencegrænse før et kandidat-token og efter det, så en identifikator, der fortsætter ind i et bogstav, ciffer, underscore, punktum eller en åbningsparentes, ikke er en cellereference. Hvis man arbejder i den anden notationsfamilie, viser det samme grænseproblem sig anderledes, og R1C1-notationsartiklen dækker hvor de to modeller afviger
Hvorfor sluger et selvlukkende f-element den næste værdi?
Fordi et selvlukkende element ikke producerer nogen end-element-hændelse. Dette er den enkelt dyreste fejl i hele funktionen, og den er ikke specifik for nogen bestemt XML-parser. I TXMLReader rejser <f t="shared" si="4"/> præcis én Element-hændelse med IsEmptyElement sat til True, og rejser aldrig den matchende EndElement. En parser, der kun lukker sin formel-indfangende tilstand ved EndElement, forbliver derfor inde i formlen, og den næste tekst den ser, som er det cachede resultat inde i <v>, bliver tilføjet til formelbufferen. Værre, tilstanden overlever cellegrænsen, så den næste celle, der ejer en rigtig <f>, får sin formeltekst absorberet af den forrige celle. Rettelsen er at afslutte formeltilstanden ved selve Element-hændelsen, når IsEmptyElement er True, og at køre hele følger-opløsningen der frem for at vente. Det betyder at læse t, si, ref, aca og ca fra attributterne, anvende den delte ekspansion, skrive genberegningsattributterne på cellen, og rydde den delte tilstand, alt sammen inde i den gren, der håndterer det tomme element. Bemærk at formatet tillader begge stavemåder, <f t="shared" si="4"/> og <f t="shared" si="4"></f>, og den anden rejser faktisk en EndElement. En korrekt læser skal håndtere parret identisk, hvilket er hvorfor HotXLS dækker begge stavemåder i den samme regressionsfil
Sparsomme, uordnede si-værdier og pending-køen
si-attributten er et fil-leveret unsigned heltal, ikke en array-position man styrer. Intet i skemaet kræver, at delte indekser er tætte, starter ved nul, eller optræder i stigende rækkefølge, og intet stopper en fjendtlig eller blot mærkelig fil fra at bruge si="4294967290" på den første celle. At dimensionere et opslagsarray fra det største observerede si er derfor en hukommelsesudtømningsprimitiv, ikke en optimering. HotXLS holder i stedet arbejdsbog-åbn-stien på en sorteret sparsom tabel: delte grupper registreres under deres heltalsnøgle i en sorteret TStringList, hvilket gør opslag til en binær søgning over hvor mange grupper der faktisk findes, uden nogen relation til den numeriske størrelse af indekserne. Rækkefølge er den anden halvdel af problemet. En master går normalt forud for sine følgere i dokumentrækkefølge, men det er en konvention frem for en regel, så enhver følger, der ikke kan opløse sin si i det øjeblik den parses, går ind i en pending-kø. Når arket er færdigt, afspilles køen mod den nu-komplette tabel, og de sene mastere opløser deres forældreløse. Celler, der aldrig finder en master, beholder en tom formel, hvilket er det ærlige udfald for en fil, der refererer en gruppe, den aldrig definerede
At ekspandere delte formler uden at indlæse arbejdsbogen
De strømmende læsere står over for det samme krav under et meget strammere hukommelsesbudget, og de løser det med en arknær tabel. TXLSDirectReader og TXLSRowCursor ekspanderer begge følgere til komplette per-celle-formler, mens de bevarer deres begrænset-hukommelses- og projektionsopførsel, så en fremad-kun-gennemgang over et 300 MB-ark stadig giver dig rigtig formeltekst
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;To begrænsninger falder ud af det design. Først, projektion kan aldrig springe masteren over. Et rækkefilter sat med FirstRow og LastRow, eller et kolonnefilter bygget med IncludeColumn, kan springe over at udsende master-cellen til dit callback, men parseren skal stadig registrere dens si, ankerkoordinater, gældende interval og formeltekst, ellers opløser hver følger inden for projektionen til intet. Kun følger-side-arbejdet, forskydningen og værdi-afkodningen, er sikkert at springe over. For det andet er tabellen per regneark, og dens levetid skal styres eksplicit: TXLSRowCursor holder én instans i løbet af en arkgennemgang og rydder den ved genstart, arkskift, filafslutning, undtagelse og lukning, så en gruppe defineret på ark et aldrig kan lække ind i ark to. Fordi den strømmende sti er en hed løkke, bruger den en open-addressing-heltalshash frem for den sorterede strengtabel, hvilket undgår en heltal-til-streng-konvertering per celle
Hvad sker der ved gem, og hvor grænserne er
Når en følger er blevet ekspanderet, er den en almindelig formel, og HotXLS skriver den tilbage som et uafhængigt <f>-element uden t="shared" og uden si. Rundturen er stabil, og de cachede <v>-resultater overlever, men outputtet er større end inputtet for et tungt delt ark, og den gruppering Excel oprettede, genopbygges ikke ved gem. Hvis byte-niveau-troskab af de delte grupper betyder mere for dig end at have rigtig formeltekst i hver celle, er dette den afvejning, man accepterer. XLS-siden er anderledes, i øvrigt: BIFF8 SHRFMLA-posten har sin egen kodning og sin egen skriver, med en delt-gruppe-kontakt på arbejdsbogen
To beslægtede ting er eksplicit ikke delte formler, selvom de deler <f>-elementet. Legacy CSE-array-formler bruger t="array" med et ref der dækker det ankrede interval, og dynamiske arrays bruger den samme t="array"-stavemåde, men identificeres af et cm-attribut, der kæder gennem cellMetadata til en XLDAPR-post. At behandle en dynamisk-array-spill-celle som en delt eller CSE-følger er en ægte korrekthedsfejl, og adskillelsen dækkes i artiklen om dynamisk array og spill-formler. Læs de tre tilfælde som tre parsere, der tilfældigvis deler et tag-navn, og koden forbliver ærlig
Den delte-formel-ekspansion, de strømmende læsere, og referenceoversætteren beskrevet her leveres som del af HotXLS Excel-komponenten til Delphi og C++Builder; produktsiden bærer den fulde formel- og direkte-læse-API-reference, inklusive de projektionsegenskaber der er brugt ovenfor