Hampir setiap bagian dari format biner Excel lama adalah satu record tunggal dengan tipe dua byte yang bersih dan panjang dua byte. Sel adalah LABELSST atau NUMBER. Wilayah yang digabungkan adalah MERGEDCELLS. Anda dapat membaca sebagian besar worksheet dengan menelusuri record satu per satu dan mengirimkannya berdasarkan tipe kata (type word). PivotTable merusak ritme tersebut. Satu tabel pivot bukanlah satu record tunggal, melainkan sebuah program kecil yang terdiri dari puluhan record yang bekerja sama, tersebar di dua tempat berbeda dalam stream dokumen senyawa (compound document) OLE yang sama, dan hubungan di antara mereka bersifat posisional, dikemas dalam bit (bit-packed), dan tidak mentoleransi kesalahan. Ini adalah struktur yang sebagian besar pembaca BIFF8 lewati sepenuhnya atau pertahankan sebagai byte buram (opaque bytes), karena menulisnya dari awal berarti mereproduksi setiap referensi silang yang dipelihara oleh Excel itu sendiri.
Alasan mengapa tabel pivot itu sulit adalah karena sebenarnya ada dua artefak yang dilas menjadi satu. Ada cache pivot, cuplikan mandiri (self-contained snapshot) dari data sumber dengan substream-nya sendiri, dan ada tampilan tabel, tata letak yang menyatakan kolom mana yang berada di sumbu mana. Cache dan tampilan merujuk satu sama lain berdasarkan indeks. Jika salah satu indeks salah, file akan terbuka dengan kesalahan penyegaran (refresh error) atau kisi yang kosong secara diam-diam.
Cache pivot adalah substream-nya sendiri
Cache hidup dalam stream global workbook sebagai substream BIFF lengkap, dibingkai oleh record BOF yang tipe dokumennya adalah 0x0006 (nilai yang menandai cache pivot, berbeda dengan 0x0005 untuk workbook atau 0x0010 untuk worksheet) and ditutup oleh EOF yang cocok. Di dalam bingkai itu, strukturnya tetap. Record SXDB adalah header cache. Record ini membawa hitungan record, jumlah kolom cache, dan pengidentifikasi stream yang akan dikutip oleh tampilan tabel untuk mengikat dirinya ke cache ini. Setiap kolom sumber kemudian menyumbangkan record definisi kolom SXFDB diikuti oleh SXFDBType yang mengklasifikasikannya, dan kemudian nilai unik yang diambil kolom tersebut, dikeluarkan sebagai satu record item bertipe per nilai yang berbeda.
Record item adalah tempat cache membuktikan kegunaannya. Nilai teks menjadi SXSTRING, nilai numerik menjadi SXNUM, nilai logis menjadi SXBOOLEAN, dan kesalahan formula menjadi SXERR. Cache tidak menyimpan kisi sumber (source grid), ia menyimpan nilai-nilai unik per kolom ditambah tabel indeks yang menyatakan, untuk record n, item unik mana yang diambil oleh setiap kolom. Itulah mengapa membangun tabel pivot secara terprogram bukan sekadar menyalin sel. Anda harus memindai rentang sumber, menyimpulkan tipe setiap kolom dari nilai yang dikandungnya, mendeduplikasinya menjadi daftar item bertipe, dan mencatat setiap baris sebagai tuple indeks item. HotXLS melakukan hal ini dengan tepat: kolom yang semuanya numerik dikeluarkan dengan item SXNUM, kolom teks campuran menjadi item SXSTRING, dan tanggal dibawa sebagai nilai serial melalui jalur numerik yang sama.
SXDBB dan pengemasan bit (bit-packing) yang membuatnya menarik
Tabel indeks per record is adalah satu-satunya bagian paling unik secara teknis dari seluruh struktur, dan ia berada di record SXDBB. Pengodean naif akan menyimpan indeks item setiap kolom sebagai kata (word) 16-bit. Excel tidak melakukan itu. Excel mengemas indeks setiap kolom ke dalam jumlah bit yang tepat yang diperlukan untuk menangani item kolom tersebut, tidak kurang dan tidak lebih. Lebarnya adalah bit ceil(log2(itemCount + 1)). Nilai + 1 sangat penting: nilai ekstra tersebut adalah sentinel yang berarti "kosong, tidak ada nilai untuk kolom ini dalam record ini", sehingga kolom dengan tiga item unik perlu merepresentasikan empat keadaan dan oleh karena itu mengambil dua bit, bukan satu bit seperti yang disarankan oleh tiga item saja. Kolom tanpa item sama sekali menyumbang nol bit dan dilewati sepenuhnya selama pengemasan.
Bit untuk satu record digabungkan (concatenated) di seluruh kolom, lalu record berikutnya dimulai pada batas byte baru. Record diselaraskan dengan byte (byte-aligned), tidak dikemas dalam bit dari ujung ke ujung, yang membuat akses acak ke tabel dapat ditangani dengan konsekuensi beberapa bit bantalan (padding) per baris. Pengemasan dalam byte adalah bit paling tidak signifikan (least-significant-bit) terlebih dahulu. Setelah Anda menerima kedua aturan tersebut, enkoder adalah pompa bit langsung, dan dekoder adalah cerminannya.
// 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;Alasan mengapa detail ini tidak dapat diabaikan adalah batas atas 8224-byte pada satu record BIFF tunggal. Setiap record dalam format tersebut, termasuk record pivot, harus memuat payload-nya paling banyak 8224 byte, dan cache pivot yang sibuk dengan ribuan baris sumber akan melampaui batas itu jauh sebelum mengeluarkan setiap baris. Jadi tabel indeks dibagi. HotXLS membatasi badan SXDBB tunggal pada 8220 byte, yang merupakan batas record 8224 dikurangi header record empat byte tipe dan panjang, membaginya dengan lebar byte dari satu record terkemas untuk mengetahui berapa banyak baris utuh yang muat, lalu mengeluarkan record SXDBB lanjutan sebanyak yang diminta oleh jumlah baris. Setiap kelanjutan dimulai kembali secara bersih pada batas record, sehingga tidak ada baris yang terpotong di dua record. Pembacaan yang mengetahui lebar bit per-record dapat melangkah melalui setiap SXDBB secara berurutan seolah-olah mereka adalah satu array bit yang berdekatan.
Tata letak tampilan: SXLI untuk badan, SXPI untuk halaman
Sumbu halaman, dropdown filter yang berada di atas tabel pivot, adalah record terpisah. SXPI (tipe record 0x00B6, [MS-XLS] §2.4.276) membawa satu entri sepuluh byte per kolom halaman: indeks kolom pivot isxvd, item cache terpilih iCache, kata posisi ipos, dan id objek lama objId. Nilai iCache adalah nilai yang perlu diperhatikan. Kolom halaman yang menunjukkan "(All)", tidak memfilter apa pun, menyimpan sentinel 0x7FFD daripada indeks item yang sebenarnya. Pivot yang dibangun secara terprogram terbuka dengan setiap kolom halaman disetel ke "(All)" hingga pemanggil memilih item terlebih dahulu, pada titik mana indeks cache item tersebut menggantikan sentinel dan Excel terbuka dengan filter yang sudah diterapkan. Di samping ini terdapat record pendukung yang menjelaskan kolom individual dan pemformatannya, SXVD dan SXVDEx untuk definisi tampilan kolom, SXIVD untuk daftar indeks kolom yang mengurutkan setiap sumbu, dan SXFormat untuk pemformatan angka, masing-masing mengindeks kembali ke cache yang sama dengan yang dirujuk oleh baris badan.
Dua penulis dalam satu: blob mentah dan model bertipe
Ada alasan struktural mengapa HotXLS mempertahankan dua jalur yang sepenuhnya terpisah untuk menulis tabel pivot, dan itu datang langsung dari tuntutan ketepatan (fidelity). Ketika workbook dibaca dari disk, record pivot-nya ditulis oleh Excel atau produsen lain, dan mereka mungkin menggunakan varian record, keunikan pengurutan, atau record ekstensi yang tidak dimodelkan sepenuhnya oleh penulis pihak ketiga. Satu-satunya hal aman yang dapat dilakukan dengan byte tersebut adalah mengembalikannya tanpa diubah. Jadi tabel pivot yang masuk dari file ditandai FromRawBlobs = True, dan saat disimpan, penulis memutar ulang blob record yang dipertahaman secara verbatim. Tidak ada yang dibuat ulang, tidak ada yang ditafsirkan ulang, dan proses pulang-pergi melalui buka dan simpan bersifat byte-stabil.
Tabel pivot yang dibangun oleh program adalah kasus sebaliknya. Tidak ada byte asli yang harus dipertahankan, hanya model objek bertipe: TXLSPivotCache dengan kolom dan daftar itemnya, dan TXLSPivotTable dengan penetapan sumbunya. Tabel tersebut ditandai FromRawBlobs = False, dan penulis menserialisasikannya dengan cara yang sulit, mengeluarkan substream cache BOF = 0x0006 yang baru, mengemas tabel indeks SXDBB dari indeks item yang dipegang model bertipe, dan meletakkan record SXLI dan SXPI dari konfigurasi sumbu. Flag tersebut adalah apa yang memungkinkan kedua jenis tersebut berdampingan dalam satu workbook. Tanpanya, penulis tunggal harus membuang ketepatan tabel yang dibaca atau menolak untuk menghasilkan yang baru. Record ekstensi khusus produsen apa pun yang dibawa oleh tabel yang dibaca disimpan sebagai record tambahan, dapat dijangkau melalui daftar SupplementalRecords tabel, sehingga tabel yang diperiksa melalui model bertipe tidak kehilangan bagian yang tidak dijelaskan oleh model tersebut.
Membangun tabel pivot dalam kode
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;Baris pertama dari rentang sumber dibaca sebagai header yang menamai kolom cache, sehingga AddRowField('Region') cocok dengan kolom berdasarkan teks headernya daripada berdasarkan posisi. Karena tabel yang dikembalikan adalah model bertipe dengan FromRawBlobs = False, penulis mengambil jalur dari awal: ia membangun cache mandiri yang tidak bergantung pada rentang sumber yang masih ada pada waktu penyegaran (refresh), yang merupakan properti yang Anda inginkan ketika pivot akan dikirim ke penerima yang mungkin memindahkan atau menghapus data dasar.
Membaca dan merekonsiliasi record pivot dan cache dari file yang tidak Anda buat, termasuk jalur pelestarian raw-blob, dibahas dalam panduan audit workbook dan workbench konversi. Ketika rentang sumber mencapai puluhan ribu baris dan stream SXDBB mencakup banyak record lanjutan, teknik dalam catatan kinerja workbook besar menjaga agar pembuatan cache tidak mendominasi runtime Anda. Keduanya berpasangan dengan penulis pivot yang dikirimkan sebagai bagian dari komponen spreadsheet HotXLS untuk Delphi dan C++Builder, bersama dengan API sel, formula, grafik, dan pemformatan yang dibahas di bagian lain di blog ini.