Fast jeder Teil des alten binären Excel-Formats ist ein einzelner Datensatz mit einem sauberen Zwei-Byte-Typ und einer Zwei-Byte-Länge. Eine Zelle ist ein LABELSST oder eine NUMBER. Ein verbundener Bereich ist ein MERGEDCELLS. Sie können den größten Teil eines Arbeitsblatts lesen, indem Sie die Datensätze einzeln durchgehen und anhand des Typworts verzweigen. PivotTables brechen diesen Rhythmus. Eine einzelne Pivot-Tabelle ist kein Datensatz, sondern ein kleines Programm aus Dutzenden kooperierenden Datensätzen, die auf zwei verschiedene Stellen im selben OLE-Compound-Dokumentstream verteilt sind. Die Beziehungen zwischen ihnen sind positionsabhängig, bitgepackt und unbarmherzig. Dies ist die Struktur, die die meisten BIFF8-Reader entweder ganz überspringen oder als opake Bytes aufbewahren, da das Schreiben einer solchen Tabelle von Grund auf die Nachbildung aller Querverweise erfordert, die Excel selbst unterhält
Der Grund, warum eine Pivot-Tabelle schwierig ist, liegt darin, dass es sich eigentlich um zwei miteinander verschweißte Artefakte handelt. Es gibt den Pivot-Cache, eine in sich geschlossene Momentaufnahme der Quelldaten mit einem eigenen Substream, und es gibt die Tabellenansicht, also das Layout, das festlegt, welche Felder auf welcher Achse liegen. Der Cache und die Ansicht verweisen per Index aufeinander. Wenn Sie einen Index falsch setzen, öffnet sich die Datei mit einem Aktualisierungsfehler oder einem leeren Raster
Der Pivot-Cache ist ein eigener Substream
Der Cache befindet sich im workbook globals stream als vollständiger BIFF-Substream, der von einem BOF-Datensatz umrahmt wird, dessen Dokumenttyp 0x0006 ist (der Wert, der einen Pivot-Cache kennzeichnet, im Gegensatz zu 0x0005 für das Workbook oder 0x0010 für ein Arbeitsblatt), und wird durch das passende EOF geschlossen. Innerhalb dieses Rahmens ist die Struktur fest vorgegeben. Ein SXDB-Datensatz ist der Cache-Header. Er trägt die Anzahl der Datensätze, die Anzahl der Cache-Felder und den Stream-Identifikator, den die Tabellenansicht angibt, um sich an diesen Cache zu binden. Jede Quellspalte steuert dann einen SXFDB-Felddefinitionsdatensatz bei, gefolgt von einem SXFDBType, der sie klassifiziert, und schließlich die eindeutigen Werte, die diese Spalte angenommen hat, ausgegeben als ein typisiertes Elementdatensatz pro eindeutigem Wert
Die Elementdatensätze sind der Ort, an dem der Cache seinen Zweck erfüllt. Ein Textwert wird zu einem SXSTRING, ein numerischer Wert zu einem SXNUM, ein logischer Wert zu einem SXBOOLEAN und ein Formelfehler zu einem SXERR. Der Cache speichert nicht das Quellraster, sondern die eindeutigen Werte pro Feld plus eine Index-Tabelle, die für den Datensatz n angibt, welches eindeutige Element jedes Feld angenommen hat. Deshalb ist das programmgesteuerte Erstellen einer Pivot-Tabelle keine Frage des Kopierens von Zellen. Sie müssen den Quellbereich scannen, den Typ jedes Felds aus den darin enthaltenen Werten ableiten, sie in einer typisierten Elementliste deduplizieren und jede Zeile als Tupel von Elementindizes aufzeichnen. HotXLS tut genau dies: Eine rein numerische Spalte wird mit SXNUM-Elementen ausgegeben, eine gemischte Textspalte wird zu SXSTRING-Elementen und Datumsangaben werden als serielle Werte über denselben numerischen Pfad übertragen
SXDBB und das Bit-Packing, das es interessant macht
Die Index-Tabelle pro Datensatz ist der technisch kurioseste Teil der gesamten Struktur und befindet sich im SXDBB-Datensatz. Die naive Codierung würde den Elementindex jedes Feldes als 16-Bit-Wort speichern. Excel tut das nicht. Es packt den Index jedes Feldes in genau die Anzahl von Bits, die zur Adressierung der Elemente dieses Feldes erforderlich ist, und nicht mehr. Die Breite beträgt ceil(log2(itemCount + 1)) Bits. Das + 1 ist wichtig: Der zusätzliche Wert ist ein Wächter mit der Bedeutung „leer, kein Wert für dieses Feld in diesem Datensatz". Ein Feld mit drei eindeutigen Elementen muss also vier Zustände darstellen können und benötigt daher zwei Bits und nicht das eine Bit, das drei Elemente allein vermuten ließen. Ein Feld ohne Elemente trägt null Bits bei und wird beim Packen komplett übersprungen
Die Bits für einen Datensatz werden über alle Felder hinweg verkettet, dann beginnt der nächste Datensatz an einer neuen Bytegrenze. Datensätze sind Byte-ausgerichtet und nicht Ende-zu-Ende bitgepackt, was den wahlfreien Zugriff auf die Tabelle auf Kosten einiger Padding-Bits pro Zeile handhabbar macht. Das Packen innerhalb eines Bytes erfolgt beginnend mit dem niederwertigsten Bit (least-significant-bit first). Sobald man diese beiden Regeln akzeptiert, ist der Encoder eine einfache Bit-Pumpe und der Decoder sein Spiegelbild
// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
capacity: Integer;
begin
Result := 0;
if itemCount <= 0 then
Exit; // empty field contributes zero bits
Result := 1;
capacity := 2;
while capacity < itemCount + 1 do
begin
Inc(Result);
capacity := capacity * 2;
end;
end;Der Grund, warum dieses Detail nicht ignoriert werden kann, ist die Grenze von 8224 Byte für einen einzelnen BIFF-Datensatz. Jeder Datensatz im Format, einschließlich der Pivot-Datensätze, muss seine Nutzlast in höchstens 8224 Byte unterbringen, und ein vielgenutzter Pivot-Cache mit Tausenden von Quellzeilen wird diese Grenze sprengen, lange bevor er jede Zeile ausgegeben hat. Daher wird die Index-Tabelle aufgeteilt. HotXLS begrenzt einen einzelnen SXDBB-Körper auf 8220 Byte (das 8224-Datensatzlimit abzüglich des vier Byte langen Datensatz-Headers für Typ und Länge), teilt dies durch die Bytebreite eines gepackten Datensatzes, um zu ermitteln, wie viele ganze Zeilen hineinpassen, und gibt dann so viele fortlaufende SXDBB-Datensätze aus, wie die Zeilenanzahl erfordert. Jede Fortsetzung beginnt sauber an einer Datensatzgrenze, sodass keine Zeile jemals über zwei Datensätze hinweg abgeschnitten wird. Ein Lader, der die Bitbreite pro Datensatz kennt, kann alle SXDBB nacheinander wie ein einziges zusammenhängendes Bit-Array durchlaufen
Das Ansichten-Layout: SXLI für den Körper, SXPI für die Seite
Wenn der Cache aufgebaut ist, bildet die Tabellenansicht die zweite Hälfte. Ihr Kern sind die Achsenzeilenelemente (Axis Line Items), die Zeilen des Pivot-Körpers, die jede Kombination von Zeilen- und Spaltenfeldwerten enthalten, die die Tabelle zeichnet. Diese werden in SXLI-Datensätzen übertragen (Datensatztyp 0x00B5, beschrieben in [MS-XLS] §2.4.275). Ein SXLI enthält viele Zeilen, wiederum bis das Limit von 8224 Byte einen neuen Datensatz erzwingt. Es nutzt einen kleinen Komprimierungstrick: Jede Zeile speichert nur, wie sie sich von der darüber liegenden Zeile unterscheidet, ausgedrückt als Anzahl gemeinsamer Präfixe. So wiederholt eine tief verschachtelte Achse nicht bei jeder Zeile die Werte der äußeren Felder. Die Gesamtsummenzeile und die erste Zeile eines Datensatzes setzen diesen Präfixzähler immer auf Null zurück, sodass ein Reader nie über eine Datensatzgrenze zurückblicken muss, um eine Zeile zu rekonstruieren
Die Seitenachse, also die Filter-Dropdowns oberhalb einer Pivot-Tabelle, ist ein separater Datensatz. SXPI (Datensatztyp 0x00B6, [MS-XLS] §2.4.276) trägt einen zehn Byte langen Eintrag pro Seitenfeld: den Pivot-Feldindex isxvd, das ausgewählte Cache-Element iCache, ein Positionswort ipos und eine alte Objekt-ID objId. Der iCache-Wert ist derjenige, auf den man achten muss. Ein Seitenfeld, das „(Alle)" anzeigt und nichts filtert, speichert den Wächter 0x7FFD anstelle eines echten Elementindexes. Eine programmgesteuert erstellte Pivot-Tabelle öffnet sich mit jedem Seitenfeld auf „(Alle)" eingestellt, bis der Aufrufer ein Element vorab auswählt. An diesem Punkt ersetzt der Cache-Index dieses Elements den Wächter und Excel öffnet sich mit dem bereits angewendeten Filter. Daneben befinden sich die unterstützenden Datensätze, die die einzelnen Felder und deren Formatierung beschreiben: SXVD und SXVDEx für Feldansichtsdefinitionen, SXIVD für die Feldindexlisten, die jede Achse ordnen, und SXFormat für die Zahlenformatierung, wobei jeder auf denselben Cache verweist, den die Körperzeilen referenzieren
Zwei Schreiber in einem: Rohe Blobs und das typisierte Modell
Es gibt einen strukturellen Grund, warum HotXLS zwei völlig getrennte Pfade zum Schreiben einer Pivot-Tabelle bereithält, und dieser ergibt sich direkt aus den Anforderungen an die Originaltreue. Wenn ein Workbook von der Festplatte gelesen wird, seine Pivot-Datensätze wurden von Excel oder einem anderen Programm geschrieben, und sie können Datensatzvarianten, Anordnungsbesonderheiten oder Erweiterungsdatensätze verwenden, die kein Drittanbieter-Schreiber vollständig abbildet. Das einzig Sichere, was man mit diesen Bytes tun kann, ist, sie unverändert zurückzugeben. Daher wird eine Pivot-Tabelle, die aus einer Datei stammt, mit FromRawBlobs = True gekennzeichnet, und beim Speichern spielt der Schreiber die erhaltenen Datensatz-Blobs wortwörtlich wieder ab. Nichts wird neu generiert, nichts wird neu interpretiert, und ein Round-Trip über Öffnen und Speichern ist byte-stabil
Eine vom Programm erstellte Pivot-Tabelle ist der umgekehrte Fall. Es gibt keine Originalbytes zu erhalten, sondern nur das typisierte Objektmodell: einen TXLSPivotCache mit seinen Feldern und Elementlisten und eine TXLSPivotTable mit ihren Achsenzuweisungen. Diese Tabelle wird mit FromRawBlobs = False gekennzeichnet, und der Schreiber serialisiert sie auf dem schwierigen Weg. Er gibt einen neuen BOF = 0x0006-Cache-Substream aus, packt die SXDBB-Index-Tabelle aus den Elementindizes des typisierten Modells und legt die SXLI- und SXPI-Datensätze entsprechend der Achsenkonfiguration an. Das Flag ermöglicht es, dass beide Arten in einem Workbook nebeneinander existieren. Ohne dieses müsste ein einziger Schreiber entweder die Originaltreue eingelesener Tabellen verwerfen oder sich weigern, neue zu generieren. Alle herstellerspezifischen Erweiterungsdatensätze, die eine eingelesene Tabelle trug, werden als ergänzende Datensätze in der SupplementalRecords-Liste der Tabelle aufbewahrt, sodass eine durch das typisierte Modell untersuchte Tabelle nicht die Teile verliert, die das Modell nicht beschreibt
Erstellen einer Pivot-Tabelle im Code
Die gesamte oben beschriebene Maschinerie verbirgt sich hinter einem einzigen Aufruf. AddPivotTable nimmt den Quellbereich in A1-Notation, die Zielzelle, an der die obere linke Ecke der Tabelle verankert wird, und einen Namen entgegen. Es parst den Bereich, scannt ihn, um Feldtypen abzuleiten und den Cache aufzubauen (wobei ein vorhandener Cache wiederverwendet wird, wenn sich eine andere Tabelle bereits an denselben Bereich bindet), und gibt eine typisierte TXLSPivotTable mit einem Feld pro Quellspalte zurück, wobei jedes Feld anfangs außerhalb der Achsen liegt. Sie platzieren dann Felder auf den Achsen und wählen eine Aggregation. Die Signatur sieht genau so aus, und der Cache, das SXDBB-Packen und die Ansichtsdatensätze werden beim Speichern automatisch für Sie erstellt
uses
lxHandle, lxPivot;
var
Book : TXLSWorkbook;
Sheet: IXLSWorkSheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('Sales.xls');
Sheet := Book.Sheets[1];
// Source A1:E500 on 'Data'; anchor the pivot at row 3, col 1.
Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
if Pivot <> nil then
begin
Pivot.AddRowField('Region');
Pivot.AddColumnField('Quarter');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
end;
Book.SaveAs('Sales-Pivot.xls');
finally
Book.Free;
end;
end;Die erste Zeile des Quellbereichs wird als Header gelesen, der die Cache-Felder benennt, sodass AddRowField('Region') eine Spalte nach ihrem Header-Text und nicht nach ihrer Position abgleicht. Da die zurückgegebene Tabelle ein typisiertes Modell mit FromRawBlobs = False ist, wählt der Schreiber den Pfad von Grund auf: Er baut einen in sich geschlossenen Cache auf, der nicht darauf angewiesen ist, dass der Quellbereich beim Aktualisieren noch vorhanden ist. Dies ist genau die Eigenschaft, die Sie wünschen, wenn die Pivot-Tabelle an einen Empfänger gesendet wird, der die zugrundeliegenden Daten verschieben oder löschen könnte
Das Lesen und Abgleichen der Pivot- und Cache-Datensätze einer Datei, die Sie nicht selbst erstellt haben, einschließlich des Pfads zur Erhaltung roher Blobs, wird in the workbook audit and conversion workbench walkthrough behandelt. Wenn der Quellbereich Zehntausende von Zeilen umfasst und der SXDBB-Stream viele fortlaufende Datensätze umfasst, verhindern die Techniken in den Performance-Notizen für große Workbooks, dass der Cache-Aufbau Ihre Laufzeit dominiert. Beide passen hervorragend zum Pivot-Schreiber, der in der HotXLS-Tabellenkalkulationskomponente für Delphi und C++Builder zusammen mit den an anderer Stelle auf diesem Blog behandelten Zellen-, Formel-, Diagramm- und Formatierungs-APIs ausgeliefert wird