واجهة برمجة تطبيقات الجداول المحورية

يدعم HotXLS الفحص المكتوب الأنواع للجداول المحورية وتأليفها وتجسيد نتائجها لمصنفات XLS وXLSX. يحلّ تحميل XLSX المدفوع بالعلاقات أسماء أجزاء الحزمة العشوائية، ويعيد سجلات ذاكرة التخزين المؤقت والعناصر المشتركة وبيانات حقول التعريف الوصفية والمرشحات والتجميع والتعريفات المحسوبة والتنسيقات والامتدادات إلى النموذج العام. ويحافظ إدخال XLS الكلاسيكي على سجلاته المحورية الأصلية متاحةً لرحلات حفظ أمينة

الذهاب والإياب والنموذج المكتوب

عندما يحتوي مصنف كلاسيكي بالفعل على جداول محورية، يحافظ القارئ على سجلات BIFF الخام ويعرض أيضًا نموذجًا مكتوبًا. تحتفظ الجداول وذاكرات التخزين المؤقت المحمَّلة من ملف كلاسيكي بالقيمة FromRawBlobs = True، لذا تعيد مخرجات الحفظ تشغيل البايتات الأصلية حفاظًا على الدقة. وتُحمَّل الجداول المحورية في XLSX وتُحفظ عبر علاقات الحزمة المالكة لها، بينما تستخدم الجداول المحورية المنشأة برمجيًّا الكاتب المكتوب

const
  xlPivotBaseItemPrevious = $7FFB;
  xlPivotBaseItemNext = $7FFC;

