Ein Shared-Formula-Follower in XLSX trägt keinen Formeltext. Sein <f t="shared" si="N"/>-Element zeigt auf eine Master-Zelle anderswo im Sheet, und der Reader muss den Text rekonstruieren, indem er die Master-Formel um die Zeilen- und Spaltendifferenz verschiebt. Die HotXLS Component für Delphi und C++Builder führt diese Expansion beim Öffnen durch, sodass jeder Follower eine vollständige Formel meldet
Wer je eine reale XLSX in einer Drittanbieter-Bibliothek geladen und festgestellt hat, dass eine Spalte mit tausend Formeln in genau einer Zelle Text und in den übrigen 999 leere Strings hat, ist diesem Feature von der falschen Seite begegnet. Nichts ist beschädigt. Die Datei tut, was ECMA-376 ihr erlaubt, und der Reader hat schlicht dort aufgehört, wo das XML aufhörte
Warum ist die Shared-Formula-Zelle leer?
Weil das Format die Formel bewusst nur einmal speichert. In ECMA-376 Teil 1 und ISO/IEC 29500-1 trägt das <f>-Element (§18.3.1.40) ein t-Attribut vom Typ ST_CellFormulaType, und der Wert shared bedeutet, dass diese Zelle an einer durch das si-Attribut identifizierten Gruppe teilnimmt. Genau eine Zelle in der Gruppe, der Master, trägt außerdem ein ref-Attribut, das den Bereich angibt, auf den die Gruppe angewendet wird, und nur diese Zelle trägt den Formeltext als Elementinhalt. Jede andere Zelle in der Gruppe ist ein Follower. Sie wiederholt t="shared" und dasselbe si, und ihr Elementinhalt ist leer. Excel schreibt diese Gruppen aggressiv, denn ein Ausfüllen nach unten über eine Spalte mit 200.000 Zeilen kollabiert von 200.000 Formel-Strings zu einem String plus 199.999 winzigen Platzhalterelementen. Die Ersparnis ist real, und die Kosten landen vollständig beim Reader: Ohne Expansion hat der Follower keine eigenständige Bedeutung
Die Verschiebung ist eine Übersetzung, keine Textkopie
HotXLS löst einen Follower auf, indem es den unter demselben si registrierten Master lokalisiert, das Zeilen- und Spaltendelta vom Master-Anker zur aktuellen Zelle berechnet und jede Referenz in der Master-Formel um dieses Delta übersetzt. Relative Dimensionen bewegen sich, absolute nicht, und gemischte Referenzen bewegen nur ihre nicht-absolute Hälfte. Zeichenketten-Literale werden vollständig übersprungen, sodass eine Formel, die zufällig den Text "A1" enthält, diesen Text in jedem Follower unverändert behält
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;Das ref-Attribut ist ein Tor, keine Dekoration. Ein Follower, dessen Koordinaten außerhalb des anwendbaren Master-Bereichs liegen, wird nicht expandiert, denn die Datei würde damit eine Aussage treffen, die die Gruppe nicht stützt. Ebenso emittiert HotXLS #REF! für dieses Token, statt es still zu klemmen, wenn eine Verschiebung eine Referenz über Zeile eins hinaus oder links von Spalte A schieben würde – genau das, was Excel selbst für dieselbe Bearbeitung erzeugen würde. Diese Übersetzung ist eng verwandt mit, aber nicht dasselbe wie das Umschreiben von Referenzen, das beim Einfügen oder Löschen von Zeilen geschieht. Jener Pfad hat seine eigenen Regeln darüber, was ein Bereich tut, wenn eine Bearbeitung ihn durchschneidet, und wird separat im Artikel zur Formel-Referenzanpassung beim Einfügen und Löschen beschrieben. Shared Expansion ist einfacher: Es ist ein reiner Offset von einem bekannten Anker, einmal angewendet, zur Parse-Zeit
Welche Referenzformen muss der Shifter abdecken?
Alle, sonst ist die Expansion ein getarnter Datenverlust-Bug. Ein naiver Shifter, der nur A1 und A1:B2 versteht, wird die exotischeren Formen korrumpieren oder verwerfen, und reale Workbooks sind voll davon. Der HotXLS-Shared-Formula-Übersetzer erkennt die gesamte A1-Familie, bevor er entscheidet, was verschoben wird. Externe Workbook-Referenzen wie [Book.xlsx]Sheet1!A1 und 3D-Referenzen wie Sheet1:Sheet3!A1 behalten ihr Präfix intakt, während sich die nachgestellte Zellreferenz verschiebt. Zitierte Sheet-Namen überleben, einschließlich des unangenehmen Falls, in dem das Sheet wörtlich A1 heißt, sodass 'A1'!A1 nur den Teil nach dem Ausrufezeichen verschiebt. Ganze Spalte A:A verschiebt ihre Spaltendimension und sonst nichts; ganze Zeile 1:1 verschiebt ihre Zeilendimension und sonst nichts; $A:$A bewegt sich überhaupt nicht. Strukturierte Tabellenreferenzen wie Table[A1] bleiben unangetastet, denn der geklammerte Teil ist ein Spaltenname, keine Koordinate
// 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]Funktionsnamen sind hier die stille Falle. Ein Token-Scanner, der Buchstaben gefolgt von Ziffern schnappt, schreibt bereitwillig LOG10 zu LOG11 eine Zeile weiter unten um. HotXLS verlangt eine Referenzgrenze vor und nach einem Kandidaten-Token, sodass ein Bezeichner, der in einen Buchstaben, eine Ziffer, einen Unterstrich, einen Punkt oder eine öffnende Klammer übergeht, keine Zellreferenz ist. Wer in der anderen Notationsfamilie arbeitet, dem begegnet dasselbe Grenzproblem anders, und der Artikel zur R1C1-Notation behandelt, wo die beiden Modelle auseinanderlaufen
Warum verschluckt ein selbstschließendes f-Element den nächsten Wert?
Weil ein selbstschließendes Element kein End-Element-Ereignis erzeugt. Das ist der mit Abstand teuerste Bug im gesamten Feature, und er ist nicht spezifisch für einen bestimmten XML-Parser. In TXMLReader löst <f t="shared" si="4"/> genau ein Element-Ereignis mit IsEmptyElement gleich True aus, und nie das passende EndElement. Ein Parser, der seinen formel-erfassenden Zustand nur bei EndElement schließt, bleibt daher im Formel-Zustand, und der nächste Text, den er sieht – das gecachte Ergebnis innerhalb von <v> – wird an den Formel-Puffer angehängt. Schlimmer noch, der Zustand überlebt die Zellgrenze, sodass die nächste Zelle mit einem echten <f> ihren Formeltext von der vorherigen Zelle absorbiert bekommt. Die Lösung besteht darin, den Formel-Zustand bereits beim Element-Ereignis selbst zu beenden, wann immer IsEmptyElement True ist, und dort die gesamte Follower-Auflösung durchzuführen, statt zu warten. Das bedeutet, t, si, ref, aca und ca aus den Attributen zu lesen, die Shared Expansion anzuwenden, die Neuberechnungs-Attribute auf die Zelle zu schreiben und den Shared-Zustand zu löschen, alles innerhalb des Zweigs, der das leere Element behandelt. Man beachte, dass das Format beide Schreibweisen erlaubt, <f t="shared" si="4"/> und <f t="shared" si="4"></f>, und die zweite löst tatsächlich ein EndElement aus. Ein korrekter Reader muss beide identisch behandeln, weshalb HotXLS beide Schreibweisen in derselben Regressionsdatei abdeckt
Spärliche, unsortierte si-Werte und die Pending-Queue
Das si-Attribut ist eine von der Datei gelieferte vorzeichenlose Ganzzahl, keine Array-Position, die man kontrolliert. Nichts im Schema verlangt, dass Shared-Indizes dicht sind, bei null beginnen oder in aufsteigender Reihenfolge erscheinen, und nichts hindert eine feindliche oder schlicht seltsame Datei daran, si="4294967290" auf der ersten Zelle zu verwenden. Ein Lookup-Array anhand des größten beobachteten si zu dimensionieren ist daher ein Speicher-Erschöpfungs-Primitiv, keine Optimierung. HotXLS hält den Workbook-Open-Pfad stattdessen auf einer sortierten Sparse-Tabelle: Shared-Gruppen werden unter ihrem Ganzzahlschlüssel in einer sortierten TStringList registriert, was den Lookup zu einer binären Suche macht, über wie viele Gruppen auch immer tatsächlich existieren, ohne Bezug zur numerischen Größe der Indizes. Reihenfolge ist die zweite Hälfte des Problems. Ein Master geht seinen Followern normalerweise in Dokumentreihenfolge voraus, aber das ist eine Konvention, keine Regel, sodass jeder Follower, dessen si im Moment des Parsens nicht auflösbar ist, in eine Pending-Queue wandert. Ist das Sheet fertig, wird die Queue gegen die nun vollständige Tabelle abgespielt, und die späten Master lösen ihre Waisen auf. Zellen, die nie einen Master finden, behalten eine leere Formel, was das ehrliche Ergebnis für eine Datei ist, die eine Gruppe referenziert, die sie nie definiert hat
Shared Formulas expandieren, ohne das Workbook zu laden
Die Streaming-Reader stehen vor derselben Anforderung bei einem deutlich engeren Speicherbudget, und sie lösen es mit einer Sheet-lokalen Tabelle. TXLSDirectReader und TXLSRowCursor expandieren beide Follower zu vollständigen Formeln pro Zelle, während sie ihr begrenztes Speicherverhalten und ihre Projektionslogik bewahren, sodass ein reiner Vorwärtsdurchlauf über ein 300-MB-Sheet weiterhin echten Formeltext liefert
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;Aus diesem Design ergeben sich zwei Einschränkungen. Erstens kann die Projektion niemals den Master überspringen. Ein mit FirstRow und LastRow gesetzter Zeilenfilter, oder ein mit IncludeColumn gebauter Spaltenfilter, kann das Ausgeben der Master-Zelle an den eigenen Callback überspringen, aber der Parser muss dennoch dessen si, Ankerkoordinaten, anwendbaren Bereich und Formeltext erfassen, sonst löst jeder Follower innerhalb der Projektion ins Leere auf. Nur die Follower-seitige Arbeit, die Verschiebung und die Wertdekodierung, darf gefahrlos übersprungen werden. Zweitens ist die Tabelle pro Arbeitsblatt, und ihre Lebensdauer muss explizit verwaltet werden: TXLSRowCursor hält eine Instanz für die Dauer eines Sheet-Durchlaufs und leert sie bei Neustart, Sheet-Wechsel, Dateiende, Exception und Schließen, sodass eine auf Sheet eins definierte Gruppe nie in Sheet zwei durchsickern kann. Weil der Streaming-Pfad eine heiße Schleife ist, verwendet er einen Open-Addressing-Ganzzahl-Hash statt der sortierten String-Tabelle, was eine Ganzzahl-zu-String-Konvertierung pro Zelle vermeidet
Was beim Speichern passiert, und wo die Grenzen liegen
Ist ein Follower einmal expandiert, ist er eine gewöhnliche Formel, und HotXLS schreibt ihn als unabhängiges <f>-Element ohne t="shared" und ohne si zurück. Der Round-Trip ist stabil, und die gecachten <v>-Ergebnisse überleben, aber die Ausgabe ist bei einem stark Shared-Sheet größer als die Eingabe, und die von Excel erzeugte Gruppierung wird beim Speichern nicht rekonstruiert. Zählt Byte-genaue Treue der Shared-Gruppen für Sie mehr als echter Formeltext in jeder Zelle, ist das der Tausch, den Sie eingehen. Die XLS-Seite ist übrigens anders: Der BIFF8-SHRFMLA-Datensatz hat seine eigene Kodierung und seinen eigenen Writer, mit einem Shared-Gruppen-Umschalter auf dem Workbook
Zwei verwandte Dinge sind ausdrücklich keine Shared Formulas, obwohl sie sich das <f>-Element teilen. Legacy-CSE-Array-Formeln verwenden t="array" mit einem ref, das den verankerten Bereich abdeckt, und dynamische Arrays verwenden dieselbe t="array"-Schreibweise, werden aber über ein cm-Attribut identifiziert, das über cellMetadata zu einem XLDAPR-Datensatz verkettet. Eine Dynamic-Array-Spill-Zelle als Shared- oder CSE-Follower zu behandeln ist ein echter Korrektheitsfehler, und die Trennung wird im Artikel zu dynamischen Array- und Spill-Formeln behandelt. Liest man die drei Fälle als drei Parser, die zufällig einen Tag-Namen teilen, bleibt der Code ehrlich
Die hier beschriebene Shared-Formula-Expansion, die Streaming-Reader und der Referenzübersetzer sind Teil der HotXLS Excel Component für Delphi und C++Builder; die Produktseite führt die vollständige Formel- und Direct-Read-API-Referenz, einschließlich der oben verwendeten Projektionseigenschaften