بناء وتحديث جداول Excel المحورية في Delphi باستخدام HotXLS

يقوم HotXLS ببناء وتحديث جداول Excel المحورية (Pivot Tables) الأصلية لـ XLSX من Delphi و C++Builder دون الحاجة لتثبيت Excel ودون استخدام أتمتة COM على الجهاز؛ حيث تستدعي AddPivotTable مع تحديد نطاق المصدر، وتضع الحقول على محاور الصفوف والأعمدة والصفحات والبيانات، وتضيف عناصر محسوبة أو عرض نسبة مئوية من الإجمالي، ويكتب المكون أجزاء pivotCacheDefinition و pivotTableDefinition التي يفتحها Excel كجدول محوري حي وقابل للتحديث

السيناريو الذي يجعل هذا الأمر يستحق العناء هو خادم التقارير؛ حيث تقوم بإنشاء مئات دفاتر العمل كل ليلة، ويحمل كل منها جدولاً محورياً يلخص حساباً واحداً، وفي الشهر التالي تتغير الأرقام ويتعين على كل ملف أن يعكس صفوف المصدر الجديدة؛ وإن قيادة Excel من خدمة ويندوز أمر هش وغير مرخص للاستخدام على الخوادم، وكتابة ملفات XML للجداول المحورية يدوياً يعد مشروعاً لا ينتهي؛ ويقع HotXLS بين هذين الطريقين المسدودين: نموذج كائنات مكتوب فوق أجزاء الجداول المحورية لـ OOXML، بحيث أن نفس كود Pascal الذي يملأ الخلايا يصرح أيضاً بالجدول المحوري ويعيد بناء ذاكرته التخزينية المؤقتة (cache) في نفس العملية

كيف تقوم بإنشاء جدول محوري في Delphi بدون Excel؟

تقوم بإنشاء جدول محوري باستدعاء واحد ثم تضع الحقول على المحاور؛ حيث تأخذ دالة AddPivotTable نطاق المصدر بتنسيق A1، وخلية الوجهة التي يثبت الجدول عندها، واسماً؛ وتقوم بتحليل النطاق، وفحص كل عمود لاستنتاج نوع بياناته، وبناء ذاكرة تخزين مؤقتة للجدول المحوري (أو إعادة استخدام واحدة مرتبطة بنفس النطاق بالفعل)، وترجع كائن TXLSPivotTable تبدأ جميع حقوله خارج المحاور؛ ومن هناك، تقوم الطرق الملائمة AddRowField و AddColumnField و AddPageField و AddDataFieldByName بتهيئة التخطيط، ويأخذ كل حقل بيانات عملية تجميع من بين إحدى عشرة عملية في التعداد TXLSPivotAggregation (وهي xlpaSum، xlpaCount، xlpaAverage، xlpaMax، xlpaMin، xlpaProduct، xlpaCountNums، xlpaStdDev، xlpaStdDevP، xlpaVar، xlpaVarP)

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // ورقة التقرير (تبدأ من ١ في محرك XLSX)

    // المصدر A1:E500 في ورقة 'Data'؛ تثبيت الجدول المحوري عند الصف ٣، العمود ١.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

لماذا تعد الذاكرة المؤقتة (cache) والجدول جزءاً منفصلاً

الجدول المحوري عبارة عن قطعتين تشيران إلى بعضهما البعض، وفهم هذا الانقسام هو ما يبقي بقية الأمور واضحة؛ حيث إن pivotCacheDefinition هو لقطة البيانات: worksheetSource يشير إلى نطاق المصدر، وعنصر cacheField واحد لكل عمود يحمل القيم الفرعية لذلك العمود (عناصره المشتركة sharedItems) بالإضافة إلى الحدود المشتقة؛ أما pivotTableDefinition فهو العرض (view): أي حقل ذاكرة مؤقتة يقبع على أي محور، وحقول البيانات وتجميعاتها، ومفاتيح التخطيط؛ ويرتبط العرض بالذاكرة المؤقتة بواسطة cacheId من خلال علاقة pivotCaches لدفتر العمل، تماماً كما تحدده المواصفة ECMA-376 الجزء ١ الفقرة 18.10 و [MS-XLSX]

