API tabeli przestawnej klasycznego XLS

HotXLS obsługuje typowaną inspekcję tabel przestawnych, ich tworzenie i materializację wyników dla skoroszytów XLS i XLSX. Ładowanie XLSX sterowane relacjami rozwiązuje dowolne nazwy części pakietu i odtwarza do modelu publicznego rekordy pamięci podręcznej, współdzielone elementy, metadane pól, filtry, grupowania, definicje obliczeniowe, formaty i rozszerzenia. Wejście Classic XLS zachowuje oryginalne rekordy tabel przestawnych, umożliwiając wierny round-trip przy zapisie

Round-trip i typowany model

Gdy klasyczny skoroszyt już zawiera tabele przestawne, czytnik zachowuje surowe rekordy BIFF, a dodatkowo udostępnia typowany model. Tabele i pamięci podręczne wczytane z pliku klasycznego mają FromRawBlobs = True, więc zapis odtwarza oryginalne bajty dla wiernej zgodności. Tabele przestawne XLSX są wczytywane i zapisywane przez relacje należącego do nich pakietu, natomiast tabele tworzone programowo korzystają z typowanego writera

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;
      

Punkty wejścia arkusza i skoroszytu

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 rejestruje ciąg formatu liczb i zwraca jego indeks ifmt (BIFF8) — właśnie tego oczekują TXLSPivotDataField.NumberFormat i TXLSPivotCacheField.NumberFormat; zarejestrowanie tego samego ciągu dwukrotnie zwraca ten sam indeks

DestRow i DestCol są liczone od 1, tak jak Cells[Row, Col], więc wywołanie AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') zakotwicza tabelę przestawną w A3. Pozycje udostępniane przez TXLSPivotTable (FirstRow .. FirstDataCol) oraz zakres źródłowy w TXLSPivotCache też są liczone od 1 — dokładnie tak samo jak w silniku XLSX; kotwica poza arkuszem 65,536 x 256 daje w wyniku nil

Budowa układu tabeli przestawnej typu drag-and-drop

TXLSPivotDesigner to niewizualny model układu, który może zasilać interfejsy list pól VCL, FMX, webowe lub wiersza poleceń, bez spinania logiki tabel przestawnych z jednym zestawem narzędzi UI

Każde pole raportuje strefę dostępna / wiersz / kolumna / filtr / wartości wraz ze swoją deterministyczną pozycją; MoveField wykonuje upuszczenie, SetAggregation zmienia pole wartości, Validate raportuje niekompletne układy, a Apply waliduje i materializuje wynik przez zwykły writer tabel przestawnych

Nowe liczbowe i datowe pola wartości mają domyślnie xlpaSum, natomiast pola tekstowe, logiczne, błędów, puste i mieszane — domyślnie 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;
      

Tworzenie tabeli przestawnej

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;
      

Pokazywanie wartości jako

Ustaw ShowDataAs na każdym polu danych przed wywołaniem Make albo zapisem skoroszytu. Difference, Percent i PercentDiff wymagają zerowej bazowej indeksu pola przestawnego BaseField plus BaseItem. RunTotal wymaga tylko BaseField. Tryby wierszowy, kolumnowy, totalny i indeksowy wyliczają mianowniki z rekordów źródłowych przy skonfigurowanej agregacji

Jawny BaseItem to indeks elementu od zera wewnątrz bazowego pola przestawnego. Do porównań sąsiednich elementów użyj xlPivotBaseItemPrevious albo xlPivotBaseItemNext. Brakujące przecięcia porównania pozostają puste, natomiast obecne zerowy mianownik daje #DIV/0!

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

Filtrowanie, grupowanie i sortowanie

Make stosuje ukryte elementy i wybory stron przed budową domeny, a potem ewaluuje filtry podpisów, dat, liczności, procentów, sum i wartości. Zakresy liczbowe, grupy kalendarzowe i grupy dyskretne korzystają z metadanych grupowania pola pamięci podręcznej jeszcze przed agregacją. SortType wybiera stabilne porządkowanie po etykietach, a SortDataField wskazuje pole wartości (indeks od zera) dla stabilnego porządkowania po wartościach

Wyrażenia obliczeniowe

Obliczane pola pamięci podręcznej wykonują się raz na rekord źródłowy, obliczane elementy — po agregatach elementów siostrzanych, a obliczone elementy członkowskie z MemberType = 'data' dodają wirtualne pola wartości po agregacji źródłowej. Nazwy formuł są nierozróżniające wielkości liter i mogą być w nawiasach kwadratowych lub cudzysłowach. Wspierana jest arytmetyka, porównania, procenty, nawiasy oraz popularne funkcje liczbowe i logiczne, z powtarzanymi przebiegami zależności dla acyklicznych definicji obliczeniowych

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

Sumy częściowe i sumy całkowite

Każde pole wiersza lub kolumny może materializować dowolną skonfigurowaną funkcję sumy częściowej powyżej lub poniżej swoich elementów szczegółowych przez Subtotals i SubtotalTop. RowGrandTotals i ColumnGrandTotals sterują polityką zapisywaną w skoroszycie, a MaterializeGrandTotalRow i MaterializeGrandTotalColumn włączają w callbackach wyników jawne sumy całkowite bez ponownego skanowania rekordów źródłowych

Rozszerzone tryby pokazywania wartości

ExtendedShowDataAs udostępnia procent od rodzica, procent od wiersza rodzica, procent od kolumny rodzica, procent od sumy narastającej oraz rangę rosnącą lub malejącą. Te tryby korzystają z modelu rozszerzeń XLSX i nie emitują niewspieranych klasycznych wartości pól danych BIFF

Inspekcja istniejącego skoroszytu przestawnego

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;
      

Tematy pokrewne

• IXLSWorkbook interfejs
• TXLSWorksheet klasa
• InteractiveFeatures demo
• klasyczny XLS metadane skoroszytu zachowanie