Pisanje BIFF8 PivotTable tablica u Delphi-ju: SXDB i SXLI

Gotovo svaki dio naslijeđenog Excel binarnog formata je jedan zapis s čistim dvobajtnim tipom i dvobajtnom duljinom. Ćelija je LABELSST ili NUMBER. Spojeno područje je MERGEDCELLS. Većinu radnog lista možete pročitati prolazeći kroz zapise jedan po jedan i šaljući ih na temelju riječi tipa. PivotTable tablice prekidaju taj ritam. Jedna pivot tablica nije zapis, to je mali program sastavljen od desetaka suradničkih zapisa raspoređenih na dva različita mjesta u istom toku OLE složenog dokumenta, a odnosi između njih su pozicijski, bit-pakirani i nemilosrdni. To je struktura koju većina čitača BIFF8 ili u potpunosti preskače ili čuva kao neprozirne bajtove, jer pisanje jedne ispočetka znači reproduciranje svake unakrsne reference koju sam Excel održava

Razlog zašto je pivot tablica teška jest taj što su to zapravo dva artefakta spojena zajedno. Postoji pivot predmemorija, samostalna snimka izvornih podataka s vlastitim podtokom, i postoji prikaz tablice, izgled koji govori koja polja sjede na kojoj osi. Predmemorija i prikaz upućuju jedan na drugi putem indeksa. Pogriješite li jedan indeks, datoteka se otvara uz pogrešku osvježavanja ili tiho praznu mrežu

Pivot predmemorija je zaseban podtok

Predmemorija živi u toku globalnih varijabli radne knjige kao potpuni BIFF podtok, uokviren zapisom BOF čiji je tip dokumenta 0x0006 (vrijednost koja označava pivot predmemoriju, nasuprot 0x0005 za radnu knjigu ili 0x0010 za radni list) i zatvoren odgovarajućim EOF. Unutar tog okvira struktura je fiksna. Zapis SXDB je zaglavlje predmemorije. On nosi broj zapisa, broj polja predmemorije i identifikator toka koji će prikaz tablice citirati kako bi se povezao s ovom predmemorijom. Svaki izvorni stupac zatim doprinosi zapisom definicije polja SXFDB praćenim tipom SXFDBType koji ga klasificira, a zatim jedinstvenim vrijednostima koje je taj stupac poprimio, emitiranim kao jedan tipizirani zapis stavke po jedinstvenoj vrijednosti

Zapisi stavki su mjesto gdje predmemorija opravdava svoj rad. Tekstualna vrijednost postaje SXSTRING, numerička vrijednost SXNUM, logička vrijednost SXBOOLEAN, a pogreška formule SXERR. Predmemorija ne pohranjuje izvornu mrežu, već pohranjuje jedinstvene vrijednosti po polju plus tablicu indeksa koja govori, za zapis n, koju je jedinstvenu stavku svako polje poprimilo. Zato izgradnja pivot tablice programski nije stvar kopiranja ćelija. Morate skenirati izvorni raspon, zaključiti tip svakog polja na temelju vrijednosti koje sadrži, ukloniti duplikate u tipizirani popis stavki i zabilježiti svaki redak kao torku indeksa stavki. HotXLS radi upravo to: potpuno numerički stupac emitira se sa stavkama SXNUM, stupac s miješanim tekstom postaje stavka SXSTRING, a datumi se prenose kao serijske vrijednosti kroz istu numeričku putanju

SXDBB i pakiranje bitova koje ga čini zanimljivim

Tablica indeksa po zapisu tehnički je najzanimljiviji dio cijele strukture, a živi u zapisu SXDBB. Naivno kodiranje bi pohranilo indeks stavke svakog polja kao 16-bitnu riječ. Excel to ne radi. On pakira indeks svakog polja u točno onaj broj bitova koji je potreban za adresiranje stavki tog polja, i ništa više. Širina je ceil(log2(itemCount + 1)) bitova. Vrijednost + 1 je važna: dodatna vrijednost je sentinel koji znači "prazno, nema vrijednosti za ovo polje u ovom zapisu", pa polje s tri jedinstvene stavke treba predstavljati četiri stanja i stoga uzima dva bita, a ne jedan bit koji bi same tri stavke sugerirale. Polje bez stavki uopće pridonosi s nula bitova i u potpunosti se preskače tijekom pakiranja