type
  TXLSPivotFieldAxis = (xlpfaNone, xlpfaRowField, xlpfaColumnField, xlpfaPageField, xlpfaDataField);
  TXLSPivotAggregation = (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP);
  TXLSPivotShowDataAs = (xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal, xlpsdaIndex);
  TXLSPivotExtendedShowDataAs = (xlpesdaNone, xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending, xlpesdaRankDescending);
  TXLSPivotSubtotal = (xlpsDefault, xlpsSum, xlpsCount, xlpsAverage, xlpsMax, xlpsMin, xlpsProduct, xlpsCountNums, xlpsStdDev, xlpsStdDevP, xlpsVar, xlpsVarP);
  TXLSPivotSubtotals = set of TXLSPivotSubtotal;
  TXLSPivotDesignerZone = (xlpdzAvailable, xlpdzRows, xlpdzColumns, xlpdzFilters, xlpdzValues);

  TXLSPivotCacheValue = record
    ValueType: TXLSPivotCacheFieldType;
  end;

  TXLSPivotCacheField = class
    property Name: WideString;
    property FieldType: TXLSPivotCacheFieldType;
    property ItemCount: Integer;
    property Items[Index: Integer]: TXLSPivotCacheItem;
  end;

  TXLSPivotCache = class
    property CacheId: Integer;
    property SourceRangeSheet: WideString;
    property SourceFirstRow: Integer;
    property SourceFirstCol: Integer;
    property SourceLastRow: Integer;
    property SourceLastCol: Integer;
    property RecordCount: Integer;
    property FieldCount: Integer;
    property Fields[Index: Integer]: TXLSPivotCacheField;
    property RecordIndices[RecordIdx, FieldIdx: Integer]: Integer;
    property FromRawBlobs: Boolean;
  end;

  TXLSPivotCaches = class
    function Add: TXLSPivotCache;
    function FindByCacheId(ACacheId: Integer): TXLSPivotCache;
    property Count: Integer;
    property Items[Index: Integer]: TXLSPivotCache;
  end;

  TXLSPivotField = class
    function AddItem: TXLSPivotItem;
    function AddCalculatedItem(const AName, AFormula: WideString): TXLSPivotCalculatedItem;
    property Name: WideString;
    property Axis: TXLSPivotFieldAxis;
    property CacheFieldIndex: Integer;
    property ItemCount: Integer;
    property Items[Index: Integer]: TXLSPivotItem;
    property SubtotalMask: Word;
    property Subtotals: TXLSPivotSubtotals;
    property PageItemIndex: Integer;
    property SortType: TXLSPivotFieldSortType;
    property SortDataField: Integer;
    property Filters: TXLSPivotFilters;
  end;

  TXLSPivotDataField = class
    property Field: TXLSPivotField;
    property Aggregation: TXLSPivotAggregation;
    property NumberFormat: Word;
    property DisplayName: WideString;
    property ShowDataAs: TXLSPivotShowDataAs;
    property ExtendedShowDataAs: TXLSPivotExtendedShowDataAs;
    property BaseField: Integer;
    property BaseItem: Integer;
  end;

  TXLSPivotSupplementalRecord = class
    property RecID: Word;
    property BodyLength: Integer;
    property Body[Index: Integer]: Byte;
  end;

  TXLSPivotTable = class
    function AddField(const AName: WideString; ACacheFieldIndex: Integer): TXLSPivotField;
    function FindFieldByName(const AName: WideString): TXLSPivotField;
    function SetFieldAxis(const AName: WideString; AAxis: TXLSPivotFieldAxis): TXLSPivotField;
    function AddRowField(const AName: WideString): TXLSPivotField;
    function AddColumnField(const AName: WideString): TXLSPivotField;
    function AddPageField(const AName: WideString): TXLSPivotField;
    function AddDataField(AField: TXLSPivotField; AAggregation: TXLSPivotAggregation): TXLSPivotDataField;
    function AddDataFieldByName(const AName: WideString; AAggregation: TXLSPivotAggregation = xlpaSum): TXLSPivotDataField;
    function AddCalculatedMember(const AName, AFormula: WideString): TXLSPivotCalculatedMember;
    function AddSupplementalRecord(ARecID: Word; const ABody: TBytes): TXLSPivotSupplementalRecord;
    function Make(AWriter: TlxPivotResultWriter): Integer;
    property Name: WideString;
    property DataCaption: WideString;
    property CacheId: Integer;
    property Cache: TXLSPivotCache;
    property FieldCount: Integer;
    property Fields[Index: Integer]: TXLSPivotField;
    property DataFieldCount: Integer;
    property DataFields[Index: Integer]: TXLSPivotDataField;
    property RowGrandTotals: Boolean;
    property ColumnGrandTotals: Boolean;
    property MaterializeGrandTotalRow: Boolean;
    property MaterializeGrandTotalColumn: Boolean;
    property SupplementalRecordCount: Integer;
    property SupplementalRecords[Index: Integer]: TXLSPivotSupplementalRecord;
    property FromRawBlobs: Boolean;
  end;

  TXLSPivotDesigner = class
    constructor Create(APivotTable: TXLSPivotTable = nil);
    procedure ClearLayout;
    function MoveField(const AName: WideString; AZone: TXLSPivotDesignerZone; APosition: Integer = -1): Boolean;
    function SetAggregation(const AName: WideString; AAggregation: TXLSPivotAggregation): Boolean;
    function Validate(out ErrorText: WideString): Boolean;
    function Apply(AWriter: TlxPivotResultWriter): Integer;
    property PivotTable: TXLSPivotTable;
    property FieldCount: Integer;
    property Fields[Index: Integer]: TXLSPivotDesignerField;
  end;

  TXLSPivotTables = class
    function Add: TXLSPivotTable;
    function FindByName(const AName: WideString): TXLSPivotTable;
    property Count: Integer;
    property Items[Index: Integer]: TXLSPivotTable;
  end;
      

نقاط دخول ورقة العمل والمصنف

type
  TXLSWorksheet = class
    function AddPivotTable(const SourceRangeA1: WideString; DestRow, DestCol: Integer; const Name: WideString): TXLSPivotTable;
    function PivotSetDataFieldFormat(DataField: TXLSPivotDataField; const FormatStr: WideString): Word;
    property PivotTables: TXLSPivotTables;
  end;

  TXLSWorkbook = class
    property PivotCaches: TXLSPivotCaches;
    function RegisterNumFormat(const FormatStr: WideString): Word;
end;
      