وهذا التوجيه غير المباشر ليس تعقيداً بيروقراطياً، بل يشتري شيئين: يمكن لذاكرة مؤقتة واحدة أن تدعم عدة جداول، ولذلك فإن تحديث الذاكرة المؤقتة مرة واحدة يحدّث كل عرض مرسوم منها؛ وتخزن الذاكرة المؤقتة قيم كل عمود بعد إزالة التكرارات مع جدول فهارس لكل سجل بدلاً من الشبكة الخام، ولهذا السبب فإن بناء جدول محوري يعني فحص المصدر وليس نسخ الخلايا؛ ويصيغ HotXLS هذا بنفس الطريقة لكلا المحركين، ولذلك فإن الكود أعلاه متطابق تقريباً مع المسار الكلاسيكي الموثق بجوار تخطيط سجلات SX الثنائية خلف الجداول المحورية لـ .xls الكلاسيكية؛ وإذا كان مصدرك يعيش في ورقة أخرى أو تخاطبه بواسطة اسم، فإن سابقة النطاق و الأسماء المعرفة ومراجع الأوراق المتقاطعة تتبع قواعد A1 المعتادة، مع السماح بأسماء الأوراق المقتبسة للأسماء التي تحتوي على مسافات

العناصر المحسوبة ليست حقولاً محسوبة

تسمي هذه المصطلحات الثلاثة ثلاثة أشياء مختلفة، والخلط بينها هو الخطأ الكلاسيكي في الجداول المحورية؛ حيث يعيش العنصر المحسوب (calculated item) داخل حقل واحد ويجمع بين عناصر ذلك الحقل نفسه بالاسم، فداخل حقل المنطقة (Region) يمكنك تحديد صف صناعي CoreMarkets يساوي North زائد South؛ ويعرضه HotXLS كـ TXLSPivotField.AddCalculatedItem ويكتب <calculatedItem> تحت <calculatedItems> لذلك الحقل؛ أما الحقل المحسوب (calculated field) فهو مختلف: فهو قيمة جديدة مشتقة من أعمدة أخرى، مثل الهامش (Margin) من الإيرادات (Revenue) والتكلفة (Cost)، ويُحمل كصيغة على حقل الذاكرة المؤقتة من خلال TXLSPivotCacheField.Formula؛ والعضو المحسوب (calculated member)، المضاف بواسطة TXLSPivotTable.AddCalculatedMember، هو عضو مخصص على مستوى الجدول يمكنه العمل كمقياس (نوع العضو data) أو كعضو بعد (dimension member)، وهو مهم بشكل رئيسي للجداول المحورية ذات الشكل OLAP

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // يجمع العنصر المحسوب (ITEM) بين عناصر حقل واحد بالاسم.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // يتم التصريح عن العضو المحسوب (MEMBER) على مستوى الجدول. نوع العضو
  // 'data' يميزه كمقياس؛ والنوع الفارغ هو عضو بعد.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

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

كيف تعرض القيم كنسبة مئوية من الإجمالي؟

تقوم بضبط وضع عرض القيمة في حقل البيانات بدلاً من تحويل الأرقام بنفسك؛ حيث تأخذ TXLSPivotDataField.ShowDataAs التعداد TXLSPivotShowDataAs، الذي يعكس قيم OOXML لـ ST_ShowDataAs: وهي xlpsdaNormal، xlpsdaDifference، xlpsdaPercent، xlpsdaPercentDiff، xlpsdaRunTotal، xlpsdaPercentOfRow، xlpsdaPercentOfCol، xlpsdaPercentOfTotal، و xlpsdaIndex؛ والحيلة الشائعة هي وضع نفس عمود المصدر على محور البيانات مرتين، مرة كمجموع خام ومرة كحصة من المجموع الكلي، بحيث يعرض التقرير كلاً من الرقم ووزنه

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // الأوضاع المتعلقة بالعناصر تحتاج إلى أساس للمقارنة ضدها. المجموع التراكمي
  // على امتداد 'Quarter' (فهرس حقل الذاكرة المؤقتة ٣) يقرأ:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // فهرس حقل الذاكرة المؤقتة الذي يعمل التحويل فوقه
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

