En delad formel-följare i XLSX bär ingen formeltext. Dess <f t="shared" si="N"/>-element pekar på en mastercell någon annanstans i bladet, och läsaren måste bygga om texten genom att förskjuta masterformeln med rad- och kolumnskillnaden. HotXLS Component för Delphi och C++Builder gör den expansionen vid öppningstillfället, så varje följare rapporterar en fullständig formel
Om du någonsin har laddat en verklig XLSX i ett tredjepartsbibliotek och hittat en kolumn med tusen formler som har text i exakt en cell och tomma strängar i de andra 999, har du mött den här funktionen från fel håll. Ingenting är korrupt. Filen gör vad ECMA-376 låter den göra, och läsaren stannade helt enkelt vid punkten där XML:en stannade
Varför är den delade formelcellen tom?
Därför att formatet avsiktligt lagrar formeln en gång. I ECMA-376 del 1 och ISO/IEC 29500-1 bär <f>-elementet (§18.3.1.40) ett t-attribut av typen ST_CellFormulaType, och värdet shared betyder att den här cellen deltar i en grupp identifierad av si-attributet. Exakt en cell i gruppen, mastern, bär också ett ref-attribut som anger intervallet gruppen gäller för, och bara den cellen bär formeltexten som elementinnehåll. Varje annan cell i gruppen är en följare. Den upprepar t="shared" och samma si, och dess elementinnehåll är tomt. Excel skriver dessa grupper aggressivt, eftersom en neddragning över en 200 000-rads kolumn kollapsar från 200 000 formelsträngar till en sträng plus 199 999 små platshållarelement. Besparingen är verklig och kostnaden landar helt på läsaren: utan expansion har följaren ingen mening på egen hand
Förskjutningen är en översättning, inte en textkopiering
HotXLS löser en följare genom att lokalisera mastern registrerad under samma si, beräkna rad- och kolumndeltat från masterankaret till den nuvarande cellen, och översätta varje referens i masterformeln med det deltat. Relativa dimensioner rör sig, absoluta dimensioner gör det inte, och blandade referenser rör bara sin icke-absoluta halva. Strängliteraler hoppas över helt, så en formel som råkar innehålla texten "A1" behåller den texten oförändrad i varje följare
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-attributet är en grind, inte dekoration. En följare vars koordinater faller utanför masterns tillämpliga intervall expanderas inte, eftersom filen då gör ett anspråk gruppen inte stödjer. På samma sätt, när en förskjutning skulle trycka en referens ovanför rad ett eller till vänster om kolumn A, avger HotXLS #REF! för den token istället för att tyst klämma fast den, vilket är vad Excel självt skulle producera för samma redigering. Den här översättningen är nära besläktad med, men inte samma sak som, referensomskrivningen som sker när du infogar eller tar bort rader. Den vägen har sina egna regler för vad ett intervall gör när en redigering skär genom det, och den beskrivs separat i artikeln om formelreferensjustering vid infogning och borttagning. Delad expansion är enklare: det är en ren offset från ett känt ankare, tillämpad en gång, vid parsningstillfället
Vilka referensformer måste förskjutaren täcka?
Alla, annars är expansionen en dataförlustbugg i förklädnad. En naiv förskjutare som bara förstår A1 och A1:B2 kommer att korrumpera eller tappa de mer exotiska formerna, och riktiga arbetsböcker är fulla av dem. HotXLS delad-formel-översättaren känner igen hela A1-familjen innan den bestämmer vad som ska flyttas. Externa arbetsboksreferenser som [Book.xlsx]Sheet1!A1 och 3D-referenser som Sheet1:Sheet3!A1 behåller sitt prefix intakt medan den avslutande cellreferensen förskjuts. Citerade bladnamn överlever, inklusive det otäcka fallet där bladet bokstavligen heter A1, så 'A1'!A1 förskjuter bara delen efter utropstecknet. Hel kolumn A:A flyttar sin kolumndimension och inget annat; hel rad 1:1 flyttar sin raddimension och inget annat; $A:$A rör sig inte alls. Strukturerade tabellreferenser som Table[A1] lämnas orörda, eftersom den inhägnade delen är ett kolumnnamn, inte 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]Funktionsnamn är den tysta fällan här. En tokenscanner som griper bokstäver följda av siffror kommer glatt att skriva om LOG10 till LOG11 en rad ner. HotXLS kräver en referensgräns före en kandidattoken och efter den, så en identifierare som fortsätter in i en bokstav, siffra, understreck, punkt eller en öppningsparentes är inte en cellreferens. Om du arbetar i den andra notationsfamiljen visar sig samma gränsproblem annorlunda, och artikeln om R1C1-notation täcker var de två modellerna skiljer sig åt
Varför sväljer ett självstängande f-element nästa värde?
Därför att ett självstängande element inte producerar någon slutelementhändelse. Det här är den enskilt dyraste buggen i hela funktionen, och den är inte specifik för någon särskild XML-parser. I TXMLReader väcker <f t="shared" si="4"/> precis en Element-händelse med IsEmptyElement satt till True, och väcker aldrig den matchande EndElement. En parser som stänger sitt formeluppsamlingstillstånd bara vid EndElement stannar därför inuti formeln, och nästa text den ser, som är det cachade resultatet inuti <v>, läggs till i formelbufferten. Värre, tillståndet överlever cellgränsen, så nästa cell som äger en riktig <f> får sin formeltext absorberad av den föregående cellen. Fixen är att avsluta formeltillståndet vid själva Element-händelsen närhelst IsEmptyElement är True, och att köra hela följarupplösningen där istället för att vänta. Det betyder att läsa t, si, ref, aca och ca från attributen, tillämpa den delade expansionen, skriva omräkningsattributen på cellen, och rensa det delade tillståndet, allt inuti grenen som hanterar det tomma elementet. Notera att formatet tillåter båda stavningarna, <f t="shared" si="4"/> och <f t="shared" si="4"></f>, och den andra väcker faktiskt en EndElement. En korrekt läsare måste hantera paret identiskt, vilket är varför HotXLS täcker båda stavningarna i samma regressionsfil
Glesa, oordnade si-värden och kön för väntande poster
si-attributet är ett filgivet osignerat heltal, inte en arrayposition du kontrollerar. Ingenting i schemat kräver att delade index ska vara täta, börja på noll, eller framträda i stigande ordning, och ingenting hindrar en illvillig eller bara ovanlig fil från att använda si="4294967290" på den första cellen. Att dimensionera en uppslagningsarray från det största observerade si är därför en minnesutmattningsprimitiv, inte en optimering. HotXLS håller vägen för öppning av arbetsboken på en sorterad gles tabell istället: delade grupper registreras under sin heltalsnyckel i en sorterad TStringList, vilket gör uppslagning till en binärsökning över hur många grupper som än faktiskt existerar, utan relation till indexens numeriska storlek. Ordning är den andra halvan av problemet. En master föregår normalt sina följare i dokumentordning, men det är en konvention snarare än en regel, så varje följare som inte kan lösa sitt si i det ögonblick den parsas hamnar i en väntande kö. När bladet är klart spelas kön upp mot den nu kompletta tabellen, och de sena mästarna löser sina föräldralösa. Celler som aldrig hittar en master behåller en tom formel, vilket är det ärliga utfallet för en fil som refererar en grupp den aldrig definierade
Att expandera delade formler utan att ladda arbetsboken
De strömmande läsarna möter samma krav under en mycket stramare minnesbudget, och de löser det med en bladlokal tabell. TXLSDirectReader och TXLSRowCursor expanderar båda följare till kompletta formler per cell samtidigt som de bevarar sitt gränsade minne och projektionsbeteende, så en framåt-endast genomgång av ett 300 MB-blad fortfarande ger dig riktig formeltext
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;Två begränsningar faller ut av den designen. För det första kan projektion aldrig hoppa över mastern. Ett radfilter satt med FirstRow och LastRow, eller ett kolumnfilter byggt med IncludeColumn, kan hoppa över att skicka mastercellen till din callback, men parsern måste ändå registrera dess si, ankarkoordinater, tillämpliga intervall och formeltext, annars löser varje följare inuti projektionen till ingenting. Bara följararbetet, förskjutningen och värdeavkodningen, är säkert att hoppa över. För det andra är tabellen per kalkylblad och dess livslängd måste hanteras uttryckligen: TXLSRowCursor håller en instans under hela en bladgenomgång och rensar den vid omstart, bladbyte, filslut, undantag och stängning, så en grupp definierad på blad ett kan aldrig läcka in i blad två. Eftersom den strömmande vägen är en het loop, använder den en öppen-adressering-heltalshash istället för den sorterade strängtabellen, vilket undviker en heltal-till-sträng-konvertering per cell
Vad händer vid sparande, och var gränserna ligger
När en följare väl har expanderats är den en vanlig formel, och HotXLS skriver tillbaka den som ett fristående <f>-element utan t="shared" och utan si. Round-trippen är stabil och det cachade <v>-resultatet överlever, men utdata är större än indata för ett kraftigt delat blad, och grupperingen Excel skapade återskapas inte vid sparande. Om byte-nivå-trohet för de delade grupperna spelar mer roll för dig än att ha riktig formeltext i varje cell, är det den avvägningen du accepterar. XLS-sidan är annorlunda, förresten: BIFF8 SHRFMLA-posten har sin egen kodning och sin egen skrivare, med en delad-grupp-brytare på arbetsboken
Två relaterade saker är uttryckligen inte delade formler även om de delar <f>-elementet. Äldre CSE-array-formler använder t="array" med ett ref som täcker det ankrade intervallet, och dynamiska arrayer använder samma t="array"-stavning men identifieras av ett cm-attribut som kedjar genom cellMetadata till en XLDAPR-post. Att behandla en dynamisk-array-spillcell som en delad eller CSE-följare är en genuin korrekthetsbugg, och separationen täcks i artikeln om dynamisk array och spillformler. Läs de tre fallen som tre parsrar som råkar dela ett taggnamn, och koden förblir ärlig
Den delade formelexpansionen, de strömmande läsarna och referensöversättaren som beskrivs här levereras som en del av HotXLS Excel-komponent för Delphi och C++Builder; produktsidan innehåller den fullständiga formel- och direktläsnings-API-referensen, inklusive projektionsegenskaperna som används ovan