يسجّل RegisterNumFormat سلسلة تنسيق أرقام ويعيد فهرس ifmt الخاص بـ BIFF8، وهو ما تتوقعه TXLSPivotDataField.NumberFormat وTXLSPivotCacheField.NumberFormat؛ وتسجيل السلسلة نفسها مرتين يعيد الفهرس نفسه

DestRow وDestCol مستندة إلى 1 مثل Cells[Row, Col]، لذا يُرسوّ AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') الجدول المحوري عند A3. والمواضع على TXLSPivotTable (FirstRow .. FirstDataCol) والنطاق المصدر على TXLSPivotCache مستندة إلى 1 كذلك، تمامًا كما في محرك XLSX؛ ونقطة تثبيت خارج ورقة 65,536 x 256 تُعيد nil

بناء تخطيط محوري بالسحب والإفلات

TXLSPivotDesigner نموذج تخطيط غير مرئي يمكنه دعم واجهات قوائم الحقول في VCL وFMX والويب وسطر الأوامر دون ربط منطق الجداول المحورية بمجموعة أدوات واجهة واحدة

يبلّغ كل حقل عن منطقة متاحٍة أو صف أو عمود أو مرشح أو قيمة، إضافةً إلى موضعه الحتمي؛ يطبّق MoveField عملية الإفلات، ويغيّر SetAggregation حقل قيمة، ويبلّغ Validate عن التخطيطات غير المكتملة، ويتحقق Apply ويجسّد النتيجة عبر كاتب الجداول المحورية الاعتيادي

تُبتدَأ حقول القيم الرقمية والتاريخية الجديدة على xlpaSum، بينما تُبتدَأ حقول النص والقيم المنطقية والأخطاء والفراغ والمختلطة على xlpaCount

var
  Designer: TXLSPivotDesigner;
  ErrorText: WideString;
begin
  Designer := TXLSPivotDesigner.Create(Pivot);
  try
    Designer.MoveField('Region', xlpdzRows);
    Designer.MoveField('Quarter', xlpdzColumns);
    Designer.MoveField('Closed', xlpdzFilters);
    Designer.MoveField('Revenue', xlpdzValues);
    Designer.SetAggregation('Revenue', xlpaAverage);
    if Designer.Validate(ErrorText) then
      Designer.Apply(ResultWriter.WriteCell);
  finally
    Designer.Free;
  end;
end;
      

إنشاء جدول محوري

uses lxHandle;

var
  Workbook: TXLSWorkbook;
  SourceSheet, PivotSheet: TXLSWorksheet;
  Pivot: TXLSPivotTable;
  RowField: TXLSPivotField;
  DataField: TXLSPivotDataField;
begin
  Workbook := TXLSWorkbook.Create;
  try
    SourceSheet := Workbook.Sheets[1];
    SourceSheet.Name := 'SalesData';
    SourceSheet.Cells[1, 1].Value := 'Region';
    SourceSheet.Cells[1, 2].Value := 'Product';
    SourceSheet.Cells[1, 3].Value := 'Revenue';

    PivotSheet := Workbook.Sheets.Add;
    PivotSheet.Name := 'PivotReport';
    Pivot := PivotSheet.AddPivotTable('SalesData!A1:C20', 4, 1, 'SalesPivot');
    RowField := Pivot.AddRowField('Region');
    Pivot.AddColumnField('Product');
    DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
    RowField.Subtotals := [xlpsSum];
    PivotSheet.PivotSetDataFieldFormat(DataField, '$#,##0.00');

    Workbook.SaveAs('pivot-report.xls');
  finally
    Workbook.Free;
  end;
end;
      

عرض القيم بصيغ

اضبط ShowDataAs على كل حقل بيانات قبل استدعاء Make أو حفظ المصنف. تتطلب Difference وPercent وPercentDiff فهرس BaseField لحقل محوري (قائم على الصفر) إضافةً إلى BaseItem. ويتطلب RunTotal ‏BaseField وحده. وتشتق أوضاع الصف والعمود والمجموع والفهرس مقاماتها من سجلات المصدر وفق التجميع المهيَّأ