ولا يحتاج xlpsdaPercentOfTotal إلى أساس لأنه نسبي للمجموع الكلي، ولكن الأوضاع المتعلقة بالعناصر تحتاج ذلك؛ حيث تتطلب xlpsdaDifference و xlpsdaPercentDiff و xlpsdaRunTotal المعلمة BaseField (فهرس حقل الذاكرة المؤقتة الذي تعمل المقارنة فوقه) وفهرس BaseItem للنماذج المثبتة بالعناصر، حيث يمثل $7FFD الحارس (All)؛ ويكتب HotXLS هذه السمات كـ <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/>، وكما هو الحال مع الصيغ المحسوبة، يترك الحساب لـ Excel

تجميع التواريخ والأرقام، ومرشحات حقول الصفحة

يتم تهيئة التجميع (grouping) في حقل الذاكرة المؤقتة، وليس في حقل الجدول المحوري، لأنه يغير كيفية تقسيم نطاق المصدر؛ فقبل تعيين HasGroup := True في حقل الذاكرة المؤقتة الذي ترجعه دالة Cache.FindFieldByName، ثم اختر إما مقابض النطاقات الرقمية (GroupStartNum، GroupEndNum، GroupInterval) أو أعلام الهيكل الهرمي للتواريخ (GroupByDate مع GroupMonths، GroupQuarters، GroupYears، ونطاق GroupStartDate / GroupEndDate)؛ ويصدر HotXLS عنصر <fieldGroup><rangePr groupBy="months"/> المطابق بحيث يفتح الحقل المجمع حسب الشهر أو بواسطة نطاق رقمي بعرض ١٠٠٠ بالطريقة التي قد يجمعه بها Excel

حقول الصفحة (Page fields) هي القوائم المنسدلة للمرشحات أعلى الجدول؛ حيث تضع AddPageField حقلاً على محور الصفحة، ويقوم PageItemIndex بالتحديد المسبق لعنصر ذاكرة مؤقتة واحد، والقيمة الافتراضية هي xlPageItemAll (وهي $7FFD التي تعني (All))؛ ولالسماح للقارئ بتحديد عدة عناصر في نفس الوقت، اضبط MultipleItemSelectionAllowed := True، وهو ما يكتبه HotXLS كـ <pivotField multipleItemSelectionAllowed="1"/>؛ وللمعايير التي تتجاوز الاختيار اليدوي، يحمل كل حقل مجموعة Filters مكتوبة تغطي عائلات OOXML لـ ST_FilterType — كمرشحات العدد والنسبة المئوية والمجموع والتسمية التوضيحية والقيمة والتاريخ — حيث تقرن كل خانة نوع المرشح مع قيم المقارنة الخاصة به

كيف تقوم بتحديث الذاكرة المؤقتة للجدول المحوري عندما يتغير المصدر؟

تقوم دالة RefreshPivotCache في TXLSXWorkbook بإعادة فحص نطاق المصدر وإعادة بناء الذاكرة المؤقتة في مكانها، وهو ما تحتاجه خطوط الأنابيب الدفعتية بعد تعديل الصفوف الأساسية؛ فقم بتمرير معرف الذاكرة المؤقتة (cache id)، وتقرأ الطريقة كل خلية في نطاق المصدر (الرأس في الصف الأول، والبيانات من الصف الثاني)، وتعيد بناء نطاق العناصر المشتركة لكل حقل عن طريق إزالة تكرارات القيم وإعادة اشتقاق الحدود لكل نوع، وتكتب فهارس العناصر لكل سجل؛ وترجع القيمة 1 عند النجاح و -1 عندما يكون معرف الذاكرة المؤقتة غير معروف أو ورقة المصدر مفقودة

