Eski Excel ikili biçiminin hemen hemen her parçası, temiz iki baytlık bir türe ve iki baytlık bir uzunluğa sahip tek bir kayıttır. Bir hücre bir LABELSST veya bir NUMBER'dır. Birleştirilmiş bir bölge bir MERGEDCELLS'tir. Kayıtları tek tek dolaşarak ve tip kelimesi üzerinden gönderim yaparak bir çalışma sayfasının çoğunu okuyabilirsiniz. PivotTable'lar bu ritmi bozar. Tek bir pivot tablosu bir kayıt değildir; aynı OLE bileşik belge akışında iki farklı yere yayılmış düzinelerce işbirliği yapan kayıttan oluşan küçük bir programdır ve aralarındaki ilişkiler konumsal, bit paketli ve acımasızdır. Bu, çoğu BIFF8 okuyucusunun ya tamamen atladığı ya da opak baytlar olarak koruduğu yapıdır, çünkü sıfırdan bir tane yazmak Excel'in kendisinin sürdürdüğü her çapraz referansı yeniden üretmek anlamına gelir
Bir pivot tablosunun zor olmasının nedeni, gerçekte birbirine kaynaklanmış iki yapıt olmasıdır. Kendi alt akışına sahip kaynak verilerin bağımsız bir anlık görüntüsü olan pivot önbelleği vardır ve hangi alanların hangi eksende oturduğunu söyleyen tablo görünümü vardır. Önbellek ve görünüm dizine göre birbirine başvurur. Bir dizini yanlış girerseniz, dosya bir yenileme hatasıyla veya sessizce boş bir ızgarayla açılır
Pivot önbelleği kendi başına bir alt akıştır
Önbellek, çalışma kitabı genel akışında eksiksiz bir BIFF alt akışı olarak yaşar; belge türü 0x0006 olan bir BOF kaydıyla (çalışma kitabı için 0x0005 veya çalışma sayfası için 0x0010'un aksine, bir pivot önbelleğini işaret eden değer) çerçevelenir ve eşleşen EOF ile kapatılır. Bu çerçeve içinde yapı sabittir. Bir SXDB kaydı önbellek başlığıdır. Kayıt sayısını, önbellek alanlarının sayısını ve tablo görünümünün kendisini bu önbelleğe bağlamak için alıntı yapacağı akış tanımlayıcısını taşır. Her kaynak sütunu daha sonra bir SXFDB alan tanımlama kaydı ve ardından onu sınıflandıran bir SXFDBType ile katkıda bulunur; ardından o sütunun aldığı benzersiz değerler, farklı değer başına bir yazılmış öğe kaydı olarak yayılır
Öğe kayıtları, önbelleğin değerini kanıtladığı yerdir. Bir metin değeri bir SXSTRING, sayısal bir değer bir SXNUM, mantıksal bir değer bir SXBOOLEAN ve bir formül hatası bir SXERR olur. Önbellek kaynak ızgarayı saklamaz, alan başına belirgin (distinct) değerleri ve n. kayıt için her alanın hangi belirgin öğeyi aldığını söyleyen bir dizin tablosunu saklar. Programlı olarak bir pivot tablosu oluşturmanın hücreleri kopyalamaktan ibaret olmamasının nedeni budur. Kaynak aralığı taramanız, tuttuğu değerlerden her alanın türünü çıkarmanız, bunları yazılmış bir öğe listesinde tekilleştirmeniz ve her satırı öğe dizinlerinin bir grubu (tuple) olarak kaydetmeniz gerekir. HotXLS tam olarak bunu yapar: tamamen sayısal bir sütun SXNUM öğeleriyle yayılır, karışık metinli bir sütun SXSTRING öğeleri olur ve tarihler aynı sayısal yol üzerinden seri değerler olarak taşınır
SXDBB ve onu ilginç kılan bit paketleme
Kayıt başına dizin tablosu, tüm yapının teknik olarak en çok merak edilen tek parçasıdır ve SXDBB kaydında yaşar. Naif kodlama, her alanın öğe dizinini 16 bitlik bir kelime olarak saklardı. Excel bunu yapmaz. Her alanın dizinini, o alanın öğelerini adreslemek için gereken bit sayısına tam olarak paketler, daha fazlasına değil. Genişlik ceil(log2(itemCount + 1)) bittir. + 1 önemlidir: ekstra değer "boş, bu kayıtta bu alan için değer yok" anlamına gelen bir nöbetçidir, bu nedenle üç belirgin öğeye sahip bir alanın dört durumu temsil etmesi gerekir ve bu nedenle yalnızca üç öğenin önereceği tek bit yerine iki bit alır. Hiç öğesi olmayan bir alan sıfır bit katkıda bulunur ve paketleme sırasında tamamen atlanır
Bir kayda ait bitler tüm alanlar boyunca birleştirilir, ardından bir sonraki kayıt taze bir bayt sınırında başlar. Kayıtlar uç uca bit paketli değil, bayt hizalıdır; bu da satır başına birkaç dolgu biti pahasına tabloya rastgele erişimi izlenebilir kılar. Bir bayt içindeki paketleme, önce en önemsiz bit (least-significant-bit first) şeklindedir. Konum iki kuralı kabul ettiğinizde, kodlayıcı basit bir bit pompasıdır ve kod çözücü onun aynasıdır
// 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;Bu detayın göz ardı edilememesinin nedeni, tek bir BIFF kaydındaki 8224 baytlık tavandır. Pivot kayıtları da dahil olmak üzere biçimdeki her kayıt, yükünü en fazla 8224 bayta sığdırmalıdır ve binlerce kaynak satıra sahip meşgul bir pivot önbelleği, her satırı yaymadan çok önce bunu aşacaktır. Bu yüzden dizin tablosu bölünür. HotXLS, tek bir SXDBB gövdesini 8220 bayt ile sınırlar (bu, 8224 kayıt sınırından tür ve uzunluktaki dört baytlık kayıt başlığının çıkarılmasıyla elde edilir), kaç bütün satırın sığacağını öğrenmek için bunu bir paketlenmiş kaydın bayt genişliğine böler ve ardından satır sayısının gerektirdiği kadar devam eden SXDBB kaydı yayar. Her devam kaydı bir kayıt sınırında temiz bir şekilde yeniden başlar, böylece hiçbir satır asla iki kayıt arasında kesilmez. Kayıt başına bit genişliğini bilen bir okuyucu, sanki tek bir bitişik bit dizisiymiş gibi sırayla her SXDBB boyunca ilerleyebilir
Görünüm düzeni: gövde için SXLI, sayfa için SXPI
Önbellek oluşturulduktan sonra, tablo görünümü ikinci yarıdır. Çekirdeği, tablonun çizdiği satır alanı ve sütun alanı değerlerinin her kombinasyonunu listeleyen pivot gövdesinin satırları olan eksen çizgi öğeleridir. Bunlar SXLI kayıtlarında taşınır (kayıt türü 0x00B5, [MS-XLS] §2.4.275'te açıklanmıştır). Bir SXLI, yine 8224 bayt sınırı yeni bir kaydı zorlayana kadar birçok çizgi tutar ve küçük bir sıkıştırma hilesi kullanır: her çizgi, ortak önek sayısı olarak ifade edilen, yalnızca üzerindeki çizgiden nasıl farklı olduğunu saklar; böylece derinden yuvalanmış bir eksen, dış alan değerlerini her satırda tekrarlamaz. Genel toplam çizgisi ve herhangi bir kaydın ilk çizgisi bu önek sayısını her zaman sıfırlayarak bir okuyucunun bir çizgiyi yeniden yapılandırmak için asla bir kayıt sınırının gerisine bakmak zorunda kalmamasını sağlar
Bir pivot tablosunun üzerinde yer alan filtre açılır menüleri olan sayfa ekseni ayrı bir kayıttır. SXPI (kayıt türü 0x00B6, [MS-XLS] §2.4.276), sayfa alanı başına on baytlık bir girdi taşır: pivot alanı dizini isxvd, seçilen önbellek öğesi iCache, bir konum kelimesi ipos ve eski bir nesne kimliği objId. İzlenecek değer iCache değeridir. Hiçbir şeyi filtrelemeyen, "(All)" (Tümü) gösteren bir sayfa alanı, gerçek bir öğe dizini yerine 0x7FFD nöbetçisini saklar. Programlı olarak oluşturulmuş bir pivot, çağıran bir öğeyi önceden seçene kadar her sayfa alanı "(All)" olarak ayarlanmış şekilde açılır; bu noktada o öğenin önbellek dizini nöbetçinin yerini alır ve Excel filtre zaten uygulanmış olarak açılır. Bunların yanında, bireysel alanları ve biçimlendirmelerini açıklayan destekleyici kayıtlar yer alır: alan görünümü tanımları için SXVD ve SXVDEx, her ekseni sıralayan alan dizini listeleri için SXIVD ve sayı biçimlendirme için SXFormat; her biri gövde çizgilerinin başvurduğu aynı önbelleğe geri dizinlenir
Bir arada iki yazar: ham blob'lar ve yazılmış model
HotXLS'in bir pivot tablosu yazmak için tamamen ayrı iki yol tutmasının yapısal bir nedeni vardır ve bu doğrudan sadakat taleplerinden kaynaklanır. Çalışma kitabı diskten okunduğunda, pivot kayıtları Excel veya başka bir üretici tarafından yazılmıştır ve hiçbir üçüncü taraf yazarın tam olarak modelleyemediği kayıt varyantlarını, sıralama tuhaflıklarını veya uzantı kayıtlarını kullanabilirler. Bu baytlarla yapılacak tek güvenli şey, onları değiştirilmeden geri vermektir. Bu nedenle, bir dosyadan gelen bir pivot tablosu FromRawBlobs = True olarak bayraklanır ve kaydetme sırasında yazar, korunan kayıt blob'larını kelimesi kelimesine yeniden oynatır. Hiçbir şey yeniden oluşturulmaz, hiçbir şey yeniden yorumlanmaz ve açma ve kaydetme yoluyla bir gidiş-dönüş bayt kararlıdır
Programın oluşturduğu bir pivot tablosu ise tam tersi durumdur. Korunacak orijinal baytlar yoktur, yalnızca yazılmış nesne modeli vardır: alanları ve öğe listeleriyle bir TXLSPivotCache ve eksen atamalarıyla bir TXLSPivotTable. Bu tablo FromRawBlobs = False olarak bayraklanır ve yazar bunu zor yoldan serileştirir; taze bir BOF = 0x0006 önbellek alt akışı yayar, yazılmış modelin tuttuğu öğe dizinlerinden SXDBB dizin tablosunu paketler ve eksen yapılandırmasından SXLI ve SXPI kayıtlarını düzenler. Bayrak, her iki türün de tek bir çalışma kitabında bir arada var olmasını sağlayan şeydir. Bu olmasaydı, tek bir yazar ya okunan tabloların sadakatini bir kenara bırakmak zorunda kalır ya da yenilerini oluşturmayı reddederdi. Okunan bir tablonun taşıdığı üreticiye özgü uzantı kayıtları, tablonun SupplementalRecords listesi aracılığıyla ulaşılabilen ek kayıtlar olarak tutulur; böylece yazılmış model aracılığıyla incelenen bir tablo, modelin tanımlamadığı parçaları kaybetmez
Kod içinde bir pivot tablosu oluşturma
Yukarıdaki tüm mekanizma tek bir çağrının arkasında yer alır. AddPivotTable, A1 notasyonundaki kaynak aralığı, tablonun sol üst köşesinin bağlandığı hedef hücreyi ve bir adı alır. Aralığı ayrıştırır, alan türlerini çıkarıp önbelleği oluşturmak için tarar (başka bir tablo zaten aynı aralığa bağlanıyorsa mevcut bir önbelleği yeniden kullanır) ve kaynak sütun başına bir alana sahip, başlangıçta her alanı eksen dışı olan yazılmış bir TXLSPivotTable döndürür. Daha sonra alanları eksenlere yerleştirir ve bir toplama (aggregation) seçersiniz. İmza tam olarak budur ve önbellek, SXDBB paketleme ve görünüm kayıtlarının tümü kaydetme zamanında sizin için üretilir
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;Kaynak aralığın ilk satırı, önbellek alanlarını adlandıran başlık olarak okunur, böylece AddRowField('Region') bir sütunu konuma göre değil, başlık metnine göre eşleştirir. Döndürülen tablo FromRawBlobs = False olan yazılmış bir model olduğundan, yazar sıfırdan başlama yolunu izler: yenileme zamanında kaynak aralığın hala mevcut olmasına bağlı olmayan bağımsız bir önbellek oluşturur; bu, pivot, temel verileri taşıyabilecek veya silebilecek bir alıcıya gönderileceğinde tam olarak istediğiniz özelliktir
Üretmediğiniz bir dosyanın pivot ve önbellek kayıtlarının okunması ve uzlaştırılması (ham blob koruma yolu dahil), çalışma kitabı denetimi ve dönüşüm tezgahı kılavuzunda ele alınmıştır. Kaynak aralık on binlerce satıra ulaştığında ve SXDBB akışı birçok devam eden kayda yayıldığında, büyük çalışma kitabı performansı notlarındaki teknikler önbellek oluşturmanın çalışma sürenizi domine etmesini engeller. Her ikisi de, bu blogun başka yerlerinde ele alınan hücre, formül, grafik ve biçimlendirme API'lerinin yanı sıra Excel otomasyonu olmadan XLS ve XLSX dosyalarını okuyan ve yazan Delphi and C++Builder için HotXLS elektronik tablo bileşeni'nde sunulan pivot yazıcıyla eşleşir