كتابة جداول PivotTable لـ BIFF8 في Delphi: سجلات SXDB و SXLI

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

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

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

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

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

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

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

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

// 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)، وهي صفوف جسم المحور التي تسرد كل مجموعة من قيم حقول الصفوف وحقول الأعمدة التي يرسمها الجدول. يتم حمل هذه السجلات في سجلات SXLI (نوع السجل 0x00B5، الموصوف في [MS-XLS] §2.4.275). يحتفظ SXLI الواحد بالعديد من السطور، مرة أخرى حتى يجبر حد 8224 بايت على سجل جديد، ويستخدم خدعة ضغط صغيرة: يخزن كل سطر فقط كيف يختلف عن السطر الذي فوقه، معبراً عنه بعدد بادئة مشتركة، لذلك لا يكرر المحور المتداخل بعمق قيم الحقل الخارجية في كل صف. يعيد سطر المجموع الإجمالي والسطر الأول لأي سجل دائماً تعيين عدد البادئة إلى الصفر بحيث لا يتعين على القارئ أبداً النظر إلى الوراء عبر حد سجل لإعادة بناء سطر.

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

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

هناك سبب هيكلي وراء احتفاظ HotXLS بمسارين منفصلين تماماً لكتابة جدول المحور، ويأتي ذلك مباشرة من متطلبات الدقة. عندما يتم قراءة كتيب عمل من القرص، تكون سجلات المحور الخاصة به قد كتبت بواسطة Excel أو بواسطة منتج آخر، وقد تستخدم متغيرات سجلات، أو غرائب ترتيب، أو سجلات ملحقة لا ينمذجها أي كاتب طرف ثالث بالكامل. الشيء الآمن الوحيد الذي يمكن فعله بهذه البايتات هو إعادتها دون تغيير. لذلك يتم وضع علامة FromRawBlobs = True على جدول المحور الذي جاء من ملف، وعند الحفظ يعيد الكاتب تشغيل كتل السجلات المحفوظة حرفياً. لا يتم إعادة توليد أي شيء، ولا يتم إعادة تفسير أي شيء، وتكون الرحلة ذهاباً وإياباً من خلال الفتح والحفظ مستقرة على مستوى البايت.

جدول المحور الذي بناه البرنامج هو الحالة المعاكسة. لا توجد بايتات أصلية للحفاظ عليها، فقط نموذج الكائنات المكتوب: TXLSPivotCache مع حقوله وقوائم عناصره، و TXLSPivotTable مع تعيينات المحور الخاصة به. يتم وضع علامة FromRawBlobs = False على هذا الجدول، ويقوم الكاتب بتسلسله بالطريقة الصعبة، مرسلاً دفقاً فرعياً جديداً للذاكرة المؤقتة BOF = 0x0006، وحازماً جدول فهرس SXDBB من فهارس العناصر التي يحتفظ بها النموذج المكتوب، وتخطيط سجلات SXLI و SXPI من تكوين المحور. العلم هو ما يسمح لكلا النوعين بالتعايش في كتيب عمل واحد. وبدونه، سيتعين على كاتب واحد إما التخلص من دقة الجداول المقروءة أو رفض إنشاء جداول جديدة. يتم الاحتفاظ بأي سجلات ملحقة خاصة بالمنتج يحملها الجدول المقروء كسجلات تكميلية، يمكن الوصول إليها من خلال قائمة SupplementalRecords للجدول، لذلك لا يفقد الجدول الذي يتم فحصه من خلال النموذج المكتوب الأجزاء التي لا يصفها النموذج.

بناء جدول محور في الكود

تقع كل الآليات المذكورة أعلاه خلف استدعاء واحد. يأخذ AddPivotTable النطاق المصدر بتدوين A1، وخلية الوجهة حيث ترسو الزاوية اليسرى العليا للجدول، واسماً. يقوم بتحليل النطاق، ومسحه لاستنتاج أنواع الحقول وبناء ذاكرة التخزين المؤقت (مع إعادة استخدام ذاكرة مؤقتة موجودة إذا كان جدول آخر يرتبط بالفعل بنفس النطاق)، ويرجع TXLSPivotTable مكتوباً مع حقل واحد لكل عمود مصدر، ويكون كل حقل في البداية خارج المحور. ثم تضع الحقول على المحاور وتختار التجميع. التوقيع هو هذا بالضبط، ويتم إنتاج ذاكرة التخزين المؤقت وحزم 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') يطابق عموداً بنص الرأس الخاص به بدلاً من الموضع. ونظراً لأن الجدول المرتجع هو نموذج مكتوب مع FromRawBlobs = False، فإن الكاتب يأخذ مسار البدء من الصفر: فهو يبني ذاكرة تخزين مؤقت قائمة بذاتها لا تعتمد على بقاء النطاق المصدر موجوداً في وقت التحديث، وهي بالضبط الخاصية التي تريدها عندما يتم شحن المحور إلى مستلم قد ينقل البيانات الأساسية أو يحذفها.

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