var
  Rc: Integer;
begin
  // ...تغيرت بيانات المصدر منذ بناء الجدول المحوري...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // رقم إيرادات مصحح

  // إعادة بناء عناصر الذاكرة المؤقتة المشتركة وفهارس السجلات من المصدر.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = refreshed, -1 = cache/source missing
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

إن الحد الدلالي هنا يستحق الذكر بوضوح: فقبل وجود هذه الطريقة، كان HotXLS يعتمد على علامة refreshOnLoad="1" ويترك كل أعمال التحديث لـ Excel عند الفتح التالي، وهو أمر جيد عندما يفتح الملف إنسان ولكنه عديم الفائدة في خطوط الأنابيب بدون واجهة رسومية والتي يتعين عليها تسليم بيانات صحيحة؛ وتجعل RefreshPivotCache الذاكرة المؤقتة المحفوظة محدثة داخل العملية، ولأن الجداول ترتبط بالذاكرة المؤقتة بواسطة معرفها، فإن كل جدول محوري مرسوم من تلك الذاكرة يرى التحديث؛ وما لا تفعله هو تخطيط أو تجميع الشبكة المرئية — فلا يزال Excel يعيد حساب الجدول المعروض من الذاكرة المؤقتة المحدثة عندما يفتح الملف

ما يكتبه HotXLS وما يحسبه Excel

أبقِ تقسيم العمل في الحسبان ولن يفاجئك شيء؛ حيث إن HotXLS كاتب للتعريفات: فهو ينتج pivotCacheDefinition، وسجلات الذاكرة المؤقتة، و pivotTableDefinition، كاملة بالمحاور والتجميعات والعناصر والأعضاء المحسوبة وعروض القيم والتجميع والمرشحات؛ بينما Excel هو الحاسبة: فعند الفتح يقوم بتجسيد دلاء المجموعات (group buckets)، ويقيم الصيغ المحسوبة، ويطبق تحويلات showDataAs، ويجمع الجسم؛ والقيم التي يراها المستخدم هي قيم Excel، المنتجة من التعليمات التي كتبها HotXLS، ولهذا السبب يجب أن تكون كل صيغة وفهرس أساسي صحيحة في وقت الكتابة بدلاً من التحقق منها في وقت العرض

وهناك حدان يستحقان المعرفة قبل تصميم نموذجك حول هذه الميزة: تحلل دالة AddPivotTable نطاق A1 مستطيل مع بادئة ورقة اختيارية؛ ومصادر النطاقات المسماة ودفاتر العمل الخارجية تقع خارج ما يحله الباني، على الرغم من أن الذاكرة المؤقتة المقروءة من ملف تم تأليفه بواسطة Excel تحافظ على مصدر النطاق المسمى الخاص بها في الدورة الكاملة؛ والجداول المحورية المنشأة بواسطة Excel والتي يقرؤها HotXLS من القرص يتم إعادتها بايتًا ببايت عند الحفظ، ولذلك تنطبق التعديلات المكتوبة الموصوفة هنا بنظافة على الجداول المحورية التي تبنيها في الكود بينما تظل الجداول المحورية الحالية دون خسارة؛ وبالنسبة لجانب المدخلات للتقرير — كالخلايا التي تم التحقق من صحتها والجداول المفلترة التي يلخصها الجدول المحوري — انظر المقال المصاحب حول التحقق من صحة البيانات، والمرشح التلقائي (AutoFilter)، والجداول المنظمة

إن نموذج الجدول المحوري الموضح هنا هو جزء من مكون HotXLS لـ Delphi Excel القياسي لـ Delphi و C++Builder، والذي يقرأ ويكتب أجزاء الجداول المحورية المكتوبة لـ XLSX وسجلات BIFF8 الكلاسيكية من نفس نموذج الكائنات