Bitovi za jedan zapis spajaju se kroz sva polja, a zatim sljedeći zapis počinje na novoj granici bajta. Zapisi su poravnani po bajtovima, a ne pakirani po bitovima s kraja na kraj, što čini nasumični pristup tablici izvedivim uz cijenu nekoliko bitova podstave po retku. Pakiranje unutar bajta ide od najmanje značajnog bita prema naprijed. Jednom kada prihvatite ta dva pravila, enkoder je jednostavna pumpa bitova, a dekoder je njegovo zrcalo

// Širina indeksa jednog polja u toku SXDBB.
// citmTotal jedinstvenih stavki treba ceil(log2(citmTotal + 1)) bitova,
// pri čemu +1 rezervira sentinel vrijednost "prazno".
function BitsForFieldItems(itemCount: Integer): Integer;
var
  capacity: Integer;
begin
  Result := 0;
  if itemCount <= 0 then
    Exit;            // prazno polje pridonosi s nula bitova
  Result := 1;
  capacity := 2;
  while capacity < itemCount + 1 do
  begin
    Inc(Result);
    capacity := capacity * 2;
  end;
end;

Razlog zašto se ovaj detalj ne može zanemariti je gornja granica od 8224 bajta na jednom BIFF zapisu. Svaki zapis u formatu, uključujući pivot zapise, mora uklopiti svoj korisni teret u najviše 8224 bajta, a aktivna pivot predmemorija s tisućama izvornih redaka preletjet će to davno prije nego što emitira svaki redak. Zato je tablica indeksa podijeljena. HotXLS ograničava jedno tijelo SXDBB na 8220 bajtova, što je limit zapisa od 8224 minus četverobajtno zaglavlje zapisa tipa i duljine, dijeli to sa širinom bajta jednog pakiranog zapisa kako bi saznao koliko cijelih redaka stane, a zatim emitira onoliko nastavaka zapisa SXDBB koliko to broj redaka zahtijeva. Svaki nastavak počinje čisto na granici zapisa, tako da nijedan redak nikada nije presječen na dva zapisa. Čitač koji zna širinu bita po zapisu može proći kroz svaki SXDBB redom kao da se radi o jednom neprekinutom nizu bitova

Izgled prikaza: SXLI za tijelo, SXPI za stranicu

S izgrađenom predmemorijom, prikaz tablice je druga polovica. Njezina srž su stavke linije osi, redovi tijela pivota koji nabrajaju svaku kombinaciju vrijednosti polja redaka i polja stupaca koje tablica iscrtava. Oni se prenose u zapisima SXLI (tip zapisa 0x00B5, opisan u [MS-XLS] §2.4.275). Jedan SXLI drži mnogo linija, opet dok limit od 8224 bajta ne nametne novi zapis, i koristi mali trik kompresije: svaka linija pohranjuje samo kako se razlikuje od linije iznad nje, izraženo kao broj zajedničkih prefiksa, tako da duboko ugniježđena os ne ponavlja vrijednosti vanjskih polja u svakom retku. Linija sveukupnog zbroja i prva linija bilo kojeg zapisa uvijek vraćaju taj broj prefiksa na nulu, tako da čitač nikada ne mora gledati unatrag preko granice zapisa kako bi rekonstruirao liniju

Os stranice, padajući izbornici filtara koji stoje iznad pivot tablice, zaseban je zapis. SXPI (tip zapisa 0x00B6, [MS-XLS] §2.4.276) nosi jedan deseterobajtni unos po polju stranice: indeks pivot polja isxvd, odabranu stavku predmemorije iCache, riječ pozicije ipos i naslijeđeni ID objekta objId. Vrijednost iCache je ona na koju treba paziti. Polje stranice koje prikazuje "(All)", ne filtrirajući ništa, pohranjuje sentinel 0x7FFD umjesto stvarnog indeksa stavke. Programski izgrađen pivot otvara se sa svakim poljem stranice postavljenim na "(All)" dok pozivatelj unaprijed ne odabere stavku, na kojoj točki indeks predmemorije te stavke zamjenjuje sentinel i Excel se otvara s već primijenjenim filtrom. Uz njih stoje prateći zapisi koji opisuju pojedinačna polja i njihovo oblikovanje, SXVD and SXVDEx za definicije prikaza polja, SXIVD za popise indeksa polja koji uređuju svaku os i SXFormat za oblikovanje brojeva, od kojih svaki indeksira natrag u istu predmemoriju na koju se odnose linije tijela

Dva pisca u jednom: sirovi blobovi i tipizirani model

