PivotTable-API

HotXLS unterstützt typisierte PivotTable-Inspektion, -Authoring und -Ergebnismaterialisierung für XLS- und XLSX-Arbeitsmappen. Das beziehungsbasierte XLSX-Laden löst beliebige Paketteil-Namen auf und stellt Cache-Datensätze, gemeinsame Items, Feldmetadaten, Filter, Gruppierung, berechnete Definitionen, Formate und Erweiterungen im öffentlichen Modell wieder her. Klassischer XLS-Input hält seine ursprünglichen Pivot-Datensätze für getreue Save-Roundtrips bereit

Round-Trip und typisiertes Modell

Enthält eine klassische Arbeitsmappe bereits PivotTables, bewahrt der Reader die rohen BIFF-Datensätze und legt zusätzlich ein typisiertes Modell offen. Tabellen und Caches aus einer klassischen Datei behalten FromRawBlobs = True, sodass die Save-Ausgabe zur Wahrung der Treue die ursprünglichen Bytes wiedergibt. XLSX-Pivots werden über die Beziehungen ihres besitzenden Pakets geladen und gespeichert, während programmatisch erstellte Pivots den typisierten Writer verwenden

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;
      

Einstiegspunkte für Arbeitsblatt und Arbeitsmappe

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 registriert einen Zahlenformat-String und liefert seinen BIFF8-ifmt-Index — genau das, was TXLSPivotDataField.NumberFormat und TXLSPivotCacheField.NumberFormat erwarten; dieselbe Zeichenfolge liefert bei erneuter Registrierung denselben Index

DestRow und DestCol sind 1-basiert wie Cells[Row, Col], AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') verankert die PivotTable also bei A3. Auch die Positionsangaben auf TXLSPivotTable (FirstRow .. FirstDataCol) und der Quellbereich auf TXLSPivotCache sind 1-basiert, genau wie in der XLSX-Engine; liegt der Anker außerhalb des Arbeitsblatts mit 65,536 x 256 Zellen, liefert die Funktion nil zurück

Ein Drag-and-drop-Pivot-Layout bauen

TXLSPivotDesigner ist ein nicht-visuelles Layoutmodell, das Feldlisten-Oberflächen für VCL, FMX, Web oder Kommandozeile bedienen kann, ohne die Pivot-Logik an ein UI-Toolkit zu koppeln

Jedes Feld meldet eine verfügbare, Zeilen-, Spalten-, Filter- oder Wertzone plus seine deterministische Position; MoveField wendet einen Drop an, SetAggregation ändert ein Wertfeld, Validate meldet unvollständige Layouts, und Apply validiert und materialisiert das Ergebnis über den normalen Pivot-Writer

Neue numerische und Datums-Wertfelder haben standardmäßig xlpaSum, während Text-, Boolean-, Fehler-, leere und gemischte Felder standardmäßig xlpaCount erhalten

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-Tabelle erstellen

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;
      

Werte anzeigen als

Setzen Sie ShowDataAs auf jedem Datenfeld, bevor Sie Make aufrufen oder die Arbeitsmappe speichern. Difference, Percent und PercentDiff benötigen einen nullbasierten BaseField-Pivotfeld-Index plus ein BaseItem. RunTotal benötigt nur BaseField. Zeilen-, Spalten-, Gesamt- und Index-Modi leiten ihre Nenner aus den Quelldatensätzen mit der konfigurierten Aggregation ab

Ein explizites BaseItem ist ein nullbasierter Item-Index innerhalb des Basis-Pivotfelds. Verwenden Sie xlPivotBaseItemPrevious oder xlPivotBaseItemNext für Vergleiche mit benachbarten Items. Fehlende Vergleichsschnittpunkte bleiben leer, während ein vorhandener Nenner null #DIV/0! ergibt

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

Filtern, Gruppieren und Sortieren

Make wendet ausgeblendete Items und Seitenauswahl an, bevor die Domäne konstruiert wird, und wertet dann Beschriftungs-, Datums-, Anzahl-, Prozent-, Summen- und Wertfilter aus. Numerische Bereiche, Kalendergruppen und diskrete Gruppen nutzen die Gruppierungsmetadaten der Cache-Felder vor der Aggregation. SortType wählt eine stabile Beschriftungsordnung, SortDataField das nullbasierte Wertfeld für eine stabile Datenwert-Ordnung

Berechnete Ausdrücke

Berechnete Cache-Felder laufen je Quelldatensatz einmal, berechnete Items laufen über die Aggregate der Geschwister-Items, und berechnete Member mit MemberType = 'data' ergänzen virtuelle Wertfelder nach der Aggregation der Quelldaten. Formelnamen unterscheiden nicht zwischen Groß- und Kleinschreibung und dürfen geklammert oder in Anführungszeichen gesetzt sein. Arithmetik, Vergleiche, Prozentwerte, Klammern sowie gängige numerische und logische Funktionen werden unterstützt, mit wiederholten Abhängigkeitsdurchläufen für zyklenfreie berechnete Definitionen

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

Zwischensummen und Gesamtergebnisse

Jedes Zeilen- oder Spaltenfeld kann über Subtotals und SubtotalTop jede konfigurierte Zwischensummenfunktion ober- oder unterhalb seiner Detail-Items materialisieren. RowGrandTotals und ColumnGrandTotals steuern die beim Speichern der Arbeitsmappe gesetzte Richtlinie, während MaterializeGrandTotalRow und MaterializeGrandTotalColumn die Ergebnis-Callbacks in eine explizite Gesamtergebnis-Ausgabe einschalten, ohne die Quelldatensätze erneut zu durchlaufen

Erweiterte Werte-anzeigen-Modi

ExtendedShowDataAs bietet Prozent vom übergeordneten Element, Prozent von übergeordneter Zeile, Prozent von übergeordneter Spalte, Prozent vom laufenden Total sowie auf- oder absteigenden Rang. Diese Modi nutzen das XLSX-Erweiterungsmodell und emittieren keine nicht unterstützten klassischen BIFF-Datenfeldwerte

Eine vorhandene Pivot-Arbeitsmappe untersuchen

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;
      

Verwandte Themen

• IXLSWorkbook-Schnittstelle
• TXLSWorksheet-Klasse
• InteractiveFeatures Demo
• Beibehaltung von Metadaten klassischer XLS-Arbeitsmappen