Niemal każda część starszego formatu binarnego programu Excel to pojedynczy rekord z czytelnym dwubajtowym typem i dwubajtową długością. Komórka to LABELSST lub NUMBER. Scalony obszar to MERGEDCELLS. Większość arkusza można przeczytać, przechodząc przez rekordy po kolei i dopasowując je do typu. Tabele przestawne (PivotTables) łamią ten rytm. Pojedyncza tabela przestawna nie jest pojedynczym rekordem – to mały program składający się z kilkudziesięciu współpracujących ze sobą rekordów, rozproszonych w dwóch różnych miejscach tego samego strumienia dokumentu OLE, a relacje między nimi są oparte na pozycji, upakowane bitowo i bezlitosne. Jest to struktura, którą większość czytników BIFF8 albo całkowicie pomija, albo zachowuje jako nieprzezroczyste bajty, ponieważ napisanie jej od zera oznacza odtworzenie każdego odsyłacza skrośnego, jaki utrzymuje sam Excel.
Powodem, dla którego tabela przestawna jest trudna w implementacji, jest to, że składa się z dwóch połączonych artefaktów. Istnieje bufor tabeli przestawnej (pivot cache) – samowystarczalna migawka danych źródłowych z własnym podstrumieniem – oraz widok tabeli, czyli układ określający, które pola znajdują się na której osi. Bufor i widok odwołują się do siebie nawzajem za pomocą indeksu. Pomyl jeden indeks, a plik otworzy się z błędem odświeżania lub po cichu pustą siatką.
Bufor tabeli przestawnej to jej własny podstrumień
Bufor (cache) żyje w strumieniu globals skoroszytu jako kompletny podstrumień BIFF, obramowany rekordem BOF, którego typ dokumentu to 0x0006 (wartość oznaczająca bufor tabeli przestawnej, w przeciwieństwie do 0x0005 dla skoroszytu lub 0x0010 dla arkusza) i zamknięty pasującym rekordem EOF. Wewnątrz tej ramki struktura jest stała. Rekord SXDB to nagłówek bufora. Niesie ze sobą liczbę rekordów, liczbę pól bufora oraz identyfikator strumienia, który widok tabeli przytoczy w celu powiązania się z tym buforem. Każda kolumna źródłowa wnosi następnie rekord definicji pola SXFDB, po którym następuje SXFDBType klasyfikujący go, a następnie unikalne wartości, jakie przybrała ta kolumna, wyemitowane jako jeden rekord elementu określonego typu na każdą unikalną wartość.
Rekordy elementów to miejsce, w którym bufor udowadnia swoją przydatność. Wartość tekstowa staje się rekordem SXSTRING, wartość numeryczna – SXNUM, wartość logiczna – SXBOOLEAN, a błąd formuły – SXERR. Bufor nie przechowuje siatki źródłowej; przechowuje unikalne wartości dla każdego pola plus tabelę indeksów mówiącą, którą unikalną wartość przybrało każde pole w danym rekordzie n. Właśnie dlatego programowe budowanie tabeli przestawnej nie polega na kopiowaniu komórek. Musisz przeskanować zakres źródłowy, wydedukować typ każdego pola na podstawie przechowywanych wartości, usunąć duplikaty w typowaną listę elementów i zapisać każdy wiersz jako krotkę indeksów elementów. HotXLS robi dokładnie to: kolumna zawierająca same liczby jest emitowana z elementami SXNUM, kolumna o typie mieszanym staje się elementami SXSTRING, a daty są przenoszone jako wartości seryjne przez tę samą ścieżkę numeryczną.
SXDBB i pakowanie bitowe, które czyni to interesującym
Tabela indeksów dla każdego rekordu to pojedyncza, najbardziej interesująca technicznie część całej struktury, i znajduje się w rekordzie SXDBB. Naiwne kodowanie przechowywałoby indeks elementu każdego pola jako 16-bitowe słowo. Excel tego nie robi. Pakuje indeks każdego pola dokładnie w taką liczbę bitów, jaka jest wymagana do zaadresowania elementów tego pola, i nic więcej. Szerokość ta wynosi ceil(log2(itemCount + 1)) bitów. To + 1 ma znaczenie: dodatkowa wartość to wartownik (sentinel) oznaczający "puste pole, brak wartości dla tego pola w tym rekordzie", więc pole z trzema unikalnymi elementami musi reprezentować cztery stany, a zatem zajmuje dwa bity, a nie jeden, jak sugerowałyby same trzy elementy. Pole bez żadnych elementów wnosi zero bitów i jest całkowicie pomijane podczas pakowania.
Bity dla jednego rekordu są łączone we wszystkich polach, a następnie kolejny rekord zaczyna się od nowej granicy bajtu. Rekordy są wyrównane do bajtów, a nie pakowane bitowo od początku do końca, co sprawia, że losowy dostęp do tabeli jest wykonalny kosztem kilku bitów dopełnienia na wiersz. Pakowanie w bajcie odbywa się począwszy od najmniej znaczącego bitu (LSB-first). Po zaakceptowaniu tych dwóch reguł enkoder staje się prostą pompą bitową, a dekoder jego lustrzanym odbiciem.
// 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;Powodem, dla którego ten szczegół nie może być zignorowany, jest limit 8224 bajtów dla pojedynczego rekordu BIFF. Każdy rekord w tym formacie (włączając rekordy tabeli przestawnej) musi mieścić swój ładunek w maksymalnie 8224 bajtach, a aktywny bufor tabeli przestawnej z tysiącami wierszy źródłowych przekroczy tę wartość na długo przed wyemitowaniem każdego wiersza. Z tego powodu tabela indeksów jest dzielona. HotXLS ogranicza korpus pojedynczego rekordu SXDBB do 8220 bajtów (limit rekordu 8224 minus czterobajtowy nagłówek typu i długości rekordu), dzieli to przez szerokość bajtową jednego spakowanego rekordu, aby dowiedzieć się, ile całych wierszy się zmieści, a następnie emituje tyle kolejnych rekordów SXDBB, ile wymaga liczba wierszy. Każda kontynuacja zaczyna się czysto od granicy rekordu, dzięki czemu żaden wiersz nie jest przecięty na dwa rekordy. Czytnik znający szerokość bitową każdego rekordu może przechodzić przez każdy kolejny SXDBB tak, jakby były one jedną ciągłą tablicą bitową.
Układ widoku: SXLI dla korpusu, SXPI dla strony
Po zbudowaniu bufora widok tabeli stanowi drugą połowę. Jego rdzeniem są elementy linii osi, czyli wiersze korpusu tabeli przestawnej, które wyliczają każdą kombinację wartości pól wierszy i kolumn rysowanych przez tabelę. Są one przenoszone w rekordach SXLI (typ rekordu 0x00B5, opisany w [MS-XLS] §2.4.275). Jeden rekord SXLI mieści wiele linii, ponownie dopóki limit 8224 bajtów nie wymusi nowego rekordu, i wykorzystuje małą sztuczkę kompresji: każda linia przechowuje tylko to, czym różni się od linii powyżej, wyrażone jako liczba wspólnych prefiksów, dzięki czemu głęboko zagnieżdżona oś nie powtarza zewnętrznych wartości pól w każdym wierszu. Linia sumy końcowej oraz pierwsza linia każdego rekordu zawsze resetują ten licznik prefiksów do zera, dzięki czemu czytnik nigdy nie musi patrzeć wstecz poza granicę rekordu w celu zrekonstruowania linii.
Oś strony, czyli rozwijane filtry znajdujące się nad tabelą przestawną, to oddzielny rekord. SXPI (typ rekordu 0x00B6, [MS-XLS] §2.4.276) niesie jeden dziesięciobajtowy wpis na pole strony: indeks pola przestawnego isxvd, wybrany element bufora iCache, słowo pozycji ipos oraz stary identyfikator obiektu objId. Wartość iCache to ta, na którą należy uważać. Pole strony, które pokazuje "(All)", nie filtrując niczego, przechowuje wartownika 0x7FFD zamiast rzeczywistego indeksu elementu. Programowo zbudowana tabela otwiera się z każdym polem strony ustawionym na "(All)", dopóki wywołujący nie wybierze wcześniej elementu, w którym to momencie indeks tego elementu w buforze zastępuje wartownika, a Excel otwiera się z już zastosowanym filtrem. Obok nich znajdują się rekordy pomocnicze opisujące poszczególne pola i ich formatowanie: SXVD i SXVDEx dla definicji widoku pól, SXIVD dla list indeksów pól porządkujących każdą oś oraz SXFormat dla formatowania liczb, z których każdy odsyła z powrotem do tego samego bufora, do którego odwołują się linie korpusu.
Dwa moduły zapisu w jednym: surowe bloki danych i model typowany
Istnieje strukturalny powód, dla którego HotXLS utrzymuje dwie całkowicie oddzielne ścieżki zapisu tabeli przestawnej, i wynika on wprost z wymogów wierności danych. Gdy skoroszyt jest odczytywany z dysku, jego rekordy tabeli przestawnej zostały zapisane przez program Excel lub inny generator i mogą wykorzystywać warianty rekordów, specyficzne cechy kolejności lub rekordy rozszerzeń, których żaden zewnętrzny moduł zapisu nie modeluje w pełni. Jedyną bezpieczną rzeczą do zrobienia z tymi bajtami jest zwrócenie ich w niezmienionej postaci. Dlatego tabela przestawna pochodząca z pliku ma ustawioną flagę FromRawBlobs = True, a przy zapisie moduł zapisu odtwarza zachowane surowe bloki rekordów (blobs) dosłownie. Nic nie jest generowane ponownie, nic nie jest interpretowane na nowo, a zapis w obie strony jest stabilny na poziomie bajtów.
Tabela przestawna zbudowana przez program to przypadek odwrotny. Nie ma tu oryginalnych bajtów do zachowania, jest tylko typowany model obiektowy: TXLSPivotCache z jego polami i listami elementów oraz TXLSPivotTable z konfiguracją osi. Ta tabela ma flagę FromRawBlobs = False, a moduł zapisu serializuje ją w trudniejszy sposób, emitując świeży podstrumień bufora BOF = 0x0006, pakując tabelę indeksów SXDBB z indeksów elementów przechowywanych przez typowany model i układając rekordy SXLI oraz SXPI na podstawie konfiguracji osi. Flaga ta pozwala obu rodzajom współistnieć w jednym skoroszycie. Bez niej pojedynczy moduł zapisu musiałby albo odrzucić wierność odczytanych tabel, albo odmówić generowania nowych. Wszelkie rekordy rozszerzeń specyficzne dla producenta, które niosła odczytana tabela, są zachowywane jako rekordy uzupełniające, dostępne poprzez listę SupplementalRecords tabeli, dzięki czemu tabela badana przez typowany model nie traci części, których model nie opisuje.
Budowanie tabeli przestawnej w kodzie
Cała powyższa machineria kryje się za jednym wywołaniem. Metoda AddPivotTable przyjmuje zakres źródłowy w notacji A1, komórkę docelową, w której kotwiczy się lewy górny róg tabeli, oraz nazwę. Analizuje zakres, skanuje go w celu wydedukowania typów pól i zbudowania bufora (używając ponownie istniejącego bufora, jeśli inna tabela już wiąże się z tym samym zakresem) i zwraca typowany obiekt TXLSPivotTable z jednym polem na kolumnę źródłową, przy czym każde pole jest początkowo poza osią. Następnie umieszczasz pola na osiach i wybierasz agregację. Sygnatura wygląda dokładnie tak, a bufor, pakowanie SXDBB i rekordy widoku są generowane w czasie zapisu.
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;Pierwszy wiersz zakresu źródłowego jest odczytywany jako nagłówek nazywający pola bufora, więc AddRowField('Region') dopasowuje kolumnę po jej tekście nagłówka, a nie po pozycji. Ponieważ zwracana tabela to model typowany z FromRawBlobs = False, moduł zapisu wybiera ścieżkę tworzenia od zera: buduje samowystarczalny bufor, który nie zależy od tego, czy zakres źródłowy jest nadal obecny w czasie odświeżania, co jest dokładnie cechą, której oczekujesz, gdy tabela przestawna będzie wysyłana do odbiorcy, który może przenieść lub usunąć dane bazowe.
Odczytywanie i uzgadnianie rekordów tabeli przestawnej oraz bufora pliku, którego nie wyprodukowałeś, w tym ścieżki zachowywania surowych bloków (raw blobs), zostało omówione w przewodniku po audycie skoroszytu i konwersji. Gdy zakres źródłowy obejmuje dziesiątki tysięcy wierszy, a strumień SXDBB obejmuje wiele kontynuowanych rekordów, techniki opisane w notatkach o wydajności dużych skoroszytów zapobiegają zdominowaniu czasu wykonania przez budowę bufora. Oba te rozwiązania łączą się z modułem zapisu tabel przestawnych dostarczanym w komponencie HotXLS spreadsheet component dla Delphi i C++Builder, obok interfejsów API do obsługi komórek, formuł, wykresów i formatowania omówionych w innych miejscach tego bloga.