الـ BaseItem الصريح هو فهرس عنصر قائم على الصفر داخل حقل المحوري الأساسي. استخدم xlPivotBaseItemPrevious أو xlPivotBaseItemNext لمقارنات العناصر المتجاورة. تظل تقاطعات المقارنة المفقودة فارغة، بينما يُنتج المقام الصفري الموجود ‏#DIV/0!

DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
DataField.ShowDataAs := xlpsdaPercentDiff;
DataField.BaseField := 0;
DataField.BaseItem := xlPivotBaseItemPrevious;
CellsWritten := Pivot.Make(ResultWriter.WriteCell);
      

التصفية والتجميع والفرز

يطبّق Make العناصر المخفية واختيارات الصفحات قبل بناء النطاق، ثم يقيّم مرشحات التسمية والتاريخ والعدد والنسبة والمجموع والقيمة. تستخدم النطاقات الرقمية والمجموعات التقويمية والمجموعات المنفصلة بيانات تجميع حقل الذاكرة المؤقتة قبل التجميع. ويختار SortType ترتيبًا مستقرًّا للتسميات، بينما يختار SortDataField حقل القيمة ذا الفهرس الصفري لترتيب مستقر لقيم البيانات

التعبيرات المحسوبة

تُنفَّذ حقول ذاكرة التخزين المؤقت المحسوبة مرة واحدة لكل سجل مصدر، وتُنفَّذ العناصر المحسوبة عبر مجمّعات العناصر الشقيقة، وتضيف الأعضاء المحسوبة ذات MemberType = 'data' حقول قيم افتراضية بعد تجميع المصدر. أسماء الصيغ غير حساسة لحالة الأحرف ويجوز إحاطتها بأقواس أو علامتي اقتباس. وتُدعم العمليات الحسابية والمقارنات والنسب والأقواس والدوال الرقمية والمنطقية الشائعة، مع تمرّرات تبعية متكررة للتعريفات المحسوبة اللاحلقية

ProfitField := Pivot.Cache.AddField('Profit', xlpcftNumber);
ProfitField.Formula := '=Revenue-Cost';
ProfitField.DatabaseField := False;
Pivot.AddDataField(Pivot.AddField('Profit', 4), xlpaSum);

RegionField.AddCalculatedItem('Combined', '=North+South');
Pivot.AddCalculatedMember('Margin', '=Profit/Revenue').MemberType := 'data';
      

المجاميع الفرعية والمجاميع الكلية

يمكن لكل حقل صف أو عمود تجسيد أي دالة مجاميع فرعية مهيَّأة فوق عناصره التفصيلية أو تحتها عبر Subtotals وSubtotalTop. يتحكم RowGrandTotals وColumnGrandTotals في سياسة حفظ المصنف، بينما تلحق MaterializeGrandTotalRow وMaterializeGrandTotalColumn عمليات رد نداء النتائج بمخرجات المجموع الكلي الصريحة دون إعادة مسح سجلات المصدر

أوضاع عرض القيم الموسعة

يوفر ExtendedShowDataAs نسبة من الأصل ونسبة من صف الأصل ونسبة من عمود الأصل ونسبة من الإجمالي الجاري، والترتيب تصاعديًّا أو تنازليًّا. تستخدم هذه الأوضاع نموذج امتداد XLSX ولا تُصدر قيم حقول بيانات BIFF كلاسيكية غير مدعومة

فحص مصنف محوري موجود

Workbook.Open('excel-authored-pivot.xls');
for SheetIndex := 1 to Workbook.Sheets.Count do
begin
  Sheet := Workbook.Sheets[SheetIndex];
  for PivotIndex := 0 to Sheet.PivotTables.Count - 1 do
  begin
    Pivot := Sheet.PivotTables[PivotIndex];
    Writeln(Pivot.Name);
    Writeln(Pivot.FieldCount);
    if Assigned(Pivot.Cache) then
      Writeln(Pivot.Cache.SourceRangeSheet);
  end;
end;
      

مواضيع ذات صلة

• واجهة IXLSWorkbook
• فئة TXLSWorksheet
• عرض توضيحي لـ InteractiveFeatures
• الحفاظ على بيانات تعريف مصنف XLS الكلاسيكية