Postoji strukturni razlog zašto HotXLS čuva dvije potpuno odvojene putanje za pisanje pivot tablice, a on dolazi izravno iz zahtjeva za vjernošću. Kada se radna knjiga čita s diska, njezine pivot zapise napisao je Excel ili neki drugi proizvođač, i oni mogu koristiti varijante zapisa, neobičnosti u redoslijedu ili zapise proširenja koje nijedan pisac treće strane ne modelira u potpunosti. Jedina sigurna stvar s tim bajtovima jest vratiti ih nepromijenjene. Stoga je pivot tablica koja je došla iz datoteke označena s FromRawBlobs = True, a pri spremanju pisac doslovno reproducira sačuvane blobove zapisa. Ništa se ne regenerira, ništa se ponovno ne tumači, a kružno putovanje kroz otvaranje i spremanje je bajtovno stabilno

Pivot tablica koju je program izgradio je suprotan slučaj. Nema originalnih bajtova za čuvanje, samo tipizirani objektni model: TXLSPivotCache sa svojim poljima i popisima stavki, te TXLSPivotTable sa svojim dodjelama osi. Ta je tablica označena s FromRawBlobs = False, a pisac je serijalizira na tekući način, emitirajući svježi podtok predmemorije BOF = 0x0006, pakirajući indeksnu tablicu SXDBB iz indeksa stavki koje drži tipizirani model i raspoređujući zapise SXLI i SXPI iz konfiguracije osi. Zastavica je ono što omogućuje objema vrstama da koegzistiraju u jednoj radnoj knjizi. Bez nje bi jedan pisac morao ili odbaciti vjernost učitanih tablica ili odbiti generiranje novih. Svi zapisi proširenja specifični za proizvođača koje je učitana tablica nosila čuvaju se kao dopunski zapisi, dostupni kroz popis tablice SupplementalRecords list, tako da tablica pregledana kroz tipizirani model ne gubi dijelove koje model ne opisuje

Izgradnja pivot tablice u kodu

Sav gornji mehanizam nalazi se iza jednog poziva. AddPivotTable uzima izvorni raspon u bilježenju A1, odredišnu ćeliju na kojoj se sidri gornji lijevi kut tablice i naziv. On analizira raspon, skenira ga kako bi zaključio tipove polja i izgradio predmemoriju (ponovno koristeći postojeću predmemoriju ako se druga tablica već veže na isti raspon) te vraća tipizirani TXLSPivotTable s jednim poljem po izvornom stupcu, pri čemu je svako polje u početku izvan osi. Zatim postavljate polja na osi i birate agregaciju. Potpis je točno ovakav, a predmemorija, pakiranje SXDBB i zapisi prikaza proizvode se za vas u trenutku spremanja

uses
  lxHandle, lxPivot;

var
  Book : TXLSWorkbook;
  Sheet: IXLSWorkSheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('Sales.xls');
    Sheet := Book.Sheets[1];

    // Izvor A1:E500 na listu 'Data'; sidri pivot u retku 3, stupcu 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;

Prvi redak izvornog raspona čita se kao zaglavlje koje imenuje polja predmemorije, pa AddRowField('Region') odgovara stupcu prema tekstu zaglavlja, a ne prema poziciji. Budući da je vraćena tablica tipizirani model s FromRawBlobs = False, pisac ide putem stvaranja ispočetka: gradi samostalnu predmemoriju koja ne ovisi o tome da je izvorni raspon još uvijek prisutan u trenutku osvježavanja, što je upravo svojstvo koje želite kada se pivot šalje primatelju koji može premjestiti ili izbrisati temeljne podatke

Čitanje i usklađivanje pivot zapisa i zapisa predmemorije datoteke koju niste sami proizveli, uključujući putanju očuvanja sirovih blobova, pokriveno je u vodiču za reviziju radne knjige i radni stol za pretvorbu. Kada izvorni raspon doseže desetke tisuća redaka, a tok SXDBB obuhvaća mnogo nastavaka zapisa, tehnike u bilješkama o performansama s velikim radnim knjigama sprječavaju da izgradnja predmemorije dominira vašim vremenom izvođenja. Obje se povezuju s pivot piscem koji se isporučuje u softverskoj komponenti HotXLS Delphi spreadsheet component za Delphi i C++Builder, zajedno s API-jima za ćelije, formule, grafikone i oblikovanje koji su obrađeni drugdje na ovom blogu