كتابة جداول BIFF8 المحورية (PivotTables) في Delphi: SXDB و SXLI

يتكون كل جزء تقريباً من التنسيق الثنائي القديم لـ Excel من سجل واحد بنوع من بايتين وطول من بايتين. الخلية إما LABELSST أو NUMBER. المنطقة المدمجة هي MERGEDCELLS. يمكنك قراءة معظم ورقة العمل عن طريق المشي على السجلات واحداً تلو الآخر والإرسال (dispatching) على كلمة النوع. تكسر الجداول المحورية (PivotTables) هذا الإيقاع. لا يمثل الجدول المحوري الواحد سجلاً، بل هو برنامج صغير يتكون من عشرات السجلات المتعاونة المنتشرة عبر مكانين مختلفين في نفس تيار مستند OLE المركب، والعلاقات بينها موضعية (positional)، ومحزومة بالبت (bit-packed)، ولا ترحم. هذه هي البنية التي إما يتخطاها معظم قراء BIFF8 تماماً أو يحافظون عليها كبايتات غير شفافة (opaque bytes)، لأن كتابة واحدة من الصفر تعني إعادة إنتاج كل مرجع تبادلي (cross-reference) يحافظ عليه Excel نفسه

السبب الذي يجعل الجدول المحوري صعباً هو أنه في الحقيقة عبارة عن أداتين ملحومتين معاً. هناك ذاكرة التخزين المؤقت المحورية (pivot cache)، وهي لقطة مستقلة للبيانات المصدر مع تيارها الفرعي الخاص، وهناك عرض الجدول (table view)، التخطيط الذي يحدد الحقول الموجودة على أي محور. تشير ذاكرة التخزين المؤقت والعرض إلى بعضهما البعض حسب الفهرس (index). أخطئ في فهرس واحد وسيُفتح الملف على خطأ تحديث أو شبكة فارغة بصمت

ذاكرة التخزين المؤقت المحورية هي تيار فرعي بحد ذاته

تعيش ذاكرة التخزين المؤقت في تيار عموميات المصنف (workbook globals stream) كتيار فرعي لـ BIFF كامل، محاط بسجل BOF يكون نوع مستنده 0x0006 (القيمة التي تحدد ذاكرة التخزين المؤقت المحورية، على عكس 0x0005 للمصنف أو 0x0010 لورقة العمل) ويغلقه EOF المطابق. داخل هذا الإطار يكون الهيكل ثابتاً. سجل SXDB هو رأس ذاكرة التخزين المؤقت. ويحمل عدد السجلات، وعدد حقول ذاكرة التخزين المؤقت، ومعرف التيار الذي سيقتبسه عرض الجدول لربط نفسه بذاكرة التخزين المؤقت هذه. يساهم كل عمود مصدر بعد ذلك بسجل تعريف حقل SXFDB متبوعاً بـ SXFDBType الذي يصنفه، ثم القيم الفريدة التي أخذها ذلك العمود، ويتم إخراجها كسجل عنصر مكتوب واحد لكل قيمة مميزة

سجلات العناصر هي المكان الذي تكتسب فيه ذاكرة التخزين المؤقت قيمتها. تصبح قيمة النص SXSTRING، والقيمة الرقمية SXNUM، والقيمة المنطقية SXBOOLEAN، وخطأ الصيغة SXERR. لا تقوم ذاكرة التخزين المؤقت بتخزين الشبكة المصدر (source grid)، بل تخزن القيم المميزة لكل حقل بالإضافة إلى جدول فهرس يقول، بالنسبة للسجل n، أي عنصر مميز أخذه كل حقل. هذا هو السبب في أن بناء جدول محوري برمجياً ليس مسألة نسخ الخلايا. يجب عليك فحص نطاق المصدر، واستنتاج نوع كل حقل من القيم التي يحتفظ بها، وإلغاء تكرارها (deduplicate) في قائمة عناصر مكتوبة، وتسجيل كل صف على أنه مجموعة (tuple) من فهارس العناصر. يفعل HotXLS هذا بالضبط: يتم إخراج عمود رقمي بالكامل مع عناصر SXNUM، ويصبح العمود النصي المختلط عناصر SXSTRING، ويتم نقل التواريخ كقيم تسلسلية عبر نفس المسار الرقمي

