Pivot tablo API'si

HotXLS; XLS ve XLSX çalışma kitapları için türlü pivot tablo incelemeyi, pivot tablo oluşturmayı ve sonuçları somutlaştırmayı destekler. İlişki tabanlı XLSX yükleme, paket parça adlarını serbestçe çözümler ve önbellek kayıtlarını, paylaşılan öğeleri, alan üst verisini, filtreleri, gruplamayı, hesaplanan tanımları, biçimleri ve uzantıları genel modele aktarır. Klasik XLS girdisinde özgün pivot kayıtları saklanır; böylece kaydetme işleminde birebir round-trip yapılır

Round-trip ve türlü model

Klasik bir çalışma kitabı zaten pivot tablolar içeriyorsa okuyucu ham BIFF kayıtlarını korur ve buna ek olarak türlü bir model de sunar. Klasik bir dosyadan yüklenen tablolar ve önbellekler FromRawBlobs = True değerini taşır; böylece kaydetme çıktısı, sadakat için özgün baytları aynen geri yazar. XLSX pivotları, ait oldukları paket ilişkileri üzerinden yüklenir ve kaydedilir; programatik olarak oluşturulan pivotlar ise türlü yazıcıyı kullanır

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;
      

Çalışma sayfası ve çalışma kitabı giriş noktaları

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 bir sayı biçimi dizesini kaydeder ve BIFF8 ifmt indeksini döndürür; TXLSPivotDataField.NumberFormat ve TXLSPivotCacheField.NumberFormat tam olarak bu indeksi bekler. Aynı dize ikinci kez kaydedilirse aynı indeks döner

DestRow ve DestCol 1 tabanlıdır, tıpkı Cells[Row, Col] gibi; dolayısıyla AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') çağrısı pivot tabloyu A3'e çapar. TXLSPivotTable üzerindeki konumlar (FirstRow .. FirstDataCol) ve TXLSPivotCache üzerindeki kaynak aralık da 1 tabanlıdır, aynen XLSX motorundaki gibi; 65,536 x 256'lık sayfanın dışına düşen bir çapa nil döndürür

Sürükle-bırak pivot düzeni kurma

TXLSPivotDesigner, görsel olmayan bir düzen modelidir; pivot mantığını tek bir UI araç takımına bağmadan VCL, FMX, web ya da komut satırı alan listesi arayüzlerinin arkasında çalışabilir

Her alan, kullanılabilir, satır, sütun, filtre veya değer bölgesinden hangisine düştüğünü ve konumunu belirli bir sırayla bildirir; MoveField bir bırakma işlemini uygular, SetAggregation bir değer alanını değiştirir, Validate eksik düzenleri raporlar, Apply ise doğrular ve sonucu normal pivot yazıcısı üzerinden somutlaştırır

Yeni sayısal ve tarih değer alanları xlpaSum ile başlar; metin, Boolean, hata, boş ve karma alanlar ise xlpaCount ile başlar

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;
      

Pivot tablo oluşturma

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;
      

Değerleri farklı gösterme

Make çağrısından ya da çalışma kitabını kaydetmeden önce her veri alanında ShowDataAs değerini ayarlayın. Difference, Percent ve PercentDiff sıfır tabanlı bir BaseField pivot alanı indeksi ile bir BaseItem bekler. RunTotal için yalnızca BaseField yeterlidir. Satır, sütun, toplam ve indeks modları paydalarını, yapılandırılan birleştirme ile kaynak kayıtlarından türetir

Açık bir BaseItem, taban pivot alanı içindeki sıfır tabanlı bir öğe indeksidir. Bitişik öğe karşılaştırmaları için xlPivotBaseItemPrevious ya da xlPivotBaseItemNext kullanın. Karşılaştırma kesişimi yoksa hücre boş kalır; payda sıfır ise sonuç #DIV/0! olur

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

Filtreleme, gruplama ve sıralama

Make, etki alanı inşasından önce gizli öğeleri ve sayfa seçimlerini uygular; ardından başlık, tarih, sayı, yüzde, toplam ve değer filtrelerini değerlendirir. Sayısal aralıklar, takvim grupları ve ayrık gruplar, birleştirme öncesinde önbellek alanı gruplama üst verisini kullanır. SortType etiketlerin kararlı sıralamasını seçer; SortDataField ise veri değerlerine göre kararlı sıralama için sıfır tabanlı değer alanını belirler

Hesaplanan ifadeler

Hesaplanan önbellek alanları her kaynak kaydı için bir kez çalışır; hesaplanan öğeler kardeş öğe birleşimleri üzerinden çalışır ve MemberType = 'data' olan hesaplanan üyeler, kaynak birleştirmesinin ardından sanal değer alanları ekler. Formül adları büyük/küçük harfe duyarsızdır ve köşeli parantez ya da tırnak içinde yazılabilir. Aritmetik işlemler, karşılaştırmalar, yüzdeler, parantezler ve yaygın sayısal ile mantıksal işlevler desteklenir; döngüsüz hesaplanan tanımlar için bağımlılık turları tekrarlanır

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

Ara toplamlar ve genel toplamlar

Her satır ya da sütun alanı, yapılandırılmış ara toplam işlevlerini ayrıntı öğelerinin altına ya da üstüne Subtotals ve SubtotalTop ile somutlaştırabilir. RowGrandTotals ve ColumnGrandTotals kaydedilen çalışma kitabının politikasını belirler; MaterializeGrandTotalRow ve MaterializeGrandTotalColumn ise sonuç geri çağrılarını, kaynak kayıtlarını yeniden taramadan açık genel toplam çıktısına dahil eder

Genişletilmiş değer gösterme modları

ExtendedShowDataAs; üst öğe yüzdesi, üst satır yüzdesi, üst sütun yüzdesi, kümülatif toplam yüzdesi ile artan veya azalan sıralama (rank) modlarını sunar. Bu modlar XLSX uzantı modelini kullanır ve klasik BIFF'te desteklenmeyen veri alanı değerleri üretmez

Mevcut bir pivot çalışma kitabını inceleme

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;
      

İlgili konular

• IXLSWorkbook arayüzü
• TXLSWorksheet sınıfı
• InteractiveFeatures demosu
• Klasik XLS çalışma kitabı üst verisi koruma