Een shared-formula-volger in XLSX draagt geen formuletekst. Het <f t="shared" si="N"/>-element wijst naar een mastercel elders in het blad, en de reader moet de tekst herbouwen door de masterformule te verschuiven met het rij- en kolomverschil. HotXLS Component voor Delphi en C++Builder voert die expansie uit op openingstijd, zodat elke volger een volledige formule rapporteert
Als je ooit een reële XLSX in een bibliotheek van een derde partij hebt geladen en merkte dat een kolom met duizend formules tekst heeft in precies één cel en lege tekenreeksen in de andere 999, dan heb je deze functie van de verkeerde kant meegemaakt. Er is niets corrupt. Het bestand doet wat ECMA-376 toestaat, en de reader stopte simpelweg op het punt waar de XML stopte
Waarom is de shared-formula-cel leeg
Omdat het formaat de formule doelbewust maar één keer opslaat. In ECMA-376 Part 1 en ISO/IEC 29500-1 draagt het <f>-element (§18.3.1.40) een t-attribuut van het type ST_CellFormulaType, en de waarde shared betekent dat deze cel deel uitmaakt van een groep die wordt geïdentificeerd door het si-attribuut. Precies één cel in de groep, de master, draagt ook een ref-attribuut dat het bereik aangeeft waarop de groep van toepassing is, en alleen die cel draagt de formuletekst als elementinhoud. Elke andere cel in de groep is een volger. Ze herhaalt t="shared" en dezelfde si, en de elementinhoud ervan is leeg. Excel schrijft deze groepen agressief, want een fill-down over een kolom van 200.000 rijen wordt teruggebracht van 200.000 formuletekenreeksen naar één tekenreeks plus 199.999 kleine placeholder-elementen. De besparing is reëel en de kosten komen volledig bij de reader terecht: zonder expansie heeft de volger geen betekenis op zichzelf
De verschuiving is een vertaling, geen tekstkopie
HotXLS lost een volger op door de master te vinden die onder dezelfde si is geregistreerd, het rij- en kolomdelta van het masteranker naar de huidige cel te berekenen, en elke referentie in de masterformule met dat delta te vertalen. Relatieve dimensies verplaatsen mee, absolute dimensies niet, en gemengde referenties verplaatsen alleen hun niet-absolute helft. Tekenreeksliteralen worden volledig overgeslagen, zodat een formule die toevallig de tekst "A1" bevat, die tekst ongewijzigd behoudt in elke volger
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;Het ref-attribuut is een poort, geen decoratie. Een volger wiens coördinaten buiten het toepasselijke bereik van de master vallen, wordt niet geëxpandeerd, omdat het bestand dan een claim doet die de groep niet ondersteunt. Evenzo, wanneer een verschuiving een referentie boven rij één of links van kolom A zou duwen, geeft HotXLS #REF! voor dat token in plaats van het stilzwijgend af te knijpen, wat Excel zelf ook zou produceren bij dezelfde bewerking. Deze vertaling is een naaste verwant van, maar niet hetzelfde als, het herschrijven van referenties dat gebeurt wanneer je rijen invoegt of verwijdert. Dat pad heeft zijn eigen regels over wat een bereik doet wanneer een bewerking er dwars doorheen snijdt, en dat wordt afzonderlijk beschreven in het artikel over aanpassing van formulereferenties bij invoegen en verwijderen. Shared-expansie is eenvoudiger: het is een pure offset vanaf een bekend anker, eenmalig toegepast, tijdens het parsen
Welke referentievormen moet de shifter afdekken
Allemaal, anders is de expansie een verkapte databug. Een naïeve shifter die alleen A1 en A1:B2 begrijpt zal de meer exotische vormen corrumperen of laten vallen, en echte werkboeken staan er vol mee. De shared-formula-vertaler van HotXLS herkent de hele A1-familie voordat hij beslist wat er verschoven moet worden. Externe werkboekreferenties zoals [Book.xlsx]Sheet1!A1 en 3D-referenties zoals Sheet1:Sheet3!A1 behouden hun prefix intact terwijl de afsluitende celreferentie verschuift. Aangehaalde bladnamen overleven, inclusief het nare geval waarin het blad letterlijk A1 heet, zodat 'A1'!A1 alleen het deel na het uitroepteken verschuift. Hele kolom A:A verplaatst alleen zijn kolomdimensie; hele rij 1:1 verplaatst alleen zijn rijdimensie; $A:$A verplaatst helemaal niet. Gestructureerde tabelreferenties zoals Table[A1] blijven onaangeroerd, omdat het deel tussen haakjes een kolomnaam is, geen coördinaat
// 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]Functienamen zijn hier de stille valkuil. Een tokenscanner die letters gevolgd door cijfers grijpt, zal LOG10 vrolijk herschrijven naar LOG11 één rij lager. HotXLS vereist een referentiegrens vóór een kandidaat-token en erna, zodat een identifier die doorgaat in een letter, cijfer, underscore, punt of een openend haakje geen celreferentie is. Als je met de andere notatiefamilie werkt, duikt hetzelfde grensprobleem anders op, en het artikel over R1C1-notatie behandelt waar de twee modellen uiteenlopen
Waarom slikt een self-closing f-element de volgende waarde op
Omdat een self-closing element geen end-element-event oplevert. Dit is verreweg de duurste bug in de hele functie, en hij is niet specifiek voor één XML-parser. In TXMLReader genereert <f t="shared" si="4"/> precies één Element-event met IsEmptyElement ingesteld op True, en nooit het bijbehorende EndElement. Een parser die zijn formule-opvangstatus alleen bij EndElement sluit, blijft daardoor binnen de formule, en de volgende tekst die hij ziet, namelijk het gecachete resultaat binnen <v>, wordt aan de formulebuffer toegevoegd. Erger nog, de status overleeft de celgrens, zodat de volgende cel die een echt <f> bezit, zijn formuletekst laat opslokken door de vorige cel. De oplossing is om de formulestatus al bij het Element-event zelf te beëindigen wanneer IsEmptyElement True is, en de hele volger-resolutie daar uit te voeren in plaats van te wachten. Dat betekent t, si, ref, aca en ca lezen uit de attributen, de shared-expansie toepassen, de herberekeningsattributen op de cel schrijven, en de shared-status wissen, allemaal binnen de tak die het lege element afhandelt. Merk op dat het formaat beide spellingen toestaat, <f t="shared" si="4"/> en <f t="shared" si="4"></f>, en de tweede genereert wel een EndElement. Een correcte reader moet het paar identiek afhandelen, en daarom dekt HotXLS beide spellingen af in hetzelfde regressiebestand
Schaarse, ongeordende si-waarden en de wachtrij in behandeling
Het si-attribuut is een door het bestand aangeleverd unsigned integer, geen array-positie die je zelf beheert. Niets in het schema vereist dat shared-indexen dicht opeenvolgend zijn, bij nul beginnen, of in oplopende volgorde verschijnen, en niets weerhoudt een vijandig of gewoon vreemd bestand ervan si="4294967290" op de eerste cel te gebruiken. Een opzoekarray dimensioneren op de grootste waargenomen si is daarom een geheugenuitputtingsprimitief, geen optimalisatie. HotXLS houdt het openingspad van het werkboek in plaats daarvan op een gesorteerde ijle tabel: shared-groepen worden onder hun integer-sleutel geregistreerd in een gesorteerde TStringList, wat opzoeking een binaire zoekopdracht maakt over hoeveel groepen er ook daadwerkelijk bestaan, zonder relatie met de numerieke grootte van de indexen. Volgorde is de tweede helft van het probleem. Een master gaat normaal aan zijn volgers vooraf in documentvolgorde, maar dat is een conventie, geen regel, dus elke volger die zijn si niet kan oplossen op het moment dat hij wordt geparset, gaat in een wachtrij in behandeling. Wanneer het blad klaar is, wordt de wachtrij afgespeeld tegen de nu complete tabel, en de late masters lossen hun wezen op. Cellen die nooit een master vinden houden een lege formule, wat de eerlijke uitkomst is voor een bestand dat naar een groep verwijst die het nooit heeft gedefinieerd
Shared formules expanderen zonder het werkboek te laden
De streaming readers staan voor dezelfde eis onder een veel krapper geheugenbudget, en ze lossen het op met een blad-lokale tabel. TXLSDirectReader en TXLSRowCursor expanderen beide volgers naar complete per-cel formules terwijl ze hun begrensde-geheugen- en projectiegedrag behouden, zodat een forward-only pass over een blad van 300 MB nog steeds echte formuletekst oplevert
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;Twee beperkingen volgen uit dat ontwerp. Ten eerste kan projectie de master nooit overslaan. Een rijfilter ingesteld met FirstRow en LastRow, of een kolomfilter opgebouwd met IncludeColumn, mag het weergeven van de mastercel aan je callback overslaan, maar de parser moet toch zijn si, ankercoördinaten, toepasselijke bereik en formuletekst vastleggen, anders lost elke volger binnen de projectie op naar niets. Alleen het werk aan de volgerzijde, de verschuiving en de waardedecodering, mag veilig worden overgeslagen. Ten tweede is de tabel per werkblad en moet de levensduur ervan expliciet worden beheerd: TXLSRowCursor houdt één instantie aan voor de duur van een bladdoorgang en wist deze bij herstart, bladwissel, einde van bestand, uitzondering en sluiten, zodat een groep gedefinieerd op blad één nooit kan lekken naar blad twee. Omdat het streamingpad een hot loop is, gebruikt het een open-addressing integer-hash in plaats van de gesorteerde tekenreekstabel, wat een integer-naar-tekenreeks-conversie per cel vermijdt
Wat gebeurt er bij opslaan, en waar liggen de grenzen
Zodra een volger is geëxpandeerd, is het een gewone formule, en HotXLS schrijft hem terug als een onafhankelijk <f>-element zonder t="shared" en zonder si. De round trip is stabiel en de gecachete <v>-resultaten overleven, maar de uitvoer is groter dan de invoer voor een zwaar gedeeld blad, en de groepering die Excel creëerde wordt bij opslaan niet gereconstrueerd. Als byte-niveau-trouw van de shared-groepen belangrijker voor je is dan echte formuletekst in elke cel, is dit de afweging die je accepteert. De XLS-kant is overigens anders: het BIFF8-record SHRFMLA heeft zijn eigen codering en zijn eigen writer, met een shared-groep-schakelaar op het werkboek
Twee verwante dingen zijn expliciet géén shared formules, ook al delen ze het <f>-element. Legacy CSE-array-formules gebruiken t="array" met een ref die het verankerde bereik dekt, en dynamische arrays gebruiken dezelfde spelling t="array" maar worden geïdentificeerd door een cm-attribuut dat via cellMetadata doorschakelt naar een XLDAPR-record. Een dynamic-array-spillcel behandelen als een shared- of CSE-volger is een echte correctheidsbug, en het onderscheid wordt behandeld in het artikel over dynamische arrays en spill-formules. Lees de drie gevallen als drie parsers die toevallig een tagnaam delen, en de code blijft eerlijk
De shared-formula-expansie, de streaming readers en de referentievertaler die hier zijn beschreven maken deel uit van de HotXLS Excel-component voor Delphi en C++Builder; de productpagina bevat de volledige referentie voor formules en direct-read-API, inclusief de hierboven gebruikte projectie-eigenschappen