SXDBB وحزم البت الذي يجعله مثيراً للاهتمام

يعد جدول الفهرس لكل سجل هو الجزء الفردي الأكثر إثارة للفضول من الناحية الفنية في البنية بأكملها، وهو يعيش في سجل SXDBB. يخزن الترميز الساذج فهرس عنصر كل حقل ككلمة 16 بت. لا يفعل Excel ذلك. فهو يحزم فهرس كل حقل في عدد البتات المطلوب بالضبط لعنونة عناصر ذلك الحقل، ولا شيء أكثر من ذلك. العرض هو ceil(log2(itemCount + 1)) بت. الـ + 1 مهم: القيمة الإضافية هي حارس (sentinel) يعني "فارغ، لا توجد قيمة لهذا الحقل في هذا السجل"، وبالتالي فإن الحقل الذي يحتوي على ثلاثة عناصر مميزة يحتاج إلى تمثيل أربع حالات ولذلك يأخذ بتين (two bits)، وليس البت الواحد الذي قد توحي به ثلاثة عناصر وحدها. لا يساهم الحقل الذي لا يحتوي على أي عناصر على الإطلاق بأي بتات ويتم تخطيه تماماً أثناء الحزم

يتم ربط بتات سجل واحد عبر جميع الحقول، ثم يبدأ السجل التالي على حد بايت جديد. تتم محاذاة السجلات بالبايت (byte-aligned)، ولا يتم حزمها بالبت من البداية إلى النهاية، مما يجعل الوصول العشوائي إلى الجدول قابلاً للتتبع على حساب عدد قليل من بتات الحشو (padding bits) لكل صف. الحزم داخل البايت يكون من البت الأقل أهمية أولاً (least-significant-bit first). بمجرد قبولك لهاتين القاعدتين، يكون المشفر عبارة عن مضخة بت (bit pump) مباشرة، وفك التشفير هو مرآته

// 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;

السبب الذي يجعل هذه التفاصيل لا يمكن تجاهلها هو سقف 8224 بايت على سجل BIFF واحد. يجب أن يتسع كل سجل في التنسيق، بما في ذلك السجلات المحورية، لحمولته في 8224 بايت كحد أقصى، وذاكرة التخزين المؤقت المحورية المزدحمة بآلاف صفوف المصدر ستتجاوز ذلك بوقت طويل قبل أن تُصدر كل صف. لذا فإن جدول الفهرس مقسم. يقصر HotXLS جسم SXDBB واحداً عند 8220 بايت، وهو حد السجل 8224 مطروحاً منه رأس سجل مكون من أربعة بايت من النوع والطول، ويقسمه على عرض البايت لسجل واحد معبأ لمعرفة عدد الصفوف الكاملة التي تتسع، ثم يصدر عدداً من سجلات SXDBB المستمرة كما يتطلب عدد الصفوف. يُعاد تشغيل كل استمرار بشكل نظيف على حد سجل، لذلك لا يتم قطع أي صف أبداً عبر سجلين. يمكن للقارئ الذي يعرف عرض البت لكل سجل أن يمر عبر كل SXDBB بالتسلسل كما لو كانت مصفوفة بتات متجاورة واحدة

تخطيط العرض: SXLI للجسم، SXPI للصفحة

مع بناء ذاكرة التخزين المؤقت، يكون عرض الجدول هو النصف الثاني. جوهره هو عناصر خط المحور (axis line items)، وهي صفوف الجسم المحوري التي تُعدد (enumerate) كل مجموعة من قيم حقل الصف (row-field) وحقل العمود (column-field) التي يرسمها الجدول. تُحمل هذه في سجلات SXLI (نوع السجل 0x00B5، موصوف في [MS-XLS] §2.4.275). يحتفظ SXLI واحد بالعديد من الأسطر، مرة أخرى حتى يفرض حد 8224 بايت تسجيلاً جديداً، ويستخدم حيلة ضغط صغيرة: كل سطر يخزن فقط كيف يختلف عن السطر الذي فوقه، معبراً عنه كعدد بادئة مشتركة (common-prefix count)، وبالتالي فإن المحور المتداخل بعمق لا يكرر قيم الحقل الخارجي في كل صف. السطر الإجمالي الكلي (grand-total line) والسطر الأول من أي سجل يعيد دائماً تعيين عدد البادئة هذا إلى الصفر بحيث لا يضطر القارئ أبداً إلى النظر إلى الوراء عبر حد السجل لإعادة بناء سطر

محور الصفحة، القوائم المنسدلة للتصفية (filter dropdowns) الموجودة أعلى الجدول المحوري، هو سجل منفصل. يحمل SXPI (نوع السجل 0x00B6، [MS-XLS] §2.4.276) إدخالاً واحداً مكوناً من عشرة بايت لكل حقل صفحة: فهرس الحقل المحوري isxvd، وعنصر ذاكرة التخزين المؤقت المحدد iCache، وكلمة الموضع ipos، ومعرف الكائن القديم (legacy object id) objId. القيمة iCache هي التي يجب مراقبتها. حقل الصفحة الذي يُظهر "(الكل)"، والذي لا يصفي شيئاً، يخزن الحارس (sentinel) 0x7FFD بدلاً من فهرس عنصر حقيقي. يُفتح المحور المبني برمجياً مع ضبط كل حقل صفحة على "(الكل)" حتى يحدد المتصل عنصراً مسبقاً، وعند هذه النقطة يحل فهرس ذاكرة التخزين المؤقت لذلك العنصر محل الحارس ويفتح Excel مع تطبيق عامل التصفية بالفعل. إلى جانب هذه، توجد السجلات الداعمة التي تصف الحقول الفردية وتنسيقها، SXVD و SXVDEx لتعريفات عرض الحقل، و SXIVD لقوائم فهرس الحقل التي ترتب كل محور، و SXFormat لتنسيق الأرقام، كل منها يفهرس مرة أخرى في نفس ذاكرة التخزين المؤقت التي تشير إليها أسطر الجسم

كاتبان في واحد: raw blobs و النموذج المكتوب

هناك سبب هيكلي يجعلك تجد أن HotXLS يحتفظ بمسارين منفصلين تماماً لكتابة جدول محوري، ويأتي مباشرة من متطلبات الدقة (fidelity). عندما يُقرأ مصنف من القرص، فإن سجلاته المحورية تكون مكتوبة بواسطة Excel أو من قبل بعض المنتجين الآخرين، وقد يستخدمون متغيرات السجل، أو ترتيب المراوغات (ordering quirks)، أو السجلات الممتدة التي لا ينمذجها أي كاتب طرف ثالث (third-party) بشكل كامل. الشيء الآمن الوحيد الذي يمكن فعله بهذه البايتات هو إعادتها دون تغيير. لذلك فإن الجدول المحوري الذي جاء من ملف يتم تمييزه بالعلامة FromRawBlobs = True، وعند الحفظ يعيد الكاتب تشغيل كتل (blobs) السجل المحفوظة حرفياً. لا يتم إعادة إنشاء أي شيء، ولا يتم إعادة تفسير أي شيء، والرحلة الدائرية (round-trip) عبر الفتح والحفظ مستقرة بالبايت

الجدول المحوري الذي بناه البرنامج هو الحالة المعاكسة. لا توجد بايتات أصلية للحفاظ عليها، فقط نموذج الكائن المكتوب (typed object model): وهو TXLSPivotCache مع حقوله وقوائم عناصره، و TXLSPivotTable مع تعيينات المحاور الخاصة به. يتم تمييز هذا الجدول بالعلامة FromRawBlobs = False، ويقوم الكاتب بتسلسله بالطريقة الصعبة، ويخرج تياراً فرعياً جديداً لذاكرة التخزين المؤقت BOF = 0x0006، ويحزم جدول الفهرس SXDBB من فهارس العناصر التي يحتفظ بها النموذج المكتوب، ويخطط سجلات SXLI و SXPI من تكوين المحور. العلامة (flag) هي ما يسمح لكلا النوعين بالتعايش في مصنف واحد. بدونها سيتعين على كاتب واحد إما التخلص من دقة (fidelity) الجداول المقروءة أو رفض إنشاء جداول جديدة. يتم الاحتفاظ بأي سجلات ممتدة خاصة بالمنتج (producer-specific extension records) يحملها جدول مقروء كسجلات تكميلية (supplemental records)، يمكن الوصول إليها من خلال قائمة SupplementalRecords الخاصة بالجدول، لذلك لا يفقد الجدول الذي تم فحصه من خلال النموذج المكتوب الأجزاء التي لا يصفها النموذج

بناء جدول محوري في التعليمات البرمجية

تكمن جميع الآليات المذكورة أعلاه خلف استدعاء واحد. يأخذ AddPivotTable النطاق المصدر بتدوين A1، وخلية الوجهة حيث يتم تثبيت الزاوية العلوية اليسرى للجدول، واسماً. يحلل النطاق، ويفحصه لاستنتاج أنواع الحقول وبناء ذاكرة التخزين المؤقت (إعادة استخدام ذاكرة التخزين المؤقت الموجودة إذا كان هناك جدول آخر يرتبط بالفعل بنفس النطاق)، ويعيد TXLSPivotTable المكتوب مع حقل واحد لكل عمود مصدر، وكل حقل في البداية خارج المحور (off-axis). ثم تضع الحقول على المحاور وتختار تجميعاً (aggregation). التوقيع هو هذا بالضبط، وذاكرة التخزين المؤقت، وحزم SXDBB، وسجلات العرض يتم إنتاجها جميعاً لك في وقت الحفظ

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;

تتم قراءة الصف الأول من النطاق المصدر كرأس يسمي حقول ذاكرة التخزين المؤقت، لذلك يتطابق AddRowField('Region') مع عمود بواسطة نص الرأس الخاص به بدلاً من الموضع. ونظراً لأن الجدول المرتجع هو نموذج مكتوب (typed model) به FromRawBlobs = False، يأخذ الكاتب المسار من الصفر: فهو يبني ذاكرة تخزين مؤقت مستقلة لا تعتمد على النطاق المصدر الذي لا يزال موجوداً في وقت التحديث، وهي بالضبط الخاصية التي تريدها عندما يتم شحن المحور (pivot) إلى مستلم قد ينقل البيانات الأساسية أو يحذفها

تتم تغطية قراءة ومواءمة (reconciling) السجلات المحورية وسجلات ذاكرة التخزين المؤقت لملف لم تقم بإنتاجه، بما في ذلك مسار الحفاظ على raw-blob، في التدقيق في المصنف والإرشادات التفصيلية لمنصة التحويل. عندما يصل النطاق المصدر إلى عشرات الآلاف من الصفوف ويمتد تيار SXDBB إلى العديد من السجلات المستمرة، فإن التقنيات الموجودة في ملاحظات أداء المصنف الكبير تمنع بناء ذاكرة التخزين المؤقت من السيطرة على وقت التشغيل الخاص بك. كلاهما يقترن مع الكاتب المحوري (pivot writer) الذي يتم شحنه في مكون جداول البيانات HotXLS لـ Delphi و C++Builder، إلى جانب واجهات برمجة تطبيقات الخلية، والصيغة، والمخطط، والتنسيق التي تم تناولها في مكان آخر في هذه المدونة